Introduction
GPT Query is an artificial intelligence-based tool designed to make writing SQL queries easier and faster. It allows users to generate complex database queries using natural language. Let's see some examples.
Although it's not perfect (some queries may have an error, but it is rare and only happens in very complicated cases), it is impressive the speed with which latest OpenAI model (gpt3.5) generates queries.
Examples
Consider having the following database (described using natural language):
Customers (customer_id, first_name, last_name, email, phone_number, address)
Categories (category_id, category_name, description)
Products (product_id, product_name, description, price, category_id)
Suppliers (supplier_id, supplier_name, contact_name, contact_email, phone_number, address)
Orders (order_id, customer_id, order_date, order_total, supplier_id)
OrderDetails (order_detail_id, order_id, product_id, quantity, price)
Create table
User prompt:
Create tables
GPT Query result:
CREATE TABLE Customers (
customer_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
phone_number VARCHAR(20),
address VARCHAR(200)
);
CREATE TABLE Categories (
category_id SERIAL PRIMARY KEY,
category_name VARCHAR(50) NOT NULL,
description TEXT
);
CREATE TABLE Products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
description TEXT,
price NUMERIC(10,2) NOT NULL,
category_id INTEGER REFERENCES Categories(category_id) ON DELETE CASCADE
);
CREATE TABLE Suppliers (
supplier_id SERIAL PRIMARY KEY,
supplier_name VARCHAR(100) NOT NULL,
contact_name VARCHAR(100),
contact_email VARCHAR(100),
phone_number VARCHAR(20),
address VARCHAR(200)
);
CREATE TABLE Orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES Customers(customer_id) ON DELETE CASCADE,
order_date DATE NOT NULL,
order_total NUMERIC(10,2) NOT NULL,
supplier_id INTEGER REFERENCES Suppliers(supplier_id) ON DELETE CASCADE
);
CREATE TABLE OrderDetails (
order_detail_id SERIAL PRIMARY KEY,
order_id INTEGER REFERENCES Orders(order_id) ON DELETE CASCADE,
product_id INTEGER REFERENCES Products(product_id) ON DELETE CASCADE,
quantity INTEGER NOT NULL,
price NUMERIC(10,2) NOT NULL
);
Create dummy data
User prompt:
Create dummy data
GPT Query result:
INSERT INTO Customers (customer_id, first_name, last_name, email, phone_number, address)
VALUES
(1, 'John', 'Doe', 'johndoe@gmail.com', '1234567890', '123 Main St'),
(2, 'Jane', 'Doe', 'janedoe@gmail.com', '0987654321', '456 Oak Ave'),
(3, 'Bob', 'Smith', 'bobsmith@gmail.com', '5555555555', '789 Elm St');
INSERT INTO Categories (category_id, category_name, description)
VALUES
(1, 'Electronics', 'Products that use electricity'),
(2, 'Clothing', 'Apparel for men, women, and children'),
(3, 'Home Goods', 'Products for the home');
INSERT INTO Products (product_id, product_name, description, price, category_id)
VALUES
(1, 'Smartphone', 'A mobile device with advanced computing capabilities', 999.99, 1),
(2, 'T-Shirt', 'A comfortable and casual cotton shirt', 19.99, 2),
(3, 'Sofa', 'A comfortable piece of furniture for sitting or lounging', 499.99, 3);
INSERT INTO Suppliers (supplier_id, supplier_name, contact_name, contact_email, phone_number, address)
VALUES
(1, 'Tech Co', 'John Smith', 'john@techco.com', '5555555555', '123 Tech St'),
(2, 'Fashion Inc', 'Jane Smith', 'jane@fashioninc.com', '1234567890', '456 Fashion Ave'),
(3, 'Furniture Co', 'Bob Johnson', 'bob@furnitureco.com', '0987654321', '789 Furniture Blvd');
INSERT INTO Orders (order_id, customer_id, order_date, order_total, supplier_id)
VALUES
(1, 1, '2021-01-01', 999.99, 1),
(2, 2, '2021-01-02', 19.99, 2),
(3, 3, '2021-01-03', 499.99, 3);
INSERT INTO OrderDetails (order_detail_id, order_id, product_id, quantity, price)
VALUES
(1, 1, 1, 1, 999.99),
(2, 2, 2, 1, 19.99),
(3, 3, 3, 1, 499.99);
Data analysis
User prompt:
For each month, which category sells the most?
GPT Query result:
SELECT
EXTRACT(MONTH FROM o.order_date) AS month,
c.category_name,
SUM(od.quantity) AS total_quantity
FROM
Orders o
JOIN OrderDetails od ON o.order_id = od.order_id
JOIN Products p ON od.product_id = p.product_id
JOIN Categories c ON p.category_id = c.category_id
GROUP BY
month, c.category_name
HAVING
SUM(od.quantity) = (
SELECT
MAX(total_quantity)
FROM
(
SELECT
EXTRACT(MONTH FROM o.order_date) AS month,
c.category_name,
SUM(od.quantity) AS total_quantity
FROM
Orders o
JOIN OrderDetails od ON o.order_id = od.order_id
JOIN Products p ON od.product_id = p.product_id
JOIN Categories c ON p.category_id = c.category_id
GROUP BY
month, c.category_name
) t
WHERE
t.month = month
)
ORDER BY
month;
Top comments (0)