Day 17 (SQL)

단일 행 함수 변환함수

변환함수는 DB사용시 무조건 사용해 줘야한다. DB설정값에서 자동으로 해준다고 해도 불확실하므로 안전하게 한번 더 해준다.

날짜 출력을 변환하는 형식에서 연도는 YYYY로 4자리로 출력한다.(※YY나 RR도 있는데 두자리 표기는 1999년 Y2K이후에 사용하지 않는다.)

월을 출력하는 형식은 MM, MONTH, MON 3가지가 있는데 MM은 월을 두자리 숫자로 출력된다. MONTH는 영문이나 한글로 표현한 월이 출력된다. MON은 영문이나 한글로 표현하는 월을 약자로 출력한다. 하지만 한글에서는 MONTH나 MON이나 동일하다. 

일을 출력하는 형식은 DD, DAY, DY 3가지가 있는데 DD은 일을 두자리 숫자로 출력된다. DAY는 영문이나 한글로 표현한 요일이 출력된다. DY는 영문이나 한글로 표현하는 요일을 약자로 출력한다. 

시간은 24시로 표시하는 HH24와 12시로 표현하는 HH가 있다. HH에는 오전과 오후를 표기하기위해 AM혹은 PM을 같이 적는다.

분은 MI로 두자리 숫자로 분을 표시한다.

초는 SS로 두자리 숫자로 초를 표시하는 방식과 SSSSSS로 하루를 초로 표시한 다음 표현하는 방식이 있다. SSSSSS의 경우에는 0~86399의 범위를 갖는다. 왜냐하면 86400초가 되면 날짜가 바뀌기 때문이다.

 

숫자 출력을 변환하는 형식은 8가지가 있는데 표로 나타내어 보겠다.

표현 문자	의미(예)
9	숫자의 출력 폭 지정(자리수가 부족하면 생략한다.)
0	선행 0 표기 (자리수를 반드시 맞춘다.)
$	화폐 표기 (달러)
L	지역 화폐 표기 (각 국가 코드에 따라 다르다.)
,	쉼표 위치 지정
.	마침표 위치 지정
MI	음수의 -기호를 오른쪽에 표기
EEEE	실수 표현법을 이용

 

여기서 주의할점은 'L'을 사용할때 시스템 설정에 따라 \이 지원되지 않는다면 ￥나 $로 표기되는 경우도 있다. 그리고 ','사용의 국제 표준은 3자리마다 ','를 사용하는 것이다.

마지막으로 변환 함수에는 TO_CHAR(날짜/숫자, 출력형식)는 날짜나 숫자를 문자로 변환한다. 그런데 이미 문자열을 입력하는 것이므로 출력 데이터의 형식을 지정하는 의미로 보는 것이 맞다.

TO_DATE(문자,해석형식)은 데이터를 날짜 형으로 해석하는 것인데 받은 문자를 날짜 형식으로 바꾸는 것이다. 만약 1203,YYMM을 넣으면 12년 3월로 해석하는 것이다.

TO_NUM은 데이터를 숫자로 변환하는 것인데 그럴 필요가 없으므로 굳이 사용하지 않는다.

 

 

1. 학생의 평균 평점을 다음 형식에 따라 소수점 이하 두 자리까지 검색한다. (XXX 학생의 평균 평점은 X.XX입니다.)

2. 교수의 부임 일을 다음 형식으로 검색한다. (XXX 교수의 부임일은 YYYY년 MM월 DD일입니다.)

3. 교수 중에 3월에 부임한 교수의 명단을 검한다.

4. 화학과 1학년 학생들의 4.5 환산 평점을 다음 형식에 따라 소수점 이하 두 자리까지 검색한다.(XXX학생의 4.5 환산 평점은 X.XX입니다.)

 

---

그룹 함수와 GROUP BY

함수명	기능
MAX	값들 중에 최대 값을 반환한다. MAX(칼럼)
MIN	값들 중에 최소 값을 반환한다. MIN(칼럼)
AVG	평균값을 계산한다. AVG(칼럼)
COUNT	반환된 행의 수를 계산한다. COUNT(칼럼| * )//NULL은 무시한다.
SUM	합계를 계산한다. SUM(칼럼)
STDDEV	표준편차를 계산한다. STDDEV(칼럼)
VARIANCE	분산을 계산한다. VARIANCE(칼럼)

 

