Fichier de rejeu Close

Indication Close

A propos de... Close

Commentaire Close

Systèmes d'Information

Présentation

Films

Repas

Repas

Index

Téléchargements

Crédits

Aide

Menu	Contenu
	Sommaire, Téléchargements
	Aide sur les outils

Chevron	Action
	Aller en haut de la page courante
	Aller en bas de la page courante
	Passer à la page précédente
	Passer à la page suivante

Groupements

L’objectif de ces exercices est de savoir regrouper le informations et appliquer des fonctions d’agrégat sur ces regroupements « Aux pays des bières ».

4/4 GROUP BY, HAVING

Groupement

Ecrire une requête SQL répondant à la question

\(Q_1\) : « Rechercher le nom, la couleur des bières et leur quantité en stock dans la base de données ».

Votre réponse :

sql : essai.sql

DROP TABLE IF EXISTS bars;
DROP TABLE IF EXISTS bieres;
DROP TABLE IF EXISTS services;

CREATE TABLE bars ( id_bar INTEGER NOT NULL PRIMARY KEY,
                    bar    VARCHAR(20),
                    pays   VARCHAR(20) );

CREATE TABLE bieres ( id_biere  INTEGER NOT NULL PRIMARY KEY,
                      biere     VARCHAR(20),
                      couleur   VARCHAR(10),
                      origine   VARCHAR(20) );

CREATE TABLE services  ( id_bar   INTEGER NOT NULL,
                         id_biere INTEGER NOT NULL,
                         stock    SMALLINT,
                         PRIMARY KEY (id_bar, id_biere),
                         FOREIGN KEY (id_bar)   REFERENCES bars,
                         FOREIGN KEY (id_biere) REFERENCES bieres);

INSERT INTO bars VALUES (1, 'Bar du Coin', 'France');
INSERT INTO bars VALUES (2, 'Corners Pub',  'USA');
INSERT INTO bars VALUES (3, 'Cafe der Ecke','Allemagne');
INSERT INTO bars VALUES (4, 'Cafe des Amis', 'France');

INSERT INTO bieres  VALUES (1, 'Kronenbourg', 'Blonde', 'France');
INSERT INTO bieres  VALUES (2, 'Guinness', 'Brune', 'Irlande');
INSERT INTO bieres  VALUES (3, 'Heineken', 'Blonde', 'Hollande');
INSERT INTO bieres  VALUES (4, 'Newcastle', 'Rousse', 'UK');
INSERT INTO bieres  VALUES (5, 'Spaten', 'Blonde', 'Allemagne');
INSERT INTO bieres  VALUES (6, 'Bush', 'Blonde', 'USA');
INSERT INTO bieres  VALUES (7, 'Kanterbrau', 'Blonde', 'France');
INSERT INTO bieres  VALUES (8, 'Kronenbourg', 'Brune', 'France');

INSERT INTO services VALUES (1,1,1000);
INSERT INTO services VALUES (1,2, 250);
INSERT INTO services VALUES (1,3, 50);
INSERT INTO services VALUES (1,4, 10);
INSERT INTO services VALUES (1,5, 10);
INSERT INTO services VALUES (1,6, 10);
INSERT INTO services VALUES (1,7, 40);
INSERT INTO services VALUES (1,8, 60);
INSERT INTO services VALUES (2,1, 100);
INSERT INTO services VALUES (2,6, 1500);
INSERT INTO services VALUES (3,5, 5000);

Output

Interpréteur

Menu	Action
	Ré-initialiser les sorties
	Faire apparaître le menu d'aide
	Interpréter le programme

Editeur

Menu	Raccouci	Action
	`Ctrl+N`	Initialiser l'éditeur
	`Ctrl+O`	Charger le contenu d'un fichier dans l'éditeur
	`Ctrl+S`	Sauvegarder le contenu de l'éditeur dans un fichier
	`Ctrl+P`	Imprimer le contenu de l'éditeur
	`Ctrl+Z`	Annuler la dernière modification
	`Maj+Ctrl+Z`	Rétablir la modification précedente
	`Ctrl+F`	Chercher une expression dans l'éditeur
	`Maj+Ctrl+F`	Chercher et remplacer une expression par une autre
	`F10`	Ouvrir une documentation du langage

