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

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)

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)

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)

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)

SELECT DISTINCT C1.name 
FROM city JOIN country AS C1 ON city.country = C1.name
WHERE city.name <> capital AND score = (
  SELECT max(score)
  FROM city JOIN country ON city.country = country.name
  WHERE country.name = C1.name
  GROUP BY country.name
);

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.

SELECT T1.name, T2.name
FROM 
  (SELECT * FROM city JOIN country ON city.country = country.name) AS T1
  JOIN
  (SELECT * FROM city JOIN country ON city.country = country.name) AS T2
  ON T1.language = T2.language
  WHERE T1.country <> T2.country AND T1.name < T2.name;

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