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

GROUP BY

Construire un arbre de requêtes 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 :

Arbre de requêtes : graph45.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	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, 20); INSERT INTO services VALUES (1,8, 20); INSERT INTO services VALUES (2,1, 100); INSERT INTO services VALUES (2,6, 1500); INSERT INTO services VALUES (3,5, 5000);

Vue d'ensemble

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, 20);
INSERT INTO services VALUES (1,8, 20);
INSERT INTO services VALUES (2,1, 100);
INSERT INTO services VALUES (2,6, 1500);
INSERT INTO services VALUES (3,5, 5000);

biere	couleur	sum(stock)
Bush	Blonde	1510
Guinness	Brune	250
Heineken	Blonde	50
Kanterbrau	Blonde	20
Kronenbourg	Blonde	1100
Kronenbourg	Brune	20
Newcastle	Rousse	10
Spaten	Blonde	5010

Une solution possible :

On récupère d’abord les services de bieres :

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

puis on fait un regroupement par nom et couleur des bières servies en appliquant la fonction d’aggrégat pour calculer la somme des stocks de bières (SUM(stock) sur les regroupements :

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

Ecriture en algèbre relationnelle :

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

Arbre de requêtes : bars-16.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	{ "class": "go.GraphLinksModel", "copiesArrays": true, "copiesArrayObjects": true, "linkFromPortIdProperty": "fromPort", "linkToPortIdProperty": "toPort", "nodeDataArray": [ {"category":"query", "name":"Q_1", "info":"QUERY", "key":-7, "loc":"225 -225"}, {"category":"agreg", "name":"G", "attributes":"(biere,couleur)", "having":"[]", "functions":"(sum(stock))", "info":"AGGREGATION", "key":-4, "loc":"225 -100"}, {"category":"table", "name":"services", "info":"TABLE", "key":-1, "loc":"150 175"}, {"category":"table", "name":"bieres", "info":"TABLE", "key":-5, "loc":"325 175"}, {"category":"innerjoin", "name":"NJ", "condition":"[]", "info":"INNER JOIN", "key":-8, "loc":"225 25"} ], "linkDataArray": [ {"from":-1, "to":-8, "fromPort":"", "toPort":"L", "points":[150,158.65,150,158.65,150,101.65,186.81,101.65,186.81,44.65,186.81,29.65]}, {"from":-5, "to":-8, "fromPort":"", "toPort":"R", "points":[325,158.65,325,158.65,325,101.65,237.19,101.65,237.19,44.65,237.19,29.65]}, {"from":-8, "to":-4, "fromPort":"T", "toPort":"B", "points":[212,4.099,212,4.099,212,4,212,4,212,-28,216.5,-28,216.5,-47.5,216.5,-62.5]}, {"from":-4, "to":-7, "fromPort":"T", "toPort":"R", "points":[216.5,-137.5,216.5,-137.5,216.5,-165.57500000000002,224.99999999999994,-165.57500000000002,224.99999999999994,-193.65000000000003,224.99999999999994,-208.65000000000003]} ]} 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, 20); INSERT INTO services VALUES (1,8, 20); INSERT INTO services VALUES (2,1, 100); INSERT INTO services VALUES (2,6, 1500); INSERT INTO services VALUES (3,5, 5000);

Vue d'ensemble

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) );

biere	couleur	sum(stock)
Bush	Blonde	1510
Guinness	Brune	250
Heineken	Blonde	50
Kanterbrau	Blonde	20
Kronenbourg	Blonde	1100
Kronenbourg	Brune	20
Newcastle	Rousse	10
Spaten	Blonde	5010

GROUP BY HAVING

Construire un arbre de requêtes 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 :

Arbre de requêtes : graph52.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	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, 20); INSERT INTO services VALUES (1,8, 20); INSERT INTO services VALUES (2,1, 100); INSERT INTO services VALUES (2,6, 1500); INSERT INTO services VALUES (3,5, 5000);

Vue d'ensemble

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) );

id_bar	bar	bar:1	COUNT(id_biere)	SUM(stock)
1	Bar du Coin	Bar du Coin	8	1370
2	Corners Pub	Corners Pub	2	1600

Une solution possible :

On récupère d’abord les services des bars :

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

puis on fait un regroupement par nom de bars en ne retenant dans les regroupements que les bars qui servent au moins 2 bières :

