SQL MASTER NOTES

Beginner to Advanced — Interview Ready

MySQL · PostgreSQL · SQL Server
 CHAPTER 1: Database & SQL Basics

1.1 What is a Database?

A database is an organized collection of data that allows data to be stored, accessed, and manipulated
efficiently.

Term Definition Example

Database Organized collection of data Amazon's product catalog
DBMS Software to manage the database MySQL, PostgreSQL, Oracle
RDBMS Relational DBMS - stores data in MySQL, SQL Server
tables
SQL Structured Query Language SELECT * FROM users;
Table Data arranged in rows and columns Employees table
Row (Record) One entry in a table Ramesh, 28, Mumbai
Column (Field) One attribute in a table emp_name, salary

🔥 Interview Q: What is the difference between DBMS and RDBMS?

✅ Ans: DBMS stores data as files; RDBMS stores data in tables with relationships. RDBMS follows
ACID properties. Examples: DBMS = file system; RDBMS = MySQL, Oracle.
🔥 Interview Q: What is SQL? Is SQL a programming language?
✅ Ans: SQL (Structured Query Language) is a standard language for managing relational databases. It
is a declarative language — you tell WHAT you want, not HOW. It is not a general-purpose
programming language.

1.2 SQL Sub-Languages (DDL, DML, DQL, DCL, TCL)

Type Full Name Commands What it does

DDL Data Definition CREATE, ALTER, DROP, Defines/modifies table
Language TRUNCATE structure
DML Data Manipulation INSERT, UPDATE, DELETE Manipulates data in tables
Language
DQL Data Query Language SELECT Retrieves data
DCL Data Control GRANT, REVOKE Controls access/permissions
Language
TCL Transaction Control COMMIT, ROLLBACK, Manages transactions
Language SAVEPOINT

🔥 Interview Q: What is the difference between DROP, DELETE and TRUNCATE?

 ✅ Ans: DELETE removes specific rows (DML, can rollback, WHERE clause allowed). TRUNCATE
removes all rows (DDL, faster, cannot rollback, resets AUTO_INCREMENT). DROP removes the entire
table including structure (DDL, cannot rollback).

CHAPTER 2: Creating Databases & Tables

2.1 Create & Use Database

CREATE DATABASE CompanyDB;
USE CompanyDB;
SHOW DATABASES;
DROP DATABASE CompanyDB;

2.2 MySQL Data Types

Numeric Types
Datatype Range / Size Use For
TINYINT -128 to 127 (1 Byte) Age, boolean-like flags
SMALLINT -32,768 to 32,767 (2 Bytes) Small counters
INT -2.1B to 2.1B (4 Bytes) IDs, general numbers
BIGINT Very large integers (8 Phone numbers, large IDs
Bytes)
DECIMAL(p,s) Exact numeric Salary, price (e.g. DECIMAL(10,2))
FLOAT / DOUBLE Approximate decimal Scientific calculations

String / Text Types

Datatype Size Use For
CHAR(n) Fixed length 0-255 Country code, gender
VARCHAR(n) Variable length 0-65535 Names, email, address
TEXT Up to 65,535 chars Long descriptions, notes

Date & Time Types

Datatype Format Example
DATE YYYY-MM-DD 2025-06-15
TIME HH:MM:SS 14:30:00
DATETIME YYYY-MM-DD HH:MM:SS 2025-06-15 14:30:00
TIMESTAMP UTC format (auto-updates) 2025-06-15 14:30:00
BOOLEAN Stored as TINYINT(1) 0 = FALSE, 1 = TRUE

🔥 Interview Q: What is the difference between CHAR and VARCHAR?

✅ Ans: CHAR is fixed-length — always uses n bytes. VARCHAR is variable-length — uses only as
many bytes as the actual string + 1-2 bytes overhead. Use CHAR for fixed-size data (e.g., country code
'IN'), VARCHAR for variable data (e.g., names).

2.3 MySQL Constraints

