OperationalResearch.org

Topics

← Back to SQL

Top 50 SQL Queries

comprehensive list of the Top 50 SQL Queries commonly asked in interviews—from fresher to mid-level and advanced roles, with detailed explanations and real-life analogies to simplify your understanding.

🔹 SECTION 1: BASIC SQL QUERIES (1–15)

These test fundamental understanding of data retrieval, filtering, and sorting.

1. SELECT all columns from a table

SELECT * FROM Employees;

🔸 Analogy: Think of this as opening an Excel sheet and looking at all columns and rows.

2. SELECT specific columns

SELECT Emp_Name, Salary FROM Employees;

🔸 Analogy: Like highlighting just the "Name" and "Salary" columns from a sheet to print.

3. WHERE clause (filtering)

SELECT * FROM Employees WHERE Dept_No = 10;

🔸 Analogy: Filtering all employees who belong to Dept 10—like filtering rows in Excel.

4. ORDER BY clause

SELECT * FROM Employees ORDER BY Salary DESC;

🔸 Analogy: Sorting employee salaries from highest to lowest.

5. DISTINCT values

SELECT DISTINCT Job FROM Employees;

🔸 Analogy: Listing all unique job titles like “Analyst,” “Manager” without repeats.

6. COUNT aggregate

SELECT COUNT(*) FROM Employees;

🔸 Analogy: Counting how many total employees are present—like checking row count in Excel.

7. GROUP BY + COUNT

SELECT Job, COUNT(*) FROM Employees GROUP BY Job;

🔸 Analogy: Tallying how many people hold each job type—like pivot tables.

8. HAVING clause

SELECT Job, COUNT(*) FROM Employees GROUP BY Job HAVING COUNT(*) > 1;

🔸 Analogy: Like saying “only show jobs with more than one employee.”

9. LIKE operator

SELECT * FROM Employees WHERE Emp_Name LIKE 'S%';

🔸 Analogy: Finding names starting with ‘S’ — similar to filter using “Starts with S”.

10. IN clause

SELECT * FROM Employees WHERE Dept_No IN (10, 30);

🔸 Analogy: Like saying "I want data for departments 10 and 30 only".

11. BETWEEN clause

SELECT * FROM Employees WHERE Salary BETWEEN 30000 AND 50000;

🔸 Analogy: Show salaries in a given range—like applying number filters.

12. IS NULL / IS NOT NULL

SELECT * FROM Employees WHERE Manager_ID IS NULL;

🔸 Analogy: Like checking for blank cells in a column.

13. LIMIT (TOP N Records)

SELECT * FROM Employees ORDER BY Salary DESC LIMIT 5;

🔸 Analogy: "Show me top 5 highest paid employees."

14. Simple INNER JOIN

SELECT E.Emp_Name, D.Dept_Name
FROM Employees E
JOIN Departments D ON E.Dept_No = D.Dept_No;

🔸 Analogy: Combining two Excel sheets based on a common column.

15. Aliases

SELECT Emp_Name AS Name, Salary AS Monthly_Income FROM Employees;

🔸 Analogy: Like renaming column headers in a report.

🔹 SECTION 2: INTERMEDIATE LEVEL QUERIES (16–30)

16. Subquery (single value)

SELECT * FROM Employees 
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

🔸 Analogy: Like filtering people who earn more than the average salary.

17. Subquery (IN clause)

SELECT * FROM Employees 
WHERE Dept_No IN (SELECT Dept_No FROM Departments WHERE Location = 'Delhi');

🔸 Analogy: Pulling employees only from departments located in Delhi.

18. CASE statement

SELECT Emp_Name,
       CASE 
           WHEN Salary >= 50000 THEN 'High'
           WHEN Salary >= 30000 THEN 'Medium'
           ELSE 'Low'
       END AS Salary_Level
FROM Employees;

🔸 Analogy: Like writing an IF-ELSE ladder to classify data.

19. UNION / UNION ALL

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers;

🔸 Analogy: Merging two mailing lists without duplicates.

20. EXISTS

SELECT * FROM Employees E
WHERE EXISTS (SELECT 1 FROM Projects P WHERE E.Emp_ID = P.Emp_ID);

🔸 Analogy: Checking if a record exists in a related table (like checking if someone has submitted any project).

21. NOT EXISTS

SELECT * FROM Employees E
WHERE NOT EXISTS (SELECT 1 FROM Projects P WHERE E.Emp_ID = P.Emp_ID);

22. DELETE with condition

DELETE FROM Employees WHERE Emp_Name = 'John';

23. UPDATE with condition

UPDATE Employees SET Salary = Salary * 1.10 WHERE Job = 'Analyst';

24. INSERT INTO SELECT

INSERT INTO BackupEmployees
SELECT * FROM Employees WHERE Dept_No = 10;

25. Self Join

SELECT E1.Emp_Name, E2.Emp_Name AS Manager
FROM Employees E1
JOIN Employees E2 ON E1.Manager_ID = E2.Emp_ID;

🔸 Analogy: Finding who reports to whom in the same table.

26. Find Nth Highest Salary

SELECT * FROM (
  SELECT Emp_Name, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk
  FROM Employees
) AS T WHERE rnk = 2;

