07월 25일 목 | SQL활용 06 - SQL Join

 

 

SQL View와 Index

Join

--테이블 두개 조인 select T1.*, T2.* from T1 join T2 on T1.x = T2.x; --테이블 세개 조인 select T1.*, T2.*, T3.* from T1 join T2 on T1.x = T2.x join T3 on T1.y = T3.y; --테이블 네개 조인 select T1.x, T2.y, T3.z, T4.* from T1 join T2 on T1.x = T1.x join T3 on T1.y = T3.y join T4 on T1.z = T4.z;

 

-- 테이블명.컬럼명 으로 결합 select emp.empno, emp.ename, dept.deptno, dept.dname from emp join dept on emp.deptno = dept.deptno;

 

 

--2개 테이블에 중복되지 않은 컬럼명은 테이블명 생략가능 select empno, ename, dname, loc  from emp join dept on emp.deptno = dept.deptno;

컬럼명이 다른 테이블과 중복이 아닐 때는 테이블명을 생략해도 되지만

테이블명이 겹친다면 테이블몀을 명시해주어야한다.

 

 

테이블에 별칭주기

select EM.*, DE.* from emp EM, dept DE where EM.deptno=DE.deptno;

 

 

논리적테이블에 별칭을 주었다.

select AA.* from (         select empno, ename, EM.deptno, dname, loc         from emp EM join dept DE         on EM.deptno = DE.deptno         ) AA;

 

 

이런 식으로 사용할 수 있다.

select AA.empno, AA.ename, AA.deptno, AA.dname, AA.loc from (         select empno, ename, EM.deptno, dname, loc         from emp EM join dept DE         on EM.deptno = DE.deptno         ) AA;

 

select empno, ename, deptno, dname, loc from (         select empno, ename, EM.deptno, dname, loc         from emp EM join dept DE         on EM.deptno = DE.deptno         ) AA;

select할 때 AA라는 별칭을 생략할 수 있다. 그래도 같은 결과가 나온다.

 

테이블 select 실행순서를 보자면

select empno, ename, deptno, dname, loc --2번실행 from (         select empno, ename, EM.deptno, dname, loc         from emp EM join dept DE         on EM.deptno = DE.deptno         ) AA --1번실행 order by AA.deptno, ename; --3번실행

 

 

 

 

문제 2) 각 부서의 인원수를 합계를 내어, 인원수순으로 아래와 같이 조회하시오.
-- 연구팀 5명
-- 총무팀 4명
-- 경리팀 2명

 

select AA.dname, count(AA.dname)||'명' as 인원수 --2번실행 from (         select ename, EM.deptno, dname, loc         from emp EM join dept DE         on EM.deptno = DE.deptno         ) AA --1번실행 group by AA.dname order by 인원수 desc;

 

group by

count

order by 를 사용해야하는 문제였다.

그리고 그렇게 사용했음

 

부서별 인원수를 구하려면 group을 지어야한다.

 

 

 

 

 

 

문제 3) 각 부서별 급여, 커미션의 합계를 구한 후 급여합계 순으로 아래와 같이 조회하시오.
-- 총무팀 1655 413
-- 연구팀 1535 307
-- 경리팀 425 212

 

select AA.dname, sum(sal) as 급여합계 ,sum(nvl(comm,0)) as 커미션합계 --2번실행 from (         select ename, sal, comm, dname         from emp EM join dept DE         on EM.deptno = DE.deptno         ) AA --1번실행 group by AA.dname order by 급여합계 desc;

 

 

 

 

 

문제 4) 각 지역별 커미션합계를 구한 후, 그 커미션합계가 20이하 지역만 지역순으로 아래와 같이 조회하시오.
-- 대구 15
-- 대전 13

select AA.loc, round(sum(nvl(comm,0)),0) as 커미션합계 --2번실행 from (         select ename, sal, comm, dname, loc         from emp EM join dept DE         on EM.deptno = DE.deptno         ) AA --1번실행 group by AA.loc having round(sum(nvl(comm,0)),0) <= 20 order by AA.loc desc;

 

선생님 풀이

select AA.loc, round(sum(nvl(comm,0)),0) from (     select dept.loc, emp.comm     from emp join dept     on emp.deptno = dept.deptno ) group by AA.loc having round(sum(nvl(AA.comm,0)),0) <= 20 order by AA.loc;

 

 

학사관리프로그램 짜기

우선 엑셀과 같은 도구들로 테이블의 시나리오를 짜본다.

학생, 교과목, 수강신청을 하고 수강신청 총 학점

 

차근차근 생각해보면서 구조를 짜면 우선

 

학생테이블 ----------- 수강 ----------- 과목테이블

1                                                       n

n                                                       1

n                                                       m

 

있어야 한다.

 

비슷한 예로

쇼핑몰, 영화예매 등이 있음.

 

회원테이블 ------------ 예매 ----------- 영화테이블

1                                                       n

n                                                       1

n                                                       m

 

이런 관계 중간에 교차테이블이 있어야한다.

그렇지 않으면 테이블 모양이 이상해짐.

 

회원테이블 ------------ 구매 ----------- 상품테이블

 

 

* 한글로 되어있는 자료를 절대 PK로 줄수 없다.