Get documents about "Travaux Pratiques en SQL
Document Sample
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
• Utilisation de MySQL Front sur
Windows
• Description de la BD SUMMIT
• Liste d‟exercices optionnels
E. Vyncke, 2004/02/27 2
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/Linux
http://www.mysql.com/downloads/
– Sous Windows, intégration avec serveur Web et
PHP
http://www.easyphp.org/index.php3?lang=fr
E. Vyncke, 2004/02/27 3
MySQL sur Linux
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>)
E. Vyncke, 2004/02/27 5
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
E. Vyncke, 2004/02/27 6
Démarrage de MySQL sur Linux /3
Nom de la base de
• Démarrez MySQL sans journal
données
mysql -p –u utilisateur base
mysql –p –u test test
Nom de l'utilisateur
• Démarrez MySQL avec un journal: mysql.log
mysql –p –u summit –-tee=mysql1.log summit
Il 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é)
E. Vyncke, 2004/02/27 7
Les diverses bases MySQL sur Omega
Base Objectif Nom Mot de
d’utilisateur passe
test Accès en écriture, test test
excercices
CREATE, INSERT,
UPDATE, …
summit Accès en lecture, summit Summit
excercices sur
SELECT
world Accès en lecture, routard guidedu
autres exercices
sur SELECT
E. Vyncke, 2004/02/27 8
Exemple de démarrage
E. Vyncke, 2004/02/27 9
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
E. Vyncke, 2004/02/27 10
Terminer MySQL
• Retourner au prompt de Linux: exit
• Terminer le programme Telnet: exit
E. Vyncke, 2004/02/27 11
Utilisation d’HyperTerminal
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/Communication
s/Hyperterminal
– Sinon, cliquer sur l‟icône Omega du bureau
E. Vyncke, 2004/02/27 13
Hyperterminal /2
• Entrer un nom pour cette
nouvelle configuration,
par exemple „Omega‟
• Choisir une icône
(n‟importe laquelle)
E. Vyncke, 2004/02/27 14
Hyperterminal /3
• Sélectionner TCP/IP
comme moyen de
connexion
• L‟adresse hôte doit être
omega.hec.be
• La porte 23
E. Vyncke, 2004/02/27 15
Hyperterminal /4
• Et voila
E. Vyncke, 2004/02/27 16
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
E. Vyncke, 2004/02/27 17
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
E. Vyncke, 2004/02/27 18
Information sur omega.hec.be
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, …)
E. Vyncke, 2004/02/27 20
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
E. Vyncke, 2004/02/27 21
Omega et le courier électronique
• Tout les étudiants ont
une adresse email du
genre
Prenom.Nom@studen
ts.hec.be
• Sans publicité et
gratuite
• Interface web comme
hotmail
http://webmail.student
s.hec.be
E. Vyncke, 2004/02/27 22
Utilisation de MySQL Front
Interface graphique SQL sur Windows
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)
E. Vyncke, 2004/02/27 24
MySQL Front 1ère Connexion /1
E. Vyncke, 2004/02/27 25
MySQL Front 1ère Connexion /2
E. Vyncke, 2004/02/27 26
MySQL Front 1ère Connexion /3
Nom
d’utilisateur
Mot de
passe. Ici
summit
Nom de la
base de
données
E. Vyncke, 2004/02/27 27
MySQL Front: l’écran…
Structure de la
Historique des
base de données
Requêtes SQL
E. Vyncke, 2004/02/27 28
MySQL Front: contenu d’une table
1) Cliquer sur le nom d’une table
2) Cliquer sur le taquet data
E. Vyncke, 2004/02/27 29
MySQL Front: Requête SQL
1) Cliquer sur le taquet SQL Query
2) Entrer une instruction SQL
3) Cliquer sur le triangle pour executer
TOUTES les instructions SQL du panneau
E. Vyncke, 2004/02/27 30
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
E. Vyncke, 2004/02/27 31
La base de données Summit
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)
E. Vyncke, 2004/02/27 33
Schéma de la base de données
CUSTOMER ORD ITEM
Id Id Ord_id
name customer_id item_id
phone date_ordered product_id
REGION address date_shipped price
city sales_rep_id quantity
Id total quantity_shipped
state
name payment_type
country
zip_code order_filled
credit_rating PRODUCT
sales_rep_id Id
region_id name
DEPT comments short_desc
Id EMP suggested_whlsl_price
name whlsl_units
region_id Id WAREHOUSE
last_name INVENTORY
first_name Id
user_id region_id Product_id
start_date address warehouse_id
comments city amount_in_stock
TITLE manager_id state reorder_point
title country max_in_stock
dept_id zip_code out_of_stock_explanation
title
salary manager_id restock_date
commission_pct
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))
E. Vyncke, 2004/02/27 35
DEPT
CREATE TABLE dept
(id NUMERIC(7,0)NOT NULL,
name VARCHAR(25)NOT NULL,
region_id NUMERIC(7,0))
E. Vyncke, 2004/02/27 36
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))
E. Vyncke, 2004/02/27 37
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)
E. Vyncke, 2004/02/27 38
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))
E. Vyncke, 2004/02/27 39
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))
E. Vyncke, 2004/02/27 40
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))
E. Vyncke, 2004/02/27 41
REGION
CREATE TABLE region
(id NUMERIC(7,0) NOT NULL,
name VARCHAR(50))
E. Vyncke, 2004/02/27 42
TITLE
CREATE TABLE title
(title VARCHAR(25))
E. Vyncke, 2004/02/27 43
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))
E. Vyncke, 2004/02/27 44
Exercices Optionnels sur Summit
Exercices sur un tableau /1
• 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 ?
E. Vyncke, 2004/02/27 46
Exercices sur un tableau /2
• 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.
E. Vyncke, 2004/02/27 47
Exercices sur plusieurs tableaux /1
• 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)
E. Vyncke, 2004/02/27 48
Exercices sur plusieurs tableaux /2
• 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)
E. Vyncke, 2004/02/27 49
Exercices sur plusieurs tableaux /3
• 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.
E. Vyncke, 2004/02/27 50
Related docs
