DS1 requetes

Bases des Données
Devoir Surveillé n°1 : requêtes SQL
durée 1 heure
aucun document n’est autorisé
Soit le modèle logique suivant
Ecrivez les
suivantes :
requêtes
SQL
permettant
d’obtenir
les
informations
•
personne(id, nom, prenom)
•
commande(id, dateCommande, idPersonne)
•
ligne(idCommande, numLigne, idProduit, quantite)
1. Montant total des commandes par marque ;
produit(id, nom, prix, idMarque)
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 ;
Ce MLD permet de définir le schéma physique suivant :
4. Nom des personnes qui ont passé au moins une commande ;
Table « personne »
•
id : entier, clé primaire ;
•
nom : texte ;
•
prenom : texte
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é ;
Table « commande »
•
id : chaîne de 3 caractères, clé primaire ;
8. Nom et prénom des personnes qui ont commandé des écrans plats en
septembre, mais pas octobre ;
•
dateCommande : date ;
9. Id des commandes qui ne concernent pas des écrans ;
•
idPersonne : entier, clé secondaire
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é.
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 ;
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 ;
La clé primaire est composée des champs « idCommande » et « numLigne »
13. Id, nom et marque des produits pour lesquels il existe un autre produit
moins cher et qui porte le même nom ;
table « produit »
14. Nombre de personnes ayant commandé le produit le plus cher ;
•
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
15. Id des produits qui apparaissent dans toutes les commandes ;
page 1
Semestre 3 - Bases de Données
Devoir Surveillé n°1
page 2
9/----------------------------------------------------------------------------
Corrigé du Bases des Données
DS n°1 : requêtes SQL
select id from commande
except
select l.idCommande
from ligne l join produit pdt on l.idProduit=pdt.id
where pdt.nom like'%cran%';
1/-----------------------------------------------------------------------------
10/----------------------------------------------------------------------------
select idMarque, sum(l.quantite*pdt.prix)
from ligne l join produit pdt on l.idProduit=pdt.id
group by idMarque;
select id, prix
from produit
order by abs(prix-(select avg(prix) from produit));
2/----------------------------------------------------------------------------
11/----------------------------------------------------------------------------
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;
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) ;
3/---------------------------------------------------------------------------select nom, prix
from produit
where id not in (select idProduit from ligne);
12/----------------------------------------------------------------------------
4/---------------------------------------------------------------------------select distinct nom
from personne per join commande cmd on per.id=cmd.idPersonne;
select 100*count(*)/(select count(*) from personne)
from personne
where id not in (select idPersonne from commande);
5/----------------------------------------------------------------------------
13/----------------------------------------------------------------------------
select id,dateCommande
from commande c1
where dateCommande = (select max(dateCommande) from commande where
idPersonne=c1.idPersonne);
14/----------------------------------------------------------------------------
select p1.id, p1.nom, p1.idMarque
from produit p1 join produit p2 on p1.nom=p2.nom
where p1.prix<p2.prix;
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)
) ;
6/---------------------------------------------------------------------------select id
from produit pdt1
where prix>(select avg(prix) from produit pdt2 where
pdt1.idMarque=pdt2.idMarque);
7/----------------------------------------------------------------------------
ou
select id,nom,prenom
from personne where id not in (select idPersonne from commande);
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) ;
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
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) ;
page 3
Semestre 3 - Bases de Données
Devoir Surveillé n°1
page 4