elements sql

[ phr @ skynet . be ] 09/03/2015 PHR SQL - 1

Elments de SQL


Tables des matires des pages SQL 1. Introduction au Modle Relationnel ............................................................................................................................... 3 2. Le langage SQL .............................................................................................................................................................. 6 3. Les oprateurs de base .................................................................................................................................................... 8

3.1. La projection ........................................................................................................................................................ 8 3.1.1. La projection en SQL ............................................................................................................................... 8

3.2. La slection .......................................................................................................................................................... 8 3.2.1. La slection en SQL ................................................................................................................................. 8

3.3. Les jointures ........................................................................................................................................................ 9 3.3.1. Les jointures en SQL .............................................................................................................................. 11

3.4. Lunion .............................................................................................................................................................. 13 3.4.1. Lunion en SQL ...................................................................................................................................... 13

3.5. Lintersection ..................................................................................................................................................... 13 3.5.1. Lintersection en SQL ............................................................................................................................ 15

3.6. La diffrence ...................................................................................................................................................... 15 3.6.1. La diffrence en SQL ............................................................................................................................. 15

3.7. Le produit cartsien ........................................................................................................................................... 17 3.7.1. Le produit cartsien en SQL ................................................................................................................... 17

3.8. La division ......................................................................................................................................................... 19 4. Les oprateurs complmentaires ................................................................................................................................... 21

4.1. Le renommage ................................................................................................................................................... 21 4.1.1. Lattribut renomm ................................................................................................................................ 21 4.1.2. Lentit renomme ................................................................................................................................. 21

4.2. Les calculs ......................................................................................................................................................... 23 4.2.1. Les fonctions statistiques de base en SQL ............................................................................................. 23 4.2.2. La fonction de comptage ........................................................................................................................ 23

4.3. Les agrgations .................................................................................................................................................. 23 4.4. Le tri .................................................................................................................................................................. 24

5. Approche visuelle de llaboration des requtes .......................................................................................................... 24 5.1. Visualisation des entits .................................................................................................................................... 24 5.2. Elaboration de requtes sur base lapproche visuelle ........................................................................................ 25

5.2.1. Requtes sur un ensemble unique. ......................................................................................................... 25 5.2.2. Requtes sur une paire densembles. ...................................................................................................... 25 5.2.3. Requtes sur trois ensembles ayant des intersections deux deux. ....................................................... 30 5.2.4. Requtes sur un ensemble densembles ayant des intersections deux deux. ....................................... 31 5.2.5. Requtes sur trois ensembles, ou plus, prsentant une intersection commune....................................... 34 5.2.6. A la recherche de linexistant. ................................................................................................................ 36

5.3. Exemples de rsolutions de problmes. ............................................................................................................. 37 6. Oprations de cration et suppression des tables .......................................................................................................... 46

6.1. Cration des tables ............................................................................................................................................. 46 6.1.1. Dfinition des cls primaires .................................................................................................................. 46 6.1.2. Dfinition des cls trangres et des relations ........................................................................................ 46

6.2. Cration dune table par copie dune autre ........................................................................................................ 46 6.2.1. Cration et copie des donnes ................................................................................................................ 46 6.2.2. Cration et copie de la structure seule .................................................................................................... 46

6.3. Suppression dune table ..................................................................................................................................... 46 7. Oprations de cration, modification et suppression dindex et de colonnes ............................................................... 48 8. Oprations de maintenance des tuples .......................................................................................................................... 49

8.1. Lajout denregistrements .................................................................................................................................. 49 8.2. La modification denregistrements .................................................................................................................... 49 8.3. La suppression denregistrements ..................................................................................................................... 49

8.3.1. Suppression de tuples contenant un identifiant et des doublons sur un attribut donn .......................... 49


1. Introduction au Modle Relationnel Le modle relationnel est un modle d'organisation des donnes avec leurs noms et types sous forme de tables, chacune reprsentant une relation, au sens mathmatique de la thorie des ensembles. Par exemple, les tables dcrites ci-dessous reprsentent lensemble des relations permettant la gestion dun rpertoire des adresses et des lignes de communications (tlphones, fax, ) de personnes. Ce sont les mthodes danalyse des donnes qui permettent la structuration dune base de donnes telle que celle-ci.

Les mathmaticiens utilisent une symbolique et une syntaxe particulire pour reprsenter le modle relationnel et ses oprations. Ils traitent de lalgbre relationnelle qui est un concept mathmatique de la relation dans la thorie des ensembles. L'algbre relationnelle a t invente en 1970 par E. F. Codd, le directeur de recherche du centre IBM de San Jos. Elle est constitue d'un ensemble d'oprations formelles sur les relations. Les oprations relationnelles permettent de crer une nouvelle relation (table) partir d'oprations lmentaires sur d'autres tables (par exemple l'union, l'intersection, ou encore la diffrence). Pour modliser les requtes laborer en vue dextractions particulires de donnes, linformaticien peut acqurir la matrise des outils de lalgbre relationnelle sans sembarrasser du formalisme employ par les mathmaticiens. Il y a toutefois un minimum de vocabulaire appropri aux bases de donnes quil convient de connatre. Une partie de ce vocabulaire se dfinit aisment ci aprs par comparaison avec le vocabulaire adapt aux fichiers. Lautre partie du vocabulaire tient davantage lalgbre relationnelle voire, de manire plus gnrale lalgbre ensembliste et, pour lessentiel, il sert nommer les oprations ralises. Il est tout naturellement dfini plus loin en mme temps que les oprations elles-mmes.

Nom CPost Localit Pierre 1300 Wavre Georges 1400 Nivelles Jacques 1490 Court-St-Et. Michel 1370 Jodoigne Charles 1300 Limal Serge 1315 Glimes Marc 6000 Charleroi Henri 6180 Courcelles

3 colonnes

8 lignes

S.G.B.D.R.

Entit

Attribut

Tuple

Proprit

Degr

Cardinalit

Fichiers classiques

Fichier

Champ ou zone

Enregistrement

Donne ou valeur

Nombre de champs

Nombre denregistrements

TPays IdPays Entier (non auto) LibPays Texte TCommunication IdCom Entier (non auto) LibCom Texte

TLocaliteIdLoc Entier (non auto) CPost Texte LibLoc Texte XIdPays Entier TNumero XIdPers Entier XIdCom Entier Numero Texte

TPersonne IdPers Entier (non auto) NomPers Texte PrenomPers Texte AdrPers Texte XIdLoc Entier


Il convient de dfinir encore deux termes, schma et domaine, qui se retrouvent souvent dans la littrature traitant des bases de donnes relationnelles ainsi que dans ces pages.


Le schma dune entit est lensemble de ses attributs et de leurs types de proprits. Deux entits ont le mme schma si leurs attributs respectifs ont le mme nom et le mme type de proprits. Par extension, schma est galement utilis au niveau de la base de donnes elle-mme auquel cas il est lensemble de ses schmas. Exemples : Les entits TClient et TFournisseur suivantes ont un mme schma. Les entits TClient et TFournisseur suivantes nont pas le mme schma. Le domaine est lensemble des valeurs possibles pour les proprits dun ou plusieurs attributs qui peuvent dans ce cas, avoir des noms diffrents. Les valeurs possibles se reprent bien entendu par rapport leurs types, mais aussi dun point de vue logique faute de quoi la comparaison de domaines ne peut livrer des rsultats cohrents. Exemples : Les attributs CapitalesDuMonde et LieuDeNaissance suivants peuvent tre considrs comme appartenant un mme domaine dans la mesure o ils dsignent tous deux des villes.

CapitalesDuMonde de type alphanumrique de 40 caractres LieuDeNaissance de type alphanumrique de 40 caractres

Les attributs LangueMaternelle et Profession suivants ne peuvent tre considrs comme appartement un mme domaine dans la mesure o ils dsignent des proprits sans lien logique entre elles.

LangueMaternelle de type alphanumrique de 40 caractres Profession de type alphanumrique de 40 caractres

Les diffrentes tables dune base de donnes relationnelle sont lies entre elles par des relations au sens commun de lanalyse. Ce sont aussi les mthodes danalyse des donnes qui permettent la dfinition de ces relations. Ainsi, lexemple de la base des donnes des personnes reprsente les ensembles dinformations que sont les personnes (TPersonne), les localits (TLocalite), les pays (TPays), les lignes de communication (TCommunication), et enfin les numros de chaque ligne de chaque personne (TNumero). Dans chaque table, un attribut ne prend qu'une seule valeur par tuple. L'ordre des lignes et des colonnes n'a pas d'importance. Chaque table doit avoir une cl primaire constitue par un ensemble minimum d'attributs permettant de distinguer chaque tuple de la relation par rapport tous les autres. Chaque ensemble dattributs formant la cl primaire d'un tuple est donc unique au sein d'une table. C'est ainsi que dans la table TPersonne, chaque personne a un numro distinctif (IdPers) nomm aussi identifiant qui est dclar comme cl primaire dans la base de donnes. Les relations dfinies par lanalyse des donnes, qui sont galement appeles liens smantiques, sont ralises par l'intermdiaire de cls trangres faisant elles-mmes rfrence des cls primaires d'autres tables. Ainsi, dans la table TPersonne la cl trangre XIdLoc (faisant rfrence la cl primaire IdLoc de la table TLoc) traduit les deux rgles de gestion suivantes :

TClient TFournisseurNom Chiffre Nom Chiffre Belgacom 1150 ABComputer 28500 Dupont 19950 Belgacom 6250

TClient TFournisseurNomCli CACli NomFou CAFouBelgacom 1150 ABComputer 28500Dupont 19950 Belgacom 6250


Une personne est domicilie dans une localit Une localit est habite par plusieurs personnes

Il existe deux grands types de liens : Un - Plusieurs (comme le prcdent) et Plusieurs - Plusieurs. La ralisation de ce dernier type de liens, un peu plus complexe, passe par l'utilisation d'une table intermdiaire dont la cl primaire est forme des cls trangres des tables qu'elle relie. La table TNumero de la base prcdente doit contenir le numro dappel de chaque ligne de chaque personne. Elle exprime les deux rgles de gestion suivantes :

Une personne possde plusieurs moyens communications Un moyen de communication sert plusieurs personnes

Le modle relationnel est le plus souvent dcrit sous la forme suivante, les cls primaires tant soulignes et les cls trangres marques par un signe distinctif (ici *, en plus de commencer par la lettre X de eXterne).

