Bases de Données
Chapitre 5:
Langage SQL
Pr. Fatima-Zohra Hibbi
[email protected] 2022/2023
Plan
Chapitre 1:
1. Introduction aux Systèmes d’Information et Bases de Données.
2. Introduction aux Systèmes de Gestion de Base de Données (SGBD).
Chapitre 2: Le Modèle Entité/Association.
Chapitre 3: Le modèle relationnel.
• Chapitre 4: L’algèbre relationnelle.
Chapitre 5: SQL.
Cours: Bases de Données Chapitre 4: L‘algèbre Relationnel
Plan
Chapitre 5 : Langage SQL
LDD: Langage de définition des données
LMD: Langage de Manipulation des données
Cours: Bases de Données Chapitre 4: L‘algèbre Relationnel
SQL: LDD :Langage de
définition de donnée
SQL:LDD:
Créer des tables;
Décrire les différents types de données
utilisables pour les définitions de Colonne;
Modifier la définition des tables;
Supprimer, renommer et tronquer une table.
SQL:LDD
Conventions de Dénomination:
Un nom :
Doit commencer par une lettre
Peut comporter de 1 à 30 caractères
Ne peut contenir que les caractères A à Z, a à z, 0 à 9, _, $, et #
Ne doit pas porter le nom d’un autre objet appartenant au
même utilisateur
SQL:LDD: Création des tables
Créer la table dept (département) :
SQL> CREATE TABLE dept_2
(deptno int, dname VARCHAR2(14),loc VARCHAR2(13));
Table created.
Vérifier la création des tables :
SQL> DESC dept;
SQL:LDD: Création des tables
Les types de données:
Type de donnée Description
VARCHAR (size) Caractère de longueur variable
CHAR (size) Caractère de longueur fixe
INT, FLOAT, DOUBLE numérique
DATE Valeur de date et l’heure
SQL:LDD: Création des tables
UTILISER L’ORDRE ALTER TABLE POUR:
Ajouter une colonne;
Modifier une colonne existante;
Définir une valeur par défaut pour une nouvelle colonne.
ALTER TABLE table
ADD (column datatype [DEFAULT expr] [, column datatype]...);
SQL> ALTER TABLE dept 2
ADD (job VARCHAR(9)); Table altered.
Ajout de Colonnes:
SQL:LDD: Création des tables
Modification de colonne
Vous pouvez modifier le type de données, la taille et la valeur
par défaut d'une colonne.
ALTER TABLE table
MODIFY column datatype [DEFAULT expr] ;
ALTER TABLE dept
MODIFY COLUMN dname VARCHAR(15);
Table altered.
SQL:LDD: Création des tables
Suppression de colonne
Vous pouvez supprimer la colonne:
ALTER TABLE table
DROP dname;
SQL:LDD: Création des tables
Modification du nom d’une table
Pour modifier le nom d'une table, d'une vue, d'une séquence ou
d'un synonyme, utilisez l'ordre RENAME.
RENAME TABLE dept TO
Departement;
Table renamed.
SQL:LDD: Suppression des tables
SQL> DROP TABLE Departement;
Table dropped.
SQL:LDD: Vider une table
L'ordre TRUNCATE TABLE :
Supprime toutes les lignes d'une table ;
Libère l'espace de stockage utilisé par la table;
SQL> TRUNCATE TABLE
department;
Table truncated.
Exercice
Soit le schéma relationnel suivant:
PILOTE (PLNUM, PLNOM, PLPRENOM, VILLE, SALAIRE) ;
1. Créer une base de données nommé EXO_1;
2. Utilisé la BD: EXO_1
3. Créer la structure de la table PILOTE;
4. Ajouter un nouvelle colonne s’appel « ADRESSE »;
5. Renommer la table PILOTE par le nom: « T_PILOTE ».
6. Supprimer la table.
SQL: LMD :Langage de
Manipulation des Données
SQL: LMD :
Définition
Le LMD est le langage de manipulation de données. Il permet de
modifier le CONTENU d'une table en:
- Ajoutant de nouvelles lignes INSERT INTO nom_table;
- Modifiant une ou plusieurs lignes UPDATE nom_table;
- Supprimant des lignes DELETE FROM nom_table ;
- Consultant des lignes SELECT.
SQL: LMD :
Insertion de ligne
Syntaxe:
INSERT INTO nom_table (nom_colonne_1,
nom_colonne_2, …)
VALUES(valeur_colonne_1, valeur_colonne_2, …)
Exemple:
INSERT INTO employe VALUES(12, 'Dupont',
'comptable', 1800, 2, NULL)
SQL: LMD :
Modification de ligne
Syntaxe:
UPDATE nom_table
SET nom_colonne = valeur
WHERE condition(s);
Exemple:
Augmentation de 10%:
UPDATE employe
SET sal = sal * 1.1
WHERE fontion = 'informaticien';
SQL: LMD :
Modification de ligne
Exemple: modifier la valeur d’une colonne pour toutes les
lignes
Augmenter tous les employés de 1%
UPDATE employe
SET sal = sal * 1.01;
SQL: LMD :
Suppression:
Syntaxe:
DELETE FROM nom_table
WHERE condition(s);
Exemple:
DELETE FROM employe
WHERE code_service = 3
DELETE FROM DOC
WHERE NomDoc IN ( select NomDOc from DOC WHERE
VilleDoc=NULL);
SQL: LMD :
Consultation d’une ligne
Syntaxe:
SELECT [DISTINCT] FROM
Nom_Table
[WHERE condition]
Exemple:
SELECT Nom FROM Personne;
Exercice
La base de données Hôtel contient 2 tables:
•Chambre (Num_Chambre, Prix, Nbr_Lit, Nbr_Pers) ;
•Client (Num_Client, Nom, Prenom, Adresse) ;
1.Créer les tables : Chambre et Client ;
2.Remplir les tables « Chambre » et « Client » ;
SQL: LDD :Approfondissement
I. Les contraintes d’intégrités:
Définition:
Une contrainte d'intégrité est une règle qui permet d'assurer
la validité (cohérence) des données stockées dans une base.
Les différentes contraintes que l'on peut
• non nullité (obligation) : NOT NULL;
définir sont :
• unicité : UNIQUE;
• clé primaire : PRIMARY KEY;
• intégrité référentielle (clé étrangère) : FOREIGN KEY.
• Conditions: CHECK
1. Non nullité (obligation) : NOT NULL
Cette contrainte permet de rendre obligatoire la
saisie d'une colonne:
• Exemple:
CREATE TABLE PERSONNE (NomPers Varchar(30) NOT
NULL, PrénomPers Varchar(30));
2. unicité : UNIQUE;
Cette contrainte permet de contrôler que chaque ligne a une valeur unique
pour la colonne (pas de doublons).
• Exemple:
Sur une seule colonne
CREATE TABLE PERSONNE
(Téléphone INT(10) UNIQUE);
Ou bien:
CREATE TABLE PERSONNE ( NomPers Varchar(30) NOT NULL,
PrénomPers Varchar(30), UNIQUE (NomPers));
3. clé primaire : PRIMARY KEY;
La contrainte de clé primaire permet d'indiquer que la ou les
colonnes sont uniques et ne peuvent pas avoir de valeur nulle. On peut dire
que :
• Exemple:
PRIMARY KEY = UNIQUE + NOT NULL
CREATE TABLE PERSONNE (NomPers Varchar2(30), PrenomPers Varchar2(30),
PRIMARY KEY (NomPers, PrenomPers));
4. intégrité référentielle (clé étrangère) : FOREIGN KEY
• L'intégrité référentielle permet de vérifier la cohérence des liens
entre tables, lors de l'ajout, de la suppression et de la modification de
lignes.
• Elle est utilisée lorsqu'on a une clé étrangère.
• Elle permet d'assurer que toute valeur de clé étrangère
correspond bien à une valeur de clé primaire de la table liée.
5. Contraint:
Une contrainte d'intégrité est une clause permettant de
contraindre la modification de tables, faite par l'intermédiaire de requêtes
d'utilisateurs, afin que les données saisies dans la base soient conformes
aux données attendues.
• Exemple -1-:
Alter table Employe
ADD CONSTRAINT Const_Fr
Foreign key id_Client REFERENCES Client (id_c);
Exemple-2-
Create Table DET(
NumORD integer,
NumLigne integer,
NumMED integer,
QTE integer Not Null,
Constraint PK_DET Primary Key (NumORD, NumLigne),
Constraint NbMaxMed Check (NumLigne < 5),
Constraint Ref_ORD Foreign Key (NumORD) References ORD(NumORD)
on delete cascade,
Constraint Ref_MED Foreign Key( NumMED )References MED(NumMED)
on update cascade);
SQL: LMD :Approfondissement
LES FONCTIONS D'ENSEMBLE
SQL a cinq fonctions importantes :
• SUM (): Donne le total d'un champ de tous les enregistrements
satisfaisant la condition de la
requête. Le champ doit bien sur être de type numérique;
• AVG (): donne la moyenne d'un champ de tous les enregistrements
satisfaisant la condition de la requête;
• MAX (): Donne la valeur la plus élevée d'un champ de tous les
enregistrements satisfaisant la condition de la requête
• MIN (): Donne la valeur la plus petite d'un champ de tous les
enregistrements satisfaisant la condition de la requête.
• COUNT (*) :Renvoie le nombre d'enregistrements satisfaisant la
requête.
LES FONCTIONS D'ENSEMBLE
Exemple:
• Pour retourner le prix le plus petit de la table Produit, le prix le
plus élevé et le prix moyen:
SELECT MIN(prix_unitaire),MAX(prix_unitaire), AVG(prix_unitaire) FROM Produits
• Pour retourner le nombre de produits dont le libellé commence
par la lettre 'P‘:
SELECT COUNT (*)
FROM Produits
WHERE libelle LIKE 'P%';
LA CLAUSE LIKE et Not Like
La clause LIKE permet de faire des recherches sur le contenu d'un
champ.
Exemple:
pour sélectionner les étudiants dont le nom commence par la lettre D :
SELECT *
FROM etudiant
WHERE nom LIKE ‘D%';
Remarque:
% remplace un ensemble de caractères
Clause BETWEEN
Pour sélectionner des enregistrements dont la valeur d'un champ peut
être comprise entre deux valeurs, on utilise la clause BETWEEN.
Exemple:
pour sélectionner les étudiants dont le nom commence par la lettre D :
SELECT *
FROM etudiant
WHERE age BETWEEN 18 AND
24;
Clauses IN et NOT IN
• Pour sélectionner des enregistrements dont la valeur d'un champ peut
être comprise dans une liste on utilise la clause IN
• NOT IN sélectionne les enregistrements exclus de la liste spécifiée
après IN.
SELECT *
FROM etudiant
WHERE age IN (18,
20,22,24);
SELECT *
FROM etudiant
WHERE age NOT IN
(16,17,18);
DISTINCT
• L'option DISTINCT permet de ne conserver que des lignes distinctes,
en éliminant les doublons
SELECT DISTINCT nom, age
FROM etudiant WHERE nom=
‘Amrani’;
Les opérateurs:
• On peut utiliser les opérateurs suivants dans les conditions c’est-à-
dire dans la clause WHERE:
Opérateurs logiques
• Opérateur AND:
SELECT * FROM etudiant WHERE
age=20 AND nom=‘ahmed’;
Opérateur OR:
SELECT * FROM etudiant WHERE
nom=‘Amrani’ OR nom=‘Salhi’;
SELECT
Il existe d'autres options pour la commande SELECT :
• GROUP BY
• HAVING
• ORDER BY
GROUP BY
Si nous voulons la liste des étudiants, avec pour chaque
étudiants le nombre de prêt qu'il a fait:
SELECT num_etudiant, COUNT(*)
FROM prêt
GROUP BY num_etudiant;
HAVING
La clause HAVING est utilisé avec la clause GROUP BY,
elle permet d'appliquer une restriction sur les groupes créés grâce
à la clause GROUP BY.
SELECT ville, COUNT(*)
FROM etudiant
GROUP BY ville HAVING ville
IN("fes","meknes" );
ORDER BY
La clause ORDER BY suivie des mots clés ASC ou DESC,
permet de Trier les résultats d’une requête en ordre croissant ou
bien décroissant:
SELECT * FROM
etudiant
ORDER BY nom
ASC;
Jointure:
Equijointure ( jointure naturelle)
Autojointure (jointure sur la même table)
Jointure externe
Non-équijointure (jointure par non égalité, théta jointure)
Opérations Spécifiques
JOINTURE
Exemple:
Donnez pour chaque vente la référence du produit, sa désignation, son prix, le numéro
de client, la date et la quantité vendue:
Cours: Bases de Données Chapitre 4: L‘algèbre Relationnel
La jointure
Equijointure : Jointure Naturelle
La jointure
Exemple
Liste des RDV avec le docteur ‘Alaoui’:
Solution:
SELECT NumRDV
FROM RDV R , DOC D
WHERE R.NumDoc = D.NumDoc
and D.NomDoc =‘Alaoui’;
La jointure
La jointure
AutoJointure
La jointure
AutoJointure:
Exemple
Exercice d’application N°1:
1. Trouver les numéro de commande passées par le client « Amrani Ahmed »?
2. Lister les Num Client ayant commandé un produit dont le prix = 1000
3. Lister les noms des clients qui ont commandé le produit 2
Exercice d’application N°2:
Soit le modèle relationnel suivant relatif à une base de données sur des
représentations musicales :
REPRESENTATION (n °représentation, titre_représentation, lieu)
MUSICIEN (id_music, nom, #n °représentation)
PROGRAMMER (id_programmer, date, #n °représentation, tarif)
Questions:
1. Donner la liste des titres des représentations.
2. Donner la liste des titres des représentations ayant lieu à l'opéra « Olympia »
3. Donner la liste des noms des musiciens et des titres des représentations auxquelles
ils participent.
4. Donner la liste des titres des représentations, les lieux et les tarifs pour la journée
du ‘12/03/2023’.
Jointure
Jointure Externe
Les jointures externes permettent de visualiser des lignes qui ne répondent pas à la
condition de jointure.
Jointure externe
Jointure externe à gauche
Jointure externe à droite
Cours: Bases de Données Chapitre 5: Langage SQL
Jointure
Jointure Externe
SELECT table1.colonne, table2.colonne
FROM table1 FULL OUTER JOIN table2
ON table1.col1 = table2.col2 ;
Cours: Bases de Données Chapitre 5: Langage SQL
Jointure
Jointure Externe à gauche
SELECT table1.colonne, table2.colonne
FROM table1 LEFT OUTER JOIN table2
ON table1.col1 = table2.col2 ;
Cours: Bases de Données Chapitre 5: Langage SQL
Jointure
Jointure Externe à droite
SELECT table1.colonne, table2.colonne
FROM table1 RIGHT OUTER JOIN table2
ON table1.col1 = table2.col2 ;
Cours: Bases de Données Chapitre 5: Langage SQL
Jointure
Non Equijointure (thêta jointure)
Exemple : la liste des employés et leurs grades
SELECT EMP.nom, SAL.gra
FROM EMP, SAL
WHERE EMP.salemp BETWEEN SAL.salmin and SAL.salmax
Cours: Bases de Données Chapitre 5: Langage SQL
Requête Imbriqué
Syntaxe générale
SELECT colonnes_de_projection
FROM table
WHERE expr operator (
SELECT colonnes_de_projection
FROM table
WHERE …..
);
Cours: Bases de Données Chapitre 5: Langage SQL
Requête Imbriqué
Types de sous-requête
Cours: Bases de Données Chapitre 5: Langage SQL
Requête Imbriqué
Exemple
Les noms des employés qui gagnent plus que ’Filali’ ?
SELECT nom
FROM EMP
WHERE salemp > (SELECT salemp
FROM EMP
WHERE nom=‘Filali’);
Les employés ayant un salaire supérieur à la moyenne ?
SELECT nom
FROM EMP
WHERE salemp > (SELECT AVG(salemp)
FROM EMP);
Cours: Bases de Données Chapitre 5: Langage SQL
Requête Imbriqué
Exemple
Les noms des employés qui ne sont pas les moins payés ?
SELECT nom
FROM EMP
WHERE salemp > ANY (SELECT salemp
FROM EMP );
Le nom de l’employé le mieux payé ?
SELECT nom
FROM EMP
WHERE salemp >= ALL (SELECT salemp
FROM EMP);
Cours: Bases de Données Chapitre 5: Langage SQL
Exercice d’application
Q1 : Les numéros d’ordonances et leur montant total
Q2 : Les noms des patients ayant pris au moins un médicament de prix supérieur à 150
DH.
Q3 : Le nombre de RDV par docteur en 2019
Q4 : Patients sans RDV en 2019
Q5 : Les patients ayant eu des RDV avec tous les docteurs
Q6 : Les docteurs ayant eu des RDV avec tous les patients
Q7 : Les patients ayant eu des RDV avec les mêmes docteurs que le patient N°10.
Q8 : Le médicaments le plus prescrit en 2019.
Cours: Bases de Données Chapitre 5: Langage SQL
INDEX
Définition:
Un index est une structure de données qui permet d'accélérer les recherches
dans une table.
Syntaxe de création:
- Syntaxe basique :
CREATE INDEX ‘Index_Nom’ ON ‘Table_Nom’;
- Index sur une seule colonne :
CREATE INDEX ‘Index_Nom’ ON ‘Table_Nom’ (‘Colonne1’);
- Index sur plusieurs colonnes
CREATE INDEX ‘Index_Nom’ ON ‘Table_Nom’ (‘Colonne1’, ‘Colonne 2’, …);
- Index unique (unicité des valeurs de la colonne indexée)
CREATE UNIQUE INDEX ‘Index_Nom’ ON ‘Table_Nom’ (‘Colonne1’);
Cours: Bases de Données Chapitre 5: Langage SQL
Opérateurs Ensemblistes
T1 T2
Intersect Id Nom Id Nom
Union 1 N1 2 N2
Union all 2 N2 4 N4
Minus (EXCEPT) 3 N3 5 N5
Exemple:
1. SELECT id, nom FROM T1 id | nom
UNION ----|-------
SELECT id, nom FROM T2 1 | N1
2 | N2
2. SELECT id, nom FROM T1 3 | N3
UNION ALL 2 | N2
SELECT id, nom FROM T2 4 | N4
5 | N5
3. SELECT id, nom FROM T1
id | nom
INTERSECT
----|-------
SELECT id, nom FROM T2
2 | N2
Cours: Bases de Données Chapitre 5: Langage SQL
Opérateurs Ensemblistes
Exemple
Les médicaments prescrits en mêmes temps dans
les ordonnances 1 et 3:
Select NumMed from DET Where NumOrd=1
INTERSECT
Select NumMed from DET Where NumOrd=3;
Les docteurs n’ayant pas eu de RDV en 2022
Select NumDoc from DOC
MINUS
Select NumDoc from RDV Where dateRDV
Between ‘01/01/2022’ and ’31/12/2022’;
Cours: Bases de Données Chapitre 5: Langage SQL