Constraint Description Example
NOT NULL Column cannot have NULL value emp_name VARCHAR(50) NOT NULL
UNIQUE All values must be unique email VARCHAR(100) UNIQUE
DEFAULT Assigns default value if none given status VARCHAR(10) DEFAULT 'active'
CHECK Validates data with a condition age INT CHECK (age >= 18)
PRIMARY KEY Unique + NOT NULL — uniquely user_id INT PRIMARY KEY
identifies row
AUTO_INCREMEN Auto increases numeric value user_id INT AUTO_INCREMENT
T
FOREIGN KEY Links to PRIMARY KEY of another FOREIGN KEY (dept_id) REFERENCES
table dept(dept_id)

2.4 CREATE TABLE Example

CREATE TABLE Users (
user_id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
age TINYINT CHECK (age >= 18),
status VARCHAR(10) DEFAULT 'active',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

🔥 Interview Q: What is the difference between PRIMARY KEY and UNIQUE KEY?
✅ Ans: PRIMARY KEY = UNIQUE + NOT NULL. Only one PRIMARY KEY per table (but can be
composite). Multiple UNIQUE constraints allowed. Primary key creates a clustered index; unique key
creates a non-clustered index.
🔥 Interview Q: What is a FOREIGN KEY? Why is it used?
✅ Ans: A Foreign Key is a column in Table B that references the PRIMARY KEY of Table A. It enforces
referential integrity — ensures that a value in Table B must exist in Table A. Example:
emp_table.dept_id references dept_table.dept_id.
 2.5 ALTER TABLE (Modify Structure)
Add Column
ALTER TABLE users ADD userAddress VARCHAR(100);

Modify Column Type

ALTER TABLE users MODIFY useremail VARCHAR(150) NOT NULL;

Rename Column
ALTER TABLE users CHANGE COLUMN userAddress address VARCHAR(50);

Drop Column
ALTER TABLE users DROP COLUMN address;

Rename Table
ALTER TABLE users RENAME TO userlist;

💡 ALTER TABLE changes structure permanently. Always take backup before altering production tables!

CHAPTER 3: CRUD Operations

3.1 INSERT — Add Records

Single Row Insert
INSERT INTO Users (username, email, password, age)
VALUES ('Subodh', 'subodh@[Link]', 'pass123', 26);

Multiple Rows Insert

INSERT INTO Users (username, email, password, age)
VALUES
('Amit', 'amit@[Link]', 'amit123', 22),
('Neha', 'neha@[Link]', 'neha123', 28),
('Ravi', 'ravi@[Link]', 'ravi123', 30);

3.2 SELECT — Read Records

SELECT * FROM Users; -- All columns
SELECT username, email FROM Users; -- Specific columns
SELECT DISTINCT status FROM Users; -- Unique values only
SELECT username AS Name FROM Users; -- Column alias
 3.3 UPDATE — Modify Records
UPDATE Users
SET status = 'inactive'
WHERE user_id = 1;

💡 ALWAYS use WHERE with UPDATE. Without WHERE, ALL rows get updated!

3.4 DELETE — Remove Records

DELETE FROM Users WHERE user_id = 2;

💡 Without WHERE clause, DELETE removes all rows (like TRUNCATE but slower and rollback-able).

🔥 Interview Q: What happens if you run DELETE without WHERE clause?

✅ Ans: All rows from the table are deleted. Unlike TRUNCATE, DELETE without WHERE is DML so it
can be rolled back if inside a transaction. But in auto-commit mode, data is permanently lost.

CHAPTER 4: WHERE Clause & Operators

4.1 WHERE Clause

The WHERE clause filters rows — only rows matching the condition are returned.
SELECT * FROM employees WHERE dept_id = 2;
SELECT * FROM employees WHERE salary > 50000;

4.2 Comparison Operators

Operator Meaning Example
= Equal to WHERE city = 'Delhi'
<> or != Not equal to WHERE city <> 'Mumbai'
> Greater than WHERE salary > 50000
< Less than WHERE salary < 30000
>= Greater than or equal WHERE age >= 18
<= Less than or equal WHERE age <= 60
BETWEEN...A Range (inclusive) WHERE salary BETWEEN 30000 AND 70000
ND
IN Matches any value in list WHERE city IN ('Delhi', 'Mumbai')
NOT IN Excludes values in list WHERE city NOT IN ('Pune', 'Nagpur')
LIKE Pattern matching WHERE name LIKE 'A%'
NOT LIKE Does not match pattern WHERE name NOT LIKE 'Z%'
IS NULL Check NULL value WHERE manager_id IS NULL
IS NOT NULL Check non-NULL value WHERE email IS NOT NULL

4.3 LIKE Pattern Matching

Pattern Meaning Matches
'A%' Starts with A Alice, Amit, Ankit
'%a' Ends with a Neha, Priya, Kiran
'%am%' Contains 'am' Ramesh, Shamim
'_a%' Second char is 'a' Ravi, Rajesh
'A__%' Starts with A, min 3 chars Ali, Amy, Alex

4.4 AND, OR, NOT

-- AND: Both conditions must be true
SELECT * FROM dept WHERE city = 'Delhi' AND budget > 200000;

-- OR: At least one condition must be true

SELECT * FROM dept WHERE city = 'Delhi' OR city = 'Mumbai';

-- NOT: Reverses the condition

SELECT * FROM emp WHERE NOT dept_id = 3;

🔥 Interview Q: What is the order of precedence: AND, OR, NOT?

✅ Ans: NOT > AND > OR. So NOT is evaluated first, then AND, then OR. Always use parentheses to
make logic explicit: WHERE (city='Delhi' OR city='Mumbai') AND salary > 50000.

4.5 ORDER BY, LIMIT, OFFSET

ORDER BY
SELECT * FROM employees ORDER BY salary ASC; -- Low to High
SELECT * FROM employees ORDER BY salary DESC; -- High to Low
SELECT * FROM employees ORDER BY dept_id ASC, salary DESC; -- Multiple columns

LIMIT
SELECT * FROM employees LIMIT 5; -- First 5 rows
SELECT * FROM employees LIMIT 5, 10; -- Skip 5, get next 10 (OFFSET)
-- Top 3 highest salary:
SELECT * FROM employees ORDER BY salary DESC LIMIT 3;
 🔥 Interview Q: How to get the 2nd highest salary?
✅ Ans: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM
employees); OR: SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

