1.

DBMS – COMPLETE DETAILED THEORY

📌 What is DBMS (Deep Explanation)

A Database Management System (DBMS) is software that allows users to:

 Store data
 Retrieve data
 Manipulate data
 Ensure security & consistency

👉 Example: College system storing student records.

🔹 Why DBMS is needed?

Without DBMS:

 Data redundancy increases

 Data inconsistency occurs
 No security
 Hard to access data

With DBMS:

 Organized storage (tables)

 Fast retrieval (queries)
 Multi-user access
 Data integrity

📌 DBMS Architecture

🔹 3-Level Architecture

1. External Level (View Level)

o What user sees
o Example: Only student names visible
2. Conceptual Level
o Logical structure of database
o Tables, relationships
3. Internal Level
o Physical storage (disk, indexing)

👉 This separation gives data independence

📌 Data Independence (Very Important)

🔹 Physical Data Independence

 Changes in storage do NOT affect users

👉 Example: Changing indexing method

🔹 Logical Data Independence

 Changes in table structure do NOT affect users

👉 Example: Adding new column

🔷 2. DBMS vs RDBMS (Detailed)

Feature DBMS RDBMS
Structure Files Tables
Relationship No Yes (Foreign Key)
Redundancy High Low
Security Low High
Examples File system MySQL

👉 RDBMS follows relational model (tables + relations)

🔷 3. ER MODEL (Entity Relationship Model)

📌 Entity

Real-world object
👉 Example: Student, Employee

📌 Attribute

Property of entity
👉 Example: Name, Age

📌 Types of Attributes

 Simple → Age
 Composite → Full Name
 Derived → Age from DOB
 Multi-valued → Phone numbers

📌 Relationship
Connection between entities
👉 Student "enrolls" in Course

🔷 4. KEYS (VERY IMPORTANT FOR EXAMS)

📌 Super Key

Set of attributes that uniquely identify a record

📌 Candidate Key

Minimal super key

📌 Primary Key

 Selected candidate key

 Unique + NOT NULL

📌 Foreign Key

 Creates relationship
 Refers to primary key of another table

📌 Composite Key

Combination of columns

🔷 5. NORMALIZATION (DETAILED + LOGIC)

👉 Goal: Reduce redundancy & dependency

📌 1NF (First Normal Form)

✔ No repeating groups
✔ Atomic values

❌ Wrong:

Name Subjects
A Math, Sci

✔ Correct:
Name Subject
A Math
A Sci

📌 2NF

✔ Must be in 1NF
✔ Remove partial dependency

👉 Partial dependency = Non-key depends on part of key

📌 3NF

✔ Must be in 2NF
✔ Remove transitive dependency

👉 Example:

 Student → Dept → HOD

❌ HOD depends on Dept, not student

📌 BCNF (Advanced)

Stronger version of 3NF

🔷 6. SQL COMMAND TYPES (DETAILED)

📌 DDL (Data Definition Language)

 CREATE
 ALTER
 DROP

👉 Defines structure

📌 DML (Data Manipulation Language)

 INSERT
 UPDATE
 DELETE
👉 Modifies data

📌 DQL (Data Query Language)

 SELECT

📌 DCL (Data Control Language)

 GRANT
 REVOKE

👉 Security

📌 TCL (Transaction Control)

 COMMIT
 ROLLBACK
 SAVEPOINT

👉 Transaction management

🔷 7. JOINS (DEEP CONCEPT)

📌 INNER JOIN

Returns matching records

📌 LEFT JOIN

All from left + matched from right

📌 RIGHT JOIN

All from right + matched from left

📌 FULL JOIN

All records (MySQL workaround using UNION)

📌 JOIN vs SUBQUERY
Join Subquery
Faster Slower
Readable Nested

🔷 8. TRANSACTIONS (VERY IMPORTANT)

📌 What is Transaction?

A group of SQL operations executed together.

👉 Example: Bank transfer

📌 ACID Properties (DETAILED)

🔹 Atomicity

 All or nothing
 If one fails → rollback

🔹 Consistency

 Database remains valid

🔹 Isolation

 Transactions don’t interfere

🔹 Durability

 Data saved permanently

🔷 9. INDEXING (DETAILED)

📌 What is Index?

Improves query speed by reducing search time

👉 Like index in book

📌 Types
  Clustered
 Non-clustered
 Unique index

📌 Advantage

✔ Fast search
✔ Faster joins

📌 Disadvantage

❌ Extra storage
❌ Slower insert/update

🔷 10. VIEWS (DETAILED)

📌 What is View?

Virtual table based on query

👉 Does NOT store data physically

📌 Advantages

✔ Security
✔ Simplifies queries
✔ Data abstraction

🔷 11. STORED PROCEDURE

📌 What is it?

Precompiled SQL code stored in database

📌 Advantages

✔ Faster execution
✔ Reusability
✔ Security
🔷 12. TRIGGERS

📌 What is Trigger?

Automatically executed when event occurs

👉 INSERT / UPDATE / DELETE

📌 Types

 BEFORE
 AFTER

🔷 13. WINDOW FUNCTIONS (ADVANCED)

📌 What are they?

Perform calculations across rows without grouping

👉 Very important for Data Analyst

📌 Examples

 RANK()
 DENSE_RANK()
 ROW_NUMBER()

🔷 14. DIFFERENCE QUESTIONS (INTERVIEW FAVORITE)

🔹 DELETE vs TRUNCATE vs DROP

DELETE TRUNCATE DROP
Row-wise All rows Table deleted
Can rollback Cannot rollback Cannot rollback

🔹 WHERE vs HAVING

 WHERE → before grouping

  HAVING → after grouping

🔹 PRIMARY KEY vs UNIQUE

 PK → one per table

 UNIQUE → multiple allowed

🔷 15. REAL-WORLD UNDERSTANDING (IMPORTANT)

👉 Think like a Data Analyst / Business Analyst

 Sales table → revenue analysis

 Customer table → segmentation
 Orders → trend analysis

🔷 FINAL STRATEGY (IMPORTANT FOR YOU)

Since you are preparing for:

👉 AKTU Exams + Data Analyst + Interviews

Focus deeply on:

✔ Normalization (theory + examples)

✔ Joins (with logic)
✔ Subqueries
✔ Transactions + ACID
✔ Indexing
✔ Window functions
✔ Real-world use cases

🔷 SECTION 1: DBMS THEORY (CORE – VERY IMPORTANT)

1. What is DBMS?

A system to store, manage, and retrieve data efficiently.

2. What is RDBMS?

A DBMS based on relational model (tables + relationships).

3. Difference between DBMS and RDBMS?

RDBMS supports relationships, normalization, constraints.

4. What is data abstraction?

Hiding internal details from users.

5. What are the levels of abstraction?

External, Conceptual, Internal.

6. What is data independence?

Ability to modify schema without affecting users.

7. Types of data independence?

Physical and Logical.

8. What is schema vs instance?

 Schema → structure
 Instance → actual data

9. What is ER model?

Graphical representation of database.

10. What is entity?

Real-world object (Student, Employee).

🔷 SECTION 2: KEYS & CONSTRAINTS

11. What is primary key?

Unique + NOT NULL identifier.

12. What is foreign key?

References another table’s primary key.

13. What is candidate key?

Possible primary keys.

14. What is super key?

Set of attributes uniquely identifying row.

15. What is composite key?

Multiple columns forming key.

16. Difference between primary and unique?

Primary → only one, unique → many allowed.

17. What is NOT NULL constraint?

Prevents null values.

18. What is CHECK constraint?

Applies condition on column.

🔷 SECTION 3: NORMALIZATION

19. What is normalization?

Process to reduce redundancy.

20. What is 1NF?

Atomic values, no repeating groups.

21. What is 2NF?

Remove partial dependency.

22. What is 3NF?

Remove transitive dependency.

23. What is BCNF?

Stronger 3NF.

24. What is denormalization?

Combining tables for performance.

🔷 SECTION 4: SQL BASICS

25. What is SQL?

Structured Query Language.

26. Types of SQL commands?

DDL, DML, DQL, DCL, TCL.

27. Difference between DELETE and TRUNCATE?

DELETE is row-wise, TRUNCATE removes all rows instantly.

28. What is SELECT?

Used to fetch data.

29. What is WHERE clause?

Filters records.

30. Difference between WHERE and HAVING?

WHERE before grouping, HAVING after.

🔷 SECTION 5: JOINS (VERY IMPORTANT)

31. What is JOIN?

Combining tables.

32. Types of JOIN?

INNER, LEFT, RIGHT, FULL.

33. What is INNER JOIN?

Returns matching rows.

34. What is LEFT JOIN?

All left + matched right.

35. What is SELF JOIN?

Table joins with itself.

36. Join vs Subquery?

Join is faster generally.

🔷 SECTION 6: AGGREGATE FUNCTIONS

37. What is COUNT()?

Counts rows.

38. What is AVG()?

Average value.

39. What is GROUP BY?

Groups rows.

40. What is HAVING?

Filters grouped data.

🔷 SECTION 7: SUBQUERIES

41. What is subquery?

Query inside query.

42. Types?

Single row, multi-row, correlated.

43. Correlated subquery?

Runs for each row.

🔷 SECTION 8: INDEXING

44. What is index?

Improves performance.

45. Types of index?

Clustered, Non-clustered.
46. Advantage?

Fast search.

47. Disadvantage?

Slower insert/update.

🔷 SECTION 9: TRANSACTIONS

48. What is transaction?

Group of SQL operations.

49. What is COMMIT?

Save changes.

50. What is ROLLBACK?

Undo changes.

51. What is SAVEPOINT?

Partial rollback.

🔷 SECTION 10: ACID PROPERTIES

52. Atomicity?

All or nothing.

53. Consistency?

Valid state.

54. Isolation?

No interference.

55. Durability?

Permanent storage.
🔷 SECTION 11: ADVANCED SQL

56. What is window function?

Works across rows.

57. What is RANK()?

Ranking with gaps.

58. What is DENSE_RANK()?

Ranking without gaps.

59. ROW_NUMBER()?

Unique sequence.

🔷 SECTION 12: VIEWS & PROCEDURES

60. What is view?

Virtual table.

61. What is stored procedure?

Precompiled SQL.

62. What is trigger?

Auto-executed SQL.

🔷 SECTION 13: INTERVIEW SQL PROBLEMS

63. Find second highest salary

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

64. Find duplicates

SELECT name, COUNT(*)
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;
65. Delete duplicates
DELETE e1 FROM employees e1
JOIN employees e2
ON [Link] > [Link] AND [Link] = [Link];

66. Top N records

SELECT * FROM employees
ORDER BY salary DESC LIMIT 3;

67. Employees > average salary

SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

68. Nth highest salary

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC LIMIT 1 OFFSET 2;

69. Running total

SELECT salary,
SUM(salary) OVER (ORDER BY id)
FROM employees;

70. Rank employees

SELECT name, RANK() OVER (ORDER BY salary DESC)
FROM employees;

🔷 SECTION 14: SCENARIO QUESTIONS (VERY IMPORTANT)

