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 |