SQL Complete Guide | Interview & Placement Preparation

01 Introduction to SQL

What is SQL?
SQL (Structured Query Language) is a standard programming language used to store, manipulate, and
retrieve data from relational databases. It was developed by IBM in the 1970s and has become the
backbone of virtually all database systems.

💡 SQL is NOT case-sensitive. SELECT = select = Select. However, writing SQL keywords in
UPPERCASE is the widely followed convention.

Why SQL for Interviews?

• SQL appears in 80%+ of technical interview rounds for developer, analyst, and data roles
• Used in MySQL, PostgreSQL, Oracle, SQL Server, SQLite — all follow similar syntax
• Demonstrates your ability to work with structured data and databases
• From FAANG to startups — SQL is universally required

Types of SQL Commands

Type Full Form Commands Purpose
DDL Data Definition CREATE, ALTER, DROP, Define/modify table structure
Language TRUNCATE
DML Data Manipulation INSERT, UPDATE, DELETE Modify data in tables
Language
DQL Data Query SELECT Retrieve/query data
Language
DCL Data Control GRANT, REVOKE Access control
Language
TCL Transaction Control COMMIT, ROLLBACK, Manage transactions
Language SAVEPOINT

Sample Database — We'll Use These Tables Throughout

All queries in this document use these four tables. Study them carefully.

employees
emp_id name dept_id salary hire_date manager_id
101 Arjun 10 55000 2020-03-15 NULL
Sharma

Page 1 of 28
SQL Complete Guide | Interview & Placement Preparation

emp_id name dept_id salary hire_date manager_id

102 Priya Verma 20 72000 2019-07-01 101
103 Ravi Kumar 10 48000 2021-11-20 101
104 Sneha Patel 30 61000 2022-01-10 102
105 Karan Mehta 20 90000 2018-05-05 101
106 Divya Nair 10 55000 2020-03-15 101
107 Rohit Das 30 40000 2023-06-01 102

departments
dept_id dept_name location
10 Engineering Hyderabad
20 Marketing Mumbai
30 HR Bangalore
40 Finance Delhi

projects
proj_id proj_name emp_id budget
P001 Website Revamp 101 150000
P002 SEO Campaign 102 80000
P003 Mobile App 103 200000
P004 HR Portal 104 60000
P005 Data Pipeline 105 300000

Page 2 of 28
SQL Complete Guide | Interview & Placement Preparation

02 DDL — Data Definition Language

CREATE TABLE
Used to create a new table in the database.
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
dept_id INT,
salary DECIMAL(10, 2),
hire_date DATE,
manager_id INT
);

Common Data Types

Data Type Description Example
INT / INTEGER Whole numbers emp_id INT
VARCHAR(n) Variable-length text (max n name VARCHAR(100)
chars)
CHAR(n) Fixed-length text gender CHAR(1)
DECIMAL(p,s) Precise decimal numbers salary DECIMAL(10,2)
DATE Date (YYYY-MM-DD) hire_date DATE
DATETIME Date + Time created_at DATETIME
BOOLEAN TRUE or FALSE is_active BOOLEAN
TEXT Large text fields description TEXT

Constraints
Constraints enforce rules on table columns.

Constraint Purpose Example

PRIMARY Uniquely identifies each emp_id INT PRIMARY KEY
KEY row
NOT NULL Column cannot be empty name VARCHAR(100) NOT NULL
UNIQUE All values must be email VARCHAR UNIQUE
different
DEFAULT Sets a default value salary DECIMAL DEFAULT 30000
CHECK Validates data before CHECK (salary > 0)
insert

Page 3 of 28
SQL Complete Guide | Interview & Placement Preparation

Constraint Purpose Example

FOREIGN Links to another table's REFERENCES
KEY PK departments(dept_id)

ALTER TABLE
Modify the structure of an existing table.
-- Add a new column
ALTER TABLE employees ADD COLUMN email VARCHAR(150);

-- Rename a column
ALTER TABLE employees RENAME COLUMN email TO work_email;

-- Change data type of a column

ALTER TABLE employees MODIFY COLUMN salary BIGINT;

