View source for PostgreSQL-Database/C2/Effective-Use-of-AI-in-PostgreSQL/English

'''Title of the Script:''' Effective Use of AI in PostgreSQL.
 
'''Author:''' Anjali Satdive / EduPyramids.

'''Keywords:''' PostgreSQL, PgAdmin4, Claude AI, Create table, EduPyramids, Video Tutorial.

{| border=1
|-
! width="35%" | '''Visual Cue'''
! width="65%" | '''Narration'''

|-
|| '''Slide 1'''

'''Title Slide'''

|| Welcome to this spoken tutorial on '''Effective Use of AI in PostgreSQL.'''

|-
|| '''Slide 2'''

'''Learning Objectives'''

|| In this tutorial, we will learn how to:

* Use '''AI''' to generate SQL queries.
* Execute and verify the AI-generated queries in '''pgAdmin4'''.

|-
|| '''Slide 3'''

'''Disclaimer'''

Please verify AI-generated SQL queries for correctness and security before execution.
|| Please verify AI-generated SQL queries for correctness and security before execution.

|-
|| '''Slide 4'''

'''System Requirements'''

•  '''Ubuntu 24.04 LTS'''

• '''PostgreSQL version 18.1''',

• '''PgAdmin4 version 9.11'''

• '''Firefox web browser v141.0''' and

• A Working internet connection.

EduPyramids periodically verifies the correctness of the tutorials for subsequent versions. Changes, if any, are explained through the Instruction Sheet.

|| To record this tutorial, I am using the following setup.

|-

|| '''Slide 5'''

'''Pre-requisites'''

|| To follow this tutorial,
* Learners should have '''PostgreSQL''' and '''pgAdmin4'''.

* For the prerequisite '''PostgreSQL''' tutorials, please visit this website.

|-
|| '''Slide 6'''

'''Code Files'''

The following code file is required to practice this tutorial.

'''eai-commands.txt'''

This file is provided in the Code Files link of this tutorial page.

Please download and extract the file.

|| The following '''Code File''' is required to practice this tutorial.

This file is provided in the '''Code Files''' link of this tutorial page.
Please download and extract the file.

|-
|| '''Slide 7'''

'''How Claude AI Works'''

|| '''LLMs''' such as '''Claude AI''' identify patterns in language queries.

They understand the meaning and context of user input.
This helps generate relevant '''SQL''' queries and responses.

|-
|| '''Open pgAdmin4'''

|| Let us get started.

I have opened '''pgAdmin4''' and connected to the '''PostgreSQL''' server.
We are using the '''Company database'''.

|-
|| Type:

Press '''Enter'''.

|| I am using Claude AI to generate SQL queries from the plain English instructions.
Users may use any AI tool they are comfortable with.
Let us open '''Firefox web browser'''.
In the address bar type this URL and press '''Enter'''.
Claude's AI window opens.

|-

|| Type:

'''Write a simple CREATE TABLE SQL query for a projects table with project_id (serial, primary key), project_name (varchar 50), team_name (varchar 30), budget (integer), status (varchar 20). No extra constraints.'''
Point to the generated code.

Click on the '''Execute query''' button.

|| We will create a new table named projects.

In the prompt box type the following prompt and press '''Enter'''.

The code gets generated.
Let us copy and paste this generated query in the '''pgAdmin4''' query window.

Click on the '''Execute query icon'''.
This query creates a projects table with project details.

|-

|| Type in pgAdmin4:

'''INSERT INTO projects(project_name, team_name, budget, status) VALUES ('Inventory System','Development',75000,'completed'), ('Website Redesign','UI Team',45000,'ongoing'), ('Mobile App','Development',90000,'completed');'''
Click on the '''Execute query icon'''.

|| Let us insert sample records into the table.

Type the following query.

Click on the '''Execute query icon'''.

The records are inserted successfully in the projects table.
.

|-
|| Type:

*SELECT * FROM projects;'''

Click on the '''execute query button.

|| We will now type this query.

Let us execute the query to see the created table.
Here is the '''projects''' table with 3 records.

|-
|| 

|| Let us switch back to '''Claude AI'''.

|-

|| Type on the prompt:

'''Write a PostgreSQL query to display completed projects from the projects table.'''

Press '''Enter'''.

|| Type the following prompt and press '''Enter'''.

This prompt clearly describes the required output.

|-

|| Show Claude generated output.

Copy and paste the output.
In the pgAdmin4.

Click on the '''Execute query icon'''.

|| AI generates the query and retrieves projects with completed status.

Let us copy and paste this generated query in '''pgAdmin4.'''
Click on the '''Execute query icon'''.

|-

|| Show output.

|| The output displays completed projects from the projects table.

Here, only the matching records are displayed.

|-
|| Enter prompt:

'''Write a PostgreSQL query to display projects with a budget greater than 50000.'''
Press '''Enter'''.
Show generated output.
Copy and paste the output.
In the '''pgAdmin4'''.

|| Let us generate another query using '''Claude AI'''.

Type the following prompt in Claude AI and press '''Enter'''.
Let us copy and paste this generated query in '''pgAdmin4.'''

|-
|| Click on the '''Execute query icon'''.

Highlight output.

|| This query filters projects based on the budget value.

Click on the '''Execute query icon'''.

The output shows projects whose budget is greater than 50000.

|-
|| Type in pgAdmin4: 

'''SELECT project_name budget FROM projects;'''

Click on the '''Execute query icon'''.

|| Type this query in the query window.

Click on the '''Execute query icon'''.
Observe the output carefully.
The column name is shown as budget, but the displayed values are project names.

|-

|| Point to the output.

|| Sometimes, queries may execute even when they are not written clearly.

|-

|| Type in Claude AI: 

'''SELECT project_name budget FROM projects;'''

Press '''Enter'''.

Copy and paste the given query in pgAdmin4.
Click on the '''Execute query icon'''.

|| Now let us type the following prompt in '''Claude AI''' and press '''Enter'''.

Claude AI shows the following output.
Observe that the comma between the column names is missing.

Let us copy and paste this correctly generated query in '''pgAdmin4.'''

Click on the '''Execute query icon'''.
Observe that correct output is shown.

|-
|| '''Slide 8'''

'''Summary'''

In this tutorial, we have learnt how to:
• Use '''AI''' to generate SQL queries.
• Execute and verify the AI-generated queries in '''pgAdmin4'''.

|| With this we come to the end of this tutorial.

Let us summarise.

|-

|| '''Slide 9'''

'''Assignment'''

As an assignment,

Ask Claude AI to write a query to add a new column named project_deadline in the '''projects''' table.

|| We encourage you to try this assignment.

|-

|| '''Slide 10'''

'''Thank you'''

This '''Spoken Tutorial''' is brought to you by '''EduPyramids Educational Services Private Limited, SINE IIT, Bombay'''.
|| Thank you.

|}