1. What riders belong to the team ‘Os Velozes’? (name)
1
2
3
SELECT name
FROM rider
WHERE team = 'Os Velozes'

2. What rider won the ‘Porto - Aveiro’ stage? (name)
1
2
3
4
5
6
SELECT name
FROM rider JOIN
classification USING (ref) JOIN
stage USING (num)
WHERE description = 'Porto - Aveiro' AND
position = 1

3. What riders ciclistas raced the ‘Coimbra - Lisboa’ stage and what was their final position? Order the answer by their position. (name, position)
1
2
3
4
5
6
SELECT name, position
FROM rider JOIN
classification USING (ref) JOIN
stage USING (num)
WHERE description = 'Coimbra - Lisboa'
ORDER BY position

4. How many riders are there in each team? (team, total)
1
2
3
SELECT team, COUNT(*)
FROM rider
GROUP BY team

5. What is the total sum of times of each rider? (name, total)
1
2
3
4
SELECT name, SUM(time)
FROM rider JOIN
classification USING (ref)
GROUP BY ref

6. What team, or teams, has a smaller sum of its riders total times? (team)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT team, SUM(time) as total
FROM rider JOIN
classification USING (ref)
GROUP BY team
HAVING SUM(time) = (
select min(sum_time)
from
(
SELECT SUM(time) as sum_time
FROM rider JOIN
classification USING (ref)
GROUP BY team
)
);

7. What is the average time in each stage? (description, average)
1
2
3
4
SELECT description, AVG(time)
FROM stage JOIN
classification USING (num)
GROUP BY num

8. What stage, or stages, had the smaller average time? (description)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT description
FROM stage JOIN
classification USING (num)
GROUP BY num
HAVING AVG(time) = (
select min(avg_time)
from
(
SELECT AVG(time) as avg_time
FROM stage JOIN
classification USING (num)
GROUP BY num
)
);

9. What was the time difference between the first and second rider in each stage? (description, difference)
1
2
3
4
5
6
7
8
9
10
11
12
SELECT first.description, strftime('%s', second.time) - strftime('%s', first.time) as difference
FROM
(SELECT *
FROM stage JOIN
classification USING (num)
WHERE position = 1) AS first
JOIN
(SELECT *
FROM stage JOIN
classification USING (num)
WHERE position = 2) AS second
USING (num);

10. What stage had the biggest time difference between the first and second rider to finish it, what rider won that stage and with how much lead time. (description, name, difference).
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SELECT first.description, first.name, strftime('%s', second.time) - strftime('%s', first.time) as difference
FROM
(
SELECT *
FROM stage JOIN
classification USING (num)
join rider USING (ref)
WHERE position = 1
) AS first
JOIN
(
SELECT *
FROM stage JOIN
classification USING (num)
WHERE position = 2
) as second
on first.num = second.num
WHERE strftime('%s', second.time) - strftime('%s', first.time) =
(
select max(difference)
from
(
SELECT strftime('%s', second.time) - strftime('%s', first.time) as difference
FROM classification AS first JOIN
classification AS second USING (num)
WHERE first.position = 1 AND second.position = 2
)
);


(Credits: André Restivo https://web.fe.up.pt/~arestivo)