NAME: Md Aamir ROLL:2200291520
Ansari 107
Branch:-CSE(AI)
Sec:-B
LAB – 7
Subqueries
1. find all employees that belong to the location 1700.
SELECT * FROM employees WHERE location_id =
1700;
2. find all employees who do not locate at the location
1700. SELECT * FROM employees WHERE location_id !=
1700;
3. finds the employees who have the highest
salary. SELECT * FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
4. finds all employees who salaries are greater than the average salary of all
employees. SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
`
5. finds all departments which have at least one employee with the salary is greater
than 10,000.
SELECT DISTINCT dept_id FROM employees
WHERE salary > 10000;
6. finds all departments that do not have any employee with the salary greater than
10,000. SELECT dept_id FROM departments
WHERE dept_id NOT IN (
SELECT DISTINCT dept_id FROM employees WHERE salary > 10000
);
7. finds all employees whose salaries are greater than the lowest salary of every
department. SELECT * FROM employees
WHERE salary > (SELECT MIN(salary) FROM employees);
8. finds all employees whose salaries are greater than or equal to the highest salary of
every department.
SELECT * FROM employees
WHERE salary >= (SELECT MAX(salary) FROM employees);
9. calculate the average of average salary of
departments. SELECT AVG(dept_avg) AS
avg_of_avg_salary
FROM (
SELECT AVG(salary) AS
dept_avg FROM employees
GROUP BY dept_id
);
10. Find the Second Highest Salary.
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
