9- SELECT * from employees where to_char(hire_date,'yyyy')='2000'; 10- SELECT * from employees where to_char(hire_date,'yyyy')='1996' and to_char(hire_date,'q')=1; 11- SELECT * from employees where to_char(hire_date,'yyyy')=2020 and (to_char(hire_date,'q')=3 OR to_char(hire_date,'q')=4); 12- SELECT last_name||' '||first_name as "Nom Prenom",hire_date as "Date D'embauche", salary as "Salaire",commission_pct as "Prime" from employees where commission_pct IS NOT NULL order by salary DESC,commission_pct; 13- SELECT upper(last_name) as "Nom", initcap(first_name) as "Prenom", lower(email) as "Email" from employees; 14- select * from employees where job_id like '%MAN%'; 15- SELECT * from employees where first_name like 'd_n%' and (last_name like '%t' or last_name like '%y') ; 16- SELECT ROW_NUMBER()over (partition by department_id order by salary desc) "rank",first_name,last_name,salary,department_id from employees ; 17-a select rank() over (order by salary desc) "rang",first_name,last_name,job_id,salary from employees where job_id like '%MAN%'; 17-b select dense_rank() over (order by salary desc) "rang",first_name,last_name,job_id,salary from employees where job_id like '%MAN%'; 18- select count (*) from employees; 19-a select max(salary),min(salary),trunc(avg(salary),3)from employees; 19-b select max(salary),min(salary),trunc(avg(salary),3) from employees where department_id=50; 19-c select max(salary),min(salary),trunc(avg(salary),3) from employees where department_id IN (50,80); 20- SELECT count(*)"Number of employees",department_id from employees group by department_id; 21- SELECT count(*)"Number of employees",job_id from employees group by job_id; 22-a SELECT department_id,max(salary),min(salary),trunc(avg(salary),3) from employees group by department_id; 22-b SELECT department_id,max(salary),min(salary),trunc(avg(salary),3) from employees group by department_id having department_id IN (50,80); 23-a SELECT to_char(hire_date,'yyyy') as "Annee ", count(to_char(hire_date,'yyyy')) as "nb recrutements" from employees group by to_char(hire_date,'yyyy') order by 1; 23-b SELECT to_char(hire_date,'yyyy') as "Annee ", count(to_char(hire_date,'yyyy')) as "nb recrutements" from employees where (to_char(hire_date,'yyyy')>=1990 and to_char(hire_date,'yyyy')<2000) group by to_char(hire_date,'yyyy') order by 1; 24- select department_id,count(department_id) as "nb employees" from employees group by department_id having count(department_id)>10 order by 2 desc; 25- select department_id, trunc(avg(salary),3) as "Moyennes des Salaires" from employees group by department_id having avg(salary)>4000 and department_id>30 order by 1; 32- select department_id,count(department_id) as "nb employees" from employees group by department_id having count(department_id)>10 or count(department_id)=0 order by 2 desc; 34- select E.last_name||' '||E.first_name as "Employee",E.department_id, M.first_name||' '||M.last_name as "Manager",M.department_id from employees E JOIN employees M ON E.manager_id=M.employee_id; 36- select last_name,first_name,salary from employees where salary=(select salary from employees where employee_id=119); 37- select * from employees where salary>(select avg(salary) from employees); 38-a select * from employees where employee_id Not in (select employee_id from job_history); 38-b select employee_id from employees minus select employee_id from job_history; 39- select department_id,department_name from departments where department_id in(select department_id from employees group by department_id having avg(salary)>4000);