SQL Interview Questions

Complete Guide: Easy • Medium • Hard

Generated: August 04, 2026

1. BASICS & SELECT STATEMENTS

Easy

Q1. What is SQL?

Answer: SQL (Structured Query Language) is a standard language used to manage and
manipulate relational databases. It allows users to create, read, update, and delete
data.

Q2. Write a query to select all columns from a table named 'employees'
Answer: SELECT * FROM employees;

Q3. How do you select specific columns?

Answer: SELECT column1, column2, column3 FROM table_name;

Medium

Q1. What's the difference between DISTINCT and GROUP BY?

Answer: DISTINCT removes duplicate rows from results. GROUP BY groups rows by
specified columns and is used with aggregate functions. GROUP BY is more powerful for
analysis.

Q2. Write a query to get employees earning between 50000 and 100000
Answer: SELECT * FROM employees WHERE salary BETWEEN 50000 AND 100000;

Q3. How do you limit results to first 10 rows?

Answer: SELECT * FROM employees LIMIT 10; (or use FETCH FIRST 10 ROWS ONLY in some
databases)

Hard

Q1. Write a query to find employees with salaries in top 10%

Answer: SELECT * FROM employees WHERE salary >= (SELECT PERCENTILE_CONT(0.9) WITHIN
GROUP (ORDER BY salary) FROM employees);

Q2. How would you handle NULL values comparison?

 Answer: Use 'IS NULL' or 'IS NOT NULL'. Regular comparison operators (=, !=) don't
work with NULL. Example: SELECT * FROM employees WHERE manager_id IS NULL;

Q3. Explain the difference between WHERE and HAVING clauses

Answer: WHERE filters rows before grouping. HAVING filters groups after aggregation.
WHERE works on individual rows; HAVING works on aggregated results.

2. WHERE & FILTERING

Easy

Q1. Write a query to find employees from 'IT' department

Answer: SELECT * FROM employees WHERE department = 'IT';

Q2. How do you use AND operator?

Answer: SELECT * FROM employees WHERE department = 'IT' AND salary > 60000;

Q3. Write a query using OR operator

Answer: SELECT * FROM employees WHERE department = 'IT' OR department = 'HR';

Medium

Q1. What's the difference between IN and OR?

Answer: Both can achieve same results. IN is cleaner: SELECT * FROM employees WHERE
department IN ('IT', 'HR', 'Finance'); vs WHERE department='IT' OR department='HR' OR
department='Finance';

Q2. Write a query to find names containing 'John'

Answer: SELECT * FROM employees WHERE name LIKE '%John%';

Q3. How do you use NOT operator?

Answer: SELECT * FROM employees WHERE NOT department = 'IT'; or SELECT * FROM
employees WHERE department != 'IT';

Hard

Q1. Write a case-insensitive search query

Answer: SELECT * FROM employees WHERE LOWER(name) LIKE LOWER('%john%');

Q2. How do you filter using multiple conditions efficiently?

 Answer: Use parentheses and logical operators: SELECT * FROM employees WHERE
(department IN ('IT', 'HR')) AND (salary > 50000 OR years_experience > 5);

Q3. Write a query to find records with pattern matching

Answer: SELECT * FROM employees WHERE name LIKE '[A-M]%'; (or REGEXP in MySQL: WHERE
name REGEXP '^[A-M]');

3. JOINS

Easy

Q1. What is INNER JOIN?

Answer: INNER JOIN returns only rows that have matching values in both tables. Syntax:
SELECT * FROM table1 INNER JOIN table2 ON [Link] = [Link];

Q2. What is LEFT JOIN?

Answer: LEFT JOIN returns all rows from left table and matching rows from right table.
Non-matching rows from right table show NULL values.

Q3. Write an INNER JOIN query between employees and departments

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

Medium

Q1. What's the difference between LEFT and RIGHT JOIN?

Answer: LEFT JOIN keeps all rows from left table. RIGHT JOIN keeps all rows from right
table. RIGHT JOIN is less common; can be rewritten as LEFT JOIN by switching tables.

Q2. How do you perform FULL OUTER JOIN?

