Exercices SQL interactifs

Vous pourrez trouver de très nombreux exercices interactifs d'entraînement sur le site de Nicolas Reveret, à l'adresse https://nreveret.forge.apps.education.fr/exercices_bdd/

Exercice 1

Questions interactives à réaliser sur le site sqlzoo.net.

Q1. Travail sur SELECT, (base de données Nobel) ici.

Correction

Corrections extraites du dépôt https://github.com/jisaw/sqlzoo-solutions.

/*
Third section of sqlzoo, SELECT from Nobel
*/

--#1
/*
Change the query shown so that it displays Nobel prizes for 1950.
*/
SELECT yr, subject, winner
FROM nobel
WHERE yr = 1950

--#2
/*
Show who won the 1962 prize for Literature.
*/
SELECT winner
FROM nobel
WHERE yr = 1962 AND subject = 'Literature'

--#3
/*
Show the year and subject that won 'Albert Einstein' his prize.
*/
SELECT yr, subject
FROM nobel
WHERE winner = 'Albert Einstein'

--#4
/*
Give the name of the 'Peace' winners since the year 2000, including 2000.
*/
SELECT winner
FROM nobel
WHERE subject = 'Peace' AND yr >= 2000

--#5
/*
Show all details (yr, subject, winner) of the Literature prize winners for 1980 to 1989 inclusive
*/
SELECT yr, subject, winner
FROM nobel
WHERE (yr >=1980 AND yr <=1989) AND subject = 'Literature'

--#6
/*
Show all details of the presidential winners:

Theodore Roosevelt
Woodrow Wilson
Jimmy Carter
*/
SELECT *
FROM nobel
WHERE winner IN ('Theodore Roosevelt', 'Woodrow Wilson', 'Jimmy Carter')

--#7
/*
Show the winners with first name John
*/
SELECT winner
FROM nobel
WHERE winner LIKE 'John%'

--#8
/*
Show the Physics winners for 1980 together with the Chemistry winners for 1984.
*/
SELECT *
FROM nobel
WHERE (subject = 'Physics' AND yr = '1980') OR (subject = 'Chemistry' AND yr = 1984)

--#9
/*
Show the winners for 1980 excluding the Chemistry and Medicine
*/
SELECT *
FROM nobel
WHERE yr = 1980 AND subject NOT IN ('Chemistry', 'Medicine')

--#10
/*
Show who won a 'Medicine' prize in an early year (before 1910, not including 1910) together with winners of a 'Literature' prize in a later year (after 2004, including 2004)
*/
SELECT *
FROM nobel
WHERE (subject  = 'Medicine' AND yr < 1910) OR (subject = 'Literature' AND yr >= 2004)

--#11
/*
Find all details of the prize won by PETER GRÜNBERG
*/
SELECT *
FROM nobel
WHERE winner LIKE 'peter gr%nberg'

--#12
/*
Find all details of the prize won by EUGENE O'NEILL
*/
SELECT *
FROM nobel
WHERE winner = 'Eugene O''Neill'

--#13
/*
Knights in order

List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order.
*/
SELECT winner, yr, subject
FROM nobel
WHERE winner LIKE 'sir%'
ORDER BY yr DESC, winner

--#14
/*
The expression subject IN ('Chemistry','Physics') can be used as a value - it will be 0 or 1.

Show the 1984 winners ordered by subject and winner name; but list Chemistry and Physics last.
*/
SELECT winner, subject, subject IN ('Physics','Chemistry')
FROM nobel
WHERE yr=1984
ORDER BY subject IN ('Physics','Chemistry'),subject,winner

