How do you find duplicate records in a SQL table?

Use GROUP BY on the column(s) and filter with HAVING COUNT(*) > 1, for example SELECT Country, COUNT(*) FROM Customers GROUP BY Country HAVING COUNT(*) > 1.

What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes rows one by one and supports WHERE and rollback. TRUNCATE quickly removes all rows without a WHERE clause. DROP deletes the entire table and its structure permanently.

How do you find customers who never placed an order?

Use a LEFT JOIN from the customers table to the orders table and keep rows where the order ID IS NULL, or use a NOT EXISTS subquery.

What is a SELF JOIN and when is it used?

A SELF JOIN joins a table to itself using two aliases, for example employee and manager in the same Employees table, to resolve hierarchical relationships.

What is SQL Injection and how do you prevent it?

SQL Injection is when malicious input containing SQL fragments changes how a query runs. Prevent it by never concatenating user input into queries and using parameterized queries (prepared statements) instead.

Learn SQL Free – Live Sandbox & Interview Q&A

Q: How do you find the 2nd highest salary in an Employee table?

Select the maximum salary that is lower than the overall maximum: SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees). In MySQL or PostgreSQL you can also use ORDER BY Salary DESC LIMIT 1 OFFSET 1.

Q: What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters grouped results after GROUP BY, so it can use aggregates such as COUNT() or SUM().

Q: What is the difference between PRIMARY KEY and UNIQUE KEY?

A table can have only one primary key and it can never be NULL, while it can have multiple unique keys that may allow NULL values. The primary key defines row identity; a unique key enforces uniqueness on other columns such as email.

Q: What is the difference between ROW_NUMBER(), RANK() and DENSE_RANK()?

With tied values, ROW_NUMBER() gives unique consecutive numbers (1,2,3,4), RANK() gives ties the same rank and then skips numbers (1,2,2,4), and DENSE_RANK() gives ties the same rank with no gaps (1,2,2,3).

Q: What is a database index and what are its pros and cons?

An index lets the database find rows without scanning the whole table, which greatly speeds up SELECT and WHERE lookups. The trade-off is slower INSERT, UPDATE and DELETE operations and extra disk space.

What is SQL? SQL (Structured Query Language) is the standard language for storing, retrieving, updating and deleting data in relational databases such as MySQL, PostgreSQL, SQL Server and Oracle. This free guide gives you runnable examples, a live in-browser sandbox, the top 10 interview questions with answers, and a 50-question certificate exam. Last updated: 29 September 2026

🧭 Brand New to SQL? Just understand these 4 core concepts!

SQL is not difficult programming. Imagine having an Excel Sheet open and typing instructions telling it exactly what to do:

1. Database A digital cabinet or folder holding multiple related sheets.

2. Table An Excel sheet containing Columns (headings) and Rows (individual records).

3. Row vs Column Column = Header (e.g. Name, City). Row = A complete data record for one person.

4. Golden Formula SELECT (what to view) + FROM (which sheet) + WHERE (what filter to apply).

⚡ Live Interactive SQL Sandbox (Browser Engine)

AlaSQL Engine Active

Test and run live SQL queries right in your browser! Click any of the quick buttons below or type your SQL code and press Run Query (Ctrl + Enter):

Quick Queries:

Results will appear here...

🗄️ Active Sandbox Data Tables (Sample Dataset)

Sheet 1: `Customers` 4 Rows

CustomerID	CustomerName	City	Country
1	Amit Sharma	Delhi	India
2	Pooja Verma	Mumbai	India
3	John Miller	Berlin	Germany
4	Sara Smith	London	UK

Sheet 2: `Employees` 4 Rows

EmpID	Name	dept_id	Salary
101	Rahul Roy	10	65000
102	Sneha Rao	20	48000
103	Vikas Jain	10	72000
104	Karan Mehta	NULL	42000

Sheet 3: `Products` 4 Rows

