HintsToday

Perfect—now I understand! You’re looking for tricky, high-quality SQL interview questions like:

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

These are concept-based, real-world, and interview-style—not just syntax exercises.

🔥 Top Tricky SQL Interview Questions (with Answers)

Below is a carefully curated list covering real-world understanding, edge cases, performance, and design:

✅ 1. What is the difference between WHERE, HAVING, and GROUP BY?

🧠 Answer:

• WHERE filters rows before grouping.
• GROUP BY aggregates data.
• HAVING filters groups after aggregation.

SELECT department, COUNT(*) 
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING COUNT(*) > 10;

✅ 2. Why is NULL = NULL false in SQL?

🧠 Answer: Because NULL means “unknown”, and comparing two unknowns gives unknown (NULL), not true.
Use IS NULL or IS NOT NULL instead of = NULL.

✅ 3. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

✅ 4. Can you use GROUP BY without an aggregate function?

🧠 Answer: Yes. You can use it to remove duplicates:

SELECT department FROM employees GROUP BY department;

But it’s more meaningful with aggregation.

✅ 5. What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN?

🧠 Answer:

✅ 6. Why is DISTINCT not always reliable for deduplication?

🧠 Answer:

• DISTINCT works only on selected columns.
• If the table has hidden duplicates in other fields, they won’t be removed.
• Better use ROW_NUMBER() OVER(PARTITION BY ...).

✅ 7. What happens when you run DELETE FROM table_name; vs TRUNCATE TABLE table_name;?

🧠 Answer:

• DELETE: row-by-row deletion, logs each row, slower, rollback possible.
• TRUNCATE: deallocates pages, faster, resets identity, limited rollback support.

✅ 8. Can a table have multiple primary keys?

🧠 Answer: ❌ No. A table can only have one PRIMARY KEY, but it can be composite (multi-column).

✅ 9. Can a UNIQUE key column have NULL values?

🧠 Answer: Yes—one or more NULLs, depending on DBMS.

• PostgreSQL, MySQL: ✅ multiple NULLs allowed
• SQL Server: ✅ one NULL allowed

✅ 10. Can you use a window function in a WHERE clause?

🧠 Answer: ❌ No. You can only use it in a SELECT or ORDER BY.
Use a CTE or subquery:

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC) as rnk
  FROM employees
)
SELECT * FROM ranked WHERE rnk = 1;

🎯 More Tricky SQL Topics to Ask/Study:

-- SQL Tricky Interview Questions & Answers Notebook

-- 🔹 Q1: What is the difference between DELETE, TRUNCATE, and DROP?
-- ✅ DELETE: Removes selected rows, can use WHERE, transactional.
-- ✅ TRUNCATE: Removes all rows, resets identity, faster, limited rollback.
-- ✅ DROP: Deletes entire table (structure + data).

-- 🔹 Q2: Why is NULL = NULL false in SQL?
-- ✅ Because NULL represents 'unknown'. Use IS NULL instead of = NULL.

-- 🔹 Q3: Can you use WHERE with a window function?
-- ❌ No, use a CTE or subquery.
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT * FROM ranked WHERE rnk = 1;

-- 🔹 Q4: What's the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?
SELECT emp_id, dept, salary,
       ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC) AS rn,
       RANK()       OVER(PARTITION BY dept ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER(PARTITION BY dept ORDER BY salary DESC) AS drnk
FROM employees;

-- 🔹 Q5: Can a table have multiple PRIMARY KEYs?
-- ❌ No. One PRIMARY KEY only (can be composite). Multiple UNIQUE constraints are allowed.

-- 🔹 Q6: What does COUNT(col) exclude that COUNT(*) includes?
-- ✅ COUNT(col) ignores NULLs; COUNT(*) counts all rows.

-- 🔹 Q7: What is the output of this?
SELECT NULL = NULL;   -- NULL
SELECT NULL IS NULL;  -- TRUE

-- 🔹 Q8: Why might DISTINCT not fully remove duplicates?
-- ✅ It applies only to selected columns. Hidden diffs in others may remain.

-- 🔹 Q9: Can UNIQUE allow multiple NULLs?
-- ✅ Yes (DBMS-specific). PostgreSQL and MySQL allow multiple NULLs in a UNIQUE column.

-- 🔹 Q10: How would you find duplicates based on email?
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- 🔹 Q11: How to delete all duplicates but keep latest record per email?
WITH dedup AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY email ORDER BY updated_at DESC) AS rn
  FROM users
)
DELETE FROM users WHERE id IN (
  SELECT id FROM dedup WHERE rn > 1
);

-- 🔹 Q12: What happens if you run DELETE FROM table; inside a transaction?
-- ✅ It can be rolled back. Useful for testing cleanup scripts safely.

-- 🔹 Q13: Can GROUP BY be used without an aggregate function?
-- ✅ Yes, but rarely useful. Better with aggregates like COUNT(), SUM(), etc.

-- 🔹 Q14: What’s the use of HAVING without GROUP BY?
SELECT COUNT(*)
FROM orders
HAVING COUNT(*) > 1000;
-- ✅ This works; HAVING filters on aggregates even without GROUP BY.

-- 🔹 Q15: How can you find the 2nd highest salary per department?
WITH ranked AS (
  SELECT *, RANK() OVER(PARTITION BY dept ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT * FROM ranked WHERE rnk = 2;

Pages: 1 2 3 4