André Restivo

Travel SQL Solutions

  1. What cities were visited in the 24th of May 2007 trip? (name)
SELECT DISTINCT name
FROM stop
WHERE day = '2007-05-24'
  1. 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'
  1. 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
  1. What is the average city score for each country? (name, average)
SELECT country, AVG(score)
FROM city
GROUP BY country
  1. What countries have cities that are not the capital but are the highest scoring city in the country? (name)

  1. 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.