Scribd
Upload
4 views2 pages

Tricky SQL Commands

The document outlines common SQL commands often asked in interviews, highlighting key concepts such as IS NULL vs = NULL, finding the second highest salary, and identifying duplicate rows. It also covers advanced topics like using EXISTS vs IN, NULL-safe comparisons in MySQL, and self joins. Additionally, it provides examples of using CASE statements and LEFT JOINs to find unmatched records.

Uploaded by

hr715301
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views2 pages

Tricky SQL Commands

The document outlines common SQL commands often asked in interviews, highlighting key concepts such as IS NULL vs = NULL, finding the second highest salary, and identifying duplicate rows. It also covers advanced topics like using EXISTS vs IN, NULL-safe comparisons in MySQL, and self joins. Additionally, it provides examples of using CASE statements and LEFT JOINs to find unmatched records.

Uploaded by

hr715301
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Most Asked Tricky SQL Commands for Interviews

1. IS NULL vs = NULL
-- Incorrect:
SELECT * FROM users WHERE email = NULL;

-- Correct:
SELECT * FROM users WHERE email IS NULL;

2. Find 2nd Highest Salary


SELECT MAX(salary) AS SecondHighest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

3. Find Duplicate Rows


SELECT name, COUNT(*)
FROM users
GROUP BY name
HAVING COUNT(*) > 1;

4. Top N Rows per Group


SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rn
FROM employee
) t
WHERE rn <= 3;

5. BETWEEN is Inclusive
SELECT * FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

6. EXISTS vs IN
-- Faster for large datasets:
SELECT name FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = [Link]
);

7. NULL-safe comparison (MySQL)


-- Normal = comparison fails for NULL
-- Use <=> in MySQL
SELECT * FROM table WHERE col <=> NULL;

8. Self Join Example


Most Asked Tricky SQL Commands for Interviews

SELECT [Link] AS Employee, [Link] AS Manager


FROM employees A
JOIN employees B ON A.manager_id = [Link];

9. Find All Without a Match (LEFT JOIN + IS NULL)


SELECT [Link]
FROM products a
LEFT JOIN sales b ON [Link] = b.product_id
WHERE b.product_id IS NULL;

10. Using CASE in SELECT


SELECT name,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
ELSE 'C'
END AS grade
FROM students;

You might also like