-- Drop a column
ALTER TABLE employees DROP COLUMN work_email;

DROP vs TRUNCATE vs DELETE

WHERE
Command What it does Rollback?
allowed?
DROP TABLE Removes entire table No No
(structure + data)
TRUNCATE Removes all rows, keeps No No (in most
TABLE structure DBs)
DELETE Removes specific or all rows Yes Yes (within
transaction)

DROP TABLE employees; -- Deletes table entirely

TRUNCATE TABLE employees; -- Empties table, keeps columns
DELETE FROM employees WHERE emp_id = 107; -- Deletes one row

Page 4 of 28
SQL Complete Guide | Interview & Placement Preparation

03 DML — Data Manipulation Language

INSERT — Add Data

-- Insert a single row
INSERT INTO employees (emp_id, name, dept_id, salary, hire_date)
VALUES (108, 'Ananya Singh', 10, 52000, '2024-01-15');

-- Insert multiple rows at once

INSERT INTO employees (emp_id, name, dept_id, salary)
VALUES
(109, 'Vikram Rao', 20, 65000),
(110, 'Meera Joshi', 30, 44000);

UPDATE — Modify Data

-- Update salary of one employee
UPDATE employees
SET salary = 60000
WHERE emp_id = 103;

-- Update multiple columns

UPDATE employees
SET salary = salary * 1.10, -- 10% hike
dept_id = 20
WHERE dept_id = 10 AND salary < 50000;

💡 Always use WHERE in UPDATE. Without it, EVERY row gets updated — a very common interview
gotcha!

DELETE — Remove Data

-- Delete a specific employee
DELETE FROM employees WHERE emp_id = 108;

-- Delete all employees in a department

DELETE FROM employees WHERE dept_id = 40;

-- Delete all rows (dangerous!)

DELETE FROM employees;

Page 5 of 28
SQL Complete Guide | Interview & Placement Preparation

04 SELECT — Basic Queries (Most Important for Interviews)

Basic SELECT
-- Select all columns
SELECT * FROM employees;

-- Select specific columns

SELECT name, salary FROM employees;

-- Using column alias (AS)

SELECT name AS employee_name, salary AS monthly_pay FROM employees;

WHERE Clause — Filtering Data

Filter rows based on conditions.
-- Single condition
SELECT * FROM employees WHERE dept_id = 10;

-- Multiple conditions with AND / OR

SELECT * FROM employees WHERE dept_id = 10 AND salary > 50000;
SELECT * FROM employees WHERE dept_id = 10 OR dept_id = 20;

-- NOT condition
SELECT * FROM employees WHERE NOT dept_id = 30;

Comparison Operators
Operator Meaning Example
= Equal to WHERE salary = 55000
!= or <> Not equal to WHERE dept_id != 30
> Greater than WHERE salary > 60000
< Less than WHERE salary < 50000
>= Greater than or equal WHERE salary >= 55000
<= Less than or equal WHERE salary <= 72000

BETWEEN, IN, LIKE, IS NULL

-- BETWEEN (inclusive on both ends)
SELECT * FROM employees WHERE salary BETWEEN 50000 AND 75000;

-- IN (match any value in a list)

SELECT * FROM employees WHERE dept_id IN (10, 20);

Page 6 of 28
SQL Complete Guide | Interview & Placement Preparation

SELECT * FROM employees WHERE dept_id NOT IN (30);

-- LIKE (pattern matching)

SELECT * FROM employees WHERE name LIKE 'A%'; -- starts with A
SELECT * FROM employees WHERE name LIKE '%kumar'; -- ends with kumar
SELECT * FROM employees WHERE name LIKE '%ar%'; -- contains 'ar'
SELECT * FROM employees WHERE name LIKE '_avi%'; -- 2nd char is 'a'

-- IS NULL / IS NOT NULL

SELECT * FROM employees WHERE manager_id IS NULL;
SELECT * FROM employees WHERE manager_id IS NOT NULL;

💡 LIKE wildcards: % means any number of characters. _ means exactly one character.

ORDER BY — Sorting Results

