Exercice SQL pour Oracle

• Chaque employé est associé à un seul département
• Chaque départements peut avoir plusieurs employés

Exo 1 : Informations sur les employés dont la fonction est "MANAGER" dans les départements 20 et 30
Select * from emp where job = 'MANAGER' and deptno in (20,30);

Exo 2 : Liste des employés qui n'ont pas la fonction "MANAGER" et qui ont été embauchés en 81
Select ename, job, hiredate from emp where job != 'MANAGER' and TO_CHAR(HIREDATE,'YY') = '81'
Select ename, job, hiredate from emp where job != 'MANAGER' and
HIREDATE between to_date('01-jan-81','dd-mon-YY') and to_date('31-dec-81','dd-mon-YY')

Exo 3 : Liste des employés ayant un "M" et un "A" dans leur nom
select ename from emp where ename like '%M%' and ename like '%A%'

Exo 4 : Liste des employés ayant deux "A" dans leur nom
select ename from emp where ename like '%A%A%'

Exo 5 : Liste des employés ayant une commission
select * from emp where comm IS NOT NULL

Exo 6 : Liste des noms, numéros de département, jobs et dates d'embauches, triés par :
- numéro de département croissant,
- ordre alphabétique des jobs,
- ancienneté croissante (les derniers embauchés d'abord)

select ename, deptno, job, hiredate from emp order by deptno, job, hiredate DESC

----- Jointure -----

Exo 7 : Liste des employés travaillant à "DALLAS"
select emp.* from emp, dept where emp.deptno = dept.deptno and loc='DALLAS'

Exo 8 : Noms et dates d'embauche des employés embauchés avant leur manager, avec le nom et la date d'embauche du manager
select e.ename, e.hiredate, m.ename, m.hiredate from emp e, emp m where e.mgr=m.empno and e.hiredate < m.hiredate

Exo 9 : Noms et dates d'embauche des employés embauchés avant 'BLAKE'
select emp.ename, emp.hiredate from emp, emp bl where bl.name='black' and emp.hiredate<bl.hiredate
select ename, hiredate from emp where hiredate < (select hiredate from emp where ename = 'BLACK')

----- Sous interrogation -----

Exo 10 : Lister les noms et numéros des employés n'ayant pas de subordonnés
select ename, empno from emp X where NOT EXISTE (select mgr from emp where X.ename = mgr)
select X.ename, X.empno from emp, emp X where emp.mgr(+)=X.empno and emp.mgr IS NULL
=> fait l'association emp.mgr(+)=x.emp et (+) ajoute des lignes blanches (= subordonnés fictifs) associé à l'emp
pour afficher l'employé non énuméré dans la liste des managers avec des subordonnées vide.
ont fait un test sur le mgr vide pour chercher les subordonnées vide.
select ename, empno from emp where empno in (select empno from emp MINUS select mgr form emp)
select ename, empno from emp where empno NOT IN(select mgr from emp where mgr IS NOT NULL)

exo 11 : Employés embauchés le même jour que 'FORD'
select * from emp where hiredate=(select hiredate from emp where ename='FORD')
??? select emp.* from emp, emp E where emp.hiredate=E.hiredate and E.ename='FORD'

Exo 12 : Employés ayant le même manager que 'CLARK'
select * from emp where mgr=(select mgr from emp where ename='CLARK') and ename =! 'CLARK'

Exo 13 : Employés embauchés avant tous les employés du département 10
select * from emp where hiredate < all (select hiredate from emp where deptno = 10)

Exo 14 : Employés ayant le même job et même manager que 'TURNER'
select * from emp where (job,mgr) = (select job,mgr from emp where ename='TURNER') and ename!='TURNER';

Exo 15 : Employés de département 'RESEARCH' embauchés le même jour que quelqu'un du département 'SALES'
select emp.* from emp, dept where emp.deptno=dept.deptno and dname ='RESEARCH' and hiredate IN
(select hiredate from emp, dept where emp.deptno=dept.deptno and dname='SALES');

Exo 16 : Employés gagnant plus que leur manager (on ne prend pas comm en compte)
select * from emp E where E.sal > (select sal from emp where emp.empno=E.mgr) and E.mgr is not null;

----- Fonctions -----

Exo 17 : Liste des noms des employés avec les salaires tronqués au millier
select ename, TRUNC(sal,-3) "Salaire au millier" from emp order by sal;

Exo 18 : Liste des employés en remplaçant les noms par "---" dans le département 10
Select DECODE(deptno,10,'---',ename), deptno from emp;

Exo 19 : Faire un histogramme des salaires
taper "set arraysize 1" sous SQL*Plus afin d'eviter l'erreur "Debordement tampon. Reduire ARRAYSIZE ou augmenter MAXDATA avec SET"
col Histogramme format a50
select ename, LPAD('>',sal/5000*20,'#') HISTOGRAMME from emp order by sal desc;

Exo 20 : Noms des employés avec date de début du mois d'embauche
col mois format a35 heading "Début du mois d'embauche"
select ename, trunc(hiredate,'mm') mois from emp;

Exo 21 : Nom et nombre de mois d'ancienneté des employés le 1 er janvier 2000
select ename, month_between(TO_DATE('01-01-2000', 'DD-MM-YYYY'), hiredate) "Ancienneté en mois" from emp order by 2 desc

Exo 22 : Listes des salaires par job et par département
select job, decode(deptno,10,sal,0) "Département 10", decode(deptno,20,sal,0)) "Département 20"
decode(deptno,30,sal,0) "Département 30" from emp;

