Data Insights MySQL Masterclass — Part 6

Advanced MySQL — Views, Indexes, Stored Procedures, Functions, Triggers & Window Functions

Production-level MySQL — Views se data abstraction, Indexes se blazing fast queries, Stored Procedures se reusable logic, Triggers se automatic actions, aur Window Functions se advanced analytics. Sab kuch real-world examples ke saath Data Insights par.

📑 Is Part 6 Mein Aap Kya Sikhenge:

• Topic 1: Views — CREATE VIEW, Uses & Limitations
• Topic 2: Indexes — Types, CREATE INDEX & Performance
• Topic 3: Stored Procedures — CREATE PROCEDURE, Parameters
• Topic 4: Functions — CREATE FUNCTION vs Stored Procedure
• Topic 5: Triggers — BEFORE/AFTER INSERT/UPDATE/DELETE
• Topic 6: Window Functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD

📋 Note: Is Part 6 mein wahi employees aur departments tables use karenge jo Part 4 mein banaye the. Kuch naye tables bhi banayenge jaise audit_log Triggers ke liye.

text

🔍 Definition: A View is a virtual table created by a stored SQL query. It does not store data itself — it pulls fresh data from the underlying tables every time it is queried. Views simplify complex queries, provide security by hiding sensitive columns, and create reusable query logic.

🎯 Samjho Simple Bhasha Mein: View ek saved query ka naam hai — ek shortcut ki tarah. Socho ek complex JOIN query hai jo tum baar baar run karte ho. Us query ko ek view ke roop mein save karo. Phir sirf SELECT * FROM view_name likho — complex query chhup jaati hai, clean simple naam se data milta hai. View ek window ki tarah hai jo tumhare actual tables ke data ko ek specific angle se dikhata hai.

💡 View ke 3 Main Benefits:
1. Simplicity: Complex JOIN queries ek simple naam se access karo
2. Security: Sensitive columns (salary, password) hide karo — sirf allowed columns dikhao
3. Reusability: Ek baar define karo, baar baar use karo — DRY principle

💻 Real-World Code Examples:

Example 1: Basic View — Employee + Department info ek saath.

-- View banao: employees + departments joined
CREATE VIEW vw_employee_details AS
SELECT
e.emp_id,
e.emp_name,
e.salary,
e.hire_date,
d.dept_name,
d.location

FROM employees e
INNER
JOIN departments d
ON e.dept_id = d.dept_id;

-- Use the view (just like a table!)
SELECT *
FROM vw_employee_details;

-- Filter, sort, aggregate on view
SELECT dept_name, COUNT(*) AS emp_count

FROM vw_employee_details

GROUP BY dept_name;

Example 2: Security View — salary column hide karo junior users se.

-- View without salary (for restricted users)
CREATE VIEW vw_public_employee_info AS
SELECT
emp_id,
emp_name,
dept_id,
hire_date

FROM employees;

-- High salary view (for HR/Finance only)
CREATE VIEW vw_high_earners AS
SELECT
emp_name,
salary,
dept_id

FROM employees

WHERE salary > 70000;

Example 3: View management — Show, Alter, Drop.

-- All views in database dekhna
SHOW FULL TABLES
WHERE Table_type = 'VIEW';

-- View ka definition dekhna
SHOW CREATE VIEW vw_employee_details;

-- View modify karna
CREATE OR REPLACE VIEW vw_employee_details AS
SELECT
e.emp_id,
e.emp_name,
e.salary,
e.hire_date,
d.dept_name,
d.location,
e.manager_id -- New column added

FROM employees e
INNER
JOIN departments d
ON e.dept_id = d.dept_id;

-- View delete karna
DROP VIEW vw_public_employee_info;

DROP VIEW IF EXISTS vw_high_earners;

📊 Expected Output (vw_employee_details):

emp_id	emp_name	salary	hire_date	dept_name	location
101	Rahul Sharma	85000.00	2020-03-15	IT	Bangalore
102	Priya Singh	62000.00	2020-07-01	HR	Mumbai
103	Amit Kumar	48000.00	2021-01-10	Sales	Delhi
104	Sneha Patel	92000.00	2019-11-20	IT	Bangalore
107	Deepak Rao	68000.00	2020-05-30	Finance	Chennai