CHAPTER 5: SQL Functions

5.1 Aggregate Functions

Aggregate functions perform calculations on a SET of rows and return a single value.
Function Definition Example Output
COUNT(*) Count total rows SELECT COUNT(*) FROM emp; 15
COUNT(col) Count non-NULL SELECT COUNT(email) FROM 14 (if 1 null)
values emp;
SUM(col) Sum of values SELECT SUM(salary) FROM emp; 4,50,000
AVG(col) Average of values SELECT AVG(salary) FROM emp; 30,000
MAX(col) Maximum value SELECT MAX(salary) FROM emp; 75,000
MIN(col) Minimum value SELECT MIN(salary) FROM emp; 15,000

💡 COUNT(*) counts all rows including NULLs. COUNT(column_name) skips NULLs.

5.2 String Functions

Function Definition Example Output
UPPER() Converts to uppercase UPPER('hello') HELLO
LOWER() Converts to lowercase LOWER('HELLO') hello
LENGTH() Returns string length LENGTH('Subodh') 6
CONCAT() Joins strings CONCAT('Mr. ', name) Mr. Subodh
SUBSTRING() Extracts part of string SUBSTRING('Hello', 1, 3) Hel
TRIM() Removes spaces from TRIM(' hi ') hi
both ends
LTRIM() Removes leading LTRIM(' hi') hi
spaces
RTRIM() Removes trailing RTRIM('hi ') hi
spaces
REPLACE() Replaces text REPLACE('aXb', 'X', '-') a-b
INSTR() Position of substring INSTR('Hello', 'e') 2
LPAD() Left pads string LPAD('5', 3, '0') 005
 5.3 Numeric Functions
Function Definition Example Output
ABS() Absolute value ABS(-25) 25
ROUND(x,y) Rounds to y ROUND(3.456, 2) 3.46
decimals
FLOOR() Rounds down FLOOR(9.9) 9
CEIL() / CEILING() Rounds up CEIL(9.1) 10
MOD(x,y) Remainder after MOD(17, 5) 2
division
POWER(x,y) x to the power y POWER(2, 3) 8
SQRT() Square root SQRT(16) 4

5.4 Date Functions

