Realtime SQL Queries Asked in Interviews


Write a query to find the second-highest salary in a table.

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

Write a query to find the names of employees who have the same name as their manager.

SELECT e.name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.name = m.name;

Write a query to find the names of employees who have a higher salary than their manager.

SELECT e.name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

Write a query to find the top 5 best-selling products in a table.

SELECT product_name, SUM(quantity_sold) as total_quantity
FROM sales
GROUP BY product_name
ORDER BY total_quantity DESC
LIMIT 5;

Write a query to find the names of customers who have placed orders in all months of the year.

SELECT customer_name
FROM customers
WHERE NOT EXISTS (
  SELECT *
  FROM months
  WHERE NOT EXISTS (
    SELECT *
    FROM orders
    WHERE customers.customer_id = orders.customer_id
      AND MONTH(order_date) = months.month
  )
);

Write a query to find the total revenue generated by each category of products.

SELECT category, SUM(price * quantity_sold) AS total_revenue
FROM products
JOIN sales ON products.product_id = sales.product_id
GROUP BY category;

Write a query to find the employees who have worked for the company for more than 10 years.

SELECT name
FROM employees
WHERE hire_date < DATEADD(year, -10, GETDATE());

Write a query to find the average salary of employees in each department.

SELECT department_name, AVG(salary) AS average_salary
FROM employees
JOIN departments ON employees.department_id = departments.department_id
GROUP BY department_name;

Write a query to find the top 10 customers who have spent the most money in the last month.

SELECT customer_name, SUM(price * quantity_sold) AS total_spent
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
JOIN order_details ON orders.order_id = order_details.order_id
JOIN products ON order_details.product_id = products.product_id
WHERE order_date >= DATEADD(month, -1, GETDATE())
GROUP BY customer_name
ORDER BY total_spent DESC
LIMIT 10;

Write a query to find the products that have been ordered by at least 3 different customers.

SELECT product_name
FROM products
JOIN order_details ON products.product_id = order_details.product_id
JOIN orders ON order_details.order_id = orders.order_id
GROUP BY product_name
HAVING COUNT(DISTINCT customer_id) >= 3;

Write a query to find the names of employees who have a salary that is higher than the average salary of their department.

SELECT name
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = employees.department_id
);

Write a query to find the top 5 customers who have placed the most orders.

SELECT customer_name, COUNT(*) AS num_orders
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customer_name
ORDER BY num_orders DESC
LIMIT 5;

Write a query to find the customers who have not placed any orders.

SELECT customer_name
FROM customers
WHERE NOT EXISTS (
  SELECT *
  FROM orders
  WHERE customers.customer_id = orders.customer_id
);

Write a query to find the products that have been ordered the most in the last month.

SELECT product_name, SUM(quantity_sold) AS total_quantity_sold
FROM orders
JOIN order_details ON orders.order_id = order_details.order_id
JOIN products ON order_details.product_id = products.product_id
WHERE order_date >= DATEADD(month, -1, GETDATE())
GROUP BY product_name
ORDER BY total_quantity_sold DESC
LIMIT 10;

Write a query to find the names of customers who have placed orders for all products in a specific category.

SELECT customer_name
FROM customers
WHERE NOT EXISTS (
  SELECT *
  FROM products
  WHERE products.category_id = 1
    AND NOT EXISTS (
      SELECT *
      FROM orders
      JOIN order_details ON orders.order_id = order_details.order_id
      WHERE customers.customer_id = orders.customer_id
        AND products.product_id = order_details.product_id
    )
);

Write a query to find the names of employees who have the same salary as the highest paid employee in the company.

SELECT name
FROM employees
WHERE salary = (
  SELECT MAX(salary)
  FROM employees
);

Write a query to find the customers who have placed orders for more than 5 products in the last quarter.

SELECT customer_name, COUNT(DISTINCT products.product_id) AS num_products_ordered
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
JOIN order_details ON orders.order_id = order_details.order_id
JOIN products ON order_details.product_id = products.product_id
WHERE order_date >= DATEADD(quarter, -1, GETDATE())
GROUP BY customer_name
HAVING COUNT(DISTINCT products.product_id) > 5;

Write a query to find the names of employees who have never been a manager.

SELECT name
FROM employees
WHERE employee_id NOT IN (
  SELECT DISTINCT manager_id
  FROM employees
);

Write a query to find the top 10 customers who have spent the most money on orders in the last month.

SELECT customer_name, SUM(price * quantity_sold) AS total_spent
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
JOIN order_details ON orders.order_id = order_details.order_id
JOIN products ON order_details.product_id = products.product_id
WHERE order_date >= DATEADD(month, -1, GETDATE())
GROUP BY customer_name
ORDER BY total_spent DESC
LIMIT 10;

Write a query to find the products that have been ordered by all customers.

SELECT product_name
FROM products
WHERE NOT EXISTS (
  SELECT *
  FROM customers
  WHERE NOT EXISTS (
    SELECT *
    FROM orders
    JOIN order_details ON orders.order_id = order_details.order_id
    WHERE customers.customer_id = orders.customer_id
      AND products.product_id = order_details.product_id
  )
);