데이터베이스 (집공부4)

1. 그룹 함수

 -> 그룹 함수는 하나 이상의 행을 그룹으로 묶어 연산하여 총합, 평균 등 하나의 결과로 나타납니다.

구 분	설 명
SUM	그룹의 누적 합게를 반환합니다.
AVG	그룹의 평균을 반환합니다.
COUNT	그룹의 총 개수를 반환합니다.
MAX	그룹의 최대값을 반환합니다.
MIN	그룹의 최소값을 반환합니다.
STDDEV	그룹의 표준편차를 반환합니다.
VARIANCE	그룹의 분산을 반환합니다.

1-1. SUM 함수

 -> 해당 칼럼 값들에 대한 총합을 구하는 함수입니다.

ex) SELECT SUM(SAL) FROM EMP;

1-2. AVG 함수

 -> 해당 칼럼 값들에 대해 평균을 구하는 함수입니다.

 -> 해당 칼럼 값이 NULL 인 것에 대해서는 제외하고 계산합니다.

ex) SELECT AVG(SAL) FROM EMP;

1-3. MAX, MIN

 -> 지정한 칼럼 값들 중에서 최대값을 구하는 함수가 MAX이고, 최소값을 구하는 함수가 MIN 입니다.

ex) SELECT MAX(SAL), MIN(SAL) FROM EMP;

-----------------------------------------------------------------------------------------------------------------------------

①. 가장 최근에 입사한 사원의 입사일과 입사한지 가장 오래된 사원의 입사일을 출력하는 쿼리문을 작성하시오.

select MAX(HIRE_DATE) as 입사일, MIN(HIRE_DATE) as 입사일 from EMPLOYEE; 

-----------------------------------------------------------------------------------------------------------------------------

1-4. 로우 개수 구하는 COUNT 함수

 -> COUNT 함수는 테이블에서 조건을 만족하는 행의 개수를 반환하는 함수입니다.

 -> COUNT 함수에 특정 칼럼을 기술하면 해당 칼럼 값을 갖고 있는 로우의 개수를 계산하여 되돌립니다.

 -> COUNT함수는 NULL값에 대해서는 개수를 세지 않습니다.

 -> COUNT함수에 COUNT(*)를 적용하면 테이블의 전체 로우수를 구하게 됩니다.

ex) SELECT COUNT(COMM) FROM EMP;

-----------------------------------------------------------------------------------------------------------------------------

②. 부서코드별로 개수가 몇개인지 나타내보시오

select DEPT_CODE, COUNT(*) from EMPLOYEE GROUP by DEPT_CODE order by 1;

③. 부서코드가 몇개인지 즉, 중복되지 않은 부서코드의 개수를 작성하시오 (null값제외)

select COUNT(DISTINCT DEPT_CODE) 부서개수 from EMPLOYEE; 

-----------------------------------------------------------------------------------------------------------------------------

2. GROUP BY 절

 -> 그룹함수를 쓰되 어떤 컬럼 값을 기준으로 그룹함수를 적용할 경우 GROUP BY 절 뒤에 해당 컬럼을 기술하면 됩니다.

 -> 합계, 평균, 최대값이나, 최소값 등을 어떤 칼럼을 기준으로 그 칼럼의 값별로 보고자 할 때 GROUP BY 절 뒤에 해당 칼럼을 기술하면 됩니다.

형식

SELECT 칼럼명, 그룹함수

FROM 테이블명

WHERE 조건 (연산자)

GROUP BY 칼럼명;

ex) select EMP_ID FROM EMPLOYEE GROUP by EMP_ID;

-----------------------------------------------------------------------------------------------------------------------------

④. 소속 부서별 평균 급여를 구해보시오

select DEPT_CODE, FLOOR(avg(salary)), count(*) from EMPLOYEE GROUP BY DEPT_CODE order by 1;

⑤. 소속 부서별 최대 급여와 최소 급여를 구하시오

select DEPT_CODE  소속부서, MAX(SALARY) 최대, MIN(SALARY)최저 FROM EMPLOYEE GROUP BY DEPT_CODE;

⑥. 소속 부서별로 인원수와 보너스 받는 사람수를 구하시오

select DEPT_CODE 소속부서, count(*)인원수, COUNT(BONUS) 보너스받는사람 from EMPLOYEE GROUP BY DEPT_CODE;

-----------------------------------------------------------------------------------------------------------------------------

3. HAVING 조건

 -> SELECT 절에 조건을 사용하여 결과를 제한할 때는 WHERE절을 사용하지만 그룹의 결과를 제한할 때는 HAVING 절을 사용합니다.

ex) SELECT DEPT_CODE, FLOOR(AVG(SALARY)) FROM EMPLOYEE GROUP BY DEPT_CODE HAVING AVG(SALARY)>=200000;

