Preparing for SQL Tricky Questions interviews requires not only a solid grasp of fundamental concepts but also the ability to tackle complex and nuanced problems. In this guide, we’ll explore some of the most challenging SQL interview questions, providing detailed explanations and examples to enhance your understanding and readiness.
1. Identifying Duplicate Records Without Using DISTINCT
Problem:
How can you retrieve duplicate records from a table without employing the DISTINCT keyword?
Solution:
Utilize the GROUP BY clause in conjunction with the HAVING clause to filter groups with more than one occurrence.
Example:
sqlCopyEditSELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;
Explanation:
This query groups the data by the specified column and filters out groups that have more than one occurrence, effectively identifying duplicates.360digitmg.com
2. Ranking Without Using Window Functions
Problem:
How can you assign ranks to records in a table without utilizing window functions like RANK() or DENSE_RANK()?
Solution:
Implement correlated subqueries to calculate the rank of each record.
Example:
sqlCopyEditSELECT a.employee_id, a.salary,
(SELECT COUNT(DISTINCT b.salary)
FROM employees b
WHERE b.salary >= a.salary) AS rank
FROM employees a
ORDER BY rank;
Explanation:
In this query, for each employee, the subquery counts the number of distinct salaries that are greater than or equal to the current employee’s salary, effectively assigning a rank based on salary.
3. Retrieving the Second Highest Salary
Problem:
How can you find the second highest salary from an employees table?
Solution:
One approach is to use the LIMIT clause with an offset.
Example:
sqlCopyEditSELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
Explanation:
This query orders the salaries in descending order, skips the first record (highest salary) using OFFSET 1, and then retrieves the next record, which is the second highest salary.
4. Swapping Values Between Two Columns
Problem:
How can you swap the values of two columns in a table without using a temporary table?
Solution:
Use the UPDATE statement with a single query.
Example:
sqlCopyEditUPDATE table_name
SET column1 = column1 + column2,
column2 = column1 - column2,
column1 = column1 - column2;
Explanation:
This approach uses arithmetic operations to swap the values between column1 and column2 without the need for a temporary variable.
5. Deleting Duplicate Rows While Keeping One
Problem:
How can you delete duplicate rows from a table while retaining one instance of each?
Solution:
Employ the ROW_NUMBER() function to assign a unique identifier to each row within a partition and then delete rows where this identifier is greater than one.
Example:
sqlCopyEditWITH CTE AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY id) AS rn
FROM table_name
)
DELETE FROM CTE WHERE rn > 1;
Explanation:
This query assigns a row number to each record within each partition of column_name and deletes the records where the row number is greater than one, effectively removing duplicates while retaining one instance.
6. Finding Employees With More Than One Manager
Problem:
How can you identify employees who report to more than one manager?
Solution:
Utilize the GROUP BY clause along with the HAVING clause.
Example:
sqlCopyEditSELECT employee_id
FROM employees
GROUP BY employee_id
HAVING COUNT(DISTINCT manager_id) > 1;
Explanation:
This query groups the records by employee_id and filters out groups where the count of distinct manager_id is greater than one, identifying employees with multiple managers.
7. Calculating Running Totals
Problem:
How can you calculate a running total of sales in a sales table?
Solution:
Use the SUM() function as a window function.
Example:
sqlCopyEditSELECT sales_id, sale_date, amount,
SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;
Explanation:
This query calculates a running total of the amount column, ordered by sale_date.
8. Identifying Gaps in a Sequence
Problem:
How can you identify gaps in a sequential column, such as invoice numbers?
Solution:
Utilize the LEAD() function to compare the current value with the next value.
Example:
sqlCopyEditSELECT invoice_number, next_invoice
FROM (
SELECT invoice_number,
LEAD(invoice_number) OVER (ORDER BY invoice_number) AS next_invoice
FROM invoices
) subquery
WHERE next_invoice IS NOT NULL AND next_invoice <> invoice_number + 1;
Explanation:
This query identifies rows where the next invoice number is not sequentially incremented by one, indicating a gap.360digitmg.com
9. Transposing Rows to Columns
Problem:
How can you transform row values into column headers?
Solution:
Use the PIVOT operator if supported, or employ conditional aggregation.
Example:
sqlCopyEditSELECT employee_id,
MAX(CASE WHEN skill = 'SQL' THEN 1 ELSE 0 END) AS knows_sql,
MAX(CASE WHEN skill = 'Python' THEN 1 ELSE 0 END)
::contentReference[oaicite:63]{index=63}