Tweekers : Plateforme de conception et d'architecture logicielle collaborative

Connexion | Inscription

Solution

Exemples de requètes SQL

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

SELECT * FROM abonne ORDER BY ville,nom

- 2 - Liste des livres de langue francaise

SELECT * from livre WHERE langue = "FR"

- 3 - Les livres de Voltaire

SELECT titre FROM livre,auteur WHERE auteur.numA = livre.numA AND nom = "Voltaire"

- 4 - Les exemplaires de Voltaire

SELECT exemplaire.numE,exemplaire.numL,etat 
FROM livre,auteur,exemplaire 
WHERE exemplaire.numL = livre.numL 
AND livre.numA = auteur.numA 
AND auteur.nom = "Voltaire"

- 5 - Les auteurs dont la bibliothèque possède des exemplaires de livre

SELECT DISTINCT auteur.nom 
FROM livre,auteur,exemplaire 
WHERE exemplaire.numL = livre.numL 
AND auteur.numA = livre.numA

- 6 - Les auteurs dont la bibliothèque ne possède pas d'exemplaire

SELECT auteur.nom 
FROM auteur 
WHERE auteur.nom NOT IN (
	SELECT DISTINCT auteur.nom  
	FROM livre,exemplaire 
	WHERE exemplaire.numL = livre.numL )

- 7 - L' auteur qui a écrit le plus de livres

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 )

- 8 - L'age moyen des abonnés

SELECT AVG(age) FROM abonne

- 9 - L'abonné le plus vieux

SELECT nom, prenom 
FROM abonne 
WHERE age = ( SELECT MAX(age) 
		FROM abonne )

- 10 - Les abonnés de l'Hérault qui ont emprunté des livres de langue anglaise

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___'

- 11 - Les abonnés qui ont des prêts en cours

SELECT nom, prenom
FROM  abonne, pret
WHERE dateretour IS NULL
AND abonne.numAb = pret.numAb

- 12 - Les abonnés qui ont emprunté des livres de langue française ou anglaise

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" )

- 13 - Les abonnés qui ont emprunté des livres de langue française et anglaise

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")

- 14 - Les abonnés qui n'ont emprunté que des livres en langue anglaise

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")

- 15 - Le nombre d'exemplaire par auteur

SELECT COUNT(*), auteur.nom
FROM exemplaire,livre,auteur
WHERE exemplaire.numL = livre.numL
AND livre.numA = auteur.numA
GROUP BY auteur.numA, auteur.nom

- 16 - Le ou les emprunts les plus récents

SELECT * 
FROM pret
WHERE date = (SELECT MAX(date)
		FROM pret )

- 17 - Les abonnés ayant emprunté plus de 10 livres de Voltaire

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

- 18 - Les abonnés ayant emprunté plus de 10 fois Voltaire

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

- 19 - Les abonnés ayant emprunté le plus de livres de Voltaire

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 )

- 20 - Les abonnés dont les prêts en cours ont plus de 15 jours

SELECT DISTINCT abonne.nom, abonne.prenom
FROM abonne,pret
WHERE abonne.numAb = pret.numAb
AND pret.dateretour IS NULL
AND DATESYSTEM - pret.date >= 15

- 21 - Le % de livre emprunté pour chaque auteur

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