Difference between revisions of "PostgreSQL-Database/C2/Effective-Use-of-AI-in-PostgreSQL/English"

From Script | Spoken-Tutorial
Jump to: navigation, search
 
(One intermediate revision by the same user not shown)
Line 41: Line 41:
 
'''System Requirements'''
 
'''System Requirements'''
  
−
• '''Ubuntu 24.04 LTS'''
+
* '''Ubuntu 24.04 LTS'''
  
−
• '''PostgreSQL version 18.1''',
+
* '''PostgreSQL version 18.1''',
  
−
• '''PgAdmin4 version 9.11'''
+
* '''PgAdmin4 version 9.11'''
  
−
• '''Firefox web browser v141.0''' and
+
* '''Firefox web browser v141.0''' and
  
−
• A Working internet connection.
+
* '''A Working internet connection'''.
  
 
EduPyramids periodically verifies the correctness of the tutorials for subsequent versions. Changes, if any, are explained through the Instruction Sheet.
 
EduPyramids periodically verifies the correctness of the tutorials for subsequent versions. Changes, if any, are explained through the Instruction Sheet.
Line 60: Line 60:
  
 
'''Pre-requisites'''
 
'''Pre-requisites'''
 +
 +
https://EduPyramids.org
  
 
|| To follow this tutorial,
 
|| To follow this tutorial,
Line 82: Line 84:
  
 
This file is provided in the '''Code Files''' link of this tutorial page.
 
This file is provided in the '''Code Files''' link of this tutorial page.
 +
 
Please download and extract the file.
 
Please download and extract the file.
  
Line 92: Line 95:
  
 
They understand the meaning and context of user input.
 
They understand the meaning and context of user input.
 +
 
This helps generate relevant '''SQL''' queries and responses.
 
This helps generate relevant '''SQL''' queries and responses.
  
Line 100: Line 104:
  
 
I have opened '''pgAdmin4''' and connected to the '''PostgreSQL''' server.
 
I have opened '''pgAdmin4''' and connected to the '''PostgreSQL''' server.
 +
 
We are using the '''Company database'''.
 
We are using the '''Company database'''.
  
 
|-
 
|-
−
|| Type:
+
|| Type:  
 +
 
 +
https://claude.ai/new
 +
 
 +
 
  
 
Press '''Enter'''.
 
Press '''Enter'''.
  
 
|| I am using Claude AI to generate SQL queries from the plain English instructions.
 
|| I am using Claude AI to generate SQL queries from the plain English instructions.
 +
 
Users may use any AI tool they are comfortable with.
 
Users may use any AI tool they are comfortable with.
 +
 
Let us open '''Firefox web browser'''.
 
Let us open '''Firefox web browser'''.
 +
 
In the address bar type this URL and press '''Enter'''.
 
In the address bar type this URL and press '''Enter'''.
 +
 
Claude's AI window opens.
 
Claude's AI window opens.
  
Line 118: Line 131:
  
 
'''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.'''
 
'''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.
 
Point to the generated code.
  
Line 127: Line 141:
  
 
The code gets generated.
 
The code gets generated.
 +
 
Let us copy and paste this generated query in the '''pgAdmin4''' query window.
 
Let us copy and paste this generated query in the '''pgAdmin4''' query window.
  
 
Click on the '''Execute query icon'''.
 
Click on the '''Execute query icon'''.
 +
 
This query creates a projects table with project details.
 
This query creates a projects table with project details.
  
Line 137: Line 153:
  
 
'''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');'''
 
'''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'''.
 
Click on the '''Execute query icon'''.
  
Line 146: Line 163:
  
 
The records are inserted successfully in the projects table.
 
The records are inserted successfully in the projects table.
−
.
+
 
  
 
|-
 
|-
 
|| Type:
 
|| Type:
  
−
*SELECT * FROM projects;'''
+
'''SELECT * FROM projects;'''
  
 
Click on the '''execute query button.
 