-- Sort by salary ascending (default)
SELECT * FROM employees ORDER BY salary;

-- Sort by salary descending

SELECT * FROM employees ORDER BY salary DESC;

-- Sort by multiple columns

SELECT * FROM employees ORDER BY dept_id ASC, salary DESC;

LIMIT / TOP — Restricting Rows

-- MySQL / PostgreSQL / SQLite
SELECT * FROM employees ORDER BY salary DESC LIMIT 5;

-- SQL Server
SELECT TOP 5 * FROM employees ORDER BY salary DESC;

-- OFFSET — skip first N rows (pagination)

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

DISTINCT — Remove Duplicates

-- Get unique department IDs
SELECT DISTINCT dept_id FROM employees;

-- Count distinct departments

SELECT COUNT(DISTINCT dept_id) AS total_departments FROM employees;

Page 7 of 28
SQL Complete Guide | Interview & Placement Preparation

05 Aggregate Functions & GROUP BY

Aggregate Functions
Function Description Example
COUNT(*) Total rows SELECT COUNT(*) FROM
employees
COUNT(col) Rows where col is not SELECT COUNT(manager_id)
NULL FROM employees
SUM(col) Total of numeric column SELECT SUM(salary) FROM
employees
AVG(col) Average of numeric SELECT AVG(salary) FROM
column employees
MAX(col) Highest value SELECT MAX(salary) FROM
employees
MIN(col) Lowest value SELECT MIN(salary) FROM
employees

GROUP BY — Grouping Data

GROUP BY groups rows with the same values. It is ALWAYS used with aggregate functions.
-- Count employees in each department
SELECT dept_id, COUNT(*) AS employee_count
FROM employees
GROUP BY dept_id;

-- Average salary per department

SELECT dept_id, AVG(salary) AS avg_salary, MAX(salary) AS max_salary
FROM employees
GROUP BY dept_id;

-- Total budget per project employee

SELECT emp_id, SUM(budget) AS total_budget
FROM projects
GROUP BY emp_id;

HAVING — Filtering Groups

HAVING is used to filter results AFTER grouping. WHERE cannot be used with aggregates.
-- Departments with more than 2 employees
SELECT dept_id, COUNT(*) AS emp_count
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 2;

Page 8 of 28
SQL Complete Guide | Interview & Placement Preparation

-- Departments with average salary above 60000

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

WHERE HAVING
Filters rows BEFORE grouping Filters groups AFTER grouping
Cannot use aggregate functions Can use aggregate functions
e.g. WHERE salary > 50000 e.g. HAVING AVG(salary) > 60000

SQL Query Execution Order

💡 SQL executes in this order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER
BY → LIMIT. Understanding this prevents many bugs!

Order Clause What it does

1 FROM Identifies which tables to read
2 WHERE Filters individual rows
3 GROUP BY Groups remaining rows
4 HAVING Filters groups
5 SELECT Picks columns / computes expressions
6 ORDER BY Sorts the result
7 LIMIT Restricts output row count

Page 9 of 28
SQL Complete Guide | Interview & Placement Preparation

06 JOINs — Combining Tables (Critical Interview Topic)

Joins combine rows from two or more tables based on a related column. Mastering JOINs is the single
most important SQL skill for interviews.

INNER JOIN — Only Matching Rows

Returns only rows that have a matching value in BOTH tables.
-- Get employee names with their department names
SELECT [Link], [Link], d.dept_name, [Link]
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

💡 Employees with dept_id = 40 (Finance) would NOT appear — dept 40 has no employees.
Employees with NULL dept_id also won't appear.

LEFT JOIN — All Left Table Rows

Returns ALL rows from the LEFT table and matching rows from the right table. Non-matching right-
table columns appear as NULL.
-- All departments, even those without employees
SELECT d.dept_name, [Link], [Link]
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id;

-- Find departments with NO employees

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

RIGHT JOIN — All Right Table Rows

Returns ALL rows from the RIGHT table and matching rows from the left table. Less commonly used
(most queries can be rewritten as LEFT JOIN).
SELECT [Link], d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;
-- Same result as the LEFT JOIN example above (just swapped tables)

