- Find employees earning above average salary
SELECT * FROM Employee
WHERE salary > (SELECT AVG(salary) FROM Employee);
- Find employees earning below average salary
SELECT * FROM Employee
WHERE salary < (SELECT AVG(salary) FROM Employee);
- Find highest salary
SELECT MAX(salary) FROM Employee;
- Find 2nd highest salary
SELECT MAX(salary)
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);
- Find 3rd highest salary
SELECT MAX(salary)
FROM Employee
WHERE salary < (
SELECT MAX(salary)
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee)
);
- Find employees in same department as John
SELECT *
FROM Employee
WHERE dept_id = (
SELECT dept_id FROM Employee WHERE emp_name = 'John'
);
- Find employees working in departments with more than 5 employees
SELECT *
FROM Employee
WHERE dept_id IN (
SELECT dept_id
FROM Employee
GROUP BY dept_id
HAVING COUNT(*) > 5
);
- Find customers who placed orders
SELECT *
FROM Customers c
WHERE EXISTS (
SELECT 1 FROM Orders o WHERE o.customer_id = c.customer_id
);
- Find customers with no orders
SELECT *
FROM Customers c
WHERE NOT EXISTS (
SELECT 1 FROM Orders o WHERE o.customer_id = c.customer_id
);
- Find products with price above average
SELECT *
FROM Products
WHERE price > (SELECT AVG(price) FROM Products);
- Find department with highest avg salary
SELECT TOP 1 department
FROM Employee
GROUP BY department
ORDER BY AVG(salary) DESC;
- Find employees earning highest salary in each department
SELECT *
FROM Employee e
WHERE salary = (
SELECT MAX(salary)
FROM Employee
WHERE dept_id = e.dept_id
);
- Find duplicate names
SELECT emp_name, COUNT(*)
FROM Employee
GROUP BY emp_name
HAVING COUNT(*) > 1;
- Find employees hired after company avg hire date
SELECT *
FROM Employee
WHERE hire_date > (SELECT AVG(hire_date) FROM Employee);
- Find departments with no employees using subquery
SELECT *
FROM Department
WHERE dept_id NOT IN (SELECT dept_id FROM Employee);
