🧭 Never seen SQL before? Just understand these 4 things!

SQL is not hard programming. Imagine you have an Excel Sheet open and you are typing out instructions telling it what to do:

1. Database A cupboard or folder that holds many different sheets.

2. Table One Excel sheet with Columns (headings) and Rows (records).

3. Row vs Column Column = Header (like Name, City). Row = all the data of one person.

4. Golden Formula SELECT (what to see) + FROM (from which sheet) + WHERE (what the filter is).

ℹ️ Dialect note: The basic commands are the same across different databases (MySQL, SQL Server, PostgreSQL, Oracle), but some syntax changes. The examples and outputs in this course use MySQL style; wherever the syntax differs (such as LIMIT / TOP, AUTO_INCREMENT, Stored Procedures), the syntax for other databases is given alongside.

⚡ Live Interactive SQL Sandbox (Browser Engine)

AlaSQL Engine Active

Here you can run live SQL queries! Click the quick buttons below, or write your own SQL code and press Run Query (Ctrl + Enter):

Quick Queries:

Results will appear here...

🗄️ Active Sandbox Data Tables (Our Sample Data)

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 (Everyday Basic Commands)

SQL SELECT & FROM

Pull out just the columns you want from the sheet.

💡 In simple words: Imagine you open Excel and look at only the columns you like. If you want all columns, just put a * (star). Very simple.

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 rows in set (0.00 sec)
[4 records fetched]

SQL SELECT DISTINCT

Remove repeated values and see only the unique ones.

💡 In simple words: Think of Excel's Remove Duplicates button. If 10 people are from India, India shows up only once, okay?

Syntax:SELECT DISTINCT column_name FROM table_name;

Example:SELECT DISTINCT Country FROM Customers;

Output:+---------+
| Country |
+---------+
| India   |
| Germany |
| UK      |
+---------+
3 rows in set (0.00 sec)
[India appeared twice, but was shown only once]

SQL WHERE

Add a condition and get only the rows that match it.

💡 In simple words: In Excel you press Filter and say show only 'India'. That is exactly what WHERE does.

Syntax:SELECT * FROM table WHERE condition;

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

Output:+--------------+--------+
| CustomerName | City   |
+--------------+--------+
| Amit Sharma  | Delhi  |
| Pooja Verma  | Mumbai |
+--------------+--------+
2 rows in set (0.00 sec)

SQL AND, OR, NOT

Combine more than one condition together.

💡 In simple words: AND means both things must be true. OR means either one is enough. NOT flips the condition.

Syntax:SELECT * FROM table WHERE cond1 AND cond2;

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

Output:+------------+--------------+-------+---------+
| CustomerID | CustomerName | City  | Country |
+------------+--------------+-------+---------+
|          1 | Amit Sharma  | Delhi | India   |
+------------+--------------+-------+---------+
1 row in set (0.00 sec)
[Only Amit matched both conditions]

SQL ORDER BY

Arrange data from smallest to largest or the other way round.

💡 In simple words: Think of Excel's Sort button: A-to-Z is ASC, Z-to-A is DESC.

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  |
+--------------+--------+
4 rows in set (0.00 sec)
[Z to A alphabetical order]

SQL INSERT INTO

Add a brand new row to the table.

💡 In simple words: Like writing a new line in a register, that's all it is.

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 added: CustomerID 5, Rohan Das, Pune]

SQL UPDATE

Fix data that is wrong without touching the rest of the table.

💡 In simple words: Like erasing a written name and correcting it. And one thing, always use WHERE, otherwise everyone's data will change!

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

Remove a row from the table for good.

💡 In simple words: Like right-clicking a row in Excel and pressing Delete Row. That is DELETE.

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 completely deleted from the table]

SQL LIMIT / TOP

See only the first few records. (LIMIT in MySQL / PostgreSQL / SQLite, TOP in SQL Server / MS Access)

💡 In simple words: Showing only the top 2 or top 5 from a long list. The job is the same, only the way you write it changes with the database.

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

Syntax (SQL Server / MS Access):SELECT TOP count * FROM table;

Syntax (Oracle 12c+ / SQL standard):SELECT * FROM table FETCH FIRST count ROWS ONLY;

Example (MySQL):SELECT CustomerName, City FROM Customers LIMIT 2;

Same Example (SQL Server):SELECT TOP 2 CustomerName, City FROM Customers;

