Corrigé:
1. SELECT * FROM client;
|
1
|
DUPONT
|
Albert
|
1970-06-01
|
Rue de Crimée
|
69001
|
LYON
|
|
2
|
WEST
|
James
|
1963-09-03
|
Studio
|
0
|
HOLLYWOOD
|
|
3
|
MARTIN
|
Marie
|
1978-06-05
|
Rue des
Acacias
|
69130
|
ECULLY
|
|
4
|
DURAND
|
Gaston
|
1980-11-15
|
Rue de la Meuse
|
69008
|
LYON
|
|
5
|
TITGOUTTE
|
Justine
|
1975-02-28
|
Chemin du Château
|
69630
|
CHAPONOST
|
|
6
|
DUPOND
|
Noémie
|
1957-09-18
|
Rue de Dôle
|
69007
|
LYON
|
2. SELECT * FROM client ORDER BY nom DESC;
|
2
|
WEST
|
James
|
1963-09-03
|
Studio
|
0
|
HOLLYWOOD
|
|
5
|
TITGOUTTE
|
Justine
|
1975-02-28
|
Chemin du Château
|
69630
|
CHAPONOST
|
|
3
|
MARTIN
|
Marie
|
1978-06-05
|
Rue des
Acacias
|
69130
|
ECULLY
|
|
4
|
DURAND
|
Gaston
|
1980-11-15
|
Rue de la Meuse
|
69008
|
LYON
|
|
1
|
DUPONT
|
Albert
|
1970-06-01
|
Rue de Crimée
|
69001
|
LYON
|
|
6
|
DUPOND
|
Noémie
|
1957-09-18
|
Rue de Dôle
|
69007
|
LYON
|
3.
SELECT desi, prixuni/6,57 FROM produit;
|
Soldat qui tire
|
7.6103
|
|
Cochon qui rit
|
7.6103
|
|
Poupée qui pleure
|
15.2207
|
|
Jean
|
38.0517
|
|
Blouson
|
53.2724
|
|
Chaussures
|
30.4414
|
|
T-shirt
|
15.2207
|
|
Table
|
76.1035
|
|
Chaise
|
15.2207
|
|
Armoire
|
152.2070
|
|
Lit
|
761.0350
|
|
Raquette de tennis
|
45.6621
|
|
VTT
|
106.3926
|
|
Ballon
|
11.4155
|
4. SELECT nom, prenom FROM client;
|
DUPONT
|
Albert
|
|
WEST
|
James
|
|
MARTIN
|
Marie
|
|
DURAND
|
Gaston
|
|
TITGOUTTE
|
Justine
|
|
DUPOND
|
Noémie
|
5. SELECT nom, prenom FROM client WHERE ville='LYON';
|
DUPONT
|
Albert
|
|
DURAND
|
Gaston
|
|
DUPOND
|
Noémie
|
6.
SELECT * FROM commande WHERE quantite>=3;
|
1
|
109
|
4
|
1999-09-24
|
|
4
|
104
|
3
|
1999-09-24
|
|
4
|
107
|
5
|
1999-09-24
|
|
5
|
114
|
10
|
1999-09-24
|
|
6
|
103
|
5
|
1999-09-24
|
7.
SELECT desi FROM produit WHERE
prixuni BETWEEN 50 AND 100;
|
Soldat qui tire
|
|
Cochon qui rit
|
|
Poupée qui pleure
|
|
T-shirt
|
|
Chaise
|
|
Ballon
|
8.
SELECT * FROM commande WHERE quantite IS NULL;
|
6
|
114
|
NULL
|
1999-09-24
|
9.
SELECT nom, ville FROM client WHERE ville LIKE '%LL%';
|
WEST
|
HOLLYWOOD
|
|
MARTIN
|
ECULLY
|
10.SELECT prenom FROM client WHERE nom
IN ('DUPONT','DURAND','MARTIN');
|
Albert
|
|
Marie
|
|
Gaston
|
11. SELECT AVG(prixuni) FROM produit;
|
626.714286
|
12. SELECT COUNT(*) FROM commande;
|
13
|
13. SELECT numcli, SUM(quantite) FROM commande GROUP BY numcli;
|
1
|
6
|
|
3
|
3
|
|
4
|
11
|
|
5
|
10
|
|
6
|
7
|
14. SELECT numprod, AVG(quantite) FROM commande GROUP BY numprod HAVING COUNT(numprod)>1;
|
102
|
1.5000
|
|
114
|
10.0000
|