Informatique et Techniques Numériques en Economie
-
TD 1 : SQL / Gestion base de donnée

1- Présentation générale

Ce premier TD vise à concevoir et créer une base de données sur le serveur sql.free.fr et de rentrer des données dans la base. Il s'agit de disposer d'un moyen de stocker l'information sur les produits et les achats pour permettre l'interaction future à base de votre site. Si vous jugez que le présent TD ne vous suffit pas pour gagner assez d'assurance en SQL, vous trouverez des exercices corrigés en assez grand nombre sur ce lien : SQL de base.

Les informations que votre site doit conserver et manipuler sont les suivantes : les informations sur les produits que vend votre site, les informations sur les demandes de devis et achats sur votre site. Si vous voulez un rappel sur la manière d'organiser une base de données et sur la notion de base de données en général : Document Introductif

2- Compétences à acquérir au cours du TD

• Notion de base sur le schéma relationnel
• Utilisation de base de l'interface graphique phpMyAdmin
• Syntaxes des requêtes SQL de base.

3- Création d'un modèle relationnel

4- Création des tables correspondantes sur sql.free.fr

Vous disposez de l'interface graphique de phpMyAdmin pour créer des tables. Vous pouvez accéder cette interface à l'adresse sql.free.fr en renseignant votre login et password. L'interface graphique de phpMyAdmin vous permet de faire un certain nombre d'opérations : consucltation d'une table, insertion d'éléments dans une table etc... Toutes les actions graphiques de l'utilisateur sont retraduites en langage SQL via cette interface. Vous pouvez donc créer les tables que vous avez définies selon les besoins estimés de votre site. Vous pourrez également intégrer des données via cette interface graphique. Notez que l'interface phpMyAdmin permet également d'ouvrir une fenêtre permettant la redaction des requêtes directement en SQL. Par la suite, la création de sites dynamiques vous obligera à connaître la syntaxe SQL. La syntaxe qu'exige les TD est assez simple ici : il s'agit de savoir consulter les tables, de savoir insérer des enregistrements dans la table (ie lors de la consultation d'un produit par un client, on enregistre l'occurence). Voici une prise d'écran de l'interface :

5- Manipuler les informations dans les tables

Dans ce qui suit, on vous introduit des commandes utiles dans la perspective de la création de votre site, avec les liens vers le document ElementsSQL et on vous propose une série d'exercices. A la fin du TD, il faudrait avoir une base qui fonctionne et que vous savez modifier et consulter. La base que vous aurez en fin de TD pourra évoluer : rajout de champ, ajout de produit etc... Le but des 4 premières séances est de parvenir à un site fonctionnel avec un thème et des produits qui correspondent à ce thème (vente de voyages, de livres, de CDs...), vous n'êtes pas obligé de trouver ce thème avec ce TD, rentrez des données virtuelles.

Interroger une table (SELECT)
1. Demander l'affichage de la table Produit entière
2. Demander l'affichage de 2 colonnes particulières dans la table Produit : ref et prix
3. Renvoyer la liste des produits dont le prix est supérieur à un certain montant.
4. Renvoyer la liste des produits trié par prix (ORDER BY).

Opérateur LIKE
5. Chercher tous les produits dont le nom contient "er"
6. Chercher tous les produits dont le prix est supérieur à un certain montant et dont le nom contient "er" (AND)

Select avancé
7. Compter le nombre des produits (COUNT)
8. Renvoyer le prix moyen (AVG), le prix maximum (MAX)

Insérer des enregistrements dans une table (INSERT)
9. Entrer des enregistrements dans votre table Produit
10. Rentrer des achats virtuels dans la table : achat pour des produits pré-existant

Supprimer des enregistrements (DELETE)
11. Eliminer un enregistrement de votre base. (commande DELETE)
12. Eliminer les produit dont le prix est supérieur à un certain montant.

Requêtes imbriquées
13. Renvoyer le ou les produits qui sont les plus chers
14. Renvoyer les produits dont le prix est supérieur au prix moyen

Jointure
15. Renvoyer le prix de tous les produits achetés
16. Renvoyer le nombre d'achat de chaque produit (GROUP BY)
17. Renvoyer le prix moyen des produits achetés

Modifier des enregistrement (UPDATE)
18. Augmenter tous les prix de 10%



19. Corriger votre base de données pour être assuré qu'elle est cohérente avec votre projet de site.



Pour ceux qui veulent aller plus loin :

20. Calculer le prix normalisé par produit (base 100 = prix moyen).
21. Renvoyer la distribution du nombre d'achat par produit.
22. Quelle requête permet de savoir quels produits n'ont pas été achetés mais consultés en 2005
23. Afficher les données de la table Produit au format CSV
24. Renvoyer le prix du produit le plus acheté