remarkable-book

HackerRank Population Census SELECT SUM(CITY.POPULATION) FROM CITY JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE WHERE COUNTRY.CONTINENT = 'Asia';

HackerRank Basic Join Average Population of Each Continent SELECT COUNTRY.CONTINENT, FLOOR(AVG(CITY.POPULATION)) FROM CITY JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE GROUP BY COUNTRY.CONTINENT;

HackerRank Basic Join African Cities SELECT CITY.NAME FROM CITY JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE WHERE COUNTRY.CONTINENT = 'Africa';

HackerRank Population Census SELECT SUM(CITY.POPULATION) FROM CITY JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE WHERE COUNTRY.CONTINENT = 'Asia';

Weather Observation Station 12 SELECT DISTINCT CITY FROM STATION WHERE (LEFT(CITY, 1) NOT IN ('a', 'e', 'i', 'o', 'u') AND RIGHT(CITY, 1) NOT IN ('a', 'e', 'i', 'o', 'u'));

Weather Observation Station 11 SELECT DISTINCT CITY FROM STATION WHERE (LEFT(CITY, 1) NOT IN ('a', 'e', 'i', 'o', 'u') OR RIGHT(CITY, 1) NOT IN ('a', 'e', 'i', 'o', 'u'));

Weather Observation Station 10 SELECT DISTINCT CITY FROM STATION WHERE CITY NOT LIKE '%a' AND CITY NOT LIKE '%e' AND CITY NOT LIKE '%i' AND CITY NOT LIKE '%o' AND CITY NOT LIKE '%u';

HeackerRank Weather Observation Station 9 모음으로 시작하지 않고, 중복되는 이름이 없는 도시를 조회하는 쿼리다. SELECT DISTINCT CITY FROM STATION WHERE LEFT(CITY, 1) NOT IN ('a', 'e', 'i', 'o', 'u'); 1. LEFT에 옵션 1을 설정한다. 2. in 안에 모음을 할당한다.

HeackerRank Weather Observation Station 8 (i.e., a, e, i, o, and u) 가 이름의 처음과 끝에 있는 도시명을 조회하는 쿼리다. SELECT DISTINCT CITY FROM STATION WHERE LEFT(CITY, 1) IN ('a', 'e', 'i', 'o', 'u') AND RIGHT(CITY, 1) IN ('a', 'e', 'i', 'o', 'u'); 1. Left, Right를 사용한다. 2. in 안에 각 특징을 할당한다.

HeackerRank Basic Select Weather Observation Station 7 aeiou로 끝나는 도시 이름을 찾는 문제이다. 해결 방법은 아래와 같다. SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '[aeiouAEIOU]$';