71. How to optimize slow query?

 Use index
 Avoid SELECT *
 Optimize joins

72. What causes deadlock?

Two transactions waiting on each other.

73. How to handle deadlock?

Rollback one transaction.

74. What is normalization vs denormalization?

 Normalize → reduce redundancy

 Denormalize → improve speed

🔷 SECTION 15: REAL BUSINESS QUESTIONS

75. Monthly sales analysis

SELECT MONTH(date), SUM(amount)
FROM sales GROUP BY MONTH(date);

76. Customer lifetime value

SELECT customer_id, SUM(amount)
FROM orders GROUP BY customer_id;

🔷 SECTION 16: RAPID FIRE (SHORT QUESTIONS)

77. What is schema? → Structure

78. What is tuple? → Row
79. What is attribute? → Column
80. What is cardinality? → Number of rows
81. What is degree? → Number of columns
82. What is NULL? → Missing value
83. What is constraint? → Rule
84. What is alias? → Temporary name
85. What is DISTINCT? → Unique values

🔷 SECTION 17: ADVANCED INTERVIEW QUESTIONS

86. Difference between OLTP and OLAP?

 OLTP → transactions
 OLAP → analysis

87. What is star schema?

Central fact table + dimension tables.

88. What is snowflake schema?

Normalized star schema.

89. What is fact table?

Stores measurable data.

90. What is dimension table?

Stores descriptive data.

🔷 SECTION 18: MORE PRACTICAL QUESTIONS

91. Find employees without department

SELECT * FROM employees e
LEFT JOIN dept d ON e.dept_id = [Link]
WHERE [Link] IS NULL;

92. Find highest salary per department

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

93. Find common records in two tables

SELECT name FROM A
INTERSECT
SELECT name FROM B;

🔷 SECTION 19: EDGE CASE QUESTIONS

94. Difference between UNION and UNION ALL?

 UNION removes duplicates

 UNION ALL keeps duplicates

95. What is COALESCE?

Returns first non-null value.

96. What is NULLIF?

Returns NULL if equal.

🔷 SECTION 20: FINAL INTERVIEW LEVEL

97. How to design database for e-commerce?

 Users
 Orders
 Products
  Payments

98. What is indexing strategy?

Index frequently searched columns.

99. What is query optimization?

Improving performance.

100. What is partitioning?

Dividing large table.

101. What is sharding?

Distributing database.

🔥 FINAL ADVICE (IMPORTANT)

For your preparation (AKTU + Data Analyst roles):

Focus MOST on:

✔ Joins
✔ Group By + Having
✔ Subqueries
✔ Window Functions
✔ Normalization
✔ Real-world queries

🔷 1. SAMPLE DATABASE (We’ll use this everywhere)

CREATE DATABASE company;
USE company;

CREATE TABLE department (

dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);

