Database Testing - SQL - Sub-Query Operators: (ALL,ANY,SOME,EXISTS)

Sub-Query operators: (ALL,ANY,SOME,EXISTS)

43. List out the employees who earn more than every employee in department 30.

Select * from employee where salary > (Select Max(salary) from employee where department_id=30)

44. List out the employees who earn more than the lowest salary in department 30.

Select * from employee where salary > (Select min(salary) from employee where department_id=30)

45. Find out whose department has no employees.

Select * from department d where not exists (Select department_id from employee e where d.department_id=e.department_id)

By Using Joins

select employee_id, d.department_id, Name, Location_id from employee e right join department d on d.department_id=e.department_id where e.employee_id is null and d.department_id is not null

46. Find out which department does not have any employees.

Select * from department d where not exists (Select department_id from employee e where d.department_id=e.department_id)

Co-Related Sub Queries:

47. Find out the employees who earn greater than the average salary for their department.

Select employee_id, last_name, salary, department_id from employee e where salary > (select avg(salary) from employee where department_id=e.department_id)