TPAYS (IdPays, LibPays) TLOCALITE (IdLoc, CPost, LibLoc, XIdPays*) TPERSONNE (IdPers, NomPers, PrenomPers, AdrPers, XIdLoc*) TNUMEROS (XIdPers*, XIdCom*, Numero) TCOMMUNICATION (IdCom, LibCom)

On peut aussi le reprsenter sous forme graphique, de manire mieux visualiser et interprter les liens.

Dans le cadre d'un projet d'informatisation, la conception d'une base de donnes relationnelle passe d'abord par l'identification des objets de gestion (personnes, moyens de communications, ) et des rgles de gestion du domaine modlis (interviews des utilisateurs, tude des documents manipuls, des fichiers existants, ). Une fois nonces et valides, ces rgles conduisent automatiquement la structure du modle relationnel correspondant.

2. Le langage SQL Directement driv de lalgbre relationnelle de Codd, SQL (Structured Query Language) est un langage complet de gestion de bases de donnes relationnelles conu par IBM. Il est le langage standard des systmes de gestion de bases de donnes relationnelles (SGBDR). C'est la fois :

- un langage d'interrogation de la base (ordre SELECT) - un langage de manipulation des donnes (DML : ordres UPDATE, INSERT, DELETE) - un langage de dfinition des donnes (DDL : ordres CREATE, ALTER, DROP) - un langage de contrle de l'accs aux donnes (DCL : ordres GRANT, REVOKE).

Le langage SQL est utilis par les principaux SGBDR : DB2, Oracle, Informix, Ingres, ... Chacun de ces SGBDR a cependant sa propre variante du langage, mais prsente toutefois un noyau de commandes commun. Une premire tentative de normalisation de SQL a t faite en 1986, mais cette norme a gnralement t ignore par les diteurs de SGBD. La norme SQL-2 (ou SQI.-92) de 1992 est plus gnralement respecte, mais les SGBDR dominants du march (en particulier Oracle) intgrent toujours leurs particularits.

TCommunicationIdCom LibCom

TPays IdPays LibPays

TLocalite IdLoc CPost LibLoc XIdPays*

TPersonneIdPersNomPersPrenomPersAdrPersXIdLoc*

TNumeroXIdPers*XIdCom*Numero


La norme SQL-2 dfinit trois niveaux de standardisation : - Entry Level (conditions minimales de la norme SQL-2) - Intermediate SQL - Full SQL (ensemble de la norme)

Cest de la volont des producteurs de se diffrencier, jointe aux diffrents degrs de normalisation, que rsultent les diffrences entre les SQL. Le SQL prsent dans ces pages est essentiellement celui du noyau de commandes commun.


3. Les oprateurs de base Les oprateurs de base dans les oprations concernant les bases de donnes relationnelles sont la projection, la slection, la jointure, lunion, lintersection, la diffrence et le produit. Ils sont gnralement reprsents en algbre relationnelle par des symboles tels que pi minuscule () pour la projection, sigma minuscule () pour la slection, et dautres symboles de lalgbre ensembliste. Ces oprateurs qui ncessitent parfois quelques paramtres, seffectuent sur une ou plusieurs relations (entits) appeles ici oprantes et toutes fournissent une nouvelle relation. 3.1. La projection Cet oprateur qui naccepte quun seul oprante requiert une liste dattributs comme paramtres. La relation rsultante (R) est lensemble des attributs spcifis de tous les tuples de la relation oprante (R0), ventuellement sans doublon. Loprateur de projection est symbolis dans ces pages par le sigle PJ.

R = PJ(R0, Liste_Attributs) Par exemple, la projection suivante donne les nom et prnom de chaque personne de la relation TPersonne.

R = PJ(TPersonne, Nom, Prenom) 3.1.1. La projection en SQL

SELECT Nom, Prenom FROM TPersonne;

3.2. La slection Cet oprateur qui naccepte quun seul oprante requiert lexpression dune condition comme paramtres. La relation rsultante (R) est lensemble des tuples de la relation oprante (R0), qui satisfont la condition exprime. La condition concerne les proprits dun ou plusieurs attributs et elle est exprime laide des oprateurs relationnels ( =, >, =, = "Didier") 3.2.1. La slection en SQL

SELECT * FROM TPersonne WHERE Prenom >= 'Didier'; Autres exemples : Soit la table TETUDIANT(IdEtudiant, Nom, Age, CodePostal, Ville)

SELECT * FROM TETUDIANT WHERE Age IN (19, 20, 21, 22, 23); SELECT * FROM TETUDIANT WHERE Age BETWEEN 19 AND 23; SELECT * FROM TETUDIANT


WHERE CodePostal LIKE '13%'; // sous Access : LIKE "13*" SELECT * FROM TETUDIANT WHERE CodePostal LIKE '13__'; // sous Access : LIKE "13??" SELECT * FROM TETUDIANT WHERE CodePostal >= '1300';

