SQL

Interview Mastery Guide

Top Questions & 200 Queries for Placements & Interviews

Covers: DDL · DML · DQL · Joins · Subqueries · Window Functions · Indexes · Stored Procedures ·
Transactions · Optimization

Complete Reference — 2025 Edition

SECTION 1: Top SQL Interview Questions (Conceptual)
These 50 conceptual questions cover the most commonly asked theory topics in SQL interviews and
campus placements.

1. What is SQL?
SQL (Structured Query Language) is a standard language for managing and manipulating relational
databases. It is used to create, read, update, and delete data in database tables.

2. What are the different SQL sub-languages?

DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE. DML (Data Manipulation
Language): INSERT, UPDATE, DELETE. DQL (Data Query Language): SELECT. DCL (Data Control
Language): GRANT, REVOKE. TCL (Transaction Control Language): COMMIT, ROLLBACK,
SAVEPOINT.

3. What is a Primary Key?

A Primary Key is a column (or combination of columns) that uniquely identifies each row in a table. It
cannot contain NULL values and must be unique across all rows.

4. What is a Foreign Key?

A Foreign Key is a column that creates a relationship between two tables by referencing the Primary Key
of another table. It enforces referential integrity.

5. What is the difference between DELETE, TRUNCATE, and DROP?

DELETE: Removes specific rows; can use WHERE clause; logged; supports rollback. TRUNCATE:
Removes ALL rows; faster; minimal logging; cannot rollback in most DBs. DROP: Removes the entire
table structure and data permanently.

6. What is a JOIN? Name its types.

A JOIN combines rows from two or more tables based on a related column. Types: INNER JOIN, LEFT
JOIN (LEFT OUTER), RIGHT JOIN (RIGHT OUTER), FULL OUTER JOIN, CROSS JOIN, SELF JOIN.

7. Difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows with matching values in both tables. LEFT JOIN returns all rows from the
left table and matched rows from the right table (NULLs for non-matches).

8. What is a subquery?
A subquery is a query nested inside another query. It can appear in SELECT, FROM, WHERE, or
HAVING clauses. Types: correlated and non-correlated subqueries.

9. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping (works on individual rows). HAVING filters groups after GROUP BY
(works on aggregated data). Aggregate functions cannot be used in WHERE.

10. What is an Index? Types of Indexes?

An index is a data structure that improves query speed by allowing faster data retrieval. Types:
Clustered Index (sorts table data), Non-Clustered Index (separate structure), Unique Index, Composite
Index, Full-Text Index.

11. What is the difference between CHAR and VARCHAR?

 CHAR is a fixed-length string (pads with spaces). VARCHAR is a variable-length string (stores only
actual characters). CHAR is faster for fixed-length data; VARCHAR saves space for variable data.

12. What is Normalization? Name the Normal Forms.

Normalization organizes data to reduce redundancy and improve integrity. Normal Forms: 1NF (atomic
values, no repeating groups), 2NF (no partial dependency), 3NF (no transitive dependency), BCNF,
4NF, 5NF.

13. What is Denormalization?

Denormalization is the intentional introduction of redundancy into a database to improve read
performance by reducing costly JOINs. Used in data warehouses and reporting systems.

14. What is a View?

A View is a virtual table based on the result of a SQL query. It does not store data physically (unless it's
a materialized view). Views simplify complex queries and enhance security.

15. What is a Stored Procedure?

A Stored Procedure is a precompiled set of SQL statements stored in the database that can be called
with a name. Benefits: code reuse, improved performance, security, reduced network traffic.

16. What is a Trigger?

A Trigger is a set of SQL statements that automatically execute in response to certain events (INSERT,
UPDATE, DELETE) on a table. Types: BEFORE, AFTER, INSTEAD OF.

17. What is a Transaction? ACID properties?

A Transaction is a unit of work that is either fully completed or fully rolled back. ACID: Atomicity (all or
nothing), Consistency (valid state transitions), Isolation (transactions don't interfere), Durability
(committed data persists).

18. What is COMMIT and ROLLBACK?

COMMIT permanently saves all changes made in the current transaction. ROLLBACK undoes all
changes made since the last COMMIT or SAVEPOINT.

19. What are Window Functions?

Window functions perform calculations across a set of rows related to the current row without collapsing
them into groups. Examples: ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), SUM()
OVER(), AVG() OVER().

20. Difference between RANK() and DENSE_RANK()?

RANK() assigns the same rank to ties but skips the next rank (1,1,3). DENSE_RANK() assigns the same
rank to ties but does NOT skip (1,1,2).

21. What is a CTE (Common Table Expression)?

A CTE is a temporary named result set defined with WITH clause that can be referenced within a
SELECT, INSERT, UPDATE, or DELETE statement. It improves readability and allows recursive
queries.

22. What is COALESCE()?

COALESCE() returns the first non-NULL value from a list of arguments. Example: COALESCE(col1,
col2, 'default') returns the first non-NULL column value.

23. What is NULLIF()?

 NULLIF(a, b) returns NULL if a equals b, otherwise returns a. Useful to avoid division by zero: value /
NULLIF(divisor, 0).

24. What is CASE WHEN?

CASE WHEN is SQL's conditional expression, similar to if-else. It evaluates conditions and returns a
value when the first condition is met.

25. What is the difference between UNION and UNION ALL?

UNION removes duplicate rows from the combined result. UNION ALL retains all rows including
duplicates. UNION ALL is faster as it skips duplicate elimination.

26. What is an INTERSECT?

INTERSECT returns only the rows that appear in both result sets of two SELECT statements (common
rows only).

27. What is EXCEPT (or MINUS)?

EXCEPT (SQL Server/PostgreSQL) or MINUS (Oracle) returns rows from the first SELECT that are not
in the second SELECT result set.

28. What is a Clustered vs Non-Clustered Index?

Clustered Index: sorts and stores data rows physically in the index order; only one per table. Non-
Clustered Index: stores a separate structure with pointers to data rows; multiple allowed per table.

29. What is a Composite Index?

A Composite Index (also called multi-column index) is an index on two or more columns. The order of
columns matters — queries must use the leading columns to benefit from the index.

30. What is Query Optimization?

Query optimization is the process of improving query performance through techniques like using
indexes, avoiding SELECT *, rewriting subqueries as JOINs, using proper JOIN types, and analyzing
execution plans.

31. What is EXPLAIN / EXECUTION PLAN?

