SQL : Chapitre 7

-> Pour chaque guide (nom, prénom) affichez les tours qu’il propose (nom, durée, description). Triez les résultats alphabétiquement. Proposer une requête qui utilise JOIN et une autre qui ne l’utilise pas.

​

SELECT u.usr_name, u.usr_surname, t.tour_name, t.tour_duration, t.tour_description
FROM tour t, utilisateur u
WHERE t.tour_guide=u.usr_email
ORDER BY u.usr_name ASC;

​

SELECT u.usr_name, u.usr_surname, t.tour_name, t.tour_duration, t.tour_description
FROM tour t
JOIN utilisateur u
ON t.tour_guide=u.usr_email
ORDER BY u.usr_name ASC;

​

-> Affichez les tours disponibles à LILLE ainsi que des informations sur leurs guides. Triez les tours par dates.

​

SELECT *
FROM tour t, utilisateur u
WHERE t.tour_guide=u.usr_email AND t.tour_city="LILLE"
ORDER BY t.tour_date ASC;

​

-> Pour chaque tour (nom, durée), listez les commentaires et les notes laissés par des participants. Utilisez JOIN et LEFT JOIN pour réussir l’exercice et comparer les résultats.

​

SELECT t.tour_name, t.tour_duration, c.*
FROM tour t
JOIN commentaire c
ON t.tour_id=c.tour_id;

​

SELECT t.tour_name, t.tour_duration, c.*
FROM tour t
LEFT JOIN commentaire c
ON t.tour_id=c.tour_id;

​

Notez que LEFT JOIN permet d'inclure les tour qui n'ont aucun commentaire dans la selection.

​

-> Affichez toutes les réservations en mentionnant le nom du tour, la durée du tour, le prix du tour ainsi que le nom et prénom de l’utilisateur qui a effectué la réservation.

​

SELECT t.tour_name, t.tour_duration, t.tour_price, u.usr_name, u.usr_surname
FROM tour t, booking b, utilisateur u
WHERE b.tour_id=t.tour_id AND b.usr_customer=u.usr_email;

​

SELECT t.tour_name, t.tour_duration, t.tour_price, b.bkg_nb_visitors*t.tour_price AS TOTAL_PRICE, u.usr_name, u.usr_surname
FROM tour t, booking b, utilisateur u
WHERE b.tour_id=t.tour_id AND b.usr_customer=u.usr_email;

​

-> Pour chaque utilisateur (e-mail), affichez ces réservations avec les dates et les prix de réservations. Gardez les utilisateurs sans réservation dans les résultats de votre requête.

​

SELECT u.usr_email, b.bkg_date, b.bkg_nb_visitors*t.tour_price
FROM utilisateur u
LEFT JOIN booking b
ON u.usr_email=b.usr_customer
LEFT JOIN tour t
ON b.tour_id=t.tour_id;

​

-> Pour chaque utilisateur (nom , prénom), affichez ses réservations avec les dates de réservation et le nom du tour réservé. Gardez les utilisateurs sans réservation dans les résultats de votre requête. Affichez l’identifiant de la réservation dans la première colonne et triez le résultat par la référence de la réservation.

​

SELECT b.bkg_id, u.usr_name, u.usr_surname, b.bkg_date, t.tour_name
FROM utilisateur u
LEFT JOIN booking b
ON u.usr_email=b.usr_customer
LEFT JOIN tour t
ON b.tour_id=t.tour_id
ORDER BY -b.bkg_id DESC;

​

SELECT b.bkg_id, u.usr_name, u.usr_surname, b.bkg_date, t.tour_name
FROM tour t
RIGHT JOIN booking b
ON b.tour_id=t.tour_id
RIGHT JOIN utilisateur u
ON u.usr_email=b.usr_customer
ORDER BY -b.bkg_id DESC;

​

-> Sans utiliser de clause JOIN, listez les utilisateurs Premium (nom, prénom, e-mail) qui ont effectué au moins une réservation ?

​

SELECT u.usr_email, u.usr_name, u.usr_surname
FROM utilisateur u, booking b
WHERE u.usr_is_premium=1 
AND  b.usr_customer=u.usr_email;

​

-> Complétez votre requête pour connaitre le nom du tour réservé par ces utilisateurs.

​

SELECT u.usr_email, u.usr_name, u.usr_surname, t.tour_name
FROM utilisateur u, booking b, tour t
WHERE u.usr_is_premium=1 
AND  b.usr_customer=u.usr_email AND b.tour_id=t.tour_id;

​

-> Est-ce possible d’utiliser NATURAL JOIN pour joindre ‘booking’ et ‘utilisateur’ ? Pourquoi ?

​

Vous ne pouvez pas utiliser NATURAL JOIN car les noms d’attributs qui permettent la jointure (clé primaire, clé étrangère) sont différents. Il faut impérativement définir les attributs pour effectuer la jointure.

​

SELECT *
FROM utilisateur u
JOIN booking b
ON u.usr_email=b.usr_customer;

​

Il est possible d'effectuer un NATURAL JOIN de booking et tour (avec tour_id).

​

SELECT * FROM booking
NATURAL JOIN tour;

​

-> Pour chaque utilisateur ayant réservé le tour, affichez son e-mail, le nom du tour ainsi que la dépense totale pour la réservation.

​

SELECT u.usr_email, t.tour_name, b.bkg_nb_visitors*t.tour_price AS "Dépense totale"
FROM tour t, booking b, utilisateur u
WHERE b.tour_id=t.tour_id AND b.usr_customer=u.usr_email;

​