· 9 years ago · Dec 15, 2016, 08:40 PM
1/*
2* @Author: Mehdi-H
3* @Date: 2015-03-26 10:09:54
4* @Last Modified by: Mehdi-H
5* @Last Modified time: 2015-04-07 20:29:13
6*/
7
8
9-- Considérons les tables suivantes à créer et à instancier dans la base de données du TD précédent en
10-- choisissant les types adéquats et les contraintes de clés primaires et clés étrangères.
11-- Etudiant (NumEtu, Nom, Prenom, DateNaiss, Rue, CP, Ville)
12-- Matiere (CodeMat, Libelle, Coef)
13-- Epreuve (numepreuve, DateEpreuve, Lieu, #CodeMat)
14-- Notation (#NumEtu, #NumEpreuve, Note)
15
16-- PARTIE 1
17
18DROP TABLE IF EXISTS notation;
19DROP TABLE IF EXISTS epreuve;
20DROP TABLE IF EXISTS etudiant;
21DROP TABLE IF EXISTS matiere;
22
23DROP TABLE IF EXISTS Etudiant;
24CREATE TABLE Etudiant (
25 NumEtu INT UNSIGNED NOT NULL,
26 Nom VARCHAR(40) NOT NULL,
27 Prenom VARCHAR(40) NOT NULL,
28 DateNaiss DATETIME NOT NULL,
29 Rue VARCHAR(40),
30 CP INT UNSIGNED,
31 Ville VARCHAR(40),
32 PRIMARY KEY (NumEtu)
33);
34
35DROP TABLE IF EXISTS Matiere;
36CREATE TABLE Matiere (
37 CodeMat VARCHAR(40) NOT NULL,
38 Libelle VARCHAR(40) NOT NULL,
39 Coeff FLOAT NOT NULL,
40 PRIMARY KEY (CodeMat)
41);
42
43DROP TABLE IF EXISTS Epreuve;
44CREATE TABLE Epreuve (
45 numepreuve INT UNSIGNED NOT NULL,
46 DateEpreuve DATETIME NOT NULL,
47 Lieu VARCHAR(40) NOT NULL,
48 EpreuveCodeMat VARCHAR(40) NOT NULL,
49 PRIMARY KEY (numepreuve),
50 FOREIGN KEY (EpreuveCodeMat) REFERENCES Matiere(CodeMat)
51);
52
53DROP TABLE IF EXISTS Notation;
54CREATE TABLE Notation (
55 NoteNumEtu INT UNSIGNED NOT NULL,
56 Notenumepreuve INT UNSIGNED NOT NULL,
57 Note FLOAT,
58 PRIMARY KEY (NoteNumEtu,Notenumepreuve),
59 FOREIGN KEY (NoteNumEtu) REFERENCES Etudiant(NumEtu),
60 FOREIGN KEY (Notenumepreuve) REFERENCES Matiere(numepreuve),
61 CHECK (Note <= 20)
62);
63
64INSERT INTO Etudiant VALUES(1100, 'TOTO', 'TAlbert', '1981-07-01 00:00:00', 'Rue de Crimée', 75019, 'Paris');
65
66
67-- Ajout des Etudiants
68SELECT * FROM Etudiant;
69INSERT INTO Etudiant(NumEtu, Nom, Prenom, DateNaiss, Rue, CP, Ville) VALUES (110, 'Dupont', 'Albert', '1980-06-01 00:00:00', 'Rue de Crimée', 69001, 'Lyon');
70INSERT INTO Etudiant(NumEtu, Nom, Prenom, DateNaiss, Rue, CP, Ville) VALUES (222, 'West', 'James', '1983-09-03 00:00:00', 'Studio', NULL, 'Hollywood');
71INSERT INTO Etudiant(NumEtu, Nom, Prenom, DateNaiss, Rue, CP, Ville) VALUES (300, 'Martin', 'Marie', '1988-06-05 00:00:00', 'Rue des Acacias', 69130, 'Ecully');
72INSERT INTO Etudiant(NumEtu, Nom, Prenom, DateNaiss, Rue, CP, Ville) VALUES (421, 'Durand', 'Gaston', '1980-11-15 00:00:00', 'Rue de la Meuse', 69008, 'Lyon');
73INSERT INTO Etudiant(NumEtu, Nom, Prenom, DateNaiss, Rue, CP, Ville) VALUES (575, 'Titgoutte', 'Justine', '1985-02-28 00:00:00', 'Chemin du Château', 69630, 'Chaponost');
74INSERT INTO Etudiant(NumEtu, Nom, Prenom, DateNaiss, Rue, CP, Ville) VALUES (667, 'Dupond', 'Noémie', '1987-09-18 00:00:00', 'Rue de Dôle ', 69007, 'Lyon');
75INSERT INTO Etudiant(NumEtu, Nom, Prenom, DateNaiss, Rue, CP, Ville) VALUES (999, 'Phantom', 'Marcel', '1960-01-30 00:00:00', NULL, NULL, NULL);
76
77-- Ajout des Notations
78SELECT * FROM Notation;
79INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (110, 11031, 10);
80INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (110, 11032, 11.5);
81INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (110, 21031, 8.5);
82INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (110, 21032, NULL );
83INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (110, 31030, 13);
84INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (222, 11031, 9);
85INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (222, 11032, 14);
86INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (222, 21031, 12);
87INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (222, 21032, 16);
88INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (222, 31030, 20);
89INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (300, 11031, 14);
90INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (300, 11032, 20);
91INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (300, 21031, 20);
92INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (300, 21032, 13.5);
93INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (300, 31030, 16);
94INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (421, 11031, 5.5);
95INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (421, 11032, 17);
96INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (421, 21031, 1.5);
97INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (421, 21032, NULL );
98INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (421, 31030, 10);
99INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (575, 11031, 13);
100INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (575, 11032, 19);
101INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (575, 21031, 12.5);
102INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (575, 21032, 14);
103INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (575, 31030, 7);
104INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (667, 11031, 16);
105INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (667, 11032, 20);
106INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (667, 21031, 8.5);
107INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note) VALUES (667, 21032, 9.5);
108
109-- Ajout Epreuve
110SELECT * FROM Epreuve;
111INSERT INTO Epreuve(numepreuve, DateEpreuve, Lieu, EpreuveCodeMat) VALUES (11031, '2003-12-15 00:00:00', 'Salle 191L', 'STA ');
112INSERT INTO Epreuve(numepreuve, DateEpreuve, Lieu, EpreuveCodeMat) VALUES (11032, '2004-4-1 00:00:00', 'Amphi G ', 'STA ');
113INSERT INTO Epreuve(numepreuve, DateEpreuve, Lieu, EpreuveCodeMat) VALUES (21031, '2003-10-30 00:00:00', 'Salle 191L', 'INF ');
114INSERT INTO Epreuve(numepreuve, DateEpreuve, Lieu, EpreuveCodeMat) VALUES (21032, '2004-6-1 00:00:00', 'Salle 192L', 'INF ');
115INSERT INTO Epreuve(numepreuve, DateEpreuve, Lieu, EpreuveCodeMat) VALUES (31030, '2004-6-2 00:00:00', 'Salle 05R ', 'ECO');
116
117-- Ajout Matiere
118SELECT * FROM Matiere;
119INSERT INTO Matiere(CodeMat, Libelle, Coeff) VALUES ('STA ', 'Statistique ', 0.4);
120INSERT INTO Matiere(CodeMat, Libelle, Coeff) VALUES ('INF ', 'Informatique', 0.4);
121INSERT INTO Matiere(CodeMat, Libelle, Coeff) VALUES ('ECO ', 'Econométrie ', 0.2);
122
123
124
125-- PARTIE 2
126
127-- Question 1
128-- 1. Liste des étudiants triés par ordre alphabétique décroissant.
129SELECT * FROM Etudiant ORDER BY Prenom DESC;
130
131-- Question 2
132-- 2. Liste des épreuves dont la date se situe entre le 1er janvier et le 30 juin 2004.
133SELECT * FROM Epreuve WHERE DateEpreuve BETWEEN '2004-01-01 00:00:00' AND '2004-06-30 00:00:00'
134
135-- Question 3
136-- 3. Nombre total d'épreuves
137SELECT COUNT(*) AS nb_epreuve FROM Epreuve
138
139-- Question 4
140-- 4. Nombre de notes indéterminées (NULL).
141SELECT COUNT(*) FROM notation WHERE note IS NULL
142
143-- Question 5
144-- 5. Liste des notes en précisant pour chacune le nom et le prénom de l'étudiant qui l'a obtenue.
145SELECT Etudiant.nom, Etudiant.Prenom, Notation.note FROM Etudiant
146INNER JOIN Notation ON Etudiant.NumEtu = Notation.NoteNumEtu
147
148-- Question 6
149-- 6. Moyennes des notes de chaque étudiant (indiquer le nom et le prénom), classées de la meilleure à la moins
150-- bonne.
151SELECT Etudiant.nom, Etudiant.Prenom, AVG(Notation.note) AS moyenne_etu FROM Etudiant
152INNER JOIN Notation ON Etudiant.NumEtu = Notation.NoteNumEtu
153GROUP BY Etudiant.NumEtu
154ORDER BY moyenne_etu DESC
155
156-- Question 7
157-- 7. Moyennes des notes pour les matières (indiquer le libellé de la matière) comportant plus d'une épreuve.
158SELECT Matiere.libelle, SUM(Notation.note)/COUNT(Notation.note) AS moyenne_matiere
159FROM Etudiant
160INNER JOIN Notation ON Etudiant.NumEtu = Notation.NoteNumEtu
161INNER JOIN Epreuve ON Epreuve.NumEpreuve = Notation.NoteNumEpreuve
162INNER JOIN Matiere ON Matiere.CodeMat = Epreuve.EpreuveCodeMat
163GROUP BY Matiere.libelle
164HAVING COUNT(DISTINCT Epreuve.NumEpreuve) > 1
165
166-- Question 8
167-- 8. Moyennes des notes obtenues aux épreuves (indiquer le numéro d'épreuve) où moins de 6 étudiants ont été
168-- notés.
169
170SELECT Epreuve.NumEpreuve, Epreuve.EpreuveCodeMat, AVG(Notation.note) AS moyenne_epreuve, COUNT(DISTINCT Notation.note) AS nb_note
171FROM Epreuve
172INNER JOIN Notation ON Epreuve.NumEpreuve = Notation.Notenumepreuve
173GROUP BY Epreuve.EpreuveCodeMat
174HAVING COUNT(DISTINCT Notation.note) < 6
175
176-- Question 9
177-- 9. Liste des étudiants qui n’ont obtenu aucune note
178SELECT Etudiant.nom, Etudiant.Prenom FROM Etudiant
179INNER JOIN Notation ON Etudiant.NumEtu = Notation.NoteNumEtu
180GROUP BY Etudiant.NumEtu
181HAVING COUNT(DISTINCT Notation.Note) = 0
182
183-- Question 10
184-- 10. Liste des étudiants qui ont obtenu au moins une note
185SELECT Etudiant.nom, Etudiant.Prenom FROM Etudiant
186INNER JOIN Notation ON Etudiant.NumEtu = Notation.NoteNumEtu
187GROUP BY Etudiant.NumEtu
188HAVING COUNT(Notation.Note) > 0
189
190-- Question 11
191-- 11. Liste des étudiants qui ont obtenu la plus petite note en Informatique
192SELECT Etudiant.nom, Etudiant.Prenom, Notation.note, Epreuve.EpreuveCodeMat FROM Etudiant
193INNER JOIN Notation ON Etudiant.NumEtu = Notation.NoteNumEtu
194INNER JOIN Epreuve ON Notation.NoteNumEpreuve = Epreuve.NumEpreuve
195WHERE Epreuve.EpreuveCodeMat = 'INF'
196GROUP BY Etudiant.NumEtu
197HAVING Notation.note = (
198 SELECT MIN(Notation.note)
199 FROM Notation
200 INNER JOIN Epreuve ON Notation.NoteNumEpreuve = Epreuve.NumEpreuve
201 WHERE Epreuve.EpreuveCodeMat = 'INF'
202)
203
204-- Question 12
205-- 12. Liste des étudiants qui ont obtenu la meilleure moyenne
206SELECT Etudiant.nom, Etudiant.Prenom, AVG(Notation.note) AS moyenne_etu FROM Etudiant
207INNER JOIN Notation ON Etudiant.NumEtu = Notation.NoteNumEtu
208GROUP BY Etudiant.NumEtu
209HAVING moyenne_etu =
210 (SELECT MAX(avg_note) FROM
211 (SELECT avg(notation.note) AS avg_note
212 FROM notation
213 INNER JOIN etudiant ON etudiant.NumEtu = notation.NoteNumEtu
214 GROUP BY etudiant.NumEtu) AS T1);
215
216-- 13. Liste des étudiants qui ont participé à toutes les épreuves
217SELECT Etudiant.nom, Etudiant.Prenom FROM Etudiant
218INNER JOIN Notation ON Etudiant.NumEtu = Notation.NoteNumEtu
219WHERE NULLIF(Notation.note,NULL) IS NOT NULL
220GROUP BY Etudiant.nom
221
222-- 14. Liste des étudiants qui ont obtenu des notes dans toutes les épreuves
223CF 13
224
225-- 15. Liste des matières dont toutes les notes sont supérieures ou égales à 10
226SELECT Matiere.Codemat FROM Matiere WHERE 10 <= ALL(
227SELECT Notation.note FROM Etudiant
228INNER JOIN Notation ON Etudiant.NumEtu = Notation.NoteNumEtu
229INNER JOIN Epreuve ON Epreuve.NumEpreuve = Notation.NoteNumEpreuve
230INNER JOIN Matiere ON Matiere.CodeMat = Epreuve.EpreuveCodeMat )
231
232
233-- -- III – Gestion des vues
234
235-- 1. Créer la vue renfermant tous les étudiants ayant eu des épreuves en Informatique ainsi que les notes
236-- obtenues.
237CREATE VIEW view_notes_info AS
238SELECT Etudiant.nom, Etudiant.Prenom, Notation.note, Epreuve.EpreuveCodeMat FROM Etudiant
239INNER JOIN Notation ON Etudiant.NumEtu = Notation.NoteNumEtu
240INNER JOIN Epreuve ON Notation.NoteNumEpreuve = Epreuve.NumEpreuve
241WHERE Epreuve.EpreuveCodeMat = 'INF'
242
243-- 2. Donner la moyenne et le nombre d’épreuves en informatique de chaque étudiant ayant passé au moins une
244-- épreuve dans cette matière.
245SELECT view_notes_info.nom, view_notes_info.Prenom, COUNT(Epreuve.NumEpreuve) AS nb_epreuve, AVG(view_notes_info.note) AS moyenne_info
246FROM view_notes_info
247INNER JOIN Epreuve ON view_notes_info.EpreuveCodeMat = Epreuve.EpreuveCodeMat
248GROUP BY view_notes_info.nom
249
250-- 3. Donner le nombre d’étudiants ayant eu au moins une moyenne de 10 en Informatique
251SELECT view_notes_info.nom, view_notes_info.Prenom, COUNT(Epreuve.NumEpreuve) AS nb_epreuve, AVG(view_notes_info.note) AS moyenne_info
252FROM view_notes_info
253INNER JOIN Epreuve ON view_notes_info.EpreuveCodeMat = Epreuve.EpreuveCodeMat
254GROUP BY view_notes_info.nom
255HAVING moyenne_info >= 10
256
257-- 4. Donner les noms d’étudiants ainsi que leur moyenne ayant eu une moyenne supérieure ou égale à 10 en
258-- Informatique et classés par ordre de mérite.
259SELECT view_notes_info.nom, view_notes_info.Prenom, COUNT(Epreuve.NumEpreuve) AS nb_epreuve, AVG(view_notes_info.note) AS moyenne_info
260FROM view_notes_info
261INNER JOIN Epreuve ON view_notes_info.EpreuveCodeMat = Epreuve.EpreuveCodeMat
262GROUP BY view_notes_info.nom
263HAVING moyenne_info >= 10
264ORDER BY moyenne_info DESC
265
266-- 5. Donner les noms d’étudiants qui n’ont passé aucune épreuve en Informatique en utilisant les sous requêtes
267-- puis la jointure externe.
268SELECT view_notes_info.nom, view_notes_info.Prenom
269FROM view_notes_info
270WHERE view_notes_info.nom NOT IN (SELECT Etudiant.nom FROM Etudiant)
271
272
273-- -- V – SQL programmable (Triggers, Procédures stockées)
274-- 1. Créer la vue Moyennes contenant la moyenne par étudiant (les informations contenues dans la vue
275-- sont : numéro de l’étudiant, son nom et son prénom, la moyenne obtenue sur l’ensemble de ses
276-- épreuves en tenant compte des coefficients des matières concernées)
277
278CREATE VIEW view_Moyennes AS
279SELECT Etudiant.NumEtu, Etudiant.nom, Etudiant.Prenom, ROUND((SUM(ROUND(Notation.note*Matiere.Coeff,1)))/SUM(Matiere.Coeff),1) AS moy_gen FROM Etudiant
280INNER JOIN Notation ON Etudiant.NumEtu = Notation.NoteNumEtu
281INNER JOIN Epreuve ON Epreuve.NumEpreuve = Notation.NoteNumEpreuve
282INNER JOIN Matiere ON Matiere.CodeMat = Epreuve.EpreuveCodeMat
283GROUP BY Etudiant.NumEtu
284ORDER BY moy_gen DESC
285
286-- 2. Ecrire un trigger qui vérifie bien que la note à saisir pour chaque étudiant est entre 0 et 20 sinon il
287-- affiche un message d’erreur de saisie
288DELIMITER //
289DROP TRIGGER IF EXISTS check_note //
290CREATE TRIGGER check_note BEFORE INSERT ON Notation
291 FOR EACH ROW
292 BEGIN
293 IF (NEW.Note<0 OR NEW.Note>20)
294 THEN
295 SIGNAL sqlstate '45000' SET message_text = 'La valeur de la note doit etre comprise entre 0 et 20';
296 END IF;
297END //
298DELIMITER ;
299
300-- Pour afficher les triggers actifs
301SELECT * FROM information_schema.triggers
302
303--Test pour déclencher le trigger
304INSERT INTO Notation(NoteNumEtu, Notenumepreuve, Note)
305VALUES(1100, 31031, 21);
306
307-- 4. Ecrire la procédure qui permet de stocker dans la table EtudiantsParisiens tous les étudiants parisiens
308-- à chaque fois qu’ils sont insérés dans la table Etudiant.
309
310-- Création de la table EtudiantsParisiens
311DROP TABLE IF EXISTS EtudiantsParisiens;
312CREATE TABLE EtudiantsParisiens AS
313 SELECT * FROM Etudiant WHERE LOWER(Etudiant.Ville) = 'paris';
314
315DELIMITER $$
316DROP TRIGGER IF EXISTS trig_etudiants_parisiens $$
317CREATE TRIGGER trig_etudiants_parisiens AFTER INSERT ON Etudiant
318 FOR EACH ROW
319 BEGIN
320 -- CALL proc_etudiants_parisiens
321 IF NEW.Ville = 'Paris' THEN
322 INSERT INTO EtudiantsParisiens SELECT * FROM Etudiant WHERE Etudiant.NumEtu = NEW.NumEtu;
323 END IF;
324 END $$
325DELIMITER ;
326
327
328-- test
329INSERT INTO Etudiant(NumEtu, Nom, Prenom, DateNaiss, Rue, CP, Ville)
330VALUES (10, 'Houacine', 'Mehdi', '1994-04-18 08:00:00', 'Rue de la paix', 75000, 'Paris');