DROP TABLE OrderDetails;
DROP TABLE Orders;
DROP TABLE Products;
DROP TABLE Customers;
Part 1: The Setup Script (Copy & Run)
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100)
);
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Category VARCHAR(50),
Price DECIMAL(10, 2)
);
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
CREATE TABLE OrderDetails (
OrderDetailID INT PRIMARY KEY,
OrderID INT,
ProductID INT,
Quantity INT,
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
INSERT INTO Customers (CustomerID, FirstName, LastName, Email) VALUES
(1, 'John', 'Doe', 'john.doe@example.com'),
(2, 'Jane', 'Smith', 'jane.smith@example.com'),
(3, 'Michael', 'Johnson', 'michael.j@example.com'),
(4, 'Emily', 'Davis', 'emily.d@example.com'),
(5, 'Chris', 'Brown', 'chris.b@example.com'),
(6, 'Jessica', 'Garcia', 'jessica.g@example.com'),
(7, 'David', 'Miller', 'david.m@example.com'),
(8, 'Sarah', 'Martinez', 'sarah.m@example.com'),
(9, 'James', 'Rodriguez', 'james.r@example.com'),
(10, 'Linda', 'Wilson', 'linda.w@example.com'),
(11, 'Robert', 'Anderson', 'robert.a@example.com'),
(12, 'Karen', 'Thomas', 'karen.t@example.com'),
(13, 'William', 'Taylor', 'william.t@example.com'),
(14, 'Elizabeth', 'Moore', 'liz.moore@example.com'),
(15, 'Joseph', 'Jackson', 'joe.jack@example.com');
INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES
(101, 'Smartphone X', 'Electronics', 699.99),
(102, 'Laptop Pro', 'Electronics', 1200.00),
(103, 'Wireless Headphones', 'Electronics', 199.50),
(104, '4K Monitor', 'Electronics', 299.99),
(105, 'Mechanical Keyboard', 'Electronics', 120.00),
(106, 'Gaming Mouse', 'Electronics', 60.00),
(107, 'Cotton T-Shirt', 'Clothing', 19.99),
(108, 'Denim Jeans', 'Clothing', 49.99),
(109, 'Running Shoes', 'Footwear', 89.99),
(110, 'Coffee Maker', 'Home', 85.00),
(111, 'Blender', 'Home', 45.00),
(112, 'Smart Watch', 'Electronics', 250.00),
(113, 'Tablet Air', 'Electronics', 450.00),
(114, 'Office Chair', 'Furniture', 150.00),
(115, 'Desk Lamp', 'Home', 30.00);
INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES
(5001, 1, '2023-01-15'),
(5002, 2, '2023-02-10'),
(5003, 1, '2023-03-05'),
(5004, 3, '2023-01-20'),
(5005, 4, '2023-05-12'),
(5006, 5, '2023-06-18'),
(5007, 2, '2023-07-22'),
(5008, 6, '2023-08-30'),
(5009, 7, '2023-09-14'),
(5010, 8, '2023-10-05'),
(5011, 1, '2023-11-01'),
(5012, 9, '2023-12-10'),
(5013, 10, '2023-04-15'),
(5014, 11, '2023-02-28'),
(5015, 3, '2023-03-15'),
(5016, 5, '2023-07-19'),
(5017, 12, '2023-08-21'),
(5018, 13, '2023-09-09'),
(5019, 14, '2022-12-25'),
(5020, 15, '2024-01-05');
INSERT INTO OrderDetails (OrderDetailID, OrderID, ProductID, Quantity) VALUES
(1, 5001, 101, 1),
(2, 5001, 103, 1),
(3, 5002, 107, 2),
(4, 5003, 102, 1),
(5, 5004, 104, 1),
(6, 5005, 110, 1),
(7, 5006, 112, 1),
(8, 5007, 105, 2),
(9, 5008, 113, 1),
(10, 5009, 106, 1),
(11, 5010, 111, 1),
(12, 5011, 102, 2),
(13, 5012, 115, 4),
(14, 5013, 108, 1),
(15, 5014, 101, 1),
(16, 5015, 103, 2),
(17, 5016, 112, 1),
(18, 5017, 107, 3),
(19, 5018, 114, 1),
(20, 5019, 101, 1),
(21, 5020, 102, 1),
(22, 5006, 101, 1),
(23, 5003, 106, 2),
(24, 5011, 112, 1),
(25, 5005, 109, 1);
Step 1: The Setup (The Broken Query)
Tell your students: “Imagine we are working with an E-commerce database in SQL Server. We want to find the top 5 customers who spent the most money on ‘Electronics’ in 2023. Here is the query we wrote, but it is full of bugs.”
The Query with Errors:
SQL
SELECT
c.CustomerID,
c.FirstName + ' ' + c.LastName AS FullName,
COUNT(o.OrderID) AS TotalOrders,
SUM(od.Quantity * p.Price) AS TotalSpent,
YEAR(o.OrderDate) AS OrderYear
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN OrderDetails od ON o.OrderID = od.OrderID
-- Logic Trap: Filtering on the right table turns this LEFT JOIN into an INNER JOIN implicitly
LEFT JOIN Products p ON od.ProductID = p.ProductID
WHERE
p.Category = "Electronics"
AND o.OrderDate = '2023-02-30'
GROUP BY
c.CustomerID
HAVING
TotalSpent > 500
ORDER BY
TotalSpent DESC
LIMIT 5;
Step 2: The Prompt
Instruct your students to copy the query above and paste it into Gemini with the following instruction:
“I am using SQL Server. This query has several syntax and logical errors. Please identify the errors one by one, explain why they are wrong in T-SQL, and provide the corrected code.”
Here are a few prompt variations you can use with AI tools (like ChatGPT, Claude, or Gemini) to fix your SQL queries.
Option 1: The “Fix & Explain” Prompt (Best for Learning)
Use this if you want to understand exactly what went wrong, just like in your transcript.
Prompt:
“I have written the following SQL query for [Insert Database Name, e.g., Microsoft SQL Server], but it is returning errors.
Please analyze the code and:
- Identify the specific syntax or logical errors.
- Explain why each error is occurring.
- Provide the fully corrected SQL query.
Here is the code:
[PASTE YOUR CODE HERE]”
Option 2: The “Quick Fix” Prompt
Use this if you just want the working code immediately.
Prompt:
“The following SQL code for [Insert Database Name] is failing. Please rewrite it with correct syntax and formatting.
Code:
[PASTE YOUR CODE HERE]”
Option 3: The “Optimize & Format” Prompt
Use this if your code works but looks messy or runs slowly.
Prompt:
“I have this SQL query for [Insert Database Name]. It works, but I want to improve it. Please rewrite it to follow standard best practices, improve indentation for readability, and optimize performance if possible.
Code:
[PASTE YOUR CODE HERE]”
Pro Tip: Why specify the Database Name?
Always mention if you are using MySQL, PostgreSQL, Oracle, or MS SQL Server.
- Example: In MySQL, you use backticks (`) for column names, but in SQL Server, you use brackets (
[]). The AI needs to know which rules to apply!
Step 3: The Corrections (Gemini’s Analysis)
Here is how to break down the corrections for the class, exactly as Gemini would identify them.
Correction 1: LIMIT vs. TOP
- The Error:
LIMIT 5 at the end of the query. - The Explanation: The
LIMIT keyword is used in MySQL and PostgreSQL. SQL Server (T-SQL) does not support LIMIT. - The Fix: In SQL Server, you must use
TOP immediately after the SELECT.- Change:
SELECT TOP 5 c.CustomerID...
Correction 2: Double Quotes for Strings
- The Error:
p.Category = "Electronics" - The Explanation: In standard T-SQL, double quotes
" are often reserved for identifiers (like table names with spaces). String literals (text) must always be enclosed in single quotes. - The Fix: Change
"Electronics" to 'Electronics'.
Correction 3: The GROUP BY Violation
- The Error:
GROUP BY c.CustomerID - The Explanation: In SQL, if you use an aggregate function (like
SUM or COUNT), every column in your SELECT list that is not inside an aggregate function must be included in the GROUP BY clause. - The Fix: We must add
FullName (or the components creating it) and OrderYear to the group by list.- Change:
GROUP BY c.CustomerID, c.FirstName, c.LastName, YEAR(o.OrderDate)
Correction 4: Using Alias in HAVING
- The Error:
HAVING TotalSpent > 500 - The Explanation: The logical order of operations in SQL processes the
HAVING clause before the SELECT aliases are created. Therefore, SQL Server doesn’t know what TotalSpent is yet. - The Fix: You must repeat the calculation inside the
HAVING clause.- Change:
HAVING SUM(od.Quantity * p.Price) > 500
Correction 5: Date Validity
- The Error:
'2023-02-30' - The Explanation: February never has 30 days. This will cause a runtime conversion error when SQL Server tries to convert that string into a Date type.
- The Fix: Change it to a valid date or a date range (e.g.,
BETWEEN '2023-01-01' AND '2023-12-31').
Step 4: The Final Corrected Query
This is the working code your students should arrive at:
SQL
SELECT TOP 5
c.CustomerID,
c.FirstName + ' ' + c.LastName AS FullName,
COUNT(o.OrderID) AS TotalOrders,
SUM(od.Quantity * p.Price) AS TotalSpent,
YEAR(o.OrderDate) AS OrderYear
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN OrderDetails od ON o.OrderID = od.OrderID
LEFT JOIN Products p ON od.ProductID = p.ProductID
WHERE
p.Category = 'Electronics'
AND o.OrderDate BETWEEN '2023-01-01' AND '2023-12-31' -- Corrected Date Logic
GROUP BY
c.CustomerID,
c.FirstName,
c.LastName,
YEAR(o.OrderDate)
HAVING
SUM(od.Quantity * p.Price) > 500
ORDER BY
TotalSpent DESC;
