Difference between revisions of "PostgreSQL-Database/C2/Troubleshooting-and-Monitoring-with-AI/English"

From Script | Spoken-Tutorial
Jump to: navigation, search
Line 30: Line 30:
  
 
|-
 
|-
−
|| '''Disclaimer Slide 3'''
+
|| '''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.
Line 40: Line 42:
 
'''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 59: Line 61:
 
'''Pre-requisites'''
 
'''Pre-requisites'''
  
−
https://edupyramids.org/
+
https://EduPyramids.org/
  
 
|| To follow this tutorial,
 
|| To follow this tutorial,
Line 74: Line 76:
 
The following code file is required to practice this tutorial:
 
The following code file is required to practice this tutorial:
  
−
• '''tm-commands.txt'''
+
* '''tm-commands.txt'''
  
 
This file is provided in the Code Files link on this tutorial page.
 
This file is provided in the Code Files link on this tutorial page.
Line 109: Line 111:
 
SELECT * FROM projects;'''
 
SELECT * FROM projects;'''
  
−
Click on the '''Execute Query icon**.
+
Click on the '''Execute Query icon.
  
 
|| Type the following query.
 
|| Type the following query.
Line 120: Line 122:
 
|| Type:  
 
|| Type:  
  
−
*SELECT COUNT(*) FROM projects;'''
+
SELECT COUNT(*) FROM projects;'''
  
−
Click on the '''Execute Query icon**.
+
Click on the '''Execute Query icon.
 
Highlight output.
 
Highlight output.
  
Line 159: Line 161:
 
Explain the purpose of SELECT COUNT(*) in PostgreSQL'''
 
Explain the purpose of SELECT COUNT(*) in PostgreSQL'''
  
−
Press '''Enter**.
+
Press '''Enter.
  
 
|| In the prompt box, type the following prompt and press '''Enter'''.
 
|| In the prompt box, type the following prompt and press '''Enter'''.
Line 177: Line 179:
 
SELECT * FROM projects WHERE budget > 'high';'''
 
SELECT * FROM projects WHERE budget > 'high';'''
  
−
Click on the '''Execute Query icon**.
+
Click on the '''Execute Query icon.
  
 
|| In '''pgAdmin4''' type the following query.
 
|| In '''pgAdmin4''' type the following query.
Line 186: Line 188:
  
 
This is because the budget column contains numeric values.
 
This is because the budget column contains numeric values.
 +
 
Datatype mismatches occur when incompatible values are used together.
 
Datatype mismatches occur when incompatible values are used together.
  
Line 194: Line 197:
 
|| Type: Fix this PostgreSQL query:  
 
|| Type: Fix this PostgreSQL query:  
  
−
SELECT * FROM projects WHERE budget > 'high';**
+
SELECT * FROM projects WHERE budget > 'high';
  
 
|| Let us use '''Claude AI''' to understand and correct this query.
 
|| Let us use '''Claude AI''' to understand and correct this query.
Line 209: Line 212:
  
  
−
*SELECT * FROM projects WHERE budget > 50000;'''
+
SELECT * FROM projects WHERE budget > 50000;'''
  
−
Click on the '''Execute query icon**.
+
Click on the '''Execute query icon.
  
 
|| Let us copy the correct query and paste into '''pgAdmin4'''.
 
|| Let us copy the correct query and paste into '''pgAdmin4'''.
Line 229: Line 232:
 
|| Type:  
 
|| Type:  
  
−
*SELECT * FROM projects ORDER budget DESC;'''
+
SELECT * FROM projects ORDER budget DESC;'''
  
−
Click on the '''Execute query icon**.
+
Click on the '''Execute query icon.
  
 
Highlight error.
 
Highlight error.
Line 253: Line 256:
 
|| Type in Claude AI:  
 
|| Type in Claude AI:  
  
−
Why does this query gives syntax error: SELECT * FROM projects ORDER budget DESC;**
+
Why does this query gives syntax error: SELECT * FROM projects ORDER budget DESC;  
  
 
|| Type this prompt and press '''Enter'''.
 
|| Type this prompt and press '''Enter'''.
Line 263: Line 266:
 
|| Copy and paste the correct query given by '''Claude AI'''.
 
|| Copy and paste the correct query given by '''Claude AI'''.
  
−
*SELECT * FROM projects ORDER BY budget DESC;'''
+
SELECT * FROM projects ORDER BY budget DESC;'''
  
−
Click on the '''Execute query icon**.
+
Click on the '''Execute query icon.
  
 
Highlight the output.
 
Highlight the output.
Line 291: Line 294:
 
In this tutorial, we have learnt how to:
 
In this tutorial, we have learnt how to:
  
−
• Monitor table records using SQL queries
+
* Monitor table records using SQL queries
  
−
• Count records in a PostgreSQL table
+
* Count records in a PostgreSQL table
  
−
• Use AI tools to troubleshoot and monitor SQL queries
+
* Use AI tools to troubleshoot and monitor SQL queries
  
 
|| With this we come to the end of this tutorial.
 
|| With this we come to the end of this tutorial.
Line 308: Line 311:
 
'''As an assignment,'''
 