EXPLAIN (MySQL/PostgreSQL) or EXPLAIN PLAN (Oracle) shows how the database engine executes
a query — which indexes are used, join methods, estimated costs — helping identify bottlenecks.

32. What is a Self Join?

A Self Join joins a table to itself using table aliases. Used to query hierarchical data or compare rows
within the same table (e.g., employee-manager relationships).

33. What is a Cross Join?

A Cross Join produces the Cartesian product of two tables — every row from the first table combined
with every row from the second. Result rows = rows_in_table1 × rows_in_table2.

34. What is GROUP BY?

GROUP BY groups rows sharing the same values in specified columns into summary rows. It is typically
used with aggregate functions (COUNT, SUM, AVG, MAX, MIN).

35. What is DISTINCT?

DISTINCT eliminates duplicate rows from the result set. SELECT DISTINCT col1, col2 returns unique
combinations of the specified columns.

36. What is the difference between IN and EXISTS?

 IN checks if a value matches any value in a list or subquery result. EXISTS checks if a subquery returns
any rows. EXISTS is generally faster for large datasets as it stops at the first match.

37. What is a Materialized View?

A Materialized View stores the result of a query physically on disk, unlike regular views. It needs periodic
refreshing but provides faster query performance for complex aggregations.

38. What is Database Sharding?

Sharding is horizontal partitioning of data across multiple databases or servers. Each shard holds a
subset of data, improving scalability and performance for large datasets.

39. What is a Deadlock?

A Deadlock occurs when two or more transactions are waiting for each other to release locks, creating a
circular dependency. Databases resolve deadlocks by terminating one of the transactions.

40. What is Database Partitioning?

Partitioning divides a large table into smaller, more manageable pieces (partitions) stored separately.
Types: Range, List, Hash, and Composite partitioning. Improves query performance and maintenance.

41. What is ROWNUM / ROW_NUMBER()?

ROWNUM (Oracle) assigns sequential numbers to rows as they are returned. ROW_NUMBER() (ANSI
SQL) is a window function that assigns a unique sequential integer to each row within a partition ordered
by a specified column.

42. What are Aggregate Functions?

Aggregate functions operate on a set of rows and return a single value: COUNT() - counts rows, SUM() -
total, AVG() - average, MIN() - minimum value, MAX() - maximum value,
GROUP_CONCAT/STRING_AGG - concatenates values.

43. What is Schema?

A Schema is a logical container within a database that groups related objects (tables, views, stored
procedures, functions). It helps with organization, access control, and avoiding naming conflicts.

44. What is a Sequence?

A Sequence is a database object that generates a series of unique numeric values in order. Used to
generate primary keys automatically (similar to AUTO_INCREMENT).

45. What is the difference between OLTP and OLAP?

OLTP (Online Transaction Processing): handles day-to-day transactions, normalized, optimized for
INSERT/UPDATE/DELETE. OLAP (Online Analytical Processing): handles complex queries and
analysis, denormalized, optimized for SELECT.

46. What are Constraints?

Constraints enforce rules on data in tables. Types: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN
KEY, CHECK, DEFAULT. They maintain data integrity at the database level.

47. What is ISNULL / IFNULL / NVL?

These functions replace NULL with a specified value. ISNULL(col, 0) - SQL Server. IFNULL(col, 0) -
MySQL. NVL(col, 0) - Oracle. COALESCE(col, 0) - ANSI standard.

48. What is the N+1 Query Problem?

The N+1 problem occurs when an application executes 1 query to fetch N records, then N additional
queries for each record's related data. Solved by using JOINs or eager loading.
49. What are SQL Injection Attacks?
SQL Injection is a security vulnerability where malicious SQL code is inserted into input fields to
manipulate queries. Prevention: use parameterized queries/prepared statements, input validation,
ORMs.

50. What is SAVEPOINT?

A SAVEPOINT is a marker within a transaction that allows partial rollback. ROLLBACK TO SAVEPOINT
name undoes changes only to that point without rolling back the entire transaction.
SECTION 2: Top 200 SQL Queries
All queries are production-ready and use standard ANSI SQL with dialect notes where required. Table
schemas are representative — adapt column/table names to your context.

Basic SELECT Queries

Q1. Select all columns from a table
SELECT * FROM employees;

Q2. Select specific columns

SELECT first_name, last_name, salary FROM employees;

Q3. Select with WHERE condition

SELECT * FROM employees WHERE department = 'IT';

Q4. Select with multiple conditions

SELECT * FROM employees WHERE department = 'IT' AND salary > 50000;

Q5. Select with OR condition

SELECT * FROM employees WHERE department = 'HR' OR department = 'Finance';

Q6. Select with LIKE (pattern matching)

SELECT * FROM employees WHERE first_name LIKE 'A%';

Q7. Select with IN operator

SELECT * FROM employees WHERE department IN ('IT', 'HR', 'Finance');

Q8. Select with BETWEEN

SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000;

Q9. Select with ORDER BY

SELECT * FROM employees ORDER BY salary DESC;

Q10. Select with LIMIT / TOP

SELECT * FROM employees ORDER BY salary DESC LIMIT 10;
-- SQL Server: SELECT TOP 10 * FROM employees ORDER BY salary DESC;

Q11. Select DISTINCT values

SELECT DISTINCT department FROM employees;

Q12. Select with NULL check

SELECT * FROM employees WHERE manager_id IS NULL;

Q13. Select with NOT NULL check

SELECT * FROM employees WHERE email IS NOT NULL;

Q14. Count total rows

SELECT COUNT(*) AS total_employees FROM employees;

Q15. Count with condition

SELECT COUNT(*) AS it_employees FROM employees WHERE department = 'IT';
Aggregate Functions
Q16. SUM of a column
SELECT SUM(salary) AS total_salary FROM employees;

Q17. AVG of a column

SELECT AVG(salary) AS avg_salary FROM employees;

Q18. MAX and MIN values

SELECT MAX(salary) AS highest, MIN(salary) AS lowest FROM employees;

Q19. GROUP BY department

SELECT department, COUNT(*) AS headcount FROM employees GROUP BY department;

Q20. GROUP BY with SUM

SELECT department, SUM(salary) AS dept_salary FROM employees GROUP BY
department;
Q21. GROUP BY with HAVING
SELECT department, COUNT(*) AS cnt FROM employees GROUP BY department HAVING
COUNT(*) > 5;

Q22. Average salary per department

