Use ChatGPT For SQL Queries

The AI bot can be used to learn T-SQL for the junior Developer or provide more complex queries to save time on coding. Here we are using the WideWordImporters sample database created by Microsoft. Chat GPT is already aware of the database so we do not need to upload or import it to request query samples.

SQL Query WideWordImporters

Right off the bat we need to see what tables are available. We can ask for the table list and then begin asking for queries to the data.

Prompt:
please create a list of the table names in the WideWorldImporters database.

Response:
    Application.Cities
    Application.Countries
    Application.DeliveryMethods
    Application.People
    Application.StateProvinces
    Purchasing.PurchaseOrders
    Purchasing.PurchaseOrderLines
    Sales.CustomerCategories
    Sales.Customers
    Sales.Invoices
    Sales.InvoiceLines
    Sales.OrderLines
    Sales.Orders
    Sales.SpecialDeals
    Warehouse.Colors
    Warehouse.PackageTypes
    Warehouse.StockGroups
    Warehouse.StockItemHoldings
    Warehouse.StockItems
    Warehouse.StockItemStockGroups
    Warehouse.StockItemTransactions
    Warehouse.Suppliers
    Website.IncomingOrders

Prompt:
You are a report developer. Using the WideWordImporters database, create a query that includes the sale details, customer details and salesman involved

Response:
SELECT 
  CONCAT(c.CustomerName, ' (', c.CustomerID, ')') AS Customer, 
  CONCAT(p.FullName, ' (', p.PersonID, ')') AS Salesman, 
  i.InvoiceID, 
  i.InvoiceDate, 
  i.DueDate, 
  i.TotalValue 
FROM 
  Sales.Invoices i 
  JOIN Sales.Customers c ON i.CustomerID = c.CustomerID 
  JOIN Sales.People p ON i.SalespersonPersonID = p.PersonID;

Learn T-SQL Basics

We can already see that natural language processing (NLP) is understood well enough to provide a working statement with JOIN’s too.
In addition the AI bot will provide an explanation, if possible, for the code provided. This is great for someone who is not as familiar with things like aggregates, JOIN’s and GROUP’s.

Prompt:
Please create a query to return the total number of sales per state including the total sale amount

Response:
SELECT 
  c.StateProvince, 
  COUNT(i.InvoiceID) AS TotalSales, 
  SUM(i.TotalValue) AS TotalSaleAmount 
FROM 
  Sales.Invoices i 
  JOIN Sales.Customers c ON i.CustomerID = c.CustomerID 
GROUP BY 
  c.StateProvince;

This query joins the "Invoices" table with the "Customers" table using their respective IDs. It groups the results by state/province using the GROUP BY clause. The COUNT and SUM functions are used to retrieve the total number of sales and total sale amount, respectively, for each state/province. You can execute this query in a SQL Server management tool to view the results.

Prompt:
please create a query to report on the average sale amount per item per stat

Response:

SELECT 
  c.StateProvince, 
  i.StockItemID, 
  AVG(i.TotalValue / i.Quantity) AS AverageSaleAmount 
FROM 
  Sales.InvoiceLines i 
  JOIN Sales.Invoices inv ON i.InvoiceID = inv.InvoiceID 
  JOIN Sales.Customers c ON inv.CustomerID = c.CustomerID 
GROUP BY 
  c.StateProvince, 
  i.StockItemID;

This query joins the “InvoiceLines” table with the “Invoices” and “Customers” tables using their respective IDs. It calculates the average sale amount per item per state by dividing the total sale value by the quantity sold for each invoice line, and then taking the average of those values for each stock item and state/province. The GROUP BY clause is used to group the results by state/province and stock item. You can execute this query in a SQL Server management tool to view the results.

Example Chat GPT Prompts to Find Late Deliveries

Prompt:
please create a query that returns the deliveries that were late

Response:
SELECT 
  d.DeliveryID, 
  d.DeliveryDate, 
  d.ActualDeliveryDate, 
  d.ExpectedDeliveryDate 
FROM 
  Warehouse.Deliveries d 
WHERE 
  d.ActualDeliveryDate > d.ExpectedDeliveryDate;

Prompt:
please create a query that returns the deliveries that were late. include the customer contact information for each. also exclude the states of California, Utah and Florida from the results

Response:
SELECT 
  d.DeliveryID, 
  d.DeliveryDate, 
  d.ActualDeliveryDate, 
  d.ExpectedDeliveryDate, 
  c.CustomerName, 
  c.PhoneNumber, 
  c.EmailAddress, 
  c.StreetAddressLine1, 
  c.StreetAddressLine2, 
  c.City, 
  c.StateProvince, 
  c.Country, 
  c.PostalCode 
FROM 
  Warehouse.Deliveries d 
  JOIN Sales.Orders o ON d.OrderID = o.OrderID 
  JOIN Sales.Customers c ON o.CustomerID = c.CustomerID 
WHERE 
  d.ActualDeliveryDate > d.ExpectedDeliveryDate AND 
  c.StateProvince NOT IN ('California', 'Utah', 'Florida');

Prompt:
please rewrite this query and add the total sales for each state

Response:
SELECT 
  d.DeliveryID, 
  d.DeliveryDate, 
  d.ActualDeliveryDate, 
  d.ExpectedDeliveryDate, 
  c.CustomerName, 
  c.PhoneNumber, 
  c.EmailAddress, 
  c.StreetAddressLine1, 
  c.StreetAddressLine2, 
  c.City, 
  c.StateProvince, 
  c.Country, 
  c.PostalCode,
  SUM(i.TotalValue) AS TotalSales
FROM 
  Warehouse.Deliveries d 
  JOIN Sales.Orders o ON d.OrderID = o.OrderID 
  JOIN Sales.Invoices i ON o.OrderID = i.OrderID 
  JOIN Sales.Customers c ON i.CustomerID = c.CustomerID 
WHERE 
  d.ActualDeliveryDate > d.ExpectedDeliveryDate AND 
  c.StateProvince NOT IN ('California', 'Utah', 'Florida')
GROUP BY 
  d.DeliveryID, 
  d.DeliveryDate, 
  d.ActualDeliveryDate, 
  d.ExpectedDeliveryDate, 
  c.CustomerName, 
  c.PhoneNumber, 
  c.EmailAddress, 
  c.StreetAddressLine1, 
  c.StreetAddressLine2, 
  c.City, 
  c.StateProvince, 
  c.Country, 
  c.PostalCode;

This modified query includes a SUM function that calculates the total sales for each state for all late deliveries. The GROUP BY clause is used to group the results by delivery, customer, and state. The rest of the query remains the same, retrieving information such as the delivery ID, delivery date, actual delivery date, and expected delivery date for all late deliveries, along with the customer name, phone number, email address, street address, city, country, postal code, and total sales for each state. You can execute this query in a SQL Server management tool to view the results.

More T-SQL ChatGPT Prompts for Learning T-SQL

Prompt:
1. provide and example of replacing 2 characters in the customer last name field
2. create a query to find all deliveries and convert the delivery date to the UTC timezone
3. create a query that will provide a different color for each state
4. create a query to show the average sales per item per month
5. create a report for the top selling 5 States and the sales amount, include the number of late delivers and exclude the States of Nevada, Ohio and Texas

---