Exercice SQL 2
Partie 1
Sur la table lpecom_livres
Quelle requête utiliser pour afficher l'ensemble des enregistrements de la table lpecom_livres ?
SELECT *
FROM lpecom_livres;Quelle requête utiliser pour sélectionner uniquement les livres qui ont un prix strictement supérieur à 20 de la table lpecom_livres ?
SELECT *
FROM lpecom_livres
WHERE prix > 20;Quelle requête utiliser pour trier les enregistrements de la table lpecom_livres du prix le plus élevé aux prix le plus bas ?
SELECT *
FROM lpecom_livres
ORDER BY prix DESC;Quelle requête utiliser pour récupérer le prix du livre le plus élevé de la table lpecom_livres ?
SELECT MAX(prix)
FROM lpecom_livres;Quelle requête utiliser pour récupérer les livres de la table lpecom_livres qui ont un prix compris entre 20 et 22 ?
SELECT *
FROM lpecom_livres
WHERE prix BETWEEN 20 AND 22;Quelle requête utiliser pour récupérer tous les livres de la table lpecom_livres à l'exception de celui portant la valeur pour la colonne isbn_10 : 2092589547 ?
SELECT *
FROM lpecom_livres
WHERE isbn_10 != 2092589547;Quelle requête utiliser pour récupérer le prix du livre le moins élevé de la table lpecom_livres en renommant la colonne dans les résultats par minus ?
SELECT MIN(prix) as minus
FROM lpecom_livres;Quelle requête utiliser pour sélectionner uniquement les 3 premiers résultats sans le tout premier de la table lpecom_livres ?
SELECT *
FROM lpecom_livres
LIMIT 3 OFFSET 1;Partie 2
Les tables sont lpecom_etudiants et lpecom_examens
Quelle requête utiliser pour afficher l'id des étudiants qui ont participés à au moins un examen ?
SELECT DISTINCT id_etudiant
FROM lpecom_examens;Quelle requête utiliser pour compter le nombre d'étudiants qui ont participés à au moins un examen ?
SELECT COUNT(DISTINCT id_etudiant)
FROM lpecom_examens;Quelle requête utiliser pour calculer la moyenne de l'examen portant l'id : 45 ?
SELECT AVG(note)
FROM lpecom_examens
WHERE id_examen = 45;Quelle requête utiliser pour récupérer la meilleure note de l'examen portant l'id : 87 ?
SELECT MAX(note)
FROM lpecom_examens
WHERE id_examen = 87;Quelle requête utiliser pour afficher l'id des étudiants qui ont eu plus de 11 à l'examen 45 ou plus de 12 à l'examen 87 ?
SELECT DISTINCT id_etudiant
FROM lpecom_examens
WHERE (id_examen = 45 AND note > 11)
OR (id_examen = 87 AND note > 12);Quelle requête utiliser pour afficher tous les enregistrement de la table lpecom_examens avec en plus, si c'est possible, le prenom et le nom de l'étudiant ?
SELECT ex.*, et.prenom, et.nom
FROM lpecom_examens ex
LEFT JOIN lpecom_etudiants et ON ex.id_etudiant = et.id_etudiant;Quelle requête utiliser pour afficher les enregistrement de la table lpecom_examens avec le prenom et le nom de l'étudiant, uniquement quand les étudiants sont présents dans la table lpecom_etudiants ?
SELECT ex.*, et.prenom, et.nom
FROM lpecom_examens ex
INNER JOIN lpecom_etudiants et ON ex.id_etudiant = et.id_etudiant;Quelle requête utiliser pour afficher uniquement le nom et le prenom de l'étudiant avec l'id : 30 avec la moyenne de ses deux examens dans une colonne moyenne ?
SELECT et.prenom, et.nom, AVG(ex.note) as moyenne
FROM lpecom_examens ex
INNER JOIN lpecom_etudiants et ON ex.id_etudiant = et.id_etudiant
WHERE et.id_etudiant = 30;Quelle requête utiliser pour afficher les 3 meilleures examens, du meilleur au moins bon, avec le prenom et le nom de l'étudiant associé ?
SELECT *
FROM lpecom_examens ex
INNER JOIN lpecom_etudiants et ON ex.id_etudiant = et.id_etudiant
ORDER BY ex.note DESC
LIMIT 3;
Partie 4
Tables lpecom_cities, lpecom_departments, lpecom_regions
Quelle requête utiliser pour retrouver la ville qui possède les coordonnées GPS suivantes : 48.66913724637683, 1.87586057971015 ?
SELECT * FROM lpecom_cities WHERE gps_lat = 48.66913724637683 AND gps_lng = 1.87586057971015;Sans jointure, quelle requête utiliser pour calculer le nombre de villes que compte le département de l'Essonne ?
SELECT COUNT(*) FROM lpecom_cities WHERE department_code = 91;Sans jointure, quelle requête utiliser pour calculer le nombre de villes en Île-de-France se terminant par "-le-Roi" ?
SELECT COUNT(*) FROM lpecom_cities WHERE name LIKE "%-le-Roi";Combien de villes possèdent le code postal (zip_code) 77320 ? Renommez la colonne de résultat n_cities.
SELECT COUNT(*) as n_cities FROM lpecom_cities WHERE zip_code = 77320;Sans jointure, quelle requête utiliser pour calculer le nombre de villes commençant par "Saint-" en Seine-et-Marne ?
SELECT COUNT(*) FROM lpecom_cities WHERE name LIKE "SAINT-%" AND department_code = 77;Sans jointure, quelles sont les deux villes de Seine-et-Marne à avoir le code postal (zip_code) le plus grand ?
SELECT * FROM lpecom_cities WHERE department_code = 77 ORDER BY zip_code DESC LIMIT 2;Quel est le code postal (zip_code) le plus grand de la table lpecom_cities ?
SELECT MAX(zip_code) FROM lpecom_cities;Avec un seul WHERE et aucun OR, quelle est la requête permettant d'afficher les départements des régions ayant le code suivant : 75, 27, 53, 84 et 93 ? Le résultat doit afficher le nom du département ainsi que le nom et le slug de la région associée.
SELECT d.name AS departement, r.name AS region, d.slug
FROM lpecom_departments d
INNER JOIN lpecom_regions r ON (d.region_code = r.code)
WHERE d.region_code IN (75, 27, 53, 84, 93);Quelle requête utiliser pour obtenir en résultat, les noms de la région, du département et de chaque ville du département ayant pour code 77 ?
SELECT r.name as reg, d.name as dep, c.name as ville
FROM lpecom_cities c
INNER JOIN lpecom_departments d ON (c.department_code = d.code)
INNER JOIN lpecom_regions r ON (d.region_code = r.code)
WHERE d.code = 77;Partie 5
Tables lpecom_covid,lpecom_regions
Quelle requête utiliser pour afficher toutes les données de vaccination uniquement pour le 1er avril 2021 ?
SELECT c.*
FROM lpecom_covid c
WHERE jour = '2021-04-01';Quelle requête utiliser pour afficher toutes les données de vaccination uniquement pour le 1er avril 2021 avec le nom de la région concernée ?
SELECT r.name, c.*
FROM lpecom_covid c
INNER JOIN lpecom_regions r ON c.id_region = r.code
WHERE jour = '2021-04-01';Quelle requête utiliser pour afficher le nombre au cumulé de vaccination première dose toutes régions en 2020 ? Proposez également une solution pour les vaccination deuxième dose.
SELECT SUM(n_dose1)
FROM lpecom_covid c
WHERE jour <= '2020-12-31';
SELECT SUM(n_dose2)
FROM lpecom_covid c
WHERE jour <= '2020-12-31';Quelle requête SQL utiliser pour afficher le nombre au cumulé de vaccination première dose pour la région avec le code 93 uniquement pour le mois de mars 2021 ?
SELECT SUM(n_dose1)
FROM lpecom_covid c
WHERE id_region = '93'
AND jour BETWEEN '2021-03-01' AND '2021-03-31';Quelle requête utiliser pour afficher le nombre au cumulé de vaccination deuxième dose pour la région avec le code 11 uniquement pour le mois de mars 2021 ?
SELECT SUM(n_dose2)
FROM lpecom_covid c
WHERE id_region = '11'
AND jour BETWEEN '2021-03-01' AND '2021-03-31';Quelle requête SQL utiliser pour afficher le record de vaccination première dose en une seule journée ? Avec une deuxième requête, afficher les informations de la région concernée, dont son nom, ainsi que le jour du record.
SELECT MAX(n_dose1)
FROM lpecom_covid c;
SELECT c.*, r.name
FROM lpecom_covid c
INNER JOIN lpecom_regions r ON c.id_region = r.code
WHERE c.n_dose1 >= 56661;Quelle requête utiliser pour afficher le record de vaccination deuxième dose en une seule journée ? Avec une deuxième requête, afficher les informations de la région concernée, dont son nom, ainsi que le jour du record.
SELECT MAX(n_dose2)
FROM lpecom_covid c;
SELECT c.*, r.name
FROM lpecom_covid c
INNER JOIN lpecom_regions r ON c.id_region = r.code
WHERE c.n_dose2 >= 21524;
Quelles requêtes permettent de connaitre quelle région possède la plus grande couverture de vaccination avec une dose et deux doses ? Vous aurez besoin de 4 requêtes pour répondre aux deux questions. Vous aurez besoin du résultat de la première requête pour la deuxième.
SELECT MAX(couv_dose1)
FROM lpecom_covid c;
SELECT c.*, r.name
FROM lpecom_covid c
INNER JOIN lpecom_regions r ON c.id_region = r.code
WHERE c.couv_dose1 >= 19.7;
SELECT MAX(couv_dose2)
FROM lpecom_covid c;
SELECT c.*, r.name
FROM lpecom_covid c
INNER JOIN lpecom_regions r ON c.id_region = r.code
WHERE c.couv_dose2 >= 8;
Quelle requête utiliser pour afficher le nom de la région qui a le plus faible taux de couverture de vaccination avec une dose ? Vous aurez besoin de 2 requêtes pour répondre à la question.
SELECT MIN(c.couv_dose1)
FROM lpecom_covid c
WHERE c.jour = '2021-04-06';
SELECT c.*, r.name
FROM lpecom_covid c
INNER JOIN lpecom_regions r ON c.id_region = r.code
WHERE c.jour = '2021-04-06'
AND c.couv_dose1 <= 2.80;
Quelle requête utiliser pour calculer la couverture moyenne entre les différentes régions à la date la plus récente, pour les vaccinations une et deux doses ? Vous renommez les colonnes de résultats : couverture_dose1_avg et couverture_dose2_avg.
SELECT AVG(c.couv_dose1) AS couverture_dose1_avg, AVG(c.couv_dose2) AS couverture_dose2_avg
FROM lpecom_covid c
WHERE c.jour = '2021-04-06';
Quelle requête utiliser pour afficher les données de vaccination des régions (avec leur nom) qui possèdent une couveture vaccinale supérieure à 15 % pour la première dose et supérieure à 5 % pour la deuxième dose ?
SELECT c.*, r.name
FROM lpecom_covid c
INNER JOIN lpecom_regions r ON c.id_region = r.code
WHERE c.couv_dose1 >= 15
AND c.couv_dose2 >= 5
AND c.jour = '2021-04-06';