SELECT department, ROUND(AVG(salary), 2) AS avg_sal FROM employees GROUP BY
department ORDER BY avg_sal DESC;

Q23. Departments with salary sum > 500000

SELECT department, SUM(salary) AS total FROM employees GROUP BY department
HAVING SUM(salary) > 500000;

Q24. Count distinct values

SELECT COUNT(DISTINCT department) AS dept_count FROM employees;

Q25. Min salary per job title

SELECT job_title, MIN(salary) AS min_sal FROM employees GROUP BY job_title;

Q26. Max salary per department

SELECT department, MAX(salary) AS max_sal FROM employees GROUP BY department;

Q27. GROUP BY multiple columns

SELECT department, job_title, COUNT(*) AS cnt FROM employees GROUP BY
department, job_title;

Q28. Employees hired per year

SELECT YEAR(hire_date) AS year, COUNT(*) AS hired FROM employees GROUP BY
YEAR(hire_date) ORDER BY year;

Q29. Average salary above overall average

SELECT department, AVG(salary) AS avg_sal FROM employees GROUP BY department
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

Q30. Total orders per customer

SELECT customer_id, COUNT(order_id) AS total_orders, SUM(amount) AS
total_amount FROM orders GROUP BY customer_id;

JOIN Queries
Q31. INNER JOIN two tables
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = [Link];

Q32. LEFT JOIN (all employees, even without department)

SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = [Link];

Q33. RIGHT JOIN

SELECT e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = [Link];
Q34. FULL OUTER JOIN
SELECT e.first_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = [Link];

Q35. Self JOIN (employee and manager)

SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id;

Q36. CROSS JOIN

SELECT p.product_name, c.color_name
FROM products p
CROSS JOIN colors c;

Q37. Three-table JOIN

SELECT e.first_name, d.department_name, [Link]
FROM employees e
JOIN departments d ON e.department_id = [Link]
JOIN locations l ON d.location_id = [Link];

Q38. JOIN with aggregate

SELECT d.department_name, COUNT(e.employee_id) AS headcount
FROM departments d
LEFT JOIN employees e ON [Link] = e.department_id
GROUP BY d.department_name;

Q39. JOIN with WHERE filter

SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = [Link]
WHERE d.department_name = 'Engineering';

Q40. Find employees with no department (using LEFT JOIN)

SELECT e.first_name
FROM employees e
LEFT JOIN departments d ON e.department_id = [Link]
WHERE [Link] IS NULL;
Q41. JOIN with ORDER BY
SELECT e.first_name, d.department_name, [Link]
FROM employees e
JOIN departments d ON e.department_id = [Link]
ORDER BY [Link] DESC;

Q42. Products with their category names

SELECT p.product_name, c.category_name, [Link]
FROM products p
INNER JOIN categories c ON p.category_id = [Link];

Q43. Orders with customer names

SELECT c.customer_name, o.order_id, o.order_date, [Link]
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

Q44. Customers who have never ordered

SELECT c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Q45. JOIN with HAVING

SELECT d.department_name, AVG([Link]) AS avg_sal
FROM employees e
JOIN departments d ON e.department_id = [Link]
GROUP BY d.department_name
HAVING AVG([Link]) > 60000;

Q46. Join to find top earner per department

SELECT d.department_name, e.first_name, [Link]
FROM employees e
JOIN departments d ON e.department_id = [Link]
WHERE [Link] = (SELECT MAX(salary) FROM employees e2 WHERE e2.department_id =
e.department_id);

Q47. Students with their enrolled courses

SELECT s.student_name, c.course_name
FROM students s
JOIN enrollments en ON s.student_id = en.student_id
JOIN courses c ON en.course_id = c.course_id;

Q48. Non-equi JOIN (salary ranges)

SELECT e.first_name, [Link], [Link]
FROM employees e
JOIN salary_grades sg ON [Link] BETWEEN sg.min_sal AND sg.max_sal;

Q49. Sales with product and customer info

SELECT cu.customer_name, pr.product_name, [Link], s.sale_date
FROM sales s
JOIN customers cu ON s.customer_id = cu.customer_id
JOIN products pr ON s.product_id = pr.product_id;
Q50. Employees sharing the same manager
SELECT a.first_name AS emp1, b.first_name AS emp2, a.manager_id
FROM employees a
JOIN employees b ON a.manager_id = b.manager_id
WHERE a.employee_id < b.employee_id;

Subqueries
Q51. Employee with highest salary
SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

Q52. Employees earning above average

SELECT first_name, salary FROM employees WHERE salary > (SELECT AVG(salary)
FROM employees);

Q53. Second highest salary

SELECT MAX(salary) AS second_highest FROM employees WHERE salary < (SELECT
MAX(salary) FROM employees);

Q54. Nth highest salary (parameterized)

-- 3rd highest salary
SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;

Q55. Employees in departments with >10 people

SELECT first_name, department_id FROM employees
WHERE department_id IN (SELECT department_id FROM employees GROUP BY
department_id HAVING COUNT(*) > 10);

Q56. Subquery in FROM clause (derived table)

SELECT dept, avg_sal
FROM (SELECT department AS dept, AVG(salary) AS avg_sal FROM employees GROUP BY
department) AS dept_avg
WHERE avg_sal > 55000;

Q57. Correlated subquery — employees above dept average

SELECT e1.first_name, [Link], [Link]
FROM employees e1
WHERE [Link] > (SELECT AVG([Link]) FROM employees e2 WHERE [Link]
= [Link]);

Q58. EXISTS — departments with at least one employee

SELECT department_name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = [Link]);

Q59. NOT EXISTS — departments with no employees

SELECT department_name
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = [Link]);

Q60. Subquery with IN and dates

SELECT * FROM orders
WHERE customer_id IN (SELECT customer_id FROM customers WHERE country =
'India');
Q61. ALL operator — salary greater than all IT salaries
SELECT first_name, salary FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'IT');

Q62. ANY operator — salary greater than any HR salary

SELECT first_name, salary FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department = 'HR');

Q63. Top 3 customers by total orders

SELECT customer_id, total
FROM (SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY
customer_id) AS t
ORDER BY total DESC LIMIT 3;

Q64. Products never ordered

SELECT product_name FROM products
WHERE product_id NOT IN (SELECT product_id FROM order_items);

Q65. Delete duplicate rows (keep min ID)

DELETE FROM employees
WHERE employee_id NOT IN (SELECT MIN(employee_id) FROM employees GROUP BY
first_name, last_name, department);