Raccourci	Action
`F1`	Afficher cette aide
`Tab`	Indenter la sélection
`Maj+Tab`	Désindenter la sélection
`Ctrl+A`	Sélectionner le contenu de l'éditeur
`Ctrl+C`	Copier la sélection dans le presse-papier
`Ctrl+V`	Remplacer la sélection par le contenu du presse-papier
`Ctrl+X`	Supprimer la sélection et la copier dans le presse-papier
`Maj+Ctrl+R`	Chercher et remplacer une expression par une autre dans tout l'éditeur

Une solution possible :

On fait un regroupement (GROUP BY) par nom de bières (biere) et leur couleur

qui sont servies (\(\Join_{[]}(services, bieres)\)).

On récupère dans le résultat final les attribut de groupement (code:nom,couleur) et on calcule sur ces regroupements la somme des quantités servies (code:SUM(stock)).

Ecriture en algèbre relationnelle :

\(NJ = \Join_{[]}(services, bieres)\).

\(G = G_{[]}^{(biere, couleur,SUM(stock))}(NJ_1)\).

sql : essai.sql

DROP TABLE IF EXISTS bars;
DROP TABLE IF EXISTS bieres;
DROP TABLE IF EXISTS services;

CREATE TABLE bars ( id_bar INTEGER NOT NULL PRIMARY KEY,
                    bar    VARCHAR(20),
                    pays   VARCHAR(20) );

CREATE TABLE bieres ( id_biere  INTEGER NOT NULL PRIMARY KEY,
                      biere     VARCHAR(20),
                      couleur   VARCHAR(10),
                      origine   VARCHAR(20) );

Output

Interpréteur

Menu	Action
	Ré-initialiser les sorties
	Faire apparaître le menu d'aide
	Interpréter le programme

Editeur

Menu	Raccouci	Action
	`Ctrl+N`	Initialiser l'éditeur
	`Ctrl+O`	Charger le contenu d'un fichier dans l'éditeur
	`Ctrl+S`	Sauvegarder le contenu de l'éditeur dans un fichier
	`Ctrl+P`	Imprimer le contenu de l'éditeur
	`Ctrl+Z`	Annuler la dernière modification
	`Maj+Ctrl+Z`	Rétablir la modification précedente
	`Ctrl+F`	Chercher une expression dans l'éditeur
	`Maj+Ctrl+F`	Chercher et remplacer une expression par une autre
	`F10`	Ouvrir une documentation du langage

Raccourci	Action
`F1`	Afficher cette aide
`Tab`	Indenter la sélection
`Maj+Tab`	Désindenter la sélection
`Ctrl+A`	Sélectionner le contenu de l'éditeur
`Ctrl+C`	Copier la sélection dans le presse-papier
`Ctrl+V`	Remplacer la sélection par le contenu du presse-papier
`Ctrl+X`	Supprimer la sélection et la copier dans le presse-papier
`Maj+Ctrl+R`	Chercher et remplacer une expression par une autre dans tout l'éditeur

Groupement et restriction

Ecrire une requête SQL répondant à la question :

\(Q_1\) : « Rechercher le nom des bars, le nombre de bières qu’ils servent et la somme de leur stocks pour les bars qui servent au moins deux bières ».

Votre réponse :

sql : essai.sql

DROP TABLE IF EXISTS bars;
DROP TABLE IF EXISTS bieres;
DROP TABLE IF EXISTS services;

CREATE TABLE bars ( id_bar INTEGER NOT NULL PRIMARY KEY,
                    bar    VARCHAR(20),
                    pays   VARCHAR(20) );

CREATE TABLE bieres ( id_biere  INTEGER NOT NULL PRIMARY KEY,
                      biere     VARCHAR(20),
                      couleur   VARCHAR(10),
                      origine   VARCHAR(20) );

Output

Interpréteur

Menu	Action
	Ré-initialiser les sorties
	Faire apparaître le menu d'aide
	Interpréter le programme

Editeur

Menu	Raccouci	Action
	`Ctrl+N`	Initialiser l'éditeur
	`Ctrl+O`	Charger le contenu d'un fichier dans l'éditeur
	`Ctrl+S`	Sauvegarder le contenu de l'éditeur dans un fichier
	`Ctrl+P`	Imprimer le contenu de l'éditeur
	`Ctrl+Z`	Annuler la dernière modification
	`Maj+Ctrl+Z`	Rétablir la modification précedente
	`Ctrl+F`	Chercher une expression dans l'éditeur
	`Maj+Ctrl+F`	Chercher et remplacer une expression par une autre
	`F10`	Ouvrir une documentation du langage

