31 January 2018

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%"; 

30 January 2018

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"; 

24 January 2018

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; 

23 January 2018

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 

22 January 2018

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 

21 January 2018

usersByContinent

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

20 January 2018

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 

19 January 2018

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