Practical SQL Interview Questions

SELECT emp_id, name, salary FROM employees WHERE city = 'Delhi' AND salary > 50000 ORDER BY salary DESC; WHERE filters rows, and ORDER BY DESC sorts from highest to lowest. Add LIMIT 10 in MySQL or PostgreSQL, or TOP 10 in SQL Server, to return only the first ten.

CREATE TABLE students ( student_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(150) UNIQUE, age INT CHECK (age >= 17), city VARCHAR(50) DEFAULT 'Delhi', joined_on DATE ); PRIMARY KEY ensures unique non-null IDs, UNIQUE blocks duplicate emails, and CHECK validates age.

INSERT INTO students (student_id, name, city) VALUES (101, 'Anita', 'Noida'); UPDATE students SET city = 'Gurugram' WHERE student_id = 101; DELETE FROM students WHERE student_id = 101; Always use WHERE with UPDATE and DELETE; without it every row changes.

SELECT customer_id, name FROM customers WHERE email IS NULL OR TRIM(email) = ''; NULL needs IS NULL, because email = NULL never matches. Empty strings and spaces are separate values, so TRIM handles both. Consider fixing data at entry with NOT NULL and CHECK constraints.

SELECT customer_id, SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered, SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled, SUM(CASE WHEN status = 'Returned' THEN 1 ELSE 0 END) AS returned FROM orders GROUP BY customer_id; Conditional aggregation works in every database, unlike vendor-specific PIVOT syntax.

MySQL and PostgreSQL: SELECT * FROM products ORDER BY price DESC LIMIT 3; SQL Server: SELECT TOP 3 * FROM products ORDER BY price DESC; Standard SQL: SELECT * FROM products ORDER BY price DESC FETCH FIRST 3 ROWS ONLY; Always use ORDER BY, otherwise the top rows are not guaranteed.

SELECT o.order_id, o.order_date, c.name, o.amount FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id ORDER BY o.order_date DESC; Aliases o and c keep the query short and clear. INNER JOIN drops orders whose customer is missing, so use LEFT JOIN if such orders must still appear.

SELECT c.customer_id, c.name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_id IS NULL; Alternative: SELECT customer_id, name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id );

SELECT e.emp_id, e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id; The same table is used twice with different aliases. LEFT JOIN ensures the CEO, who has no manager, still appears with a NULL manager name.

SELECT cat.category_name, SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN products p ON oi.product_id = p.product_id JOIN categories cat ON p.category_id = cat.category_id GROUP BY cat.category_name ORDER BY revenue DESC; Join from the most detailed table outward and aggregate at the end.

SELECT e.name, e.salary, g.grade FROM employees e JOIN salary_grades g ON e.salary BETWEEN g.min_salary AND g.max_salary; This is a non-equi join. Ensure grade ranges do not overlap, otherwise an employee matches more than one grade and appears twice.

WITH days AS ( SELECT generate_series(DATE '2025-01-01', DATE '2025-01-31', INTERVAL '1 day')::date AS d ) SELECT d.d, COALESCE(SUM(s.amount), 0) AS sales FROM days d LEFT JOIN sales s ON s.sale_date = d.d GROUP BY d.d ORDER BY d.d; This PostgreSQL example uses a date series; other databases use a calendar table.

SELECT department_id, COUNT(*) AS emp_count, ROUND(AVG(salary), 2) AS avg_salary FROM employees GROUP BY department_id ORDER BY emp_count DESC; Join with the departments table if department names are needed, and use LEFT JOIN from departments to include empty departments.

SELECT city, COUNT(*) AS customers FROM customers GROUP BY city HAVING COUNT(*) > 100 ORDER BY customers DESC; HAVING is needed because the condition uses an aggregate. A WHERE clause could still filter rows first, for example only active customers.

SELECT email, COUNT(*) AS cnt FROM users GROUP BY email HAVING COUNT(*) > 1; To see full duplicate rows: SELECT * FROM users WHERE email IN ( SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1 ) ORDER BY email; Normalise case with LOWER(email) if needed.