CREATE TABLE employees (

emp_id INT PRIMARY KEY,
name VARCHAR(50),
salary INT,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

INSERT INTO department VALUES

(1, 'IT'),
(2, 'HR'),
(3, 'Finance');

INSERT INTO employees VALUES

(101, 'Rahul', 50000, 1),
(102, 'Amit', 60000, 1),
(103, 'Neha', 45000, 2),
(104, 'Sara', 70000, 3);

🔷 2. PRIMARY KEY & FOREIGN KEY

📌 Primary Key

 Unique + NOT NULL

 Identifies each row

CREATE TABLE students (

id INT PRIMARY KEY,
name VARCHAR(50)
);

📌 Foreign Key

 Links tables

FOREIGN KEY (dept_id) REFERENCES department(dept_id)

👉 Ensures referential integrity

🔷 3. UPDATE QUERY (DETAILED)

📌 Syntax
UPDATE table_name
SET column = value
WHERE condition;

📌 Example
UPDATE employees
SET salary = 55000
WHERE emp_id = 101;
👉 Only updates Rahul’s salary

⚠️Without WHERE (Danger)

UPDATE employees SET salary = 50000;

👉 Updates ALL rows

🔷 4. DELETE QUERY

📌 Syntax
DELETE FROM table_name
WHERE condition;

📌 Example
DELETE FROM employees
WHERE emp_id = 104;

⚠️Without WHERE
DELETE FROM employees;

👉 Deletes all data

🔷 5. INNER JOIN (MOST IMPORTANT)

📌 Concept

Returns only matching records

📌 Query
SELECT [Link], [Link], d.dept_name
FROM employees e
INNER JOIN department d
ON e.dept_id = d.dept_id;

📌 Output
name salary dept_name
Rahul 50000 IT

👉 Only matched rows

🔷 6. LEFT JOIN

📌 Concept

Returns:
✔ All rows from LEFT table
✔ Matching rows from RIGHT

📌 Query
SELECT [Link], d.dept_name
FROM employees e
LEFT JOIN department d
ON e.dept_id = d.dept_id;

👉 If no match → NULL

🔷 7. RIGHT JOIN

📌 Concept

✔ All rows from RIGHT table

✔ Matching from LEFT

SELECT [Link], d.dept_name

FROM employees e
RIGHT JOIN department d
ON e.dept_id = d.dept_id;

🔷 8. CROSS JOIN

📌 Concept

👉 Cartesian Product (All combinations)

SELECT [Link], d.dept_name
FROM employees e
CROSS JOIN department d;

👉 If 4 employees × 3 departments = 12 rows

🔷 9. MULTIPLE TABLE JOIN (VERY IMPORTANT)

📌 Add Another Table

CREATE TABLE projects (
proj_id INT,
emp_id INT,
proj_name VARCHAR(50)
);

INSERT INTO projects VALUES

(1, 101, 'AI'),
(2, 102, 'Web'),
(3, 103, 'HR System');

📌 Join 3 Tables
SELECT [Link], d.dept_name, p.proj_name
FROM employees e
INNER JOIN department d
ON e.dept_id = d.dept_id
INNER JOIN projects p
ON e.emp_id = p.emp_id;

👉 Combines all related data

🔷 10. EXISTS (IMPORTANT)

📌 Concept

Checks if subquery returns rows

SELECT name
FROM employees e
WHERE EXISTS (
SELECT 1
 FROM department d
WHERE e.dept_id = d.dept_id
);

👉 Returns employees having valid department

🔷 11. NOT EXISTS

SELECT name
FROM employees e
WHERE NOT EXISTS (
SELECT 1
FROM projects p
WHERE e.emp_id = p.emp_id
);

👉 Employees with NO projects

🔷 12. GROUP BY (VERY IMPORTANT)

📌 Concept

Groups rows for aggregation

SELECT dept_id, AVG(salary)

FROM employees
GROUP BY dept_id;

👉 Output:
| dept_id | avg_salary |

🔷 13. HAVING CLAUSE

📌 Concept

Filters grouped data

SELECT dept_id, AVG(salary)

FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 50000;
👉 Filters after grouping

🔷 14. WHERE vs HAVING (INTERVIEW)

WHERE HAVING
Before grouping After grouping
Works on rows Works on groups

🔷 15. COMBINED REAL QUERY (INTERVIEW LEVEL)

SELECT d.dept_name, COUNT(e.emp_id) AS total_emp, AVG([Link]) AS avg_salary
FROM employees e
INNER JOIN department d
ON e.dept_id = d.dept_id
GROUP BY d.dept_name
HAVING AVG([Link]) > 50000;

👉 Combines:
✔ JOIN
✔ GROUP BY
✔ HAVING

SECTION 1: DBMS & MySQL

Complete Theory — Basic to Advanced

1.1 Database & DBMS Fundamentals

What is a Database?
A database is an organized collection of structured data stored electronically. It allows data to be easily
accessed, managed, modified, updated, controlled, and organized.

What is DBMS?
A Database Management System (DBMS) is software that enables users to create, maintain, and manage
databases. It acts as an interface between users/applications and the database.

Types of DBMS
• Hierarchical DBMS — Data organized in tree structure (IBM IMS)
• Network DBMS — Records linked using pointers (IDMS)
• Relational DBMS (RDBMS) — Data in tables with relations (MySQL, PostgreSQL, Oracle)
 • Object-Oriented DBMS — Data stored as objects (db4o)
• NoSQL DBMS — Non-relational, flexible schema (MongoDB, Cassandra, Redis)

RDBMS vs DBMS

Uses tables with rows & columns, enforces relationships via foreign keys, supports SQL,
RDBMS
ensures ACID properties. Examples: MySQL, PostgreSQL, Oracle, SQL Server.

Broader term, may not support table-based storage or relationships. No normalization

DBMS
enforced. Suitable for smaller-scale data management.

1.2 ACID Properties

ACID ensures reliable processing of database transactions:

Property Full Form Meaning
Atomicity All-or-Nothing Transaction fully completes or fully rolls back. No
partial execution.
Consistency Valid State Always Data remains consistent before and after transaction.
Constraints maintained.
Isolation Concurrent Safety Transactions execute independently. Intermediate
states hidden from others.
Durability Permanent Commit Once committed, data persists even after system
failure.

1.3 Keys in Database

Types of Keys — Quick Reference

Key Type Definition Example

Primary Key Uniquely identifies each row. NOT NULL, emp_id in Employees
UNIQUE.
Foreign Key Links two tables. References Primary Key dept_id in Employees → Dept
of another table.
Candidate Key All attributes that can serve as Primary emp_id, email (both unique)
Key.
Super Key Set of attributes that uniquely identifies a {emp_id}, {emp_id, name}
row.
Composite Key Primary Key made of two or more (order_id, product_id)
columns.
Unique Key Ensures uniqueness but allows one NULL. email column
 Alternate Key Candidate keys not chosen as Primary email (if emp_id is PK)
Key.
Surrogate Key System-generated artificial key (auto- AUTO_INCREMENT id
increment).
Natural Key Key from real-world meaningful SSN, Aadhar number
attribute.

1.4 Normalization

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

Normal Forms
• 1NF (First Normal Form) — Each column holds atomic (indivisible) values. No repeating groups. All
values in a column are of the same type.
• 2NF (Second Normal Form) — Must be in 1NF + No partial dependency (every non-key attribute depends
on the WHOLE primary key). Applies to tables with composite keys.
• 3NF (Third Normal Form) — Must be in 2NF + No transitive dependency (non-key attribute must not
depend on another non-key attribute).
• BCNF (Boyce-Codd Normal Form) — Stronger version of 3NF. For every functional dependency X→Y, X
must be a super key.
• 4NF — No multi-valued dependencies.
• 5NF — No join dependencies.

📝 For most interviews: know 1NF, 2NF, 3NF, BCNF deeply with examples. 4NF and 5NF
Interview are rarely asked but mention awareness.
Tip

Denormalization
Intentionally introducing redundancy by merging tables to improve read performance. Used in data warehouses
and analytics systems (OLAP). Trade-off: faster reads vs. more storage and update anomalies.

1.5 ER Diagram (Entity-Relationship)

Components
• Entity — Real-world object (Rectangle). e.g., Employee, Student, Product
• Attribute — Property of entity (Ellipse). e.g., name, age, salary
• Relationship — Association between entities (Diamond). e.g., works_in, buys
• Weak Entity — Depends on another entity for identification (Double Rectangle)
• Multivalued Attribute — Can have multiple values (Double Ellipse). e.g., phone_numbers
• Derived Attribute — Derived from other attribute (Dashed Ellipse). e.g., age from DOB
• Composite Attribute — Made of sub-attributes. e.g., name → first_name, last_name
Cardinality Types
• One-to-One (1:1) — One entity instance relates to one instance of another. e.g., Person ↔ Passport
• One-to-Many (1:N) — One entity relates to many instances. e.g., Department → Employees
• Many-to-Many (M:N) — Many instances relate to many instances. e.g., Students ↔ Courses

1.6 Transactions & Concurrency Control

Transaction States
• Active — Transaction is being executed
• Partially Committed — Last operation executed, not yet committed
• Committed — Transaction successfully completed and saved
• Failed — Error occurred, cannot continue
• Aborted — Transaction rolled back to consistent state

Concurrency Problems
• Dirty Read — Reading uncommitted data from another transaction
• Non-Repeatable Read — Reading same row twice gives different results (due to another transaction
updating it)
• Phantom Read — New rows appear in result set due to another transaction inserting rows
• Lost Update — Two transactions update same row; one overwrites the other

Transaction Isolation Levels

Isolation Level Dirty Read Non-Repeatable Phantom Read

Read
READ UNCOMMITTED Yes Yes Yes
READ COMMITTED No Yes Yes
REPEATABLE READ No No Yes
SERIALIZABLE No No No

1.7 Indexing

An index is a data structure that improves the speed of data retrieval operations on a table at the cost of
additional writes and storage space.

Types of Indexes
• Primary Index — Created on ordered data file using primary key
• Clustered Index — Rows are physically sorted by indexed column. Only one per table.
• Non-Clustered Index — Separate structure pointing to data rows. Multiple per table.
• Unique Index — Ensures no duplicate values in the indexed column
• Composite Index — Index on two or more columns
• Full-Text Index — For searching large text data (LIKE %keyword%)
 • Spatial Index — For geographic data
⚠️When Avoid indexing small tables, columns with low cardinality (few distinct values), columns
NOT to rarely used in WHERE/JOIN, or tables with heavy writes (indexes slow down
Index INSERT/UPDATE/DELETE).

1.8 Joins — Complete Reference

A JOIN combines rows from two or more tables based on a related column.
Join Type Returns Use Case
INNER JOIN Only matching rows from both Common records in both tables
tables
LEFT JOIN (LEFT OUTER) All rows from left + matching All records from left table
right (NULL if no match)
RIGHT JOIN (RIGHT All rows from right + matching All records from right table
OUTER) left (NULL if no match)
FULL OUTER JOIN All rows from both tables (NULL All records from both tables
where no match)
CROSS JOIN Cartesian product (all All combinations of two sets
combinations)
SELF JOIN Table joined with itself Hierarchical/recursive relationships
NATURAL JOIN Auto-join on same-named Simplified join (use carefully)
columns

1.9 SQL Subqueries & Views

Types of Subqueries
• Scalar Subquery — Returns a single value. Used in SELECT, WHERE
• Row Subquery — Returns a single row with multiple columns
• Column Subquery — Returns a single column with multiple rows. Used with IN, ANY, ALL
• Table Subquery — Returns multiple rows and columns. Used in FROM clause (derived table)
• Correlated Subquery — References outer query. Executed once per outer row. Slower.

Views
A view is a virtual table based on a SELECT query. It does not store data physically (unless materialized).
• Simple View — Based on single table, no GROUP BY, no aggregate functions. Can perform DML.
• Complex View — Based on multiple tables, may have joins, aggregates. DML usually restricted.
• Materialized View — Stores query results physically. Refreshed periodically. Faster reads.
✅ Benefits Security (hide sensitive columns), Simplicity (hide complexity), Reusability (use view
of Views across queries), Abstraction (insulate apps from schema changes).
1.10 Stored Procedures, Functions & Triggers

Stored Procedures
Precompiled SQL code stored in database. Can accept parameters and return results. Used to encapsulate
business logic.
• Supports IN, OUT, INOUT parameters
• Can contain DML (INSERT, UPDATE, DELETE)
• Improves performance (cached execution plan)

Functions (User-Defined Functions)

Must return a value. Used in SELECT statements. Cannot perform transactions. More restrictive than
procedures.

Triggers
Automatically executed when a specified event (INSERT, UPDATE, DELETE) occurs on a table.
• BEFORE trigger — Executes before the DML operation
• AFTER trigger — Executes after the DML operation
• INSTEAD OF trigger — Replaces the DML operation (used with views in some DBMS)

1.11 Database Storage & File Organization

File Organization Types

• Heap File — Records stored in insertion order. No sorting. Fastest insert, slowest search.
• Sequential File — Records sorted by key. Good for range queries.
• Hashing — Records stored based on hash function. Fastest for equality search.
• B-Tree / B+ Tree — Balanced tree structure. Standard for database indexes. O(log n) search.

1.12 CAP Theorem (NoSQL Basics)

CAP Theorem states a distributed system can only guarantee two of three: Consistency, Availability, Partition
Tolerance.
• Consistency — Every read receives the most recent write
• Availability — Every request receives a response (not necessarily the latest data)
• Partition Tolerance — System continues despite network partitions
📌 Key RDBMS typically chooses CA (Consistency + Availability). NoSQL systems often choose
Point AP (Availability + Partition Tolerance) or CP based on use case.

1.13 Data Warehouse & OLAP vs OLTP

Aspect OLTP OLAP

 Purpose Day-to-day transactions Analysis and reporting
Operations INSERT, UPDATE, DELETE SELECT, Aggregations
Data Volume GBs TBs to PBs
Query Type Simple, fast queries Complex, long-running queries
Normalization Highly normalized Denormalized (Star/Snowflake
schema)
Users Operational users (thousands) Analysts/BI users (hundreds)
Examples Banking, ERP, CRM Data Warehouses, BI Tools

Star vs Snowflake Schema

• Star Schema — Central fact table connected to denormalized dimension tables. Faster queries. More
storage.
• Snowflake Schema — Dimension tables further normalized into sub-dimensions. Less storage. More
complex joins.
• Galaxy/Fact Constellation — Multiple fact tables sharing dimension tables.
 SECTION 2: MySQL — All Clauses, Commands & Concepts
Complete SQL Reference Guide

2.1 SQL Command Categories

Category Full Name Commands Purpose

DDL Data Definition Language CREATE, ALTER, DROP, Define database
TRUNCATE, RENAME structure
DML Data Manipulation INSERT, UPDATE, DELETE, Manipulate data in tables
Language MERGE
DQL Data Query Language SELECT Retrieve data from tables
DCL Data Control Language GRANT, REVOKE Control access
permissions
TCL Transaction Control COMMIT, ROLLBACK, Manage transactions
Language SAVEPOINT

2.2 SELECT Statement — Full Syntax

SELECT [DISTINCT] column1, column2, ...

FROM table_name
[JOIN other_table ON condition]
[WHERE condition]
[GROUP BY column]
[HAVING condition]
[ORDER BY column [ASC|DESC]]
[LIMIT n OFFSET m];

SQL Execution Order (Critical for Interviews!)

FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY

⚡ Order
→ LIMIT

2.3 WHERE Clause & Operators

Comparison Operators
• = (Equal), != or <> (Not Equal), > (Greater), < (Less), >= (Greater or Equal), <= (Less or Equal)

Logical Operators
• AND — Both conditions must be true
 • OR — At least one condition must be true
• NOT — Negates condition

Special Operators
• BETWEEN — WHERE salary BETWEEN 30000 AND 80000
• IN — WHERE dept IN ('HR', 'IT', 'Finance')
• LIKE — Pattern matching: % (any chars), _ (one char)
• IS NULL / IS NOT NULL — Check null values
• EXISTS — Check if subquery returns any rows
• ANY / ALL — Compare with any/all values in subquery
-- LIKE examples
WHERE name LIKE 'A%' -- starts with A
WHERE name LIKE '%son' -- ends with son
WHERE name LIKE '_a%' -- second char is a
WHERE name LIKE '%kumar%' -- contains kumar

2.4 GROUP BY & HAVING

GROUP BY groups rows with same values. HAVING filters grouped results (like WHERE but for groups).
SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING COUNT(*) > 5 -- Filter groups with more than 5 employees
ORDER BY avg_sal DESC;

🔑 Key WHERE filters individual rows BEFORE grouping. HAVING filters groups AFTER GROUP
Difference BY. You cannot use aggregate functions in WHERE.

2.5 Aggregate Functions

Function Description Example

COUNT(*) Counts all rows including NULLs COUNT(*) → total rows
COUNT(col) Counts non-NULL values in column COUNT(email) → non-null
emails
SUM(col) Sum of numeric column values SUM(salary)
AVG(col) Average of numeric column values AVG(salary)
MAX(col) Maximum value in column MAX(salary)
MIN(col) Minimum value in column MIN(hire_date)
GROUP_CONCAT Concatenates values (MySQL) GROUP_CONCAT(name
SEPARATOR ',')
 STD/STDDEV Standard deviation STD(salary)
VARIANCE Statistical variance VARIANCE(sales)

2.6 String Functions

Function Syntax Result

UPPER UPPER('hello') 'HELLO'
LOWER LOWER('WORLD') 'world'
LENGTH LENGTH('MySQL') 5
CHAR_LENGTH CHAR_LENGTH('MySQL') 5 (counts chars)
SUBSTRING SUBSTRING('Hello',2,3) 'ell'
SUBSTR SUBSTR('Hello',2,3) 'ell'
LEFT LEFT('Hello',3) 'Hel'
RIGHT RIGHT('Hello',3) 'llo'
TRIM TRIM(' hello ') 'hello'
LTRIM LTRIM(' hi') 'hi'
RTRIM RTRIM('hi ') 'hi'
REPLACE REPLACE('abc','b','X') 'aXc'
CONCAT CONCAT('Hi',' ','World') 'Hi World'
CONCAT_WS CONCAT_WS(',','a','b','c') 'a,b,c'
INSTR INSTR('hello','ll') 3
LOCATE LOCATE('l','hello') 3
LPAD LPAD('5',3,'0') '005'
RPAD RPAD('5',3,'0') '500'
REVERSE REVERSE('hello') 'olleh'
REPEAT REPEAT('ab',3) 'ababab'
STRCMP STRCMP('a','b') -1 (a < b)
FORMAT FORMAT(12345.678,2) '12,345.68'

2.7 Numeric Functions

Function Description Example

 ABS(n) Absolute value ABS(-5) → 5
CEIL/CEILING(n) Round up to integer CEIL(4.2) → 5
FLOOR(n) Round down to integer FLOOR(4.8) → 4
ROUND(n,d) Round to d decimal places ROUND(4.567,2) → 4.57
TRUNCATE(n,d) Truncate to d decimal places TRUNCATE(4.567,2) → 4.56
MOD(n,m) Modulo (remainder) MOD(10,3) → 1
POWER(n,m) n raised to power m POWER(2,8) → 256
SQRT(n) Square root SQRT(16) → 4
RAND() Random number 0-1 RAND() → 0.742...
SIGN(n) Sign: -1, 0, or 1 SIGN(-5) → -1
GREATEST(...) Maximum of values GREATEST(3,7,2) → 7
LEAST(...) Minimum of values LEAST(3,7,2) → 2

2.8 Date & Time Functions

Function Description Example

NOW() Current date and time 2024-01-15 14:30:00
CURDATE() Current date only 2024-01-15
CURTIME() Current time only 14:30:00
DATE(datetime) Extract date part DATE('2024-01-15 14:30')
TIME(datetime) Extract time part TIME('2024-01-15 14:30')
YEAR(date) Extract year YEAR('2024-01-15') → 2024
MONTH(date) Extract month (1-12) MONTH('2024-01-15') → 1
DAY/DAYOFMONTH Extract day DAY('2024-01-15') → 15
HOUR/MINUTE/SECOND Extract time parts HOUR('14:30:45') → 14
DAYNAME(date) Name of weekday DAYNAME('2024-01-15') → Monday
MONTHNAME(date) Name of month MONTHNAME('2024-01-15') →
January
WEEKDAY(date) Weekday index (0=Mon) WEEKDAY('2024-01-15') → 0
DATE_FORMAT Format date string DATE_FORMAT(date,'%d-%m-%Y')
DATE_ADD Add interval to date DATE_ADD(date, INTERVAL 7 DAY)
DATE_SUB Subtract interval DATE_SUB(date, INTERVAL 1
 MONTH)
DATEDIFF(d1,d2) Days between two dates DATEDIFF('2024-12-31','2024-01-
01') → 365
TIMESTAMPDIFF Difference in any unit TIMESTAMPDIFF(MONTH,start,end)
STR_TO_DATE String to date STR_TO_DATE('15-01-2024','%d-
%m-%Y')
UNIX_TIMESTAMP Unix timestamp UNIX_TIMESTAMP(date)
FROM_UNIXTIME Timestamp to date FROM_UNIXTIME(1705315200)
LAST_DAY Last day of month LAST_DAY('2024-02-01') → 2024-02-
29

2.9 Window Functions (Advanced — High Priority!)

Window functions perform calculations across a set of rows related to the current row without collapsing
results.
function_name() OVER (
[PARTITION BY column] -- Divide into groups
[ORDER BY column] -- Sort within group
[ROWS/RANGE frame] -- Define row range
)

Function Category Description

ROW_NUMBER() Ranking Unique sequential number per row within partition
RANK() Ranking Rank with gaps for ties (1,1,3,4)
DENSE_RANK() Ranking Rank without gaps for ties (1,1,2,3)
NTILE(n) Ranking Divides rows into n equal buckets
PERCENT_RANK() Ranking Relative rank as percentage (0 to 1)
CUME_DIST() Ranking Cumulative distribution of value
LAG(col, n) Navigation Value of col from n rows before current
LEAD(col, n) Navigation Value of col from n rows after current
FIRST_VALUE(col) Navigation First value in window frame
LAST_VALUE(col) Navigation Last value in window frame
NTH_VALUE(col,n) Navigation Nth value in window frame
SUM() OVER() Aggregate Running/cumulative sum
AVG() OVER() Aggregate Moving/running average
 COUNT() OVER() Aggregate Running count
MAX() OVER() Aggregate Running maximum
MIN() OVER() Aggregate Running minimum

-- Window Function Examples

SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk,
LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary) AS prev_sal,
SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;

2.10 CTEs (Common Table Expressions)

A CTE is a temporary named result set defined with WITH clause. More readable than subqueries. Can be
recursive.
-- Simple CTE
WITH high_earners AS (
SELECT emp_id, name, salary
FROM employees
WHERE salary > 70000
)
SELECT * FROM high_earners ORDER BY salary DESC;

-- Recursive CTE (Hierarchy)

WITH RECURSIVE emp_hierarchy AS (
SELECT emp_id, name, manager_id, 1 AS level
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, [Link], e.manager_id, [Link]+1
FROM employees e
JOIN emp_hierarchy eh ON e.manager_id = eh.emp_id
)
SELECT * FROM emp_hierarchy;

2.11 Conditional Expressions

CASE Statement
-- Simple CASE
SELECT name, salary,
CASE
WHEN salary >= 100000 THEN 'Executive'
 WHEN salary >= 70000 THEN 'Senior'
WHEN salary >= 40000 THEN 'Mid-Level'
ELSE 'Junior'
END AS salary_grade
FROM employees;

IF, IFNULL, NULLIF, COALESCE

• IF(condition, true_val, false_val) — Inline conditional
• IFNULL(col, default) — Return default if col is NULL
• NULLIF(expr1, expr2) — Return NULL if expr1=expr2, else expr1
• COALESCE(v1,v2,...) — Return first non-NULL value
SELECT COALESCE(phone, email, 'No Contact') AS contact FROM users;
SELECT NULLIF(score, 0) AS score FROM tests; -- NULL when score=0

2.12 DDL Commands

-- CREATE TABLE
CREATE TABLE employees (
emp_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL,
department VARCHAR(50),
salary DECIMAL(10,2) DEFAULT 0.00,
hire_date DATE NOT NULL,
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);

-- ALTER TABLE
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2);
ALTER TABLE employees DROP COLUMN phone;
ALTER TABLE employees RENAME COLUMN hire_date TO joining_date;
ALTER TABLE employees ADD INDEX idx_dept (department);

-- DROP vs TRUNCATE vs DELETE

DROP TABLE employees; -- Removes table + data + structure
TRUNCATE TABLE employees; -- Removes all data, keeps structure, resets AUTO_INCREMENT
DELETE FROM employees; -- Removes all data, keeps structure, transactional

2.13 MySQL Data Types

Category Type Description

 Integer TINYINT 1 byte: -128 to 127
Integer SMALLINT 2 bytes: -32768 to 32767
Integer MEDIUMINT 3 bytes
Integer INT/INTEGER 4 bytes: ~±2 billion
Integer BIGINT 8 bytes: ~±9 quintillion
Decimal FLOAT 4 bytes approximate
Decimal DOUBLE 8 bytes approximate
Decimal DECIMAL(p,s) Exact; p=total digits, s=decimal places
String CHAR(n) Fixed length, max 255
String VARCHAR(n) Variable length, max 65535
String TEXT Up to 65535 chars
String LONGTEXT Up to 4GB
Date DATE YYYY-MM-DD
Date DATETIME YYYY-MM-DD HH:MM:SS
Date TIMESTAMP UTC stored; auto-converts timezone
Date TIME HH:MM:SS
Date YEAR 4-digit year
Binary BLOB Binary Large Object
Other BOOLEAN 0=FALSE, 1=TRUE (TINYINT alias)
Other ENUM Limited set of values
Other SET Multiple values from set
Other JSON JSON document storage

2.14 Constraints

Constraint Description Example

PRIMARY KEY Unique + NOT NULL identifier emp_id INT PRIMARY KEY
FOREIGN KEY References PK in another table FOREIGN KEY (dept_id) REFERENCES
dept(id)
UNIQUE All values must be distinct (allows email VARCHAR(150) UNIQUE
one NULL)
NOT NULL Column cannot have NULL values name VARCHAR(100) NOT NULL
DEFAULT Default value if none provided salary DECIMAL DEFAULT 0
CHECK Validates condition (MySQL 8.0+) CHECK (salary > 0)
AUTO_INCREMENT Auto-generates sequential integers id INT AUTO_INCREMENT
 SECTION 3: MySQL — 100+ Practice Queries
Basic → Intermediate → Advanced Problem Solving

3.0 Practice Database Setup

Use these tables for all practice queries below:

CREATE DATABASE company_db; USE company_db;

CREATE TABLE departments (

dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(50) NOT NULL,
location VARCHAR(50)
);

CREATE TABLE employees (

emp_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE,
department VARCHAR(50),
salary DECIMAL(10,2),
hire_date DATE,
manager_id INT,
city VARCHAR(50),
FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);

CREATE TABLE orders (

order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT, product_id INT,
quantity INT, price DECIMAL(10,2),
order_date DATE, status VARCHAR(20)
);

CREATE TABLE products (

product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(100),
category VARCHAR(50),
price DECIMAL(10,2), stock INT
);

CREATE TABLE customers (

customer_id INT PRIMARY KEY AUTO_INCREMENT,
customer_name VARCHAR(100),
email VARCHAR(150), city VARCHAR(50),
 join_date DATE
);

CREATE TABLE sales (

sale_id INT PRIMARY KEY AUTO_INCREMENT,
emp_id INT, region VARCHAR(50),
sale_amount DECIMAL(10,2), sale_date DATE,
FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);

3.1 Basic Queries (Q1–Q35)

Covers: SELECT, WHERE, ORDER BY, LIMIT, basic aggregates, string/date functions,
💡 Level
NULL handling, INSERT, UPDATE, DELETE.

Q1. Retrieve all employees

SELECT * FROM employees;

Q2. Select specific columns

SELECT emp_id, name, salary FROM employees;

Q3. Unique/Distinct departments

SELECT DISTINCT department FROM employees;

Q4. Employees with salary > 60000

SELECT name, salary FROM employees WHERE salary > 60000;

Q5. Sort by salary descending

SELECT name, salary FROM employees ORDER BY salary DESC;

Q6. Top 5 highest paid employees

SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 5;

Q7. Employees in IT department

SELECT * FROM employees WHERE department = 'IT';

Q8. Employees with NULL email

SELECT name FROM employees WHERE email IS NULL;
Q9. Employees hired in 2023
SELECT name, hire_date FROM employees WHERE YEAR(hire_date) = 2023;

Q10. Count total employees

SELECT COUNT(*) AS total_employees FROM employees;

Q11. Average salary of all employees

SELECT AVG(salary) AS avg_salary FROM employees;

Q12. Maximum and minimum salary

SELECT MAX(salary) AS max_sal, MIN(salary) AS min_sal FROM employees;

Q13. Total payroll cost

SELECT SUM(salary) AS total_payroll FROM employees;

Q14. Employees with name starting with 'A'

SELECT name FROM employees WHERE name LIKE 'A%';

Q15. Employees in IT or HR department

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

Q16. Salary between 40000 and 80000

SELECT name, salary FROM employees WHERE salary BETWEEN 40000 AND 80000;

Q17. Count employees per department

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

Q18. Average salary per department

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

Q19. Departments with more than 3 employees

SELECT department, COUNT(*) AS cnt FROM employees GROUP BY department HAVING COUNT(*) > 3;

Q20. Total orders per customer

SELECT customer_id, COUNT(*) AS total_orders FROM orders GROUP BY customer_id;

Q21. Format employee name to uppercase

SELECT UPPER(name) AS upper_name FROM employees;
Q22. First 3 characters of name
SELECT name, LEFT(name,3) AS short_name FROM employees;

Q23. Length of each name

SELECT name, LENGTH(name) AS name_len FROM employees ORDER BY name_len DESC;

Q24. Replace NULL city with 'Unknown'

SELECT name, COALESCE(city,'Unknown') AS city FROM employees;

Q25. Days since hire date

SELECT name, DATEDIFF(CURDATE(), hire_date) AS days_worked FROM employees;

Q26. Extract month from hire_date

SELECT name, MONTH(hire_date) AS hire_month FROM employees;

Q27. Add 30 days to order_date

SELECT order_id, DATE_ADD(order_date, INTERVAL 30 DAY) AS due_date FROM orders;

Q28. Concatenate first+last name (split by space)

SELECT CONCAT(LEFT(name, INSTR(name,' ')-1),' ',SUBSTRING(name, INSTR(name,' ')+1)) AS full_name FROM
employees;

Q29. Insert a new employee

INSERT INTO employees (name,email,department,salary,hire_date) VALUES ('Ananya
Sharma','ananya@[Link]','IT',75000,'2024-01-15');

Q30. Update salary of one employee

UPDATE employees SET salary = 80000 WHERE emp_id = 1;

Q31. Delete employees with no email

DELETE FROM employees WHERE email IS NULL;

Q32. Get 2nd page of results (10 per page)

SELECT * FROM employees ORDER BY emp_id LIMIT 10 OFFSET 10;

Q33. Revenue from all orders

SELECT SUM(quantity * price) AS total_revenue FROM orders;
Q34. Orders placed today
SELECT * FROM orders WHERE order_date = CURDATE();

Q35. Products with low stock (<10)

SELECT product_name, stock FROM products WHERE stock < 10 ORDER BY stock ASC;
3.2 Intermediate Queries (Q36–Q75)

Covers: JOINs, Subqueries, CASE, GROUP BY with HAVING, String manipulation, Date
💡 Level
logic, Multiple conditions, Aggregates with filters.

Q36. Employee names with department location (JOIN)

SELECT [Link], [Link], [Link]
FROM employees e
INNER JOIN departments d ON [Link] = d.dept_name;

Q37. All employees including those without department (LEFT JOIN)

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

Q38. Departments with no employees (RIGHT JOIN / NOT IN)

SELECT d.dept_name FROM departments d
LEFT JOIN employees e ON d.dept_name = [Link]
WHERE e.emp_id IS NULL;

Q39. Employees who are also managers

SELECT DISTINCT [Link] AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id;

Q40. Customer with their total order value

SELECT c.customer_name, SUM([Link] * [Link]) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spent DESC;

Q41. Second highest salary (Subquery)

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

Q42. Nth highest salary (generalized)

-- For Nth highest (e.g., 3rd highest)
SELECT salary FROM employees
ORDER BY salary DESC
 LIMIT 1 OFFSET 2; -- OFFSET = N-1

Q43. Employees earning above dept average

SELECT name, department, salary
FROM employees e
WHERE salary > (
SELECT AVG(salary) FROM employees
WHERE department = [Link]
);

Q44. Duplicate email addresses

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

Q45. Delete duplicate rows keeping one

DELETE e1 FROM employees e1
INNER JOIN employees e2
WHERE e1.emp_id > e2.emp_id AND [Link] = [Link];

Q46. Classify employees by salary (CASE)

SELECT name, salary,
CASE
WHEN salary >= 100000 THEN 'Executive'
WHEN salary >= 70000 THEN 'Senior'
WHEN salary >= 40000 THEN 'Mid-Level'
ELSE 'Junior'
END AS grade
FROM employees;

Q47. Monthly revenue trend

SELECT DATE_FORMAT(order_date,'%Y-%m') AS month,
SUM(quantity * price) AS monthly_revenue
FROM orders
GROUP BY month
ORDER BY month;

Q48. Products never ordered

SELECT p.product_name FROM products p
WHERE p.product_id NOT IN (
SELECT DISTINCT product_id FROM orders
);
Q49. Top 3 products by revenue
SELECT p.product_name,
SUM([Link] * [Link]) AS revenue
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY p.product_id, p.product_name
ORDER BY revenue DESC
LIMIT 3;

Q50. Employees hired in last 6 months

SELECT name, hire_date FROM employees
WHERE hire_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

Q51. Percentage of each dept in total headcount

SELECT department,
COUNT(*) AS dept_count,
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM employees), 2) AS pct
FROM employees
GROUP BY department;

