· 8 years ago · Aug 19, 2018, 06:32 PM
1-- TP sur Entreprrise
2
3
4-- Création des tables avec contraintes internes
5DROP TABLE IF EXISTS employe CASCADE;
6CREATE TABLE employe (
7 num_ss int PRIMARY KEY,
8 nom text NOT NULL,
9 prenom text NOT NULL,
10 adresse text,
11 dob date,
12 sexe char NOT NULL CHECK (sexe = 'M' OR sexe = 'F'),
13 num_service int,
14 salaire int DEFAULT (0) NOT NULL CHECK (salaire >= 0)
15);
16
17DROP TABLE IF EXISTS service CASCADE;
18CREATE TABLE service (
19 num_service int PRIMARY KEY,
20 nom_service text NOT NULL,
21 num_responsable int,
22 nombre_employes int CHECK (nombre_employes >= 0)
23);
24
25DROP TABLE IF EXISTS batiment_service CASCADE;
26CREATE TABLE batiment_service (
27 num_service int,
28 num_batiment int
29);
30
31DROP TABLE IF EXISTS projet CASCADE;
32CREATE TABLE projet (
33 num_projet int PRIMARY KEY,
34 nom_projet text NOT NULL,
35 num_service int
36);
37
38DROP TABLE IF EXISTS affectation CASCADE;
39CREATE TABLE affectation (
40 num_employe int,
41 num_projet int,
42 heures_passees int CHECK (heures_passees >= 0),
43 CONSTRAINT uniq_row UNIQUE (num_employe, num_projet)
44);
45
46DROP TABLE IF EXISTS famille CASCADE;
47CREATE TABLE famille (
48 num_employe int,
49 type_relation text NOT NULL,
50 nom text NOT NULL,
51 prenom text NOT NULL,
52 dob date,
53 sexe char NOT NULL CHECK (sexe = 'M' OR sexe = 'F'),
54 CONSTRAINT pk PRIMARY KEY (num_employe, type_relation, prenom)
55);
56
57-- Création des contraintes de clés étrangères
58ALTER TABLE employe ADD CONSTRAINT jt_serv FOREIGN KEY (num_service) REFERENCES service (num_service) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE;
59ALTER TABLE service ADD CONSTRAINT id_resp FOREIGN KEY (num_responsable) REFERENCES employe (num_ss) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE;
60ALTER TABLE batiment_service ADD CONSTRAINT jt_serv FOREIGN KEY (num_service) REFERENCES service (num_service) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE;
61ALTER TABLE projet ADD CONSTRAINT jt_serv FOREIGN KEY (num_service) REFERENCES service (num_service) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE;
62ALTER TABLE affectation
63 ADD CONSTRAINT jt_emp FOREIGN KEY (num_employe) REFERENCES employe (num_ss) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE,
64 ADD CONSTRAINT jt_proj FOREIGN KEY (num_projet) REFERENCES projet (num_projet) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE;
65ALTER TABLE famille ADD CONSTRAINT jt_emp FOREIGN KEY (num_employe) REFERENCES employe (num_ss) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE;
66
67-- Insertion de données
68INSERT INTO employe (num_ss, nom, prenom, adresse, dob, sexe, num_service, salaire) VALUES (1882991234, 'Grandjean', 'Gerard', '123 rue Bidon, 79000 Niort', '1967-11-16', 'M', 3, 1200);
69INSERT INTO employe (num_ss, nom, prenom, adresse, dob, sexe, num_service, salaire) VALUES(1882995687, 'Martin', 'Jean', '12 av V. Hugo, 75000 Paris', '1987-02-01', 'M', 1, 1100);
70INSERT INTO employe (num_ss, nom, prenom, adresse, sexe, num_service, salaire) VALUES (1882996512, 'Bouffard', 'Mathilde','avenue einstein', 'F', 1, 3000);
71INSERT INTO employe (num_ss, nom, prenom, adresse, sexe, num_service, salaire) VALUES (1882992150, 'Dupond', 'Louis','avenue monnet', 'M', 2, 1000);
72INSERT INTO employe (num_ss, nom, prenom, adresse, sexe, num_service, salaire) VALUES (1882992153, 'Dupont', 'Louise','avenue monnet', 'F', 2, 2000);
73
74INSERT INTO service (num_service, nom_service, num_responsable, nombre_employes) VALUES (1, 'Informatique', 1882991234, 17);
75INSERT INTO service (num_service, nom_service, num_responsable, nombre_employes) VALUES (2, 'Qualite', 1882996512, 22);
76INSERT INTO service (num_service, nom_service, num_responsable, nombre_employes) VALUES (3, 'Recherche', 1882995687, 5);
77INSERT INTO service (num_service, nom_service, num_responsable, nombre_employes) VALUES (4, 'Administratif', 1882992153, 58);
78
79INSERT INTO batiment_service (num_service, num_batiment ) VALUES (1, 2);
80INSERT INTO batiment_service (num_service, num_batiment ) VALUES (2, 5);
81INSERT INTO batiment_service (num_service, num_batiment ) VALUES (3, 4);
82INSERT INTO batiment_service (num_service, num_batiment ) VALUES (4, 7);
83
84INSERT INTO projet (num_projet, nom_projet, num_service ) VALUES (1, 'Projet Beta', 2);
85INSERT INTO projet (num_projet, nom_projet, num_service ) VALUES (2, 'Projet Delta', 4);
86INSERT INTO projet (num_projet, nom_projet, num_service ) VALUES (3, 'Projet Epsylone', 1);
87INSERT INTO projet (num_projet, nom_projet, num_service ) VALUES (4, 'Projet Alpha', 3);
88INSERT INTO projet (num_projet, nom_projet, num_service ) VALUES (5, 'Projet Beta', 3);
89INSERT INTO projet (num_projet, nom_projet, num_service ) VALUES (6, 'Projet Delta', 3);
90INSERT INTO projet (num_projet, nom_projet, num_service ) VALUES (7, 'Projet Epsylone', 3);
91
92INSERT INTO affectation (num_employe, num_projet, heures_passees ) VALUES (1882991234, 1, 30);
93INSERT INTO affectation (num_employe, num_projet, heures_passees ) VALUES (1882992150, 1, 200);
94INSERT INTO affectation (num_employe, num_projet, heures_passees ) VALUES (1882992150, 2, 700);
95INSERT INTO affectation (num_employe, num_projet, heures_passees ) VALUES (1882992150, 3, 600);
96INSERT INTO affectation (num_employe, num_projet, heures_passees ) VALUES (1882996512, 3, 100);
97INSERT INTO affectation (num_employe, num_projet, heures_passees ) VALUES (1882996512, 2, 400);
98INSERT INTO affectation (num_employe, num_projet, heures_passees ) VALUES (1882995687, 4, 600);
99INSERT INTO affectation (num_employe, num_projet, heures_passees ) VALUES (1882995687, 5, 100);
100INSERT INTO affectation (num_employe, num_projet, heures_passees ) VALUES (1882995687, 6, 200);
101INSERT INTO affectation (num_employe, num_projet, heures_passees ) VALUES (1882995687, 7, 300);
102
103INSERT INTO famille (num_employe, type_relation, nom, prenom, dob, sexe) VALUES (1882991234, 'Marie', 'Grandjean', 'Nicole', '1977-11-03', 'F');
104INSERT INTO famille (num_employe, type_relation, nom, prenom, dob, sexe) VALUES (1882991234, 'Enfant', 'Grandjean', 'Mark', '1992-08-10', 'M');
105INSERT INTO famille (num_employe, type_relation, nom, prenom, dob, sexe) VALUES (1882991234, 'Enfant', 'Grandjean', 'Myriame', '1993-01-17', 'F');
106INSERT INTO famille (num_employe, type_relation, nom, prenom, dob, sexe) VALUES (1882996512, 'Marie', 'Bouffard', 'Michel', '1970-08-03', 'M');
107
108-- Requêtes SQL
109-- a) Trouver le nom des employés dont on ne connaît pas la date de naissance
110SELECT prenom, nom
111FROM employe
112WHERE dob IS NULL;
113
114-- prenom | nom
115-- ----------+----------
116-- Mathilde | Bouffard
117-- Louis | Dupond
118-- Louise | Dupont
119-- (3 rows)
120
121
122-- b) Trouver le nom et l'adresse des employés mariés, mais sans enfant
123SELECT employe.prenom, employe.nom, adresse
124FROM employe, famille
125WHERE num_ss = num_employe
126AND type_relation = 'Marie'
127AND num_ss NOT IN (
128 SELECT num_employe
129 FROM famille
130 WHERE type_relation = 'Enfant'
131);
132
133-- prenom | nom | adresse
134-- ----------+----------+-----------------
135-- Mathilde | Bouffard | avenue einstein
136-- (1 row)
137
138
139-- c) Trouver le nom des employés travaillant sur tous les projets pris en charge par le service "Recherche"
140SELECT DISTINCT prenom, nom
141FROM employe, affectation, projet, service
142WHERE num_ss = affectation.num_employe
143AND affectation.num_projet = projet.num_projet
144AND projet.num_service = service.num_service
145AND nom_service = 'Recherche';
146
147-- prenom | nom
148-- --------+--------
149-- Jean | Martin
150-- (1 row)
151
152
153-- d) Trouver la moyenne des salaires de chaque service, ainsi que le salaire minimum et le salaire maximum, et donner le résultat par ordre alphabétique de nom de service
154SELECT nom_service, ROUND(AVG(salaire), 2) AS moyenne, MAX(salaire) AS max, MIN(salaire) AS min
155FROM employe, service
156WHERE employe.num_service = service.num_service
157GROUP BY nom_service
158ORDER BY nom_service;
159
160-- nom_service | moyenne | max | min
161-- --------------+---------+------+------
162-- Informatique | 2050.00 | 3000 | 1100
163-- Qualite | 1500.00 | 2000 | 1000
164-- Recherche | 1200.00 | 1200 | 1200
165-- (3 rows)
166
167
168-- e) Trouver le nom des chefs de service ayant des projets pour lesquels le nombre cumulé d'heures passées dépasse 1000 heures
169SELECT prenom, nom
170FROM employe, service, affectation, projet
171WHERE num_ss = num_responsable
172AND service.num_service = projet.num_service
173AND projet.num_projet = affectation.num_projet
174GROUP BY prenom, nom
175HAVING SUM(heures_passees) >= 1000;
176
177-- prenom | nom
178-- --------+--------
179-- Louise | Dupont
180-- Jean | Martin
181-- (2 rows)
182
183
184-- f) Trouver toutes les informations concernant les employés célibataires et sans enfants (pas de n-uplet dans la relation FAMILLE pour ces employés)
185SELECT DISTINCT *
186FROM employe , service, projet, affectation, batiment_service
187WHERE employe.num_service = service.num_service
188AND service.num_service = batiment_service.num_service
189AND projet.num_service = service.num_service
190AND affectation.num_projet = projet.num_projet
191AND NOT EXISTS (
192 SELECT num_employe
193 FROM famille
194 WHERE num_ss = famille.num_employe
195);
196
197-- num_ss | nom | prenom | adresse | dob | sexe | num_service | salaire | num_service | nom_service | num_responsable | nombre_employes | num_projet | nom_projet | num_service | num_employe | num_projet | heures_passees | num_service | num_batiment
198-- ------------+--------+--------+----------------------------+------------+------+-------------+---------+-------------+--------------+-----------------+-----------------+------------+-----------------+-------------+-------------+------------+----------------+-------------+--------------
199-- 1882992150 | Dupond | Louis | avenue monnet | | M | 2 | 1000 | 2 | Qualite | 1882996512 | 22 | 1 | Projet Beta | 2 | 1882991234 | 1 | 30 | 2 | 5
200-- 1882992150 | Dupond | Louis | avenue monnet | | M | 2 | 1000 | 2 | Qualite | 1882996512 | 22 | 1 | Projet Beta | 2 | 1882992150 | 1 | 200 | 2 | 5
201-- 1882992153 | Dupont | Louise | avenue monnet | | F | 2 | 2000 | 2 | Qualite | 1882996512 | 22 | 1 | Projet Beta | 2 | 1882991234 | 1 | 30 | 2 | 5
202-- 1882992153 | Dupont | Louise | avenue monnet | | F | 2 | 2000 | 2 | Qualite | 1882996512 | 22 | 1 | Projet Beta | 2 | 1882992150 | 1 | 200 | 2 | 5
203-- 1882995687 | Martin | Jean | 12 av V. Hugo, 75000 Paris | 1987-02-01 | M | 1 | 1100 | 1 | Informatique | 1882991234 | 17 | 3 | Projet Epsylone | 1 | 1882992150 | 3 | 600 | 1 | 2
204-- 1882995687 | Martin | Jean | 12 av V. Hugo, 75000 Paris | 1987-02-01 | M | 1 | 1100 | 1 | Informatique | 1882991234 | 17 | 3 | Projet Epsylone | 1 | 1882996512 | 3 | 100 | 1 | 2
205-- (6 rows)