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)