5장 복수행 함수 변환 함수 및 조건부 표현식 사용

 

■5장 . 복수행 함수

• • 5장 목차

• • max, min, avg, sum, count 함수
  • 그룹함수에서의 null 값의 의미
  • having 절
  • 그룹함수를 중첩하는 방법

 

• • ■max 함수

: 최대값을 출력하는 함수

 

문제 101. 최대 월급을 출력하시오 !

SELECT MAX(sal) FROM EMP;

 

문제102. 직업이 SALESMAN인 사원들중에서의 최대월급을 출력하시오

SELECT MAX(sal) FROM EMP WHERE JOB = 'SALESMAN'

 

 

문제103. SK 텔레콤 사용 학생중 최대 나이를 출력하시오

SELECT  MAX(age)

FROM EMP2

WHERE TELECOM = 'sk';

 

3 SELECT telecom  MAX(age)

1 FROM EMP2

2 WHERE TELECOM = 'sk';

telecom은 여러개 나오려고 하고 max는 하나만 나오려고하니까 에러남

 

4 SELECT  telecom, MAX(age)

1 FROM EMP2

2 WHERE TELECOM = 'sk'

3 GROUP BY telecom;

 

• • ※ group by 역할 : 데이터를 grouping 하는 역할
  • select
  • from
  • where group by

 

 

문제104. 부서번호, 부서번호별 최대월급을 출력하시오!

SELECT  deptno, MAX(sal)

FROM EMP

GROUP BY deptno;

 

문제105. 전공, 전공별 최대나이를 출력하시오

SELECT major, MAX(age)

FROM EMP2

GROUP BY major;

 

문제106. 성씨별 최대나이를 출력하시오

SELECT substr(ename,1,1), MAX(age)

FROM EMP2

GROUP BY  substr(ename,1,1);

 

문제107. 위의 결과를 다시 출력하는데 나이가 높은 성부터 출력되게 하시오

SELECT substr(ename,1,1), MAX(age) 나이

FROM EMP2

GROUP BY  substr(ename,1,1)

ORDER BY  나이 desc;

 

문제108. 서울시 물가 데이터를 오라클 database에 입력하시오 !

 

문제109. a_price (가격)

서울시 물가 데이터중 최대 가격을 출력하시오!!

 

SELECT MAX(a_price) FROM price;

 

 

• • min 함수

: 최소값을 출력하는 함수

 

 

 

 

문제 110. 사원테이블에서 최소 월급을 출력하시오

SELECT MIN(sal) FROM EMP;

문제111. 직업, 직업별 최소월급을 출력하시오!

 

SELECT job, MIN(sal)

FROM EMP

GROUP BY job;

 

문제112. 위의 결과를 다시 출력하는데 직업이 null은

출력 안되게 하시오

 

SELECT job, MIN(sal)

FROM EMP

WHERE job IS NOT null

GROUP BY job;

 

문제113. 위의 결과를 다시 출력하는데 최소 월급이 높은 것부터 출력하시오

SELECT job, MIN(sal)

FROM EMP

WHERE job IS NOT null

GROUP BY job

ORDER BY min(sal) desc;

 

문제114. 사원 테이블에 가장 먼저 입사한 사원의 입사일을 출력하시오

select min(hiredate)

from emp;

 

문제115. 전공 , 전공별 최소나이를 출력하는데 컴공은 제외하고 출력하시오

select major, min(age)

  from emp2

 where major <> '컴퓨터공학'

group by major;

 

문제116. 주소, 주소별 최소나이를 출력하시오

select substr(address,1,7), min(age)

from emp2

group by substr(address,1,7);

 

• • count 함수 : 건수를 세는 함수

문제117. 사원 테이블의 전체 건수를 출력하시오

 

select count(empno)

from emp;

 

select count(*)

 from emp;

 

문제118. 커미션을 count하시오

select count(comm)

from emp;

 

 

문제119. 직업이 SALESMAN 인 사원들이 총 몇명이 잇는가

 

SELECT COUNT(*)

FROM EMP

WHERE job = 'SALESMAN';

 

문제120. 직업이 ANALYST 인 사원들은 총 몇명이 있는가?

 

SELECT COUNT(*)

FROM EMP

WHERE JOB='ANALYST';

 

※ GROUP 함수는 NULL 값을 무시한다!!

 

문제121. 직업 , 직업별 인원수를 출력하시오

SELECT JOB, COUNT(*)

FROM EMP;

 

 

문제122. 전공, 전공별 인원수를 출력하시오

SELECT MAJOR, COUNT(*)

