marketReport

My solution:

CREATE PROCEDURE marketReport() 
BEGIN 
  SELECT DISTINCT country, 
                  COUNT(*) AS competitors 
  FROM   foreignCompetitors 
  GROUP  BY country 
  UNION 
  SELECT "Total:" AS country, 
         COUNT(competitor) 
  FROM   foreignCompetitors; 
END 

soccerPlayers

My solution:

CREATE PROCEDURE soccerPlayers() 
BEGIN 
  SELECT GROUP_CONCAT(first_name, ' ', surname, ' #', player_number ORDER BY 
         player_number ASC 
         SEPARATOR '; ') AS players 
  FROM   soccer_team; 
END 

movieDirectors

My solution:

CREATE PROCEDURE movieDirectors() 
BEGIN 
  SELECT director 
  FROM   (SELECT director, 
                 SUM(oscars) AS n_oscars 
          FROM   moviesInfo 
          WHERE  year > 2000 
                 AND oscars > 0 
          GROUP  BY director) x 
  WHERE  x.n_oscars > 2; 
END 

travelDiary

My solution:

CREATE PROCEDURE travelDiary() 
BEGIN 
  SELECT GROUP_CONCAT(DISTINCT country ORDER BY country ASC SEPARATOR ';') AS 
         countries 
  FROM   diary; 
END 

usersByContinent

My solution:

CREATE PROCEDURE usersByContinent() 
BEGIN 
  SELECT continent, 
         SUM(users) AS users 
  FROM   countries 
  GROUP  BY continent 
  ORDER  BY users DESC; 
END 

itemCounts

My solution:

CREATE PROCEDURE itemCounts() 
BEGIN 
  SELECT item_name, 
         item_type, 
         COUNT(*) AS item_count 
  FROM   availableItems 
  GROUP  BY item_name, 
            item_type 
  ORDER  BY item_type, 
            item_name; 
END 

countriesInfo

My solution:

CREATE PROCEDURE countriesInfo() 
BEGIN 
  SELECT COUNT(*)        AS number, 
         AVG(population) AS average, 
         SUM(population) AS total 
  FROM   countries; 
END