Exercice Langage SQL : Une Médiathèque (Suite...)

Rédigé par GC Team, Publié le 14 Décembre 2011, Mise à jour le Mardi, 30 Novembre 1999 00:00
Participez au vote ☆☆☆☆☆★★★★★

Suite de l'exercice une Médiathèque 

Enoncé de l'Exercice

On considère le schéma relationnel suivant qui modélise une application sur la gestion de livres et de disques dans une Médiathèque :

                      Disque (CodeOuv, Titre, Style, Pays, Année, Producteur)

                      E_Disque (CodeOuv, NumEx, DateAchat, Etat)

                      Livre (CodeOuv, Titre, Editeur, Collection)

                      E_Livre (CodeOuv, NumEx, DateAchat, Etat)

                      Auteurs (CodeOuv, Identité)

                      Abonne (NumAbo, Nom, Prénom, Rue, Ville, CodeP, Téléphone)

                      Prêt (CodeOuv, NumEx, DisqueOuLivre, NumAbo, DatePret)

                      Personnel (NumEmp, Nom, Prénom, Adresse, Fonction, Salaire)

Travail à Faire :

Traduisez en SQL les questions suivantes :

                  15) Quel est le salaire annuel des membres du personnel gagnant plus de 20000 euros ?

                  16) Quel est le nom, prénom et l’adresse des abonnés ayant emprunté un disque le ’12/01/2006’ ?

                  17) Quels sont les titres des livres et des disques actuellement empruntés par Frédéric Gava ?

                  18) Quels sont les titres des ouvrages livres policiers ou disques de Jazz empruntés par Frédéric Gava ?

                  19) Quel est l’identité des auteurs qui n’ont écrit que des romans policiers (genre=policier) ?

                  20) Quel sont les codes des ouvrages des livres pour lesquels il y a au moins un exemplaire emprunté et au moins un exemplaire disponible ?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

-- 15) Quel est le salaire annuel des membres du personnel gagnant plus de 20000 euros ?
 
SELECT Nom, Prénom, Salaire*12 AS Salaire_Annuel
FROM Personnel WHERE Salaire_Annuel>20000
 
-- 16) Quel est le nom, prénom et l’adresse des abonnés ayant emprunté un disque le ’12/01/2006’ ?
 
SELECT Nom, Prénom, Rue, Ville, CodeP 
FROM Abonne A, Prêt P, Disque D 
WHERE A.NumAbo=P.NumAbo 
AND P.CodeOuv=D.CodeOuv AND DatePret=’12-Jan-2006’
 
-- 17) Quels sont les titres des livres et des disques actuellement empruntés par Frédéric Gava ?
 
(SELECT Titre FROM Abonne A, Prêt P, Disque D 
WHERE A.NumAbo=P.NumAbo AND P.CodeOuv=D.CodeOuv 
AND NOM="Gava" AND Prénom="Frédéric") 
UNION 
(SELECT Titre FROM Abonne A, Prêt P, Livre L 
WHERE A.NumAbo=P.NumAbo AND P.CodeOuv=L.CodeOuv 
AND NOM="Gava" AND Prénom="Frédéric")
 
-- 18) Quels sont les titres des ouvrages livres policiers ou disques de Jazz empruntés par Frédéric Gava ?
 
SELECT CodeOuv FROM Prêt P, Abonne A 
WHERE P.NumAbo=A.NumAboANDPrénom="Frédéric"
AND Nom="Gava" AND CodeOuv IN 
(SELECT CodeOuv FROM Livre WHERE Genre="Policier") 
OR 
CodeOuv IN (SELECT CodeOuv FROM Disque WHERE Style="Jazz")
 
-- 19) Quel est l’identité des auteurs qui n’ont écrit que des romans policiers (genre=policier) ?
 
SELECT Identité FROM Auteur A, Livre L 
WHERE A.CodeOuv=L.CodeOuv AND Genre="Policier" 
AND NOT ALL(SELECT Identité FROM Auteur A, Livre L
WHERE A.CodeOuv=L.CodeOuvAND
Genre"Policier")
 
-- 20) Quel sont les codes des ouvrages des livres pour lesquels il y a au moins un exemplaire emprunté et au moins un exemplaire disponible ?
 
(SELECT P.CodeOuv FROM E_Livre E, Prêt P
WHERE E.CodeOuv=P.CodeOuv)INTERSECT
(SELECT CodeOuv FROM E_Livre E 
WHERE NOT EXISTS(SELECT * FROM Prêt P 
WHERE E.CodeOuv=P.CodeOuv 
AND E.NumEx=P.NumEx