Introduction

Ever feel stuck when reports demand 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 confidently write those queries. Let’s explore 15 examples of using ChatGPT for SQL!

Overview of ChatGPT for SQL

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 made by customers.
   • Columns: order_id, customer_id (foreign key referencing customers.id), order_amount, order_date

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

Customers Table:

Orders 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: Crafting Complex SQL Queries with Generative AI Assistance

15 Ways to Use ChatGPT for SQL

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

Writing SQL Queries

If you want a query to select all columns from a table called customers.

Prompt:

Output:

SELECT * FROM customers;

Filtering Data with WHERE Clause

Selecting customers from a specific city.

Prompt:

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

Now, you want to find all customers who live in a specific city. Let’s say you’re interested in customers 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

Output:

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

Sorting Data with ORDER BY Clause

Sorting customers by their names.

Prompt:

Imagine you have a table named “customers” containing information about customers. Write a SQL query to sort all the data from this table by the “name” column in ascending order.pen_sparktunesharemore_vert

Output:

SELECT * FROM customers ORDER BY name;

Joining Tables

Joining orders and customers tables.

Prompt:

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 (referencing the customer who placed the order), order_amount, and order_date.

customers: This table stores information about your customers, including columns like customer_id, name, email, city, and 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 together based on the customer_id to achieve this.

Output:

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

Aggregating Data with GROUP BY

Getting total orders per customer.

Prompt:

Imagine you have a table named orders that stores information about customer orders. It includes columns like order_id, customer_id (referencing the customer who placed the order), and other relevant details.

You’re interested in analyzing customer purchase behavior by finding out how many orders each customer has placed. Write an SQL query that achieves this using the GROUP BY clause.

Output:

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

Using Aggregate Functions

Getting the average order amount.

Prompt:

Imagine you’re tasked with analyzing customer spending trends in your e-commerce store. You have a table named orders that contains information about customer purchases, including columns like order_id, customer_id (referencing the customer), order_amount, and potentially other details.

Your objective is to calculate the average amount spent per order. Craft an SQL query that leverages the AVG function to achieve this. The query should:

SELECT AVG(order_amount) as avg_order_amount
FROM orders;

Using Subqueries

Selecting orders with amounts greater than the average order amount:

Prompt:

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

Output:

Using Joins with Subqueries

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

Prompt:

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

Output:

Filtering Null Values

Selecting customers with no email.

Prompt:

Imagine you have a customer database table named customers. This table stores customer information, including columns like customer_id, name, email, city, and phone.

You’d like to identify customers who haven’t provided an email address. Write an SQL query to achieve this by filtering the customers table based on the email column.

Output:

SELECT * FROM customers WHERE email IS NULL;

Using LIKE Operator for Pattern Matching

Selecting customers whose name starts with ‘J’.

Prompt:

Imagine you have a customer database table named customers. This table stores customer information, including columns like customer_id, name, email, and others.

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

Output:

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

Combining Conditions with AND & OR

Selecting customers from New York who also made a purchase.

Prompt:

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

Output:

Updating Records with UPDATE

Updating customer’s city.

Prompt:

Consider you have a customer database table named customers. This table stores various customer details such as customer_id, name, email, and more.

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

Write an SQL query to select all customers whose names begin with ‘J’.

Output:

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

Inserting Records with INSERT INTO

Inserting a new customer record.

Prompt:

Imagine you’re managing a customer database named customers. You need to 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 into the customers table.

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

Output:

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

Deleting Records with DELETE

Deleting a customer record.

Prompt:

Suppose you’re managing a customer database called customers. Occasionally, you need to remove outdated or incorrect records from this database.

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

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

Output:

DELETE FROM customers WHERE id = 123;

Creating and Modifying Tables with CREATE TABLE and ALTER TABLE

Prompt:

Write the SQL code for creating and modifying tables in SQL using the CREATE TABLE and ALTER TABLE statements.

Output:

Also Read: Code Like a Pro and Write SQL in Seconds with Snowflake Arctic

SQL Tutorial for Beginners 

Conclusion

Now you’ve seen 15 compelling examples of how ChatGPT, or similar AI tools, can become your secret weapon for conquering complex SQL queries. Whether you’re a seasoned analyst or just starting your data exploration journey, AI bridges the gap and empowers you to write queries confidently.

Remember, these tools act as your intelligent assistants, not replacements. Their true value lies in their ability to streamline the process, boost your efficiency, and unlock a deeper understanding of your data. So, embrace the power of AI, keep honing your SQL skills, and together, you’ll become an unstoppable data analysis force!

Frequently Asked Questions