8 SQL Interview Questions Every Developer Should Master

-- Employees table
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    salary DECIMAL(10,2)
);

-- Departments table  
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    budget DECIMAL(12,2)
);

-- Projects table
CREATE TABLE projects (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    employee_id INT
);

SELECT e.name, d.name as department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
-- Result: Only employees who have a valid department

SELECT e.name, d.name as department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
-- Result: All employees, including those without departments (NULL values)

SELECT e.name, d.name as department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;
-- Result: All departments, including those with no employees

SELECT e.name, d.name as department
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;
-- Result: All employees and all departments, with NULLs where no match

SELECT e.name, d.name
FROM employees e
CROSS JOIN departments d;
-- Result: Every employee paired with every department
```"

### 2. Write a query to find the second highest salary in each department.

This tests understanding of window functions and ranking.

**Example Answer**: "Multiple approaches depending on SQL dialect:

**Using Window Functions (Modern SQL)**:
```sql
WITH ranked_salaries AS (
    SELECT 
        name,
        department_id,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department_id 
            ORDER BY salary DESC
        ) as salary_rank
    FROM employees
)
SELECT name, department_id, salary
FROM ranked_salaries
WHERE salary_rank = 2;

WITH ranked_salaries AS (
    SELECT 
        name,
        department_id,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department_id 
            ORDER BY salary DESC
        ) as salary_rank
    FROM employees
)
SELECT name, department_id, salary
FROM ranked_salaries
WHERE salary_rank = 2;