GROUP BY bar

HAVING COUNT(id_biere) > 1

On applique sur ces regroupements les fonctions d’agrégat pour pouvoir compter le nombre de bières servies (COUNT(id_biere)) et calculer la somme des stocks (SUM(stock)) dans ces bars.

Ecriture en algèbre relationnelle :

\(G = G_{[COUNT(id\_biere) > 1]}^{(bar,COUNT(id\_biere),SUM(stock))}(\Join_{[]}(services, bars))\).

Arbre de requêtes : bars-17.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	{ "class": "go.GraphLinksModel", "copiesArrays": true, "copiesArrayObjects": true, "linkFromPortIdProperty": "fromPort", "linkToPortIdProperty": "toPort", "nodeDataArray": [ {"category":"query", "name":"Q_1", "info":"QUERY", "key":-7, "loc":"225 -225"}, {"category":"agreg", "name":"G", "attributes":"(id_bar,bar)", "having":"[count(id_biere) > 1]", "functions":"(bar, COUNT(id_biere), SUM(stock))", "info":"AGGREGATION", "key":-4, "loc":"225 -100"}, {"category":"table", "name":"services", "info":"TABLE", "key":-1, "loc":"150 175"}, {"category":"table", "name":"bars", "info":"TABLE", "key":-5, "loc":"325 175"}, {"category":"innerjoin", "name":"NJ", "condition":"[]", "info":"INNER JOIN", "key":-8, "loc":"225 25"} ], "linkDataArray": [ {"from":-1, "to":-8, "fromPort":"", "toPort":"L", "points":[150,158.65,150,158.65,150,101.65,186.81,101.65,186.81,44.65,186.81,29.65]}, {"from":-5, "to":-8, "fromPort":"", "toPort":"R", "points":[325.00000000000006,158.64999999999998,325.00000000000006,158.64999999999998,325.00000000000006,148,237.19,148,237.19,44.65,237.19,29.65]}, {"from":-8, "to":-4, "fromPort":"T", "toPort":"B", "points":[212,4.099,212,4.099,212,-4,212,-4,212,-28,216.49999999999994,-28,216.49999999999994,-47.5,216.49999999999994,-62.5]}, {"from":-4, "to":-7, "fromPort":"T", "toPort":"R", "points":[216.49999999999994,-137.5,216.49999999999994,-137.5,216.49999999999994,-148,225,-148,225,-193.64999999999998,225,-208.64999999999998]} ]} 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, 20); INSERT INTO services VALUES (1,8, 20); INSERT INTO services VALUES (2,1, 100); INSERT INTO services VALUES (2,6, 1500); INSERT INTO services VALUES (3,5, 5000);

Vue d'ensemble

{ "class": "go.GraphLinksModel",
  "copiesArrays": true,
  "copiesArrayObjects": true,
  "linkFromPortIdProperty": "fromPort",
  "linkToPortIdProperty": "toPort",
  "nodeDataArray": [ 
{"category":"query", "name":"Q_1", "info":"QUERY", "key":-7, "loc":"225 -225"},
{"category":"agreg", "name":"G", "attributes":"(id_bar,bar)", "having":"[count(id_biere) > 1]", "functions":"(bar,  COUNT(id_biere), SUM(stock))", "info":"AGGREGATION", "key":-4, "loc":"225 -100"},
{"category":"table", "name":"services", "info":"TABLE", "key":-1, "loc":"150 175"},
{"category":"table", "name":"bars", "info":"TABLE", "key":-5, "loc":"325 175"},
{"category":"innerjoin", "name":"NJ", "condition":"[]", "info":"INNER JOIN", "key":-8, "loc":"225 25"}
 ],
  "linkDataArray": [ 
{"from":-1, "to":-8, "fromPort":"", "toPort":"L", "points":[150,158.65,150,158.65,150,101.65,186.81,101.65,186.81,44.65,186.81,29.65]},
{"from":-5, "to":-8, "fromPort":"", "toPort":"R", "points":[325.00000000000006,158.64999999999998,325.00000000000006,158.64999999999998,325.00000000000006,148,237.19,148,237.19,44.65,237.19,29.65]},
{"from":-8, "to":-4, "fromPort":"T", "toPort":"B", "points":[212,4.099,212,4.099,212,-4,212,-4,212,-28,216.49999999999994,-28,216.49999999999994,-47.5,216.49999999999994,-62.5]},
{"from":-4, "to":-7, "fromPort":"T", "toPort":"R", "points":[216.49999999999994,-137.5,216.49999999999994,-137.5,216.49999999999994,-148,225,-148,225,-193.64999999999998,225,-208.64999999999998]}
 ]}

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) );