(10 rows shown — Rohit Tiwari excluded due to NULL dept_id)

📊 Views — Limitations Table:

Feature	Supported?	Note
SELECT from View	✅ Yes	Works like a table
INSERT via View	⚠️ Limited	Only simple single-table views
UPDATE via View	⚠️ Limited	No GROUP BY, DISTINCT, JOIN views
Indexes on View	❌ No	Underlying table indexes used
Stores Data	❌ No	Always fetches fresh from tables
Nested Views	✅ Yes	View on top of another view

⚠️ Common Mistakes:

• Mistake: View ko permanent storage samajhna → View data store nahi karta — har baar underlying tables se fetch hota hai.
  Fix: Materialized view chahiye toh temporary table ya scheduled procedure use karo.
• Mistake: Complex view par INSERT/UPDATE karna → GROUP BY, DISTINCT, JOIN wale views updatable nahi hote.
  Fix: Direct table par DML karo, view sirf SELECT ke liye use karo.
• Mistake: View ka naam table se same rakhna → Confusion create hogi.
  Fix: Convention follow karo: vw_ prefix lagao views ke liye.

💬 Interview Questions:

text

🔍 Definition: An Index is a data structure that improves the speed of data retrieval operations on a table. Without an index, MySQL performs a full table scan (reads every row). With an index, MySQL uses a B-Tree or Hash structure to find rows directly — like a book's index page. Indexes speed up SELECT but slightly slow down INSERT, UPDATE, DELETE.

🎯 Samjho Simple Bhasha Mein: Index ek book ke index page ki tarah hai. Agar tumhe ek 1000 page ki book mein "MySQL" word dhundhna ho — bina index ke puri book padhni padegi. Index page se seedha page number milega. Database mein bhi — bina index ke 10 lakh rows mein ek row dhundhne ke liye sab scan karna padega. Index se seedha jump ho jaata hai.

💡 MySQL Index Types:
PRIMARY KEY: Auto-created, unique + not null, clustered index
UNIQUE INDEX: Duplicate values allowed nahi, NULL allowed
REGULAR INDEX: Duplicate values allowed, fastest for reads
COMPOSITE INDEX: Multiple columns ka combined index
FULL-TEXT INDEX: Text search ke liye (MATCH AGAINST)
SPATIAL INDEX: Geographic/geometry data ke liye

💻 Real-World Code Examples:

Example 1: Index create, show aur drop karna.

-- Simple index on dept_id column
CREATE INDEX idx_dept_id

ON employees (dept_id);

-- UNIQUE index on email
CREATE UNIQUE INDEX idx_unique_email

ON employees (emp_name);

-- COMPOSITE index (dept_id + salary together)
CREATE INDEX idx_dept_salary

ON employees (dept_id, salary);

-- Show all indexes on a table
SHOW INDEX
FROM employees;

-- Drop an index
DROP INDEX idx_dept_id
ON employees;

-- Add index via ALTER TABLE
ALTER TABLE employees
ADD INDEX idx_hire_date (hire_date);

Example 2: EXPLAIN — Query execution plan dekhna (index use ho raha hai ya nahi).

-- Before index: Full table scan
EXPLAIN SELECT *
FROM employees
WHERE dept_id = 1;

-- type: ALL (full scan) — slow!

-- Create index
CREATE INDEX idx_dept_id
ON employees (dept_id);

-- After index: Index scan
EXPLAIN SELECT *
FROM employees
WHERE dept_id = 1;

-- type: ref (index used) — fast!

📊 EXPLAIN Output — Before vs After Index:

-- BEFORE INDEX (Full Table Scan):

id	select_type	table	type	key	rows	Extra
1	SIMPLE	employees	ALL	NULL	11	where
id	select_type	table	type	key	rows	Extra
1	SIMPLE	employees	ref	idx_dept_id	3	NULL

Using -- type=ALL means full scan (reads all 11 rows to find matches) -- AFTER INDEX: -- type=ref means index used (reads only 3 rows!)

text

📊 Index Trade-offs:

Operation	Without Index	With Index
SELECT (WHERE filter)	🐢 Slow (full scan)	⚡ Fast (direct lookup)
ORDER BY	🐢 Slow (sort needed)	⚡ Fast (already sorted)
JOIN operations	🐢 Slow (nested loops)	⚡ Fast (indexed join)
INSERT	⚡ Fast	🐢 Slightly slower (index update)
UPDATE/DELETE	⚡ Fast (write)	🐢 Slightly slower (index maintain)
Storage Space	Less	Extra space for index structure

⚠️ Common Mistakes:

• Mistake: Har column par index lagana → Over-indexing se INSERT/UPDATE slow ho jaata hai aur storage waste hota hai.
  Fix: Sirf frequently searched, joined ya ordered columns par index lagao.
• Mistake: Function-based WHERE se index bypass hona → WHERE YEAR(hire_date) = 2021 index use nahi karega.
  Fix: WHERE hire_date BETWEEN '2021-01-01' AND '2021-12-31' — range filter index use karega.
• Mistake: Leading wildcard LIKE se index bypass → WHERE name LIKE '%Kumar' full scan karega.
  Fix: LIKE 'Kumar%' (no leading %) index use karega.

💬 Interview Questions:

text

🔍 Definition: A Stored Procedure is a pre-compiled set of SQL statements stored in the database with a name. It can accept input parameters (IN), output parameters (OUT), and both (INOUT). It is called using CALL statement and can contain control flow logic (IF, WHILE, LOOP, CASE).

🎯 Samjho Simple Bhasha Mein: Stored Procedure ek function ki tarah hai jo database mein save hoti hai. Jaise programming mein ek function banate ho jo baar baar use hota hai — waise hi yahan SQL ka ek "function" banate ho jo baar baar CALL karo. Parameters de sakte ho, result le sakte ho. Application code mein SQL likhne ki zaroorat nahi — sirf procedure ka naam aur parameters do.

💡 Parameter Types:
IN: Input parameter — procedure ke andar pass karo (default)
OUT: Output parameter — procedure se value wapas lo
INOUT: Both — pass karo aur modified value wapas lo

💻 Real-World Code Examples:

Example 1: Basic Procedure — Department ke employees dikhao.

-- Delimiter change karna zaroori hai (MySQL confusion avoid)
DELIMITER //

CREATE PROCEDURE GetDeptEmployees(
IN p_dept_name VARCHAR(50)
)
BEGIN
SELECT
e.emp_name,
e.salary,
d.dept_name,
d.location

FROM employees e
INNER
JOIN departments d
ON e.dept_id = d.dept_id

WHERE d.dept_name = p_dept_name

ORDER BY e.salary DESC;

END //

DELIMITER ;

-- Call the procedure
CALL GetDeptEmployees('IT');

CALL GetDeptEmployees('Sales');

Example 2: OUT parameter — Department ki average salary nikalo.

DELIMITER //
CREATE PROCEDURE GetDeptAvgSalary(
IN p_dept_id INT,
OUT p_avg_sal DECIMAL(10,2),
OUT p_emp_count INT
)
BEGIN
SELECT
ROUND(AVG(salary), 2),
COUNT(*)
INTO p_avg_sal, p_emp_count

FROM employees

WHERE dept_id = p_dept_id;

END //

DELIMITER ;

-- Call with OUT parameters
CALL GetDeptAvgSalary(1, @avg_sal, @emp_count);

-- Retrieve OUT values
SELECT @avg_sal AS avg_salary, @emp_count AS total_employees;

Example 3: Procedure with IF logic — Salary hike based on performance.

DELIMITER //
CREATE PROCEDURE ApplySalaryHike(
IN p_emp_id INT,
IN p_rating VARCHAR(20)
)
BEGIN
DECLARE v_hike_pct DECIMAL(5,2);

text

-- Rating ke hisaab se hike % decide karo
IF p_rating = 'Excellent'
THEN

SET v_hike_pct = 20.00;

ELSEIF p_rating = 'Good'
THEN

SET v_hike_pct = 10.00;

ELSEIF p_rating = 'Average'
THEN

SET v_hike_pct = 5.00;

ELSE

SET v_hike_pct = 0.00;

END IF;

-- Salary update karo

UPDATE employees

SET salary = salary * (1 + v_hike_pct / 100)

