[SQL] HackerRank - Revising, Select, Population, Japan 9문제(E)

1. Revising the Select Query 1

2. Revising the Select Query 2

3. Select All

4. Select By ID

5. Average Population

6. Population Density Difference

7. Japan Population

8. Japanese Cities’ Names

9. Japanese Cities’ Attributes

HackerRank

 

 

---

1. Revising the Select Query 1

 

 

SELECT *  -- all columns
  FROM city  -- city table
 WHERE population > 100000 AND countrycode = 'USA';

• population이 100000보다 크면서 (AND) countrycode가 'USA'인 조건을 WHERE절에 추가

 

 

 

2. Revising the Select Query 2

 

 

SELECT name
  FROM city
 WHERE population > 120000 AND countrycode = 'USA';

• population이 120000보다 크면서 (AND) countrycode가 'USA'인 조건을 WHERE절에 추가

 

 

 

3. Select All

 

 

SELECT *
  FROM CITY;

 

 

 

4. Select By ID

 

 

SELECT *
  FROM CITY
 WHERE ID = 1661;

 

 

 

5. Average Population

 

 

SELECT FLOOR(AVG(population))
  FROM city;

• 버림을 하라고했으므로 FLOOR 함수를 사용해준다.

 

 

 

6. Population Density Difference

 

 

SELECT MAX(population) - MIN(population)
  FROM city;

• 최댓값 구하기 MAX함수 / 최솟값 구하기 MIN 함수

 

 

 

7. Japan Population

 

 

SELECT SUM(population)
  FROM city
 WHERE countrycode = 'JPN';

• population의 합계를 구해야하므로 SUM(population)
• countrycode가 'JPN'이라는 조건을 where절에 추가

 

 

 

8. Japanese Cities' Names

 

 

SELECT name
  FROM city
 WHERE countrycode = 'JPN';

• countrycode가 'JPN'인 조건을 주고 name 컬럼을 SELECT 했다.

 

 

 

9. Japanese Cities' Attributes

 

 

SELECT *
  FROM city
 WHERE countrycode = 'JPN';