[SQL99]
* NATURAL JOIN
- SQL99에서는 EQUI JOIN을 NATURAL JOIN이라고 함
- JOIN 없이 구하는 것과, CROSS JOIN의 경우 둘 다 데카르트의 곱에 따라 가능한 모든 조합이 생성됨
- 오히려 SQL99에서는 오류가 나지 않으려면 아래와 같이 써야 함. > DEPTNO가 E나 D에 포함되지 않는 것
SELECT E.ENAME, D.DNAME, DEPTNO
2 FROM EMP E NATURAL JOIN DEPT D;
- 이름이 똑같은 컬럼을 찾아서 연결하기 때문에, 이름이 똑같은 열이 두 개 이상이면 사용하지 못함
> 두 테이블의 컬럼명이 동일한 것을 기준으로 연결될 때는, NATURAL JOIN 사용 불가
* USING
- 동일한 컬럼명이 2개 이상일 땐, 어떤 기준으로 연결할 것인지 명시해야 함
- 따라서, 두 개의 컬럼 중 이름이 똑같은 게 2개 이상이라면 'USING'을 사용할 수 있음
SQL> SELECT E.ENAME, D.DNAME
2 FROM EMP E JOIN DEPT D
3 USING (DEPTNO);
* ON
- 두 테이블에 같은 데이터가 없는 경우에는ON 사용
SQL> SELECT E.ENAME, D.DNAME
2 FROM EMP E JOIN DEPT D
3 ON (E.DNO = D.DEPTNO);
ON을 사용함
(E.DEPTNO를 잠깐 E.DNO로 바꿔 놓은 상태 > ALTER 사용)
(alter table employees_r
rename column first_name to namenamename;)
SQL> SELECT E.ENAME, D.DNAME
2 FROM EMP E JOIN DEPT D
3 ON (E.DEPTNO = D.DEPTNO);
SQL> --위의 식은 아래와 같다.
SQL> SELECT E.ENAME, D.DNAME, E.DEPTNO
2 FROM EMP E, DEPT D
3 WHERE E.DEPTNO = D.DEPTNO;
>> ON은 두 테이블에 똑 같은 데이터가 없다면! 사용
* SELF JOIN
SQL99 형식으로 SELF JOIN
SQL> SELECT E.ENAME, C.ENAME
2 FROM EMP E JOIN EMP C
3 ON (E.MGR = C.EMPNO);
* 99 OUTER JOIN
SQL> -- 99형식 OUTER JOIN
SQL> SELECT E.ENAME, C.ENAME
2 FROM EMP E LEFT OUTER JOIN EMP C
3 ON (E.MGR = C.EMPNO);
위의 식은 92 형식의 아래 식과 같음
SQL> SELECT E.ENAME, C.ENAME
2 FROM EMP E, EMP C
3 WHERE E.MGR = C.EMPNO(+);
* FUNCTION
1 SINGLE ROW FUNCTION
2. MULTIPLE ROW FUNCTION è 여러 개의 행을 만나면 한 번 실행되는 함수
* MULTIPLE ROW FUNCTION = GROUP FUNCTION
: 여러 개의 행을 이뤄서 그룹 당 결과를 보여주는 함수
- COUNT, AVG, SUM, MIN, MAX, 표준편차, 분산 … 등등
- 모두 숫자와 연관이 있음
> 함수를 설계할 때부터 NULL은 제외하도록 되어 있음
- MIN, MAX : 숫자, 문자, 날짜에서 사용 가능
| - ASCII 코드 번호 A: 65 a: 97 0: 48 ENTER: 13, 10 |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
SQL> -- 사원들의 평균 급여?
SQL> SELECT AVG(SAL)
2 FROM EMP;
AVG(SAL)
----------
2073.21429
-- 평균 급여로 알아본 부서의 중요도?(급여가 높을수록 부서가 중요)
SQL> -- 30번 부서 사원들의 평균 급여?
SQL> SELECT AVG(SAL)
2 FROM EMP
3 WHERE DEPTNO = 30;
AVG(SAL)
----------
1566.66667
SQL> SELECT AVG(SAL)
2 FROM EMP
3 WHERE DEPTNO = 20;
AVG(SAL)
----------
2175
|
cs |
* GROUP BY : 소그룹을 만들어주는 함수
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
-- 그러나.. 회사의 부서가 5000개라면?..
SQL> -- 그룹별로 묶어서 소그룹당 함수를 한번씩 실행함
SQL>
SQL>
SQL> SELECT AVG(SAL)
2 FROM EMP
3 GROUP BY DEPTNO;
AVG(SAL)
----------
1566.66667
2175
2916.66667
|
cs |
> GROUP BY로 그룹을 만들고, GROUP FUNCTION 실행시킴
- 일반적으로 GROUP FUNCTINO과 일반 컬럼은 함께 SELECT절에 쓸 수 없음
GROUP BY절에 있는 컬럼은 예외적으로 GROUP FUNCTION과 함께 쓰일 수 있음
SQL> SELECT DEPTNO, AVG(SAL)
2 FROM EMP
3 GROUP BY DEPTNO;
|
1
2
3
4
5
6
7
8
9
|
SQL> SELECT DEPTNO, AVG(SAL)
2 FROM EMP
3 GROUP BY DEPTNO;
DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 2175
10 2916.66667
|
cs |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
|
SQL> -- 부서번호별 급여 평균
SQL> SELECT DEPTNO, AVG(SAL)
2 FROM EMP
3 GROUP BY DEPTNO;
DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 2175
10 2916.66667
SQL> -- JOB 별 평균 급여를 출력 ( JOB, 평균급여 ) SQL> SELECT JOB, AVG(SAL)
2 FROM EMP
3 GROUP BY JOB;
JOB AVG(SAL)
--------- ----------
CLERK 1037.5
SALESMAN 1400
PRESIDENT 5000
MANAGER 2758.33333
ANALYST 3000
SQL> -- 조건이 두 개 이상이면?
SQL>
SQL> SELECT AVG(SAL)
2 FROM EMP
3 GROUP BY DEPTNO, JOB;
AVG(SAL)
----------
950
1400
2975
950
5000
2850
1300
2450
3000
SQL> --위의 식은 곧 아래의 식과 내부적으로 똑같은 실행을 함
SQL> SELECT DEPTNO, JOB, SAL
2 FROM EMP
3 ORDER BY DEPTNO, JOB;
DEPTNO JOB SAL
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 3000
20 ANALYST 3000
20 CLERK 1100
20 CLERK 800
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1250
DEPTNO JOB SAL
---------- --------- ----------
30 SALESMAN 1250
30 SALESMAN 1600
30 SALESMAN 1500
14 개의 행이 선택되었습니다.
SQL> --> 각각 DEPTNO와 JOB이 모두 동일한 애들끼리 소그룹을 생성
|
cs |
* HAVING 조건 (연산자)
> 평균 급여가 2500 넘는 그런 부서만 보고 싶다..
- HAVING : GROUP 대한 제한
- WHERE : ROW 대한 제한
SQL> SELECT DEPTNO, AVG(SAL)
2 FROM EMP
3 GROUP BY DEPTNO
4 HAVING AVG(SAL) >= 2500
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL> -- JOB별 평균 급여를 출력 (JOB, 평균급여)
SQL> -- 평균급여가 2700보다 많은 그룹만 출력
SQL>
SQL> SELECT JOB, AVG(SAL)
2 FROM EMP
3 GROUP BY JOB
4 HAVING AVG(SAL) >= 2700;
JOB AVG(SAL)
--------- ----------
PRESIDENT 5000
MANAGER 2758.33333
ANALYST 3000
|
cs |
* SELECT문의 6가지 절
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
|
* 내부에서 논리적으로 실행하는 순서
1. FROM 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT 6. ORDER BY |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
SQL> -- 급여 2000 이상인 사원들의 평균 급여는?
SQL>
SQL> SELECT AVG(SAL)
2 FROM EMP
3 WHERE SAL >= 2000;
AVG(SAL)
----------
3212.5
SQL>
SQL> -- 급여 2000 이상인 사원들을 대상으로 부서번호별 평균 급여는?
SQL>
SQL> SELECT DEPTNO, AVG(SAL)
2 FROM EMP
3 WHERE SAL >= 2000
4 GROUP BY DEPTNO;
DEPTNO AVG(SAL)
---------- ----------
30 2850
20 2991.66667
10 3725
SQL>
SQL>
SQL> -- 급여 1000 이상인 사월을 대상으로 조사함
SQL> -- 부서번호별 JOB별 평균급여를 구하되 평균급여가 1500 이상인 그룹만 출력하세요
SQL> -- (단, 평균급여가 많은 순으로)
SQL>
SQL>
SQL> SELECT DEPTNO, JOB, AVG(SAL)
2 FROM EMP
3 WHERE SAL >= 1000
4 GROUP BY DEPTNO, JOB
5 HAVING AVG(SAL) >= 1500
6 ORDER BY AVG(SAL) DESC;
DEPTNO JOB AVG(SAL)
---------- --------- ----------
10 PRESIDENT 5000
20 ANALYST 3000
20 MANAGER 2975
30 MANAGER 2850
10 MANAGER 2450
|
cs |
* SUBQUERY
- JONES 사원보다 더 많은 급여를 받는 사원의 사번 이름 급여?
1. JONES 급여
SELECT SAL ==? 2975
FROM EMP
WHERE ENAME = ‘JONES’;
2. 2975보다 더 많은 급여를 받는 사람의 사번 이름 급여?
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL > 2975
>> 먼저 했으면 좋겠는 문장을 어떻게 표현?
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = ‘JONES’);
>> 이처럼 SELECT 문 안에 포함되어 있는 SELECT 문 = SUBQUERY
* MAIN QUERY / SUB QUERY
- MAIN QUERY: 원래 알고 싶은 값
- SUB QUERY : MAIN QUERY를 위해 먼저 가져와야 하는 값
: SUB QUERY는 실행 후 어떤 데이터가 리턴되는지에 따라 이름이 다르다.
>SELECT SAL FROM EMP
WHERE ENAME = ‘JONES’;
> 위와 같이 얻어지는 결과가 하나인 쿼리는 ‘SINGLE ROW SUBQUERY’
> 실행시켰을 때 결과가 여러 행으로 나오는 쿼리는 ‘MULTIPLE ROW SUBQUERY’
> SELECT SAL, COMM FROM EMP
WHERE DEPTNO = 10;
> 열이 여러 열이 나온다면 ‘MULTIPLE COLUMN SUBQUERY’
# 주의사항
1. SUBQUERY는 () 안에 포함한다.
2. S.R.S -> 앞에는 S.R.O(>, <, >=, <=)가 와야 한다.
3. M.R.S -> 앞에는 M.R.O(IN)가 와야 한다.
* BLAKE 사원보다 더 늦게 입사한 사원의 이름 입사일?
- MAIN : 입사한 사원의 이름 입사일
- SUB: BLAKE 사원보다 더 늦게
1. SUBQUERY 문장 작성 : BLAKE의 입사일?
SELECT HIREDATE == > 81/01/01이라고 침
FROM EMP
WHERE ENAME = ‘BLAKE’;
2. 이 값을 가지고 MAIN QUERY 작성
SELECT ENAME, HIREDATE
FROM EMP
WHERE HIREDATE > 81/01/01
3. 1+2 결합
SELECT ENAME, HIREDATE
FROM EMP
WHERE HIREDATE > (SELECT HIREDATE FROM EMP WHERE ENAME = ‘BLAKE’);
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL> -- JOIN 10문제: BLAKE 사원보다 더 늦게 입사한 사원의 이름, 입사일?
SQL>
SQL> SELECT ENAME, HIREDATE
2 FROM EMP
3 WHERE HIREDATE > (SELECT HIREDATE FROM EMP WHERE ENAME = 'BLAKE');
ENAME HIREDATE
---------- --------
MARTIN 81/09/28
CLARK 81/06/09
SCOTT 87/04/19
KING 81/11/17
TURNER 81/09/08
ADAMS 87/05/23
JAMES 81/12/03
FORD 81/12/03
MILLER 82/01/23
|
cs |
* M.R.O
- 10번 부서 사원들과 똑 같은 급여를 받는 사람들은?
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL IN (SELECT SAL FROM EMP WHERE DEPTNO = 10);
>> 위를 진행하면,
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL IN (3000, 1000, 2000);
* SELECT문은 JOIN과 SUB QUERY 때문에 문장이 복잡해지고 방대해지게 됨
> 그렇지만 SELECT문의 6가지 절 규칙은 지키기!
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL> -- M.R.O를 S.R.O를 사용하면 오류가 발생
SQL> SELECT EMPNO, ENAME, SAL
2 FROM EMP
3 WHERE SAL = (SELECT SAL FROM EMP WHERE DEPTNO = 10);
WHERE SAL = (SELECT SAL FROM EMP WHERE DEPTNO = 10)
*
3행에 오류:
ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.
SQL>
SQL> -- M.R.S는 M.R.O를 사용해야 함
SQL>
SQL> SELECT EMPNO, ENAME, SAL
2 FROM EMP
3 WHERE SAL IN (SELECT SAL FROM EMP WHERE DEPTNO = 10);
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
|
cs |
++ 응용
| < 조인 실습 문제 > - SQL99ver. 1. 사원들의 이름, 부서 번호, 부서 이름을 출력하라 ? SQL> SELECT E.ENAME, DEPTNO, D.DNAME 2 FROM EMP E NATURAL JOIN DEPT D; 2. 30 번 부서의 사원들의 이름, 직업, 부서위치를 출력하라 ? SQL> SELECT E.ENAME, E.JOB, D.LOC 2 FROM EMP E NATURAL JOIN DEPT D 3 WHERE DEPTNO = 30; 3. 커미션을 받는 사원의 이름 , 부서이름 및 부서위치를 출력하라 ? ( COMM 0 도 포함) SQL> SELECT E.ENAME, D.LOC 2 FROM EMP E NATURAL JOIN DEPT D 3 WHERE COMM IS NOT NULL OR COMM = 0; 4. DALLAS 에서 근무하는 사원의 이름 , 직업, 부서번호 , 부서이름을 출력하라 ? SQL> SELECT E.ENAME, E.JOB, DEPTNO, D.DNAME 2 FROM EMP E NATURAL JOIN DEPT D 3 WHERE D.LOC = 'DALLAS'; 5. 이름에 A 가 들어가는 사원들의 이름과 부서이름을 출력하라 ? SQL> SELECT E.ENAME, D.DNAME 2 FROM EMP E NATURAL JOIN DEPT D 3 WHERE E.ENAME LIKE '%A%'; 6. 사원이름과 그 사원의 관리자 이름을 출력하라 ( 단 컬럼 HEADING 을 employeee, manager 출력 ) SQL> SELECT E.ENAME "employee", C.ENAME "manager" 2 FROM EMP E LEFT OUTER JOIN EMP C 3 ON (E.MGR = C.EMPNO); 7. 사원이름과 직업, 급여, 급여등급 을 출력하라 ? SQL> SELECT E.ENAME, E.JOB, E.SAL, S.GRADE 2 FROM EMP E NATURAL JOIN SALGRADE S 3 WHERE (E.SAL BETWEEN S.LOSAL AND S.HISAL); 8. 사원이름과 부서명과 월급을 출력하는데 월급이 3000 이상인 사원을 출력하라 ? SQL> SELECT E.ENAME, D.DNAME, E.SAL 2 FROM EMP E NATURAL JOIN DEPT D 3 WHERE E.SAL >= 3000; 9. 사원의 이름, 부서번호 와 같은 부서에 근무하는 동료 사원들을 출력하라 ? ex) SCOTT 20 SMITH SCOTT 20 JONES SMITH 20 SCOTT … SQL> SELECT E.ENAME, E.DEPTNO, C.ENAME 2 FROM EMP E JOIN EMP C 3 ON (E.DEPTNO = C.DEPTNO) 4 AND E.ENAME != C.ENAME; 10. BLAKE 이란 사원보다 늦게 입사한 사원의 이름과 입사일을 출력하라 ? SQL> SELECT C.ENAME, C.HIREDATE 2 FROM EMP E JOIN EMP C 3 ON (E.ENAME = 'BLAKE') 4 AND E.HIREDATE < C.HIREDATE; |
| < SUBQUERY 실습문제 > 1. SMITH 보다 월급을 많이 받는 사원들의 이름과 월급을 출력하라 ? SQL> SELECT ENAME, SAL 2 FROM EMP 3 WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'SMITH'); 2. 10 번 부서의 사원들과 같은 월급을 받는 사원들의 이름과 월급과 부서 번호를 출력하라 ? SQL> SELECT ENAME, SAL, DEPTNO 2 FROM EMP 3 WHERE SAL IN (SELECT SAL FROM EMP WHERE DEPTNO = 10); 3. BLAKE 와 같은 부서에 있는 사람들의 이름과 입사일을 출력하되 BLAKE 는 제외 SQL> SELECT ENAME, HIREDATE 2 FROM EMP 3 WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'BLAKE') 4 AND ENAME != 'BLAKE'; 4. 평균 급여보다 많은 급여를 받는 사원들의 사번, 이름, 월급을 나타내되 월급이 높은 사람 순으로 출력하라 ? SQL> SELECT EMPNO, ENAME, SAL 2 FROM EMP 3 WHERE SAL > (SELECT AVG(SAL) FROM EMP) 4 ORDER BY SAL DESC; 5. 이름에 T 를 포함하고 있는 사원들과 동일한 부서에서 근무하고 있는 사원의 사원 번호와 이름을 출력하라 ? SQL> SELECT EMPNO, ENAME 2 FROM EMP 3 WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME LIKE '%T%'); 6. 30 번 부서의 최고 급여를 받는 사원 보다도 더 많은 급여를 받는 전체 사원들을 출력하라 SQL> SELECT ENAME 2 FROM EMP 3 WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30); 7. 부서 위치가 DALLAS 인 모든 사원의 이름, 부서 번호 및 직업을 출력하라 ? SQL> SELECT ENAME, DEPTNO, JOB 2 FROM EMP 3 WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS'); 8. SALES 부서의 모든 사원에 대한 부서번호, 이름 및 직업을 출력하라 ? SQL> SELECT DEPTNO, ENAME, JOB 2 FROM EMP 3 WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES'); 9. KING 에게 보고 하는 모든 사원의 이름과 급여를 출력하라 ? (즉 직속상사가 KING 인 사원) SQL> SELECT ENAME, SAL 2 FROM EMP 3 WHERE MGR = (SELECT EMPNO FROM EMP WHERE ENAME = 'KING'); 10. 자신의 급여가 사원 평균 급여보다 많으면서 이름에 S 자 들어가는 사원과 동일 부서인 모든 사원의 사원번호, 이름 및 급여를 출력하라 ? 1) 회사전체평균급여보다 많음 2) 이름s자가 들어가는 사원과 동일한부서 근무 2가지조건을 모두 만족하는 사원의 사번 , 이름 , 급여 ? SQL> SELECT EMPNO, ENAME, SAL 2 FROM EMP 3 WHERE SAL >= (SELECT AVG(SAL) FROM EMP) 4 AND DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME LIKE '%S%'); EMPNO ENAME SAL ---------- ---------- ---------- 7902 FORD 3000 7788 SCOTT 3000 7566 JONES 2975 7698 BLAKE 2850 위의 3번 행을 아래와 같이 실행하면 답이 나오지 않음. 데이터베이스 함수는 SELECT 형식으로 실행해야 값이 나옴! ** 종합2 ** 1개의 SELECT 문 TOTAL 1980 1981 1982 1987 -------- ------ ----- -------- -------- 14 1 10 1 2 > 위와 같이 출력하려면? SQL> SELECT COUNT(*) "TOTAL", 2 SUM(DECODE(TO_CHAR(HIREDATE, 'YYYY'), '1980', 1, 0)) "1980", 3 SUM(DECODE(TO_CHAR(HIREDATE, 'YYYY'), '1981', 1, 0)) "1981", 4 SUM(DECODE(TO_CHAR(HIREDATE, 'YYYY'), '1982', 1, 0)) "1982", 5 SUM(DECODE(TO_CHAR(HIREDATE, 'YYYY'), '1987', 1, 0)) "1987" 6 FROM EMP; |