TD3 - SQL, fonctions d'agrégation
Objectifs
- Manipuler des requêtes SQL;
- Comprendre les fonctions d'agrégation.
Au préalable
- Se créer un dossier
Terminale NSIsur votre ordinateur ou clé USB - Dans ce dossier, créer un dossier
BDD - Enregistrer les bases de données dans ce dossier.
TD
L'ensemble du TP se fait sur papier (ou word).
Exercice 1
On donne les tables suivantes :
ventes :
| id_vente | produit | client_id | quantite | montant | date_vente |
|---|---|---|---|---|---|
| 1 | A | 101 | 2 | 20.00 | 2023-11-01 |
| 2 | B | 102 | 1 | 15.00 | 2023-11-01 |
| 3 | A | 103 | 3 | 30.00 | 2023-11-02 |
| 4 | C | 101 | 4 | 40.00 | 2023-11-02 |
| 5 | B | 104 | 2 | 30.00 | 2023-11-03 |
| 6 | A | 105 | 1 | 10.00 | 2023-11-03 |
produits :
| produit | categorie | prix_unitaire |
|---|---|---|
| A | X | 10.00 |
| B | Y | 15.00 |
| C | X | 10.00 |
Exercice
Écrire les requêtes suivantes :
- Calculer le montant total des ventes dans la table
ventes. - Afficher le nombre total de ventes pour chaque produit.
- Calculer la quantité moyenne achetée par client.
- Afficher les produits dont le nombre total de ventes dépasse 3 unités.
- En utilisant la table
produits, calculer le chiffre d’affaires total pour chaque catégorie. - Identifier le produit avec la plus grande quantité totale vendue.
- Lister les clients dont le montant total des achats dépasse 50€.
- Identifier la date où le montant total des ventes a été le plus élevé.
- Calculer le prix moyen des ventes pour chaque produit.
- Trouver la catégorie avec le chiffre d’affaires le plus élevé.
Exercice 2
On dispose des tables suivantes :
clients :
| client_id | nom | ville |
|---|---|---|
| 1 | Dupont | Paris |
| 2 | Martin | Lyon |
| 3 | Durand | Marseille |
| 4 | Lefevre | Paris |
| 5 | Moreau | Lyon |
produits :
| produit_id | nom_produit | catégorie | prix |
|---|---|---|---|
| 1 | Smartphone | Électronique | 500 |
| 2 | Ordinateur | Électronique | 1200 |
| 3 | Table | Mobilier | 300 |
| 4 | Chaise | Mobilier | 150 |
| 5 | Casque | Électronique | 100 |
commandes :
| commande_id | client_id | produit_id | quantite | date_commande |
|---|---|---|---|---|
| 1 | 1 | 1 | 2 | 2024-01-15 |
| 2 | 2 | 3 | 1 | 2024-02-20 |
| 3 | 1 | 2 | 1 | 2024-03-05 |
| 4 | 3 | 4 | 4 | 2024-04-10 |
| 5 | 4 | 5 | 5 | 2024-05-25 |
| 6 | 5 | 1 | 3 | 2024-06-30 |
| 7 | 2 | 2 | 2 | 2024-07-12 |
| 8 | 3 | 5 | 1 | 2024-08-18 |
| 9 | 1 | 3 | 2 | 2024-09-22 |
| 10 | 5 | 4 | 3 | 2024-10-05 |
Exercice
Écrire les requêtes suivantes :
- Écrire une requête SQL qui compte le nombre total de commandes dans la table
commandes. - Calculer le chiffre d'affaires total généré par toutes les commandes. Le chiffre d'affaires est obtenu en multipliant la quantité par le prix du produit.
- Pour chaque client, calculer le montant moyen de ses commandes.
- Déterminer quel produit a été vendu en plus grande quantité totale.
- Lister les clients dont le total des achats dépasse 1000€.
- Pour chaque catégorie de produit, trouver le prix minimum et le prix maximum.
- Calculer combien de clients résident dans chaque ville.
- Identifier les mois où le chiffre d'affaires total a dépassé 1000€.
- Pour chaque produit, calculer la quantité moyenne commandée.
- Lister les commandes dont la quantité est supérieure à la quantité moyenne de toutes les commandes.