Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Conception de bases de données SQL2 Interrogation de bases de données SQL Pater

Conception de bases de données SQL2 Interrogation de bases de données SQL Paternité - Partage des Conditions Initiales à l'Identique : http://creativecommons.org/licenses/by-sa/2.0/fr/ STÉPHANE CROZAT 13 février 2017 Table des matières I - Cours 3 A. Questions simples avec le Langage de Manipulation de Données (SELECT)..................3 1. Représentation de représentants.............................................................................................................3 2. Question (SELECT)....................................................................................................................................5 3. Opérateurs de comparaisons et opérateurs logiques..............................................................................6 4. Renommage de colonnes et de tables avec les alias...............................................................................6 5. Dédoublonnage (SELECT DISTINCT).........................................................................................................7 6. Tri (ORDER BY)..........................................................................................................................................7 7. Projection de constante............................................................................................................................8 8. Commentaires en SQL..............................................................................................................................8 B. Opérations d'algèbre relationnelle en SQL....................................................................9 1. Expression d'une restriction.....................................................................................................................9 2. Expression d'une projection......................................................................................................................9 3. Expression du produit cartésien.............................................................................................................10 4. Expression d'une jointure.......................................................................................................................11 5. Exercice : Tableau final...........................................................................................................................12 6. Expression d'une jointure externe..........................................................................................................14 7. Exercice : Photos à gauche....................................................................................................................16 8. Opérateurs ensemblistes........................................................................................................................17 II - Exercices 18 A. Location d'appartements............................................................................................18 B. Employés et salaires...................................................................................................18 III - Devoirs 20 A. Library.........................................................................................................................20 B. Gestion de bus............................................................................................................21 Questions de synthèse 23 Solution des exercices 24 Signification des abréviations 29 Contenus annexes 30 Stéphane Crozat (Contributions : Benjamin Lussier, Antoine Vincent) 2 I - Cours I SQL est un langage standardisé, implémenté par tous les SGBDR, qui permet, indépendamment de la plate-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 manipuler. A. Questions simples avec le Langage de Manipulation de Données (SELECT) 1. 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) 1 /* Les requêtes peuvent être testées dans un SGBDR, en créant une base de données avec le script SQL suivant */ 2 3 /* 4 DROP TABLE VENTES ; 5 DROP TABLE CLIENTS ; 6 DROP TABLE PRODUITS ; 7 DROP TABLE REPRESENTANTS ; 8 */ 9 10 CREATE TABLE REPRESENTANTS ( 11 NR INTEGER PRIMARY KEY, 12 NOMR VARCHAR, 13 VILLE VARCHAR 14 ); 15 16 CREATE TABLE PRODUITS ( 17 NP INTEGER PRIMARY KEY, 18 NOMP VARCHAR, 19 COUL VARCHAR, 20 PDS INTEGER 21 ); 22 23 CREATE TABLE CLIENTS ( 24 NC INTEGER PRIMARY KEY, 25 NOMC VARCHAR, Stéphane Crozat (Contributions : Benjamin Lussier, Antoine Vincent) 3 26 VILLE VARCHAR 27 ); 28 29 CREATE TABLE VENTES ( 30 NR INTEGER REFERENCES REPRESENTANTS(NR), 31 NP INTEGER REFERENCES PRODUITS(NP), 32 NC INTEGER REFERENCES CLIENTS(NC), 33 QT INTEGER, 34 PRIMARY KEY (NR, NP, NC) 35 ); 36 37 INSERT INTO REPRESENTANTS (NR, NOMR, VILLE) VALUES (1, 'Stephane', 'Lyon'); 38 INSERT INTO REPRESENTANTS (NR, NOMR, VILLE) VALUES (2, 'Benjamin', 'Paris'); 39 INSERT INTO REPRESENTANTS (NR, NOMR, VILLE) VALUES (3, 'Leonard', 'Lyon'); 40 INSERT INTO REPRESENTANTS (NR, NOMR, VILLE) VALUES (4, 'Antoine', 'Brest'); 41 INSERT INTO REPRESENTANTS (NR, NOMR, VILLE) VALUES (5, 'Bruno', 'Bayonne'); 42 43 INSERT INTO PRODUITS (NP, NOMP, COUL, PDS) VALUES (1, 'Aspirateur', 'Rouge', 3546); 44 INSERT INTO PRODUITS (NP, NOMP, COUL, PDS) VALUES (2, 'Trottinette', 'Bleu', 1423); 45 INSERT INTO PRODUITS (NP, NOMP, COUL, PDS) VALUES (3, 'Chaise', 'Blanc', 3827); 46 INSERT INTO PRODUITS (NP, NOMP, COUL, PDS) VALUES (4, 'Tapis', 'Rouge', 1423); 47 48 INSERT INTO CLIENTS (NC, NOMC, VILLE) VALUES (1, 'Alice', 'Lyon'); 49 INSERT INTO CLIENTS (NC, NOMC, VILLE) VALUES (2, 'Bruno', 'Lyon'); 50 INSERT INTO CLIENTS (NC, NOMC, VILLE) VALUES (3, 'Charles', 'Compiègne'); 51 INSERT INTO CLIENTS (NC, NOMC, VILLE) VALUES (4, 'Denis', 'Montpellier'); 52 INSERT INTO CLIENTS (NC, NOMC, VILLE) VALUES (5, 'Emile', 'Strasbourg'); 53 54 INSERT INTO VENTES (NR, NP, NC, QT) VALUES (1, 1, 1, 1); 55 INSERT INTO VENTES (NR, NP, NC, QT) VALUES (1, 1, 2, 1); 56 INSERT INTO VENTES (NR, NP, NC, QT) VALUES (2, 2, 3, 1); 57 INSERT INTO VENTES (NR, NP, NC, QT) VALUES (4, 3, 3, 200); 58 INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 4, 2, 190); 59 INSERT INTO VENTES (NR, NP, NC, QT) VALUES (1, 3, 2, 300); 60 INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 1, 2, 120); 61 INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 1, 4, 120); 62 INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 4, 4, 2); 63 INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 1, 1, 3); 64 INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 4, 1, 5); 65 INSERT INTO VENTES (NR, NP, NC, QT) VALUES (3, 1, 3, 1); Écrire en SQL les requêtes permettant d'obtenir les informations ci-après. Q u e s t i o n 1 [Solution n°1 p 23] T ous les détails de tous les clients. Q u e s t i o n 2 [Solution n°2 p 23] Les numéros et les noms des produits de couleur rouge et de poids supérieur à 2000. Q u e s t i o n 3 [Solution n°3 p 23] Les représentants ayant vendu au moins un produit. Q u e s t i o n 4 [Solution n°4 p 23] Les noms des clients de Lyon ayant acheté un produit pour une quantité supérieure à 180. Q u e s t i o n 5 [Solution n°5 p 24] Les noms des représentants et des clients à qui ces représentants ont vendu un produit de couleur rouge pour une quantité supérieure à 100. Cours Stéphane Crozat (Contributions : Benjamin Lussier, Antoine Vincent) 4 2. Question (SELECT) Fondamental: Question La requête de sélection ou question est la base de la recherche de données en SQL. Défi nition : Sélection La sélection est la composition d'un produit cartésien, d'une restriction et d'une projection (ou encore la composition d'une jointure et d'une projection). Syntaxe 1 SELECT liste d'attributs projetés 2 FROM liste de relations du produit cartésien 3 WHERE condition de la restriction  La 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).  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 optionnelle. Exemple 1 SELECT Nom, Prenom 2 FROM Personne 3 WHERE Age>18 Cette requête sélectionne les attributs Nom et Prénom des tuples de la relation Personne, ayant un attribut Age supérieur à 18. Syntaxe : Notation préfixée Afin 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 relation.attribut. Exemple 1 SELECT Personne.Nom, Personne.Prenom, Vol.Depart 2 FROM Personne, Vol 3 WHERE Personne.Vol=Vol.Numero Syntaxe : SELECT * Pour projeter l'ensemble des attributs d'une relation, on peut utiliser le caractère * à la place de la liste des attributs à projeter. Exemple 1 SELECT * 2 FROM Avion Cette requête sélectionne tous les attributs de la relation Avion. Notons que dans cet exemple, la relation résultat est exactement la relation Avion Cours Stéphane Crozat (Contributions : Benjamin Lussier, Antoine Vincent) 5 3. Opérateurs de comparaisons et opérateurs logiques Introduction La clause WHERE d'une instruction de sélection est définie par une condition. 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éen. Défi nition : Condition 1 Condition Elémentaire ::= Propriété <Opérateur de comparaison> Constante 2 Condition ::= Condition <Opérateur logique> Condition | Condition Elémentaire Les opérateurs de comparaison sont :  P = C  P <> C  P < C  P > C  P <= C  P >= C  P BETWEEN C1 AND C2  P IN (C1, C2, ...)  P LIKE 'chaîne'  P IS NULL Les opérateur logique sont :  OR  AND  NOT Remarque: Opérateur LIKE L'opérateur LIKE 'chaîne' permet d'insérer des jokers dans l'opération de comparaison (alors que l'opérateur = 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 LIKE lorsque la comparaison n'utilise pas de joker. 4. Renommage de colonnes et de tables avec les alias Syntaxe : Alias de table Il est possible de redéfinir le nom des relations au sein de la requête afin d'en simplifier la syntaxe. 1 SELECT t1.attribut1, t2.attribut2 2 FROM table1 t1, table2 t2 Exemple 1 SELECT Parent.Prenom, Enfant.Prenom 2 FROM Parent, Enfant 3 WHERE Enfant.Nom=Parent.Nom Cours Stéphane Crozat (Contributions : Benjamin Lussier, Antoine Vincent) 6 Cette requête sélectionne les prénoms des enfants et des parents ayant le même nom. On remarque la notation Parent.Nom et Enfant.Nom pour distinguer les attributs Prenom des relations Parent et Enfant. On notera que cette sélection effectue une jointure sur les propriétés Nom des relations Parent et Enfant. Remarque: Alias d'attribut (AS) Il est possible de redéfinir le nom des propriétés de la relation résultat. 1 SELECT attribut1 AS a1, attribut2 AS a2 2 FROM table 5. uploads/Marketing/ sql-2.pdf