-----------------------------------------------------------------------------------------------------------------------------

⑦. 소속 부서의 최대값과 최소값을 구하되 최대 급여가 2900이상인 부서만 구해보시오

select DEPT_CODE, MAX(SALARY), MIN(SALARY) FROM EMPLOYEE GROUP BY DEPT_CODE HAVING MAX(SALARY)>2900;

-----------------------------------------------------------------------------------------------------------------------------

4. ROLLUP / CUBE

 -> 그룹화한 데이터에 대해 집계를 수행하는 함수

 -> ROLLUP을 적용한 부서의 직급별 합계 및 부서별 합계, 모든 부서의 총 합까지 출력

ex) SELECT DEPT_CODE 부서코드, JOB_CODE 직급코드, SUM(SALARY) 부서별급여합계 FROM EMPLOYEE GROUP BY ROLLUP(DEPT_CODE,JOB_CODE) ORDER by 1;

 -> CUBE 사용 시, 부서의 직급별 합계 및 부서별 합계, 모든 부서의 총합, 직급별 합계 까지 출력

ex) SELECT DEPT_CODE 부서코드, JOB_CODE 직급코드, SUM(SALARY) 부서별급여합계 FROM EMPLOYEE GROUP BY CUBE(DEPT_CODE,JOB_CODE) ORDER by 1;

-----------------------------------------------------------------------------------------------------------------------------

5. GROUPING

 -> ROLLUP 또는 CUBE 같은 집계 함수에 의해 도출된 결과는 1을 return / 그게 아니면 0을 return

-----------------------------------------------------------------------------------------------------------------------------

＊decode 팁!! :  인수가 짝이 안 맞을때 나머지를 else로 여김 

＊order by 팁!! : 정렬도 기준이 하나만 있는것이 아니라 뒤에 ,하고 두번째 정렬을 할 수 있음

-----------------------------------------------------------------------------------------------------------------------------

⑧. 부서별 인원 및 모든 부서의 총 인원을 출력하시오

select decode(grouping(dept_code),0,nvl(dept_code,'인턴'),1,'총 인원') 부서코드, count(*)||'명' 인원 

from employee group by rollup(dept_code) order by 1,2;

⑨. 부서별 인원 및 모든 부서의 총 인원을 출력하시오2

select decode(grouping(dept_code),0,nvl(dept_code,'인턴'),1,'합계') 부서코드, 

decode(grouping(dept_code),0,nvl(job_code,'부서별합계'),1,'합계') 직급코드, sum(salary) 부서별급여합계

from employee group by rollup(dept_code,job_code) order by 1,2;

-----------------------------------------------------------------------------------------------------------------------------

6. JOIN

 -> 원하는 정보가 2개 이상의 테이블에 나누어져있다면 여러번 질의를 할필요없이 조인기능을 쓰면 된다.

 -> 하나 이상의 테이블에서 연관된 정보를 모아 하나의 가상테이블로 만들어 주는 명령

구 분	내 용
Cross Join	다음은 Cross Join으로 특별한 키워드 없이 SELECT 문의 FROM 절에 테이블을 연속해서 연결하는 것이다.	select * from department,EMPLOYEE;

-----------------------------------------------------------------------------------------------------------------------------

6.1. Cross Join

 -> 특별한 키워드 없이 SELECT 문의 FROM 절에 테이블을 연속해서 연결하는 것이다.

종 류	설 명
Equi Join	동일 칼럼을 기준으로 조인합니다.
Non-Equi Join	동일 칼럼이 없이 다른 조건을 사용하여 조인합니다.
Outer Join	조인 조건에 만족하지 않는 행도 나타낸다.
Self Join	한 테이블 내에서 조인합니다.

-----------------------------------------------------------------------------------------------------------------------------

6.1.1. Equi Join

 -> 가장 많이 사용하는 조인 방법으로서 조인 대상이 되는 두 테이블에서 공통적으로 존재하는 컬럼의 값이 일치되는 행을 연결하여 결과를 생성한다.

ex) select * from Job, EMPLOYEE where job.job_code=employee.JOB_CODE;

 -> Equi Join에 AND 연산하기

ex) select * from Job, EMPLOYEE where job.job_code=employee.JOB_CODE AND BONUS is not null;

 -> 별칭 부여하기 (FROM 절 다음에 테이블 이름을 명시하고 공백을 둔 다음에 별칭을 지절하면 됩니다.)

ex) select e.emp_name, j.job_name,e.JOB_code, j.job_code from EMPLOYEE e, job j where e.JOB_CODE=j.JOB_CODE and e.emp_name='선동일';

-----------------------------------------------------------------------------------------------------------------------------

6.1.2. Non-Equi Join

 -> 조인 조건에 특정 범위 내에 있는지를 조사하기 위해서 WHERE 절에 조인 조건을 = 연산자 이외의 비교 연산자를 사용합니다.

