récupérer les informations stockées dans la base de manière cohérente. select { [, … ] | *}...

◦Récupérer les informations stockées dans la base de manière cohérente.

select { <col>[, <col>… ] | *} from <tab> [where <expr>];

◦ SELECT <liste de projection> FROM <liste de tables> [WHERE <critère de jointure> AND <critère de

restriction>] [GROUP BY <attributs de partitionnement>] [HAVING <citère de restriction>]

Restriction : ◦ arithmétique (=, <, >, )◦ textuelle (LIKE)◦ sur intervalle (BETWEEN)◦ sur liste (IN)

Possibilité de blocs imbriqués par : IN, EXISTS, NOT EXISTS, ALL, SOME, ANY

◦ Récupérer tous les enregistrements de la table MA_TABLE

SELECT * FROM MA_TABLE;

◦ Récupérer seulement les colonnes C1 et C2

SELECT C1, C2 FROM MA_TABLE;

◦ L’ajout d’un prédicat sur un ordre select s’exprime dans une clause WHERE par :

SELECT * FROM MA_TABLE WHERE <EXPR>;

◦ Exemple :

SELECT * FROM MA_TABLE WHERE C1 = 600;

Opérateurs de comparaison := , != , <> , <= , >= , < , >

Opérateurs logiques AND, OR, NOT

Exemple :SELECT * from MA_TABLE

where c1 < > 12 and c2 = 15;

L’opérateur LIKE permet de tester les chaînes de caractères avec l’expression d’un motif (pattern)

Les méta-caractères sont :

_ 1 seul caractère% 0 ou plusieurs caractères

Attention : bien que la syntaxe SQL ne soit pas sensible à la casse, les tests sur les

chaînes le sont.

Toutes les peronnes dont le nom commence par A :

SELECT * from personne where nom LIKE ‘ A% ’ ;

Tous les prénoms dont le nom contient un X en deuxième position.

SELECT prenom from personne where nom LIKE ‘ _X% ’ ;

Cas particulier des champs ‘NULL’ :Il sont testés avec le prédicat IS NULL ouIS NOT NULL

Exemple : tous les enregistrements dont la colonne C2 n’est pas remplie :

SELECT * FROM MA_TABLEWHERE C2 IS NULL;

<search condition> ::= [NOT] <nom_colonne> constante <nom_colonne>

<nom_colonne> LIKE <modèle_de_chaîne><nom_colonne> IN <liste_de_valeurs>

<nom_colonne> (ALL ANY SOME)<liste_de_valeurs>

EXISTS <liste_de_valeurs>UNIQUE <liste_de_valeurs><tuple> MATCH [UNIQUE] <liste_de_tuples>

<nom_colonne> BETWEEN constante AND constante <search condition> AND OR <search condition>

avec : Opérateur de comparaison

Remarque: <liste_de_valeurs> peut être dynamiquement déterminée par une requête

Le produit cartésien de A et B s’exprime enSQL par :

SELECT * FROM A, B;

ATTENTION : le produit cartésien n'a pasde sens logique, c'est la première étaped'une jointure

La jointure de 2 tables s'effectue en ajoutant un pivot au produit cartésien :

SELECT * FROM A, B WHERE A.C1 = B.C2;

Pour effectuer des jointures multiples :SELECT * FROM T1, T2, T3…

WHEREPIVOT1 AND PIVOT2 AND PIVOT3

AND …ATTENTION : il est indispensable de citerautant de pivots que de jointures.

