langage sql 2 - montefiore.ulg.ac.be · langage sql 2 info 0009 - bases de donn ees ulg march 23,...
TRANSCRIPT
![Page 1: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/1.jpg)
Langage SQL 2
INFO 0009 - Bases de donnees
ULg
March 23, 2015
![Page 2: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/2.jpg)
Creation de tables
Format general:
CREATE TABLE IF NOT EXISTS <NOM_TABLE>(
<nom_var1> <type_var1> <option_var1>,
...
<nom_varN> <type_varN> <option_varN>,
PRIMARY KEY(nom_varI),
FOREIGN KEY(nom_varJ) REFERENCES <TABLE>(<nom_var>),
...
FOREIGN KEY(nom_varK) REFERENCES <TABLE>(<nom_var>)
)ENGINE=InnoDB;
Type : INT,BIGINT,CHAR,VARCHAR,DATE,...Options : NOT NULL, UNIQUE, PRIMARY KEY,AUTO INCREMENT,...
![Page 3: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/3.jpg)
Exercices
Creer les tables suivantes:
I Client(id client, prenom client, nom client, nationalite client)
I Commande(id commande,id client, date commande)
I Produit(id produit, nom produit)
I Employe(id employe, nom employe, prenom employe)
I DetailsCommande(id commande, id produit, id employe,quantite, prix piece)
I Paiement(id client, id commande, montant)
![Page 4: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/4.jpg)
Creation de tables
Client(id client, prenom client, nom client, nationalite client)
CREATE TABLE IF NOT EXISTS Client(
![Page 5: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/5.jpg)
Creation de tables
Client(id client, prenom client, nom client, nationalite client)
CREATE TABLE IF NOT EXISTS Client(
id_client INT AUTO_INCREMENT,
![Page 6: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/6.jpg)
Creation de tables
Client(id client, prenom client, nom client, nationalite client)
CREATE TABLE IF NOT EXISTS Client(
id_client INT AUTO_INCREMENT,
prenom_client VARCHAR(50) NOT NULL,
![Page 7: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/7.jpg)
Creation de tables
Client(id client, prenom client, nom client, nationalite client)
CREATE TABLE IF NOT EXISTS Client(
id_client INT AUTO_INCREMENT,
prenom_client VARCHAR(50) NOT NULL,
nom_client VARCHAR(50) NOT NULL,
![Page 8: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/8.jpg)
Creation de tables
Client(id client, prenom client, nom client, nationalite client)
CREATE TABLE IF NOT EXISTS Client(
id_client INT AUTO_INCREMENT,
prenom_client VARCHAR(50) NOT NULL,
nom_client VARCHAR(50) NOT NULL,
nationalite_client VARCHAR(50) NOT NULL,
![Page 9: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/9.jpg)
Creation de tables
Client(id client, prenom client, nom client, nationalite client)
CREATE TABLE IF NOT EXISTS Client(
id_client INT AUTO_INCREMENT,
prenom_client VARCHAR(50) NOT NULL,
nom_client VARCHAR(50) NOT NULL,
nationalite_client VARCHAR(50) NOT NULL,
PRIMARY KEY(id_client)
![Page 10: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/10.jpg)
Creation de tables
Client(id client, prenom client, nom client, nationalite client)
CREATE TABLE IF NOT EXISTS Client(
id_client INT AUTO_INCREMENT,
prenom_client VARCHAR(50) NOT NULL,
nom_client VARCHAR(50) NOT NULL,
nationalite_client VARCHAR(50) NOT NULL,
PRIMARY KEY(id_client)
)ENGINE=InnoDB;
![Page 11: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/11.jpg)
Exercices
Creer les tables suivantes:
I Commande(id commande,id client, date commande)
I Produit(id produit, nom produit)
I Employe(id employe, nom employe, prenom employe)
I DetailsCommande(id commande, id produit, id employe,quantite, prix piece)
I Paiement(id client, id commande, montant)
![Page 12: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/12.jpg)
Commande(id commande, id client, date commande)
CREATE TABLE IF NOT EXISTS Commande(
id_commande INT AUTO_INCREMENT,
id_client INT NOT NULL,
date_commande DATE NOT NULL,
PRIMARY KEY(id_commande),
FOREIGN KEY(id_client) REFERENCES Client(id_client)
)ENGINE=InnoDB;
![Page 13: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/13.jpg)
Produit(id produit, nom produit)
CREATE TABLE IF NOT EXISTS Produit(
id_produit INT AUTO_INCREMENT,
nom_produit VARCHAR(50) NOT NULL,
PRIMARY KEY(id_produit)
)ENGINE=InnoDB;
![Page 14: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/14.jpg)
Employe(id employe, nom employe, prenom employe)
CREATE TABLE IF NOT EXISTS Employe(
id_employe INT AUTO_INCREMENT,
nom_employe VARCHAR(50),
prenom_employe VARCHAR(50),
PRIMARY KEY(id_employe)
)ENGINE=InnoDB;
![Page 15: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/15.jpg)
DetailsCommande(id commande,id produit,id employe,quantite,prix piece)
CREATE TABLE IF NOT EXISTS DetailsCommande(
id_commande INT NOT NULL,
id_produit INT NOT NULL,
id_employe INT NOT NULL,
quantite INT NOT NULL,
prix_piece FLOAT NOT NULL,
PRIMARY KEY (id_commande,id_produit),
FOREIGN KEY(id_commande)
REFERENCES Commande(id_commande),
FOREIGN KEY(id_produit)
REFERENCES Produit(id_produit),
FOREIGN KEY(id_employe)
REFERENCES Employe(id_employe)
)ENGINE=InnoDB;
![Page 16: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/16.jpg)
Paiement(id client, id commande , montant)
CREATE TABLE IF NOT EXISTS Paiement(
id_client INT NOT NULL,
id_commande INT NOT NULL,
montant FLOAT NOT NULL,
PRIMARY KEY (id_client, id_commande),
FOREIGN KEY(id_client) REFERENCES Client(id_client),
FOREIGN KEY(id_commande)
REFERENCES Commande(id_commande)
)ENGINE=InnoDB;
![Page 17: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/17.jpg)
Requetes
![Page 18: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/18.jpg)
Requetes
SELECT *
FROM <nom_table>
Selectionne tous les tuples de la table
![Page 19: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/19.jpg)
Requetes
Table ETUDIANT:Nom Prenom Age Points
Dupont Georges 17 12Dupont Henri 16 13Durant Francis 17 15Durant Gustave 17 14Proviste Alain 14 12
SELECT *
FROM ETUDIANTS
Nom Prenom Age PointsDupont Georges 17 12Dupont Henri 16 13Durant Francis 17 15Durant Gustave 17 14Proviste Alain 14 12
![Page 20: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/20.jpg)
Requetes
SELECT <col_i>,...,<col_k>
FROM <nom_table>
Selectionne tous les tuples de la tableLes tuples sont composes des colonnes specifiees
![Page 21: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/21.jpg)
Requetes
Table ETUDIANT:Nom Prenom Age Points
Dupont Georges 17 12Dupont Henri 16 13Durant Francis 17 15Durant Gustave 17 14Proviste Alain 14 12
SELECT Nom,Prenom
FROM ETUDIANTS
Nom PrenomDupont GeorgesDupont HenriDurant FrancisDurant GustaveProviste Alain
![Page 22: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/22.jpg)
Requetes
SELECT DISTINCT *
FROM <nom_table>
DISTINCT permet de selectionner tous les tuples differents de latable
![Page 23: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/23.jpg)
Requetes
Table ETUDIANT:Nom Prenom Age Points
Dupont Georges 17 12Dupont Henri 16 13Durant Francis 17 15Durant Gustave 17 14Proviste Alain 14 12
SELECT DISTINCT Nom
FROM ETUDIANTS
NomDupontDurantProviste
![Page 24: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/24.jpg)
Requetes
SELECT COUNT *
FROM <nom_table>
COUNT compte le nombre de tuples de la table
![Page 25: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/25.jpg)
Requetes
Table ETUDIANT:Nom Prenom Age Points
Dupont Georges 17 12Dupont Henri 16 13Durant Francis 17 15Durant Gustave 17 14Proviste Alain 14 12
SELECT COUNT(DISTINCT Name)
FROM ETUDIANTS
3(Dupont, Durant, Proviste)
![Page 26: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/26.jpg)
Requetes
SELECT *
FROM <nom_table>
WHERE CSTR(column)
WHERE impose des contraintes sur les tuples en fonction desvaleurs des colonnes
![Page 27: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/27.jpg)
Requetes
Table ETUDIANT:Nom Prenom Age Points
Dupont Georges 17 12Dupont Henri 16 13Durant Francis 17 15Durant Gustave 17 14Proviste Alain 14 12
SELECT *
FROM ETUDIANTS
WHERE Nom="Dupont"
Nom Prenom Age PointsDupont Georges 17 12Dupont Henri 16 13
![Page 28: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/28.jpg)
Requetes
SELECT *
FROM <nom_table_1> NATURAL JOIN <nom_table_2>
NATURAL JOIN est le produit cartesien entre deux tables base surles noms de colonnes communs
![Page 29: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/29.jpg)
Requetes
TABLE ETUDIANTS: TABLE INTERROS:
ID Nom Prenom1 Proviste Alain2 Durant Georges
ID Cours Points1 Math 171 Francais 102 Geographie 142 Francais 12
SELECT *
FROM ETUDIANTS NATURAL JOIN INTERROS
ID Nom Prenom Cours Points1 Proviste Alain Math 171 Proviste Alain Francais 122 Durant Georges Geographie 142 Durant Georges Francais 12
![Page 30: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/30.jpg)
Requetes
TABLE ETUDIANTS: TABLE INTERROS:
ID Nom Prenom1 Proviste Alain2 Durant Georges
ID Cours Points1 Math 171 Francais 102 Geographie 142 Francais 12
SELECT DISTINCT Nom,Prenom
FROM ETUDIANTS NATURAL JOIN INTERROS
WHERE Points >= 14
Nom PrenomProviste AlainDurant Georges
![Page 31: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/31.jpg)
Requetes
TABLE ETUDIANTS: TABLE INTERROS:
ID Nom Prenom1 Proviste Alain2 Durant Georges
ID Cours Points1 Math 171 Francais 102 Geographie 142 Francais 12
SELECT DISCTINCT NOM,PRENOM
FROM ETUDIANTS
WHERE ID IN
(SELECT ID FROM INTERROS
WHERE Points>=14)
Nom PrenomProviste AlainDurant Georges
![Page 32: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/32.jpg)
Requetes
SELECT *
FROM <NOM_TABLE>
GROUP BY <colonne>
GROUP BY regroupe les resultats par une ou plusieurs colonnes
![Page 33: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/33.jpg)
Requetes
TABLE ETUDIANTS:Nom Prenom Age Points
Dupont Georges 17 12Dupont Henri 16 13Durant Francis 17 15Durant Gustave 17 14Proviste Alain 14 12
SELECT Nom, SUM(Age), AVG(Points)
FROM ETUDIANTS
GROUP BY Nom
Nom SUM(Age) AVG(Points)Dupont 33 12.5Durant 34 14.5Proviste 14 12
![Page 34: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/34.jpg)
Exercices
I Client(id client, prenom client, nom client, nationalite client)
I Commande(id commande,id client, date commande)
I Produit(id produit, nom produit)
I Employe(id employe, nom employe, prenom employe)
I DetailsCommande(id commande, id produit, id employe,quantite, prix piece)
I Paiement(id client, id commande, montant)
a) Pour chaque client, donner le nombre de commandes passeesb) Chercher les commandes passees par des clients belges oufrancaisc) Donner le cout total pour chaque commanded) Pour chaque client, donner le cout total de ses commandese) Pour chaque client, donner le cout moyen de ses commandes
![Page 35: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/35.jpg)
a) Pour chaque client, donner le nombre de commandespassees
1. Les clients qui ont passe au moins une commande
SELECT prenom_client, nom_client, COUNT(*) AS cnt
FROM (Client NATURAL JOIN Commande)
GROUP BY id_client;
2. Les clients qui n’ont pas passe de commande
SELECT prenom_client, nom_client, 0 AS cnt
FROM Client
WHERE id_client NOT IN
(SELECT DISTINCT id_client
FROM Commande)
![Page 36: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/36.jpg)
a) Pour chaque client, donner le nombre de commandespassees
3. Union
(SELECT prenom_client, nom_client, COUNT(*) AS cnt
FROM (Client NATURAL JOIN Commande)
GROUP BY id_client)
UNION
(SELECT prenom_client, nom_client, 0 AS cnt
FROM Client
WHERE id_client NOT IN
(SELECT DISTINCT id_client
FROM Commande));
![Page 37: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/37.jpg)
b) Chercher les commandes passees par des clients belgesou francais
SELECT id_commande
FROM (Client NATURAL JOIN Commande)
WHERE nationalite_client="Belge"
OR nationalite_client="Francais";
![Page 38: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/38.jpg)
c) Donner le cout total pour chaque commande
SELECT id_commande, SUM(prix_piece*quantite)
FROM DetailsCommande
GROUP BY id_commande;
![Page 39: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/39.jpg)
d) Pour chaque client, donner le cout total de sescommandes
(SELECT prenom_client, nom_client,
SUM(prix_piece*quantite) as cout_total
FROM (DetailsCommande NATURAL JOIN Commande)
NATURAL JOIN Client
GROUP BY id_client)
UNION
(SELECT prenom_client, nom_client, 0 as cout_total
FROM Client
WHERE id_client NOT IN
(SELECT DISTINCT id_client FROM Commande));
![Page 40: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/40.jpg)
e) Pour chaque client, donner le cout moyen de sescommandes
SELECT prenom_client, nom_client, AVG(prix_piece*quantite)
FROM (DetailsCommande NATURAL JOIN Commande)
NATURAL JOIN Client
GROUP BY id_client;
Ne pas oublier ceux qui n’ont rien commande... (voir d))
![Page 41: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/41.jpg)
Exercices
I Client(id client, prenom client, nom client, nationalite client)
I Commande(id commande,id client, date commande)
I Produit(id produit, nom produit)
I Employe(id employe, nom employe, prenom employe)
I DetailsCommande(id commande, id produit, id employe,quantite, prix piece)
I Paiement(id client, id commande, montant)
f) Donner les produits qui n’ont pas encore ete commandesg) Donner les produits commandes par tout le mondeh) Donner les produits commandes par au moins 30 clientsi) Pour les clients qui n’ont pas encore paye toutes leurscommandes, donner la somme totale des ses commandes, lasomme qu’ils ont paye, et la somme qui reste a etre payeej) Pour chaque produit, donner le nombre total de piecescommandees
![Page 42: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/42.jpg)
f) Donner les produits qui n’ont pas encore ete commandes
SELECT nom_produit
FROM Produit
WHERE id_produit NOT IN
(SELECT DISTINCT id_produit
FROM DetailsCommande)
![Page 43: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/43.jpg)
g) Donner les produits commandes par tout le monde
1. Trouver toutes les commandes d’un client c pour un produit p
SELECT *
FROM DetailsCommande NATURAL JOIN Commande
WHERE id_client=c and id_produit = p;
![Page 44: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/44.jpg)
g) Donner les produits commandes par tout le monde
2. Trouver tous les clients n’ayant pas commande un produit p
SELECT id_client
FROM Client C
WHERE NOT EXISTS
(SELECT *
FROM DetailsCommande NATURAL JOIN Commande
WHERE id_client=C.id_client and id_produit = p);
![Page 45: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/45.jpg)
g) Donner les produits commandes par tout le monde
3. Trouver tous les produits pour lequel il n’existe pas un client quine l’a pas commande
SELECT nom_produit FROM Produit P
WHERE NOT EXISTS
(SELECT id_client
FROM Client C
WHERE NOT EXISTS
(SELECT *
FROM DetailsCommande NATURAL JOIN Commande
WHERE id_client=C.id_client
AND id_produit = P.id_produit));
![Page 46: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/46.jpg)
h) Donner les produits commandes par au moins 30 clients
SELECT nom_produit
FROM Commande NATURAL JOIN DetailsCommande
NATURAL JOIN Produit
GROUP BY id_produit
HAVING COUNT(DISTINCT id_client)>=30;
![Page 47: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/47.jpg)
i) Pour les clients qui n’ont pas encore paye toutes leurscommandes, donner la somme totale des ses commandes,la somme qu’ils ont paye, et la somme qui reste a etrepayee
1. Regrouper le prix de chaque commande, et ce qui a ete paye
SELECT id_client,
SUM(prix_piece*quantite) AS prix_commande,
AVG(montant) AS paye
FROM DetailsCommande NATURAL JOIN Paiement
GROUP BY id_commande
![Page 48: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/48.jpg)
i) Pour les clients qui n’ont pas encore paye toutes leurscommandes, donner la somme totale des ses commandes,la somme qu’ils ont paye, et la somme qui reste a etrepayee
2. Regrouper la table precedente client par client
SELECT id_client,SUM(prix_commande),SUM(paye),
SUM(prix_commande)-SUM(paye)
FROM
(SELECT id_client,
SUM(prix_piece*quantite) AS prix_commande,
AVG(montant) AS paye
FROM DetailsCommande NATURAL JOIN Paiement
GROUP BY id_commande)
GROUP BY id_client
HAVING SUM(prix_commande)>SUM(paye)
![Page 49: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/49.jpg)
j) Pour chaque produit, donner le nombre total de piecescommandees
((SELECT nom_produit,SUM(quantite) as total
FROM Produit NATURAL JOIN DetailsCommande
GROUP BY id_produit)
UNION
(SELECT nom_produit, 0 as total
FROM Produit
WHERE id_produit NOT IN
SELECT DISTINCT id_produit FROM Commande))
![Page 50: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/50.jpg)
I Client(id client, prenom client, nom client, nationalite client)
I Commande(id commande,id client, date commande)
I Produit(id produit, nom produit)
I Employe(id employe, nom employe, prenom employe)
I DetailsCommande(id commande, id produit, id employe,quantite, prix piece)
I Paiement(id client, id commande, montant)
k) Donner le produit le + commande (indice : ORDER BY, LIMIT)l) Pour chaque produit, donner la valeur totale des piecescommandeesm) Donner le nombre de produits par employen) Donner le nombre d’employes travaillant pour chaque client
![Page 51: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/51.jpg)
k) Donner le produit le + commande
SELECT nom_produit,SUM(quantite)
FROM Produit NATURAL JOIN DetailsCommande
GROUP BY id_produit
ORDER BY SUM(quantite) DESC
LIMIT 1;
![Page 52: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/52.jpg)
l) Pour chaque produit, donner la valeur totale des piecescommandees
SELECT nom_produit,SUM(quantite*prix_piece)
FROM Produit NATURAL JOIN DetailsCommande
GROUP BY id_produit;
Ne pas oublier les produits qui n’ont pas ete commande (voir j))
![Page 53: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/53.jpg)
m) Donner le nombre de produits par employe
(SELECT nom_employe,SUM(quantite) as n◦_par_employeFROM Employe NATURAL JOIN DetailsCommande
GROUP BY id_employe)
UNION
(SELECT nom_employe, 0 as n_par_employe
FROM Employe
WHERE id_employe NOT IN
(SELECT DISTINCT id_employe FROM DetailsCommande));
![Page 54: Langage SQL 2 - montefiore.ulg.ac.be · Langage SQL 2 INFO 0009 - Bases de donn ees ULg March 23, 2015](https://reader031.vdocuments.us/reader031/viewer/2022020120/5b46e5737f8b9a40638b983c/html5/thumbnails/54.jpg)
n) Donner le nombre d’employes travaillant pour chaqueclient
SELECT prenom_client,nom_client,COUNT(DISTINCT id_employe)
FROM DetailsCommande NATURAL JOIN Commande
NATURAL JOIN Client
GROUP BY id_client;
Ne pas oublier ceux qui n’ont rien commande (voir a)).