placesOfInterestPairs

My solution:

CREATE PROCEDURE placesOfInterestPairs() 
BEGIN 
  SELECT place1, 
         place2 
  FROM   (SELECT sights.name                          AS place1, 
                 sights2.name                         AS place2, 
                 SQRT(POW(sights.x - sights2.x, 2) 
                      + POW(sights.y - sights2.y, 2)) AS distance 
          FROM   sights, 
                 sights AS sights2 
          WHERE  sights.name < sights2.name) AS t 
  WHERE  distance < 5 
  ORDER  BY place1, 
            place2; 
END 

userCountries

My solution:

CREATE PROCEDURE userCountries() 
BEGIN 
  SELECT id, 
         country 
  FROM   users, 
         cities 
  WHERE  users.city = cities.city 
  UNION 
  SELECT DISTINCT id, 
                  "unknown" AS country 
  FROM   users, 
         cities 
  WHERE  users.city NOT IN (SELECT city 
                            FROM   cities) 
  ORDER  BY id; 
END 

scholarshipsDistribution

My solution:

CREATE PROCEDURE scholarshipsDistribution() 
BEGIN 
  SELECT candidate_id AS student_id 
  FROM   candidates 
  WHERE  candidate_id NOT IN (SELECT DISTINCT candidate_id AS id 
                              FROM   candidates, 
                                     detentions 
                              WHERE  candidate_id = student_id 
                              ORDER  BY candidate_id); 
END 

companyEmployees

My solution:

CREATE PROCEDURE companyEmployees() 
BEGIN 
  SELECT dep_name, 
         emp_name 
  FROM   departments, 
         employees 
  ORDER  BY dep_name, 
            emp_name; 
END 

alarmClocks

My solution:

CREATE PROCEDURE alarmClocks() 
BEGIN 
  DROP TABLE IF EXISTS calendarWeek; 

  CREATE TABLE calendarWeek 
    ( 
       `number` INT 
    ); 

  SET @s = 0; 

  loopLabel: 
LOOP 
    INSERT INTO calendarWeek 
                (`number`) 
    VALUES      (@s); 
    SET @s = @s + 1; 
    IF @s <= 53 THEN 
      ITERATE loopLabel; 
    END IF; 
    LEAVE loopLabel; 
  END LOOP loopLabel; 

  SELECT alarm_date 
  FROM   (SELECT DATE_ADD(input_date, INTERVAL (7 * number) DAY) AS alarm_date 
          FROM   userInput, 
                 calendarWeek) AS t, 
         userInput 
  WHERE  YEAR(alarm_date) = YEAR(input_date); 
END 

netIncome

My solution:

CREATE PROCEDURE 
  netIncome() 
BEGIN 
  SELECT   YEAR(   date)                                                AS year, 
           QUARTER(date)                                                AS quarter, 
           SUM(CONVERT(profit, SIGNED INT) - CONVERT(loss, SIGNED INT)) AS net_profit 
  FROM     accounting 
  GROUP BY year, 
           quarter 
  ORDER BY date; 

END

Note:
profit: BIGINT UNSIGNED and
loss: BIGINT UNSIGNED
need to be converted to SIGNED INT.

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; 

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