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

From Script | Spoken-Tutorial
Jump to: navigation, search
(Created page with "'''Title of the Script:''' Troubleshooting and Monitoring with AI. '''Author:''' EduPyramids. '''Keywords''': PostgreSQL, pgAdmin4, Claude AI, SQL Troubleshooting, SQL Monit...")
 
 
(3 intermediate revisions by 2 users not shown)
Line 3: Line 3:
 
'''Author:''' EduPyramids.
 
'''Author:''' EduPyramids.
  
−
'''Keywords''': PostgreSQL, pgAdmin4, Claude AI, SQL Troubleshooting, SQL Monitoring, COUNT function, ORDER BY clause, AI in PostgreSQL, Query Errors.
+
'''Keywords''': PostgreSQL, pgAdmin4, Claude AI, SQL Troubleshooting, SQL Monitoring, COUNT function, ORDER BY clause, AI in PostgreSQL, Query Errors, EduPyramids, video tutorial.
 +
 
  
  
Line 30: Line 31:
  
 
|-
 
|-
−
|| '''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 43:
 
'''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 58: Line 61:
  
 
'''Pre-requisites'''
 
'''Pre-requisites'''
 +
 +
https://EduPyramids.org/
  
 
|| To follow this tutorial,
 
|| To follow this tutorial,
Line 72: Line 77:
 
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 103: Line 108:
  
 
|-
 
|-
−
|| Type: **SELECT * FROM projects;'''
+
|| Type:  
 +
 
 +
SELECT * FROM projects;'''
  
−
Click on the '''Execute Query icon**.
+
Click on the '''Execute Query icon.
  
 
|| Type the following query.
 
|| Type the following query.
Line 116: Line 123:
 
|| 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 139: Line 146:
 
|| Open '''Firefox''' web browser.
 
|| Open '''Firefox''' web browser.
  
−
Type:
+
Type: https://claude.ai/new
  
 
Show '''Claude AI''' interface.
 
Show '''Claude AI''' interface.
Line 151: Line 158:
 
|-
 
|-
  
−
|| Type: **Explain the purpose of SELECT COUNT(*) in PostgreSQL'''
+
|| Type:  
  
−
Press '''Enter**.
+
Explain the purpose of SELECT COUNT(*) in PostgreSQL'''
 +
 
 +
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 167: Line 176:
 
|-
 
|-
  
−
|| Type: **SELECT * FROM projects WHERE budget > 'high';'''
+
|| Type:  
  
−
Click on the '''Execute Query icon**.
+
SELECT * FROM projects WHERE budget > 'high';'''
 +
 
 +
Click on the '''Execute Query icon.
  
 
|| In '''pgAdmin4''' type the following query.
 
|| In '''pgAdmin4''' type the following query.
Line 178: Line 189:
  
 
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 184: Line 196:
 
|-
 
|-
  
−
|| Type: **Fix this PostgreSQL query: SELECT * FROM projects WHERE budget > 'high';**
+
|| Type: Fix this PostgreSQL query:  
 +
 
 +
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 198: Line 212:
 
|| paste into pgAdmin4 :  
 
|| paste into pgAdmin4 :  
  
−
*SELECT * FROM projects WHERE budget > 50000;'''
 
  
−
Click on the '''Execute query icon**.
+
SELECT * FROM projects WHERE budget > 50000;'''
 +
 
 +
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 218: Line 233:
 
|| 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 240: Line 255:
 
|-
 
|-
  
−
|| Type in **Claude AI: Why does this query gives syntax error: SELECT * FROM projects ORDER budget DESC;**
+
|| Type in Claude AI:  
 +
 
 +
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'''.
  
−
Observe that the AI generated answer shows  '''BY''' keyword is missing in the query.
+
 
 +
Observe that the '''AI''' generated answer shows  the keyword '''BY''' is missing in the query.
  
 
|-
 
|-
Line 250: Line 268:
 
|| 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 278: Line 296:
 
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 295: Line 313:
 
'''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.

Latest revision as of 11:34, 21 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, EduPyramids, video tutorial.


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 the keyword BY 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=57839"