WHERE emp_id = p_emp_id;

-- Confirmation dikhao
SELECT
emp_id,
emp_name,
salary AS new_salary,
v_hike_pct AS hike_percent

FROM employees

WHERE emp_id = p_emp_id;

END //

DELIMITER ;

-- Apply 20% hike to emp 101
CALL ApplySalaryHike(101, 'Excellent');

-- Show and drop procedure
SHOW PROCEDURE STATUS
WHERE Db = 'company_db';

DROP PROCEDURE IF EXISTS GetDeptEmployees;

📊 Expected Output (Example 2 — OUT params):

-- CALL GetDeptAvgSalary(1, @avg_sal, @emp_count); -- SELECT @avg_sal, @emp_count;

avg_salary	total_employees
90666.67	3

(dept_id=1 is IT — 3 employees, avg 90666.67)

text

⚠️ Common Mistakes:

• Mistake: DELIMITER change bhool jaana → MySQL ; ke baad hi procedure end samjhega — incomplete procedure save hogi.
  Fix: Hamesha DELIMITER // se shuru karo aur DELIMITER ; se end karo.
• Mistake: OUT parameters ke liye @ (user variables) use karna bhool jaana → CALL proc(1, avg_sal) ❌
  Fix: CALL proc(1, @avg_sal) ✅ — @ se user variable banata hai.
• Mistake: DECLARE variable ko BEGIN ke baad immediately na likhna → DECLARE statements hamesha BEGIN ke turant baad aani chahiye, any logic se pehle.

💬 Interview Questions:

text

🔍 Definition: A User-Defined Function (UDF) in MySQL returns exactly one value and can be used directly in SQL statements (SELECT, WHERE, ORDER BY). Unlike Stored Procedures (called with CALL), functions are called like built-in functions. Functions must have a RETURN statement and cannot modify data (no INSERT/UPDATE/DELETE in most cases).

🎯 Samjho Simple Bhasha Mein: Function ek calculator ki tarah hai — kuch value do, ek calculated answer wapas lo. Jaise MySQL ka built-in UPPER('hello') function 'HELLO' return karta hai — waise hi tum apna custom function bana sakte ho. Function SELECT mein use ho sakta hai: SELECT GetAnnualSalary(salary) FROM employees — har row ke liye function call hoga.

💡 Function vs Stored Procedure:
Function: RETURN karta hai → SELECT mein use hota hai → Data modify nahi kar sakta
Stored Procedure: CALL karta hai → SELECT mein use nahi hota → Data modify kar sakta hai

Simple rule: Calculation chahiye → Function. Action karna hai → Procedure.

💻 Real-World Code Examples:

Example 1: Annual salary calculate karne ka function.

DELIMITER //
CREATE FUNCTION GetAnnualSalary(
p_monthly_salary DECIMAL(10,2)
)
RETURNS DECIMAL(12,2)
DETERMINISTIC
BEGIN
RETURN p_monthly_salary * 12;

END //

DELIMITER ;

-- Use function in
SELECT (just like built-in functions!)
SELECT
emp_name,
salary AS monthly,
GetAnnualSalary(salary) AS annual

FROM employees

ORDER BY annual DESC;

Example 2: Salary grade classify karne ka function.

DELIMITER //
CREATE FUNCTION GetSalaryGrade(
p_salary DECIMAL(10,2)
)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
DECLARE v_grade VARCHAR(20);

text

IF p_salary >= 90000
THEN

SET v_grade = 'A - Executive';

ELSEIF p_salary >= 70000
THEN

SET v_grade = 'B - Senior';

ELSEIF p_salary >= 50000
THEN

SET v_grade = 'C - Mid Level';

ELSE

SET v_grade = 'D - Junior';

END IF;

RETURN v_grade;

END //

DELIMITER ;

-- Use in
SELECT and
WHERE
SELECT
emp_name,
salary,
GetSalaryGrade(salary) AS grade

FROM employees

WHERE salary IS NOT NULL

ORDER BY salary DESC;

-- Drop function
DROP FUNCTION IF EXISTS GetAnnualSalary;

📊 Expected Output (Example 2 — Salary Grade):