FROM EMP2

GROUP BY MAJOR

문네123. 위의 결과를 다시 출력하는데 전공별 인원수가 높은 것 부터

SELECT MAJOR, COUNT(*)

FROM EMP2

GROUP BY MAJOR

ORDER BY COUNT(*) DESC;

 

문제124. 지역, 지역별 인원수를 출력하시오

 

 

SELECT SUBSTR(ADDRESS,1,7), COUNT(*)

FROM EMP2

GROUP BY SUBSTR(ADDRESS,1,7)

ORDER BY COUNT(*) DESC;

 

 

*avg 함수

: 평균값을 출력하는 함수

문제125. 사원 테이블에 월급을 평균내시오

 

select avg(sal)

  from emp

 ;

 

 

문제126. 우리반 평균나이를 구하세요

 

select avg(age)

  from emp2;

 

문제127. 통신사 , 통신사별 평균나이를 구하시오

 

SELECT telecom, AVG(age)

FROM EMP2

GROUP BY telecom;

 

문제128. (점심시간 문제)

 

이메일 도메인 명, 이메일 도메인 별 인우너수를 출력하느데

이메일 도메인별 인원수가 높은것 부터 출력하고

컬럼명을 아래 와 같이 이메일 도메인 , 인원수로 출력되게 하시오

 

SELECT  substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1) AS "이메일 도메인" , count(*) as "인원수"

from emp2

group by substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1);

 

문제129. 커미션의 평균값을 출력하시오

 

select avg(comm)

from emp;

 

※그룹함수는 null 값을 무시하기 때문에 comm을 다 다해서 4로 나눈값이다.

 

문제130. 위의 결과를 다시 출력하는데 전체 사원수로 나눠지게 하려면 어떻게

해야하는가.

 

select avg(nvl(comm,0) )

   from emp;

 

5.2 그룹함수에서 null값의 의미

 

" group 함수는 null 값을 무시한다."

예제1: 값이 달라지는 경우

 

select avg(comm) from emp;

select avg(nvl(comm,0)) from emp;

 

 

예제2:

 

select sum(comm) form emp;

select sum(nvl(comm,0)) from emp;

결과는 같으나 어떤 SQL이 성능이 좋을까?

널 값을 무시하지 않고 0으로 바꿔서 연산하는 밑에 연산이 비효율적이다.

 

• • group 함수의 특징 2가지

1. 1. 1. group 함수는 null값을 무시한다.
      2. group 함수는 결과를 항상 출력한다.

문제131. 이름과 월급과 직업을 출력하는데 월급이 높은 사원부터 출력하시오

select ename, sal, job

from emp

order by sal desc;

 

문제132. 월급을 출력하는데 decode를 이용해서 직어빙 president는 null이 나오게 하고  다른 직업이면 월급이 출력되게 하시오

 

select decode( job, 'PRESIDENT', ''

,sal)

from emp;

 

그런데 !!

 

 

 

SELECT MAX(decode(job,'PRESIDENT', NULL,sal))

      1           2                  3        4

from emp;

※ decode 문에서 세번째 인자값의 데이터 유형에 네이번째 인자값의 데이터 유형이 결정된다.

 

null은 데이터 유형이 문자형이다.

 

select to_char(sal)

from emp;

 

문자로 하면 9가 큰값이기 때문에

 

SELECT MAX(decode(job,'PRESIDENT', 0,sal))

 

from emp;

으로 쓰는게 좋아용

 

*sum 함수

: 값을 다 더하는 함수

문제 133. 우리반의 나이를 다 더하면 얼마인가?

 

select sum(age)

  from emp2;

 

 

문제134. 직업, 직업별 토탈 월급을 출력하시오

 

select job, sum(sal)

 from emp

group by job;

 

문제135. 직업과 직업별 토탈월급을 출력하는데 SALESMAN은 제외하고 출력하시오

4 select job, sum(sal)

1 from emp

2 where job <> 'SALESMAN'

3 group by job;

 

문제 135. 위 결과를 출력하는데 직업별 토탈월급이 6000 이상인것만 나오게 하시오

 

 

※ Group 함수로 조건을 줄 때 사용하는 절은 where 절이 아니라 having 절을 사용해야 한다.

 

5  select job, sum(sal)

1 from emp

2 where  job <> 'SALESMAN'

3 group by job

4 HAVING SUM(sal) >= 6000;

이렇게 하세요

having 절은 group by 다음에 나와야 해요

 

 

 

select 문의 6가지절

 

5 select        --- 컬럼명