FULL OUTER JOIN — All Rows from Both Tables

Returns all rows from both tables. NULLs appear where there is no match.
SELECT [Link], d.dept_name

Page 10 of 28
SQL Complete Guide | Interview & Placement Preparation

FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

-- MySQL doesn't support FULL OUTER JOIN directly — use UNION:

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

CROSS JOIN — Every Combination

Returns the Cartesian product — every row from table A paired with every row from table B. 4
departments x 7 employees = 28 rows.
SELECT [Link], d.dept_name
FROM employees e
CROSS JOIN departments d;

SELF JOIN — Table Joins Itself

Join a table to itself. Classic use case: finding employee-manager relationships within the same table.
-- Get each employee with their manager's name
SELECT
[Link] AS employee,
[Link] AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

JOIN with Multiple Tables

-- Employee name, department, and project details
SELECT
[Link],
d.dept_name,
p.proj_name,
[Link]
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
INNER JOIN projects p ON e.emp_id = p.emp_id
ORDER BY [Link];

JOIN Types — Quick Reference

JOIN Type Result Keyword
INNER JOIN Only matching rows from both tables INNER JOIN
LEFT JOIN All rows from left + matched rows from right LEFT JOIN

Page 11 of 28
SQL Complete Guide | Interview & Placement Preparation

JOIN Type Result Keyword

RIGHT JOIN All rows from right + matched rows from left RIGHT JOIN
FULL OUTER JOIN All rows from both tables (with NULLs) FULL OUTER JOIN
CROSS JOIN Every row × every row (Cartesian product) CROSS JOIN
SELF JOIN Table joined with itself using aliases JOIN (with alias)

Page 12 of 28
SQL Complete Guide | Interview & Placement Preparation

07 Subqueries (Nested Queries)

A subquery is a SELECT statement inside another SELECT, INSERT, UPDATE, or DELETE. They are
extremely common in interviews.

Subquery in WHERE
-- Find employees who earn more than the average salary
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Employees in the same department as 'Priya Verma'

SELECT name FROM employees
WHERE dept_id = (
SELECT dept_id FROM employees WHERE name = 'Priya Verma'
);

Subquery with IN
-- Employees who have at least one project
SELECT name FROM employees
WHERE emp_id IN (SELECT emp_id FROM projects);

-- Employees with NO project assigned

SELECT name FROM employees
WHERE emp_id NOT IN (SELECT emp_id FROM projects);

Subquery in FROM (Derived Table)

-- Average of departmental averages
SELECT AVG(avg_sal) AS overall_avg
FROM (
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
) AS dept_averages;

Correlated Subquery
A correlated subquery references the outer query. It runs once per row in the outer query.
-- Employees who earn more than their department's average salary
SELECT name, salary, dept_id
FROM employees e
WHERE salary > (
SELECT AVG(salary)

Page 13 of 28
SQL Complete Guide | Interview & Placement Preparation

FROM employees
WHERE dept_id = e.dept_id -- refers to outer query
);

EXISTS / NOT EXISTS

-- Employees who have at least one project (using EXISTS)
SELECT name FROM employees e
WHERE EXISTS (
SELECT 1 FROM projects p WHERE p.emp_id = e.emp_id
);

-- Departments with no employees

SELECT dept_name FROM departments d
WHERE NOT EXISTS (
SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);

💡 EXISTS is often faster than IN for large tables because it stops searching as soon as one match is
found.

Page 14 of 28
SQL Complete Guide | Interview & Placement Preparation

08 Built-in Functions — String, Numeric, Date

