Exercices requêtes avancées

• Correction
• Annexe

Exercices

Exercice 1 **

La clinique vétérinaire utilise la base de données construite précédemment.

1. Télécharger et ouvrir la base animaux-requetes-avancees.db .

2. Écrire les requêtes simples pour:

   1. insérer l’espèce poisson dans la table Especes,
   2. insérer le soin patte cassée à l’animal d’identifiant 3 dans la table Soins,
   3. sélectionner les animaux de 10 ans et plus, ordonnés par nom.
   4. sélectionner tous les soins distincts réalisés,
   5. compter tous les soins réalisés,
   6. compter tous les soins distincts réalisés,
   7. compter le nombre de stérilisations réalisé,
   8. renommer l’animal charly en charlie.

3. Écrire les requêtes pour:

   1. trouver l’espèce de zappy,
   2. donner les noms des chats dans l’ordre alphabétique,
   3. compter le nombre de chiens,
   4. compter le nombre de soins pour chouchou,
   5. donner le nom (par ordre alphabétique) de tous les chats qui ont subi une stérilisation,
   6. insérer le poisson bubulle, âgé de 8 ans, dans la table Animaux,
   7. insérer le soin stérilisation pour cartman.

4. Les soins prodigués par le vétérinaire sont souvent les mêmes. Quelle modification pourrait-on mettre en place pour améliorer le schéma de la base?

5. Écrire le modèle relationnel avec la modification apportée puis écrire les requêtes permettant de reconstruire la base.

Exercice 2 **

La base de données Chinook contient de l’information relative à des albums musicaux. Celle-ci est structurée dans plusieurs tables.

1. Télécharger et ouvrir la base chinook.db .

2. Observer l’architecture de la base ci-dessus et répondre aux questions:

   1. Quelle est la clé primaire de la relation albums?
   2. Quelle est la clé primaire de la relation playlist_track? Expliquer ce choix.
   3. Que signifie la liaison entre les relations tracks et genres?
   4. Observer l’attribut Total de la relation invoices (factures) et la relation invoice_items. Critiquer cette construction.

3. Écrire les requêtes simples pour:

   1. sélectionner le nom de tous les artistes,
   2. sélectionner tous les morceaux (tracks) d’une durée supérieure à 300000 millisecondes,
   3. sélectionner tous les albums contenant le mot Black, classés dans l’ordre décroissant,
   4. compter le nombre d’albums,
   5. sélectionner le morceau le plus long,
   6. renommer le genre 17 en Hip-Hop and Rap.

4. Écrire les requêtes pour:

   1. trouver le genre du morceau Stone Crazy,
   2. donner le nom des morceaux de la playlist TV Shows,
   3. donner le nombre de factures (invoices) de Terhi Hämäläinen,
   4. donner le montant total de la facture de Terhi Hämäläinen datant du 2011-03-18 00:00:00,
   5. vérifier le résultat précédent en recalculant la somme des achats,
   6. donner le nom des artistes de la playlist TV Shows.

5. Proposer une requête au reste du groupe.

Exercice 3 **

Table: fournisseurs

Table: clients

1. Observer les tables et déterminer les clés primaires et étrangères.
2. Écrire une requête SQL pour trouver le(s) fournisseur(s) et le(s) client(s) qu’il(s) représente(nt).
3. Écrire une requête SQL pour trouver les fournisseurs qui ont reçu des commissions de plus de 5% (0.05).
4. Écrire une requête SQL pour trouver les fournisseurs qui n’habitent pas dans la même ville que leurs clients et qui ont reçu une commission de plus de 5%.

Exercice 4 ***

• Le projet SQL Murder Mystery a été créé par Joon Park et Cathy He lors de leurs travaux au Knight Lab de l’université américaine Northwerstern University. Il a été adapté et dévéloppé pour le Web par Joe Germuska pour en faire un exercice en ligne permettant de travailler la langage SQL.

• Vous trouverez plus d’informations sur le dépôt GitHub du projet.

• Pour résoudre le meurtre, vous pouvez :

  • le faire en ligne à l’adresse : https://mystery.knightlab.com/
  • le faire en local (avec DB Browser for SQLite par exemple) en téléchargeant la base de données sql-murder-mystery.db attachée à ce notebook (ou en la téléchargeant sur le dépôt GitHub du projet en cliquant ici )