Limited Offer
Exam में 80%+ score करें और जीतें Certificate + Gifts!
- ✅ पूरा course free
- 🎓 Final Exam में 80%+ = Certificate
- 📝 50 Questions
- 🎁 Cheat Sheet + Gifts + बहुत कुछ
🧭 Never seen SQL before? Just understand these 4 things!
SQL कोई कठिन programming नहीं है। सोचिए आपके पास एक Excel Sheet खुली है और आप उसे type करके बोल रहे हैं कि क्या करना है:
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: SQL के अलग-अलग database (MySQL, SQL Server, PostgreSQL, Oracle) में बुनियादी commands एक जैसी हैं, पर कुछ syntax बदल जाता है। इस course के examples और outputs MySQL style में हैं; जहाँ syntax अलग है (जैसे LIMIT / TOP, AUTO_INCREMENT, Stored Procedure) वहाँ दूसरे databases का syntax भी साथ में दिया गया है।
⚡ Live Interactive SQL Sandbox (Browser Engine)
AlaSQL Engine Activeयहाँ आप live SQL queries चलाकर देख सकते हैं! नीचे दिए गए quick buttons पर click करें या अपना SQL code लिखकर Run Query (Ctrl + Enter) दबाएँ:
🗄️ 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 2 बार था, पर सिर्फ़ 1 बार दिखा]
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)
[सिर्फ़ Amit दोनों शर्तों पर फ़िट बैठा]
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 से 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)
[नई row जुड़ गई: 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 का city अब Delhi से बदलकर 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 का record table से completely delete हो गया]
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)
[दोनों queries का output एक जैसा: सिर्फ़ पहले 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 का कोई department assign नहीं था]
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)
[Header सुंदर और साफ़ दिखा]
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 छूट गया क्योंकि उसका dept_id खाली था]
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 और Germany filter हो गए]
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)
[NULL की जगह 0 आ गया!]
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)
[सभी शहरों की एक single list बन गई]
4. Database & Table Management (नई File और 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' बन गई, अब इसमें data डाला जा सकता है]
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)
[Customers sheet में 'Email' का नया column successfully जुड़ गया]
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: duplicate RollNo आते ही database मना कर देगा]
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 अपने-आप allot हो गया]
5. Stored Procedures & Security (Shortcut और सुरक्षा)
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;
नोट: MySQL में
DELIMITER इसलिए बदलते हैं ताकि procedure के अंदर का ; पूरी command को बीच में न काट दे। PostgreSQL (11+) में भी CALL चलता है, पर procedure की body लिखने का तरीका अलग है।
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:-- सिर्फ़ 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)
[Comment (-- वाली line) ignore हो गई और query execute हो गई]
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):
सबसे पहले सबसे बड़ी salary निकालो, फिर उससे छोटी जो सबसे बड़ी salary हो उसे select करो:
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.
जब किसी column पर
GROUP BY लगाकर HAVING COUNT(*) > 1 check करते हैं, तो सारे duplicate records निकलकर आ जाते हैं:
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) |
|---|---|---|---|
| करता क्या है | Rows को एक-एक करके हटाता है | सारी rows को एक झटके में खाली करता है | Table और उसके structure दोनों को मिटा देता है |
| WHERE Clause | हाँ (Specific rows filter कर सकते हैं) | नहीं (पूरी sheet साफ़ होगी) | नहीं |
| Rollback | हाँ (Transactions के साथ वापस आ सकता है) | नहीं (बहुत मुश्किल / Engine dependent) | नहीं (Permanently delete) |
| Speed | Slow (हर row का log बनता है) | Very Fast (Storage de-allocate करता है) | 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 ने अभी तक 1 भी order नहीं किया]
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.
एक ही table को दो अलग नाम (Aliases
e और m) देकर आपस में जोड़ने को 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: Data के group बनने से पहले individual rows पर filter लगाता है। इसमें
• HAVING:
SUM(), AVG() नहीं चलते।• HAVING:
GROUP BY होने के बाद calculated summary पर filter लगाता है (जैसे HAVING COUNT(*) > 2)।
SELECT City, COUNT(*) AS Total
FROM Customers
WHERE Country = 'India' -- पहले सिर्फ़ India छाँटा (WHERE)
GROUP BY City
HAVING COUNT(*) >= 1; -- Group बनने के बाद filter किया (HAVING)
Q7: What is the difference between PRIMARY KEY and UNIQUE KEY?
Schema Design
1. Count: एक table में सिर्फ़ 1 Primary Key हो सकती है, लेकिन कई सारी Unique Keys हो सकती हैं।
2. NULL Value: Primary key कभी भी
3. उपयोग: Primary Key row की पहचान होती है। Unique key extra unique data के लिए होती है।
2. NULL Value: Primary key कभी भी
NULL नहीं हो सकती। लेकिन Unique key में NULL allow होता है।3. उपयोग: Primary Key row की पहचान होती है। Unique key 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.
मान लीजिए दो लोगों के marks बराबर (90, 90) हैं:
- ROW_NUMBER(): हमेशा लगातार अलग number देगा:
1, 2, 3, 4. - RANK(): बराबर आने पर rank repeat करेगा, फिर अगला number skip करेगा:
1, 2, 2, 4. - DENSE_RANK(): बराबर आने पर rank repeat करेगा, और कोई number skip नहीं करेगा:
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: जब कोई hacker login form में
बचाव (Defense): हमेशा Parameterized Queries (Prepared Statements) का use करें।
' OR '1'='1 लिखकर बिना password के data access कर लेता है।बचाव (Defense): हमेशा Parameterized Queries (Prepared Statements) का use करें।
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: किताब के आख़िर में बने Index की तरह होता है।
• फ़ायदा (Pros):
• नुकसान (Cons): Write operation (
• फ़ायदा (Pros):
SELECT और WHERE search speed 100x fast हो जाती है।• नुकसान (Cons): Write operation (
INSERT/UPDATE) थोड़ा slow हो जाता है और extra storage लगती है।
CREATE INDEX idx_city ON Customers(City);
📁 Database Schema:
1. Users (ग्राहक) 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 (सामान) 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: महँगे सामान की List निकालना
SELECT + WHERE + ORDER BY
💼 मालिक का सवाल: "हमारी दुकान में ₹1,000 या उससे ज़्यादा क़ीमत के कौन से products हैं? उन्हें सबसे महँगे से सस्ते order में arrange करके दिखाओ।"
SELECT title, price
FROM Items
WHERE price >= 1000
ORDER BY price DESC;
🔹 Step 2: Total Sales (कमाई) का हिसाब निकालना
COUNT + SUM + INNER JOIN
💼 मालिक का सवाल: "Successfully deliver हुए orders से हमारी कुल कितनी कमाई (Revenue) हुई और total कितने orders deliver हुए?"
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
💼 मालिक का सवाल: "मुझे एक ऐसी summary report चाहिए जिसमें साफ़ दिखे कि किस customer ने कौन सा product ख़रीदा है और उसका delivery status क्या है?"
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: शहर के अनुसार कमाई (City Analytics)
GROUP BY + SUM + ORDER BY
💼 मालिक का सवाल: "किस शहर से हमें कितने रुपये की delivered sales आई है? सबसे ज़्यादा कमाई करवाने वाले शहर को 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: शांत रहने वाले Customers और VIP Tagging
LEFT JOIN + COALESCE + CASE
💼 मालिक का सवाल: "सभी registered customers को list करो। अगर किसी ने ₹2,000 से ऊपर ख़र्च किया है तो 'VIP' लिखो, कम ख़र्च वाले को 'Regular', और जिसने एक भी order नहीं किया उसे 'Inactive' tag दो।"
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;