How to connect ChatGPT to a SQL database for data retrieval and analysis
Tags: writing, learning, python, datascience
Published: 2024-03-23 02:31:46


* What is chatGPT?
* How chatGPT works
* How to use ChatGPT in Data Analysis

##What is chatGPT?
ChatGPT is an advanced language model that can be used to generate tasks. It can also be used to understand tasks. ChatGPT can be used to write contents, emails, and articles, even to the extent of writing and explaining codes. ChatGPT can be used to generate data, unit codes, and train machine learning modules.

##How chatGPT works
To be able to work with chatGPT, head over to [ChatGPT] (https://chat.openai.com/) and sign up if you haven't already. If you have signed up, all you need to do is log in

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9eafnib70lrxehir3pog.PNG)
Once you click on Sign Up or Log in, it brings you the sign-up or log-in page where you will have to sign in with your Email, Apple or Microsoft account 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c7a00esd5z1nofm94emn.PNG)
After sign-in, you will get to see the next page where you have to input your prompt and start getting responses from ChatGPT

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/48elimzomy35awc2h2dw.PNG)

One thing to know is that once you prompt chatGPT, it can always refer back to the question you asked it previously, provided you are still on the same page.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bpkk4f7ghxzp0s7amz42.PNG)

##ChatGPT for Data Analysis
Now let's use ChatGPT for data analysis. 
First, we will go to [w3schools](https://www.w3schools.com/sql) to get data sets. Click on the try it yourself button. it will bring you to the image below.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/go9re8secttvuk3lan09.PNG)

Change the customers to products, then click on the "Run SQL" button

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/su6tif4egxyovthg5v5t.PNG)

Copy some entries from the table, including the headers, 
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mpo827n27qigon21eo6j.PNG)
Go to chatGPT and paste them. 
Instruct it that this is the product table. It is going to bring out the results of the interpretation of the table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sqed9g3gtzwa76isunar.PNG)
From here, we can ask it any question of our choice. 
First, let's ask about the product with the highest price.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fj0bj8oa9w5391k3ptcq.PNG)
If you go over to the table, you will find out that the highest price is 40 for Northwood Cranberry Sauce. Indeed, it is correct.

Ask for the lowest price. 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xjc1ow19pq4xcj2hpvsp.PNG)

You can also prompt ChatGPT for the average of the products. It will not only give you the average, it will also show you how to calculate it.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kzpmj2jdo1ffm0xulx4g.PNG)
Lets go ahead and solve this using SQL. 
```
SELECT AVG(Price) AS AveragePrice FROM products;
```
Let prompt chatGPT write the SQL query for the product with the highest price 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/91uzgj842smyb92o9h5b.PNG)

Prompt ChatGPT to get the product with the lowest price

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2i6hkkltzqsqu2c7n0ui.PNG)

Tell it to calculate the average

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2wphr79rol2sywdqi421.PNG)
Now it's time to make things a little bit harder. So we have to make ChatGPT join two tables together. So go back to [w3schools](https://www.w3schools.com/sql) to get the OrderDetails table

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dcoa5mdlszphtio3rg27.PNG)

Then copy the values you get there and paste them in ChatGPT. You will get something like this below:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/29t6smy3rzdqcwklofo1.PNG)
Also, do same for orders table and then suppliers table

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/59esjy8y1y4j1nha6suw.PNG)
Finally, you do the suppliers table

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ftrwwnujc6lzwx7q3z58.PNG)
Continue by asking ChatGPT to write SQL query to calculate the averageproduct price per supplier. The code below is useful:
```
SELECT s.SupplierName, AVG(p.Price) AS AvgPrice
FROM Suppliers s
JOIN Products p ON s.SupplierID = p.SupplierID
GROUP BY s.SupplierName
ORDER BY AvgPrice DESC;

```
You can see that the query above first joins the “Suppliers” and “Products” tables on the SupplierID field. It then groups the results by SupplierName and calculates the average price for each group. 

Finally, it orders the results by AvgPrice in descending order. 

Replace the table and column names if they are different in your database.

Next, write an SQL query that gets the products that achieved the highest revenue
```
SELECT p.ProductName, SUM(od.Quantity * p.Price) AS Revenue
FROM Products p
JOIN OrderDetails od ON p.ProductID = od.ProductID
GROUP BY p.ProductName
ORDER BY Revenue DESC;

```
This query first joins the “Products” and “OrderDetails” tables on the ProductID field. It then groups the results by ProductName and calculates the total revenue for each product (which is the sum of the product of Quantity and Price for each order detail). 

Finally, it orders the results by Revenue in descending order. 

Replace the table and column names if they are different in your database. If you want to limit the result to the top product, you can add a LIMIT 1 clause at the end of the query.

Next, ChatGPT is going to write an SQL query that gets the employee that made the highest sales from the table above
```
SELECT e.EmployeeID, SUM(od.Quantity * p.Price) AS Sales
FROM Employees e
JOIN Orders o ON e.EmployeeID = o.EmployeeID
JOIN OrderDetails od ON o.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID
GROUP BY e.EmployeeID
ORDER BY Sales DESC
LIMIT 1;

```
Finally, you can prompt ChatGPT to use window functions and subqueries by asking it about employees that made the second highest sales. Check out the code below:
```
SELECT EmployeeID, Sales
FROM (
    SELECT e.EmployeeID, SUM(od.Quantity * p.Price) AS Sales,
    DENSE_RANK() OVER (ORDER BY SUM(od.Quantity * p.Price) DESC) as SalesRank
    FROM Employees e
    JOIN Orders o ON e.EmployeeID = o.EmployeeID
    JOIN OrderDetails od ON o.OrderID = od.OrderID
    JOIN Products p ON od.ProductID = p.ProductID
    GROUP BY e.EmployeeID
) AS EmployeeSales
WHERE SalesRank = 2;

```
first of all, Sure, to find the employee that made the second highest sales, you can use a subquery to rank employees by sales and then select the one with the second highest sales. Here is an SQL query that gets the employee that achieved the second highest sales:

```sql
SELECT EmployeeID, Sales
FROM (
    SELECT e.EmployeeID, SUM(od.Quantity * p.Price) AS Sales,
    DENSE_RANK() OVER (ORDER BY SUM(od.Quantity * p.Price) DESC) as SalesRank
    FROM Employees e
    JOIN Orders o ON e.EmployeeID = o.EmployeeID
    JOIN OrderDetails od ON o.OrderID = od.OrderID
    JOIN Products p ON od.ProductID = p.ProductID
    GROUP BY e.EmployeeID
) AS EmployeeSales
WHERE SalesRank = 2;
```

You can see that this query first joins the "Employees", "Orders", "OrderDetails", and "Products" tables on the respective ID fields. 

It then groups the results by `EmployeeID` and calculates the total sales for each employee (which is the sum of the product of `Quantity` and `Price` for each order detail). 

The `DENSE_RANK()` function is used to rank the employees by their sales in descending order. 

Finally, the outer query selects the employee with a `SalesRank` of 2, which represents the second highest sales. 

Do well to replace the table and column names if they are different in your database. 

If you want to get the employee name instead of the ID, you can replace `e.EmployeeID` with `e.EmployeeName` in the SELECT clause, assuming `EmployeeName` is the correct column name in your "Employees" table.

In conclusion, ChatGPT makes work easier and faster. But don't rely too much on it. You can further instruct it to do more for you if you are not satisfied.