You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
32 lines
1.3 KiB
32 lines
1.3 KiB
/* Question n°1 */
|
|
|
|
SELECT DISTINCT p.nom
|
|
FROM PRODUIT p
|
|
WHERE p.id_produit NOT IN (SELECT id_produit FROM AVOIR);
|
|
|
|
/* Question n°2 */
|
|
|
|
SELECT c.nom ,l.id_location , p.nom, a.qte
|
|
FROM PRODUIT p, AVOIR a, LOCATION l, LOUER lo, CLIENT c
|
|
WHERE c.authorize_reduc = 'y' AND c.id_client = lo.id_client AND lo.id_location = l.id_location AND a.id_produit = p.id_produit AND a.id_location = l.id_location;
|
|
|
|
/* Question n°3 */
|
|
/* Ici, on supposera que se sera les produits qui ne sont jamais réserver pour différencier la question 3 de la question 1 */
|
|
|
|
SELECT DISTINCT p.nom
|
|
FROM PRODUIT p
|
|
WHERE p.id_produit NOT IN (SELECT id_produit FROM CONTIENT);
|
|
|
|
/* Question n°4 */
|
|
|
|
SELECT round((p.prix * a.qte),2) AS Prix_location, p.nom, initcap(lower(c.nom))
|
|
FROM CLIENT c, LOUER lo, LOCATION l, AVOIR a, PRODUIT p
|
|
WHERE c.type_client = 'Particulier' AND c.id_client = lo.id_client AND lo.id_location = l.id_location AND l.id_location = a.id_location AND a.id_produit = p.id_produit;
|
|
|
|
SELECT round((p.prix * a.qte),2) AS Prix_location, p.nom, initcap(lower(c.nom))
|
|
FROM CLIENT c, LOUER lo, LOCATION l, AVOIR a, PRODUIT p
|
|
WHERE c.type_client = 'Professionel' AND c.id_client = lo.id_client AND lo.id_location = l.id_location AND l.id_location = a.id_location AND a.id_produit = p.id_produit;
|
|
|
|
|
|
|