Common SQL Queries Asked in Interviews: A Comprehensive Guide

SQL Questions and Solutions

Find the Second Highest Salary:

Question: Can you compose an SQL query? One that retrieves the second highest salary from a table named Employee?

Solution:

SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT MAX(Salary) FROM Employee);

Identify Duplicate Records:

Question: Compose an SQL query. It should identify duplicate records in "Users" table. The query should be based on the "email" column.

Solution:

SELECT email, COUNT(email) AS NumOccurrences
FROM Users
GROUP BY email
HAVING COUNT(email) > 1;

Calculate Running Total:

Question: Can you write an SQL query? The query calculates running total of a column called "Amount". Every row must be accounted for in table named "Transactions".

Solution:

SELECT TransactionID, Amount, 
       SUM(Amount) OVER (ORDER BY TransactionID) AS RunningTotal
FROM Transactions;

Pivot Data:

Question: Could you turn rows into columns for total sales per product in a "Sales" table?

Solution:

SELECT 
    SUM(CASE WHEN Product = 'ProductA' THEN Amount ELSE 0 END) AS ProductA_Sales,
    SUM(CASE WHEN Product = 'ProductB' THEN Amount ELSE 0 END) AS ProductB_Sales,
    SUM(CASE WHEN Product = 'ProductC' THEN Amount ELSE 0 END) AS ProductC_Sales
FROM Sales;

Generate a Series of Numbers:

Question: Can you create a sequence of numbers from 1 up to 10 using an SQL query?

Solution:

SELECT ROW_NUMBER() OVER () AS Number
FROM (SELECT 1 AS n UNION ALL
      SELECT 1 UNION ALL
      SELECT 1 UNION ALL
      SELECT 1 UNION ALL
      SELECT 1 UNION ALL
      SELECT 1 UNION ALL
      SELECT 1 UNION ALL
      SELECT 1 UNION ALL
      SELECT 1 UNION ALL
      SELECT 1) AS dummy;