Interrogation de bases de données SQL

1 Interrogation de bases de donn es SQLSt phane janvier 2018 Paternit - Partage des Conditions Initiales l'Identique : des mati resI - Cours 31. Questions simples avec le Langage de Manipulation de Donn es (SELECT) .. Exercice : Repr sentation de repr sentants .. Question (SELECT) .. Op rateurs de comparaisons et op rateurs logiques .. Renommage de colonnes et de tables avec les alias .. D doublonnage (SELECT DISTINCT) .. Tri (ORDER BY) .. Projection de constante .. Commentaires en SQL .. 92. Op rations d'alg bre relationnelle en SQL .. Expression d'une restriction .. Expression d'une projection .. Expression du produit cart sien .. Expression d'une jointure .. Exercice : Tableau final .. Expression d'une jointure externe .. Exercice : Photos gauche .. Op rateurs ensemblistes.

2 18II - Exercices 201. Exercice : Location d'appartements .. 202. Exercice : Employ s et salaires .. 20 III - Devoirs 221. Exercice : Library .. 222. Exercice : Gestion de bus .. 23 Contenus annexes 25 Questions de synth se 27 Solutions des exercices 28 Abr viations 33 Cours3 est un langage standardis , impl ment par tous les , qui permet, ind pendamment de la plate-SQL*SGBDR*forme technologique et de fa on d clarative, de d finir le mod le de donn es, de le contr ler et enfin de le : Repr sentation de repr sentants41.