String Functions
Function Description Example Result
UPPER(s) Converts to UPPER('arjun') ARJUN
uppercase
LOWER(s) Converts to LOWER('RAVI') ravi
lowercase
LENGTH(s) Number of characters LENGTH('Priya') 5
TRIM(s) Remove TRIM(' hello ') hello
leading/trailing
spaces
LTRIM(s) Remove leading LTRIM(' hi') hi
spaces
RTRIM(s) Remove trailing RTRIM('hi ') hi
spaces
SUBSTRING(s,p,n) Extract part of string SUBSTRING('Arjun',1,3) Arj
CONCAT(s1,s2) Join strings together CONCAT('SQL',' Guide') SQL
Guide
REPLACE(s,f,r) Replace part of string REPLACE('SQL SQL 2025
2024','2024','2025')
INSTR(s,sub) Position of substring INSTR('Hello','ll') 3
LPAD(s,n,p) Pad left to length n LPAD('5',3,'0') 005
RPAD(s,n,p) Pad right to length n RPAD('5',3,'0') 500

-- Practical examples
SELECT UPPER(name), LENGTH(name) FROM employees;
SELECT CONCAT(name, ' (Dept: ', dept_id, ')') AS label FROM employees;
SELECT name, SUBSTRING(name, 1, INSTR(name,' ')-1) AS first_name FROM
employees;

Numeric Functions
Function Description Example Result
ROUND(n,d) Round to d decimal ROUND(55678.567, 2) 55678.57
places
CEIL(n) Round up to nearest CEIL(55.2) 56
integer
FLOOR(n) Round down to FLOOR(55.9) 55
nearest integer

Page 15 of 28
SQL Complete Guide | Interview & Placement Preparation

Function Description Example Result

ABS(n) Absolute value ABS(-500) 500
MOD(n,d) Remainder (n % d) MOD(10, 3) 1
POWER(n,p) n raised to power p POWER(2, 10) 1024
SQRT(n) Square root SQRT(144) 12

Date Functions
Function Description Example
NOW() Current date and SELECT NOW()
time
CURDATE() Current date only SELECT CURDATE()
YEAR(d) Extract year YEAR(hire_date)
MONTH(d) Extract month (1– MONTH(hire_date)
12)
DAY(d) Extract day of DAY(hire_date)
month
DATEDIFF(d1,d2) Days between two DATEDIFF(NOW(), hire_date)
dates
DATE_ADD(d,INTERVAL) Add time to a date DATE_ADD(hire_date,
INTERVAL 1 YEAR)
DATE_FORMAT(d,fmt) Format a date as DATE_FORMAT(hire_date,'%d-
string %m-%Y')

-- Years of experience for each employee

SELECT name, hire_date,
FLOOR(DATEDIFF(CURDATE(), hire_date) / 365) AS years_experience
FROM employees;

-- Employees hired in 2020

SELECT name, hire_date FROM employees WHERE YEAR(hire_date) = 2020;

Page 16 of 28
SQL Complete Guide | Interview & Placement Preparation

09 Window Functions (Advanced — Frequently Asked)

Window functions perform calculations across a set of rows related to the current row — without
collapsing rows like GROUP BY does. They are very popular in mid-to-senior level interviews.

💡 Syntax: FUNCTION() OVER (PARTITION BY col ORDER BY col) — Think of PARTITION BY as

GROUP BY for window functions.

ROW_NUMBER, RANK, DENSE_RANK

SELECT
name, dept_id, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS row_num,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS
rank_num,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS
dense_num
FROM employees;

Example (scores
Function Ties Handling
100,100,90)
ROW_NUMBER() No ties — unique sequential 1, 2, 3
number
RANK() Ties get same rank; next rank 1, 1, 3
skips
DENSE_RANK() Ties get same rank; next rank is 1, 1, 2
consecutive

Common Interview Question: Top N per Group

-- Top 2 highest-paid employees in each department
SELECT name, dept_id, salary
FROM (
SELECT name, dept_id, salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk <= 2;

LAG and LEAD

Access data from previous (LAG) or next (LEAD) rows without a self-join.
SELECT
name, salary,
LAG(salary, 1) OVER (ORDER BY salary) AS prev_salary,

Page 17 of 28
SQL Complete Guide | Interview & Placement Preparation

LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary

FROM employees;

SUM / AVG as Window Functions (Running Totals)

-- Running total of salary (ordered by hire date)
SELECT
name, hire_date, salary,
SUM(salary) OVER (ORDER BY hire_date) AS running_total,
AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg_salary
FROM employees;

NTILE — Divide Rows into Buckets

-- Divide employees into 4 salary quartiles
SELECT
name, salary,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

Page 18 of 28
SQL Complete Guide | Interview & Placement Preparation

10 SET Operations & CTEs

UNION and UNION ALL

Combine results of two SELECT queries.

Operator Duplicates Performance

UNION Removes duplicate rows Slower (sorts to find
duplicates)
UNION ALL Keeps all rows including Faster (no deduplication)
duplicates

-- Employees from Dept 10 OR Dept 20 (no duplicates)

SELECT name, dept_id FROM employees WHERE dept_id = 10
UNION
SELECT name, dept_id FROM employees WHERE dept_id = 20;

-- All records including duplicates

SELECT name FROM employees WHERE dept_id = 10
UNION ALL
SELECT name FROM employees WHERE dept_id = 10;

💡 Both SELECT statements in a UNION must have the same number of columns with compatible
data types.

INTERSECT and EXCEPT

-- INTERSECT: rows common to both queries
SELECT emp_id FROM employees
INTERSECT
SELECT emp_id FROM projects;

-- EXCEPT (MINUS in Oracle): rows in first but not second

SELECT emp_id FROM employees
EXCEPT
SELECT emp_id FROM projects;
-- Returns employees who have NO project

CTE — Common Table Expressions (WITH clause)

A CTE is a named temporary result set. It makes complex queries readable and is very popular in
modern SQL interviews.
-- Simple CTE
WITH high_earners AS (
SELECT name, salary, dept_id
FROM employees

Page 19 of 28
SQL Complete Guide | Interview & Placement Preparation

WHERE salary > 60000

)
SELECT [Link], d.dept_name
FROM high_earners h
JOIN departments d ON h.dept_id = d.dept_id;

-- Multiple CTEs
WITH
dept_avg AS (
SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id
),
above_avg AS (
SELECT [Link], [Link], e.dept_id
FROM employees e
JOIN dept_avg da ON e.dept_id = da.dept_id
WHERE [Link] > da.avg_sal
)
SELECT [Link], d.dept_name, [Link]
FROM above_avg a
JOIN departments d ON a.dept_id = d.dept_id;

Recursive CTE — Hierarchy Queries

-- Get full management hierarchy (employee -> manager -> their manager...)
WITH RECURSIVE emp_hierarchy AS (
-- Base case: top-level manager (no manager_id)
SELECT emp_id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL

UNION ALL

-- Recursive case: join with subordinates

SELECT e.emp_id, [Link], e.manager_id, [Link] + 1
FROM employees e
INNER JOIN emp_hierarchy h ON e.manager_id = h.emp_id
)
SELECT name, level FROM emp_hierarchy ORDER BY level, name;

Page 20 of 28
SQL Complete Guide | Interview & Placement Preparation

11 CASE Statement, Views & Indexes

CASE — Conditional Logic in SQL

CASE works like IF-ELSE in programming languages. Extremely common in interviews.
-- Simple CASE: Salary bands
SELECT
name, salary,
CASE
WHEN salary >= 80000 THEN 'Senior'
WHEN salary >= 60000 THEN 'Mid-Level'
WHEN salary >= 40000 THEN 'Junior'
ELSE 'Intern'
END AS salary_band
FROM employees;

-- CASE in aggregate: count by band

SELECT
SUM(CASE WHEN salary >= 80000 THEN 1 ELSE 0 END) AS senior_count,
SUM(CASE WHEN salary >= 60000 AND salary < 80000 THEN 1 ELSE 0 END) AS
mid_count,
SUM(CASE WHEN salary < 60000 THEN 1 ELSE 0 END) AS junior_count
FROM employees;

COALESCE — Handle NULL Values

-- Replace NULL manager_id with 'No Manager'
SELECT name, COALESCE(CAST(manager_id AS CHAR), 'No Manager') AS manager
FROM employees;

-- IFNULL (MySQL specific): same idea

SELECT name, IFNULL(manager_id, 0) AS manager_id FROM employees;

Views — Virtual Tables

A View is a stored SELECT query that behaves like a table. It does NOT store data itself.
-- Create a view
CREATE VIEW emp_dept_view AS
SELECT e.emp_id, [Link], [Link], d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

-- Query the view just like a table

SELECT * FROM emp_dept_view WHERE salary > 60000;

-- Drop a view
DROP VIEW emp_dept_view;

Page 21 of 28
SQL Complete Guide | Interview & Placement Preparation

Feature View Table

Stores data? No (just stores the query) Yes
Always up to date? Yes (runs query each time) Only when modified
Performance? Can be slower on large data Fast with indexes
Use case Simplify complex queries, Store actual data
security

Indexes — Speed Up Queries

An index is a data structure that allows faster data retrieval. Like an index in a book.
-- Create an index on salary column
CREATE INDEX idx_salary ON employees(salary);

-- Composite index (multiple columns)

CREATE INDEX idx_dept_salary ON employees(dept_id, salary);

-- Unique index
CREATE UNIQUE INDEX idx_emp_name ON employees(name);

-- Drop an index
DROP INDEX idx_salary ON employees;

💡 Indexes speed up SELECT but slow down INSERT/UPDATE/DELETE. Use them on columns
frequently used in WHERE, JOIN, and ORDER BY clauses.

Page 22 of 28
SQL Complete Guide | Interview & Placement Preparation

12 Classic SQL Interview Questions & Solutions

These are the most frequently asked SQL questions across companies like TCS, Infosys, Wipro,
Amazon, Flipkart, and startups.

Q1. Find the 2nd Highest Salary

-- Method 1: Using LIMIT/OFFSET
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- Method 2: Using Subquery (works everywhere)

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

-- Method 3: Using DENSE_RANK (best — handles ties)

SELECT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) t WHERE rnk = 2;

-- Generic: Nth highest salary (replace 2 with N)

SELECT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) t WHERE rnk = 3; -- 3rd highest

Q2. Find Duplicate Rows

-- Find duplicate names
SELECT name, COUNT(*) AS occurrences
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

-- Show full details of duplicate rows

SELECT * FROM employees
WHERE name IN (
SELECT name FROM employees GROUP BY name HAVING COUNT(*) > 1
);

Q3. Delete Duplicate Rows (Keep One)

-- Keep the row with the lowest emp_id, delete others
DELETE FROM employees

Page 23 of 28
SQL Complete Guide | Interview & Placement Preparation

WHERE emp_id NOT IN (

SELECT MIN(emp_id)
FROM employees
GROUP BY name
);

Q4. Employees with Salary Above Department Average

SELECT [Link], [Link], e.dept_id
FROM employees e
JOIN (
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
) dept_avg ON e.dept_id = dept_avg.dept_id
WHERE [Link] > dept_avg.avg_sal
ORDER BY e.dept_id, [Link] DESC;

Q5. Department with Highest Average Salary

SELECT d.dept_name, AVG([Link]) AS avg_salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_name
ORDER BY avg_salary DESC
LIMIT 1;

Q6. Employees Who Are Also Managers

SELECT DISTINCT [Link] AS manager_name
FROM employees e
WHERE e.emp_id IN (SELECT DISTINCT manager_id FROM employees WHERE manager_id
IS NOT NULL);

Q7. Find Employees With No Project

-- Using NOT IN
SELECT name FROM employees
WHERE emp_id NOT IN (SELECT emp_id FROM projects);

-- Using LEFT JOIN (preferred — handles NULLs better)

SELECT [Link]
FROM employees e
LEFT JOIN projects p ON e.emp_id = p.emp_id
WHERE p.proj_id IS NULL;

Page 24 of 28
SQL Complete Guide | Interview & Placement Preparation

Q8. Swap Male/Female Gender Values

-- Using CASE
UPDATE employees
SET gender = CASE
WHEN gender = 'Male' THEN 'Female'
WHEN gender = 'Female' THEN 'Male'
ELSE gender
END;

Q9. Rolling/Cumulative Sum

SELECT
hire_date, name, salary,
SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW)
AS cumulative_salary
FROM employees
ORDER BY hire_date;

