DEV Community

Oleksandr Demian
Oleksandr Demian

Posted on

GPT Query: the AI tool that can write SQL queries for you

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

Image description

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
);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Top comments (0)