· 10 years ago · Sep 13, 2016, 07:22 AM
1DROP schema if exists eurotour cascade;
2CREATE SCHEMA eurotour;
3SET search_path = eurotour;
4\i euroTour.sql
5
6/* Pour recharger le fichier : \i td01.sql */
7
8/** 2.2 **/
9select count(*)
10from team;
11
12select count(*)
13from competitor
14group by idTeam;
15
16select count(*)
17from stage;
18
19select name, count(stage.idstage)
20from class join stage on stage.idClass = class.idclass
21group by name;
22
23
24
25/** 3.1 Jointures **/
26select count(*)
27from competitor join country on competitor.idCountry = country.idcountry
28where country.name = 'France';
29
30
31select stage.idstage, dateStage, competitor.idcompetitor, competitor.surname, team.name as team
32from competitor join team on competitor.idTeam = team.idteam
33 join performance on competitor.idcompetitor = performance.idCompetitor
34 join stage on performance.idStage = stage.idstage
35where rank=1
36order by dateStage asc;
37
38
39Select stage.idstage, c1.name as Depart, c2.name as Arrivee, d1.name as Paysc1, d2.name as Paysc2, stage.distance
40from stage join city c1 on stage.idStart=c1.idcity
41 join city c2 on stage.idEnd=c2.idcity
42 join country d1 on c1.idCountry = d1.idcountry
43 join country d2 on c2.idCountry = d2.idcountry
44order by stage.idstage asc;
45
46
47/** 3.2 Sous-requetes **/
48select competitor.idcompetitor
49from competitor
50where competitor.idcompetitor not in
51 ( select competitor.idcompetitor
52 from competitor join performance on competitor.idcompetitor = performance.idCompetitor
53 where performance.idStage=10);
54
55select idstage , dateStage, distance
56from stage
57where distance in
58 ( select max(distance)
59 from stage);
60
61select count(*)
62from country
63where country.idcountry not in
64 (select competitor.idCountry
65 from country join competitor on country.idcountry = competitor.idCountry);
66
67
68
69/** 3.3 Groupements **/
70select bloodtype, count(*)
71from competitor
72group by bloodtype;
73
74
75select sum(weight), count (*), team.name
76from competitor join team on competitor.idTeam = team.idteam
77group by team.name;
78
79
80/*select competitor.surname, min(rank), sum(duration), count(idStage)
81from competitor join performance on performance.idCompetitor=competitor.idcompetitor
82group by competitor.idcompetitor;*/
83
84/** 3.4 Groupements et selection **/
85select competitor.surname
86from competitor join performance on performance.idCompetitor=competitor.idcompetitor
87where rank=1
88group by competitor.idcompetitor
89having count(*)>=2;
90
91
92select team.name
93from team join competitor on competitor.idTeam = team.idteam
94 join performance on competitor.idcompetitor = performance.idcompetitor
95where idStage in
96 (select max(idStage)
97 from stage)
98group by team.name
99having count(*)=9;
100
101
102select competitor.idcompetitor, avg(rank)
103from competitor join performance on competitor.idcompetitor = performance.idcompetitor
104group by competitor.idcompetitor
105order by avg(rank) asc
106limit 10;
107
108
109/** 3.5 Jointures externes (pas de where ni de count(*)) **/
110select team.name, count(idcompetitor)
111from team left join competitor on team.idteam = competitor.idteam and competitor.weight > 100
112group by team.name;
113
114select class.name, count(stage.idstage)
115from class left join stage on stage.idclass=class.idclass and stage.distance < 600
116group by class.idclass;
117
118select team.name, count(competitor.idcompetitor)
119from team left join competitor on competitor.idteam = team.idteam
120 join performance on performance.idcompetitor = competitor.idcompetitor
121 and rank=1
122group by team.name;
123
124
125/** 3.6 Distinct **/
126select distinct country.name
127from country right join team on team.idcountry = country.idcountry
128 left join competitor on competitor.idteam= team.idteam;
129
130select count(distinct country.name)
131from country right join team on team.idcountry = country.idcountry
132 left join competitor on competitor.idteam= team.idteam;
133
134select count(distinct competitor.idcountry), team.name
135from team left join competitor on competitor.idteam= team.idteam
136group by team.name;
137
138
139/** 3.7 Combinaison de requetes **/
140Select country.idcountry
141from country join competitor on competitor.idCountry = country.idcountry
142UNION
143Select country.idcountry
144from country join team on team.idCountry = country.idcountry
145UNION
146Select country.idcountry
147from country join city on city.idCountry = country.idcountry;
148
149Select bloodtype
150from competitor join team on competitor.idteam = team.idteam
151where team.idteam = 'INC'
152EXCEPT
153Select bloodtype
154from competitor join team on competitor.idteam = team.idteam
155where team.idteam = 'DYN';
156
157Select c.idcity
158from city c join stage on stage.idStart=c.idcity
159INTERSECT
160Select c.idcity
161from city c join stage on stage.idend=c.idcity
162order by idcity;
163
164
165/** 4.1 INSERT **/
166Insert into competitor values
167(default, 'Jean-marc', 'duhaut', 'male', 'LYON', 2101, 'FRA',
168 'jean.m@hotmail.fr', '1964-12-11', 'A+', 120, 160, 'EXS');
169
170create table topFive (
171 idstage int not null,
172 idcompetitor int not null,
173 givenname character varying(20) NOT NULL,
174 surname character varying(23) NOT NULL
175 );
176
177insert into topFive
178select idStage, competitor.idCompetitor, givenname, surname
179from competitor join performance on competitor.idcompetitor = performance.idcompetitor
180where rank <6;
181
182
183/*insert into topFive*/
184select idStage, competitor.idCompetitor, givenname, surname, rank
185from competitor join performance on competitor.idcompetitor = performance.idcompetitor
186where rank > ( select max(rank)-5
187 from performance
188 limit 5)
189order by idstage;
190
191select idstage, rank
192from performance
193where rank in ( select max(rank)-5
194 from performance
195 group by idstage);