SQL Server Tutorial in Telugu: Fix Query Errors with Google Gemini | SQL Debugging Guide

Always handy to give students a script to delete the tables if they mess up and need to restart:

SQL

DROP TABLE OrderDetails;
DROP TABLE Orders;
DROP TABLE Products;
DROP TABLE Customers;

Part 1: The Setup Script (Copy & Run)
SQL
-- 1. Create Tables
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)
);

-- 2. Insert Data: Customers (15 Records)
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');

-- 3. Insert Data: Products (15 Records - Mix of Categories)
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);

-- 4. Insert Data: Orders (20 Records - Dates mostly in 2023)
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'), -- John Doe big spender
(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'), -- Old order (should be filtered out)
(5020, 15, '2024-01-05'); -- Future order (should be filtered out)

-- 5. Insert Data: OrderDetails (Linking Table - 25 Records)
INSERT INTO OrderDetails (OrderDetailID, OrderID, ProductID, Quantity) VALUES
(1, 5001, 101, 1), -- John Doe buys Smartphone
(2, 5001, 103, 1), -- John Doe buys Headphones
(3, 5002, 107, 2),
(4, 5003, 102, 1), -- John Doe buys Laptop (High Value)
(5, 5004, 104, 1),
(6, 5005, 110, 1),
(7, 5006, 112, 1), -- Chris buys Smart Watch
(8, 5007, 105, 2),
(9, 5008, 113, 1),
(10, 5009, 106, 1),
(11, 5010, 111, 1),
(12, 5011, 102, 2), -- John Doe buys 2 Laptops (Very High Value)
(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;