soccerGameSeries

My solution:

CREATE PROCEDURE soccerGameSeries() 
BEGIN 
  SELECT IF(first_team_wins > second_team_wins, 1, 
                      IF(second_team_wins > first_team_wins, 2, IF( 
                      first_team_goals > second_team_goals, 1, IF( 
         second_team_goals > first_team_goals 
         , 2, IF( 
         first_team_away_goals > second_team_away_goals 
         , 1, IF( 
         second_team_away_goals > 
         first_team_away_goals, 2, 0)))))) AS winner 
  FROM   (SELECT SUM(IF(first_team_score > second_team_score, 1, 0)) AS 
                 first_team_wins, 
                 SUM(IF(second_team_score > first_team_score, 1, 0)) AS 
                 second_team_wins, 
                 SUM(first_team_score)                               AS 
                 first_team_goals, 
                 SUM(second_team_score)                              AS 
                 second_team_goals 
                        , 
                 SUM(IF(match_host = 1, first_team_score, 0))        AS 
                        first_team_home_goals, 
                 SUM(IF(match_host = 2, first_team_score, 0))        AS 
                        first_team_away_goals, 
                 SUM(IF(match_host = 1, second_team_score, 0))       AS 
                        second_team_away_goals, 
                 SUM(IF(match_host = 2, second_team_score, 0))       AS 
                        second_team_home_goals 
          FROM   scores) AS results; 
END 

placesOfInterest

My solution:

SELECT ap.country, 
       ap.adventure_park, 
       g.golf, 
       rc.river_cruise, 
       kr.kart_racing 
FROM   (SELECT country, 
               SUM(IF(leisure_activity_type = "Adventure park", number_of_places 
                   , 0)) 
                      AS 
               adventure_park 
        FROM   countryActivities 
        GROUP  BY country) ap, 
       (SELECT country, 
               SUM(IF(leisure_activity_type = "Golf", number_of_places, 0)) AS 
               golf 
        FROM   countryActivities 
        GROUP  BY country) g, 
       (SELECT country, 
               SUM(IF(leisure_activity_type = "River cruise", number_of_places, 
                   0)) AS 
               river_cruise 
        FROM   countryActivities 
        GROUP  BY country) rc, 
       (SELECT country, 
               SUM(IF(leisure_activity_type = "Kart racing", number_of_places, 0 
                   )) AS 
               kart_racing 
        FROM   countryActivities 
        GROUP  BY country) kr 
WHERE  ap.country = g.country 
       AND g.country = rc.country 
       AND rc.country = kr.country; 

orderingEmails

My solution:

CREATE PROCEDURE orderingEmails() 
BEGIN 
  SELECT id, 
         email_title, 
         IF(size >= POWER(2, 20), CONCAT(FLOOR(size / POWER(2, 20)), " Mb"), 
         CONCAT(FLOOR(size / POWER(2, 10)), " Kb")) AS short_size 
  FROM   emails 
  ORDER  BY size DESC; 
END 

orderOfSuccession

My solution:

CREATE PROCEDURE orderOfSuccession() 
BEGIN 
  SELECT CONCAT(IF(gender = "M", "King ", "Queen "), name) AS name 
  FROM   Successors 
  ORDER  BY DATE(birthday); 
END 

habitatArea

My solution:

CREATE PROCEDURE habitatArea() 
BEGIN 
  SELECT ST_Area(ST_ConvexHull(ST_GeomFromText(m.g))) AS area 
  FROM   places, 
         (SELECT CONCAT("MULTIPOINT(", i.str, ")") AS g 
          FROM   (SELECT GROUP_CONCAT(x, " ", y) AS str 
                  FROM   places) i) AS m 
  LIMIT  1; 
END 

I used

+----------------------------------+
| ST_Area(ST_GeomFromText(@mpoly)) |

+----------------------------------+

Source: MySQL Reference Manual - 12.15.7.4 Polygon and MultiPolygon Property Functions.

and

+-----------------------------------------------+
| ST_ConvexHull(ST_GeomFromText(@mpoly))        |
+-----------------------------------------------+

Source: MySQL Reference Manual - 12.15.8 Spatial Operator Functions.

personalHobbies

My solution:

CREATE PROCEDURE personalHobbies() 
BEGIN 
  SELECT name 
  FROM   people_hobbies 
  WHERE  hobbies LIKE "%sports%" 
         AND hobbies LIKE "%reading%" 
  ORDER  BY name; 
END 

interestClub

My solution:

CREATE PROCEDURE interestClub() 
  SELECT name 
  FROM   people_interests 
  WHERE  interests && interests LIKE "%drawing%" 
         AND interests && interests LIKE "%reading%" 
  ORDER  BY name