News Ticker

Menu

Exercices SQL

By X-Proof - -


Exercice 1:
On suppose qu'une bibliothèque gère une base de données dont le schéma est le suivant (les clés primaires des relations sont soulignées) 
 Emprunt (Personne, Livre, DateEmprunt, DateRetourPrevue, DateRetourEffective)
 Retard (Personne, Livre, DateEmprunt, PenalitéRetard)
  1. Quelles sont les personnes ayant emprunté le livre "Recueil Examens BD" ?
  2. Quelles sont les personnes n'ayant jamais rendu de livre en retard ?
  3. Quelles sont les personnes ayant emprunté tous les livres (empruntés au moins une fois) ?
  4. Quels sont les livres ayant été empruntés par tout le monde (i.e. tous les emprunteurs) ?
  5. Quelles sont les personnes ayant toujours rendu en retard les livres qu'elles ont empruntés ?

Exercice 2:
Soit le modèle relationnel suivant relatif à la gestion simplifiée des étapes du Tour de France 97, dont une des étapes de type "contre la montre individuel" se déroula à Saint-Etienne :
EQUIPE(CodeEquipe, NomEquipe, DirecteurSportif)
COUREUR(NuméroCoureur, NomCoureur, CodeEquipe*, CodePays*)
PAYS(CodePays, NomPays)
TYPE_ETAPE(CodeType, LibelléType)
ETAPE(NuméroEtape, DateEtape, VilleDép, VilleArr, NbKm, CodeType*)
PARTICIPER(NuméroCoureur*, NuméroEtape*, TempsRéalisé)
ATTRIBUER_BONIFICATION(NuméroEtape*, km, Rang, NbSecondes, NuméroCoureur*)
Remarque : les clés primaires sont soulignées et les clés étrangères sont marquées par *
  1.  Quelle est la composition de l'équipe Festina (Numéro, nom et pays des coureurs) ?
  2.  Quel est le nombre de kilomètres total du Tour de France 97 ?
  3.  Quel est le nombre de kilomètres total des étapes de type "Haute Montagne"?
  4.  Quels sont les noms des coureurs qui n'ont pas obtenu de bonifications ?
  5.  Quels sont les noms des coureurs qui ont participé à toutes les étapes ?
  6.  Quel est le classement général des coureurs (nom, code équipe, code pays et temps des coureurs) à l'issue des 13 premières étapes sachant que les bonifications ont été intégrées dans les temps réalisés à chaque étape ?
  7.  Quel est le classement par équipe à l'issue des 13 premières étapes (nom et temps des équipes) ?

Exercice 3:
Soit le schéma de base de données relationnel suivant :
                     AGENCE (Num_Agence, Nom, Ville, Actif)
                     CLIENT (Num_Client, Nom, Ville)
                     COMPTE (Num_Compte, Num_Agence, Num_Client, Solde
                    EMPRUNT (Num_Emprunt, Num_Agence, Num_Client, Montant)
Ecrire les requêtes suivantes en SQL :
  • 1.        Liste des agences ayant des comptes-clients
  • 2.        Clients ayant un compte à “Agadir”
  • 3.        Clients ayant un compte ou un emprunt à “Agadir”
  • 4.        Clients ayant un compte et un emprunt à “Agadir”
  • 5.        Clients ayant un compte et pas d’emprunt à “Agadir”
  • 6.        Clients ayant un compte et nom de la ville où ils habitent
  • 7.        Clients ayant un compte à “Paris-Etoile” et nom de la ville où ils habitent
  • 8.        Clients ayant un compte dans une agence où “Ahmed” a un compte
  • 9.        Agences ayant un actif plus élevé que toute agence d'“AAAA”
  • 10.     Clients ayant un compte dans chaque agence d'“ AAAA ”
  • 11.     Clients ayant un compte dans au-moins une agence d'“AAAA”
  • 12.     Emprunteurs de l'agence “Grédit-Agadir” classés par ordre alphabétique
  • 13.     Solde moyen des comptes-clients de chaque agence
  • 14.     Solde moyen des comptes-clients des agences dont le solde moyen est > “10  000”
  • 15.     Nombre de clients habitant “Safi”
  • 16.     Nombre de clients de l'agence “ CETELEM ” n'ayant pas leur adresse dans la relation CLIENT
  • 17.     Insérer le n-uplet dans la relation CLIENT
  • 18.     Diminuer l'emprunt de tous les clients habitant “Tanger” de “5%”
  • 19.     Fermer les comptes de “Ahmed”
  • 20.     Supprimer de la relation AGENCE toutes les agences sans client

Correction

Exercice 1
1- Quelles sont les personnes ayant emprunté le livre "Recueil Examens BD" ?
SELECT Personne FROM Emprunt
 WHERE Livre = 'Recueil...
2- Quelles sont les personnes n'ayant jamais rendu de livre en retard ?
SELECT t.Personne FROM Emprunt t
WHERE NOT EXISTS
SELECT * FROM Retard u WHERE u.Personne=t.Personne)