1  from        --- 테이블명

2  where      --- 검색조건

3 group by   --- grouping 할 컬럼

4 having       --- group 함수로 조건을 줄 때

5 order by   ---- 데이터를 정렬하는 절

 

문제137. 통신사, 통신사별 인원수를 출력하는데

통신사가 sk는 제외하고 출력하고

통신사별 인원수가 6명이상인 것만 출력하시오

 

 

 

데이터 게시판 : 6번 범죄원인

 

 

문제138. 범죄유형에는 무엇이 있는지 조회하시오 !

(중복제거해서 표기하시오 )

SELECT DISTINCT crime_type

  FROM crime_cause2;

 

문제139. 범죄 원인은 무엇이 있는지 조회하시오 .

SELECT distinct term  FROM crime_cause2;

 

 

 

문제140. 범죄유형, 범죄유형별 총 건수를 출력하시오!

 

SELECT crime_type, SUM(cnt)

  from crime_cause2

  GROUP BY crime_type;

 

 

문제141. 위결과에서 건수 합계가 높은것 부터출력

SELECT crime_type, SUM(cnt)

  from crime_cause2

  GROUP BY crime_type

  ORDER BY SUM(cnt) desc;

 

문제142. 위의 결과에서 건 단위가 1000단위가 표시되게 하시오

 

SELECT crime_type, TO_CHAR(SUM(cnt),'999,999')

  from crime_cause2

  GROUP BY crime_type

  ORDER BY SUM(cnt) desc;

 

 

5.4 그룹함수를 중첩하는 방법

문제143. 직업, 직업별 토탈월급을 출력하시오 !

select job, sum(sal)

  from emp

 group by job;

 

문제144. 위의 결과중에 최대 값을 출력하시오.

  select MAX(sum(sal))

  from emp

 group by job;

 

문제145. 통신사중 가장 많은 인원수인 통신사의 인원수가 몇명인지 출력하라

 

 

 SELECT MAX(COUNT(*))

 FROM EMP2

 GROUP BY telecom;

 

 

 

문제146. 직업, 직업별 토탈월급을 출력하는데 직업이 SALESMAN은 제외하고

출력하고 직업별 토탈 월급이 4000이상인 것만 출력하고

직업별 토탈 월급을 높은 것 부터 출력하는데

직업별 토탈월급에 천단위를 부여하라.

 

 

 SELECT job, TO_CHAR(SUM(sal),'999,999')

   FROM EMP

  WHERE job <> 'SALESMAN'

  GROUP BY job

  HAVING SUM(sal) >= 4000

  ORDER BY SUM(sal) DESC;

 

 

 

 

 

 SELECT job, TO_CHAR(SUM(sal),'999,999')

   FROM EMP

 

  GROUP BY job

  HAVING SUM(sal) >= 5000 AND job <> 'SALESMAN'

  ORDER BY SUM(sal) DESC;

성능이 좋지 않음 SQL 인덱스를 사용하지 못함

 

 

 SELECT job, TO_CHAR(SUM(sal),'999,999') aaa

   FROM EMP

 

  GROUP BY job

  HAVING SUM(sal) >= 5000 AND job <> 'SALESMAN'

  ORDER BY aaa DESC;

해도 문자 순으로 구별하는게 아니라 숫자로 정렬해줌

 

문제 147. 직업, 직업별  부서번호별 토탈 월급을 출력하시오

select job,deptno, sum(sal)

  from emp

 group by job;

 

문제148. 통신사별 , 전공별, 인원수를 출력하시오!

  select telecom, major, count(*)

 from emp2

group by telecom, major

ORDER BY telecom , major;

 

 

 

■ 결과를 가로로 출력하는 방법

 

문제 149. 부서번호, 부서번호별 토탈월급을 출력하시오

  select deptno, sum(sal)

  from emp

 group by deptno;

 

 이거를

 

-가로 :

 

10	20	30
8750	10875	9400

가로로 나오게 케로로하라

 

 

