SQL Queries Interview Questions and Answers for Placements

Last Updated: March 2026

Introduction to SQL for Placements

SQL (Structured Query Language) is the standard language for relational database management systems. Whether you're targeting product companies, service-based companies, or startups, SQL proficiency is essential. From writing basic SELECT statements to complex window functions and query optimization, SQL interviews test your ability to work with data effectively.

Why SQL is Critical for Placements

• Universal Requirement: Asked in 90%+ of software engineering interviews
• Data Engineering Roles: Core skill for analytics and data positions
• Backend Development: Essential for building data-driven applications
• Real-world Relevance: Every application stores and retrieves data

---

Key Concepts and Theory

SQL Command Categories

Category	Commands	Purpose
DDL	CREATE, ALTER, DROP, TRUNCATE	Define database structure
DML	SELECT, INSERT, UPDATE, DELETE	Manipulate data
DCL	GRANT, REVOKE	Control access
TCL	COMMIT, ROLLBACK, SAVEPOINT	Transaction control

Join Types

-- INNER JOIN: Returns matching rows from both tables
SELECT * FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- LEFT JOIN: All from left, matching from right
SELECT * FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- RIGHT JOIN: All from right, matching from left
SELECT * FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

-- FULL OUTER JOIN: All rows when match in either
SELECT * FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

-- CROSS JOIN: Cartesian product
SELECT * FROM employees
CROSS JOIN departments;

Aggregate Functions

SELECT 
    COUNT(*) as total_rows,
    COUNT(DISTINCT dept_id) as unique_depts,
    AVG(salary) as avg_salary,
    SUM(salary) as total_salary,
    MAX(salary) as max_salary,
    MIN(salary) as min_salary
FROM employees;

---

Practice Questions with Solutions

Question 1: Second Highest Salary ⭐⭐ [Medium]

Problem: Find the second highest salary from the Employees table.

Table Structure:

CREATE TABLE Employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary INT
);

Solutions:

-- Method 1: Using LIMIT and OFFSET
SELECT DISTINCT salary 
FROM Employees 
ORDER BY salary DESC 
LIMIT 1 OFFSET 1;

-- Method 2: Using Subquery
SELECT MAX(salary) 
FROM Employees 
WHERE salary < (SELECT MAX(salary) FROM Employees);

-- Method 3: Using DENSE_RANK (handles duplicates)
SELECT salary 
FROM (
    SELECT DISTINCT salary, 
           DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
    FROM Employees
) ranked
WHERE rnk = 2;

Explanation: Method 1 is simplest but may fail with NULLs. Method 2 is ANSI SQL compatible. Method 3 handles duplicate salaries correctly.

---

Question 2: Nth Highest Salary ⭐⭐ [Medium]

Problem: Write a query to get the nth highest salary.

Solution:

-- Using LIMIT with variable
SET @N = 5;
SELECT salary 
FROM (
    SELECT DISTINCT salary 
    FROM Employees 
    ORDER BY salary DESC 
    LIMIT 1 OFFSET @N - 1
) AS result;

-- Using Window Function (preferred)
SELECT salary 
FROM (
    SELECT DISTINCT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) as salary_rank
    FROM Employees
) ranked_salaries
WHERE salary_rank = N;

Explanation: Window functions provide clean, readable solutions for ranking problems. DENSE_RANK handles ties better than RANK.

---

Question 3: Department Highest Salary ⭐⭐ [Medium]

Problem: Find employees who have the highest salary in each department.

Solution:

-- Method 1: Using JOIN with aggregate
SELECT e.name, e.department, e.salary
FROM Employees e
INNER JOIN (
    SELECT department, MAX(salary) as max_salary
    FROM Employees
    GROUP BY department
) max_salaries 
ON e.department = max_salaries.department 
AND e.salary = max_salaries.max_salary;

-- Method 2: Using Window Function
SELECT name, department, salary
FROM (
    SELECT *,
           RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rnk
    FROM Employees
) ranked
WHERE rnk = 1;

Explanation: Method 1 is traditional SQL. Method 2 with window functions is more elegant and handles ties based on RANK vs DENSE_RANK choice.

