TP4 : Fusions et jointures

Consignes

Dans le cadre de ce TP, vous utiliserez la base "chimie" disponible via ce lien.

Sous-requêtes

  1. Déterminez la structure de la base de donnée et de ses tables.
  2. Écrivez les requêtes SQL permettant d'obtenir les chimistes ayant au moins un résultat d'expérience.
    a. En utilisant une sous-requête non-corrélée. b. En utilisant une sous-requête corrélée.

JOIN

  1. Pour chaque résultats, affichez le numéro d'expérience et le nom, prénom du chimiste l'ayant réalisé.
  2. Sans modifier la jointure, adaptez la requête précédente afin d'afficher le nom, prénom des chimistes suivis des numéro, concaténés, des expériences qu'ils ont réalisés (colonne ).
  3. Modifiez la requête précédente afin d'afficher le nom, prénom des chimistes suivis du nombre d'expériences différentes qu'ils ont réalisés. (colonne )
  4. Sans modifier la jointure, adaptez la requête précédente afin de n'afficher que ceux qui n'ont pas effectué d'expérience.
  5. Vous remarquerez que la requête ne retourne aucun résultats, pourquoi ?
  6. Modifiez la requête précédente pour afficher tous les chimistes avec leur nombre d'expériences distinctes.
  7. Modifiez la requête précédente pour afficher les chimistes avec leur moyenne des températures des expériences qu'ils ont réalisés (colonne ).
  8. Pourquoi utiliser pour la seconde jointure ?
  9. En repartant de la requête 6, et en utilisant la structure ci-dessous, affichez le nom, prénom des chimistes suivis de leur taux de réussite :

Mesurer la performance des sous-requêtes et jointures

⚠ Pour des raisons de performances, vous devrez utiliser une base de données "in memory". Pour cela, lancez une nouvelle instance de SQLite sans préciser ni ouvrir de fichiers.

  1. Activez et .
  2. Re-créez les tables , , et du TP3, mais cette fois avec = , et sans manuellement créer d'index.

Sous-requêtes

  1. Exécutez la requête suivante (sous-requête avec clef primaire) :
  2. Exécutez la requête suivante (sous-requête corrélée avec ) :
  3. La requête précédente limite le résultat aux 100 premières entrées, calculez le temps qu'il faudrait sans cette limite.
  4. Transformez cette requête pour utiliser .
  5. Calculez son temps d'exécution sans limite, expliquer l'avantage de .

JOIN

  1. Exécutez, une à une, les requêtes suivantes (JOIN avec clef primaire) :
  2. D'après l'explication de la requête, l'ordre des tables dans une jointure a-t-il un impact sur l'exécution ?
  3. Testez la même jointure avec , puis avec .
  4. Cela impacte-t-il l'exécution ?
  5. Exécutez unes à unes les requêtes suivantes :
  6. Comment expliquer le dans l'explication de la requête, bien qu'aucune de ces 2 tables n'ont d'index ?

Produit cartésien

  1. Exécutez la requête suivante (produit cartésien avec clef primaire) :
  2. Comment expliquer qu'une telle requête soit aussi rapide ?
  3. Exécutez la requête ci-dessous, puis calculez son temps d'exécution sans limites.

Sous-requêtes vs jointures

  1. Exécutez une à une les requêtes ci-dessus avec :
  2. Pourquoi n'est pas nécessaire pour la requête avec sous-requête ?
  3. Pourquoi des différence dans les explications de requêtes des jointures ?
  4. Vaut-il mieux utiliser des sous-requêtes ou des jointures ? Argumentez.