SELECT Personne FROM Emprunt
WHERE Personne NOT IN
(SELECT Personne FROM Retard)

3- Quelles sont les personnes ayant emprunté tous les livres (empruntés au moinsune fois) ?
SELECT t.Personne
  FROM Emprunt t
    WHERE NOT EXISTS
              ( SELECT * FROM Emprunt u WHERE NOT EXISTS
                                    ( SELECT * FROM Emprunt v
                                    WHERE v.Personne=t.Personne
                                 AND v.Livre=u.Livre
                                                                       )
                                    )
4- Quels sont les livres ayant été empruntés par tout le monde (i.e. tous les emprunteurs)?
SELECT t.Livre FROM Emprunt t
WHERE NOT EXISTS
                ( SELECT * FROM Emprunt u
                 WHERE NOT EXISTS
( SELECT * FROM Emprunt v
WHERE u.Livre=t.Livre
AND v.Personne=u.Personne
)
                                 )
5- Quelles sont les personnes ayant toujours rendu en retard les livres qu'elles ontempruntés ?
SELECT t.Personne FROM Emprunt t
WHERE NOT EXISTS
                      (SELECT * FROM Emprunt u
                       WHERE u.Personne=t.Personne
                        AND NOT EXISTS
                                    (SELECT * FROM Retard v
                                    WHERE v.Personne=u.Personne
                                                          AND v.Livre=u.Livre
                                                           )
                                 )   

Exercice 2
1 - Quelle est la composition de l'équipe FESTINA (Numéro, nom et pays des coureurs) ?
SELECT NuméroCoureur, NomCoureur, NomPays
FROM EQUIPE A, COUREUR B, PAYS C
WHERE A.CodeEquipe=B.CodeEquipe And B.CodePays=C.CodePays And NomEquipe="FESTINA" ;
2 - Quel est le nombre de kilomètres total du Tour de France 97 ?
SELECT SUM(Nbkm)
FROM ETAPE ;
3 - Quel est le nombre de kilomètres total des étapes de type HAUTE MONTAGNE ?
SELECT SUM(Nbkm)
FROM ETAPE A, TYPE_ETAPE B
WHERE A.CodeType=B.CodeType And LibelléType="HAUTE MONTAGNE" ;
4 - Quels sont les noms des coureurs qui n'ont pas obtenu de bonifications ?
SELECT NomCoureur FROM COUREUR
WHERE NuméroCoureur NOT IN (SELECT NuméroCoureur
FROM ATTRIBUER_BONIFICATION) ;
5 - Quels sont les noms des coureurs qui ont participé à toutes les étapes ?
SELECT NomCoureur
FROM PARTICIPER A, COUREUR B
WHERE A.NuméroCoureur=B.NuméroCoureur
GROUP BY NuméroCoureur, NomCoureur
HAVING COUNT(*)=(SELECT COUNT(*) FROM ETAPE) ;
6 - Quel est le classement général des coureurs (nom, code équipe, code pays et temps des coureurs) à l'issue des 13 premières étapes sachant que lesbonifications ont été intégrées dans les temps réalisés à chaque étape ? SELECT NomCoureur, CodeEquipe, CodePays, SUM(TempsRéalisé) AS Total
FROM PARTICIPER A, COUREUR B
WHERE A.NuméroCoureur=B.NuméroCoureur and NuméroEtape<=13
GROUP BY A.NuméroCoureur, NomCoureur, CodeEquipe, CodePays
ORDER BY Total;
7 - Quel est le classement par équipe à l'issue des 13 premières étapes (nom et temps des équipes) ?
SELECT NomEquipe, SUM(TempsRéalisé) AS Total
FROM PARTICIPER A, COUREUR B, EQUIPE C
WHERE A.NuméroCoureur=B.NuméroCoureur And B.CodeEquipe=C.CodeEquipe
And NuméroEtape<=13
GROUP BY B.CodeEquipe, NomEquipe
ORDER BY Total;
Exercice3
1- Quelles sont les dates du concert de Corneille au Zenith ?
SELECT Date
FROM Concert t, Spectacle u, Salle v
  WHERE t.Spectacle_ID = u.Spectacle_ID
    AND u.Chanteur = 'Corneille'
    AND u.Salle_ID = v.Salle_ID
    AND v.Nom = 'Zenith'
