Lab 6: Sub queries. 

Example: Display on screen the employees who work with "Davies" 


select first_name
from employees 
where department_id = (select department_id
from employees 
where upper(last_name) = 'DAVIES')  //35 //subquery 


//Single row subquery   (=,<,>,>=,<=,!=) 

Example: Display the employee who earn the minimum salary 
select first_name, salary
from employees
where salary = (select min(salary) from employees) //1200TL

//Multiple row subquery                        
select first_name, salary
from employees          Ali = 1200                 //Exist - In - All - Any (<,>,=)
where salary Exists (select salary from employees) //2500,1200,1000,6000

select first_name, salary
from employees            Ali = 3000                  //Exist - In - All - Any 
where salary not Exists (select salary from employees) //2500,1200,1000,6000

select first_name, salary
from employees             Ali 1200             //Exist - In - All - Any 
where salary In (select salary from employees) //2500,1200,1000,6000

select first_name, salary
from employees             Ali 1600            //Exist - In - All - Any 
where salary Not In (select salary from employees) //2500,1200,1000,6000

select first_name, salary
from employees             Ali 500            //Exist - In - All - Any 
where salary < All (select salary from employees) //2500,1200,1000,6000

select first_name, salary
from employees             Ali 10000            //Exist - In - All - Any 
where not salary < All (select salary from employees) //2500,1200,1000,6000

select first_name, salary
from employees             Ali 5000             //Exist - In - All - Any 
where salary < Any (select salary from employees) //2500,1200,1000,6000

select first_name, salary
from employees             Ali 1000             //Exist - In - All - Any 
where salary = Any (select salary from employees) //2500,1200,1000,6000

select first_name, salary
from employees             Ali 3000             //Exist - In - All - Any 
where not salary = Any (select salary from employees) //2500,1200,1000,6000