[코드카타 연습하기] 15일차(127-136)레벨7

코드카타 127-136

127. 

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

128. 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';

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

SELECT *
FROM CITY;

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

SELECT *
FROM CITY
WHERE ID = 1661;

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

SELECT *
FROM CITY
WHERE COUNTRYCODE = 'JPN';

132. 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';

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

SELECT CITY, STATE
FROM STATION;

134. Query the following two values from the STATION table:

The sum of all values in LAT_N rounded to a scale of decimal places.
The sum of all values in LONG_W rounded to a scale of decimal places.

SELECT ROUND(SUM(LAT_N),2)TOTAL_LN, 
       ROUND(SUM(LONG_W),2)TOTAL_LW
FROM STATION;

135. 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 DISTINCT CITY
FROM STATION
WHERE (ID%2=0);

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

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

인사이트

hackerRank로 오면서 새로운 방식이라 조금 어색하지만, 초반의 문제들은 무난하게 해결할 수 있는 문제로 구성되어있었다.