Raccourci	Action
`F1`	Afficher cette aide
`Tab`	Indenter la sélection
`Maj+Tab`	Désindenter la sélection
`Ctrl+A`	Sélectionner le contenu de l'éditeur
`Ctrl+C`	Copier la sélection dans le presse-papier
`Ctrl+V`	Remplacer la sélection par le contenu du presse-papier
`Ctrl+X`	Supprimer la sélection et la copier dans le presse-papier
`Maj+Ctrl+R`	Chercher et remplacer une expression par une autre dans tout l'éditeur

Une solution possible :

On fait un regroupement (GROUP BY) par bar servant des bières.

On ne retient (HAVING) dans ce regroupement que les bars qui servent au moins 2 bières différentes.

On récupère dans le résultat final le nom des bars, leur nombre de bières et la quantité totale (bar,COUNT(id_biere),SUM(stock)) servie dans ces bars.

Ecriture en algèbre relationnelle :

\(NJ = \Join_{[]}(services, bars)\).

\(G = G_{[COUNT(id_biere) > 1]}^{(bar,COUNT(id_biere),SUM(stock))}(NJ)\).

sql : essai.sql

DROP TABLE IF EXISTS bars;
DROP TABLE IF EXISTS bieres;
DROP TABLE IF EXISTS services;

CREATE TABLE bars ( id_bar INTEGER NOT NULL PRIMARY KEY,
                    bar    VARCHAR(20),
                    pays   VARCHAR(20) );

CREATE TABLE bieres ( id_biere  INTEGER NOT NULL PRIMARY KEY,
                      biere     VARCHAR(20),
                      couleur   VARCHAR(10),
                      origine   VARCHAR(20) );

Output

Interpréteur

Menu	Action
	Ré-initialiser les sorties
	Faire apparaître le menu d'aide
	Interpréter le programme

Editeur

Menu	Raccouci	Action
	`Ctrl+N`	Initialiser l'éditeur
	`Ctrl+O`	Charger le contenu d'un fichier dans l'éditeur
	`Ctrl+S`	Sauvegarder le contenu de l'éditeur dans un fichier
	`Ctrl+P`	Imprimer le contenu de l'éditeur
	`Ctrl+Z`	Annuler la dernière modification
	`Maj+Ctrl+Z`	Rétablir la modification précedente
	`Ctrl+F`	Chercher une expression dans l'éditeur
	`Maj+Ctrl+F`	Chercher et remplacer une expression par une autre
	`F10`	Ouvrir une documentation du langage

Raccourci	Action
`F1`	Afficher cette aide
`Tab`	Indenter la sélection
`Maj+Tab`	Désindenter la sélection
`Ctrl+A`	Sélectionner le contenu de l'éditeur
`Ctrl+C`	Copier la sélection dans le presse-papier
`Ctrl+V`	Remplacer la sélection par le contenu du presse-papier
`Ctrl+X`	Supprimer la sélection et la copier dans le presse-papier
`Maj+Ctrl+R`	Chercher et remplacer une expression par une autre dans tout l'éditeur

Groupement et restriction avec requête imbriquée

Ecrire une requête SQL répondant à la question

\(Q_1\) : « Rechercher le nom des pays, le nombre de toutes les bières servies dans ces pays et la somme de leurs stocks, pour les pays dont la quantité de bières est supérieure à la moyenne des stocks mondiaux ».

Votre réponse :

sql : essai.sql

DROP TABLE IF EXISTS bars;
DROP TABLE IF EXISTS bieres;
DROP TABLE IF EXISTS services;

CREATE TABLE bars ( id_bar INTEGER NOT NULL PRIMARY KEY,
                    bar    VARCHAR(20),
                    pays   VARCHAR(20) );

CREATE TABLE bieres ( id_biere  INTEGER NOT NULL PRIMARY KEY,
                      biere     VARCHAR(20),
                      couleur   VARCHAR(10),
                      origine   VARCHAR(20) );

Output

Interpréteur

Menu	Action
	Ré-initialiser les sorties
	Faire apparaître le menu d'aide
	Interpréter le programme

Editeur

Menu	Raccouci	Action
	`Ctrl+N`	Initialiser l'éditeur
	`Ctrl+O`	Charger le contenu d'un fichier dans l'éditeur
	`Ctrl+S`	Sauvegarder le contenu de l'éditeur dans un fichier
	`Ctrl+P`	Imprimer le contenu de l'éditeur
	`Ctrl+Z`	Annuler la dernière modification
	`Maj+Ctrl+Z`	Rétablir la modification précedente
	`Ctrl+F`	Chercher une expression dans l'éditeur
	`Maj+Ctrl+F`	Chercher et remplacer une expression par une autre
	`F10`	Ouvrir une documentation du langage