Q52. Rolling 3-month average sales

SELECT sale_date, sale_amount,
AVG(sale_amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rolling_avg
FROM sales;

Q53. Employees with same salary as another

SELECT DISTINCT [Link], [Link]
FROM employees e1
JOIN employees e2 ON [Link] = [Link]
AND e1.emp_id != e2.emp_id;

Q54. City with most customers

SELECT city, COUNT(*) AS cnt
FROM customers
GROUP BY city
ORDER BY cnt DESC
LIMIT 1;
Q55. Orders in last 30 days per status
SELECT status, COUNT(*) AS cnt, SUM(quantity * price) AS revenue
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY status;

Q56. Pivot: Sales by region per month

SELECT region,
SUM(CASE WHEN MONTH(sale_date)=1 THEN sale_amount ELSE 0 END) AS Jan,
SUM(CASE WHEN MONTH(sale_date)=2 THEN sale_amount ELSE 0 END) AS Feb,
SUM(CASE WHEN MONTH(sale_date)=3 THEN sale_amount ELSE 0 END) AS Mar
FROM sales
GROUP BY region;

Q57. Customer retention: repeat buyers

SELECT customer_id, COUNT(DISTINCT order_date) AS order_days
FROM orders
GROUP BY customer_id
HAVING COUNT(DISTINCT order_id) > 1;

Q58. Average order value per customer

SELECT customer_id,
ROUND(AVG(quantity * price), 2) AS avg_order_value
FROM orders
GROUP BY customer_id
ORDER BY avg_order_value DESC;

Q59. Employees without a manager

SELECT name FROM employees WHERE manager_id IS NULL;

Q60. Manager with most direct reports

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

Q61. Extract domain from email

SELECT email,
SUBSTRING(email, INSTR(email,'@')+1) AS domain
FROM employees;
Q62. Format salary with comma separator
SELECT name, FORMAT(salary, 2) AS formatted_salary FROM employees;

Q63. Employees hired same month as birth month (using hire_date)

SELECT name, hire_date FROM employees
WHERE MONTH(hire_date) = MONTH(CURDATE());

Q64. Year-over-year revenue growth

SELECT
YEAR(order_date) AS yr,
SUM(quantity*price) AS revenue,
LAG(SUM(quantity*price)) OVER (ORDER BY YEAR(order_date)) AS prev_year,
ROUND((SUM(quantity*price) - LAG(SUM(quantity*price)) OVER (ORDER BY YEAR(order_date)))
/ LAG(SUM(quantity*price)) OVER (ORDER BY YEAR(order_date)) * 100, 2) AS growth_pct
FROM orders
GROUP BY YEAR(order_date);

Q65. Customers with orders in every month (2024)

SELECT customer_id FROM orders
WHERE YEAR(order_date) = 2024
GROUP BY customer_id
HAVING COUNT(DISTINCT MONTH(order_date)) = 12;

Q66. Products with above-average price in their category

SELECT product_name, category, price FROM products p
WHERE price > (
SELECT AVG(price) FROM products
WHERE category = [Link]
);

Q67. Get first order date per customer

SELECT customer_id,
MIN(order_date) AS first_order
FROM orders
GROUP BY customer_id;

Q68. Multi-table join: order details with names

SELECT c.customer_name, p.product_name,
[Link], [Link], o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;
Q69. UPDATE salary increase 10% for IT dept
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'IT';

Q70. Conditional update: give bonus based on performance

UPDATE employees
SET salary = salary + CASE
WHEN department='IT' THEN 5000
WHEN department='Sales' THEN 3000
ELSE 1000
END;

Q71. CROSS JOIN: all product-customer combinations

SELECT c.customer_name, p.product_name
FROM customers c
CROSS JOIN products p;

Q72. NATURAL JOIN example

SELECT * FROM employees NATURAL JOIN departments;

Q73. Create a view for active orders

CREATE VIEW active_orders AS
SELECT * FROM orders WHERE status = 'Active';
-- Query view:
SELECT * FROM active_orders;

Q74. Create and call stored procedure

DELIMITER //
CREATE PROCEDURE GetDeptEmployees(IN dept VARCHAR(50))
BEGIN
SELECT name, salary FROM employees
WHERE department = dept;
END //
DELIMITER ;
CALL GetDeptEmployees('IT');

Q75. String aggregation: list employees per dept

SELECT department,
GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS employees
FROM employees
GROUP BY department;
3.3 Advanced Queries (Q76–Q115)

Covers: CTEs, Window Functions, Recursive queries, Complex analytics, Performance

💡 Level
optimization, Real-world business problems, Advanced subqueries.

Q76. Rank employees by salary within each department (DENSE_RANK)

SELECT name, department, salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

Q77. Top earner per department (using CTE + RANK)

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

Q78. Running total of sales

SELECT sale_id, emp_id, sale_amount, sale_date,
SUM(sale_amount) OVER (ORDER BY sale_date, sale_id) AS running_total
FROM sales;

Q79. Previous month's salary comparison (LAG)

SELECT name, salary,
LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY hire_date) AS prev_emp_salary,
salary - LAG(salary,1,0) OVER (PARTITION BY department ORDER BY hire_date) AS diff
FROM employees;

