SQL 4장 실습문제

소스파일 : https://github.com/bjpublic/postgresql

1

한해 동안 특수학교에서 졸업한 학생의 수가 25명 이상이었던 학교 이름과 남, 여 졸업생 수를 출력.

# 테이블 정보 확인
\dt 

# 어떤 컬럼에 "특수학교"라는 데이터가 있는지 유추되는 컬럼 하나씩 직접 확인
학교급명에 들어있음

# 졸업한 학생의 수가 25명 이상
졸업학생수에 대한 칼럼은 없고, 남자 졸업생수와 여자 졸업생수만 있어서 둘을 합침

# 정답
select 학교명, 졸업남자수, 졸업여자수 from graduates where 학교급명 = '특수학교' AND (졸업남자수 +
졸업여자수) >= 25;

2

2015년에 남,여 통합 취업률이 50%가 넘은 학교의 지역명, 이름과 취업률 출력.

# 졸업년도 확인. date_trunc로 년도만 남기고, distinct로 중복제거
select distinct date_trunc('year', 기준년도) from graduates;

# 남, 여 통합취합률에 대한 컬럼이 없음. 직접계산
남,여 통합 취업률 = (취업남자수 + 취업여자수) / (졸업남자수 + 졸업여자수) * 100

# 정답. 데이터가 바뀌었는지 쿼리식은 맞는데 데이터가 달랐다.
 select 학교명, 지역명, 100*(취업남자수+취업여자수)/(졸업남자수+졸업여자수) as 취업률 from graduates where extract(year from 기준년도) = 2015 and (졸업남자수+졸업여자수) > 0 and 100 * (취업남자수+취업여자수) / (졸업남자수+졸업여자수) >= 30;

3

"경기도 고양시 일산" 지역에 있는 고등학교의 각 연도별 졸업생 정보를 다음의 조건을 만족하도록 출력
a. 진학률을 기준으로 내림차순 출력
b. 졸업생이 없으면 진학률은 0%로 표시
c. 지역명에 "고양시 일산"이 포함되어 있는 행 검색
d. 출력될대 기준년도는 네자리 숫자로, 지역명에는 "경기도 고양시"라는 문자열을 뺀 뒷부분만 출력

# d의 기준년도 부터 풀어보자. 
select extract(year from 기준년도) as 기준년도  from graduates; 

# b 졸업생이 없으면 진학률은 0%로 표시
진학률이 없어서 직접 계산해야한다. 진학률 = (남자진학수+여자진학수) / (남자졸업수+여자졸업수) * 100
여기서 남자졸업수+여자졸업수가 0인 경우가 생긴다. 이를 해결하기 위해 case 함수를 사용해보자.
또한 필드값이 정수로 되어있어서 먼저 나눈후 100을 곱하면 모두 0이 되는 문제가 발생한다. 
그래서 100을 먼저 곱하고 나눠준다.
CASE
  when 졸업남자수 + 졸업여자수 = 0
  then 0
  else 100*(진학남자수 + 진학여자수) / (졸업남자수 + 졸업여자수)
end as 진학률

# a 내림차순 출력
쿼리 뒤에 order by 진학률 컬럼 인덱스번호 desc; 추가

# c "고양시 일산" 포함 행 검색
select 지역명 from graduates where 지역명 like '경기도 고양시 일산%';

# d 지역명에서 "경기도 고양시 일산" 뺌
select replace(지역명, '경기도 고양시 일산', '') from graduates

# 정답. 어마어마한 괴물 쿼리가 탄생한다...
select extract(year from 기준년도) as 기준년도, 학교명, replace(지역명, '경기도 고양시', ''),
CASE
  when 졸업남자수 + 졸업여자수 = 0
  then 0
  else 100*(진학남자수 + 진학여자수) / (졸업남자수 + 졸업여자수)
end as 진학률
from graduates where 지역명 like '경기도 고양시 일산%' order by 4 desc;