----- Fonction groupement sous-interrogation -----

Exo 23 : Salaire moyen en tenant compte des commission
select ROUND(AVG(sal+NVL(comm,0)),2) "Salaire moyen" from emp;

Exo 24 : Nombre d'employés pour chaque job
select job, count(*) "Nombre d'employés" from emp GROUP BY job order by 2 desc

Exo 25 : Nombre d'employés dans chaque tranche de salaire (tranche en millier)
select trunc(sal,-3) Tranche, count(*) "Nombre d'employés" from emp GROUP BY trunc(sal,-3);

Exo 26 : Employés ayant le salaire le plus élevé dans chaque département
select ename, sal, deptno from emp where (deptno, sal) in (select deptno, MAX(sal) from emp GROUPE BY deptno)
?? select ename, sal, deptno from emp E where E.sal = MAX(select emp.sal from emp where emp.dept=E.deptno) order by deptno

Exo 27 : Job ayant le salaire moyen le plus bas
select job, AVG(sal) "Salaire moyen" from emp GROUPE BY job HAVING AVG(sal)=(select MIN(AVG(sal)) from emp GROUPE BY job)

Exo 28 : Somme des salaires par job et par département
select job, sum(decode(deptno,10,sal,0)) "Département 10", sum(decode(deptno,20,sal,0)) "Département 20",
sum(decode(deptno,30,sal,0)) "Département 30" from emp group by job;

Exo 29 : Totaliser l'état précédent par job
select job, sum(decode(deptno,10,sal,0)) "Département 10", sum(decode(deptno,20,sal,0)) "Département 20",
sum(decode(deptno,30,sal,0)) "Département 30",sum(sal) "Total par job" from emp group by job;

----- Arbre hiérarchique -----

Exo 30 : Arbre hiérarchique de la société
select lpad(' ',LEVEL*3)||ename Hiérarchie from emp CONNECT BY mgr = PRIOR empno START WITH mgr is null;

----- DML -----

Exo 31 : Ajouter au salaire des managers 5% du salaire de KING
verifier par un select
Faire un rollback
UPDATE emp E SET sal=(select E.sal+0.05*emp.sal from emp where ename='KING') where job='MANAGER';
select * from emp;
rollback;

Exo 32 : Insérer le salaire minimum et maximum pour chaque département dans la table
SALGRADE(GRADE, LOSAL, HISAL) qui exist déja, avec le numéro de département comme grade.
Verifier par un select
Faire un rollback
INSERT INTO salgrade select deptno, min(sal), max(sal) from emp groupe by deptno;
select * from salgrade;
rollback;

Exo 33 : Supprimer les employés dont le salaire est inférieur au salaire moyen de leur département
DELETE FROM emp where sal<(select avg(E.sal) from emp E where E.deptno=emp.deptno);

----- Création de table DDL -----

Exo 34 : Créer la table des bonus remplie avec le numéro d'employé et sa commission uniquement si la commission existe
Ajouter une colonne type_paiement (1 chiffre)
CREATE TABLE bonus AS select empno, comm from emp where comm is not null;
ALTER TABLE bonus ADD(TYPE_PAIEMENT number(1))

----- Vue et administration -----

Exo 35 : Créer une vue des managers
Augmenter les salaires des managers de 10% en utilisant cette vue
Vérifier que les modifications ont été prises en compte dans la table EMP
Faire un rollback et regarder l'état de la table EMP
Create VIEW vue_manager as select * from emp where job = 'MANAGER'
UPDATE vue_manager SET sal=sal*0.1

Exo 36 : Création d'une table dont la structure est identique à la table DEPT
Modification de cette table afin de rendre le "deptno" obligatoire
Autorisation de lecture de cette table par le voisin
Fichier SQL paramétré d'insertion de lignes dans la table
Création d'un synonyme pour utiliser la table du voisin
Insertion dans votre table des valeurs de la table du voisin
create table dept2 as select * from dept where 1=2;
alter table dept2 modify(deptno number(2) not null;
GRAND select ON dept2 TO voisin;
insert into dept2 values (&deptno,'&dname','&loc');
CREATE SYNONYM deptVoisin for voisin.dept;
insert into dept2 select* from deptVoisin;