반응형
1) 단일 행 기타 함수
1-1) ifnull
1-2) 조건함수 if , case
2) 그룹함수
2-1) count
2-2 ) ** group by **
2-3) sum , avg
2-4) min max stddev variance
2-5 ) having
2-6) 순위지정 , 누계함수(rank ()over() , sum()over() )
3) 마무리 예제
1) @@ 단일행 기타함수 @@
1-1) ifnull
ifnull(컬럼,기본값) : 컬럼의 값이 null인 경우 기본값을 치환
-- 교수의 이름 , 직급 , 급여 , 보너스 , 급여+보너스
SELECT NAME , POSITION , salary , bonus , salary+bonus
FROM professor; #bonus가 null --> 연산불가
SELECT NAME , POSITION , salary , bonus , salary+bonus
FROM professor
WHERE bonus IS NOT null
UNION
SELECT NAME , POSITION , salary , bonus , salary
FROM professor
WHERE bonus IS NULL;↓ ↓ ↓ ↓ ↓ if null 사용으로 변경 ↓ ↓ ↓ ↓ ↓ ↓
-- ifnull사용 : ifnull(컬럼명 , 기본값)
# ifnull(bonus,0) : bonus컬럼의값이 null일경우 0으로치환해 연산
SELECT NAME , POSITION , salary , bonus , salary+IFNULL(bonus,0)
FROM professor;
#ifnull(salary+bonus,salary) : salary+bonus 가 null이면 salary로치환
SELECT NAME , POSITION , salary , bonus , IFNULL(salary+bonus,salary)
FROM professor;
예제)
#------예제-------
-- 교수의 이름 , 직책 , 급여 , 보너스 출력
-- 보너스없는 경우 보너스없음 출력
SELECT NAME , POSITION , salary , IFNULL(bonus,'보너스없음')
FROM professor;
-- 학생의 이름 지도교수의 번호 출력
-- 지도교수번호가 null이면 999로 출력하자
SELECT s.name , IFNULL(s.profno, 999)
FROM student s;
1-2) 조건함수 if , case
1) if 조건함수 : if(조건문, '참','거짓')
(거짓에 여러개의 if문을 넣어줄수있음)
사용 예
-- 1학년 학생인 경우는 신입생으로 1학년 학생이 아닌경우 재학생으로출력
SELECT NAME , grade, if(grade=1,'신입생','재학생') 신입생여부
FROM student;
#-----문제----------------------------------
-- 교수명 , 학과번호 , 학과명
-- 학과명은 학과번호가 101 : 컴공 , 나머지는 기타 학과으로 출력
SELECT NAME , deptno , if(deptno=101,'컴공',' 기타학과') 학과
FROM professor;
# -- 학생의 주민번호 7번쨰 자리가 1,3 인경우 남자 , 2 ,4 인경우 여자
# 그외는 주민번호 오류
SELECT NAME , jumin ,
if(SUBSTR(jumin,7,1)=1 ,"남자",
if(SUBSTR(jumin,7,1)=2,"여자",
if(SUBSTR(jumin,7,1)=3,"남자",
if(SUBSTR(jumin,7,1)=4,"여자","오류") ) ) )성별
FROM student;
#----in을 이용해 더 간단하게.----
SELECT NAME , jumin ,
if(SUBSTR(jumin,7,1) IN(1,3),"남자",
if(SUBSTR(jumin,7,1) IN(2,4),"여자","주민번호오류"))성별
#1번쨰if문 거짓일 시 2번쨰if문 사용
# 2번쨰if문도 거짓이라면 주민번호오류 발생
FROM student;
예제)
-- 문제1
-- 교수이름 , 학과번호 , 학과명출력
-- 학과명 : 101 컴공 , 102 : 멀공 , 201: 기공 , 그외 : 그외학과
SELECT NAME , deptno ,
if(deptno=101,"컴공",
if(deptno=102,"멀공",
if(deptno=201,"기공",'그외학과'))) 학과명 #괄호 갯수 잘 확인해!
FROM professor;
2)Case조건문
사용법
1) case 컬럼명 when 값1 then 문자열
when 값2 then 문자열
...
else 문자열 end
2) case when 조건문1 then 문자열
when 조건문2 then 문자열
....
else 문자열 end;
위에서 if문으로 다뤘던문제를
case로 바꿔봤음; (가독성 ↑ )
-- 교수이름 , 학과번호 , 학과명출력
-- 학과명 : 101 컴공 , 그외학과 : 그외학과
select NAME ,deptno ,
(case deptno when 101 then "컴공"
ELSE " 그외학과" END )학과명
FROM professor;
-- 문제1
-- 교수이름 , 학과번호 , 학과명출력
-- 학과명 : 101 컴공 , 102 : 멀공 , 201: 기공 , 그외 : 그외학과
SELECT NAME , deptno,
case deptno when 101 then '컴공'
when 102 then '멀공'
when 201 then '기공'
ELSE '그외학과' END 학과명
FROM professor;
예시2)
#----------------------문제-------------------------------------------
-- 교수이름 , 학과번호 , 대학명 출력
-- 대학명 : 101,201,201:공대 , 그외 : 그외 대학
#if+in
SELECT NAME , deptno,
if(deptno IN(101,201,201) ,'공대','그외 대학')
FROM professor;
#case(switch 느낌)
SELECT NAME ,deptno,
case deptno when 101 then '공대'
when 102 then '공대'
when 201 then '공대'
ELSE '그외 대학' END 학과명
FROM professor;
#case(조건ver)
SELECT NAME ,deptno,
case when deptno in(101,102,201) then '공대'
ELSE '그외 대학' END 학과명
FROM professor;
예제)
-- 학생명 , 주민번호 , 출생분기 출력
-- 출생분기 : 주민번호기준 1~3 :1분기 4~6 2분기 7~9 3분기 10~12 4분기
SELECT NAME,jumin ,
case when SUBSTR(jumin,3,2) BETWEEN 1 AND 3 then '1분기'
when SUBSTR(jumin,3,2) BETWEEN 4 AND 6 then '2분기'
when SUBSTR(jumin,3,2) BETWEEN 7 AND 9 then '3분기'
when SUBSTR(jumin,3,2) BETWEEN 10 AND 12 then '34분기'
ELSE '오류' END 출생분기
FROM student;# substr(jumin,3,2)로 주민번호 3번쨰자리부터 2개를 가져옴 (월)
# 가져온 월을 between 함수로 숫자구간을 정해준 후 각각 문자열을 정해줌
case when 조건문1 then 문자열
when 조건문2 then 문자열
....
else 문자열 end 사용
생일은 date타입이므로 이와같은 방식으로 조건문 설정가능 !
-- 학생 이름 , 생일 , 출생분기
-- 출색분기 : 생일기준으로 !!
SELECT NAME , birthday ,
case when MONTH(birthday) BETWEEN 1 AND 3 then '1분기'
when MONTH(birthday) BETWEEN 4 AND 6 then '2분기'
when MONTH(birthday) BETWEEN 7 AND 9 then '3분기'
when MONTH(birthday) BETWEEN 10 AND 12 then '4분기'
ELSE '오류' END 출생분기
FROM student;
2)@@ 그룹함수 @@
그룹함수 : 여러개의 행의 정보 이용해 결과 리턴 함수
select 컬럼명 |* from 테이블명
[where 조건문]
[group by 컬럼명] # 여기서조건문을 사용할수없음
--> 레코드를 그룹화하기 위한 함수
[having 조건문] # group by의 조건
[order by 컬럼명||별명||컬럼순서 [asc||desc]]
2-1) count
count() :레코드의 갯수 리턴 , null값은 갯수에서 제외
-- 교수의 전체인원수 보너스를 받는 인원수 조회
-- count(bonus) : bonus 값이 null이 아닌 레코드 수
-- count(*) : 레코드 수
SELECT COUNT(*) , COUNT(bonus) FROM professor;
-- 학생 중 전공1학과(major1) 101인 학과에 속한 학생 수 조회
SELECT COUNT(*)'101학과학생수'
FROM student
WHERE major1 = 101;
-- 1학년 학생의 전체인원수와 지도교수를 배정받은 학생의 인원조회
SELECT COUNT(*) , COUNT(profno)
FROM student
WHERE grade=1;
-- 2학년 학생의 전체인원수와 지도교수를 배정받은 학생의 인원조회
SELECT COUNT(*) , COUNT(profno)
FROM student
WHERE grade=2;
-- 3학년 학생의 전체인원수와 지도교수를 배정받은 학생의 인원조회
SELECT COUNT(*) , COUNT(profno)
FROM student
WHERE grade=3;
-- 4학년 학생의 전체인원수와 지도교수를 배정받은 학생의 인원조회
SELECT COUNT(*) , COUNT(profno)
FROM student
WHERE grade=4;
-- 학생 전체인원 수와 지도교수를 배정받은 학생인원수 조회
SELECT COUNT(*) 학생수 , COUNT(s.profno) '학생(지도교수O)'
FROM student s;
2-2) group by
[group by 컬럼명] 여기서조건문을 사용할수없음
--> 레코드를 그룹화하기 위한 함수
(select문 맨 앞에 그룹화한 컬럼을 적어주는게 좋다!!!!! )
사용 예시
#학년별 전체인원수와 지도교수배정받은 학생의 인원수조회---
SELECT grade, COUNT(*) , COUNT(profno)
FROM student
GROUP BY grade;
#전공1학과별 전체인원 , 지도교수 배정받은 학생의 인원수조회
SELECT major1 , COUNT(*) , COUNT(profno)
FROM student
GROUP BY major1;
#지도교수가 배정되지 않은 학년의 전체인원수 출력
SELECT grade , COUNT(*)'지도교수X학생'
FROM student
GROUP by grade
HAVING COUNT(profno)=0
2-3) sum , avg
#합계 : sum , 평균 :avg
#교수들의 급여합계 , 보너스합계 출력
SELECT SUM(salary),SUM(bonus)
FROM professor;
-- 교수들 급여평균과 보너스평균
SELECT COUNT(*),SUM(salary),SUM(bonus),AVG(salary),AVG(bonus)
FROM professor;
bonus가 null이라면 ' / ' 연산을 하지않기때문에 발생하는 문제!
해결법
-- 교수들의 급여평균과 보너스평균 ( 보너스없는교수도 포함해출력)
-- avg(ifnull(bonus,0)) : bonus받는 교수의 평균
SELECT COUNT(*),SUM(salary),SUM(bonus),AVG(salary),AVG(ifnull(bonus,0))
FROM professor;보너스가null이라면 0으로 대체해준 후 평균을 구한다!
-- 문제
-- 교수의 부서코드 , 부서별 인원수 , 급여합계 , 보너스합계 , 급여평균
-- 보너스평균 출력 , (단 보너스없는교수도 평균에포함)
SELECT deptno , COUNT(*) , SUM(salary) , SUM(bonus) , AVG(salary),AVG(IFNULL(bonus,0))
FROM professor
GROUP BY deptno;
예제)
#------------문제2 -----------
-- 학년별 , 학생의 인원수 , 키와몸무게의 평균
-- 학년순 정렬
SELECT grade,COUNT(*),AVG(height),AVG(weight)
FROM student
GROUP BY grade
ORDER BY grade;
#------------문제3 --------------
# 부서별교수의 (급여,보너스,연봉) 합,보너스 출력
# 연봉 : 급여*12 + 보너스
# 보너스가없는경우 0
# 평균출력시 소숫점이하 2자리 반올림
SELECT deptno , SUM(salary) 급여합,
SUM(bonus) 보너스합,
SUM(salary*12+bonus) 연봉합,
round(AVG(salary),2) 급여평균,
round(AVG(bonus)) 보너스평균,
round(avg(salary*12+bonus)) 연봉평균
FROM professor
WHERE IFNULL(bonus,0) #bonus가 null이라면0으로대체
GROUP BY deptno;sum(컬럼) 컬럼의합
avg(컬럼) 컬럼의 평균
bonus에 null값이 존재시 0으로 대체 (ifnull())이용
부서별로 정렬 ( group by ) 이용
소수점이하2자리반올림 (round(컬럼,2)) 이용
2-4) min max stddev variance
min max
-- 전공1학과별 가장 키가 큰 학생의키값 , 가장 작은 키 값
SELECT major1 , MAX(height) , MIN(height)
FROM student
GROUP BY major1;
-- 교수 중 최고급여 값
SELECT concat(MAX(salary),'만원') 최고급여,concat(Min(salary),'만원') 최저급여
FROM professor;
stddev (표준편차) , variance(분산)
-- 교수들의 평균급여, 급여의표준편차 , 분산
SELECT AVG(salary) , STDDEV(salary) , variance(salary)
FROM professor;
-- 학생의 점수테이블에서 합계 평균 합계표준편차 합계분산
SELECT SUM(math+eng+kor) 합계, AVG(math+eng+kor) 평균 ,STDDEV(math+eng+kor) 표준편차
,VARIANCE(math+eng+kor) 분산
FROM score
2-5) having
having : 그룹의조건
-- group by에서 설정된컬럼만 select컬럼으로 사용
#ex) 학과별 가장 키가 큰 학생의키와 , 가장작은학생의 키 , 학과별 평균키출력
-- 평균키가 172.5이상인 학과정보를 출력
SELECT major1 , MAX(height),MIN(height) , AVG(height)
FROM student
GROUP BY major1
HAVING AVG(height)>172.5;having절을 넣지않을경우
having절을 추가한경우
-- 교수테이블에서 학과별 평균급여 350이상인 부서의 코드와 평균급여출력
SELECT deptno , AVG(salary)
FROM professor p
GROUP BY p.deptno
HAVING AVG(salary >= 350;
# 주민번호기준 남, 여학생의 최대키 , 최소키 , 평균키 나타내보자
SELECT if(SUBSTR(jumin,7,1) IN(1,3) ,'남','여') 성별 ,
MAX(height) , MIN(height) , AVG(height)
FROM student
GROUP BY if(SUBSTR(jumin,7,1) IN(1,3) ,'남','여');
-- ----학생의 생일의 월별 인원수 출력하기------
#month 사용 (birthday는 date타입임)
SELECT concat(MONTH(birthday),'월') 생월, COUNT(*) 인원수
FROM student
GROUP BY MONTH(birthday);
#substr + instr로 첫번째'-'를 찾아서 그뒤의 2자리사용
SELECT SUBSTR(birthday,INSTR(birthday,'-')+1,2)'생월', COUNT(*)
FROM student
GROUP BY SUBSTR(birthday,INSTR(birthday,'-')+1,2);
# 각각의 속성을 만듬
SELECT CONCAT(COUNT(*)+"",'건수') 전체 ,
SUM(if(MONTH(birthday)=1,1,0)) '1월',
SUM(if(MONTH(birthday)=2,1,0)) '2월',
SUM(if(MONTH(birthday)=3,1,0)) '3월',
SUM(if(MONTH(birthday)=4,1,0)) '4월',
SUM(if(MONTH(birthday)=5,1,0)) '5월',
SUM(if(MONTH(birthday)=6,1,0)) '6월',
SUM(if(MONTH(birthday)=7,1,0)) '7월',
SUM(if(MONTH(birthday)=8,1,0)) '8월',
SUM(if(MONTH(birthday)=9,1,0)) '9월',
SUM(if(MONTH(birthday)=10,1,0)) '10월',
SUM(if(MONTH(birthday)=11,1,0)) '11월',
SUM(if(MONTH(birthday)=12,1,0)) '12월'
#if(month(birthday)=x , 1,0) : birthday의 month가 x면 1을 만듬
# sum(xxx) : 1을 모두더함
FROM student;
# 각각의 컬럼을 만듬(count 이용 count는null을 더해주지않음)
SELECT CONCAT(COUNT(*)+"",'건수') 전체 ,
count(if(MONTH(birthday)=1,1,null)) '1월',
count(if(MONTH(birthday)=2,1,null)) '2월',
count(if(MONTH(birthday)=3,1,null)) '3월',
count(if(MONTH(birthday)=4,1,null)) '4월',
count(if(MONTH(birthday)=5,1,null)) '5월',
count(if(MONTH(birthday)=6,1,null)) '6월',
count(if(MONTH(birthday)=7,1,null)) '7월',
count(if(MONTH(birthday)=8,1,null)) '8월',
count(if(MONTH(birthday)=9,1,null)) '9월',
count(if(MONTH(birthday)=10,1,null)) '10월',
count(if(MONTH(birthday)=11,1,null)) '11월',
count(if(MONTH(birthday)=12,1,null)) '12월'
# if birthday의 month가 x면 1을 넣어줌 , 아니라면 null
# 그 후 1의 갯수를 count로 센다
FROM student;
2-6) 순위지정 , 누계함수(rank ()over() , sum(column)over() )
순위지정함수 : rank()over(정렬방식)
누계 함수 : sum(컬럼)over(정렬방식)
-- 교수의 번호 , 이름 , 급여 , 급여 순위 출력
SELECT NO , NAME , salary , RANK()OVER(ORDER BY salary DESC) 급여내림차순
FROM professor;
-- 예제
-- score 테이블에 학번 국수영 , 총점 , 총점기준 등수출력
SELECT *, (kor+math+eng) 총점
, RANK()OVER(ORDER BY (kor+math+eng)) 등수
,RANK()OVER(ORDER BY kor) 국어등수
,RANK()OVER(ORDER BY math) 수학등수
,RANK()OVER(ORDER BY eng) 영어등수
FROM score;
# 교수의 이름 , 급여 , 보너스 , 급여누계 조회
SELECT name , salary , bonus , SUM(salary)OVER(ORDER BY salary desc) 급여누계
FROM professor;# score 테이블에서 학번 국어 수학 영어 총점 총점누계 총점등수
SELECT studno , kor , math , eng , (kor+math+eng) ,
RANK()OVER(order by kor+math+eng desc) 총점등수,
SUM(kor+math+eng)OVER(ORDER BY kor+math+eng desc) 총점누계
FROM score;
with Rollup 함수
(group에서만 사용)
부분합 구하는 함수
# 학년별 , 지역 , 몸무게평균 , 키평균 조회
SELECT grade , SUBSTR(tel,1,INSTR(tel,')')-1) 지역,
AVG(weight) 몸무게평균, AVG(height) 키평균
FROM student
GROUP BY grade,SUBSTR(tel,1,INSTR(tel,')')-1);
# 각 학년의몸무게평균,키평균까지 구해줌
SELECT grade , SUBSTR(tel,1,INSTR(tel,')')-1) 지역,
sum(weight) 몸무게합,sum(height) 키합,AVG(weight) 몸무게평균, AVG(height) 키평균
FROM student
GROUP BY grade,SUBSTR(tel,1,INSTR(tel,')')-1) WITH ROLLUP;
# 각 학년의몸무게평균,키평균까지 구해줌
#학년으로 그룹을 나눈 후 각 학년안에서 지역번호로 한번 더 나눈다
#with rollup 으로 각학년의 키의 총 합,몸무게총합,몸무게평균 , 키평균을구해줌
간단예제
# 학년별 , 성별 몸무게평균, 키평균 조회하기 학년별로도 평균조회
SELECT grade , if(SUBSTR(jumin,7,1) IN(1,3),'남','여') 성별,
AVG(weight) 몸무게평균, AVG(height) 키평균
FROM student
GROUP BY grade , if(SUBSTR(jumin,7,1) IN(1,3),'남','여') WITH ROLLUP;학년별로 그룹을 만든 후 그룹내에서 남 , 여로 그룹을 나눈다.
남 여의 평균들을 구한 후
rooll up 을 통해 학년별 평균(남+여/2)을 출력해준다
내가만든 예제
#생년 별 , 학과 몸무게,키(합 평균) 구해보자
SELECT year(birthday) , major1 , sum(height),sum(weight),AVG(height) , AVG(weight)
FROM student
GROUP BY YEAR(birthday),major1 WITH rollup;한마디로 학과별 합평균도 구하고 , 년도별의 합 평균도 구하는 것.
3) 마무리 예제
-- 1. 학생의 이름과 지도교수번호 조회하기
-- 지도교수가 없는 경우 지도교수배정안됨 출력하기
SELECT NAME , IFNULL(profno,'지도교수X')
#ifnull(컬럼, null값일 시 대체문자)
FROM student;
--
-- 2. major 테이블에서 코드, 코드명, build 조회하기
-- build 값이 없는 경우 '단독 건물 없음' 출력하기
SELECT m.code , m.`name` , IFNULL(build,'단독건물X')
FROM major m;
-- 3. 학생의 이름, 전화번호, 지역명 조회하기
-- 지역명 : 지역번호가 02 : 서울, 031:경기, 032:인천 그외 기타지역
SELECT NAME , tel , #left(tel,instr(tel,')')-1) 을 통해 ')'의 앞에 번호(지역번호)만 반환
case when LEFT(tel,INSTR(tel,')')-1) = 02 then '서울'
when LEFT(tel,INSTR(tel,')')-1) = 031 then '경기'
when LEFT(tel,INSTR(tel,')')-1) = 032 then '인천'
ELSE '그외 기타지역' end
FROM student;
-- 4. 학생의 이름, 전화번호, 지역명 조회하기
-- 지역명 : 지역번호가 02,031,032: 수도권, 그외 기타지역
SELECT NAME , tel , #left로 하거나 substr로 구하거나 결과는같음!!(취향차이)
case when SUBSTR(tel,1,INSTR(tel,')')-1) IN(02,031,032) then '수도권'
ELSE '그외 기타지역' end
FROM student;
-- 5. 학생을 3개 팀으로 분류하기 위해 학번을 3으로 나누어
-- 나머지가 0이면 'A팀',
-- 1이면 'B팀',
-- 2이면 'C팀'으로
-- 분류하여 학생번호, 이름, 학과번호, 팀 이름을 출력하여라
# 팀의 오름차순 정렬을해보자 a팀 b팀 c팀
SELECT studno , NAME ,major1 ,
case when MOD(studno,3) = 0 then 'a팀'
when MOD(studno,3) = 1 then 'b팀'
ELSE 'c팀' END # 학번%3 이 0 or 1 이 아니면 c팀
FROM student s
order by MOD(studno,3);
-- 6. score 테이블에서 학번, 국어,영어,수학, 학점, 인정여부 을 출력하기
-- 학점은 세과목 평균이 95이상이면 A+,90 이상 A0
-- 85이상이면 B+,80 이상 B0
-- 75이상이면 C+,70 이상 C0
-- 65이상이면 D+,60 이상 D0
-- 인정여부는 평균이 60이상이면 PASS로 미만이면 FAIL로 출력한다.
-- 으로 출력한다.
SELECT studno , kor , eng , math ,
case when (kor+eng+math)/3 >=95 then 'A+'
when (kor+eng+math)/3 >=90 then 'A'
when (kor+eng+math)/3 >=85 then 'B+'
when (kor+eng+math)/3 >=80 then 'B'
when (kor+eng+math)/3 >=75 then 'C+'
when (kor+eng+math)/3 >=70 then 'C'
when (kor+eng+math)/3 >=65 then 'D+'
when (kor+eng+math)/3 >=60 then 'D'
ELSE '재수강' END 학점, #else 문자 end 문은 꼭 들어가야함!!!
if((kor+eng+math)/3,'PASS','FAIL') 인정여부
FROM score
ORDER BY (kor+eng+math)/3 DESC;
-- 7. 학생 테이블에서 이름, 키, 키의 범위에 따라 A, B, C ,D그룹을 출력하기
-- 160 미만 : A그룹
-- 160 ~ 169까지 : B그룹
-- 170 ~ 179까지 : C그룹
-- 180이상 : D그룹
SELECT NAME , height ,
if(height > 180 ,'d',
if(height BETWEEN 170 AND 179 , 'C',
if(height BETWEEN 160 AND 169 ,'B','A'))) 키그룹
FROM student
ORDER BY height ASC;
-- 8. 교수테이블에서 교수의 급여액수를 기준으로 200이하는 4급, 201~300 : 3급, 301~400:2급
-- 401 이상은 1급으로 표시한다. 교수의 이름, 급여, 등급을 출력하기
-- 단 등급의 오름차순으로 정렬하기
SELECT NAME , salary ,
case when salary <= 200 then '4급'
when salary BETWEEN 201 AND 300 then '3급'
when salary BETWEEN 301 AND 400 then '2급'
ELSE '1급' end
FROM professor
ORDER BY salary DESC;
#돈이많을수록 숫자가적으므로 돈기준 내림차순!
-- 9 학생의 학년별 키와 몸무게 평균 출력하기.
-- 학년별로 정렬하기.
-- 평균은 소숫점2자리 반올림하여 출력하기
SELECT grade , ROUND(AVG(height),2) 키평균 ,ROUND(AVG(weight),2) 몸무게평균
FROM student
GROUP BY grade;
-- 10. 평균키가 170이상인 전공1학과의
-- 가장 키가 큰키와, 가장 작은키, 키의 평균을 구하기
SELECT major1 , MAX(height) 큰키 , MIN(height) 작은키 , AVG(height) 키평균
FROM student
GROUP BY major1 #전공1학과를 그룹화
HAVING AVG(height) >=170; # 평균 키 170이상인 과만 조회
-- 11. 사원의 직급(job)별로 평균 급여를 출력하고,
-- 평균 급여가 1000이상이면 '우수', 작으면 '보통'을 출력하여라
#floor를 이용해 작은 근사치 정수로반환
SELECT e.job , floor(AVG(salary)) 평균급여 , if(AVG(salary) >= 1000 , '우수','보통') 평가
FROM emp e
GROUP BY e.job;
-- 12. 학과별로 학생의 평균 몸무게와 학생수를 출력하되
-- 평균 몸무게의 내림차순으로 정렬하여 출력하
SELECT major1 , COUNT(*) 학생수 , AVG(weight) 평균몸무게
FROM student
GROUP BY major1
ORDER BY AVG(weight) DESC;
-- 13. 학과별 교수의 수가 2명 이하인 학과번호, 인원수를 출력하기
SELECT deptno , COUNT(*) 인원수
FROM professor
GROUP BY deptno #학과별로 그룹을 만듬
HAVING COUNT(*) <= 2; # 각 그룹의 수가 2 이하인 그룹만 남는다
-- 14. 전화번호의 지역번호가 02서울 031 경기, 051 부산, 052 경남, 나머지 그외지역
-- 학생의 인원수를 조회하기
SELECT COUNT(*) ,
case when (SUBSTR(tel,1,INSTR(tel,')')-1) = 02) then '서울'
when (SUBSTR(tel,1,INSTR(tel,')')-1) = 031) then '경기'
when (SUBSTR(tel,1,INSTR(tel,')')-1) = 051) then '부산'
when (SUBSTR(tel,1,INSTR(tel,')')-1) = 052) then '경남'
ELSE '그외지역' END
FROM student
GROUP BY SUBSTR(tel,1,INSTR(tel,')')-1);
-- 15. 전화번호의 지역번호가 02서울 031 경기, 051 부산, 052 경남, 나머지 그외지역
-- 학생의 인원수를 조회하기. 가로출력
SELECT COUNT(*) 총인원,
COUNT(if(SUBSTR(tel,1,INSTR(tel,')')-1) = 02,1,NULL))'서울',#count는 null을 세지않음.
COUNT(if(SUBSTR(tel,1,INSTR(tel,')')-1) = 031,1,NULL))'경기',
COUNT(if(SUBSTR(tel,1,INSTR(tel,')')-1) = 051,1,NULL))'부산',
COUNT(if(SUBSTR(tel,1,INSTR(tel,')')-1) = 052,1,NULL))'경남',
COUNT(if(SUBSTR(tel,1,INSTR(tel,')')-1) not IN(02,031,051,052),1,NULL))'그외지역'
# not in(a,b,c,) abc가 아닌경우!
FROM student;
-- 16. 교수들의 번호,이름,급여,보너스, 총급여(급여+보너스)
-- 급여많은순위,보너스많은순위,총급여많은 순위 조회하기
-- 총급여순위로 정렬하여 출력하기. 보너스없는 경우 0으로 함
SELECT f.no , f.name , f.salary , IFNULL(bonus,0) , (f.salary+IFNULL(bonus,0)) 총급여,
RANK()OVER(ORDER BY salary DESC) 급여순 , RANK()OVER(order by ifnull(bonus,0) DESC) 보너스순위
FROM professor f;
-- 17. 교수의 직급,직급별 인원수,급여합계,보너스합계,급여평균,보너스평균 출력하기
-- 단 보너스가 없는 교수도 평균에 포함되도록 한다.
-- 급여평균이 높은순으로 정렬하기
# count는 상관이없지만 sum ,avg 같은 연산자에null이존재하면 null로변해버림
SELECT POSITION , COUNT(*) 인원, SUM(salary) , SUM(IFNULL(bonus,0)) , AVG(salary),AVG(IFNULL(bonus,0))
FROM professor
GROUP BY POSITION;
--
-- 18. 1학년 학생의 인원수,키와 몸무게의 평균 출력하기
SELECT grade,COUNT(*) 인원수 , AVG(height) , AVG(weight)
FROM student
GROUP BY grade
HAVING grade=1;
-- 19. 학생의 점수테이블(score)에서 수학 평균,수학표준편차,수학분산 조회하기
SELECT AVG(math)'수학평균' , STDDEV(math) 표준편차 , VARIANCE(math) 수학분산
FROM score s;
-- 20. 교수의 월별 입사 인원수를 출력하기
SELECT concat(mONTH(hiredate),'월')입사월 ,COUNT(*) 인원수
FROM professor
GROUP BY MONTH(hiredate); ##다른방법도 가능하지만 date타입이므로 month로 월별로 그룹만듬
'부트캠프(DB)' 카테고리의 다른 글
| 부트캠프 27일차 (DDL,DML,VIEW,사용자관리) (0) | 2025.03.11 |
|---|---|
| 부트캠프 26일차(서브쿼리 , DDL) (0) | 2025.03.10 |
| 부트캠프 25일차 (join (0) | 2025.03.07 |
| 부트캠프23일차 (집합연산자 함수 날짜) (1) | 2025.03.05 |
| 자바/스프링 부트캠프 22일차( 시작,SELECT, WHERE ) (0) | 2025.03.04 |