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 

The PADS

My solution:

SELECT CONCAT(Name, "(", LEFT(Occupation, 1), ")") 
FROM   OCCUPATIONS 
ORDER  BY Name; 

SELECT CONCAT("There are a total of ", COUNT(Occupation), " ", LCASE(Occupation) 
       , "s.") 
FROM   OCCUPATIONS 
GROUP  BY Occupation 
ORDER  BY COUNT(Occupation), 
          Occupation; 

Type of Triangle

My solution:

SELECT IF(( A + B > C 
            AND ABS(A - B) < C ) 
          AND ( A + C > B 
                AND ABS(A - C) < B ) 
          AND ( B + C > A 
                AND ABS(B - C) < A ), IF (A = B 
                                          AND A = C 
                                          AND B = C, "Equilateral", 
                                      IF(A = B 
                                          OR B = C 
                                          OR A = C, "Isosceles", "Scalene")), 
              "Not A Triangle") 
FROM   TRIANGLES; 

Employee Salaries

My solution:

SELECT name 
FROM   Employee 
WHERE  salary > 2000 
       AND months < 10 
ORDER  BY employee_id; 

Employee Names

My solution:

SELECT name 
FROM   Employee 
ORDER  BY name; 

Higher Than 75 Marks

My solution:

SELECT Name 
FROM   STUDENTS 
WHERE  Marks > 75 
ORDER  BY RIGHT(Name, 3), 
          ID; 

Weather Observation Station 12

My solution:

SELECT DISTINCT CITY 
FROM   STATION 
WHERE  CITY REGEXP "^[^AEIOU].*[^aeiou]$"; 

Weather Observation Station 5

My solution:

(SELECT CITY, 
        LENGTH(CITY) 
 FROM   STATION 
 GROUP  BY CITY 
 HAVING LENGTH(CITY) <= (SELECT MIN(LENGTH(CITY)) 
                         FROM   STATION) 
 LIMIT  1) 
UNION 
(SELECT CITY, 
        LENGTH(CITY) 
 FROM   STATION 
 GROUP  BY CITY 
 HAVING LENGTH(CITY) >= (SELECT MAX(LENGTH(CITY)) 
                         FROM   STATION) 
 LIMIT  1); 

Weather Observation Station 11

My solution:

SELECT DISTINCT CITY 
FROM   STATION 
WHERE  CITY REGEXP "^.*[^aeiou]$" 
        OR CITY REGEXP "^[^AEIOU].*$"; 

Weather Observation Station 10

My solution:

SELECT DISTINCT CITY 
FROM   STATION 
WHERE  CITY REGEXP "^.*[^aeiou]$"; 

Weather Observation Station 9

My solution:

SELECT DISTINCT CITY 
FROM   STATION 
WHERE  CITY REGEXP "^[^AEIOU].*$"; 

Weather Observation Station 8

My solution:

SELECT DISTINCT CITY 
FROM   STATION 
WHERE  CITY REGEXP "^(A|E|I|O|U).*(a|e|i|o|u)$"; 

Weather Observation Station 7

My solution:

SELECT DISTINCT CITY 
FROM   STATION 
WHERE  CITY LIKE "%A" 
        OR CITY LIKE "%E" 
        OR CITY LIKE "%I" 
        OR CITY LIKE "%O" 
        OR CITY LIKE "%U"; 

Weather Observation Station 6

My solution:

SELECT DISTINCT CITY 
FROM   STATION 
WHERE  CITY LIKE "A%" 
        OR CITY LIKE "E%" 
        OR CITY LIKE "I%" 
        OR CITY LIKE "O%" 
        OR CITY LIKE "U%"; 

Weather Observation Station 4

My solution:

SELECT COUNT(CITY) - COUNT(DISTINCT CITY) 
FROM   STATION; 

Weather Observation Station 3

My solution:

SELECT DISTINCT CITY 
FROM   STATION 
WHERE  ID % 2 = 0; 

Weather Observation Station 1

My solution:

SELECT CITY, 
       STATE 
FROM   STATION; 

Japanese Cities' Names

My solution:

SELECT NAME 
FROM   CITY 
WHERE  COUNTRYCODE = "JPN"; 

Japanese Cities' Attributes

My solution:

SELECT * 
FROM   CITY 
WHERE  COUNTRYCODE = "JPN"; 

Select By ID

My solution:

SELECT * 
FROM   CITY 
WHERE  ID = 1661; 

Select All

My solution:

SELECT * 
FROM   CITY; 

Revising the Select Query II

My solution:

SELECT NAME 
FROM   CITY 
WHERE  POPULATION > 120000 
       AND COUNTRYCODE = "USA"; 

Revising the Select Query I

My solution:

SELECT * 
FROM   CITY 
WHERE  POPULATION > 100000 
       AND COUNTRYCODE = "USA"; 

legsCount

My solution:

DROP PROCEDURE IF EXISTS legsCount; 

CREATE PROCEDURE legsCount() 
  SELECT SUM(IF(type = "human", 2, 4)) AS summary_legs 
  FROM   creatures 
  ORDER  BY id; 

websiteHacking

My solution:

CREATE PROCEDURE websiteHacking() 
  SELECT id, 
         login, 
         name 
  FROM   users 
  WHERE  type = 'user' 
          OR type != 'user' 
  ORDER  BY id 

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 

testCheck

My solution:

CREATE PROCEDURE testCheck() 
  SELECT id, 
         IF (given_answer IS NULL, "no answer", CASE 
         WHEN given_answer = correct_answer THEN "correct" 
         ELSE "incorrect" END) AS checks
  FROM   answers 
  ORDER  BY id; 

newsSubscribers

My solution:

CREATE PROCEDURE newsSubscribers() 
  BEGIN 
    SELECT DISTINCT subscriber 
    FROM   full_year 
    WHERE  full_year.newspaper LIKE "%Daily%" 
    UNION 
    SELECT DISTINCT subscriber 
    FROM   half_year 
    WHERE  half_year.newspaper LIKE "%Daily%" 
    ORDER  BY subscriber ASC; 
  END