6 Exo
Soient les relations
vente(numvoit, numpers, prix) voiture(numvoit, marque)
• Calculez la moyenne des prix de vente par marque en ne considérant que les marques dont
cette moyenne est supérieure `a 40000.
• Affichez les prix qui sont supérieurs `a la moyenne.
SELECT [Link], AVG([Link]) AS moyenne_prix
FROM voiture v
JOIN vente ve ON [Link] = [Link]
GROUP BY [Link]
HAVING AVG([Link]) > 40000;
SELECT [Link], [Link], [Link]
FROM vente ve
WHERE [Link] > (SELECT AVG(prix) FROM vente);
Explication
Requête 1 : On joint les tables venteet voitureen utilisant numvoitcomme clé de
jointure. Ensuite, on regroupe les résultats par marque et on utilise HAVINGpour
filtrer les moyennes supérieures à 40000.
Requête 2 : On récupèrevente
7 Exo
soient les relations suivantes : etudiant(nummat, nom, prenom, codfiliere, libfiliere, effectif) avoir-note(nummat,
codmat, date, libmat, coefficient, codfil, note)
professeur(nom, prenom, adresse) enseigner(numprof, nom, codmat, libmat, codfiliere)
Mettez les relation en troisi`eme forme sachant que :
• le coefficient d’une mati`ere d´dépend de la filière dans laquelle elle est ´étudiée
• une matières est enseignée par un et seul professeur dans la même filière.
8 Exo
Soient les relations suivantes :
Emp(numE, nomE, fonction, numS, embauche, salaire, comm, numD) Dept(numD, nomD, Lieu)
1. Donnez la liste des employ´es ayant une commission, class´e par commission d´ecroissante.
2. Donnez les noms des personnes embauch´ees depuis le 01-09-2006.
3. Donnez la liste des employ´es travaillant `a Cr´eteil.
4. Donnez la moyenne des salaires
5. Donnez le nombre de commission non NULL
6. Donnez la liste des employ´ees gagnant plus que la moyenne des salaires de l’entreprise.
9 Exo
disque(codeOuv, titre, style, pays, annee, producteur) exempl disk(codeOuv, numEx, dateachat, etat)
livre(codeOuv, titre, editeur, collection) exempl liv(codeOuv, numEx, dateachat, etat) auteur(codeOuv, identit´e)
abonne(numabo, nom, rue, ville, codep, tel)
pret(codeOuv, numEx, diskoulivr, numabo, datepret) personnel(numemp, nom, adresse, fonction, salaire)
1. Quel est le contenu de la relation livre ?
2. Quels sont les titres des romans ´edit´es par Gava-editor ?
3. Quelle est la liste des titre que l’on retrouve `a la fois comme titre de disque et titre de livre
4. Quelle est l’identit´e des auteurs qui ont fait des disque et ´ecrit des livres ?
5. Quels sont les diff´erents style de disques propos´es ?
6. Quel est le salaure annuel des membres du personnel gagnant plus de 20000 francs en
ordonnant le r´esultat par salaire d´ecroissant ?
7. Donnez le nombre de prets en cours pour chaque famille en consid´erant qu’une famille
regroupe des personnes de mˆeme nom et poss´edant le mˆeme num´ero de t´el´ephone ?
8. Quel est le code du disque dont la m´ediath`eque poss`ede le plus grand nombre d’exemplaire
9. Quels sont les ´editeurs pour lesquels l’attribut collection n’a pas ´et´e renseign´e ?
10. Quels sont les abonn´es d ont le nom contient la chaˆıne ”toto” et habitant en Is`ere ?
11. Quel est le nombre de genres de livres diff´erents ?
12. Quel est le nombre de disque achet´e en 1998 ?
13. Quel est le nom des abonn´es ayant emprunt´e un disque le 12/01/2006 ?
14. Quels sont les titres des livres et des disques actuellement emprunt´es par Franck ?
15. Quels sont les codes des ouvrages des livres pour lesquels il y a au moins un exemplaire
emprunt´e et au moins un exemplaire disponible ?
Voici les réponses aux exercices en utilisant SQL :
---
### Exercice 7
#### Mise en troisième forme normale des relations
Pour mettre les relations en 3NF, voici les ajustements basés sur les règles fournies
:
1. **Pour la relation `avoir-note` :**
- Puisque le coefficient d'une matière dépend de la filière, on peut séparer `avoir-
note` en deux tables :
- Une table `matiere` qui stocke `codmat`, `libmat`, `codfil`, et `coefficient`.
- La table `avoir-note` conserve `nummat`, `codmat`, `date`, et `note`.
```sql
-- Nouvelle table `matiere`
matiere(codmat, libmat, codfil, coefficient)
-- Table modifiée `avoir-note`
avoir_note(nummat, codmat, date, note)
```
2. **Pour la relation `enseigner` :**
- Puisque chaque matière est enseignée par un seul professeur dans la même
filière, on supprime `nom` de `enseigner`, car il est déjà dans `professeur`.
- On crée une clé primaire composite (codmat, codfiliere) pour représenter
l'association unique entre le professeur, la matière et la filière.
```sql
-- Table modifiée `enseigner`
enseigner(numprof, codmat, codfiliere)
```
---
### Exercice 8
#### 1. Liste des employés ayant une commission, classée par commission
décroissante
```sql
SELECT *
FROM Emp
WHERE comm IS NOT NULL
ORDER BY comm DESC;
```
#### 2. Noms des personnes embauchées depuis le 01-09-2006
```sql
SELECT nomE
FROM Emp
WHERE embauche >= '2006-09-01';
```
#### 3. Liste des employés travaillant à Créteil
```sql
SELECT E.*
FROM Emp E
JOIN Dept D ON [Link] = [Link]
WHERE [Link] = 'Créteil';
```
#### 4. Moyenne des salaires
```sql
SELECT AVG(salaire) AS moyenne_salaire
FROM Emp;
```
#### 5. Nombre de commissions non NULL
```sql
SELECT COUNT(comm) AS nombre_commissions
FROM Emp
WHERE comm IS NOT NULL;
```
#### 6. Liste des employés gagnant plus que la moyenne des salaires
```sql
SELECT *
FROM Emp
WHERE salaire > (SELECT AVG(salaire) FROM Emp);
```
---
### Exercice 9
#### 1. Contenu de la relation `livre`
```sql
SELECT *
FROM livre;
```
#### 2. Titres des romans édités par "Gava-editor"
```sql
SELECT titre
FROM livre
WHERE editeur = 'Gava-editor';
```
#### 3. Liste des titres communs entre disques et livres
```sql
SELECT [Link]
FROM disque
JOIN livre ON [Link] = [Link];
```
#### 4. Identité des auteurs ayant fait des disques et écrit des livres
```sql
SELECT [Link]
FROM auteur
JOIN disque ON [Link] = [Link]
JOIN livre ON [Link] = [Link];
```
#### 5. Différents styles de disques proposés
```sql
SELECT DISTINCT style
FROM disque;
```
#### 6. Salaire annuel des membres du personnel gagnant plus de 20000 francs,
par salaire décroissant
```sql
SELECT nom, (salaire * 12) AS salaire_annuel
FROM personnel
WHERE salaire > 20000
ORDER BY salaire_annuel DESC;
```
#### 7. Nombre de prêts en cours pour chaque famille (même nom et numéro de
téléphone)
```sql
SELECT nom, tel, COUNT(*) AS nb_prets
FROM abonne
JOIN 2pret ON [Link] = [Link]
GROUP BY nom, tel;
```
#### 8. Code du disque avec le plus grand nombre d’exemplaires
```sql
SELECT codeOuv
FROM exempl_disk
GROUP BY codeOuv
ORDER BY COUNT(numEx) DESC
LIMIT 1;
```
#### 9. Éditeurs pour lesquels l’attribut `collection` n’est pas renseigné
```sql
SELECT DISTINCT editeur
FROM livre
WHERE collection IS NULL;
```
#### 10. Abonnés dont le nom contient "toto" et habitant en Isère
```sql
SELECT *
FROM abonne
WHERE nom LIKE '%toto%' AND codep LIKE '38%';
```
#### 11. Nombre de genres de livres différents
```sql
SELECT COUNT(DISTINCT collection) AS nb_genres
FROM livre;
```
#### 12. Nombre de disques achetés en 1998
```sql
SELECT COUNT(*) AS nb_disques
FROM exempl_disk
WHERE YEAR(dateachat) = 1998;
```
#### 13. Nom des abonnés ayant emprunté un disque le 12/01/2006
```sql
SELECT [Link]
FROM abonne
JOIN 2pret ON [Link] = [Link]
WHERE [Link] = 'disque' AND [Link] = '2006-01-12';
```
#### 14. Titres des livres et disques empruntés par "Franck"
```sql
SELECT [Link]
FROM 2pret
JOIN livre ON [Link] = [Link]
JOIN abonne ON [Link] = [Link]
WHERE [Link] = 'Franck'
UNION
SELECT [Link]
FROM 2pret
JOIN disque ON [Link] = [Link]
JOIN abonne ON [Link] = [Link]
WHERE [Link] = 'Franck';
```
#### 15. Codes des livres avec au moins un exemplaire emprunté et un exemplaire
disponible
```sql
SELECT [Link]
FROM livre l
JOIN exempl_liv e1 ON [Link] = [Link] AND [Link] = 'emprunté'
JOIN exempl_liv e2 ON [Link] = [Link] AND [Link] = 'disponible'
GROUP BY [Link];
```
Ces requêtes devraient fournir les informations demandées pour chaque exercice.