Q80. Employees in top 25% by salary (NTILE)

SELECT name, salary, ntile_bucket
FROM (
SELECT name, salary,
NTILE(4) OVER (ORDER BY salary DESC) AS ntile_bucket
FROM employees
)t
WHERE ntile_bucket = 1;

Q81. Median salary

SELECT AVG(salary) AS median_salary FROM (
 SELECT salary, ROW_NUMBER() OVER (ORDER BY salary) AS rn,
COUNT(*) OVER () AS total
FROM employees
)t
WHERE rn IN (FLOOR((total+1)/2), CEIL((total+1)/2));

Q82. Consecutive login days (gaps and islands)

WITH numbered AS (
SELECT emp_id, login_date,
DATE_SUB(login_date, INTERVAL ROW_NUMBER()
OVER (PARTITION BY emp_id ORDER BY login_date) DAY) AS grp
FROM logins
),
consecutive AS (
SELECT emp_id, MIN(login_date) AS start_date,
MAX(login_date) AS end_date,
COUNT(*) AS consecutive_days
FROM numbered GROUP BY emp_id, grp
)
SELECT * FROM consecutive WHERE consecutive_days >= 3;

Q83. Month-over-month growth rate per product

WITH monthly AS (
SELECT product_id,
DATE_FORMAT(order_date,'%Y-%m') AS month,
SUM(quantity*price) AS revenue
FROM orders GROUP BY product_id, month
)
SELECT product_id, month, revenue,
ROUND((revenue - LAG(revenue) OVER (PARTITION BY product_id ORDER BY month))
/ LAG(revenue) OVER (PARTITION BY product_id ORDER BY month) * 100, 2) AS mom_growth
FROM monthly;

Q84. Customers who have NOT ordered in last 90 days

SELECT c.customer_id, c.customer_name, MAX(o.order_date) AS last_order
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING last_order < DATE_SUB(CURDATE(), INTERVAL 90 DAY)
OR last_order IS NULL;