SELECT EXTRACT(MONTH FROM order_date) AS month, SUM(amount) AS revenue, COUNT(DISTINCT customer_id) AS buyers FROM orders WHERE order_date >= DATE '2025-01-01' AND order_date < DATE '2026-01-01' GROUP BY EXTRACT(MONTH FROM order_date) ORDER BY month; A date range in WHERE keeps an index on order_date usable.

SELECT COALESCE(region, 'All Regions') AS region, COALESCE(city, 'Subtotal') AS city, SUM(sales) AS sales FROM store_sales GROUP BY ROLLUP(region, city) ORDER BY region, city; In MySQL use GROUP BY region, city WITH ROLLUP. Use GROUPING() to label totals when real NULLs exist.

PostgreSQL and standard SQL: SELECT department_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary FROM employees GROUP BY department_id; SQL Server uses PERCENTILE_CONT as a window function with OVER (PARTITION BY department_id). Median resists outliers better than average for salary analysis.

SELECT emp_id, name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees ) ORDER BY salary DESC; The inner query is a non-correlated scalar subquery that runs once and returns one value used for comparison.

SELECT MAX(salary) AS second_highest FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); With a window function: SELECT DISTINCT salary FROM ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees ) t WHERE rnk = 2; Both handle duplicate top salaries correctly.

SELECT e.emp_id, e.name, e.department_id, e.salary FROM employees e WHERE e.salary > ( SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e.department_id ); This is a correlated subquery. A window version uses AVG(salary) OVER (PARTITION BY department_id) in a CTE and filters in the outer query.

WITH order_totals AS ( SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id ), ranked AS ( SELECT customer_id, total, NTILE(10) OVER (ORDER BY total DESC) AS decile FROM order_totals ) SELECT * FROM ranked WHERE decile = 1; Each CTE is a named step, making top 10 percent customer logic easy to read.

WITH RECURSIVE org AS ( SELECT emp_id, name, manager_id, 1 AS lvl FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.emp_id, e.name, e.manager_id, o.lvl + 1 FROM employees e JOIN org o ON e.manager_id = o.emp_id ) SELECT * FROM org ORDER BY lvl, name; SQL Server omits the RECURSIVE keyword.

SELECT c.customer_id, o.order_id, o.order_date FROM customers c CROSS JOIN LATERAL ( SELECT order_id, order_date FROM orders WHERE orders.customer_id = c.customer_id ORDER BY order_date DESC LIMIT 3 ) o; With an index on (customer_id, order_date), each lookup is fast. SQL Server uses CROSS APPLY with TOP 3.

SELECT d.department_id, d.name FROM departments d WHERE NOT EXISTS ( SELECT 1 FROM employees e WHERE e.department_id = d.department_id AND e.salary <= 40000 ) AND EXISTS ( SELECT 1 FROM employees e WHERE e.department_id = d.department_id ); Double negation expresses for all; the EXISTS excludes empty departments.

SELECT emp_id, name, department_id, salary, DENSE_RANK() OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS dept_rank FROM employees; PARTITION BY restarts ranking for each department, and DENSE_RANK keeps equal salaries at the same rank.

SELECT sale_date, amount, SUM(amount) OVER ( ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM daily_sales ORDER BY sale_date; Add PARTITION BY store_id to restart the running total for each store.

WITH r AS ( SELECT emp_id, name, department_id, salary, ROW_NUMBER() OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS rn FROM employees ) SELECT * FROM r WHERE rn <= 2; Use DENSE_RANK instead of ROW_NUMBER if tied salaries should all be included.

WITH m AS ( SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue FROM orders GROUP BY 1 ) SELECT month, revenue, ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS mom_pct FROM m ORDER BY month; NULLIF avoids division by zero.

SELECT activity_date, dau, ROUND(AVG(dau) OVER ( ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ), 1) AS dau_7d_avg FROM daily_users ORDER BY activity_date; This assumes one row per day; fill missing dates with a calendar table first, or ROWS will span more than seven days.

WITH d AS ( SELECT DISTINCT user_id, login_date FROM logins ), g AS ( SELECT user_id, login_date, login_date - CAST(ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY login_date) AS INT) AS grp FROM d ) SELECT user_id, MIN(login_date) AS start_day, COUNT(*) AS days FROM g GROUP BY user_id, grp HAVING COUNT(*) >= 3; Consecutive dates minus row number give the same group value (PostgreSQL date arithmetic).