Q2. Travail sur SUM et COUNT, (base de données World) ici. (jusqu'à la question 5.)

Correction

Corrections extraites du dépôt https://github.com/jisaw/sqlzoo-solutions.

/*
Fifth section of sqlzoo, SUM and COUNT
*/


--#1
/*
Show the total population of the world.
*/
SELECT SUM(population)
FROM world

--#2
/*
List all the continents - just once each.
*/
SELECT DISTINCT(continent)
FROM world

--#3
/*
Give the total GDP of Africa
*/
SELECT SUM(gdp)
FROM world
WHERE continent = 'Africa'

--#4
/*
How many countries have an area of at least 1000000
*/
SELECT COUNT(name)
FROM world
WHERE area >= 1000000

--#5
/*
What is the total population of ('France','Germany','Spain')
*/
SELECT SUM(population)
FROM world
WHERE name IN ('France', 'Germany', 'Spain')

--#6
/*
For each continent show the continent and number of countries.
*/
SELECT continent, COUNT(name)
FROM world
GROUP BY continent


--#7
/*
For each continent show the continent and number of countries with populations of at least 10 million.
*/
SELECT continent, COUNT(name)
FROM world
WHERE population >= 10000000
GROUP BY continent

--#8
/*
List the continents that have a total population of at least 100 million.
*/
SELECT continent
FROM world
GROUP BY continent
HAVING SUM(population) > 100000000

Q3. Travail sur JOIN, (base de données Euro2012) ici.

Exercice 2

Gestion d'un réseau d'agences de location de voitures.
D'après le travail de J. Le Coupanec (Académie de Rennes)

La base de données locations.db contient les tables Agences,Locations, Vehicules.

Requête :
Exécuter

Questions sur la relation Agences

Q1. Visualisez toute la relation Agences

Correction

SELECT * 
FROM Agences

Q2. Listez uniquement les noms des agences et de leur ville.

Correction

SELECT nom, ville 
FROM Agences

Q3. Listez les noms des agences de la ville de Lorient

Correction

SELECT nom 
FROM Agences
WHERE ville = 'Lorient'

Q4. Listez les noms des agences du département du Morbihan (code postal 56***) ainsi que les codes postaux en utilisant par exemple un WHERE LIKE.

Correction

SELECT nom 
FROM Agences
WHERE code LIKE '56%'

Questions sur la relation Vehicules

Q5. Déterminez le nombre de voitures que vous possédez.

Correction

SELECT COUNT(*) 
FROM Vehicules

Q6. Déterminez l'âge minimum et maximum de vos véhicules.

Correction

SELECT MAX(age), MIN(age) 
FROM Vehicules

Q7. Quels sont la marque et le modèle de votre dernière acquisition qui date de trois mois ?

Correction

SELECT nom 
FROM Vehicules
WHERE age = 3

Q8. Quel est le kilométrage maximum des véhicules ?

Correction

SELECT MAX(kilometrage) 
FROM Vehicules

Q9. Quel est le kilométrage moyen des véhicules ?

Correction

SELECT AVG(kilometrage) 
FROM Vehicules

Q10. Afficher toute la flotte de véhicules par ordre décroissant de kilométrage.

Correction

SELECT * 
FROM Vehicules
ORDER BY kilometrage DESC

Questions sur la relation Locations

Q11. Visualisez toute la relation Locations.

Correction

SELECT * 
FROM Locations

Q12. Déterminez le nombre de locations effectuées avec changement d'agence

Correction

SELECT COUNT(*) 
FROM Locations
WHERE depart != retour

Q13. Déterminez le nombre total de kilomètres effectués durant les locations

Correction

SELECT SUM(kilometrage) 
FROM Locations

Q14. Listez toutes les locations en y associant les caractéristiques du véhicule

Correction

SELECT * 
FROM Locations
JOIN Vehicules ON Locations.vehicule = Vehicules.immatriculation

Q15. Affichez le nom et l'immatriculation du véhicule ainsi que la date de la location et le kilométrage réalisé pour chacune des locations

Correction

SELECT Vehicules.nom, Vehicules.immatriculation, Locations.date, Locations.kilometrage 
FROM Locations
JOIN Vehicules ON Locations.vehicule = Vehicules.immatriculation

Q16. Affichez une seule fois le nom et l'immatriculation des véhicules ayant déjà été loués.

Correction

SELECT DISTINCT nom, immatriculation 
FROM Locations
JOIN Vehicules ON Locations.vehicule = Vehicules.immatriculation

Q17. Affichez les locations du véhicule immatriculé AB-224-BA en précisant le nom de l'agence de départ ainsi que la ville de départ dans l'ordre chronologique des locations.

Correction

SELECT Agences.nom, Agences.ville, Locations.* 
FROM Locations
JOIN Agences ON Locations.depart = Agences.id
WHERE vehicule = 'AB-224-BA'
ORDER BY Locations.date

Exercice 3

Championnat de France de Football 2015-2016

D'après le travail de J. Le Coupanec (Académie de Rennes)

La base de données soccer.db contient les tables Team,Match, Event, Player.

Requête :
Exécuter

Q1. Combien d'équipes ont participé à ce championnat ?

Q2. Listez les noms des clubs ainsi que leur date de création dans l'ordre chronologique de leur création.

Q3. Combien de pénaltys ont été marqués ?

Q4. Combien de pénaltys ont été sifflés ?

Q5. Combien de cartons ont été distribués ?

Q6. Combien de buts ont été marqués ?

Q7. Affichez tous les renseignements sur les 10 cartons rouges obtenus le plus rapidement pendant un match.

Q8. Donnez le nom du joueur qui a obtenu le carton rouge le plus rapidement.

Exercice 4

Cet exercice en ligne est proposé le Knight Lab de l'université américaine Northwerstern University.

Le point de départ de l'histoire : un meurtre a été commis dans la ville de SQL City le 15 janvier 2018.

À partir de ce point de départ et d'une base de données dont le diagramme est donné ci-dessous, il s'agit de trouver le meurtrier.

Zone d'enquête (à coups de requêtes)

Il est conseillé de travailler avec un Bloc-Notes ouvert à côté afin d'y coller les renseignements obtenus. Vous pouvez utiliser le champ de requêtes ci-dessous mais il est possible que des colonnes n'apparaissent pas. Vous pouvez aussi utiliser n'importe quel champ sur la la page officielle.

Tapez votre requête ci-dessous
Exécuter

Vous pensez avoir trouvé le meurtrier ?

Copiez la requête

INSERT INTO solution VALUES (1, 'nom du meurtrier');
SELECT value FROM solution;

dans le champ ci-dessous :
Exécuter

Sur la page officielle, vous pouvez être guidés étape par étape jusqu'à la recherche du meurtrier (qui n'est pas la fin de l'énigme !)

• Vous pouvez si vous le souhaitez télécharger la base sql-murder-mystery.db.

Correction

Vous pouvez trouver des éléments de correction ici.