ProductID	ProductName	Price	SupplierID
1	Mechanical Keyboard	45	1
2	Gaming Mouse	25	1
3	HD Monitor	180	2
4	USB Hub	15	3

1. SQL Basics & Queries (Essential Everyday Commands)

SQL SELECT & FROM

Fetch and view specific columns from a sheet.

💡 In Simple Terms: "Opening an Excel sheet and viewing selected columns." Use * (star) to retrieve all columns.

Syntax:SELECT column1, column2 FROM table_name;

Example:SELECT CustomerName, City FROM Customers;

Output:+---------------+--------+
| CustomerName  | City   |
+---------------+--------+
| Amit Sharma   | Delhi  |
| Pooja Verma   | Mumbai |
| John Miller   | Berlin |
| Sara Smith    | London |
+---------------+--------+ (4 records fetched)

SQL SELECT DISTINCT

Retrieve only unique values and remove duplicate entries.

💡 In Simple Terms: "Excel's Remove Duplicates button." If 10 rows have India, India will only appear once.

Syntax:SELECT DISTINCT column_name FROM table_name;

Example:SELECT DISTINCT Country FROM Customers;

Output:+---------+
| Country |
+---------+
| India   |
| Germany |
| UK      |
+---------+ (India was listed twice, but appears only once)

SQL WHERE

Filter rows based on specified conditions.

💡 In Simple Terms: "Clicking the Excel Filter icon and showing only rows where Country is 'India'."

Syntax:SELECT * FROM table WHERE condition;

Example:SELECT CustomerName, City FROM Customers WHERE Country = 'India';

Output:+---------------+--------+
| CustomerName  | City   |
+---------------+--------+
| Amit Sharma   | Delhi  |
| Pooja Verma   | Mumbai |
+---------------+--------+

SQL AND, OR, NOT

Combine multiple conditions simultaneously.

💡 In Simple Terms: AND = both conditions must be true. OR = either condition can be true. NOT = exclude these records.

Syntax:SELECT * FROM table WHERE cond1 AND cond2;

Example:SELECT * FROM Customers WHERE Country='India' AND City='Delhi';

Output:+----+-------------+-------+---------+
| ID | CustomerName| City  | Country |
+----+-------------+-------+---------+
| 1  | Amit Sharma | Delhi | India   |
+----+-------------+-------+---------+ (Only Amit matched both conditions)

SQL ORDER BY

Sort data in ascending or descending order.

💡 In Simple Terms: "Excel's Sort A-to-Z (ASC) or Z-to-A (DESC) button."

Syntax:SELECT * FROM table ORDER BY column ASC|DESC;

Example:SELECT CustomerName, City FROM Customers ORDER BY CustomerName DESC;

Output:+---------------+--------+
| CustomerName  | City   |
+---------------+--------+
| Sara Smith    | London |
| Pooja Verma   | Mumbai |
| John Miller   | Berlin |
| Amit Sharma   | Delhi  |
+---------------+--------+ (Alphabetical Z-to-A order)

SQL INSERT INTO

Insert a brand-new row of data into a sheet.

💡 In Simple Terms: "Writing and adding a new entry line in a register."

Syntax:INSERT INTO table (col1, col2) VALUES (val1, val2);

Example:INSERT INTO Customers (CustomerName, City, Country) VALUES ('Rohan Das', 'Pune', 'India');

Output:Query OK, 1 row affected (0.01 sec)
[New row inserted: CustomerID 5, Rohan Das, Pune]

SQL UPDATE

Modify and update existing data in a table.

💡 In Simple Terms: "Erasing and updating an existing entry." (Always use WHERE, otherwise every row will be updated!)

Syntax:UPDATE table SET column = new_val WHERE condition;

Example:UPDATE Customers SET City = 'Noida' WHERE CustomerID = 1;

