Base de données :SQL
OBJECTIF :
MANIPULATION D'UNE BASE DE DONNES EN MODE SQL :
Manipulation des tables.
Manipulation des enregistrements.
SOMMAIRE :
PROBLEME
SOLUTION DU PROBLEME
ANNEXE 1: EXEMPLE DE CREATION DES TABLES EN SQL.
ANNEXE 2: MODIFICATION DES TABLES EN SQL.
ANNEXE 3 : COMMANDES SQL DE MISE A JOUR DES ENREGISTREMENTS.
ANNEXE 4 : COMMANDES SQL DE SELECTION
WEBOGARPHIE
P AGE 1 / 8
PROBLEME
Soit la représentation textuelle relative à une base de données d’un organisme de voyage :
Station (nomstation, capacité, lieu, région, tarif) - Clé primaire soulignée
- Clé étrangère suivi du symbole #
Activité(nomstation#,libellé, prix)
Client(id,nom,prénom,ville,région,solde)
Séjour(idclient#,station#,début,nbplaces)
Table mère Table fille
Table Station Table Activité
nomstation capacité lieu région tarif
nomstation libellé prix
Table mère
Farniente 200 Seychelles Océan indien 1200
Farniente plongée 130
Passac 400 Alpes Europe 1500
Passac kayac 50
Santalba 150 Martinique Antilles 2000
Venusa 350 Guadeloupe Antilles 1200 Passac Piscine 20
Table fille Santalba kayac 100
Table fille
Table Séjour Venusa plongée 120
idclient station début nbplaces Venusa voile 150
20 Venusa 03/08/1998 6 Table mère
30 Venusa 03/08/1998 3 Table Client
20 Santalba 03/08/1998 4 id nom prénom ville région solde
30 Santalba 14/08/1996 5 10 Fogg Philas Londres Europe 12465
30 Passac 15/08/1998 3 20 Pascal Blaise Paris Europe 6763
10 Passac 01/07/1998 2 30 Kerouac Jack New York Amérique 981
10 Farniente 05/09/1998 3
30 Farniente 24/06/1998 5
Liste des clés primaires :
Nom de la table Clé primaire
Station nomstation
Activité nomstation,libellé
Client id
Séjour idclient ,station,début
La liste des liens :
Table mère Table fille Clé primaire Clé étrangère
Station Activité nomstation nomstation
Activité Séjour nomstation station
Client Séjour id idclient
P AGE 2 / 8
La liste des colonnes :
Liste des colonnes
Obligatoire
Valeur par
autorisées
Valeurs
défaut
Taille
Sujet
Type de
Nom colonne Description
données
nomstation Chaîne de caractères 20 Oui Station
capacité Numérique (entier) 4 Oui >=0 Station
lieu Chaîne de caractères 50 Station
région Chaîne de caractères 50 Oui Station
tarif Numérique (décimal) (8 ,3) Oui >0 Station
nomstation Chaîne de caractères 20 Oui Activité
libellé Chaîne de caractères 20 Oui Activité
prix Numérique (décimal) (8 ,2) Oui Activité
id Chaîne de caractères 20 Oui Client
nom Chaîne de caractères 20 Oui Client
prénom Chaîne de caractères 20 Oui Client
ville Chaîne de caractères 20 Client
région Chaîne de caractères 20 Client
solde Numérique (décimal) (8 ,3) Oui >0 Client
idclient Chaîne de caractères 20 Oui Séjour
station Chaîne de caractères 20 Oui Séjour
début DATE Oui Séjour
nbplaces Numérique (entier) 4 NOM 1 >0 Séjour
P AGE 3 / 8
Répondre aux questions suivantes en écrivant les commandes SQL correspondantes :
1/ Créer la table "Station".
2/ Créer la table "Activité".
3/ Créer la table "Client".
4/ Créer la table "Séjour".
5/ Ajouter la colonne "email" dans la table "Client" comme chaîne de caractères (50 max) qui
contient forcément le caractère "@".
6/ Supprimer la colonne "email" de la table "Client".
7/ Ajouter la table "Société" tell que : Société (Numsoc,libellé,adresse, tel) .
8/ Supprimer la table "Société".
9/ Modifier le champ "prix" de la table "Activité" en prenant 3 chiffres après la virgule au
maximum.
10/ Stocker la liste des stations dans la table "Station".
11/ Stocker la liste des clients dans la table "Client".
12/ Stocker la liste des séjours dans la table "séjour".
13/ Stocker la liste des activités dans la table "Activité".
14/ Afficher toutes les stations se trouvant aux "Antilles" par ordre alphabétique décroissant selon les
noms des stations.
15/ Afficher tous les clients dont leurs noms contiennent le syllabe "pa".
16/ Afficher tous les séjours réservés pendant le mois d'Août 1998 dans la station"Venusa".
17/ Afficher le nombre de places réservées par Mr "Kerouac".
18/ Afficher les régions avec leurs nombres des stations correspondantes.
19/ Ajouter une place à tous les séjours de Mr "Kerouac".
20/ Supprimer tous les séjours réservés à "Venusa".
P AGE 4 / 8
#1/
CREATE TABLE Station(
nomstation varchar( 20 ) PRIMARY KEY ,
capacité int( 4 ) NOT NULL CHECK (capacité >=0),
lieu varchar( 50 ) ,
région varchar( 50 ) NOT NULL ,
tarif decimal( 8,3 ) CHECK (tarif >0)
);
#2/
Create table Activité(
nomstation varchar( 20 ) ,
libellé varchar( 20 ) NOT NULL ,
prix decimal( 8,2 ) NOT NULL CHECK (prix>0),
PRIMARY KEY(nomstation, libellé ) ,
Foreign Key (nomstation ) REFERENCES Station (nomstation) ON UPDATE CASCADE ON
DELETE CASCADE
);
#3/
CREATE TABLE Client(
id varchar( 20 ) PRIMARY KEY,
nom varchar( 20 ) NOT NULL,
prénom varchar( 20 ) NOT NULL,
ville varchar( 20 ) ,
région varchar( 20 ) ,
solde decimal( 8,3 ) NOT NULL CHECK (solde >0) );
#4/
CREATE TABLE Séjour(
idclient varchar( 20 ) NOT NULL,
station varchar( 20 ) NOT NULL,
début DATE NOT NULL ,
nbplaces INT( 4 ) DEFAULT 1 CHECK(nbplaces>0 ),
PRIMARY KEY(idclient, station,début),
Foreign Key(idclient) REFERENCES Client (id) ON UPDATE CASCADE ON DELETE CASCADE,
Foreign Key (station) REFERENCES Station (nomstation) ON UPDATE CASCADE ON DELETE
CASCADE
);
#5/
ALTER TABLE client ADD COLUMN ( email VARCHAR( 50 ) CHECK ( email LIKE "%@%")) ;
#6/
ALTER TABLE client DROP COLUMN email ;
#7/
CREATE TABLE Société(
Numsoc varchar( 20 ) PRIMARY KEY ,
libellé varchar( 20 ) NOT NULL ,
adresse varchar( 50 ) NOT NULL ,
tel varchar( 50 ) NOT NULL
);
#8/
DROP TABLE Société;
#9/
ALTER TABLE Activité MODIFY prix decimal( 8, 3 ) NOT NULL ;
#10
INSERT INTO Station Values
('Farniente',200,'seychelles','Océan indien',1200),
('Passac', 400 ,'Alpes', 'Europe',1500),
('Santalba',150 ,'Martinique' ,'Antilles',2000),
('Venusa', 350 ,'Guadeloupe','Antilles',1200);
#11
P AGE 5 / 8
INSERT INTO Client Values
(10,'Fogg','Philas','Londres','Europe',12465),
(20, 'Pascal', 'Blaise','Paris','Europe',6763),
(30, 'Kerouac','Jack','New York','Amérique', 981) ;
#12
INSERT INTO Séjour Values
(10, 'Farniente' ,05/091998,3),
(10, 'Passac' ,01/07/1998,2),
(20, 'Santalba' ,03/08/1998,4),
(20, 'Venusa' ,03/08/1998,6),
(30, 'Farniente' ,24/06/1998,5),
(30, 'Passac' ,15/08/1998,3),
(30, 'Santalba' ,14/08/1996,5),
(30, 'Venusa' ,03/08/1998,3);
#13
INSERT INTO Activité Values
('Farniente', 'plongée',130),
('Passac', 'kayac', 50),
('Passac', 'Piscine', 20),
('Santalba','kayac', 100),
('Venusa', 'plongée', 120),
('Venusa', 'voile', 150);
#14
SELECT Station.*
FROM Station
WHERE région="Antilles"
ORDER BY nomstation DESC;
#15
SELECT Client.*
FROM Client
WHERE Client.nom Like "%pa%";
#16
SELECT Séjour . *
FROM Séjour
WHERE ( début >=8 /01/1998) AND ( début <=8 /31/1998)AND ( station = "venusa");
#17
SELECT Client.nom, Sum( Séjour.nbplaces ) AS NBP
FROM Client, Séjour
WHERE Client.id = Séjour.idclient
GROUP BY Client.nom
HAVING Client.nom = "kerouac";
#18
SELECT région, Count(nomstation) AS NBSTATION
FROM Station
GROUP BY région;
#19
UPDATE Séjour,client
SET Séjour.nbplaces = nbplaces +1
WHERE (Client.nom = "kerouac" )AND( Séjour.idclient = client.id );
#20
DELETE
FROM Séjour
WHERE station="Venusa";
P AGE 6 / 8
REMARQUE : Dans les annexes, la notation [] signifie un argument optionnel..
Annexe 1: Exemple de création des tables en SQL.
Article (code_art,des_art,pu,qte_stock)
Client(code_client,nom_client,prenom_client,adr_client,tel_client,tel_client)
Commande(num_comm,date_comm,code_client#)
Détail_commande(num_comm#,num_ligne,code_art#,qte_comm)
CREATE TABLE Article (
code_art VARCHAR(20) PRIMARY KEY ,
des_art VARCHAR(50) NOT NULL ,
pu DECIMAL(8,3) CHECK(pu >0),
qte_stock INT(5) DEFAULT 0 CHECK(qte_stock >0)) ;
CREATE TABLE Client(
code_client VARCHAR(20) PRIMARY KEY,
nom_client VARCHAR(20) NOT NULL,
prenom_client VARCHAR(20),
adr_client VARCHAR(100) NOT NULL ,
tel_client VARCHAR(20) ,
email VARCHAR(20) CHECK (email LIKE "*@*") );
CREATE TABLE Commande(
num_comm VARCHAR(20) PRIMARY KEY ,
date_comm DATE NOT NULL ,
code_client VARCHAR(20) REFERENCES client ( code_client));
CREATE TABLE Détail_commande(
num_comm VARCHAR(20) REFERENCES Commande ON DELETE CASCADE ,
num_ligne INT(4) NOT NULL ,
code_art VARCHAR(20) REFERENCES Article ,
qte_comm INT(4) CHECK(qte_comm >0) ,
PRIMARY KEY (num_comm, num_ligne) );
Annexe 2: Modification des tables en SQL.
Manipulation des colonnes et des contraintes:
ALTER TABLE nom_table
[ADD COLUMN définition colonne]
[ADD CONSTRAINT définition_contrainte]
[MODIFY définition_colonne]
[DROP COLUMN nom_colonne]
[DROP CONSTRAINT nom_contrainte]
[ENABLE| DISABLE nom_constrainte] ;
Suppression d'une table :
DROP TBLE nom_table ;
P AGE 7 / 8
Annexe 3 : Commandes SQL de mise à jour des enregistrements.
1- Insertion des lignes
INSERT INTO nom_table [liste_nom_colonne]
VALUES (liste des valeurs séparées par des virgules) ;
2- Suppression des lignes
DELETE FROM nom_table
[WHERE condition];
3- Modification des lignes
UPDATE nom_table
SET nom_colonne1 =Expression1 [, nom_colonne2 =expression2 …]
[WHERE Condition] ;
Annexe 4 : Commandes SQL de sélection
SELECT [option clause d’unicité All ou DISTINCT] <liste de colonnes ou *>
FROM <liste de tables>
[WHERE <critères de sélection > ]
[GROUP BY <liste de colonnes>]
[HAVING <critères de sélection d'un groupr >]
[ORDER BY <critère d’ordre >] ;
P AGE 8 / 8
