조인 종류
1. 카타시안의 곱 → n:n의 형태, 쓰레기값 포함, 데이터를 복제하는 용도로 사용함
데이터베이스 제품에서 제공되는 함수가 없는 경우에는 카타시안의 곱을 활용해서 데이터를 2 배수, 3배 수로 복제하고 컬럼레벨에 있는 값을 로우레벨에 출력하는 것이 가능함
2. 등가조인(equals join, Natural join, inner join) → 표기법 3가지
양쪽 테이블에 모두 존재하는것만 조회된다
a) where절 없음→PK와 FK 연결이 필요 없다(자동연결)
b) inner join .. on
c) pk와 fk연결 - 직관적이다
3. non-equi조인
between A and B
IN - or(이거나, 또는)
is null, is not null
4. outer join
한 테이블엔 있는데 다른 테이블에 없어서, 없는 부분을 null값으로 하더라도 출력하고 싶을 때
둘 중 한쪽에만 있는 것을 조회하고 싶을 때
emp=14명 사원
dept=4가지 부서
결과 로우수 14
만약 56가지라면 카타시안의 곱 - 나올 수 있는 경우의 수(근무할 수 있는 부서의 종류)가 모두 출력
아우터조인을 걸면 15회 조회(40번 부서엔 아무도 없기에)
5. self join
나 자신과 1:1 혹은 1:n의 관계에 있을 때
같은 이름의 테이블을 두 번 적었다고 해서 self join이 아니다
관계형태
1:1 관계
1:n 관계
n:n 관계 →조인 걸면 안 됨
부서와 사원이 1:n? → 집합의 정의에 따라서 관계형태는 바뀔 수 있다
만약 부서집합의 정의가 현재 근무하는 부서를 관리하는 집합이 아니라. 근무한 이력을 관리하는 부서집합이라면 부서와 사원은 n:n의 관계
이력관리하는 집합이 필요하다
/* Formatted on 2023/01/10 오후 4:59:17 (QP5 v5.215.12089.38647) */
--이너조인
--deptno -> emp: FK(외래키), dept: PK(고유키)
--FK에 인덱스가 있기에 detp집합 먼저 읽고 emp집합을 읽는게 더 빠르다
SELECT *
FROM emp, dept
WHERE emp.deptno = dept.deptno;
--아우터조인
--부서집합 10, 20, 30, 40(40번부서는 아무도 없음)
--없는쪽(더 보여줘야 하는 곳)에 (+)를 붙인다
SELECT *
FROM emp, dept
WHERE emp.deptno(+) = dept.deptno;
--셀프조인
SELECT * FROM temp;
SELECT * FROM tdept;
SELECT b.dept_name AS "상위부서명"
FROM tdept a, tdept b
WHERE a.parent_dept = b.dept_code;
--테이블에 총계넣기
--14가지 경우의 수
SELECT deptno FROM emp;
--3가지로 group by
SELECT deptno
FROM emp
GROUP BY deptno;
--group by를 쓰면 집합함수로 묶어줘야 select문에 올 수 있다
--해당 부서번호에 근무하는 사원 수 카운트
SELECT deptno, COUNT (ename)
FROM emp
GROUP BY deptno;
--max(ename)는 별로 의미없는 값
SELECT deptno, COUNT (ename), MAX (ename)
FROM emp
GROUP BY deptno;
--결과가 14개 출력 -> group by를 한 의미가 없어짐
SELECT deptno, COUNT (ename), MAX (ename)
FROM emp
GROUP BY deptno, ename;
--컬럼을 늘리는 방법
SELECT 1, 2, 3 FROM DUAL;
--로우를 늘리는 방법
SELECT 1 FROM DUAL
UNION ALL
SELECT 2 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL;
--18가지 로우
--사원번호 14 + 부서번호 4가지
--union all은 중복을 제거하지 않는다
--두 집합을 순서를 따지지 않고 그대로 합친다.
--같은 타입끼리만 가능하다(union과 공통사항)
SELECT deptno FROM emp
UNION ALL
SELECT deptno FROM dept;
--부서번호 4가지
--union은 중복을 제거한다
--같은값이 있는지 비교 -> 2차가공 -> 속도차이가 발생
--순서를 따짐 -> 두 집합을 스캔해서 값을 비교한다는 의미
--union all을 사용한 경우와 결과가 같을 수 있지만 정렬 순서는 다를 수 있다
--왜냐하면 두 집합을 각각 스캔하고 비교한 후 결과값을 출력하니까
SELECT deptno FROM emp
UNION
SELECT deptno FROM dept;
--union, union all할 때는 타입을 맞춰줘야한다
--아래와 같이 하면 돌아가지 않음
--deptno는 number, dname은 varchar2타입이기에
--SELECT deptno FROM emp
--UNION
--SELECT dname FROM dept;
--그렇기에 아래와 같이 쓸 수 없음(타입이 다르기에)
--SELECT deptno
--FROM emp
--GROUP BY deptno
--UNION ALL
--SELECT '총계' FROM dual;
SELECT dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
GROUP BY dname
UNION ALL
SELECT '총계' FROM DUAL;
--emp에 dname은 들어있지 않다
--SELECT deptno, dname FROM emp; --제3정규화 위배함
--dept엔 모두 들어있어서 괜찮지만, sal과 job은 emp에 들어있기에 두 테이블을 모두 사용해야힘
SELECT deptno, dname FROM dept;
--총계를 구하기위해 부서3개만큼 총계가 있어야함(2배수)
--2배수를 만들기위해 로우를 늘리기
SELECT 1 rno FROM DUAL
UNION ALL
SELECT 2 rno FROM DUAL;
--아래와같이하면 컬럼이 늘고 로우값이 4개만 나오게됨
SELECT 1 rno, 2
FROM DUAL, dept;
--인라인뷰로 넣어줌
--이런식으로 해야 데이터가 2배로 늘어남(더미테이블의 필요성)
SELECT *
FROM (SELECT 1 rno FROM DUAL
UNION ALL
SELECT 2 rno FROM DUAL),
dept;
--같은 테이블을 절대로 두 번씩 읽지 않는다
--같은 튜플에서 같은 컬럼의 값이 두 번씩 사용되어야 한다
--한 번은 부서별 그리고 job별로 계산하는데 필요하고
--다른 한 번은 총계에서 job별로 계산하는데 필요하다
--그래서 더미테이블을 사용한다
--job, 부서별로 sal구하기
--clerk, manager, 그 외의 salary
--각 14개 로우씩 출력됨
SELECT DECODE (job, 'CLERK', sal, NULL),
DECODE (job, 'MANAGER', sal, NULL),
DECODE (job, 'CLERK', NULL, 'MANAGER', NULL, sal)
AS "그 외 합계"
FROM emp;
--sal의 총합 구하기
SELECT SUM (sal) FROM emp;
--sum으로 decode를 감싸 각 직업별 총합 구하기
SELECT SUM (DECODE (job, 'CLERK', sal, NULL)) AS "CLERK 급여",
SUM (DECODE (job, 'MANAGER', sal, NULL)) AS "MANAGER 급여",
SUM (DECODE (job, 'CLERK', NULL, 'MANAGER', NULL, sal))
AS "그 외 급여",
SUM (sal) AS "총 급여"
FROM emp;
--미리 경우의 수 줄여두기
SELECT deptno,
SUM (DECODE (job, 'CLERK', sal, NULL)),
SUM (DECODE (job, 'MANAGER', sal, NULL)),
SUM (DECODE (job, 'CLERK', NULL, 'MANAGER', NULL, sal)),
SUM (sal)
FROM emp
GROUP BY deptno;
--총계와 타입 맞추기
--하지만 40번부서는 근무하는 사원이 없기에 제외해야함
SELECT dname,
SUM (DECODE (job, 'CLERK', sal, NULL)),
SUM (DECODE (job, 'MANAGER', sal, NULL)),
SUM (DECODE (job, 'CLERK', NULL, 'MANAGER', NULL, sal)),
SUM (sal)
FROM emp, dept
GROUP BY dname;
--group by 밑에는 where절을 사용할 수 없음
--having절을 쓰거나 from 다음에 써야함
--SELECT dname
--,SUM(DECODE(job, 'CLERK', sal, null))
--,SUM(DECODE(job, 'MANAGER', sal, null))
--,SUM(DECODE(job, 'CLERK', null, 'MANAGER',null, sal))
--,SUM(sal)
--FROM emp, dept
--GROUP BY dname
--WHERE emp.deptno = DEPT.DEPTNO;
--group by 아래 부분은 where대신 having 사용
SELECT SUM (sal)
FROM emp
GROUP BY deptno
HAVING SUM (sal) > 9000;
--from 다음에 where을 사용한 경우
SELECT dname,
SUM (DECODE (job, 'CLERK', sal, NULL)) clerk,
SUM (DECODE (job, 'MANAGER', sal, NULL)) manager,
SUM (DECODE (job, 'CLERK', NULL, 'MANAGER', NULL, sal)) etc,
SUM (sal) dept_sal
FROM emp, dept
WHERE emp.deptno = DEPT.DEPTNO
GROUP BY dname;
--인라인뷰를 사용했을 경우
SELECT dept.dname,
a.clerk,
a.manager,
a.etc,
a.dept_sal
FROM ( SELECT deptno,
SUM (DECODE (job, 'CLERK', sal, NULL)) clerk,
SUM (DECODE (job, 'MANAGER', sal, NULL)) manager,
SUM (DECODE (job, 'CLERK', NULL, 'MANAGER', NULL, sal)) etc,
SUM (sal) dept_sal
FROM emp
GROUP BY deptno) a,
dept
WHERE a.DEPTNO = dept.deptno;
--위에서 구한 2배로 늘어난 인라인뷰 테이블과 sum을 사용한 급여총합 합치기
--where문을 사용했을 때보다 인라인뷰를 사용했을 때 코스트가 적게든다
--where사용 -> 코스트20
SELECT DECODE (b.rno, '1', dname, '총계')
FROM ( SELECT dname,
SUM (DECODE (job, 'CLERK', sal, NULL)) clerk,
SUM (DECODE (job, 'MANAGER', sal, NULL)) manager,
SUM (DECODE (job, 'CLERK', NULL, 'MANAGER', NULL, sal)) etc,
SUM (sal) dept_sal
FROM emp, dept
WHERE emp.deptno = DEPT.DEPTNO
GROUP BY dname) a,
(SELECT 1 rno FROM DUAL
UNION ALL
SELECT 2 rno FROM DUAL) b
GROUP BY DECODE (b.rno, '1', dname, '총계')
ORDER BY DECODE (b.rno, '1', dname, '총계');
--emp전체와 dept전체를 조인 조건으로 사용하는 것 보다는
--emp집합에 gruop by를 통해서 1차 가공을 하여 경우의 수를 3건으로 만들고
--dept집합과 inner join을 시도 (3*4)=12가지 경우의 수(56가지에서 12로 줄임)
--인라인뷰사용 -> 코스트14
SELECT DECODE (b.rno, '1', dname, '총계') AS "dname",
SUM (clerk) clerk_sum,
SUM (manager) manager_sum,
SUM (etc) etc,
SUM (dept_sal) dept_sal
FROM (SELECT dept.dname,
a.clerk,
a.manager,
a.etc,
a.dept_sal
FROM ( SELECT deptno,
SUM (DECODE (job, 'CLERK', sal, NULL)) clerk,
SUM (DECODE (job, 'MANAGER', sal, NULL)) manager,
SUM (
DECODE (job,
'CLERK', NULL,
'MANAGER', NULL,
sal))
etc,
SUM (sal) dept_sal
FROM emp
GROUP BY deptno) a,
dept
WHERE a.DEPTNO = dept.deptno) a,
(SELECT 1 rno FROM DUAL
UNION ALL
SELECT 2 FROM DUAL) b
GROUP BY DECODE (b.rno, '1', dname, '총계')
ORDER BY DECODE (b.rno, '1', dname, '총계');
--소계와 총계 연습문제
--다음 t_orderbasket 테이블에서 분석용 함수를 사용하지 않고 각 날짜별로
--총 몇개의 물건이 얼마만큼 팔렸으며 매출액은 어떻게 되는지 알고 싶다.
--어떻게 소계와 합계를 구할 것인가?
SELECT * FROM t_orderbasket;
--기본틀 만들기
SELECT indate_vc "날짜",
SUM (qty_nu) "팔린 물건 수",
SUM (price_nu) "매출액"
FROM t_orderbasket
GROUP BY indate_vc;
--총합을 위한 로우 늘리기
SELECT 1 FROM DUAL
UNION ALL
SELECT 2 FROM DUAL;
--카타시안의 곱으로 총계 구하기
SELECT *
FROM (SELECT 1 rno FROM DUAL
UNION ALL
SELECT 2 rno FROM DUAL),
t_orderbasket;
--sum과 decode로 각 날짜별 값 구하기
SELECT SUM (DECODE (indate_vc, '20040601', qty_nu, NULL)),
SUM (DECODE (indate_vc, '20040602', qty_nu, NULL)),
SUM (DECODE (indate_vc, '20040603', qty_nu, NULL)),
SUM (price_nu)
FROM t_orderbasket;
--날짜별 물건과 가격 구하기
SELECT indate_vc,
a.qty1,
a.qty2,
a.qty3,
a.price
FROM ( SELECT indate_vc,
SUM (DECODE (indate_vc, '20040601', qty_nu, NULL)) qty1,
SUM (DECODE (indate_vc, '20040602', qty_nu, NULL)) qty2,
SUM (DECODE (indate_vc, '20040603', qty_nu, NULL)) qty3,
SUM (price_nu) price
FROM t_orderbasket
GROUP BY indate_vc) a;
--합치기 1, 물건 통합이 필요함
SELECT DECODE (b.rno, '1', indate_vc, '총계') AS "판매날짜",
SUM (a.qty1),
SUM (a.qty2),
SUM (a.qty3),
SUM (a.price)
FROM (SELECT indate_vc,
a.qty1,
a.qty2,
a.qty3,
a.price
FROM ( SELECT indate_vc,
SUM (DECODE (indate_vc, '20040601', qty_nu, NULL))
qty1,
SUM (DECODE (indate_vc, '20040602', qty_nu, NULL))
qty2,
SUM (DECODE (indate_vc, '20040603', qty_nu, NULL))
qty3,
SUM (price_nu) price
FROM t_orderbasket
GROUP BY indate_vc) a) a,
(SELECT 1 rno FROM DUAL
UNION ALL
SELECT 2 FROM DUAL) b
GROUP BY DECODE (b.rno, '1', indate_vc, '총계')
ORDER BY DECODE (b.rno, '1', indate_vc, '총계') ASC;
--최종, 판매개수 통합-> 판매가격이 반영되지 않음...
SELECT DECODE (b.rno, '1', indate_vc, '총계') AS "판매날짜",
SUM (a.qty) || '개' AS "판매개수",
SUM (a.price) || '원' AS "판매가격"
FROM (SELECT indate_vc, a.qty, a.price
FROM ( SELECT indate_vc,
SUM (
DECODE (indate_vc,
'20040601', qty_nu,
'20040602', qty_nu,
'20040603', qty_nu))
qty,
SUM (price_nu) price
FROM t_orderbasket
GROUP BY indate_vc) a) a,
(SELECT 1 rno FROM DUAL
UNION ALL
SELECT 2 FROM DUAL) b
GROUP BY DECODE (b.rno, '1', indate_vc, '총계')
ORDER BY DECODE (b.rno, '1', indate_vc, '총계');
--모범답안
--1. 판매날짜별로 판매가격 계산 - price_nu가 2번 필요하다
--2. 카타시안의 곱을 사용하여 데이터를 2배수로 복제함
--3. 더미테이블 생성하기 - rownum사용
SELECT DECODE (b.dm, 1, a.indate_vc, 2, '총계') AS "판매날짜",
SUM (a.qty_nu) || ' 개' AS "판매개수",
SUM (a.qty_nu * a.price_nu) || ' 원' AS "판매가격"
FROM t_orderbasket a,
(SELECT ROWNUM dm
FROM emp
WHERE ROWNUM < 3) b
GROUP BY DECODE (b.dm, 1, a.indate_vc, 2, '총계')
ORDER BY "판매날짜";
SELECT a.indate_vc, b.dm
FROM t_orderbasket a,
(SELECT ROWNUM dm
FROM emp
WHERE ROWNUM < 3) b;
SELECT DECODE (b.dm, 1, a.indate_vc, 2, '총계')
FROM t_orderbasket a,
(SELECT ROWNUM dm
FROM emp
WHERE ROWNUM < 3) b;
--decode는 from절을 제외하고 모두 사용 가능하다
SELECT DECODE (b.dm, 1, a.indate_vc, 2, '총계')
FROM t_orderbasket a,
(SELECT ROWNUM dm
FROM emp
WHERE ROWNUM < 3) b
GROUP BY DECODE (b.dm, 1, a.indate_vc, 2, '총계');
SELECT DECODE (b.dm, 1, a.indate_vc, 2, '총계'),
SUM (a.qty_nu) || '개' AS "판매개수"
FROM t_orderbasket a,
(SELECT ROWNUM dm
FROM emp
WHERE ROWNUM < 3) b
GROUP BY DECODE (b.dm, 1, a.indate_vc, 2, '총계')
ORDER BY DECODE (b.dm, 1, a.indate_vc, 2, '총계');
SELECT DECODE (b.dm, 1, a.indate_vc, 2, '총계') AS "날짜",
SUM (a.qty_nu) || '개' AS "판매개수",
SUM (a.qty_nu * a.price_nu) || '원' AS "판매가격"
FROM t_orderbasket a,
(SELECT ROWNUM dm
FROM emp
WHERE ROWNUM < 3) b
GROUP BY DECODE (b.dm, 1, a.indate_vc, 2, '총계')
ORDER BY DECODE (b.dm, 1, a.indate_vc, 2, '총계');
SELECT ROWNUM rno, ename FROM emp;
SELECT ROWNUM rno, ename
FROM emp
WHERE deptno = 30;
SELECT ROWNUM rno, ename
FROM ( SELECT ename
FROM emp
GROUP BY ename);
SELECT empno, ename FROM emp;
SELECT empno, ename
FROM emp
ORDER BY ename ASC;
--rownum 순서가 뒤죽박죽
SELECT ROWNUM rno, empno, ename
FROM emp
ORDER BY ename ASC;
--인라인뷰를 이용해 rownum 순서를 맞춰줌
SELECT ROWNUM rno, empno, ename
FROM ( SELECT empno, ename
FROM emp
ORDER BY ename ASC);
SELECT ROWNUM rno, empno
FROM emp
WHERE ROWNUM < 3;
--인라인뷰를 사용하면 rno 형태로 쓸 수 있음
SELECT rno, empno
FROM (SELECT ROWNUM rno, empno FROM emp)
WHERE rno < 3;
--작다, 작거나 같다는 가능
SELECT empno, ename
FROM emp
WHERE ROWNUM <= 3;
--하지만 크다, 크거나 같다, 같다는 불가능
SELECT empno, ename
FROM emp
WHERE ROWNUM > 3;
--ROLLUP은 GROUP BY절에 사용되며 GROUP의 순서에 의한 멀티 레벨의 소계 및 합계를 구해주는 함수이다.
--ROLLUP이나 CUBE를 다루면서는 GROUPPING()함수를 종종 볼수 있는데
--이것은 모든 값에 대한 SET을 나타내는 NULL값과 컬럼의 NULL값을 구분하는데 쓰여진다.
--즉 GROUPPING()은 모든 값의 SET을 표현함에 있어서 NULL이면 1을 아니면 0을 RETURN함으로써
--소계와 총계부분을 나타내 주는 역할을 한다.
SELECT indate_vc, gubun_vc, SUM (qty_nu * price_nu)
FROM t_orderbasket
GROUP BY ROLLUP (indate_vc, gubun_vc);
--연습문제
--날짜별 판매개수와 매출액이 아닌 날짜와 판매물품의 구분을 같이 출력하되
--마지막에 판매 물품에 대한 구분을 넣어서 각각의 소계와 합계를 출력해 본다.
SELECT * FROM t_orderbasket;
SELECT COUNT (qty_nu), SUM (qty_nu) FROM t_orderbasket;
SELECT DISTINCT (gubun_vc) || '계' FROM t_orderbasket;
SELECT gubun_vc || '계'
FROM t_orderbasket
GROUP BY gubun_vc;
--3배수로 만들기-날짜, 소계, 총합을 구하기위헤
--162개(54*3)의 결과가 나옴
SELECT *
FROM t_orderbasket a,
(SELECT ROWNUM dm
FROM emp
WHERE ROWNUM < 4) b;
SELECT DECODE (b.dm, 1, a.indate_vc, 2, '총계', 3, '소계')
FROM t_orderbasket a,
(SELECT ROWNUM dm
FROM emp
WHERE ROWNUM < 4) b
GROUP BY DECODE (b.dm, 1, a.indate_vc, 2, '총계', 3, '소계')
ORDER BY DECODE (b.dm, 1, a.indate_vc, 2, '총계', 3, '소계');
SELECT DECODE (b.dm, 1, a.indate_vc, 2, '총계', 3, '소계'),
DECODE (b.dm, 1, a.gubun_vc, 3, a.gubun_vc || '계')
FROM t_orderbasket a,
(SELECT ROWNUM dm
FROM emp
WHERE ROWNUM < 4) b;
--최종정답
SELECT DECODE (b.dm, 1, a.indate_vc, 2, '총계', 3, '소계'),
DECODE (b.dm, 1, a.gubun_vc, 3, a.gubun_vc || '계')
FROM t_orderbasket a,
(SELECT ROWNUM dm
FROM emp
WHERE ROWNUM < 4) b
GROUP BY DECODE (b.dm, 1, a.indate_vc, 2, '총계', 3, '소계'),
DECODE (b.dm, 1, a.gubun_vc, 3, a.gubun_vc || '계')
ORDER BY DECODE (b.dm, 1, a.indate_vc, 2, '총계', 3, '소계');
SELECT DECODE (b.dm, 1, a.indate_vc, 2, '총계', 3, '소계')
AS "판매날짜",
DECODE (b.dm, 1, a.gubun_vc, 3, a.gubun_vc || '계')
AS "물품구분",
SUM (a.qty_nu) || '개' AS "판매개수",
SUM (a.qty_nu * a.price_nu) || '원' AS "판매가격"
FROM t_orderbasket a,
(SELECT ROWNUM dm
FROM emp
WHERE ROWNUM < 4) b
GROUP BY DECODE (b.dm, 1, a.indate_vc, 2, '총계', 3, '소계'),
DECODE (b.dm, 1, a.gubun_vc, 3, a.gubun_vc || '계')
ORDER BY DECODE (b.dm, 1, a.indate_vc, 2, '총계', 3, '소계');
--서브쿼리 연습문제
--1.temp에서 연봉이 가장 많은 직원의 row를 찾아서 이 금액과 동일한 금액을
--받는 직원의 사번과 성명을 출력하시오.
SELECT * FROM temp;
SELECT MAX (salary) FROM temp;
SELECT emp_id, emp_name FROM temp;
SELECT emp_id, emp_name
FROM temp
WHERE salary = 100000000;
--서브쿼리 사용 -> where절 뒤의 select문
SELECT emp_id, emp_name
FROM temp
WHERE salary = (SELECT MAX (salary) FROM temp);
--인라인뷰와 서브쿼리의 차이
--인라인뷰에서 사용한 컬럼명은 메인쿼리에서 사용이 가능하다
--서브쿼리에서 사용한 컬럼명은 메인쿼리에서 사용할 수 없다
--서브쿼리는 간접적인 조건을 주고 검색할 때 사용 가능
--아래의 경우 select에서 max_sal을 사용할 수 없다
--SELECT emp_id, emp_name, max_sal
--FROM temp
--WHERE salary = (SELECT max(salary) max_sal FROM temp);
--2.temp의 자료를 이용하여 salary의 평균을 구하고 이보다 큰 금액을 salary로
--받는 직원의 사번과 성명, 연봉을 보여주시오.
SELECT * FROM temp;
SELECT AVG (salary) FROM temp;
SELECT emp_id, emp_name, salary
FROM temp
WHERE salary > (SELECT AVG (salary) FROM temp);
--3.temp의 직원 중 인천에 근무하는 직원의 사번과 성명을 읽어오는 SQL을 서브
--쿼리를 이용해 만들어보시오.
SELECT * FROM temp;
SELECT * FROM tdept;
SELECT dept_code, area
FROM tdept
WHERE area = '인천';
SELECT ename, deptno
FROM emp
WHERE deptno IN (10, 30);
SELECT emp_id, emp_name
FROM temp, tdept
WHERE area IN ('인천') AND TEMP.dept_code = tdept.dept_code;
SELECT emp_id, emp_name
FROM temp
WHERE dept_code IN (SELECT dept_code
FROM tdept
WHERE area = '인천');
--4.tcom에 연봉 외에 커미션을 받는 직원의 사번이 보관되어 있다.
--이 정보를 서브쿼리로 select하여 부서 명칭별로 커미션을 받는
--인원수를 세는 문장을 만들어 보시오.
SELECT * FROM tcom;
SELECT * FROM temp;
SELECT * FROM tdept;
--*는 전체 로우를 카운트하기에 PK를 세는 것과 같음
SELECT COUNT (empno), COUNT (*), COUNT (comm) FROM emp;
SELECT emp_id FROM tcom;
SELECT dept_name
FROM temp, tdept
WHERE temp.dept_code = tdept.dept_code;
SELECT dept_name
FROM temp, tdept
WHERE temp.dept_code = tdept.dept_code
GROUP BY dept_name;
SELECT dept_name
FROM temp, tdept
WHERE temp.dept_code = tdept.dept_code
AND emp_id IN (SELECT emp_id FROM tcom);
--group by로 중복제거
SELECT dept_name
FROM temp, tdept
WHERE temp.dept_code = tdept.dept_code
AND emp_id IN (SELECT emp_id FROM tcom)
GROUP BY dept_name;
SELECT dept_name, COUNT (*)
FROM temp, tdept
WHERE temp.dept_code = tdept.dept_code
AND emp_id IN (SELECT emp_id FROM tcom)
GROUP BY dept_name;
--심화문제
--tdept에서 부서코드와 boss_id를 읽어서 이 두개 컬럼이 temp의 부서코드와
--사번에 일치하는 사람의 사번과 성명을 읽어오는 쿼리를 만들어보자.
SELECT * FROM tdept;
SELECT * FROM temp;
SELECT emp_id, emp_name
FROM temp, tdept
WHERE temp.dept_code = tdept.dept_code AND temp.emp_id = tdept.boss_id;
SELECT emp_id, MAX (emp_name)
FROM temp, tdept
WHERE emp_id IN (SELECT boss_id FROM tdept)
GROUP BY emp_id
ORDER BY emp_id;
--모범답안
--멀티 컬럼 서브쿼리
--서브쿼리에서 결과로 나오는 행이 한 컬럼이 아닌 두 개 이상을 가지는 경우
--사원(emp_id)중에서 부서장 아이디도 존재함
SELECT emp_id, emp_name
FROM temp
WHERE (dept_code, emp_id) IN (SELECT dept_code, boss_id FROM tdept);
--RDBMS 제품
--1. 테이블 개수가 많아진다.
--2. 업무에대한 복잡도에따라 복합키가 많이 발생한다.
--2개 이상의 테이블 조인 시에 먼저 읽는 테이블과 나중에 읽는 테이블이 있다
--실행계획 -> Ctrl + E
--안쪽에서 바깥쪽으로 읽고, 실행 계획을 세우는 기준은 select statement(ALL_ROWS 부분)
-- HASH JOIN 방식
-- 각각의 테이블을 풀 스캔하고 조건을 비교하면서 만족하는 로우값을 꺼내서 메모리에 올린다.
--실행계획에서 index를 사용하지 않음
SELECT emp.empno, emp.ename, dept.dname
FROM emp, dept
WHERE emp.deptno = dept.deptno;
-- NESTED LOOP JOIN 방식
--먼저 한 테이블을 읽고(드라이브하고) 조건에 있는 컬럼을 기준으로 상대 테이블의 컬럼값을 비교해가면서
--조건을 수렴하는 결과를 메모리에 올린다.
SELECT /*+rule*/emp.empno, emp.ename, dept.dname
FROM emp, dept
WHERE emp.deptno = dept.deptno;
--UNION ALL - 정렬이 일어나지 않음, 속도 빠름, 중복은 제거되지 않음
--UNION - Hash JOIN 방식으로 중복제거, 정렬이 일어남, 속도 느림
--기본적으로 정렬이 일어나면 2차 가공이 일어난다
테이블에 기본키가 2개 이상인 경우가 있다(복합키)
업무에 대한 복잡도가 증가함에따라 테이블의 관계가 depth도 깊어지고 관계형태가 n:n인 경우 업무에대한 정의가 덜 된 경우임
두 집합 사이에서 1:n 관계로 만들어주는 행위엔티티가 필요하다.
예) 회원집합과 도서집합(n:n) - 대여행위집합 / 학생과 교과목(n:n) - 수강신청집합
PK(Primary Key)
주키, 프라이머리키
값의 식별자로 사용하기 가장 적합한 것을 테이블마다 선택, 정의된 후보키
NULL 허용되지 않음
FK(Foreign Key)
한 테이블의 키 중 다른 테이블의 행(row)을 식별할 수 있는 키
한 테이블의 PK가 다른 테이블에서의 FK
AK(Alternate Key)
후보키 중 기본키로 선정되지 않은 키
사용하면 인덱스의 크기가 작아짐
의미상의 주어는 아니다 → 부모테이블이 누구인지 드러나지 않는다
조인 시에는 사용할 수 없다
--FK가 조인의 직접적인 대상이 되는 컬럼이다
SELECT 학생.학생이름, 교과목.강의실, 수강신청.출석점수, 수강신청.평가점수
FROM 학생, 교과목, 수강신청
WHERE 학생.학번 = 수강신청.학번
AND 교과목.과목코드 = 수강신청.과목코드
주문자 - 회원집합
상품명 - 상품집합
주문일자 - 주문집합
배송일자 - 배송집합
가격 - 상품집합, 단가집합
inner join을 걸어야 실제 주문자가 거래한 내용 확인 가능함(가장 일반적)
SELECT 회원.회원명, 상품.상품명, 주문.주문일자, 단가.가격
FROM 회원, 상품, 주문, 단가(이력관리)
WHERE 회원.회원코드 = 주문.회원코드
AND 상품.상품코드 = 주문.상품코드
AND 상품/상품코드 = 단가.상품코드
집합이 4개이면 조인조건은 n-1개가 필요하다
SELECT문 처리 순서
1. 파싱(parsing) - 문법 맞는지 여부
2. DBMS 실행계획 세움
3. 실행계획을 옵티마이저에게 넘김
4. open... cursor(이동의미. true or false) ...fetch.. close 위변조
'국비학원 > 수업기록' 카테고리의 다른 글
| 국비 지원 개발자 과정_Day34 (0) | 2023.01.12 |
|---|---|
| 국비 지원 개발자 과정_Day33 (0) | 2023.01.11 |
| 국비 지원 개발자 과정_Day31 (0) | 2023.01.09 |
| 국비 지원 개발자 과정_Day30 (0) | 2023.01.06 |
| 국비 지원 개발자 과정_Day29 (1) | 2023.01.05 |
댓글