3 Questions simples avec le Langage de Manipulation de Donn es (SELECT) Exercice : Repr sentation de repr sentants[30 minutes]Soit le sch ma relationnel et le code SQL suivants :1 REPRESENTANTS (#NR, NOMR, VILLE)2 PRODUITS (#NP, NOMP, COUL, PDS)3 CLIENTS (#NC, NOMC, VILLE)4 VENTES (#NR=>REPRESENTANTS(NR), #NP=>PRODUITS(NP), #NC=>CLIENTS(NC), QT)1donn es avec le script SQL suivant */2345678910 REPRESENTANTS (11 NR PRIMARY KEY,12 NOMR ,13 VILLE 14);1516 PRODUITS (17 NP PRIMARY KEY,18 NOMP ,19 COUL ,20 PDS 21);2223 CLIENTS (24 NC PRIMARY KEY,25 NOMC ,26 VILLE 27);2829 VENTES (30 NR REFERENCES REPRESENTANTS(NR),31 NP REFERENCES PRODUITS(NP),32 NC REFERENCES CLIENTS(NC),33 QT ,34 PRIMARY KEY (NR, NP, NC)35);3637 REPRESENTANTS (NR, NOMR, VILLE) (, , );38 REPRESENTANTS (NR, NOMR, VILLE) (, , );39 REPRESENTANTS (NR, NOMR, VILLE) (, , );40 REPRESENTANTS (NR, NOMR, VILLE) (, , );41 REPRESENTANTS (NR, NOMR, VILLE) (, , );4243 PRODUITS (NP, NOMP, COUL, PDS) (, , , );REPRESENTANTS (#NR, NOMR, VILLE)PRODUITS (#NP, NOMP, COUL, PDS)CLIENTS (#NC, NOMC, VILLE)VENTES (#NR=>REPRESENTANTS(NR), #NP=>PRODUITS(NP), #NC=>CLIENTS(NC), QT)/* Les requ tes peuvent tre test es dans un SGBDR, en cr ant une base de donn es avec le script SQL suivant *//*DROP TABLE VENTES ;DROP TABLE CLIENTS ;DROP TABLE PRODUITS ;DROP TABLE REPRESENTANTS ;*/ REPRESENTANTS (CREATETABLE NR PRIMARY KEY,INTEGER NOMR ,VARCHAR VILLE VARCHAR); PRODUITS (CREATETABLE NP PRIMARY KEY,INTEGER NOMP ,VARCHAR COUL ,VARCHAR PDS INTEGER); CLIENTS (CREATETABLE NC PRIMARY KEY,INTEGER NOMC ,VARCHAR VILLE VARCHAR); VENTES (CREATETABLE NR REFERENCES REPRESENTANTS(NR),INTEGER NP REFERENCES PRODUITS(NP),INTEGER NC REFERENCES CLIENTS(NC),INTEGER QT ,INTEGER PRIMARY KEY (NR, NP, NC)); REPRESENTANTS (NR, NOMR, VILLE) (, , ).

4 INSERTINTOVALUES1'Stephane''Lyon' REPRESENTANTS (NR, NOMR, VILLE) (, , );INSERTINTOVALUES2'Benjamin''Paris' REPRESENTANTS (NR, NOMR, VILLE) (, , );INSERTINTOVALUES3'Leonard''Lyon' REPRESENTANTS (NR, NOMR, VILLE) (, , );INSERTINTOVALUES4'Antoine''Brest' REPRESENTANTS (NR, NOMR, VILLE) (, , );INSERTINTOVALUES5'Bruno''Bayonne' PRODUITS (NP, NOMP, COUL, PDS) (, , , INSERTINTOVALUES1'Aspirateur''Rouge'3546 );Question (SELECT)544 PRODUITS (NP, NOMP, COUL, PDS) (, , , );45 PRODUITS (NP, NOMP, COUL, PDS) (, , , );46 PRODUITS (NP, NOMP, COUL, PDS) (, , , );4748 CLIENTS (NC, NOMC, VILLE) (, , );49 CLIENTS (NC, NOMC, VILLE) (, , );50 CLIENTS (NC, NOMC, VILLE) (, , );51 CLIENTS (NC, NOMC, VILLE) (, , );52 CLIENTS (NC, NOMC, VILLE) (, , );5354 VENTES (NR, NP, NC, QT) (, , , );55 VENTES (NR, NP, NC, QT) (, , , );56 VENTES (NR, NP, NC, QT) (, , , );57 VENTES (NR, NP, NC, QT) (, , , );58 VENTES (NR, NP, NC, QT) (, , , );59 VENTES (NR, NP, NC, QT) (, , , );60 VENTES (NR, NP, NC, QT) (, , , );61 VENTES (NR, NP, NC, QT) (, , , );62 VENTES (NR, NP, NC, QT) (, , , );63 VENTES (NR, NP, NC, QT) (, , , );64 VENTES (NR, NP, NC, QT) (, , , );65 VENTES (NR, NP, NC, QT) (, , , ); crire en SQL les requ tes permettant d'obtenir les informations ci-apr 1 Question 2 Question 3 Question 4 Question Question (SELECT)La requ te de ou est la base de la recherche de donn es en lectionquestionTous les d tails de tous les clients.

5 []solution n 1*[] num ros et les noms des produits de couleur rouge et de poids sup rieur 2000.[]solution n 2*[] repr sentants ayant vendu au moins un produit.[]solution n 3*[] noms des clients de Lyon ayant achet un produit pour une quantit sup rieure 180.[]solution n 4*[] noms des repr sentants et des clients qui ces repr sentants ont vendu un produit de couleur rouge pour une quantit sup rieure 100.[]solution n 5*[] : Question PRODUITS (NP, NOMP, COUL, PDS) (, , , INSERTINTOVALUES2'Trottinette''Bleu'1423 ); PRODUITS (NP, NOMP, COUL, PDS) (, , , );INSERTINTOVALUES3'Chaise''Blanc'3827 PRODUITS (NP, NOMP, COUL, PDS) (, , , );INSERTINTOVALUES4'Tapis''Rouge'1423 CLIENTS (NC, NOMC, VILLE) (, , );INSERTINTOVALUES1'Alice''Lyon' CLIENTS (NC, NOMC, VILLE) (, , );INSERTINTOVALUES2'Bruno''Lyon' CLIENTS (NC, NOMC, VILLE) (, , );INSERTINTOVALUES3'Charles''Compi gne' CLIENTS (NC, NOMC, VILLE) (, , );INSERTINTOVALUES4'Denis''Montpellier' CLIENTS (NC, NOMC, VILLE) (, , );INSERTINTOVALUES5'Emile''Strasbourg' VENTES (NR, NP, NC, QT) (, , , );INSERTINTOVALUES1111 VENTES (NR, NP, NC, QT) (, , , );INSERTINTOVALUES1121 VENTES (NR, NP, NC, QT) (, , , );INSERTINTOVALUES2231 VENTES (NR, NP, NC, QT) (, , , );INSERTINTOVALUES433200 VENTES (NR, NP, NC, QT) (, , , ).

6 INSERTINTOVALUES342190 VENTES (NR, NP, NC, QT) (, , , );INSERTINTOVALUES132300 VENTES (NR, NP, NC, QT) (, , , );INSERTINTOVALUES312120 VENTES (NR, NP, NC, QT) (, , , );INSERTINTOVALUES314120 VENTES (NR, NP, NC, QT) (, , , );INSERTINTOVALUES3442 VENTES (NR, NP, NC, QT) (, , , );INSERTINTOVALUES3113 VENTES (NR, NP, NC, QT) (, , , );INSERTINTOVALUES3415 VENTES (NR, NP, NC, QT) (, , , );INSERTINTOVALUES3131Op rateurs de comparaisons et op rateurs logiques6- - - La s lection est la composition d'un , d'une et d'une (ou encore la produit cart sienrestrictionprojectioncomposition d'une jointure et d'une projection).1 SELECT liste d'attributs projet s2 FROM liste de relations du produit cart sien3 WHERE condition de la restrictionLa partie SELECT indique le sous-ensemble des attributs qui doivent appara tre dans la r ponse (c'est le sch ma de la relation r sultat).La partie FROM d crit les relations qui sont utilisables dans la requ te (c'est dire l'ensemble des attributs que l'on peut utiliser).

7 La partie WHERE exprime les conditions que doivent respecter les attributs d'un tuple pour pouvoir tre dans la r ponse. Une condition est un pr dicat et par cons quent renvoie un bool en. Cette partie est Nom, Prenom2 Personne3 Age>Cette requ te s lectionne les attributs Nom et Pr nom des tuples de la relation Personne, ayant un attribut Age sup rieur de d crire un attribut d'une relation en particulier (dans le cas d'une requ te portant sur plusieurs relations notamment), on utilise la notation . Personne, Personne, Vol2 Personne, Vol3 Personne=VolPour projeter l'ensemble des attributs d'une relation, on peut utiliser le caract re la place de la liste des *attributs *2 AvionCette requ te s lectionne tous les attributs de la relation que dans cet exemple, la relation r sultat est exactement la relation AvionD finition : S lectionSyntaxeExempleSyntaxe : Notation pr fix eExempleSyntaxe : SELECT *ExempleSELECT liste d'attributs projet sFROM liste de relations du produit cart sienWHERE condition de la restriction Nom, PrenomSELECT PersonneFROM Age>WHERE18 Personne, Personne, Personne, VolFROM Personne= *SELECT AvionFROMR enommage de colonnes et de tables avec les alias7- - - - - - - - - - - - - - - Op rateurs de comparaisons et op rateurs logiquesLa clause WHERE d'une instruction de s lection est d finie par une condition.

8 Une telle condition s'exprime l'aide d'op rateurs de comparaison et d'op rateurs logiques. Le r sultat d'une expression de condition est toujours un bool El mentaire ::= Propri t <Op rateur de comparaison> Constante2 Condition ::= Condition <Op rateur logique> Condition | Condition El mentaire Les op rateurs de comparaison sont :P = CP <> CP < CP > CP <= CP >= CP BETWEEN C1 AND C2P IN (C1, C2, ..)P LIKE 'cha ne'P IS NULLLes op rateur logique sont :ORANDNOTL'op rateur 'cha ne' permet d'ins rer des jokers dans l'op ration de comparaison (alors que l'op rateur LIKE=teste une galit stricte) :Le joker d signe 0 ou plusieurs caract res quelconques%Le joker d signe 1 et 1 seul caract re_On pr f rera l'op rateur l'op rateur lorsque la comparaison n'utilise pas de Renommage de colonnes et de tables avec les aliasIl est possible de red finir le nom des relations au sein de la requ te afin d'en simplifier la , table1 t1, table2 t2 IntroductionD finition : ConditionRemarque : Op rateur LIKES yntaxe : Alias de tableCondition El mentaire ::= Propri t <Op rateur de comparaison> ConstanteCondition.

9 = Condition <Op rateur logique> Condition | Condition El mentaire SELECT , table1 t1, table2 t2D doublonnage (SELECT DISTINCT)81 SELECT , Parent, Enfant3 WHERE requ te s lectionne les pr noms des enfants et des parents ayant le m me nom. On remarque la notation et pour distinguer les attributs Prenom des relations Parent et notera que cette s lection effectue une jointure sur les propri t s Nom des relations Parent et est possible de red finir le nom des propri t s de la relation r attribut1 a1, attribut2 a2 2 D doublonnage (SELECT DISTINCT)L'op rateur SELECT n' limine pas les doublons ( les tuples identiques dans la relation r sultat) par d faut. Il faut pour cela utiliser l'op rateur SELECT Avion2 Vol3 =--Cette requ te s lectionne l'attribut Avion de la relation Vol, concernant uniquement les vols du 31 d cembre 2000 et renvoie les tuples sans Tri (ORDER BY)On veut souvent que le r sultat d'une requ te soit tri en fonction des valeurs des propri t s des tuples de ce r liste d234attributsLes tuples sont tri s d'abord par le premier attribut sp cifi dans la clause ORDER BY, puis en cas de doublons par le second, : Alias d'attribut (AS)Attention : SELECT DISTINCTE xempleIntroductionSyntaxe : ORDER BYSELECT , Parent, EnfantWHERE attribut1 a1, attribut2 a2 SELECTASAS FROM table AvionSELECTDISTINCT VolFROM =--WHEREDate31122000 liste dSELECT'attributs projet sFROM liste de relationsWHERE conditionattributsORDER BY liste ordonn e d'Projection de constante9- - Pour effectuer un tri d croissant on fait suivre l'attribut du mot cl "DESC".

10 1 *2 Personne3 Nom, Age Projection de constanteIl est possible de projeter directement des constantes (on utilisera g n ralement un alias d'attribut pour nommer la colonne).1 constante nomCette requ te renverra une table avec une seule ligne et une seule colonne la valeur de .constante1 num1 num 2-----3 11 hw1 hw 2-------------3 Hello world1 CURRENT_DATE now1 now 2------------3 Commentaires en SQLL'introduction de commentaires au sein d'une requ te SQL se fait :En pr c dant les commentaires de pour les commentaires mono-ligne--En encadrant les commentaires entre et pour les commentaires **/Remarque : Tri d croissantExempleSyntaxe : Projection de constanteExempleExempleExemple *SELECT PersonneFROM Nom, Age ORDERBYDESC constante nomSELECTAS numSELECT'1'AS num ----- 1 hwSELECT'Hello world'AS hw ------------- Hello world CURRENT_DATE nowSELECTAS now ------------ 2016-10-21Op rations d'alg bre relationnelle en SQL1012345 person (6pknum () PRIMARY KEY, 7name () 8);910 2.