Output:+--------------+--------+
| CustomerName | City   |
+--------------+--------+
| Amit Sharma  | Delhi  |
| Pooja Verma  | Mumbai |
+--------------+--------+
2 rows in set (0.00 sec)
[Both queries gave the same output: only the first 2 records]

SQL NULL Values

Check whether a box is empty or not.

💡 In simple words: NULL does not mean 0, my friend. It means 'empty / there is no data'. That is why we write IS NULL, not = 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    |
+-------------+---------+
1 row in set (0.00 sec)
[Karan had no department assigned]

2. SQL Aggregate Functions (Automatic Maths and Totals)

SQL MIN() & MAX()

Find the smallest and the largest number.

💡 In simple words: Which item in the shop is the cheapest and which is the most expensive? That is all we are asking.

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

Example:SELECT MIN(Price) AS Sasta, MAX(Price) AS Mehnga FROM Products;

Output:+-------+--------+
| Sasta | Mehnga |
+-------+--------+
|    15 |    180 |
+-------+--------+
1 row in set (0.00 sec)
[USB Hub = 15, HD Monitor = 180]

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

Find how many there are, the total, and the average.

💡 In simple words: COUNT gives the count, SUM gives the total, and AVG gives the average. Simple.

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

Example:SELECT COUNT(ProductID) AS Kul_Item, SUM(Price) AS Jod, AVG(Price) AS Ausat FROM Products;

Output:+----------+-----+-------+
| Kul_Item | Jod | Ausat |
+----------+-----+-------+
|        4 | 265 | 66.25 |
+----------+-----+-------+
1 row in set (0.00 sec)
[Total 4 items, Total rate 265, Average 66.25]

3. Advanced Querying & Joins (Smart Search and Joining Two Sheets)

SQL LIKE & Wildcards (%)

Search by the first or last letters of a name.

💡 In simple words: Typing 'Del' in your contact list so that Delhi and Dehradun both show up.

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

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

Output:+--------------+-------+
| CustomerName | City  |
+--------------+-------+
| Amit Sharma  | Delhi |
+--------------+-------+
1 row in set (0.00 sec)

SQL BETWEEN & IN

Search within a range, or among a few chosen options.

💡 In simple words: Saying, show me the items priced between 20 and 50 rupees.

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 |
+---------------------+-------+
2 rows in set (0.00 sec)

SQL Aliases (AS)

Give a column a new, nicer name, that's all.

💡 In simple words: In code the column is called cust_first_nm, but on screen you want to show Client_Name. That is what an alias does.

Syntax:SELECT col AS Nickname FROM table;

Example:SELECT CustomerName AS Client_Name FROM Customers LIMIT 2;

Output:+-------------+
| Client_Name |
+-------------+
| Amit Sharma |
| Pooja Verma |
+-------------+
2 rows in set (0.00 sec)
[The header looks neat and clean]

SQL INNER JOIN

Join two separate sheets using a common ID.

💡 In simple words: One sheet has the Employee name, the other has the Department. Match them on dept_id and you get one big table. That is JOIN.

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 |
+------------+-------------+
3 rows in set (0.00 sec)
[Karan Mehta was left out because his dept_id was empty]

SQL GROUP BY & HAVING

Make groups of rows with the same values and get a summary.

💡 In simple words: Think of Excel's Pivot Table. Count by country which countries have 2 or more people.

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 |
+---------+-------+
1 row in set (0.00 sec)
[UK and Germany were filtered out]

SQL CASE Statement

Make decisions based on a condition, i.e. if-else.

💡 In simple words: If the salary is above 60,000 call them 'Senior', otherwise 'Associate'. Plain if-else.

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 |
+-------------+-----------+
4 rows in set (0.00 sec)

SQL COALESCE / IFNULL

Show a default value in place of an empty (NULL) spot.

💡 In simple words: If someone's department is empty, don't show an error, just write '0' or 'Not Assigned'.

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 |
| Vikas Jain  |       10 |
| Karan Mehta |        0 |
+-------------+----------+
4 rows in set (0.00 sec)
[0 appeared in place of NULL!]

SQL UNION

Stack two lists one below the other into one long list.

💡 In simple words: One list from a Delhi shop, one from a Mumbai shop. Stick them one below the other.

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 |
+--------+
5 rows in set (0.00 sec)
[A single list of all cities was created]

4. Database & Table Management (Creating a New File and Sheet)

SQL CREATE TABLE

