하나의 테이블에 원하는 데이터가 모두 있다면 좋겠지만, 두 개의 테이블을 엮어야 원하는 결과가 나오는 경우도 많다.
이때, 조인을 사용하면 두 개의 테이블을 엮어 원하는 데이터를 추출할 수 있다.
chapter04에서 join의 사용법과 inner join(내부 조인)을 배웠었다.
이번에는 outer join(외부 조인)과 self join , 서브쿼리에 대해서 배워볼것이다
outer join(외부 조인)
outer join은 한쪽에만 데이터가 있어도 결과가 나온다. 즉, 한 테이블에 null값이라도 데이터를 추출한다.
outer join은 크게 left outer join, right outer join, full outer join으로 나눌 수 있다.
- left outer join : 왼쪽 테이블의 모든 값이 출력되는 조인
- right outer join : 오른쪽 테이블의 모든 값이 출력되는 조인
- full outer join : 왼쪽 또는 오른쪽 테이블의 모든 값이 출력되는 조인
left outer join
left outer join은 왼쪽 테이블의 모든 값이 출력되는 조인으로, 표기법은 다음과 같다.
--오라클 방식 : 조인컬럼 오른쪽에 (+) 표시한다.
select s.name, p.name from student s, professor p
where s.profno = p.profno(+);
--ANSI 방식 : 테이블 조인시 left outer join을 명시한다.
select s.name, p.name from student s left outer join professor p
where s.profno = p.profno;right outer join
right outer join 은 오른쪽 테이블의 모든 값이 출력되는 조인으로, 표기법은 다음과 같다.
--오라클 방식 조인컬럼 왼쪽에 (+)를 표시한다.
select s.name, p.name from student s, professor p
where s.profno(+) = p.profno;
--ANSI 방식 : 테이블 조인 시 right outer join를 명시한다.
select s.name, p.name from student s right outer join professor p
where s.profno = p.profno;full outer join
양쪽 테이블의 모든 값이 출력되는 조인으로, 표기법은 다음과 같다.
--오라클 방식 : left outer join과 right outer join을 union한다.
select s.name,p.name from student s, professor p
where s.profno = p.profno(+)
union
select s.name, p.name from student s, professor p
where s.profno(+) = p.profno;
--ANSI 방식 full outer join을 하면된다.
select s.name, p.name from student s full outer join professor p
on s.profno = p.profno;문제
--교수별로 교수이름과 지도학생수를 출력하기
--지도학생이 없는 교수도 조회하도록 하기
select p.name,count(s.name) from professor p,student s
where p.profno = s.profno(+)
group by p.name;
self join
self join은 조인되는 테이블이 같은 경우에 사용하는 조인이며, 테이블 이름은 반드시 별명을 설정해야한다.
또한 조회되는 모든 컬럼은 별명을 붙여야 한다.
select d1.dcode as 부서코드, d1.dname as 부서명, d1.pdept, d2.dname
from dept2 d1, dept2 d2
where d1.pdept = d2.dcode(+)
order by d1.dcode;문제
--dept2 테이블에서 상위부서명, 하위부서의 갯수 출력
--모든 부서 조회하기
select d2.dname as "상위부서명" ,count(d1.dname) as 하위부서개수
from dept2 d1, dept2 d2
where d1.pdept(+) = d2.dcode
group by d2.dname
order by 2 desc;
sub query(서브쿼리)
하나의 SQL 문에 포함되어 있는 또 다른 SQL문을 말한다.
sub query는 단일행서브쿼리, 다중행서브쿼리로 나눌 수 있다.
- 단일행 서브 쿼리 : 서브쿼리의 결과가 1개인 경우.
- 사용가능연산자 : =, <, <=, >, >=, <>
- 다중행서브쿼리 : 서브쿼리의 결과가 여러개인 경우.
- 사용가능연산자 : in, [any,all]
단일행 서브 쿼리
서브쿼리가 단일 행 비교 연산자와 함께 사용할 때는 서브쿼리의 결과 건수가 반드시 1건 이하여야 한다.
만약 결과가 2건 이상인 경우 오류가 발생한다.
--예시
select s.name, s.grade, s.deptno1, d.dname
from student s, department d
where s.deptno1=d.deptno
and s.grade > (select grade from student where name = '김진욱');
--교수테이블에서 김현정 교수보다 나중에 입사한 교수의 이름, 입사일, 학과명 출력하기
select p.name,p.hiredate,d.dname
from professor p, department d
where p.deptno = d.deptno
and p.hiredate > (select hiredate from professor
where name ='김현정');다중행 서브 쿼리
서브쿼리의 결과가 2건 이상 반환될 수 있다면 반드시 다중 행 비교 연산자와 함께 사용해야 한다.
다중 행 비교 연산자는 다음과 같다.
- In : 서브쿼리의 결과에 존재하는 임의의 값과 동일한 조건을 의미한다.
- All : 서브쿼리의 결과에 존재하는 모든 값을 만족하는 조건을 의미한다.
- Any : 서브쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미한다.
--emp2, dept2 테이블을 이용하여 근무지역이 서울지사인 모든사원의
--사번, 이름, 부서번호 조회하기
--join방식
select e.empno,e.name,e.deptno
from emp2 e, dept2 d
where e.deptno = d.dcode
and d.area = '서울지사';
--sub query방식
select empno,name,deptno
from emp2
where deptno in (select dcode from dept2 where area='서울지사');문제
--emp2 테이블에서 과장직급의 최소 연봉자보다 연봉이 높은사람의
--이름, 직급, 연봉(pay) 조회하기
select name,position,pay from emp2
where pay > (select min(pay) from emp2 where position = '과장');
--학생 각 학년의 최대키를 가진 학생의 학년과 이름 키를 출력하기
--1학년 중 최대키를 가진 학생의 이름 키 출력하기
select grade,name,height from student
where (grade,height) in
(select grade, max(height) from student group by grade);
--교수 중 학과별로 입사일이 가장 빠른 교수의 교수번호, 이름, 학과명 출력
select d.dname,p.profno, p.name
from professor p join department d
on p.deptno = d.deptno
and (p.deptno,p.hiredate) in
(select deptno,min(hiredate) from professor
group by deptno)
order by d.dname;
테스트
/*
1. 고객테이블과 상품테이블을 조인하여 준비할 상품의 이름과, 필요수량을 출력하기.
단 수량이 많은 순으로 정렬하기
[결과]
상품명 상품수량
------------------------------------------------------------ ----------
샴푸세트 3
참치세트 3
세차용품세트 3
벽걸이TV 2
주방용품세트 2
산악용자전거 2
노트북 2
LCD모니터 2
양쪽문냉장고 1
*/
select gi.gname as 상품명,count(gi.gname) as 상품수량 from gogak go , gift gi
where go.point between gi.g_start and g_end
group by gi.gname
order by 상품수량 desc;
/*
2. 교수테이블에서 교수번호, 교수이름,입사일 자신보다 입사일이 빠른사람의 인원수를 출력하기
모든 교수정보가 조회되도록 하고, 입사일이 빠른순으로 정렬하기
[결과]
PROFNO NAME HIREDATE COUNT(P2.NAME)
---------- ---------------------------------------- -------- --------------
1001 김명선 90/06/23 0
3001 김도형 91/10/23 1
4001 심슨 91/10/23 1
2013 이창익 92/04/29 3
4005 바비 95/09/18 4
2012 장혜진 95/11/30 5
1002 김명신 97/01/30 6
3002 나한열 07/07/01 7
1003 김자바 08/03/22 8
4003 나몰라 09/12/01 9
4006 비 10/06/28 10
4007 김태희 11/05/23 11
2011 엄호선 11/09/01 12
3003 김현정 12/02/24 13
4004 아이유 19/01/28 14
4002 최슬기 19/08/30 15
*/
select p1.profno,p1.name,p1.hiredate,count(p2.name)
from professor p1, professor p2
where p1.hiredate > p2.hiredate(+)
group by p1.profno, p1.name, p1.hiredate
order by count(p1.hiredate) ;
/*
3. emp 테이블에서 사원번호,사원명,직업,상사번호,상사이름,상사의직업을 출력하기.
모든 사원이 조회되도록 한다.
[결과]
사원번호 사원명 사원직업 상사번호 상사명 상사직업
---------- -------------------- ------------------ ---------- -------------------- ------------------
7369 SMITH CLERK 7902 FORD ANALYST
7499 ALLEN SALESMAN 7698 BLAKE MANAGER
7521 WARD SALESMAN 7698 BLAKE MANAGER
7566 JONES MANAGER 7839 KING PRESIDENT
7654 MARTIN SALESMAN 7698 BLAKE MANAGER
7698 BLAKE MANAGER 7839 KING PRESIDENT
7782 CLARK MANAGER 7839 KING PRESIDENT
7788 SCOTT ANALYST 7566 JONES MANAGER
7839 KING PRESIDENT
7844 TURNER SALESMAN 7698 BLAKE MANAGER
7876 ADAMS CLERK 7788 SCOTT ANALYST
7900 JAMES CLERK 7698 BLAKE MANAGER
7902 FORD ANALYST 7566 JONES MANAGER
7934 MILLER CLERK 7782 CLARK MANAGER
*/
select e1.empno,e1.ename,e1.job,e1.mgr,e2.ename,e2.job
from emp e1, emp e2
where e1.mgr = e2.empno(+)
order by e1.empno;
/*
4. 부서테이블(department)에서 공과대학에 소속된 학과이름을 출력
[결과]
DNAME
------------------------
소프트웨어공학과
컴퓨터공학과
멀티미디어공학과
화학공학과
전자공학과
기계공학과
*/
select * from department;
select dname from department
where part in (select deptno from department
where part = (select deptno from department where dname = '공과대학')
);
/*
5.학생테이블에서 전공학과가 101번인 학과의 평균몸무게보다 몸무게가 많은 학생들의 이름과 몸무게, 학과명 출력
[결과]
NAME WEIGHT DNAME
-------------------- ---------- -------------------------------------
홍길동 72 컴퓨터공학과
이사랑 64 멀티미디어공학과
나학생 83 전자공학과
누구야 70 기계공학과
일지매 72 컴퓨터공학과
김진욱 70 멀티미디어공학과
안광훈 82 전자공학과
노정호 62 문헌정보학과
안은수 63 전자공학과
인영민 69 전자공학과
김주현 81 멀티미디어공학과
*/
select s.name,s.weight,d.dname from student s join department d
on s.deptno1 = d.deptno
and s.weight > (select avg(weight) from student where deptno1 = '101');
/*
6. 교수테이블의 심슨교수와 같은 입사일에 입사한 교수 중 김명선교수 보다 월급을 적게받는 교수의 이름, 급여, 입사일 출력
[결과]
NAME PAY HIREDATE
---------------------------------------- ---------- --------
김도형 530 91/10/23
*/
select name,pay,hiredate from professor
where pay< (select pay from professor where name = '김명선')
and hiredate = (select hiredate from professor where name = '심슨');
/*
7.101번 학과 학생들의 평균 몸무게 보다 몸무게가 적은 학생의 학번과,이름과, 학과번호, 몸무게를 출력
[결과]
STUDNO NAME DEPTNO1 WEIGHT
---------- -------------------- ---------- ----------
9413 조아해 103 52
9511 이서진 101 48
9512 유해진 102 42
9513 오나라 202 55
9514 구유미 301 58
9515 몰라두 201 54
9614 김문호 201 51
9711 이윤나 101 48
9715 허우 103 51
*/
select studno,name,deptno1,weight from student
where weight < (select avg(weight) from student where deptno1 = '101');
/*
8. 9712학생과 학년이 같고 키는 9713학생보다 큰 학생의 이름, 학년, 키를 출력
[결과]
NAME GRADE HEIGHT
-------------------- ---------- ----------
안은수 1 175
김주현 1 179
*/
select name,grade,height from student
where grade = (select grade from student where studno = '9712')
and height > (select height from student where studno = '9713');
/*
9. 컴퓨터정보학부에 소속된 모든 학생의 학번,이름, 학과번호 출력
학과번호 순으로 정렬하여 출력하기
[결과]
학번 이름 학과번호
---------- -------------------- ----------
9511 이서진 101
9411 홍길동 101
9611 일지매 101
9711 이윤나 101
9412 이사랑 102
9512 유해진 102
9612 김진욱 102
9714 김주현 102
9413 조아해 103
9715 허우 103
*/
select studno,name,deptno1 from student
where deptno1 in (select deptno from department where part = (select deptno from department where dname = '컴퓨터정보학부'));
/*
10. 4학년학생 중 키가 제일 작은 학생보다 키가 더 작은 학생의 학번,이름,키를 출력
[결과]
STUDNO NAME HEIGHT
---------- -------------------- ----------
9511 이서진 164
9512 유해진 161
9514 구유미 160
9614 김문호 166
9711 이윤나 162
9715 허우 163
*/
select studno,name,height from student where height < (select min(height) from student where grade = 4);
/*
11. 학생 중에서 생년월일이 가장 빠른 학생의 학번, 이름, 생년월일을 출력
[결과]
STUDNO NAME BIRTHDAY
---------- -------------------- --------
9412 이사랑 75/02/24
*/
select studno,name,birthday from student
where birthday = (select min(birthday) from student);
/*
12.학년별로 평균체중이 가장 적은 학년의 학년과 평균 몸무게를 출력
[결과]
GRADE AVG(WEIGHT)
---------- -----------
3 51.4
*/
select grade,평균 from (select grade,평균 from (select grade,avg(weight) as "평균" from student group by grade)) s1,(select min(최소값) as 최소값 from (select grade,avg(weight) as "최소값" from student group by grade)) s2 where s1.평균 = s2.최소값 ;
[참조]
'데이터베이스' 카테고리의 다른 글
| 데이터베이스_K_Digital_chapter07 (0) | 2022.08.19 |
|---|---|
| 데이터베이스_K_Digital_chapter04 (0) | 2022.08.17 |
| 데이터베이스_K_Digital_chapter03 (0) | 2022.08.16 |
| 데이터베이스_chapter01_데이터베이스 시스템 (0) | 2022.08.12 |
| 데이터베이스_10.정규화 (0) | 2021.11.08 |