그룹함수를 사용할때 주의할사항은 다음과 같다. 

 

√ NULL값은 무시된다. 존재하지 않는 행으로 생각한다.

√ 반드시 단 하나의 값만을 반환한다.

√ GROUP BY 설정 없이 일반 컬럼과 기술 될 수 없다.

 

 

 

1. 각 학과별 학생 수를 검색한다.

2. 화학과와 생물학과 학생 4.5 환산 평점의 평균을 각각 검색한다.

첫 번째 segment가 맞는 답이고 두 번째와 세 번째 segment는 틀렸다. 두 번째 segment는 WHERE이 GROUP BY보다 앞에 나와야 한다. 세 번째 segment는 WHERE절에 입력해준 조건이 말이 안되는 조건이기 때문이다. 

3. 부임일이 10년 이상 된 직급별(정교수, 조교수, 부교수) 교수의 수를 검색한다.

4. 과목명에 화학이 포함된 과목이 학점수 총합을 검색한다.

COUNT를 사용할때 GROUP BY로 지정하면 각 조건당으로 계산한다.

5. 화학과 학생들의 기말고사 성적을 성적순으로 검색한다.

6. 학과별 기말고사 평균을 성적순으로 검색한다.

7. 30번 부서의 업무별 연봉의 평균을 검색한다.

단 출력 양식은 소수 이하 두 자리까지 통일된 형식으로 출력한다.

 

8. 물리학과 학생 중에 학년별로 성적이 가장 우수한 학생의 평점을 검색한다.

개인적으로 가장 많이 고민했던 실습이다. GROUP BY를 할때 묶어주는 값과 비교하는 값을 확실하게 이해하지 못했던것이라고 생각한다.

9. 학년별로 환산 평점의 평균값을 검색한다.

단 출력 양식은 소수이하 두 자리까지 통일된 양식으로 출력한다.

10. 화학과 1학년 학생 중 평점이 평균 이하인 학생을 검색한다.

두번째로 풀이에 굉장히 오래 걸렸던 실습이다.  실제로 코딩하려고 했던 내용은 각 과의 1학년 평균학점을 구하고 그 값들을 비교하려고 했는데 그룹함수를 이중으로 사용하지 못했다.

 

---

WHERE와 HAVING은 조건절을 적는 부분으로 같은 역할을 한다. 다만 WHERE은 단일 행 함수 조건에 사용하고, HAVING은 그룹 함수 조건에 사용한다. 따라서 WHERE절에서 사용할 것인지 HAVING절에서 사용할 것인지를 확실하게 인지하고 사용해야한다. 아래 실습하다 이 부분을 실수해서 데이터가 잘못 출력된 것이 있으니 확인해봐야 한다.

1. 화학과를 제외하고 학과별로 학생들의 평균 평점을 검색한다.

2. 화학과를 제외한 각 학과별 평균 평점 중에 평점이 2.0 이상인 정보를 검색한다.

3. 기말고사 평균이 60점 이상인 학생의 정보를 검색한다. (학번과 기말고사 평균)

그룹함수를 지정해야 SELECT에서 열을 출력할 수 있다. 그렇지 않으면 에러가 발생한다.

4. 강의 학점수가 3학점 이상인 교수의 정보를 검색한다. (교수번호, 이름과 담당 학점수)

5. 기말고사 성적이 핵 화학과목보다 우수한 과목의 과목명과 담당 교수명을 검색한다.

6. 근무 중인 직원이 4명 이상인 부서를 검색한다.

HAVING절에 적어야 할 조건을 WHERE절에 적은 경우 에러가 발생한다.

7. 업무별 평균 연봉이 2만불 이상인 업무를 검색한다.

8. 각 학과의 학년별 인원중 인원이 5명 이상인 학년을 검색한다.

학년도 참조하고 싶었지만 출력되지 않는다.

9. 인원수가 가장 많은 학과를 검색한다.

'중첩된 레벨이 너무 깊습니다.'라는 코드가 왜 뜨는지 모르겠다.

10. 학생 중 기말고사 성적이 가장 낮은 학생의 정보를 검색한다.

서브쿼리 사용하는 결과와 GROUP BY를 사용하는 결과