id_bar	bar	bar:1	COUNT(id_biere)	SUM(stock)
1	Bar du Coin	Bar du Coin	8	1370
2	Corners Pub	Corners Pub	2	1600

GROUP BY HAVING, requête imbriquée

Construire un arbre de requêtes 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 :

Arbre de requêtes : graph59.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	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, 20); INSERT INTO services VALUES (1,8, 20); INSERT INTO services VALUES (2,1, 100); INSERT INTO services VALUES (2,6, 1500); INSERT INTO services VALUES (3,5, 5000);

Vue d'ensemble

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) );

Une solution possible :

On récupère d’abord les services des bars :

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

puis on fait un regroupement par nom de bars en ne retenant dans les regroupements que les bars dont la quantité de bières est supérieure à la moyenne des stocks mondiaux. Il faut donc faire une requête imbriquée dans la clause HAVING pour calculer la moyenne des stocks de bières servies et vérifier qu’elle est supérieure à la somme des stocks de chaque bar regroupé :

GROUP BY bar

HAVING SUM(stock) > SELECT AVG(stock) FROM services)

On obtient dans le résultat final le nom des pays, le nombre de bières et la somme des SUM(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)\).

Arbre de requêtes : bars-18.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	{ "class": "go.GraphLinksModel", "copiesArrays": true, "copiesArrayObjects": true, "linkFromPortIdProperty": "fromPort", "linkToPortIdProperty": "toPort", "nodeDataArray": [ {"category":"query", "name":"Q_1", "info":"QUERY", "key":-7, "loc":"225 -225"}, {"category":"agreg", "name":"G", "attributes":"(pays)", "having":"[sum(stock) > (SELECT AVG (stock) FROM services)]", "functions":"(COUNT(id_biere), SUM(stock))", "info":"AGGREGATION", "key":-4, "loc":"225 -100"}, {"category":"table", "name":"services", "info":"TABLE", "key":-1, "loc":"150 175"}, {"category":"table", "name":"bars", "info":"TABLE", "key":-5, "loc":"325 175"}, {"category":"innerjoin", "name":"NJ", "condition":"[]", "info":"INNER JOIN", "key":-8, "loc":"225 25"} ], "linkDataArray": [ {"from":-1, "to":-8, "fromPort":"", "toPort":"L", "points":[150,158.65,150,158.65,150,101.65,186.81,101.65,186.81,44.65,186.81,29.65]}, {"from":-5, "to":-8, "fromPort":"", "toPort":"R", "points":[325.00000000000006,158.64999999999998,325.00000000000006,158.64999999999998,325.00000000000006,148,237.19,148,237.19,44.65,237.19,29.65]}, {"from":-8, "to":-4, "fromPort":"T", "toPort":"B", "points":[212,4.099,212,4.099,212,-4,212,-4,212,-28,216.5,-28,216.5,-47.5,216.5,-62.5]}, {"from":-4, "to":-7, "fromPort":"T", "toPort":"R", "points":[216.5,-137.5,216.5,-137.5,216.5,-148,225,-148,225,-193.64999999999998,225,-208.64999999999998]} ]} 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, 20); INSERT INTO services VALUES (1,8, 20); INSERT INTO services VALUES (2,1, 100); INSERT INTO services VALUES (2,6, 1500); INSERT INTO services VALUES (3,5, 5000);

Vue d'ensemble

