I N S T I T U T
S U P E R I E U R
INFORMATIQUE
ةـيــملاـعلإل يـلاـعلا دـهعمـلا
ISI
Année Universitaire 2020-2021
Mastère professionnel en logiciels libres - M1
Bases de données Avancées – Activité 3.2
R. ZAAFRANI, 25/10/2020
Correction de l'activité 3.2
1) -- Types
CREATE TYPE Controle_t AS OBJECT(
NumCtrl NUMBER(6),
Type VARCHAR(15),
DateCtrl DATE,
Resultat CHAR(1))
/
CREATE TYPE TAB_Controle AS TABLE OF Controle_t
/
CREATE TYPE Athlete_t AS OBJECT(
NumDossard NUMBER(5),
Nom VARCHAR(20),
Prenom VARCHAR(20),
Controles TAB_Controle)
/
CREATE TYPE TAB_Athlete AS TABLE OF Athlete_t
/
CREATE TYPE Equipe_t AS OBJECT(
NumEq NUMBER(4),
CodePays VARCHAR(3),
Sport VARCHAR(15),
Athletes TAB_Athlete)
/
2) -- Table
CREATE TABLE Equipe OF T_Equipe (CONSTRAINT pk_equipe PRIMARY KEY(NumEq))
NESTED TABLE Athletes STORE AS table_imbriquee_athletes
Advertisement
(NESTED TABLE Controles STORE AS table_ imbriquee_controles);
DESC Equipe
DESC table_imbriquee_athletes
DESC table_imbriquee_controles
3) INSERT INTO Equipe VALUES(1, 'USA', '100 metres',
TAB_Athlete(Athlete_t(1, 'Green', 'Maurice',
TAB_Controle(Controle_t(1, 'Sanguin', '3/08/2019', 'N'))),
Athlete_t(2, 'Lewis', 'Karl',
TAB_Controle(Controle_t(2, 'Sanguin', '3/08/2019', 'N')))));
INSERT INTO Equipe VALUES(2, 'USA', 'Basket',
TAB_Athlete(Athlete_t(23, 'Jordan', 'Michael',
TAB_Controle(Controle_t(3, 'Urinaire', '3/08/2019', 'N'))),
Athlete_t(8, 'Bryant', 'Kobe',
TAB_Controle(Controle_t(4, 'Urinaire', '3/08/2019', 'N'))),
Athlete_t(34, 'O Neal', 'Shaquille',
TAB_Controle(Controle_t(5, 'Urinaire', '3/08/2019', 'N')))));
Bases de données Avancées – Activité 3.2
1
INSERT INTO Equipe VALUES(3, 'UK', '100 metres',
TAB_Athlete(Athlete_t(4, 'Sphynx', 'Le',
TAB_Controle(Controle_t(6, 'Sanguin', '3/08/2019', 'P'),
Controle_t(7, 'Sanguin', '4/08/2019', 'N')))));
INSERT INTO Equipe VALUES(4, 'UK', 'Aviron',
TAB_Athlete(Athlete_t(5, 'Smith', 'John',
TAB_Controle(Controle_t(8, 'Urinaire', '3/08/2019', 'N'))),
Athlete_t(6, 'Smith', 'Jack',
TAB_Controle(Controle_t(9, 'Urinaire', '3/08/2019', 'P'),
Controle_t(10, 'Urinaire', '4/08/2019', 'P')))));
INSERT INTO Equipe VALUES(5, 'FRA', 'Aviron',
TAB_Athlete(Athlete_t(9, 'Dupond', 'Albert',
TAB_Controle(Controle_t(11, 'Urinaire', '3/08/2019', 'N'))),
Athlete_t(7, 'Martin', 'Maurice',
TAB_Controle(Controle_t(12, 'Urinaire', '3/08/2019', 'N')))));
INSERT INTO Equipe VALUES(6, 'FRA', 'Basket',
TAB_Athlete(Athlete_t(10, 'Bilba', 'Jim',
TAB_Controle(Controle_t(13, 'Urinaire', '3/08/2019', 'N'))),
Advertisement
Athlete_t(11, 'Parker', 'Tony',
TAB_Controle(Controle_t(14, 'Urinaire', '3/08/2019', 'N'))),
Athlete_t(12, 'Diaw', 'Boris', TAB_Controle())));
-- Requêtes
-- 1
SELECT e.CodePays, e.Sport, a.Nom, a.Prenom
FROM Equipe e, TABLE(e.Athletes) a;
-- 2
SELECT e.CodePays, e.Sport,
CURSOR(SELECT a.Nom, a.Prenom FROM TABLE(e.Athletes) a)
FROM Equipe e;
Cette seconde requête affiche d’abord le pays et le sport de la première équipe, puis sa liste d’athlètes (noms et
prénoms des athlètes de l’équipe), ensuite le pays et le sport de la seconde équipe et ses athlètes, et ainsi de
suite. La première requête donne un résultat sous forme tabulaire avec le pays, le sport, le nom et le prénom de
chaque athlète.
-- 3
SELECT e.CodePays, a.Nom, a.Prenom
FROM Equipe e, TABLE(e.Athletes) a
ORDER BY e.CodePays, a.Nom, a.Prenom;
-- 4
SELECT e.NumEq, c.NumCtrl, c.DateCtrl
FROM Equipe e, TABLE(e.Athletes) a, TABLE(a.Controles) c;
Bases de données Avancées – Activité 3.2
2
-- 5
SELECT e.NumEq,
CURSOR(SELECT
CURSOR(SELECT c.NumCtrl, c.DateCtrl FROM TABLE(a.Controles) c)
FROM TABLE(e.Athletes) a)
FROM Equipe e;
-- 6
SELECT a.Nom, a.Prenom
FROM TABLE (SELECT Athletes FROM Equipe WHERE NumEq = 2) a;
-- 7
SELECT *
FROM TABLE (SELECT Controles
Advertisement
FROM TABLE (SELECT Athletes FROM Equipe WHERE NumEq = 3)
WHERE NumDossard = 4);
-- 8
SELECT COUNT(*)
FROM TABLE (SELECT Athletes FROM Equipe WHERE NumEq = 6);
-- 9
SELECT CodePays, COUNT(*)
FROM Equipe e, TABLE (e.Athletes)
GROUP BY CodePays;
-- 10
SELECT a.Nom, a.Prenom, COUNT(*)
FROM Equipe e, TABLE(e.Athletes) a, TABLE(a.Controles)
GROUP BY a.Nom, a.Prenom
ORDER BY a.Nom, a.Prenom;
-- 11
SELECT a.Nom, a.Prenom, COUNT(*)
FROM Equipe e, TABLE(e.Athletes) a, TABLE(a.Controles) c
WHERE c.Resultat = 'P'
GROUP BY a.Nom, a.Prenom
ORDER BY a.Nom, a.Prenom;
-- 12
SELECT e.CodePays, COUNT(*)
FROM Equipe e, TABLE(e.Athletes) a, TABLE(a.Controles) c
WHERE c.Resultat = 'P'
GROUP BY e.CodePays
ORDER BY e.CodePays;
-- 13
SELECT a.Nom, a.Prenom, MAX(c.DateCtrl)
FROM Equipe e, TABLE(e.Athletes) a, TABLE(a.Controles) c
GROUP BY a.Nom, a.Prenom
ORDER BY a.Nom, a.Prenom;
Bases de données Avancées – Activité 3.2
3
-- 14
SELECT e.NumEq, e.CodePays, e.Sport
FROM Equipe e, TABLE(e.Athletes) a, TABLE(a.Controles)
Advertisement
GROUP BY e.NumEq, e.CodePays, e.Sport
HAVING COUNT(*) =
(SELECT MAX(COUNT(*))
FROM Equipe e1, TABLE(e1.Athletes) a1, TABLE(a1.Controles)
GROUP BY e1.NumEq);
-- 15
SELECT NumEq, CodePays, Sport
FROM Equipe e1
WHERE NOT EXISTS(
SELECT *
FROM Equipe e2, TABLE(e2.Athletes) a, TABLE(a.Controles) c
WHERE e1.NumEq = e2.NumEq
AND c.Resultat = 'P');
-- Mises à jour
-- 1
INSERT INTO TABLE (SELECT Athletes FROM Equipe WHERE NumEq = 6)
VALUES(30, 'Pietrus', 'Michael', TAB_Controle());
-- 2
INSERT INTO TABLE ( SELECT a.Controles FROM Equipe, TABLE(Athletes) a
WHERE NumEq = 6 AND NumDossard = 30)
VALUES(15, 'Urinaire', '23/08/2019', 'N');
INSERT INTO TABLE ( SELECT a.Controles FROM Equipe, TABLE(Athletes) a
WHERE NumEq = 6 AND NumDossard = 30)
VALUES(16, 'Sanguin', '23/08/2019', 'N');
-- 3
DELETE FROM TABLE ( SELECT a.Controles FROM Equipe, TABLE(Athletes) a
WHERE NumEq = 3 AND NumDossard = 4);
-- 4
UPDATE TABLE ( SELECT a.Controles FROM Equipe, TABLE(Athletes) a
WHERE NumEq = 4 AND NumDossard = 6)
SET Resultat = 'N'
WHERE DateCtrl < '04/10/2019';
Bases de données Avancées – Activité 3.2
4