TP SQL
Jeu de données DONESOL
Accès à la base de données sous MySql : [Link]
Contenu d’une table
Enregistrements: 11
1- Lister le contenu de la table auteur
auteur no_etude sigle
GAULTIER 2101 INRA
SELECT * FROM auteur; FALIPOU 2101 INRA
BORNAND 2101 INRA
LEGROS 2101 INRA
VIGNERON 2101 INRA
ARNALD 2101 CNABRL
BOUTEYRE 2101 CNABRL
GAULTIER 2200 INRA
FALIPOU 2200 INRA
LEGROS 2300 INRA
BORNAND 2300 INRA
Enregistrements: 35
2 – Sélectionnez les unités
cartographiques et tous leurs
attributs, en les triant par numéro no_etu no_uc alt_m alt_ma type_r occup exces salinite pierro
d’ordre croissant de in x egion _eau site
2101 10408 1 20 6 2 1 0 2
SELECT * FROM u_carto order by no_uc ; 2101 17101 75 275 6 2 0 0 0
2101 17102 NULL NULL 6 2 0 0 25
2101 17105 30 90 6 2 NULL NULL 20
2101 17106 60 160 6 2 0 NULL 40
2101 17107 10 20 5 2 1 NULL 5
2101 17301 2 20 6 2 1 NULL 3
2101 20201 0 2 5 3 1 3 NULL
2101 30616 5 50 5 2 NULL NULL 4
2101 30701 60 100 3 1 0 NULL NULL
2101 30901 20 65 5 2 0 NULL 40
2101 30902 NULL NULL 5 2 0 NULL 2
2101 30902 NULL NULL 5 2 NULL NULL 40
2101 30903 40 75 6 2 0 NULL 50
2101 30922 25 130 5 2 1 NULL 50
2101 31017 30 160 3 2 0 NULL 45
2101 37101 20 100 3 2 0 0 50
2101 37216 15 130 3 2 1 0 15
2101 41001 125 700 4 4 0 0 50
2101 41002 200 780 4 4 0 NULL 40
2101 42301 125 700 4 4 0 NULL 50
2101 42501 100 140 4 2 0 NULL 55
2101 44602 10 600 4 3 0 NULL 55
2101 44603 250 500 2 4 0 0 40
2101 45301 13 200 2 4 0 0 0
2101 45601 40 170 2 4 0 NULL 55
2101 45901 200 780 4 3 0 NULL 50
2101 50508 30 80 5 2 1 NULL 20
2101 52701 200 400 2 4 0 NULL 40
2101 52902 50 100 5 NULL 0 0 0
2101 55201 20 160 2 2 1 NULL NULL
2101 55222 50 120 2 2 1 NULL 2
2101 55302 120 450 3 5 0 NULL 5
2101 66101 150 750 2 4 0 NULL 50
2101 66102 150 750 3 4 0 0 50
Sélection et projection
Enregistrements: 9
3- Sélectionnez le nom de tous les auteurs
travaillant à l’INRA auteur
GAULTIER
FALIPOU
SELECT auteur FROM auteur BORNAND
WHERE sigle = "INRA" ; LEGROS
VIGNERON
GAULTIER
FALIPOU
LEGROS
BORNAND
Enregistrements: 5
4 - Sélectionnez le nom de tous les auteurs
travaillant à l’INRA sans doublons et triés par auteur
ordre alphabétique BORNAND
FALIPOU
SELECT DISTINCT auteur FROM auteur GAULTIER
WHERE sigle = "INRA" LEGROS
ORDER BY auteur ; VIGNERON
Fonctions de groupe et agrégation
5 –Combien y a-t-il d’horizons décrits ? Enregistrements: 1
COUNT(*)
SELECT COUNT(*) FROM horizon;
71
6 – Quelle est l’épaisseur de l’horizon le plus Enregistrements: 1
épais ?
MAX(epais)
SELECT MAX(epais) FROM horizon ;
90
7 – Sélectionnez dans la table auteur le numéro Enregistrements: 3
d’étude et le nombre d’auteurs de chaque étude
no_etude COUNT(*)
SELECT no_etude, COUNT(*) FROM auteur GROUP BY
2101 7
no_etude LIMIT 2200 2
2300 2
Sélection sur les groupes
Enregistrements: 8
8 – Sélectionnez le numéro de tous les profils
qui ont plus de 3 horizons ; no_profil
122069
SELECT no_profil FROM horizon 122084
GROUP BY no_profil
HAVING count(*)>3 LIMIT 0, 30 ; 122089
122095
122139
122145
122160
122203
Gestion de la valeur NULL
9 - Combien y-a-t-il de profils non encore Enregistrements: 1
affectés à une unité cartographique ?
count(*)
SELECT count(*) FROM profil 24
WHERE no_uc IS NULL;
Enregistrements: 8
10 – Sélectionnez les numéros de profil ayant
une profondeur supérieure à 100 cm no_profil
ou ayant un horizon dont le pH est supérieur à 122069
8.5
122089
SELECT no_profil FROM profil 122095
WHERE prof > 100 122139
UNION 122145
SELECT no_profil FROM horizon
WHERE ph > 8.5 LIMIT 0, 30 ; 122203
122142
122104
Sélection avec sous-requêtes (SELECT imbriqués)
11 - Quel est le numéro du profil ayant l’horizon
le plus épais ? Indiquez n° de profil et n°
Enregistrements: 1
d’horizon. no_profil no_horizon
SELECT no_profil FROM horizon 122139 4
WHERE epais =
(SELECT MAX(epais) FROM `horizon`
);
12 – Quels auteurs ont contribué à l’étude
TOULOUSE ou à l’étude AGEN? Enregistrements: 4
SELECT auteur FROM auteur auteur
WHERE no_etude in ( GAULTIER
SELECT no_etude FROM `etude`
FALIPOU
WHERE titre ='AGEN'
LEGROS
OR titre='TOULOUSE');
BORNAND
Produit cartésien et jointure
Enregistrements: 4
13 - Quels sont les auteurs ayant contribué à
l’étude TOULOUSE ou l’étude AGEN? (Faites une auteur
jointure) GAULTIER
FALIPOU
SELECT auteur FROM auteur, etude
WHERE (etude.no_etude = auteur.no_etude) LEGROS
AND (titre ='AGEN' BORNAND
OR titre='TOULOUSE');
Enregistrements: 14
14 – Sélectionnez les unités cartographiques
pour lesquelles le premier horizon (d’un des no_uc no_profil
profils) a une épaisseur supérieure à 20 cm. 31017 122100
Affichez aussi le numéro du profil.
17101 122200
Solution 1 :On fait une jointure entre les tables profil 17301 122069
et horizon NULL 122069
SELECT profil.no_uc,profil.no_profil 37216 122079
FROM profil NULL 122079
INNER JOIN horizon 30901 122089
ON profil.no_profil = horizon.no_profil 42501 122093
WHERE no_horizon = 1 AND epais > 20 ; 55302 122094
30922 122095
55201 122139
30616 122160
30701 166666
45301 122225
Enregistrements: 12
14 – Même question.
no_uc no_profil
Solution 2 On fait 2 jointures en intégrant la table 31017 122100
u_carto 17101 122200
SELECT u_carto.no_uc, horizon.no_profil 17301 122069
FROM horizon
JOIN profil 37216 122079
ON horizon.no_profil = profil.no_profil 30901 122089
JOIN u_carto
ON u_carto.no_uc = profil.no_uc 42501 122093
WHERE no_horizon = 1 55302 122094
AND epais > 20 ; 30922 122095
55201 122139
30616 122160
30701 166666
45301 122225
Enregistrements: 12
14 – Même question.
no_uc no_profil
3ème solution (jointures en utilisant la clause 31017 122100
WHERE) 17101 122200
17301 122069
SELECT u_carto.no_uc, horizon.no_profil
FROM horizon, profil, u_carto 37216 122079
WHERE horizon.no_profil = profil.no_profil 30901 122089
AND u_carto.no_uc = profil.no_uc
42501 122093
AND no_horizon = 1
AND epais > 20 ; 55302 122094
30922 122095
55201 122139
30616 122160
30701 166666
45301 122225
15 - Sélectionnez toutes les unités Enregistrements: 4
cartographiques dont le taux d’argile dans
no_uc no_profil
l’horizon 2 (d’un des profils) est supérieur au
taux de sable dans ce même horizon. Affichez 17101 122200
aussi le numéro du profil. 30901 122089
SELECT u_carto.no_uc, horizon.no_profil 30616 122160
FROM horizon 41001 122104
JOIN profil
ON horizon.no_profil = profil.no_profil
JOIN u_carto
ON u_carto.no_uc = profil.no_uc
WHERE no_horizon = 2
AND argile > sable ;
16 - Sélectionner tous les profils, en indiquant le Enregistrements: 21
nombre d’horizons, la somme des épaisseurs
no_profil COUNT(no_horizon) SUM(epais)
des horizons, dont l’épaisseur d’un horizon est
supérieure à 30 cm. 122064 3 45
122069 4 200
SELECT profil.no_profil, COUNT(no_horizon), 122079 3 75
SUM(epais)
FROM profil
122084 4 100
INNER JOIN horizon ON profil.no_profil = 122085 3 70
horizon.no_profil 122089 4 110
WHERE [Link] > 30
GROUP BY horizon.no_profil ;
122093 2 55
122094 3 80
122095 4 200
122096 3 50
122100 3 80
122104 3 50
122139 4 250
122142 2 35
122145 5 180
122160 4 100
122200 3 100
122201 3 100
122203 4 120
122225 3 80
166666 2 80
Enregistrements: 1
17 - Même question avec tous les horizons du
profil d’épaisseur supérieure à 30 cm. no_profil COUNT(no_horizon) SUM(epais)
SELECT profil.no_profil, COUNT(no_horizon),
122139 4 250
SUM(epais)
FROM profil
INNER JOIN horizon
ON profil.no_profil = horizon.no_profil
WHERE [Link] > 30
AND horizon.no_profil NOT IN (
SELECT horizon.no_profil
FROM horizon
WHERE epais <=30 )
GROUP BY horizon.no_profil ;
