SQL-exercices-solutionnés

Outils pour la conception de systèmes d'information

Chapitre 8 : Algèbre relationnelle et SQL Les opérations de manipulations des données d'une base relationnelle 1.

L'algèbre relationnelle, qu'est ce que c'est ?

C'est un outil de raisonnement qui permet de décomposer quelles sont les opérations élémentaires à faire sur une base de données pour obtenir les résultats souhaités. Exemple de résultat : Afficher la liste de tous les clients ayant passé une commande avant le 31 Décembre 1998. De même qu'une addition utilise des nombres comme opérandes et a toujours un nombre comme résultat, une opération en algèbre relationnelle utilise une ou plusieurs relations comme opérandes et a toujours pour résultat une relation. De même qu'il existe plusieurs opérations sur les nombres (addition, soustraction, etc..), il existe différentes opérations que l'on peut faire sur les relations d'un schéma relationnel. Pour comprendre facilement l'algèbre relationnelle, il faut se représenter chaque relation d'un schéma relationnel comme un tableau dont chaque colonne est en fait un attribut de la relation. Exemple : Soit la relation enfant (EnfantID, EnfantNom, EnfantPrenom). Pour manipuler facilement la relation "enfant" en algèbre relationnelle, il faut se la représenter comme suit (comme une table de la base) : EnfantId 1234 54321 2.

enfant EnfantNom Radroite Phoutus

EnfantPrenom Sylvie Honnet

Et le SQL, c'est quoi ?

S.Q.L. (Structured Query Language) est un langage de programmation particulier qui permet de manipuler des bases de données relationnelles. S.Q.L. se retrouve aujourd'hui dans la très grande majorité des S.G.B.D. (Système de Gestion de Bases de Données), et fonctionne sur des plates-formes allant des gros systèmes aux microordinateurs. Exemple de S.G.B.D. utilisant S.Q.L. pour la gestion des données : MS-ACCESS, ORACLE, SYBASE, INFORMIX, DB2, INGRES, SQLSERVER... S.Q.L. fait l'objet d'une normalisation (la norme actuelle est la norme ANS 1985), et le fait qu'il soit devenu un standard dans l'interrogation des bases de données, n'empêche pas les éditeurs de S.G.B.D. d'avoir des versions de SQL qui diffèrent légèrement les unes des autres, ce qui limite considérablement la portabilité des bases de données d'un environnement à un autre. S.Q.L. n'est pas un langage de programmation au sens classique du terme, c'est un langage qui permet de faire des manipulations sur des bases de données (ou requêtes). 1


Ce langage contient plusieurs catégories de commandes : 

