sql 기본 / 복수행함수

ㅁ복습

1. 오라클 설치(18c)

2. 기본 select 문 : select , from , where, order by (실행순서 : from > where > order by > select)

3. 단일행 함수 : 문자, 숫자, 날자, 변환, 일반 

   복수행 함수 : max, min, avg, sum, count

 

 

036 최대값 출력하기(MAX)

max(컬럼명)

--사원 테이블에서 최대 월급을 출력하세요
select max(sal)
 from emp;

문제125. 우리반에서 최대 나이를 출력하시오

select max(age)
 from emp14;

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

select max(sal)
 from emp
 where job = 'SALESMAN';
--최대월급만 출력됨

문제127. 문제126번을 다시 수행하는데 아래와 같이 salesman도 같이 출력되게 하시오.

SALESMAN 1600

select max(sal)
 from emp
 where job = 'SALESMAN';
 
/* max(sal)은 한가지인데 job은 여러가지이기 때문에 "단일그룹의 그룹 함수가 아닙니다" 에러
   > 그룹으로 묶을 필요가 있음 > group by 컬럼명 함수*/
 
select job, max(sal)
 from emp
 where job = 'SALESMAN'
 group by job ;

 

ㅇ group by 절 : 데이터를 grouping, select절에 그룹함수와 더불어 컬럼명을 기술하고자 할때는 반드시 group by 절에 해당 컬럼명을 적어주고 실행해야 오류를 피할 수 있음.

코딩순서 : select > from > where > group by

실행순서 : from > where > group by > select

 

문제128. 직업과 직업별 최대월급을 출력하시오

select job, max(sal)
 from emp
 group by job;

뮨재129. emp14 class_type, class_type별 최대나이를 출력하시오

select class_type, max(age)
 from emp14
 group by class_type;

문제130. 부서번호, 부서번호별 최대월급을 출력하는데 최대월급이 높은것부터 출력하시오

select deptno, max(sal)
 from emp
 group by deptno
 order by 2 desc;

문제131. 입사년도(4자리), 입사한 연도별 최대월급을 출력하시오

select to_char(hiredate, 'RRRR'), max(sal)
 from emp
 group by to_char(hiredate,'RRRR');

ㅁ 복습설명 :  날짜에서 연도, 월, 일, 요일 등을 추출하려면 to_char를 이용해서 날짜를 문자로 명시적 형변환을 해줘야함.

ㅁ to_char 날짜포맷의 ww, iw란?

--오늘이 올해의 몇주차인지 출력하는 것 
select to_char(to_date('2021/01/08','RRRR/MM/DD'),'ww')
 from dual; # 출력값 02 
--1~7일을 한 주로 계산해서 7단위로 주차를 끊어버림
 
select to_char(to_date('2021/01/08','RRRR/MM/DD'),'iw')
 from dual; # 출력값 01
--해당 연도의 주차를 나타냄 > 달력상 월요일을 기준으로 한 주차수
--1월1일이 수요일 이전이면 1주의 시작, 목요일 이후면 전년도로 포함되어 들어감.

 

 

037 최소값 출력하기(MIN)

--직업이 SALESMAN인 사원들 중에서 최소월급을 출력하세요
select min(sal)
 from emp
 where job = 'SALESMAN';

문제132. 부서번호가 20번인 사원들 중에서 최소월급을 출력하시오

select min(sal)
 from emp
 where deptno = 20;

문제133. 우리반에서 최소나이를 출력하시오

select min(age)
 from emp14
 where class_type = 'A';

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

select job,min(sal)
 from emp
 group by job;

문제135. 위의 결과를 다시 출력하는데 직업을 abcd순으로 출력하시오

select job,min(sal)
 from emp
 group by job
 order by 1 asc;

코딩순서 : select > from > group by > order by

실행순서 : from > group by > select > order by

문제136. 부서번호, 부서번호별 최소월급을 출력하는데 부서번호별 최소월급이 낮은 것부터 출력하시오

select deptno, min(sal)
 from emp
 group by deptno
 order by 2 asc;

문제137. 통신사, 통신사별 최소나이를 출력하시오

--내가 쓴 코드
select decode(lower(telecom), 'kt', 'kt','lg','lg','sk'), min(age)
 from emp14
 group by decode(lower(telecom), 'kt', 'kt','lg','lg','sk');
 
--선생님이 쓴 코드
select decode(lower(telecom), 'skt','sk',lower(telecom)), min(age)
 from emp14
 group by decode(lower(telecom), 'skt','sk',lower(telecom));
 