Function Definition Example Output
NOW() Current date and SELECT NOW(); 2025-06-15
time 14:30:00
CURDATE() Current date only SELECT CURDATE(); 2025-06-15
CURTIME() Current time only SELECT CURTIME(); 14:30:00
YEAR(date) Extract year YEAR('2025-06-15') 2025
MONTH(date) Extract month MONTH('2025-06-15') 6
DAY(date) Extract day DAY('2025-06-15') 15
DATEDIFF(d1,d2) Days between two DATEDIFF('2025-12- 364
dates 31','2025-01-01')
DATE_ADD(d,INT n Add days to date DATE_ADD('2025-01-01', 2025-01-31
DAY) INTERVAL 30 DAY)
DATE_FORMAT(d, Format date as DATE_FORMAT(NOW(),'%d- 15-06-2025
fmt) string %m-%Y')

CHAPTER 6: GROUP BY & HAVING

6.1 GROUP BY
GROUP BY groups rows with the same values so aggregate functions can be applied per group.
-- Total salary per department
SELECT dept_id, SUM(salary) AS TotalSalary
FROM employees
GROUP BY dept_id;

-- Count employees per city

 SELECT city, COUNT(*) AS EmpCount
FROM employees
GROUP BY city;

💡 Every column in SELECT must either be in GROUP BY OR inside an aggregate function.

6.2 HAVING
HAVING filters GROUPS (works after GROUP BY). WHERE filters individual ROWS (before GROUP
BY).
-- Locations with more than 2 departments
SELECT location, COUNT(*) AS DeptCount
FROM department
GROUP BY location
HAVING COUNT(*) > 2;

Feature WHERE HAVING

Filters Individual rows Groups (after GROUP BY)
Used with SELECT, UPDATE, DELETE Only with GROUP BY
Aggregate functions Cannot use Can use (COUNT, SUM, etc.)
Execution order Runs before GROUP BY Runs after GROUP BY

🔥 Interview Q: What is the difference between WHERE and HAVING?

✅ Ans: WHERE filters rows BEFORE grouping. HAVING filters groups AFTER GROUP BY. WHERE
cannot use aggregate functions; HAVING can. Example: WHERE salary > 50000 (row level) vs
HAVING AVG(salary) > 50000 (group level).

6.3 SQL Query Execution Order

This is critical for interviews! SQL executes in this order (NOT the order you write it):

Order Clause What it does

1 FROM Identifies the table(s)
2 JOIN Combines tables
3 WHERE Filters individual rows
4 GROUP BY Groups filtered rows
5 HAVING Filters groups
6 SELECT Selects columns / applies functions
7 DISTINCT Removes duplicates
8 ORDER BY Sorts result
9 LIMIT / OFFSET Limits rows returned
 🔥 Interview Q: Why can't we use aliases in WHERE clause?
✅ Ans: Because WHERE executes BEFORE SELECT, so the alias hasn't been defined yet. You can
use aliases in ORDER BY and HAVING because they execute after SELECT.

CHAPTER 7: SQL JOINs

JOIN combines data from two or more tables based on a common column (usually a Foreign Key).

7.1 Types of JOINs

JOIN Type Returns Use When
INNER JOIN Only matching rows from BOTH tables Most common — need matches in
both tables
LEFT JOIN All rows from LEFT table + matching Need all records from main table
from RIGHT (NULL if no match)
RIGHT JOIN All rows from RIGHT table + matching Need all records from reference table
from LEFT (NULL if no match)
FULL OUTER JOIN All rows from BOTH tables (NULL for Need complete data from both
non-matches) (MySQL: use UNION)
CROSS JOIN Cartesian product — every row × Generate combinations
every row
SELF JOIN Table joins with itself Hierarchy (employee-manager),
comparing rows

7.2 INNER JOIN

SELECT e.emp_name, [Link], d.dept_name, [Link]
FROM employees e
INNER JOIN department d
ON e.dept_id = d.dept_id;
💡 Returns only employees WHO HAVE a department. Employees without dept_id match → excluded.

7.3 LEFT JOIN

SELECT e.emp_name, [Link], d.dept_name
FROM employees e
LEFT JOIN department d
ON e.dept_id = d.dept_id;
💡 Returns ALL employees. If no department match → dept_name = NULL.

-- Find employees WITHOUT a department:

 SELECT e.emp_name FROM employees e
LEFT JOIN department d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

7.4 RIGHT JOIN

SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT JOIN department d
ON e.dept_id = d.dept_id;
💡 Returns ALL departments. If no employee in that dept → emp_name = NULL.

7.5 SELF JOIN

-- Find each employee and their manager:
SELECT e1.emp_name AS Employee, e2.emp_name AS Manager
FROM employees e1
LEFT JOIN employees e2
ON e1.manager_id = e2.emp_id;

7.6 UNION vs UNION ALL

Feature UNION UNION ALL
Duplicates Removes duplicate rows Keeps all duplicates
Speed Slower (sorts to remove dups) Faster
Use when Need unique combined results Need all results including dups

-- Employees in Sales OR HR:

SELECT emp_name FROM employees WHERE dept_id = 1
UNION
SELECT emp_name FROM employees WHERE dept_id = 2;

🔥 Interview Q: What is the difference between JOIN and UNION?

✅ Ans: JOIN combines columns (horizontal) from multiple tables based on a condition. UNION
combines rows (vertical) from multiple SELECT queries. JOIN needs a common column; UNION needs
the same number of columns with compatible data types.
🔥 Interview Q: Difference between INNER JOIN and LEFT JOIN?
✅ Ans: INNER JOIN returns only matching rows from both tables. LEFT JOIN returns ALL rows from the
left table + matched rows from right (NULL for non-matches). Use LEFT JOIN when you don't want to
lose records from the primary table.
 CHAPTER 8: Subqueries
A subquery (inner query) is a query nested inside another query. The inner query executes first.

8.1 Subquery in WHERE

-- Find employees earning above average salary:
SELECT emp_name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Find employees in 'Sales' department (without JOIN):

SELECT emp_name FROM employees
WHERE dept_id = (SELECT dept_id FROM department WHERE dept_name = 'Sales');

8.2 Subquery in FROM (Derived Table)

SELECT dept_id, avg_sal FROM
(SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) AS
dept_avg
WHERE avg_sal > 50000;

8.3 Subquery in SELECT (Correlated)

-- Show each employee and max salary in their department:
SELECT emp_name,
(SELECT MAX(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id) AS
MaxSalary
FROM employees e1;

8.4 EXISTS / NOT EXISTS

-- Departments that HAVE at least one employee:
SELECT dept_name FROM department d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);

🔥 Interview Q: What is a correlated subquery?

✅ Ans: A correlated subquery references a column from the outer query. It executes ONCE FOR EACH
ROW of the outer query (unlike a normal subquery that runs once). It's slower but useful for row-by-row
comparisons.
🔥 Interview Q: Second highest salary — multiple ways?
✅ Ans: 1) SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp); 2)
SELECT salary FROM emp ORDER BY salary DESC LIMIT 1 OFFSET 1; 3) Using DENSE_RANK():
SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM emp) t WHERE rnk = 2;
 CHAPTER 9: Views, Indexes & CASE