Answer: SELECT * FROM table1 FULL OUTER JOIN table2 ON [Link] = [Link]; (Note:
MySQL doesn't support FULL JOIN; use UNION with LEFT and RIGHT joins)

Q3. Write a query joining 3 tables

Answer: SELECT [Link], d.dept_name, p.project_name FROM employees e JOIN departments d
ON e.dept_id = [Link] JOIN projects p ON [Link] = p.emp_id;

Hard

Q1. What is a CROSS JOIN?

Answer: CROSS JOIN produces Cartesian product - every row from table1 matched with
every row from table2. SELECT * FROM employees CROSS JOIN projects; (gives emp_count *
 project_count rows)

Q2. How do you find unmatched records between two tables?

Answer: SELECT * FROM table1 LEFT JOIN table2 ON [Link] = [Link] WHERE [Link]
IS NULL;

Q3. Explain self-join with an example

Answer: Self-join joins a table to itself. Example - find employees and their
managers: SELECT [Link] as employee, [Link] as manager FROM employees e LEFT JOIN
employees m ON e.manager_id = [Link];

4. AGGREGATION & GROUP BY

Easy

Q1. What is COUNT function?

Answer: COUNT returns number of rows. COUNT(*) counts all rows including NULLs.
COUNT(column) counts non-NULL values only.

Q2. Write a query to find total salary expense

Answer: SELECT SUM(salary) as total_salary FROM employees;

Q3. How do you find average salary?

Answer: SELECT AVG(salary) as avg_salary FROM employees;

Medium

Q1. Write a GROUP BY query to find average salary by department

Answer: SELECT department, AVG(salary) as avg_salary FROM employees GROUP BY
department;

Q2. What's the difference between COUNT(*) and COUNT(column)?

Answer: COUNT(*) includes NULL values. COUNT(column) excludes NULL values. Example:
COUNT(*) = 100 but COUNT(manager_id) = 95 means 5 employees have NULL manager_id.

Q3. Write a query using GROUP BY with multiple columns

Answer: SELECT department, job_title, COUNT(*) as count FROM employees GROUP BY
department, job_title;

Hard

Q1. Write a query to find departments with average salary > 75000
 Answer: SELECT department, AVG(salary) as avg_salary FROM employees GROUP BY
department HAVING AVG(salary) > 75000;

Q2. How do you find employees in top-paid department?

Answer: SELECT * FROM employees WHERE department = (SELECT department FROM employees
GROUP BY department ORDER BY AVG(salary) DESC LIMIT 1);

Q3. Write a query to rank values: SELECT department, salary, ROW_NUMBER() OVER (PARTITION
BY department ORDER BY salary DESC) as rank FROM employees;
Answer: This uses window functions to rank salaries within each department.

5. SUBQUERIES

Easy

Q1. What is a subquery?

Answer: A subquery is a query within another query. Also called inner query or inner
select. Used in WHERE, FROM, or SELECT clauses.

Q2. Write a query to find employees earning more than average

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

Q3. Write a subquery in FROM clause

Answer: SELECT * FROM (SELECT * FROM employees WHERE department='IT') as it_employees;

Medium

Q1. What's the difference between scalar and row subqueries?

Answer: Scalar subquery returns single value (1 row, 1 column). Row subquery returns 1
row but multiple columns. Scalar can be used with =; row subqueries used with IN or
EXISTS.

Q2. Write a query using IN with subquery

Answer: SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments
WHERE budget > 100000);

Q3. Explain correlated subquery

Answer: A subquery that references columns from outer query. Executed once for each
outer row. Example: SELECT * FROM e1 WHERE salary > (SELECT AVG(salary) FROM employees
e2 WHERE [Link] = [Link]);

Hard
 Q1. Write a query using EXISTS
Answer: SELECT * FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE
e.dept_id = [Link] AND [Link] > 100000);

Q2. What's the difference between IN and EXISTS?

Answer: IN checks if value exists in list. EXISTS checks if subquery returns rows.
EXISTS is often faster for large datasets. NOT EXISTS is useful for finding
non-matching records.

Q3. Write a query to find 2nd highest salary using subquery

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

6. ORDER BY & SORTING

Easy

Q1. How do you sort results in ascending order?

Answer: SELECT * FROM employees ORDER BY salary ASC; (ASC is default)

Q2. How do you sort in descending order?

Answer: SELECT * FROM employees ORDER BY salary DESC;

Q3. How do you sort by multiple columns?

Answer: SELECT * FROM employees ORDER BY department ASC, salary DESC;

Medium

Q1. How do you handle NULL values in ORDER BY?

Answer: Different databases handle differently. In MySQL: ORDER BY
COALESCE(commission, 0) DESC; or use: ORDER BY column IS NULL, column DESC;