Raccourci	Action
`F1`	Afficher cette aide
`Tab`	Indenter la sélection
`Maj+Tab`	Désindenter la sélection
`Ctrl+A`	Sélectionner le contenu de l'éditeur
`Ctrl+C`	Copier la sélection dans le presse-papier
`Ctrl+V`	Remplacer la sélection par le contenu du presse-papier
`Ctrl+X`	Supprimer la sélection et la copier dans le presse-papier
`Maj+Ctrl+R`	Chercher et remplacer une expression par une autre dans tout l'éditeur

Une solution possible :

On fait un regroupement (GROUP BY) par nom de pays des bars servant des bières.

On ne retient dans ces regroupements que les pays dont la somme des stock de bières de leurs bars est supérieure à la moyenne des stocks mondiaux.

On obtient dans le résultat final le nom des pays, le nombre de bières et la somme des stock servies dans ces pays.

Ecriture en algèbre relationnelle :

\(NJ = \Join_{[]}(services, bars)\).

\(G = G_{[SUM(stock)> (SELECT AVG( stock ) FROM services)]}^{(pays,COUNT(id\_biere),SUM(stock))}(NJ)\).

sql : essai.sql

DROP TABLE IF EXISTS bars;
DROP TABLE IF EXISTS bieres;
DROP TABLE IF EXISTS services;

CREATE TABLE bars ( id_bar INTEGER NOT NULL PRIMARY KEY,
                    bar    VARCHAR(20),
                    pays   VARCHAR(20) );

CREATE TABLE bieres ( id_biere  INTEGER NOT NULL PRIMARY KEY,
                      biere     VARCHAR(20),
                      couleur   VARCHAR(10),
                      origine   VARCHAR(20) );

Output

Interpréteur

Menu	Action
	Ré-initialiser les sorties
	Faire apparaître le menu d'aide
	Interpréter le programme

Editeur

Menu	Raccouci	Action
	`Ctrl+N`	Initialiser l'éditeur
	`Ctrl+O`	Charger le contenu d'un fichier dans l'éditeur
	`Ctrl+S`	Sauvegarder le contenu de l'éditeur dans un fichier
	`Ctrl+P`	Imprimer le contenu de l'éditeur
	`Ctrl+Z`	Annuler la dernière modification
	`Maj+Ctrl+Z`	Rétablir la modification précedente
	`Ctrl+F`	Chercher une expression dans l'éditeur
	`Maj+Ctrl+F`	Chercher et remplacer une expression par une autre
	`F10`	Ouvrir une documentation du langage

Raccourci	Action
`F1`	Afficher cette aide
`Tab`	Indenter la sélection
`Maj+Tab`	Désindenter la sélection
`Ctrl+A`	Sélectionner le contenu de l'éditeur
`Ctrl+C`	Copier la sélection dans le presse-papier
`Ctrl+V`	Remplacer la sélection par le contenu du presse-papier
`Ctrl+X`	Supprimer la sélection et la copier dans le presse-papier
`Maj+Ctrl+R`	Chercher et remplacer une expression par une autre dans tout l'éditeur

Restriction, groupement et restriction

Ecrire une requête SQL répondant à la question

\(Q_1\) : « Rechercher le nom des bars, le nombre de toutes les bières servies dans ces bars et la somme de leurs stocks, pour les bars de “France” ou des “USA” servant plus d’une bière ».

Votre réponse :

sql : essai.sql

DROP TABLE IF EXISTS bars;
DROP TABLE IF EXISTS bieres;
DROP TABLE IF EXISTS services;

CREATE TABLE bars ( id_bar INTEGER NOT NULL PRIMARY KEY,
                    bar    VARCHAR(20),
                    pays   VARCHAR(20) );

CREATE TABLE bieres ( id_biere  INTEGER NOT NULL PRIMARY KEY,
                      biere     VARCHAR(20),
                      couleur   VARCHAR(10),
                      origine   VARCHAR(20) );

Output

Interpréteur

Menu	Action
	Ré-initialiser les sorties
	Faire apparaître le menu d'aide
	Interpréter le programme

Editeur

