pastEvents

My solution:

CREATE PROCEDURE pastEvents() 
BEGIN 
  SELECT name, 
         event_date 
  FROM   Events, 
         (SELECT MAX(DATE(event_date)) AS last_date 
          FROM   Events) AS t 
  WHERE  event_date != t.last_date 
         AND DATEDIFF(t.last_date, event_date) <= 7 
  ORDER  BY event_date DESC; 
END 

dateFormatting

My solution:

CREATE PROCEDURE dateFormatting() 
BEGIN 
  SELECT DATE(date_str) AS date_iso 
  FROM   documents 
  ORDER  BY id; 
END 

importantEvents

My solution:

CREATE PROCEDURE importantEvents() 
BEGIN 
  SELECT * 
  FROM   events 
  ORDER  BY ( DAYOFWEEK(event_date) + 5 ) % 7, 
            participants DESC; 
END 

validPhoneNumbers

My solution:

CREATE PROCEDURE validPhoneNumbers() 
BEGIN 
  SELECT * 
  FROM   phone_numbers 
  WHERE  phone_number REGEXP "^1-[[:digit:]]{3}-[[:digit:]]{3}-[[:digit:]]{4}$" 
          OR phone_number REGEXP 
"^[[.left-parenthesis.]]1[[.right-parenthesis.]][[:digit:]]{3}-[[:digit:]]{3}-[[:digit:]]{4}$" 
ORDER  BY surname; 
END 

correctIPs

My solution:

CREATE PROCEDURE correctIPs() 
BEGIN 
  SELECT id, 
         ip 
  FROM   ips 
  WHERE  ip REGEXP 
"^((25[0-6]|2[0-4][0-9]|1[0-9][0-9]|[1-9][0-9]|[0-9])\.){2}[1-9][0-9]\.(25[0-6]|2[0-4][0-9]|1[0-9][0-9]|[1-9][0-9]|[0-9])$" 
 OR ip REGEXP 
"^((25[0-6]|2[0-4][0-9]|1[0-9][0-9]|[1-9][0-9]|[0-9])\.){2}(25[0-6]|2[0-4][0-9]|1[0-9][0-9]|[1-9][0-9]|[0-9])\.[1-9][0-9]$" 
ORDER  BY id;
END 

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;