emp_name	salary	grade
Anjali Mehta	95000.00	A - Executive
Sneha Patel	92000.00	A - Executive
Rahul Sharma	85000.00	B - Senior
Neha Gupta	71000.00	B - Senior
Deepak Rao	68000.00	C - Mid Level
Priya Singh	62000.00	C - Mid Level
Rohit Tiwari	55000.00	C - Mid Level
Ravi Verma	53000.00	C - Mid Level
Suresh Nair	51000.00	C - Mid Level
Amit Kumar	48000.00	D - Junior
Kavita Joshi	45000.00	D - Junior

text

⚠️ Common Mistakes:

• Mistake: DETERMINISTIC keyword bhool jaana → MySQL might throw error "you might want to use the less safe log_bin_trust_function_creators".
  Fix: Pure calculation functions mein DETERMINISTIC likhna — same input always same output.
• Mistake: Function mein INSERT/UPDATE/DELETE karna → Error aayega in strict mode.
  Fix: Data modification ke liye Stored Procedure use karo.
• Mistake: Function ko CALL se call karna → Functions SELECT mein use hote hain, CALL se nahi.
  Fix: SELECT GetSalaryGrade(50000) ✅ — CALL GetSalaryGrade(50000) ❌

💬 Interview Questions:

text

🔍 Definition: A Trigger is a stored program that automatically executes (fires) in response to a specific event on a table — INSERT, UPDATE, or DELETE. Triggers can fire BEFORE or AFTER the event. They use NEW (new row data) and OLD (previous row data) references.

🎯 Samjho Simple Bhasha Mein: Trigger ek automatic alarm ki tarah hai. Jaise bank account mein paise aate hain toh automatically SMS aata hai — tumne kuch nahi kiya, automatically hua. Database mein bhi — jab koi row insert/update/delete ho toh automatically koi action ho jaata hai. Audit log banana, salary validation, backup — sab kuch automatically.

💡 Trigger ke 6 Types (Event × Timing):
BEFORE INSERT | AFTER INSERT
BEFORE UPDATE | AFTER UPDATE
BEFORE DELETE | AFTER DELETE

NEW: INSERT ya UPDATE mein nayi value → NEW.salary
OLD: UPDATE ya DELETE mein purani value → OLD.salary

💻 Real-World Code Examples:

Setup: Pehle audit_log table banao.

-- Audit log table
CREATE TABLE salary_audit_log (
log_id INT PRIMARY KEY AUTO_INCREMENT,
emp_id INT,
old_salary DECIMAL(10,2),
new_salary DECIMAL(10,2),
changed_by VARCHAR(50),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
action_type VARCHAR(10)
);

Example 1: AFTER UPDATE Trigger — Salary change automatically log karo.

DELIMITER //
CREATE TRIGGER trg_salary_update
AFTER
UPDATE
ON employees
FOR EACH ROW
BEGIN
-- Only log if salary actually changed
IF OLD.salary != NEW.salary
THEN

INSERT INTO salary_audit_log
(emp_id, old_salary, new_salary, changed_by, action_type)

VALUES
(NEW.emp_id, OLD.salary, NEW.salary,
USER(), 'UPDATE');

END IF;

END //

DELIMITER ;

-- Test the trigger

UPDATE employees
SET salary = 90000
WHERE emp_id = 101;

-- Check audit log
SELECT *
FROM salary_audit_log;

Example 2: BEFORE INSERT Trigger — Salary validation (negative salary prevent karo).

DELIMITER //
CREATE TRIGGER trg_validate_salary
BEFORE
INSERT
ON employees
FOR EACH ROW
BEGIN
-- Negative salary prevent karo
IF NEW.salary 0
THEN
SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = 'ERROR: Salary cannot be negative!';

END IF;

text

-- Minimum salary enforce karo
IF NEW.salary < 20000
THEN

SET NEW.salary = 20000;
-- Minimum salary 20000 set karo

END IF;

END //

DELIMITER ;

-- Test: Negative salary try karo

INSERT INTO employees (emp_id, emp_name, dept_id, salary)

VALUES (999, 'Test User', 1, -5000);

-- ERROR 1644: ERROR: Salary cannot be negative!

Example 3: AFTER DELETE Trigger — Deleted employee ko archive karo.

