SQL Queries Asked In Interview Questions

Last updated on April 7th, 2025 at 03:23 am

Here, we will discuss SQL Queries Asked in Interview Questions, which interviewers ask in most company interviews.

1. What is MySQL?

MySQL is an open-source relational database management system (RDBMS) that uses SQL (Structured Query Language) to manage data. It’s known for its speed, reliability, and ease of use, powering applications.

2. Top MySQL Interview Topics

Storage engines (InnoDB vs MyISAM), indexes (B-Tree, covering), ACID properties, normalization (1NF-3NF), JOIN types, transactions/isolation levels, SQL optimization (EXPLAIN, indexing), replication, partitioning, stored procedures, triggers, deadlocks, constraints (PK, FK), SQL injection prevention, TRUNCATE vs DELETE, CHAR vs VARCHAR, GROUP BY/HAVING, query tuning, NULL handling, and data types.

1. You have a table called “website_traffic” with columns “date”, “page_views”, and “unique_visitors”.
Write a query to find the days where the page views increased by more than 20% compared to the previous day, and the unique visitors also increased by more than 15% compared to the previous day.

SELECT date 
FROM 
  (
    SELECT date, page_views, unique_visitors, 
      LAG(page_views, 1, 0) OVER (ORDER BY date) AS prev_page_views, 
      LAG(unique_visitors, 1, 0) OVER (ORDER BY date) AS prev_unique_visitors 
    FROM website_traffic
  ) AS subquery 
WHERE 
  page_views > 1.2 * prev_page_views 
  AND unique_visitors > 1.15 * prev_unique_visitors;

2. You have a table called “orders” with columns “order_id”, “customer_id”, “order_date”, and “total_amount”.
Write a query to find the customers who have placed exactly three orders in the last 90 days, with each order having a total amount greater than $500.

SELECT customer_id 
FROM orders 
WHERE order_date > DATEADD (DAY, -90, GETDATE()) 
GROUP BY customer_id 
HAVING 
  COUNT(DISTINCT order_id) = 3 
  AND MIN(total_amount) > 500;

3. You have a table “employees” with columns “employee_id”, “manager_id”, and “salary”.
Write a SQL query to find the employees who earn more than their manager’s average salary.

employee_id	manager_id	salary
1	3	10000
2	3	20000
3	NULL	30000
4	1	40000
5	2	50000

WITH manager_averages AS (
  SELECT manager_id, AVG(salary) AS avg_salary 
  FROM employees 
  GROUP BY manager_id
),
high_earners AS (
  SELECT e.employee_id, e.salary, m.avg_salary 
  FROM employees e 
    JOIN manager_averages m ON e.manager_id = m.manager_id 
  WHERE 
    e.salary > m.avg_salary
) 

SELECT * 
FROM high_earners;

4. You have a table “orders” with columns “order_id”, “customer_id”, and “order_date”.
Write a SQL query to find the customers who have placed an order in every month, using multiple CTEs.

order_id	customer_id	order_date
1	1	2022-01-01
2	1	2022-02-01
3	2	2022-01-01
4	2	2022-03-01
5	1	2022-03-01

WITH monthly_orders AS (
  SELECT customer_id, 
    EXTRACT(MONTH FROM order_date) AS order_month 
  FROM orders
), 
all_months AS (
  SELECT customer_id 
  FROM monthly_orders 
  GROUP BY customer_id 
  HAVING COUNT(DISTINCT order_month) = 12
) 

SELECT * 
FROM all_months;

5. You have a table called “orders” with columns “order_id”, “customer_id”, “order_date”, and “total_amount”.
Write a query to find the customers who have placed an order in the last 30 days, but not in the last 14 days.

SELECT customer_id 
FROM orders 
WHERE order_date BETWEEN DATEADD(DAY, -30, GETDATE()) 
  AND DATEADD(DAY, -14, GETDATE()) 
GROUP BY customer_id 
HAVING 
  COUNT(order_id) > 0;

6. You have a table called “products” with columns “product_id”, “product_name”, “price”, and “product_category”.
Write a query to find the products that have a price greater than the average price of all products in their category, but less than the maximum price in their category.

SELECT p1.product_id, p1.product_name, p1.price 
FROM products p1 
  JOIN (
    SELECT product_category, AVG(price) AS avg_price, MAX(price) AS max_price 
    FROM products 
    GROUP BY product_category
  ) p2 ON p1.product_category = p2.product_category 
WHERE 
  p1.price > p2.avg_price 
  AND p1.price < p2.max_price;

7. You have two tables- 1. Employees & 2. Salaries

Employee Table:

EID	Name	Dept
1	John	HR
2	Sarah	IT
3	Mark	Sales
4	Jane	IT
5	Bob	Sales

Salary Table:

EID	Sal
1	70000
2	95000
3	60000
4	105000
5	75000

Write an SQL query to find the second-highest salary in each department.

SELECT Department, MAX(Salary) AS SecondHighestSalary 
FROM Salaries s 
  JOIN Employees e ON s.EmployeeID = e.EmployeeID 
WHERE 
  Salary < (SELECT MAX(Salary) 
    FROM Salaries s2 
      JOIN Employees e2 ON s2.EmployeeID = e2.EmployeeID 
    WHERE 
      e.Department = e2.Department
  ) 
GROUP BY Department;

8. You have a table Transactions with columns TransactionID, CustomerID, Date, and Amount.
Write a query to calculate the cumulative revenue per customer for each month in the last year.

SELECT CustomerID, DATE_TRUNC('month', Date) AS Month, 
  SUM(Amount) OVER (
    PARTITION BY CustomerID 
    ORDER BY DATE_TRUNC('month', Date) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS CumulativeRevenue 
FROM Transactions 
WHERE Date >= DATEADD(year, -1, GETDATE()) 
ORDER BY CustomerID, Month;

9. A table Production contains columns PlantID, Date, and Output.
Write a query to identify the plants that consistently exceeded their daily average output for at least 20 days in a given month.

WITH DailyAverage AS (
  SELECT PlantID, Date, AVG(Output) OVER (
      PARTITION BY PlantID 
      ORDER BY Date
    ) AS AvgOutput 
  FROM Production
) 
SELECT PlantID 
FROM DailyAverage 
WHERE Output > AvgOutput 
GROUP BY PlantID, Date 
HAVING 
  COUNT(*) >= 20;

10. In a table Employee Attendance with columns EmployeeID, Date, and Status (values: ‘Present’, ‘Absent’).
Write a query to find employees with the highest consecutive absences in the last quarter.

WITH AbsenceGroups AS (
  SELECT EmployeeID, Date, 
    ROW_NUMBER() OVER (
      PARTITION BY EmployeeID ORDER BY Date
    ) - ROW_NUMBER() OVER (
      PARTITION BY EmployeeID, Status ORDER BY Date
    ) AS GroupID 
  FROM EmployeeAttendance 
  WHERE Status = 'Absent' 
    AND Date >= DATEADD(quarter, -1, GETDATE())
) 
SELECT EmployeeID, COUNT(*) AS ConsecutiveAbsences 
FROM AbsenceGroups 
GROUP BY EmployeeID, GroupID 
ORDER BY ConsecutiveAbsences DESC 
LIMIT 1;

Table of Contents

1. What is MySQL?

2. Top MySQL Interview Topics