SELECT user_id, event_time, event_name, event_time - LAG(event_time) OVER ( PARTITION BY user_id ORDER BY event_time ) AS gap_since_prev FROM events ORDER BY user_id, event_time; Gaps above 30 minutes can start a new session using a SUM of a flag over the same window, a common sessionisation pattern.

WITH spend AS ( SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id ) SELECT customer_id, total, NTILE(4) OVER (ORDER BY total DESC) AS quartile FROM spend; Quartile 1 contains the highest spenders. Use this for targeted offers or loyalty tiers.

CREATE TABLE students ( student_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL ); CREATE TABLE courses ( course_id INT PRIMARY KEY, title VARCHAR(100) NOT NULL ); CREATE TABLE enrolments ( student_id INT REFERENCES students(student_id), course_id INT REFERENCES courses(course_id), enrolled_on DATE, PRIMARY KEY (student_id, course_id) ); The junction table resolves the many-to-many relationship.

ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id); ALTER TABLE orders ADD CONSTRAINT chk_amount_positive CHECK (amount > 0); First clean existing rows that violate the rule, otherwise the ALTER fails. Naming constraints makes errors easier to read.

SELECT COALESCE(c.product_id, p.product_id) AS product_id, COALESCE(c.sales, 0) AS this_month, COALESCE(p.sales, 0) AS last_month FROM cur_month c FULL OUTER JOIN prev_month p ON c.product_id = p.product_id; Products sold in only one of the months still appear, with zero for the other month.

CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date); This helps queries like: SELECT * FROM orders WHERE customer_id = 101 AND order_date >= '2025-01-01'; Check with EXPLAIN that the index is used, and avoid creating duplicate indexes on the same columns.

Slow: SELECT * FROM orders WHERE YEAR(order_date) = 2025 AND MONTH(order_date) = 3; Fast: SELECT * FROM orders WHERE order_date >= '2025-03-01' AND order_date < '2025-04-01'; The second form compares the raw column, so an index on order_date can be used with a range seek.

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 101; Read the tree from the innermost node outward. Check node types such as Seq Scan, Index Scan or Hash Join, compare estimated rows with actual rows, and look at actual time per node. Large estimate errors suggest stale statistics, and Seq Scan on a selective filter suggests a missing index.

Slow for deep pages: SELECT * FROM orders ORDER BY id LIMIT 50 OFFSET 500000; Keyset pagination: SELECT * FROM orders WHERE id > :last_seen_id ORDER BY id LIMIT 50; OFFSET reads and discards all skipped rows, while keyset seeks directly using the index on id, keeping each page fast.

BEGIN; UPDATE accounts SET balance = balance - 5000 WHERE account_id = 1 AND balance >= 5000; UPDATE accounts SET balance = balance + 5000 WHERE account_id = 2; COMMIT; If the first update affects zero rows or any error occurs, the application issues ROLLBACK so neither change is saved.

PostgreSQL: INSERT INTO inventory (sku, qty) VALUES ('A100', 10) ON CONFLICT (sku) DO UPDATE SET qty = inventory.qty + EXCLUDED.qty; MySQL: INSERT INTO inventory (sku, qty) VALUES ('A100', 10) ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty); SQL Server and others use MERGE. A unique key on sku is required.

MySQL: DELIMITER // CREATE PROCEDURE get_orders(IN p_customer INT) BEGIN SELECT order_id, order_date, amount FROM orders WHERE customer_id = p_customer ORDER BY order_date DESC; END // DELIMITER ; CALL get_orders(101); Parameters prevent SQL injection compared with building query strings in code.

PostgreSQL: CREATE FUNCTION log_salary() RETURNS trigger AS $$ BEGIN IF NEW.salary <> OLD.salary THEN INSERT INTO salary_audit(emp_id, old_sal, new_sal, changed_at) VALUES (OLD.emp_id, OLD.salary, NEW.salary, NOW()); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_salary AFTER UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION log_salary();