SQL Interview Questions

-- LEFT JOIN example
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- Find employees and their managers
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Find duplicates by email
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Delete duplicates, keep first
DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id)
    FROM users
    GROUP BY email
);

-- Generate all size-color combinations
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

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

WITH high_earners AS (
    SELECT id, name, salary
    FROM employees
    WHERE salary > 100000
)
SELECT AVG(salary) FROM high_earners;

-- Method 1: Using DENSE_RANK (recommended)
SELECT salary FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) t WHERE rnk = 3;

-- Method 2: Using LIMIT/OFFSET (MySQL, PostgreSQL)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

-- Runs once, result used by outer query
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Runs for every row in 'e'
SELECT e.name FROM employees e
WHERE e.salary = (
    SELECT MAX(salary) FROM employees
    WHERE dept_id = e.dept_id  -- references outer 'e'
);

-- Stored Procedure
CREATE PROCEDURE GetEmployeesByDept(IN dept INT)
BEGIN
    SELECT * FROM employees WHERE dept_id = dept;
END;

-- Trigger: log every salary update
CREATE TRIGGER log_salary_change
AFTER UPDATE ON employees
FOR EACH ROW
    INSERT INTO audit_log VALUES (OLD.salary, NEW.salary, NOW());

WITH RECURSIVE org_tree AS (
    -- Anchor: start from the top manager
    SELECT id, name, manager_id, 0 AS level
    FROM employees WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: get each employee's reports
    SELECT e.id, e.name, e.manager_id, o.level + 1
    FROM employees e
    INNER JOIN org_tree o ON e.manager_id = o.id
)
SELECT * FROM org_tree ORDER BY level;

DECLARE emp_cursor CURSOR FOR
    SELECT id, name FROM employees;

OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @id, @name;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- process each row
    FETCH NEXT FROM emp_cursor INTO @id, @name;
END;

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

-- PIVOT: show monthly sales as columns
SELECT product,
    SUM(CASE WHEN month = 'Jan' THEN sales END) AS Jan,
    SUM(CASE WHEN month = 'Feb' THEN sales END) AS Feb,
    SUM(CASE WHEN month = 'Mar' THEN sales END) AS Mar
FROM sales_data
GROUP BY product;

-- UNPIVOT: convert columns back to rows (SQL Server)
SELECT product, month, sales
FROM sales_pivot
UNPIVOT (sales FOR month IN (Jan, Feb, Mar)) u;

-- Composite key
CREATE TABLE order_items (
    order_id   INT,
    product_id INT,
    PRIMARY KEY (order_id, product_id)
);

-- Surrogate key
CREATE TABLE users (
    id    INT AUTO_INCREMENT PRIMARY KEY,  -- surrogate
    email VARCHAR(255) UNIQUE               -- natural candidate
);

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

-- MySQL
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

# Bad: N+1 queries
for user in users:
    orders = db.query("SELECT * FROM orders WHERE user_id = ?", user.id)

# Good: 2 queries with eager loading
users  = db.query("SELECT * FROM users")
orders = db.query("SELECT * FROM orders WHERE user_id IN (...)")

# VULNERABLE: string concatenation
query = "SELECT * FROM users WHERE name = '" + user_input + "'"

# SAFE: parameterized query
cursor.execute("SELECT * FROM users WHERE name = %s", (user_input,))

-- Grant only SELECT on specific table
GRANT SELECT ON sales.orders TO reporting_user;

-- Revoke dangerous permissions
REVOKE DROP ON *.* FROM app_user;