Oracle DB
[Oracle DB] SQL - JOIN(equi join/nonequi join/self join/outer join), ORDER BY(ASC/DESC)
소댓
2023. 2. 22. 18:26
[각기 다른 두 테이블에서 자료를 가져오기]
- ‘,’로 연결하면
SELECT ENAME, DNAME
FROM EMP, DEPT;
> 두 테이블의 행의 가능한 모든 조합이 발생
> 올바르게 자료를 선별하지 못해 모든 경우의 수를 다 계산하여 생성함
>> CARTESIAN PRODUCT : 데카르트의 곱 – 두 수의 조합만큼 만들어짐
> 행의 가능한 모든 조합 > TAB1 : M개 행 / TAB2 : N개 행 >> M*N행 : 따라서, 조인 조건 필요
* EMP.DEPTNO / DEPT.DEPTNO
> 이렇게 테이블 명을 컬럼 앞에 써주면 엑세스 효율이 향상된다.
- 테이블 이름이 너무 길면, 별칭 설정도 가능함
|
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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
|
SQL> -- DEPTNO가 EMP, DEPT 두 개의 테이블에 다 존재하기 때문에
SQL> -- 애매한 에러 메시지가 나오게 됨
SQL>
SQL> -- 어떤 테이블의 DEPTNO인지 확실히 쓰고 싶다면,
SQL> -- EMP.DEPTNO의 방식으로 쓰면 됨(EMP 테이블의 DEPTNO)
SQL>
SQL> SELECT ENAME, EMP.DEPTNO, DEPT.DEPTNO, DNAME
2 FROM EMP, DEPT;
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
SMITH 20 10 ACCOUNTING
ALLEN 30 10 ACCOUNTING
WARD 30 10 ACCOUNTING
JONES 20 10 ACCOUNTING
MARTIN 30 10 ACCOUNTING
BLAKE 30 10 ACCOUNTING
CLARK 10 10 ACCOUNTING
SCOTT 20 10 ACCOUNTING
KING 10 10 ACCOUNTING
TURNER 30 10 ACCOUNTING
ADAMS 20 10 ACCOUNTING
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
JAMES 30 10 ACCOUNTING
FORD 20 10 ACCOUNTING
MILLER 10 10 ACCOUNTING
SMITH 20 20 RESEARCH
ALLEN 30 20 RESEARCH
WARD 30 20 RESEARCH
JONES 20 20 RESEARCH
MARTIN 30 20 RESEARCH
BLAKE 30 20 RESEARCH
CLARK 10 20 RESEARCH
SCOTT 20 20 RESEARCH
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
KING 10 20 RESEARCH
TURNER 30 20 RESEARCH
ADAMS 20 20 RESEARCH
JAMES 30 20 RESEARCH
FORD 20 20 RESEARCH
MILLER 10 20 RESEARCH
SMITH 20 30 SALES
ALLEN 30 30 SALES
WARD 30 30 SALES
JONES 20 30 SALES
MARTIN 30 30 SALES
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
BLAKE 30 30 SALES
CLARK 10 30 SALES
SCOTT 20 30 SALES
KING 10 30 SALES
TURNER 30 30 SALES
ADAMS 20 30 SALES
JAMES 30 30 SALES
FORD 20 30 SALES
MILLER 10 30 SALES
SMITH 20 40 OPERATIONS
ALLEN 30 40 OPERATIONS
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
WARD 30 40 OPERATIONS
JONES 20 40 OPERATIONS
MARTIN 30 40 OPERATIONS
BLAKE 30 40 OPERATIONS
CLARK 10 40 OPERATIONS
SCOTT 20 40 OPERATIONS
KING 10 40 OPERATIONS
TURNER 30 40 OPERATIONS
ADAMS 20 40 OPERATIONS
JAMES 30 40 OPERATIONS
FORD 20 40 OPERATIONS
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
MILLER 10 40 OPERATIONS
SQL> SELECT E.ENAME, E.DEPTNO, D.DEPTNO, D.DNAME
2 FROM EMP E, DEPT D;
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
SMITH 20 10 ACCOUNTING
ALLEN 30 10 ACCOUNTING
WARD 30 10 ACCOUNTING
JONES 20 10 ACCOUNTING
MARTIN 30 10 ACCOUNTING
BLAKE 30 10 ACCOUNTING
CLARK 10 10 ACCOUNTING
SCOTT 20 10 ACCOUNTING
KING 10 10 ACCOUNTING
TURNER 30 10 ACCOUNTING
ADAMS 20 10 ACCOUNTING
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
JAMES 30 10 ACCOUNTING
FORD 20 10 ACCOUNTING
MILLER 10 10 ACCOUNTING
SMITH 20 20 RESEARCH
ALLEN 30 20 RESEARCH
WARD 30 20 RESEARCH
JONES 20 20 RESEARCH
MARTIN 30 20 RESEARCH
BLAKE 30 20 RESEARCH
CLARK 10 20 RESEARCH
SCOTT 20 20 RESEARCH
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
KING 10 20 RESEARCH
TURNER 30 20 RESEARCH
ADAMS 20 20 RESEARCH
JAMES 30 20 RESEARCH
FORD 20 20 RESEARCH
MILLER 10 20 RESEARCH
SMITH 20 30 SALES
ALLEN 30 30 SALES
WARD 30 30 SALES
JONES 20 30 SALES
MARTIN 30 30 SALES
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
BLAKE 30 30 SALES
CLARK 10 30 SALES
SCOTT 20 30 SALES
KING 10 30 SALES
TURNER 30 30 SALES
ADAMS 20 30 SALES
JAMES 30 30 SALES
FORD 20 30 SALES
MILLER 10 30 SALES
SMITH 20 40 OPERATIONS
ALLEN 30 40 OPERATIONS
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
WARD 30 40 OPERATIONS
JONES 20 40 OPERATIONS
MARTIN 30 40 OPERATIONS
BLAKE 30 40 OPERATIONS
CLARK 10 40 OPERATIONS
SCOTT 20 40 OPERATIONS
KING 10 40 OPERATIONS
TURNER 30 40 OPERATIONS
ADAMS 20 40 OPERATIONS
JAMES 30 40 OPERATIONS
FORD 20 40 OPERATIONS
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
MILLER 10 40 OPERATIONS
|
cs |
- E.DEPTNO와 D.DEPTNO의 값이 같은 행만 나올 수 있게 정리
>> 올바른 데이터만 나올 수 있게 하는 것 : JOIN 조건
|
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
|
SQL> -- E.DEPTNO와 D.DEPTNO의 값이 같은 행만 나올 수 있게 정리
SQL>
SQL> SELECT E.ENAME, E.DEPTNO, D.DEPTNO, D.DNAME
2 FROM EMP E, DEPT D
3 WHERE E.DEPTNO = D.DEPTNO;
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
CLARK 10 10 ACCOUNTING
KING 10 10 ACCOUNTING
MILLER 10 10 ACCOUNTING
JONES 20 20 RESEARCH
FORD 20 20 RESEARCH
ADAMS 20 20 RESEARCH
SMITH 20 20 RESEARCH
SCOTT 20 20 RESEARCH
WARD 30 30 SALES
TURNER 30 30 SALES
ALLEN 30 30 SALES
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
JAMES 30 30 SALES
BLAKE 30 30 SALES
MARTIN 30 30 SALES
14 개의 행이 선택되었습니다.
SQL> SELECT E.ENAME, D.DNAME
2 FROM EMP E, DEPT D
3 WHERE E.DEPTNO = D.DEPTNO;
ENAME DNAME
---------- --------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
JONES RESEARCH
FORD RESEARCH
ADAMS RESEARCH
SMITH RESEARCH
SCOTT RESEARCH
WARD SALES
TURNER SALES
ALLEN SALES
ENAME DNAME
---------- --------------
JAMES SALES
BLAKE SALES
MARTIN SALES
14 개의 행이 선택되었습니다.
SQL> -- 위와 같이 올바른 데이터만 나올 수 있게 하는 것이 'JOIN 조건'
SQL>
SQL> -- 이 중에서 일반 조건 (E.DEPTNO=10)까지 포함한다면,
SQL> SELECT E.ENAME, D.DNAME
2 FROM EMP E, DEPT D
3 WHERE E.DEPTNO = D.DEPTNO
4 AND E.DEPTNO = 10;
ENAME DNAME
---------- --------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
|
cs |
* JOIN : 두 개 이상의 테이블을 마치 하나의 테이블처럼 연결해서 사용하는 기법
FROM TAB, TAB2
- 조인조건 : 올바른 연결을 선택하기 위해 사용하는 조건
>> N개의 테이블 조인
조인조건 최소 몇 개 필요? n-1개
* JOIN의 종류
1. =: equi join
2. 다른연산 : nonequi join
3. self join
4. outer join
* 조인조건 사용된 연산자
= : EQUI JOIN (등가 조인)
|
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
|
SQL> -- 사원명 JOB 부서명 부서위치?
SQL>
SQL> SELECT E.ENAME, E.JOB, D.DNAME, D.LOC
2 FROM EMP E, DEPT D
3 WHERE E.DEPTNO = D.DEPTNO;
ENAME JOB DNAME LOC
---------- --------- -------------- -------------
CLARK MANAGER ACCOUNTING NEW YORK
KING PRESIDENT ACCOUNTING NEW YORK
MILLER CLERK ACCOUNTING NEW YORK
JONES MANAGER RESEARCH DALLAS
FORD ANALYST RESEARCH DALLAS
ADAMS CLERK RESEARCH DALLAS
SMITH CLERK RESEARCH DALLAS
SCOTT ANALYST RESEARCH DALLAS
WARD SALESMAN SALES CHICAGO
TURNER SALESMAN SALES CHICAGO
ALLEN SALESMAN SALES CHICAGO
ENAME JOB DNAME LOC
---------- --------- -------------- -------------
JAMES CLERK SALES CHICAGO
BLAKE MANAGER SALES CHICAGO
MARTIN SALESMAN SALES CHICAGO
|
cs |
* NONEQUI JOIN
|
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
|
SQL> SELECT E.ENAME, S.GRADE
2 FROM EMP E, SALGRADE S
3 WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
ENAME GRADE
---------- ----------
SMITH 1
JAMES 1
ADAMS 1
WARD 2
MARTIN 2
MILLER 2
TURNER 3
ALLEN 3
CLARK 4
BLAKE 4
JONES 4
ENAME GRADE
---------- ----------
SCOTT 4
FORD 4
KING 5
14 개의 행이 선택되었습니다.
SQL> -- NONEQUI JOIN
|
cs |
* 세 가지 테이블 참조
사원명, 부서명, 급여등급
ENAME DNAME GRADE
EMP DEPT SALGRADE
- JOIN이 잘못된 것은 찾기 쉽다. : 1. 쓸데없이 뭔가 중복되어 나옴
2. n-1 이상
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
SQL> SELECT E.ENAME, D.DNAME, S.GRADE
2 FROM EMP E, DEPT D, SALGRADE S
3 WHERE E.DEPTNO = D.DEPTNO
4 AND E.SAL BETWEEN S.LOSAL AND S.HISAL;
ENAME DNAME GRADE
---------- -------------- ----------
KING ACCOUNTING 5
FORD RESEARCH 4
SCOTT RESEARCH 4
JONES RESEARCH 4
BLAKE SALES 4
CLARK ACCOUNTING 4
ALLEN SALES 3
TURNER SALES 3
MILLER ACCOUNTING 2
WARD SALES 2
MARTIN SALES 2
ENAME DNAME GRADE
---------- -------------- ----------
ADAMS RESEARCH 1
JAMES SALES 1
SMITH RESEARCH 1
|
cs |
* WHERE절은 항상 행 단위로 비교하기 때문에
다른 행과 행을 비교하는 일은 할 수 없음
SQL> -- 사원명, 상사의 이름
SQL> SELECT *
2 FROM EMP
3 WHERE MGR = EMPNO;
- 따라서, 테이블 복사
SQL> CREATE TABLE COPY_EMP
2 AS
3 SELECT * FROM EMP;
> 하지만 대용량의 데이터를 처리할 경우에 테이블을 복사하는 것이 효율이 떨어짐
* 따라서, 테이블에 별칭을 줌! : SELF JOIN
SQL> SELECT E.ENAME, C.ENAME
2 FROM EMP E, EMP C
3 WHERE E.MGR = C.EMPNO;
|
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> SELECT E.ENAME, C.ENAME
2 FROM EMP E, EMP C
3 WHERE E.MGR = C.EMPNO;
ENAME ENAME
---------- ----------
FORD JONES
SCOTT JONES
TURNER BLAKE
ALLEN BLAKE
WARD BLAKE
JAMES BLAKE
MARTIN BLAKE
MILLER CLARK
ADAMS SCOTT
BLAKE KING
JONES KING
ENAME ENAME
---------- ----------
CLARK KING
SMITH FORD
13 개의 행이 선택되었습니다.
SQL> -- SELF JOIN
|
cs |
* OUTER JOIN : 정상적인 조인의 결과에 널값에 가진 행들도 포함시키는 연산
SQL> SELECT E.ENAME, C.ENAME
2 FROM EMP E, EMP C
3 WHERE E.MGR = C.EMPNO(+);
|
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
|
SQL> -- 위의 조직도에서 상사가 없는 '대표'는 사라짐
SQL>
SQL> -- KING의 MGR은 NULL이기 때문에.. WHERE E.MGR = C.EMPNO 비교하면 참 불가
SQL>
SQL> -- OUTER JOIN: 정상적인 조인의 결과에 널값에 가진 행들도 포함시키는 연산
SQL> SELECT E.ENAME, C.ENAME
2 FROM EMP E, EMP C
3 WHERE E.MGR = C.EMPNO(+);
ENAME ENAME
---------- ----------
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
ENAME ENAME
---------- ----------
JONES KING
SMITH FORD
KING
|
cs |
* ORDER BY - 정렬
SQL> SELECT EMPNO, ENAME, SAL
2 FROM EMP
3 ORDER BY 컬럼명
- ASC : 오름차순 / DESC : 내림차순 (생략하면 ASC)
|
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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
|
SQL> SELECT EMPNO, ENAME, SAL
2 FROM EMP
3 ORDER BY SAL DESC;
EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
7902 FORD 3000
7788 SCOTT 3000
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7499 ALLEN 1600
7844 TURNER 1500
7934 MILLER 1300
7521 WARD 1250
7654 MARTIN 1250
EMPNO ENAME SAL
---------- ---------- ----------
7876 ADAMS 1100
7900 JAMES 950
7369 SMITH 800
14 개의 행이 선택되었습니다.
SQL> SELECT EMPNO, ENAME, SAL
2 FROM EMP
3 ORDER BY SAL ASC;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7900 JAMES 950
7876 ADAMS 1100
7521 WARD 1250
7654 MARTIN 1250
7934 MILLER 1300
7844 TURNER 1500
7499 ALLEN 1600
7782 CLARK 2450
7698 BLAKE 2850
7566 JONES 2975
EMPNO ENAME SAL
---------- ---------- ----------
7788 SCOTT 3000
7902 FORD 3000
7839 KING 5000
SQL> SELECT EMPNO, ENAME, SAL, SAL*12+NVL(COMM,0) ANNSAL
2 FROM EMP
3 ORDER BY ANNSAL DESC;
EMPNO ENAME SAL ANNSAL
---------- ---------- ---------- ----------
7839 KING 5000 60000
7902 FORD 3000 36000
7788 SCOTT 3000 36000
7566 JONES 2975 35700
7698 BLAKE 2850 34200
7782 CLARK 2450 29400
7499 ALLEN 1600 19500
7844 TURNER 1500 18000
7654 MARTIN 1250 16400
7934 MILLER 1300 15600
7521 WARD 1250 15500
EMPNO ENAME SAL ANNSAL
---------- ---------- ---------- ----------
7876 ADAMS 1100 13200
7900 JAMES 950 11400
7369 SMITH 800 9600
14 개의 행이 선택되었습니다.
SQL> --연봉을 많이 받는 순으로 정렬
SQL> SELECT EMPNO, ENAME, SAL, SAL*12+NVL(COMM,0) ANNSAL
2 FROM EMP
3 ORDER BY 4 DESC;
EMPNO ENAME SAL ANNSAL
---------- ---------- ---------- ----------
7839 KING 5000 60000
7902 FORD 3000 36000
7788 SCOTT 3000 36000
7566 JONES 2975 35700
7698 BLAKE 2850 34200
7782 CLARK 2450 29400
7499 ALLEN 1600 19500
7844 TURNER 1500 18000
7654 MARTIN 1250 16400
7934 MILLER 1300 15600
7521 WARD 1250 15500
EMPNO ENAME SAL ANNSAL
---------- ---------- ---------- ----------
7876 ADAMS 1100 13200
7900 JAMES 950 11400
7369 SMITH 800 9600
14 개의 행이 선택되었습니다.
SQL> -- 네번째 있는 열을 기준으로 정렬
SQL> SELECT EMPNO, ENAME, SAL, SAL*12+NVL(COMM,0) ANNSAL
2 FROM EMP
3 ORDER BY 7 DESC;
ORDER BY 7 DESC
*
3행에 오류:
ORA-01785: ORDER BY 항목은 SELECT 목록 식의 수라야 합니다
SQL> -- 7번째 열이 없기 때문에 오류
|
cs |
- 성적이 높은 순으로 정렬해서 출력, 동점자 처리 > 가나다 순으로
사원의 월급이 많은 순으로 출력. 단, 동일급여를 받는 사람은 이름이 가나다순으로 출력
SQL> SELECT EMPNO, ENAME, SAL
2 FROM EMP
3 ORDER BY SAL DESC, ENAME ASC;
- 지정하는 컬럼에 따라 정렬만 하고 SELECT에는 없을 수 있음
|
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
70
71
72
73
74
|
SQL> -- 성적이 높은 순으로 정렬해서 출력, 동점자 처리 > 가나다 순으로
SQL>
SQL> -- 사원의 월급이 많은 순으로 출력. 단, 동일급여를 받는 사람은 이름이 가나다순으로 출력
SQL> SELECT EMPNO, ENAME, SAL
2 FROM EMP
3 ORDER BY SAL DESC, ENAME ASC;
EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
7902 FORD 3000
7788 SCOTT 3000
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7499 ALLEN 1600
7844 TURNER 1500
7934 MILLER 1300
7654 MARTIN 1250
7521 WARD 1250
EMPNO ENAME SAL
---------- ---------- ----------
7876 ADAMS 1100
7900 JAMES 950
7369 SMITH 800
-- 30번 부서 사원들의 사번, 이름, 급여, 부서번호를 출력하되 이름이 가나다순으로 출력
SQL> SELECT EMPNO, ENAME, SAL, DEPTNO
2 FROM EMP
3 WHERE DEPTNO = 30
4 ORDER BY ENAME ASC;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7499 ALLEN 1600 30
7698 BLAKE 2850 30
7900 JAMES 950 30
7654 MARTIN 1250 30
7844 TURNER 1500 30
7521 WARD 1250 30
SQL> -- 급여를 많이 받는 순으로 사번, 이름, JOB ?
SQL> SELECT EMPNO, ENAME, JOB
2 FROM EMP
3 ORDER BY SAL DESC;
EMPNO ENAME JOB
---------- ---------- ---------
7839 KING PRESIDENT
7902 FORD ANALYST
7788 SCOTT ANALYST
7566 JONES MANAGER
7698 BLAKE MANAGER
7782 CLARK MANAGER
7499 ALLEN SALESMAN
7844 TURNER SALESMAN
7934 MILLER CLERK
7521 WARD SALESMAN
7654 MARTIN SALESMAN
EMPNO ENAME JOB
---------- ---------- ---------
7876 ADAMS CLERK
7900 JAMES CLERK
7369 SMITH CLERK
14 개의 행이 선택되었습니다.
SQL> -- SAL에 따라 정렬만 하고 SELECT에는 없을 수 있음
|
cs |
더보기
| ++ 응용 1. 사원들의 이름, 부서 번호, 부서 이름을 출력하라 ? SQL> SELECT E.ENAME, E.DEPTNO, D.DNAME 2 FROM EMP E, DEPT D 3 WHERE E.DEPTNO = D.DEPTNO; 2. 30 번 부서의 사원들의 이름, 직업, 부서위치를 출력하라 ? SQL> SELECT E.ENAME, E.JOB, D.LOC 2 FROM EMP E, DEPT D 3 WHERE E.DEPTNO = 30 4 AND D.DEPTNO = 30; 3. 커미션을 받는 사원의 이름 , 부서이름 및 부서위치를 출력하라 ? ( COMM 0 도 포함) SQL> SELECT E.ENAME, D.DNAME, D.LOC 2 FROM EMP E, DEPT D 3 WHERE E.DEPTNO = D.DEPTNO 4 AND COMM IS NOT NULL OR COMM=0; 4. dallas 에서 근무하는 사원의 이름 , 직업, 부서번호 , 부서이름을 출력하라 ? SQL> SELECT E.ENAME, D.DNAME, D.LOC 2 FROM EMP E, DEPT D 3 WHERE COMM IS NOT NULL 4 OR COMM = 0 5 AND E.DEPTNO = D.DEPTNO; 5. 이름에 A 가 들어가는 사원들의 이름과 부서이름을 출력하라 ? SQL> SELECT E.ENAME, D.DNAME 2 FROM EMP E, DEPT D 3 WHERE E.DEPTNO = D.DEPTNO 4 AND E.ENAME LIKE '%A%'; 6. 사원이름과 그 사원의 관리자 이름을 출력하라 ( 단 컬럼 HEADING 을 employeee, manager 출력 ) SQL> SELECT E.ENAME "employee", C.ENAME "manager" 2 FROM EMP E, EMP C 3 WHERE E.MGR = C.EMPNO(+); 7. 사원이름과 직업, 급여, 급여등급 을 출력하라 ? SQL> SELECT E.JOB, E.SAL, S.GRADE 2 FROM EMP E, 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, DEPT D 3 WHERE E.DEPTNO = D.DEPTNO 4 AND E.SAL >= 3000; 9. 사원의 이름, 부서번호 와 같은 부서에 근무하는 동료 사원들을 출력하라 ? SQL> SELECT E.ENAME, E.DEPTNO, C.ENAME 2 FROM EMP E, EMP C 3 WHERE E.DEPTNO = C.DEPTNO 4 AND E.ENAME != C.ENAME;-- 10. BLAKE 이란 사원보다 늦게 입사한 사원의 이름과 입사일을 출력하라 ? SQL> SELECT C.ENAME, C.HIREDATE 2 FROM EMP E, EMP C 3 WHERE E.ENAME = 'BLAKE' 4 AND E.HIREDATE < C.HIREDATE; 22. 이름의 첫 글자가 M으로 시작하거나 급여가 1000 이상인 사람의 사원명과 급여 부서번호를 출력하라. SQL> SELECT ENAME, SAL, DEPTNO 2 FROM EMP 3 WHERE ENAME LIKE 'M%' 4 OR SAL >= 1000; 23. 급여가 1000이상이고 부서번호가 30번인 사람의 사원명 급여 사원번호를 출력하라. SQL> SELECT ENAME, SAL, DEPTNO 2 FROM EMP 3 WHERE SAL >= 1000 4 AND DEPTNO = 30; 24. 이름이 A로 시작하는 사원번호,성명,부서번호를 출력하시오. SQL> SELECT EMPNO, ENAME, DEPTNO 2 FROM EMP 3 WHERE ENAME LIKE 'A%'; 25. 사원번호에 8을 포함하고 있는 사원번호,성명을 출력하시오. SQL> SELECT EMPNO, ENAME 2 FROM EMP 3 WHERE EMPNO LIKE '%8%'; 26. 급여가 800이상 1500미만의 사람의 이름,부서번호,월급을 출력하여라. SQL> SELECT ENAME, DEPTNO, SAL 2 FROM EMP 3 WHERE SAL >= 800 4 AND SAL < 1500; 27. 수당이 300 이거나, 500 이거나, 1400인 사람들의 사번, 이름, 수당을 출력하세요. SQL> SELECT EMPNO, ENAME, COMM 2 FROM EMP 3 WHERE COMM IN (300, 500, 1400); 28. 이름에서 2번째 자리에 L 이 들어가는 사람의 이름을 출력하세요. SQL> SELECT ENAME 2 FROM EMP 3 WHERE ENAME LIKE '_L%'; 27. 해당 관리자가 null 인 사람을 찾아 출력하여라. SQL> SELECT * 2 FROM EMP 3 WHERE MGR IS NULL; 28. 이름이 B로시작하는 사람의 연봉을 출력하라 SQL> SELECT SAL*12+NVL(COMM,0) 2 FROM EMP 3 WHERE ENAME LIKE 'B%'; 29. 연봉이 15000이상50000이하인 사원 이름을 출력하라 SQL> SELECT ENAME 2 FROM EMP 3 WHERE SAL*12+NVL(COMM,0) BETWEEN 15000 AND 50000; 30. 사원테이블에서 급여가 2500 이하이고 직무가 MANAGER인 사람의 사원번호, 성명, 급여, 해당관리자번호를 출력하라. SQL> SELECT EMPNO, ENAME, SAL, MGR 2 FROM EMP 3 WHERE SAL <= 2500 4 AND JOB = 'MANAGER'; 31. 사원테이블에서 입사일자가 82년도~83년도인 사원의 사원번호, 성명, 직무, 입사일자를 출력하라. SQL> SELECT EMPNO, ENAME, JOB, HIREDATE 2 FROM EMP 3 WHERE HIREDATE BETWEEN '82/01/01' AND '83/12/31'; 32. 입사일자가 83년 이후 이며 87년 이전인 사람의 이름과 사번을 출력하시오. SQL> SELECT ENAME, EMPNO 2 FROM EMP 3 WHERE HIREDATE > '83/01/01' 4 AND HIREDATE < '87/12/31'; (++추가적으로, 날짜는 '81%'과 같은 방식으로도 검색 가능) 33. 수당의 해당사항없는 사람들의 사번,이름, 입사날짜를 출력하시오. SQL> SELECT EMPNO, ENAME, HIREDATE 2 FROM EMP 3 WHERE COMM IS NULL; 34. 회계부서에서 급여가 1500이 넘는 사람 SQL> SELECT * 2 FROM EMP E, DEPT D 3 WHERE D.DNAME = 'ACCOUNTING' 4 AND E.SAL >= 1500; 35. 판매부서의 직원이름, 연봉, 입사일자 출력 SQL> SELECT E.ENAME, E.SAL*12+NVL(COMM,0), E.HIREDATE 2 FROM EMP E, DEPT D 3 WHERE D.DNAME = 'SALES'; 종합문제1) [5조건] 1. 컬럼명 1개 2. * <== $100 1개 3. 반올림 16.5 ==> 17 4. (급여) 5. 급여가 많은순으로 >> 1개의 SELECT문으로 사원별 급여 현황 ------------------------------------------------------------- KING *************** (5000) SCOTT *********** (3000) SMITH ****** (1650) 답) SQL> SELECT ENAME || LPAD(' ', ROUND(SAL/100+1), '*') || '(' || SAL || ')' "사원별 급여 현황" 2 FROM EMP 3 ORDER BY SAL DESC; +이름 때문에 길이 제각각인 것 정리 SQL> SELECT RPAD(ENAME, 8, ' ') || LPAD(' ', ROUND(SAL/100+1), '*') || '(' || SAL || ')' "사원별 급여 현황" 2 FROM EMP 3 ORDER BY SAL DESC; |