-----------------------------------------------------------------------------------------------------------------------------

⑩. 각 department 테이블의 각 부서가 어느 지역에 위치하는지 location 테이블에서 찾아 출력하기

ex) select d.dept_title, l.Local_name from department d, location l where d.location_id=l.local_code;

⑪. EMPLOYEE 부서코드에 따른 DEPARTMENT 부서명 출력

ex) select e.dept_code , d.dept_title From employee e, department d where e.dept_code=d.dept_id;

⑫. 부서의 위치 아이디에 따른 지역이름(department, location 사용)

ex) select d.location_id, l.local_name from department d, location l where d.location_id=l.local_code;

⑬. 다중조인 문제. 부서별코드/부서이름/local_name과 national_name을 출력하기

ex) select d.dept_id, d.dept_title, l.local_name, n.national_name from DEPARTMENT d, location l,national n

where  d.location_id=l.local_code and l.national_code=n.national_code order by 1;

-----------------------------------------------------------------------------------------------------------------------------

6.1.3. Self Join

 -> 조인은 두 개 이상의 서로 다른 테이블을 서로 연결하는 것뿐만 아니라, 하나의 테이블 내에서 조인을 해야만 원하는 자료를 얻는

    경우가 생긴다.

 -> Self Join이란 말 그대로 자기 자신과 조인을 맺는 것을 말한다.

ex) select e1.emp_name, e2.emp_name from employee e1, employee e2 where e1.manager_id=e2.emp_id;

-----------------------------------------------------------------------------------------------------------------------------

6.1.4. Outer Join

 -> 조인 조건에 만족하지 못하였더라도 해당 로우를 나타내고 싶을 때에 사용하는 것이 외부조인(Outer Join)이다.

 -> NULL 값이기에 배제된 행을 결과에 포함시킬 수 있으며 다음과 같이"(+)" 기호를 조인 조건에서 정보가 부족한 칼럼 이름 뒤에 덧붙인다.

ex) select employee.ename || '의 매니저는' || manager.ename || '입니다.' FROM emp employee, emp manager WHERE employee.mgr=manager.empno(+);

-----------------------------------------------------------------------------------------------------------------------------

7. ANSI Join

 -> ANSI Cross Join

ex) SELECT * FROM EMP CROSS JOIN DEPT;

 -> ANSI INNER Join

 -> FROM 다음에 INNER JOIN 이란 단어를 사용하여 조인할 테이블 이름을 명시하고 ON절을 사용하여 조건절 명시한다.

ex) select * from table1 INNER JOIN table2 on table1.column1=table2.column2;

 -> USING을 이용한 조인 조건 지정하기

 -> 두 테이블에 각각 조인을 정의한 컬럼의 이름이 동일하다면 UNSING 절에서 조인할 컬럼을 지정하여 구문을 더 간단하게 표현 할 수 있다.

ex) SELECT * FROM table1 JOIN table2 USING (공통컬럼)

 -> NATURAL Join

 -> 두 테이블에 각각 조인을 정의한 컬럼의 이름이 동일하다면 USING 절에서 조인할 컬럼을 지정하여 구문을 더 간단하게표현가능하다.

ex) SELECT * FROM table1 NATURAL JOIN table2;

-----------------------------------------------------------------------------------------------------------------------------

8. FULL OUTER JOIN

 -> LEFT OUTER JOIN, RIGHT OUTER JOIN을 합한 형태이다.

ex) SELECT * FROM DEPT01 FULL OUTER JOIN DEPT02 USING(DEPTNO);

-----------------------------------------------------------------------------------------------------------------------------

연습문제

--문제 1 2020년 12월 25일이 무슨 요일인지 출력하기

select to_char(to_date(20201225),'day') from dual;

select to_char(to_date(sysdate),'month') from dual;

--문제 2 이름에 '형'자가 들어가는 직원들의 사번, 사원명, 부서명 출력하기

select EMP_ID 사번, emp_name 사원명, DEPT_CODE 부서명 from employee where emp_name like '%형%';

 

 --문제2-1. 이름에 '형'자가 들어가는 직원들의 사번, 사원명, 부서명(department) 출력하기

 select e.EMP_ID 사번, e.EMP_NAME 사원명, d.DEPT_TITLE from employee e, department d where e.emp_name like '%형%';

 

 --문제2-2. 이름에 '형'자가 들어가는 직원들의 사번, 사원명, 부서명(department)과 일치하는 값 출력하기

 select e.emp_id 사번, e.emp_name 사원명, d.dept_TITLE from employee e, department d where e.emp_name like '%형%'

  and d.dept_id = e.dept_code;

  

--문제 3 1970년대 생이면서 성별이 여자이고, 성이 전씨인 사람의 사원명, 주민번호, 부서명, 직급명을 출력하기 