Q85. Self join: employees with same manager

SELECT [Link] AS emp1, [Link] AS emp2, e1.manager_id
FROM employees e1
 JOIN employees e2 ON e1.manager_id = e2.manager_id
AND e1.emp_id < e2.emp_id;

Q86. Recursive CTE: employee hierarchy tree

WITH RECURSIVE hierarchy AS (
SELECT emp_id, name, manager_id, 0 AS level,
CAST(name AS CHAR(500)) AS path
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, [Link], e.manager_id, [Link]+1,
CONCAT([Link], ' > ', [Link])
FROM employees e
JOIN hierarchy h ON e.manager_id = h.emp_id
)
SELECT REPEAT(' ', level) AS indent, name, level, path
FROM hierarchy ORDER BY path;

Q87. Find nth percentile salary

SELECT PERCENTILE_CONT(0.90) WITHIN GROUP
(ORDER BY salary) OVER () AS p90_salary
FROM employees LIMIT 1;
-- Or manual approach:
SELECT salary FROM (
SELECT salary, PERCENT_RANK() OVER (ORDER BY salary) AS pct
FROM employees
) t WHERE pct >= 0.90 ORDER BY salary LIMIT 1;

Q88. Pivot table: product category sales per quarter

SELECT
category,
SUM(CASE WHEN QUARTER(order_date)=1 THEN quantity*price ELSE 0 END) AS Q1,
SUM(CASE WHEN QUARTER(order_date)=2 THEN quantity*price ELSE 0 END) AS Q2,
SUM(CASE WHEN QUARTER(order_date)=3 THEN quantity*price ELSE 0 END) AS Q3,
SUM(CASE WHEN QUARTER(order_date)=4 THEN quantity*price ELSE 0 END) AS Q4
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY category;

Q89. Detect anomalies: sales > 3 standard deviations

WITH stats AS (
SELECT AVG(sale_amount) AS avg_sal, STD(sale_amount) AS std_sal
FROM sales
)
SELECT s.*, stats.avg_sal, stats.std_sal
 FROM sales s, stats
WHERE ABS(s.sale_amount - stats.avg_sal) > 3 * stats.std_sal;

Q90. Cohort analysis: revenue by customer join month

SELECT
DATE_FORMAT(c.join_date,'%Y-%m') AS cohort_month,
COUNT(DISTINCT o.customer_id) AS customers,
SUM([Link] * [Link]) AS revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY cohort_month
ORDER BY cohort_month;

Q91. Find missing IDs in a sequence

SELECT [Link]+1 AS missing_start
FROM (SELECT ROW_NUMBER() OVER (ORDER BY emp_id) AS id FROM employees) s
LEFT JOIN (SELECT ROW_NUMBER() OVER (ORDER BY emp_id) AS id FROM employees) s2
ON [Link]+1 = [Link]
WHERE [Link] IS NULL;

Q92. Employees who never took leave (NOT EXISTS)

SELECT e.emp_id, [Link]
FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM leaves l WHERE l.emp_id = e.emp_id
);

Q93. Upsert: INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO products (product_id, product_name, price, stock)
VALUES (1, 'Laptop Pro', 85000, 50)
ON DUPLICATE KEY UPDATE
price = VALUES(price),
stock = stock + VALUES(stock);

Q94. Create index and show query plan

CREATE INDEX idx_emp_dept_sal ON employees(department, salary);
EXPLAIN SELECT * FROM employees WHERE department='IT' ORDER BY salary DESC;

Q95. Optimized query with covering index

-- Avoid SELECT * when using index; use only indexed columns
SELECT department, salary FROM employees
WHERE department = 'IT'
ORDER BY salary DESC;
Q96. Cumulative revenue percentage
WITH rev AS (
SELECT product_id,
SUM(quantity*price) AS revenue FROM orders GROUP BY product_id
),
totals AS (
SELECT *, SUM(revenue) OVER () AS total_revenue,
SUM(revenue) OVER (ORDER BY revenue DESC) AS cumulative
FROM rev
)
SELECT product_id, revenue,
ROUND(cumulative / total_revenue * 100, 2) AS cum_pct
FROM totals;

Q97. ABC Pareto analysis (80-20 rule)

WITH rev AS (
SELECT product_id, SUM(quantity*price) AS revenue
FROM orders GROUP BY product_id
),
cum AS (
SELECT *, SUM(revenue) OVER (ORDER BY revenue DESC) AS cum_rev,
SUM(revenue) OVER () AS total
FROM rev
)
SELECT product_id, revenue,
ROUND(cum_rev/total*100,2) AS cum_pct,
CASE
WHEN cum_rev/total <= 0.80 THEN 'A'
WHEN cum_rev/total <= 0.95 THEN 'B'
ELSE 'C'
END AS abc_class
FROM cum ORDER BY revenue DESC;

Q98. Week-over-week sales change

SELECT
WEEK(sale_date) AS week_num,
SUM(sale_amount) AS weekly_sales,
SUM(sale_amount) - LAG(SUM(sale_amount)) OVER (ORDER BY WEEK(sale_date)) AS wow_change
FROM sales
GROUP BY WEEK(sale_date);

Q99. Full audit log trigger

DELIMITER //
 CREATE TRIGGER salary_audit
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF [Link] != [Link] THEN
INSERT INTO salary_audit_log(emp_id, old_salary, new_salary, changed_at)
VALUES(OLD.emp_id, [Link], [Link], NOW());
END IF;
END//
DELIMITER ;

Q100. Advanced stored procedure with exception handling

