7 Unusual Uses of Chat-GPT for Text-to-SQL and SQL Queries

As a developer, you may have used natural language processing (NLP) and its potential to revolutionize the way we interact with technology. Text-to-SQL tools powered by OpenAI, are a powerful method that can generate SQL statements from natural language text. In this blog post, we will explore seven creative and unusual examples of how to use ChatGPT to generate SQL statements.

1. Take my Existing Query and Modify it

Here is an example of how you can provide an existing complex SQL to Text-to-SQL and ask it to do modifications:
Removing Specific Results from an Existing SQL Query

Text Prompt (Try it here)

Take the attached SQL query and remove all results that 
happened in the first quarter of 2021.
SELECT product_name, SUM(quantity) AS total_quantity_sold 
FROM sales WHERE 
date BETWEEN '2021-01-01' AND '2021-12-31' 
GROUP BY product_name 
ORDER BY total_quantity_sold DESC;”

Expected Output

SELECT product_name, SUM(quantity) AS total_quantity_sold
FROM sales WHERE date BETWEEN '2021-04-01' AND '2021-12-31'
GROUP BY product_name
ORDER BY total_quantity_sold DESC;

In this example, we provide an existing SQL query that retrieves the total quantity sold for each product in the first quarter of 2021. Text-to-SQL modified the SQL statement to exclude the results from the first quarter of 2021 by changing the date range in the WHERE clause.

2. Not only English! Use your own language

You can generating a SQL Query from Natural Language Input in Any Language. Here is an example in Spanish

Text Prompt (Try it here)

Muestra la cantidad de ventas de productos en cada 
categoría durante el último mes, junto con el nombre 
de la categoría y la fecha de la última venta."

Expected Output

SELECT categories.category_name, COUNT(*) AS sales_count, MAX(orders.order_date) AS last_sale_date
FROM categories
JOIN products ON categories.category_id = products.category_id
JOIN order_details ON products.product_id = order_details.product_id
JOIN orders ON order_details.order_id = orders.order_id
WHERE orders.order_date BETWEEN DATEADD(MONTH, -1, GETDATE()) AND GETDATE()
GROUP BY categories.category_name

This example demonstrates the flexibility of Text-to-SQL in being able to generate SQL queries from natural language input in any language. The text prompt is in Spanish, but Text-to-SQL is still able to understand and translate the query into a valid SQL statement. The query retrieves the number of sales for each product category during the last month, along with the name of the category and the date of the last sale. The query joins the categories, products, order_details, and orders tables and uses aggregate functions to calculate the sales count and last sale date for each category. The results are filtered to only include orders from the last month and grouped by category name.

3. Adding a Filter to an Existing SQL Query

Text Prompt (Try it here)

Take the attached SQL query and add a filter 
to show only the top 10 customers by total
spending who live in California.
“SELECT customer_name, SUM(order_total) AS total_spent 
FROM orders 
GROUP BY customer_name 
ORDER BY total_spent DESC;”

Expected Output

SELECT customer_name, SUM(order_total) 
AS total_spent FROM orders JOIN customers 
ON orders.customer_id = customers.customer_id 
WHERE state = 'California' 
GROUP BY customer_name 
ORDER BY total_spent DESC LIMIT 10;

In this example, we provide an existing SQL query that retrieves the total spending for each customer. Using Text-to-SQL, we can modify the SQL statement to add a filter that shows only the top 10 customers who live in California. By joining the orders and customers tables and adding a WHERE clause that filters by state, we can retrieve only the customers who live in California and then limit the results to the top 10 by total spending.

4. Provide your Database Schema and Generate a Query 

Text Prompt (Try it here)

“This is my Schema
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(50),
  address VARCHAR(100),
  city VARCHAR(50),
  state VARCHAR(50),
  zip_code VARCHAR(10)
);
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  order_date DATE,
  customer_id INT,
  order_total DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

CREATE TABLE order_details (
  order_id INT,
  product_name VARCHAR(50),
  quantity INT,
  price DECIMAL(10,2),
  PRIMARY KEY (order_id, product_name),
  FOREIGN KEY (order_id) REFERENCES orders (order_id)
);

