Solutions des exercices de TD 4: Le langage SQL
Exercice 1 :
Q1 : CREATE TABLE Auteur
(
numAut INTEGER PRIMARY KEY,
nomAut VARCHAR(30),
email VARCHAR(50)
);
Q2 : CREATE TABLE Ouvrage
(
codeOuv INTEGER PRIMARY KEY,
titre VARCHAR(30),
genre VARCHAR(20),
numAut INTEGER REFERENCES Auteur,
éditeur VARCHAR(30)
);
Q3 : CREATE TABLE Prêt
(
numAbo INTEGER REFERENCES Abonné,
codeOuv INTEGER REFERENCES Ouvrage,
numExemp INTEGER,
datePrêt DATE,
CONSTRAINT c1 PRIMARY KEY(codeOuv, numExemp, numAbo,datePrêt),
CONSTRAINT c2 FOREIGN KEY(codeOuv, numExemp) REFERENCES
Exemp_Ouvrage
);
Q4 : SELECT *
FROM Ouvrage ;
Q5 : SELECT DISTINCT genre
FROM Ouvrage ;
1
Q6 : SELECT titre
FROM Ouvrage
WHERE genre = ’Roman’
AND éditeur = ’Eyrolles’;
Q7 : SELECT codeOuv, COUNT(numExemp)
FROM Exemp_Ouvrage
WHERE état = 'D'
GROUP BY codeOuv;
Q8 : SELECT [Link], nomAbo, COUNT(*)
FROM Abonné A, Prêt P
WHERE [Link] = [Link]
GROUP BY [Link], nomAbo;
Q9 : SELECT *
FROM Auteur
WHERE email IS NULL;
Q10 : SELECT * FROM Abonné
WHERE nomAbo LIKE ’%Abd%’;
Q11 : SELECT [Link], titre
FROM Ouvrage O, Exemp_Ouvrage E
WHERE [Link] = [Link]
AND dateAchat = (SELECT MAX(dateAchat)
FROM Exemp_Ouvrage);
Q12 : SELECT COUNT(*)
FROM Exemp_Ouvrage
WHERE dateAchat LIKE ’%15’;
Q13 : SELECT titre
FROM Abonné A, Prêt P, Ouvrage O
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] = 'Mejri Mohamed';
Q14 : SELECT nomAut
FROM Auteur A, Ouvrage O
WHERE [Link] = [Link]
AND [Link] = 'Policier'
2
AND NOT EXISTS (SELECT numAut
FROM Ouvrage O2
WHERE [Link] = [Link]
AND [Link] <> 'Policier');
Exercice 2 :
Q1 : SELECT numCoureur, nomCoureur, nomEquipe
FROM Coureur C, Equipe E, Pays P
WHERE [Link] = [Link]
AND [Link] = [Link]
AND nomPays = ’Tunisie’;
Q2 : SELECT SUM(nbKm)
FROM Etape;
Q3 : SELECT SUM(nbKm)
FROM Etape E, TypeEtape T
WHERE [Link] = [Link]
AND libelléType = ’Haute Montagne’;
Q4 : SELECT [Link], nomCoureur, COUNT(*) AS nbEtpaes
FROM Coureur C, Participer P
WHERE [Link] = [Link]
GROUP BY [Link], nomCoureur
HAVING COUNT(*) = (SELECT COUNT(*) FROM Etape);
Q5 : SELECT [Link], nomCoureur, [Link], codePays,
SUM(tempsRéalisé) AS TempsTotal
FROM Coureur C, Equipe E, Participer P
WHERE [Link] = [Link]
AND [Link] = [Link]
AND numEtape <= 13
GROUP BY [Link], nomCoureur, [Link], codePays
ORDER BY TempsTotal;
3
Q6 : SELECT [Link], nomEquipe, SUM(tempsRéalisé) AS
TempsTotal
FROM Coureur C, Equipe E, Participer P
WHERE [Link] = [Link]
AND [Link] = [Link]
AND NumEtape <= 13
GROUP BY [Link], nomEquipe
ORDER BY TempsTotal;
Exercice 3 :
1. Il s’agit de retourner les numéros de fournisseurs de produits rouges qui sont
présents dans l’ensemble des numéros de fournisseurs de produits verts :
SELECT numF
FROM Produit, Catalogue
WHERE [Link] = [Link]
AND couleur = ’rouge’
AND [Link] IN (SELECT [Link]
FROM Produit P1, Catalogue C1
WHERE [Link] = [Link]
AND [Link] = ’vert’);
Une autre manière de procéder consiste à faire l’intersection de l’ensemble des
numéros de fournisseurs de produits rouges avec l’ensemble des numéros de
fournisseurs de produits verts :
(SELECT [Link]
FROM Produit P, Catalogue C
WHERE [Link] = [Link]
AND [Link] = ’rouge’)
INTERSECT
(SELECT [Link]
FROM Produit P1, Catalogue C1
WHERE [Link] = [Link]
AND [Link] = ’vert’);
4
2. Il s’agit de sélectionner les numéros de fournisseurs (numF) pour lesquels le
nombre de produits offerts (d’après le catalogue) est égal au nombre total de
produits :
SELECT numF, COUNT(*)
FROM Catalogue
GROUP BY numF
HAVING COUNT(*) = (SELECT COUNT(*) FROM Produit);
3. Une manière de procéder consiste à compter le nombre de fournisseurs pour
chaque produit. Si ce nombre est supérieur à un, alors on affiche le produit.
SELECT DISTINCT [Link]
FROM Catalogue C1
WHERE (SELECT COUNT(DISTINCT [Link])
FROM Catalogue C2
WHERE [Link] = [Link]) > 1;
Voici une deuxième façon d’exprimer cette requête :
SELECT numP, COUNT(numF)
FROM Catalogue
GROUP By nump
Having COUNT(numF)> 1;
4. L’idée consiste à tester pour chaque produit proposé par « Ahmed », si son
prix est égal au prix maximum des produits qu’il fournit.
SELECT numP
FROM Catalogue C, Fournisseur F
WHERE [Link] = [Link]
AND nomF = 'Ahmed'
AND prix = (SELECT MAX(prix)
FROM Catalogue C1
WHERE [Link] = [Link]);