[HackerRank] MySQL 기초 연습문제 (1)

멋쟁이 사자처럼 AI스쿨
데이터리안 스페셜 특강  SQL 첫걸음
HackerRank sql 연습문제 풀이 - 1

Solve SQL Code Challenges

---

1. SELECT ALL

Select All | HackerRank

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

The CITY table is described as follows

SELECT *
FROM CITY

 

• 모든 열을 가져오는 아스타(*) 기호를 사용하여 CITY 데이터를 모두 불러온다.

 

 

2. Weather Observation Station 1

Weather Observation Station 1 | HackerRank

 

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

 

• city와 state 칼럼만 불러오기

 

3. Revising the Select Query I

Revising the Select Query I | HackerRank

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:

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

 

• POPULATION 이 100000 보다 많고 (초과) COUNTRYCODE가 'USA' 인 데이터를 출력하기.

 

4. Select By ID

Select By ID | HackerRank

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

The CITY table is described as follows:

SELECT *
FROM CITY
WHERE ID = 1661

 

• ID가 1661 인 데이터 출력

 

5. Weather Observation Station 6

Weather Observation Station 6 | HackerRank

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.

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%'

 

• 도시명이 모음 5글자 중 하나로 시작한다. ( WHERE ~ LIKE 'VOWEL%' )
  • 모음으로 시작해야 하므로 해당 모음 뒤에 오는 모든 어떤 문자든 %로 가져온다.
• 중복을 제거한다.
  • DISTINCT 사용
• 데이터는 STATION에서 가져옴

6 . Weather Observation Station 12

Weather Observation Station 12 | HackerRank

Query the list of CITY names from STATION that do not start with vowels and do not end with vowels. 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.

SELECT DISTINCT CITY
FROM STATION
WHERE CITY NOT LIKE 'a%'
OR CITY NOT LIKE 'e%'
OR CITY NOT LIKE 'i%'
OR CITY NOT LIKE 'o%'
OR CITY NOT LIKE 'u%'
OR CITY NOT LIKE '%a'
OR CITY NOT LIKE '%e'
OR CITY NOT LIKE '%i'
OR CITY NOT LIKE '%o'
OR CITY NOT LIKE '%u'

 

• TABLE은 STATION
• 모음으로 시작하면 안되고, 모음으로 끝나서도 안된다. 따라서 %vowels, vowels% 조건을 다 OR로 추가해줘야한다.
• 중복을 DISTINCT 로 제거

 

7. Employee Names

Employee Names | HackerRank

Write a query that prints a list of employee names (i.e.: the name attribute) from the Employee table in alphabetical order.

 

Input Format

The Employee table containing employee data for a company is described as follows:

where employee_id is an employee's ID number, name is their name, months is the total number of months they've

been working for the company, and salary is their monthly salary.

 

Sample Input

Sample Output

Angela
Bonnie
Frank
Joe
Kimberly
Lisa
Michael
Patrick
Rose
Todd

SELECT name
FROM Employee 
ORDER BY name (ASC)

 

• Employee Table 에서 직원의 이름 리스트를 출력해보자.
  • 알파벳 순서대로 출력한다. -> 알파벳 기준 오름차순 ORDER BY _ ASC

 

8. Employee Salaries

Employee Salaries | HackerRank

Write a query that prints a list of employee names (i.e.: the name attribute) for employees in Employee having a salary greater than $ 2000 per month who have been employees for less than 10 months. Sort your result by ascending employee_id.

 

Input Format

The Employee table containing employee data for a company is described as follows:

where employee_id is an employee's ID number, name is their name, months is the total number of months they've been working for the company, and salary is the their monthly salary.

 

Sample Input

Sample Output

Angela
Michael
Todd
Joe

문법 순서는 SELECT -> FROM -> WHERE -> ORDER BY

정렬이 마지막 !!

SELECT name
FROM employee 
WHERE months < 10 AND salary > 2000
ORDER BY employee_id

문제 그대로 읽어가면서 코드를 작성한다.

• 직원의 name 리스트를 select 해서 출력할 거다.
  • 테이블은 Employee로 쓸거다.
    • 고용된 지 10달 미만이면서, 월급을 2000불 넘게 받는 직원을 찾는다.
      • 해당 직원들을 출력할 건데 id 순이 제일 빠른 순서대로 정렬 후 출력할 것이다.

 

9. Weather Observation Station 15

Weather Observation Station 15 | HackerRank

Query the Western Longitude (LONG_W) for the largest Northern Latitude (LAT_N) in STATION that is less than 137.2345 . Round your answer to 4 decimal places.

 

Input Format

The STATION table is described as follows:

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

SELECT ROUND(LONG_W,4)
FROM STATION 
WHERE LAT_N < 137.2345 
ORDER BY LAT_N DESC
LIMIT 1

 

• latitude가 137.2345 미만인 LONG_W를 출력할 것이다.
  • 위 조건을 모두 만족하는 LONG_W를 소수 4자리까지 round 적용하여 출력
    • LAT_N 값이 가장 큰 하나의 데이터를 출력한다.