SQL : Chapitre 8

-> Adaptez cette requête pour connaitre l’âge de l’étudiant le plus âgé. Utilisez la fonction max.

​

SELECT max(std_age) FROM student;

​

-> Adaptez cette requête pour connaitre l’âge moyen des étudiants. Utilisez la fonction avg.

​

SELECT avg(std_age) FROM student;

​

-> Count

​

INSERT INTO student
VALUES
(1, 'Stanford', 'Pierre', 'Curie', 'American', 24),
(2, 'PSB', 'Vianney', 'Bureau', 'Français', 20),
(3, 'Princeton', 'Lyman', 'Page', 'French', 26 );

​

Vous devez obtenir le même résultat pour les 3 requêtes count, car count compte le nombre de lignes dans le résultat. Le mot clé DISTINCT permet de compter l'occurrence uniquement des éléments différents du résultat: uniquement le nombre d'étudiants avec le nom différent seront comptés. Pierre Curie et Marie Curie ont le même nom qui sera compté seulement une fois avec count distinct. Pour compter le nombre total d'étudiants, il faut mieux compter leurs identifiants plutôt que leurs noms (clé primaire qui est unique par étudiant).

​

-> Affichez l’âge moyen des étudiants inscrits à Stanford ?

​

SELECT u.unv_name, avg(std_age)
FROM student s, university u
WHERE s.unv_name=u.unv_name 
AND s.unv_name="Stanford";

​

-> Testez la requête suivante contenant un attribut supplémentaire dans la clause SELECT. Pourquoi cette requête ne fonctionne-t-elle pas correctement ?

 

SELECT u.unv_name, count(std_surname), s.std_id

FROM student s, university u

WHERE s.unv_name=u.unv_name  

AND s.unv_name="PSB";

​

La requête doit produire l'erreur suivante:

​

#1140 - In aggregated query without GROUP BY, expression #3 of SELECT list contains nonaggregated column 'education.s.std_id'; this is incompatible with sql_mode=only_full_group_by

​

Cette requête compte le nombre d'étudiants inscrits à PSB. Il est impossible de l'associer avec une liste d'identifiants des étudiants. Il faut soit afficher la liste, soit afficher les données agrégées, mais pas les deux en même temps.

​

SELECT u.unv_name, COUNT(*)
FROM student s, university u
WHERE s.unv_name=u.unv_name AND s.unv_name="PSB";

​

SELECT u.unv_name, s.std_id
FROM student s, university u
WHERE s.unv_name=u.unv_name AND s.unv_name="PSB";

​

Les visites guidées

​

-> Quelle est la durée moyenne et le prix moyen des parcours proposés sur la plateforme ?

​

SELECT AVG(t.tour_duration) AS "Durée moyen", AVG(t.tour_price) AS "Prix moyen"
FROM tour t;

​

-> Quelle est la durée moyenne et le prix moyen des parcours proposés dans chaque ville ?

​

SELECT t.tour_city,
AVG(t.tour_duration) AS "Durée moyen", 
AVG(t.tour_price) AS "Prix moyen"
FROM tour t
GROUP BY t.tour_city;

​

-> Affichez les tours proposés sur la plateforme et comptez le nombre de commentaires et la note moyenne pour chaque tour.

​

SELECT t.*, COUNT(c.cmt_id), AVG(c.cmt_note)
FROM tour t
JOIN commentaire c
ON t.tour_id=c.tour_id
GROUP BY t.tour_id;

​

-> Affichez les tours qui ont une note moyenne supérieure à 4.

​

SELECT t.*, COUNT(c.cmt_id), AVG(c.cmt_note)
FROM tour t
JOIN commentaire c
ON t.tour_id=c.tour_id
GROUP BY t.tour_id
HAVING AVG(c.cmt_note)>4;

​

-> Comparez les requêtes suivantes. Que pouvez-vous en conclure ?

 

SELECT cmt_id, cmt_body

FROM commentaire;

 

SELECT count(cmt_id)

FROM commentaire;

 

SELECT count(cmt_body)

FROM commentaire;

​

Notez que la fonction COUNT ignore les valeurs NULLs.

​

-> Sélectionnez le nombre de réservations effectuées par chaque utilisateur « Premium ».

​

SELECT u.usr_email, COUNT(b.bkg_id)
FROM utilisateur u, booking b
WHERE u.usr_email=b.usr_customer AND u.usr_is_premium=1
GROUP BY b.usr_customer;

​

-> Combien de personnes ont noté un tour sans laisser de commentaires ?

​

SELECT COUNT(DISTINCT c.usr_email)
FROM commentaire c
WHERE c.cmt_body IS NULL;

​

-> Quel est le nombre maximum de personnes concernées par une réservation (bkg_nb_visitors) ?

​

SELECT max(b.bkg_nb_visitors)
FROM booking b;

​

-> Analysez la requête suivante. Qu’est-ce qu’elle permet de sélectionner ?

 

SELECT * FROM booking b
WHERE b.bkg_nb_visitors=(SELECT MAX(b2.bkg_nb_visitors) FROM booking b2);

 

Le deuxième SELECT permet de connaitre le nombre maximum de personnes concernées par une réservation. Le SELECT principat permet de sélectionner les réservations ayant ce nombre maximum de personnes. La clause WHERE b.bkg_nb_visitors compare le nombre de personnes avec le nombre maximum pour faire une sélection. Notez que le résultat du SELECT MAX est un chiffre qui peut être comparé avec le chiffre stocké dans le bkg_nb_visitors de chaque réservation.

​

-> Afficher des informations sur le tour le plus court à Paris.

​

SELECT * FROM tour t
WHERE t.tour_duration=(SELECT MIN(t2.tour_duration) FROM tour t2 WHERE t2.tour_city="PARIS")
AND t.tour_city="PARIS";

​

-> Afficher des informations sur le guide qui propose le tour le plus cher à Paris.

​

SELECT u.*, t.tour_name, t.tour_price FROM tour t, utilisateur u
WHERE t.tour_price=(SELECT MAX(t2.tour_price) FROM tour t2 WHERE t2.tour_city="PARIS")
AND t.tour_city="PARIS" AND t.tour_guide=u.usr_email;

​

-> Pour chaque utilisateur ayant réservé un tour, affichez son courriel ainsi que la dépense totale qu’il a effectuée pour toutes ces réservations.

​

SELECT b.usr_customer, SUM(b.bkg_nb_visitors*t.tour_price)
FROM booking b, tour t
WHERE b.tour_id=t.tour_id
GROUP BY b.usr_customer;

​

-> De combien de guides la plateforme dispose-t-elle dans chaque ville ?

​

SELECT t.tour_city, COUNT(DISTINCT t.tour_guide)
FROM tour t
GROUP BY t.tour_city;

​

-> Sélectionnez le profil d’utilisateur ayant effectué la plus grosse dépense sur la plateforme ?

​

SELECT *
FROM utilisateur u
WHERE u.usr_email=
(SELECT b2.usr_customer FROM booking b2, tour t2 WHERE b2.bkg_nb_visitors*t2.tour_price=
   (SELECT MAX(b.bkg_nb_visitors*t.tour_price)
   FROM booking b, tour t
   WHERE b.tour_id=t.tour_id) 
AND b2.tour_id=t2.tour_id);

​