HW

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);