2- Quels sont les noms des salles ayant la plus grande capacité ?
Il est possible de traduire directement la requête exprimée en calcul relationnel, comme ci-dessous.
SELECT Nom
FROM Salle t
WHERE NOT EXISTS
               (SELECT * FROM Salle u
                WHERE u.Capacité >= t. Capacité
                )

Il est également possible d'utiliser l'opérateur d'agrégation MAX, comme pour la requêtesuivante.
SELECT Nom
FROM Salle
WHERE Capacité >= ( SELECT (MAX(Capacité)
FROM Salle
)
Il est également possible d'utiliser le mot-clé ALL :
SELECT Nom
FROM Salle
WHERE Capacité >= ALL ( SELECT Capacité
FROM Salle
)
3- Quels sont les chanteurs n'ayant jamais réalisé de concert à la Cygale ?
SELECT Chanteur
FROM Spectacle
WHERE Chanteur NOT IN
                    (SELECT Chanteur
                     FROM Spectacle u, Salle v
                     WHERE u.Salle_ID=v.Salle_ID
                     AND v.Nom='Cygale'
                     )

Cette requête peut aussi s'exprimer avec un NOT EXISTS en utilisant une variable nuplet tdans le premier FROM, par une simple traduction du calcul relationnel :
SELECT Chanteur
FROM Spectacle t
WHERE Chanteur NOT EXISTS
                        (SELECT * FROM Spectacle u, Salle v
                         WHERE u.Salle_ID=v.Salle_ID
                         AND v.Nom='Cygale'
                         AND t.CHanteur=u.Chanteur
                         )
4- Quels sont les chanteurs ayant réalisé au moins un concert dans toutes les salles?
SELECT Chanteur FROM Spectacle t
WHERE NOT EXISTS
               (SELECT * FROM Salle u WHERE NOT EXISTS
                            (SELECT * FROM Spectacle v
                             WHERE v.Chanteur = t. Chanteur
                             AND u.Salle_ID = v.Salle_ID
                             )
                )

5- Quels sont les dates et les identi cateurs des concerts pour lesquels il ne resteaucun billet invendu ?
SELECT Concert_ID, Date
FROM Concert t
WHERE NOT EXISTS
               (SELECT * FROM Billet u
                WHERE u.Concert_ID=t.Concert_ID
                AND NOT EXISTS
                             (SELECT * FROM Vente v
                              WHERE u.Billet_ID = v.Billet_ID
                              )

Share This:

Post Tags:

#
  • To add an Emoticons Show Icons
  • To add code Use [pre]code here[/pre]
  • To add an Image Use [img]IMAGE-URL-HERE[/img]
  • To add Youtube video just paste a video link like http://www.youtube.com/watch?v=0x_gnfpL3RM