TP3 "Conception de Systèmes d'Information" - requêtes SQL

L'objectif de ce TP est d'apprendre à utiliser le langage SQL pour l'interrogation de données. Il s'agit de continuer ce qui a été fait au TP2, et d'essayer d'arriver au bout des requêtes.

Pensez à éxécuter EasyPHP et pensez à consulter la document de MySQL 5.6 lorsque vous bloquez. Le poly contient également en annexe des exemples de requêtes et leurs résultats.

Les requêtes des sections 4, 5, 6 et 7 devront être écrites dans un fichier portant l'extension .sql. Ce fichier sera envoyé par email à antoine.zimmermann@emse.fr. Utilisez un éditeur de fichier tel Notepad++ pour sauvegarder les requêtes.

Nommez votre fichier avec votre nom ainsi que celui de votre binôme, si vous en avez un. Lorsque vous envoyez votre fichier, mettez en copie votre binôme. Si votre binôme n'est pas en copie, il sera considéré qu'il n'a pas rendu son travail.

1. Téléchargement de la base

Les exercices de ce TP portent sur la base de données des spectacles vues en TD. Téléchargez les instructions de création des tables et d'insertion de données de cette base db-tp3.sql. Exécutez les requêtes téléchargées pour créer les tables. Plutôt que de copier-coller les commandes SQL en ouvrant le fichier dans votre navigateur, vous pouvez sauvegarder le fichier sur votre machine et utiliser l'action Importer dans le menu de PHPmyAdmin.

2. Observer le schéma de la base

Observez le schéma de la base de données pour voir les différence avec le schéma utilisé en TD. En déduire les requêtes SQL correspondant aux questions que vous avez traitées dans le TD3.

3. Expression de requêtes de suppression et de modification

Écrivez en SQL des requêtes pour modifier les informations suivantes :

supprimer les billets coûtant plus de 50 euros ;
modifier l'heure du spectacle ayant lieu le 21 septembre 2012 dans la salle de spectacle 11 (Le Rocher de Palmer) pour qu'elle prenne la valeur '20:30:00'

Dans une clause DELETE, on ne peut indiquer qu'une seule table, donc on ne peut pas faire de jointure dans la condition de WHERE. Pour supprimer des enregistrements en fonction de valeurs d'autres tables, il faut imbriquer des requêtes. Si vous êtes à l'aise avec SQL, essayez de modifier les commandes de Philippe Colin pour les annuler toutes.

4. Expression de requêtes de projection et de sélection

Écrivez en SQL des requˆtes pour accéder aux informations suivantes :

les nom, adresse et numéro de cartes de fidélité de tous les clients ayant une carte,
les spectacles ayant lieu après le 1er avril 2012, par ordre chronologique,
les adresses des salles de concert ayant plus de 2 000 places (sans doublons dans la réponse),
le nom des clients dont le prénom commence par 'j'.

5. Expression de requêtes de jointure

Écrivez en SQL des requˆtes pour accéder aux informations suivantes :

les identifiant, date et heure de tous les spectacles ayant lieu à Saint-Étienne,
les identifiant, date, heure et lieu de tous les spectacles ayant lieu à Bordeaux dans l'aprè-midi entre 14 heures et 18 heures,
le nom des clients ayant achetés des billets pour un concert de Apparatus Jingle.

6. Expression de requêtes sur des groupes

Écrivez en SQL des requêtes pour accéder aux informations suivantes :

le nombre de commandes annulées,
le nombre de commandes effectuées par client,
la capacité moyenne des salles,
les villes disposant de plus de 5 salles de concerts.

7. Requêtes complexes

Dans un premier temps, testez les requêtes complexes vues en TD. Ensuite, voici quelques requêtes supplémentaires :

la commande ayant été effectuée la plus en avance par rapport à la date du spectacle,
le palmarès des villes ayant les plus grandes salles en moyenne,
le nombre total d'enregistrements dans toute la base (en comptant les billets, les artistes, les spectacles, les lieux, les clients, les commandes et les artistes supplémentaires),
pour chaque lieu, le spectacle ayant rapporté le plus d'argent.

8. Rendu du travail

Au fur et à mesure que vous avancez, copiez vos requêtes dans un fichier que vous nommerez nom1-nom2.sql, où nom1 et nom2 sont les noms des membres du binôme. Par exemple, si vous vous appelez Paul Dupond et votre binôme est Charles Durand, votre fichier s'appellera paul-dupond-charles-durand.sql.
Envoyez ce fichier par email à antoine.zimmermann@emse.fr en mettant en copie votre binôme.

tp3.html: last modified 2013/12/20 10:42:42 by Antoine Zimmermann.