27. String Functions

SELECT UPPER(Emp_Name), LENGTH(Job) FROM Employees;

28. Date Functions

SELECT Emp_Name, MONTH(Joining_Date), YEAR(Joining_Date)
FROM Employees;

29. CASE inside ORDER BY

SELECT * FROM Employees
ORDER BY 
  CASE 
    WHEN Job = 'Manager' THEN 1
    WHEN Job = 'Analyst' THEN 2
    ELSE 3
  END;

30. IFNULL / COALESCE

SELECT Emp_Name, COALESCE(Manager_ID, 'No Manager') FROM Employees;

🔹 SECTION 3: ADVANCED / INDUSTRY USAGE (31–50)

31. CTE (Common Table Expression)

WITH TopEarners AS (
  SELECT Emp_Name, Salary 
  FROM Employees 
  WHERE Salary > 50000
)
SELECT * FROM TopEarners;

32. Window Functions (OVER + PARTITION)

SELECT Emp_Name, Dept_No, Salary,
       RANK() OVER (PARTITION BY Dept_No ORDER BY Salary DESC) AS DeptRank
FROM Employees;

33. Pivot Table using CASE

SELECT Dept_No,
       SUM(CASE WHEN Job = 'Analyst' THEN 1 ELSE 0 END) AS Analysts,
       SUM(CASE WHEN Job = 'Manager' THEN 1 ELSE 0 END) AS Managers
FROM Employees GROUP BY Dept_No;

34. Find Duplicate Rows

SELECT Emp_Name, COUNT(*) 
FROM Employees 
GROUP BY Emp_Name 
HAVING COUNT(*) > 1;

35. Running Total

SELECT Emp_Name, Salary,
       SUM(Salary) OVER (ORDER BY Emp_ID) AS Running_Total
FROM Employees;

36. Top N per Category

SELECT * FROM (
  SELECT *, RANK() OVER (PARTITION BY Dept_No ORDER BY Salary DESC) AS rnk
  FROM Employees
) AS T WHERE rnk <= 2;

37. Delete Duplicates

DELETE FROM Employees
WHERE Emp_ID NOT IN (
  SELECT MIN(Emp_ID) FROM Employees GROUP BY Emp_Name, Job
);

38. Find Employees Without Department

SELECT * FROM Employees
WHERE Dept_No NOT IN (SELECT Dept_No FROM Departments);

39. Complex JOIN with Aggregates

SELECT D.Dept_Name, COUNT(E.Emp_ID) AS Num_Employees, AVG(E.Salary)
FROM Employees E
JOIN Departments D ON E.Dept_No = D.Dept_No
GROUP BY D.Dept_Name;

40. Cross Join (Cartesian Product)

SELECT * FROM Products CROSS JOIN Suppliers;

41. Find Gaps in Sequence

SELECT Emp_ID + 1 AS Missing_ID
FROM Employees E1
WHERE NOT EXISTS (
  SELECT 1 FROM Employees E2 WHERE E2.Emp_ID = E1.Emp_ID + 1
);

42. JSON Query (MySQL/PostgreSQL)

SELECT JSON_EXTRACT(Profile_Data, '$.skills[0]') FROM Users;

43. Recursive CTE (Hierarchy)

WITH RECURSIVE OrgChart AS (
  SELECT Emp_ID, Emp_Name, Manager_ID
  FROM Employees
  WHERE Manager_ID IS NULL
  UNION ALL
  SELECT E.Emp_ID, E.Emp_Name, E.Manager_ID
  FROM Employees E
  JOIN OrgChart O ON E.Manager_ID = O.Emp_ID
)
SELECT * FROM OrgChart;

44. ROLLUP

SELECT Dept_No, Job, COUNT(*)
FROM Employees
GROUP BY ROLLUP (Dept_No, Job);

45. CUBE

SELECT Dept_No, Job, COUNT(*)
FROM Employees
GROUP BY CUBE (Dept_No, Job);

46. MERGE statement (SQL Server / Oracle)

MERGE INTO TargetTable T
USING SourceTable S
ON T.ID = S.ID
WHEN MATCHED THEN UPDATE SET T.Name = S.Name
WHEN NOT MATCHED THEN INSERT (ID, Name) VALUES (S.ID, S.Name);

47. Stored Procedure to return salary stats

CREATE PROCEDURE GetSalaryStats()
BEGIN
  SELECT AVG(Salary), MIN(Salary), MAX(Salary) FROM Employees;
END;

48. Trigger (example before insert)

CREATE TRIGGER before_insert_emp
BEFORE INSERT ON Employees
FOR EACH ROW
SET NEW.Created_At = NOW();

49. Dynamic SQL Execution

SET @sql = 'SELECT * FROM ' + @tableName;
PREPARE stmt FROM @sql;
EXECUTE stmt;

50. Find % Contribution of Salary per Dept

SELECT Emp_Name, Salary,
       ROUND(Salary * 100.0 / SUM(Salary) OVER (PARTITION BY Dept_No), 2) AS Pct_Contribution
FROM Employees;

ORA.ai

🤖

Hello! I'm your AI assistant

Ask me anything about Operations Research, algorithms, or optimization!