SELECT e1.name, e1.department_id, e1.salary
FROM employees e1
WHERE 1 = (
    SELECT COUNT(DISTINCT e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
    AND e2.salary > e1.salary
);

SELECT name, salary
FROM employees
WHERE department_id = 1
ORDER BY salary DESC
LIMIT 1 OFFSET 1;  -- PostgreSQL/MySQL syntax

-- PostgreSQL
EXPLAIN ANALYZE 
SELECT e.name, d.name, COUNT(p.id)
FROM employees e
JOIN departments d ON e.department_id = d.id
LEFT JOIN projects p ON e.id = p.employee_id
WHERE e.salary > 50000
GROUP BY e.name, d.name
ORDER BY COUNT(p.id) DESC;

-- SQL Server
SET STATISTICS IO ON;
-- Query here

-- Create indexes on frequently queried columns
CREATE INDEX idx_employee_salary ON employees(salary);
CREATE INDEX idx_employee_dept ON employees(department_id);
CREATE INDEX idx_project_employee ON projects(employee_id);

-- Composite index for multiple WHERE conditions
CREATE INDEX idx_employee_dept_salary ON employees(department_id, salary);

-- Before: Inefficient subquery in WHERE clause
SELECT name FROM employees 
WHERE department_id IN (
    SELECT id FROM departments WHERE budget > 100000
);

-- After: JOIN instead of subquery
SELECT e.name 
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
WHERE d.budget > 100000;

-- Before: SELECT * and unnecessary columns
SELECT * FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > 50000
ORDER BY e.name;

-- After: Select only needed columns
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > 50000
  AND e.department_id IS NOT NULL
ORDER BY e.name;

-- Add pagination for large datasets
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > 50000
ORDER BY e.salary DESC
LIMIT 20 OFFSET 0;

BEGIN TRANSACTION;

-- Transfer $1000 from account A to account B
UPDATE accounts SET balance = balance - 1000 WHERE id = 'A';
UPDATE accounts SET balance = balance + 1000 WHERE id = 'B';

-- If either update fails, both are rolled back
IF @@ERROR != 0
    ROLLBACK TRANSACTION;
ELSE
    COMMIT TRANSACTION;

-- Constraint ensures account balance never goes negative
ALTER TABLE accounts ADD CONSTRAINT check_positive_balance 
CHECK (balance >= 0);

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 1500 WHERE id = 'A';
-- This will fail if account A only has $1000, maintaining consistency
COMMIT TRANSACTION;

-- Transaction 1 (Transfer)
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 'A'; -- $1000
UPDATE accounts SET balance = balance - 500 WHERE id = 'A';
-- ... other operations
COMMIT TRANSACTION;

-- Transaction 2 (Concurrent read)
-- Won't see partial results from Transaction 1
SELECT balance FROM accounts WHERE id = 'A';

-- Once committed, this change survives power failures, crashes
BEGIN TRANSACTION;
INSERT INTO audit_log (action, timestamp, user_id) 
VALUES ('LOGIN', NOW(), 12345);
COMMIT TRANSACTION;
-- Data is permanently stored and recoverable

-- READ UNCOMMITTED: Allows dirty reads
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- READ COMMITTED: Prevents dirty reads
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- REPEATABLE READ: Prevents dirty and non-repeatable reads
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- SERIALIZABLE: Prevents all phenomena, highest isolation
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Atomic money transfer with validation
CREATE PROCEDURE transfer_money(
    @from_account INT,
    @to_account INT,
    @amount DECIMAL(10,2)
)
AS
BEGIN
    BEGIN TRANSACTION;
    
    -- Check sufficient funds (Consistency)
    IF (SELECT balance FROM accounts WHERE id = @from_account) < @amount
    BEGIN
        ROLLBACK TRANSACTION;
        RAISERROR('Insufficient funds', 16, 1);
        RETURN;
    END
    
    -- Perform transfer (Atomicity)
    UPDATE accounts SET balance = balance - @amount WHERE id = @from_account;
    UPDATE accounts SET balance = balance + @amount WHERE id = @to_account;
    
    -- Log transaction (Durability)
    INSERT INTO transaction_log (from_id, to_id, amount, timestamp)
    VALUES (@from_account, @to_account, @amount, GETDATE());
    
    COMMIT TRANSACTION;
END
```"

### 5. What's the difference between clustered and non-clustered indexes?

Index types significantly impact query performance and storage.

**Example Answer**: "Indexes optimize data retrieval but differ in storage and behavior:

**Clustered Index**: Physically reorders table data
```sql
-- Primary key automatically creates clustered index
CREATE TABLE employees (
    id INT PRIMARY KEY,  -- Clustered index
    name VARCHAR(100),
    salary DECIMAL(10,2)
);

-- Table data physically stored in id order: 1, 2, 3, 4...
-- Each table can have only ONE clustered index

-- Create non-clustered index on salary
CREATE INDEX idx_salary ON employees(salary);

-- Index structure:
-- Salary | Row Pointer
-- 30000  | Row 4
-- 45000  | Row 2  
-- 60000  | Row 1
-- 75000  | Row 3

-- Clustered index: Direct data access (fast)
SELECT * FROM employees WHERE id = 123;
-- Goes directly to physical location of row 123

-- Non-clustered index: Two-step lookup
SELECT * FROM employees WHERE salary = 50000;
-- Step 1: Find salary in index → get row pointer
-- Step 2: Go to row pointer location → get data

-- Table can have multiple non-clustered indexes
CREATE INDEX idx_name ON employees(name);
CREATE INDEX idx_department ON employees(department_id);
CREATE INDEX idx_salary_dept ON employees(salary, department_id);  -- Composite

-- Good clustered index: Sequential, unique, static
-- Primary key (auto-incrementing ID) - default choice
CREATE TABLE orders (
    id INT IDENTITY PRIMARY KEY,  -- Good clustered index
    customer_id INT,
    order_date DATE
);

-- Alternative: Date-based clustering for time-series data
CREATE CLUSTERED INDEX idx_orders_date ON orders(order_date);

-- Clustered index determines physical storage order
-- Insertions may cause page splits if not sequential
INSERT INTO employees (id, name) VALUES (999999, 'New Employee');
-- If id=999999 needs to be inserted between existing records,
-- causes expensive page reorganization

-- Non-clustered indexes require maintenance overhead
-- Each INSERT/UPDATE/DELETE must update all relevant indexes

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10,2),
    manager_id INT,  -- References employees.id
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

-- Sample data
INSERT INTO employees VALUES
(1, 'CEO', 200000, NULL),
(2, 'VP Engineering', 150000, 1),
(3, 'Senior Dev', 120000, 2),
(4, 'Junior Dev', 160000, 2),  -- Earns more than manager!
(5, 'Intern', 140000, 3);      -- Earns more than manager!