DELIMITER //
CREATE PROCEDURE TransferEmployee(
IN p_emp_id INT, IN p_new_dept VARCHAR(50),
OUT p_status VARCHAR(100)
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET p_status = 'ERROR: Transfer failed';
END;
START TRANSACTION;
UPDATE employees SET department = p_new_dept WHERE emp_id = p_emp_id;
IF ROW_COUNT() = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Employee not found';
END IF;
COMMIT;
SET p_status = 'SUCCESS';
END//
DELIMITER ;

Q101. Dynamic SQL using prepared statements

SET @dept = 'IT';
SET @sql = CONCAT('SELECT * FROM employees WHERE department = ?');
PREPARE stmt FROM @sql;
EXECUTE stmt USING @dept;
DEALLOCATE PREPARE stmt;

Q102. Find employees whose salary is in top 10%

SELECT name, salary FROM employees
WHERE salary >= (
SELECT salary FROM (
SELECT salary, PERCENT_RANK() OVER (ORDER BY salary) AS pct
 FROM employees
) t WHERE pct >= 0.90
ORDER BY salary LIMIT 1
);

Q103. Customer lifetime value (CLV)

SELECT c.customer_id, c.customer_name,
COUNT(DISTINCT o.order_id) AS total_orders,
SUM([Link] * [Link]) AS total_spent,
ROUND(SUM([Link]*[Link])/COUNT(DISTINCT o.order_id),2) AS avg_order_val,
DATEDIFF(MAX(o.order_date), MIN(o.order_date)) AS customer_lifespan_days
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spent DESC;

Q104. Market basket: frequently bought together

SELECT o1.product_id AS p1, o2.product_id AS p2,
COUNT(*) AS times_bought_together
FROM orders o1
JOIN orders o2 ON o1.customer_id = o2.customer_id
AND o1.product_id < o2.product_id
AND o1.order_date = o2.order_date
GROUP BY p1, p2
ORDER BY times_bought_together DESC
LIMIT 10;

Q105. Employee churn prediction: inactive > 6 months

SELECT emp_id, name, MAX(sale_date) AS last_active,
DATEDIFF(CURDATE(), MAX(sale_date)) AS days_inactive
FROM employees e
LEFT JOIN sales s USING (emp_id)
GROUP BY emp_id, name
HAVING days_inactive > 180 OR last_active IS NULL
ORDER BY days_inactive DESC;
 SECTION 4: 100+ Interview Questions
DBMS & MySQL — Data Analyst & Business Analyst Profile

4.1 DBMS Fundamentals (Q1–Q25)

Q1. What is a DBMS and how does it differ from a file system?
→ A DBMS provides organized data storage with query support, security, concurrency control, and ACID
compliance. File systems lack these features — no query language, no relationships, no concurrent access
control.
Q2. What are the ACID properties?
→ Atomicity (all-or-nothing), Consistency (valid state transitions), Isolation (concurrent transactions don't
interfere), Durability (committed data persists after failure).
Q3. Explain normalization and its importance.
→ Normalization organizes tables to reduce redundancy and improve data integrity. It prevents update
anomalies (insertion, deletion, update anomalies) and saves storage.
Q4. What is the difference between 2NF and 3NF?
→ 2NF removes partial dependencies (non-key attribute depends on part of composite key). 3NF removes
transitive dependencies (non-key attribute depends on another non-key attribute).
Q5. What is BCNF and when does it differ from 3NF?
→ BCNF (Boyce-Codd NF) requires every functional dependency X→Y to have X as a superkey. BCNF is
stricter — a table can be in 3NF but not BCNF when there are overlapping candidate keys.
Q6. What are the types of keys in a database?
→ Primary Key, Foreign Key, Candidate Key, Super Key, Composite Key, Unique Key, Alternate Key, Surrogate
Key, Natural Key.
Q7. Difference between Primary Key and Unique Key?
→ Primary Key: NOT NULL + UNIQUE, only one per table. Unique Key: allows one NULL, can have multiple
per table.
Q8. What are database anomalies?
→ Update Anomaly (updating one record leaves inconsistency), Insertion Anomaly (can't insert data without
related data), Deletion Anomaly (deleting a record loses other important data).
Q9. What is a foreign key? What is referential integrity?
→ Foreign key links to a primary key in another table. Referential integrity ensures FK values must match
existing PK values or be NULL — prevents orphan records.
Q10. What is denormalization and when is it used?
→ Intentional redundancy added back for read performance. Used in data warehouses, reporting systems
where query speed matters more than update efficiency.
Q11. Explain the difference between OLTP and OLAP.
→ OLTP: transactional, normalized, fast writes/reads, many concurrent users. OLAP: analytical,
denormalized, complex queries on large data, fewer users, supports BI tools.
Q12. What is a data warehouse?
 → Centralized repository of integrated historical data from multiple sources, optimized for analysis and
reporting (not transactional processing).
Q13. Explain Star Schema vs Snowflake Schema.
→ Star: fact table at center, denormalized dimensions. Faster queries, more storage. Snowflake: normalized
dimensions into sub-tables. Less redundancy, more joins required.
Q14. What are indexes? Types?
→ Data structures accelerating data retrieval. Types: Clustered (physically sorted), Non-clustered (separate
pointer structure), Unique, Composite, Full-text.
Q15. When should you NOT create an index?
→ Small tables, low-cardinality columns, frequently updated columns, tables with mostly
INSERT/UPDATE/DELETE operations, columns not used in WHERE/JOIN/ORDER BY.
Q16. What is a clustered vs non-clustered index?
→ Clustered: physically sorts table rows; only one per table; fast for range queries. Non-clustered: separate
structure with pointers; multiple per table; slightly slower.
Q17. What are database transactions?
→ A unit of work containing one or more SQL operations that must complete entirely or not at all. Managed
using COMMIT and ROLLBACK.
Q18. What are transaction isolation levels?
→ READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE — each progressively
prevents more concurrency issues (dirty reads, non-repeatable reads, phantom reads).
Q19. What is a deadlock in databases?
→ Two transactions each waiting for the other to release a lock, causing infinite wait. Resolved by: timeout,
deadlock detection (rollback one), or using consistent lock ordering.
Q20. Explain database views.
→ Virtual tables based on SELECT queries. Don't store data (unless materialized). Provide security, simplicity,
and abstraction. Simple views support DML; complex views often don't.
Q21. What is a trigger?
→ Automatically executed code when INSERT/UPDATE/DELETE occurs on a table. Used for auditing,
validation, enforcing business rules. BEFORE or AFTER event types.
Q22. Stored Procedure vs Function?
→ Stored Procedure: can have IN/OUT params, no return required, can do DML/TCL. Function: must return
value, used in SELECT, cannot do transactions, more restricted.
Q23. What is a cursor?
→ A database object to traverse rows returned by a query one at a time. Used in stored procedures when
row-by-row processing is needed. Generally slow — prefer set-based operations.
Q24. What is the difference between DELETE, TRUNCATE, and DROP?
→ DELETE: removes rows, transactional, WHERE can filter, slow, logs each row. TRUNCATE: removes all rows
fast, DDL, resets auto-increment, can't rollback in MySQL. DROP: removes entire table structure and data.
Q25. Explain CAP theorem.
→ Distributed systems can guarantee only 2 of 3: Consistency (same data), Availability (always responds),
Partition Tolerance (works despite network split). RDBMS=CA, most NoSQL=AP or CP.
4.2 MySQL & SQL Questions (Q26–Q75)

Q26. What is the execution order of a SQL SELECT statement?

→ FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT
Q27. Difference between WHERE and HAVING?
→ WHERE filters rows before grouping; cannot use aggregate functions. HAVING filters groups after GROUP
BY; can use aggregates.
Q28. What are the types of JOINs?
→ INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, SELF JOIN, NATURAL JOIN.
Q29. What is a SELF JOIN and when is it used?
→ A table joined with itself. Used for hierarchical data like employee-manager relationships. Requires table
aliases.
Q30. Difference between UNION and UNION ALL?
→ UNION: removes duplicates (uses sorting). UNION ALL: includes all duplicates, faster. Both require same
number of columns with compatible data types.
Q31. What is a correlated subquery?
→ A subquery that references columns from the outer query. Executed once per row of outer query. Slower
than regular subqueries. Used with EXISTS, NOT EXISTS.
Q32. Explain ROW_NUMBER(), RANK(), DENSE_RANK() differences.
→ ROW_NUMBER: always unique (1,2,3,4). RANK: same rank for ties, gaps after (1,1,3,4). DENSE_RANK:
same rank for ties, no gaps (1,1,2,3).
Q33. What is a CTE and its advantages?
→ Common Table Expression defined with WITH. Advantages: better readability, can be referenced multiple
times, supports recursion, easier to maintain than nested subqueries.
Q34. What is a window function?
→ Performs calculations across a set of rows related to the current row without collapsing them into one
row. Uses OVER() clause with PARTITION BY and ORDER BY.
Q35. How do you find the Nth highest salary?
→ SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET N-1; Or using DENSE_RANK()
window function with CTE.
Q36. How to find duplicate records?
→ SELECT col, COUNT(*) FROM table GROUP BY col HAVING COUNT(*) > 1;
Q37. How to delete duplicate rows keeping only one?
→ DELETE e1 FROM employees e1 JOIN employees e2 WHERE [Link] > [Link] AND [Link] = [Link];
Q38. What is COALESCE and when to use it?
→ Returns first non-NULL argument. Used to handle NULL values: COALESCE(phone, email, 'No Contact').
More flexible than IFNULL (accepts multiple args).
Q39. Difference between CHAR and VARCHAR?
→ CHAR(n): fixed length, pads with spaces, faster for fixed-size data. VARCHAR(n): variable length, uses only
needed space, better for variable-size data.
Q40. What is AUTO_INCREMENT?
 → MySQL feature that automatically generates unique sequential integers for a column (usually primary
key). Resets only on TRUNCATE.
Q41. How to optimize a slow SQL query?
→ Use EXPLAIN to analyze, add appropriate indexes, avoid SELECT *, reduce subqueries, use JOINs instead
of subqueries, partition large tables, use LIMIT, rewrite correlated subqueries.
Q42. What is EXPLAIN in MySQL?
→ Shows query execution plan: which indexes are used, join types, rows examined. Key columns: type (ALL
is worst), key (index used), rows, Extra (Using filesort/index).
Q43. What is a full-table scan and why is it bad?
→ MySQL reads every row when no index is usable. Extremely slow for large tables. Shown as 'ALL' in
EXPLAIN type column.
Q44. What is the difference between IN and EXISTS?
→ IN: evaluates subquery once, compares values. EXISTS: checks if subquery returns any rows, short-circuits.
EXISTS is faster when subquery result is large; IN is faster for small result sets.
Q45. How does GROUP_CONCAT work?
→ Aggregates non-NULL values into a concatenated string: GROUP_CONCAT(name ORDER BY name
SEPARATOR ', '). Has default max length of 1024.
Q46. What is ROLLUP in GROUP BY?
→ GROUP BY department, city WITH ROLLUP adds subtotal rows and a grand total row.
Q47. Difference between INNER JOIN and WHERE clause join?
→ Functionally same result, but JOIN syntax is cleaner and standard. ANSI JOIN makes the join condition
explicit and improves readability.
Q48. What is a materialized view?
→ A view that stores query results physically and refreshes periodically. Faster read performance than
regular views. Not natively supported in MySQL (use summary tables as workaround).
Q49. What is partitioning in MySQL?
→ Dividing large table into smaller parts (partitions) based on a column value. Types: RANGE, LIST, HASH,
KEY partitioning. Improves query performance and data management.
Q50. What are MySQL storage engines?
→ InnoDB (default): ACID, foreign keys, row-level locking. MyISAM: no FK, table-level lock, faster reads.
MEMORY: in-memory, very fast, lost on restart. ARCHIVE: compressed storage.
Q51. Difference between InnoDB and MyISAM?
→ InnoDB: supports transactions, FK, row-level lock, crash recovery. MyISAM: no transactions, no FK, table-
level lock, faster for read-heavy no-transaction workloads.
Q52. What is database sharding?
→ Horizontal scaling by splitting data across multiple database instances (shards) based on a key (e.g.,
user_id range). Each shard holds a subset of total data.
Q53. What is a composite index? How does it work?
→ Index on multiple columns. Works left-to-right: composite index on (dept, salary) helps queries filtering
on dept or dept+salary, but NOT salary alone.
Q54. Explain LAG and LEAD functions.
 → LAG(col, n): returns value of col from n rows before current row. LEAD(col, n): returns value from n rows
after. Used for period-over-period comparisons.
Q55. How do you calculate running totals?
→ SUM(amount) OVER (ORDER BY date) AS running_total — uses window function with cumulative frame.
Q56. What is PERCENT_RANK()?
→ Returns relative rank of current row as percentage: (rank - 1) / (total rows - 1). Returns 0 for lowest, 1 for
highest.
Q57. What is NTILE()?
→ Divides rows into n equal-numbered groups. NTILE(4) creates quartiles (1,2,3,4). Used for percentile
analysis.
Q58. What are MySQL string functions commonly used in analytics?
→ SUBSTRING, CONCAT, REPLACE, TRIM, UPPER, LOWER, LEFT, RIGHT, INSTR, LENGTH, REGEXP,
GROUP_CONCAT.
Q59. What is CASE WHEN and where is it used?
→ Conditional logic in SQL. Used in SELECT (derive category columns), WHERE (filter conditions), ORDER BY
(custom sort), aggregations (conditional counts/sums).
Q60. How to pivot data in MySQL (without PIVOT keyword)?
→ Use conditional aggregation: SUM(CASE WHEN col='value' THEN amount ELSE 0 END) for each pivot
column in GROUP BY query.
Q61. What is a recursive CTE?
→ CTE that references itself. Used for hierarchical data (org charts, bill of materials). Has anchor member
(base case) and recursive member (iterative step) joined by UNION ALL.
Q62. How to handle NULL in calculations?
→ NULL in arithmetic returns NULL. Use COALESCE(col, 0) or IFNULL(col, 0) to replace NULL with default.
COUNT(*) includes NULLs; COUNT(col) excludes NULLs.
Q63. Difference between COUNT(*) and COUNT(column)?
→ COUNT(*): counts all rows including those with NULL values. COUNT(column): counts only non-NULL
values in that specific column.
Q64. What is a CROSS JOIN and when is it useful?
→ Produces Cartesian product of two tables. Useful for generating all combinations (date ranges, product-
store combinations, test data). No JOIN condition.
Q65. What is normalization vs. what a data analyst does differently?
→ Analysts often work with denormalized data warehouses (star/snowflake schema) for query performance.
Normalization is for transactional systems; denormalization is for analytical systems.
Q66. How to find records in table A not in table B?
→ Using LEFT JOIN: SELECT a.* FROM a LEFT JOIN b ON [Link]=[Link] WHERE [Link] IS NULL; Or NOT IN / NOT
EXISTS subquery.
Q67. What is the difference between SUBQUERY and JOIN?
→ Subquery: nested query, usually slower for large datasets. JOIN: combines tables, optimized by query
planner, generally faster. Prefer JOINs when possible.
Q68. Explain ON DELETE CASCADE and ON UPDATE CASCADE.
 → Cascade options for foreign keys. ON DELETE CASCADE: deleting parent row automatically deletes child
rows. ON UPDATE CASCADE: updating parent PK updates child FK values.
Q69. What is a database transaction log?
→ Records all changes to database for recovery purposes. Enables ROLLBACK and crash recovery. Sequential
write makes it fast.
Q70. How to do conditional aggregation?
→ SUM(CASE WHEN condition THEN value ELSE 0 END) — aggregates only rows meeting condition.
COUNT(CASE WHEN condition THEN 1 END) counts conditional rows.
Q71. What is the difference between INTERSECT and EXCEPT?
→ INTERSECT: rows in both queries. EXCEPT/MINUS: rows in first query not in second. MySQL doesn't
support natively — use JOIN/NOT IN alternatives.
Q72. How to update table using JOIN?
→ UPDATE employees e JOIN departments d ON e.dept_id=d.dept_id SET [Link]=[Link] WHERE
d.dept_name='IT';
Q73. What is a semi-join?
→ Returns rows from left table where matching rows exist in right table, without returning columns from
right table. Implemented with EXISTS or IN subquery.
Q74. What is query optimization?
→ Process of selecting the most efficient execution plan. Involves: index selection, join order, predicate
pushdown, avoiding full scans, rewriting subqueries, using EXPLAIN.
Q75. How does MySQL decide which index to use?
→ MySQL optimizer estimates cost of different access paths based on index statistics (cardinality,
selectivity). Can override with USE INDEX, FORCE INDEX hints.
4.3 Business Analyst & Data Analyst Scenario Questions (Q76–Q115)

Q76. How would you write a SQL query to find the month with highest sales?
→ SELECT DATE_FORMAT(order_date,'%Y-%m') AS month, SUM(quantity*price) AS revenue FROM orders
GROUP BY month ORDER BY revenue DESC LIMIT 1;
Q77. How do you calculate churn rate using SQL?
→ Customers who were active last period but not in current period / total customers last period × 100. Use
LEFT JOIN between monthly active customer lists.
Q78. How would you find top 5 performing sales reps?
→ JOIN employees with sales table, GROUP BY emp_id, SUM(sale_amount), ORDER BY total DESC LIMIT 5.
Q79. A query that was fast is now slow. What steps do you take?
→ Run EXPLAIN, check indexes, look for full table scans, check data volume growth, check for missing
indexes, analyze query structure, look for lock contention, check server resources.
Q80. How do you compare current week vs previous week sales?
→ Use LAG() over weekly grouping or self-join on WEEK() function. Calculate difference and percentage
change.
Q81. How to calculate customer LTV (Lifetime Value)?
→ SUM(order_value) per customer, or average purchase value × purchase frequency × customer lifespan.
Q82. How do you find products contributing to 80% of revenue (Pareto)?
→ Cumulative sum of revenue ordered DESC, find cutoff where cumulative_pct ≤ 80%.
Q83. How would you build a sales dashboard in SQL?
→ Queries for: total revenue, MoM growth, top products, top customers, regional breakdown, trend over
time — all as individual SELECT statements feeding into BI tool.
Q84. What is cohort analysis and how do you write it in SQL?
→ Group customers by first purchase month (cohort), then track their behavior in subsequent months. Use
CTEs: first get join cohort, then left join with orders by month offset.
Q85. How do you handle missing data in SQL analysis?
→ Use COALESCE/IFNULL for NULLs, investigate if data is missing randomly or systematically, document
assumptions, use imputation if appropriate, flag in reports.
Q86. How to detect seasonal patterns in sales data?
→ GROUP BY MONTH and compare year-over-year, use ROLLUP for subtotals, plot time series from SQL
output in BI tool.
Q87. How would you analyze funnel conversion rates?
→ Count users at each stage, calculate drop-off: stage2_users/stage1_users as conversion rate. Use CTEs for
each funnel stage.
Q88. How to write a query for DAU, WAU, MAU?
→ COUNT(DISTINCT user_id) WHERE action_date = CURDATE() for DAU, within 7 days for WAU, within 30
days for MAU.
Q89. How do you validate data quality using SQL?
→ Check for NULLs (IS NULL), duplicates (GROUP BY HAVING COUNT>1), range violations (WHERE val < 0),
orphan records (LEFT JOIN with IS NULL), date consistency (start > end).
Q90. What is a surrogate key? When do you use it in BI?
→ System-generated artificial key (auto-increment integer). Used in dimension tables to handle SCD (Slowly
Changing Dimensions), replace natural keys for performance.
Q91. Explain SCD Type 1, 2, and 3.
→ Type 1: Overwrite old data (no history). Type 2: Add new row with effective dates (full history). Type 3:
Add column for previous value (limited history).
Q92. How do you calculate Net Promoter Score in SQL?
→ (COUNT of Promoters - COUNT of Detractors) / Total respondents × 100. Promoters: score 9-10,
Detractors: 0-6.
Q93. How do you analyze A/B test results in SQL?
→ Compare metric (conversion rate, revenue) between control and variant groups using GROUP BY group.
Calculate statistical significance using sample sizes and proportions.
Q94. What is a fact table vs dimension table?
→ Fact table: numerical metrics/measures (sales_amount, quantity), foreign keys to dimensions, large.
Dimension table: descriptive attributes (customer_name, product_category), smaller.
Q95. How do you write a query for retention analysis?
→ Users who made first purchase in month M and returned in month M+1: use self-join or CTE approach
comparing first_order_month with subsequent order months.
Q96. What is the difference between a measure and a dimension?
→ Dimension: categorical/descriptive attribute used to slice data (product_category, region, date). Measure:
numerical value to be aggregated (revenue, units_sold, profit).
Q97. How do you handle date dimension in data warehouse?
→ Create a dim_date table with all dates and attributes (year, quarter, month, week, weekday, is_holiday,
fiscal_period). Join fact tables to it by date key.
Q98. How would you write a query to identify at-risk customers?
→ Customers with declining order frequency or value over recent periods. Use LAG to compare current vs
previous period order counts/values.
Q99. What are KPIs you'd track as a Business Analyst?
→ Revenue, MoM/YoY growth, customer acquisition cost (CAC), LTV, churn rate, conversion rate, average
order value (AOV), DAU/MAU, retention rate, NPS.
Q100. How to detect outliers in SQL?
→ Using standard deviation: WHERE ABS(value - avg) > 2*std. Or using percentile: flag values outside 5th-
95th percentile range.
Q101. How do you write a query for market basket analysis?
→ Self-join orders table on same customer_id, different product_id, same time window. Count frequency of
product pairs appearing together.
Q102. What is the difference between granularity and aggregation?
→ Granularity: level of detail in data (daily vs monthly, transaction vs summary). Aggregation: combining
data at higher level. Lower granularity = more detail.
Q103. How do you measure employee productivity in SQL?
→ Metrics: tasks_completed, revenue_generated, targets_met, calls_made — per employee per period.
Compare vs team average or targets.
Q104. What is slowly changing dimension (SCD)?
→ Dimension attributes that change over time (customer address, product price). SCD types define how to
handle historical data: overwrite, new row, or add column.
Q105. How do you write a year-over-year comparison query?
→ SELECT year, revenue, LAG(revenue) OVER (ORDER BY year) as prev_year, (revenue-prev)/prev*100 as
growth FROM yearly_summary;
Q106. What is data lineage?
→ Documentation of data's origin, movement, transformation, and destination through systems. Critical for
data governance, debugging, and compliance.
Q107. What is the difference between a report and a dashboard?
→ Report: detailed, historical, static snapshot, scheduled. Dashboard: real-time/near-real-time, visual,
interactive, for monitoring KPIs continuously.
Q108. How to calculate conversion rate in SQL?
→ Converted events / Total events × 100. E.g., purchases/visitors × 100. Use COUNT with CASE for
conditional counting in single query.
Q109. What is data mart vs data warehouse?
→ Data warehouse: enterprise-wide, all business data. Data mart: subset focused on specific department
(Sales mart, Finance mart). Marts can be derived from warehouse.
Q110. How do you prioritize which queries to optimize?
→ Impact: queries running most frequently or used in dashboards. Cost: queries with high execution time.
Business: queries supporting critical decisions.
Q111. What is a surrogate vs natural key in data warehousing?
→ Surrogate: meaningless integer for performance, handles SCD. Natural: real-world identifier (SSN,
product_code). Use surrogate in facts, keep natural as attribute in dimensions.
Q112. How would you approach a new data analysis project?
→ Understand business question → identify data sources → assess data quality → design query/model →
analyze → validate findings → present insights with visualization.
Q113. What is the difference between deduplication and normalization?
→ Deduplication: removing duplicate records from a dataset. Normalization: structuring database to reduce
data redundancy through proper table design.
Q114. How do you calculate average revenue per user (ARPU)?
→ Total Revenue / Total Active Users for a period. In SQL: SUM(revenue) / COUNT(DISTINCT user_id) for the
given time period.
Q115. What makes a good data analyst technically?
→ Strong SQL (joins, window functions, CTEs), understanding of data modeling, ability to translate business
questions into queries, data quality awareness, statistical literacy, visualization skills.
 QUICK REFERENCE CHEAT SHEET
Must-Know for Tomorrow's Interview

🎯 Last-Minute Revision Points

SQL Must-Know Patterns

• 2nd highest salary: SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp)
• Find duplicates: SELECT col, COUNT(*) FROM t GROUP BY col HAVING COUNT(*) > 1
• Delete duplicates: DELETE e1 FROM t e1 JOIN t e2 WHERE [Link] > [Link] AND [Link]=[Link]
• Left-only records: SELECT a.* FROM a LEFT JOIN b ON [Link]=[Link] WHERE [Link] IS NULL
• Running total: SUM(col) OVER (ORDER BY date)
• Rank within group: DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC)
• YoY growth: LAG(revenue) OVER (ORDER BY year)
• Conditional count: COUNT(CASE WHEN status='Active' THEN 1 END)
• Pivot: SUM(CASE WHEN month=1 THEN rev ELSE 0 END) AS Jan
• Top N per group: ROW_NUMBER() OVER (PARTITION BY dept ORDER BY sal DESC) then WHERE rn<=N