힌트

 

 select ( decode(dettno, 10,sal ,0) as "10"

 from emp;

 

 

 

 

 

정답:

 

 select SUM(decode(deptno, 10,sal ,0)) AS "10",

                 SUM(decode(deptno, 20,sal ,0)) AS "20",

                 SUM(decode(deptno, 30,sal ,0)) AS "30"

   from emp;

 

 

 

 

 

삼성 sql 악성 SQL입니당!

 

 

문제150 통신사, 통신사별 인원수를 세로, 가로로 출력하시오

-세로

 

 SELECT telecom, COUNT(*)

 FroM EMP2

 GROUP BY telecom;

 

 

 

-가로

(카운트는 Null을 무시함)

SELECT COUNT(DECODE(telecom, 'sk',telecom,null)) AS "sk",

         COUNT(DECODE(telecom, 'lg',telecom,null)) AS "lg",

             COUNT(DECODE(telecom, 'kt',telecom,null)) AS "kt"

                 

 from EMP2;

 

 

 SELECT sum(DECODE(telecom, 'sk',1,0)) AS "sk",

         sum(DECODE(telecom, 'lg',1,0)) AS "lg",

              sum(DECODE(telecom, 'kt',1,0)) AS "kt"

                 

 from EMP2;

 

 

 SELECT sum(DECODE(telecom, 'sk',1,null)) AS "sk",

         sum(DECODE(telecom, 'lg',1,null)) AS "lg",

              sum(DECODE(telecom, 'kt',1,null)) AS "kt"

                 

 from EMP2;

 

널로 바꾸면 더 빨라짐

 

 

 

 

문제151. 나이 , 나이별 인원수를 출력하는데 세로, 가로로 출력하시오

-세로

 SELECT age, COUNT(*)

   FROM EMP2

  GROUP BY age

  ORDER BY age desc;

 

 

 

-가로

  SELECT SUM(DECODE(age,24,1,null)) AS "24",

   SUM(DECODE(age,25,1,null)) AS "25",

   SUM(DECODE(age,26,1,null)) AS "26",

   SUM(DECODE(age,27,1,null)) AS "27",

   SUM(DECODE(age,28,1,null)) AS "28",

   SUM(DECODE(age,29,1,null)) AS "29",

   SUM(DECODE(age,30,1,null)) AS "30",

   SUM(DECODE(age,31,1,null)) AS "31",

   SUM(DECODE(age,33,1,null)) AS "33",

   SUM(DECODE(age,35,1,null)) AS "35"

  FROM EMP2;

 

 

문제152. 위의 결과에서 전공도 같이 출력하시오!

  SELECT major, SUM(DECODE(age,24,1,null)) AS "24",

   SUM(DECODE(age,25,1,null)) AS "25",

   SUM(DECODE(age,26,1,null)) AS "26",

   SUM(DECODE(age,27,1,null)) AS "27",

   SUM(DECODE(age,28,1,null)) AS "28",

   SUM(DECODE(age,29,1,null)) AS "29",

   SUM(DECODE(age,30,1,null)) AS "30",

   SUM(DECODE(age,31,1,null)) AS "31",

   SUM(DECODE(age,33,1,null)) AS "33",

   SUM(DECODE(age,35,1,null)) AS "35"

  FROM EMP2

group by major;

 

 

문제153. (오늘의 마지막 문제!)메일 도메인 별, 전공별 인원수를 출력하시오

가로로 이쁘게

SELECT  substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1) AS "이메일 도메인" , 

SUM(DECODE(major,'산업경영공학',1,null)) AS "산업경영공학",

SUM(DECODE(major,'미술경영학',1,null)) AS "미술경영학",

SUM(DECODE(major,'경영정보학',1,null)) AS "경영정보학",

SUM(DECODE(major,'신문방송학',1,null)) AS "신문방송학",

SUM(DECODE(major,'정보통신공학',1,null)) AS "정보통신공학",

SUM(DECODE(major,'경영학',1,null)) AS "경영학",

SUM(DECODE(major,'컴퓨터공학',1,null)) AS "컴퓨터공학",

SUM(DECODE(major,'응용수학',1,null)) AS "응용수학",

SUM(DECODE(major,'관광학',1,null)) AS "관광학",

SUM(DECODE(major,'행정학',1,null)) AS "행정학",

SUM(DECODE(major,'경제학',1,null)) AS "경제학",

SUM(DECODE(major,'산업심리학',1,null)) AS "산업심리학",

SUM(DECODE(major,'전자공학',1,null)) AS "전자공학",

SUM(DECODE(major,'글로벌금융학',1,null)) AS "글로벌금융학",

SUM(DECODE(major,'통계학',1,null)) AS "통계학",

SUM(DECODE(major,'정보통계학',1,null)) AS "정보통계학",

SUM(DECODE(major,'물리학',1,null)) AS "물리학",

from emp2

group by substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1);

 

은총이문제 학과별 이메일 도메인(갯수)를 나타내어라

예

컴퓨터공학과	naver(3), gmail(1)
응용수학	gmail(1)

 

 

select major , substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1)

from emp2

group major;