Introduction au langage SQL
Mehdi HAJJI
Conception BD II2
Introduction
} SQL : acronyme pour Structured Query Language qui a t con u
par IBM, et a succ d au langage SEQUEL.
} Origine : SQL est un langage de requ tes standard pour les SGBD
relationnels
} Il se d cline en quatre parties :
} le DDL (Data Definition Language) comporte les instructions qui
permettent de d finir la fa on dont les donn es sont repr sent es.
} le DML (Data Manipulation Language) permet d crire dans la base et
donc de modifier les donn es.
} le DQL (Data Query Language) est la partie la plus complexe du SQL,
elle permet de lire les donn es dans la base laide de requ tes.
} le DCL (Data Control Language), qui permet de g rer les droits dacc s
aux donn es.
} A cela sajoute des extensions proc durales du SQL (appel PL/SQL
en Oracle). Celui-ci permet d crire des scripts ex cut s par le
serveur de base de donn es.
Introduction au langage SQL
-M. HAJJI-
2
Les types de donn es utilisables
Types alphanum riques
} CHARACTER (ou CHAR) : valeurs alpha de longueur fixe
} CHARACTER VARYING (ou VARCHAR ou CHAR VARYING) : valeur alpha
de longueur maximale fix e
} Ces types de donn es sont cod s sur 2 octets (EBCDIC ou ASCII) et on
doit sp cifier la longueur de la cha ne.
NOM_CLIENT CHAR(32)
OBSERVATIONS VARCHAR(32)
} NATIONAL CHARACTER (ou NCHAR ou NATIONAL CHAR) : valeurs
alpha de longueur fixe
} NATIONAL CHARACTER VARYING (ou NCHAR VARYING ou
NATIONAL CHAR VARYING) : valeur alpha de longueur maximale fix e
sur le jeu de caract re du pays
} Ces types de donn es sont cod s sur 4 octets (UNICODE) et on doit
sp cifier la longueur de la cha ne.
NOM_CLIENT NCHAR(32)
OBSERVATIONS NCHAR VARYING(32)
Introduction au langage SQL
-M. HAJJI-
3
Les types de donn es utilisables
Types num riques
} NUMERIC (ou DECIMAL ou DEC) : nombre d cimal repr sentation
exacte chelle et pr cision facultatives
} INTEGER (ou INT): entier long
} SMALLINT : entier court
} FLOAT : r el virgule flottante dont la repr sentation est binaire chelle
et pr cision obligatoire
} REAL : r el virgule flottante dont la repr sentation est binaire, de faible
pr cision
} DOUBLE PRECISION : r el virgule flottante dont la repr sentation est
binaire, de grande pr cision
} BIT : cha ne de bit de longueur fixe
} BIT VARYING : cha ne de bit de longueur maximale
} Pour les types r els NUMERIC, DECIMAL, DEC et FLOAT, on doit sp cifier
le nombre de chiffres significatifs et la pr cision des d cimales apr s la
virgule.
NUMERIC (15,2)
Introduction au langage SQL
-M. HAJJI-
4
Les types de donn es utilisables
Types temporels
} DATE : date du calendrier gr gorien
} TIME : temps sur 24 heures
} TIMESTAMP : combin date temps
} INTERVAL : intervalle de date / temps
1999-03-26 22:54:28.123
est le 26 mars 1999 22h 54m, 28s et 123 millisecondes.
} Le type INTERVAL est tr s particulier. Il est rarement pr sent dans les
SGBDR.
INTERVAL pr cision_min TO
} o pr cision_min et pr cision_max peuvent prendre les valeurs :
} {YEAR | MONTH | DAY | HOUR | MINUTE | SECOND}
} pr cision_max ne peut tre qu'une mesure temporelle plus fine que
pr cision_min.
JOURS INTERVAL DAY
TRIMESTRE INTERVAL MONTH TO DAY
TACHE INTERVAL HOUR TO SECOND
DUREE_FILM INTERVAL MINUTE
Introduction au langage SQL
-M. HAJJI-
5
Les commandes DDL
} la commande CREATE
} CREATE TABLE nom_de_la_table
(
)
colonne1 type_donnees,
colonne2 type_donnees,
colonne3 type_donnees,
colonne4 type_donnees
Introduction au langage SQL
-M. HAJJI-
6
Les commandes DDL
} la commande CREATE
Introduction au langage SQL
-M. HAJJI-
7
Les commandes DDL
} la commande CREATE
contrainte_de_colonne ::
NULL
| UNIQUE | PRIMARY KEY
| CHECK ( pr dicat_de_colonne )
| FOREIGN KEY REFERENCES table (colonne) sp cification_r f rence
contrainte_de_table ::
CONSTRAINT nom_contrainte
{ UNIQUE | PRIMARY KEY ( liste_colonne )
| CHECK ( pr dicat_de_table )
| FOREIGN KEY liste colonne REFERENCES nom_table (liste_colonne)
sp cification_r f rence }
Introduction au langage SQL
-M. HAJJI-
8
Les commandes DDL
} la commande CREATE
} Contraintes
} NOT NULL : emp che denregistrer une valeur nulle pour une colonne.
} DEFAULT : attribuer une valeur par d faut si aucune donn es nest
indiqu e pour cette colonne lors de lajout dune ligne dans la table.
} PRIMARY KEY : indiquer si cette colonne est consid r e comme cl
primaire pour un index.
} FOREIGN KEY : permet, pour les valeurs de la colonne, de faire
r f rence des valeurs pr existantes dans une colonne d'une autre table.
Ce m canisme s'appelle int grit r f rentielle
} UNIQUE : les valeurs de la colonne doivent tre unique ou NULL, c'est
dire qu' l'exception du marqueur NULL, il ne doit jamais y avoir plus
d'une fois la m me valeur (pas de doublon)
} CHECK : permet de pr ciser un pr dicat qui acceptera la valeur s'il est
valu vrai
Introduction au langage SQL
-M. HAJJI-
9
Les commandes DDL
CREATE TABLE utilisateur
(
id INT PRIMARY KEY NOT NULL,
nom VARCHAR(100),
prenom VARCHAR(100),
email VARCHAR(255),
date_naissance DATE,
pays VARCHAR(255),
ville VARCHAR(255),
code_postal VARCHAR(5),
nombre_achat INT
)
} id : identifiant unique qui est utilis comme cl primaire et qui
nest pas nulle
} nom : nom de lutilisateur dans une colonne de type
VARCHAR avec un maximum de 100 caract res au maximum
} prenom : idem mais pour le pr nom
Introduction au langage SQL
-M. HAJJI-
10
Les commandes DDL
} email : adresse email enregistr sous 255 caract res au
maximum
} date_naissance : date de naissance enregistr au format
AAAA-MM-JJ (exemple : 1973-11-17)
} pays : nom du pays de lutilisateur sous 255 caract res au
maximum
} ville : idem pour la ville
} code_postal : 5 caract res du code postal
} nombre_achat : nombre dachat de cet utilisateur sur le
site
Introduction au langage SQL
-M. HAJJI-
11
Les commandes DDL
} La commande CREATE:
} Cl primaire compos e
} Cl trang re
CREATE TABLE TJ_CHB_PLN_CLI
( CHB_ID INTEGER
NOT NULL,
not null,
PLN_JOUR DATE not null,
CLI_ID INTEGER
CHB_PLN_CLI_NB_PERS SMALLINT not null,
CHB_PLN_CLI_RESERVE NUMERIC(1) not null
CHB_PLN_CLI_OCCUPE NUMERIC(1) not null
CONSTRAINT PK_TJ_CHB_PLN_CLI PRIMARY KEY (CHB_ID, PLN_JOUR) ,
CONSTRAINT FK_CHB_ID CHB_ID REFERENCES T_CHAMBRE (CHB_ID) ,
CONSTRAINT FK_PLN_JOUR PLN_JOUR REFERENCES T_PLANNING
default 0,
default 1,
(PLN_JOUR) ,
CONSTRAINT FK_CLI_ID CLI_ID REFERENCES T_CLIENT CLI_ID)
)
Introduction au langage SQL
-M. HAJJI-
12
Les commandes DDL
} La commande CREATE:
} Exemples
CREATE TABLE T_CLIENT
(CLI_NOM CHAR(32) NOT NULL,
CLI_PRENOM VARCHAR(32) NOT NULL,
CONSTRAINT PK_CLIENT PRIMARY KEY (CLI_NOM, CLI_PRENOM))
CREATE TABLE T_CLIENT
Publicité
(CLI_ID INTEGER NOT NULL PRIMARY KEY,
CLI_NOM CHAR(32) NOT NULL CHECK (SUBSTRING(VALUE, 1, 1) <> ' ' AND
UPPER(VALUE) = VALUE),
CLI_PRENOM VARCHAR(32) REFERENCES TR_PRENOM (PRN_PRENOM))
CREATE TABLE T_VOITURE
(VTR_ID INTEGER NOT NULL PRIMARY KEY,
VTR_MARQUE CHAR(32) NOT NULL,
VTR_MODELE VARCHAR(16),
VTR_IMMATRICULATION CHAR(10) NOT NULL UNIQUE,
VTR_COULEUR CHAR(16) CHECK (VALUE IN ('BLANC', 'NOIR', 'ROUGE',
'VERT', 'BLEU')))
Introduction au langage SQL
-M. HAJJI-
13
Les commandes DDL
} la commande DESCRIBE
} Syntaxe
} DESC table
Introduction au langage SQL
-M. HAJJI-
14
Les commandes DDL
} La commande ALTER
} Modifier une table existante.
} ajouter une colonne, supprimer une ou modifier une colonne
existante, par exemple pour changer le type.
} Syntaxe g n rale
} ALTER TABLE nom_table
instruction
} instruction : une commande suppl mentaire, selon laction
que lont souhaite effectuer : ajouter, supprimer ou modifier
une colonne.
Introduction au langage SQL
-M. HAJJI-
15
Les commandes DDL
Ajouter une colonne
} Syntaxe
} ALTER TABLE nom_table
ADD nom_colonne type_donnees
} Exemple
ALTER TABLE utilisateur
ADD adresse_rue VARCHAR(255)
Supprimer une colonne
} Syntaxe
} ALTER TABLE nom_table
DROP nom_colonne
} Ou (le r sultat sera le m me)
} ALTER TABLE nom_table
DROP COLUMN nom_colonne
Introduction au langage SQL
-M. HAJJI-
16
Les commandes DDL
Modifier une colonne
} MySQL
} ALTER TABLE nom_table
MODIFY nom_colonne type_donnees
Renommer une colonne
} Syntaxe
} ALTER TABLE nom_table
CHANGE colonne_ancien_nom colonne_nouveau_nom
type_donnees
Introduction au langage SQL
-M. HAJJI-
17
Les commandes DDL
} La commande DROP
} Supprimer d finitivement une table dune base de
donn es.
} Cela supprime en m me temps les ventuels index,
trigger, contraintes et permissions associ es cette table.
} Syntaxe
} DROP TABLE nom_table
DROP TABLE client_2009
Introduction au langage SQL
-M. HAJJI-
18
Les commandes DDL
} la commande RENAME
} Renommer une plusieurs tables
} Syntaxe
} RENAME TABLE tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2] ...
Introduction au langage SQL
-M. HAJJI-
19
Les commandes DML-DQL
} La commande INSERT
} Linsertion de donn es dans une table seffectue laide de la
commande INSERT INTO.
} Cette commande permet au choix dinclure une seule ligne
la base existante ou plusieurs lignes dun coup.
} INSERT INTO table VALUES ('valeur 1', 'valeur 2', ...)
} Cette syntaxe poss de les avantages et inconv nients suivants:
} Obliger de remplir toutes les donn es, tout en respectant lordre des
colonnes
} Il ny a pas le nom de colonne, donc les fautes de frappe sont
limit es. Par ailleurs, les colonnes peuvent tre renomm es sans
avoir changer la requ te
} Lordre des colonnes doit rest identique sinon certaines valeurs
prennent le risque d tre compl t e dans la mauvaise colonne
Introduction au langage SQL
-M. HAJJI-
20
Les commandes DML-DQL
} La commande INSERT
} Sp cifier les colonnes ins rer
} INSERT INTO table (nom_colonne_1, nom_colonne_2, ...
VALUES ('valeur 1', 'valeur 2', ...)
} Insertion de plus dun ligne la fois
} INSERT INTO client (prenom, nom, ville, age)
VALUES
('R becca', 'Armand', 'Saint-Didier-des-Bois', 24),
('Aim e', 'Hebert', 'Marigny-le-Ch tel', 36),
('Marielle', 'Ribeiro', 'Maill res', 27),
('Hilaire', 'Savary', 'Conie-Molitard', 58);
Introduction au langage SQL
-M. HAJJI-
21
Les commandes DML-DQL
} La commande UPDATE
} Modifications sur des lignes existantes.
} Tr s souvent cette commande est utilis e avec WHERE
pour sp cifier sur quelles lignes doivent porter la ou les
modifications.
} Syntaxe
} UPDATE table
SET colonne_1 = 'valeur 1', colonne_2 = 'valeur 2', &
WHERE condition
UPDATE client
SET rue = '49 Rue Ameline',
ville = 'Saint-Eustache-la-For t',
code_postal = '76210'
WHERE id = 2
Introduction au langage SQL
-M. HAJJI-
22
Les commandes DML-DQL
} La commande DELETE
} Supprimer des lignes dans une table.
} En utilisant cette commande associ WHERE il est
possible de s lectionner les lignes concern es qui seront
supprim es.
} Syntaxe
} DELETE FROM table
WHERE condition
DELETE FROM utilisateur
WHERE date_inscription < '2012-04-10'
} Supprimer toutes les donn es dune table:
} TRUNCATE TABLE utilisateur
Introduction au langage SQL
-M. HAJJI-
23
Les commandes DML-DQL
} La commande SELECT
} Lire des donn es issues de la base de donn es.
} Retourne des enregistrements dans un tableau de r sultat.
} Cette commande peut s lectionner une ou plusieurs colonnes dune
table.
} Syntaxe commande basique
} SELECT nom_du_champ FROM nom_de_tableau
} Syntaxe avanc e
} SELECT *
FROM table
WHERE condition
GROUP BY expression
HAVING condition
{ UNION | INTERSECT | EXCEPT }
ORDER BY expression
LIMIT count
OFFSET start
Introduction au langage SQL
-M. HAJJI-
24
Les commandes DML-DQL
Fonctions dagr gation SQL
} Permettent deffectuer des op rations statistiques sur un
ensemble denregistrement.
} tant donn es que ces fonctions sappliquent plusieurs
lignes en m me temps, elle permettent des op rations qui
servent r cup rer lenregistrement le plus petit, le plus
grand ou bien encore de d terminer la valeur moyenne
sur plusieurs enregistrement.
Introduction au langage SQL
-M. HAJJI-
25
Les commandes DML-DQL
Fonctions dagr gation SQL
} Les principales fonctions sont les suivantes :
} AVG() pour calculer la moyenne sur un ensemble denregistrement
} COUNT() pour compter le nombre denregistrement sur une table
ou une colonne distincte
} MAX() pour r cup rer la valeur maximum dune colonne sur un
ensemble de ligne.
} Cela sapplique la fois pour des donn es num riques ou alphanum rique
} MIN() pour r cup rer la valeur minimum de la m me mani re que
MAX()
} SUM() pour calculer la somme sur un ensemble denregistrement
Introduction au langage SQL
-M. HAJJI-
26
Les commandes DML-DQL
Fonctions dagr gation SQL
} Utilisation simple
SELECT fonction(colonne) FROM table
} Exemple: la fonction COUNT()
} Pour compter le nombre total de ligne dune table, il convient
Publicité
dutiliser l toile * qui signifie que lont cherche compter le
nombre denregistrement sur toutes les colonnes.
SELECT COUNT(*) FROM table
Introduction au langage SQL
-M. HAJJI-
27
Les commandes DML-DQL
Fonctions dagr gation SQL
} Utilisation avec GROUP BY
} Toutes ces fonctions prennent tout leur sens lorsquelles sont
utilis e avec la commande GROUP BY qui permet de filtrer les
donn es sur une ou plusieurs colonnes.
} Imaginons une table qui contient tout les achats sur un site avec le
montant de chaque achat pour chaque enregistrement.
} Pour obtenir le total des ventes par clients, il est possible dex cuter
la requ te suivante :
SELECT client, SUM(tarif)
FROM achat
GROUP BY client
Client
Pierre
Simon
Marie
SUM(tarif)
262
47
38
Introduction au langage SQL
-M. HAJJI-
28
Autres commandes (LCD)
} La commande GRANT
} GRANT permet d'attribuer un privil ge diff rents
utilisateurs sur diff rents objets.
} GRANT <privileges>
TO <gratifi > [ { , <gratifi > }... ]
[ WITH GRANT OPTION ]
} La clause WITH GRANT OPTION, est utilis e pour autoriser
la transmission des droits.
} La clause ALL PRIVILEGES n'a d'int r t que dans le cadre de la
transmission des droits.
GRANT SELECT
ON T_CHAMBRE
TO DUBOIS
Autorise DUBOIS lancer des ordres SQL SELECT sur
la table T_CHAMBRE. Notez l'absence du mot TABLE.
GRANT INSERT, UPDATE, DELETE
ON TABLE T_CHAMBRE
TO DUVAL, DUBOIS
Autorise DUVAL et DUBOIS modifier les donn es par
tous les ordres SQL de mise jour (INSERT, UPDATE,
DELETE) mais pas les lire !
Introduction au langage SQL
-M. HAJJI-
29
Autres commandes (LCD)
} La commande GRANT
GRANT SELECT
ON TABLE T_CMAMBRE
TO DUFOUR WITH GRANT OPTION
Autorise DUFOUR lancer des ordres SQL SELECT
sur la table T_CHAMBRE mais aussi transmettre
tout autre utilisateur les droits qu'il a acquis dans cet
ordre.
GRANT SELECT, INSERT, DELETE
ON TABLE T_CHAMBRE
TO DURAND WITH GRANT OPTION
Autorise DURAND lancer des ordres SQL SELECT,
INSERT, DELETE sur la table T_CHAMBRE.
GRANT SELECT, UPDATE
ON TABLE T_CHAMBRE
TO PUBLIC
Autorise tous les utilisateurs pr sent et venir lancer
des ordres SQL SELECT et UPDATE sur la table
T_CHAMBRE.
Introduction au langage SQL
-M. HAJJI-
30
Autres commandes (LCD)
} La commande REVOKE
} REVOKE permet de r voquer, c'est dire "retirer" un
privil ge.
} REVOKE [ GRANT OPTION FOR ] <privileges>
FROM <gratifi > [ { , <gratifi > }... ]
[ RESTRICT
| CASCADE ]
REVOKE SELECT
ON T_CHAMBRE
FROM DUBOIS
REVOKE INSERT, DELETE
ON TABLE T_CHAMBRE
FROM DUVAL, DUBOIS
Supprime le privil ge de selection de la table
T_CHAMBRE attribu DUBOIS dans l'exemple 1.
Supprime les privil ges d'insertion et de suppression de la
table T_CHAMBRE attribu DUVAL et DUBOIS dans
l'exemple 2, mais pas celui de mise jour (UPDATE).
REVOKE GRANT OPTION FOR SELECT
ON TABLE T_CMAMBRE
FROM DUFOUR
Supprime la possibilit pour DUFOUR de
transmettre le privil ge de s lection sur la table
T_CHAMBRE.
Introduction au langage SQL
-M. HAJJI-
31
Autres commandes (LCD)
} Les commandes COMMIT et ROLLBACK
Introduction au langage SQL
-M. HAJJI-
32
Les vues: cr ation
} Syntaxe de cr ation dune vue :
CREATE VIEW nomVue [(alias, alias, ...)] AS interrogation
;
} Linterrogation se fait par un SELECT
} La clause WITH CHECK OPTION sp cifie que les
insertions et mises jour r alis es travers la vue ne
pourront affecter des tuples que la vue ne peut atteindre.
Elle peut tre utilis e dans le cas dune vue construite sur
une autre vue.
} La suppression de la vue est r alis e par :
DROP VIEW <nomVue>
Introduction au langage SQL
-M. HAJJI-
33
Les vues: cr ation
} Exemple
-- la table suivante :
CREATE TABLE T_TARIF
(TRF_ID INTEGER PRIMARY KEY,
TRF_DATE DATE,
PRD_ID INTEGER,
TRF_VALEUR FLOAT)
-- permet de stocker l' volution d'un tarif, sachant que celui-ci n'est
applicable
-- pour un produit donn (PRD_ID) qu' partir de la date TRF_DATE
INSERT INTO T_TARIF VALUES (1, '1996-01-01', 53, 123.45)
INSERT INTO T_TARIF VALUES (2, '1998-09-15', 53, 128.52)
INSERT INTO T_TARIF VALUES (3, '1999-12-31', 53, 147.28)
INSERT INTO T_TARIF VALUES (4, '1997-01-01', 89, 254.89)
INSERT INTO T_TARIF VALUES (5, '1999-12-31', 89, 259.99)
INSERT INTO T_TARIF VALUES (6, '1996-01-01', 97, 589.52)
Introduction au langage SQL
-M. HAJJI-
34
Les vues: cr ation
} Exemple
-- pour des raisons de commodit d'interrogation des donn es, on
voudrait
-- faire appara tre l'intervalle de validit du tarif plut t que la date
d'application
-- la vue suivante r pond cette attente
CREATE VIEW V_TARIF
AS
SELECT TRF_ID, PRD_ID, TRF_DATE AS TRF_DATE_DEBUT,
COALESCE: Retourne la premi re
valeur non nulle dans la liste
(SELECT COALESCE(MIN(TRF_DATE) - INTERVAL 1 DAY, CURRENT_DATE)
FROM T_TARIF T2
WHERE T2.PRD_ID = T1.PRD_ID
AND T2.TRF_DATE > T1.TRF_DATE) AS TRF_DATE_FIN,
TRF_VALEUR
FROM T_TARIF T1
Introduction au langage SQL
-M. HAJJI-
35
Les s quences
} Une s quence joue le r le de distributeur de num ro. Elle
constitue une s rie unique de num ros, chaque tirage
modifiant la valeur du num ro courant.
} Excellent moyen davoir une base de donn es qui g n re
automatiquement des cl s primaires enti res uniques.
} Le privil ge syst me CREATE SEQUENCE est n cessaire
pour ex cuter cette commande.
} La s quence n'est fonctionnellement pas rattach e la table
qui l'utilise, ce qui a ses avantages et ses d fauts:
} Avantage : plusieurs tables peuvent se partager une s quence (ce qui
est fort pratique dans les h ritages, lors du traitement d'une cl par
des tables filles mutuellement exclusives)
} Inconv nient : sans une certaine rigueur, on peut ne plus savoir quel
compteur est utilis dans une table donn e, m langer les cl s, etc.....
Introduction au langage SQL
-M. HAJJI-
36
Les s quences
} Syntaxe
CREATE SEQUENCE
schema.name
INCREMENT BY x1
x2
START WITH
x3
MAXVALUE
x4
MINVALUE
CYCLE
CACHE
ORDER
x5
NOMAXVALUE
NOMINVALUE
NOCYCLE
NOCACHE
NOORDER
Introduction au langage SQL
-M. HAJJI-
37
Publicité
Les s quences
} SCHEMA est un param tre optionnel qui identifie le sch ma de base de
donn es dans lequel se place cette s rie. Par d faut, cest celui de
lutilisateur.
} NAME est obligatoire car cest le nom de la s rie.
} INCREMENT BY est optionnel. La valeur par d faut est 1. 0 nest pas
autoris . Si un entier n gatif est sp cifi , la s rie d cro tra dans lordre. Un
entier positif fera cro tre en ordre.
} START WITH est un entier optionnel qui permet la s rie de
commencer avec nimporte quelle valeur.
} MAXVALUE est un entier optionnel qui d finit une limite pour la s rie.
} NOMAXVALUE est optionnel. Ceci a pour effet de d finir la valeur maximale
croissante 1027 et la valeur maximale d croissante 1. Cette option est
loption par d faut.
} MINVALUE est un entier optionnel qui d termine le minimum dune
s rie.
} NOMINVALUE est optionnel. Ceci a pour effet de d finir la valeur minimale
croissante 1 et la valeur minimale d croissante 1026. Ceci est loption par
d faut.
Introduction au langage SQL
-M. HAJJI-
38
Les s quences
} CYCLE est une option qui permet la s rie de continuer
m me lorsque le maximum a t atteint. Dans ce cas, la s rie
suivante qui sera g n r e est celle correspondant la valeur
minimale.
} NOCYCLE est une option qui interdit la s rie de produire des
valeurs au-del des maximum ou minimum d finis. Cest la valeur par
d faut.
} CACHE est une option qui permet des num ros de s rie
d tre pr allou s et stock s en m moire pour un acc s plus
rapide. La valeur minimale est 2. Par d faut cest 20.
} NOCACHE est une option qui nautorise pas la pr allocation de
num ros de s rie.
} ORDER est une option qui assure que les num ros de s rie
seront g n r s dans lordre des demandes.
} NOORDER est une option qui nassure pas que les num ros de
s rie seront g n r s dans lordre o ils sont demand s.
Introduction au langage SQL
-M. HAJJI-
39
Les s quences
} Par d faut, lincr ment est de 1 et la valeur de d part
vaut 0.
} Une s quence est munie de 2 pseudo-colonnes :
NEXTVAL et CURRVAL.
} Leur syntaxe dappel est :
} nomSequence.NEXTVAL et
} nomSequence.CURRVAL.
CREATE SEQUENCE seq_emp;
INSERT INTO EMP VALUES (seq_emp.nextval, TOTO, 10000.00);
Introduction au langage SQL
-M. HAJJI-
40
Les s quences
} Linvocation de NEXTVAL remet automatiquement jour
la valeur de CURRVAL, ce qui permet dattribuer
chaque nouvel employ un num ro chaque fois
diff rent.
} Sous ORACLE, Pour conna tre la valeur de CURRVAL, on
peut utiliser la pseudo-table DUAL.
SELECT seq.currval FROM dual;
} La suppression de la s quence est r alis e par
DROP SEQUENCE <nomSequence>
Introduction au langage SQL
-M. HAJJI-
41
Les s quences
} Exemple
CREATE SEQUENCE adrs_seq
INCREMENT BY 5
START WITH 100;
} adrs_seq.nextval rendrait 100 pour le premier acc s et
105 pour le second.
} adrs_seq.currval renvoie la valeur courante de la s rie.
Introduction au langage SQL
-M. HAJJI-
42
Exercices (1) - Enonc
} Il sagit de d finir un sch ma de base de donn es, dy
int grer des contraintes, des vues et dy ins rer quelques
informations.
} Cr ation des tables
} Cr ez les tables du sch ma Agence de voyages, donne ci-
dessous.
} Station (nomStation, capacit , lieu, r gion, tarif)
} Activite (nomStation, libell , prix)
} Client (id, nom, pr nom, ville, r gion, solde)
} Sejour (id, station, d but , nbPlaces)
Introduction au langage SQL
-M. HAJJI-
43
Exercices (1) - Enonc
} Attention bien d finir les cl s primaires et trang res. Voici
les autres contraintes portant sur ces tables.
1.
2.
3.
4.
5.
Les donn es capacit , lieu, nom, ville, solde et nbPlaces doivent
toujours tre connues.
Les montants (prix, tarif et solde) ont une valeur par d faut 0.
Il ne peut pas y avoir deux stations dans le m me lieu et la m me
r gion.
Les r gions autoris es sont : Ocean Indien, Antilles, Europe,
Ameriques et Extreme Orient.
Le prix dune activit doit tre inf rieur au tarif de la station et
sup rieur 0.
} Conseil : donnez des noms vos contraintes PRIMARY KEY,
FOREIGN KEY et CHECK avec la clause CONSTRAINT.
Introduction au langage SQL
-M. HAJJI-
44
Exercices (1) - Correction
} Cr ation Table Station
} Station (nomStation, capacit , lieu, r gion, tarif)
CREATE TABLE Station (
nomStation VARCHAR(255) NOT NULL PRIMARY KEY,
capacite INTEGER NOT NULL,
lieu VARCHAR(100),
region VARCHAR2(100),
tarif DECIMAL (16,2) DEFAULT 0);
} Activite (nomStation, libell , prix)
CREATE TABLE Activite (
nomStation VARCHAR2(255) NOT NULL,
libelle VARCHAR2(255),
prix DECIMAL (16,2) DEFAULT 0,
CONSTRAINT PK_ACTIVITE PRIMARY KEY (nomStation, libelle),
CONSTRAINT FK_ACTIVITE_NOMSTATION FOREIGN KEY (nomStation)
REFERENCES Station (nomStation));
Introduction au langage SQL
-M. HAJJI-
45
Exercices (1) - Correction
} Cr ation Table Client
} Client (id, nom, pr nom, ville, r gion, solde)
CREATE TABLE Client (
id NUMBER NOT NULL PRIMARY KEY,
nom VARCHAR2(255) NOT NULL,
prenom VARCHAR2(255) DEFAULT 0,
} Activite (nomStation, libell , prix)
ville VARCHAR2(255) NOT NULL,
region VARCHAR2(255) DEFAULT 0,
solde DECIMAL (16,2) DEFAULT 0 NOT NULL);
NOT NULL DEFAULT 0 provoquera une erreur
Introduction au langage SQL
-M. HAJJI-
46
Exercices (1) - Correction
} Cr ation Table Sejour
} Sejour (id, station, d but , nbPlaces)
CREATE TABLE Sejour (
id NUMBER NOT NULL,
station VARCHAR2(255) NOT NULL,
} Activite (nomStation, libell , prix)
debut DATE,
nbPlaces NUMBER NOT NULL);
'2002-11-03'
ALTER TABLE Sejour
ADD CONSTRAINT FK_ID FOREIGN KEY (id) REFERENCES Client (id);
ALTER TABLE Sejour
ADD CONSTRAINT FK_STATION FOREIGN KEY (station) REFERENCES
Station (nomStation);
ALTER TABLE Sejour
ADD CONSTRAINT PK_IDSTATIONDEBUT PRIMARY KEY (id,station,debut);
Introduction au langage SQL
-M. HAJJI-
47
Exercices (1) - Correction
3.
Il ne peut pas y avoir deux stations dans le m me lieu et
la m me r gion.
ALTER TABLE Station
ADD CONSTRAINT UN_LIEU_REGION
UNIQUE(lieu,region);
4.
Les r gions autoris es sont : Ocean Indien, Antilles,
Europe, Ameriques et Extreme Orient.
ALTER TABLE Station
ADD CONSTRAINT CHK_REGION CHECK (region IN
('Ocean Indien', 'Antilles', 'Europe', 'Ameriques' , 'Extreme Orient'));
Introduction au langage SQL
-M. HAJJI-
48
Exercices (1) - Correction
5.
Le prix dune activit doit tre inf rieur au tarif de la
station et sup rieur 0.
ALTER TABLE Activite
ADD CONSTRAINT CHK_PRIX CHECK (prix >0);
Introduction au langage SQL
-M. HAJJI-
49
Exercices (2) - Enonc
} Soit la base relationnelle de donn es PUF de sch ma :
} U(NumU, NomU, VilleU)
} P(NumP, NomP, Couleur, Poids)
} F(NumF, NomF, Statut, VilleF)
} PUF(NumP, NumU, NumF, Quantit )
} d crivant le fait que (avec des DF videntes) :
} U : une usine est d crite par son num ro NumU, son nom NomU et
la ville VilleU o elle est situ e
} P : un produit est d crit par son num ro NumP, son nom NomP, sa
couleur et son poids
} F : un fournisseur est d crit par son num ro NumP, son nom NomF,
son statut (sous-traitant, client&) et la ville VilleF o il est domicili
Publicité
} PUF : le produit de num ro NumP a t d livr lusine de num ro
NumU par le fournisseur de num ro NumF dans une quantit
donn e
Introduction au langage SQL
-M. HAJJI-
50
Exercices (2) - Enonc
1.
2.
Ajouter un nouveau fournisseur avec les attributs de votre choix
Supprimer tous les produits de couleur noire et de num ros compris
entre 100 et 1999
Changer la ville du fournisseur 3 par Toulouse
3.
4. Donnez le num ro, le nom, la ville de toutes les usines
5. Donnez le num ro, le nom, la ville de toutes les usines de Paris
6. Donnez les num ros des fournisseurs qui approvisionnent lusine de
num ro 2 en produit de num ro 100
7. Donnez les noms et les couleurs des produits livr s par le fournisseur de
num ro 2
8. Donnez les num ros des fournisseurs qui approvisionnent lusine de
num ro 2 en un produit rouge
9. Donnez les noms des fournisseurs qui approvisionnent une usine de
Paris ou de Cr teil en produit rouge
10. Donnez les num ros des produits livr s une usine par un fournisseur
de la m me ville
Introduction au langage SQL
-M. HAJJI-
51
Exercices (2) - Correction
1.
2.
3.
4.
5.
6.
7.
INSERT INTO F VALUES (45, Alfred, Sous-traitant, Chalon)
DELETE P WHERE NumP>=100 AND NumP<=199 AND Couleur=Noire
UPDATE F SET VilleF=Toulouse WHERE NumF=3
SELECT * FROM U
SELECT * FROM U WHERE VilleU= "Paris"
SELECT NumF FROM PUF WHERE NumU=2 AND NumP=100
SELECT DISTINCT NomP, Couleur FROM P, PUF WHERE PUF.NumP=P.NumP
AND NumF=2
Ou bien
SELECT NomP, Couleur FROM P WHERE NumP IN (SELECT NumP FROM PUF
WHERE NumF=2)
Introduction au langage SQL
-M. HAJJI-
52
Exercices (2) - Correction
8.
9.
SELECT DISTINCT NumF FROM PUF, P WHERE
Couleur="Rouge" AND PUF.NumP=P.NumP AND NumU=2
Ou bien
SELECT DISTINCT NumF FROM PUF WHERE NumP IN
(SELECT NumP FROM P WHERE Couleur="Rouge") AND
NumU=2
SELECT NomF FROM PUF, P, F, U WHERE Couleur=Rouge
AND PUF.NumP=P.NumP AND PUF.NumF=F.NumF AND
PUF.NumU=U.NumU AND (U.VilleU IN (Paris,Cr teil))
10. SELECT DISTINCT NumP FROM PUF, F, U WHERE
PUF.NumF=F.NumF AND PUF.NumU=U.NumU AND
U.VilleU=F.VilleF
Introduction au langage SQL
-M. HAJJI-
53
Exercices (2) - Enonc
11. Donnez les num ros des produits livr s une usine de Paris par
un fournisseur de Paris.
12. Donnez les num ros des usines qui ont au moins un fournisseur
qui nest pas de la m me ville
13. Donnez les num ros des fournisseurs qui approvisionnent la fois
des usines de num ros 2 et 3
14. Donnez les num ros des usines qui utilisent au moins un produit
disponible chez le fournisseur de num ro 3 (cest- -dire un
produit que le fournisseur livre mais pas n cessairement cette
usine)
15. Donnez le num ro du produit le plus l ger (les num ros si
plusieurs produits ont ce m me poids)
16. Donnez le num ro des usines qui ne re oivent aucun produit
rouge dun fournisseur parisien
17. Donnez les num ros des fournisseurs qui fournissent au moins un
produit fourni par au moins un fournisseur qui fournit au moins
un produit rouge
Introduction au langage SQL
-M. HAJJI-
54
Exercices (2) - Correction
11.
12.
13.
SELECT DISTINCT NumP FROM PUF, F, U WHERE PUF.NumF=F.NumF
AND PUF.NumU=U.NumU AND U.Ville=F.Ville AND U.Ville=Paris
Ou bien
SELECT DISTINCT NumP FROM PUF WHERE NumF IN
(SELECT NumF FROM F WHERE Ville=Paris) AND NumU IN
(SELECT NumU FROM U WHERE Ville=Paris)
SELECT DISTINCT PUF.NumU FROM PUF, F, U WHERE
PUF.NumF=F.NumF AND PUF.NumU=U.NumU AND U.Ville<>F.ville
Ou bien
SELECT DISTINCT NumU FROM PUF WHERE
NumF=ANY(SELECT NumF FROM F, U WHERE PUF.NumF=F.NumF AND
PUF.NumU=U.NumU AND F.Ville<>U.Ville)
SELECT DISTINCT First.NumF FROM PUF First, PUF Second
WHERE First.NumF=Second.NumF AND First.NumU=2 AND Second.NumU=3
Ou bien
SELECT DISTINCT NumF FROM PUF WHERE NumF IN
(SELECT NumF FROM PUF WHERE Nu=2) AND Nu=3
14.
SELECT DISTINCT NumU FROM PUF WHERE NumP IN (SELECT
NumP FROM PUF WHERE NumF=3)
Introduction au langage SQL
-M. HAJJI-
55
Exercices (2) - Correction
15.
16.
17.
SELECT NumP FROM P WHERE Poids IN (SELECT MIN(Poids) FROM
P)
Ou bien
SELECT NumP FROM P p1 WHERE NOT EXISTS
(SELECT * FROM P WHERE P1.Poids>Poids)
SELECT NumU FROM U WHERE NumU NOT IN (SELECT NumU
FROM
PUF, F, P WHERE PUF.NumP=P.NumP AND PUF.NumF=F.NumF AND
Couleur=Rouge AND Ville=Paris)
SELECT DISTINCT PUF.NumF FROM PUF, PUF PUF1, PUF PUF2,
P WHERE Couleur=Rouge AND P.NumP=PUF2.NumP AND
PUF2.NumF=PUF1.NumF AND PUF1.NumP=PUF.NumP
Ou bien
SELECT DISTINCT NumF FROM PUF WHERE NumP IN
(SELECT NumP FROM PUF WHERE NumF IN (SELECT NumF FROM
PUF WHERE NumP IN (SELECT NumP FROM P WHERE
Couleur=Rouge)))
Introduction au langage SQL
-M. HAJJI-
56
Exercices (2) - Enonc
18. Donnez tous les triplets (VilleF, NumP, VilleU) tels quun
fournisseur de la premi re ville VilleF approvisionne une
usine de la deuxi me ville VilleU avec un produit NumP
19. M me question que pr c demment mais sans les
triplets o les deux villes sont identiques
20. Donnez les num ros des produits qui sont livr s
toutes les usines de Paris
21. Donnez les num ros des fournisseurs qui
approvisionnent toutes les usines avec un m me produit
22. Donnez les num ros des usines qui sapprovisionnent
uniquement chez le fournisseur de num ro 3
Introduction au langage SQL
-M. HAJJI-
57
Exercices (2) - Correction
18. SELECT DISTINCT F.Ville, NumP, U.Ville
FROM PUF, U, F
WHERE PUF.NumF=F.NumF AND PUF.NumU=U.NumU
19. SELECT DISTINCT F.Ville, NP, U.Ville
FROM PUF, U, F
WHERE F.Ville<>U.Ville AND PUF.NumF=F.NumF AND
PUF.NumU=U.NumU
20. SELECT NumP
FROM PUF
WHERE NOT EXISTS
(SELECT NumU FROM U WHERE
NOT EXISTS (SELECT * FROM PUF
WHERE NOT (Ville=Paris) OR
(P.NumP=PUF.NumP AND U.NumU=PUF.NumU))
Introduction au langage SQL
-M. HAJJI-
58
Exercices (2) - Correction
21.
SELECT NF
FROM PUF
WHERE NOT EXISTS
(SELECT NumU FROM U
WHERE NOT EXISTS
(SELECT * FROM PUF PUF1 WHERE F.NumF=PUF1.NF AND
U.NumU=PUF1.NumU AND PUF.NumP=PUF1.NumP))
Ou bien
SELECT NumF
FROM F
WHERE EXISTS (SELECT NumP FROM P WHERE NOT EXISTS
(SELECT NumU FROM U WHERE NOT EXISTS (SELECT * FROM PUF
WHERE F.NumF=PUF.NumF AND U.NumU=PUF.NumU AND
P.NumP=PUF.NumP)))
22.
SELECT NumU
FROM U
WHERE NumU NOT IN (SELECT NumU FROM PUF WHERE
NumF<>3)
Introduction au langage SQL
-M. HAJJI-
59
Exercices (3) - Enonc
} On consid re le sch ma relationnel suivant qui mod lise
une application sur la gestion de livres et de disques dans
une m diath que:
} Disque(CodeOuv, Titre, Style, Pays, Ann e, Producteur)
} E_Disque(CodeOuv, NumEx, DateAchat, Etat)
} Livre(CodeOuv, Titre,...