'''As an assignment,'''
  
−
• Write a query to display projects with budget less than 50000
+
* Write a query to display projects with budget less than 50000
  
−
• Use Claude AI to explain the query
+
* Use AI of your choice to explain the query.
  
−
• Execute and verify the output in pgAdmin4
+
* Execute and verify the output in pgAdmin4
  
−
• Add intentional mistakes in a query and use AI to debug it.
+
* Add intentional mistakes in a query and use AI to debug it.
  
 
|| We encourage you to try this assignment.
 
|| We encourage you to try this assignment.

Revision as of 16:03, 20 May 2026

Title of the Script: Troubleshooting and Monitoring with AI.

Author: EduPyramids.

Keywords: PostgreSQL, pgAdmin4, Claude AI, SQL Troubleshooting, SQL Monitoring, COUNT function, ORDER BY clause, AI in PostgreSQL, Query Errors.


Visual Cue Narration
Slide 1

Title Slide

Welcome to this spoken tutorial on Troubleshooting and Monitoring with AI.
Slide 2

Learning Objectives

In this tutorial, we will learn how to:
  • Monitor table records using SQL queries.
  • Count records in a PostgreSQL table.
  • Use AI tools to troubleshoot and monitor SQL queries.
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:

  • tm-commands.txt

This file is provided in the Code Files link on 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.

Show pgAdmin4.

Highlight company database.

Let us begin.

I have opened pgAdmin4 and connected to the PostgreSQL server.

We are using the Company database.

The projects table already contains project records.

Let us monitor the records stored in the projects table.

Type:

SELECT * FROM projects;

Click on the Execute Query icon.

Type the following query.

Click on the Execute Query icon.

The output displays all records from the projects table.

Type:

SELECT COUNT(*) FROM projects;

Click on the Execute Query icon. Highlight output.

Let us check the total number of records in the table.

Type the following query.

Click on the Execute Query icon.

The output displays the total number of records in the projects table.

Let us use Claude AI to understand this query.

Users may use any AI tool they are comfortable with.

Open Firefox web browser.

Type: https://claude.ai/new

Show Claude AI interface.

Open Firefox web browser.

In the address bar, type this URL and press Enter.

Claude AI opens.

Type:

Explain the purpose of SELECT COUNT(*) in PostgreSQL

Press Enter.

In the prompt box, type the following prompt and press Enter.
Highlight output. Claude AI explains that COUNT is used to count the number of rows in a table.

Now let us look at a troubleshooting example.

Type:

SELECT * FROM projects WHERE budget > 'high';

Click on the Execute Query icon.

In pgAdmin4 type the following query.

Click on the Execute Query icon.

Observe that the query does not return the expected result.

This is because the budget column contains numeric values.

Datatype mismatches occur when incompatible values are used together.

This may cause errors or incorrect query results.

Type: Fix this PostgreSQL query:

SELECT * FROM projects WHERE budget > 'high';

Let us use Claude AI to understand and correct this query.

Type this following prompt and press Enter.

Claude AI explains that the budget column contains numeric values.

It should be compared using a numeric value instead of text.

paste into pgAdmin4 :


SELECT * FROM projects WHERE budget > 50000;

Click on the Execute query icon.

Let us copy the correct query and paste into pgAdmin4.

Here, we will replace the value according to our table records.

Click on the Execute query icon.

Highlight the output. The corrected query now displays projects with a budget greater than 50000.
Type:

SELECT * FROM projects ORDER budget DESC;

Click on the Execute query icon.

Highlight error.

Type this following query.

Click on the Execute query icon.

Notice that we are getting the syntax error.

We introduced this syntax error intentionally.

Open Claude AI window. Let us use Claude AI to understand why this happens.
Type in Claude AI:

Why does this query gives syntax error: SELECT * FROM projects ORDER budget DESC;

Type this prompt and press Enter.

Observe that the AI generated answer shows BY keyword is missing in the query.

Copy and paste the correct query given by Claude AI.

SELECT * FROM projects ORDER BY budget DESC;

Click on the Execute query icon.

Highlight the output.

Copy and paste the correct query given by Claude AI.

Click on the Execute query icon.

The query successfully runs.

AI tools improve developer productivity and speed up coding tasks.

However, debugging and logical reasoning skills are still essential.

Slide 7

Summary

In this tutorial, we have learnt how to:

  • Monitor table records using SQL queries
  • Count records in a PostgreSQL table
  • Use AI tools to troubleshoot and monitor SQL queries
With this we come to the end of this tutorial.

Let us summarise.

Slide 8

Assignment

As an assignment,

  • Write a query to display projects with budget less than 50000
  • Use AI of your choice to explain the query.
  • Execute and verify the output in pgAdmin4
  • Add intentional mistakes in a query and use AI to debug it.
We encourage you to try this assignment.
Slide 9

Thank you

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

Thank you.

Thank you for joining.

Contributors and Content Editors

ANJALISATDIVE, Madhurig

Retrieved from "https://script.spoken-tutorial.org/index.php?title=PostgreSQL-Database/C2/Troubleshooting-and-Monitoring-with-AI/English&oldid=57812"