--decode를 이용해서 통신사를 전처리해줘야 

--다른 학생이 쓴 코드
select replace(lower(telecom),'skt','sk')
 from emp14;

문제138. 입사한 연도(4자리), 입사년도별 최소월급을 출력하는데 입사한 년도를 asc하게 출력하세요

select to_char(hiredate, 'RRRR'), min(sal)
 from emp
 group by to_char(hiredate, 'RRRR')
 order by 1 asc;

 

 

038 평균값 출력하기(AVG)

--사원 테이블에서 평균 월급을 출력하세요
select avg(sal)
 from emp;

문제139. 우리반 테이블에서 평균나이를 출력하시오

select round(avg(age))
 from emp14;

문제140. 직업, 직업별 평균월급을 출력하시오

select job, round(avg(sal))
 from emp
 group by job;

 

ㅇavg 유의점

--1. 커미션만 출력
select comm
 from emp;

--2. 커미션의 평균값 출력
select avg(comm)
 from emp; 
 --출력값 550 = null값을 제외하여 평균값을 구함 =  comm / 4 로 계산됨
 --group 함수는 null값을 무시하기 때문

문제141. 그렇다면 커미션을 다 더해서 전체 사원수로 나누게 하려면 어떻게 해야할까요?

select avg(nvl(comm,0))
 from emp;
 --출력값 157

문제142. class_type, class_type별 평균나이를 출력하시오

 select class_type, avg(age)
  from emp14
  group by class_type;

문제143. 부서번호, 부서번호별 평균월급을 출력하는데 부서번호별 평균월급을 출력할때 천단위가 표시되게 하시오

select deptno, to_char(avg(sal),'999,999,999') as 평균월급
 from emp
 group by deptno;
 
--to_char(컬럼명,'999,999,999.99') 소수점 둘째자리까지 표기

문제144. 직업, 직업별 평균월급을 출력하는데 직업이 SALESMAN은 제외하고 출력하시오

select job, round(avg(sal))
 from emp
 where job != 'SALESMAN'
 group by job;

문제145. (점심시간 문제) 통신사, 통신사별 평균나이를 출력하는데 통신사별 평균나이를 소수점 이하는 안나오게 반올림하고 통신사별 평균나이가 높은순서대로 출력하시오

select decode(lower(telecom),'sk','skt',lower(telecom)) as 통신사, round(avg(age)) as 평균나이
 from emp14
 group by decode(lower(telecom),'sk','skt',lower(telecom))
 order by 2 desc;

 

 

039 토탈값 출력하기(SUM)

--부서번호, 부서번호별 토탈 월급을 출력하세요
select deptno, sum(sal)
 from emp
 group by deptno;

ㅁ group 함수의 특징 = null값을 무시함.

--아래 2개의 sql 중 대용량 데이터 환경에서 속도가 빠른 sql은 무엇일까?
select sum(comm)
 from emp;
 
select sum(nvl(comm,0))
 from emp;
 
--sum(comm) 이 속도가 더 빠름 > group 함수는 null값을 무시하기 때문
--sum(nvl(comm,0)) 은 null값을 0으로 전부 치환하고 0을 sum연산에 포함시키기 때문에 속도가 느림.

문제146. 직업, 직업별 토탈월급을 출력하는데 직업별 토탈월급이 높은것부터 출력하시오

select job, sum(sal)
 from emp
 group by job
 order by 2 desc;

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

select job, sum(sal)
 from emp
 where job != 'SALESMAN'
 group by job
 order by 2 desc;
 
--where절에 조건 기입

문제148. 직업, 직업별 토탈월급을 출력하는데 직업별 토탈월급이 5000이상인 것만 출력되게 하시오

select job, sum(sal)
 from emp
 where sum(sal) >= 5000;
 group by job
/* 에러 
 group 함수로 검색조건을 주는 것은 where절에서 사용할 수 없음
 where 절은 group 함수외에 일반 검색조건을 줄 때만 사용할 수 있음
 group 함수로 검색조건을 주는 절 : having */

--having 절을 이용한 코드
select job, sum(sal)
 from emp
 group by job
 having sum(sal) >= 5000;

 

ㅁ select 문의 6가지 절(코드순서)

select 

 from

 where

 group by

 having

 order by

(실행순서 : from > where > group by > having > select > order by)

 

