chap 3 : structered query language

Ramzi Trabelsi

1

Chapitre III : Le langage de commande SQL

2014-2015

2014-20152

Pour faire des interrogations sur les données de ma base :• QBE (Query By Exemple)• SQL (Structured Query Langage),• ...

SQL : standard de fait (norme ANSI) il permet : - la création, interrogation et manipulation des données d'une base - déclarer les relations, créer les occurrences - faire toute opération définie par l'algèbre relationnelle.

SELECT liste des colonnesFROM nom des tablesWHERE conditions

Le Langage SQL

2014-20153

Langage de Définition de Données CREATE, ALTER, DROP, RENAME, TRUNCATE

Langage de Manipulation de Données INSERT, UPDATE, DELETE

Langage d'Interrogation des Données SELECT

Langage de Contrôle des Données GRANT, DENY, REVOKE

Le Langage SQL

Quatre grandes familles de commandes

2014-20154

LE LANGAGE SQL : Définition des données

1 – Création de table: CREATE TABLE

2 – Modification de la table : ALTER TABLE

Modélisation à plusieurs niveaux

5

Réel

Modèle conceptuel

Indépendant du modèle de données

Indépendant du SGBD

Modèle logique

Dépendant du modèle de données

Indépendant du SGBD

Codasyl Relationnel Objet XML

Modèle Physique

Dépendant du modèle de données

Dépendant du SGBD

Oracle MySQL MS.ACESS SQL SERVER

Médecin effectue Visite

2014-20156

EpreuvesCodeEpNomEpCoefEp

EtudiantsCodeEtNomEtPrenomEtVilleEt…

NotesCodeNtNoteNtAvoir

Note

appartenir

1,11,N

1,1

0,NMCD

2014-20157

EpreuvesCodeEpNomEpCoefEp

EtudiantsCodeEtNomEtPrenomEtVilleEt…

NotesCodeNtNoteNt#CodeEp#CodeEt

1,1

0,NMLD

Etude de cas

2014-20158

MPD

Création de tableCREATE TABLE nom_de_la_table (     colonne1 type_donnees [Contrainte],     colonne2 type_donnees,     colonne3 type_donnees,     colonne4 type_donnees )

Type_données : VARCHAR(taille) INTEGER NUMERIC (ou DECIMAL ou DEC)  DATE

Contrainte : NOT NULL , PRIMARY KEY, FORIGN KEY, UNIQUE

2014-20159

Création de tablesCREATE TABLE Etudiants(CodeEt VARCHAR(50) PRIMARY KEY,NomEt VARCHAR(50) NOT NULL,PrenomEt VARCHAR(50) NOT NULL,VilleEt VARCHAR(50) );

2014-201510

Création de tables CREATE TABLE Epreuves ( CodeEp VARCHAR(10), NomEp VARCHAR(50) UNIQUE, CoefEp NUMERIC NOT NULL);

ALTER TABLE Epreuves ADD CONSTRAINT PK_CodeEp PRIMARY KEY(CodeEp) ;

2014-201511

Création de tables CREATE TABLE Etudiants( CodeEt VARCHAR(10) PRIMARY KEY, NomEp VARCHAR(50) NOT NULL, NomEt VARCHAR(50) NOT NULL, VilleEt VARCHAR(50));

2014-201512

Création de tables CREATE TABLE Etudiants( CodeEt VARCHAR(10), NomEp VARCHAR(50) NOT NULL, NomEt VARCHAR(50) NOT NULL, VilleEt VARCHAR(50));

ALTER TABLE Etudiants ADD CONSTRAINT PK_CodeEt PRIMARY KEY(CodeEp) ;

2014-201513

Création de tables CREATE TABLE Notes( CodeNt VARCHAR(10) PRIMARY KEY NoteNt NUMERIC, CodeEp VARCHAR(10) FOREIGN KEY

REFERENCES Epreuves(CodeEp), CodeEt VARCHAR(10) FOREIGN KEY

REFERENCES Etudiants(CodeEt));

2014-201514

