[SQL] 프로그래머스 6

1.노선별 평균 역 사이 거리 조회하기

문제 설명

SUBWAY_DISTANCE 테이블은 서울지하철 2호선의 역 간 거리 정보를 담은 테이블입니다. SUBWAY_DISTANCE 테이블의 구조는 다음과 같으며 LINE, NO, ROUTE, STATION_NAME, D_BETWEEN_DIST, D_CUMULATIVE는 각각 호선, 순번, 노선, 역 이름, 역 사이 거리, 노선별 누계 거리를 의미합니다.

Column nameTypeNullable

LINE	VARCHAR(10)	FALSE
NO	NUMBER	FALSE
ROUTE	VARCHAR(50)	FALSE
STATION_NAME	VARCHAR(100)	FLASE
D_BETWEEN_DIST	NUMBER	FLASE
D_CUMULATIVE	NUMBER	FLASE

---

문제

SUBWAY_DISTANCE 테이블에서 노선별로 노선, 총 누계 거리, 평균 역 사이 거리를 노선별로 조회하는 SQL문을 작성해주세요.

총 누계거리는 테이블 내 존재하는 역들의 역 사이 거리의 총 합을 뜻합니다. 총 누계 거리와 평균 역 사이 거리의 컬럼명은 각각 TOTAL_DISTANCE, AVERAGE_DISTANCE로 해주시고, 총 누계거리는 소수 둘째자리에서, 평균 역 사이 거리는 소수 셋째 자리에서 반올림 한 뒤 단위(km)를 함께 출력해주세요.
결과는 총 누계 거리를 기준으로 내림차순 정렬해주세요.

 

문제 요구사항

1. 'SUBWAY_DISTANCE' 테이블을 사용한다. 이 테이블은 서울 지하철 2호선의 역 간 거리 정보를 담고 있다.
2. 노선별로 총 누계 거리와 평균 역 사이 거리를 조회한다. 여기서,
   • 총 누계 거리는 해당 노선 내 모든 역 사이 거리의 합이다.
   • 평균 역 사이 거리는 해당 노선 내 모든 역 사이 거리의 평균값이다.
3. 결과 컬럼명은 총 누계 거리를 'TOTAL_DISTANCE', 평균 역 사이 거리를 'AVERAGE_DISTANCE'로 한다.
4. 총 누계 거리는 소수 둘째 자리에서 반올림하고, 평균 역 사이 거리는 소수 셋째 자리에서 반올림한다. 그리고, 각 값의 단위로 'km'를 붙인다.
5. 결과는 총 누계 거리를 기준으로 내림차순으로 정렬한다.

 

#'ROUTE' 컬럼을 선택하여 결과에 포함시킨다. 이는 서울 지하철 2호선의 각 노선 이름을 나타낸다.
SELECT 
    ROUTE, 
    
    # 'D_BETWEEN_DIST' 컬럼의 합계를 구하고, 소수 첫째 자리에서 반올림한 후 'km'를 붙여서 문자열로 변환
    CONCAT(ROUND(SUM(D_BETWEEN_DIST),1), 'km') AS TOTAL_DISTANCE, 
    
    #'D_BETWEEN_DIST' 컬럼의 평균값을 구하고, 소수 둘째 자리에서 반올림한 후 'km'를 붙여서 문자열로 변환
    CONCAT(ROUND(AVG(D_BETWEEN_DIST), 2), 'km') AS AVERAGE_DISTANCE
    
FROM SUBWAY_DISTANCE

#결과를 'ROUTE' 컬럼 기준으로 그룹화한다. 이는 노선별로 총 누계 거리와 평균 역 사이 거리를 계산하기 위함이다
GROUP BY ROUTE

ORDER BY SUM(D_BETWEEN_DIST) DESC

1.SELECT ROUTE : 루트 칼럼을 선택한다 -> 서울 지하철 2호선의 각 노선이름을 나타낸다 이는 문제에서 노선별로 조회를 하라고 요구했기때문이다.

 

2.CONCAT(ROUND(SUM(D_BETWEEN_DIST),1),'KM') AS TOTAL_DISTANCE, : D_BETWEEN_DIST 컬럼의 합계를 구하고, 소수 첫째 자리에서 반올림

 

3.CONCAT(ROUND(AVG(D_BETWEEN_DIST),2)'KM') AS AVERAGE_DISTANCE : D_BETWEEN_DIST 평균값을 구하고 소수 둘째 자리에서 반올림

 

4. SUBWAY_DISTANCE 테이블 에서 불러온다

 

5.결과를 ROUTE 칼럼 기준으로 그룹화, 이는 노선별로 총 누계 거리와 역 사이의 거리를 계산하기 위함

 

6. D_BETWEEN_DIST 올림차수

 

2.연도 별 평균 미세먼지 농도 조회하기