Q66. Update salary based on subquery

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = (SELECT id FROM departments WHERE department_name =
'Engineering');

Q67. Find duplicate email addresses

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

Q68. Employees with salary rank 2 in their department

SELECT * FROM employees e
WHERE 2 = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE [Link]
= [Link] AND [Link] >= [Link]);

Q69. Most recent order per customer

SELECT * FROM orders o1
WHERE order_date = (SELECT MAX(order_date) FROM orders o2 WHERE o2.customer_id
= o1.customer_id);

Q70. Employees hired in the same year as a specific employee

SELECT first_name, hire_date FROM employees
WHERE YEAR(hire_date) = (SELECT YEAR(hire_date) FROM employees WHERE
employee_id = 101);

String Functions
Q71. Concatenate first and last name
 SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
-- Oracle: SELECT first_name || ' ' || last_name AS full_name FROM employees;

Q72. Convert to uppercase / lowercase

SELECT UPPER(first_name) AS upper_name, LOWER(last_name) AS lower_name FROM
employees;

Q73. Extract substring

SELECT SUBSTRING(phone_number, 1, 3) AS area_code FROM employees;
-- Oracle: SUBSTR(phone_number, 1, 3)

Q74. Find string length

SELECT first_name, LENGTH(first_name) AS name_length FROM employees;

Q75. Trim whitespace

SELECT TRIM(first_name) AS clean_name FROM employees;

Q76. REPLACE function

SELECT REPLACE(phone_number, '-', '') AS clean_phone FROM employees;

Q77. CHARINDEX / INSTR — find position of character

SELECT CHARINDEX('@', email) AS at_position FROM users;
-- MySQL: INSTR(email, '@')

Q78. LEFT and RIGHT extract

SELECT LEFT(email, CHARINDEX('@', email) - 1) AS username FROM users;

Q79. LPAD / RPAD for padding

SELECT LPAD(employee_id::TEXT, 6, '0') AS emp_code FROM employees;
-- Formats as: 000042

Q80. String aggregation per group

SELECT department, GROUP_CONCAT(first_name SEPARATOR ', ') AS members FROM
employees GROUP BY department;
-- PostgreSQL: STRING_AGG(first_name, ', ')
Q81. Extract domain from email
SELECT SUBSTRING(email, CHARINDEX('@', email) + 1, LEN(email)) AS domain FROM
users;

Q82. Count employees whose name starts with each letter

SELECT LEFT(first_name, 1) AS initial, COUNT(*) AS cnt FROM employees GROUP BY
LEFT(first_name, 1) ORDER BY initial;

Q83. REVERSE a string

SELECT first_name, REVERSE(first_name) AS reversed FROM employees;

Q84. FORMAT number with commas

SELECT FORMAT(salary, 0) AS formatted_salary FROM employees;
-- Result: 75,000

Q85. Soundex for phonetic matching

SELECT first_name FROM employees WHERE SOUNDEX(first_name) = SOUNDEX('Jon');

Date & Time Functions

Q86. Get current date and time
SELECT GETDATE(); -- SQL Server
SELECT NOW(); -- MySQL / PostgreSQL
SELECT SYSDATE FROM dual; -- Oracle

Q87. Extract year, month, day

SELECT YEAR(hire_date), MONTH(hire_date), DAY(hire_date) FROM employees;
-- PostgreSQL: EXTRACT(YEAR FROM hire_date)

Q88. Date difference in days

SELECT DATEDIFF(GETDATE(), hire_date) AS days_employed FROM employees;
-- MySQL: DATEDIFF(NOW(), hire_date)

Q89. Add days / months to a date

SELECT DATEADD(day, 30, order_date) AS due_date FROM orders;
-- MySQL: DATE_ADD(order_date, INTERVAL 30 DAY)

Q90. Format a date

SELECT FORMAT(hire_date, 'dd-MM-yyyy') AS formatted_date FROM employees;
-- MySQL: DATE_FORMAT(hire_date, '%d-%m-%Y')

Q91. Filter records by date range

SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

Q92. Records from the last 30 days

SELECT * FROM orders WHERE order_date >= DATEADD(day, -30, GETDATE());
-- MySQL: WHERE order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)

Q93. Records from current month

SELECT * FROM orders WHERE MONTH(order_date) = MONTH(GETDATE()) AND
YEAR(order_date) = YEAR(GETDATE());

Q94. Find employees' tenure in years

 SELECT first_name, DATEDIFF(YEAR, hire_date, GETDATE()) AS years_of_service
FROM employees;

Q95. Count orders per month

SELECT YEAR(order_date) AS yr, MONTH(order_date) AS mo, COUNT(*) AS orders FROM
orders GROUP BY YEAR(order_date), MONTH(order_date) ORDER BY yr, mo;

Q96. Get day of week

SELECT order_date, DATENAME(weekday, order_date) AS day_name FROM orders;
-- MySQL: DAYNAME(order_date)

Q97. Convert string to date

SELECT CAST('2024-06-15' AS DATE) AS converted_date;
-- MySQL: STR_TO_DATE('15-06-2024', '%d-%m-%Y')

Q98. First day of current month

SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS first_day;
-- MySQL: DATE_FORMAT(NOW(), '%Y-%m-01')

Q99. Orders placed on weekends

SELECT * FROM orders WHERE DATEPART(weekday, order_date) IN (1, 7);
-- MySQL: WHERE DAYOFWEEK(order_date) IN (1, 7)

Q100. Age calculation from birthdate

SELECT first_name, DATEDIFF(YEAR, birth_date, GETDATE()) AS age FROM employees;
Window Functions
Q101. ROW_NUMBER — unique row per partition
SELECT first_name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

Q102. RANK — with gaps for ties

SELECT first_name, salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

Q103. DENSE_RANK — no gaps for ties

SELECT first_name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Q104. Top earner per department using ROW_NUMBER

SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS
rn
FROM employees
) t
WHERE rn = 1;

Q105. Running total of salary

SELECT first_name, salary,
SUM(salary) OVER (ORDER BY hire_date ROWS UNBOUNDED PRECEDING) AS
running_total
FROM employees;

Q106. Moving average (3-row)

SELECT order_date, amount,
AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT
ROW) AS moving_avg
FROM orders;

Q107. LAG — previous row value

SELECT employee_id, salary,
LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_salary
FROM employees;

Q108. LEAD — next row value