9.1 Views
A view is a virtual table created from a query. It stores the query definition, NOT the data.
-- Create a view:
CREATE VIEW HighSalaryEmp AS
SELECT emp_id, emp_name, salary
FROM employees
WHERE salary > 50000;

-- Use the view:

SELECT * FROM HighSalaryEmp;

-- Drop a view:
DROP VIEW HighSalaryEmp;

Feature View Table

Stores data? No — stores only query Yes — stores actual data
Updatable? Sometimes (simple views) Always
Performance Re-executes query on use Faster (data stored)
Use case Security, simplify complex queries Primary data storage

🔥 Interview Q: Why use Views?

✅ Ans: 1) Security — hide sensitive columns from users. 2) Simplicity — hide complex JOIN logic. 3)
Reusability — create once, use many times. 4) Abstraction — changes in underlying table don't affect
users of the view.

9.2 Indexes
An index is a data structure that speeds up data retrieval. Like a book index — you don't scan every
page.
Index Type Description When to Use
Clustered Index Physically sorts table data (1 per Primary Key (auto-created)
table)
Non-Clustered Index Separate structure pointing to data Frequently searched columns
(many per table)
Unique Index Ensures all values are unique Email, username columns
Composite Index Index on multiple columns Queries filtering by multiple columns
 Full-Text Index For text search Search in TEXT/VARCHAR