{ "class": "go.GraphLinksModel",
  "copiesArrays": true,
  "copiesArrayObjects": true,
  "linkFromPortIdProperty": "fromPort",
  "linkToPortIdProperty": "toPort",
  "nodeDataArray": [ 
{"category":"query", "name":"Q_1", "info":"QUERY", "key":-7, "loc":"225 -225"},
{"category":"agreg", "name":"G", "attributes":"(pays)", "having":"[sum(stock) > (SELECT AVG (stock) FROM services)]", "functions":"(COUNT(id_biere), SUM(stock))", "info":"AGGREGATION", "key":-4, "loc":"225 -100"},
{"category":"table", "name":"services", "info":"TABLE", "key":-1, "loc":"150 175"},
{"category":"table", "name":"bars", "info":"TABLE", "key":-5, "loc":"325 175"},
{"category":"innerjoin", "name":"NJ", "condition":"[]", "info":"INNER JOIN", "key":-8, "loc":"225 25"}
 ],
  "linkDataArray": [ 
{"from":-1, "to":-8, "fromPort":"", "toPort":"L", "points":[150,158.65,150,158.65,150,101.65,186.81,101.65,186.81,44.65,186.81,29.65]},
{"from":-5, "to":-8, "fromPort":"", "toPort":"R", "points":[325.00000000000006,158.64999999999998,325.00000000000006,158.64999999999998,325.00000000000006,148,237.19,148,237.19,44.65,237.19,29.65]},
{"from":-8, "to":-4, "fromPort":"T", "toPort":"B", "points":[212,4.099,212,4.099,212,-4,212,-4,212,-28,216.5,-28,216.5,-47.5,216.5,-62.5]},
{"from":-4, "to":-7, "fromPort":"T", "toPort":"R", "points":[216.5,-137.5,216.5,-137.5,216.5,-148,225,-148,225,-193.64999999999998,225,-208.64999999999998]}
 ]}

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) );

WHERE, GROUP BY HAVING

Construire un arbre de requêtes 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 :

Arbre de requêtes : graph64.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	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, 20); INSERT INTO services VALUES (1,8, 20); INSERT INTO services VALUES (2,1, 100); INSERT INTO services VALUES (2,6, 1500); INSERT INTO services VALUES (3,5, 5000);

Vue d'ensemble

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) );

bar	COUNT(id_biere)	SUM(stock)
Bar du Coin	8	1370
Corners Pub	2	1600

Une solution possible :

On vérifie d’abord que les bars sont bien en 'France' ou aux 'USA' :

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

On récupère ensuite les services de bars de ces deux 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 bars dont le nombre de bières est supérieur à la moyenne des stocks mondiaux.

On récupère dans le résultat final le nom des bars (l’attribut de groupement bar), le nombre (COUNT(id_biere))) de bières qu’ils servent et la somme (SUM(stock)) 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)\).

Arbre de requêtes : bars-19.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	{ "class": "go.GraphLinksModel", "copiesArrays": true, "copiesArrayObjects": true, "linkFromPortIdProperty": "fromPort", "linkToPortIdProperty": "toPort", "nodeDataArray": [ {"category":"query", "name":"Q_1", "info":"QUERY", "key":-7, "loc":"225 -225"}, {"category":"agreg", "name":"G", "attributes":"(bar)", "having":"[COUNT(id_biere) > 1]", "functions":"(COUNT(id_biere), SUM(stock))", "info":"AGGREGATION", "key":-4, "loc":"225 -100"}, {"category":"table", "name":"services", "info":"TABLE", "key":-1, "loc":"150 250"}, {"category":"table", "name":"bars", "info":"TABLE", "key":-5, "loc":"275 250"}, {"category":"innerjoin", "name":"NJ", "condition":"[]", "info":"INNER JOIN", "key":-8, "loc":"250 25"}, {"category":"restrict", "name":"R", "condition":"[pays ='France' OR pays='USA']", "info":"RESTRICTION", "key":-6, "loc":"275 150"} ], "linkDataArray": [ {"from":-1, "to":-8, "fromPort":"", "toPort":"L", "points":[149.99999999999994,233.64999999999995,149.99999999999994,233.64999999999995,149.99999999999994,236,149.99999999999994,236,149.99999999999994,220,124,220,124,92,211.81000000000006,92,211.81000000000006,44.650000000000006,211.81000000000006,29.650000000000002]}, {"from":-8, "to":-4, "fromPort":"T", "toPort":"B", "points":[237.00000000000006,4.099000000000004,237.00000000000006,4.099000000000004,237.00000000000006,-4,237.00000000000006,-4,237.00000000000006,-28,216.5,-28,216.5,-47.5,216.5,-62.5]}, {"from":-4, "to":-7, "fromPort":"T", "toPort":"R", "points":[216.5,-137.5,216.5,-137.5,216.5,-148,225,-148,225,-193.64999999999998,225,-208.64999999999998]}, {"from":-5, "to":-6, "fromPort":"", "toPort":"B", "points":[275.00000000000006,233.64999999999995,275.00000000000006,233.64999999999995,275.00000000000006,201.64999999999998,275,201.64999999999998,275,169.65,275,154.65]}, {"from":-6, "to":-8, "fromPort":"T", "toPort":"R", "points":[275,103.65,275,103.65,275,92,262.19000000000005,92,262.19000000000005,44.650000000000006,262.19000000000005,29.650000000000002]} ]} 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, 20); INSERT INTO services VALUES (1,8, 20); INSERT INTO services VALUES (2,1, 100); INSERT INTO services VALUES (2,6, 1500); INSERT INTO services VALUES (3,5, 5000);

