Transcript Corrigé

Lyc´ee Thiers
Bases de donn´
ees - TD1 - Corrig´
e
Table des mati`
eres
SELECT nom, MIN(conductivite thermique) FROM periodic
1 Exercice 1 : Base de donn´
ee atomic.slqite
1.1 Enonc´e . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.2 Corrig´e . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1
4)
1
1 SELECT nom FROM periodic ORDER BY Tebullition DESC LIMIT 1
2 Exercice 2 : Base de donn´
ee nobel.sqlite
2.1 Enonc´e . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.2 Corrig´e . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1 5)
1 SELECT nom FROM periodic ORDER BY masse atomique DESC LIMIT 15
1
3 Exercice 3 : base de donn´
ee movie.sqlite
3.1 Enonc´e . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.2 Corrig´e . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2
2
2
4 Exercice 4 : densit´
e de population et ressources hydriques
4.1 Enonc´e . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.2 Corrig´e . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3
3 Exercice 2
3 Charger la base de donn´ee nobel.sqlite concernant les laur´eats du prix nobel. Elle
contient une seule table nobel
1
2
2.1
Enonc´
e
1. Quel est son sch´ema de relation ? Quelle requˆete a permis sa cr´eation ?
Exercice 1 : Base de donn´
ee atomic.slqite
1.1
Exercice 2 : Base de donn´
ee nobel.sqlite
2. Afficher tous les laur´eats de l’ann´ee 1990 et leurs disciplines.
Enonc´
e
3. Afficher les diff´erentes disciplines du nobel.
Exercice 1
Charger la base periodic.sqlite qui contient une table contenant les donn´ees de la
table p´eriodique des ´el´ements.
4. Mˆeme question mais en affichant aussi le nombre de laur´eats par discipline.
5. Combien y-a-t-il eu de laur´eats du prix nobel de Physique au XX`eme si`ecle ?
1. Afficher numero atomique, symbole et nom des ´el´ements de la table p´eriodique.
6. Sur quelle p´eriode s’´etend la table ?
2. Quel est l’´el´ement ayant la plus faible conductivit´e thermique ?
3. Quels est l’´el´ements ayant la temp´erature d’´ebullition la plus ´elev´ee ?
7. Sur cette p´eriode, combien d’ann´ees n’a ´et´e d´ecern´e aucun prix nobel ?
4. Quels sont les 15 atomes les plus lourds ?
8. * Le d´ecernement des prix nobels s’est interrompue sur une p´eriode de plusieurs
ann´ees. Quelle requˆete permet de d´eterminer la p´eriode o`
u n’a ´et´e d´ecern´e aucun
prix nobel ?
indication : Commencer par d´eterminer quelle requˆete retourne le d´ebut de cette
p´eriode, quelle requˆete en retourne la fin, puis appliquer un produit cart´esien sur
les r´esultats de ces requˆetes :
SELECT * FROM (SELECT ...), (SELECT ...)
1.2
Corrig´
e
1)
SELECT numero atomique, symbole, nom FROM periodic
2)
SELECT symbole FROM periodic WHERE Tfusion >=1000 AND Tfusion <= 2000
2.2
3)
(1)
1
Corrig´
e
Lyc´ee Thiers
Bases de donn´
ees - TD1 - Corrig´
e
3
Sch´ema de relation :
nobel (ann´
ee INT, sujet VARCHAR(15), laureat VARCHAR(50))
Exercice 3 : base de donn´
ee movie.sqlite
3.1
Obtenu par :
Enonc´
e
Exercice 3
Charger la base de donn´
ee movie.sqlite. Elle contient 3 tables v´
erifiant les sch´
emas suivants :
CREATE TABLE nobel(
annee INT
,sujet VARCHAR(15)
,laureat VARCHAR(50)
)
actor ( id INTEGER, name VARCHAR(35))
casting (movieid INTEGER, actorid INTEGER, ord INTEGER)
movie (id INTEGER, title VARCHAR(70), yr DECIMAL(4), score FLOAT,
votes INTEGER, director INTEGER)
Elle porte sur tous les films de cinema, leur casting, acteurs et r´
ealisateurs jusqu’`
a l’ann´
ee 2000. La table
actor contient les acteurs mais aussi les r´
ealisateurs
(2)
SELECT laureat, sujet FROM nobel WHERE annee = 1990;
1. Afficher les (titres des) 10 films les mieux not´
es de l’ann´
ee 1995.
2. Afficher le nombre de films contenus dans la base.
3. Afficher la liste des acteurs/r´
ealisateurs dont les initiales sont T.G.
(3)
SELECT sujet FROM nobel GROUP BY sujet
4. Combien de films ont obtenu une note sup´
erieure a
` 8?
5. Combien d’acteurs/r´
ealisateurs ont un nom commen¸
cant par la lettre A ?
6.
(4)
SELECT sujet, COUNT(laureat) FROM nobel GROUP BY sujet
(a) Quels sont les (identifiants des) 10 r´
ealisateurs ayant obtenu la meilleure note moyenne pour
leurs films ?
(b) D´
eterminer `
a l’aide d’une jointure (SELECT ... FROM table1 JOIN table2 ON ...) les noms des 10
r´
ealisateurs ayant obtenu la meilleure note moyenne pour leurs films.
7. Afficher le casting du film ”Star Wars”. On pourra proc´
eder `
a une double jointure (SELECT ... FROM
... JOIN ... JOIN ... ON ...).
(5)
SELECT COUNT(laureat) FROM nobel WHERE annee > 1900 AND annee <= 2000
AND sujet = ’Physique’
3.2
Corrig´
e
(6)
SELECT MIN(annee), MAX(annee) FROM nobel
(1)
SELECT title FROM movie ORDER BY score DESC LIMIT 10
(7)
SELECT MAX(annee)-MIN(annee)+1-COUNT(annee)
FROM (SELECT annee FROM nobel GROUP BY annee)
(2)
SELECT COUNT(title) FROM movie;
(8)
SELECT * FROM
(SELECT annee+1 FROM nobel
WHERE annee < 2008
AND NOT(annee+1 IN
(SELECT annee from nobel)) GROUP BY annee)
,(SELECT annee-1 FROM nobel
WHERE annee>1901
AND NOT(annee-1 IN
(SELECT annee FROM nobel)) GROUP BY annee);
(3)
SELECT name FROM actor WHERE name LIKE ’T% G%’
(4)
SELECT COUNT(title) FROM movie WHERE score > 8
(5)
SELECT COUNT(name) FROM actor WHERE name LIKE ’% A%’
2
Lyc´ee Thiers
Bases de donn´
ees - TD1 - Corrig´
e
(6.1)
SELECT AVG(score), director FROM movie
GROUP BY director
ORDER BY AVG(score) DESC
LIMIT 10
3. Cr´eer 3 tables teau, tsup, tpop ayant chacune pour attributs region
VARCHAR(20), et valeur FLOAT. Donner des requˆetes permettant de les remplir
avec les 6 continents et les valeurs absolues (sans %) de l’eau, de la superficie et
de la population. En d´eduire `
a l’aide d’une jointure simple SELECT ... FROM ...
JOIN... ON la table tdens pour la densit´e de population des 6 continents.
4. Donner une requˆete d´eterminant le nombre de continents dont la densit´e est
sup´erieure `a 30 hab/km2 ? Donner une requˆete retournant le nom, class´e dans
l’ordre alphab´etique, puis par densit´e d´ecroissante, de ceux de densit´e inf´erieure `
a
30 hab/km2 .
(6.2)
SELECT name FROM actor JOIN
(SELECT AVG(score), director FROM movie
GROUP BY director ORDER BY AVG(score) DESC LIMIT 10)
ON director=actor.id
5. Comment afficher les donn´ees de la table t0 par densit´e d´ecroissante ? Par quantit´e
d’eau par habitant ?
(7)
SELECT actor.name FROM actor JOIN casting JOIN movie
ON actor.id = casting.actorid
AND movie.id=casting.movieid
AND movie.title="Star Wars"
4
4.2
(1)
CREATE TABLE region (VARCHAR(20), eau INT(2), superficie INT(2),
population INT(2))
Exercice 4 : densit´
e de population et ressources hydriques
4.1
Corrig´
e
(2) Chargement des donn´ees :
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
Enonc´
e
INTO
INTO
INTO
INTO
INTO
INTO
t0
t0
t0
t0
t0
t0
VALUES(’Afrique’, 11, 23, 13);
VALUES(’AmerNord’, 15, 17, 8);
VALUES(’AmerSud’, 26, 13, 6);
VALUES(’Asie’, 36, 24, 61);
VALUES(’Europe’, 8, 17, 12);
VALUES(’Oceanie, 5, 6, 1);
3)
CREATE TABLE teau(region VARCHAR(20), valeur FLOAT);
INSERT INTO teau SELECT nom, 43000*eau/100 FROM t0;
CREATE TABLE tsup(region VARCHAR(20), valeur FLOAT);
INSERT INTO tsup SELECT nom, 130677343*superficie/100 FROM t0;
CREATE TABLE tpop(region VARCHAR(20), valeur FLOAT);
INSERT INTO tpop SELECT nom, 6033323000*population/100 FROM t0;
CREATE TABLE tiens (nom VARCHAR(20), valeur FLOAT);
INSERT INTO tdens SELECT tpop.nom, tpop.valeur/tsup.valeur
1. Cr´eer une base de donn´ee appel´ee continents, puis une table t0 dans cette
FROM tpop JOIN tsup ON tpop.nom=tsup.nom
base ayant 4 attributs : region VARCHAR(20), eau INT(2), superficie INT(2),
population INT(2).
4)
2. A partir des donn´ees pour les 6 continents dans le tableau ci-dessus charger la
table t0.
SELECT COUNT(*) FROM tiens WHERE valeur > 30
3
Lyc´ee Thiers
Bases de donn´
ees - TD1 - Corrig´
e
SELECT nom FROM tiens WHERE valeur <= 30 ORDER BY nom
SELECT nom FROM tiens WHERE valeur <= 30 ORDER BY valeur DESC
5)
SELECT t0.nom, t0.eau, t0.superficie, t0.population
FROM t0 JOIN tdens ON t0.nom = tdens.nom
ORDER BY tdens.valeur DESC
CREATE TABLE eauhab (nom VARCHAR(20), valeur FLOAT);
INSERT INTO eauhab SELECT
teau.nom, teau.valeur/tpop.valeur
FROM teau JOIN tpop
ON teau.nom = tpop.nom;
SELECT t0.nom, t0.eau, t0.superficie, t0.population
FROM t0 JOIN eauhab ON t0.nom = eauhab.nom
ORDER BY eauhab.valeur DESC
6)
CREATE TABLE t1(nom VARCHAR(20), eau FLOAT, superficie FLOAT;
CREATE TABLE t2(nom VARCHAR(20), eau FLOAT, superficie FLOAT,
population FLOAT);
CREATE TABLE t(nom VARCHAR(20), eau FLOAT, superficie FLOAT,
population FLOAT, densit´
e FLOAT)
INSERT INTO t1 SELECT teau.nom, teau.valeur, tsup.valeur
FROM teau JOIN tsup ON teau.nom = tsup.nom;
INSERT INTO t2 SELECT t1.nom, t1.eau, t1.superficie, tpop.valeur
FROM t1 JOIN tpop ON t1.nom = tpop.nom;
4