Create a brand new empty table.

💡 In simple words: Open a new sheet in Excel and write the headers in the first row, like ID, Name, Salary.

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' was created; data can now be inserted into it]

SQL ALTER TABLE

Add a new column to an existing table, or remove one.

💡 In simple words: Adding a new 'Email' column later to a sheet that is already in use, that's all.

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)
[A new 'Email' column was successfully added to the Customers sheet]

SQL Primary Key

An ID number that uniquely identifies each row.

💡 In simple words: Think of a school Roll Number. No two people can have the same one, and it can't be left blank.

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: the database will reject any duplicate RollNo]

SQL AUTO_INCREMENT / IDENTITY

The ID goes up by itself, 1, 2, 3, with every new entry. (AUTO_INCREMENT in MySQL, IDENTITY in SQL Server)

💡 In simple words: Think of a token machine. A new customer arrives and automatically gets the next number.

Syntax (MySQL):ID INT AUTO_INCREMENT PRIMARY KEY

Syntax (SQL Server):ID INT IDENTITY(start, step) PRIMARY KEY

Syntax (PostgreSQL / SQLite):PostgreSQL: ID SERIAL PRIMARY KEY
SQLite: ID INTEGER PRIMARY KEY AUTOINCREMENT

Example (MySQL):CREATE TABLE Users (UserID INT AUTO_INCREMENT PRIMARY KEY, FullName VARCHAR(50)) AUTO_INCREMENT = 101;
INSERT INTO Users (FullName) VALUES ('David Miller');

Same Example (SQL Server):CREATE TABLE Users (UserID INT IDENTITY(101, 1) PRIMARY KEY, FullName VARCHAR(50));
INSERT INTO Users (FullName) VALUES ('David Miller');

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

5. Stored Procedures & Security (Shortcuts and Security)

SQL Stored Procedure

Save a big query under a name so you don't have to type it again. (CALL in MySQL, EXEC in SQL Server)

💡 In simple words: Think of an Excel Macro. Don't type a 20-line query again and again, save it once and run it with one click.

Syntax (MySQL):DELIMITER //
CREATE PROCEDURE ProcName()
BEGIN
    query;
END //
DELIMITER ;

CALL ProcName();

Syntax (SQL Server):CREATE PROCEDURE ProcName
AS
    query;
GO

EXEC ProcName;

Example (MySQL):DELIMITER //
CREATE PROCEDURE AllCustomers()
BEGIN
    SELECT CustomerName, Country FROM Customers;
END //
DELIMITER ;

CALL AllCustomers();

Same Example (SQL Server):CREATE PROCEDURE AllCustomers
AS
    SELECT CustomerName, Country FROM Customers;
GO

EXEC AllCustomers;

Note: In MySQL, DELIMITER is changed so that the ; inside the procedure does not cut the whole command short. CALL also works in PostgreSQL (11+), but the way the procedure body is written is different.

Output:+--------------+---------+
| CustomerName | Country |
+--------------+---------+
| Amit Sharma  | India   |
| Pooja Verma  | India   |
+--------------+---------+
2 rows in set (0.00 sec)
[Procedure instantly executed]

SQL Comments (--)

Leave a note for yourself next to the code.

💡 In simple words: It is like putting up a sticky note. The database ignores it when it runs the query.

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

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

Output:+------------+
| Name       |
+------------+
| Rahul Roy  |
| Sneha Rao  |
| Vikas Jain |
+------------+
3 rows in set (0.00 sec)
[The comment (the line starting with --) was ignored and the query executed]

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

💼 Why the interviewer asks this: To see whether you understand Subqueries and Window Functions, or you only rely on basic LIMIT.

Solution A (Using Subquery - Standard SQL): First find the highest salary, then select the largest salary that is smaller than it:

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

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

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

Solution C (Using OFFSET-FETCH - SQL Server 2012+):

SELECT DISTINCT Salary 
FROM Employees 
ORDER BY Salary DESC 
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

Result on Employees Table:+---------------------+
| SecondHighestSalary |
+---------------------+
|               65000 |
+---------------------+
1 row in set (0.00 sec)
[Top is Vikas: 72000, 2nd is Rahul: 65000]

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

💼 Why the interviewer asks this: Data cleaning and finding duplicates is a data analyst's daily job, so they ask this.

When you apply GROUP BY on a column and check HAVING COUNT(*) > 1, all the duplicate records come out:

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