SELECT employee_id, salary,
LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary
FROM employees;

Q109. Salary difference from previous employee

SELECT first_name, salary,
salary - LAG(salary) OVER (ORDER BY salary) AS salary_diff
FROM employees;
Q110. NTILE — divide into quartiles
SELECT first_name, salary,
NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

Q111. FIRST_VALUE and LAST_VALUE

SELECT first_name, salary,
FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS
top_sal,
LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC ROWS
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS min_sal
FROM employees;

Q112. Percentage of total salary per department

SELECT first_name, department, salary,
ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department), 2) AS
pct_of_dept
FROM employees;

Q113. Cumulative count

SELECT order_date,
COUNT(*) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS
cumulative_orders
FROM orders;

Q114. Rank customers by total spend

SELECT customer_id, total_spend,
RANK() OVER (ORDER BY total_spend DESC) AS spend_rank
FROM (SELECT customer_id, SUM(amount) AS total_spend FROM orders GROUP BY
customer_id) t;

Q115. Detect salary changes (using LAG)

SELECT employee_id,
LAG(salary) OVER (PARTITION BY employee_id ORDER BY effective_date) AS
old_salary,
salary AS new_salary
FROM salary_history;

Q116. Find gaps in sequential IDs

SELECT id + 1 AS gap_start
FROM orders o1
WHERE NOT EXISTS (SELECT 1 FROM orders o2 WHERE [Link] = [Link] + 1)
AND id < (SELECT MAX(id) FROM orders);

Q117. Percent rank

SELECT first_name, salary,
PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank
FROM employees;

Q118. CUME_DIST — cumulative distribution

SELECT first_name, salary,
CUME_DIST() OVER (ORDER BY salary) AS cum_dist
FROM employees;
Q119. Top 2 salaries per department
SELECT * FROM (
SELECT *, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS
dr
FROM employees
) t WHERE dr <= 2;

Q120. Month-over-month growth

SELECT mo, revenue,
LAG(revenue) OVER (ORDER BY mo) AS prev_revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY mo)) / LAG(revenue) OVER
(ORDER BY mo), 2) AS growth_pct
FROM monthly_revenue;
CTEs (Common Table Expressions)
Q121. Basic CTE
WITH dept_stats AS (
SELECT department, AVG(salary) AS avg_sal FROM employees GROUP BY department
)
SELECT * FROM dept_stats WHERE avg_sal > 60000;

Q122. Multiple CTEs

WITH high_earners AS (
SELECT * FROM employees WHERE salary > 80000
),
it_dept AS (
SELECT * FROM employees WHERE department = 'IT'
)
SELECT h.first_name FROM high_earners h JOIN it_dept i ON h.employee_id =
i.employee_id;

Q123. CTE for nth highest salary

WITH salary_rank AS (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr FROM employees
)
SELECT salary FROM salary_rank WHERE dr = 3;

Q124. Recursive CTE — employee hierarchy

WITH RECURSIVE emp_hierarchy AS (
SELECT employee_id, first_name, manager_id, 0 AS level
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.first_name, e.manager_id, [Link] + 1
FROM employees e
JOIN emp_hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM emp_hierarchy ORDER BY level;

Q125. Recursive CTE — number series 1 to 10

WITH RECURSIVE nums AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM nums WHERE n < 10
)
SELECT * FROM nums;

Q126. CTE for running balance

WITH txn_order AS (
SELECT *, SUM(amount) OVER (ORDER BY txn_date) AS running_bal FROM
transactions
)
SELECT * FROM txn_order;
Q127. CTE to find duplicate records
WITH dupes AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY first_name, last_name, department
ORDER BY employee_id) AS rn FROM employees
)
SELECT * FROM dupes WHERE rn > 1;

Q128. CTE to delete duplicates

WITH dupes AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
FROM users
)
DELETE FROM dupes WHERE rn > 1;

Q129. CTE replacing complex subquery

WITH dept_max AS (
SELECT department_id, MAX(salary) AS max_sal FROM employees GROUP BY
department_id
)
SELECT e.first_name, [Link], d.max_sal
FROM employees e
JOIN dept_max d ON e.department_id = d.department_id
WHERE [Link] = d.max_sal;

Q130. CTE for pagination

WITH paginated AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rn FROM products
)
SELECT * FROM paginated WHERE rn BETWEEN 21 AND 40; -- Page 2, 20 per page

Q131. CTE chain for data pipeline

WITH raw AS (
SELECT * FROM sales WHERE sale_date >= '2024-01-01'
),
cleaned AS (
SELECT *, COALESCE(discount, 0) AS discount FROM raw
),
final AS (
SELECT *, amount * (1 - discount) AS net_amount FROM cleaned
)
SELECT * FROM final;

Q132. Recursive CTE — Fibonacci sequence

WITH RECURSIVE fib AS (
SELECT 0 AS a, 1 AS b
UNION ALL
SELECT b, a + b FROM fib WHERE a < 1000
)
SELECT a FROM fib;

Q133. CTE for year-over-year comparison

 WITH yearly AS (
SELECT YEAR(sale_date) AS yr, SUM(amount) AS revenue FROM sales GROUP BY
YEAR(sale_date)
)
SELECT [Link], [Link], [Link] AS prev_year,
[Link] - [Link] AS yoy_change
FROM yearly cur
LEFT JOIN yearly prev ON [Link] = [Link] + 1;

Q134. CTE for cohort analysis

WITH cohorts AS (
SELECT customer_id, MIN(YEAR(order_date)) AS cohort_year FROM orders GROUP BY
customer_id
)
SELECT c.cohort_year, YEAR(o.order_date) AS order_year, COUNT(DISTINCT
o.customer_id) AS active_customers
FROM cohorts c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.cohort_year, YEAR(o.order_date);

Q135. CTE for rolling 7-day average

WITH daily AS (
SELECT order_date, SUM(amount) AS daily_rev FROM orders GROUP BY order_date
)
SELECT order_date, daily_rev,
AVG(daily_rev) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT
ROW) AS rolling_7day
FROM daily;

DDL & DML Queries

Q136. CREATE TABLE
CREATE TABLE employees (
employee_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
salary DECIMAL(10, 2) DEFAULT 0.00,
hire_date DATE,
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(id)
);

Q137. ALTER TABLE — add column

ALTER TABLE employees ADD COLUMN phone VARCHAR(20);