select * from employee;

select * from department;

select * from job;

select e.emp_name 사원명, e.emp_no 주번, d.dept_title 부서명, j.job_name 직급명 from employee e, department d, job j

where e.emp_no like '%-2%' and (substr(e.emp_no,1,2) between '70' and '80')and e.emp_name like '%전%';

select e.emp_name 사원명, e.emp_no 주번, d.dept_title 부서명, j.job_name 직급명 from employee e, department d, job j

where e.emp_no like '%-2%' and (substr(e.emp_no,1,2) between '70' and '80')and e.emp_name like '%전%' and e.job_code=j.job_code;

select e.emp_name 사원명, e.emp_no 주번, d.dept_title 부서명, j.job_name 직급명 from employee e, department d, job j

where e.emp_no like '%-2%' and (substr(e.emp_no,1,2) between '70' and '80')and e.emp_name like '%전%' and e.job_code=j.job_code and e.dept_code=d.dept_id;

--문제 4 해외영업부에 근무하는 사원명, 직급명, 부서코드, 부서명을 출력하기

select * from employee;

select * from job;

select * from DEPARTMENT;

select e.emp_name 사원명 ,j.job_name 직급명, d.dept_id 부서코드,  d.dept_title 부서명 from employee e, job j, department d where d.dept_title like '해외영업%';

select e.emp_name 사원명 ,j.job_name 직급명, d.dept_id 부서코드,  d.dept_title 부서명 from employee e, job j, department d where d.dept_title like '해외영업%'

and e.job_code = j.job_code;

select e.emp_name 사원명 ,j.job_name 직급명, d.dept_id 부서코드,  d.dept_title 부서명 from employee e, job j, department d where d.dept_title like '해외영업%'

and e.job_code = j.job_code and d.dept_id = e.dept_code and d.dept_id = e.dept_code;

select e.emp_name 사원명, j.job_name 직급명, e.dept_code 부서코드 ,d.dept_title 부서명 

from employee e,department d,job j,national n,location l 

where d.DEPT_ID=e.dept_code and e.job_code=j.job_code and d.location_id=l.local_code and

n.NATIONAL_CODE=l.national_code and d.DEPT_TITLE in('해외영업1부','해외영업2부') order by 3;

--문제 5 보너스 포인트를 받는 직원들의 사원명, 보너스포인트, 부서명, 근무지역명 출력하기

select * from employee;

select * from job;

select * from DEPARTMENT;

select * from location;

select e.emp_name,e.bonus,d.dept_title,l.local_name from employee e, department d, location l where e.bonus is not null and e.dept_code = d.dept_id and l.local_code=location_id;

select e.emp_name 사원명, e.bonus 보너스포인트, d.dept_title 부서명, l.local_name 근무지역명 

from employee e,department d,location l 

where e.dept_code=d.dept_id and l.local_code= d.location_id 

and e.BONUS is not null order by 2;

--문제 6 보너스가 적용된 급여가 자신의 급여등급 최대한도를 넘어가는 직원을 찾으세요 출력 내용은 직원명, 직급명, 보너스가 적용된  급여등급,급여 총액,급여한도

select * from employee;

select * from job;

select * from DEPARTMENT;

select * from location;

select * from sal_grade;

select e.emp_name 직원명, j.job_name 직급, e.salary+e.salary*e.bonus 급여총액, s.max_sal 급여한도 from employee e, job j, sal_grade s 

where j.job_code=e.job_code and s.sal_level=e.sal_level and e.salary+e.salary*e.bonus>s.max_sal;

select e.emp_name 직원명,j.job_name 직급명, e.salary 급여, e.salary+e.salary*e.bonus 급여총액,s.MAX_SAL 급여한도 

from employee e,job j,sal_grade s where j.job_code=e.job_code and s.sal_level=e.sal_level and salary+salary*bonus>s.Max_sal;

--문제 7 한국(ko)와 일본 에 근무하는 직원들의 사원명, 부서명, 지역명, 국가명 출력하기

select * from employee;

select * from job;

select * from DEPARTMENT;

select * from location;

select e.emp_name 사원명, d.dept_title 부서명, l.local_code 지역명, l.NATIONAL_CODE from employee e, location l, department d where l.national_code in ('KO','JP'); 

select e.emp_name 사원명, d.dept_title 부서명, l.local_name 지역명, l.NATIONAL_CODE from employee e, location l, department d where l.national_code in ('KO','JP')

and e.dept_code = d.dept_id and d.location_id = l.local_code; 

select e.emp_name 사원명, d.dept_title 부서명,l.local_name 지역명, n.national_name 국가명 

from employee e, department d,location l,national n 

where d.location_id=l.local_code  and e.dept_code=d.dept_id and

n.NATIONAL_CODE=l.NATIONAL_CODE and l.national_code in ('KO','JP');