columns

-- Create index:
CREATE INDEX idx_emp_name ON employees(emp_name);
-- Drop index:
DROP INDEX idx_emp_name ON employees;

🔥 Interview Q: What is the downside of too many indexes?

✅ Ans: Indexes slow down INSERT, UPDATE, DELETE because the index must also be updated. They
also consume storage space. Rule of thumb: Index columns used in WHERE, JOIN ON, and ORDER
BY clauses. Don't index low-cardinality columns (like gender).

9.3 CASE Statement

CASE is like IF-THEN-ELSE inside SQL. Used for conditional logic in SELECT.
SELECT emp_name, salary,
CASE
WHEN salary > 100000 THEN 'High'
WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium'
ELSE 'Low'
END AS Salary_Category
FROM employees;

-- CASE in ORDER BY:

SELECT * FROM employees
ORDER BY
CASE WHEN dept_id = 1 THEN 0 ELSE 1 END, salary DESC;

CHAPTER 10: Window Functions

Window functions perform calculations across a SET of rows related to the current row — WITHOUT
collapsing rows like GROUP BY does.

-- Syntax:
function_name() OVER (PARTITION BY col ORDER BY col ROWS/RANGE ...)

10.1 Ranking Functions

Function Assigns Handles Ties?
ROW_NUMBER() Unique sequential rank (1,2,3,4) No — ties get different ranks
RANK() Same rank for ties, then gaps (1,1,3) Yes — skips numbers after ties
DENSE_RANK() Same rank for ties, no gaps (1,1,2) Yes — no gaps after ties
NTILE(n) Divides rows into n equal buckets N/A

-- Rank employees by salary within each department:

SELECT emp_name, dept_id, salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense_rank,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS row_num
FROM employees;

🔥 Interview Q: Difference between RANK(), DENSE_RANK(), ROW_NUMBER()?

✅ Ans: For salaries 100, 100, 80: ROW_NUMBER = 1,2,3 (always unique). RANK = 1,1,3 (same rank,
gap after tie). DENSE_RANK = 1,1,2 (same rank, no gap). Use DENSE_RANK for 'top N' queries.

10.2 Aggregate Window Functions

-- Running total of salary:
SELECT emp_name, salary,
SUM(salary) OVER (ORDER BY emp_id) AS running_total
FROM employees;

-- Department-wise avg alongside each employee row:

SELECT emp_name, salary,
AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees;

10.3 LAG and LEAD

-- Compare current salary with previous row:
SELECT emp_name, salary,
LAG(salary, 1) OVER (ORDER BY salary) AS prev_salary,
LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary
FROM employees;

💡 LAG() gets the previous row's value. LEAD() gets the next row's value. Great for period-over-period
analysis!

CHAPTER 11: Stored Procedures, Functions & Triggers

 11.1 Stored Procedure
A stored procedure is a saved block of SQL statements that can be executed with parameters.
DELIMITER $$
CREATE PROCEDURE getEmployeesByDept(IN deptId INT)
BEGIN
SELECT emp_id, emp_name, salary
FROM employees
WHERE dept_id = deptId;
END $$
DELIMITER ;

-- Call it:
CALL getEmployeesByDept(2);

Feature Stored Procedure Function

Returns 0 or more values (OUT params) Exactly ONE value
Call syntax CALL proc_name() Used inside SELECT / WHERE
DML allowed? Yes (INSERT, UPDATE, DELETE) No DML (read-only usually)
Transaction? Can manage transactions Cannot manage transactions

