Haan, overall tumhare concepts sahi hain. Bas interview ke liye definitions ko thoda precise aur language ko clean karna hai. Main tumhare notes ko 4–5 years experience level ke interview answer ke according correct kar raha hoon.
1. What is JOIN?
JOIN is used to combine data from two or more tables based on a related column or join condition.
Example:
orders.customer_id = customers.id
Types of JOIN
1. INNER JOIN
Returns only the records that have matching values in both tables.
A mein 5 rows aur B mein 5 rows hain, but only 3 match → result mein 3 matching rows.
2. LEFT JOIN
Returns:
- All matching records from both tables
- All records from the left table
- If there is no match in right table → right-side columns become
NULL
3. RIGHT JOIN
Same concept as LEFT JOIN, but all records from the right table are preserved.
4. FULL OUTER JOIN
Returns:
- All matching records
- All unmatched records from left table
- All unmatched records from right table
So simple:
FULL OUTER = LEFT + RIGHT
5. CROSS JOIN
Creates a Cartesian product.
If:
- Table A = 3 rows
- Table B = 4 rows
Then:
3 × 4 = 12 rows
So your understanding here is correct.
2. WHERE vs HAVING
Tumhara answer correct hai.
WHERE
WHERE individual rows ko filter karta hai before GROUP BY / aggregation.
Example:
SELECT *
FROM employees
WHERE salary > 50000;
Pehle rows filter hongi.
HAVING
HAVING groups ko filter karta hai after GROUP BY and aggregation.
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
Yahan pehle department-wise groups banenge, phir jin groups ka count > 5 hai woh return honge.
Interview shortcut
WHERE → rows filter
HAVING → groups filter
3. SQL Optimization Techniques
Tumhari definition achhi hai. Thoda interview-ready:
SQL optimization is the process of improving query performance by reducing execution time, unnecessary I/O, CPU and memory usage, and the amount of data processed.
Important techniques:
1. Avoid N+1 Query Problem
Suppose API ko 100 orders fetch karne hain.
Instead of:
1 query → fetch 100 orders
100 queries → fetch customer/details for each order
Total:
101 queries
This is N+1 problem.
Possible solutions:
- JOIN / fetch join
- Batch fetching
- Proper ORM configuration
- Carefully using eager/lazy loading
Important: Sirf EAGER kar dena universal solution nahi hai. Eager loading unnecessary data bhi fetch kar sakta hai.
2. Pagination
Large dataset ko ek saath fetch karne ke bajaye small chunks mein fetch karna.
Instead of:
1,00,000 records
fetch:
Page 1 → 20 records
Page 2 → 20 records
...
This reduces memory and response time.
3. Avoid SELECT *
Instead of:
SELECT *
FROM employees;
required columns select karo:
SELECT id, name, salary
FROM employees;
Isse unnecessary data transfer/read kam ho sakta hai.
4. Indexing
Frequently searched/filter/join/sort columns par appropriate indexes use karna query ko faster bana sakta hai.
Example:
WHERE customer_id = 101
Agar customer_id par suitable index hai, database ko relevant rows locate karna easier ho sakta hai.
But: Har column par index nahi banana chahiye, because indexes storage lete hain aur INSERT/UPDATE/DELETE ko expensive bana sakte hain.
5. EXPLAIN
EXPLAIN se database ka query execution plan dekhte hain.
Isse pata chal sakta hai:
- Index use ho raha hai ya nahi
- Full table scan ho raha hai?
- Kitni rows scan ho rahi hain?
- Join kaise execute ho raha hai?
- Expensive sort/group operation hai?
- Estimated cost kya hai?
4. EXPLAIN — Production Scenario
Tumhara production scenario bilkul practical hai.
Interview mein aise bol sakte ho:
"Suppose one of our APIs is taking more time than expected. First, I check the API response time and application logs. If the database query is suspected, I check the query execution time and use EXPLAIN to analyze the execution plan. I check whether indexes are being used, whether there is a full table scan, how many rows are being scanned, and how joins or sorting are being performed. Based on that, I optimize the query or add/modify the appropriate index and then verify the improvement."
Flow yaad rakho:
API slow → identify DB query → EXPLAIN → analyze plan → optimize → test again
5. What is CTE?
Tumhari definition correct hai, but ek important correction:
CTE ko sirf "another way to write subquery" mat bolo.
Better:
CTE (Common Table Expression) is a temporary named result set that we define using the WITH clause and use within a SQL statement.
Example:
WITH high_salary AS (
SELECT *
FROM employees
WHERE salary > 50000
)
SELECT *
FROM high_salary;
Why use CTE?
- Readability
- Complex query ko smaller logical parts mein divide karna
- Same logical result ko query ke andar reuse karna
- Recursive queries ke liye
Interview point
CTE ka main benefit query readability and organization hai.
6. What is Window Function?
Tumhari definition almost perfect hai.
Simple definition:
A window function performs a calculation across a set of related rows while keeping the individual rows in the result.
Yahi sabse important point hai:
Window function calculation karta hai, but original rows ko remove nahi karta.
Common examples:
ROW_NUMBER()
Har row ko unique sequential number deta hai.
Employee Salary Row_Number
A 50000 1
B 50000 2
C 40000 3
RANK()
Same value → same rank
But gap create hota hai.
Salary Rank
50000 1
50000 1
40000 3
30000 4
DENSE_RANK()
Same value → same rank
But gap nahi hota.
Salary Dense_Rank
50000 1
50000 1
40000 2
30000 3
Other window functions
SUM()AVG()COUNT()MIN()MAX()LEAD()LAG()
Usually OVER() ke saath use hote hain.
7. GROUP BY vs Window Function
Ye distinction interview mein bahut important hai.
GROUP BY
GROUP BY multiple rows ko group mein combine karta hai aur generally one result row per group deta hai.
Example:
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
Result:
Department Average Salary
IT 70000
HR 50000
Finance 60000
Original individual employee rows result mein nahi hain.
Window Function
Window function calculation karta hai but individual rows ko preserve karta hai.
Example:
SELECT
name,
department,
salary,
AVG(salary) OVER(PARTITION BY department) AS dept_avg
FROM employees;
Result:
Name Department Salary Dept_Avg
A IT 70000 65000
B IT 60000 65000
C HR 50000 50000
D HR 50000 50000
Dekho:
GROUP BY → rows combine
Window Function → rows preserve + calculation
Ek line mein yaad karo:
GROUP BY changes the number of rows; Window Function normally does not.
Aur PARTITION BY ko GROUP BY mat samajhna. PARTITION BY window function ke calculation ke liye rows ko logical groups mein divide karta hai, lekin individual rows result mein bani rehti hain.
Top comments (0)