SELECT 
    emp.name as employee_name,
    emp.salary as employee_salary,
    mgr.name as manager_name,
    mgr.salary as manager_salary
FROM employees emp
INNER JOIN employees mgr ON emp.manager_id = mgr.id
WHERE emp.salary > mgr.salary;

SELECT 
    emp.name as employee_name,
    emp.salary as employee_salary,
    mgr.name as manager_name,
    mgr.salary as manager_salary,
    (emp.salary - mgr.salary) as salary_difference,
    ROUND(((emp.salary - mgr.salary) / mgr.salary) * 100, 2) as percentage_higher
FROM employees emp
INNER JOIN employees mgr ON emp.manager_id = mgr.id
WHERE emp.salary > mgr.salary
ORDER BY salary_difference DESC;

SELECT 
    emp.name as employee_name,
    dept.name as department,
    emp.salary as employee_salary,
    mgr.name as manager_name,
    mgr.salary as manager_salary
FROM employees emp
INNER JOIN employees mgr ON emp.manager_id = mgr.id
INNER JOIN departments dept ON emp.department_id = dept.id
WHERE emp.salary > mgr.salary
ORDER BY dept.name, (emp.salary - mgr.salary) DESC;

-- Find employees who earn more than any manager in their chain
WITH management_chain AS (
    -- Base case: direct reports
    SELECT 
        id as employee_id,
        manager_id,
        1 as level
    FROM employees
    WHERE manager_id IS NOT NULL
    
    UNION ALL
    
    -- Recursive case: indirect reports
    SELECT 
        mc.employee_id,
        e.manager_id,
        mc.level + 1
    FROM management_chain mc
    INNER JOIN employees e ON mc.manager_id = e.id
    WHERE e.manager_id IS NOT NULL
)
SELECT DISTINCT
    emp.name as employee_name,
    emp.salary as employee_salary,
    STRING_AGG(mgr.name, ', ') as managers_earning_less
FROM employees emp
INNER JOIN management_chain mc ON emp.id = mc.employee_id
INNER JOIN employees mgr ON mc.manager_id = mgr.id
WHERE emp.salary > mgr.salary
GROUP BY emp.id, emp.name, emp.salary;

-- Current employees
CREATE TABLE current_employees (
    id INT, name VARCHAR(100), department VARCHAR(50)
);
INSERT VALUES 
(1, 'John', 'Engineering'),
(2, 'Jane', 'Marketing'),
(3, 'Bob', 'Engineering');

-- Former employees
CREATE TABLE former_employees (
    id INT, name VARCHAR(100), department VARCHAR(50)
);
INSERT VALUES 
(2, 'Jane', 'Marketing'),  -- Duplicate with current
(4, 'Alice', 'Sales'),
(5, 'Carol', 'Engineering');

SELECT name, department FROM current_employees
UNION
SELECT name, department FROM former_employees;

-- Result: John, Jane, Bob, Alice, Carol (Jane appears once)
-- Automatically sorts results and removes duplicates
-- Performance impact: Must sort and deduplicate

SELECT name, department FROM current_employees
UNION ALL
SELECT name, department FROM former_employees;

-- Result: John, Jane, Bob, Jane, Alice, Carol (Jane appears twice)
-- Faster than UNION (no sorting or deduplication)
-- Use when you know there are no duplicates or duplicates are acceptable

SELECT name, department FROM current_employees
INTERSECT
SELECT name, department FROM former_employees;

-- Result: Jane, Marketing (only record present in both tables)
-- Useful for finding overlaps between datasets

SELECT name, department FROM current_employees
EXCEPT
SELECT name, department FROM former_employees;

-- Result: John, Bob (current employees who were never former employees)
-- Useful for finding differences between datasets

-- Find records that exist in production but not in backup
SELECT customer_id, email FROM production.customers
EXCEPT
SELECT customer_id, email FROM backup.customers;