11.2 Functions
DELIMITER $$
CREATE FUNCTION getAnnualSalary(monthly_salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
RETURN monthly_salary * 12;
END $$
DELIMITER ;

-- Use in SELECT:
SELECT emp_name, getAnnualSalary(salary) AS annual_salary FROM employees;

11.3 Triggers
A trigger automatically executes SQL code BEFORE or AFTER INSERT, UPDATE, or DELETE on a
table.

BEFORE INSERT Trigger

DELIMITER $$
CREATE TRIGGER before_insert_employees
 BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF [Link] < 0 OR [Link] IS NULL THEN
SET [Link] = 0;
END IF;
END $$
DELIMITER ;

AFTER DELETE Trigger (Audit Log)

DELIMITER $$
CREATE TRIGGER trg_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employees_audit (emp_id, action_type)
VALUES (OLD.emp_id, 'DELETED');
END $$
DELIMITER ;

💡 NEW refers to the new row (INSERT/UPDATE). OLD refers to the old row (DELETE/UPDATE).
🔥 Interview Q: What is a trigger? When would you use it?
✅ Ans: A trigger is auto-executed code attached to a table event. Uses: 1) Audit logging (track who
deleted what). 2) Data validation (prevent negative salary). 3) Auto-fill derived columns. Avoid using
triggers for business logic that should be in application code.

CHAPTER 12: Transactions & ACID Properties

12.1 What is a Transaction?

A transaction is a sequence of SQL operations treated as a single unit — either ALL succeed or ALL
fail.
START TRANSACTION;
UPDATE accounts SET balance = balance - 5000 WHERE acc_id = 1;
UPDATE accounts SET balance = balance + 5000 WHERE acc_id = 2;
COMMIT; -- Save changes permanently

-- If error occurs:
ROLLBACK; -- Undo all changes since START TRANSACTION
 12.2 ACID Properties
Property Meaning Example
Atomicity ALL or NOTHING — either all Bank transfer: both debit and credit
operations succeed or none do happen, or neither
Consistency Database moves from one valid state Account balance can never go negative
to another valid state
Isolation Concurrent transactions don't interfere Two users booking same seat
with each other simultaneously — one must wait
Durability Once committed, data is permanently After COMMIT, data survives power failure
saved even after crash

🔥 Interview Q: What are ACID properties? Explain with example.

✅ Ans: ACID = Atomicity (all or nothing — bank transfer), Consistency (data remains valid — balance ≥
0), Isolation (transactions don't see each other's uncommitted data), Durability (committed data survives
crashes). These properties ensure database reliability.

CHAPTER 13: Normalization

Normalization is the process of organizing a database to reduce data redundancy and improve data
integrity.

Normal Form Rule Eliminates

1NF (First) Each column has atomic (single) values. No Repeating columns (Phone1,
repeating groups. Phone2, Phone3)
2NF (Second) In 1NF + No partial dependency (non-key Partial dependency in composite
column depends on FULL PK) PKs
3NF (Third) In 2NF + No transitive dependency (non-key Transitive dependency
depends on another non-key)
BCNF Stronger version of 3NF — every Certain anomalies missed by
determinant is a candidate key 3NF

🔥 Interview Q: What is normalization? What are 1NF, 2NF, 3NF?

✅ Ans: Normalization organizes tables to reduce redundancy. 1NF: no repeating groups, atomic values.
2NF: 1NF + no partial dependency (non-key fully depends on entire PK). 3NF: 2NF + no transitive
dependency (A→B→C is bad; A should directly determine C). Most production databases are in 3NF.
🔥 Interview Q: What is denormalization?
✅ Ans: Denormalization is intentionally adding redundancy to improve query performance. Used in data
warehouses and reporting systems where read performance matters more than write efficiency. It's the
opposite of normalization.
 CHAPTER 14: Top SQL Interview Questions
These questions come up in 90% of Data Analyst interviews. Study these thoroughly.

14.1 Must-Know Interview Questions

🔥 Interview Q: Find duplicate records in a table.

✅ Ans: SELECT email, COUNT(*) AS cnt FROM users GROUP BY email HAVING COUNT(*) > 1;

🔥 Interview Q: Delete duplicate rows, keep only one.

✅ Ans: DELETE FROM users WHERE user_id NOT IN (SELECT MIN(user_id) FROM users GROUP
BY email);

🔥 Interview Q: Find employees who earn more than their manager.

✅ Ans: SELECT e.emp_name FROM employees e JOIN employees m ON e.manager_id = m.emp_id
WHERE [Link] > [Link];

🔥 Interview Q: Find departments with no employees.

✅ Ans: SELECT d.dept_name FROM department d LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;

🔥 Interview Q: Get the Nth highest salary.

✅ Ans: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS
rnk FROM employees) t WHERE rnk = N; -- Replace N with desired rank

🔥 Interview Q: Find employees whose salary is above department average.

