Why GPT is the SQL of our century?

And conversely, was SQL the GPT of the seventies?

SQL, which emerged in the 1970s, represented a revolutionary breakthrough in human-computer interaction. Its design aimed to make queries as readable and writable as possible, resembling plain English. For instance, a query to fetch names and salaries of employees in SQL might look like this: SELECT name, salary FROM employee – simple and comprehensible, right? This made databases accessible to a broader audience, not just computer nerds.

Although this intention was commendable, it soon became clear that writing SQL queries still required experts. Moreover, because a universal application interface never emerged, using natural language for modern programming became more of a burden. Programmers today communicate with databases by writing complex SQL command generators, which databases then decode.

Enter GPT. These advanced language models bring a similar revolution in the era of artificial intelligence as SQL did for databases in its time. They enable interaction with databases on an entirely new level, 50 years advanced. Although there is currently no database directly controlled by a language model, we can use GPT to generate SQL. And it works great.

How to do it? Just copy the structure of your database, i.e., a dump with CREATE TABLE commands, into ChatGPT and include the name of the database server you are using. You can then ask questions directly in Czech.

If you want to make using this technique even easier, use the prepared SQL Wizard, where you just need to copy the database structure and you can start asking questions right away without further explanation.

Let's see a practical example. We will use two database tables linked through a foreign key. The users table will contain information about users, and the orders table will store information about orders made by users. Here are the definitions of these tables:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    created_at DATE,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Copy these definitions into ChatGPT and you can immediately ask naturally and in Czech, for example, about all orders from user Jan Novák. Specifically, I would ask: “Write SQL that lists all orders from user Jan Novák.” And GPT might respond like this:

SELECT orders.order_id, orders.created_at, orders.amount
FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.name = 'Jan Novák';

Many programmers can, of course, write such an SQL query faster than logging into ChatGPT 🙂 But let's try a slightly more complex example: “List users who have only one order.”

SELECT users.id, users.name, users.email
FROM users
JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name, users.email
HAVING COUNT(orders.order_id) = 1;

Or “List users who have no orders.”

SELECT users.*
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.order_id IS NULL;

Here the fundamental difference in approach is evident. While with GPT, I specify what data I want to get, in SQL, I have to describe how to get it. And that requires knowledge and experience.

Thanks to GPT, you can focus on what you want to get, instead of how to get it, which is especially valuable for those who are not SQL experts.

Finally, let's demonstrate how to use GPT to create database triggers. For example, you can write: “Add a column order_count to the users table that will track the number of orders. Initialize it to the value corresponding to the current number of orders for each user. And create a trigger that will update the value when an order is created or deleted.”

GPT will generate queries for adding and initializing the order_count column:

ALTER TABLE users ADD COLUMN order_count INT DEFAULT 0;
UPDATE users SET order_count = (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id);

and the required triggers to keep its value up to date:

CREATE TRIGGER update_order_count_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE users
    SET order_count = order_count + 1
    WHERE id = NEW.user_id;
END;

CREATE TRIGGER update_order_count_after_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    UPDATE users
    SET order_count = order_count - 1
    WHERE id = OLD.user_id;
END;

GPT offers a way to work effectively and intuitively with databases, even for those who are not SQL experts. It's a revolutionary tool that truly makes advanced database operations accessible to the general public. However, it is still crucial to carefully check each output to ensure data correctness and security.