Q2. Write a query to find top 5 paid employees

Answer: SELECT * FROM employees ORDER BY salary DESC LIMIT 5;

Q3. How do you sort by expression?

Answer: SELECT *, (salary * 12) as annual FROM employees ORDER BY (salary * 12) DESC;

Hard

Q1. How do you randomize results?

 Answer: SELECT * FROM employees ORDER BY RAND(); (MySQL) or ORDER BY RANDOM();
(SQLite)

Q2. Write a query to sort dates properly

Answer: SELECT * FROM employees ORDER BY CAST(hire_date AS DATE) DESC; or ORDER BY
STR_TO_DATE(hire_date, '%d-%m-%Y') DESC;

Q3. How do you use CASE in ORDER BY?

Answer: SELECT * FROM employees ORDER BY CASE WHEN department='IT' THEN 1 WHEN
department='HR' THEN 2 ELSE 3 END, salary DESC;

7. WINDOW FUNCTIONS

Easy

Q1. What is a window function?

Answer: Window functions perform calculations over a set of rows (window) related to
current row. Include: ROW_NUMBER(), RANK(), LAG(), LEAD(), etc.

Q2. What is ROW_NUMBER()?

Answer: Assigns unique sequential number to rows. Syntax: SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) as rank FROM employees;

Q3. Write a query using RANK() function

Answer: SELECT name, salary, RANK() OVER (ORDER BY salary DESC) as rank FROM
employees; (RANK() assigns same rank to ties, skips next ranks)

Medium

Q1. What's difference between ROW_NUMBER, RANK, DENSE_RANK?

Answer: ROW_NUMBER: 1,2,3,4 RANK: 1,2,2,4 DENSE_RANK: 1,2,2,3. Use PARTITION BY to
rank within groups.

Q2. Write a query using LAG and LEAD

Answer: SELECT name, salary, LAG(salary) OVER (ORDER BY hire_date) as prev_salary,
LEAD(salary) OVER (ORDER BY hire_date) as next_salary FROM employees;

Q3. Write a partitioned window function query

Answer: SELECT dept, name, salary, RANK() OVER (PARTITION BY dept ORDER BY salary
DESC) as dept_rank FROM employees;

Hard
 Q1. Write a query to find running total
Answer: SELECT name, salary, SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW) as running_total FROM employees;

Q2. How do you use PERCENT_RANK()?

Answer: SELECT name, salary, PERCENT_RANK() OVER (ORDER BY salary) as percentile FROM
employees; (Returns decimal 0-1 showing percentage rank)

Q3. Write a query to calculate moving average

Answer: SELECT date, sales, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING
AND CURRENT ROW) as moving_avg FROM sales_data;

8. INDEXES & PERFORMANCE

Easy

Q1. What is an index?

Answer: Index is database structure that improves query performance. Works like book
index - speeds up data retrieval. Slows down INSERT/UPDATE/DELETE.

Q2. How do you create an index?

Answer: CREATE INDEX idx_name ON employees(last_name);

Q3. What is a primary key index?

Answer: Primary key index uniquely identifies each row. Automatically created as
clustered index (physical ordering). Ensures uniqueness and NOT NULL.

Medium

Q1. What's difference between clustered and non-clustered index?

Answer: Clustered: physical order of table (only 1 per table, usually on primary key).
Non-clustered: separate structure pointing to data (up to 999 per table). Clustered is
faster.

Q2. How do you create composite index?

Answer: CREATE INDEX idx_dept_salary ON employees(department, salary); (Order
matters!)

Q3. Why might an index slow things down?

Answer: Indexes speed SELECT but slow INSERT/UPDATE/DELETE. Too many indexes waste
space. Unused indexes waste resources. Table needs balanced indexing.
 Hard

Q1. What is index cardinality?

Answer: Ratio of unique values to total rows. High cardinality (many unique values) =
good for indexing. Low cardinality (few unique values) = indexing less beneficial.

Q2. How do you use EXPLAIN to analyze queries?

Answer: EXPLAIN SELECT * FROM employees WHERE salary > 100000; Shows query execution
plan, index usage, rows scanned. Look for 'type' = ALL (bad, full scan).

Q3. How do you avoid index fragmentation?

Answer: Periodically rebuild indexes: REBUILD INDEX idx_name; or DEFRAGMENT. In MySQL:
OPTIMIZE TABLE; Fragmented indexes degrade performance over time.

9. TRANSACTIONS & ACID

