Ejv57q8V

1/*Q1*/
2
3SELECT EMPNO FROM EMPLOYE MINUS SELECT CHEF FROM EMPLOYE;
4
5SELECT EMPNO FROM EMPLOYE WHERE EMPNO NOT IN (SELECT DISTINCT CHEF FROM EMPLOYE WHERE CHEF IS NOT NULL);
6
7SELECT EMPNO FROM EMPLOYE EMP WHERE NOT EXISTS (SELECT * FROM EMPLOYE WHERE EMPLOYE.CHEF = EMP.EMPNO);
8
9SELECT EMPNO FROM EMPLOYE EMP WHERE EMPNO != ALL(SELECT DISTINCT CHEF FROM EMPLOYE WHERE CHEF IS NOT NULL);
10
11SELECT EMP.EMPNO FROM EMPLOYE EMP LEFT OUTER JOIN EMPLOYE ON EMP.EMPNO = EMPLOYE.CHEF WHERE EMPLOYE.CHEF IS NULL;
12SELECT EMP.EMPNO, EMP.CHEF, EMPLOYE.CHEF FROM EMPLOYE EMP LEFT OUTER JOIN EMPLOYE ON EMP.EMPNO = EMPLOYE.CHEF;
13
14/*Q2*/
15SELECT EMPNO FROM EMPLOYE WHERE CHEF IS NULL;
16
17/*Q3*/
18SELECT DISTINCT CHEF FROM EMPLOYE WHERE CHEF IS NOT NULL;
19
20/*Q4*/
21
22SELECT DISTINCT EMPNO FROM EMPLOYE E WHERE EMPNO IN (SELECT DISTINCT CHEF FROM EMPLOYE WHERE CHEF IS NOT NULL) AND SALAIRE < ANY(SELECT SALAIRE FROM EMPLOYE WHERE CHEF = E.EMPNO);
23
24/*Q5*/
25SELECT DISTINCT EMPNO FROM EMPLOYE E WHERE EMPNO IN (SELECT DISTINCT CHEF FROM EMPLOYE WHERE CHEF IS NOT NULL) AND SALAIRE > ALL(SELECT SALAIRE FROM EMPLOYE WHERE CHEF = E.EMPNO);
26
27/*Q6*/
28SELECT NOM FROM EMPLOYE WHERE EmpNo IN (SELECT DISTINCT CHEF FROM EMPLOYE WHERE NOM = '&value');
29
30
31/*Arbres oracle*/
32
33SELECT Nom, LEVEL FROM EMPLOYE 
34WHERE EMPNO IN(SELECT DISTINCT CHEF FROM EMPLOYE WHERE CHEF IS NOT NULL) OR CHEF IN(SELECT DISTINCT CHEF FROM EMPLOYE WHERE CHEF IS NOT NULL)
35START WITH EMPNO = (SELECT EMPNO FROM EMPLOYE WHERE NOM = 'Ramirez')
36CONNECT BY PRIOR EmpNo = Chef;
37
38/*Affichage*/
39SELECT LPAD('-', 3*LEVEL) || Nom AS "Les esclaves de Ramirez sont" FROM EMPLOYE 
40WHERE EMPNO IN(SELECT DISTINCT CHEF FROM EMPLOYE WHERE CHEF IS NOT NULL) OR CHEF IN(SELECT DISTINCT CHEF FROM EMPLOYE WHERE CHEF IS NOT NULL)
41START WITH EMPNO = (SELECT EMPNO FROM EMPLOYE WHERE NOM = 'Ramirez')
42CONNECT BY PRIOR EmpNo = Chef;
43
44/*Avec CTE*/
45
46WITH CTE_EMP(EMPNO, NOM, LVL) AS 
47((SELECT EMPLOYE.EMPNO, EMPLOYE.NOM, 1 LVL FROM EMPLOYE WHERE NOM = 'Ramirez')  
48UNION ALL 
49(SELECT E.EMPNO, E.NOM, CTE_EMP.LVL +1 FROM CTE_EMP, EMPLOYE E WHERE CTE_EMP.EMPNO = E.CHEF)) SEARCH DEPTH FIRST BY EMPNO SET niveau SELECT LPAD('-', 3*CTE_EMP.LVL) || Nom AS "Les esclaves de Ramirez sont" FROM CTE_EMP;
50
51/*Feuille, branche, racine */
52
53
54SELECT NOM, CONNECT_BY_ISLEAF AS FEUILLE,
55(SELECT COUNT(*) FROM EMPLOYE E WHERE E.CHEF = EMPLOYE.EMPNO AND EMPLOYE.CHEF IS NOT NULL AND ROWNUM =1) AS BRANCHE, 
56DECODE(EMPNO,CONNECT_BY_ROOT(EMPNO),1,0) AS RACINE FROM EMPLOYE WHERE EMPNO IN(SELECT DISTINCT CHEF FROM EMPLOYE WHERE CHEF IS NOT NULL) OR CHEF IN(SELECT DISTINCT CHEF FROM EMPLOYE WHERE CHEF IS NOT NULL)
57START WITH EMPNO = (SELECT EMPNO FROM EMPLOYE WHERE NOM = 'Ramirez')
58CONNECT BY PRIOR EmpNo = Chef;
59
60/*Vues*/
61CREATE VIEW SALAIRES AS SELECT SERVICE.SERNO, SERVICE.NOM AS NomS, SERVICE.LIEU, MIN(SALAIRE) AS SalaireMin, MAX(SALAIRE) AS SalaireMax FROM SERVICE LEFT JOIN EMPLOYE ON SERVICE.SERNO = EMPLOYE.SERNO GROUP BY SERVICE.SERNO, SERVICE.NOM, SERVICE.LIEU;
62
63CREATE OR REPLACE VIEW SALAIREFRANC (EMPNO,NOM,PRENOM,FONCTION,SERNO,SALAIREFRANC,COMMISSIONFRANC) AS SELECT EMPNO, NOM, PRENOM, FONCTION, SERNO, (SALAIRE*6.55957) ,(COMMISSION*6.55957) FROM EMPLOYE;
64
65INSERT INTO EMPLOYE VALUES(7658, 'Lapin','Pierre','Secretaire',7839,'17/11/1981',5000.00,NULL,40);
66
67DELETE FROM SALAIREFRANC WHERE EMPNO=7658;
68
69/*Fonctions Analytiques*/
70
71/*1*/
72SELECT EMPNO, SALAIRE, SUM(SALAIRE) OVER (ORDER BY SALAIRE) AS SOMMEINTERMEDIAIRE FROM EMPLOYE;
73
74/*2*/
75SELECT SERV10.SALARY AS SALAIRESERVICE10,SERV10.SALARY / S.SALAIRE *100 AS PARTSALAIRETOTAL FROM (SELECT SUM(SALAIRE)AS SALAIRE FROM EMPLOYE) S, (SELECT SUM(SALAIRE) As salary FROM EMPLOYE WHERE SERNO = 10) SERV10;
76
77SELECT distinct(SALARY / SALAIRE *100) AS PARTSALAIRETOTAL 
78FROM 
79(SELECT SERNO, SUM(SALAIRE) OVER () AS SALAIRE, SUM(SALAIRE) over (PARTITION BY SERNO) As salary FROM EMPLOYE) where SERNO = 10;
80
81/*3*/
82SELECT DIFFERENCE FROM (SELECT SERNO, LEAD(DATEEMBAUCHE,1) OVER(ORDER BY DATEEMBAUCHE) - DATEEMBAUCHE AS DIFFERENCE FROM EMPLOYE) WHERE SERNO = 10;
83
84SELECT TEST - DATEEMBAUCHE AS JOURS_DIFFERENCE FROM (SELECT SERNO, DATEEMBAUCHE, LEAD(DATEEMBAUCHE,1) OVER(ORDER BY DATEEMBAUCHE) AS TEST FROM EMPLOYE) WHERE SERNO = 10;
85
86/*4*/
87SELECT DISTINCT(SERNO), SUM(SALAIRE) AS SOMME_SALAIRE, DENSE_RANK() OVER (ORDER BY SUM(SALAIRE) DESC) AS RANG FROM EMPLOYE GROUP BY SERNO;
88
89/*5*/
90SELECT EMPNO, SERNO, SALAIRE, DATEEMBAUCHE, MAX(SALAIRE) KEEP (DENSE_RANK LAST ORDER BY DATEEMBAUCHE) OVER (PARTITION BY SERNO) AS SALAIRE_DERNIER FROM EMPLOYE;
91
92/*Triggers*/
93/*1*/
94CREATE OR REPLACE TRIGGER OUPS 
95INSTEAD OF INSERT
96ON SALAIREFRANC
97FOR EACH ROW
98BEGIN
99INSERT INTO EMPLOYE VALUES(:NEW.EMPNO, :NEW.NOM, :NEW.PRENOM, :NEW.FONCTION, NULL,SYSDATE,:NEW.SALAIREFRANC/6.55957, :NEW.COMMISSIONFRANC/6.55957, :NEW.SERNO);	
100END;
101/
102
103INSERT INTO SALAIREFRANC VALUES (7658,'Lapin','Pierre','Secretaire',40,7000,200);
104/*2*/
105ALTER TABLE SERVICE ADD NBEMPLOYES INTEGER DEFAULT 0;
106
107/*3*/
108CREATE OR REPLACE TRIGGER ALALA AFTER INSERT OR DELETE OR UPDATE ON EMPLOYE
109BEGIN	
110	UPDATE SERVICE SET NBEMPLOYES = (SELECT COUNT(*) FROM EMPLOYE WHERE Serno = SERVICE.Serno);
111END;
112/
113
114/*4*/
115CREATE OR REPLACE TRIGGER BAHSUPER
116BEFORE INSERT ON SERVICE
117FOR EACH ROW
118DECLARE
119	var SERVICE.Serno%TYPE;
120BEGIN
121	SELECT MAX(SERNO) INTO var FROM SERVICE;
122	IF var IS NULL then
123		:NEW.Serno := 10;
124	ELSE
125			:NEW.Serno := var +10;	
126	END IF;
127END;
128/