Telechargé par houssemeddin loudhabachi

HW

publicité
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); 
Téléchargement