# 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` ? ```SQL 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 ? ```SQL 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` ? ```SQL 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 ? ```SQL 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** ? ```SQL 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` ? ```SQL 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 ? ```SQL 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 ? ```SQL SELECT COUNT(DISTINCT id_etudiant) FROM lpecom_examens; ``` Quelle requête utiliser pour calculer la moyenne de l'examen portant l'id : **45** ? ```SQL 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** ? ```SQL 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** ? ```SQL 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 ? ```SQL 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 ? ```SQL 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** ? ```SQL 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é ? ```SQL 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 3 Tables lpecom\_realisateurs, lpecom\_films, lpecom\_films\_notes Quel est le résultat de la requête ci-dessous ? ```SQL SELECT id, prenom, nom FROM lpecom_realisateurs WHERE nation = "us" AND sexe = 1; ```
idprenomnom
47PattyJenkins
Quel est le résultat de la requête ci-dessous ? ```SQL SELECT * FROM lpecom_realisateurs WHERE sexe = "0" ORDER BY nom DESC LIMIT 1; ```
idnomprenomsexenation
16ScottRidley0uk
Quel est le résultat de la requête ci-dessous ? ```SQL SELECT f.id, f.nom AS film, r.prenom, r.nom FROM lpecom_films f INNER JOIN lpecom_realisateurs r ON f.id_realisateur = r.id ORDER BY f.id ASC; ```
idfilmprenomnom
121Requiem for a DreamDarrenAronofsky
546GladiatorRidleyScott
775Blade RunnerRidleyScott
984Seul sur MarsRidleyScott
986Black SwanDarrenAronofsky
987Wonder WomanPattyJenkins
Quel est le résultat de la requête ci-dessous ? ```SQL SELECT f.id, f.nom AS film, r.prenom, r.nom FROM lpecom_films f LEFT JOIN lpecom_realisateurs r ON f.id_realisateur = r.id ORDER BY f.id ASC; ```
idfilmprenomnom
121Requiem for a DreamDarrenAronofsky
546GladiatorRidleyScott
666Fight Club
775Blade RunnerRidleyScott
984Seul sur MarsRidleyScott
986Black SwanDarrenAronofsky
987Wonder WomanPattyJenkins
988The Tomorrow Man
Quel est le résultat de la requête ci-dessous ? ```SQL SELECT f.id, f.nom, fn.note FROM lpecom_films f LEFT JOIN lpecom_films_notes fn ON f.id = fn.id_film ORDER BY f.id ASC; ```
idnomnote
121Requiem for a Dream1
546Gladiator4.5
546Gladiator2.5
666Fight Club4.2
775Blade Runner5
984Seul sur Mars3.5
986Black Swan4.3
986Black Swan3
987Wonder Woman3.1
988The Tomorrow Man
Quel est le résultat de la requête ci-dessous ? ```SQL SELECT f.nom, r.prenom AS realisateur_prenom, r.nom AS realisateur_nom, AVG(fn.note) AS moyenne_note FROM lpecom_films f INNER JOIN lpecom_realisateurs r ON f.id_realisateur = r.id INNER JOIN lpecom_films_notes fn ON f.id = fn.id_film WHERE f.id = 546; ```
nomrealisateur\_prenomrealisateur\_nommoyenne\_note
GladiatorRidleyScott3.5
Quel est le résultat de la requête ci-dessous ? ```SQL SELECT r.nation, AVG(fn.note) AS moyenne_note FROM lpecom_films f INNER JOIN lpecom_realisateurs r ON f.id_realisateur = r.id INNER JOIN lpecom_films_notes fn ON f.id = fn.id_film WHERE r.nation = "us"; ```
nationmoyenne\_note
us2.85
Quel est le résultat de la requête ci-dessous ? ```SQL SELECT r.nation, MAX(fn.note) AS max_note FROM lpecom_films f INNER JOIN lpecom_realisateurs r ON f.id_realisateur = r.id INNER JOIN lpecom_films_notes fn ON f.id = fn.id_film WHERE r.nation = "uk"; ```
nationmax\_note
uk5
## ## 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** ? ```SQL 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** ? ```SQL 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**" ? ```SQL 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`. ```SQL 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** ? ```SQL 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 ? ```SQL 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` ? ```SQL 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. ```SQL 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** ? ```SQL 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** ? ```SQL 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 ? ```SQL 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. ```SQL 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** ? ```SQL 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** ? ```SQL 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. ```SQL 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. ```SQL 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. ```SQL 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. ```SQL 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`. ```SQL 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 ? ```SQL 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'; ``` ## Partie 6 table lpecom\_departments,lpecom\_covid\_vaccin\_type,lpecom\_covid\_vaccin Sans jointure, quelle requête SQL utiliser pour afficher **toutes les données de vaccination** du **14 février 2021** uniquement, pour le département de **Seine-et-Marne (77)** ? ```SQL SELECT * FROM lpecom_covid_vaccin v WHERE v.jour = '2021-02-14' AND v.dep_code = 77; ``` Sans jointure, quelle requête SQL utiliser pour afficher **le cumul de toutes les données de vaccination pour tous les vaccins** du **14 février 2021** uniquement, pour les départements de l'**Essonne (91)** et de la **Seine-et-Marne (77)** ? ```SQL SELECT * FROM lpecom_covid_vaccin v WHERE v.jour = '2021-02-14' AND v.dep_code IN (77, 91) AND v.vaccin = 0; ``` Sans jointure, quelle requête utiliser pour afficher la **somme des vaccinations première dose** réalisées uniquement avec le vaccin **AstraZeneka** pour le mois de **février 2021** pour le département de la **Seine-et-Marne (77)** ? ```SQL SELECT SUM(v.n_dose1) FROM lpecom_covid_vaccin v WHERE v.dep_code = 77 AND v.jour BETWEEN '2021-02-01' AND '2021-02-31' AND v.vaccin = 3; ``` Sans jointure, quelle requête utiliser pour afficher la **somme des vaccinations deuxième dose** réalisées avec le vaccin **AstraZeneka** ou **Moderna** pour le mois de **mars 2021** pour le département de la **Seine-et-Marne (77)** ? ```SQL SELECT SUM(v.n_dose2) FROM lpecom_covid_vaccin v WHERE v.dep_code = 77 AND v.jour BETWEEN '2021-03-01' AND '2021-03-31' AND v.vaccin IN (2, 3); ``` Sans jointure, quelle requête utiliser pour afficher le **record de vaccination première dose** avec un type de vaccin en **une seule journée** ? Avec une deuxième requête qui exploitera une jointure, afficher toutes les informations possibles pour cette journée record et sur le type de vaccin. ```SQL SELECT MAX(v.n_dose1) FROM lpecom_covid_vaccin v WHERE v.vaccin != 0; SELECT * FROM lpecom_covid_vaccin v INNER JOIN lpecom_covid_vaccin_type t ON t.id = v.vaccin WHERE v.vaccin != 0 AND v.n_dose1 >= 7494; ``` Sans jointure, quelle requête utiliser pour afficher **le record de vaccination deuxième dose** avec un type de vaccin en une **seule journée** ? Avec une deuxième requête qui exploitera deux jointures, afficher toutes les informations possibles pour cette journée record, sur le type de vaccin et sur le département. ```SQL SELECT MAX(v.n_dose2) FROM lpecom_covid_vaccin v WHERE v.vaccin != 0; SELECT * FROM lpecom_covid_vaccin v INNER JOIN lpecom_covid_vaccin_type t ON t.id = v.vaccin INNER JOIN lpecom_departments d ON d.code = v.dep_code WHERE v.vaccin != 0 AND v.n_dose2 >= 5046; ``` Quelle requête permet de savoir quel département possède le **plus grand nombre d'injections première dose** pour le vaccin **AstraZeneka** ? Avec une deuxième requête, afficher uniquement les colonnes suivantes : - le nom du vaccin ; - le jour ; - le nom et le code du département ; - le nombre cumulé d'injections. ```SQL SELECT MAX(v.n_cum_dose1) FROM lpecom_covid_vaccin v INNER JOIN lpecom_covid_vaccin_type t ON t.id = v.vaccin INNER JOIN lpecom_departments d ON d.code = v.dep_code WHERE jour = '2021-04-06' AND v.vaccin = 3; SELECT v.jour, t.nom, v.n_cum_dose1, d.code, d.name FROM lpecom_covid_vaccin v INNER JOIN lpecom_covid_vaccin_type t ON t.id = v.vaccin INNER JOIN lpecom_departments d ON d.code = v.dep_code WHERE jour = '2021-04-06' AND v.vaccin = 3 AND v.n_cum_dose1 >= 122709; ``` Quelle requête permet de savoir quel département a eu le **moins de vaccinations première dose** avec le vaccin **COMIRNATY Pfizer/BioNTech** ? Avec une deuxième requête, afficher uniquement les colonnes suivantes : - le nom du vaccin ; - le jour ; - le nom et le code du département ; - le nombre cumulé d'injections. ```SQL SELECT MIN(v.n_cum_dose1) FROM lpecom_covid_vaccin v INNER JOIN lpecom_covid_vaccin_type t ON t.id = v.vaccin INNER JOIN lpecom_departments d ON d.code = v.dep_code WHERE jour = '2021-04-06' AND vaccin = 1; SELECT v.jour, t.nom, v.n_cum_dose1, d.code, d.name FROM lpecom_covid_vaccin v INNER JOIN lpecom_covid_vaccin_type t ON t.id = v.vaccin INNER JOIN lpecom_departments d ON d.code = v.dep_code WHERE jour = '2021-04-06' AND v.vaccin = 1 AND v.n_cum_dose1 <= 90832; ``` Quelle requête permet de connaître la moyenne de vaccinations **première dose** dans tous les départements pour le vaccin **Moderna** ? Renommer la colonne de résultat avec `avg_moderna`. ```SQL SELECT AVG(n_cum_dose1) AS avg_moderna FROM lpecom_covid_vaccin v INNER JOIN lpecom_covid_vaccin_type t ON t.id = v.vaccin INNER JOIN lpecom_departments d ON d.code = v.dep_code WHERE v.jour = '2021-04-06' AND v.vaccin = 2; ``` Quelle requête utiliser pour afficher les départements (avec leur nom) qui possèdent un nombre d'injections deuxième dose avec le vaccin **Moderna** supérieur à **9000** ou un nombre d'injections première dose avec le vaccin **COMIRNATY Pfizer/BioNTech** supérieur à **120000** ? Vous aurez besoin de deux jointures. ```SQL SELECT v.jour, t.nom, v.n_cum_dose1, d.code, d.name FROM lpecom_covid_vaccin v INNER JOIN lpecom_covid_vaccin_type t ON t.id = v.vaccin INNER JOIN lpecom_departments d ON d.code = v.dep_code WHERE (v.jour = '2021-04-06' AND v.vaccin = 1 AND v.n_cum_dose1 > 120000) OR (v.jour = '2021-04-06' AND v.vaccin = 2 AND v.n_cum_dose2 > 9000); ``` ## Partie 7 Table lpecom\_rpps Quelle requête SQL utiliser pour compter, sans doublons, le nombre de professionnels de santé en **Seine-et-Marne (77)** ? ```SQL SELECT COUNT(DISTINCT id_pp_nat) FROM lpecom_rpps; ``` Quelle requête SQL utiliser pour afficher pour tous les professionnels de santé avec le code postal **77300** les colonnes suivantes : `id_pp_nat`, `prenom`, `nom`, `code_postal`, `ville`, `departement` et `région`. Vous aurez besoin de plusieurs jointures. ```SQL SELECT rpps.id_pp_nat, rpps.prenom, rpps.nom, rpps.code_postal, c.name as ville, d.name as departement, r.name as region FROM lpecom_rpps rpps INNER JOIN lpecom_cities c ON (rpps.code_postal = c.zip_code) INNER JOIN lpecom_departments d ON (c.department_code = d.code) INNER JOIN lpecom_regions r ON (d.region_code = r.code) WHERE rpps.code_postal = 77300; ```