Introduction au langage SQL
Mehdi HAJJI [email protected]
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 à l’aide de requêtes.
le DCL (Data Control Language), qui permet de gérer les droits d’accès
aux données.
A cela s’ajoute 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 [précision_max] 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 :: [CONSTRAINT nom_contrainte] [NOT] NULL | UNIQUE | PRIMARY KEY | CHECK ( prédicat_de_colonne ) | FOREIGN KEY [colone] 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 d’enregistrer une valeur nulle pour une colonne. DEFAULT : attribuer une valeur par défaut si aucune données n’est indiquée pour cette colonne lors de l’ajout d’une 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
n’est pas nulle
nom : nom de l’utilisateur 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 l’utilisateur sous 255 caractères au
maximum
ville : idem pour la ville code_postal : 5 caractères du code postal nombre_achat : nombre d’achat 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 (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 l’action que l’ont 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 d’une base de
données.
Cela supprime en même temps les éventuels index,
trigger, contraintes et permissions associées à cette table.
Publicité
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 L’insertion de données dans une table s’effectue à l’aide de la
commande INSERT INTO.
Cette commande permet au choix d’inclure une seule ligne à
la base existante ou plusieurs lignes d’un 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 l’ordre des
colonnes
Il n’y 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
L’ordre 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 d’un 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 d’une 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 d’une
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 d’agrégation SQL Permettent d’effectuer des opérations statistiques sur un
ensemble d’enregistrement.
Étant données que ces fonctions s’appliquent à plusieurs
lignes en même temps, elle permettent des opérations qui servent à récupérer l’enregistrement 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 d’agrégation SQL Les principales fonctions sont les suivantes :
AVG() pour calculer la moyenne sur un ensemble d’enregistrement COUNT() pour compter le nombre d’enregistrement sur une table
ou une colonne distincte
MAX() pour récupérer la valeur maximum d’une colonne sur un
ensemble de ligne. Cela s’applique à 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 d’enregistrement
Introduction au langage SQL
-M. HAJJI-
26
Les commandes DML-DQL
Fonctions d’agrégation SQL Utilisation simple
SELECT fonction(colonne) FROM table
Exemple: la fonction COUNT()
Pour compter le nombre total de ligne d’une table, il convient d’utiliser l’étoile “*” qui signifie que l’ont cherche à compter le nombre d’enregistrement sur toutes les colonnes.
SELECT COUNT(*) FROM table
Introduction au langage SQL
-M. HAJJI-
27
Les commandes DML-DQL Fonctions d’agrégation SQL Utilisation avec GROUP BY
Toutes ces fonctions prennent tout leur sens lorsqu’elles 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 d’exé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 d’une vue :
CREATE VIEW nomVue [(alias, alias, ...)] AS interrogation [WITH CHECK OPTION] ;
L’interrogation 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 d’une 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
Publicité
-- 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 d’avoir 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
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, c’est celui de l’utilisateur.
NAME est obligatoire car c’est le nom de la série. INCREMENT BY est optionnel. La valeur par défaut est 1. 0 n’est pas
autorisé. Si un entier négatif est spécifié, la série décroîtra dans l’ordre. Un entier positif fera croître en ordre.
START WITH est un entier optionnel qui permet à la série de
commencer avec n’importe 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 l’option par défaut.
MINVALUE est un entier optionnel qui détermine le minimum d’une
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 l’option 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. C’est 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 c’est 20. NOCACHE est une option qui n’autorise 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 l’ordre des demandes. NOORDER est une option qui n’assure pas que les numéros de
série seront générés dans l’ordre où ils sont demandés.
Introduction au langage SQL
-M. HAJJI-
39
Les séquences
Par défaut, l’incré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 d’appel 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
L’invocation de NEXTVAL remet automatiquement à jour
la valeur de CURRVAL, ce qui permet d’attribuer à 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 s’agit de définir un schéma de base de données, d’y
intégrer des contraintes, des vues et d’y 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 d’une 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 d’une 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é PUF : le produit de numéro NumP a été délivré à l’usine 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 l’usine 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 l’usine de
Publicité
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 n’est 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 (c’est-à-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 d’un 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 qu’un 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 s’approvisionnent
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, Editeur, Collection) E_Livre(CodeOuv, NumEx, DateAchat, Etat) Auteurs(CodeOuv, Identité) Abonne(NumAbo, Nom, Prénom, Rue, Ville, CodeP, Téléphone) Prêt(CodeOuv, NumEx, DisqueOuLivre, NumAbo, DatePret) Personnel(NumEmp, Nom, Prénom, Adresse, Fonction, Salaire)
Introduction au langage SQL
-M. HAJJI-
60
Exercices (3) - Enoncé
1. Quel est le contenu de la relation Livre ? 2. Quels sont les titres des romans édités par Gava-Editor 3. Quelle est la liste des titres que l’on retrouve à la fois
comme titre de disque et titre de livre ?
4. Quelle est l’identité des auteurs qui ont fait des disques
et écrit des livres ?
5. Quels sont les différents style de disques proposés ? 6. Quel est le salaire annuel des membres du personnel gagnant plus de 20000 euros en ordonnant le résultat par salaire descendant et nom croissant ?
Introduction au langage SQL
-M. HAJJI-
61
Exercices (3) - Correction
1.
2.
3.
4.
SELECT * FROM Livre
SELECT Titre FROM Livre WHERE Editeur="Droit-Edition" AND Genre="Polar"
SELECT D.Titre FROM Disque D, Livre L WHERE D.Titre=L.Titre
SELECT A1.Identité FROM Disque D, Livre L, Auteur A1, Auteur A2 WHERE D.CodeOuv=A1.CodeOuv AND L.CodeOuv=A2.CodeOuv AND A1.Identité=A2.Identité
Introduction au langage SQL
-M. HAJJI-
62
Exercices (3) - Correction
5.
6.
SELECT DISTINCT Style FROM Disque
SELECT Nom, Prénom, Salaire*12 AS Salaire_Annuel FROM Personnel WHERE Salaire_Annuel>20000 ORDER BY Salaire DESC, Nom ASC
Introduction au langage SQL
-M. HAJJI-
63
Exercices (3) - Enoncé 7. Donnez le nombre de prêts en cours pour chaque famille en considérant qu’une famille regroupe des personnes de même nom et possédant le même numéro de téléphone ? 8. Quel est le code du disque dont la médiathèque possède le
plus grand nombre d’exemplaire ?
9. Quels sont les éditeurs pour lesquels l’attribut Collection
n’a pas été renseigné ?
10. Quels sont les abonnés dont le nom contient la chaîne «
ALDO » et habitant en Isère ?
11. Quel est le nombre de prêts en cours ? 12. Quels sont les salaires minimum, maximum et moyen des
employés exerçant une fonction de bibliothécaire ? 13. Quel est le nombre de genres de livres différents ? 14. Quel est le nombre de disque acheté en 1998 ?
Introduction au langage SQL
-M. HAJJI-
64
Exercices (3) - Correction
7.
8.
9.
SELECT Nom, Téléphone, COUNT(*) FROM Abonne A, Prêt P WHERE A.NumAbo=P.NimAbo GROUP BY Nom, Téléphone
SELECT CodeOuv FROM E_Disque GROUP BY CodeOuv HAVING COUNT(*)=(SELECT MAX(COUNT(*)) FROM E_Disque GROUP BY CodeOuv)
SELECT Editeur FROM Livre WHERE Collection IS NULL
Introduction au langage SQL
-M. HAJJI-
65
Exercices (3) - Correction
10.
11.
12.
13.
14.
SELECT * FROM Abonne WHERE Nom=‘%ALDO%’ AND CodeP=’38--’
SELECT COUNT(*) FROM Prêt
SELECT MIN(Salaire), MAX(Salaire), AVG(Salaire) FROM Personnel WHERE Fonction="bibliothécaire"
SELECT COUNT(DISTINCT Genre) FROM Livre
SELECT COUNT(*) FROM E_Disque WHERE DateAchat BETWEEN ’01-Jan-2006’ AND ’10-Dec-2007’
Introduction au langage SQL
-M. HAJJI-
66
Exercices (3) - Enoncé 15. Quel est le salaire annuel des membres du personnel
gagnant plus de 20000 euros ?
16. Quel est le nom, prénom et l’adresse des abonnés ayant
emprunté un disque le ’12/01/2006’ ?
17. Quels sont les titres des livres et des disque