SQL ) LV2. 조건에 맞는 사원 정보 조회하기
접근법
- 두 테이블을 EMP_NO 기준 조인 연산을 수행 후,
- WHERE 절에서 YEAR칼럼 값이 2022인 레코드로 제한을 두고, EMP_NO기준으로 그룹화 했다.
- 이때, SCORE값의 합(상반기, 하반기)을 나타내는 SCORE 칼럼 생성 후, 해당 칼럼 기준 내림차순 정렬한 뒤, LIMIT 1을 두어 가장 첫번째 행의 레코드를 출력했다.
코드
SELECT SUM(SCORE) AS SCORE, E.EMP_NO, EMP_NAME, POSITION, EMAIL
FROM HR_EMPLOYEES E JOIN HR_GRADE G ON E.EMP_NO = G.EMP_NO
WHERE YEAR = '2022'
GROUP BY EMP_NO
ORDER BY SCORE DESC
LIMIT 1;
SQL ) LV2. 연도 별 평균 미세먼지 농도 조회하기
접근법
- YM칼럼 값에서 YEAR함수를 통해 연도 추출 후 YEAR칼럼을 생성했다.
- 이후, WHERE절에서 LOCATION이 수원인 경우로 제한을 두고,
- YEAR칼럼 기준으로 그룹화 후에 PM_VAL1과 PM_VAL2의 평균값을 AVG함수로 구한 뒤, 각각 ROUND함수를 통해 소수 둘째자리까지 표현했다.
코드
SELECT YEAR(YM) AS 'YEAR', ROUND(AVG(PM_VAL1), 2) AS 'PM10', ROUND(AVG(PM_VAL2), 2) AS 'PM2.5'
FROM AIR_POLLUTION WHERE LOCATION2 = '수원' GROUP BY YEAR ORDER BY 1;
SQL ) LV2. 노선별 평균 역 사이 거리 조회하기
접근법
- 역 구간별 합계와 평균을 나타내는 칼럼을 만들기 위해 SUM, AVG 함수를 사용했고, ROUND함수로 각각 반올림을 해주었다. 또한, 뒤에 KM단위를 붙여서 나타내야하므로 CONCAT함수를 사용했다.
- 여기서 주의할 점은 정렬할 때, TOTAL_DISTANCE칼럼 기준 내림차순 정렬을 하는데, 이미 해당 칼럼은 CONCAT함수를 쓰면서 문자열 타입을 갖게 되므로, 해당 칼럼을 기준으로 내림차순 정렬하는 게 아니라, 해당 칼럼 값이 숫자 타입일 떄를 기준으로 내림차순 정렬시켰다.
코드
SELECT ROUTE,
CONCAT(ROUND(SUM(D_BETWEEN_DIST), 1), 'km') TOTAL_DISTANCE,
CONCAT(ROUND(AVG(D_BETWEEN_DIST), 2), 'km') AVERAGE_DISTANCE
FROM SUBWAY_DISTANCE
GROUP BY ROUTE
ORDER BY ROUND(SUM(D_BETWEEN_DIST), 1) DESC;
SQL ) LV2. 물고기 종류 별 잡은 수 구하기
접근법
- 두 테이블을 조인 연산 후에 FISH_NAME 기준으로 그룹화 했다
- 각 FISH_TYPE은 하나의 FISH_NAME을 의미하기 때문에,,,
- 이후 FISH_NAME 그룹 별 잡은 물고기수 개수를 COUNT 함수를 사용해서 FISH_COUNT칼럼을 함께 출력했다.
- 여기서 FISH_TYPE을 기준으로하면 틀렸다고 나왔는데,, 내가 추측했을 때, 우리가 조회할 데이터는 FISH_NAME이니까, FISH_TYPE으로 그룹화하게 되면 경계가 모호해져서 FISH_NAME으로 그룹화해야 하는 게 아닐까.. 생각해봤다..!
코드
SELECT COUNT(*) AS FISH_COUNT, FISH_NAME
FROM FISH_INFO I JOIN FISH_NAME_INFO N ON I.FISH_TYPE = N.FISH_TYPE
GROUP BY N.FISH_NAME
ORDER BY 1 DESC;
SQL ) LV2. 월별 잡은 물고기 수 구하기
접근법
- 월별 잡은 건수를 출력하기위해 TIME칼럼에서 MONTH함수로 월을 추출해 MONTH 칼럼을 만들고, 해당 칼럼 기준으로 그룹화 후 COUNT 함수를 통해 그룹별 잡은 물고기 수를 집계해서 보여줬다.
코드
SELECT COUNT(*) FISH_COUNT, MONTH(TIME) MONTH FROM FISH_INFO GROUP BY MONTH ORDER BY 2;
SQL ) LV2. 특정 물고기를 잡은 총 수 구하기
접근법
- 두 테이블을 조인 연산 후 정규식을 활용해 FISH_NAME이 BASS 또는 SNAPPER인 레코드들의 행 개수를 COUNT함수를 통해 출력했다
코드
SELECT COUNT(*) FISH_COUNT
FROM FISH_INFO I JOIN FISH_NAME_INFO F ON I.FISH_TYPE = F.FISH_TYPE
WHERE FISH_NAME REGEXP 'BASS|SNAPPER';
SQL ) LV2. 분기별 분화된 대장균의 개체 수 구하기
접근법
- QUARTER함수로 DIFFERENTIATION_DATE의 분기를 구한 뒤 COCNAT함수로 Q와 문자열을 결합했다.
- 이후, 해당 QUARTER칼럼 기준으로 그룹화하고, COUNT함수를 통해 각 분기별 레코드 수를 반환했다.
- 여기서 주의할 점은
- ORDER BY할 때 직접 QUARTER(DIFFERENTIATION_DATE)를 기준으로 정렬하면 에러가 뜬 다는 것이다.
- QUARTER 타입이 문자열이라 숫자 타입인 QUARTER(DIFFERENTIATION_DATE) 값에 의해 정렬하려 했으나 에러가 떴는데, 이유를 생각해보니 이미 QUARTER칼럼 기준으로 그룹화 했는데, 해당 레코드 값에 존재하지 않는 QUARTER(DIFFERENTIATION_DATE) 기준으로 정렬하려 해서 뜨는 에러가 아닐까..? 만약 QUARTER가 아닌 다른 칼럼을 기준으로 그룹화 한거라면 가능했을 것 같다.
- GROUP BY와 ORDER BY를 함께 사용할 때 주의할 점들은 좀 더 공부해야겠다.
코드
SELECT CONCAT(QUARTER(DIFFERENTIATION_DATE), 'Q') QUARTER, COUNT(*) ECOLI_COUNT
FROM ECOLI_DATA
GROUP BY QUARTER
ORDER BY 1 ASC;