Easy

Q1. What is a transaction?

Answer: Sequence of SQL operations treated as single unit. Either all succeed (commit)
or all fail (rollback). Ensures data consistency.

Q2. What does ACID stand for?

Answer: Atomicity (all or nothing), Consistency (valid state), Isolation
(independent), Durability (permanent once committed).

Q3. How do you start a transaction?

Answer: BEGIN; or START TRANSACTION; Then run queries. End with COMMIT; or ROLLBACK;

Medium

Q1. Write a transaction example

Answer: BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE
accounts SET balance = balance + 100 WHERE id = 2; COMMIT;

Q2. What is SAVEPOINT?

Answer: Partial rollback point within transaction. SAVEPOINT sp1; ... ROLLBACK TO sp1;
(Reverts to savepoint, not full rollback)

Q3. What are transaction isolation levels?

Answer: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Higher levels
= more consistency but lower concurrency.
 Hard

Q1. Explain dirty read, non-repeatable read, phantom read

Answer: Dirty: read uncommitted data Non-repeatable: same query returns different data
in one transaction Phantom: new rows appear during transaction

Q2. What is deadlock?

Answer: Two transactions wait for each other's locks. DB detects and rolls back one
transaction. Example: T1 locks A waits for B, T2 locks B waits for A.

Q3. How do you handle concurrency issues?

Answer: Use appropriate isolation level, lock timeout, retry logic, and
optimistic/pessimistic locking strategies. Row-level locks better than table-level.

10. STORED PROCEDURES & FUNCTIONS

Easy

Q1. What is a stored procedure?

Answer: Precompiled SQL code stored in database. Executed repeatedly without
recompiling. Can have input/output parameters and control logic.

Q2. Write a simple stored procedure

Answer: CREATE PROCEDURE GetEmployees AS SELECT * FROM employees; -- Call: EXEC
GetEmployees;

Q3. What's difference between procedure and function?

Answer: Procedure: executes actions, returns 0+ result sets Function: returns single
value, can be used in expressions

Medium

Q1. Write a procedure with parameters

Answer: CREATE PROCEDURE GetDeptEmployees @dept VARCHAR(50) AS SELECT * FROM employees
WHERE department = @dept; -- EXEC GetDeptEmployees @dept='IT';

Q2. Write a function to calculate bonus

Answer: CREATE FUNCTION CalculateBonus(@salary DECIMAL) RETURNS DECIMAL AS BEGIN
RETURN @salary * 0.1; END;

