Post on 10-Sep-2018
Analyse de bases de données SQL avec les agrégats (GROUP BY)
Stéphane Crozat
stph.scenari-community.org/bddsql3.pdf
29 janvier 2018Paternité - Partage des Conditions Initiales à l'Identique : http://creativecommons.org/licenses/by-sa/4.0/fr/
Table des matières
I - Cours 3
1. Introduction aux agrégats avec GROUP BY ................................................................................................... 3
1.1. Exercice : La somme finale .......................................................................................................................................................... 31.2. Définition de l'agrégation ............................................................................................................................................................ 31.3. Exemple d'attribut d'agrégation .................................................................................................................................................... 51.4. Fonctions d'agrégation ................................................................................................................................................................. 61.5. Exemple de fonctions d'agrégation ............................................................................................................................................... 7
2. Approfondissement des agrégats avec GROUP BY et HAVING ................................................................. 10
2.1. Single-Value Rule ...................................................................................................................................................................... 102.2. Restriction après agrégation (HAVING) ..................................................................................................................................... 122.3. Ordre de résolution des requête SQL .......................................................................................................................................... 122.4. Exercice : Re-représentation de représentants ............................................................................................................................. 13
II - Exercices 16
1. Exercice : Location d'appartements en groupe ............................................................................................. 16
2. Exercice : Championnat de Formule 1 ......................................................................................................... 16
3. Exercice : Questions scolaires ....................................................................................................................... 17
4. Quiz : SQL LMD .......................................................................................................................................... 19
III - Devoirs 21
1. Exercice : Gestion documentaire .................................................................................................................. 21
Questions de synthèse 24
Solutions des exercices 25
Abréviations 31
Cours
3
1.
2.
1. Introduction aux agrégats avec GROUP BYObjectifs
Savoir réaliser un agrégat en SQL en utilisant les fonctions de regroupement et les clause GROUP BY
1.1. Exercice : La somme finale
1.2. Définition de l'agrégation
Un agrégat est un partitionnement horizontal d'une table en sous-tables, en fonction des valeurs d'un ou plusieurs attributs de partitionnement, suivi éventuellement de l'application d'une fonction de calcul à chaque attribut des sous-tables obtenues.
Synonyme : Regroupement
1 SELECT liste d'attributs de partionnement à projeter et de fonctions de calcul2 FROM liste de relations3 WHERE condition à appliquer avant calcul de l'agrégat4 GROUP BY liste ordonnée d'attributs de partitionnement
La table est divisée en sous-ensembles de lignes, avec un sous-ensemble pour chaque valeur différente des attributs de partitionnement projetés dans le SELECTLes fonctions d'agrégation sont appliquées sur les attributs concernés
CoursI
Que renvoie la dernière instruction SQL de la séquence ci-dessous ?
1 CREATE TABLE R1 (a INTEGER, b INTEGER);2 CREATE TABLE R2 (b INTEGER);3 INSERT INTO R1 VALUES (1,1);4 INSERT INTO R1 VALUES (2,1);5 INSERT INTO R2 VALUES (1);6 INSERT INTO R2 VALUES (2);7 SELECT sum(R1.a) FROM R1, R2 WHERE R1.b=R2.b;
[ ]solution n°1 *[ ] p.25
Définition : Agrégat
Syntaxe
SELECT liste d'attributs de partionnement à projeter et de fonctions de calcul
FROM liste de relations
WHERE condition à appliquer avant calcul de l'agrégat
GROUP BY liste ordonnée d'attributs de partitionnement
CREATE TABLE R1 (a INTEGER, b INTEGER);
CREATE TABLE R2 (b INTEGER);
INSERT INTO R1 VALUES (1,1);
INSERT INTO R1 VALUES (2,1);
INSERT INTO R2 VALUES (1);
INSERT INTO R2 VALUES (2);
SELECT sum(R1.a) FROM R1, R2 WHERE R1.b=R2.b;
Définition de l'agrégation
4
1 Societe , AVG(Personne )
2 Personne, Societe
3 Personne = Societe
4 Societe
Societe.Nom est un ici le seul attribut de partitionnement, donc un sous ensemble est créé pour chaque valeur différente de , puis la moyenne (fonction AVG) est effectuée pour chaque sous-ensemble.Societe.Nom
Regroupement avec un attribut et une fonction
Cette requête calcul l'âge moyen du personnel pour chaque société.
1 Societe , Societe AVG(Personne )
2 Personne, Societe
3 Personne = Societe
4 Societe , Societe
Societe.Nom et sont les deux attributs de partitionnement, donc un sous-ensemble est créé pour Societe.Dptchaque valeur différente du couple ( , ).Societe.Nom Societe.Dpt
Regroupement avec deux attributs et une fonction
Cette requête calcul l'âge moyen du personnel pour chaque département de chaque société.
La requête est inutile dès lors que l'agrégat est défini sur une valeur unique dans la relation, puisqu'on aura bien une ligne par ligne de la table source.
1 Societe
2 Societe
3 Societe
Exemple
Exemple
Remarque : Requête inutile
Societe , AVG(Personne )SELECT .Nom .Age
Personne, SocieteFROM
Personne = SocieteWHERE .NomSoc .Nom
SocieteGROUP BY .Nom
Societe , Societe AVG(Personne )SELECT .Nom .Dpt .Age
Personne, SocieteFROM
Personne = SocieteWHERE .NomSoc .Nom
Societe , SocieteGROUP BY .Nom .Dpt
SocieteSELECT .Nom
SocieteFROM
SocieteGROUP BY .Nom
Exemple d'attribut d'agrégation
5
Exemple d'agrégat inutile (countrycode est une clé de country)
1.3. Exemple d'attribut d'agrégation
1 country(#countrycode:char(2), name:varchar, population:numeric) 2 city(#code:char(3), countrycode=>country, name:varchar, population:numeric):
1 country (
2 countrycode ( ) ,
3 name ,
4 population ( ),
5 PRIMARY KEY (countrycode)6 );78 city (
9 citycode ( ) ,
10 countrycode ( ) ,
11 name ,
12 population ( , ),
13 PRIMARY KEY (citycode),14 FOREIGN KEY (countrycode) REFERENCES country(countrycode)15 );
1 country ( , , );
2 country ( , , );
3 country ( , , );
45 city ( , , , );
6 city ( , , , );
7 city ( , , , );
89 city ( , , , );
10 city ( , , , );
11 city ( , , , );
12 city ( , , , );
Sélectionne les countrycode existants dans la table city
1 countrycode
2 city;
1 countrycode 2 -------------
Schéma relationnel
Schéma de base de données
Données
country(#countrycode:char(2), name:varchar, population:numeric)
city(#code:char(3), countrycode=>country, name:varchar, population:numeric):
country (CREATE TABLE
countrycode ( ) ,CHAR 2 NOT NULL
name , VARCHAR NOT NULL
population ( ),NUMERIC 3
PRIMARY KEY (countrycode)
);
city (CREATE TABLE
citycode ( ) ,CHAR 3 NOT NULL
countrycode ( ) ,CHAR 2 NOT NULL
name , VARCHAR NOT NULL
population ( , ),NUMERIC 2 1
PRIMARY KEY (citycode),
FOREIGN KEY (countrycode) REFERENCES country(countrycode)
);
country ( , , );INSERT INTO VALUES 'ES' 'Spain' 46
country ( , , );INSERT INTO VALUES 'FR' 'France' 67
country ( , , );INSERT INTO VALUES 'DE' 'Germany' 82
city ( , , , );INSERT INTO VALUES 'BAR' 'ES' 'Barcelona' 1.9
city ( , , , );INSERT INTO VALUES 'MAD' 'ES' 'Madrid' 3.3
city ( , , , );INSERT INTO VALUES 'ZAR' 'ES' 'Zaragoza' 0.7
city ( , , , );INSERT INTO VALUES 'PAR' 'FR' 'Paris' 2.2
city ( , , , );INSERT INTO VALUES 'LYO' 'FR' 'Paris' 0.5
city ( , , , );INSERT INTO VALUES 'LLL' 'FR' 'Lille' 0.2
city ( , , , );INSERT INTO VALUES 'AMN' 'FR' 'Amiens' 0.1
countrycode SELECT
city;FROM
countrycode
-------------
Fonctions d'agrégation
6
-
-
-
-
-
3 ES4 ES5 ES6 FR7 FR8 FR9 FR
Sélectionne les countrycode existants dans la table city avec agrégat
1 countrycode
2 city
3 countrycode;
1 countrycode 2 -------------3 FR4 ES
Principe de l'agrégation
1.4. Fonctions d'agrégation
Une fonction d'agrégation (ou fonction de regroupement) s'applique aux valeurs du sous-ensemble d'un agrégat e relation avec pour résultat la production d'une valeur atomique unique (entier, chaîne, date, etc).
Les cinq fonctions prédéfinies sont :
Count(Relation.Propriété)Renvoie le nombre de valeurs non nulles d'une propriété pour tous les tuples d'une relation ;Sum(Relation.Propriété)Renvoie la somme des valeurs d'une propriété des tuples (numériques) d'une relation ;Avg(Relation.Propriété)Renvoie la moyenne des valeurs d'une propriété des tuples (numériques) d'une relation ;Min(Relation.Propriété)Renvoie la plus petite valeur d'une propriété parmi les tuples d'une relation .Max(Relation.Propriété)Renvoie la plus grande valeur d'une propriété parmi les tuples d'une relation.
Définition : Fonctions d'agrégation
ES ES
ES
FR
FR
FR
FR
countrycode SELECT
cityFROM
countrycode; GROUP BY
countrycode
-------------
FR
ES
Exemple de fonctions d'agrégation
7
- -
Si une ou plusieurs fonctions de calcul sont appliquées sans partitionnement, le résultat de la requête est un tuple unique.
1 Min(Age), Max(Age), Avg(Age)
2 Personne
3 Qualification=
Pour effectuer un comptage sur tous les tuples d'une relation, appliquer la fonction à un attribut de la count
clé primaire. En effet cet attribut étant non nul par définition, il assure que tous les tuples seront comptés.
1.5. Exemple de fonctions d'agrégation
1 country(#countrycode:char(2), name:varchar, population:numeric) 2 city(#code:char(3), countrycode=>country, name:varchar, population:numeric):
Exemple d'attribut d'agrégation (cf. p.5)
1.5.1. Agrégat avec un attribut d'agrégation et une fonction d'agrégation
Sélectionne les et les existants dans la table .countrycode citycode city
1 countrycode, citycode
2 city;
1 countrycode | citycode 2 -------------+----------3 ES | BAR4 ES | MAD5 ES | ZAR6 FR | PAR7 FR | LYO8 FR | LLL9 FR | AMN
Sélectionne les et les existants dans la table ,countrycode citycode citypuis agrège par valeurs distinctes de .countrycode
1 countrycode, (citycode)
2 city
3 countrycode;
1 countrycode | count
Attention : Fonctions de calcul sans partitionnement
Exemple
Remarque : Comptage d'une relation
Rappel : Schéma relationnel
Requête sans agrégat
Requête avec agrégat
Min(Age), Max(Age), Avg(Age)SELECT
PersonneFROM
Qualification=WHERE 'Ingénieur'
country(#countrycode:char(2), name:varchar, population:numeric)
city(#code:char(3), countrycode=>country, name:varchar, population:numeric):
countrycode, citycode SELECT
city;FROM
countrycode | citycode
-------------+----------
ES | BAR
ES | MAD
ES | ZAR
FR | PAR
FR | LYO
FR | LLL
FR | AMN
countrycode, (citycode) SELECT count
cityFROM
countrycode;GROUP BY
countrycode | count
8
- - -
2 -------------+-------3 FR | 44 ES | 3
Regroupement avec fonction d'agrégation
1.5.2. Agrégat avec un attribut d'agrégation et deux fonctions d'agrégation
Sélectionne les , les et les existants dans la table .countrycode citycode populations city
1 countrycode, citycode, population
2 city;
1 countrycode | citycode | population 2 -------------+----------+------------3 ES | BAR | 1.94 ES | MAD | 3.35 ES | ZAR | 0.76 FR | PAR | 2.27 FR | LYO | 0.58 FR | LLL | 0.29 FR | AMN | 0.1
Sélectionne les , les et les existants dans la table ,countrycode citycode populations citypuis agrège par valeurs distinctes de countrycodepuis calcule les fonctions et .count sum
1 countrycode, (citycode), sum(population)
2 city
3 countrycode;
1 countrycode | count | sum 2 -------------+-------+-----3 FR | 4 | 3.04 ES | 3 | 5.9
Requête avec agrégat
-------------+------- FR | 4
ES | 3
countrycode, citycode, population SELECT
city;FROM
countrycode | citycode | population
-------------+----------+------------
ES | BAR | 1.9
ES | MAD | 3.3
ES | ZAR | 0.7
FR | PAR | 2.2
FR | LYO | 0.5
FR | LLL | 0.2
FR | AMN | 0.1
countrycode, (citycode), sum(population) SELECT count
cityFROM
countrycode;GROUP BY
countrycode | count | sum
-------------+-------+-----
FR | 4 | 3.0
ES | 3 | 5.9
9
- -
Regroupement avec deux fonctions d'agrégation
1.5.3. Agrégat sans attribut d'agrégation et avec une fonction d'agrégation
Sélectionne les existants dans la table .populations city
1 population
2 city;
1 population 2 ------------3 1.94 3.35 0.76 2.27 0.58 0.29 0.1
Sélectionne les existants dans la table ,populations citypuis calcule la fonction (pour , moyenne).avg average
1 avg(population)
2 city;
1 avg 2 --------------------3 1.2714285714285714
Regroupement avec fonction d'agrégation sans GROUP BY
Requête sans agrégat
Requête avec agrégat
population SELECT
city;FROM
population
------------
1.9
3.3
0.7
2.2
0.5
0.2
0.1
avg(population) SELECT
city;FROM
avg
--------------------
1.2714285714285714
Single-Value Rule
10
- -
2. Approfondissement des agrégats avec GROUP BY et HAVINGObjectifs
Savoir réaliser un agrégat en SQL en utilisant les fonctions de regroupement et les clause GROUP BY et HAVING
2.1. Single-Value Rule
Toute colonne de la clause GROUP BY doit désigner une colonne présente dans la clause SELECT :
soit comme attribut d'agrégation,soit comme attribut présent dans une fonction d'agrégation.
1 countrycode, citycode, (citycode)
2 city
3 countrycode;
1 ERROR: column "city.citycode" must appear in the GROUP BY clause or be used in an aggregate function;
Tentative de GROUP BY illégal
La requête est non standard et non logique car on cherche à mettre plusieurs données dans la case citycodeaprès le GROUP BY (il y a plusieurs par ).citycode countrycode
Elle sera refusée par tous les SGBD.
https://mariadb.com/kb/en/sql-99/the-single-value-rule
2.1.1. Tolérance (de certains SGBD)
1 citycode, countrycode, AVG(population)
2 city
3 citycode;
Fondamental : Principe de la "Single-Value Rule" établie par le standard SQL
Attention : Requête illégale
Complément : Single-Value Rule
Requête non standard tolérée par certains SGBD
countrycode, citycode, (citycode)SELECT COUNT
cityFROM
countrycode;GROUP BY
ERROR: column "city.citycode" must appear in the GROUP BY clause or be used in
an aggregate function;
citycode, countrycode, AVG(population) SELECT
cityFROM
citycode;GROUP BY
11
1 citycode | countrycode | avg 2 ----------+-------------+------------------------3 LLL | FR | 0.200000000000000000004 BAR | ES | 1.900000000000000000005 PAR | FR | 2.20000000000000006 LYO | FR | 0.500000000000000000007 ZAR | ES | 0.700000000000000000008 AMN | FR | 0.100000000000000000009 MAD | ES | 3.3000000000000000
La requête est non standard, même si c'est logiquement acceptable, car il n'y a qu'un par countrycode citycodeici, étant une clé.city
Certains SGBD comme Postgres acceptent donc cette syntaxe, en ajoutant implicitement l'attribut qui manque
au GROUP BY, car il n'y a pas d’ambiguïté si l'on analyse le graphe des : → . Mais le DF* citycode countrycodestandard impose d'être explicite, car le SGBD a le droit de ne pas le deviner.
2.1.2. Tolérance partielle
1 ci , co , (*)
2 city ci country co
3 ci =co
4 ci , co ;
1 countrycode | name | count 2 -------------+--------+-------3 FR | France | 44 ES | Spain | 3
1 co , co , (*)
2 city ci country co
3 ci =co
4 co ;
La requête est non standard, car est dans le SELECT mais pas dans le GROUP BY. Comme dans co.namel'exemple précédent certains SGBD accepteront cette requête grâce à la DF évidente : → co.countrycode co.name
Mais si l'on utilise à présent à la place de ...ci.countrycode co.countrycode
1 ci , co , (*)
2 city ci country co
3 ci =co
4 ci ;
1 ERROR: column "co.name" must appear in the GROUP BY clause or be used in an aggregate function
Requête légale
Requête non standard tolérée par certains SGBD
Attention : Requête non standard non tolérée
citycode | countrycode | avg ----------+-------------+------------------------
LLL | FR | 0.20000000000000000000
BAR | ES | 1.90000000000000000000
PAR | FR | 2.2000000000000000
LYO | FR | 0.50000000000000000000
ZAR | ES | 0.70000000000000000000
AMN | FR | 0.10000000000000000000
MAD | ES | 3.3000000000000000
ci , co , (*) SELECT .countrycode .name count
city ci country co FROM JOIN
ci =co ON .countrycode .countrycode
ci , co ;GROUP BY .countrycode .name
countrycode | name | count
-------------+--------+-------
FR | France | 4
ES | Spain | 3
co , co , (*) SELECT .countrycode .name count
city ci country co FROM JOIN
ci =co ON .countrycode .countrycode
co ;GROUP BY .countrycode
ci , co , (*) SELECT .countrycode .name count
city ci country co FROM JOIN
ci =co ON .countrycode .countrycode
ci ;GROUP BY .countrycode
ERROR: column "co.name" must appear in the GROUP BY clause or be used in an
aggregate function
Restriction après agrégation (HAVING)
12
1. 2. 3. 4. 5. 6.
La requête est non standard, doit être mentionné dans la clause GROUP BY. Logiquement on pourrait co.namejuger cela superflu, car par transitivité → , mais le SGBD (ici Postgres) ne va pas ci.countrycode co.namejusque là.
Il est donc conseillé d'appliquer le et de toujours expliciter tous les attributs dans le GROUP Single-Value RuleBY, pour éviter la dépendance au SGBD ou bien les erreurs liées à des variations mineures de syntaxe.
2.2. Restriction après agrégation (HAVING)
La clause HAVING permet d'effectuer une second restriction après l'opération d'agrégation.
1 liste d
23 agrégat
4 liste ordonnée d
5
1 Societe , AVG(Personne )
2 Personne, Societe
3 Personne = Societe
4 Societe
5 (Personne ) >
Cette requête calcul l'âge moyen du personnel pour chaque société comportant plus de 2 salariés.
2.3. Ordre de résolution des requête SQL
L'ordre de résolution standard d'une requête SQL est :
FROMWHEREGROUP BYHAVINGSELECTORDER BY
1 substring(name, , ) initiale, (citycode)
2 city
Conseil
Définition
Syntaxe
Exemple
Fondamental
Attention : Usage des alias dans le GROUP BY ou le HAVING : requête non standard, acceptée par certains SGBD
liste dSELECT 'attributs de partionnement à projeter et de fonctions de calcul
FROM liste de relations
agrégatWHERE condition à appliquer avant calcul de l'
liste ordonnée dGROUP BY 'attributs de partitionnement
HAVING condition sur les fonctions de calcul
Societe , AVG(Personne )SELECT .Nom .Age
Personne, SocieteFROM
Personne = SocieteWHERE .NomSoc .Nom
SocieteGROUP BY .Nom
(Personne ) > HAVING COUNT .NumSS 2
substring(name, , ) initiale, (citycode)SELECT 1 1 AS count
cityFROM
Exercice : Re-représentation de représentants
13
3 initiale;
1 initiale | count 2 ----------+-------3 L | 14 Z | 15 B | 16 M | 17 P | 28 A | 19
La requête n'est pas standard, les résolutions de l'alias et de la fonction sont dans le SELECT, qui est substringpostérieur à la résolution du GROUP BY. Postgres acceptera néanmoins cette syntaxe, ce qui évite de créer une vue ou d'utiliser une sous-requête.
Une restriction peut être appliquée avant calcul de l'agrégat, au niveau de la clause WHERE, portant ainsi sur la relation de départ, mais aussi après calcul de l'agrégat sur les résultats de ce dernier, au niveau de la clause HAVING.
Les fonctions d'agrégation peuvent être utilisées dans la claude HAVING ou dans la clause ORDER BY. Les fonctions être utilisées dans la clause WHERE (qui est résolu avant le GROUP BY).ne peuvent pas
2.4. Exercice : Re-représentation de représentants[30 minutes]
Soit le schéma relationnel suivant :
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)
Écrire en SQL les requêtes permettant d'obtenir les informations suivantes.
1données avec le script SQL suivant */
23456789
10 REPRESENTANTS (
11 NR PRIMARY KEY,
12 NOMR ,
13 VILLE 14 );1516 PRODUITS (
Remarque : Restriction
Remarque : Utilisation de fonctions d'agrégation
initiale;GROUP BY
initiale | count
----------+-------
L | 1
Z | 1
B | 1
M | 1
P | 2
A | 1
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 (CREATE TABLE
NR PRIMARY KEY,INTEGER
NOMR ,VARCHAR
VILLE VARCHAR
);
PRODUITS (CREATE TABLE
Exercice : Re-représentation de représentants
14
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) ( , , ,
);44 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) ( , , , );
Question 1
Question 2
Le nombre de clients.[ ]solution n°2 *[ ] p.25
Le nombre de produits de couleur rouge.[ ]solution n°3 *[ ] p.25
NP PRIMARY KEY,INTEGER
NOMP ,VARCHAR
COUL ,VARCHAR
PDS INTEGER
);
CLIENTS (CREATE TABLE
NC PRIMARY KEY,INTEGER
NOMC ,VARCHAR
VILLE VARCHAR
);
VENTES (CREATE TABLE
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) ( , , );INSERT INTO VALUES 1 'Stephane' 'Lyon'
REPRESENTANTS (NR, NOMR, VILLE) ( , , );INSERT INTO VALUES 2 'Benjamin' 'Paris'
REPRESENTANTS (NR, NOMR, VILLE) ( , , );INSERT INTO VALUES 3 'Leonard' 'Lyon'
REPRESENTANTS (NR, NOMR, VILLE) ( , , );INSERT INTO VALUES 4 'Antoine' 'Brest'
REPRESENTANTS (NR, NOMR, VILLE) ( , , );INSERT INTO VALUES 5 'Bruno' 'Bayonne'
PRODUITS (NP, NOMP, COUL, PDS) ( , , , INSERT INTO VALUES 1 'Aspirateur' 'Rouge' 3546
); PRODUITS (NP, NOMP, COUL, PDS) ( , , , INSERT INTO VALUES 2 'Trottinette' 'Bleu' 1423
); PRODUITS (NP, NOMP, COUL, PDS) ( , , , );INSERT INTO VALUES 3 'Chaise' 'Blanc' 3827
PRODUITS (NP, NOMP, COUL, PDS) ( , , , );INSERT INTO VALUES 4 'Tapis' 'Rouge' 1423
CLIENTS (NC, NOMC, VILLE) ( , , );INSERT INTO VALUES 1 'Alice' 'Lyon'
CLIENTS (NC, NOMC, VILLE) ( , , );INSERT INTO VALUES 2 'Bruno' 'Lyon'
CLIENTS (NC, NOMC, VILLE) ( , , );INSERT INTO VALUES 3 'Charles' 'Compiègne'
CLIENTS (NC, NOMC, VILLE) ( , , );INSERT INTO VALUES 4 'Denis' 'Montpellier'
CLIENTS (NC, NOMC, VILLE) ( , , );INSERT INTO VALUES 5 'Emile' 'Strasbourg'
VENTES (NR, NP, NC, QT) ( , , , );INSERT INTO VALUES 1 1 1 1
VENTES (NR, NP, NC, QT) ( , , , );INSERT INTO VALUES 1 1 2 1
VENTES (NR, NP, NC, QT) ( , , , );INSERT INTO VALUES 2 2 3 1
VENTES (NR, NP, NC, QT) ( , , , );INSERT INTO VALUES 4 3 3 200
VENTES (NR, NP, NC, QT) ( , , , );INSERT INTO VALUES 3 4 2 190
VENTES (NR, NP, NC, QT) ( , , , );INSERT INTO VALUES 1 3 2 300
VENTES (NR, NP, NC, QT) ( , , , );INSERT INTO VALUES 3 1 2 120
VENTES (NR, NP, NC, QT) ( , , , );INSERT INTO VALUES 3 1 4 120
VENTES (NR, NP, NC, QT) ( , , , );INSERT INTO VALUES 3 4 4 2
VENTES (NR, NP, NC, QT) ( , , , );INSERT INTO VALUES 3 1 1 3
VENTES (NR, NP, NC, QT) ( , , , );INSERT INTO VALUES 3 4 1 5
VENTES (NR, NP, NC, QT) ( , , , );INSERT INTO VALUES 3 1 3 1
Exercice : Re-représentation de représentants
15
Question 3
Question 4
Question 5
Le nombre de clients par ville.[ ]solution n°4 *[ ] p.25
La quantité totale des produits rouge vendus par chaque représentant.[ ]solution n°5 *[ ] p.25
La quantité totale de produits rouges vendus pour chaque représentant ayant vendu plus de 5 fois des produits rouges (ayant réalisé plus de 5 ventes différentes de produits rouges).
[ ]solution n°6 *[ ] p.26
Exercices
16
1. Exercice : Location d'appartements en groupe[20 min]
Soit le schéma relationnel suivant gérant le fonctionnement d'une agence de location d'appartements.
1 APPARTEMENT(#code_appt:String, adresse:String, type:{studio,F1,F2,F3,F4,F5+}, prix_loyer:Real)
2 LOCATAIRE(#code_loc:String, nom:String, prenom:String)3 LOCATION(#code_loc=>Locataire, #code_appt=>Appartement)4 PAIEMENT_LOYER(#code_loc=>Locataire, #code_appt=>Appartement, #date_payement:Date, prix_paye:Real)
Question 1
Question 2
Question 3
2. Exercice : Championnat de Formule 1[20 min]
La base de données suivante permet de gérer les résultats des courses de Formule 1 dans un championnat.
1 CHAMPIONNAT(#nom:string, annee:integer)2 CIRCUIT(#nom:string, ville:string)3 COURSE(#nom:string,circuit=>CIRCUIT(nom), championnat=>CHAMPIONNAT(nom))4 SPONSOR(#nom:string)5 ECURIE(#nom:string, devise:string, couleur:string, sponsor=>SPONSOR(nom))6 PILOTE(#id:integer, nom:string, prenom:string, ecurie=>ECURIE(nom))7 TOUR(#pilote => PILOTE(id), #course => COURSE(nom), #num:integer, duree:integer)
Question 1
ExercicesII
En SQL afficher le nombre d'appartements de chaque type, uniquement pour les types qui commencent par la lettre F.
[ ]solution n°7 *[ ] p.26
En SQL afficher le total payé par locataire (avec son code, nom et prenom) pour l'ensemble de ses appartements.
[ ]solution n°8 *[ ] p.26
En SQL afficher les locataires (code uniquement) qui louent au moins 2 appartements, en précisant le nombre d'appartements loués et la moyenne des loyers, et trié par ordre décroissant de cette moyenne.
[ ]solution n°9 *[ ] p.26
En algèbre relationnel et en SQL, afficher la liste de tous les pilotes dont le nom commence par la lettre 'D'.[ ]solution n°10 *[ ] p.26
APPARTEMENT(#code_appt:String, adresse:String, type:{studio,F1,F2,F3,F4,F5+},
prix_loyer:Real)LOCATAIRE(#code_loc:String, nom:String, prenom:String)
LOCATION(#code_loc=>Locataire, #code_appt=>Appartement)
PAIEMENT_LOYER(#code_loc=>Locataire, #code_appt=>Appartement, #date_payement:
Date, prix_paye:Real)
CHAMPIONNAT(#nom:string, annee:integer)
CIRCUIT(#nom:string, ville:string)
COURSE(#nom:string,circuit=>CIRCUIT(nom), championnat=>CHAMPIONNAT(nom))
SPONSOR(#nom:string)
ECURIE(#nom:string, devise:string, couleur:string, sponsor=>SPONSOR(nom))
PILOTE(#id:integer, nom:string, prenom:string, ecurie=>ECURIE(nom))
TOUR(#pilote => PILOTE(id), #course => COURSE(nom), #num:integer, duree:integer)
Exercice : Questions scolaires
17
Question 2
Question 3
Question 4
3. Exercice : Questions scolaires[30 min]
Soit le schéma relationnel suivant gérant le fonctionnement d'une école et les notes des élèves.
1 CLASSE(#intitule)2 MATIERE(#intitule)3 ELEVE(#login, nom, prenom, age, classe=>CLASSE)4 ENSEIGNANT(#login, nom, prenom, mail, matiere=>MATIERE)5 ENSEIGNE(#enseignant=> ENSEIGNANT, #classe=>CLASSE)6 NOTE(#eleve=>ELEVE, #enseignant=>ENSEIGNANT, #date_examen, note)78 Contrainte : un enseignant ne peut mettre une note à un élève que si celui-ci se trouve dans une classe dans laquelle il enseigne.
NB : Nous n'utiliserons pas de sous-requêtes.
Question 1
Question 2
Question 3
Question 4
En SQL, afficher le nombre de pilotes par écurie en classant les résultats par ordre alphabétique des noms des écuries.
[ ]solution n°11 *[ ] p.26
En algèbre relationnel et en SQL, afficher les noms des sponsors qui ne sont pas liés à une écurie.[ ]solution n°12 *[ ] p.27
En SQL, afficher le numéro, nom, prénom et écurie, avec leur temps moyen par tour, des pilotes participant à la course ; mais en ne conservant que les pilotes qui ont effectués au moins deux tours de Daytonutc 500
piste, et en classant le résultat par temps moyen décroissant.
[ ]solution n°13 *[ ] p.27
En algèbre relationnel en SQL, afficher la liste des tous les étudiants dont le nom commence par A.et
[ ]solution n°14 *[ ] p.27
En algèbre relationnel en SQL, afficher les noms et prénoms des enseignants qui n'enseignent à aucune etclasse.
[ ]solution n°15 *[ ] p.27
En SQL, affichez le nombre d'étudiants enregistrés en "Terminale S 2".[ ]solution n°16 *[ ] p.27
En SQL, affichez les logins, noms, prénoms, classes et moyennes des élèves en cours de "Mathématiques", par ordre décroissant de moyenne, à condition qu'ils aient au minimum 2 notes dans cette matière.
[ ]solution n°17 *[ ] p.28
CLASSE(#intitule)
MATIERE(#intitule)
ELEVE(#login, nom, prenom, age, classe=>CLASSE)
ENSEIGNANT(#login, nom, prenom, mail, matiere=>MATIERE)
ENSEIGNE(#enseignant=> ENSEIGNANT, #classe=>CLASSE)
NOTE(#eleve=>ELEVE, #enseignant=>ENSEIGNANT, #date_examen, note)
Contrainte : un enseignant ne peut mettre une note à un élève que si celui-ci se
trouve dans une classe dans laquelle il enseigne.
Quiz : SQL LMD
18
Question 5
En SQL, à des fins de statistiques, nous souhaitons rechercher par enseignant et par classe, les classes qui n'ont pas la moyenne générale, et afficher pour celles-ci : le nom, prénom et mail de l'enseignant en question, la matière enseignée, la classe, la moyenne d'âge des étudiants avec les extrêmes (minimum et maximum), la moyenne générale de la classe avec les valeurs extrêmes, ainsi que le nombre d'étudiants présents dans cette classe ; le tout classé par ordre alphabétique de classe, puis de nom et de prénom de l'enseignant.
Indice :
On fera l'hypothèse que tous les étudiants d'une classe ont le même nombre de notes (pas d'absence aux examens).
[ ]solution n°18 *[ ] p.28
Quiz : SQL LMD
19
4. Quiz : SQL LMDExercice
Exercice
Parmi les assertions suivantes concernant le langage SQL, sélectionner celles qui sont correctes.
Le langage SQL permet d'implémenter physiquement un modèle logique exprimé en relationnel.
Le langage SQL permet d'entrer et de sortir des données d'une base de données.
Le langage SQL permet de donner et d'enlever des droits en lecture sur les données d'une base de données.
Le langage SQL permet de donner et d'enlever des droits en écriture sur les données d'une base de données.
Le langage SQL permet de créer une interface graphique utilisable par les utilisateurs finaux.
Le langage SQL permet de faire des traitements algorithmiques complexes.
Le langage SQL permet en général de créer une application informatique complète.
[ ]solution n°19 *[ ] p.28
Soit les deux relations suivantes UV1 et UV2 représentant respectivement les places disponibles dans les cours de l'UTC et les inscriptions effectives pour les cours ouverts ce semestre :
Relations UV1 et UV2
Compléter la requête SQL suivante pour qu'elle retourne pour les cours avec leur nom, plus le nombre tousd'inscrits en excès ou en défaut pour les cours ouverts et NULL pour les cours fermés.
SELECT UV1.Nom,
FROM UV1 UV2
ON
[ ]solution n°20 *[ ] p.28
Quiz : SQL LMD
20
Exercice
Exercice
Exercice
Soit les deux relations suivantes UV1 et UV2 représentant respectivement les places disponibles dans les cours de l'UTC et les inscriptions effectives pour les cours ouverts ce semestre :
Relations UV1 et UV2
Compléter la requête SQL suivante pour qu'elle retourne, pour les cours ouverts uniquement, le nombre d'inscrits moyen selon le nombre de places (nombre d'inscrits moyens pour les cours de 168 places, pour ceux de 48 places, etc.)
SELECT UV1.Nombre,
FROM UV1 UV2
ON
[ ]solution n°21 *[ ] p.29
Quelle sont les requêtes équivalente à la requête ci-dessous :
1 SELECT Nom2 FROM Personne3 WHERE NOT(Nom IS NULL)4 GROUP BY Nom
SELECT DISTINCT NomFROM PersonneWHERE Nom LIKE '%'
SELECT NomFROM Personne
SELECT NomFROM PersonneGROUP BY NomHAVING Count(Nom) > 0
SELECT NomFROM PersonneWHERE NOT(Nom IS NULL)
[ ]solution n°22 *[ ] p.29
Que renvoie la dernière instruction SQL de la séquence ci-dessous ?
1 CREATE TABLE R (a INTEGER, b INTEGER);2 INSERT INTO R VALUES (1,1);3 INSERT INTO R VALUES (1,2);4 INSERT INTO R VALUES (3,3);5 SELECT sum(R1.b) FROM R R1, R R2 WHERE R1.a=R2.a;
[ ]solution n°23 *[ ] p.30
SELECT Nom
FROM Personne
WHERE NOT(Nom IS NULL)
GROUP BY Nom
CREATE TABLE R (a INTEGER, b INTEGER);
INSERT INTO R VALUES (1,1);
INSERT INTO R VALUES (1,2);
INSERT INTO R VALUES (3,3);
SELECT sum(R1.b) FROM R R1, R R2 WHERE R1.a=R2.a;
Devoirs
21
1. Exercice : Gestion documentaire[30 min]
Soit le schéma relationnel suivant permettant une gestion électronique de documents (GED) :
1 Users (#login, pwd, name)2 Documents (#id, title, creator=>Users(login))3 Versions (#num, #doc=>Documents(id), contrib=>Users(login))
NB : L'on utilisera ni les , ni les .opérateurs ensemblistes sous-requêtes
Vous pouvez tester vos requêtes directement sur votre base de données en l'initialisant avec le fichier de données.
1234567 Users (
8 login PRIMARY KEY,
9 pwd ,
10 name 11 ) ;1213 Documents (
14 id PRIMARY KEY,
15 title ,
16 creator REFERENCES Users(login)
17 ) ;1819 Versions (
20 num PRIMARY KEY,
21 doc REFERENCES Documents(id),
22 contrib REFERENCES Users(login)
23 ) ;2425 Users (login, pwd, name) ( , , ) ;
26 Users (login, pwd, name) ( , , ) ;
27 Users (login, pwd, name) ( , , ) ;
28 Users (login, pwd, name) ( , , ) ;
2930 Documents (id, title, creator) ( , , ) ;
31 Documents (id, title, creator) ( ,
, ) ;relativité restreinte et générale' 'al'
DevoirsIII
Users (#login, pwd, name)
Documents (#id, title, creator=>Users(login))
Versions (#num, #doc=>Documents(id), contrib=>Users(login))
/**
DROP TABLE Versions ;
DROP TABLE Documents ;
DROP TABLE Users ;
**/
Users (CREATE TABLE
login PRIMARY KEY,VARCHAR
pwd ,VARCHAR
name VARCHAR
) ;
Documents (CREATE TABLE
id PRIMARY KEY,INTEGER
title ,VARCHAR
creator REFERENCES Users(login)VARCHAR
) ;
Versions (CREATE TABLE
num PRIMARY KEY,INTEGER
doc REFERENCES Documents(id),INTEGER
contrib REFERENCES Users(login)VARCHAR
) ;
Users (login, pwd, name) ( , , ) ;INSERT INTO VALUES 'karl' 'marx' 'Karl Marx'
Users (login, pwd, name) ( , , ) ;INSERT INTO VALUES 'al' 'ecm2' 'Albert Einstein'
Users (login, pwd, name) ( , , ) ;INSERT INTO VALUES 'stc' 'nf17' 'Stéphane Crozat'
Users (login, pwd, name) ( , , ) ;INSERT INTO VALUES 'paul' 'mot' 'Paul Pagnol'
Documents (id, title, creator) ( , , ) ;INSERT INTO VALUES 1 'Le Capital' 'karl'
Documents (id, title, creator) ( , INSERT INTO VALUES 2 'La théorie de la
, ) ;relativité restreinte et générale' 'al'
Exercice : Gestion documentaire
22
32 Documents (id, title, creator) ( , ,
) ;'stc'3334 Versions (num, doc, contrib) ( , , ) ;
35 Versions (num, doc, contrib) ( , , ) ;
36 Versions (num, doc, contrib) ( , , ) ;
37 Versions (num, doc, contrib) ( , , ) ;
38 Versions (num, doc, contrib) ( , , ) ;
39 Versions (num, doc, contrib) ( , , ) ;
Question 1
Question 2
Question 3
Question 4
En et en , afficher le nom de l'auteur du document dont le titre est "Le Capital".algèbre relationnel SQL
name
Karl Marx
Résultat
En et en , afficher les logins des users qui sont à la fois creator et contrib et qui algèbre relationnel SQLcommencent par "k".
login
karl
...
Résultat
En et en , Afficher les logins des users qui n'ont ni créé, ni contribué.algèbre relationnel SQL
login
paul
...
Résultat
En , afficher la dernière version (celle qui a le numéro le plus élevé) de chaque document, la liste sera SQLtriée par id des documents croissants.
id last_version
001 2
Documents (id, title, creator) ( , , INSERT INTO VALUES 3 'Les bases de données'
) ;'stc'
Versions (num, doc, contrib) ( , , ) ;INSERT INTO VALUES 1 1 'karl'
Versions (num, doc, contrib) ( , , ) ;INSERT INTO VALUES 2 1 'karl'
Versions (num, doc, contrib) ( , , ) ;INSERT INTO VALUES 3 1 'karl'
Versions (num, doc, contrib) ( , , ) ;INSERT INTO VALUES 4 2 'al'
Versions (num, doc, contrib) ( , , ) ;INSERT INTO VALUES 5 2 'stc'
Versions (num, doc, contrib) ( , , ) ;INSERT INTO VALUES 6 3 'stc'
Exercice : Gestion documentaire
23
Question 5
... ...
Résultat
En , afficher le nombre de contributions pour chaque utilisateur, en face de leur nom, à condition que SQLl'utilisateur ait contribué au moins 2 fois, la liste sera triée par nombre de contributions décroissant.
name nb_contributions
Karl Marx 14
... ...
Résultat
Questions de synthèse
24Stéphane Crozat
A quoi sert le LMD ?
.........................................................................................................................................................................................................................................................
.........................................................................................................................................................................................................................................................
.........................................................................................................................................................................................................................................................
.........................................................................................................................................................................................................................................................
.........................................................................................................................................................................................................................................................
.........................................................................................................................................................................................................................................................
.........................................................................................................................................................................................................................................................
Quel rapport y-a-t il entre le SQL et l'algèbre relationnelle ?
.........................................................................................................................................................................................................................................................
.........................................................................................................................................................................................................................................................
.........................................................................................................................................................................................................................................................
.........................................................................................................................................................................................................................................................
.........................................................................................................................................................................................................................................................
.........................................................................................................................................................................................................................................................
.........................................................................................................................................................................................................................................................
Pourquoi SQL n'est-il pas un langage de programmation ?
.........................................................................................................................................................................................................................................................
.........................................................................................................................................................................................................................................................
.........................................................................................................................................................................................................................................................
.........................................................................................................................................................................................................................................................
.........................................................................................................................................................................................................................................................
.........................................................................................................................................................................................................................................................
.........................................................................................................................................................................................................................................................
Questions de synthèse
Solutions des exercices
25Stéphane Crozat
Exercice p. 15> Solution n°5
Exercice p. 15> Solution n°4
Exercice p. 14> Solution n°3
Exercice p. 14> Solution n°2
Exercice p. 3> Solution n°1
Que renvoie la dernière instruction SQL de la séquence ci-dessous ?
1 CREATE TABLE R1 (a INTEGER, b INTEGER);2 CREATE TABLE R2 (b INTEGER);3 INSERT INTO R1 VALUES (1,1);4 INSERT INTO R1 VALUES (2,1);5 INSERT INTO R2 VALUES (1);6 INSERT INTO R2 VALUES (2);7 SELECT sum(R1.a) FROM R1, R2 WHERE R1.b=R2.b;
3
1 (*)
2 CLIENTS ;
ou
1 (NC)
2 CLIENTS ;
1 (*)
2 PRODUITS P
3 P = ;
1 VILLE, (*)
2 CLIENTS
3 VILLE ;
1 V , SUM(V )
2 VENTES V, PRODUITS P
3 P =V
4 P =
Solutions des exercices
CREATE TABLE R1 (a INTEGER, b INTEGER);
CREATE TABLE R2 (b INTEGER);
INSERT INTO R1 VALUES (1,1);
INSERT INTO R1 VALUES (2,1);
INSERT INTO R2 VALUES (1);
INSERT INTO R2 VALUES (2);
SELECT sum(R1.a) FROM R1, R2 WHERE R1.b=R2.b;
(*)SELECT COUNT
CLIENTS ;FROM
(NC)SELECT COUNT
CLIENTS ;FROM
(*)SELECT COUNT
PRODUITS PFROM
P = ;WHERE .COUL 'Rouge'
VILLE, (*)SELECT COUNT
CLIENTSFROM
VILLE ;GROUP BY
V , SUM(V )SELECT .NR .QT
VENTES V, PRODUITS PFROM
P =V WHERE .NP .NP
P =AND .COUL 'Rouge'
Solutions des exercices
26Stéphane Crozat
Exercice p. 16> Solution n°10
Exercice p. 16> Solution n°9
Exercice p. 16> Solution n°8
Exercice p. 16> Solution n°7
Exercice p. 15> Solution n°6
5 V ;
1 V , SUM(V )
2 VENTES V, PRODUITS P
3 V =P
4 P =
5 V
6 (*)> ;
1 (*)
2 appartement
3 type
4 type
5
1 L , L , L , SUM(P )
2 locataire L, paiement_loyer P
3 L =P
4 L , L , L
nom et sont obligatoires dans le : cf .prenom group by Single-Value Rule (cf. p.10)
1 L , (A ), AVG(A )
2 location L appartement A
3 A =L
4 L
5 (A )>
6 AVG(A )
1 R1 = Restriction(PILOTE, nom COMME 'D%')2 R = Projection(R1, nom, prenom)
1 SELECT nom, prenom2 FROM PILOTE P3 WHERE P.nom LIKE 'D%' ;
Attention
V ;GROUP BY .NR
V , SUM(V )SELECT .NR .QT
VENTES V, PRODUITS PFROM
V =P WHERE .NP .NP
P = AND .COUL 'Rouge'
V GROUP BY .NR
(*)> ;HAVING COUNT 5
(*) SELECT COUNT
appartementFROM
type WHERE LIKE 'F%'
typeGROUP BY
L , L , L , SUM(P )SELECT .code_loc .nom .prenom .prix_paye
locataire L, paiement_loyer PFrom
L =PWHERE .code_loc .code_loc
L , L , LGROUP BY .code_loc .nom .prenom
L , (A ), AVG(A )SELECT .code_loc COUNT .code_appt .prix_loyer
location L appartement AFROM JOIN
A =LON .code_appt .code_appt
LGROUP BY .code_loc
(A )>HAVING COUNT .code_appt 1
AVG(A ) ORDER BY .prix_loyer DESC
R1 = Restriction(PILOTE, nom COMME 'D%')
R = Projection(R1, nom, prenom)
SELECT nom, prenom
FROM PILOTE P
WHERE P.nom LIKE 'D%' ;
Solutions des exercices
27Stéphane Crozat
Exercice p. 17> Solution n°16
Exercice p. 17> Solution n°15
Exercice p. 17> Solution n°14
Exercice p. 17> Solution n°13
Exercice p. 17> Solution n°12
Exercice p. 17> Solution n°11
1 SELECT P.ecurie, COUNT(P.nom) AS nb_pilotes2 FROM PILOTE P3 GROUP BY P.ecurie4 ORDER BY P.ecurie ;
1 R1 = JointureExterneGauche(SPONSOR, ECURIE, SPONSOR.nom = ECURIE.sponsor)2 R2 = Restriction(R1, ECURIE.nom = NULL)3 R = Projection(R2, SPONSOR.nom)
1 SELECT S.nom2 FROM SPONSOR S LEFT JOIN ECURIE E ON S.nom = E.sponsor3 WHERE E.nom IS NULL ;
1 SELECT T.pilote, P.nom, P.prenom, P.ecurie, AVG(T.duree) AS moyenne2 FROM TOUR T, PILOTE P3 WHERE T.pilote = P.id4 AND T.course = 'Daytonutc 500'5 GROUP BY T.pilote, P.nom, P.prenom, P.ecurie6 HAVING COUNT(T.num) > 17 ORDER BY moyenne DESC ;
1 R1 = Restriction(ELEVE, nom COMME 'A%')2 R = Projection(R1, nom, prenom)
1 SELECT nom, prenom2 FROM ELEVE3 WHERE nom LIKE 'A%';
1 R1 = JointureExterneGauche(ENSEIGNANT, ENSEIGNE, login=enseignant)2 R2 = Restriction(R1, classe = NULL)3 R = Projection(R2, nom, prenom)
1 SELECT nom, prenom2 FROM ENSEIGNANT LEFT JOIN ENSEIGNE on login=enseignant3 WHERE classe IS NULL;
1 SELECT classe, COUNT(login)
SELECT P.ecurie, COUNT(P.nom) AS nb_pilotes
FROM PILOTE P
GROUP BY P.ecurie
ORDER BY P.ecurie ;
R1 = JointureExterneGauche(SPONSOR, ECURIE, SPONSOR.nom = ECURIE.sponsor)
R2 = Restriction(R1, ECURIE.nom = NULL)
R = Projection(R2, SPONSOR.nom)
SELECT S.nom
FROM SPONSOR S LEFT JOIN ECURIE E ON S.nom = E.sponsor
WHERE E.nom IS NULL ;
SELECT T.pilote, P.nom, P.prenom, P.ecurie, AVG(T.duree) AS moyenne
FROM TOUR T, PILOTE P
WHERE T.pilote = P.id
AND T.course = 'Daytonutc 500'
GROUP BY T.pilote, P.nom, P.prenom, P.ecurie
HAVING COUNT(T.num) > 1
ORDER BY moyenne DESC ;
R1 = Restriction(ELEVE, nom COMME 'A%')
R = Projection(R1, nom, prenom)
SELECT nom, prenom
FROM ELEVE
WHERE nom LIKE 'A%';
R1 = JointureExterneGauche(ENSEIGNANT, ENSEIGNE, login=enseignant)
R2 = Restriction(R1, classe = NULL)
R = Projection(R2, nom, prenom)
SELECT nom, prenom
FROM ENSEIGNANT LEFT JOIN ENSEIGNE on login=enseignant
WHERE classe IS NULL;
SELECT classe, COUNT(login)
Solutions des exercices
28Stéphane Crozat
Exercice p. 19> Solution n°20
Exercice p. 19> Solution n°19
Exercice p. 18> Solution n°18
Exercice p. 17> Solution n°17
2 FROM ELEVE3 WHERE classe = 'Terminale S 2' ;
1 SELECT EL.login, EL.nom, EL.prenom, EL.classe, AVG(note) AS moyenne2 FROM ELEVE EL, NOTE, ENSEIGNANT EN3 WHERE EL.login = eleve4 AND enseignant = EN.login5 AND matiere = 'Mathématiques'6 GROUP BY EL.login, EL.nom, EL.prenom, EL.classe7 HAVING COUNT(note) > 18 ORDER BY moyenne DESC;
1 SELECT EL.classe, ENA.nom, ENA.prenom, ENA.mail, ENA.matiere, AVG(EL.age), MIN(EL.age), MAX(EL.age), COUNT(distinct EL.login), MIN(note), MAX(note), AVG(note)
2 FROM ELEVE EL, NOTE N, ENSEIGNE EN, ENSEIGNANT ENA3 WHERE EL.login = N.eleve4 AND EL.classe = EN.classe5 AND EN.enseignant = ENA.login6 AND N.enseignant = ENA.login7 GROUP BY ENA.login, ENA.nom, ENA.prenom, ENA.mail, EL.classe, ENA.matiere8 HAVING AVG(note) <= 109 ORDER BY EL.classe, ENA.nom, ENA.prenom;
Parmi les assertions suivantes concernant le langage SQL, sélectionner celles qui sont correctes.
Le langage SQL permet d'implémenter physiquement un modèle logique exprimé en relationnel.
Le langage SQL permet d'entrer et de sortir des données d'une base de données.
Le langage SQL permet de donner et d'enlever des droits en lecture sur les données d'une base de données.
Le langage SQL permet de donner et d'enlever des droits en écriture sur les données d'une base de données.
Le langage SQL permet de créer une interface graphique utilisable par les utilisateurs finaux.
Le langage SQL permet de faire des traitements algorithmiques complexes.
Le langage SQL permet en général de créer une application informatique complète.
Soit les deux relations suivantes UV1 et UV2 représentant respectivement les places disponibles dans les cours de l'UTC et les inscriptions effectives pour les cours ouverts ce semestre :
FROM ELEVEWHERE classe = 'Terminale S 2' ;
SELECT EL.login, EL.nom, EL.prenom, EL.classe, AVG(note) AS moyenne
FROM ELEVE EL, NOTE, ENSEIGNANT EN
WHERE EL.login = eleve
AND enseignant = EN.login
AND matiere = 'Mathématiques'
GROUP BY EL.login, EL.nom, EL.prenom, EL.classe
HAVING COUNT(note) > 1
ORDER BY moyenne DESC;
SELECT EL.classe, ENA.nom, ENA.prenom, ENA.mail, ENA.matiere, AVG(EL.age), MIN(EL.
age), MAX(EL.age), COUNT(distinct EL.login), MIN(note), MAX(note), AVG(note)FROM ELEVE EL, NOTE N, ENSEIGNE EN, ENSEIGNANT ENA
WHERE EL.login = N.eleve
AND EL.classe = EN.classe
AND EN.enseignant = ENA.login
AND N.enseignant = ENA.login
GROUP BY ENA.login, ENA.nom, ENA.prenom, ENA.mail, EL.classe, ENA.matiere
HAVING AVG(note) <= 10
ORDER BY EL.classe, ENA.nom, ENA.prenom;
Solutions des exercices
29Stéphane Crozat
Exercice p. 20> Solution n°22
Exercice p. 20> Solution n°21
Relations UV1 et UV2
Compléter la requête SQL suivante pour qu'elle retourne pour les cours avec leur nom, plus le nombre d'inscrits tousen excès ou en défaut pour les cours ouverts et NULL pour les cours fermés.
SELECT UV1.Nom, UV2.Nombre-UV1.Nombre
FROM UV1 UV2LEFT JOIN
ON UV1.Nom=UV2.Nom
Soit les deux relations suivantes UV1 et UV2 représentant respectivement les places disponibles dans les cours de l'UTC et les inscriptions effectives pour les cours ouverts ce semestre :
Relations UV1 et UV2
Compléter la requête SQL suivante pour qu'elle retourne, pour les cours ouverts uniquement, le nombre d'inscrits moyen selon le nombre de places (nombre d'inscrits moyens pour les cours de 168 places, pour ceux de 48 places, etc.)
SELECT UV1.Nombre, avg(UV2.Nombre)
FROM UV1 UV2JOIN
ON UV1.Nom=UV2.Nom
GROUP BY UV1.Nombre
Quelle sont les requêtes équivalente à la requête ci-dessous :
1 SELECT Nom2 FROM Personne3 WHERE NOT(Nom IS NULL)4 GROUP BY Nom
SELECT Nom
FROM Personne
WHERE NOT(Nom IS NULL)
GROUP BY Nom
Solutions des exercices
30Stéphane Crozat
Exercice p. 20> Solution n°23
SELECT DISTINCT NomFROM PersonneWHERE Nom LIKE '%'
SELECT NomFROM Personne
SELECT NomFROM PersonneGROUP BY NomHAVING Count(Nom) > 0
SELECT NomFROM PersonneWHERE NOT(Nom IS NULL)
Dans la requête de départ, la clause supprime la valeur du résultat, ce que WHERE NOT(Nom IS NULL) Null
reproduit et WHERE Nom LIKE '%' HAVING Count(Nom)>0.
Dans la requête de départ, la clause a pour seul effet de supprimer les doublons, ce qui est GROUP BY
(avantageusement) reproduit par la clause .SELECT DISTINCT
Que renvoie la dernière instruction SQL de la séquence ci-dessous ?
1 CREATE TABLE R (a INTEGER, b INTEGER);2 INSERT INTO R VALUES (1,1);3 INSERT INTO R VALUES (1,2);4 INSERT INTO R VALUES (3,3);5 SELECT sum(R1.b) FROM R R1, R R2 WHERE R1.a=R2.a;
9
CREATE TABLE R (a INTEGER, b INTEGER);
INSERT INTO R VALUES (1,1);
INSERT INTO R VALUES (1,2);
INSERT INTO R VALUES (3,3);
SELECT sum(R1.b) FROM R R1, R R2 WHERE R1.a=R2.a;