Langage de manipulation des
données (Suite)
Opérations ensemblistes
Union:
L’union de deux relations (tables) R et S de même schéma définit une relation T de
même schéma qui contient tous les lignes de R, de S ou à la fois de R et S, en
éliminant les lignes en doubles.
Opérations ensemblistes
Union:
Requête SQL :
SELECT . . . FROM . . . WHERE . . .
UNION | UNION ALL
SELECT . . . FROM . . . WHERE . . .
UNION ALL permet de garder les répétitions lors de calcul de la réunion.
Opérations ensemblistes
Exemple :
Imaginons une entreprise qui possède deux magasins et une table de clients pour
chacun :
magasin1_client (idClient, nom, prénom, adresse, tel)
magasin2_client (idClient, nom, prénom, adresse, tel)
Afficher les informations sur tous les clients de l’entreprise
Opérations ensemblistes
Exemple :
Imaginons une entreprise qui possède deux magasins et une table de clients pour
chacun :
magasin1_client (idClient, nom, prénom, adresse, tel)
magasin2_client (idClient, nom, prénom, adresse, tel)
Afficher les informations sur tous les clients de l’entreprise
SELECT * FROM magasin1_client
UNION
SELECT * FROM magasin2_client
Opérations ensemblistes
Intersection :
L’intersection de deux relations R et S de même schéma est une relation T de même
schéma contenant les lignes appartenant à la fois à R et à S
Opérations ensemblistes
Intersection :
Requête SQL :
SELECT . . . FROM . . . WHERE . . .
INTERSECT
SELECT . . . FROM . . . WHERE . . .
Opérations ensemblistes
Exemple:
Soit le schéma de l’exemple précédent:
magasin1_client (idClient, nom, prénom, adresse, tel)
magasin2_client (idClient, nom, prénom, adresse, tel)
Afficher la liste des clients qui sont présents de façon identiques dans ces 2 tables
Opérations ensemblistes
Exemple:
Soit le schéma de l’exemple précédent:
magasin1_client (idClient, nom, prénom, adresse, tel)
magasin2_client (idClient, nom, prénom, adresse, tel)
Afficher la liste des clients qui sont présents de façon identiques dans ces 2 tables
SELECT * FROM magasin1_client
INTERSECT
SELECT * FROM magasin2_client
Opérations ensemblistes
Différence :
La différence de deux relations R et S (dans cet ordre) de même schéma est une relation T de
même schéma contenant l’ensemble des lignes appartenant à R et n’appartenant pas à S.
Opérations ensemblistes
Différence :
Requête SQL :
SELECT . . . FROM . . . WHERE . . .
EXCEPT
SELECT . . . FROM . . . WHERE . . .
Opérations ensemblistes
Différence :
Exemple :
Soit les relations (tables) :
• Eleves (idEleve , Nom, Prenom, Ville, Tel, NoteBac, dateNaissance,classe)
• Emprunter (#idEleve, #idLivre, date, duree)
Afficher idEleve des élèves qui n’ont jamais empruntés des livres
Opérations ensemblistes
Différence :
Exemple :
Soit les relations (tables) :
• Eleves (idEleve , Nom, Prenom, Ville, Tel, NoteBac, dateNaissance,classe)
• Emprunter (#idEleve, #idLivre, date, duree)
Afficher idEleve des élèves qui n’ont jamais empruntés des livres
SELECT idEleve FROM Eleves
EXCEPT
SELECT idEleve FROM Emprunter
Opérations ensemblistes
Produit cartésien :
Le produit cartésien de deux relations R et S, de schéma quelconque, est une relation T dont :
• les colonnes sont la concaténation de ceux de R et S
• les lignes sont toutes les concaténations de chaque ligne de R à chaque ligne de S
Opérations ensemblistes
Produit cartésien :
Requête SQL :
SELECT * FROM table1,table2
Opérations ensemblistes
Produit cartésien :
Remarque :
Si les deux tables ont des colonnes de même nom, alors chaque nom de colonne est préfixé par
le nom de sa table
Exemple :
Salle (idSalle, capacité)
Cours (idSalle, classe)
SELECT Salle.idSalle, Salle.capacité, Cours.idSalle
FROM Salle, Cours
WHERE Salle.idSalle < 5 ;
Renommage
Il est possible d’utiliser des alias pour renommer temporairement une colonne ou une table.
Renommage des colonnes :
Requête SQL :
SELECT R1 AS S1,. . . , Ri AS Si, . . . FROM nomtable;
Ou bien
SELECT R1 S1,. . . , Ri Si . . .FROM nomtable;
Exemple :
SELECT Nom, NoteBac/2 AS note_sur_10 FROM eleves ;
Renommage
Renommage des tables :
Requête SQL :
SELECT A.A1, ... , B.B1, ... FROM nomtable1 AS A, nomtable2 AS B;
Ou bien
SELECT A.A1, ... , B.B1, ... FROM nomtable1 A, nomtable2 B;
Exemple :
SELECT S.idSalle, S.capacité, C.idSalle
FROM Salle S, Cours C
WHERE S.idSalle < 5 ;
Jointure
La jointure de deux tables R et S selon une condition P est la table T contenant l’ensemble des
lignes du produit cartésien R x S satisfaisant la condition P.
Cette condition est une proposition logique portant sur deux colonnes (en particulier, la clé
primaire de l’une et la clé étrangère de l’autre) en utilisant des opérateurs de comparaison (<,
>, , , ! =, =, . . .).
Jointure
Jointure entre la table élève et la table lycée selon la condition:
eleve.id_lycee=lycee.id_lycee
Jointure
Requête SQL :
SELECT * FROM R JOIN S ON R.colonne1= S.colonne2 ;
Ou bien
SELECT * FROM R,S WHERE R.colonne1= S.colonne2 ;
Jointure
Exemple :
Soient les tables :
• Profs (Id, Nom, Prenom, Tel, Salle)
• Eleves (Id, Nom, Prenom, Ville, Tel, #Numprof)
Afficher les noms des élèves dont le professeur est monsieur ’XXX’
Jointure
Exemple :
Soient les tables :
• Profs (Id, Nom, Prenom, Tel, Salle)
• Eleves (Id, Nom, Prenom, Ville, Tel, #Numprof)
Afficher les noms des élèves dont le professeur est monsieur ’XXX’
• SELECT E.Nom FROM Eleves as E JOIN Profs as P ON E.Numprof=P.Id WHERE P.Nom=’XXX’;
• SELECT E.Nom FROM Eleves E, Profs P WHERE E.Numprof=P.Id AND P.Nom=’XXX’;
Fonctions d’agrégation
Ces fonctions permettent d’ effectuer des opérations statistiques sur un ensemble
d’enregistrements. Etant donnée que ces fonctions s’appliquent à plusieurs lignes au même
temps, elles permettent de :
▪ AVG : Calculer la moyenne
▪ COUNT : Calculer le nombre de lignes
▪ MAX : Calculer la valeur maximale
▪ MIN : Calculer la valeur minimale
▪ SUM : Effectuer la somme des valeurs
Fonctions d’agrégation
Exemple:
Soit la table :
Eleves (Id , Nom, Prenom, Tel, NoteBac, dateNaissance, classe)
Afficher la note de bac maximale des élèves.
Fonctions d’agrégation
Exemple:
Soit la table :
Eleves (Id , Nom, Prenom, Tel, NoteBac, dateNaissance, classe)
Afficher la note de bac maximale des élèves.
SELECT MAX(NoteBac) FROM Eleves;
Fonctions d’agrégation
Exemple:
Soit la table :
Eleves (Id , Nom, Prenom, Tel, NoteBac, dateNaissance, classe)
Afficher le nombre des élèves.
SELECT COUNT(Id) FROM Eleves;
Clause Group by
La commande Group By est utilisée pour partitionner les lignes de résultats en
groupes, en fonction de leurs valeurs dans une ou plusieurs colonnes.
Généralement le regroupement est utilisé pour appliquer une fonction d ’agrégation
pour chaque groupe.
Clause Group by
Requête SQL :
SELECT A1, ...,An, f1(B1), ..., fm(Bm)FROM R GROUP BY A1, ...,An HAVING condition ;
Avec A1, ...,An des colonnes de la table R, et f1, ..., fm des fonctions d’agrégation appliquées sur
des colonnes B1, ..., Bm de la table R.
▪ les conditions de la clause HAVING sont appliquées après la formation des groupes tandis
que les conditions de la clause WHERE sont appliqués avant de former les groupes.
Clause Group by
Remarque :
▪ Chaque colonne figurant dans SELECT doit figurer dans GROUP BY
▪ Sur le résultat, on trouve qu’une seule ligne pour chaque groupe.
▪ Toutes les fonctions d’agrégation excluent par défaut les valeurs NULL avant de travailler sur
les données.
Clause Group by
Exemple :
Soit la table :
Eleves (Id , Nom, Prenom, Tel, NoteBac, dateNaissance, classe)
Afficher la note de bac maximale des élèves de chaque classe:
Clause Group by
Exemple :
Soit la table :
Eleves (Id , Nom, Prenom, Tel, NoteBac, dateNaissance, classe)
Afficher la note de bac maximale des élèves de chaque classe:
SELECT MAX(NoteBac) FROM Eleves GROUP BY classe ;
Clause Group by
Exemple :
Soit la table :
Eleves (Id , Nom, Prenom, Tel, NoteBac, dateNaissance, classe)
Afficher pour chaque classe le nombre d’élèves, mais seulement pour les classes dont la
moyenne de la note du bac est supérieure à 14:
Clause Group by
Exemple :
Soit la table :
Eleves (Id , Nom, Prenom, Tel, NoteBac, dateNaissance, classe)
Afficher pour chaque classe le nombre d’élèves, mais seulement pour les classes dont la
moyenne de la note du bac est supérieure à 14:
SELECT classe, COUNT(*) AS nombreEleves
FROM Eleves
GROUP BY classe
HAVING AVG(NoteBac)>14;
Trier les résultats
Il est possible de trier les données sur une ou plusieurs colonnes :
• par ordre ascendant (ASC)
• par ordre descendant (DESC)
Par défaut les résultats sont classés par ordre ascendant.
Trier les résultats
Requête SQL :
SELECT . . . FROM . . . WHERE . . . ORDER BY colonne1 [ASC | DESC],. . . , colonneP [ASC | DESC] ;
Trier les résultats
Exemple :
Soit la table :
Eleves (Id , Nom, Prenom, Tel, NoteBac, dateNaissance, classe)
Afficher la note de bac moyenne de chaque classe, et trier le résultat par ordre décroissant de la
note:
Trier les résultats
Exemple :
Soit la table :
Eleves (Id , Nom, Prenom, Tel, NoteBac, dateNaissance, classe)
Afficher la note de bac moyenne de chaque classe, et trier le résultat par ordre décroissant de la note:
SELECT classe, AVG(NoteBac) AS moyenne
FROM Eleves
GROUP BY classe
ORDER BY moyenne DESC;
Limitation de l’affichage
Pour limiter le nombre d’éléments (lignes) retournés par une requête, on utilisera la commande :
SELECT ... FROM ... LIMIT N OFFSET M;
▪ LIMIT N avec N un entier, permet de limiter le nombre de résultats à N.
▪ OFFSET M indique de passer M lignes avant de renvoyer les lignes restantes.
Limitation de l’affichage
Exemple : Articles (idArticle, nom, prix)
Afficher les noms des trois plus chers articles:
SELECT nom
FROM Articles
ORDER BY prix DESC
LIMIT 3 ;
Requêtes imbriquées
Une requête imbriquée est une sous requête exécutée à l’intérieur d’une autre requête SQL.
Elle remplace souvent une constante au sein d’une clause WHERE ou HAVING.
Syntaxe SQL :
SELECT . . . FROM . . . WHERE
[expression] opérateur (SELECT . . . FROM . . . ) ;
Requêtes imbriquées
Opérateur peut être :
• Si la requête renvoie un seul résultat :
▪ Un opérateur simple : =,<,>, ! =,,, ...
• Si elle renvoie plusieurs résultats :
▪ Un opérateur simple + ALL
▪ Un opérateur simple + ANY
▪ IN (équivalent à = ANY)
▪ NOT IN (équivalent a!=ALL)
▪ EXISTS (renvoie vrai si la sous requête retourne au moins une ligne)
▪ NOT EXISTS (renvoie vrai si la sous requête ne retourne aucune ligne)
Remarque : ALL et ANY ne fonctionnent pas en SQLITE.
Requêtes imbriquées
Exemple : Eleves (IdEleve, Nom, Prenom, Tel, NoteBac, dateNaissance, classe)
Afficher les élèves dont la note du bac est supérieur ou égale à la moyenne des notes de bac de
tous les élèves
Requêtes imbriquées
Exemple : Eleves (IdEleve, Nom, Prenom, Tel, NoteBac, dateNaissance, classe)
Afficher les élèves dont la note du bac est supérieur ou égale à la moyenne des notes de bac de
tous les élèves:
SELECT *
FROM Eleves
WHERE NoteBac >= (SELECT AVG(NoteBac) FROM eleves) ;
Requêtes imbriquées
Exemple : Eleves (IdEleve, Nom, Prenom, Tel, NoteBac, dateNaissance, classe)
Afficher l’élève qui a la meilleure note de bac
Requêtes imbriquées
Exemple : Eleves (IdEleve, Nom, Prenom, Tel, NoteBac, dateNaissance, classe)
Afficher l’élève qui a la meilleure note de bac
SELECT *
FROM Eleves
WHERE NoteBac >= ALL (SELECT NoteBac FROM eleves) ;
Requêtes imbriquées
Exemple : Eleves (IdEleve, Nom, Prenom, Tel, NoteBac, dateNaissance, classe)
Afficher l’élève qui a la meilleure note de bac
SELECT *
FROM Eleves
WHERE NoteBac = (SELECT MAX (NoteBac) FROM eleves) ;
Requêtes imbriquées
Exemple :
• Eleves (IdEleve , Nom, Prenom, Tel, NoteBac, dateNaissance, classe)
• Livres (idLivre , titre, auteur)
• Emprunter (#IdEleve ,#idLivre )
Afficher les élèves qui ont empruntés le livre dont idLivre est 50
Requêtes imbriquées
Exemple :
• Eleves (IdEleve , Nom, Prenom, Tel, NoteBac, dateNaissance, classe)
• Livres (idLivre , titre, auteur)
• Emprunter (#IdEleve ,#idLivre )
Afficher les élèves qui ont empruntés le livre dont idLivre est 50
SELECT *
FROM Eleves
WHERE idEleve IN (SELECT idEleve FROM Emprunter WHERE idLivre=50) ;
Requêtes imbriquées
Exemple :
• Eleves (IdEleve , Nom, Prenom, Tel, NoteBac, dateNaissance, classe)
• Livres (idLivre , titre, auteur)
• Emprunter (#IdEleve ,#idLivre )
Afficher les élèves qui ont empruntés le livre dont idLivre est 50
SELECT *
FROM Eleves
WHERE EXISTS (SELECT * FROM Emprunter WHERE idLivre=50 AND
Emprunter.idEleve=Eleves.idEleve) ;