Pour effectuer une auto-jointure (jointured'une table sur elle même) il est nécessairede déclarer des synonymes dits ALIAS surla table concernée :SELECT a.first_name, b.first_name FROM

employee a, employee b WHERE a.manager_id = b.employee_id;

Concaténation : ||SELECT first_name || ' ' ||

last_name FROM …• Sous-chaînes :

SUBSTR (chaîne, pos, long)

ATTENTION : En SQL les chaînes de caractères sont indicées à partir de 1

Majuscule : UPPER(chaîne)

Minuscule : LOWER(chaîne)

Chaîne vers nombre : TO_NUMBER()Nombre vers chaîne : TO_CHAR(ch,

masque)

Le masque permet de définir des règles detransformation pour les nombres :

9 : chiffre quelconque non représenté pour les 0 non significatifs

0 : chiffre quelconque représenté . : point décimal apparent

Objectif Effectuer des opérations en regroupant plusieurs lignes d'un SELECT.

Définition : Un groupe est un ensemble de lignes, résultat d'une requête, qui ont une valeur commune sur un ensemble de colonnes.

Cet ensemble de colonnes est appelé le facteur de groupage.

Pour regrouper des données, il faut alors utiliser la clause GROUP BY suivi du facteur de groupage.

Select col1,col2, fonction d ’agrégat()

FROM tab1,tab2

where …

group by col1,col2 ;

Exemple : Afficher le nombre total et moyenne d'heures de vol par type d'avion et par année de mise en service, tri par type et année.

SELECT CodeType, AnnServ, SUM(NbHVol),

AVG(NbHVol)

from AVION

GROUP BY CodeType, AnnServ;

Une clause HAVING permet d'opposer un prédicat à un groupe.

Exemple : SELECT NumClient,sum(montant)

FROM commandes

GROUP BY NumClient

HAVING count(*)>3; La clause HAVING élimine des groupes comme

la clause WHERE élimine des lignes.

Moyenne : AVG Somme : SUM Minimum : MIN Maximum : MAX Dénombrement : COUNT Variance : VARIANCE Ecart-type : STTDEV

C ’est une requête dont le résultat sert de valeur de référence dans le prédicat

On peut imbriquer plusieurs requêtes, le résultat de chaque requête imbriquée servant de valeur de référence dans la condition de sélection de la requête de niveau supérieure: la requête principale.

La sous-requête est évaluée entièrement avant la requête principale.

Exemple : affichez les employés ayant le même poste que l’employé Martin

Solution 1 avec auto-jointure :select nom from employe e1, employe e2 where e1.nom=‘ Martin  ’ and e1.poste=e2.poste ;

Solution 2 : avec sous-requête SELECT nom

FROM employé WHERE poste = (SELECT poste from employé WHERE nom=‘Martin’);

C ’est une sous requête indépendante qui ne renvoie qu ’une ligne.

Sous-requêtes indépendantes ◦ qui renvoie une seul ligne

Une sous-requête de ce type s'utilise lorsque la condition de sélection fait référence à une valeur.

◦ qui renvoie plusieurs lignes Une sous-requête de ce type s'utilise lorsque la

condition de sélection fait référence à une liste de valeurs.

sous requêtes dépendantes La sous-requête est évaluée pour chaque ligne de la

requête principale.

La condition de sélection utilise un opérateur IN, ou un opérateur simple =, !=, <>, <, >, <=, >= suivi de ALL ou ANY.

Exemple : lister les clients qui ont passé des commandes le 5/6/98.

SELECT NumClient FROM CLIENT

WHERE NumClient IN(SELECT NumClient FROM COMMANDE WHERE DateCommande='05-JUN-98');

=ANY est équivalent à IN !=ALL est équivalent à NOT IN

La sous-requête est évaluée pour chaque ligne de la requête principale.

Exemple: Lister les vols et les numéros de pilotes qui habitent la même ville d'un départ d'un vol.

SELECT NumPilote, NumVol, VilDep from PILOTE p, VOL where VilDep =(SELECT Adresse from PILOTE where p.NumPilote=PILOTE.NumPilote );

L'opérateur EXISTS permet de construire un prédicat évalué à VRAI si la sous-requête renvoie au moins une ligne.

SELECT NomCol [, ….] FROM NomTable [, ….][WHERE EXISTS (SELECT …)

Exemple : Lister les vols ayant utilisé au moins une fois un avion de code type '741' .

SELECT NumVol FROM AFFECTATION A WHERE EXISTS(SELECT * from AVION WHERE A.NumAvion=AVION.NumAvion AND CodeType='741');

=> Cette requête utilise une sous-requête dépendante.

Exemple : Lister les avions du même type que l'avion numéro '8832' et mis en service la même année.SELECT NumAvion

FROM AVION

WHERE (AnnServ, CodeType)=(SELECT AnnServ, CodeType from AVION where NumAvion='8832');

La jointure naturelle peut s ’écrire sous la formeselect *from tab1 natural join tab2 natural join tab3 ...;

si les nom colonnes correspondent, il est inutile d ’expliciter la jointure.

Si les noms de colonnes ne sont pas les mêmes dans les tables alors on écrit explicitement les conditions de jointures

Exemples :◦ Ces trois syntaxes sont identiques

select * from pilote natural join affectation natural join vol;

select * from pilote p natural join affectation a join vol v on v.numvol=a.numvol and a.numpilote.p.numpilote ;

select * from pilote p,affectation a,vol v where v.numvol=a.numvol and a.numpilote.p.numpilote;

Une jointure externe entre deux tables agit comme une jointure naturelle entre deux tables mais permet d ’obtenir aussi les lignes qui n ’ont pas de correspondance entre la table 1 et la table 2.

Table1

col1 col12

a x

b y

c z

Table2

col1 col22

a 1

b 2

Ligne sans correspondance dans la table 2

Lignes avec correspondance

Select * from table1 natural join table2;

◦ ne donne que les lignes en correspondance select * from table1 left outer join table2;

◦ donne toutes les lignes en correspondance et les lignes de table 1 sans correspondance

Table1

col1 col12

a x

b y

c z

Table2

col1 col22

a 1

b 2

Exemple :◦ Liste des clients et somme totale des factures

(même ceux qui n ’ont pas été facturé) :

select Idc,nom,sum(totalfact) from client left outer join facture on client.Idc=facture.idc group by client.idc ;

Même exemple mais avec une autre syntaxe◦ le signe (+) se situe sur la colonne de la table

subordonnée.

select Idc,nom,sum(totalfact) from client , facture where client.Idc=facture.idc (+) group by client.idc ;

Algèbre relationnelle : R U S SQL :

SELECT * FROM RUNIONSELECT * FROM S

Algèbre relationnelle : Intersect (R , S) SQL :

SELECT * FROM R INTERSECT SELECT * FROM S

Algèbre relationnelle : R - S SQL : SELECT * FROM R

MINUS / Except SELECT * FROM S

On peut également représenter la différence par :

SELECT * FROM R where not exists ( select * from S )

Tri des n-uplets selon un ou plusieurs attributs ;

Le tri peut être croissant ou décroissant sur chaque attribut ;

Ajout à la requête d'une clause “ORDER BY” en fin de requête suivie d'une liste de couple (attr, sens)

où attr est un attribut de la relation et sens est :

• « ASC » pour trier de manière croissante• « DESC » pour trier de manière décroissante

SELECT liste d'attributs FROM liste de relations WHERE condition GROUP BY liste d'attributs HAVING condition ORDER BY liste d'attributs avec sens de tri

Les clauses « WHERE », « GROUP BY », « HAVING » et « ORDER BY » sont facultatives

Il n'est possible d'utiliser la clause « HAVING » qu'aprèsun « GROUP BY »

La clause INSERT INTO … VALUES permet d'insérer de nouvelles lignes dans la table.

Syntaxe : INSERT INTO <nom_table>

VALUES (valeur1, valeur2,…, valeurn)ou

INSERT INTO <nom_table> (attr1, attr2, …, attrm)VALUES (v1, v2,…, vm)

Si une valeur n'est pas précisée pour un attribut qui n’est pas obligatoire, alors le système insère la valeur NULL ou ce qui a été spécifié dans la clause DEFAULT du CREATE TABLE.

n = nombre d'attributs de la tablem <= n

Insert into emloye values(123,’Mohammed’, ‘20/05/1970’, ‘maroc’);

Insert into emloye(nom,prénon,pays) values(‘Benali’,’Mohammed, ‘maroc’);

Attention : Les données de type texte ou date doivent être entre ' '. Oracle : n’oubliez pas la clause COMMIT pour rendre les

modifications permanentes SQL Server : le format des dates par défaut à l’insertion est

‘JJ/MM/AA’

La clause INSERT INTO … SELECT permet d'insérer de nouvelles lignes dans la table à partir d'une table ou vue existante.

Syntaxe :INSERT INTO <nom_table>

SELECT …

INSERT INTO <nom_table> (attr1, attr2, …, attrm)SELECT r1, r2,…, rm

FROM … Si une valeur n'est pas précisée pour un attribut qui n’est pas

obligatoire, alors le système insère la valeur NULL ou ce qui a été spécifié dans la clause DEFAULT du CREATE TABLE.

1) CREATE TABLE Employé 2000

( id_employe INTEGER, nom VARCHAR(30), prénom VARCHAR(20), DateEmbauche datetime,

id_entreprise INTEGER, Salaire_base MONEY );

2) INSERT INTO Employe2000

SELECT *

FROM Employe

WHERE DateEmbauche> ’01/01/2000’;

La clause DELETE permet de supprimer des lignes d'une table.

Syntaxe :DELETE FROM <nom_table>

[ WHERE <critère> ]

Exemple :DELETE FROM Employe

WHERE Employe_id=109;

DELETE FROM Employe;Attention si il existe une contrainte référentielle!

La clause UPDATE permet de modifier des lignes d'une table.

Syntaxe :UPDATE <nom_table>SET <nom_attribut> = <expression>[ WHERE <critère> ]

Exemple :

UPDATE Employe UPDATE Employe

SET salaire=11500 SET pays = ‘Maroc’;

WHERE id_employe=107;