[HackerRank] Basic SELECT S1

Weather Observation Station 4까지 풀이

문제 1

Query all columns for all American cities in the CITY table with populations larger than 100000. The CountryCode for America is USA

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

문제2

Query the NAME field for all American cities in the CITY table with populations larger than 120000. The CountryCode for America is USA.

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

문제3

Query all columns (attributes) for every row in the CITY table.

SELECT * 
 FROM CITY;

문제4

Query all columns for a city in CITY with the ID 1661.

SELECT *
 FROM CITY
 WHERE ID = '1661';

문제5

Query all attributes of every Japanese city in the CITY table. The COUNTRYCODE for Japan is JPN.

SELECT *
 FROM CITY
 WHERE COUNTRYCODE = 'JPN';

문제6

Query the names of all the Japanese cities in the CITY table. The COUNTRYCODE for Japan is JPN.

SELECT NAME
 FROM CITY
 WHERE COUNTRYCODE ='JPN';

문제7

Query a list of CITY and STATE from the STATION table.

SELECT  CITY
      , STATE
 FROM STATION;

문제8

Query a list of CITY and STATE from the STATION table.

SELECT  CITY
      , STATE
 FROM STATION;

문제9

Query a list of CITY names from STATION for cities that have an even ID number. Print the results in any order, but exclude duplicates from the answer.

SELECT CITY
 FROM STATION
 WHERE MOD(ID, 2) = 0
 GROUP BY CITY;

-- MySQL의 경우 %연산이 가능하지만 Oracle의 경우 MOD() 함수를 사용해야함...!
SELECT CITY
 FROM STATION
 WHERE ID%2 = 0
 GROUP BY CITY;

문제10

Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.

• 표에 있는 총 CITY수와 고유한 CITY의 차를 구하라.

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

• 이 문제는 해석의 어려움이 존재했다...(알고보니 간단...)