Output:Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1
[Amit Sharma's city changed from Delhi to Noida]

SQL DELETE

Permanently remove specific rows from a table.

💡 In Simple Terms: "Right-clicking an Excel row and clicking Delete Row."

Syntax:DELETE FROM table WHERE condition;

Example:DELETE FROM Customers WHERE CustomerName = 'Amit Sharma';

Output:Query OK, 1 row affected (0.01 sec)
[Amit Sharma's record was permanently deleted from the table]

SQL LIMIT / TOP

Fetch only a specified number of initial records.

💡 In Simple Terms: "Displaying only the Top 2 or Top 5 records from a long list."

Syntax (MySQL):SELECT * FROM table LIMIT count;

Example:SELECT CustomerName, City FROM Customers LIMIT 2;

Output:+---------------+--------+
| CustomerName  | City   |
+---------------+--------+
| Amit Sharma   | Delhi  |
| Pooja Verma   | Mumbai |
+---------------+--------+ (Only the first 2 records fetched)

SQL NULL Values

Check for blank or missing values.

💡 In Simple Terms: NULL does not mean 0 or blank text; it represents 'Missing Data'. In SQL, we write IS NULL instead of = NULL.

Syntax:SELECT * FROM table WHERE column IS NULL;

Example:SELECT Name, dept_id FROM Employees WHERE dept_id IS NULL;

Output:+-------------+---------+
| Name        | dept_id |
+-------------+---------+
| Karan Mehta | NULL    |
+-------------+---------+ (Karan has no department assigned)

2. SQL Aggregate Functions (Automated Calculations & Metrics)

SQL MIN() & MAX()

Find the smallest and largest numbers in a column.

💡 In Simple Terms: "Finding the cheapest item and the most expensive item in the catalog."

Syntax:SELECT MIN(col), MAX(col) FROM table;

Example:SELECT MIN(Price) AS Cheapest, MAX(Price) AS MostExpensive FROM Products;

Output:+-------+--------+
| Cheapest | MostExpensive |
+-------+--------+
| 15    | 180    |
+-------+--------+ (USB Hub = 15, HD Monitor = 180)

SQL COUNT(), SUM(), AVG()

Count total records, compute the sum, and calculate the average.

💡 In Simple Terms: COUNT = number of items, SUM = total monetary amount, AVG = average amount per item.

Syntax:SELECT COUNT(*), SUM(col), AVG(col) FROM table;

Example:SELECT COUNT(ProductID) AS TotalItems, SUM(Price) AS TotalSum, AVG(Price) AS AveragePrice FROM Products;

Output:+----------+-----+-------+
| TotalItems | TotalSum | AveragePrice |
+----------+-----+-------+
| 4        | 265 | 66.25 |
+----------+-----+-------+ (Total 4 items, Total sum 265, Average 66.25)

3. Advanced Querying & Joins (Smart Search & Combining Sheets)

SQL LIKE & Wildcards (%)

Search records by matching prefix, suffix, or substrings.

💡 In Simple Terms: "Searching 'Del*' in contacts to match Delhi, Dehradun, etc."

Syntax:SELECT * FROM table WHERE col LIKE 'text%';

Example:SELECT CustomerName, City FROM Customers WHERE City LIKE 'Del%';

Output:+---------------+-------+
| CustomerName  | City  |
+---------------+-------+
| Amit Sharma   | Delhi |
+---------------+-------+

SQL BETWEEN & IN

Filter within a numeric/date range or a discrete list of values.

💡 In Simple Terms: "Show me all items priced between $20 and $50."

Syntax:SELECT * FROM table WHERE col BETWEEN 20 AND 50;

Example:SELECT ProductName, Price FROM Products WHERE Price BETWEEN 20 AND 50;

Output:+---------------------+-------+
| ProductName         | Price |
+---------------------+-------+
| Mechanical Keyboard | 45    |
| Gaming Mouse        | 25    |
+---------------------+-------+

SQL Aliases (AS)

Temporarily rename columns or tables for cleaner presentation.

💡 In Simple Terms: The database column is cust_first_nm, but on screen we display it neatly as Client_Name.

Syntax:SELECT col AS Nickname FROM table;

Example:SELECT CustomerName AS Client_Name FROM Customers LIMIT 2;

Output:+---------------+
| Client_Name   |
+---------------+
| Amit Sharma   |
| Pooja Verma   |
+---------------+ (Header displayed cleanly)

SQL INNER JOIN

Combine two different sheets using a common ID column.

💡 In Simple Terms: Sheet 1 contains Employee names, Sheet 2 contains Departments. Matching them on dept_id merges them into one combined view!

Syntax:SELECT t1.col, t2.col FROM t1 INNER JOIN t2 ON t1.id = t2.id;

Example:SELECT e.Name, d.DeptName FROM Employees e INNER JOIN Departments d ON e.dept_id = d.DeptID;

Output:+-----------+-------------+
| Name      | DeptName    |
+-----------+-------------+
| Rahul Roy | Engineering |
| Sneha Rao | Marketing   |
| Vikas Jain| Engineering |
+-----------+-------------+ (Karan Mehta was excluded because his dept_id was NULL)

SQL GROUP BY & HAVING

Group data into clusters and calculate summary metrics.

💡 In Simple Terms: "Excel Pivot Table!" Group by Country and display only countries with 2 or more customers.

Syntax:SELECT col, COUNT(*) FROM t GROUP BY col HAVING COUNT(*) >= 2;

Example:SELECT Country, COUNT(CustomerID) AS Total FROM Customers GROUP BY Country HAVING COUNT(CustomerID) >= 2;

Output:+---------+-------+
| Country | Total |
+---------+-------+
| India   | 2     |
+---------+-------+ (UK and Germany are filtered out)

SQL CASE Statement

Apply conditional logic (IF-THEN-ELSE) directly in queries.

💡 In Simple Terms: "If salary is 60,000 or higher, label as 'Senior', otherwise 'Associate'."

Syntax:CASE WHEN cond THEN 'A' ELSE 'B' END;

Example:SELECT Name, CASE WHEN Salary >= 60000 THEN 'Senior' ELSE 'Associate' END AS Level FROM Employees;

Output:+-------------+-----------+
| Name        | Level     |
+-------------+-----------+
| Rahul Roy   | Senior    |
| Sneha Rao   | Associate |
| Vikas Jain  | Senior    |
| Karan Mehta | Associate |
+-------------+-----------+

SQL COALESCE / IFNULL

Display a default number or text whenever a value is NULL.

💡 In Simple Terms: If someone's department number is blank, display '0' or 'Not Assigned' instead of blank/error.

Syntax:SELECT COALESCE(col, default_val) FROM table;

Example:SELECT Name, COALESCE(dept_id, 0) AS SafeDept FROM Employees;

Output:+-------------+----------+
| Name        | SafeDept |
+-------------+----------+
| Rahul Roy   | 10       |
| Sneha Rao   | 20       |
| Karan Mehta | 0        |  <-- NULL replaced with 0!
+-------------+----------+

SQL UNION

Combine two separate query outputs into one unified list.

💡 In Simple Terms: Stacking a customer list from the Delhi store under a list from the Mumbai store.

Syntax:SELECT col FROM t1 UNION SELECT col FROM t2;

Example:SELECT City FROM Customers UNION SELECT 'Jaipur' AS City;

Output:+--------+
| City   |
+--------+
| Delhi  |
| Mumbai |
| Berlin |
| London |
| Jaipur |
+--------+ (All cities merged into a single unique list)

4. Database & Table Management (Creating & Modifying Structures)

SQL CREATE TABLE

Create a brand-new empty table (sheet).

💡 In Simple Terms: "Opening a new Excel sheet and defining headers like ID, Name, Salary in row 1."

Syntax:CREATE TABLE table_name (col1 type, col2 type);

Example:CREATE TABLE Users (UserID INT, FullName VARCHAR(100));

Output:Query OK, 0 rows affected (0.02 sec)
[Table 'Users' created; data can now be inserted]

SQL ALTER TABLE

Add, modify, or drop columns in an existing table.

💡 In Simple Terms: "Adding a new 'Email' column to a sheet that is already in active use."

Syntax:ALTER TABLE table_name ADD col_name datatype;

Example:ALTER TABLE Customers ADD Email VARCHAR(255);

Output:Query OK, 0 rows affected (0.02 sec)
['Email' column successfully added to Customers table]

SQL Primary Key

A unique identification constraint for each record.

💡 In Simple Terms: "A Student Roll Number or National ID" — cannot be duplicated and can never be left empty (NULL).

Syntax:ID INT NOT NULL PRIMARY KEY

Example:CREATE TABLE Students (RollNo INT PRIMARY KEY, Name VARCHAR(50));

Output:Query OK, 0 rows affected (0.03 sec)
[Primary Key active: database will reject any duplicate RollNo]

SQL AUTO_INCREMENT

Automatically generates sequential IDs (1, 2, 3...) with each new entry.

💡 In Simple Terms: Like a token machine — every new customer automatically gets the next sequential number.

Syntax:ID INT AUTO_INCREMENT PRIMARY KEY

Example:INSERT INTO Users (FullName) VALUES ('David Miller');

Output:Query OK, 1 row affected (0.01 sec)
[System generated: UserID = 101 allocated automatically]

5. Stored Procedures & Security (Shortcuts & Data Protection)

SQL Stored Procedure

Save a complex query under a reusable shortcut name.

💡 In Simple Terms: "An Excel Macro!" Don't type a 20-line query repeatedly—save it once and execute with a single call.

Syntax:CREATE PROCEDURE ProcName AS query;
EXEC ProcName;

Example:CREATE PROCEDURE AllCustomers AS SELECT CustomerName, Country FROM Customers;
EXEC AllCustomers;

Output:+---------------+---------+
| CustomerName  | Country |
+---------------+---------+
| Amit Sharma   | India   |
| Pooja Verma   | India   |
+---------------+---------+ (Procedure instantly executed)

SQL Comments (--)

Add explanatory notes to code for documentation.

💡 In Simple Terms: Placing a sticky note. The database ignores comments during query execution.

Syntax:-- Single Line Note
/* Multi Line Note */

Example:-- Fetch only active employees
SELECT Name FROM Employees WHERE dept_id IS NOT NULL;

Output:+------------+
| Name       |
+------------+
| Rahul Roy  |
| Sneha Rao  |
+------------+ (Comment text was ignored and query executed)

Q1: How do you find the 2nd Highest Salary in an Employee table?

Most Frequent #1

💼 Why interviewers ask this: To evaluate whether you understand subqueries or window functions, or if you only rely on basic LIMIT offsets.

Solution A (Using Subquery - Standard ANSI SQL): First find the maximum salary, then select the highest salary that is less than that maximum:

SELECT MAX(Salary) AS SecondHighestSalary 
FROM Employees 
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

Solution B (Using LIMIT & OFFSET - MySQL/PostgreSQL):

SELECT DISTINCT Salary 
FROM Employees 
ORDER BY Salary DESC 
LIMIT 1 OFFSET 1;

Result on Employees Table:+---------------------+
| SecondHighestSalary |
+---------------------+
| 65000               | (Top is Vikas: 72000, 2nd is Rahul: 65000)
+---------------------+

Q2: How do you find duplicate records in a table?

Data Cleaning

💼 Why interviewers ask this: Data cleaning and deduplication are daily responsibilities for any data professional.

By applying GROUP BY on the column(s) and filtering with HAVING COUNT(*) > 1, duplicate entries are isolated:

SELECT Country, COUNT(*) AS DuplicateCount
FROM Customers
GROUP BY Country
HAVING COUNT(*) > 1;

Output:+---------+----------------+
| Country | DuplicateCount |
+---------+----------------+
| India   | 2              |
+---------+----------------+

Q3: What is the exact difference between DELETE, TRUNCATE, and DROP?

Core Concept

💼 Why interviewers ask this: To verify whether you know which operation is transaction-safe (can be rolled back) and which drops schema entirely.

Feature	DELETE (DML)	TRUNCATE (DDL)	DROP (DDL)
Action / Function	Removes rows individually	Flushes all rows instantaneously	Deletes entire table and its schema definition
WHERE Clause	Yes (filter specific rows)	No (clears entire table)	No
Rollback	Yes (logged per row, rollback-safe)	No / Engine-dependent	No (permanent deletion)
Speed	Slower (generates per-row logs)	Very Fast (deallocates storage pages)	Fastest

Q4: Find customers who NEVER placed any order (Unmatched Records).

Joins & Subqueries

💼 Why interviewers ask this: Tests your mastery of LEFT JOIN NULL-checks and NOT EXISTS subquery patterns.

-- Approach 1: LEFT JOIN with NULL check
SELECT u.name, u.city 
FROM Users u
LEFT JOIN Orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL;

-- Approach 2: Using NOT EXISTS
SELECT u.name, u.city 
FROM Users u
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.user_id = u.user_id);

Output:+-------------+--------+
| name        | city   |
+-------------+--------+
| Varun Joshi | Jaipur |
+-------------+--------+ (Varun has never placed an order)

Q5: What is a SELF JOIN and when do we use it? (Manager-Employee Hierarchy)

Advanced Join

💼 Why interviewers ask this: When relational data resides within the same table (e.g., an employee's manager is also an employee in the same table).

Joining a table to itself using two distinct aliases (e.g., e for employee, m for manager) is called a SELF JOIN:

SELECT 
    e.Name AS EmployeeName, 
    m.Name AS ManagerName
FROM Employees e
LEFT JOIN Employees m ON e.ManagerID = m.EmpID;

Concept Result:+--------------+-------------+
| EmployeeName | ManagerName |
+--------------+-------------+
| Sneha Rao    | Rahul Roy   |
| Vikas Jain   | Rahul Roy   |
| Rahul Roy    | NULL (CEO)  |
+--------------+-------------+

Q6: What is the difference between WHERE and HAVING clause?

Fundamental Filter

💼 Why interviewers ask this: Over 90% of beginners confuse row-level filtering with post-aggregation filtering.

• WHERE: Filters individual rows before grouping occurs. It cannot evaluate aggregate functions like SUM() or AVG().
• HAVING: Filters grouped summaries after GROUP BY has aggregated the records (e.g., HAVING COUNT(*) > 2).

SELECT City, COUNT(*) AS Total
FROM Customers
WHERE Country = 'India'         -- First filter for India (WHERE)
GROUP BY City
HAVING COUNT(*) >= 1;          -- Filter after grouping is computed (HAVING)

Q7: What is the difference between PRIMARY KEY and UNIQUE KEY?

Schema Design

1. Count: A table can have only 1 Primary Key, but can contain multiple Unique Keys.
2. NULL Value: Primary key can never be NULL. Unique keys allow NULL values (typically one or multiple, depending on the database engine).
3. Usage: Primary Key defines row identity (e.g., Roll No, UUID). Unique key ensures uniqueness for auxiliary data (e.g., Email, Phone Number).

Q8: What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

Window Functions

💼 Why interviewers ask this: A top technical interview question when evaluating candidates on analytical SQL ranking behavior during ties!

Suppose two candidates tie with equal marks (e.g., 90, 90):

ROW_NUMBER(): Assigns strictly consecutive unique numbers: 1, 2, 3, 4 (ignores ties).
RANK(): Assigns identical ranks to ties, then skips subsequent rank numbers: 1, 2, 2, 4 (rank 3 skipped!).
DENSE_RANK(): Assigns identical ranks to ties, with no gaps in ranking sequence: 1, 2, 2, 3.

SELECT Name, Salary,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

Q9: What is SQL Injection and how do you protect against it?

Security & Best Practices

💼 Why interviewers ask this: Security is priority #1 in production database and backend engineering.

SQL Injection: When malicious input containing raw SQL fragments (e.g., ' OR '1'='1) alters query execution to bypass authentication or extract sensitive data.
Defense: Never concatenate raw user input into SQL queries. Always use Parameterized Queries (Prepared Statements) where inputs are handled strictly as parameters, not executable code.

Secure Pattern:-- Safe: Parameterized Placeholder
SELECT * FROM Users WHERE Username = ? AND Password = ?;

Q10: What is a Database INDEX, and what are its pros and cons?

Performance Tuning

💼 Why interviewers ask this: When tables grow to 10+ million rows, query indexing makes or breaks production performance.

Index: Similar to the index section at the end of a textbook. Without an index, the database must read every row (Full Table Scan), whereas an index allows lookups in sub-milliseconds.
• Pros: SELECT and WHERE search speeds increase by 100x+.
• Cons: Write operations (INSERT/UPDATE/DELETE) require index updates, adding write overhead and consuming extra disk storage.

CREATE INDEX idx_city ON Customers(City);

📁 The 3 Interconnected Database Tables in this Project (Database Schema):

1. `Users` (Customers) 5 Rows

user_id	name	city
1	Aman Gupta	Delhi
2	Riya Sen	Mumbai
3	Kabir Das	Delhi
4	Neha Sharma	Bangalore
5	Varun Joshi	Jaipur

2. `Items` (Products) 4 Rows

item_id	title	price (₹)
101	Wireless Earbuds	1200
102	Fast Charger	500
103	Smart Watch	2500
104	Laptop Stand	800

3. `Orders` (Sales Records) 5 Rows

order_id	user_id	item_id	status
501	1	101	Delivered
502	2	103	Delivered
503	1	102	Delivered
504	3	103	Cancelled
505	4	104	Delivered

* Note: Varun Joshi (user_id 5) has not placed any orders yet.

🔹 Step 1: High-Value Products Inventory Concepts: SELECT + WHERE + ORDER BY

💼 Store Owner: "Which products in our catalog are priced at ₹1,000 or more? Sort them from highest to lowest price."

SQL Query:SELECT title, price 
FROM Items 
WHERE price >= 1000 
ORDER BY price DESC;

Output:+------------------+-------+
| title            | price |
+------------------+-------+
| Smart Watch      | 2500  |
| Wireless Earbuds | 1200  |
+------------------+-------+
2 items found.

💡 How It Works: WHERE price >= 1000 filtered out lower-priced items (500 and 800), and ORDER BY price DESC placed the highest-priced Smart Watch (2500) at the top.

🔹 Step 2: Total Delivered Revenue & Order Count Concepts: COUNT + SUM + INNER JOIN

💼 Store Owner: "How much total revenue did we generate from successfully delivered orders, and how many orders were delivered?"

SQL Query:SELECT 
    COUNT(o.order_id) AS total_orders_delivered,
    SUM(i.price) AS total_revenue_inr
FROM Orders o
INNER JOIN Items i ON o.item_id = i.item_id
WHERE o.status = 'Delivered';

Output:+------------------------+-------------------+
| total_orders_delivered | total_revenue_inr |
+------------------------+-------------------+
| 4                      | 5000              |
+------------------------+-------------------+
(Earbuds: 1200 + Watch: 2500 + Charger: 500 + Stand: 800)

💡 How It Works: INNER JOIN joined Orders and Items on item_id to fetch item prices. WHERE status = 'Delivered' excluded cancelled orders, calculating exact net sales of ₹5,000.

🔹 Step 3: Comprehensive Order Invoice Report Concepts: 3-Table INNER JOIN + Aliases

💼 Store Owner: "Generate an invoice summary report displaying each customer's name, city, ordered product name, price, and delivery status."

SQL Query:SELECT 
    o.order_id,
    u.name AS customer_name,
    u.city,
    i.title AS product_name,
    i.price,
    o.status
FROM Orders o
INNER JOIN Users u ON o.user_id = u.user_id
INNER JOIN Items i ON o.item_id = i.item_id
ORDER BY o.order_id ASC;

Output:+----------+---------------+-----------+------------------+-------+-----------+
| order_id | customer_name | city      | product_name     | price | status    |
+----------+---------------+-----------+------------------+-------+-----------+
| 501      | Aman Gupta    | Delhi     | Wireless Earbuds | 1200  | Delivered |
| 502      | Riya Sen      | Mumbai    | Smart Watch      | 2500  | Delivered |
| 503      | Aman Gupta    | Delhi     | Fast Charger     | 500   | Delivered |
| 504      | Kabir Das     | Delhi     | Smart Watch      | 2500  | Cancelled |
| 505      | Neha Sharma   | Bangalore | Laptop Stand     | 800   | Delivered |
+----------+---------------+-----------+------------------+-------+-----------+

💡 How It Works: We placed Orders at the center, joining Users on one side and Items on the other, creating a comprehensive invoice dataset.

🔹 Step 4: City-Wise Revenue Breakdown Concepts: GROUP BY + SUM + ORDER BY

💼 Store Owner: "Which cities generate the highest delivered revenue for us? Display cities ranked by revenue from highest to lowest."

SQL Query:SELECT 
    u.city,
    COUNT(o.order_id) AS orders_count,
    SUM(i.price) AS city_revenue
FROM Orders o
INNER JOIN Users u ON o.user_id = u.user_id
INNER JOIN Items i ON o.item_id = i.item_id
WHERE o.status = 'Delivered'
GROUP BY u.city
ORDER BY city_revenue DESC;

Output:+-----------+--------------+--------------+
| city      | orders_count | city_revenue |
+-----------+--------------+--------------+
| Mumbai    | 1            | 2500         |
| Delhi     | 2            | 1700         |
| Bangalore | 1            | 800          |
+-----------+--------------+--------------+

💡 How It Works: GROUP BY u.city created city clusters and SUM(i.price) aggregated the sales for each cluster. Mumbai ranked #1 with ₹2,500.

🔹 Step 5: Inactive Customer Detection & VIP Tagging Concepts: LEFT JOIN + COALESCE + CASE

💼 Store Owner: "List all registered customers. If a customer spent ₹2,000 or more, tag them as 'VIP', if they spent less tag as 'Regular', and if they never ordered, tag them as 'Inactive' so we can email a discount coupon."

SQL Query:SELECT 
    u.name,
    u.city,
    COALESCE(SUM(CASE WHEN o.status = 'Delivered' THEN i.price ELSE 0 END), 0) AS total_spent,
    CASE 
        WHEN COUNT(o.order_id) = 0 THEN 'Inactive (Send Coupon)'
        WHEN SUM(CASE WHEN o.status = 'Delivered' THEN i.price ELSE 0 END) >= 2000 THEN '⭐ VIP Customer'
        ELSE 'Regular Customer'
    END AS customer_tag
FROM Users u
LEFT JOIN Orders o ON u.user_id = o.user_id
LEFT JOIN Items i ON o.item_id = i.item_id
GROUP BY u.user_id, u.name, u.city
ORDER BY total_spent DESC;

Output:+-------------+-----------+-------------+------------------------+
| name        | city      | total_spent | customer_tag           |
+-------------+-----------+-------------+------------------------+
| Riya Sen    | Mumbai    | 2500        | ⭐ VIP Customer        |
| Aman Gupta  | Delhi     | 1700        | Regular Customer       |
| Neha Sharma | Bangalore | 800         | Regular Customer       |
| Kabir Das   | Delhi     | 0           | Regular Customer       |
| Varun Joshi | Jaipur    | 0           | Inactive (Send Coupon) |
+-------------+-----------+-------------+------------------------+

💡 How It Works: 1. LEFT JOIN ensured customers with zero orders (Varun Joshi) were not dropped.
2. COALESCE safely converted empty amounts to 0.
3. CASE applied automated customer tier tags!

Score 80%+ in the Exam & Win Certificate + Gifts!