-- Archive table banao
CREATE TABLE employees_archive LIKE employees;

ALTER TABLE employees_archive ADD COLUMN deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

DELIMITER //

CREATE TRIGGER trg_archive_employee
AFTER
DELETE
ON employees
FOR EACH ROW
BEGIN
-- Deleted employee archive mein save karo

INSERT INTO employees_archive
(emp_id, emp_name, dept_id, manager_id, salary, hire_date)

VALUES
(OLD.emp_id, OLD.emp_name, OLD.dept_id,
OLD.manager_id, OLD.salary, OLD.hire_date);

END //

DELIMITER ;

-- Trigger management
SHOW TRIGGERS;

DROP TRIGGER IF EXISTS trg_salary_update;

📊 Expected Output (Audit Log after UPDATE):

log_id	emp_id	old_salary	new_salary	changed_by	changed_at	action_type
1	101	85000.00	90000.00	root@localhost	2024-01-15 10:30:00	UPDATE

Trigger automatically logged the salary change!

text

⚠️ Common Mistakes:

• Mistake: BEFORE trigger mein OLD value ko change karna → OLD values read-only hain, sirf NEW values modify ho sakte hain BEFORE triggers mein.
  Fix: SET NEW.salary = 20000 ✅ — SET OLD.salary = ... ❌
• Mistake: Trigger mein same table par INSERT/UPDATE karna → Infinite loop create ho sakta hai!
  Fix: Trigger ke andar same table modify karna avoid karo.
• Mistake: Ek table par same event ke liye multiple triggers order assume karna → MySQL triggers per event per timing ordered hain — but avoid complexity.
  Fix: Ek trigger mein saari logic rakhne ki koshish karo.

💬 Interview Questions:

text

🔍 Definition: Window Functions perform calculations across a set of rows related to the current row — without collapsing rows like GROUP BY does. They use the OVER() clause to define the "window" of rows. Available since MySQL 8.0. Key functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER, AVG() OVER.

🎯 Samjho Simple Bhasha Mein: Window Functions GROUP BY se alag hain. GROUP BY rows collapse karke ek summary row banata hai. Window Functions har row apni jagah rakhti hain — bas saath mein ek extra calculated column add ho jaata hai. Socho "har employee ki salary ke saath uski department ki average salary bhi dikhao — aur rows reduce mat karo" — yeh window function ka kaam hai.

💡 Window Function Structure:
function_name() OVER (
PARTITION BY column -- GROUP jaisi but rows collapse nahi
ORDER BY column -- Window ke andar sorting
ROWS/RANGE frame -- Optional: row range define
)

🔹 ROW_NUMBER() — Unique Sequential Number

Definition: ROW_NUMBER() assigns a unique sequential integer to each row within a partition, starting from 1. No two rows get the same number even if they have equal values.

-- ROW_NUMBER: Unique rank, no ties
SELECT
emp_name,
dept_id,
salary,
ROW_NUMBER() OVER (
PARTITION BY dept_id
ORDER BY salary DESC
) AS row_num

FROM employees

WHERE salary IS NOT NULL;

🔹 RANK() vs DENSE_RANK() — Ranking with Ties

Definition: RANK() gives the same rank to tied values but skips the next rank (1,1,3). DENSE_RANK() gives same rank to ties but does not skip (1,1,2). This is critical for interview questions about Nth highest salary.

-- RANK vs DENSE_RANK side by side
SELECT
emp_name,
salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank

FROM employees

WHERE salary IS NOT NULL

ORDER BY salary DESC;

-- Nth highest salary using DENSE_RANK
-- (3rd highest salary wale employees)
SELECT emp_name, salary

FROM (
SELECT
emp_name,
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees
WHERE salary IS NOT NULL
) AS ranked

WHERE drnk = 3;

🔹 LAG() and LEAD() — Previous and Next Row Values

Definition: LAG() accesses data from a previous row in the same result set. LEAD() accesses data from the next row. Both are used for comparing values across rows — like month-over-month salary changes.

-- LAG: Previous row's salary, LEAD: Next row's salary
SELECT
emp_name,
salary,
hire_date,
LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_emp_salary,

LEAD(salary, 1, 0) OVER (ORDER BY hire_date) AS next_emp_salary,