-- Combine data from multiple time periods
SELECT 'Q1' as quarter, product_id, SUM(sales) as total_sales
FROM q1_sales GROUP BY product_id
UNION ALL
SELECT 'Q2' as quarter, product_id, SUM(sales)
FROM q2_sales GROUP BY product_id
UNION ALL
SELECT 'Q3' as quarter, product_id, SUM(sales)
FROM q3_sales GROUP BY product_id
ORDER BY product_id, quarter;

-- Find customers who appear in orders but not in customer table
SELECT customer_id FROM orders
EXCEPT
SELECT customer_id FROM customers;

-- These all return NULL (unknown), not true or false
SELECT * FROM employees WHERE salary = NULL;     -- Wrong! Returns no rows
SELECT * FROM employees WHERE salary != NULL;    -- Wrong! Returns no rows
SELECT * FROM employees WHERE salary > NULL;     -- Wrong! Returns no rows

-- Correct NULL comparisons
SELECT * FROM employees WHERE salary IS NULL;
SELECT * FROM employees WHERE salary IS NOT NULL;

-- Any arithmetic with NULL returns NULL
SELECT 
    name,
    salary,
    bonus,
    salary + bonus as total_compensation  -- NULL if bonus is NULL
FROM employees;

-- Handle with COALESCE or ISNULL
SELECT 
    name,
    salary,
    COALESCE(bonus, 0) as bonus,  -- Replace NULL with 0
    salary + COALESCE(bonus, 0) as total_compensation
FROM employees;

-- Most aggregates ignore NULL values
SELECT 
    COUNT(*) as total_employees,        -- Counts all rows including NULLs
    COUNT(bonus) as employees_with_bonus, -- Counts only non-NULL bonus values
    AVG(bonus) as average_bonus,        -- Calculates average ignoring NULLs
    SUM(bonus) as total_bonus           -- Sums only non-NULL values
FROM employees;

-- Be careful with averages when NULLs present
SELECT 
    AVG(COALESCE(bonus, 0)) as avg_including_zero_bonus,
    AVG(bonus) as avg_excluding_null_bonus
FROM employees;

-- NULLs don't match in JOINs
SELECT e.name, d.name as department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
-- Employees with NULL department_id will show NULL for department name

-- Finding unmatched records
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;  -- Employees without valid department

-- Complex logic with NULLs
SELECT name FROM employees 
WHERE salary > 50000 AND bonus > 1000;
-- Excludes employees with NULL bonus

-- Include employees with NULL bonus
SELECT name FROM employees 
WHERE salary > 50000 AND (bonus > 1000 OR bonus IS NULL);

-- Three-valued logic: TRUE, FALSE, NULL

-- COALESCE: Return first non-NULL value
SELECT 
    name,
    COALESCE(work_phone, home_phone, mobile_phone, 'No phone') as contact_phone
FROM employees;

-- NULLIF: Return NULL if values are equal
SELECT 
    name,
    NULLIF(middle_name, '') as middle_name  -- Convert empty string to NULL
FROM employees;

-- CASE statements for complex NULL logic
SELECT 
    name,
    CASE 
        WHEN bonus IS NULL THEN 'No bonus'
        WHEN bonus = 0 THEN 'Zero bonus'
        ELSE CAST(bonus as VARCHAR) + ' bonus'
    END as bonus_description
FROM employees;

-- NULLs typically sort first (ASC) or last (DESC), but varies by database
SELECT name, bonus
FROM employees
ORDER BY bonus ASC;  -- NULLs first in most databases

-- Control NULL ordering explicitly
SELECT name, bonus
FROM employees
ORDER BY bonus ASC NULLS LAST;  -- PostgreSQL, Oracle

-- Alternative approach for consistent behavior
SELECT name, bonus
FROM employees
ORDER BY ISNULL(bonus, 1), bonus ASC;  -- SQL Server: NULLs last

-- Find records with unexpected NULLs
SELECT 
    'employees' as table_name,
    COUNT(*) as total_rows,
    COUNT(name) as non_null_names,
    COUNT(*) - COUNT(name) as null_names,
    COUNT(salary) as non_null_salaries,
    COUNT(*) - COUNT(salary) as null_salaries
FROM employees;