[HackerRank/MySQL] 8월 15일 SQL 스터디 10문제 (Lv Easy. 10개)

 

SQL 스터디가 프로그래머스 문제를 마무리하고 해커랭크 문제풀이를 시작했다.
근데 난 밀려서 이제 인증한다.
조금 밀렸더니 써야하는 블로그가 순식간에 불어났다.
역시 사람은 조금도 나태해지면 안된다는 걸 다시금 깨달으며....

 

 

1. Revising the Select Query I   

https://www.hackerrank.com/challenges/revising-the-select-query/problem?isFullScreen=true 

Revising the Select Query I | HackerRank

lv. easy 

문제

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

The CITY table is described as follows:

 

CODE

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

 

---

 

2. Revising the Select Query II 

https://www.hackerrank.com/challenges/revising-the-select-query-2/problem?isFullScreen=true 

Revising the Select Query II | HackerRank

lv. easy

문제

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

The CITY table is described as follows:

 

 

CODE

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

 

---

 

3. Select All 

https://www.hackerrank.com/challenges/select-all-sql/problem?isFullScreen=true 

Select All | HackerRank

lv. easy

문제

 

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

The CITY table is described as follows:

CODE

SELECT * FROM CITY;

 

---

 

4. Select By ID 

https://www.hackerrank.com/challenges/select-by-id/problem?isFullScreen=true 

Select By ID | HackerRank

lv. easy

문제

 

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

The CITY table is described as follows:

CODE

SELECT * FROM CITY WHERE ID = 1661;

 

---

 

5. Japanese Cities' Attributes 

https://www.hackerrank.com/challenges/japanese-cities-attributes/problem?isFullScreen=true 

Japanese Cities' Attributes | HackerRank

lv. easy

문제

 

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

The CITY table is described as follows:

 

 

CODE

SELECT * FROM CITY WHERE COUNTRYCODE = 'JPN';

 

---

 

6. Japanese Cities' Names 

https://www.hackerrank.com/challenges/japanese-cities-name/problem?isFullScreen=true 

Japanese Cities' Names | HackerRank

lv. easy

문제

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:

 

 

 

CODE

SELECT NAME FROM CITY WHERE COUNTRYCODE = 'JPN';

 

---

 

7. Weather Observation Station 1 

https://www.hackerrank.com/challenges/weather-observation-station-1?isFullScreen=true 

Weather Observation Station 1 | HackerRank

lv. easy

문제

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.

CODE

SELECT CITY, STATE FROM STATION;

 

---

 

8. Weather Observation Station 3 

https://www.hackerrank.com/challenges/weather-observation-station-3?isFullScreen=true 

Weather Observation Station 3 | HackerRank

lv. easy

문제

 

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. 

CODE

SELECT DISTINCT(CITY) FROM STATION WHERE MOD(ID, 2) = 0

• 짝수, 홀수 판별을 위한 MOD(ID, 2) = 0 (이건 짝수!) 나눗셈 연산.

 

---

 

9. Weather Observation Station 4  

https://www.hackerrank.com/challenges/weather-observation-station-4?isFullScreen=true

Weather Observation Station 4 | HackerRank

lv. easy

문제

 

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 1 , because total number of records - number of unique city names = 3 - 2 = 1

 

CODE

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

 

---

 

10. Weather Observation Station 6  

https://www.hackerrank.com/challenges/weather-observation-station-6/problem?isFullScreen=true 

Weather Observation Station 6 | HackerRank

lv. easy

문제

Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.

Input Format

The STATION table is described as follows:

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

CODE

SELECT DISTINCT(CITY) FROM STATION 
WHERE CITY LIKE 'a%' or CITY LIKE 'e%' or CITY LIKE 'i%' or CITY LIKE 'o%' or CITY LIKE 'u%';

 

 

- 끝! -