Exercice Langage SQL : Une Médiathèque (Suite...)
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 ?
| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647 | -- 15) Quel est le salaire annuel des membres du personnel gagnant plus de 20000 euros ? SELECT Nom, Prénom, Salaire*12 AS Salaire_AnnuelFROM 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 LWHERE A.CodeOuv=L.CodeOuvANDGenre"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 PWHERE 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 |