🔎 Q1. Retrieve employees whose salary is greater than the company’s average salary.
🗂️ Table: employees(emp_id, name, salary)
✅ Answer:
SELECT emp_id, name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
---
🔎 Q2. Identify customers who have placed more than three orders.
🗂️ Table: orders(order_id, customer_id, order_date)
✅ Answer:
SELECT customer_id
FROM orders
WHERE customer_id IN (
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 3
);
---
🔎 Q3. Display employees working in departments where the average salary exceeds 60,000.
🗂️ Table: employees(emp_id, name, department_id, salary)
✅ Answer:
SELECT emp_id, name, department_id
FROM employees e
WHERE department_id IN (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 60000
);
---
🔎 Q4. Show products that have never been ordered.
🗂️ Tables: products(product_id, product_name), orders(order_id, product_id)
✅ Answer:
SELECT product_id, product_name
FROM products
WHERE product_id NOT IN (SELECT product_id FROM orders);
(Alternative safe approach to handle NULLs in orders)
SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN orders o ON p.product_id = o.product_id
WHERE o.product_id IS NULL;
---
🔎 Q5. Fetch employee(s) receiving the maximum salary in the organization.
🗂️ Table: employees(emp_id, name, salary)
✅ Answer:
SELECT emp_id, name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
Double Tap ♥️ For More