Q10. Pivot — Rows to Columns

-- Count of employees per department shown as separate columns
SELECT
SUM(CASE WHEN dept_id = 10 THEN 1 ELSE 0 END) AS Engineering,
SUM(CASE WHEN dept_id = 20 THEN 1 ELSE 0 END) AS Marketing,
SUM(CASE WHEN dept_id = 30 THEN 1 ELSE 0 END) AS HR
FROM employees;

Page 25 of 28
SQL Complete Guide | Interview & Placement Preparation

13 Transactions, Keys & Normalization

Transactions (TCL)
A transaction is a group of SQL operations that execute as a single unit. Either all succeed, or none do
(ACID principle).
START TRANSACTION;

UPDATE employees SET salary = salary - 5000 WHERE emp_id = 101;

UPDATE employees SET salary = salary + 5000 WHERE emp_id = 102;

-- If everything is correct:
COMMIT;

-- If something went wrong:

ROLLBACK;

-- Partial rollback using SAVEPOINT

SAVEPOINT before_update;
UPDATE employees SET salary = 99999 WHERE emp_id = 103;
ROLLBACK TO before_update; -- Undo only back to savepoint
COMMIT;

ACID Property Meaning

Atomicity All operations succeed or all fail — no partial commits
Consistency Database moves from one valid state to another
Isolation Concurrent transactions don't interfere with each other
Durability Committed data persists even after a crash