Q138. ALTER TABLE — modify column

ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12, 2);
-- SQL Server: ALTER TABLE employees ALTER COLUMN salary DECIMAL(12,2);
Q139. ALTER TABLE — drop column
ALTER TABLE employees DROP COLUMN phone;

Q140. DROP TABLE

DROP TABLE IF EXISTS temp_employees;
Q141. TRUNCATE TABLE
TRUNCATE TABLE temp_logs;

Q142. INSERT single row

INSERT INTO employees (first_name, last_name, email, salary, hire_date,
department_id)
VALUES ('John', 'Doe', '[Link]@[Link]', 65000.00, '2024-01-15', 3);

Q143. INSERT multiple rows

INSERT INTO employees (first_name, last_name, salary) VALUES
('Alice', 'Smith', 70000),
('Bob', 'Brown', 65000),
('Carol', 'White', 72000);

Q144. INSERT from SELECT

INSERT INTO archive_employees
SELECT * FROM employees WHERE YEAR(hire_date) < 2010;

Q145. UPDATE single column

UPDATE employees SET salary = 75000 WHERE employee_id = 101;

Q146. UPDATE with calculation

UPDATE employees SET salary = salary * 1.10 WHERE department = 'IT';

Q147. UPDATE from another table

UPDATE employees e
SET salary = d.new_salary
FROM dept_salary_updates d
WHERE e.department_id = d.department_id;

Q148. DELETE specific rows

DELETE FROM employees WHERE employee_id = 101;

Q149. DELETE with subquery

DELETE FROM orders
WHERE customer_id IN (SELECT customer_id FROM blacklisted_customers);

Q150. UPSERT — INSERT or UPDATE (MySQL)

INSERT INTO products (product_id, name, price)
VALUES (1, 'Laptop', 999.99)
ON DUPLICATE KEY UPDATE price = VALUES(price);

Q151. MERGE (SQL Server / Oracle)

MERGE INTO target_table t
USING source_table s ON [Link] = [Link]
WHEN MATCHED THEN UPDATE SET [Link] = [Link], [Link] = [Link]
WHEN NOT MATCHED THEN INSERT (id, name, price) VALUES ([Link], [Link], [Link]);

Q152. CREATE INDEX

CREATE INDEX idx_emp_dept ON employees(department_id);
CREATE UNIQUE INDEX idx_emp_email ON employees(email);
CREATE INDEX idx_composite ON orders(customer_id, order_date);

Q153. CREATE VIEW

 CREATE VIEW v_emp_details AS
SELECT e.employee_id, e.first_name, e.last_name, d.department_name, [Link]
FROM employees e
JOIN departments d ON e.department_id = [Link];

Q154. CREATE STORED PROCEDURE

CREATE PROCEDURE GetEmployeesByDept(@dept_name VARCHAR(50))
AS
BEGIN
SELECT * FROM employees WHERE department = @dept_name ORDER BY salary DESC;
END;

Q155. Transaction with SAVEPOINT

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
COMMIT;

Advanced & Optimization Queries

Q156. PIVOT — rows to columns
SELECT department,
SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male,
SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female
FROM employees
GROUP BY department;

Q157. UNPIVOT — columns to rows (SQL Server)

SELECT employee_id, quarter, sales_amount
FROM quarterly_sales
UNPIVOT (sales_amount FOR quarter IN (Q1, Q2, Q3, Q4)) AS unpivoted;

Q158. CASE WHEN for salary bands

SELECT first_name, salary,
CASE
WHEN salary < 40000 THEN 'Junior'
WHEN salary BETWEEN 40000 AND 80000 THEN 'Mid'
WHEN salary > 80000 THEN 'Senior'
ELSE 'Unknown'
END AS level
FROM employees;

Q159. Dynamic column masking

SELECT first_name,
CONCAT(LEFT(email, 2), '****', RIGHT(email, CHARINDEX('@', REVERSE(email))))
AS masked_email
FROM employees;

Q160. Find employees with consecutive hire dates

SELECT e1.first_name, e1.hire_date, e2.first_name AS next_hire
FROM employees e1
JOIN employees e2 ON DATEDIFF(day, e1.hire_date, e2.hire_date) = 1;
Q161. Island and Gap detection
SELECT MIN(date_col) AS island_start, MAX(date_col) AS island_end
FROM (
SELECT date_col,
date_col - ROW_NUMBER() OVER (ORDER BY date_col) AS grp
FROM active_dates
) t
GROUP BY grp;

Q162. Median salary (without built-in)

SELECT AVG(salary) AS median FROM (
SELECT salary FROM employees
ORDER BY salary
LIMIT 2 - (SELECT COUNT(*) FROM employees) % 2
OFFSET (SELECT (COUNT(*) - 1) / 2 FROM employees)
) sub;

Q163. Transpose data without PIVOT

SELECT
MAX(CASE WHEN rn = 1 THEN product_name END) AS col1,
MAX(CASE WHEN rn = 2 THEN product_name END) AS col2,
MAX(CASE WHEN rn = 3 THEN product_name END) AS col3
FROM (SELECT product_name, ROW_NUMBER() OVER (ORDER BY product_id) AS rn FROM
products) t;

Q164. EXCEPT (rows in first but not second)

SELECT employee_id FROM employees
EXCEPT
SELECT employee_id FROM resigned_employees;

Q165. INTERSECT (common rows)

SELECT customer_id FROM online_orders
INTERSECT
SELECT customer_id FROM in_store_orders;

Q166. JSON data query (MySQL 5.7+)

SELECT JSON_EXTRACT(profile_json, '$.[Link]') AS city FROM users;

Q167. Optimized query using EXISTS over IN

-- Prefer EXISTS for large subquery result sets
SELECT first_name FROM employees e
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.employee_id = e.employee_id AND
[Link] > 10000);

Q168. Index hint (force index use)

SELECT * FROM employees USE INDEX (idx_emp_dept) WHERE department_id = 3;
-- SQL Server: SELECT * FROM employees WITH (INDEX(idx_emp_dept)) WHERE
department_id = 3;

Q169. Avoid SELECT * for performance

-- Bad: SELECT * FROM orders;
-- Good:
 SELECT order_id, customer_id, amount, order_date FROM orders WHERE order_date
>= '2024-01-01';

Q170. Avoid functions on indexed columns in WHERE

