15 Ways to Use ChatGPT for SQL (with Code)

Introduction

Have you ever felt stuck when reports require complex SQL queries? Here's the perfect solution: combining classic SQL skills with the power of ai assistants like ChatGPT and Gemini. ai tools are here to bridge that gap and help you write those queries with confidence. Let's explore 15 examples of using ChatGPT for SQL!

ChatGPT for SQL Overview

Let's use a simple e-commerce scenario as an example. Suppose we have the following tables in our database:

1. customers: Contains information about customers.
   • Columns: id (customer ID), name, email, city, phone
2. orders: Contains information about orders placed by customers.
   • Columns: order_id, customer_id (foreign key referencing customers.id), order_amount, order_date

In this scenario, we want to perform several SQL operations to manage and analyze data related to customers and their orders.

Customer table:

Ordering table:

Sample data:

INSERT INTO customers (id, name, email, city, phone)
VALUES 
    (1, 'Alice', '(email protected)', 'New York', '123-456-7890'),
    (2, 'Bob', '(email protected)', 'Los Angeles', '987-654-3210'),
    (3, 'Charlie', NULL, 'Chicago', '555-555-5555');

INSERT INTO orders (order_id, customer_id, order_amount, order_date)
VALUES 
    (101, 1, 100.00, '2024-04-01'),
    (102, 2, 150.00, '2024-04-02'),
    (103, 1, 200.00, '2024-04-03'),
    (104, 3, 80.00, '2024-04-04');

Also Read: Building Complex SQL Queries with the Assistance of Generative ai

15 ways to use ChatGPT for SQL

Throughout the 15 examples, we have queried, filtered, joined, and manipulated data from the two tables above to demonstrate various SQL operations.

Write SQL queries

If you want a query to select all columns in a table named customers.

Immediate:

Production:

SELECT * FROM customers;

Filtering data with the WHERE clause

Select clients from a specific city.

Immediate:

Imagine you have a table called “customers” in your database. This table stores information about your customers, including their city.

Now you want to find all the customers who live in a specific city. Let's say you're interested in clients from New York.

Write an SQL query to select all information about customers from the “customers” table, but only for those who reside in “New York”.

Production:

SELECT * FROM customers WHERE city = 'New York';

Sort data with the ORDER BY clause

Sort customers by name.

Immediate:

Imagine you have a table called “customers” that contains information about customers. Write a SQL query to sort all data in this table by column “name” in ascending order.pen_sparktunesharemore_vert

Production:

SELECT * FROM customers ORDER BY name;

Join tables

Join order and customer tables.

Immediate:

Imagine you have two tables in your database:

orders– This table stores information about orders placed by customers, including columns like order_id, customer_id (referring to the customer who placed the order), order_amountand order_date.

customers– This table stores information about your customers, including columns like customer_id, name, email, cityand phone.

Your goal is to retrieve data from both tables to understand which customers placed which orders. Write an SQL query that joins these two tables based on the customer_id to achieve this.

Production:

SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id;

Add data with GROUP BY

Obtaining total orders per customer.

Immediate:

Imagine that you have a table called orders which stores information about customer orders. Includes columns like order_id, customer_id (referring to the customer who placed the order) and other relevant details.

You are interested in analyzing customer purchasing behavior by finding out how many orders each customer has placed. Write a SQL query that accomplishes this using the GROUP BY clause.

Production:

SELECT customer_id, COUNT(*) as total_orders
FROM orders
GROUP BY customer_id;

Use aggregate functions

Get the average amount of the order.

Immediate:

Imagine you are tasked with analyzing customer spending trends in your eCommerce store. You have a table called orders containing information about customer purchases, including columns such as order_id, customer_id (referring to the client), order_amountand potentially other details.

Its objective is to calculate the average amount spent per order. Develop an SQL query that takes advantage of AVG function to achieve it. The query should:

SELECT AVG(order_amount) as avg_order_amount
FROM orders;

Using subqueries

Select orders with amounts greater than the average order amount:

Immediate:

Write an SQL query to select orders with amounts greater than the average order amount. Use subqueries.

Production:

Use joins with subqueries

Get customers who placed orders with amounts greater than the average order amount.

Immediate:

Write an SQL query that retrieves customers who have placed orders with amounts greater than the average order amount. Use joins with subqueries.

Production:

Filter null values

Select clients without email.

Immediate:

Imagine you have a customer database table called customers. This table stores customer information, including columns such as customer_id, name, email, cityand phone.

You would like to identify customers who have not provided an email address. Write a SQL query to achieve this by filtering the customers table based on email column.

Production:

SELECT * FROM customers WHERE email IS NULL;

Using the LIKE operator for pattern matching

Select customers whose name starts with 'J'.

Immediate:

Imagine you have a customer database table called customers. This table stores customer information, including columns such as customer_id, name, emailand others.

Your task is to find all customers whose names start with the letter “J”. Write an SQL query that uses the LIKE operator with pattern matching to achieve this.

Production:

SELECT * FROM customers WHERE name LIKE 'J%';

Combining conditions with AND and OR

Select New York customers who also made a purchase.

Immediate:

Write an SQL query to select all data for customers located in New York who have placed orders.

Production:

Updating records with UPDATE

Client city update.

Immediate:

Consider that you have a customer database table called customers. This table stores various customer details such as customer_id, name, emailand more.

Your task is to retrieve all customers whose names start with the letter 'J'. To achieve this, you will need to use the LIKE operator in SQL, which allows for pattern matching.

Write a SQL query to select all customers whose names start with 'J'.

Production:

UPDATE customers SET city = 'Los Angeles' WHERE id = 123;

Insert records with INSERT INTO

Insert a new customer record.

Immediate:

Imagine that you are managing a customer database called customers. You must add a new customer record to this database.

Your task is to insert a new customer named John Doe with the email address (email protected) and residing in San Francisco in the customers table.

Write an SQL query using the INSERT INTO statement to perform this task.

Production:

INSERT INTO customers (name, email, city)
VALUES ('John Doe', '(email protected)', 'San Francisco');

Delete records with DELETE

Delete a customer record.

Immediate:

Suppose you are managing a customer database called customers. Sometimes it will be necessary to delete obsolete or incorrect records from this database.

Your task is to delete a specific customer record from the customers table. The client you need to delete has an ID of 123.

Write an SQL query using the DELETE statement to delete this customer record from the database.

Production:

DELETE FROM customers WHERE id = 123;

Create and modify tables with CREATE TABLE and ALTER TABLE

Immediate:

Write SQL code to create and modify tables in SQL using the CREATE TABLE and ALTER TABLE statements.

Production:

Also Read: Code like a pro and write SQL in seconds with Snowflake Arctic

SQL Tutorial for Beginners

Conclusion

You've now seen 15 compelling examples of how ChatGPT, or similar ai tools, can become your secret weapon for conquering complex SQL queries. Whether you're an experienced analyst or just starting your data exploration journey, ai bridges the gap and allows you to write queries with confidence.

Remember, these tools act as smart assistants, not replacements. Its true value lies in its ability to streamline the process, increase its efficiency, and unlock a deeper understanding of your data. So, harness the power of ai, keep honing your SQL skills, and together you will become an unstoppable data analysis force.