DS1 requetes

Semestre 3 - Bases de Données Devoir Surveillé n°1 page 1
Bases des Données
Devoir Surveillé n°1 : requêtes SQL
Soit le modèle logique suivant
• personne(id, nom, prenom)
• commande(id, dateCommande, idPersonne)
• ligne(idCommande, numLigne, idProduit, quantite)
• produit(id, nom, prix, idMarque)
Ce MLD permet de définir le schéma physique suivant :
Table « personne »
• id : entier, clé primaire ;
• nom : texte ;
• prenom : texte
Table « commande »
• id : chaîne de 3 caractères, clé primaire ;
• dateCommande : date ;
• idPersonne : entier, clé secondaire
table « ligne »
• idCommande : chaîne de 3 caractères, clé étrangère ;
• numLigne : entier ;
• idProduit : chaîne de 3 caractères, clé étrangère ;
• quantite : entier ;
La clé primaire est composée des champs « idCommande » et « numLigne »
table « produit »
• id : chaîne de 3 caractères, clé primaire ;
• nom : texte ;
• prix : nombre décimal ;
• idMarque : chaîne de 3 caractères
Semestre 3 - Bases de Données Devoir Surveillé n°1 page 2
durée 1 heure
aucun document n’est autorisé
Ecrivez les requêtes SQL permettant d’obtenir les informations
suivantes :
1. Montant total des commandes par marque ;
2. Id et nom des personnes dont le montant total des commandes dépasse
300€
3. Nom et prix des produits qui n’ont jamais été commandés ;
4. Nom des personnes qui ont passé au moins une commande ;
5. Id de la commande la plus récente de chaque personne ;
6. Id des produits dont le prix est supérieur au prix moyen des produits de
la même marque ;
7. Id, nom et prénom des personnes qui n’ont rien commandé ;
8. Nom et prénom des personnes qui ont commandé des écrans plats en
septembre, mais pas octobre ;
9. Id des commandes qui ne concernent pas des écrans ;
10. Liste des produits triés dans l’ordre: de celui dont le prix est le plus
proche de la moyenne, à celui dont le prix est le plus éloigné.
11. Chiffre d’affaire de chaque marque. Les marques pour lesquelles aucun
produit n’a été commandé doivent apparaître avec un chiffre d’affaire
égal à zéro ;
12. Pourcentage de personnes n’ayant passé aucune commande ;
13. Id, nom et marque des produits pour lesquels il existe un autre produit
moins cher et qui porte le même nom ;
14. Nombre de personnes ayant commandé le produit le plus cher ;
15. Id des produits qui apparaissent dans toutes les commandes ;

Semestre 3 - Bases de Données Devoir Surveillé n°1 page 3
Corrigé du Bases des Données
DS n°1 : requêtes SQL
1/-----------------------------------------------------------------------------
select idMarque, sum(l.quantite*pdt.prix)
from ligne l join produit pdt on l.idProduit=pdt.id
group by idMarque;
2/----------------------------------------------------------------------------
select per.id, per.nom,sum(l.quantite*pdt.prix)
from personne per join commande cmd on per.id=cmd.idPersonne
join ligne l on cmd.id=l.idCommande
join produit pdt on l.idProduit=pdt.id
group by per.id,per.nom
having sum(l.quantite*pdt.prix)>300;
3/----------------------------------------------------------------------------
select nom, prix
from produit
where id not in (select idProduit from ligne);
4/----------------------------------------------------------------------------
select distinct nom
from personne per join commande cmd on per.id=cmd.idPersonne;
5/----------------------------------------------------------------------------
select id,dateCommande
from commande c1
where dateCommande = (select max(dateCommande) from commande where
idPersonne=c1.idPersonne);
6/----------------------------------------------------------------------------
select id
from produit pdt1
where prix>(select avg(prix) from produit pdt2 where
pdt1.idMarque=pdt2.idMarque);
7/----------------------------------------------------------------------------
select id,nom,prenom
from personne where id not in (select idPersonne from commande);
8/----------------------------------------------------------------------------
select per.nom, per.prenom
from personne per join commande cmd on per.id=cmd.idPersonne
join ligne l on cmd.id=l.idCommande
join produit pdt on l.idProduit=pdt.id
where pdt.nom='écran plat' and date_part('month',cmd.dateCommande)=9
except
select per.nom, per.prenom
from personne per join commande cmd on per.id=cmd.idPersonne
join ligne l on cmd.id=l.idCommande
join produit pdt on l.idProduit=pdt.id
where pdt.nom='écran plat' and date_part('month',cmd.dateCommande)=10;
Semestre 3 - Bases de Données Devoir Surveillé n°1 page 4
9/----------------------------------------------------------------------------
select id from commande
except
select l.idCommande
from ligne l join produit pdt on l.idProduit=pdt.id
where pdt.nom like'%cran%';
10/----------------------------------------------------------------------------
select id, prix
from produit
order by abs(prix-(select avg(prix) from produit));
11/----------------------------------------------------------------------------
select idMarque, sum(prix*quantite)
from produit join ligne on ligne.idProduit=produit.id
group by idMarque
union
select idMarque,0
from produit
where idMarque not in
(select idMarque
from produit join ligne on ligne.idProduit=produit.id) ;
12/----------------------------------------------------------------------------
select 100*count(*)/(select count(*) from personne)
from personne
where id not in (select idPersonne from commande);
13/----------------------------------------------------------------------------
select p1.id, p1.nom, p1.idMarque
from produit p1 join produit p2 on p1.nom=p2.nom
where p1.prix<p2.prix;
14/----------------------------------------------------------------------------
select count(*)
from personne
where id in (select idPersonne
from commande join ligne on commande.id=ligne.idCommande
join produit on produit.id=ligne.idProduit
where produit.prix=(select max(prix) from produit)
) ;
ou
select count(distinct idPersonne)
from commande join ligne on commande.id=ligne.idCommande
join produit on produit.id=ligne.idProduit
where produit.prix=(select max(prix) from produit) ;
15/----------------------------------------------------------------------------
select id
from produit
where (select count(distinct commande.id)
from commande join ligne on commande.id=ligne.idCommande
where idProduit=produit.id)
=
(select count(*) from commande) ;