Q3. What are advantages of stored procedures?

 Answer: Better performance (precompiled), security (user doesn't need table access),
reusability, reduced network traffic, business logic in DB

Hard

Q1. Write procedure with error handling

Answer: CREATE PROCEDURE UpdateSalary @empId INT, @newSalary DECIMAL AS BEGIN BEGIN
TRY UPDATE employees SET salary=@newSalary WHERE id=@empId; COMMIT; END TRY BEGIN
CATCH ROLLBACK; THROW; END CATCH END;

Q2. How do you handle output parameters?

Answer: CREATE PROCEDURE GetAvgSalary @dept VARCHAR(50), @avgSalary DECIMAL OUTPUT AS
SELECT @avgSalary = AVG(salary) FROM employees WHERE department = @dept;

Q3. Explain recursive stored procedures

Answer: Procedures that call themselves. Example: hierarchical data traversal. Must
have base case to avoid infinite recursion.

11. VIEWS & MATERIALIZED VIEWS

Easy

Q1. What is a view?

Answer: Virtual table based on SELECT query. Doesn't store data, just query
definition. Simplifies complex queries and provides security.

Q2. How do you create a view?

Answer: CREATE VIEW employee_details AS SELECT id, name, salary FROM employees WHERE
status='active';

Q3. How do you drop a view?

Answer: DROP VIEW employee_details;

Medium

Q1. What's the difference between view and table?

Answer: Table stores actual data on disk. View is stored query, generates data
on-the-fly. Views take no storage, always current but slower for large queries.

Q2. Write a view with JOIN

Answer: CREATE VIEW employee_dept_info AS SELECT [Link], [Link], d.dept_name FROM
employees e JOIN departments d ON e.dept_id = [Link];
 Q3. Can you update data through a view?
Answer: Yes, if view meets criteria: single table, no aggregates/DISTINCT/GROUP
BY/UNION, includes primary key. Most views are read-only.

Hard

Q1. What is a materialized view?

Answer: View that stores query results physically (like snapshot). Faster queries but
stale data. Must refresh periodically. Used for heavy computations.

Q2. How do you create indexed view?

Answer: CREATE UNIQUE CLUSTERED INDEX idx_view ON view_name(column); (SQL Server) -
Converts view to materialized version

Q3. Explain view updatability rules

Answer: Can update simple views. Can't update aggregates, complex JOINs, UNION, or
calculated columns without INSTEAD OF triggers.

12. UNIONS & SET OPERATIONS

Easy

Q1. What is UNION?

Answer: Combines results from 2+ SELECT queries, removes duplicates. Columns must be
same type and count. Syntax: SELECT a FROM t1 UNION SELECT a FROM t2;

Q2. What is UNION ALL?

Answer: Like UNION but keeps duplicates. Faster than UNION (no duplicate check). Use
when you know data has no duplicates.

Q3. What is INTERSECT?

Answer: Returns rows present in both queries. Syntax: SELECT col FROM table1 INTERSECT
SELECT col FROM table2;

Medium

Q1. What is EXCEPT / MINUS?

Answer: Returns rows in first query but not in second. Syntax: SELECT col FROM table1
EXCEPT SELECT col FROM table2; (MINUS in Oracle)

Q2. Write query combining multiple UNION

 Answer: SELECT name FROM employees WHERE dept='IT' UNION SELECT name FROM contractors
WHERE dept='IT' UNION SELECT name FROM temps WHERE dept='IT';

Q3. Why is column order important in UNION?

Answer: UNION matches columns by position, not name. First query defines column names
in result. Columns must have compatible datatypes.

Hard

Q1. How do you order results with UNION?

Answer: Apply ORDER BY to entire UNION query: (SELECT col FROM t1) UNION (SELECT col
FROM t2) ORDER BY col DESC; Put ORDER BY at end only.

Q2. Difference between UNION JOIN and regular JOIN

Answer: Regular JOIN matches rows on condition. UNION stacks result sets vertically.
JOIN is horizontal; UNION is vertical.

Q3. Write query using both JOIN and UNION

Answer: (SELECT [Link], 'Employee' as type FROM employees e WHERE salary > 100000)
UNION (SELECT [Link], 'Contractor' FROM contractors c WHERE rate > 80);

13. STRING & DATE FUNCTIONS

Easy

Q1. How do you get string length?

Answer: SELECT LENGTH(name) FROM employees; (or LEN in SQL Server)

Q2. How do you convert to uppercase?

Answer: SELECT UPPER(name) FROM employees; (or LOWER for lowercase)

Q3. How do you get current date?

Answer: SELECT GETDATE(); (SQL Server) or SELECT NOW(); (MySQL) or SELECT
CURRENT_DATE;

Medium

Q1. How do you extract substring?

Answer: SELECT SUBSTRING(name, 1, 3) FROM employees; Returns first 3 characters.
SUBSTR in Oracle/MySQL.

Q2. How do you find string position?

 Answer: SELECT INSTR(email, '@') FROM employees; Returns position of character.
CHARINDEX in SQL Server.

Q3. How do you add days to date?

Answer: SELECT DATE_ADD(hire_date, INTERVAL 30 DAY) FROM employees; (MySQL) or
DATEADD(day, 30, hire_date) (SQL Server)

Hard

Q1. How do you calculate age from birthdate?

Answer: SELECT DATEDIFF(YEAR, birthdate, GETDATE()) as age FROM employees; (SQL
Server) or TIMESTAMPDIFF(YEAR, birthdate, NOW()) (MySQL)

Q2. How do you format dates?

Answer: SELECT FORMAT(hire_date, 'dd-MM-yyyy') FROM employees; (SQL Server) or
DATE_FORMAT(hire_date, '%d-%m-%Y') (MySQL)

Q3. How do you parse string?

Answer: SELECT REPLACE(email, '@[Link]', '') FROM employees; REPLACE, TRIM, LTRIM,
RTRIM are common functions

14. CASE STATEMENTS

Easy

Q1. What is CASE statement?

Answer: Conditional logic in SQL. Returns different values based on conditions. Can
use in SELECT, WHERE, ORDER BY.

Q2. Write simple CASE example

Answer: SELECT name, CASE WHEN salary > 100000 THEN 'High' WHEN salary > 50000 THEN
'Medium' ELSE 'Low' END as salary_level FROM employees;

Q3. What is simple CASE vs searched CASE?

Answer: Simple: CASE column WHEN value THEN result... Searched: CASE WHEN condition
THEN result... Searched is more flexible.

Medium

Q1. Write CASE in WHERE clause

Answer: SELECT * FROM employees WHERE CASE WHEN department='IT' THEN salary > 80000
WHEN department='HR' THEN salary > 50000 ELSE salary > 40000 END;
 Q2. How do you use CASE for categorization?
Answer: SELECT name, CASE WHEN years >= 10 THEN 'Senior' WHEN years >= 5 THEN 'Mid'
ELSE 'Junior' END as level FROM employees;

Q3. Write nested CASE

Answer: SELECT CASE WHEN dept='IT' THEN CASE WHEN salary>100000 THEN 'IT-High' ELSE
'IT-Low' END ELSE 'Other' END FROM employees;

Hard

Q1. How do you use CASE with aggregates?

Answer: SELECT COUNT(CASE WHEN salary > 100000 THEN 1 END) as high_earners FROM
employees; (COUNT only counts non-NULL results)

Q2. Write pivot-like query with CASE

Answer: SELECT dept, SUM(CASE WHEN gender='M' THEN 1 ELSE 0 END) as males, SUM(CASE
WHEN gender='F' THEN 1 ELSE 0 END) as females FROM employees GROUP BY dept;

Q3. How do you use CASE with NULL handling?

Answer: SELECT CASE WHEN bonus IS NULL THEN 0 ELSE bonus END as bonus_amount FROM
employees; (Or use COALESCE(bonus, 0))

15. QUERY OPTIMIZATION

Easy

Q1. What is query optimization?

Answer: Process of improving SQL query performance. Reduce execution time, CPU usage,
I/O operations. Use indexes, better queries, proper joins.

Q2. How do you measure query performance?

Answer: Use EXPLAIN/ANALYZE to see execution plan. Check 'rows' scanned. Use timing
tools. Look for full table scans (bad).

Q3. Why avoid SELECT *?

Answer: Returns all columns (wastes bandwidth), harder to use indexes, less clear
intent. Use specific columns needed.

Medium

Q1. How do you optimize JOIN performance?

 Answer: Create indexes on join columns. Ensure join order is correct (filter early).
Avoid redundant columns. Consider materialized views for complex joins.

Q2. What is query plan?

Answer: Shows how database executes query. Includes access method (scan/seek), join
type, filter steps. EXPLAIN shows plan without executing.

Q3. How do you optimize GROUP BY?

Answer: Index on GROUP BY columns. Filter in WHERE before grouping. Use HAVING after
grouping. Avoid complex aggregates.

Hard

Q1. Explain query hints and when to use them

Answer: Force optimizer decisions: USE INDEX, IGNORE INDEX, FORCE INDEX. Use when
optimizer chooses wrong plan but sparingly - may fail on data changes.

Q2. What is table statistics?

Answer: DB maintains stats on column distributions. Optimizer uses for planning.
Outdated stats cause bad plans. Update regularly: ANALYZE TABLE; or UPDATE STATISTICS.

Q3. How do you identify bottlenecks?

Answer: Use execution plans, slow query logs, profilers. Check I/O vs CPU. Look for
missing indexes. Check for missing statistics. Profile at different data volumes.
 Quick Reference Guide
Clause/Function Purpose Example

SELECT Choose columns SELECT col1, col2

FROM Specify table FROM table_name

WHERE Filter rows WHERE salary > 50000

ORDER BY Sort results ORDER BY salary DESC

GROUP BY Group rows GROUP BY department

HAVING Filter groups HAVING COUNT(*) > 5

JOIN Combine tables INNER/LEFT/RIGHT/FULL JOIN

UNION Combine results UNION / UNION ALL

SUBQUERY Query in query WHERE id IN (SELECT...)

CASE Conditional logic CASE WHEN...THEN...END

INDEX Speed lookup CREATE INDEX idx ON col

Tips for SQL Interviews:

✓ Always start with simple solutions, then optimize
✓ Ask clarifying questions about requirements
✓ Explain your thought process while writing code
✓ Consider edge cases (NULL values, duplicates, empty sets)
✓ Write readable code with proper formatting
✓ Discuss indexing and performance implications
✓ Test your queries mentally with sample data
✓ Know the difference between similar functions (e.g., IN vs EXISTS)
✓ Understand ACID properties and transaction isolation
✓ Practice complex joins and window functions