문제 설명

AIR_POLLUTION 테이블은 전국의 월별 미세먼지 정보를 담은 테이블입니다. AIR_POLLUTION 테이블의 구조는 다음과 같으며 LOCATION1, LOCATION2, YM, PM_VAL1, PM_VAL2은 각각 지역구분1, 지역구분2, 측정일, 미세먼지 오염도, 초미세먼지 오염도를 의미합니다.

Column nameTypeNullable

LOCATION1	VARCHAR	FALSE
LOCATION2	VARCHAR	FALSE
YM	DATE	FALSE
PM_VAL1	NUMBER	FLASE
PM_VAL2	NUMBER	FLASE

---

문제

AIR_POLLUTION 테이블에서 수원 지역의 연도 별 평균 미세먼지 오염도와 평균 초미세먼지 오염도를 조회하는 SQL문을 작성해주세요. 이때, 평균 미세먼지 오염도와 평균 초미세먼지 오염도의 컬럼명은 각각 PM10, PM2.5로 해 주시고, 값은 소수 셋째 자리에서 반올림해주세요.
결과는 연도를 기준으로 오름차순 정렬해주세요.

 

문제 요구사항

1. 수원지역의 연도별 평균 미세먼지 오염도 조회 

2. 수원지역의 연도별 평균 초미세먼지 오염도 조회

3. 1은 PM10으로 2는 PM2.5 으로 칼럼명을 설정

4. 소수셋째 자리에서 반올림

 

#'YM' 컬럼(측정일)에서 연도를 추출하고, 이를 "YEAR"라는 별칭으로 결과에 표시
SELECT YEAR(YM) AS "YEAR",

			  #'PM_VAL1' 컬럼(미세먼지 오염도)의 평균값을 계산하고, 소수점 아래 두 번째 자리에서 반올림하여 "PM10"이라는 별칭으로 결과에 표시한다. 이는 연도별 평균 미세먼지 오염도를 나타낸다.
              ROUND(AVG(PM_VAL1), 2) AS "PM10", 
              
              # 'PM_VAL2' 컬럼(초미세먼지 오염도)의 평균값을 계산하고, 소수점 아래 두 번째 자리에서 반올림하여 "PM2.5"라는 별칭으로 결과에 표시한다. 이는 연도별 평균 초미세먼지 오염도를 나타낸다.
              ROUND(AVG(PM_VAL2), 2) AS "PM2.5"
              
FROM AIR_POLLUTION

#'수원': 'LOCATION2' 컬럼(지역구분2)이 '수원'인 레코드만을 대상
WHERE LOCATION2 = '수원'

#결과를 'YM' 컬럼에서 추출한 연도별로 그룹화한다. 이는 연도별로 평균 미세먼지 및 초미세먼지 오염도를 계산하기 위함이다.
GROUP BY YEAR(YM)

ORDER BY 1 ASC;

1.SELECT YEAR(YM) AS 'YEAR' : 'YM' 컬럼(측정일) 에서 연도를 추출, 이를 'YEAR' 라는 별칭으로 표시

 

2.ROUND(AVG(PM_VAL1),2)  AS 'PM10' : PM_VAL1 컬럼의 평균값을 구하고 PM10으로 저장 소수점 아래 두번째자리에서 반올림

 

3.ROUND(AVG(PM_VAL2),2) AS 'PM2.5' : PM_VAL2 컬럼의 평균값을 평균값으로 계산 PM2.5로 저장, 하고 소수점 아래 두번째 자리에서 반올림

 

4.테이블 불러오기

 

5. LOCATION2 컬럼의 '수원' 선택

 

6. 결과를 YM 컬럼에서 추출한 연도별로 그룹화

 

7. 오름차순으로 정렬

 

 

---

 

3. 개발자 찾기

문제 설명

SKILLCODES 테이블은 개발자들이 사용하는 프로그래밍 언어에 대한 정보를 담은 테이블입니다. SKILLCODES 테이블의 구조는 다음과 같으며, NAME, CATEGORY, CODE는 각각 스킬의 이름, 스킬의 범주, 스킬의 코드를 의미합니다. 스킬의 코드는 2진수로 표현했을 때 각 bit로 구분될 수 있도록 2의 제곱수로 구성되어 있습니다.

NAMETYPEUNIQUENULLABLE

NAME	VARCHAR(N)	Y	N
CATEGORY	VARCHAR(N)	N	N
CODE	INTEGER	Y	N

DEVELOPERS 테이블은 개발자들의 프로그래밍 스킬 정보를 담은 테이블입니다. DEVELOPERS 테이블의 구조는 다음과 같으며, ID, FIRST_NAME, LAST_NAME, EMAIL, SKILL_CODE는 각각 개발자의 ID, 이름, 성, 이메일, 스킬 코드를 의미합니다. SKILL_CODE 컬럼은 INTEGER 타입이고, 2진수로 표현했을 때 각 bit는 SKILLCODES 테이블의 코드를 의미합니다.

