View source for PostgreSQL-Database/C2/Troubleshooting-and-Monitoring-with-AI/English

'''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.


{| border=1
|-
! width="35%" | '''Visual Cue'''
! width="65%" | '''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.

|}