Join Quick Reference

INNER JOIN → only matching rows (intersection)
LEFT JOIN → all left + matching right (NULL where no match)
RIGHT JOIN → all right + matching left (NULL where no match)
FULL OUTER → all rows from both (NULLs where no match) [use UNION of LEFT+RIGHT in MySQL]
CROSS JOIN → Cartesian product (no condition)
SELF JOIN → table joins itself (use aliases)

Window Functions Pattern

ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) → 1,2,3,4 (no ties)
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) → 1,1,3,4 (ties+gaps)
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) → 1,1,2,3 (ties,no gaps)
LAG(sal,1) OVER (PARTITION BY dept ORDER BY hire_date) → previous row value
LEAD(sal,1) OVER (PARTITION BY dept ORDER BY hire_date) → next row value
SUM(sal) OVER (PARTITION BY dept) → partition total
SUM(sal) OVER (ORDER BY date) → running total

Normalization Forms
• 1NF: Atomic values, no repeating groups, same-type columns
• 2NF: 1NF + No partial dependency (on part of composite PK)
• 3NF: 2NF + No transitive dependency (non-key → non-key)
• BCNF: 3NF + Every determinant must be a superkey
ACID in One Line Each
• Atomicity: All operations succeed or all are rolled back
• Consistency: DB moves from one valid state to another
• Isolation: Transactions execute as if they're alone
• Durability: Committed data survives system failure

Key Differences for Interview

• DELETE vs TRUNCATE: DELETE=transactional+WHERE+slow; TRUNCATE=DDL+all rows+fast+resets AI
• WHERE vs HAVING: WHERE=before grouping; HAVING=after GROUP BY, can use aggregates
• UNION vs UNION ALL: UNION=removes dups (slower); UNION ALL=keeps all (faster)
• IN vs EXISTS: IN=subquery runs once; EXISTS=checks row by row, stops on first match
• CHAR vs VARCHAR: CHAR=fixed length (faster lookup); VARCHAR=variable (saves space)
• Clustered vs Non-clustered: Clustered=physical sort (1/table); Non-clustered=pointer structure
(many/table)

You've got this! Review the queries once more, stay calm, and remember: interviewers
🍀 Good
care about HOW you think, not just the syntax. Always explain your approach before
Luck!
writing the query.