Le langage de description de données (L.D.D.) qui permet la création et la modification de la structure de bases de données (tables et attributs, vues, états, index, contraintes d'intégrité ...). Cette partie n'est pas au programme de pemière année : on n'utilise pas directement les commandes SQL pour créer une base et/ou modifier sa structure, on le fait avec des logiciels qui nous simplifient le travail en créant les commandes SQL à notre place.



Le langage de contrôle des données (L.C.D.) doit permettre la sécurité des données, et leur confidentialité (qui a le droit de faire quoi, et sur quoi ?), réservé à l'administrateur de la base (le DBA).



Le langage de manipulation de données (L.M.D.) qui permet la gestion des données se trouvant dans les tables (consultation, mise à jour, ...). C'est cette partie (et encore, pas tout…) que nous étudions en première année. Exemple d'opérations de mise à jour : changer dans la base l'adresse d'un client car il a déménagé.

2


MCD utilisé pour les exercices :

Schéma relationnel correspondant : MAGASINS (mag_num, mag_loc, mag_ger) ARTICLES (art_num, art_nom, art_poids, art_coul, art_pa, art_pv, frs_num#) àFOURNISSEURS (frs_num, frs_nom) CLIENTS (clt_num, clt_nom, clt_pnom, clt_pays, clt_loc) VENTES (vnt_clt#, vnt_mag#, vnt_art#, vnt_qte, vnt_prix, vnt_date) Remarque : l'attribut vnt_mag de la relation VENTES correspond à l'attribut mag_num de la relation MAGASINS. De même, vnt_art et vnt_clt de VENTES s'appellent respectivement art_num et clt_num. En effet, un attribut ne porte pas forcément le même nom dans la relation où il est clé primaire et dans la table où il est clé étrangère. Modèle physique correspondant :

3


1. 

L'égalité Syntaxe en algèbre relationnelle (noté AR dans le reste du cours) : NouvelleRelation = RelationUtilisée



Syntaxe en SQL : SELECT * FROM RelationUtilisée

Ceci provoque l'affichage de toutes les données de la table "RelationUtilisée". Exemple : Ecrire en AR, puis en SQL une requête ayant pour effet d'afficher tous les renseignements concernant tous les fournisseurs. AR : Relation = fournisseurs SQL : Select * from fournisseurs Résultat : Affichage du contenu complet de la table fourniseurs 2.

La projection

Cette opération de l'algèbre relationnelle a pour rôle de créer une nouvelle relation ne contenant qu'une partie des attributs (colonnes) issue d'une ou plusieurs tables. 21. 

Projection simple Syntaxe en algèbre relationnelle : NouvelleRelation = Projection(Relation1 (attr1,attr2,…attrn))



Syntaxe en SQL : SELECT attr1,attr2,…attrn FROM Relation1

Exemple : Ecrire en AR, puis en SQL une requête ayant pour effet d'afficher les noms de tous les fournisseurs. AR : Relation= Projection(fournisseurs(frs_nom)) SQL : SELECT frs_nom FROM fournisseurs Résultat : Affichage de la liste des noms des fournisseurs

4


Exemple : Ecrire en AR, puis en SQL une requête ayant pour effet d'afficher les noms de tous les articles existant. AR : Relation= Projection(articles(art_nom)) SQL : SELECT art_nom FROM articles Résultat : Affichage de la liste des noms des articles Le problème : si on la laisse en l'état, cette requête affiche les répétitions, qui dans ce cas précis, ne sont pas souhaitables. 22.

Projection avec suppression des éventuels doublons

Pour éliminer les "doublons" dans le résultat, il existe une "clause" SQL qui est la clause "DISTINCT". Si on écrit la requête précédente comme suit : AR : Relation= Projection(articles(art_nom), sans double) SQL : SELECT DISTINCT art_nom FROM articles Résultat : Affichage de la liste des noms des articles avec suppression des doublons 3.

La sélection (ou Restriction)

La sélection est l'opération de l'algèbre relationnelle qui permet de sélectionner des lignes (nuplets) d'une ou plusieurs tables répondant à certains critères. AR :

Relation= selection(RelationUtilisée(condition))

SQL : SELECT * FROM RelationUtilisée WHERE condition La condition peut avoir des formes multiples : 

Comparaison à une valeur : =, <>, <, >, <=, >=

Exemple : Ecrire en AR, puis en SQL une requête ayant pour effet d'afficher tous les renseignements sur les articles de couleur rouge. AR : Relation= Sélection(articles(art_coul = "ROUGE")) SQL : SELECT * FROM articles WHERE art_coul = "ROUGE" Résultat : Affichage de la liste des articles rouges

5

Outils pour la conception de systèmes d'information 

Comparaison à une fourchette de valeurs : BETWEEN…AND

Exemple : Ecrire en AR, puis en SQL une requête ayant pour effet d'afficher tous les renseignements sur les clients dont le numéro est compris entre 5 et 15. AR : Relation= Sélection(clients(clt_num entre 5 et 15) SQL : SELECT * FROM clients WHERE clt_num BETWEEN 5 AND 15 Résultat : Affichage de la liste des clients de 5 à 15 

Comparaison à un filtre : LIKE

Exemple : Ecrire en AR, puis en SQL une requête ayant pour effet d'afficher tous les renseignements sur les clients dont le nom comporte la lettre "C". AR : Relation= Sélection(clients(clt_nom comme "%C%") SQL : SELECT * FROM clients WHERE clt_nom LIKE "%C%" 

Comparaison à la valeur d'une expression

On peut faire des requêtes du genre suivant : Exemple : Afficher les articles dont le prix de vente est supérieur ou égal à 2 fois le prix d'achat SQL : SELECT * FROM articles WHERE art_pv >= 2 * art_pa 

Comparaison à une liste de valeurs : IN

Exemple : Afficher la liste des articles de couleur rouge ou verte AR : Relation= Sélection(articles(art_coul dans ("ROUGE", "VERT")) SQL : SELECT * FROM articles WHERE art_coul IN ("ROUGE","VERT") Remarque : Cette requête peut aussi s'écrire SELECT * FROM articles WHERE art_coul = "ROUGE" or art_coul = "VERT" Autre exemple : Afficher tous les articles qui ont été vendus Principe : 6


AR :

Récupérer dans la table "ventes" tous les numéros d'articles Afficher les renseignements sur les articles dont le numéro figure dans la table "ventes" Relation = projection (ventes (art_num), sans double) Résultat = Sélection (articles (art_num dans relation))

SQL : SELECT * FROM articles WHERE art_num IN (SELECT DISTINCT art_num FROM ventes) C'est l'instruction entre parenthèses qui est effectuée en premier. Ceci s'appelle une requête imbriquée. 

Test sur la valorisation d'un attribut : IS NULL

Exemple : Afficher les articles dont le poids n'est pas renseigné. AR : Résultat = sélection (articles (art_poids = NULL)) SQL : SELECT * FROM articles WHERE art_poids IS NULL 

Négation d'une condition : NOT

Exemple : Afficher tous les articles qui ne sont pas rouges AR : Relation= Sélection(articles(art_coul <> "ROUGE")) Ou bien Relation= Sélection(articles(NON art_coul = "ROUGE")) SQL : SELECT * FROM articles WHERE NOT art_coul = "ROUGE" (ou bien art_coul <> "ROUGE")

7

Outils pour la conception de systèmes d'information 

Condition renfermant plusieurs sous-conditions liées avec : AND ou OR -

Avec AND

Exemple : Afficher tous les articles qui sont rouges et dont le poids dépasse 100 grammes AR : Relation= Sélection(articles(art_coul = "ROUGE" ET art_poids > 100)) SQL : SELECT * FROM articles WHERE art_coul = "ROUGE" AND art_poids > 100 

Avec OR : même genre

Ordre des opérateurs logiques : NOT est prioritaire sur AND qui est prioritaire sur OR.

Exemple : Afficher les articles ni rouges ni verts. AR : resultat = sélection (articles (NON(art_coul = "ROUGE" ou art_coul = "VERT")) SQL : SELECT * FROM articles WHERE NOT ( art_coul = "ROUGE" OR art_coul = "VERT") Ou bien : WHERE NOT art_coul = "ROUGE" AND NOT art_coul = "VERT" Remarque : si on enlève la parenthèse, c'est à dire si la requête est SELECT * FROM articles WHERE NOT art_coul = "ROUGE" art_coul = "VERT", alors, on affiche les articles qui ne sont pas rouges ou bien qui sont verts, c'est à dire qu'on affiche les articles verts. 4.

Sélection et projection en même temps :

Il s'agit de sélectionner certaines colonnes des lignes satisfaisant à la condition indiquée dans la clause WHERE. Exemple : Afficher les noms des articles qui sont rouges AR :

Relation= Sélection(articles(art_coul <> "ROUGE")) Resultat = projection (Relation(art_nom)

SQL : SELECT art_nom FROM articles WHERE art_coul = "ROUGE"

8


5.

La jointure :

On fait une opération de jointure quand on établit un lien entre une colonne d'une table et une colonne d'une autre table (par exemple : entre le numéro de fournisseur se trouvant dans le table article et le numéro de fournisseur se trouvant dans la table fournisseur). Les opérations de jointure servent à récupérer, par exemple, le nom du fournisseur fournissant l'article numéro 5 alors que dans la table ARTICLE, on n'a que le numéro du fournisseur : AR :

relation1 = sélection (articles (art_num = 5) Relation2 = jointure (relation1.frs_num = fournisseurs.frs_num) Resultat = projection (relation2(frs_nom)

SQL : SELECT frs_nom FROM articles, fournisseurs WHERE articles.frs_num = fournisseur.frs_num AND art_num = 5 Autre formulation SQL : SELECT frs_nom FROM fournisseurs WHERE frs_num IN (

SELECT frs_num FROM articles WHERE art_num = 5)

Remarque : on est obligé de préciser le nom de la table uniquement quand les attributs portent le même nom. Exercices : Ecrire en AR puis en SQL les requêtes suivantes Req 1 : Afficher la liste des articles fournis par le fournisseur "SARL ROULAND" Req 2 : Afficher le nom des fournisseurs qui ne fournissent pas d'articles rouges. Req 3 : Afficher les coordonnées des clients ayant commandé des produits en une quantité supérieure à 50. Req 4 : Afficher la liste des articles de la même couleur que l'article 10.

9


6.

Présentation des résultats

61.

Présentation du résultat trié sur certains critères

• Exemple 1 : Affichage du numéro, nom et poids de tous les articles SELECT art_num, art_nom, art_poids FROM articles Affichage du numéro, nom et poids de tous les articles triés l'ordre croissant de leur poids SELECT art_num, art_nom, art_poids FROM articles ORDER BY art_poids ou bien SELECT art_num, art_nom, art_poids FROM articles ORDER BY 3; (car art_poids est le 3ème attribut de la clause SELECT) ou bien SELECT art_num, art_nom, art_poids FROM articles ORDER BY 3 ASC; (par défaut, c'est à dire si on ne précise rien, le tri se fait en ordre croissant) • Exemple 2 : ordre décroissant "ORDER BY …..DESC" Liste des nom, numéro, poids et prix d'achat des articles de poids inférieur à 100 selon l'ordre décroissant de leur prix d'achat SELECT art_num, art_nom, art_poids, art_pa FROM articles WHERE art_poids <= 100 ORDER BY art_pa DESC; • Exemple 3 : Tri des articles de poids inférieur à 100 selon l'ordre croissant de leur poids et à poids égal, par prix d'achat décroissant SELECT art_num, art_nom, art_poids, art_pa FROM articles WHERE art_poids <= 100 ORDER BY art_poids, art_pa DESC; Quand on indique plusieurs critères de tri à la file l'un de l'autre, le tri relatif au deuxième critère est effectué pour les nuplets qui ont tous le premier critère identique, le tri relatif au troisième critère est effectué pour les nuplets qui ont tous le deuxième critère identique etc... 10


62.

Utilisation de fonctions arithmétiques dans les requêtes

Exemple : Afficher le nom et la marge bénéficiaire sur tous les produits, par ordre croissant sur la valeur de la marge SELECT art_nom, art_pv - art-pa FROM articles ORDER BY art_pv - art_pa; ou bien SELECT art_nom, art_pv - art-pa as marge FROM articles ORDER BY marge On indique par "art-pv - art_pa as marge" que la variable temporaire "marge" est égale à la différence entre art-pv et art_pa. 7.

Les fonctions ensemblistes ou fonctions de groupe

Ces fonctions du langage SQL effectuent un calcul sur des ensembles de valeurs. 71.

La moyenne : AVG

Elle calcule la moyenne des valeurs dans l'attribut (qui doit être de type numérique) Exemple : Poids moyen des articles SELECT AVG(art_poids) FROM articles; Remarque : pour les amoureux de l'algèbre relationnelle, on peut éventuellement introduire la fonction "moyenne" et la requête ci-dessus, en AR est alors : Résultat = projection (articles, moyenne(art_poids)) 72.

La somme : SUM

Calcule la somme des valeurs dans l'attribut. Exemple : Somme des poids de tous les articles SELECT SUM(art_poids) FROM articles 73. •

Comptage du nombre d'occurrences dans l'attribut : COUNT

Exemple : Afficher le nombre de couleurs différentes existant dans le stock SELECT COUNT(DISTINCT art_coul) FROM articles; 11


Question : que se passe-t-il si on ne met pas la clause "DISTINCT" ? • Autre exemple : Compter tous les nuplets (= toutes les lignes) de la table ARTICLES. SELECT count * FROM articles; 74. Recherche de la plus grande (ou de la plus petite) valeur dans un attribut : MAX (ou MIN) • Exemple : Nom et poids de l'article le plus lourd SELECT art_nom, MAX(art_poids) FROM articles; • Exemple : Liste des articles dont le prix de vente est supérieur au prix de vente de l'article de couleur blanche le moins cher (Recherche de la plus petite valeur dans l'attribut : MIN, même utilisation que MAX). SELECT art_nom FROM articles WHERE art_pv > (SELECT MIN(art_pv FROM articles WHERE art_coul = 'BLANC') 8.

Les requêtes sur des groupes

81.

La clause GROUP BY

Crée des sous-tables dans une table en fonction de la valeur d'un critère donné : c'est à dire que GROUP BY partitionne la table en sous-tables ayant la même valeur pour 1 attribut ou un groupe d'attributs. Les fonctions de groupe présentées ci-dessus sont alors calculées pour chaque sous-table. • Exemple 1 : Nombre d'articles pour chaque couleur SELECT art_coul, COUNT(*) FROM articles GROUP BY art_coul;

12


• Exemple 2 : Calcul du prix moyen des ventes des articles rangés par couleur SELECT art_coul, AVG(art_pv) FROM articles GROUP BY art_coul; SQL crée les groupes d'après les différentes valeurs de art_coul, puis, pour chaque valeur de art_coul, calcule le prix moyen des ventes. Si, dans le cas de notre exemple, certaines couleurs n'étaient pas indiquées, un groupe est créé pour chaque n-uplet de couleur indéfinies. • Exemple 3 : Calcul du prix moyen des ventes des articles rangés par couleur en excluant les articles dont le prix d'achat est inférieur à 5 SELECT art_coul, AVG(art_pv) FROM articles WHERE art_pa >= 5 GROUP BY art_coul; • Exemple 4 : Calcul du prix moyen des ventes des articles rangés par couleur en excluant les articles dont le prix d'achat est inférieur à 5, les couleurs devant être affichées dans l'ordre lexicographique SELECT art_coul, AVG(art_pv) FROM articles WHERE art_pa >= GROUP BY art_coul ORDER BY art_coul; • Exemple 5 : Calcul par article de la remise totale accordée par rapport au prix de vente catalogue pour la semaine allant du 23 au 30 Décembre 97, l'affichage doit être trié sur le numéro d'article. SELECT ventes.art_num, SUM(vnt_qte * (art_pv - vnt_prix)) remise FROM ventes , articles WHERE ventes.art_num = articles.art_num AND vnt_date BETWEEN '231297' AND '201297') GROUPBY vnt_art ORDER BY v.art_num ;

13


82.

La clause HAVING

C'est l'équivalent de WHERE mais appliqué aux groupes, on ne peut donc utiliser cette clause que si on a utilisé une fonction de groupe ou la clause GROUP BY. Un petit truc : il faut utiliser la clause "HAVING" quand on a envie d'écrire "WHERE COUNT…", qui n'est pas accepté par SQL. • Afficher les numéros des magasins ayant réalisé 3 ventes : SELECT vnt_mag, COUNT(*) FROM ventes GROUP BY vnt_mag HAVING COUNT(*) = 3; • Exemple : Afficher les magasins ayant réalisé plus de 2 ventes entre le 231197 et le 301197 SELECT vnt_mag, COUNT(*) FROM ventes WHERE vn_date BETWEEN '231197' AND '301197' GROUP BY vnt_mag HAVING COUNT(*) > 2 • Exemple : Afficher les noms et numéros des fournisseurs fournissant plus de 4 articles : SELECT frs_nom, count(articles.frs_num) FROM fournisseurs, articles WHERE fournisseurs.frs_num = articles.frs_num GROUP BY articles.frs_num, frs_nom HAVING COUNT(articles.frs_num) >= 4; • Remarque : ordre des commandes dans une requête SELECT… FROM…. WHERE…. GROUP BY…. HAVING….

14


9.

Quelques commandes de mise à jour d'une base de données

91.

Insertion de nuplets : INSERT INTO

Exemple1 : INSERT INTO matable VALUES (1,'DUPONT', 'Jean',33); Si le nom des attributs n'est pas spécifié, cela suppose qu'ils sont tous concernés ou bien INSERT INTO matable (numero, nom ,pnom, age) VALUES (1,'DUPONT', 'Jean',33); Equivalent à la commande ci-dessus Exemple 2 : INSERT INTO matable(numero, nom) VALUES (1,'DUPONT'); Insertion d'un nuplet en laissant le prénom et l'âge indéterminés. Ou bien INSERT INTO matable VALUES (1,'DUPONT', NULL, NULL); Equivalent à la commande ci-dessus Exemple 3 : On suppose pour cet exemple qu'une table CLIENTSBIS a été créée, ayant le même schéma que la table CLIENTS, et qu'on veut y mettre tous les nuplets concernant les clients parisiens. INSERT INTO clientbis SELECT * FROM clients WHERE clt_loc = 'PARIS'

15


92.

Suppression de nuplets : DELETE

Exemple 1 : Supprimer tous les nuplets de la table articles DELETE * FROM articles; Exemple 2 : Supprimer tous les articles fournis par le fournisseur de l'agrafeuse DELETE FROM articles WHERE frs_num = (SELECT frs_num FROM articles WHERE art_nom = 'AGRAFEUSE'); 93.

Modification de nuplets : UPDATE ... SET

Exemple 1 : Doubler le prix de vente de tous les articles dont le poids est inférieur à 100 UPDATE articles SET art_pv = art_pv * 2 WHERE art_poids <100; Exemple 2 : Donner à tous les articles le même prix de vente qui est en fait le prix de vente moyen actuel UPDATE articles SET art_pv = (SELECT AVG(art_pv) FROM articles)

16

SQL-exercices-solutionnés

Recommend Documents