---

Question 4: Running Total ⭐⭐ [Medium]

Problem: Calculate running total of sales by date.

Solution:

SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders
ORDER BY order_date;

-- Running total by category
SELECT 
    category,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY category 
        ORDER BY order_date
    ) as category_running_total
FROM orders
ORDER BY category, order_date;

Explanation: Window function SUM() OVER with ORDER BY creates running totals. PARTITION BY resets the total for each group.

---

Question 5: Top 3 per Category ⭐⭐⭐ [Hard]

Problem: Get top 3 products by sales in each category.

Solution:

SELECT category, product_name, sales
FROM (
    SELECT 
        category,
        product_name,
        sales,
        ROW_NUMBER() OVER (
            PARTITION BY category 
            ORDER BY sales DESC
        ) as rn
    FROM products
) ranked
WHERE rn <= 3;

-- With ties (include all tied for 3rd place)
SELECT category, product_name, sales
FROM (
    SELECT 
        category,
        product_name,
        sales,
        DENSE_RANK() OVER (
            PARTITION BY category 
            ORDER BY sales DESC
        ) as rnk
    FROM products
) ranked
WHERE rnk <= 3;

Explanation: ROW_NUMBER gives exactly 3 per category. DENSE_RANK includes ties. Choose based on business requirements.

---

Question 6: Find Duplicate Emails ⭐ [Easy]

Problem: Find all duplicate emails in a table.

Solution:

-- Method 1: Using GROUP BY and HAVING
SELECT email, COUNT(*) as occurrences
FROM Users
GROUP BY email
HAVING COUNT(*) > 1;

-- Method 2: Using Self JOIN
SELECT DISTINCT a.email
FROM Users a
JOIN Users b ON a.email = b.email AND a.id != b.id;

-- Method 3: Using Window Function
SELECT email
FROM (
    SELECT email, COUNT(*) OVER (PARTITION BY email) as cnt
    FROM Users
) sub
WHERE cnt > 1;

Explanation: GROUP BY with HAVING is most efficient. Self JOIN shows relationships. Window functions provide additional flexibility.

---

Question 7: Pivot Table (Rows to Columns) ⭐⭐⭐ [Hard]

Problem: Convert row data into columns (pivot).

Solution:

-- Sample data: Convert subjects into columns
-- Input: student_id, subject, marks
-- Output: student_id, math, science, english

SELECT 
    student_id,
    MAX(CASE WHEN subject = 'Math' THEN marks END) as Math,
    MAX(CASE WHEN subject = 'Science' THEN marks END) as Science,
    MAX(CASE WHEN subject = 'English' THEN marks END) as English
FROM scores
GROUP BY student_id;

-- Using PIVOT (SQL Server)
SELECT student_id, Math, Science, English
FROM scores
PIVOT (
    MAX(marks) 
    FOR subject IN (Math, Science, English)
) AS pivot_table;

Explanation: CASE with aggregation is the portable solution. PIVOT syntax is database-specific but cleaner.

---

Question 8: Consecutive Numbers/Sequences ⭐⭐⭐ [Hard]

Problem: Find all numbers that appear at least three times consecutively.

Solution:

-- Find consecutive IDs with same num
SELECT DISTINCT l1.num as ConsecutiveNums
FROM Logs l1
JOIN Logs l2 ON l1.id = l2.id - 1 AND l1.num = l2.num
JOIN Logs l3 ON l1.id = l3.id - 2 AND l1.num = l3.num;

-- Using Window Functions (more scalable)
SELECT DISTINCT num as ConsecutiveNums
FROM (
    SELECT 
        num,
        id - ROW_NUMBER() OVER (PARTITION BY num ORDER BY id) as grp
    FROM Logs
) sub
GROUP BY num, grp
HAVING COUNT(*) >= 3;

Explanation: First method works for exactly 3 consecutive. Second method using ROW_NUMBER difference technique works for any N consecutive.

---

Question 9: Median Salary ⭐⭐⭐ [Hard]

Problem: Calculate median salary per department.

Solution:

WITH RankedSalaries AS (
    SELECT 
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary) as rn_asc,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rn_desc,
        COUNT(*) OVER (PARTITION BY department) as total_count
    FROM employees
)
SELECT 
    department,
    AVG(salary) as median_salary
FROM RankedSalaries
WHERE rn_asc BETWEEN total_count / 2.0 AND total_count / 2.0 + 1
GROUP BY department;

-- Alternative using PERCENTILE_CONT (PostgreSQL, SQL Server)
SELECT 
    department,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) as median
FROM employees
GROUP BY department;

Explanation: Median is average of middle values. For even count, average two middle values. ROW_NUMBER technique works across databases.

---

Question 10: Tree Structure (Hierarchical Data) ⭐⭐⭐ [Hard]

Problem: Query hierarchical data (employee-manager relationships).

Table Structure:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

Solution:

-- Find all subordinates of a manager (recursive CTE)
WITH RECURSIVE subordinates AS (
    -- Base case: direct reports
    SELECT id, name, manager_id, 0 as level
    FROM employees
    WHERE manager_id = 1  -- Manager with id=1
    
    UNION ALL
    
    -- Recursive case: their reports
    SELECT e.id, e.name, e.manager_id, s.level + 1
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

-- Find full hierarchy path to CEO
WITH RECURSIVE hierarchy AS (
    SELECT id, name, manager_id, CAST(name AS VARCHAR(1000)) as path
    FROM employees
    WHERE manager_id IS NULL  -- CEO
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id, 
           CONCAT(h.path, ' -> ', e.name)
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.id
)
SELECT * FROM hierarchy;

Explanation: Recursive CTEs handle tree structures elegantly. Essential for org charts, category hierarchies, bill of materials.

---

Common Interview Follow-Up Questions

1. "How would you optimize this query?" → Add indexes, reduce columns, avoid SELECT *
2. "What's the difference between WHERE and HAVING?" → WHERE filters rows, HAVING filters groups
3. "When to use EXISTS vs IN?" → EXISTS for correlated subqueries, IN for simple lists
4. "UNION vs UNION ALL?" → UNION removes duplicates (slower), UNION ALL keeps all
5. "Index types and when to use each?" → B-tree (default), Hash (equality), Bitmap (low cardinality)

---

Companies Asking SQL Questions

Company	Frequency	Common Topics
Amazon	Very High	Window functions, Complex joins
Google	Very High	Query optimization, Analytics
Microsoft	Very High	T-SQL, Indexing strategies
Meta/Facebook	High	Hive/Presto, Large scale queries
Oracle	Very High	Advanced SQL, PL/SQL
Adobe	High	Reporting queries, Aggregations
Flipkart	High	MySQL, Performance tuning
Uber	High	PostgreSQL, Geospatial queries

---

Preparation Tips

1. Practice on Real Data: Use sample databases like Northwind, Chinook
2. Master Window Functions: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD are crucial
3. Understand Execution Plans: Learn to read EXPLAIN output
4. Normalization: Know 1NF, 2NF, 3NF, BCNF
5. Indexing: Understand B-trees, when indexes help vs hurt
6. Transactions: ACID properties, isolation levels
7. Practice Variations: Each problem has multiple valid solutions

---

FAQ

Q: What's the difference between RANK, DENSE_RANK, and ROW_NUMBER?
A: ROW_NUMBER gives unique sequential numbers. RANK gives same number to ties but skips next numbers. DENSE_RANK gives same number to ties without gaps.

Q: INNER JOIN vs LEFT JOIN - which is faster?
A: INNER JOIN is generally faster as it returns fewer rows. Always use the join type that matches your data requirements.

Q: When should I use a CTE vs subquery?
A: CTEs improve readability and can be recursive. Use CTEs for complex queries, subqueries for simple one-off filters.

Q: How do I handle NULLs in SQL?
A: Use COALESCE or ISNULL to provide defaults. Remember: NULL != NULL, use IS NULL instead.

Q: What's the difference between DELETE and TRUNCATE?
A: DELETE is DML (can rollback, fires triggers, row by row). TRUNCATE is DDL (faster, resets identity, minimal logging).

---

Master SQL and unlock data engineering opportunities! 🗄️