Vue d'ensemble

{ "class": "go.GraphLinksModel",
  "copiesArrays": true,
  "copiesArrayObjects": true,
  "linkFromPortIdProperty": "fromPort",
  "linkToPortIdProperty": "toPort",
  "nodeDataArray": [ 
{"category":"query", "name":"Q_1", "info":"QUERY", "key":-7, "loc":"225 -225"},
{"category":"agreg", "name":"G", "attributes":"(bar)", "having":"[COUNT(id_biere) > 1]", "functions":"(COUNT(id_biere), SUM(stock))", "info":"AGGREGATION", "key":-4, "loc":"225 -100"},
{"category":"table", "name":"services", "info":"TABLE", "key":-1, "loc":"150 250"},
{"category":"table", "name":"bars", "info":"TABLE", "key":-5, "loc":"275 250"},
{"category":"innerjoin", "name":"NJ", "condition":"[]", "info":"INNER JOIN", "key":-8, "loc":"250 25"},
{"category":"restrict", "name":"R", "condition":"[pays ='France' OR pays='USA']", "info":"RESTRICTION", "key":-6, "loc":"275 150"}
 ],
  "linkDataArray": [ 
{"from":-1, "to":-8, "fromPort":"", "toPort":"L", "points":[149.99999999999994,233.64999999999995,149.99999999999994,233.64999999999995,149.99999999999994,236,149.99999999999994,236,149.99999999999994,220,124,220,124,92,211.81000000000006,92,211.81000000000006,44.650000000000006,211.81000000000006,29.650000000000002]},
{"from":-8, "to":-4, "fromPort":"T", "toPort":"B", "points":[237.00000000000006,4.099000000000004,237.00000000000006,4.099000000000004,237.00000000000006,-4,237.00000000000006,-4,237.00000000000006,-28,216.5,-28,216.5,-47.5,216.5,-62.5]},
{"from":-4, "to":-7, "fromPort":"T", "toPort":"R", "points":[216.5,-137.5,216.5,-137.5,216.5,-148,225,-148,225,-193.64999999999998,225,-208.64999999999998]},
{"from":-5, "to":-6, "fromPort":"", "toPort":"B", "points":[275.00000000000006,233.64999999999995,275.00000000000006,233.64999999999995,275.00000000000006,201.64999999999998,275,201.64999999999998,275,169.65,275,154.65]},
{"from":-6, "to":-8, "fromPort":"T", "toPort":"R", "points":[275,103.65,275,103.65,275,92,262.19000000000005,92,262.19000000000005,44.650000000000006,262.19000000000005,29.650000000000002]}
 ]}

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) );

bar	COUNT(id_biere)	SUM(stock)
Bar du Coin	8	1370
Corners Pub	2	1600

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 :

Tables

Opérateurs unaires

Opérateurs binaires

Une solution possible :

Tables

Opérateurs unaires

Opérateurs binaires

Votre réponse :

Tables

Opérateurs unaires

Opérateurs binaires

Une solution possible :

Tables

Opérateurs unaires

Opérateurs binaires

Votre réponse :

Tables

Opérateurs unaires

Opérateurs binaires

Une solution possible :

Tables

Opérateurs unaires

Opérateurs binaires

Votre réponse :

Tables

Opérateurs unaires

Opérateurs binaires

Une solution possible :

Tables

Opérateurs unaires

Opérateurs binaires