Click on the '''execute query button.
Line 158: Line 175:
  
 
Let us execute the query to see the created table.
 
Let us execute the query to see the created table.
 +
 
Here is the '''projects''' table with 3 records.
 
Here is the '''projects''' table with 3 records.
  
 
|-
 
|-
 
||  
 
||  
−
 
 
|| Let us switch back to '''Claude AI'''.
 
|| Let us switch back to '''Claude AI'''.
  
Line 182: Line 199:
  
 
Copy and paste the output.
 
Copy and paste the output.
 +
 
In the pgAdmin4.
 
In the pgAdmin4.
  
Line 188: Line 206:
 
|| AI generates the query and retrieves projects with completed status.
 
|| AI generates the query and retrieves projects with completed status.
  
−
Let us copy and paste this generated query in '''pgAdmin4.'''
+
Let us copy and paste this generated query in '''pgAdmin4'''.
 +
 
 
Click on the '''Execute query icon'''.
 
Click on the '''Execute query icon'''.
  
Line 203: Line 222:
  
 
'''Write a PostgreSQL query to display projects with a budget greater than 50000.'''
 
'''Write a PostgreSQL query to display projects with a budget greater than 50000.'''
 +
 
Press '''Enter'''.
 
Press '''Enter'''.
 +
 
Show generated output.
 
Show generated output.
 +
 
Copy and paste the output.
 
Copy and paste the output.
 +
 
In the '''pgAdmin4'''.
 
In the '''pgAdmin4'''.
  
Line 211: Line 234:
  
 
Type the following prompt in Claude AI and press '''Enter'''.
 
Type the following prompt in Claude AI and press '''Enter'''.
 +
 
Let us copy and paste this generated query in '''pgAdmin4.'''
 
Let us copy and paste this generated query in '''pgAdmin4.'''
  
Line 234: Line 258:
  
 
Click on the '''Execute query icon'''.
 
Click on the '''Execute query icon'''.
 +
 
Observe the output carefully.
 
Observe the output carefully.
 +
 
The column name is shown as budget, but the displayed values are project names.
 
The column name is shown as budget, but the displayed values are project names.
  
Line 240: Line 266:
  
 
|| Point to the output.
 
|| Point to the output.
−
 
 
|| Sometimes, queries may execute even when they are not written clearly.
 
|| Sometimes, queries may execute even when they are not written clearly.
  
Line 252: Line 277:
  
 
Copy and paste the given query in pgAdmin4.
 
Copy and paste the given query in pgAdmin4.
 +
 
Click on the '''Execute query icon'''.
 
Click on the '''Execute query icon'''.
  
Line 257: Line 283:
  
 
Claude AI shows the following output.
 
Claude AI shows the following output.
 +
 
Observe that the comma between the column names is missing.
 
Observe that the comma between the column names is missing.
  
Line 262: Line 289:
  
 
Click on the '''Execute query icon'''.
 
Click on the '''Execute query icon'''.
 +
 
Observe that correct output is shown.
 
Observe that correct output is shown.
  
Line 270: Line 298:
  
 
In this tutorial, we have learnt how to:
 
In this tutorial, we have learnt how to:
−
• Use '''AI''' to generate SQL queries.
+
* Use '''AI''' to generate SQL queries.
−
• Execute and verify the AI-generated queries in '''pgAdmin4'''.
+
* Execute and verify the AI-generated queries in '''pgAdmin4'''.
  
 
|| With this we come to the end of this tutorial.
 
|| With this we come to the end of this tutorial.
Line 285: Line 313:
 
As an assignment,
 
As an assignment,
  
−
Ask Claude AI to write a query to add a new column named project_deadline in the '''projects''' table.
+
Ask AI (of your choice) to write a query to add a new column named project_deadline in the projects table.
  
 
|| We encourage you to try this assignment.
 
|| We encourage you to try this assignment.

Latest revision as of 16:18, 20 May 2026

Title of the Script: Effective Use of AI in PostgreSQL.

Author: EduPyramids.

Keywords: PostgreSQL, PgAdmin4, SQL queries, Claude AI, LLMs, Create table, EduPyramids, Video Tutorial.


Visual Cue 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

https://EduPyramids.org

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:

https://claude.ai/new


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 AI (of your choice) 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.

Contributors and Content Editors

ANJALISATDIVE, Madhurig

Retrieved from "https://script.spoken-tutorial.org/index.php?title=PostgreSQL-Database/C2/Effective-Use-of-AI-in-PostgreSQL/English&oldid=57813"