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
)
);