Output:+---------+----------------+
| Country | DuplicateCount |
+---------+----------------+
| India   |              2 |
+---------+----------------+
1 row in set (0.00 sec)

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

💼 Why the interviewer asks this: Do you know which operation can be rolled back and which one wipes the table structure itself? That is what they check.

Feature	DELETE (DML)	TRUNCATE (DDL)	DROP (DDL)
What it does	Removes rows one by one	Empties all rows in one go	Deletes both the table and its structure
WHERE Clause	Yes (can filter specific rows)	No (the whole sheet is cleared)	No
Rollback	Yes (can be rolled back within a transaction)	No (very difficult / engine dependent)	No (permanently deleted)
Speed	Slow (every row is logged)	Very Fast (de-allocates storage)	Fastest

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

💼 Why the interviewer asks this: To test whether you understand how NULL behaves in a LEFT JOIN.

-- 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 |
+-------------+--------+
1 row in set (0.00 sec)
[Varun has not placed even one order yet]

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

💼 Why the interviewer asks this: When data in one table points to other rows of the same table, like an employee's manager who is also an employee in that table.

Joining a table to itself by giving it two different names (aliases e and m) 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        |
+--------------+-------------+
3 rows in set (0.00 sec)

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

💼 Why the interviewer asks this: 90% of beginners get confused between WHERE and HAVING, so this gets asked.

• WHERE: Filters individual rows before the data is grouped. Aggregates like SUM() and AVG() cannot be used in it.
• HAVING: Filters the calculated summary after GROUP BY has been applied (e.g. HAVING COUNT(*) > 2).

SELECT City, COUNT(*) AS Total
FROM Customers
WHERE Country = 'India'         -- First, keep only India (WHERE)
GROUP BY City
HAVING COUNT(*) >= 1;          -- Filter applied after the groups are formed (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 it can have many Unique Keys.
2. NULL Value: A primary key can never be NULL, but a unique key allows NULL.
3. Usage: A Primary Key identifies the row. A unique key is for extra unique data.

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

💼 Why the interviewer asks this: Ranking functions are asked the most in senior interviews, especially when two people have the same score.

Suppose two people have equal marks (90, 90):

ROW_NUMBER(): Always gives consecutive, distinct numbers: 1, 2, 3, 4.
RANK(): Repeats the rank on a tie, then skips the next number: 1, 2, 2, 4.
DENSE_RANK(): Repeats the rank on a tie and does not skip any number: 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 the interviewer asks this: In production, security is the first priority, so this question comes up.

SQL Injection: This is when a hacker types ' OR '1'='1 into a login form and accesses data without a password.
Defense: Always use Parameterized Queries (Prepared Statements).

Secure Pattern:SELECT * FROM Users WHERE Username = ? AND Password = ?;

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

💼 Why the interviewer asks this: If the table has 10 million rows, how will the query run fast? The interviewer wants to know this.

Index: works like the Index at the back of a book.
• Pros: SELECT and WHERE search speed can become up to 100x faster.
• Cons: Write operations (INSERT/UPDATE) become slightly slower and extra storage is needed.

CREATE INDEX idx_city ON Customers(City);

📁 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` (Items) 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 Record) 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

🔹 Step 1: Listing the Expensive Items SELECT + WHERE + ORDER BY

💼 Owner's Question: "Which products in our shop cost ₹1,000 or more? Show them arranged from most expensive to cheapest."

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

🔹 Step 2: Calculating Total Sales (Revenue) COUNT + SUM + INNER JOIN

💼 Owner's Question: "How much total revenue did we earn from successfully delivered orders, and how many orders were delivered in total?"

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';

🔹 Step 3: Complete Invoice Report 3-Table INNER JOIN + Aliases

💼 Owner's Question: "I need a summary report that clearly shows which customer bought which product and what its delivery status is."

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;

🔹 Step 4: Revenue by City (City Analytics) GROUP BY + SUM + ORDER BY

💼 Owner's Question: "How many rupees of delivered sales came from each city? Show the city that earned us the most at the top."

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;

🔹 Step 5: Quiet Customers and VIP Tagging LEFT JOIN + COALESCE + CASE

💼 Owner's Question: "List all registered customers. If someone has spent more than ₹2,000 label them 'VIP', label lower spenders 'Regular', and tag anyone who has not placed a single order as 'Inactive'."

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;

Score 80%+ in the Final Exam to earn your Certificate