1 / 50

Travaux Pratiques en SQL

Travaux Pratiques en SQL. Eric Vyncke Eric.Vyncke@hec.be http://www.hec.be/~evyncke/cours Dernière mise à jour: 27 février 2004. Table des Matières. MySQL Utilisation de MySQL sur Linux Utilisation d’HyperTerminal pour Linux Plus d’info sur le serveur omega.hec.be

thelma
Télécharger la présentation

Travaux Pratiques en SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Travaux Pratiques en SQL Eric Vyncke Eric.Vyncke@hec.be http://www.hec.be/~evyncke/cours Dernière mise à jour: 27 février 2004

  2. Table des Matières • MySQL • Utilisation de MySQL sur Linux • Utilisation d’HyperTerminal pour Linux • Plus d’info sur le serveur omega.hec.be • Utilisation de MySQL Front sur Windows • Description de la BD SUMMIT • Liste d’exercices optionnels

  3. Outils SGBD SQL Disponibles • Les travaux pratiques se déroulent soit: • sur MySQL sur le serveur HEC disponible à partir de n’importe quel PC connecté à Internet (soit à l’école soit chez vous) • Sur MySQL installé sur votre PC Windows/Linuxhttp://www.mysql.com/downloads/ • Sous Windows, intégration avec serveur Web et PHPhttp://www.easyphp.org/index.php3?lang=fr

  4. MySQL sur Linux

  5. Démarrage de MySQL sur Linux /1 • MySQL est un programme tournant sur un serveur Linux partagé et accessible depuis l’Internet • Sélectionner Démarrer -> Exécuter • Entrer telnet omega.hec.be • Utiliser votre nom d’utilisateur email et le mot de passe email (identiques à ceux de Windows) pour: Login: utilisateur(suivi de <Enter>) Password: mot de passe(n’apparait pas à l’écran, suivi de <Enter>)

  6. Démarrage de MySQL sur Linux /2 • Le prompt standard de Linux/Unix apparait et vous pouvez exécuter des commandes Linux: • afficher le contenu du répertoire: ls -al • afficher le répertoire courant: pwd • changer de répertoire: cd nomderépertoire • afficher le contenu d’un fichier: more nomdefichier • terminer: exit • envoyer un fichier à une adresse e-mail mail -s “Fichier” adresseemail < nomdefichier

  7. Démarrage de MySQL sur Linux /3 Nom de la base de données • Démarrez MySQL sans journal mysql -p –u utilisateur base mysql –p –u test test • Démarrez MySQL avec un journal: mysql.log mysql –p –u summit –-tee=mysql1.logsummitIl est conseillé de numéroter les divers journaux, par exemple mysql5.log pour le 5ème cours • Et entrez le mot de passe de la base de donnée au prompt:Enter password: (attention aucun '*' n'est affiché) Nom de l'utilisateur

  8. Les diverses bases MySQL sur Omega

  9. Exemple de démarrage

  10. Trucs pour MySQL • La touche flèche vers le haut permet de rappeler la commande précédente (à chaque nouvelle pression la commande précédente est affichée) • Les flèches vers la droite et la gauche permettent de se déplacer dans la commande • Ne pas oublier de terminer la commande par “;” et ENTREE • Toutes les commandes entrées sont journalisées dans le fichier caché .mysql_history

  11. Terminer MySQL • Retourner au prompt de Linux: exit • Terminer le programme Telnet: exit

  12. Utilisation d’HyperTerminal

  13. Utilisation d’Hyperterminal /1 • Sur Windows 2000 (notamment aux HEC), un programme de remplacement à Telnet existe: Hyperterminal • Pour le démarrer: • Démarrer/Programmes/Accessoires/Communications/Hyperterminal • Sinon, cliquer sur l’icône Omega du bureau

  14. Hyperterminal /2 • Entrer un nom pour cette nouvelle configuration, par exemple ‘Omega’ • Choisir une icône (n’importe laquelle)

  15. Hyperterminal /3 • Sélectionner TCP/IP comme moyen de connexion • L’adresse hôte doit être omega.hec.be • La porte 23

  16. Hyperterminal /4 • Et voila

  17. Hyperterminal /5 • Sauver la configuration sur votre bureau • Fichier/Enregistrer sous… • Sauver le fichier omega.ht sur le bureau • A faire uniquement la première fois

  18. Utilisation du Bloc Note et MySQL • Il est conseillé de démarrer le bloc-note • Démarrer/Programmes/Accessoire/Bloc note • Ou Démarrer/Exécuter puis notepad • Puis de préparer les requêtes SQL dans le bloc-note, de les copier et les coller • Hyperterminal: Edition/Coller • Telnet: cliquer dans le fenêtre avec le bouton droit • A la fin du cours, il est possible de sauver le contenu du bloc-note sur Z: ou Mes Documents

  19. Information sur omega.hec.be

  20. Qq notes supplémentaires • Depuis les PC HEC, les fichiers de votre répertoire Linux par défaut (votre home) sont disponibles sur Z: • Pour impression • Pour transfert • En dehors de Z:, vous pouvez faire la même chose de n’importe où sur Internet (cybercafé, domicile, …)

  21. Quelques notes sur omega • Omega (ou omega.hec.be) est un serveur Linux avec plusieurs services: • mySQL: pour le cours et les besoins de l'école • En IPv6 (génération suivante de TCP/IP) • Avec LDAP (répertoire): http://directory.hec.be • Avec serveur web (y compris pour les pages des étudiants) http://www.students.hec.be • Serveur de fichiers: \\omega\homes

  22. Omega et le courier électronique • Tout les étudiants ont une adresse email du genre Prenom.Nom@students.hec.be • Sans publicité et gratuite • Interface web comme hotmailhttp://webmail.students.hec.be

  23. Utilisation de MySQL Front Interface graphique SQL sur Windows

  24. MySQL Front • MySQLFront • Logiciel Windows • Version 2.0 gratuite (voir les documents du cours SI06 sur campus.hec.be) • Version 3.0 payante: http://www.mysqlfront.de/ • Mode client-serveur • Interface graphique sur votre PC • Vrai serveur de base de données: MySQL sur omega.hec.be • Communication par réseau TCP/IP (y compris de chez vous)

  25. MySQL Front 1ère Connexion /1

  26. MySQL Front 1ère Connexion /2

  27. MySQL Front 1ère Connexion /3 Nom d’utilisateur Mot de passe. Ici summit Nom de la base de données

  28. MySQL Front: l’écran… Structure de la base de données Historique des Requêtes SQL

  29. MySQL Front: contenu d’une table • Cliquer sur le nom d’une table • Cliquer sur le taquet data

  30. MySQL Front: Requête SQL • Cliquer sur le taquet SQL Query • Entrer une instruction SQL • Cliquer sur le triangle pour executer TOUTES les instructions SQL du panneau

  31. MySQLFront: Quelques Astuces • MYSQLFront v2.0 est gratuit mais a quelques bugs… • Notamment: ‘permission denied’, il faut alors cliquer dans le panneau gauche sur une autre table, puis recliquer sur la taquet ‘SQL Query’ • F5 permet de rafraîchir le contenu de la table ou de la base • F9 permet d’exécuter TOUTES les requêtes du panneau d’entrée SQL

  32. La base de données Summit

  33. Base de données: SUMMIT • La base de données summit est disponible pour les travaux pratiques • Summit aide à gérer un ensemble de magasins d'articles de sport (société américaine) • A des fins de sécurité, il est impossible d'ajouter, de modifier ou d'effacer des données • Pour se connecter à cette base: mysql –p –u summit –-tee=mysql1.log summit (le mot de passe est summit)

  34. REGION TITLE DEPT ORD CUSTOMER EMP Id name Id name region_id title Id customer_id date_ordered date_shipped sales_rep_id total payment_type order_filled Id last_name first_name user_id start_date comments manager_id title dept_id salary commission_pct Id name phone address city state country zip_code credit_rating sales_rep_id region_id comments ITEM Ord_id item_id product_id price quantity quantity_shipped PRODUCT Id name short_desc suggested_whlsl_price whlsl_units WAREHOUSE INVENTORY Id region_id address city state country zip_code manager_id Product_id warehouse_id amount_in_stock reorder_point max_in_stock out_of_stock_explanation restock_date Schéma de la base de données

  35. CUSTOMER • CREATE TABLE customer • (id NUMERIC(7,0) NOT NULL, • name VARCHAR(50) NOT NULL, • phone VARCHAR(25), • address VARCHAR(255), • city VARCHAR(30), • state VARCHAR(20), • country VARCHAR(30), • zip_code VARCHAR(75), • credit_rating VARCHAR(9), • sales_rep_id NUMERIC(7,0), • region_id NUMERIC(7,0), • comments VARCHAR(255))

  36. DEPT • CREATE TABLE dept • (id NUMERIC(7,0)NOT NULL, • name VARCHAR(25)NOT NULL, • region_id NUMERIC(7,0))

  37. EMP • CREATE TABLE emp • (id NUMERIC(7,0)NOT NULL, • last_name VARCHAR(25)NOT NULL, • first_name VARCHAR(25), • userid VARCHAR(8), • start_date DATE, • comments VARCHAR(255), • manager_id NUMERIC(7,0), • title VARCHAR(25), • dept_id NUMERIC(7,0), • salary NUMERIC(11, 2), • commission_pct NUMERIC(4, 2))

  38. INVENTORY • CREATE TABLE inventory • (product_id NUMERIC(7,0) NOT NULL, • warehouse_id NUMERIC(7,0) NOT NULL, • amount_in_stock NUMERIC(9,0), • reorder_point NUMERIC(9,0), • max_in_stock NUMERIC(9,0), • out_of_stock_explanation VARCHAR(255), • restock_date DATE)

  39. ITEM • CREATE TABLE item • (ord_id NUMERIC(7,0) NOT NULL, • item_id NUMERIC(7,0) NOT NULL, • product_id NUMERIC(7,0) NOT NULL, • price NUMERIC(11, 2), • quantity NUMERIC(9,0), • quantity_shipped NUMERIC(9,0))

  40. ORD • CREATE TABLE ord • (id NUMERIC(7,0) NOT NULL, • customer_id NUMERIC(7,0) NOT NULL, • date_ordered DATE, • date_shipped DATE, • sales_rep_id NUMERIC(7,0), • total NUMERIC(11, 2), • payment_type VARCHAR(6), • order_filled VARCHAR(1))

  41. PRODUCT • CREATE TABLE product • (id NUMERIC(7,0) NOT NULL, • name VARCHAR(50) NOT NULL, • short_desc VARCHAR(255), • longtext_id NUMERIC(7,0), • image_id NUMERIC(7,0), • suggested_whlsl_price NUMBER(11, 2), • whlsl_units VARCHAR(25))

  42. REGION • CREATE TABLE region • (id NUMERIC(7,0) NOT NULL, • name VARCHAR(50))

  43. TITLE • CREATE TABLE title • (title VARCHAR(25))

  44. WAREHOUSE • CREATE TABLE warehouse • (id NUMERIC(7,0) NOT NULL, • region_id NUMERIC(7,0) NOT NULL, • address LONG, • city VARCHAR(30), • state VARCHAR(20), • country VARCHAR(30), • zip_code VARCHAR(75), • phone VARCHAR(25), • manager_id NUMERIC(7,0))

  45. Exercices Optionnels sur Summit

  46. Afficher les noms des clients habitant aux ‘USA’ Afficher les noms des clients contenant la lettre ‘k’ Afficher le total des bons de commande (quantité * prix) en USD (en supposant que la base contient les prix en USD) Afficher le total des bons de commande (quantité * prix) en EUR Combien de clients existent dans la base de données ? Exercices sur un tableau /1

  47. Afficher pour chaque bon de commande: son numéro ainsi que le nombre de lignes (de la table ITEM) qui le composent Afficher le nombre de produits dont le nom (champ name) contient ‘boot’ Afficher le nombre d’employés par n° de département Afficher, pour chaque pays, le nom du pays ainsi que le nombre de clients de ce pays. Exercices sur un tableau /2

  48. Afficher pour chaque employé: son nom, son prénom, le nom de son département et le nom de la région Afficher pour chaque nom de région, le nombre d'employés travaillant dans cette région Afficher pour chaque employé, son nom ainsi que le nom de son supérieur (utilisez deux abréviations différentes pour le même tableau) Exercices sur plusieurs tableaux /1

  49. Afficher le nom de tous les clients ayant commandé des produits contenant le mot ‘ Ski ’ Quels sont les noms des produits qui sont entreposés en ‘ Europe ’ ? Afficher pour chaque gestionnaire d’entrepôts le nombre de produits (la somme de amount_in_stock) dans ses entrepôts Afficher pour chaque entrepôt, son pays, le nom de son gestionnaire ainsi que le nombre de produits en rupture de stock (reorder_point >= amount_in_stock) Exercices sur plusieurs tableaux /2

  50. Afficher pour chaque client: son nom, tous ses numéros de bons de commande ainsi que le montant HTVA et TVAC (21%) de ceux-ci Quels sont les noms de clients habitant dans la même ville (et pays!) qu’un entrepôt ? (dans ce cas, il faut utiliser une relation qui n’est pas indiquée par des flèches rouges) Afficher pour chaque employé, son nom, son salaire annuel ainsi que la commission due sur tous les bons de commandes qu’il a reçu. Exercices sur plusieurs tableaux /3

More Related