SQL: Structured Query Language

• Les 4 dernières CI sont définissables au niveau

colonne ou au niveau table.

R.KHCHERIF

19

Exemple

• Si on considère le schéma suivant :

MAGASIN(NumMag, Adresse, Surface) PRODUIT(NumProd, DesProd, Couleur, Poids, Qte_Stk, #CodMag)

• La commande pour la création de la table Magasin étant :

Create Table Magasin (NumMag number(6) primary Key, Adresse varchar(30), Surface number(7,3));

• La commande pour la création de la table Produit peut être

écrite de deux façons:

R.KHCHERIF

20

Solution 1: clé étrangère comme contrainte de table

CREATE TABLE Produit

(Numprod number(6) primary key, Desprod varchar(15), Couleur char, Poids number(8,3), Qte_stk number(7,3), Qte_seuil number(7,3), Prix number(10,3), CodMag number(6), Constraint FK_Produit Foreing Key (CodMag) references Magasin(NumMag));

R.KHCHERIF

21

Solution 2: clé étrangère comme contrainte de colonne

CREATE TABLE Produit

(Numprod number(6) primary key, Desprod varchar(15), Couleur char, Poids number(8,3), Qte_stk number(7,3), Qte_seuil number(7,3), Prix number(10,3), CodMag number(6) references Magasin(NumMag));

R.KHCHERIF

22

Contrainte de valeur avec la clause check

• Permet de limiter les valeurs possibles pour certaine lors des

vérifiant se

en contrôle

une fera

colonne une condition. Le insertions des données. Constraint (colonne condition)

nom_contrainte

CHECK

• La condition sur la colonne peut utiliser :

– un opérateur de comparaison – la clause between val1 and val2 R.KHCHERIF – la clause in (liste de valeurs)

23

Exemple

• On suppose que le poids d’un produit doit être positif. La commande de création de la table Produit devient :

CREATE TABLE Produit

(Numprod number(6) primary key, Desprod varchar(15), Couleur char, Poids number(8,3), Qte_stk number(7,3), Qte_seuil number(7,3), Prix number(10,3), CodMag number(6) references Magasin(NumMag), Constraint Ck1_Produit CHECK (Poids >=0));

R.KHCHERIF

24

Exemple

• On suppose que pour un produit, la quantité en stock doit être supérieure ou égale à la quantité seuil. La commande de création de la table Produit devient :

CREATE TABLE Produit

(Numprod number(6) primary key,

Desprod varchar(15), Couleur char, Poids number(8,3), Qte_stk number(7,3), Qte_seuil number(7,3), Prix number(10,3), CodMag number(6) references Magasin(NumMag), Constraint Ck1_Produit CHECK (Poids >=0), Constraint Ck2_Produit CHECK (Qte_stk >= Qte_seuil));

R.KHCHERIF

25

Exemple

• On suppose que la quantité d’un produit doit être comprise entre 0 et

1000. La commande de création de la table Produit devient :

CREATE TABLE Produit

(Numprod number(6) primary key,

Desprod varchar(15), Couleur char, Poids number(8,3), Qte_stk number(7,3), Qte_seuil number(7,3), Prix number(10,3), CodMag number(6) references Magasin(NumMag), Constraint Ck1_Produit CHECK (Poids >=0), Constraint Ck2_Produit CHECK (Qte_stk >= Qte_seuil), Constraint Ck3_Produit CHECK (Qte_stk between 0 and 1000));

R.KHCHERIF

26

Exemple

• On suppose que la couleur d’un produit ne peut être que ‘N’, ‘G’, ou ‘B’.

La commande de création de la table Produit devient :

CREATE TABLE Produit

(Numprod number(6) primary key, Desprod varchar(15), Couleur char, Poids number(8,3), Qte_stk number(7,3), Qte_seuil number(7,3), Prix number(10,3), CodMag number(6) references Magasin(NumMag), Constraint Ck1_Produit CHECK (Poids >=0), Constraint Ck2_Produit CHECK (Qte_stk >= Qte_seuil), Constraint Ck3_Produit CHECK (Qte_stk between 0 and 1000), Constraint Ck4_Produit CHECK (Couleur IN (‘N’, ‘G’, ‘B’)));

R.KHCHERIF

27

Exercice

• Pilote (NPil, Nom, NbHvol, #Compa) • Compagnie(NComp, NomComp, AdrComp, Ville ) • Créer les deux tables avec les contraintes nécessaires. • Rq : Les contraintes doivent être créée au niveau

table.

R.KHCHERIF

28

Correction

CREATE Table Compagnie (NComp char(4), NomComp varchar2(15), AdrComp varchar2(20), Ville varchar2(15), Constraint pk_Compagnie PRIMARY KEY (NComp));

R.KHCHERIF

29

Correction

CREATE Table Pilote (NPil CHAR(6), Nom CHAR(15) NOT NULL, NbHvol NUMBER(7,2), Compa CHAR (4), Constraint pk_Pilote PRIMARY KEY (NPil), Constraint ck_NbHvol CHECK (NbHvol BETWEEN 0 And 2000), Constraint un_nom UNIQUE (nom), Constraint FK_Pil_Compa FOREIGN KEY (Compa) REFERENCES Compagnie (NComp));

R.KHCHERIF

30

3- Modification de la structure d’une table • Les trois possibilités de modification de la structure de table sous ORACLE permettent soit d'ajouter des colonnes, soit de modifier la structure d'une colonne, soit de supprimer des colonnes existantes.

1ère forme : Ajout de nouvelles colonnes à une table • Syntaxe :

ALTER TABLE nom_table ADD (col1 type [(taille)] [null / not null] , col2 type [(taille)] [null / not null] , . . . coln type [(taille)] [null / not null] ) ;

• Exemple :

Supposons qu'on veut ajouter une colonne type_clt à la table client : ALTER TABLE CLIENT ADD type_clt char(3) ;

R.KHCHERIF

31

2ème forme : Modification de la structure d'une colonne existante

• Syntaxe :

ALTER TABLE nom_table MODIFY (col1 type [(taille)] [null / not null] , col2 type [(taille)] [null / not null] , . . . coln type [(taille)] [null / not null] ) ;

• Remarque :

Pour modifier le nom d'une colonne : RENAME COLUMN nom_table.ancien_nom TO nom_table.nouveau_nom ;

• Exemple :

Supposons qu'on veut changer le type_clt de char(3) en char(5) : ALTER TABLE CLIENT MODIFY type_clt char(5) default ‘Monas’;

R.KHCHERIF

32

3ème forme : Suppression de colonnes existantes

•

Syntaxe :

ALTER TABLE nom_table DROP ( col1 , col2 ,…, coln ) ;

• Exemple :

Supposons qu'on veut supprimer le champ ville de la table Magasin : ALTER TABLE Magasin DROP ville ;

R.KHCHERIF

33

4ème forme : Ajout d'une contrainte

• Syntaxe :

ALTER TABLE nom_table ADD Constraint Def_de_contrainte ;

• Exemple :

Ajouter à la relation Magasin la contrainte suivante : la surface doit être comprise entre 10 et 100 m2 ALTER TABLE Magasin ADD Constraint ck1_magasin check(surface between 10 and 100) ;

R.KHCHERIF

34

5ème forme : Suppression de contraintes existantes

5.1 Suppression d'une contrainte clé primaire : • On peut effacer une clé primaire. La commande est :

ALTER TABLE nom_table DROP PRIMARY KEY [CASCADE] ;

• Remarque : • L'option cascade est ajoutée pour pouvoir supprimer une clé

primaire référencée.

• Exemple :

Supprimer la contrainte clé primaire de la table magasin ALTER TABLE magasin DROP PRIMARY KEY CASCADE ;

R.KHCHERIF

35

5.2 Suppression d'une contrainte autre que la clé

primaire :

• On peut effacer une clé étrangère. La commande est :

ALTER TABLE nom_table DROP CONSTRAINT nom_contrainte ;

• Où Le nom de la contrainte c'est celui de la contrainte à supprimer . • Exemple :

Supprimer la contrainte spécifiant les couleurs possibles pour les produits ALTER TABLE produit DROP CONSTRAINT Ck4_Produit ;

• Remarque : • Pour retrouver les différentes contraintes avec leur propriétés,

on peut utiliser la commande suivante : Select * from user_constraints [where table_name = 'NOMTABLE' ]; Il est à remarquer que pour cette commande, le nom de la table doit être écrit en majuscule.

R.KHCHERIF

36

Suppression de tables

• Syntaxe :

DROP TABLE nom_table ;

• Exemple : • Supposons qu'on veut supprimer la table client_tunis :

DROP TABLE client_tunis ;

• Remarque :

les

toutes

Contrainte de suppression de table : permet de supprimer d'intégrité référentielles qui se reflètent aux clés uniques ou primaires de la table à supprimer. La commande est : DROP CONSTRAINTS;

CASCADE

contraintes

nom_table

TABLE

R.KHCHERIF

37

Renommage et création de synonymes de tables

• Pour changer le nom d'une table existante la commande est : • Syntaxe :

RENAME ancien_nom TO nouveau_nom ;

•

Il est également possible de donner à une même table plusieurs noms différents appelés synonymes.

• Syntaxe :

CREATE

SYNONYM

nom_synonyme

FOR

nom_table ;

• Pour supprimer un synonyme donné, on utilise la commande : • Syntaxe :

DROP SYNONYM nom_synonym ;

• Remarque :

La suppression d'une table implique la suppression des R.KHCHERIF 38 synonymes correspondants.

III. Le Langage de Manipulation de données (LMD SQL)

III.1 Syntaxe générale d’une requête: SELECT [ALL|DISTINCT] attribut(s) FROM table(s) [WHERE condition] [GROUP BY attribut(s) [HAVING

condition]]

[ORDER BY attribut(s) [ASC|DESC]]; • Tous les tuples d’une table ex. SELECT * FROM Client; • Tri du résultat ex. Par ordre alphabétique inverse de nom SELECT * FROM Client ORDER BY Nom DESC; R.KHCHERIF

39

BD exemple

• client ( numcli , nom, prenom, adresse,

codepost, ville, tel);

• produit ( numprod, designation, prixunit,

qtestock );

• commande ( numcom, numcli, idvendeur,

datec, quantite, numprod);

• vendeur ( idvendeur, nomvendeur, qualité,

salaire, commission));

R.KHCHERIF

40

Interrogation des données

• Calculs ex. Calcul de prix TTC SELECT PrixUni+PrixUni*0.206 prixttc FROM

Produit; • Projection ex. Noms et Prénoms des clients, uniquement SELECT Nom, Prenom FROM Client; • Restriction ex. Clients qui habitent à Tunis SELECT * FROM Client WHERE Ville = ‘Tunis’;

R.KHCHERIF

41

Interrogation des données

• ex. Commandes en quantité au moins égale à 3 SELECT * FROM Commande WHERE Quantite >= 3; • ex. Produits dont le prix est compris entre 50 et 100 DT SELECT * FROM Produit WHERE PrixUni BETWEEN 50 AND 100; • ex. Commandes en quantité indéterminée SELECT * FROM Commande WHERE Quantite IS

NULL;

R.KHCHERIF

42

Interrogation des données

• ex. Clients habitant une ville dont le nom se termine par Menzel SELECT * FROM Client WHERE Ville LIKE ‘%Menzel’; • ‘Menzel%’  commence par Menzel • ‘%Men%’  contient le mot Men

– le joker ‘_’ peut être remplacé par n’importe quel

caractère

– le joker ‘%’ peut être remplacé par 0, 1, 2, … caractères

R.KHCHERIF

43

Interrogation des données

• ex. Prénoms des clients dont le nom est Mohamed, Ahmed ou Mahmoud SELECT Prenom FROM Client WHERE Nom IN (‘Ahmed’, ‘Mahmoud’, ‘Mohamed’); • spécifie une liste de constantes dont une doit égaler le premier

terme pour que la condition soit vraie

• pour des NUMBER, CHAR, VARCHAR, DATE

• NB : Possibilité d’utiliser la négation pour tous ces

prédicats : NOT BETWEEN, NOT NULL, NOT LIKE, NOT IN.

R.KHCHERIF

44

Fonctions d’agrégat

• Elles opèrent sur un ensemble de valeurs, fournissent une

valeur unique – AVG() : moyenne des valeurs – SUM() : somme des valeurs – MIN(), MAX() : valeur minimum, valeur maximum – COUNT() : nombre de valeurs • ex. Moyenne des prix des produits SELECT AVG(PrixUni) FROM Produit;

R.KHCHERIF

45

Opérateur DISTINCT

• ex. Nombre total de commandes SELECT COUNT(*) FROM Commande; SELECT COUNT(NumCli) FROM

Commande;

• ex. Nombre de clients ayant passé

commande

SELECT COUNT( DISTINCT NumCli) FROM Commande;

R.KHCHERIF

46

Exemple

• Table COMMANDE (simplifiée) Quantite NumCli 1 5 2

Date 22/09/99 22/09/99 22/09/99

1 3 3

 COUNT(NumCli) Résultat = 3  COUNT(DISTINCT NumCli)  Résultat = 2

R.KHCHERIF

47

Jointure

• Consiste en un produit cartésien ou certaines lignes seulement sont

sélectionnées via la clause WHERE ex. Liste des commandes avec le nom des clients

• SELECT Nom, Date, Quantite FROM Client, Commande WHERE Client.NumCli =

Commande.NumCli; ex. Idem avec le numéro de client en plus

• SELECT C1.NumCli, Nom, Date, Quantite FROM Client C1, Commande C2 WHERE C1.NumCli = C2.NumCli ORDER BY Nom; SELECT C1.NumCli, Nom, Date, Quantite FROM Client C1, Commande C2 WHERE

C1.NumCli = C2.NumCli ORDER BY 2;

• NB : Utilisation d’alias (C1 et C2) pour alléger l’écriture + tri par nom.

R.KHCHERIF

48

Jointure exprimée avec le prédicat IN

• ex. Nom des clients qui ont commandé le

23/09

SELECT Nom FROM Client WHERE NumCli IN (SELECT NumCli FROM Commande

WHERE DateC = ‘23-09-2000’ );

NB : Il est possible d’imbriquer des requêtes.

R.KHCHERIF

49

Prédicats EXISTS / NOT EXISTS

• ex. Clients qui ont passé au moins une commande

[n ’ont passé aucune commande]

SELECT * FROM Client C1 WHERE [NOT] EXISTS ( SELECT * FROM

Commande C2 WHERE C1.NumCli = C2.NumCli );

R.KHCHERIF

50

Prédicats ALL / ANY

• ex. Numéros des clients qui ont commandé au moins un produit en quantité supérieure à chacune [à au moins une] des quantités commandées par le client n° 1.

SELECT DISTINCT NumCli FROM Commande WHERE Quantite > ALL [ANY] ( SELECT Quantite FROM Commande WHERE NumCli = 1 );

R.KHCHERIF

51

Groupement

• permet de créer des groupes de lignes pour

appliquer des fonctions d’agrégat sur les groupes – il est possible de créer des groupes sur plusieurs attributs

• ex. Quantité totale commandée par chaque client SELECT NumCli, SUM(Quantite) FROM Commande GROUP

BY NumCli;

• ex. Nombre de produits différents commandés... SELECT NumCli, COUNT(DISTINCT NumProd) FROM Commande GROUP BY NumCli;

R.KHCHERIF

52

Groupement

• ex. Quantité moyenne commandée pour les produits

faisant l’objet de plus de 3 commandes

SELECT NumProd, AVG(Quantite) FROM Commande GROUP BY NumProd HAVING

COUNT(*)>3;

Attention : La clause HAVING ne s’utilise qu’avec GROUP BY.

R.KHCHERIF

53

Opérations ensemblistes

•

INTERSECT, MINUS, UNION – les deux tableaux opérandes doivent avoir une description

identique:

• nombre de colonnes • domaines des valeurs de colonnes

– structure générale

• co-requête OPERATEUR co-requête • ou chaque co-requête est une instruction SELECT

ex. Numéro des produits qui soit ont un prix inférieur à 100 DT, soit

ont été commandés par le client n° 2

SELECT NumProd FROM Produit WHERE PrixUni<100 UNION SELECT NumProd FROM Commande WHERE NumCLi=2;

R.KHCHERIF

54

Les sous-requêtes • mécanisme très puissant mais d’un emploi

subtil !

• = une requête SELECT insérée dans la clause WHERE d’une autre requête SELECT

• bien sur, une sous-requête peut contenir

une sous-sous-requête !

R.KHCHERIF

55

Les sous-requêtes

• peuvent retourner – une seule valeur – une liste de valeurs

• une sous-requête constitue le second membre d’une comparaison dans la clause WHERE

R.KHCHERIF

56

Les sous-requêtes retournant une valeur

SELECT last_name, salary FROM s_emp WHERE salary >= (SELECT AVG(salary) FROM

s_emp);

LAST_NAME SALARY ------------------------- ------------------ 2500 Velasquez Ngao Nagayama 1400 Quick-To-See 1450 ...

R.KHCHERIF

1450

sous-requête

57

Les sous-requêtes retournant une liste de valeurs SELECT last_name, salary FROM s_emp WHERE salary >= ALL (SELECT salary FROM s_emp WHERE dept_id = 10) ;

LAST_NAME SALARY ------------------------- ------------------- Velasquez Ngao Quick-To-See 1450 1550 Ropeburn 1490 Giljum

2500

1450

R.KHCHERIF

58

Contraintes des sous-requêtes

• clause ORDER BY y est interdite • chaque sous-requête doit être entourée de

parenthèses

• clause SELECT d’une sous-requête ne peut

contenir qu’un seul attribut

• les attributs définis dans la requête principale peuvent être utilises dans la sous-requête • les attributs définis dans la sous-requête ne peuvent pas être utilises dans la requête principale

R.KHCHERIF

59

Opérateurs de comparaison et les sous-requêtes

• dans le cas de sous-requêtes retournant une

seule valeur, les opérateurs classiques (<, >, …) peuvent être appliques

• dans le cas de sous-requêtes retournant une

liste de valeurs, il faut utiliser les quantificateurs – ANY: l’expression est vraie si une des valeurs de la

sous-requête vérifie la comparaison

– ALL: l’expression est vraie si toutes les valeurs de la

sous-requête vérifient la comparaison

• Note: IN équivaut à = ANY

R.KHCHERIF

60

Les sous-requêtes multiples • la clause WHERE d’une requête principale

peut contenir plusieurs sous-requêtes reliées par les connecteurs AND et OR

R.KHCHERIF

61

Mise à jour des données

• Ajout d’un tuple INSERT INTO nom_table VALUES (val_att1, val_att2,

…);

ex. INSERT INTO Produit VALUES (400, ‘Nouveau

produit’, 78.90);

• Mise à jour d’un attribut UPDATE nom_table SET attribut=valeur [WHERE condition]; ex. UPDATE Client SET Nom=‘Dudule’ WHERE

NumCli = 3;

R.KHCHERIF

62

Mise à jour des données

• Suppression de tuples DELETE FROM nom_table [WHERE condition];

ex. DELETE FROM Produit; ex. DELETE FROM Client WHERE Ville = ‘Tunis’;

R.KHCHERIF

63

Les vues

• Vue : table virtuelle calculée à partir d’autres tables grâce à une requête

• Définition d’une vue CREATE VIEW nom_vue AS requête; ex. CREATE VIEW Noms AS SELECT Nom,

Prenom FROM Client;

R.KHCHERIF

64

Les vues

• Intérêt des vues

– Simplification de l’accès aux données en masquant les

opérations de jointure

ex. CREATE VIEW Prod_com AS SELECT

P.NumProd, Dési, PrixUni, Date, Quantite FROM Produit P, Commande C WHERE P.NumProd=C.NumProd;

SELECT NumProd, Dési FROM Prod_com WHERE Quantite>10;

R.KHCHERIF

65

Les vues

• Intérêt des vues

– Sauvegarde indirecte de requêtes complexes – Présentation de mêmes données sous différentes formes

adaptées aux différents usagers particuliers

– Support de l’indépendance logique

ex. Si la table Produit est remaniée, la vue Prod_com doit être refaite, mais les requêtes qui utilisent cette vue n’ont pas à être remaniées.

R.KHCHERIF

66

Les vues

• Intérêt des vues

– Renforcement de la sécurité des données par

masquage des lignes et des colonnes sensibles aux usagers non habilités

• Problèmes de mise à jour, restrictions

– La mise à jour de données via une vue pose des problèmes et la plupart des systèmes impose d’importantes restrictions.

R.KHCHERIF

67

Les vues

• Problèmes de mise à jour, restrictions – Le mot clé DISTINCT doit être absent. – La clause FROM doit faire référence à une seule

table.

– La clause SELECT doit faire référence

directement aux attributs de la table concernée (pas d’attribut dérivé).

– Les clauses GROUP BY et HAVING sont

interdites.

R.KHCHERIF

68

• Spectacle(Spectacle_ID, Titre, DateDéb, Durée, #Salle_ID,

Chanteur)

• Concert (Concert_ID, DateC, HeureC, #Spectacle_ID) • Salle (Salle_ID, Nom, Adresse, Capacité) • Billet (Billet_ID, #Concert_ID, Num_Place, Catégorie, Prix) • Vente (Vente_ID, Date_Vente, #Billet_ID, MoyenPaiement)

• Les mêmes questions résolues avec l’algèbre relationnel.

R.KHCHERIF

69