ezaaaaaaeza

/*drop TABLE EMPLOYE;
drop TABLE SERVICE;


CREATE TABLE SERVICE
(
SerNo integer constraint pk_serno primary key,
Nom varchar2(20) not null,
Lieu varchar2(20) not null
);


CREATE TABLE EMPLOYE
(
EmpNo integer constraint pk_emp primary key,
Nom varchar2(20) not null,
Prenom varchar2(20) not null,
Fonction varchar2(50) not null constraint ch_fonction check (Fonction in('Président', 'Gérant', 'Secrétaire', 'Vendeur')),
Chef integer constraint fk_chef references EMPLOYE(EmpNo),
DateEmbauche date not null,
Salaire number(6,2) default 0,
Commission number(6,2),
SerNo integer not null constraint fk_serno references SERVICE(SerNo) on delete cascade,
constraint uk_nomprenom unique (Nom, Prenom)
);


INSERT INTO SERVICE values(10,'Comptabilité', 'Paris');
INSERT INTO SERVICE values(20,'Affaires Générales', 'Dijon');
INSERT INTO SERVICE values(30,'Ventes', 'Besancon');
INSERT INTO SERVICE values(40,'Ressources Humaines', 'Lyon');

INSERT INTO EMPLOYE VALUES(7839, 'Ramirez', 'Jules', 'Président', NULL, '17/11/1981', 5000.00, NULL, 20);
INSERT INTO EMPLOYE VALUES(7698, 'Bourgeois', 'Joël', 'Gérant', 7839, '01/05/1981', 2800.00, NULL, 30);
INSERT INTO EMPLOYE VALUES(7566, 'Deschamps', 'Jean', 'Gérant', 7839, '02/04/1981', 2975.00, NULL, 20);
INSERT INTO EMPLOYE VALUES(7782, 'Lapotre', 'Albert', 'Gérant', 7839, '09/06/1981', 2450.00, NULL, 10);
INSERT INTO EMPLOYE VALUES(7902, 'Jamme', 'Michelle', 'Gérant', 7566, '03/12/1981', 3000.00, NULL, 20);
INSERT INTO EMPLOYE VALUES(7369, 'Dupont', 'Jean', 'Secrétaire', 7902, '17/12/1980', 800.00, NULL, 20);
INSERT INTO EMPLOYE VALUES(7499, 'Martin', 'Adam', 'Vendeur', 7698, '20/02/1981', 1600.00, 300.00, 30);
INSERT INTO EMPLOYE VALUES(7521, 'Dupuy', 'Maurice', 'Vendeur', 7839, '22/09/1980', 7000.00, 500.00, 30);
INSERT INTO EMPLOYE VALUES(7654, 'Martin', 'Bernard', 'Vendeur', 7698, '28/09/1980', 1250.00, 1400.00, 30);
INSERT INTO EMPLOYE VALUES(7788, 'Remond', 'Pierre', 'Secrétaire', 7566, '09/11/1981', 3000.00, NULL, 20);
INSERT INTO EMPLOYE VALUES(7844, 'Turpin', 'Claire', 'Vendeur', 7698, '08/09/1981', 1500.00, 0.00, 30);
INSERT INTO EMPLOYE VALUES(7876, 'Jaillot', 'Elodie', 'Secrétaire', 7788, '23/09/1981', 1100.00, NULL, 20);
INSERT INTO EMPLOYE VALUES(7900, 'Sibille', 'Lionel', 'Vendeur', 7698, '03/12/1981', 950.00, NULL, 30);
INSERT INTO EMPLOYE VALUES(7934, 'Chauvet', 'Sylvie', 'Secrétaire', 7782, '23/01/1982', 1300.00, NULL, 10);*/

-- TP 2
--Q1
SELECT EmpNo FROM EMPLOYE WHERE EmpNo not in (select distinct chef from EMPLOYE where chef is not null);
SELECT EmpNo FROM EMPLOYE MINUS SELECT CHEF FROM EMPLOYE;
SELECT EmpNo From EMPLOYE empl WHERE NOT EXISTS (SELECT * from employe where employe.chef = empl.EmpNo);

--Q2
SELECT EmpNo FROM EMPLOYE WHERE CHEF IS NULL;

--Q3
SELECT distinct chef from employe where chef is not null;

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

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

--Q7
Select nom from employe where EmpNo in (Select distinct )