-- Bad (can't use index on hire_date):
-- WHERE YEAR(hire_date) = 2022
-- Good (index-friendly):
SELECT * FROM employees WHERE hire_date BETWEEN '2022-01-01' AND '2022-12-31';

Q171. Partitioned table query

-- Query only specific partition
SELECT * FROM orders PARTITION (p2024) WHERE customer_id = 101;

Q172. EXPLAIN to analyze query

EXPLAIN SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = [Link]
WHERE [Link] > 70000;

Q173. Batch update to avoid lock contention

-- Update in batches of 1000 instead of all at once
UPDATE employees SET status = 'inactive'
WHERE employee_id IN (SELECT TOP 1000 employee_id FROM employees WHERE
last_login < '2022-01-01');

Q174. ROLLUP for subtotals

SELECT department, job_title, SUM(salary) AS total
FROM employees
GROUP BY ROLLUP(department, job_title);

Q175. CUBE for cross-tabulation

SELECT department, job_title, SUM(salary) AS total
FROM employees
GROUP BY CUBE(department, job_title);

Q176. GROUPING SETS

SELECT department, job_title, SUM(salary)
FROM employees
GROUP BY GROUPING SETS ((department), (job_title), (department, job_title),
());

Q177. Materialized view refresh

-- PostgreSQL
CREATE MATERIALIZED VIEW mv_dept_stats AS
SELECT department, COUNT(*) AS cnt, AVG(salary) AS avg_sal FROM employees
GROUP BY department;

REFRESH MATERIALIZED VIEW mv_dept_stats;

Q178. Check constraint

ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary >= 0 AND salary
<= 10000000);
ALTER TABLE orders ADD CONSTRAINT chk_status CHECK (status IN ('pending',
'shipped', 'delivered', 'cancelled'));
Q179. TRIGGER — auto-update timestamp
CREATE TRIGGER trg_update_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
SET NEW.updated_at = NOW();

Q180. Optimize with covering index

-- A covering index includes all columns needed by the query,
-- eliminating the need to access the main table.
CREATE INDEX idx_covering ON employees(department_id, salary, first_name);
-- Query that uses covering index:
SELECT first_name, salary FROM employees WHERE department_id = 3;
Real-World Scenario Queries
Q181. Find customers who bought all products
SELECT customer_id FROM order_items
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) = (SELECT COUNT(*) FROM products);

Q182. Consecutive logins for 7 days

SELECT user_id FROM (
SELECT user_id, login_date,
login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)
AS grp
FROM (SELECT DISTINCT user_id, CAST(login_time AS DATE) AS login_date FROM
user_logins) t
) g
GROUP BY user_id, grp HAVING COUNT(*) >= 7;

Q183. Calculate retention rate

SELECT cohort_month,
COUNT(DISTINCT c.customer_id) AS cohort_size,
COUNT(DISTINCT r.customer_id) AS retained,
ROUND(100.0 * COUNT(DISTINCT r.customer_id) / COUNT(DISTINCT c.customer_id),
1) AS retention_pct
FROM cohorts c
LEFT JOIN orders r ON c.customer_id = r.customer_id AND r.order_month =
c.cohort_month + 1
GROUP BY cohort_month;

Q184. Products frequently bought together

SELECT a.product_id AS product1, b.product_id AS product2, COUNT(*) AS
times_bought_together
FROM order_items a
JOIN order_items b ON a.order_id = b.order_id AND a.product_id < b.product_id
GROUP BY a.product_id, b.product_id
ORDER BY times_bought_together DESC
LIMIT 10;

Q185. Bank account balance after all transactions

SELECT account_id,
SUM(CASE WHEN txn_type = 'credit' THEN amount ELSE -amount END) AS balance
FROM transactions
GROUP BY account_id;

Q186. Find the longest streak of daily sales

WITH daily AS (
SELECT DISTINCT CAST(sale_date AS DATE) AS dt FROM sales
),
streaks AS (
SELECT dt, dt - ROW_NUMBER() OVER (ORDER BY dt) AS grp FROM daily
)
 SELECT MAX(streak) AS longest_streak FROM (
SELECT COUNT(*) AS streak FROM streaks GROUP BY grp
) t;

Q187. Employee with the most subordinates

SELECT manager_id, COUNT(*) AS subordinates
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
ORDER BY subordinates DESC
LIMIT 1;

Q188. Rolling 3-month revenue sum

SELECT sale_month,
SUM(revenue) OVER (ORDER BY sale_month ROWS BETWEEN 2 PRECEDING AND CURRENT
ROW) AS rolling_3mo
FROM monthly_revenue;

Q189. Find users active in Jan but not Feb

SELECT DISTINCT user_id FROM activity WHERE activity_month = 1
EXCEPT
SELECT DISTINCT user_id FROM activity WHERE activity_month = 2;

Q190. Detect fraudulent transactions (double spending)

SELECT account_id, txn_date, amount
FROM transactions t1
WHERE EXISTS (
SELECT 1 FROM transactions t2
WHERE t2.account_id = t1.account_id
AND t2.txn_id <> t1.txn_id
AND [Link] = [Link]
AND ABS(DATEDIFF(minute, t1.txn_time, t2.txn_time)) < 5
);

Q191. Build an inventory aging report

SELECT product_id, product_name, quantity_on_hand,
CASE
WHEN DATEDIFF(day, last_received_date, GETDATE()) <= 30 THEN '0-30 days'
WHEN DATEDIFF(day, last_received_date, GETDATE()) <= 60 THEN '31-60 days'
ELSE '60+ days'
END AS aging_bucket
FROM inventory;

Q192. Employee turnover rate per year

SELECT yr,
terminated,
avg_headcount,
ROUND(100.0 * terminated / avg_headcount, 1) AS turnover_rate
FROM (
SELECT YEAR(termination_date) AS yr,
COUNT(*) AS terminated,
 (SELECT COUNT(*) FROM employees) AS avg_headcount
FROM employees WHERE termination_date IS NOT NULL
GROUP BY YEAR(termination_date)
) t;

Q193. Top 5 products by revenue each month

WITH monthly_rank AS (
SELECT product_id, MONTH(sale_date) AS mo, SUM(amount) AS revenue,
RANK() OVER (PARTITION BY MONTH(sale_date) ORDER BY SUM(amount) DESC) AS rk
FROM sales
GROUP BY product_id, MONTH(sale_date)
)
SELECT * FROM monthly_rank WHERE rk <= 5;

Q194. Churn prediction — no purchase in 90 days