Création de tables CREATE TABLE Notes( CodeNt VARCHAR(10) PRIMARY KEY NoteNt NUMERIC, CodeEp VARCHAR(10) , CodeEt VARCHAR(10), Etudiants(CodeEt));ALTER TABLE Etudiants ADD CONSTRAINT

FK_CodeEp CodeEp FOREIGN KEY REFERENCES Epreuves(CodeEp);

ALTER TABLE Etudiants ADD CONSTRAINT FK_CodeEt CodeEp FOREIGN KEY REFERENCES Etudiants(CodeEt);

2014-201515

Création de tables CREATE TABLE Notes( CodeNt VARCHAR(10) PRIMARY KEY NoteNt NUMERIC, CodeEp VARCHAR(10) , CodeEt VARCHAR(10), Etudiants(CodeEt));ALTER TABLE Etudiants ADD CONSTRAINT PK_CodeNt

PRIMARY KEY(CodeNt);ALTER TABLE Etudiants ADD CONSTRAINT FK_CodeEp

CodeEp FOREIGN KEY REFERENCES Epreuves(CodeEp);ALTER TABLE Etudiants ADD CONSTRAINT FK_CodeEt

CodeEp FOREIGN KEY REFERENCES Etudiants(CodeEt);

2014-201516

2014-201517

LE LANGAGE SQL : modification des données

1 – Insertion dans la base : Insert

2 – Suppression dans la base : Delete

3 – Modification dans la base : Update

2014-201518

INSERT INTO Agent (IdAgent, Anom, Aprenom) VALUES (25236, 'Dalton', 'Joe');

• Pour insérer une ligne dans une table on utilise la commande SQL INSERT

Le Langage SQL : Mise-à-jour des données

DELETE FROM Agent WHERE IdAgent = 25236 ;

• Pour supprimer des données dans une table on utilise la commande SQL DELETE

UPDATE Agent SET ANom = ‘DURANT’ WHERE Salaire = 2500 ;

• Pour modifier des données dans une table on utilise la commande SQL UPDATE

2014-201519

LE LANGAGE SQL : Interrogation des données

1 – Interrogation de la base : LID

2 – Les jointures

3 – Les sous-interrogations et fonctions de groupe

2014-201520

SELECT Sélection des colonnes à interroger.

FROM Choix des tables à partir desquelles on fait la sélection.

