SQL Queries Asked In Interview

Source: Dev.to

Master SQL Interview Pattern

Almost all queries follow this mental flow:

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

Mnemonic: “Smart Friends Wear Green Hats On Lunch.”

• S → SELECT (columns / functions like SUM, COUNT, AVG)
• F → FROM
• W → WHERE
• G → GROUP BY
• H → HAVING
• O → ORDER BY
• L → LIMIT

Most interview queries are variations of this pipeline.

1️⃣ Nth Highest Salary

Pattern: ORDER BY column DESC → LIMIT N

SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 3;

Memory trick: Ranking → ORDER BY + LIMIT

3️⃣ Duplicate Names

Pattern: GROUP BY column → HAVING COUNT(*) > 1

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

Memory trick: Duplicates → GROUP BY + HAVING

7️⃣ Names Starting with “A”

Pattern: WHERE condition

WHERE name LIKE 'A%';
WHERE salary BETWEEN 10000 AND 50000;

Memory trick: Filtering → WHERE

8️⃣ Number of Employees in a Department

Pattern: SELECT COUNT(*) → FROM table → WHERE condition

SELECT COUNT(*)
FROM employees
WHERE department_name = 'ABC';

Memory trick: Counting → COUNT + WHERE

5️⃣ Create Empty Table (Copy Structure)

Pattern: SELECT * INTO new_table FROM old_table WHERE 1=0

SELECT *
INTO new_table
FROM old_table
WHERE 1 = 0;

Memory trick: Copy structure → WHERE FALSE

13️⃣ Employees Under a Manager (Join)

Pattern: SELECT columns FROM table1 JOIN table2 ON condition

SELECT e.name, s.salary
FROM employees e
JOIN salaries s
  ON e.employee_id = s.employee_id;

Memory trick: Multiple tables → JOIN

19️⃣ UNION

Pattern:

SELECT ...
UNION
SELECT ...

Memory trick: Combining results → UNION

15️⃣ Employees Hired in the Last 8 Months

Pattern: WHERE date >= CURRENT_DATE - INTERVAL

WHERE hire_date >= CURDATE() - INTERVAL 8 MONTH;

Memory trick: Time filtering → INTERVAL

Pattern Summary

Query Construction Checklist

1. What data? → SELECT
2. From where? → FROM
3. Any filter? → WHERE
4. Any grouping? → GROUP BY
5. Any group filter? → HAVING
6. Any sorting? → ORDER BY
7. Any limit? → LIMIT

This order helps reconstruct the query mentally.

Remember FROGS‑HL

• F → FROM
• R → WHERE (think “Restriction”)
• O → ORDER BY
• G → GROUP BY
• S → SELECT
• H → HAVING
• L → LIMIT

Question Category Distribution

Insight: Interviewers most frequently test Filtering, Ranking, and Aggregation.