Menu	Raccouci	Action
	`Ctrl+N`	Initialiser l'éditeur
	`Ctrl+O`	Charger le contenu d'un fichier dans l'éditeur
	`Ctrl+S`	Sauvegarder le contenu de l'éditeur dans un fichier
	`Ctrl+P`	Imprimer le contenu de l'éditeur
	`Ctrl+Z`	Annuler la dernière modification
	`Maj+Ctrl+Z`	Rétablir la modification précedente
	`Ctrl+F`	Chercher une expression dans l'éditeur
	`Maj+Ctrl+F`	Chercher et remplacer une expression par une autre
	`F10`	Ouvrir une documentation du langage

Raccourci	Action
`F1`	Afficher cette aide
`Tab`	Indenter la sélection
`Maj+Tab`	Désindenter la sélection
`Ctrl+A`	Sélectionner le contenu de l'éditeur
`Ctrl+C`	Copier la sélection dans le presse-papier
`Ctrl+V`	Remplacer la sélection par le contenu du presse-papier
`Ctrl+X`	Supprimer la sélection et la copier dans le presse-papier
`Maj+Ctrl+R`	Chercher et remplacer une expression par une autre dans tout l'éditeur

Une solution possible :

On vérifie d’abord que nous sommes bien en “France” ou au “USA” :

\(R = \sigma{[pays ='France' OR pays='USA']}(bars)\)

On récupère lmes services de bars de ces deuc pays

\(NJ = \Join{[]}(services,R)\)

Puis on fait un regroupement (GROUP BY) par nom (bar) de bars.

On ne retient dans ce regroupement que les :code:`bars ` dont le nombre de de bières de leurs bars est supérieure à la moyenne des stocks mondiaux.

On obtient dans le résultat final le nom des pays, le nombre de bières est au moins égal à deux.

On récupère dans le résultat final le nom des bars, le nombre (COUNT()) de bières qu’ils servent et la somme (SUM()) des stock de bières servies dans ces bars

\(G = G_{[SUM(stock)> (SELECT COUNT (id_biere) > 1)]}^{(bar,COUNT(id\_biere),SUM(stock))}(NJ)\).

sql : essai.sql

DROP TABLE IF EXISTS bars;
DROP TABLE IF EXISTS bieres;
DROP TABLE IF EXISTS services;

CREATE TABLE bars ( id_bar INTEGER NOT NULL PRIMARY KEY,
                    bar    VARCHAR(20),
                    pays   VARCHAR(20) );

CREATE TABLE bieres ( id_biere  INTEGER NOT NULL PRIMARY KEY,
                      biere     VARCHAR(20),
                      couleur   VARCHAR(10),
                      origine   VARCHAR(20) );

Output

Interpréteur

Menu	Action
	Ré-initialiser les sorties
	Faire apparaître le menu d'aide
	Interpréter le programme

Editeur

Menu	Raccouci	Action
	`Ctrl+N`	Initialiser l'éditeur
	`Ctrl+O`	Charger le contenu d'un fichier dans l'éditeur
	`Ctrl+S`	Sauvegarder le contenu de l'éditeur dans un fichier
	`Ctrl+P`	Imprimer le contenu de l'éditeur
	`Ctrl+Z`	Annuler la dernière modification
	`Maj+Ctrl+Z`	Rétablir la modification précedente
	`Ctrl+F`	Chercher une expression dans l'éditeur
	`Maj+Ctrl+F`	Chercher et remplacer une expression par une autre
	`F10`	Ouvrir une documentation du langage

Raccourci	Action
`F1`	Afficher cette aide
`Tab`	Indenter la sélection
`Maj+Tab`	Désindenter la sélection
`Ctrl+A`	Sélectionner le contenu de l'éditeur
`Ctrl+C`	Copier la sélection dans le presse-papier
`Ctrl+V`	Remplacer la sélection par le contenu du presse-papier
`Ctrl+X`	Supprimer la sélection et la copier dans le presse-papier
`Maj+Ctrl+R`	Chercher et remplacer une expression par une autre dans tout l'éditeur

Fichier de rejeu Close

Indication Close

A propos de... Close

Commentaire Close

Systèmes d'Information

Téléchargements

Aide

En-tête

Pied de page

Groupements

Votre réponse :

Interpréteur

Editeur

Une solution possible :

Interpréteur

Editeur

Votre réponse :

Interpréteur

Editeur

Une solution possible :

Interpréteur

Editeur

Votre réponse :

Interpréteur

Editeur

Une solution possible :

Interpréteur

Editeur

Votre réponse :

Interpréteur

Editeur

Une solution possible :

Interpréteur

Editeur