HackerRank-Japanese Cities' Attributes, Japanese Cities' Names, Weather Observation Station 1, Weather Observation Station 3, Weather Observation Station 4 (COUNT(*))

Japanese Cities' Attributes📝 

➡️ CITY 테이블, 조건: COUNTRYCODE=  'JPN'

 

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

The CITY table is described as follows: 

SELECT * FROM city WHERE countrycode='JPN';

 

---

Japanese Cities' Names📝

➡️ CITY 테이블, 조건: COUNTRYCODE=  'JPN' , 출력 : name

 

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

 

SELECT name FROM city WHERE countrycode='JPN';

 

---

Weather Observation Station 1📝

➡️ STATION 테이블 -> 출력 : CITY,STATE

 

Query a list of CITY and STATE from the STATION table. 
The STATION table is described as follows: 

where LAT_N is the northern latitude and LONG_W is the western longitude.

SELECT city,state FROM station;

 

---

Weather Observation Station 3📝

➡️ STATION 테이블,조건: ID를 2로 나눌 수 있을 때 -> 출력 : 중복되지 않는 CITY 

 

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. 
The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

SELECT DISTINCT(city) FROM station WHERE MOD(id,2)=0;

 

---

 

Weather Observation Station 4(수정)📝

 

➡️ STATION 테이블에서 ( 전체 CITY의 개수 - 중복되지 않는 CITY의 개수 )

 

 

Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table. 
The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

For example, if there are three records in the table with CITY values 'New York', 'New York', 'Bengalaru', there are 2 different city names: 'New York' and 'Bengalaru'. The query returns , because .

SELECT COUNT(*)-COUNT(DISTINCT(city)) FROM station; -- 이렇게 할 경우 null인 경우 달라짐
-- 이문제의 경우 null이 없어서 괜찮지만 null이 있을 경우 이렇게 하면 안 될 것 같다
SELECT COUNT(city)-COUNT(DISTINCT(city) FROM station;

---

📌 COUNT(city)-COUNT(DISTINCT(city)), COUNT(*)-COUNT(DISTINCT(city))

COUNT(*) - null을 포함한 행의 개수

COUNT(city) - null을 포함하지 않는 행의 개수

 

이렇게 생긴 테이블이 있는 경우, COUNT(*)과 COUNT(city)를 할 경우 밑과 같이 COUNT(*) = 6, COUNT(city) = 4가 나오게 된다.

 

하지만 COUNT(DISTINCT(city))를 할 경우 DISTINCT(city)는 null을 포함하여서 분류하지만 COUNT(DISTINCT(city))를 할 경우에는 null을 제외한 행의 수를 센다. 따라서 2가 나오게 된다. 

(오른쪽)SELECT DISTINCT(city) FROM station,(왼쪽)SELECT COUNT(DISTINCT(city)) FROM station

 

그렇기에 SELECT COUNT(city)-COUNT(DISTINCT(city)) FROM station, COUNT(*)-COUNT(DISTINCT(city)) FROM station은 이런식으로 다르게 나오게 된다.