SELECT customer_id, MAX(order_date) AS last_order,
DATEDIFF(day, MAX(order_date), GETDATE()) AS days_since_order,
'At Risk' AS status
FROM orders
GROUP BY customer_id
HAVING DATEDIFF(day, MAX(order_date), GETDATE()) > 90;

Q195. Sales funnel conversion rates

SELECT
COUNT(DISTINCT session_id) AS visits,
COUNT(DISTINCT CASE WHEN added_to_cart = 1 THEN session_id END) AS cart_adds,
COUNT(DISTINCT CASE WHEN purchased = 1 THEN session_id END) AS purchases,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN added_to_cart=1 THEN session_id END) /
COUNT(DISTINCT session_id), 1) AS cart_rate,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN purchased=1 THEN session_id END) /
COUNT(DISTINCT CASE WHEN added_to_cart=1 THEN session_id END), 1) AS
conversion_rate
FROM user_sessions;

Q196. Generate a date dimension / calendar table

WITH RECURSIVE calendar AS (
SELECT CAST('2024-01-01' AS DATE) AS dt
UNION ALL
SELECT dt + INTERVAL 1 DAY FROM calendar WHERE dt < '2024-12-31'
)
SELECT dt, DAYNAME(dt) AS day_name, MONTH(dt) AS month, QUARTER(dt) AS quarter
FROM calendar;

Q197. Slowly Changing Dimension (SCD Type 2)

INSERT INTO dim_employee (employee_id, name, department, start_date, end_date,
is_current)
SELECT s.employee_id, [Link], [Link], GETDATE(), NULL, 1
FROM staging_employee s
LEFT JOIN dim_employee d ON s.employee_id = d.employee_id AND d.is_current = 1
WHERE d.employee_id IS NULL OR [Link] <> [Link];

Q198. Parse and query comma-separated values

 -- Split comma-separated tags and find rows with a specific tag
SELECT * FROM articles
WHERE FIND_IN_SET('SQL', tags) > 0;
-- PostgreSQL: WHERE 'SQL' = ANY(STRING_TO_ARRAY(tags, ','))

Q199. Audit trail — who changed what

SELECT a.changed_at, a.changed_by, a.table_name, a.column_name,
a.old_value, a.new_value
FROM audit_log a
WHERE a.table_name = 'employees'
ORDER BY a.changed_at DESC;

Q200. Full report — department scorecard

SELECT
d.department_name,
COUNT(e.employee_id) AS headcount,
ROUND(AVG([Link]), 2) AS avg_salary,
MAX([Link]) AS max_salary,
MIN([Link]) AS min_salary,
SUM([Link]) AS payroll,
COUNT(CASE WHEN [Link] = 'M' THEN 1 END) AS male_count,
COUNT(CASE WHEN [Link] = 'F' THEN 1 END) AS female_count,
ROUND(AVG(DATEDIFF(YEAR, e.hire_date, GETDATE())), 1) AS avg_tenure_yrs
FROM departments d
LEFT JOIN employees e ON [Link] = e.department_id
GROUP BY d.department_name
ORDER BY payroll DESC;
SECTION 3: SQL Quick Reference Cheat Sheet
JOIN Types at a Glance
JOIN Type Description
INNER JOIN Returns rows with matching values in BOTH tables
LEFT JOIN All rows from left table + matched rows from right (NULL if no
match)
RIGHT JOIN All rows from right table + matched rows from left (NULL if no
match)
FULL OUTER JOIN All rows from both tables (NULL where no match on either side)
CROSS JOIN Cartesian product — every row from left × every row from right
SELF JOIN Table joined to itself; requires aliases

Aggregate Functions
Function Description & Example
COUNT(*) Count of all rows: SELECT COUNT(*) FROM orders;
COUNT(col) Count non-NULL values: COUNT(email)
SUM(col) Total sum: SELECT SUM(salary) FROM employees;
AVG(col) Average: SELECT AVG(salary) FROM employees;
MAX(col) Maximum value: SELECT MAX(salary) FROM employees;
MIN(col) Minimum value: SELECT MIN(salary) FROM employees;
GROUP_CONCAT / Concatenate values: STRING_AGG(name, ', ') WITHIN GROUP
STRING_AGG (ORDER BY name)

Window Functions
Function Description
ROW_NUMBER() Unique sequential number per partition — no ties
RANK() Rank with gaps on ties (1, 1, 3, 4)
DENSE_RANK() Rank without gaps on ties (1, 1, 2, 3)
NTILE(n) Divides rows into n equal buckets
LAG(col, n) Value of col n rows before current row
LEAD(col, n) Value of col n rows after current row
SUM() OVER() Running / partition sum
FIRST_VALUE() First value in the window frame
 Function Description
LAST_VALUE() Last value in the window frame
PERCENT_RANK() Relative rank 0.0 to 1.0

Common String Functions

Function Description & Syntax
CONCAT(a, b) Concatenate strings: CONCAT(first_name, ' ', last_name)
LENGTH(s) / LEN(s) String length. MySQL: LENGTH(). SQL Server: LEN()
UPPER(s) / LOWER(s) Convert case
SUBSTRING(s, pos, len) Extract portion: SUBSTRING('Hello World', 7, 5) → 'World'
TRIM(s) Remove leading/trailing spaces
REPLACE(s, old, new) Replace occurrences: REPLACE(phone, '-', '')
CHARINDEX/INSTR Find position: CHARINDEX('@', email) — SQL Server;
INSTR(email, '@') — MySQL
COALESCE(a, b, c) Return first non-NULL value
NULLIF(a, b) Return NULL if a = b, else return a

Query Performance Tips

1. Use indexes on columns: Add indexes on columns used in WHERE, JOIN, and ORDER BY
clauses.
2. Avoid SELECT *: Select only required columns to reduce I/O and memory.
3. Use EXISTS over IN: EXISTS short-circuits on first match; IN evaluates entire subquery.
4. Filter early with WHERE: Push filters as early as possible to reduce rows processed.
5. Avoid functions on indexed columns: WHERE YEAR(date) = 2024 disables index; use date range
instead.
6. Analyze with EXPLAIN: Always check the execution plan for slow queries.
7. Partition large tables: Use table partitioning for multi-million-row tables.
8. Use CTEs for readability: CTEs improve readability and can be optimized by the query engine.
9. Covering indexes: Include all query columns in the index to avoid table lookups.
10. Batch large DML: Split large DELETE/UPDATE into smaller batches to avoid lock escalation.