Top 50 SQL Interview Questions for 2026
29 min read
interview-questions
Advertisement Placement
Top 50 SQL Interview Questions for 2026
Last Updated: March 2026 | Level: Freshers to Mid-Level | Read Time: ~22 min
SQL remains one of the most tested skills in data analyst, backend developer, and data engineer interviews. These 50 questions cover everything from basic queries to window functions and query optimization. SQL is frequently tested alongside DBMS theory — check our DBMS Interview Questions 2026 to cover the conceptual side, and Java Interview Questions 2026 if you're preparing for backend developer roles.
Table of Contents
- Basic Queries (Q1–Q15)
- Joins (Q16–Q25)
- Aggregation & Grouping (Q26–Q33)
- Subqueries & CTEs (Q34–Q40)
- Window Functions (Q41–Q45)
- Optimization & Best Practices (Q46–Q50)
Sample Tables Used Throughout
-- employees table
| emp_id | name | dept_id | salary | hire_date | manager_id |
|--------|-----------|---------|--------|------------|------------|
| 1 | Alice | 10 | 90000 | 2020-01-15 | NULL |
| 2 | Bob | 20 | 75000 | 2021-03-22 | 1 |
| 3 | Charlie | 10 | 85000 | 2019-07-01 | 1 |
| 4 | Diana | 30 | 95000 | 2022-11-10 | 1 |
| 5 | Ethan | 20 | 70000 | 2023-02-14 | 2 |
| 6 | Fiona | NULL | 60000 | 2023-08-01 | 2 |
-- departments table
| dept_id | dept_name | budget |
|---------|-------------|----------|
| 10 | Engineering | 500000 |
| 20 | Marketing | 300000 |
| 30 | Finance | 400000 |
| 40 | HR | 200000 |
Basic Queries
Q1. What is SQL? What are its main categories of commands? Easy
- DDL (Data Definition Language):
CREATE,ALTER,DROP,TRUNCATE - DML (Data Manipulation Language):
SELECT,INSERT,UPDATE,DELETE - DCL (Data Control Language):
GRANT,REVOKE - TCL (Transaction Control Language):
COMMIT,ROLLBACK,SAVEPOINT
Q2. What is the difference between WHERE and HAVING? Easy
-- WHERE: filter rows before grouping
SELECT dept_id, AVG(salary) as avg_salary
FROM employees
WHERE hire_date >= '2020-01-01' -- filter rows first
GROUP BY dept_id;
-- HAVING: filter groups after aggregation
SELECT dept_id, AVG(salary) as avg_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 80000; -- filter groups
-- Output:
-- | dept_id | avg_salary |
-- |---------|------------|
-- | 10 | 87500.00 |
Q3. What is the difference between DELETE, TRUNCATE, and DROP? Easy
DELETE FROM employees WHERE emp_id = 5; -- removes one row, logged, rollback-able
TRUNCATE TABLE temp_data; -- removes all rows, faster, resets auto-increment
DROP TABLE old_employees; -- removes table entirely
Q4. How do you write a query to get the second highest salary? Medium
-- Method 1: Subquery
SELECT MAX(salary) as second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- Output:
-- | second_highest |
-- |----------------|
-- | 90000 |
-- Method 2: LIMIT/OFFSET (MySQL/PostgreSQL)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
-- Method 3: Window Function (most robust for Nth highest)
SELECT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
FROM employees
) ranked
WHERE rnk = 2;
Q5. What is the difference between DISTINCT and GROUP BY? Medium
-- DISTINCT: unique values in one or more columns
SELECT DISTINCT dept_id FROM employees;
-- | dept_id |
-- |---------|
-- | 10 | 20 | 30 | NULL |
-- GROUP BY with aggregate
SELECT dept_id, COUNT(*) as emp_count, AVG(salary) as avg_salary
FROM employees
GROUP BY dept_id;
-- | dept_id | emp_count | avg_salary |
-- |---------|-----------|------------|
-- | 10 | 2 | 87500.00 |
-- | 20 | 2 | 72500.00 |
-- | 30 | 1 | 95000.00 |
-- | NULL | 1 | 60000.00 |
Q6. How do you handle NULL values in SQL? Easy
-- IS NULL check
SELECT name FROM employees WHERE dept_id IS NULL;
-- | name |
-- |-------|
-- | Fiona |
-- COALESCE: substitute default for NULL
SELECT name, COALESCE(dept_id, 0) as dept
FROM employees;
-- NULLIF: avoid division by zero
SELECT salary / NULLIF(hours_worked, 0) as hourly_rate
FROM timesheets;
Q7. What are SQL constraints? List the main types. Easy
PRIMARY KEY: uniquely identifies each row, NOT NULL + UNIQUEFOREIGN KEY: enforces referential integrity between tablesUNIQUE: all values in column must be distinctNOT NULL: column cannot have NULL valuesCHECK: validates values against a conditionDEFAULT: provides a default value
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
amount DECIMAL(10,2) CHECK (amount > 0),
status VARCHAR(20) DEFAULT 'pending',
order_date DATE NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
Q8. What is the LIKE operator? What are wildcards? Easy
-- Names starting with 'A'
SELECT name FROM employees WHERE name LIKE 'A%';
-- | Alice |
-- Names with exactly 3 characters
SELECT name FROM employees WHERE name LIKE '___';
-- Names containing 'li'
SELECT name FROM employees WHERE name LIKE '%li%';
-- | Alice |
-- Department names with underscore (escape)
SELECT * FROM departments WHERE dept_name LIKE '%\_%' ESCAPE '\';
Q9. How do you use ORDER BY with multiple columns? Easy
SELECT name, dept_id, salary
FROM employees
ORDER BY dept_id ASC, salary DESC;
-- Output:
-- | name | dept_id | salary |
-- |---------|---------|--------|
-- | Alice | 10 | 90000 |
-- | Charlie | 10 | 85000 |
-- | Ethan | 20 | 70000 | -- wait, salary should be...
-- | Bob | 20 | 75000 | -- sorted DESC within dept
-- | Diana | 30 | 95000 |
-- | Fiona | NULL | 60000 |
-- NULLs in ORDER BY: NULLS LAST (PostgreSQL)
SELECT * FROM employees ORDER BY dept_id ASC NULLS LAST;
Q10. What is the CASE expression in SQL? Medium
-- Salary classification
SELECT name, salary,
CASE
WHEN salary >= 90000 THEN 'Senior'
WHEN salary >= 75000 THEN 'Mid-Level'
ELSE 'Junior'
END as seniority
FROM employees;
-- Output:
-- | name | salary | seniority |
-- |---------|--------|-----------|
-- | Alice | 90000 | Senior |
-- | Bob | 75000 | Mid-Level |
-- | Charlie | 85000 | Mid-Level |
-- | Diana | 95000 | Senior |
-- | Ethan | 70000 | Junior |
-- Conditional aggregation
SELECT
SUM(CASE WHEN salary >= 90000 THEN 1 ELSE 0 END) as senior_count,
SUM(CASE WHEN salary < 90000 THEN 1 ELSE 0 END) as other_count
FROM employees;
Q11. What is the difference between UNION and UNION ALL? Easy
-- UNION (removes duplicates)
SELECT name FROM employees WHERE dept_id = 10
UNION
SELECT name FROM employees WHERE salary > 80000;
-- Returns unique names matching either condition
-- UNION ALL (keeps duplicates, faster)
SELECT name FROM employees WHERE dept_id = 10
UNION ALL
SELECT name FROM employees WHERE salary > 80000;
-- May have duplicates (Alice, Charlie appear in both conditions)
Q12. How do you retrieve the top N records? Easy
-- MySQL / PostgreSQL
SELECT * FROM employees ORDER BY salary DESC LIMIT 3;
-- SQL Server
SELECT TOP 3 * FROM employees ORDER BY salary DESC;
-- Oracle / ANSI SQL
SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 3 ROWS ONLY;
-- Output (top 3 by salary):
-- | emp_id | name | salary |
-- |--------|-------|--------|
-- | 4 | Diana | 95000 |
-- | 1 | Alice | 90000 |
-- | 3 | Charlie| 85000 |
Q13. What is a view in SQL? Medium
-- Create a view
CREATE VIEW high_earners AS
SELECT emp_id, name, dept_id, salary
FROM employees
WHERE salary >= 85000;
-- Use like a table
SELECT * FROM high_earners ORDER BY salary DESC;
-- | emp_id | name | dept_id | salary |
-- |--------|---------|---------|--------|
-- | 4 | Diana | 30 | 95000 |
-- | 1 | Alice | 10 | 90000 |
-- | 3 | Charlie | 10 | 85000 |
DROP VIEW high_earners;
Q14. What is an index? How does it improve performance? Medium
-- Create index
CREATE INDEX idx_salary ON employees(salary);
CREATE UNIQUE INDEX idx_emp_name ON employees(name);
-- Composite index (covers multiple columns)
CREATE INDEX idx_dept_salary ON employees(dept_id, salary);
-- Query that benefits from index
EXPLAIN SELECT * FROM employees WHERE salary > 80000;
-- With index: Index Range Scan vs Full Table Scan
Q15. What is the difference between CHAR and VARCHAR? Easy
CREATE TABLE example (
country_code CHAR(2), -- always 2 chars ('US', 'IN', 'UK')
email VARCHAR(255), -- variable length, up to 255
status CHAR(1) -- 'A' (active) or 'I' (inactive)
);
Joins
Q16. What are the different types of JOINs in SQL? Easy
Q17. Write a query using INNER JOIN to get employee names with department names. Easy
SELECT e.name, e.salary, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-- Output (only employees with matching dept):
-- | name | salary | dept_name |
-- |---------|--------|-------------|
-- | Alice | 90000 | Engineering |
-- | Bob | 75000 | Marketing |
-- | Charlie | 85000 | Engineering |
-- | Diana | 95000 | Finance |
-- | Ethan | 70000 | Marketing |
-- (Fiona excluded — dept_id is NULL)
Q18. What is a LEFT JOIN? When does it produce NULLs? Easy
-- LEFT JOIN: all employees, with dept info where available
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
-- Output:
-- | name | dept_name |
-- |---------|-------------|
-- | Alice | Engineering |
-- | Bob | Marketing |
-- | Charlie | Engineering |
-- | Diana | Finance |
-- | Ethan | Marketing |
-- | Fiona | NULL | ← dept_id is NULL, no match found
-- Find employees with no department:
SELECT e.name FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;
Q19. What is a FULL OUTER JOIN? Medium
-- All employees AND all departments (matched or not)
SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;
-- Output:
-- | name | dept_name |
-- |---------|-------------|
-- | Alice | Engineering |
-- | Charlie | Engineering |
-- | Bob | Marketing |
-- | Ethan | Marketing |
-- | Diana | Finance |
-- | Fiona | NULL | ← employee without dept
-- | NULL | HR | ← dept without employees
-- MySQL doesn't support FULL OUTER JOIN — emulate with UNION:
SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id
UNION
SELECT e.name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id;
Q20. What is a SELF JOIN? Give an example. Medium
-- Find each employee and their manager's name
SELECT
e.name as employee,
m.name as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;
-- Output:
-- | employee | manager |
-- |----------|---------|
-- | Alice | NULL | ← no manager (top-level)
-- | Bob | Alice |
-- | Charlie | Alice |
-- | Diana | Alice |
-- | Ethan | Bob |
-- | Fiona | Bob |
Q21. What is a CROSS JOIN? When would you use it? Medium
-- Generate all combinations (Cartesian product)
SELECT e1.name as emp1, e2.name as emp2
FROM employees e1
CROSS JOIN employees e2
WHERE e1.emp_id < e2.emp_id; -- avoid duplicates and self-pairs
-- Use case: generate all date/product combinations for a report
SELECT d.date_val, p.product_name
FROM date_dimension d
CROSS JOIN products p;
-- If date_dimension has 365 rows and products has 100: 36,500 rows
Q22. Find departments that have no employees. Medium
-- Method 1: LEFT JOIN + NULL check
SELECT d.dept_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;
-- Output:
-- | dept_name |
-- |-----------|
-- | HR |
-- Method 2: NOT EXISTS
SELECT dept_name FROM departments d
WHERE NOT EXISTS (
SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);
-- Method 3: NOT IN
SELECT dept_name FROM departments
WHERE dept_id NOT IN (
SELECT dept_id FROM employees WHERE dept_id IS NOT NULL
);
Q23. Write a query to find employees earning more than their manager. Hard
SELECT e.name as employee, e.salary as emp_salary,
m.name as manager, m.salary as mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;
-- If Ethan (70000) had manager Bob (75000), this returns no results.
-- If we had an employee with salary > manager's salary, they'd appear here.
Q24. What is the difference between ON and USING in JOINs? Medium
-- ON: flexible, works when column names differ
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;
-- USING: shorthand when column names are identical in both tables
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d USING(dept_id);
-- Both produce same result; USING is cleaner for equal-name joins
Q25. How do you join more than two tables? Medium
-- Three-table join: employees → departments → projects
SELECT e.name, d.dept_name, p.project_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN projects p ON p.dept_id = d.dept_id
WHERE e.salary > 80000;
-- General principle: chain JOIN clauses sequentially
-- Performance: ensure all join columns are indexed
Aggregation & Grouping
Q26. What are aggregate functions in SQL? Easy
SELECT
COUNT(*) as total_employees,
COUNT(dept_id) as employees_with_dept, -- excludes NULL
SUM(salary) as total_payroll,
AVG(salary) as avg_salary,
MIN(salary) as min_salary,
MAX(salary) as max_salary
FROM employees;
-- Output:
-- | total | with_dept | payroll | avg_salary | min | max |
-- |-------|-----------|---------|------------|-------|-------|
-- | 6 | 5 | 475000 | 79166.67 | 60000 | 95000 |
Q27. How do you count rows per group? Easy
SELECT dept_id, COUNT(*) as employee_count
FROM employees
GROUP BY dept_id
ORDER BY employee_count DESC;
-- Output:
-- | dept_id | employee_count |
-- |---------|----------------|
-- | 10 | 2 |
-- | 20 | 2 |
-- | 30 | 1 |
-- | NULL | 1 |
Q28. Find departments with more than 1 employee. Easy
SELECT d.dept_name, COUNT(e.emp_id) as emp_count
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name
HAVING COUNT(e.emp_id) > 1;
-- Output:
-- | dept_name | emp_count |
-- |-------------|-----------|
-- | Engineering | 2 |
-- | Marketing | 2 |
Q29. Find the highest paid employee per department. Medium
-- Method 1: Subquery
SELECT e.name, e.dept_id, e.salary
FROM employees e
WHERE e.salary = (
SELECT MAX(salary) FROM employees
WHERE dept_id = e.dept_id
);
-- Method 2: Window Function (more efficient)
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 = 1;
-- Output:
-- | name | dept_id | salary |
-- |---------|---------|--------|
-- | Alice | 10 | 90000 |
-- | Bob | 20 | 75000 |
-- | Diana | 30 | 95000 |
Q30. Calculate cumulative/running total salary. Hard
SELECT
name,
hire_date,
salary,
SUM(salary) OVER (ORDER BY hire_date) as running_total
FROM employees
ORDER BY hire_date;
-- Output:
-- | name | hire_date | salary | running_total |
-- |---------|------------|--------|---------------|
-- | Charlie | 2019-07-01 | 85000 | 85000 |
-- | Alice | 2020-01-15 | 90000 | 175000 |
-- | Bob | 2021-03-22 | 75000 | 250000 |
-- | Diana | 2022-11-10 | 95000 | 345000 |
-- | Ethan | 2023-02-14 | 70000 | 415000 |
-- | Fiona | 2023-08-01 | 60000 | 475000 |
Q31. Find employees hired in the last 30 days. Easy
-- MySQL
SELECT name, hire_date FROM employees
WHERE hire_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);
-- PostgreSQL
SELECT name, hire_date FROM employees
WHERE hire_date >= CURRENT_DATE - INTERVAL '30 days';
-- SQL Server
SELECT name, hire_date FROM employees
WHERE hire_date >= DATEADD(DAY, -30, GETDATE());
Q32. How do you pivot data in SQL? Hard
-- Convert rows to columns (salary by department)
-- MySQL approach using conditional aggregation
SELECT
SUM(CASE WHEN dept_id = 10 THEN salary END) as engineering_payroll,
SUM(CASE WHEN dept_id = 20 THEN salary END) as marketing_payroll,
SUM(CASE WHEN dept_id = 30 THEN salary END) as finance_payroll
FROM employees;
-- Output:
-- | engineering_payroll | marketing_payroll | finance_payroll |
-- |---------------------|-------------------|-----------------|
-- | 175000 | 145000 | 95000 |
Q33. Write a query to find the average salary difference from department average. Hard
SELECT
name,
salary,
dept_id,
ROUND(AVG(salary) OVER (PARTITION BY dept_id), 2) as dept_avg,
ROUND(salary - AVG(salary) OVER (PARTITION BY dept_id), 2) as diff_from_avg
FROM employees
ORDER BY dept_id, diff_from_avg DESC;
-- Output:
-- | name | salary | dept_id | dept_avg | diff_from_avg |
-- |---------|--------|---------|----------|---------------|
-- | Alice | 90000 | 10 | 87500.00 | 2500.00 |
-- | Charlie | 85000 | 10 | 87500.00 | -2500.00 |
-- | Bob | 75000 | 20 | 72500.00 | 2500.00 |
-- | Ethan | 70000 | 20 | 72500.00 | -2500.00 |
Subqueries and CTEs
Q34. What is a subquery? What are its types? Medium
-- Scalar subquery
SELECT name, salary,
(SELECT AVG(salary) FROM employees) as company_avg
FROM employees;
-- List subquery (IN)
SELECT name FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE budget > 300000);
-- Correlated subquery (references outer 'e')
SELECT name, salary FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id);
Q35. What is a CTE (Common Table Expression)? Medium
-- Basic CTE
WITH dept_stats AS (
SELECT dept_id,
AVG(salary) as avg_sal,
MAX(salary) as max_sal,
COUNT(*) as emp_count
FROM employees
GROUP BY dept_id
)
SELECT e.name, e.salary, ds.avg_sal, ds.emp_count
FROM employees e
JOIN dept_stats ds ON e.dept_id = ds.dept_id
WHERE e.salary > ds.avg_sal;
-- Output (employees earning above their dept average):
-- | name | salary | avg_sal | emp_count |
-- |-------|--------|----------|-----------|
-- | Alice | 90000 | 87500.00 | 2 |
-- | Bob | 75000 | 72500.00 | 2 |
Q36. What is a recursive CTE? Hard
-- Build org hierarchy (manager → employee chain)
WITH RECURSIVE org_chart AS (
-- Anchor: top-level employees (no manager)
SELECT emp_id, name, manager_id, 0 as level, name as path
FROM employees WHERE manager_id IS NULL
UNION ALL
-- Recursive: join to find direct reports
SELECT e.emp_id, e.name, e.manager_id,
oc.level + 1,
CONCAT(oc.path, ' → ', e.name)
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.emp_id
)
SELECT level, name, path FROM org_chart ORDER BY path;
-- Output:
-- | level | name | path |
-- |-------|---------|------------------------|
-- | 0 | Alice | Alice |
-- | 1 | Bob | Alice → Bob |
-- | 1 | Charlie | Alice → Charlie |
-- | 1 | Diana | Alice → Diana |
-- | 2 | Ethan | Alice → Bob → Ethan |
-- | 2 | Fiona | Alice → Bob → Fiona |
Q37. What is EXISTS vs IN — when to use which? Hard
-- IN: good for small, non-correlated lists
SELECT name FROM employees
WHERE dept_id IN (10, 20);
-- EXISTS: better for correlated checks
SELECT d.dept_name FROM departments d
WHERE EXISTS (
SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id AND e.salary > 80000
);
-- NOT IN vs NOT EXISTS: crucial difference with NULLs
-- NOT IN returns no rows if subquery has any NULL values!
-- NOT EXISTS handles NULLs correctly — prefer it for "not in" checks
Q38. Find employees who earn above the company average. Easy
-- Using subquery
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- Output:
-- | name | salary |
-- |---------|--------|
-- | Alice | 90000 |
-- | Charlie | 85000 |
-- | Diana | 95000 |
-- (Company avg ≈ 79,167)
Q39. Write a query to detect duplicate email addresses. Medium
-- Assuming we have a users table
SELECT email, COUNT(*) as occurrence
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrence DESC;
-- Find all rows with duplicate emails
SELECT u.*
FROM users u
JOIN (
SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1
) dupes ON u.email = dupes.email
ORDER BY u.email;
Q40. How do you delete duplicate rows keeping one? Hard
-- Keep row with lowest emp_id; delete higher duplicates
DELETE FROM employees
WHERE emp_id NOT IN (
SELECT MIN(emp_id)
FROM employees
GROUP BY name, salary, dept_id -- columns that define "duplicate"
);
-- PostgreSQL using ctid (row identifier)
DELETE FROM employees
WHERE ctid NOT IN (
SELECT MIN(ctid)
FROM employees
GROUP BY name, email
);
Window Functions
Q41. What are window functions? How do they differ from GROUP BY? Medium
-- GROUP BY collapses rows
SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id; -- 4 rows
-- Window function keeps all rows
SELECT name, dept_id, salary,
AVG(salary) OVER (PARTITION BY dept_id) as dept_avg
FROM employees; -- 6 rows (all employees)
Q42. What is the difference between RANK, DENSE_RANK, and ROW_NUMBER? Medium
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num, -- unique, no gaps
RANK() OVER (ORDER BY salary DESC) as rnk, -- gaps after ties
DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rnk -- no gaps
FROM employees;
-- Output (if two employees had salary 85000):
-- | name | salary | row_num | rnk | dense_rnk |
-- |---------|--------|---------|-----|-----------|
-- | Diana | 95000 | 1 | 1 | 1 |
-- | Alice | 90000 | 2 | 2 | 2 |
-- | Charlie | 85000 | 3 | 3 | 3 |
-- | Bob | 75000 | 4 | 4 | 4 |
-- | Ethan | 70000 | 5 | 5 | 5 |
-- | Fiona | 60000 | 6 | 6 | 6 |
Q43. What are LAG and LEAD functions? Hard
-- Compare each employee's salary with previous/next (by hire_date)
SELECT
name,
hire_date,
salary,
LAG(salary) OVER (ORDER BY hire_date) as prev_salary,
LEAD(salary) OVER (ORDER BY hire_date) as next_salary,
salary - LAG(salary) OVER (ORDER BY hire_date) as salary_change
FROM employees
ORDER BY hire_date;
-- Output:
-- | name | hire_date | salary | prev_salary | next_salary | change |
-- |---------|------------|--------|-------------|-------------|---------|
-- | Charlie | 2019-07-01 | 85000 | NULL | 90000 | NULL |
-- | Alice | 2020-01-15 | 90000 | 85000 | 75000 | 5000 |
-- | Bob | 2021-03-22 | 75000 | 90000 | 95000 | -15000 |
-- | Diana | 2022-11-10 | 95000 | 75000 | 70000 | 20000 |
Q44. Calculate a 3-month moving average. Hard
SELECT
month,
revenue,
AVG(revenue) OVER (
ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- 3-row window
) as moving_avg_3m
FROM monthly_revenue
ORDER BY month;
-- Window frame options:
-- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (cumulative)
-- ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING (centered 5-row window)
-- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING (reverse cumulative)
Q45. Use NTILE to categorize employees into salary quartiles. Medium
SELECT
name,
salary,
NTILE(4) OVER (ORDER BY salary) as quartile
FROM employees;
-- Output:
-- | name | salary | quartile |
-- |---------|--------|----------|
-- | Fiona | 60000 | 1 |
-- | Ethan | 70000 | 1 |
-- | Bob | 75000 | 2 |
-- | Charlie | 85000 | 2 |
-- | Alice | 90000 | 3 |
-- | Diana | 95000 | 4 |
Optimization & Best Practices
Q46. What is query optimization? How do you analyze a slow query? Medium
-- EXPLAIN shows execution plan
EXPLAIN SELECT e.name, d.dept_name
FROM employees e JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 80000;
-- Key indicators of problems:
-- type: ALL → full table scan (bad for large tables)
-- type: index, ref, const → good
-- rows: high number → lots of data being processed
-- Extra: "Using filesort" → consider adding ORDER BY index
Q47. What are the differences between clustered and non-clustered indexes? Hard
-- Clustered (SQL Server syntax)
CREATE CLUSTERED INDEX idx_emp_id ON employees(emp_id);
-- Non-clustered: separate B-tree with row pointers
CREATE NONCLUSTERED INDEX idx_salary ON employees(salary);
CREATE NONCLUSTERED INDEX idx_dept_salary ON employees(dept_id, salary);
-- Composite: leftmost prefix matters (dept_id, salary both used if filtered by dept_id)
Q48. When should you NOT use an index? Medium
-- Bad index candidates
CREATE INDEX idx_status ON orders(status); -- only 'pending','shipped','done'
CREATE INDEX idx_gender ON users(gender); -- only M/F/Other
-- Good index candidates
CREATE INDEX idx_email ON users(email); -- high cardinality
CREATE INDEX idx_created_at ON orders(created_at); -- range queries
CREATE INDEX idx_composite ON orders(customer_id, created_at); -- frequent join+filter
Q49. What is normalization? Explain 1NF, 2NF, 3NF. Medium
- 1NF: Atomic values (no repeating groups, no multi-valued columns), unique rows
- 2NF: 1NF + no partial dependencies (every non-key attribute depends on the WHOLE primary key)
- 3NF: 2NF + no transitive dependencies (non-key attributes don't depend on other non-key attributes)
-- Violates 1NF (multi-valued phone)
-- student_id | name | phone
-- 1 | Alice | 111,222,333
-- 1NF
-- student_id | name | phone
-- 1 | Alice | 111
-- 1 | Alice | 222
-- Violates 2NF (name depends only on student_id, not full PK)
-- student_id | course_id | student_name | grade
-- 1 | 101 | Alice | A
-- 2NF: split into Students(student_id, name) + Enrollments(student_id, course_id, grade)
Q50. What is a transaction? What are ACID properties? Medium
- Atomicity: all operations succeed or all fail (all-or-nothing)
- Consistency: database moves from one valid state to another
- Isolation: concurrent transactions don't interfere
- Durability: committed changes persist even after system failure
-- Transfer money between accounts (classic example)
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;
-- If both succeed:
COMMIT;
-- If any fails:
-- ROLLBACK; -- reverts both operations (atomicity!)
-- SAVEPOINT for partial rollback
BEGIN;
SAVEPOINT before_update;
UPDATE products SET price = price * 1.10;
-- Oops, too high!
ROLLBACK TO SAVEPOINT before_update;
COMMIT;
SQL Cheat Sheet
| Clause | When to Use |
|---|---|
| WHERE | Filter rows (before grouping) |
| HAVING | Filter groups (after GROUP BY) |
| ORDER BY | Sort results |
| GROUP BY | Aggregate per group |
| DISTINCT | Remove duplicate rows |
| LIMIT/TOP | Restrict result count |
| JOIN | Combine rows from multiple tables |
| CASE | Conditional expressions in SELECT |
| Window Function | Purpose |
|---|---|
| ROW_NUMBER() | Unique sequential numbers |
| RANK() | Rank with gaps for ties |
| DENSE_RANK() | Rank without gaps |
| LAG()/LEAD() | Access previous/next row |
| SUM() OVER | Running total |
| NTILE(n) | Divide into n equal buckets |
Related Articles
- DBMS Interview Questions 2026 — Database theory and concepts behind SQL queries
- Top 50 Java Interview Questions 2026 — Java + SQL is the classic backend developer combo
- Top 50 Python Interview Questions 2026 — Python for data engineering and SQL integration
- System Design Interview Questions 2026 — Database design and schema decisions at scale
© 2026 PlacementAdda.com | Query Smarter, Get Hired Faster
Advertisement Placement
Explore this topic cluster
More resources in interview-questions
Use the category hub to browse similar questions, exam patterns, salary guides, and preparation resources related to this topic.
More in interview-questions
interview-questions
Top 30 HR Interview Questions with Best Answers (2026)
24 min read
interview-questionsTop 30 System Design Interview Questions for 2026
20 min read
interview-questionsTop 40 React.js Interview Questions & Answers (2026)
29 min read
interview-questionsTop 50 Data Structures Interview Questions 2026
10 min read