Mastering SQL: Tricky Interview Questions And Practical Solutions

SQL interviews can be challenging, especially when faced with advanced questions that test your depth of knowledge. Below are some common yet tricky SQL interview questions, along with practical solutions and commands to help you prepare effectively.

You Should Know:

How do you identify duplicate records in a table?

SELECT column_name, COUNT(<em>)
FROM table_name
GROUP BY column_name
HAVING COUNT(</em>) > 1;

How can you delete duplicates while keeping just one entry?

WITH CTE AS (
SELECT column_name, 
ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY id) AS rn
FROM table_name
)
DELETE FROM table_name
WHERE id IN (SELECT id FROM CTE WHERE rn > 1);

What’s the difference between UNION and UNION ALL?
– `UNION` removes duplicates, while `UNION ALL` includes all records, including duplicates.
When should you use RANK, ROW_NUMBER, and DENSE_RANK?

– RANK: Skips ranks after ties.
– ROW_NUMBER: Assigns unique numbers to rows.
– DENSE_RANK: Does not skip ranks after ties.

How do you find records in one table that don’t exist in another?

SELECT * FROM table1
WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id);

How do you retrieve the second-highest salary for each department?

WITH SalaryRank AS (
SELECT department_id, salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees
)
SELECT department_id, salary
FROM SalaryRank
WHERE rank = 2;

How do you find employees earning more than their manager?

SELECT e.employee_name, e.salary, m.employee_name AS manager_name, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

What’s the key difference between INNER JOIN and LEFT JOIN?

– INNER JOIN: Returns only matching rows.
– LEFT JOIN: Returns all rows from the left table and matching rows from the right table.

How would you swap gender values (M ↔ F) in a table using SQL?

UPDATE table_name
SET gender = CASE 
WHEN gender = 'M' THEN 'F'
WHEN gender = 'F' THEN 'M'
END;

How do different types of joins impact the number of records in the output?

– INNER JOIN: Reduces rows to only matches.
– LEFT JOIN: Preserves all rows from the left table.
– FULL JOIN: Combines all rows from both tables.

How do you retrieve the nth highest salary from a table?

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET n-1;

What’s the difference between HAVING and WHERE in SQL?

– WHERE: Filters rows before grouping.
– HAVING: Filters groups after aggregation.

How do you pivot rows into columns in SQL?

SELECT *
FROM (
SELECT category, value
FROM table_name
) AS src
PIVOT (
MAX(value) FOR category IN ('Category1', 'Category2')
) AS pvt;

How do you calculate a running total in SQL?

SELECT id, value,
SUM(value) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM table_name;

How would you identify and handle orphan records in a database?

SELECT * FROM child_table
WHERE NOT EXISTS (SELECT 1 FROM parent_table WHERE child_table.parent_id = parent_table.id);

What’s the difference between DELETE, TRUNCATE, and DROP?

– DELETE: Removes rows one by one, can be rolled back.
– TRUNCATE: Removes all rows at once, cannot be rolled back.
– DROP: Deletes the entire table.

How do you find the longest consecutive sequence of values in a column?

WITH Consecutive AS (
SELECT value, 
ROW_NUMBER() OVER (ORDER BY value) AS rn
FROM table_name
)
SELECT value, COUNT(*) AS sequence_length
FROM Consecutive
GROUP BY value - rn
ORDER BY sequence_length DESC
LIMIT 1;

What is a self-join, and when would you use it?

– A self-join is used to join a table with itself, often to compare rows within the same table.

How do you find the earliest and latest records for each group in a table?

SELECT group_id, 
MIN(date_column) AS earliest_date,
MAX(date_column) AS latest_date
FROM table_name
GROUP BY group_id;

How do you optimize SQL queries for better performance?

– Use indexes on frequently queried columns.
– Avoid `SELECT *` and fetch only necessary columns.
– Use `EXPLAIN` to analyze query execution plans.

What Undercode Say:

SQL is a powerful tool for managing and analyzing data, and mastering it requires both theoretical knowledge and practical skills. The above commands and techniques are essential for tackling advanced SQL interview questions and real-world database challenges. Practice these commands in a sandbox environment to build confidence and improve your query optimization skills. For further learning, explore resources like SQLZoo or LeetCode SQL Challenges. Keep experimenting and refining your skills to become a SQL expert!