SHARE
TWEET

ezaaaaaaeza

a guest Oct 17th, 2019 114 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*drop TABLE EMPLOYE;
  2. drop TABLE SERVICE;
  3.  
  4.  
  5. CREATE TABLE SERVICE
  6. (
  7. SerNo integer constraint pk_serno primary key,
  8. Nom varchar2(20) not null,
  9. Lieu varchar2(20) not null
  10. );
  11.  
  12.  
  13. CREATE TABLE EMPLOYE
  14. (
  15. EmpNo integer constraint pk_emp primary key,
  16. Nom varchar2(20) not null,
  17. Prenom varchar2(20) not null,
  18. Fonction varchar2(50) not null constraint ch_fonction check (Fonction in('Président', 'Gérant', 'Secrétaire', 'Vendeur')),
  19. Chef integer constraint fk_chef references EMPLOYE(EmpNo),
  20. DateEmbauche date not null,
  21. Salaire number(6,2) default 0,
  22. Commission number(6,2),
  23. SerNo integer not null constraint fk_serno references SERVICE(SerNo) on delete cascade,
  24. constraint uk_nomprenom unique (Nom, Prenom)
  25. );
  26.  
  27.  
  28. INSERT INTO SERVICE values(10,'Comptabilité', 'Paris');
  29. INSERT INTO SERVICE values(20,'Affaires Générales', 'Dijon');
  30. INSERT INTO SERVICE values(30,'Ventes', 'Besancon');
  31. INSERT INTO SERVICE values(40,'Ressources Humaines', 'Lyon');
  32.  
  33. INSERT INTO EMPLOYE VALUES(7839, 'Ramirez', 'Jules', 'Président', NULL, '17/11/1981', 5000.00, NULL, 20);
  34. INSERT INTO EMPLOYE VALUES(7698, 'Bourgeois', 'Joël', 'Gérant', 7839, '01/05/1981', 2800.00, NULL, 30);
  35. INSERT INTO EMPLOYE VALUES(7566, 'Deschamps', 'Jean', 'Gérant', 7839, '02/04/1981', 2975.00, NULL, 20);
  36. INSERT INTO EMPLOYE VALUES(7782, 'Lapotre', 'Albert', 'Gérant', 7839, '09/06/1981', 2450.00, NULL, 10);
  37. INSERT INTO EMPLOYE VALUES(7902, 'Jamme', 'Michelle', 'Gérant', 7566, '03/12/1981', 3000.00, NULL, 20);
  38. INSERT INTO EMPLOYE VALUES(7369, 'Dupont', 'Jean', 'Secrétaire', 7902, '17/12/1980', 800.00, NULL, 20);
  39. INSERT INTO EMPLOYE VALUES(7499, 'Martin', 'Adam', 'Vendeur', 7698, '20/02/1981', 1600.00, 300.00, 30);
  40. INSERT INTO EMPLOYE VALUES(7521, 'Dupuy', 'Maurice', 'Vendeur', 7839, '22/09/1980', 7000.00, 500.00, 30);
  41. INSERT INTO EMPLOYE VALUES(7654, 'Martin', 'Bernard', 'Vendeur', 7698, '28/09/1980', 1250.00, 1400.00, 30);
  42. INSERT INTO EMPLOYE VALUES(7788, 'Remond', 'Pierre', 'Secrétaire', 7566, '09/11/1981', 3000.00, NULL, 20);
  43. INSERT INTO EMPLOYE VALUES(7844, 'Turpin', 'Claire', 'Vendeur', 7698, '08/09/1981', 1500.00, 0.00, 30);
  44. INSERT INTO EMPLOYE VALUES(7876, 'Jaillot', 'Elodie', 'Secrétaire', 7788, '23/09/1981', 1100.00, NULL, 20);
  45. INSERT INTO EMPLOYE VALUES(7900, 'Sibille', 'Lionel', 'Vendeur', 7698, '03/12/1981', 950.00, NULL, 30);
  46. INSERT INTO EMPLOYE VALUES(7934, 'Chauvet', 'Sylvie', 'Secrétaire', 7782, '23/01/1982', 1300.00, NULL, 10);*/
  47.  
  48. -- TP 2
  49. --Q1
  50. SELECT EmpNo FROM EMPLOYE WHERE EmpNo not in (select distinct chef from EMPLOYE where chef is not null);
  51. SELECT EmpNo FROM EMPLOYE MINUS SELECT CHEF FROM EMPLOYE;
  52. SELECT EmpNo From EMPLOYE empl WHERE NOT EXISTS (SELECT * from employe where employe.chef = empl.EmpNo);
  53.  
  54. --Q2
  55. SELECT EmpNo FROM EMPLOYE WHERE CHEF IS NULL;
  56.  
  57. --Q3
  58. SELECT distinct chef from employe where chef is not null;
  59.  
  60. --Q5
  61. 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);
  62.  
  63. --Q6
  64. 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);
  65.  
  66. --Q7
  67. Select nom from employe where EmpNo in (Select distinct )
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top