salary - LAG(salary, 1, salary) OVER (ORDER BY hire_date) AS salary_diff

FROM employees

WHERE salary IS NOT NULL

ORDER BY hire_date;

🔹 SUM() OVER / AVG() OVER — Running Totals

-- Running total salary (cumulative)
SELECT
emp_name,
dept_id,
salary,
-- Running total (cumulative sum in hire_date order)
SUM(salary) OVER (
ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total,
-- Department average alongside each employee
ROUND(AVG(salary) OVER (PARTITION BY dept_id), 2) AS dept_avg

FROM employees

WHERE salary IS NOT NULL

ORDER BY hire_date;

📊 Expected Output (RANK vs DENSE_RANK):

emp_name	salary	salary_rank	dense_rank
Anjali Mehta	95000.00	1	1
Sneha Patel	92000.00	2	2
Rahul Sharma	85000.00	3	3
Neha Gupta	71000.00	4	4
Deepak Rao	68000.00	5	5
Priya Singh	62000.00	6	6
Rohit Tiwari	55000.00	7	7
Ravi Verma	53000.00	8	8
Suresh Nair	51000.00	9	9
Amit Kumar	48000.00	10	10
Kavita Joshi	45000.00	11	11

-- If 2 employees had SAME salary (e.g., 92000):
-- RANK(): 1, 2, 2, 4 (skips 3)
-- DENSE_RANK(): 1, 2, 2, 3 (no skip)

📊 RANK vs DENSE_RANK vs ROW_NUMBER:

Salary	ROW_NUMBER()	RANK()	DENSE_RANK()
95000	1	1	1
92000	2	2	2
92000 (tie)	3 (unique!)	2 (same, skip 3)	2 (same, no skip)
85000	4	4 (skipped 3!)	3 (continuous)

⚠️ Common Mistakes:

• Mistake: MySQL 5.x par Window Functions use karna → Window Functions MySQL 8.0+ mein available hain.
  Fix: MySQL version check karo: SELECT VERSION();
• Mistake: Window function ko WHERE clause mein use karna → Error aayega — window functions WHERE mein nahi chalta.
  Fix: Derived table ya CTE use karo, phir outer WHERE mein filter karo.
• Mistake: RANK aur DENSE_RANK ka farak bhool jaana → Interview mein yeh zyaadatar poochha jaata hai.
  Fix: RANK = gaps with ties, DENSE_RANK = no gaps with ties.

💬 Interview Questions:

Feature	Purpose	Key Syntax	Called With
View	Virtual table, abstraction	CREATE VIEW v AS SELECT...	SELECT
Index	Query performance	CREATE INDEX idx ON t(col)	Auto-used
Stored Procedure	Reusable SQL logic	CREATE PROCEDURE p() BEGIN...END	CALL p()
Function	Returns single value	CREATE FUNCTION f() RETURNS...	SELECT f()
Trigger	Auto-action on DML events	CREATE TRIGGER t AFTER INSERT...	Automatic
Window Functions	Row-level analytics	RANK() OVER (PARTITION BY...)	SELECT (MySQL 8.0+)

🎯 Top Interview Questions — Rapid Fire

Q1: View data store karta hai? → No — virtual table, fresh data always from base tables

Q2: Index fast karta hai INSERT bhi? → No — SELECT fast, INSERT/UPDATE slightly slow (index maintain karna padta hai)

Q3: Function vs Procedure main diff? → Function = RETURN value, SELECT mein use. Procedure = CALL, data modify kar sakta hai

Q4: RANK vs DENSE_RANK? → RANK gaps chhodta hai ties pe (1,2,2,4). DENSE_RANK nahi chhodta (1,2,2,3)

Q5: Top N per group kaise nikalen? → ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) then WHERE rn <= N

Q6: Trigger mein NEW aur OLD kya hai? → NEW = new/updated values. OLD = previous values before change

Next: Data Insights MySQL Masterclass — Part 7

Part 7 mein hum cover karenge: Database Design — ER Diagrams, Normalization (1NF, 2NF, 3NF, BCNF), Denormalization aur Real-World Schema Design — production-grade database architecture Data Insights par.

Happy Coding & Keep Learning SQL! 🚀