1) What cities were visited in the 24th of May 2007 trip? (name)

1

SELECT DISTINCT name FROM stop WHERE day = '2007-05-24'

2) What countries where visited in the 2nd of March 2008 trip? Don’t show the same country twice. (name)

1

SELECT DISTINCT country FROM stop JOIN city USING (name) WHERE day = '2008-03-02'

3) How many cities were visited in each trip? Order the answer starting with the trip with more cities visited. If two trips have the same number of visited cities, show the most recent first. (day, number)

1

SELECT day, COUNT(*) FROM stop GROUP BY day ORDER BY COUNT(*) DESC, day DESC

4) What is the average city score for each country? (name, average)

1

SELECT country, AVG(score) FROM city GROUP BY country

5) What countries have cities that are not the capital but are the highest scoring city in the country? (name)

1

6) List all pair of cities from different countries where the spoken language is the same. Don’t show the same pair twice even if in a different order.

1




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