NAMETYPEUNIQUENULLABLE

ID	VARCHAR(N)	Y	N
FIRST_NAME	VARCHAR(N)	N	Y
LAST_NAME	VARCHAR(N)	N	Y
EMAIL	VARCHAR(N)	Y	N
SKILL_CODE	INTEGER	N	N

예를 들어 어떤 개발자의 SKILL_CODE가 400 (=b'110010000')이라면, 이는 SKILLCODES 테이블에서 CODE가 256 (=b'100000000'), 128 (=b'10000000'), 16 (=b'10000') 에 해당하는 스킬을 가졌다는 것을 의미합니다.

---

문제

DEVELOPERS 테이블에서 Python이나 C# 스킬을 가진 개발자의 정보를 조회하려 합니다. 조건에 맞는 개발자의 ID, 이메일, 이름, 성을 조회하는 SQL 문을 작성해 주세요.

결과는 ID를 기준으로 오름차순 정렬해 주세요.

---

문제 요구사항

1. Python 또는 C# 프로그래밍 스킬을 가진 개발자들의 정보를 조회.
2. 조회할 정보는 개발자의 ID, 이메일, 이름, 성.
3. 결과는 개발자의 ID를 기준으로 오름차순으로 정렬.

---

1. 필요한 코드 값 확인하기:
   • 우선, Python과 C#에 해당하는 스킬의 코드 값을 SKILLCODES 테이블에서 찾아야 한다. 이를 위해 SQL 쿼리를 사용하여 NAME 필드가 'Python' 또는 'C#' 인 행의 CODE 값을 추출한다.
2. 개발자 정보 조회:
   • 추출한 코드 값들을 이용하여 DEVELOPERS 테이블에서 해당 스킬을 보유한 개발자들을 찾는다. 이는 SKILL_CODE 필드에서 비트 마스크를 사용하여 필터링함으로써 이루어진다. 즉, Python 또는 C#의 코드 값과 비트 AND 연산을 수행했을 때 양수(즉, 해당 비트가 켜져 있음)를 반환하는 행들을 선택한다.
3. 결과 정렬:
   • 조회 결과를 개발자의 ID 순으로 오름차순으로 정렬한다. 이는 결과를 일관되게 보여주기 위해 중요하다.
4. SQL 쿼리 실행:
   • 실제 SQL 쿼리를 실행하여 위 조건에 맞는 개발자 정보를 조회한다.

---

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPERS
WHERE (SKILL_CODE & 256) != 0 OR (SKILL_CODE & 1024) != 0
ORDER BY ID;

---

1. SELECT 구문:
   • ID, EMAIL, FIRST_NAME, LAST_NAME: 이 부분은 DEVELOPERS 테이블에서 조회할 컬럼들을 지정한다. 조회하고자 하는 정보는 개발자의 ID, 이메일, 이름, 성명이다.
2. FROM 구문:
   • FROM DEVELOPERS: 이 쿼리가 실행될 테이블은 DEVELOPERS이다. 이 테이블에는 개발자들의 다양한 개인정보와 그들이 보유한 프로그래밍 스킬에 대한 정보가 담겨 있다.
3. WHERE 구문:
   • (SKILL_CODE & 256) != 0 OR (SKILL_CODE & 1024) != 0: 이 조건은 SKILL_CODE 컬럼에서 특정 스킬을 필터링하는 데 사용된다. SKILL_CODE 컬럼 값에 대해 비트 AND 연산을 수행하여 Python(256)과 C#(1024) 각각의 스킬을 가진 개발자를 찾는다. 비트 AND 연산의 결과가 0이 아니면 해당 스킬을 보유하고 있는 것으로 간주한다.
   • 예를 들어, 개발자의 SKILL_CODE가 1280(=256 + 1024)인 경우, 비트 AND 연산 결과는 Python과 C# 모두에서 0이 아닌 값을 반환하므로 이 개발자는 두 스킬을 모두 가지고 있는 것으로 판단된다.
4. ORDER BY 구문:
   • ORDER BY ID: 조회된 결과를 개발자의 ID에 따라 오름차순으로 정렬한다. 이는 결과를 보다 체계적으로 표현하고, 특정 개발자를 쉽게 찾기 위해 유용하다.

실행 과정

쿼리가 실행되면, 데이터베이스는 DEVELOPERS 테이블을 스캔하여 WHERE 조건을 만족하는 행을 찾는다. 해당 조건에 부합하는 모든 행들은 선택되고, 지정된 컬럼들(ID, EMAIL, FIRST_NAME, LAST_NAME)이 결과로 반환된다. 그 다음, 결과가 ID에 따라 정렬되어 최종적으로 출력된다.