문제149. 부서번호, 부서번호별 평균월급을 출력하는데 부서번호별 평균월급이 2000 이상인 것만 출력하고 부서번호별 평균월급이 높은 것부터 출력하시오

select deptno, round(avg(sal))
 from emp
 group by deptno
 having avg(sal) >= 2000
 order by 2 desc;

문제150. 직업, 직업별 토탈월급을 출력하는데 직업이 SALESMAN은 제외하고 출력하고 직업별 토탈월급이 높은 것부터 출력하시오

select job, sum(sal)
 from emp
 where job != 'SALESMAN'
 group by job
 order by sum(sal);

--group 함수를 제외한 나머지 일반적인 조건은 where절에 넣으면 됨

문제151. 위의 결과를 다시 출력하는데 직업별 토탈월급이 5000 이상인 것만 출력하시오

select job, sum(sal)
 from emp
 where job != 'SALESMAN'
 group by job
 having sum(sal) >= 5000
 order by sum(sal);

문제152. 부서번호, 부서번호별 토탈월급을 출력하는데 부서번호 20번은 제외하고 출력하고 부서번호별 토탈월급이 2000 이상인 것만 출력하고 부서번호별 토탈월급이 높은 것부터 출력하시오 

select deptno, sum(sal)
  from emp
  where deptno != 20
  group by deptno
  having sum(sal) >= 2000
  order by 2 desc;
  
  --(주의) having 절에 일반검색조건을 넣으면 검색 성능이 느려짐 > 일반검색조건은 where

문제153. 직업, 직업별 토탈월급을 출력하는데 직업이 SALESMAN은 제외하고, 직업별 토탈월급이 4000이상인 것만, 직업별 토탈월급이 높은 것부터, 직업별 토탈월급에 천단위를 부여하여 출력하시오

select job, to_char(sum(sal),'999,999,999')
   from emp
   where job != 'SALESMAN'
   group by job
   having sum(sal) >= 4000
   order by 2 desc;

 

 

040 건수 출력하기(COUNT)

--사원 테이블의 전체 인원수가 어떻게 되는지 출력하세요
select count(*)
 from emp;
 
 --전체 개수를 count 하는데 *가 아닌 특정 컬럼을 넣을 경우 null값이 있을 수 있음. 
 --null값 있는지 없는지 확인해야함 > *로 검색하는게 안전함

문제154. 직업이 SALESMAN인 사원들이 전부 몇명인지 카운트 하시오

select count(*)
 from emp
 where job = 'SALESMAN';

문제155. 우리반에 통신사가 LG인 학생들은 전부 몇 명이 있는지 카운트하시오

select count(*)
 from emp14
 where lower(telecom) = 'lg';

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

select job, count(*)
 from emp
 group by job;

문제157. 위의 결과를 다시 출력하는데 직업이 SALESMAN은 제외하고 출력하시오

select job, count(*)
 from emp
 where job <> 'SALESMAN'
 group by job;

문제158. 위의 결과를 다시 출력하는데 직업별 인원수가 높은 것부터 출력하시오

select job, count(*)
 from emp
 where job <> 'SALESMAN'
 group by job
 order by 2 desc;

문제159. 위의 결과를 다시 출력하는데 직업별 인원수가 2명이상인 것만 출력하시오

select job, count(*)
 from emp
 where job <> 'SALESMAN'
 group by job
 having count(*) >= 2
 order by 2 desc;

문제160. 우리반 테이블에서 나이, 나이별 인원수를 출력하는데 나이별 인원수가 높은 것부터 출력하시오

select age, count(*)
 from emp14
 group by age
 order by 2 desc;

문제161. 통신사, 통신사별 인원수를 출력하는데 통신사별 인원수가 높은 것부터 출력하시오

select replace(lower(telecom),'skt','sk'), count(*)
  from emp14
  group by replace(lower(telecom),'skt','sk')
  order by 2 desc;

문제162. 이메일의 도메인, 이메일의 도메인별 인원수를 출력하세요

select substr( substr(email, 1, instr(email, '.')-1), instr(email,'@')+1), count(*)
 from emp14
 group by substr( substr(email, 1, instr(email, '.')-1), instr(email,'@')+1);

문제163. 우리반 이름의 성씨를 출력하고 성씨별 인원수를 출력하는데 성씨별 인원수가 높은것부터 출력하세요

 select substr(ename,1,1), count(*)
  from emp14
  group by substr(ename, 1,1)
  order by 2 desc;