Write a SQL statement that retrieves the customer name,
order date, product name, and quantity for all orders
where the order total is greater than $1,000 and
the product name contains the word "chair".

Expected Output

SELECT customers.customer_name, orders.order_date, order_details.product_name, order_details.quantity
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
JOIN order_details ON orders.order_id = order_details.order_id
WHERE orders.order_total > 1000 AND order_details.product_name LIKE '%chair%';

In this example, we provide the database schema and ask Text-to-SQL to write a SQL statement that retrieves the customer name, order date, product name, and quantity for all orders where the order total is greater than $1,000 and the product name contains the word "chair". Using the schema, Text-to-SQL can determine the relationships between the customers, orders, and order_details tables and write a SQL statement that joins these tables and retrieves the required data.

5. Filtering Data by Date Range

Text Prompt (Try it here)

Show me all orders between January 1, 2022 and March 1, 2022.

Expected Output

SELECT * FROM orders WHERE order_date BETWEEN '2022-01-01' AND '2022-03-01'; 

This example demonstrates how Text-to-SQL can be used to generate an SQL statement that filters data by a specific date range. In this case, the SQL statement retrieves all orders placed between January 1, 2022, and March 1, 2022. The orders table contains information about each order, including the order date, customer name, and product information.

6. Not just SELECT - Updating Data in a Table

Text Prompt (Try it here)

Update the price of all products in the electronics category by 10%.

Expected Output

UPDATE products SET price = price * 1.1 WHERE category = 'electronics';

This example shows how Text-to-SQL can be used to generate an SQL statement that updates data in a table. In this case, the SQL statement updates the price of all products in the electronics category by 10%. By specifying the category in the WHERE clause, we can target only the products that need to be updated.

7. Generating a SQL Query to Retrieve Data for a Report

Text Prompt (Try it here)

Generate a SQL query to retrieve data for 
a custom report that shows the total revenue for 
each product category in the past quarter,
along with the number of orders and the average order value.
The data should be sorted by total revenue in descending order.

Expected Output

SELECT categories.category_name, 
SUM(order_details.quantity * order_details.price) AS total_revenue, 
COUNT(DISTINCT orders.order_id) AS num_orders, 
AVG(orders.order_total) AS avg_order_value
FROM categories 
JOIN products ON categories.category_id = products.category_id 
JOIN order_details ON products.product_id = order_details.product_id 
JOIN orders ON order_details.order_id = orders.order_id 
WHERE orders.order_date BETWEEN DATEADD(QUARTER, -1, GETDATE()) AND GETDATE()
GROUP BY categories.category_name 
ORDER BY total_revenue DESC;

In this example, we ask Text-to-SQL to generate a SQL query to retrieve data for a custom report that shows the total revenue for each product category in the past quarter, along with the number of orders and the average order value. The query joins the categories, products, order_details, and orders tables and calculates the total revenue, number of orders, and average order value for each category using aggregate functions. The results are filtered to only include orders from the past quarter and sorted by total revenue in descending order. This query could be used to generate a custom report for a business that wants to track its sales by product category.

8. Use it for Code Review - Understand What a Complex Query Does

Text Prompt (Try it here)

Explain in natural language what this SQL query does:
SELECT department_name, AVG(salary) AS avg_salary, COUNT(*) AS num_employees 
FROM employees 
JOIN departments ON employees.department_id = departments.department_id 
GROUP BY department_name 
HAVING AVG(salary) > 50000 AND COUNT(*) > 10 
ORDER BY avg_salary DESC;

Expected Output

This SQL query retrieves the name of each department along
with the average salary and number of employees in that department.
The query joins the employees and departments tables on 
the department ID, groups the results by department name, 
and filters the results to only include departments with an 
average salary greater than $50,000 and more than 10 employees. 
The results are then sorted by average salary in descending order.

Summary - Try it here

Text-to-SQL is a powerful tool that can be used not only to generate SQL statements from natural language text but also to modify existing SQL queries.