SQL Interview Questions and Answers

Basic SQL Queries:

• Write a query to retrieve all customers from a Customers table who are located in the city of ‘New York.’

  • SELECT * FROM Customers WHERE city = ‘New York’;

• Write a SQL query to calculate the total revenue generated by orders in the Orders table.

  • SELECT SUM(order_total) AS total_revenue FROM Orders;

• Write a query to fetch the second highest salary from the Employees table.

  • SELECT MAX(salary)
    FROM Employees
    WHERE salary < (SELECT MAX(salary) FROM Employees);

Join Operations:

• Write a query to retrieve all orders and their corresponding customer names using the Orders and Customers tables.

  • SELECT Orders.order_id, Customers.customer_name
    FROM Orders
    INNER JOIN Customers ON Orders.customer_id = Customers.customer_id;

• Explain the differences between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. Provide an example of when to use each.

  • INNER JOIN: Returns records that have matching values in both tables.
  • LEFT JOIN: Returns all records from the left table and the matched records from the right table. If there’s no match, NULL is returned.
  • RIGHT JOIN: Returns all records from the right table and the matched records from the left table.
  • FULL OUTER JOIN: Returns all records when there is a match in either the left or right table.

Aggregation Functions and Grouping:

• Write a query to count the number of orders placed by each customer in the Orders table, and sort the result by the number of orders.

  • SELECT CustomerID, COUNT(OrderID) AS OrderCount
    FROM Orders
    GROUP BY CustomerID
    ORDER BY OrderCount DESC;

• Write a query to find the maximum, minimum, and average price of products from the Products table grouped by category.

  • SELECT CategoryID, MAX(Price) AS MaxPrice, MIN(Price) AS MinPrice, AVG(Price) AS AvgPrice
    FROM Products
    GROUP BY CategoryID;

Subqueries:

• Write a query to list all employees whose salaries are above the company’s average salary.

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

• Use a subquery to find the customers who have never placed an order.

  • SELECT *
    FROM Customers
    WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders);

Window Functions:

• Write a query that ranks employees based on their sales performance within each department.

  • SELECT EmployeeID, DepartmentID, Sales,
    RANK() OVER (PARTITION BY DepartmentID ORDER BY Sales DESC) AS Rank
    FROM Employees;

• How would you calculate a running total (cumulative sum) of sales for each month in the Sales table?

  • SELECT Month,
    SUM(Sales) OVER (ORDER BY Month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
    FROM Sales;

Data Modification:

• Write a query to update the salary of an employee by 10% in the Employees table.

  • UPDATE Employees
    SET Salary = Salary * 1.10
    WHERE EmployeeID = 123;

• How would you delete all orders that were placed more than 5 years ago in the Orders table?

  • DELETE FROM Orders
    WHERE OrderDate < DATEADD(YEAR, -5, GETDATE());

Advanced SQL:

• Write a query that retrieves the top 5 customers with the highest order values.

  • SELECT TOP 5 CustomerID, SUM(TotalAmount) AS TotalSpent
    FROM Orders
    GROUP BY CustomerID
    ORDER BY TotalSpent DESC;

• Write a query to retrieve the department with the highest average employee salary.

  • SELECT DepartmentID, AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY DepartmentID
    ORDER BY AvgSalary DESC
    LIMIT 1;