WHERE Sélection des lignes (application d'un filtre).

Le Langage SQL : Interrogation des données

2014-201521

Sélectionner des colonnes spécifiques

• Choix des colonnes par la clause SELECT.

• Afficher toutes les colonnes d'une table.

SELECT * FROM Animaux ;

SELECT ID, Espèce, Nom FROM Animaux;

ID Espèce Nom -- ------ --- 1 Chien Bidou 2 Chat Groucha 3 Yeti Georges 4 Elephant Dumbo 5 Girafe Flip Flap

2014-201522

AgentIdAgent

...

FournisseurIdFourS

...

FournitureIdFourT

...

CollectiviteIdCollect

...

ServiceIdService

...Commander

IdComDépendre Rattacher

1,1

1,n1,1

1,n 0,n1,n

0,n

Autre exemple de BD

2014-201523

FournisseurIdFourS FNom FAdresse FType

CommandeIdCom Agent_Id FourT_Id FourS_Id DateC DateL Prix Quantite

Service IdService SNom Collect_Id

Agents

IdAgent ANom APrenom DateE Fonction Responsable Salaire

Service_Id

Collectivité IdCollect CDesignation

Fourniture IdFourT FDesignation Quantite_Stock

Commission

Autre exemple de BD

2014-201524

Expressions Arithmétiques

• Créer des expressions sur des données de type NUMBER ou DATE avec : + - * /

SELECT ANom, Salaire*12FROM Agent ; Remarque : la colonne salaire*12

existe uniquement à l’affichage.

SELECT ANom, Salaire*12 AS ‘Salaire Annuel’ FROM Agent ;

• Alias sur colonne : AS

• La clause ORDER BY permet de trier les lignes : ASC DESC

SELECT ANom, Fonction, SalaireFROM AgentORDER BY Salaire DESC;

2014-201525

Il est possible de limiter le nombre de lignes rapportées en utilisant la clause WHERE :

• permet d'exprimer une condition.

SELECT ANom, Fonction, SalaireFROM AgentWHERE Service_Id = 10;

Sélection des Lignes

SELECT ANom, Fonction, SalaireFROM AgentWHERE ANom = 'Dupont'

AND APrenom LIKE '_ea%';

chaîne de caractères• permet de faire des comparaisons

Opérateurs de comparaison logiques . = > >= < <= != <> Opérateurs de comparaison . BETWEEN, AND, IN, LIKE, IS NULL Opérateurs logiques . AND, OR, NOT

2014-201526

SELECT ANom, APrenom, Fonction, SalaireFROM AgentWHERE DateE BETWEEN #08-AOU-93#

AND #08- AOU-99#;

Opérateurs de comparaison

• On utilise l'opérateur BETWEEN pour tester l'appartenance à un intervalle.

SELECT IdService, SNom, Collect_IdFROM ServiceWHERE Collect_Id IN (38,42,69,01);

• On utilise l'opérateur IN pour tester l'appartenance à une liste de valeurs.

format des dates par défaut

2014-201527

SELECT UPPER(ANom), CONCAT(ANom, APrenom), SalaireFROM AgentWHERE LOWER(Fonction) = ‘directeur ’;

Fonctions Mono-Ligne

• LOWER convertit en minuscule• UPPER convertit en majuscule• INITCAP convertit l’initiale en capitale• CONCAT concatène des valeurs• SUBSTR retourne une sous-chaîne• LENGTH retourne un nombre de caractères

• ROUND(col,n) arrondit une valeur à n décimales spécif.• TRUNC (col,n) tronque une valeur à n décimales spécif.• MOD(m,n) retourne le reste de la division de m par n

2015-201628

SELECT *FROM Notes, Etudiants

Les jointures

WHERE CodeEt=CodeEtWHERE Notes.CodeEt=Etudiants.CodeEt

FROM Notes N, Etudiants EWHERE N.CodeEt=E.CodeEt

SELECT N.CodeNt, N.NoteNt, E.NomEt, E.PrenomEtFROM Notes N, Etudiants EWHERE N.CodeEt=E.CodeEt ANDN.CodeNt=‘N0045’

2014-201529

• Une jointure est utilisée pour rechercher les données dans plusieurs tables.• Les lignes sont reliées grâce aux valeurs communes contenues dans les clés primaires et étrangères.• La condition de jointure s’exprime dans le WHERE.

Les jointures

SELECT Ag.ANom , Ag.Service_Id , Se.IdServiceFROM Agent Ag, Service SeWHERE Ag.Service_Id = Se.IdService ;

2014-201530

Les Sous-Interrogations

• Il est possible d'imbriquer une, ou plusieurs, interrogation (SELECT) dans une autre.

• On parle alors de sous-interrogation.

• Elle est exécutée avant la requête principale ; son résultat est utilisé par la requête principale.

• Elle permet de rechercher des données basées sur un critère inconnu.

SELECT ANom, FonctionFROM AgentWHERE Service_Id = ( SELECT Service_Id

FROM Agent WHERE ANom = 'Dupont' ) ;

2014-201531

SELECT ANom, Salaire, Service_IdFROM AgentWHERE Salaire = ( SELECT MIN (Salaire)

FROM Agent );

•MIN, MAX,AVG,SUM,COUNT •Si une sous-interrogation retourne plus d'une ligne

SELECT ANom, Salaire, Service_IdFROM AgentWHERE Salaire = ( SELECT MIN (Salaire)

FROM Agent GROUP BY Service_Id );

Il faut remplacer = par IN (opérateur multi-lignes).

Réponses Multi-Lignes (1/2)

2014-201532

Réponses Multi-Lignes (2/2)

• Autre exemple

SELECT Service_Id, AVG(Salaire)FROM AgentGROUP BY Service_Id HAVING AVG(Salaire) > ( SELECT AVG(Salaire)

FROM Agent WHERE Service_Id = 10 );

Salaire moyen par service, dont le salaire moyen est supérieur à celui du service 10.

Syntaxe SelectSELECT codeEp,AVG(NoteNt)FROM Table1, Table2WHERE JoitureAND Autres_fGroup By (codeEp)HAVING AVG(NoteNt) >12ORDER BY AVG(NoteNt) DESC

2014-201533

Groupement

2014-201534

SELECT Categorie,SUM(Qte) as S_Qte FROM ProduitWHERE Couleur=‘Rouge’GROUP BY CategorieHAVING SUM(Qte)>100ORDER BY SUM(Qte) DESC

Le nom , prénom et note DS de tous les étudiants

2014-201535

1

SELECT NomEt , PrenomEt, NoteNt as Notes_DSFROM Etudiants E , Notes NWHERE E.CodeEt = N.CodeEtAND CodeEp=‘DS’;

Le nom , prénom et note DS de tous les étudiants ayant une note entre 8 et 12

2014-201536

2

SELECT NomEt , PrenomEt, NoteNt as Notes_DSFROM Etudiants E , Notes NWHERE E.CodeEt = N.CodeEtAND CodeEp=‘DS’AND NoteNt BETWEEN 8 AND 12;

Le nombre des étudiants ayant une note entre 8 et 12 en DS

2014-201537

3

SELECT Count(*)FROM Etudiants E , Notes NWHERE E.CodeEt = N.CodeEtAND CodeEp=‘DS’AND NoteNt BETWEEN 8 AND 12;

Max et Min des notes de DS

2014-201538

4

SELECT Max(NoteNt),Min(NoteNt)FROM Notes

WHERE CodeEp=‘DS’

Le nom , prénom et note DS de tous les étudiants ayant une note supérieure à la moyenne des DS

2014-201539

5

SELECT NomEt , PrenomEt, NoteNt as Notes_DSFROM Etudiants E , Notes NWHERE E.CodeEt = N.CodeEtAND CodeEp='DS'AND NoteNt > SELECT AVG(NoteNT)

FROM NotesWHERE CodeEp= 'DS'

Calcul de la moyenne des notes DS

Le nom , prénom et Examen triés de la note la plus petite à la plus grande

2014-201540

6

SELECT NomEt , PrenomEt, NoteNt as Notes_ExamenFROM Etudiants E , Notes NWHERE E.CodeEt = N.CodeEtAND CodeEp='EX‘AND NoteNt IS NOT NULLORDER BY NoteNt ASC ;

Le nom , prénom des étudiants n’ayant pas passé l’examen

2014-201541

7

SELECT NomEt , PrenomEt FROM Etudiants E , Notes NWHERE E.CodeEt = N.CodeEtAND CodeEp='EX'AND NoteNt IS NULL

Le maximum en DS , EX et NP

2014-201542

8

SELECT CodeEp,NomEp, Max(NoteNt) AS max_NoteFROM Notes GROUP BY CodeEp

Le maximum en Devoir Surveillé , Examen et Présentiel

2014-201543

9

SELECT NomEp, Max(NoteNt) AS max_NoteFROM Notes N, Epreuves EWHERE N.CodeEP= E.CodeEpGROUP BY NomEp

Le nom et Prénom de l’étudiant ayant la note de DS la plus élevée SELECT NomEt, PrenomET , NoteNtFROM Etudiants E, Notes NWHERE E.CodeEt = N.CodeEtAND CodeEp='DS‘ AND NoteNt =

2014-201544

10

SELECT MAX(NoteNT)FROM NotesWHERE CodeEp= 'DS'

Trouver le max note DS

Le nom , prénom et moyenne générale de la matière

2014-201545

11

SELECT NomEt, PrenomEt, SUM(NoteNt*CoefEp )FROM Epreuves Ep, Notes N , Etudiants EtWHERE Et.CodeEt = N.CodeETAND N.CodeEp=Ep.CodeEpGROUP BY NomEt , PrenomEt

Le nom , prénom et moyenne générale triés par Moy Générale

2014-201546

12

SELECT NomEt, PrenomEt, SUM(NoteNt*CoefEp )FROM Epreuves Ep, Notes N , Etudiants EtWHERE Et.CodeEt = N.CodeETAND N.CodeEp=Ep.CodeEpGROUP BY NomEt , PrenomEtORDER BY SUM(NoteNt*CoefEp ) DESC

Le nom , prénom et moyenne générale triés (seule moy>=10)

*HAVING n’est utilisée qu’avec GROUP BY quand il s’agit de filtrer selon l’une des fonctions de groupement

2014-201547

13

SELECT NomEt, PrenomEt, SUM(NoteNt*CoefEp )FROM Epreuves Ep, Notes N , Etudiants EtWHERE Et.CodeEt = N.CodeETAND N.CodeEp=Ep.CodeEpGROUP BY NomEt , PrenomEtHAVING SUM(NoteNt*CoefEp ) >=10ORDER BY SUM(NoteNt*CoefEp ) DESC

Le nom et prénom de la fille ayant la plus grande note de NP

2014-201548

14

SELECT NomEt, PrenomET , NoteNtFROM Etudiants E, Notes NWHERE E.CodeEt = N.CodeEtAND CodeEp=‘NP‘ AND SexeEt='F'AND NoteNt = SELECT MAX(NoteNT)

FROM Notes N, Etudiants EWhere N.CodeEt=E.CodeEtAND SexeEt= 'F'

Trouver La note TP la plus élevée obtenue par une fille

Le maximum et minimum de note par Ville

2014-201549

15

SELECT VilleEt, MAX(NoteNt) , MIN(NoteNt)FROM Etudiants E, Notes NWHERE E.CodeEt = N.CodeEtGROUP BY (VilleEt)

La note d’examen maximale par ville et par sexe

2014-201550

16

SELECT VilleEt, MAX(NoteNt)FROM Etudiants E, Notes NWHERE E.CodeEt = N.CodeEtAND CodeEp = 'EX'GROUP BY VilleEtol , SexeEt;

La note d’examen maximale par ville et par sexe dépassant 15

2014-201551

17

SELECT VilleEt, MAX(NoteNt)FROM Etudiants E, Notes NWHERE E.CodeEt = N.CodeEtAND CodeEp = 'EX'GROUP BY VilleEt , SexeEtHAVING MAX(NoteNt)>15 ;

2014-201552

Fournisseur

CodeFNomFVilleF

BoutiqueCodeBNomBVilleB

PieceCodePNomPCouleurPPoidsPNbrP

Fournit

Qte

VendQte

1,N0,N

1,N

1,N

2014-201553

BoutiqueCodeBNomBVilleB

PieceCodePNomPcCouleurPPoidsP

Note#CodeP#CodeBQteVend

2014-201554

Fournisseur

CodeFNomFVilleF

BoutiqueCodeBNomBVilleB

PieceCodePNomPCouleurPPoidsP

Fournit

Qte

0,N 1,N

1,N

2014-201555

Fournisseur

CodeFrNomFrVilleFr

BoutiqueCodeBNomBVilleB

PieceCodePNomPCouleurPPoidsP

Fournit#CodeP#CodeF#CodeBQteFrn

2014-201556

CodeF

NomF

VilleF

F1 P.M.S

Tunis

F2 S.T.M

Tunis

F3 F.R.P

Sousse

F4 M.T SfaxF5 N.X Tuni

sF6 K.L Sous

se

CodeP

NomP CouleurP

PoidsP

Pc1 Papier

Rouge 500

Pc2 Plateau

Vert 30

Pc3 Rideau

Vert 450

Pc3 Cadre Orange 250Pc4 Verre Rouge 20Pc5 Assiet

teOrange 40

CodeB

NomB

VilleB

B1 Promi TunisB2 Fonis TunisB3 Breco SousseB4 Bord NabeulB5 Halu BizerteB6 Bonp

roSfax

CodeF

CodeP

CodeB

QteFrn

F1 Pc1 B1 200F1 Pc2 B2 150F2 Pc3 B1 600F4 Pc1 B2 100F1 Pc4 B1 300F3 Pc6 B3 250