✅ Ans: SELECT e.emp_name, [Link] FROM employees e WHERE [Link] > (SELECT AVG(salary)
FROM employees e2 WHERE e2.dept_id = e.dept_id);

🔥 Interview Q: Count employees by department, show dept with > 3 employees.

✅ Ans: SELECT d.dept_name, COUNT(e.emp_id) AS cnt FROM department d JOIN employees e ON
d.dept_id = e.dept_id GROUP BY d.dept_name HAVING COUNT(e.emp_id) > 3;

🔥 Interview Q: What is the difference between a clustered and non-clustered index?

✅ Ans: Clustered index physically reorders the table data (one per table, usually PK). Non-clustered
index is a separate structure with pointers to the data (can have many). Think of a dictionary (clustered)
vs. a book's index page (non-clustered).

🔥 Interview Q: What are NULL values? How to handle them?

✅ Ans: NULL means missing/unknown value — it's not zero or empty string. NULL ≠ NULL in
comparisons. Use IS NULL / IS NOT NULL to check. Use COALESCE(col, 0) to replace NULL with
default. NULL in arithmetic returns NULL.
 🔥 Interview Q: What is the difference between COALESCE and ISNULL?
✅ Ans: ISNULL(col, replacement) takes 2 args (SQL Server / MySQL IFNULL). COALESCE(col1, col2,
col3...) takes multiple args, returns first non-NULL. COALESCE is ANSI standard and works across all
databases.

🔥 Interview Q: How would you optimize a slow SQL query?

✅ Ans: 1) Check with EXPLAIN/EXPLAIN ANALYZE to see query plan. 2) Add indexes on WHERE,
JOIN, ORDER BY columns. 3) Avoid SELECT * — select only needed columns. 4) Avoid functions on
indexed columns in WHERE (salary + 0 = 50000 is bad). 5) Use JOINs instead of correlated subqueries.
6) Avoid DISTINCT when not needed. 7) Use LIMIT to reduce result set.

CHAPTER 15: Quick Reference Cheatsheet

15.1 SQL Query Template

SELECT column1, column2, AGG_FUNC(col) -- Step 6: What to show
FROM table1 -- Step 1: Which table
JOIN table2 ON [Link] = [Link] -- Step 2: Join other table
WHERE condition -- Step 3: Filter rows
GROUP BY column1 -- Step 4: Group rows
HAVING group_condition -- Step 5: Filter groups
ORDER BY column1 ASC/DESC -- Step 7: Sort results
LIMIT n OFFSET m; -- Step 8: Limit rows

15.2 Common Pattern Queries

Top N per Group (using Window Function)
SELECT * FROM (
SELECT emp_name, dept_id, salary,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees
) ranked WHERE rnk <= 3;

Year-over-Year Comparison
SELECT YEAR(order_date) AS yr,
SUM(amount) AS total,
LAG(SUM(amount)) OVER (ORDER BY YEAR(order_date)) AS prev_year
FROM orders
GROUP BY YEAR(order_date);

Pivot-like (using CASE)

 SELECT dept_id,
SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,
SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count
FROM employees
GROUP BY dept_id;

15.3 Key Differences Summary

Comparison Option A Option B
DELETE vs TRUNCATE DELETE: DML, WHERE TRUNCATE: DDL, faster, resets
vs DROP allowed, rollback possible AUTO_INC, no rollback | DROP:
removes entire table
WHERE vs HAVING WHERE: filters rows before HAVING: filters groups after GROUP
grouping BY
UNION vs JOIN UNION: combines rows JOIN: combines columns horizontally
vertically
RANK vs DENSE_RANK RANK: gaps after ties (1,1,3) DENSE_RANK: no gaps (1,1,2)
Procedure vs Function Procedure: CALL, no return Function: used in SELECT, must
value required return value
VIEW vs TABLE VIEW: virtual, no storage, TABLE: physical storage
always fresh
CHAR vs VARCHAR CHAR: fixed length, faster for VARCHAR: variable length, saves
fixed data space
INNER vs LEFT JOIN INNER JOIN: only matching LEFT JOIN: all left rows + matches
rows
 All the Best for Your Interview!
Practice daily. Revise before interview. You've got this.