Tweekers : Plateforme de conception et d'architecture logicielle collaborative

Connexion | Inscription

Edition :Exemples de requètes SQL

Modifier l'intitulé' :

Soit la base de donnée suivante :Livre (numL,titre,numA,langue) Auteur (numA,nom) Exemplaire(numE,numL,etat) Abonne(numAB,nom,prenom,age,adresse,cp,ville) Pret(numE,date,numAB,dateretour)On écrira les requètes suivantes pour : - 1 - Abonnés triés par ville et par nom <pre>SELECT * FROM abonne ORDER BY ville,nom</pre>- 2 - Liste des livres de langue francaise<pre>SELECT * from livre WHERE langue = "FR"</pre>- 3 - Les livres de Voltaire<pre>SELECT titre FROM livre,auteur WHERE auteur.numA = livre.numA AND nom = "Voltaire"</pre>- 4 - Les exemplaires de Voltaire<pre>SELECT exemplaire.numE,exemplaire.numL,etat FROM livre,auteur,exemplaire WHERE exemplaire.numL = livre.numL AND livre.numA = auteur.numA AND auteur.nom = "Voltaire"</pre>- 5 - Les auteurs dont la bibliothèque possède des exemplaires de livre<pre>SELECT DISTINCT auteur.nom FROM livre,auteur,exemplaire WHERE exemplaire.numL = livre.numL AND auteur.numA = livre.numA </pre>- 6 - Les auteurs dont la bibliothèque ne possède pas d'exemplaire<pre>SELECT auteur.nom FROM auteur WHERE auteur.nom NOT IN ( 	SELECT DISTINCT auteur.nom 	FROM livre,exemplaire 	WHERE exemplaire.numL = livre.numL )</pre>- 7 - L' auteur qui a écrit le plus de livres<pre>SELECT A.nom FROM auteur A, livre L WHERE A.numA = L.numA GROUP BY A.numA,A.nom HAVING COUNT(*) >= ALL (SELECT COUNT( * ) FROM livre GROUP BY numA )</pre>- 8 - L'age moyen des abonnés<pre>SELECT AVG(age) FROM abonne </pre>- 9 - L'abonné le plus vieux<pre>SELECT nom, prenom FROM abonne WHERE age = ( SELECT MAX(age) 		FROM abonne ) </pre>- 10 - Les abonnés de l'Hérault qui ont emprunté des livres de langue anglaise<pre>SELECT DISTINCT nom, prenom FROM abonne, pret, exemplaire, livre WHERE abonne.numAb = pret.numAb AND pret.numE = exemplaire.numE AND exemplaire.numL = livre.numL AND livre.langue = 'EN' AND abonne.cp LIKE '34___' </pre>- 11 - Les abonnés qui ont des prêts en cours<pre>SELECT nom, prenom FROM abonne, pret WHERE dateretour IS NULL AND abonne.numAb = pret.numAb </pre>- 12 - Les abonnés qui ont emprunté des livres de langue française ou anglaise<pre>SELECT DISTINCT nom, prenom FROM pret, abonne, exemple, livre WHERE abonne.numAb = pret.numAb AND pret.numE = exemplaire.numE AND exemplaire.numL = livre.numL AND ( livre.langue = "EN" OR livre.langue = "FR" ) </pre> - 13 - Les abonnés qui ont emprunté des livres de langue française et anglaise <pre>SELECT nom, prenom FROM pret, abonne, exemple, livre WHERE abonne.numAb = pret.numAb AND pret.numE = exemplaire.numE AND exemplaire.numL = livre.numL AND livre.langue = "EN" AND abonne.numAb IN (SELECT numAb 		 FROM pret, livre, exemplaire 		 WHERE pret.numE = exemplaire.numE 		 AND exemplaire.numL = livre.numL 		 AND livre.langue = "FR") </pre> - 14 - Les abonnés qui n'ont emprunté que des livres en langue anglaise <pre>SELECT nom, prenom FROM pret, abonne, exemple, livre WHERE abonne.numAb = pret.numAb AND exemplaire.numL = livre.numL AND pret.numE = exemplaire.numE AND livre.langue = "EN" AND abonne.numAb NOT IN (SELECT numAb 			FROM pret,livre,exemplaire 			WHERE pret.numE = exemplaire.numE 			AND exemplaire.numL = livre.numL 			AND livre.langue <> "EN") </pre> - 15 - Le nombre d'exemplaire par auteur <pre>SELECT COUNT(*), auteur.nom FROM exemplaire,livre,auteur WHERE exemplaire.numL = livre.numL AND livre.numA = auteur.numA GROUP BY auteur.numA, auteur.nom</pre> - 16 - Le ou les emprunts les plus récents <pre>SELECT * FROM pret WHERE date = (SELECT MAX(date) 		FROM pret )  </pre> - 17 - Les abonnés ayant emprunté plus de 10 livres de Voltaire <pre>SELECT abonne.numAb, abonne.nom, abonne.prenom FROM abonne,pret,auteur,livre,exemplaire WHERE abonne.numAb = pret.numAb AND livre.numL = exemplaire.numL AND auteur.numA = livre.numA AND pret.numE = exemplaire.numE AND auteur.nom = "Voltaire" GROUP BY abonne.numAb,abonne.nom,abonne.prenom HAVING COUNT(DISTINCT titre) > 10 </pre> - 18 - Les abonnés ayant emprunté plus de 10 fois Voltaire <pre>SELECT abonne.numAb, abonne.nom, abonne.prenom FROM abonne,pret,auteur,livre,exemplaire WHERE abonne.numAb = pret.numAb AND livre.numL = exemplaire.numL AND auteur.numA = livre.numA AND pret.numE = exemplaire.numE AND auteur.nom = "Voltaire" GROUP BY abonne.numAb,abonne.nom,abonne.prenom HAVING COUNT(*) > 10</pre> - 19 - Les abonnés ayant emprunté le plus de livres de Voltaire <pre>SELECT abonne.nom,abonne.prenom FROM abonne,pret,livre,exemplaire,auteur WHERE abonne.numAb = pret.numAb AND pret.numE = exemplaire.numE AND exemplaire.numL = livre.numL AND livre.numA = auteur.numA AND auteur.nom = "Voltaire" GROUP BY abonne.numAb,abonne.nom, abonne.prenom HAVING COUNT(DISTINCT livre.titre) >= ALL (SELECT COUNT(DISTINCT titre) FROM livre,pret,exemplaire,auteur WHERE livre.numL = exemplaire.numL AND exemplaire.numE = pret.numE AND livre.numA = auteur.numA AND auteur.nom = "Voltaire" GROUP BY pret.numAB )</pre> - 20 - Les abonnés dont les prêts en cours ont plus de 15 jours<pre>SELECT DISTINCT abonne.nom, abonne.prenom FROM abonne,pret WHERE abonne.numAb = pret.numAb AND pret.dateretour IS NULL AND DATESYSTEM - pret.date >= 15</pre>- 21 - Le % de livre emprunté pour chaque auteur <pre>SELECT auteur.nom, COUNT(DISTINCT livre.numL) / COUNT(DISTINCT L2.numL) FROM auteur,livre,exemplaire,pret,livre L2 WHERE auteur.numA = livre.numA AND livre.numL = exemplaire.numL AND exemplaire.numE = pret.numE AND L2.numA = auteur.numA GROUP BY auteur.numA, auteur.nom </pre>

Type :