Keys in SQL
Key Type Description Example
Primary Key Uniquely identifies each row; emp_id in employees
NOT NULL + UNIQUE
Foreign Key Links to Primary Key in another dept_id references
table departments
Candidate Key Columns that could be a primary emp_id, email (both
key unique)
Composite Key Primary key made of 2+ columns student_id + subject_id
Unique Key Ensures column values are email VARCHAR
unique; can be NULL UNIQUE
Super Key Any set of columns that uniquely emp_id, or emp_id+name
identifies a row

Page 26 of 28
SQL Complete Guide | Interview & Placement Preparation

Normalization — Quick Overview

Normalization organizes tables to reduce redundancy and dependency.

Normal
Rule Fixes
Form
1NF No repeating groups; each cell has Stores multiple values in
atomic value one column
2NF Must be 1NF + No partial Attributes depending on
dependency on PK part of composite PK
3NF Must be 2NF + No transitive Column A → B → C
dependencies (should be A → C directly)
BCNF Stricter 3NF; every determinant Overlapping candidate
must be a candidate key keys

Stored Procedures vs Functions

Feature Stored Procedure Function
Returns value? Optional (OUT parameter) Mandatory (RETURN)
Called via CALL procedure_name() SELECT
function_name()
DML allowed? Yes (INSERT, UPDATE, Generally No
DELETE)
Use in SELECT? No Yes
Transaction Yes No
control?

Page 27 of 28
SQL Complete Guide | Interview & Placement Preparation

📋 Quick Reference Cheat Sheet

-- CREATE TABLE
CREATE TABLE table_name (col1 datatype constraints, col2 datatype ...);

-- SELECT with everything

SELECT col1, AGG(col2) AS alias
FROM table1 t1
JOIN table2 t2 ON [Link] = [Link]
WHERE condition
GROUP BY col1
HAVING AGG_condition
ORDER BY col1 [ASC|DESC]
LIMIT n OFFSET m;

-- Common patterns
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM
employees); -- 2nd max
SELECT * FROM employees WHERE salary BETWEEN 50000 AND 80000;
SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id HAVING COUNT(*) > 2;
SELECT name FROM employees WHERE emp_id NOT IN (SELECT emp_id FROM projects);

-- Window functions
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC)
LAG(salary, 1) OVER (ORDER BY hire_date)
SUM(salary) OVER (PARTITION BY dept_id)

-- CTE
WITH cte_name AS (SELECT ...) SELECT * FROM cte_name;

Best of luck in your interviews! Practice these queries on MySQL Workbench or DB

Fiddle ([Link])

Page 28 of 28