3.3. Les jointures Cet oprateur ncessite deux oprantes qui sont des relations (R1, R2) ayant au moins un attribut dfini dans le mme domaine. La condition de jointure concerne un ou plusieurs de ces attributs. La relation rsultante (R) est un ensemble de tuples qui dpend du type de jointure ralise. Il existe plusieurs types de jointures. La plus commune est la jointure interne (INNER JOIN) qui est aussi appele quijointure lorsque ses conditions vrifient des galits entre attributs. Une jointure interne ne livre que des tuples respectant les conditions. Elle est limite, emprisonne dans sa condition, ferme sur elle-mme, intriorise, intrieure. Les autres jointures sont des jointures externes dont les plus courantes se diffrencient en jointure gauche et en jointure droite (LEFT OUTER JOIN, RIGHT OUTER JOIN). Une jointure externe donne tous les tuples dune des deux tables, mme sil nexiste pas de correspondance dans lautre. Elle est largie, elle outrepasse sa condition, elle est ouverte, extriorise, extrieure. Les prcisions gauche et droite indiquent celle des deux tables qui doit fournir tous ses tuples, lautre table ne fournissant que des tuples satisfaisant la condition. A ces jointures externes sajoute encore la pleine jointure (FULL OUTER JOIN) supporte par certains SGBD, tel que SQL Serveur par exemple, qui donne tous les tuples des deux tables en les associant lorsquils satisfont la condition. Il faut encore noter quil existe aussi, notamment en SQL Serveur, une jointure croise (CROSS JOIN) qui ne ncessite aucune condition et qui ralise en fait le produit cartsien des deux tables. Les conditions de jointure sexpriment laide des oprateurs relationnels (=, >, =,


TClient TFournisseur

Nom Chiffre Nom Chiffre Belgacom 1150 ABComputer 28500 Dupont 19950 Belgacom 6250 Electrabel 3750 Carglass 1125 Informatifacile 3560 Electrabel 9520 Format Logique 15750 Informatifacile 3560 New Form 16840 Transport Claude 12500

Pour obtenir les tuples des tiers qui sont la fois client et fournisseur.

R = JI(TClient, TFournisseur, TClient.Nom = TFournisseur.Nom) TClient.Nom TClient.Chiffre TFournisseur.Nom TFournisseur.ChiffreBelgacom 1150 Belgacom 6250Electrabel 3750 Electrabel 9520Informatifacile 3560 Informatifacile 3560 Pour obtenir les tuples de tous clients ainsi que ceux des fournisseurs qui sont galement clients.

R = JG(TClient, TFournisseur, TClient.Nom = TFournisseur.Nom) TClient.Nom TClient.Chiffre TFournisseur.Nom TFournisseur.ChiffreBelgacom 1150 Belgacom 6250Dupont 19950Electrabel 3750 Electrabel 9520Informatifacile 3560 Informatifacile 3560Format Logique 15750New Form 16840

R = JD(TFournisseur, TClient, TFournisseur.Nom = TClient.Nom)

TFournisseur.Nom TFournisseur.Chiffre TClient.Nom TClient.ChiffreBelgacom 6250 Belgacom 1150 Dupont 19950Electrabel 9520 Electrabel 3750Informatifacile 3560 Informatifacile 3560 Format Logique 15750 New Form 16840 Pour obtenir les tuples de tous les fournisseurs ainsi que ceux des clients qui sont galement fournisseurs.

R = JG(TFournisseur, TClient, TFournisseur.Nom = TClient.Nom) TFournisseur.Nom TFournisseur.Chiffre TClient.Nom TClient.ChiffreABComputer 28500Belgacom 6250 Belgacom 1150Carglass 1125Electrabel 9520 Electrabel 3750Informatifacile 3560 Informatifacile 3560Transport Claude 12500

R = JD(TClient, TFournisseur, TClient.Nom = TFournisseur.Nom) TClient.Nom TClient.Chiffre TFournisseur.Nom TFournisseur.Chiffre ABComputer 28500Belgacom 1150 Belgacom 6250 Carglass 1125


Electrabel 3750 Electrabel 9520Informatifacile 3560 Informatifacile 3560 Transport Claude 12500 Pour obtenir les tuples des tiers qui sont la fois client et fournisseur, mais dont le chiffre daffaires du ct client est moindre que celui du ct fournisseur.

R = JI(TClient, TFournisseur, TClient.Nom = TFournisseur.Nom ET TClient.Chiffre < TFournisseur.Chiffre)

TClient.Nom TClient.Chiffre TFournisseur.Nom TFournisseur.ChiffreBelgacom 1150 Belgacom 6250Electrabel 3750 Electrabel 9520 Pour obtenir les tuples de tous les fournisseurs et ceux de tous les clients associs lorsquils sont la fois client et fournisseur.

R = JF(TFournisseur, TClient, TFournisseur.Nom = TClient.Nom) TFournisseur.Nom TFournisseur.Chiffre TClient.Nom TClient.ChiffreABComputer 28500Belgacom 6250 Belgacom 1150Carglass 1125Electrabel 9520 Electrabel 3750Informatifacile 3560 Informatifacile 3560Transport Claude 12500 Dupont 19950 Format Logique 15750 Pour obtenir toutes les personnes de TPersonne, chacune avec sa localit sil y en a une.

R = JG(TPersonne, TLocalite, TPersonne.XIdLoc = TLocalite.IdLoc) R = JD(TLocalite, TPersonne, TLocalite.IdLoc = TPersonne.XIdLoc)

Pour obtenir toutes les localites de TLocalite, chacune avec tous ses habitants sil y en existe.

R = JG(TLocalite, TPersonne, TLocalite.IdLoc = TPersonne.XIdLoc) R = JD(TPersonne, TLocalite, TPersonne.XIdLoc = TLocalite.IdLoc)

3.3.1. Les jointures en SQL Pour obtenir les tuples des tiers qui sont la fois client et fournisseur.

SELECT * FROM TClient INNER JOIN TFournisseur ON TClient.Nom = TFournisseur.Nom; Pour obtenir les tuples de tous clients ainsi que ceux des fournisseurs qui sont galement clients.

SELECT * FROM TClient LEFT OUTER JOIN TFournisseur ON TClient.Nom = TFournisseur.Nom;

ou SELECT * FROM TFournisseur RIGHT OUTER JOIN TClient ON TFournisseur.Nom = TClient.Nom;

Pour obtenir les tuples de tous les fournisseurs ainsi que ceux des clients qui sont galement fournisseurs.

SELECT * FROM TFournisseur LEFT OUTER JOIN TClient ON TFournisseur.Nom = TClient.Nom;

ou


SELECT * FROM TClient RIGHT OUTER JOIN TFournisseur ON TClient.Nom = TFournisseur.Nom;

Pour obtenir les tuples de tous les fournisseurs et ceux de tous les clients associs lorsquils sont la fois client et fournisseur.

SELECT * FROM TFournisseur FULL OUTER JOIN TClient ON TFournisseur.Nom = TClient.Nom;

Pour obtenir les tuples des tiers qui sont la fois client et fournisseur, mais dont le chiffre daffaires du ct client est moindre que celui du ct fournisseur.

SELECT * FROM TClient INNER JOIN TFournisseur ON TClient.Nom = TFournisseur.Nom AND TClient.Chiffre < TFournisseur.Chiffre;


Pour obtenir toutes les personnes de TPersonne, chacune avec sa localit sil y en a une.

SELECT * FROM TPersonne LEFT OUTER JOIN TLocalite ON TPersonne.XIdLoc = TLocalite.IdLoc;

ou SELECT * FROM TLocalite RIGHT OUTER JOIN TPersonne ON TLocalite.IdLoc = TPersonne.XIdLoc;

Pour obtenir toutes les localits de TLocalite, chacune avec tous ses habitants sil y en existe.

SELECT * FROM TLocalite LEFT OUTER JOIN TPersonne ON TLocalite.IdLoc = TPersonne.XIdLoc;

ou SELECT * FROM TPersonne RIGHT OUTER JOIN TLocalite ON TPersonne.XIdLoc = TLocalite.IdLoc;

Pour obtenir la liste des personnes, chacune avec le numro dappel pour chacune de ses lignes de communication.

SELECT TPersonne.Nom, TCommunication.Libelle, TNumero.Numero FROM TPersonne INNER JOIN (TCommunication INNER JOIN TNumero ON TCommunication.IdCom = TNumero.XIdCom) ON TPersonne.IdPers = TNumero.XIdPers;

ou (selon le SGBDR) SELECT TPersonne.Nom, TCommunication.Libelle, TNumero.Numero FROM TPersonne INNER JOIN TNumero ON TPersonne.IdPers = TNumero.XIdPers INNER JOIN TCommunication ON TNumero.XIdCom = TCommunication.IdCom;

3.4. Lunion Cet oprateur ncessite deux oprantes qui sont des relations (R1, R2) de mme schma. Loprateur produit un rsultat (R) qui possde les attributs des relations d'origine et tous les tuples de chacune. Loprateur dunion est reprsent dans ces pages par le sigle U.

R = U(R1, R2) R = U(TClient, TFournisseur)

3.4.1. Lunion en SQL Pour obtenir lunion des relations TClient et TFournisseur.

SELECT * FROM TClient UNION SELECT * FROM TFournisseur;

Pour nobtenir que les noms des relations TClient et TFournisseur.

SELECT NOM FROM TClient UNION SELECT NOM FROM TFournisseur;

3.5. Lintersection Cet oprateur ncessite deux oprantes qui sont des relations (R1, R2) de mme schma. Loprateur produit un rsultat (R) qui possde les tuples identiques de chacune sur base dun ou plusieurs (selon le SGBD) attributs spcifis. Loprateur dintersection est symbolis dans ces pages par le sigle I.

Nom ChiffreBelgacom 1150Dupont 19950Electrabel 3750Informatifacile 3560Format Logique 15750New Form 16840ABComputer 28500Belgacom 6250Carglass 1125Electrabel 9520Informatifacile 3560Transport Claude 12500


R = I(R1, R2, Attribut(s)_Determinant(s)_L_Egalite)


R = I(TClient, TFournisseur, Nom)

3.5.1. Lintersection en SQL

SELECT Nom FROM TClient WHERE Nom IN (SELECT Nom FROM TFournisseur);

ou SELECT Nom FROM TClient INTERSECT SELECT Nom FROM TFournisseur;

Pour obtenir les noms des tiers qui sont la fois client et fournisseur avec leur chiffre daffaires ct fournisseur.

SELECT Nom, Chiffre FROM TFournisseur WHERE Nom IN (SELECT Nom FROM TClient);

ou SELECT Nom, Chiffre FROM TFournisseur INTERSECT SELECT Nom FROM TClient;

3.6. La diffrence Cet oprateur ncessite deux oprantes qui sont des relations (R1, R2) de mme schma. Loprateur produit un rsultat (R) qui possde les tuples de la premire relation (R1) qui, sur base dun ou plusieurs (selon le SGBD) attributs spcifis, nappartiennent pas la deuxime (R2). Loprateur de diffrence est symbolis dans ces pages par le sigle DF. Attention, DF(R1,R2) ne donne pas le mme rsultat que DF(R2,R1).

R = DF(R1, R2, Attribut(s)_Determinant(s)_La_Difference) Exemple :

R = DF(TClient, TFournisseur, Nom)

3.6.1. La diffrence en SQL

SELECT Nom FROM TClient WHERE Nom NOT IN (SELECT Nom FROM TFournisseur);

ou SELECT Nom FROM TClient

Nom Belgacom ElectrabelInformatifacile

TClient TFournisseurNom Chiffre Nom ChiffreBelgacom 1150 ABComputer 28500Dupont 19950 Belgacom 6250Electrabel 3750 Carglass 1125Informatifacile 3560 Electrabel 9520Format Logique 15750 Informatifacile 3560New Form 16840 Transport Claude 12500

Nom Dupont Format LogiqueNew Form

TClient TFournisseurNom Chiffre Nom ChiffreBelgacom 1150 ABComputer 28500Dupont 19950 Belgacom 6250Electrabel 3750 Carglass 1125Informatifacile 3560 Electrabel 9520Format Logique 15750 Informatifacile 3560New Form 16840 Transport Claude 12500


EXCEPT SELECT Nom FROM TFournisseur;


Pour obtenir les noms et chiffres daffaires des tiers qui sont uniquement fournisseurs.

SELECT Nom, Chiffre FROM TFournisseur WHERE Nom NOT IN (SELECT Nom FROM TClient);

ou SELECT Nom, Chiffre FROM TFournisseur EXCEPT SELECT Nom FROM TClient;

ou (forme utile avec un SGBDR qui ne supporte ni lEXCEPT, ni les SELECT imbriqus) SELECT Nom, Chiffre FROM TClient LEFT OUTER JOIN TFournisseur ON TClient.Nom = TFournisseur.Nom WHERE TFournisseur.Nom IS NULL;

3.7. Le produit cartsien Cet oprateur ncessite deux oprantes qui sont des relations (R1, R2). Loprateur produit un rsultat (R) qui possde les tuples de la premire relation (R1), chacun associ lensemble des tuples de la deuxime (R2). Loprateur du produit est symbolis dans ces pages par le sigle PD. R = PD(R1,R2) Exemple sur la base de donnes des personnes :

TPersonne TCommunicationIdPers Prenom IdCom LibCom1 Pierre 1 Tl.priv2 Marc 2 GSM3 Michel

R = PD(TPersonne, TCommunication)

IdPers Prenom IdCom LibCom1 Pierre 1 Tl.priv1 Pierre 2 GSM2 Marc 1 Tl.priv2 Marc 2 GSM3 Michel 1 Tl.priv3 Michel 2 GSM

3.7.1. Le produit cartsien en SQL

SELECT * FROM TPersonne, TCommunication; Bien sr le rsultat du produit cartsien de cet exemple ne prsente pas dinformations trs utiles. Le suivant permet dtablir la liste des tudiants succeptibles de se prsenter chaque examen organis.

TEtudiant TExamenIdEtudiant Nom LibExamen1 Panzani Analyse2 Ergoton Langage C VB.Net

SELECT * FROM TExamen, TEtudiant;

LibExamen IdEtudiant NomAnalyse 1 PanzaniAnalyse 2 Ergoton


Langage C 1 PanzaniLangage C 2 ErgotonVB.Net 1 PanzaniVB.Net 2 Ergoton


3.8. La division Cet oprateur ncessite deux oprantes qui sont des relations (R1, R2) ayant au moins un attribut dfini dans le mme domaine. Tous les attributs du diviseur (R2) doivent tre des attributs du dividende (R1). La relation dividende doit avoir au moins une colonne de plus que la relation diviseur. La relation rsultante (R) contient les attributs spcifis des tuples du dividende qui sont en relation avec tous les tupes du diviseur. Il n'existe pas en SQL d'quivalent direct la division de lalgbre relationnelle. Cependant il est toujours possible de la raliser, notamment par l'intermdiaire des oprations de calcul et de regroupement. Ceux-ci sont expliqus plus loin. Exemple sur la base de donne des personnes prsente au dbut de ces pages :

TPersonne TCommunicationIdPers Prenom IdCom LibCom1 Pierre 1 Tl.priv2 Marc 2 GSM3 Michel 3 Fax

La jointure suivante (R) donne la liste des personnes, chacune avec le numro dappel pour chacune de ses lignes de communication.

SELECT TPersonne.Prenom, TCommunication.Libelle, TNumero.Numero FROM TPersonne INNER JOIN (TCommunication INNER JOIN TNumero ON TCommunication.IdCom = TNumero.XIdCom) ON TPersonne.IdPers = TNumero.XIdPers;

TPersonne.Prenom TCommunication.LibCom TNumero.Numero Pierre Tl.priv 11.11.11 Pierre GSM 12.12.12 Marc Tl.priv 21.21.21 Marc Fax 23.23.23 Michel Tl.priv 31.31.31 Michel GSM 32.32.32 Michel Fax 33.33.33

La division de cette relation rsultante (R) de la jointure par la relation TCommunication donne les attributs spcifis des tuples de R qui sont en relation avec tous les tuples de TCommunication. Dans cet exemple, seul Michel possde tous les moyens de communication existant dans TCommunication.

SELECT Prenom FROM (SELECT TPersonne.Prenom, TCommunication.Libelle, TNumero.Numero FROM TPersonne INNER JOIN (TCommunication INNER JOIN TNumero ON TCommunication.IdCom = TNumero.XIdCom) ON TPersonne.IdPers = TNumero.XidPers) GROUP BY Prenom HAVING COUNT(*) = (SELECT COUNT(*) FROM TCommunication);

Avec loprateur EXISTS :

SELECT Prenom FROM TPersonne AS TP WHERE NOT EXISTS

(SELECT NULL FROM TCommunication AS TC WHERE NOT EXISTS

(SELECT NULL FROM TNumero AS TN WHERE TP.IdPers = TN.XIdPers AND TC.IdCom = TN.XIdCom));


PrenomMichel


4. Les oprateurs complmentaires Ces oprateurs qui peuvent tre qualifis de complmentaires sont repris ici uniquement en SQL. Ils ne semploient que conjointement un oprateur de base, ou plusieurs combins, dont ils accroissent la puissance et la maniabilit. 4.1. Le renommage Le renommage consiste donner un nouveau nom un attribut, lui donner un alias. Par extension, il est permis de parler galement de renommage lorsquune relation est renomme de sorte participer plusieurs fois au mme jeu doprations, ou simplement pour produire une conomie de dactylographie au programmeur. 4.1.1. Lattribut renomm Un attribut peut tre renomm pour amliorer la lisiblit des rsultats ou pour les exploiter ultrieurement.

SELECT TPersonne.Prenom As Prnoms, TCommunication.Libelle As Lignes, TNumero.Numero As Numros FROM TPersonne INNER JOIN (TCommunication INNER JOIN TNumero ON TCommunication.IdCom = TNumero.XIdCom) ON TPersonne.IdPers = TNumero.XIdPers;

Prnoms Lignes NumrosPierre Tl.priv 11.11.11Pierre GSM 12.12.12Marc Tl.priv 21.21.21Marc Fax 23.23.23Michel Tl.priv 31.31.31Michel GSM 32.32.32Michel Fax 33.33.33

SELECT LibArt As Libells, Qte As Quantits, PA As 'Prix achat',

Qte*PA As 'Valeur en stock' FROM TStock;

Libells Quantits Prix achat Valeur en stock Article 1 500 12 6000 Article 2 100 10 1000 Article 3 25 7 175

4.1.2. Lentit renomme Le renommage des relations est ncessaire pour rfrencer plusieurs fois la mme entit au sein dune mme requte, comme par exemple ici o chaque personne possde des rfrences XIdPere et XIdMere qui dsignent ses parents parmi dautres personnes de la mme entit TPersonne.

SELECT TEnfant.Nom, TPere.Nom, TMere.Nom FROM (TPersonne AS TEnfant INNER JOIN TPersonne AS TPere ON TEnfant.XIdPere=TPere.IdPers) INNER JOIN TPersonne AS TMere ON TEnfant.XIdMere=TMere.IdPers;

Ce renommage peut aussi tre ralis dans le seul but de rduire le travail de dactylographie.

SELECT TP.Prenom AS Prnoms, TC.Libelle AS Lignes, TN.Numero AS Numros FROM TPersonne AS TP INNER JOIN (TCommunication AS TC INNER JOIN TNumero AS TN ON TC.IdCom = TN.XIdCom) ON TP.IdPers = TN.XIdPers;

SELECT TE.Nom, TP.Nom, TM.Nom FROM (TPersonne AS TE INNER JOIN TPersonne AS TP ON


TE.XIdPere=TP.IdPers) INNER JOIN TPersonne AS TM ON TE.XIdMere=TM.IdPers;


4.2. Les calculs Un calcul fournit un attribut dans la relation rsultante dont les valeurs sont obtenues par des oprations arithmtiques (+, -, *, /) portant sur des attributs de la mme relation. Le calcul est spcifi lors d'une projection ou lors de l'utilisation d'une fonction. Pour obtenir la valeur en stock de chaque article.

SELECT TStock.LibArt, TStock.Qte, TStock.PA, Qte*PA As Valeur FROM TStock; Pour obtenir la valeur total du stock.

SELECT SUM(Qte*PA) FROM TStock; 4.2.1. Les fonctions statistiques de base en SQL

SUM(attribut) : total des valeurs d'un attribut AVG(attribut) : moyenne des valeurs d'un attribut MIN(attribut) : plus petite valeur d'un attribut MAX(attribut) : plus grande valeur d'un attribut

Remarque : les valeurs NULL sont ignores. 4.2.2. La fonction de comptage La fonction de comptage donne le nombre de tuples d'une relation.

COUNT(*) : nombre de tuples de la relation. COUNT(attribut) : nombre de tuples de la relation pour lesquels l'attribut dsign nest pas NULL. COUNT(DISTINCT attribut) : nombre de valeurs diffrentes de l'attribut dsign.

4.3. Les agrgations Une agrgation fournit un ou plusieurs regroupements de tuples desquels peut ventuellement tre extrait un sous ensemble de tuples rpondant un ou plusieurs critres noncs.

TStockIdArt LibArt Qte PA1 Article 1 500 122 Article 2 100 103 Article 2 25 104 Article 3 25 7

SELECT LibArt, SUM(Qte*PA) AS Valeur FROM TStock GROUP BY LibArt;

Tous les attributs placs dans la clause SELECT doivent tre prsents dans la clause GROUP BY. La proposition inverse n'est pas vraie. La clause GROUP BY est obligatoire ds qu'il y a la fois des attributs et des fonctions de calcul dans la clause SELECT. Il est possible de slectionner des lignes issues d'un regroupement (grce la clause HAVING).

SELECT LibArt, SUM(Qte*PA) AS Valeur

LibArt ValeurArticle 1 6000Article 2 1250Article 3 175

LibArt ValeurArticle 1 6000Article 2 1250


FROM TStock GROUP BY LibArt HAVING SUM(Qte*PA)>500;

4.4. Le tri Le tri s'effectue sur un ou plusieurs attributs, dans l'ordre croissant ou dcroissant. La relation rsultante a la mme structure et le mme contenu que la relation de dpart. La commande du tri est la clause ORDER BY et dfaut de spcifications DESC (Descending) ou ASC (Ascending), le tri est croissant.

TStockIdArt LibArt Qte PA1 Article 1 500 122 Article 2 100 103 Article 2 25 104 Article 3 25 7

SELECT LibArt, Qte FROM TStock ORDER BY Qte ASC;

Il n'est pas toujours possible de placer une fonction dans la clause ORDER BY. Mais les colonnes projetes dans la clause SELECT tant implicitement numrotes de 1 n, il est facile d'y faire rfrence. Ceci explique la prsence du chiffre 2 dans la clause ORDER BY de l'exemple suivant : il fait rfrence SUM(Qte*PA).

SELECT LibArt, SUM(Qte*PA) AS Valeur FROM TStock GROUP BY LibArt HAVING SUM(Qte*PA)>500 ORDER BY 2, LibArt;

5. Approche visuelle de llaboration des requtes La plupart des requtes portant sur une base de donnes relationnelle ne peuvent pas tre ralises partir d'une seule opration mais bien par une squence doprations. En SQL, une mme instruction SELECT permet de combiner Slections, Projections, Jointures. La dfinition de cette squence et sa mise en uvre en SQL constituent la vraie difficult de mise au point dune requte. Lapproche visuelle propose ci-aprs est une aide prcieuse. 5.1. Visualisation des entits Chaque entit est un ensemble dattributs et elle peut donc tre visualise comme tel. Une requte sur un ensemble unique consiste extraire un jeu de tuples compos des attributs souhaits (clause SELECT), rpondant dventuelles conditions (clause WHERE), ventuellement groups (clause GROUP BY) et/ou ordonns (clause ORDER BY), et pour lesquels un ou plusieurs attributs regroups ou calculs peuvent tre dune valeur donne (clause HAVING). Une requte sur un ensemble unique est toujours possible.

LibArt QteArticle 3 25Article 2 25Article 2 100Article 1 500

LibArt ValeurArticle 2 1250Article 1 6000

TLoc

IdLoc Localite CPost

TPers TPersImage

Nom Prenom

IdPers XIdPere XIdMere

Nom Prenom

TLoc TPers

Localite CPost

IdPers Nom Prenom

IdLoc

XIdLoc


Mais les requtes ne portent que rarement sur un seul ensemble. Souvent, les tuples doivent tre constitus dattributs provenant de plusieurs ensembles, ou dun seul mais o les conditions de slection impliquent des attributs appartenant des ensembles diffrents. Une telle requte nest possible que sil existe dans les ensembles concerns, pris deux deux, des attributs appartenant un mme domaine, c'est--dire des attributs de mme nature (entier, rel, date, texte, ) qui puissent tre considrs comme communs selon les objectifs de la requte. Ces attributs constituent alors lintersection des ensembles. Parfois, une requte qui doit fournir des tuples extraits dun ensemble unique, sous des conditions nimpliquant que ce seul ensemble, ne peut tre labore du fait de relations multiples sur cet ensemble. Dans ce cas, la requte doit tre envisage sur deux ensembles : lensemble unique et son image (alias dentit). Si deux ensembles nont pas dattributs communs, il faut quil existe un troisime ensemble ayant des attributs communs avec lun et lautre des ensembles initiaux. Si ce troisime ensemble nexiste pas, il faut quil existe un ensemble densembles ayant des attributs communs entre eux et que cet ensemble ait des attributs communs avec lun et lautre des ensembles initiaux. 5.2. Elaboration de requtes sur base lapproche visuelle 5.2.1. Requtes sur un ensemble unique. Les requtes sur un ensemble unique sont des projections et des slections.

SELECT CPost, Localite FROM TLoc; SELECT Localite FROM TLoc WHERE CPost >= '1300';

Les attributs souhaits pour la projection (clause SELECT) et ceux utiliss pour la slection (clause WHERE) proviennent tous de lensemble unique spcifi dans la clause FROM. La dmarche dlaboration de telles requtes se rsume donc choisir les attributs de la clause SELECT et ceux de la clause WHERE. 5.2.2. Requtes sur une paire densembles. Les requtes sur une paire densembles sont des jointures ralises sous forme de projections et slections, ou par lusage des clauses de jointures. Lecture des personnes qui habitent une localit connue.

SELECT Nom, CPost, Localite FROM TPers, TLoc

TNumero

Nom Prenom IdPers

XIdPers

TLigne

XIdLigne

Ligne

TPers

IdLigne

Numero

E1 E2 Ensemble densembles

TLoc TPers

Localite CPost

IdPers Nom Prenom

IdLoc

XIdLoc

TLoc

IdLoc Localite CPost


WHERE XIdLoc = IdLoc; ou mieux :

SELECT Nom, Localite FROM TPers INNER JOIN TLoc ON TPers.XIdLoc = TLoc.IdLoc;

Les attributs souhaits pour la projection (clause SELECT) sont choisis dans les ensembles parmi ceux qui nappartiennent pas lintersection. Il nest cependant pas interdit de choisir galement des attributs dans lintersection si leur extraction prsente un intrt pour lapplication cliente de la requte. Si la requte est une slection, la jointure est ralise par la clause WHERE qui dfinit imprativement une comparaison entre les attributs contenus dans lintersection. Il est possible daffiner la slection en compltant la clause WHERE par des conditions portant sur dautres attributs des ensembles.

SELECT Nom, CPost, Localite FROM TPers, TLoc WHERE XIdLoc = IdLoc AND Localite LIKE 'Wa*';

Si clause de jointure JOIN est employe, seul le nom de lensemble qui contient les attributs principaux est donn la clause FROM. Le nom de lautre est donn la clause JOIN et la condition de jointure nest plus dfinie dans une clause WHERE, mais bien dans la clause ON indissociable du JOIN. La clause ON doit contenir les noms des entits livrant les attributs de la condition. Il est possible daffiner la slection en ajoutant une clause WHERE vrifiant des conditions portant sur dautres attributs des ensembles. Il faut remarquer que les deux requtes ci dessus produisent exactement le mme rsultat. Il en rsulte que le choix des ensembles pour les clauses FROM et JOIN est sans importance dans cet exemple. Mais ce nest pas toujours le cas et il est pratique dadopter un raisonnement qui soit appropri en toutes circonstances. Le raisonnement qui prvaut ici consiste dterminer si on souhaite les informations sur les personnes dont accessoirement leur localit, ou bien si on souhaite les informations des localits dont accessoirement leurs habitants. Dans le cas de la lecture des personnes avec leur localit, ce raisonnement conduit immanquablement la requte prsente gauche. Il y a une relation entre les clauses SQL et les diffrentes parties des ensembles concerns par une requte.

TLoc TPers

Localite CPost

IdPers Nom Prenom

IdLoc

XIdLoc

FROM

WHERE ON

INNER JOIN

SELECT SELECT

SELECT

WHERE WHERE

SELECT Nom, Localite FROM TLoc INNER JOIN TPers ON TPers.XIdLoc = TLoc.IdLocWHERE Localite LIKE 'Wa*';

SELECT Nom, Localite FROM TPers INNER JOIN TLoc ON TPers.XIdLoc = TLoc.IdLoc WHERE Localite LIKE 'Wa*';


La jointure INNER JOIN livre uniquement les tuples qui satisfont la condition de jointure exprime dans la clause WHERE ou dans la clause ON. Les requtes prcdentes ne peuvent fournir des localits non habites par les personnes de lensemble TPers, ni les personnes qui nhabitent pas une localit connue dans TLoc. Il peut tre ncessaire dobtenir tous les tuples qui satisfont la condition de jointure et aussi, distinctement, ceux dun des ensembles ou des deux, pour lesquels la condition nest pas ralise. Lecture de toutes les personnes et de leur localit si elle est connue (lecture hirarchique des parents).

SELECT Nom, Localite FROM TPers LEFT OUTER JOIN TLoc ON TLoc.IdLoc = TPers.XIdLoc ORDER BY Localite;

TLoc TPers

Localite CPost

IdPers Nom Prenom

IdLoc

XIdLoc


Lecture de toutes les localits et de leurs habitants connus (lecture hirarchique des enfants).

SELECT Localite, Nom FROM TLoc LEFT OUTER JOIN TPers ON TLoc.IdLoc = TPers.XIdLoc ORDER BY Localite;

Lecture de toutes les personnes et de toutes les localits avec mises en vidence des correspondances.

SELECT Nom, Localite FROM TLoc FULL OUTER JOIN TPers ON TLoc.IdLoc = TPers.XIdLoc ORDER BY Localite;

ou, avec loprateur UNION :

Lusage de la FULL jointure nest pas support par tous les SGBD. La conversion dune FULL jointure dans une forme mieux accepte se ralise par le constat que les tuples souhaits sont ceux dune LEFT jointure sur lensemble TPers associs ceux dune LEFT jointure sur lensemble TLoc. Il suffit donc dcrire ces deux requtes associes par un oprateur UNION. Il convient que les attributs soient spcifis dans le mme ordre dans chacune des clauses SELECT, faute de quoi il y aura de la redondance dans les tuples rsultants.

La conception des OUTER jointures ne diffre pas de celle des INNER jointures. Les attributs souhaits sont spcifis dans les clauses SELECT et choisis dans lun des ensembles, ou dans les deux, et mme si ncessaire dans lintersection. La clause FROM dsigne lensemble dont on souhaite extraire toutes les valeurs et la clause LEFT OUTER JOIN dsigne lautre ensemble. Lemploi de la clause RIGHT OUTER JOIN produit le mme rsultat que la clause LEFT OUTER JOIN si on inverse la dsignation des ensembles entre les clauses FROM et JOIN. Les clauses LEFT et RIGHT dsignent celui des ensembles qui doit livrer toutes ses valeurs : celui de gauche ou celui de droite par rapport la clause elle-mme lintrieur de la requte (la clause FROM tant toujours gauche de la clause JOIN). Dans la mesure o on dsigne lensemble principal dans le FROM, seule la clause LEFT reste ncessaire. La clause ON contient la condition de jointure qui est une comparaison dattributs appartenant lintersection. Il est possible daffiner la slection en ajoutant une clause WHERE vrifiant des conditions portant sur dautres attributs des ensembles.

SELECT Nom, Localite FROM TPers LEFT OUTER JOIN TLoc ON TLoc.IdLoc = TPers.XIdLoc WHERE Nom > 'B' ORDER BY Localite UNION SELECT Nom, Localite FROM TLoc LEFT OUTER JOIN TPers ON TLoc.IdLoc = TPers.XIdLoc WHERE Localite LIKE 'Wa*' ORDER BY Localite;

TLoc TPers

Localite CPost

IdPers Nom Prenom

IdLoc

XIdLoc

TLoc TPers

Localite CPost

IdPers Nom Prenom

IdLoc

XIdLoc

SELECT Nom, Localite FROM TPers LEFT OUTER JOIN TLoc ON TLoc.IdLoc = TPers.XIdLoc ORDER BY Localite UNION SELECT Nom, Localite FROM TLoc LEFT OUTER JOIN TPers ON TLoc.IdLoc = TPers.XIdLoc ORDER BY Localite;


5.2.3. Requtes sur trois ensembles ayant des intersections deux deux. Les requtes sur trois ensembles doivent se concevoir comme tant des jointures sur ces ensembles considrs deux par deux. Il faut raliser un ensemble regroupant lensemble devant fournir linformation principale et son voisin. Cet ensemble est le rsultat dune jointure sur ces deux sous-ensembles. Cette jointure se conoit de la manire dcrite prcdemment. La clause SELECT dsigne des attributs souhaits pour les tuples rsultants, la clause FROM dsigne lensemble principal, la clause INNER JOIN dsigne lensemble associ et la clause ON ralise la condition de jointure sur des attributs de lintersection.

SELECT Nom, Localite FROM (TPers INNER JOIN TLoc ON TPers.XIdLoc = TLoc.IdLoc);

Lensemble rsultant de cette jointure a une intersection avec le troisime ensemble. Lobtention de la requte finale se ralise en crivant la clause SELECT complte avec les attributs souhaits du troisime ensemble, la clause FROM est celle de la jointure prcdente incluant les clauses JOIN et ON et crite entre parenthses, la clause INNER JOIN dsigne le troisime ensemble et la clause ON ralise la condition de jointure sur des attributs de lintersection existant entre ce troisime ensemble et lensemble rsultant prcdent. Il est possible daffiner la slection en ajoutant une clause WHERE vrifiant des conditions portant sur dautres attributs des ensembles.

SELECT Nom, Localite, Pays FROM (TPers

INNER JOIN TLoc ON TPers.XIdLoc = TLoc.IdLoc)

INNER JOIN TPays ON TLoc.XIdPays = TPays.IdPays;

SELECT Nom, Localite, Pays FROM (TPays

INNER JOIN TLoc ON TPays.IdPays = TLoc.XIdPays)

INNER JOIN TPers ON TLoc.IdLoc = TPers.XIdLoc;

Les deux requtes livrent exactement le mme jeu de tuples. Simplement, la deuxime nest pas ralise partir de lensemble devant fournir linformation principale, mais partir lensemble qui fournit linformation du plus fin dtail. Dans bien des cas, la distinction entre linformation principale et la secondaire nest quune question de point de vue.

Les relations qui lient les ensembles TPers, TLoc et TPays constituent ordinairement une cascade de relations de type 1 N. Entre les ensembles TPers, TNumero et TLigne, les relations ne constituent plus une cascade, mais deux relations 1 N, lensemble TNumero ralisant lclatement de la relation de type N M existant entre les ensembles TPers et TLigne. La conception des requtes procde nanmoins de la mme dmarche que ci-dessus.

TLocTPers

Localite CPost

IdPers Nom Prenom

IdLoc

XIdLoc

TPays

Pays

IdPays

XIdPays


Selon la mthode employe prcdemment, et en considrant que linformation des personnes est linformation principale, les numros et lignes tlphoniques tant secondaires. La clause SELECT dsigne des attributs souhaits pour les tuples rsultants, la clause FROM dsigne lensemble principal, la clause INNER JOIN dsigne lensemble associ et la clause ON ralise la condition de jointure sur des attributs de lintersection.

SELECT Nom, Numero FROM (TPers INNER JOIN TNumero ON TPers.IdPers = TNumero.XIdPers);

Lensemble rsultant de cette jointure a une intersection avec le troisime ensemble. Lobtention de la requte finale se ralise en crivant la clause SELECT complte avec les attributs souhaits du troisime ensemble, la clause FROM est celle de la jointure prcdente incluant les clauses JOIN et ON et crite entre parenthses, la clause INNER JOIN dsigne le troisime ensemble et la clause ON ralise la condition de jointure sur des attributs de lintersection existant entre ce troisime ensemble et lensemble rsultant prcdent. Il est possible daffiner la slection en ajoutant une clause WHERE vrifiant des conditions portant sur dautres attributs des ensembles.

SELECT Nom, Numero, Ligne FROM (TPers

INNER JOIN TNumero ON TPers.IdPers = TNumero.XIdPers)

INNER JOIN TLigne ON TLigne.IdLigne = TNumero.XIdLigne;

5.2.4. Requtes sur un ensemble densembles ayant des intersections deux deux. La requte sur un ensemble densembles slabore par jointures successives de deux ensembles, chacune largissant lensemble par jointure avec son voisin, jusqu obtenir la situation illustre ci-dessus dun grand ensemble ayant une intersection avec le dernier restant. Une dernire jointure produit alors la requte souhaite.

TNumero TLigne

Numero Ligne IdLigne

XIdLigne

TPers

Nom Prenom

IdPers

XIdPers

TLoc TPers

Localite CPost

Nom Prenom

IdLoc

XIdLoc

TPays

Pays

IdPays

XIdPays

TNumero TLigne

Numero Ligne

IdLigne

XIdLigne

IdPers

XIdPers


Par exemple, la requte devant produire les pays et les types de lignes tlphoniques dont ils disposent ncessite les attributs Pays et Ligne situs dans les ensembles extrmes de la chane. En considrant linformation principale comme tant des pays, il faut crer un ensemble par une jointure sur TPays et TLoc.

SELECT Pays FROM TPays INNER JOIN TLoc ON TPays.IdPays = TLoc.XIdPays;

Il faut ensuite crer un ensemble par une jointure entre lensemble rsultant de la requte prcdente et lensemble voisin TPers.

SELECT Pays FROM (TPays


INNER JOIN TPers ON TLoc.IdLoc = TPers.XIdLoc;

Il faut ensuite crer un ensemble par une jointure entre lensemble rsultant de la requte prcdente et lensemble voisin TNumero.

SELECT Pays FROM ((TPays


INNER JOIN TPers ON TLoc.IdLoc = TPers.XIdLoc)

INNER JOIN TNumero ON TPers.IdPers = TNumero.XIdPers;

Pour terminer, il faut encore crer une jointure entre lensemble rsultant de la requte prcdente et le dernier ensemble de la chane, lensemble TLigne.

SELECT Pays, Ligne FROM (((TPays

INNER JOIN TLoc

TLoc

LocaliteCPost

IdLoc

TPays

Pays IdPaysXIdPays

TPers

Nom Prenom

IdLoc XIdLoc

TPays / TLoc

Pays IdPers

Pays

TNumero

Numero

XIdLigneIdPersXIdPers

TPays / TLoc / TPers

Pays

TLigne

Ligne IdLigneXIdLigne

TPays / TLoc / TPers / TNumero


ON TPays.IdPays = TLoc.XIdPays) INNER JOIN TPers ON TLoc.IdLoc = TPers.XIdLoc)


INNER JOIN TLigne ON TLigne.IdLigne = TNumero.XIdLigne;


Il est possible daffiner la slection en ajoutant une clause WHERE vrifiant des conditions portant sur dautres attributs des ensembles.

SELECT Pays, Ligne FROM (((TPays


INNER JOIN TPers ON TLoc.IdLoc = TPers.XIdLoc)


INNER JOIN TLigne ON TLigne.IdLigne = TNumero.XidLigne

WHERE Localite LIKE 'Wa*'; 5.2.5. Requtes sur trois ensembles, ou plus, prsentant une intersection commune. Une requte sur trois ensembles ayant entre eux une intersection commune est une cascade de jointures dont les conditions sont dfinies sur les attributs des intersections des ensembles pris deux deux. Soit par exemple, les entits TEtudiants, TProfesseurs et TLocalites dans une base de donnes de gestion dune cole de promotion sociale. Toutes les personnes, tudiants et professeurs, habitent une localit. Certains professeurs sinscrivent des cours de spcialisation et deviennent ainsi tudiants. Ds lors, des enregistrements les concernant existent dans les deux entits TEtudiants et TProfesseurs. Lapproche visuelle de conception dune requte saugrenue devant livrer les noms des localits habites par les professeurs qui sont aussi tudiants, conduit dessiner un ensemble densembles possdant une intersection commune contenant les XIdLoc des personnes des entits TEtudiants et TProfesseurs. En considrant dabord les entits TEtudiants et TProfesseurs, il est possible de crer un nouvel ensemble contenant les XIdLoc des localits recherches. Il est ncessaire ici de prciser de quelle entit doivent tre extraits ces XIdLoc faute de quoi, la requte est confronte une indtermination parce que cet attribut existe dans chacune des entits.

SELECT TEtudiants.XIdLoc FROM TEtudiants, TProfesseurs WHERE NomEtud = NomProf;

Il suffit maintenant dexplorer lintersection de ce nouvel ensemble avec le troisime ensemble dorigine TLocalites pour extraire les localits recherches.

SELECT Localite FROM TLocalites, (SELECT TEtudiants.XIdLoc

FROM TEtudiants, TProfesseurs WHERE NomEtud = NomProf)

WHERE IdLoc = XIdLoc;

TLocalites

TProfesseurs

Localite CPost

IdProfesseur PrenomProf

TEtudiants

IdEtudiant PrenomEtud

XIdLoc IdLoc

NomProf NomEtud

TProfesseurs

IdProfesseurPrenomProf

TEtudiants


XIdLoc

NomProf NomEtud

XIdLoc

TLocalites

Localite CPost

XIdLoc IdLoc

TEtudiants / TProfesseurs


La rsolution de ce problme peut aussi bien se faire par lusage des instructions de jointures en suivant la procdure dcrite prcdemment.

SELECT Localite FROM (TLocalites

INNER JOIN TEtudiants ON TLocalites.IdLoc = TEtudiants.XIdloc)

INNER JOIN TProfesseurs ON TEtudiants.NomEtud = TProfesseurs.NomProf;

Lapproche visuelle de conception dune requte, plus saugrenue encore que la prcdente, devant livrer des noms et codes postaux de localits portant le mme nom que des professeurs qui sont aussi tudiants, conduit dessiner un ensemble densembles possdant une intersection commune contenant les noms des personnes des entits TEtudiants et TProfesseurs, ainsi que les noms des localits. Les identifiants des localits et leurs homologues dans les entits TEtudiants et TProfesseurs existent galement dans lintersection commune, mais ils ne sont pas concerns pas la requte envisage. Une premire jointure dont la condition vrifie lgalit des noms livre les noms des professeurs qui sont galement tudiants. Le choix de NomProf ou de NomEtud est sans importance puisquils sont identiques, mais il faut en indiquer un dans la requte.

SELECT NomEtud FROM TProfesseurs INNER JOIN TEtudiants ON TProfesseurs.NomProf = TEtudiants.NomEtud;

Une deuxime jointure dont la condition vrifie lgalit des noms livrs par la premire avec les noms des localits, livre les CPost et Localite attendus. Lattribut CPost est ajout la clause SELECT et lattribut NomEtud est renomm Localite pour respecter la logique de lnonc initial.

SELECT CPost, NomEtud As Localite FROM (TProfesseurs

INNER JOIN TEtudiants ON TProfesseurs.NomProf = TEtudiants.NomEtud)

INNER JOIN TLocalites ON TEtudiants.NomEtud = TLocalites.Localite;

TLocalite

TProfesseurs

CPost

IdProfesseur PrenomProf

TEtudiants


NomProf NomEtud Localite


5.2.6. A la recherche de linexistant. Toutes les requtes illustres prcdemment ont pour objet lextraction dinformations prsentes dans les ensembles tudis et dans leurs intersections. Dautres requtes peuvent au contraire avoir pour objet lextraction dinformations inexistantes ou tout au moins, pour lesquelles les attributs des intersections ne reoivent aucune valeur. Ces requtes ne peuvent donc pas tre de simples jointures qui vrifient leurs conditions sur des valeurs des attributs dintersection. La conception dune telle requte se fait en trois tapes. La premire consiste crer un ensemble dinformations qui contient toutes les informations qui pourraient tre prsentes dans lintersection. La seconde consiste crer un ensemble des informations effectivement existantes dans lintersection. La troisime, qui fournit la requte finie, est la soustraction du second ensemble du premier. Cette soustraction livre les tuples reprsentant les valeurs qui auraient pu se trouver dans lintersection, mais qui ny taient pas. Soit, une requte dont lobjet est dtablir la liste des personnes avec pour chacune, la liste des types de lignes dont elle ne dispose pas. Une relation existe bien entre TPers et TLigne dans TNumero, mais l o une ligne nest pas attribue une personne, aucun tuple de TNumero nassure la liaison entre cette personne et cette ligne. La requte demande concerne donc les entits TPers et TLigne pour lesquelles aucune correspondance nexiste dans TNumero, donc pour lesquelles lintersection est vide. Or il est impossible dtablir une requte sur deux entits si elles nont pas dintersection. Premire tape : Crer un ensemble qui contient toutes les combinaisons de valeurs possibles pour les tuples de TNumero. Cet ensemble est le rsultat du produit cartsien de TPers et TLigne.

SELECT Nom, Ligne FROM TPers, TLigne;

Deuxime tape : Crer lensemble des valeurs existant dans TNumero. Il sagit dune simple requte sur un ensemble unique.

SELECT XIdPers, XIdLigne FROM TNumero;

Troisime tape : Soustraire le rsultat de la deuxime requte de celui de la premire. Une soustraction se ralise par lordre EXCEPT ou par une clause WHERE NOT EXISTS. Cette dernire convient mieux dans la mesure o les attributs requis en sortie ne sont pas ceux qui conditionnent lextraction.

SELECT Nom, Ligne FROM TPers, TLigne WHERE NOT EXISTS

(SELECT XIdPers, XIdLigne FROM TNumero WHERE IdPers = XIdPers AND IdLigne = XIdLigne);

TNumero

XIdPers XIdLigne Numero

TLigne

IdLigne Ligne

TPers

IdPers Nom Prenom


5.3. Exemples de rsolutions de problmes. Llaboration dune requte commence par la rduction de lnonc du problme son contenu essentiel, ventuellement en rpondant aux trois questions initiales de toute analyse (Que faire ? / Avec quoi ? / Comment ?), de raliser ensuite la reprsentation des modles relationnels des entits ncessaires sous forme densembles et enfin, de traduire les graphes obtenus en SQL. Dans ces pages, les problmes sont noncs de manire concise. Llaboration des requtes commence directement par la reprsentation des modles relationnels sous forme densembles. Par exemple les relations : TClient(IdClient, NomClient, AdrClient) TCommande(IdCde, Date, IdClient*) Attention, dans cet exemple, les entits TClient et TCommande ont un attribut homonyme : IdClient. Il en rsulte lobligation de prciser les noms des entits dans la requte pour viter lindtermination. 1. Obtenir lidentifiant et le nom de chaque client qui a pass une commande le 10/06/97. Les noms des clients et les dates des commandes se trouvent dans deux ensembles distincts pour lesquels il existe une intersection servant la requte envisage. Cette intersection contient lidentifiant de lentit TClient et son homologue de lentit TCommande. Il faut donc raliser une jointure dont la condition porte sur ces attributs de lintersection. Les tuples rsultants sont forms dattributs de lentit TClient.

SELECT IdClient, NomClient FROM TClient, TCommande WHERE TClient.IdClient = TCommande.IdClient AND Date = '10/06/97';

ou avec une instruction de jointure :

SELECT IdClient, NomClient FROM TClient INNER JOIN TCommande ON TClient.IdClient = TCommande.IdClient WHERE Date = '10/06/97';

2. Soit le modle relationnel suivant relatif une base de donnes sur des reprsentations musicales. Ce modle peut tre dessin comme ci-dessous, sous la forme de trois ensembles possdant des intersection deux deux ou bien, comme illustr droite, sous la forme de trois ensembles ayant une intersection commune. Dans la reprsentation de gauche, la rptition de lattribut IdConcert pourtant unique dans le modle relationnel est faite pour permettre la lecture correcte des intersections. La reprsentation de droite dispense de cet artifice. De plus, comme indiqu prcdemment, llaboration de requtes sur trois ensembles sur se fait de la mme manire quils aient des intersections deux deux ou une seule intersection commune.

TMusicien IdMus NomMus XIdConcert*

TConcertIdConcertTitreLieu

TProgrammation XIdConcert* Date Prix

TCommande

IdCde Date

TClient

NomClient AdrClient

IdClient IdClient*

TConcert

Titre Lieu

TProgrammation

Date Prix

TMusicien

XIdConcert IdConcert

IdMus NomMus

TConcert

Titre Lieu

TProgrammation

Date Prix

IdConcert XIdConcert

TMusicien

IdConcert XIdConcert

IdMus NomMus


TEtudiant IdEtud NomEtud PrenomEtud

TEvaluationXIdEtud*XIdMat*DateNote

TMatiereIdMat LibMat CoefMat

2.1. Obtenir la liste des titres des concerts. Les titres des concerts sont dans lentit TConcert. Il sagit dune requte sur un ensemble unique.

SELECT Titre FROM TConcert; 2.2. Obtenir la liste des titres des concerts ayant lieu l'opra Bastille. Il sagit ici aussi dune requte sur un ensemble unique. En effet, les attributs recherchs appartiennent tous lentit TConcert. Simplement, lnonc impose une slection sur la valeur dun des attributs. La requte doit donc tre assortie dune clause WHERE qui vrifie cette valeur.

SELECT Titre FROM TConcert WHERE Lieu = 'Bastille';

2.3. Obtenir la liste des noms des musiciens et des titres des concerts auxquels ils participent. Les attributs demands pour cette requte appartiennent aux deux ensembles TMusicien et TConcert qui prsentent une intersection. La requte est donc une jointure. La clause SELECT dsigne les attributs souhaits et la clause ON vrifie la condition de jointure sur les attributs de lintersection.

SELECT NomMus, Titre FROM TMusicien INNER JOIN TConcert ON TMusicien.XIdConcert = TConcert.IdConcert;

ou SELECT NomMus, Titre FROM TMusicien, TConcert WHERE XIdConcert = IdConcert;

2.4. Obtenir la liste des titres des concerts, les lieux et les tarifs pour la journe du 14/09/96. Pour cette requte, les attributs demands appartiennent aux ensembles TProgrammation et TConcert qui prsentent une intersection. La requte est donc une jointure. La clause SELECT dsigne les attributs souhaits et la clause ON vrifie la condition de jointure sur les attributs de lintersection. La diffrence technique entre cette requte et la prcdente, est que celle-ci tre affine par une clause WHERE vrifiant la date demande.

SELECT Titre, Lieu, Prix FROM TConcert INNER JOIN TProgrammation ON TConcert.IdConcert = TProgrammation.XIdConcert WHERE Date = '14/06/96';

ou SELECT Titre, Lieu, Prix FROM TConcert, TProgrammation WHERE IdConcert = XIdConcert AND Date = '14/06/96';

3. Soit le modle relationnel suivant relatif la gestion des notes annuelles d'une promotion d'tudiants. La reprsentation de ce modle sous forme densembles ayant des intersections deux deux ne prsente aucune difficult.

TEvaluation

Date Note

TMatiere

LibMat CoefMat

IdMat XIdMat

TEtudiant

IdEtud XIdEtud

NomEtud PrenomEtud


3.1. Obtenir le nombre total d'tudiants. Cette requte vise obtenir le nombre de tuples dune entit. Il sagit donc dune requte sur un ensemble unique qui ne recherche pas des attributs particuliers, mais bien le nombre doccurrences dun attribut donn, ou de tous dans ce cas. Lidentifiant IdEtud a bien sr une valeur dans chaque tuple. La requte peut calculer le nombre doccurrences de cet attribut. Toutefois, afin dviter toute erreur dapprciation quant la prsence ou labsence de valeur dun attribut, il vaut mieux utiliser le joker * lorsquil sagit de compter tous les tuples. Le comptage dun attribut pouvant exister sans valeur donne le compte des enregistrements pour lesquels cet attribut possde une valeur. Le renommage des valeurs calcules est une bonne pratique qui permet la prsentation des rsultats sous des tiquettes significatives.

SELECT COUNT(IdEtud) AS Nombre FROM TEtudiant;

ou mieux :

SELECT COUNT(*) AS Nombre FROM TEtudiant; La requte suivante donne le nombre de tuples de TEtudiant pour lesquels PrenomEtud nest pas nul.

SELECT COUNT(PrenomEtud) AS Nombre FROM TEtudiant; 3.2. Obtenir la note la plus haute et la note la plus basse parmi l'ensemble des notes. Il sagit ici aussi dune requte sur un ensemble unique qui ne recherche pas des attributs particuliers, mais bien des valeurs calcules sur certains attributs de lensemble.

SELECT MIN(Note) AS Minimun, MAX(Note) AS Maximum FROM TEvaluation; 3.3. Obtenir les moyennes de chaque tudiant dans chacune des matires. Cette requte implique la production du nom de chaque tudiant, avec pour chacun la liste des matires pour lesquelles il a t valu et la moyenne de ses valuations. Lexamen du graphe montre bien que les informations attendues proviennent des trois ensembles. Une premire jointure sur les entits TEtudiant et TEvaluation peut livrer les noms des tudiants et leur note pour chaque matire.

SELECT TEtudiant.IdEtud, NomEtud, Note FROM TEtudiant INNER JOIN TEvaluation ON TEtudiant.IdEtud = TEvaluation.XIdEtud;

Une deuxime jointure liant la prcdente TMatiere peut livrer les libells des matires ainsi que leur coefficient de sorte rendre significatives les moyennes calcules. La clause SELECT est complte avec CoefMat et linstruction de calcul de la moyenne. La requte est aussi complte dune clause GROUP BY obligatoire ds qu'il y a la fois des attributs et des fonctions de calcul dans la clause SELECT.

SELECT IdEtud, NomEtud, LibMat, CoefMat, AVG(Note) AS MoyEtuMat FROM (TEtudiant

INNER JOIN TEvaluation ON TEtudiant.IdEtud = TEvaluation.XIdEtud)

INNER JOIN TMatiere ON TEvaluation.XIdMat = TMatiere.IdMat

GROUP BY IdEtud, NomEtud, LibMat, CoefMat;

TEvaluation

Date Note

TMatiere

LibMat CoefMat

IdMat XIdMat

TEtudiant

IdEtud XIdEtud

NomEtud PrenomEtud


3.4. Obtenir les moyennes des notes par matire. Cette requte, qui concerne les ensembles TMatiere et TEvaluation, est une simple jointure dont la clause SELECT contient une fonction dagrgation pour le calcul de la moyenne.

SELECT LibMat, AVG(Note) AS MoyenneMatiere FROM TMatiere, TEvaluation WHERE IdMat = XIdMat GROUP BY LibMat;

3.5. Obtenir la moyenne gnrale de chaque tudiant. Les moyennes de chaque tudiant dans chaque matire doivent tre calcules pralablement au calcul de la moyenne gnrale de chaque tudiant. La production de ces moyennes nest plus dveloppe ici puisquelle tait lobjet de la requte 3.3 prcdente qui livre les informations IdEtud, NomEtud, LibMat, CoefMat et MoyEtuMat. Soit provisoirement VueMoyEtuMat, cet ensemble. Le calcul de la moyenne gnrale se rduit ainsi llaboration dune requte sur un ensemble unique. Chaque moyenne par matire est exprime par rapport son coefficient (110/120, 35/40, 85/100, ) et la moyenne gnrale sexprime ordinairement en pourcents. Pour tenir compte de la pondration de chaque cooefficient, la moyenne gnrale doit se calculer comme tant la somme des moyennes divise par la somme des coefficients. Le rsultat doit tre multipli par 100 pour fournir le pourcentage souhait.

SELECT IdEtud, NomEtud, SUM(MoyEtuMat)/SUM(CoefMat) * 100 AS MoyGenEtu FROM VueMoyEtuMat GROUP BY IdEtud, NomEtud;

La requte est complte en remplaant VueMoyEtuMat par la requte qui a produit cette slection.

SELECT IdEtud, NomEtud, SUM(MoyEtuMat)/SUM(CoefMat) * 100 AS MoyGenEtu FROM (SELECT IdEtud, NomEtud, LibMat, CoefMat, AVG(Note) AS MoyEtuMat

FROM (TEtudiant INNER JOIN TEvaluation ON TEtudiant.IdEtud = TEvaluation.XIdEtud)


GROUP BY IdEtud, NomEtud, LibMat, CoefMat) GROUP BY IdEtud, NomEtud;

3.6. Obtenir la moyenne gnrale de la promotion. La requte prcdente livre les moyennes gnrales de chaque tudiant. La moyenne gnrale de la promotion est la moyenne de ces moyennes. La requte recherche doit donc calculer les moyennes des MoyGenEtu de la requte 3.5.

SELECT AVG(MoyGenEtu) AS MoyProm FROM (SELECT IdEtud, NomEtud, SUM(MoyEtuMat)/SUM(CoefMat) * 100 AS MoyGenEtu

FROM (SELECT IdEtud, NomEtud, LibMat, CoefMat, AVG(Note) AS MoyEtuMat FROM (TEtudiant



GROUP BY IdEtud, NomEtud, LibMat, CoefMat) GROUP BY IdEtud, NomEtud);

VueMoyEtuMat

IdEtud NomEtud LibMat CoefMat MoyEtuMat


3.7. Obtenir la liste des tudiants qui ont une moyenne gnrale suprieure ou gale la moyenne gnrale de la promotion. La requte envisage porte sur deux ensembles gnrs par les requtes des exemples prcdents. La requte 3.5 livre les informations IdEtud, NomEtud et MoyGenEtu. Soit provisoirement VueMoyGenEtu cet ensemble. La requte 3.6 livre la moyenne gnrale de la promotion MoyProm. Soit provisoirement VueMoyProm cet ensemble singleton. Pour raliser une requte sur deux ensembles, il faut que ces ensembles disposent dune intersection qui contient des attributs dun mme domaine. Les attributs MoyGenEtu et MoyProm peuvent constituer cette intersection car ils sont tous deux des nombres rels exprimant un pourcentage et quils reprsentent les valeurs nonces comme critres de slection dans lnonc. La requte slabore donc comme une jointure sur les ensembles VueMoyGenEtu et VueMoyProm avec pour condition de slection que MoyGenEtu soit gale ou suprieure MoyProm.

SELECT IdEtud, NomEtud, MoyGenEtu FROM VueMoyGenEtu INNER JOIN VueMoyProm ON VueMoyGenEtu.MoyGenEtu >= VueMoyProm.MoyProm;

La requte est complte en remplaant VueMoyGenEtu et VueMoyProm par les requtes qui ont produit ces slections. Lusage dalias dentit est ncessaire pour lcriture correcte de la clause ON.

SELECT IdEtud, NomEtud, MoyGenEtu FROM (SELECT IdEtud, NomEtud, SUM(MoyEtuMat)/SUM(CoefMat) * 100 AS MoyGenEtu




GROUP BY IdEtud, NomEtud, LibMat, CoefMat) GROUP BY IdEtud, NomEtud) AS VueMoyGenEtu

INNER JOIN (SELECT AVG(MoyGenEtu) AS MoyProm FROM (SELECT IdEtud, NomEtud, SUM(MoyEtuMat)/SUM(CoefMat) * 100 AS

MoyGenEtu FROM (SELECT IdEtud, NomEtud, LibMat, CoefMat, AVG(Note) AS

MoyEtuMat FROM (TEtudiant



GROUP BY IdEtud, NomEtud, LibMat, CoefMat) GROUP BY IdEtud, NomEtud)) AS VueMoyProm

VueMoyProm

MoyProm

VueMoyGenEtu

IdEtud NomEtud MoyGenEtu

VueMoyProm

MoyProm MoyGenEtu

VueMoyGenEtu

IdEtud NomEtud


ON VueMoyGenEtu.MoyGenEtu >= VueMoyProm.MoyProm;


La requte prcdente pouvait scrire diffremment ds quun des ensembles na pas dattribut slectionn pour les tuples rsultants. En effet, lattribut MoyProm nest pas demand en sortie et ne sert que la condition de slection.

SELECT IdEtud, NomEtud, MoyGenEtu FROM VueMoyGenEtu WHERE MoyGenEtu >= VueMoyProm.MoyProm;

La requte est complte en remplaant VueMoyGenEtu et VueMoyProm par les requtes qui ont produit ces slections.

SELECT IdEtud, NomEtud, MoyGenEtu FROM (SELECT IdEtud, NomEtud, SUM(MoyEtuMat)/SUM(CoefMat) * 100 AS MoyGenEtu




GROUP BY IdEtud, NomEtud, LibMat, CoefMat) GROUP BY IdEtud, NomEtud)

WHERE MoyGenEtu >= (SELECT AVG(MoyGenEtu) AS MoyProm FROM (SELECT IdEtud, NomEtud, SUM(MoyEtuMat)/SUM(CoefMat) * 100 AS

MoyGenEtu FROM (SELECT IdEtud, NomEtud, LibMat, CoefMat, AVG(Note) AS

MoyEtuMat FROM (TEtudiant



GROUP BY IdEtud, NomEtud, LibMat, CoefMat) GROUP BY IdEtud, NomEtud));


6. Oprations de cration et suppression des tables Ces oprations sont illustres ci aprs par lnumration sans commentaire des commandes qui ont permis la cration de la base des donnes des personnes utilise prcdemment et par dautres dont le texte est parfaitement explicite. 6.1. Cration des tables CREATE TABLE TPersonne( IdPers Int, Nom Text(40), Prenom Text(30), Adresse Text(50),

XIdLoc Int ); CREATE TABLE TLocalite( IdLoc Int, LibLoc Text(35), CPost Text(6), XIdPays Int ); CREATE TABLE TPays( IdPays Int, LibPays Text(30) ); CREATE TABLE TCommunication( IdCom Int, LibCom Text(30) ); CREATE TABLE TNumero( XIdPers Int, XIdCom Int, Numero Text(20) ); CREATE TABLE TInutile( IdNul Int, LibNul Text(10) ); 6.1.1. Dfinition des cls primaires ALTER TABLE TPersonne ADD PRIMARY KEY (IdPers); ALTER TABLE TLocalite ADD PRIMARY KEY (IdLoc); ALTER TABLE TPays ADD PRIMARY KEY (IdPays); ALTER TABLE TCommunication ADD PRIMARY KEY (IdCom); ALTER TABLE TNumero ADD PRIMARY KEY (XIdPers, XIdCom); 6.1.2. Dfinition des cls trangres et des relations ALTER TABLE TPersonne ADD FOREIGN KEY (XIdLoc) REFERENCES TLocalite (IdLoc); ALTER TABLE TLocalite ADD FOREIGN KEY (XIdPays) REFERENCES TLocalite (IdPays); ALTER TABLE TNumero ADD FOREIGN KEY (XIdPers) REFERENCES TPersonne (IdPers); ALTER TABLE TNumero ADD FOREIGN KEY (XIdCom) REFERENCES TCommunication (IdCom); 6.2. Cration dune table par copie dune autre 6.2.1. Cration et copie des donnes SELECT * INTO TableCree FROM TableExistante; 6.2.2. Cration et copie de la structure seule SELECT * INTO TableCree FROM TableExistante WHERE (UnAttributNePouvantEtreNull = NULL); 6.3. Suppression dune table


DROP TABLE TInutile;


7. Oprations de cration, modification et suppression dindex et de colonnes Outre lajout des cls primaires et ltablissement des relations, la commande ALTER TABLE sert aussi aux ajouts, modifications et suppressions de colonnes, dindex et de contraintes. Ajout dune colonne Profession lentit TPers : ALTER TABLE TPers ADD COLUMN Profession Text(40); Modification de la colonne Profession de lentit TPers : ALTER TABLE TPers ALTER COLUMN Profession Text(100); Crer une contrainte sur la colonne Profession de lentit TPers : ALTER TABLE TPers ADD CONSTRAINT IdxProfession UNIQUE (Profession); Enlever la contrainte sur la colonne Profession de lentit TPers : ALTER TABLE TPers DROP CONSTRAINT Profession; Suppression de la colonne Profession de lentit TPers : ALTER TABLE TPers DROP COLUMN Profession; Crer une contrainte sur la paire de colonnes CPost et Localite de lentit TLocalites : ALTER TABLE TLocalites ADD CONSTRAINT IdxLoc UNIQUE (CPost, Localite); Lindex et la contrainte sont trs semblables. Toutefois, la contrainte peut galement utilise pour tablir une relation avec une autre table (cf. Dfinition des cls trangres et des relations). Les commandes CREATE et DROP sappliquent galement aux index. CREATE INDEX IdxProfession ON TPers (Profession); DROP INDEX IdxProfession ON Tpers; La commande CREATE INDEX permet de prciser si lindex admet les doublons ou pas, sil constitue une cl primaire, ou sinon, sil admet ou non les valeurs nulles, de mme que lordre de tri des index. Crer un index sans doublon sur la colonne Profession de lentit TPers : CREATE UNIQUE INDEX IdxProfession ON TPers (Profession); Crer un index sur les colonnes Nom et Profession de lentit TPers croissant sur Nom et dcroissant sur Profession : CREATE UNIQUE INDEX IdxProfession ON TPers (Nom ASC, Profession DESC); Crer un index sans doublon sur la colonne Profession de lentit TPers pour lequel la valeur NULL est interdite : CREATE UNIQUE INDEX IdxProfession ON TPers (Profession) WITH DISALLOW NULL Syntaxe gnrale de la commande CREATE INDEX : CREATE [ UNIQUE ] INDEX NomIndex


ON NomTable ( Attribut1 [ ASC | DESC ][, Attribut2 [ ASC | DESC ], ...] ) [WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }] 8. Oprations de maintenance des tuples Ces oprations, qui consistent en lajout, la modification et la suppression de tuples, sont illustres ci-aprs sans commentaires. 8.1. Lajout denregistrements

La commande INSERT INTO reoit le nom de lentit cible et la liste des attributs affecter. Les valeurs souhaites pour ces attributs sont spcifies en liste dans une clause VALUES. Les valeurs de type alphanumriques doivent tre spcifies entre guillemets ou apostrophes. Les parenthses encadrant les listes sont obligatoires. INSERT INTO UneTable (UnAttributNumerique, UnAutre, UnAttributText) VALUES (UneValeurNumerique, UneAutreValeurNumerique, 'UneValeurText'); 8.2. La modification denregistrements La commande UPDATE reoit le nom de lentit cible suivi dans une clause SET, dune liste des affectations des attributs modifier. Une clause WHERE est ncessaire pour dsigner les tuples modifier. UPDATE UneTable SET Attribut1 = ValeurAtt1, Attribut2 = ValeurAtt2, AttChaine = 'UnChaine' WHERE UnAttribut = UneValeur; 8.3. La suppression denregistrements La commande DELETE reoit le nom de lentit ventuellement suivi dune clause WHERE pour dsigner les tuples supprimer. Si cette clause est omise, tous les tuples sont supprims. DELETE FROM UneTableAVider; DELETE FROM UneTable WHERE UnAttribut = UneValeur; 8.3.1. Suppression de tuples contenant un identifiant et des doublons sur un attribut donn La commande SQL suivante supprime de lentit TPers, les lignes dont lattribut Nom comporte des doublons lexception de celles dont lIdPers possde la plus petite valeur. DELETE * FROM TPers WHERE IdPers NOT IN

( SELECT IdAConserver FROM

( SELECT DISTINCT Nom, Min(IdPers) AS IdAConserver FROM TPers GROUP BY Nom )

);

elements sql

Documents