第51次：2014-10-21 051的第5章和第6章

admin · 发表于 2014-10-22 09:50:24

2014-10-21-A.txt

[root@station90 ~]# su - oracle
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 21 19:25:20 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn hr/oracle_4U
Connected.
SQL> select count(*) from employees;
COUNT(*)
----------
107
SQL> select count(manager_id) from employees;
COUNT(MANAGER_ID)
-----------------
106
SQL> select count(employee_id) from employees;
COUNT(EMPLOYEE_ID)
------------------
107
SQL> select count(commission_pct) from employees;
COUNT(COMMISSION_PCT)
---------------------
35
SELECT AVG(NVL(commission_pct, 0))
FROM
3 employees;
AVG(NVL(COMMISSION_PCT,0))
--------------------------
.072897196
SQL> select sum(commission_pct)/count(*) from employees;
SUM(COMMISSION_PCT)/COUNT(*)
----------------------------
.072897196
SQL> select sum(commission_pct)/count(commission_pct) from employees;
SUM(COMMISSION_PCT)/COUNT(COMMISSION_PCT)
-----------------------------------------
.222857143
SQL> select avg(commission_pct) from employees;
AVG(COMMISSION_PCT)
-------------------
.222857143
SELECT
2 department_id, AVG(salary)
FROM
employees
5 GROUP BY department_id ;
DEPARTMENT_ID AVG(SALARY)
------------- -----------
100 8601.33333
30 4150
7000
20 9500
70 10000
90 19333.3333
110 10154
50 3475.55556
40 6500
80 8955.88235
10 4400
DEPARTMENT_ID AVG(SALARY)
------------- -----------
60 5760
12 rows selected.
SQL> ed
Wrote file afiedt.buf
1 SELECT
2 salary, department_id, AVG(salary)
3 FROM
4 employees
5* GROUP BY department_id
SQL> /
salary, department_id, AVG(salary)
*
ERROR at line 2:
ORA-00979: not a GROUP BY expression
SQL> ed
Wrote file afiedt.buf
1 SELECT
2 department_id, max(salary)
3 FROM
4 employees
5* GROUP BY department_id
SQL> /
DEPARTMENT_ID MAX(SALARY)
------------- -----------
100 12008
30 11000
7000
20 13000
70 10000
90 24000
110 12008
50 8200
40 6500
80 14000
10 4400
DEPARTMENT_ID MAX(SALARY)
------------- -----------
60 9000
12 rows selected.
SQL> ed
Wrote file afiedt.buf
1 SELECT
2 last_name, department_id, max(salary)
3 FROM
4 employees
5* GROUP BY department_id
SQL> /
SQL> /
last_name, department_id, max(salary)
*
ERROR at line 2:
ORA-00979: not a GROUP BY expression
SQL> ed
Wrote file afiedt.buf
1 select last_name, a.maxsalary, a.department_id
2 from employees e ,
3 (SELECT
4 department_id, max(salary) maxsalary
5 FROM
6 employees
7 GROUP BY department_id ) a
8* where e.department_id=a.department_id and e.salary=a.maxsalary
9 /
LAST_NAME MAXSALARY DEPARTMENT_ID
------------------------- ---------- -------------
Greenberg 12008 100
Raphaely 11000 30
Hartstein 13000 20
Baer 10000 70
King 24000 90
Higgins 12008 110
Fripp 8200 50
Mavris 6500 40
Russell 14000 80
Whalen 4400 10
Hunold 9000 60
11 rows selected.
SQL>

复制代码

2014-10-21-B.txt

80 SA_REP 51900
80 51900
80 SA_REP 43000
80 43000
80 304500
90 AD_PRES 24000
90 24000
90 AD_VP 34000
90 34000
90 58000
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
100 FI_MGR 12008
100 12008
100 FI_ACCOUNT 39600
100 39600
100 51608
110 AC_MGR 12008
110 12008
110 AC_ACCOUNT 8300
110 8300
110 20308
629616
55 rows selected.
SQL> ed
Wrote file afiedt.buf
1 SELECT department_id, manager_id, job_id, SUM(salary)
2 FROM employees
3 WHERE department_id > 40
4* GROUP BY rollup (department_id,manager_id, job_id)
SQL> /
DEPARTMENT_ID MANAGER_ID JOB_ID SUM(SALARY)
------------- ---------- ---------- -----------
50 100 ST_MAN 36400
50 100 36400
50 120 SH_CLERK 11600
50 120 ST_CLERK 10500
50 120 22100
50 121 SH_CLERK 14700
50 121 ST_CLERK 10700
50 121 25400
50 122 SH_CLERK 12800
50 122 ST_CLERK 10800
50 122 23600
DEPARTMENT_ID MANAGER_ID JOB_ID SUM(SALARY)
------------- ---------- ---------- -----------
50 123 SH_CLERK 13900
50 123 ST_CLERK 12000
50 123 25900
50 124 SH_CLERK 11300
50 124 ST_CLERK 11700
50 124 23000
50 156400
60 102 IT_PROG 9000
60 102 9000
60 103 IT_PROG 19800
60 103 19800
DEPARTMENT_ID MANAGER_ID JOB_ID SUM(SALARY)
------------- ---------- ---------- -----------
60 28800
70 101 PR_REP 10000
70 101 10000
70 10000
80 100 SA_MAN 61000
80 100 61000
80 145 SA_REP 51000
80 145 51000
80 146 SA_REP 51000
80 146 51000
80 147 SA_REP 46600
DEPARTMENT_ID MANAGER_ID JOB_ID SUM(SALARY)
------------- ---------- ---------- -----------
80 147 46600
80 148 SA_REP 51900
80 148 51900
80 149 SA_REP 43000
80 149 43000
80 304500
90 AD_PRES 24000
90 24000
90 100 AD_VP 34000
90 100 34000
90 58000
DEPARTMENT_ID MANAGER_ID JOB_ID SUM(SALARY)
------------- ---------- ---------- -----------
100 101 FI_MGR 12008
100 101 12008
100 108 FI_ACCOUNT 39600
100 108 39600
100 51608
110 101 AC_MGR 12008
110 101 12008
110 205 AC_ACCOUNT 8300
110 205 8300
110 20308
629616
55 rows selected.
SQL> ed
Wrote file afiedt.buf
1 SELECT department_id, manager_id, job_id, SUM(salary)
2 FROM employees
3 WHERE department_id > 40
4* GROUP BY rollup (department_id,manager_id, job_id)
SQL> null
SP2-0042: unknown command "null" - rest of line ignored.
SQL> department_id
SP2-0734: unknown command beginning "department..." - rest of line ignored.
SQL> department_id,manager_id,
SP2-0734: unknown command beginning "department..." - rest of line ignored.
SQL> ed
Wrote file afiedt.buf
1 SELECT department_id, job_id, SUM(salary)
2 FROM employees
3 WHERE department_id > 40
4* GROUP BY cube (department_id, job_id)
SQL> /
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
629616
AD_VP 34000
AC_MGR 12008
FI_MGR 12008
PR_REP 10000
SA_MAN 61000
SA_REP 243500
ST_MAN 36400
AD_PRES 24000
IT_PROG 28800
SH_CLERK 64300
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
ST_CLERK 55700
AC_ACCOUNT 8300
FI_ACCOUNT 39600
50 156400
50 ST_MAN 36400
50 SH_CLERK 64300
50 ST_CLERK 55700
60 28800
60 IT_PROG 28800
70 10000
70 PR_REP 10000
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
80 304500
80 SA_MAN 61000
80 SA_REP 243500
90 58000
90 AD_VP 34000
90 AD_PRES 24000
100 51608
100 FI_MGR 12008
100 FI_ACCOUNT 39600
110 20308
110 AC_MGR 12008
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
110 AC_ACCOUNT 8300
34 rows selected.
SQL> create table t05105( a number , b number , c number ) ;
Table created.
SQL> insert into t05105 vaues ( 1, 10, 100) ;
insert into t05105 vaues ( 1, 10, 100)
*
ERROR at line 1:
ORA-00928: missing SELECT keyword
SQL> insert into t05105 values ( 1, 10, 100) ;
1 row created.
SQL> insert into t05105 values ( 2, 20, 200) ;
1 row created.
SQL> commit;
Commit complete.
SQL> alter table t05105 add ( d number ) ;
Table altered.
SQL> update t05105 set d=1000 where a=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05105 set d=2000 where a=2;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from t05105;
A B C D
---------- ---------- ---------- ----------
1 10 100 1000
2 20 200 2000
SQL> ed
Wrote file afiedt.buf
1 select a,b, c ,sum( d) from t05105
2* group by cube ( a , b ,c )
3 /
A B C SUM(D)
---------- ---------- ---------- ----------
3000
100 1000
200 2000
10 1000
10 100 1000
20 2000
20 200 2000
1 1000
1 100 1000
1 10 1000
1 10 100 1000
A B C SUM(D)
---------- ---------- ---------- ----------
2 2000
2 200 2000
2 20 2000
2 20 200 2000
15 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select a,b, c ,sum( d) from t05105
2* group by rollup ( a , b ,c )
3 /
A B C SUM(D)
---------- ---------- ---------- ----------
1 10 100 1000
1 10 1000
1 1000
2 20 200 2000
2 20 2000
2 2000
3000
7 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select a,b, c ,sum( d) from t05105
2 group by cube ( a , b ,c )
3 minus
4 select a,b, c ,sum( d) from t05105
5* group by rollup ( a , b ,c )
SQL> /
A B C SUM(D)
---------- ---------- ---------- ----------
1 100 1000
2 200 2000
10 100 1000
10 1000
20 200 2000
20 2000
100 1000
200 2000
8 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select a,b, c ,sum( d), grouping ( a) ga , grouping(b) gb , grouping (c) gc from t05105
2 group by cube ( a , b ,c )
3 minus
4 select a,b, c ,sum( d) from t05105
5* group by rollup ( a , b ,c )
SQL> /
select a,b, c ,sum( d), grouping ( a) ga , grouping(b) gb , grouping (c) gc from t05105
*
ERROR at line 1:
ORA-01789: query block has incorrect number of result columns
SQL> ed
Wrote file afiedt.buf
1 select a,b, c ,sum( d), grouping(a) ga , grouping(b) gb , grouping(c) gc from t05105
2* group by cube ( a , b ,c )
SQL> /
A B C SUM(D) GA GB GC
---------- ---------- ---------- ---------- ---------- ---------- ----------
3000 1 1 1
100 1000 1 1 0
200 2000 1 1 0
10 1000 1 0 1
10 100 1000 1 0 0
20 2000 1 0 1
20 200 2000 1 0 0
1 1000 0 1 1
1 100 1000 0 1 0
1 10 1000 0 0 1
1 10 100 1000 0 0 0
A B C SUM(D) GA GB GC
---------- ---------- ---------- ---------- ---------- ---------- ----------
2 2000 0 1 1
2 200 2000 0 1 0
2 20 2000 0 0 1
2 20 200 2000 0 0 0
15 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select a,b, c ,sum( d), grouping(a) ga , grouping(b) gb , grouping(c) gc from t05105
2* group by a , b ,c
SQL> /
A B C SUM(D) GA GB GC
---------- ---------- ---------- ---------- ---------- ---------- ----------
2 20 200 2000 0 0 0
1 10 100 1000 0 0 0
SQL> ed
Wrote file afiedt.buf
1 select a,b, c ,sum( d), grouping(a) ga , grouping(b) gb , grouping(c) gc from t05105
2* group by rollup(a , b ,c)
SQL> /
A B C SUM(D) GA GB GC
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 10 100 1000 0 0 0
1 10 1000 0 0 1
1 1000 0 1 1
2 20 200 2000 0 0 0
2 20 2000 0 0 1
2 2000 0 1 1
3000 1 1 1
7 rows selected.
SELECT
MAX(AVG(salary))
FROM
employees
5 GROUP BY department_id;
MAX(AVG(SALARY))
----------------
19333.3333
SQL> ed
Wrote file afiedt.buf
1 SELECT
2 MAX(AVG(salary))
3 FROM
4* employees
5 /
MAX(AVG(salary))
*
ERROR at line 2:
ORA-00978: nested group function without GROUP BY
SQL> ed
Wrote file afiedt.buf
1 SELECT
2 AVG(salary)
3 FROM
4* employees
SQL> /
AVG(SALARY)
-----------
6461.83178
SQL> select department_id did , sum(salary)
2 from employees
3 group by did;
group by did
*
ERROR at line 3:
ORA-00904: "DID": invalid identifier
SQL> ed
Wrote file afiedt.buf
1 select department_id did , sum(salary)
2 from employees
3 group by department_id
4* order by did
SQL> /
DID SUM(SALARY)
---------- -----------
10 4400
20 19000
30 24900
40 6500
50 156400
60 28800
70 10000
80 304500
90 58000
100 51608
110 20308
DID SUM(SALARY)
---------- -----------
7000
12 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select department_id did , sum(salary)
2 from employees
3 group by department_id
4* order by did desc
SQL> /
DID SUM(SALARY)
---------- -----------
7000
110 20308
100 51608
90 58000
80 304500
70 10000
60 28800
50 156400
40 6500
30 24900
20 19000
DID SUM(SALARY)
---------- -----------
10 4400
12 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select sum(salary)
2 from employees
3 group by department_id
4* order by department_id desc
SQL> /
SUM(SALARY)
-----------
7000
20308
51608
58000
304500
10000
28800
156400
6500
24900
19000
SUM(SALARY)
-----------
4400
12 rows selected.
SQL>

复制代码

2014-10-21-C.txt

169 Bloom Sales
168 Ozer Sales
167 Banda Sales
166 Ande Sales
165 Lee Sales
164 Marvins Sales
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
163 Greene Sales
162 Vishney Sales
161 Sewall Sales
160 Doran Sales
159 Smith Sales
158 McEwen Sales
157 Sully Sales
156 King Sales
155 Tuvault Sales
154 Cambrault Sales
153 Olsen Sales
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
152 Hall Sales
151 Bernstein Sales
150 Tucker Sales
149 Zlotkey Sales
148 Cambrault Sales
147 Errazuriz Sales
146 Partners Sales
145 Russell Sales
102 De Haan Executive
101 Kochhar Executive
100 King Executive
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
113 Popp Finance
112 Urman Finance
111 Sciarra Finance
110 Chen Finance
109 Faviet Finance
108 Greenberg Finance
206 Gietz Accounting
205 Higgins Accounting
178 Grant
229 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4128141029
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 213 | 6603 | 13 (62)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | MERGE JOIN OUTER | | 106 | 3286 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 107 | 1605 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |
|* 7 | HASH JOIN OUTER | | 107 | 3317 | 7 (15)| 00:00:01 |
| 8 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."DEPARTMENT_ID"(+)="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"(+)="D"."DEPARTMENT_ID")
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
36 consistent gets
0 physical reads
0 redo size
7382 bytes sent via SQL*Net to client
685 bytes received via SQL*Net from client
17 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
229 rows processed
SQL> ed
Wrote file afiedt.buf
1 select employee_id, last_name , department_name
2 from employees e , departments d
3 where e.department_id(+)=d.department_id
4 union
5 select employee_id, last_name , department_name
6 from employees e , departments d
7* where e.department_id=d.department_id(+)
SQL> /
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
100 King Executive
101 Kochhar Executive
102 De Haan Executive
103 Hunold IT
104 Ernst IT
105 Austin IT
106 Pataballa IT
107 Lorentz IT
108 Greenberg Finance
109 Faviet Finance
110 Chen Finance
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
111 Sciarra Finance
112 Urman Finance
113 Popp Finance
114 Raphaely Purchasing
115 Khoo Purchasing
116 Baida Purchasing
117 Tobias Purchasing
118 Himuro Purchasing
119 Colmenares Purchasing
120 Weiss Shipping
121 Fripp Shipping
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
122 Kaufling Shipping
123 Vollman Shipping
124 Mourgos Shipping
125 Nayer Shipping
126 Mikkilineni Shipping
127 Landry Shipping
128 Markle Shipping
129 Bissot Shipping
130 Atkinson Shipping
131 Marlow Shipping
132 Olson Shipping
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
133 Mallin Shipping
134 Rogers Shipping
135 Gee Shipping
136 Philtanker Shipping
137 Ladwig Shipping
138 Stiles Shipping
139 Seo Shipping
140 Patel Shipping
141 Rajs Shipping
142 Davies Shipping
143 Matos Shipping
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
144 Vargas Shipping
145 Russell Sales
146 Partners Sales
147 Errazuriz Sales
148 Cambrault Sales
149 Zlotkey Sales
150 Tucker Sales
151 Bernstein Sales
152 Hall Sales
153 Olsen Sales
154 Cambrault Sales
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
155 Tuvault Sales
156 King Sales
157 Sully Sales
158 McEwen Sales
159 Smith Sales
160 Doran Sales
161 Sewall Sales
162 Vishney Sales
163 Greene Sales
164 Marvins Sales
165 Lee Sales
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
166 Ande Sales
167 Banda Sales
168 Ozer Sales
169 Bloom Sales
170 Fox Sales
171 Smith Sales
172 Bates Sales
173 Kumar Sales
174 Abel Sales
175 Hutton Sales
176 Taylor Sales
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
177 Livingston Sales
178 Grant
179 Johnson Sales
180 Taylor Shipping
181 Fleaur Shipping
182 Sullivan Shipping
183 Geoni Shipping
184 Sarchand Shipping
185 Bull Shipping
186 Dellinger Shipping
187 Cabrio Shipping
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
188 Chung Shipping
189 Dilly Shipping
190 Gates Shipping
191 Perkins Shipping
192 Bell Shipping
193 Everett Shipping
194 McCain Shipping
195 Jones Shipping
196 Walsh Shipping
197 Feeney Shipping
198 OConnell Shipping
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
199 Grant Shipping
200 Whalen Administration
201 Hartstein Marketing
202 Fay Marketing
203 Mavris Human Resources
204 Baer Public Relations
205 Higgins Accounting
206 Gietz Accounting
Benefits
Construction
Contracting
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
Control And Credit
Corporate Tax
Government Sales
IT Helpdesk
IT Support
Manufacturing
NOC
Operations
Payroll
Recruiting
Retail Sales
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
Shareholder Services
Treasury
123 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3656339828
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 213 | 6603 | 15 (67)| 00:00:01 |
| 1 | SORT UNIQUE | | 213 | 6603 | 15 (67)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | MERGE JOIN OUTER | | 106 | 3286 | 6 (17)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 107 | 1605 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |
|* 8 | HASH JOIN OUTER | | 107 | 3317 | 7 (15)| 00:00:01 |
| 9 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("E"."DEPARTMENT_ID"(+)="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"(+)="D"."DEPARTMENT_ID")
8 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
4313 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
123 rows processed
SQL> ed
Wrote file afiedt.buf
1 select employee_id, last_name , department_name
2 from employees e full outer join departments d
3* on e.department_id=d.department_id
SQL> /
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
198 OConnell Shipping
199 Grant Shipping
200 Whalen Administration
201 Hartstein Marketing
202 Fay Marketing
203 Mavris Human Resources
204 Baer Public Relations
205 Higgins Accounting
206 Gietz Accounting
100 King Executive
101 Kochhar Executive
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
102 De Haan Executive
103 Hunold IT
104 Ernst IT
105 Austin IT
106 Pataballa IT
107 Lorentz IT
108 Greenberg Finance
109 Faviet Finance
110 Chen Finance
111 Sciarra Finance
112 Urman Finance
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
113 Popp Finance
114 Raphaely Purchasing
115 Khoo Purchasing
116 Baida Purchasing
117 Tobias Purchasing
118 Himuro Purchasing
119 Colmenares Purchasing
120 Weiss Shipping
121 Fripp Shipping
122 Kaufling Shipping
123 Vollman Shipping
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
124 Mourgos Shipping
125 Nayer Shipping
126 Mikkilineni Shipping
127 Landry Shipping
128 Markle Shipping
129 Bissot Shipping
130 Atkinson Shipping
131 Marlow Shipping
132 Olson Shipping
133 Mallin Shipping
134 Rogers Shipping
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
135 Gee Shipping
136 Philtanker Shipping
137 Ladwig Shipping
138 Stiles Shipping
139 Seo Shipping
140 Patel Shipping
141 Rajs Shipping
142 Davies Shipping
143 Matos Shipping
144 Vargas Shipping
145 Russell Sales
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
146 Partners Sales
147 Errazuriz Sales
148 Cambrault Sales
149 Zlotkey Sales
150 Tucker Sales
151 Bernstein Sales
152 Hall Sales
153 Olsen Sales
154 Cambrault Sales
155 Tuvault Sales
156 King Sales
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
157 Sully Sales
158 McEwen Sales
159 Smith Sales
160 Doran Sales
161 Sewall Sales
162 Vishney Sales
163 Greene Sales
164 Marvins Sales
165 Lee Sales
166 Ande Sales
167 Banda Sales
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
168 Ozer Sales
169 Bloom Sales
170 Fox Sales
171 Smith Sales
172 Bates Sales
173 Kumar Sales
174 Abel Sales
175 Hutton Sales
176 Taylor Sales
177 Livingston Sales
178 Grant
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
179 Johnson Sales
180 Taylor Shipping
181 Fleaur Shipping
182 Sullivan Shipping
183 Geoni Shipping
184 Sarchand Shipping
185 Bull Shipping
186 Dellinger Shipping
187 Cabrio Shipping
188 Chung Shipping
189 Dilly Shipping
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
190 Gates Shipping
191 Perkins Shipping
192 Bell Shipping
193 Everett Shipping
194 McCain Shipping
195 Jones Shipping
196 Walsh Shipping
197 Feeney Shipping
NOC
Manufacturing
Government Sales
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
IT Support
Benefits
Shareholder Services
Retail Sales
Control And Credit
Recruiting
Operations
Treasury
Payroll
Corporate Tax
Construction
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
Contracting
IT Helpdesk
123 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2631508678
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 122 | 5368 | 7 (15)| 00:00:01 |
| 1 | VIEW | VW_FOJ_0 | 122 | 5368 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN FULL OUTER| | 122 | 3782 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
4321 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
123 rows processed

复制代码

PLSQL Developer

select e.employee_id, e.last_name, e.first_name, d.department_name ,d.department_id
from employees e, departments d
where e.department_id=d.department_id;
select e.employee_id, e.last_name, e.first_name, d.department_name , department_id
from employees e join departments d
using (department_id);
select e.employee_id, e.last_name, e.first_name, d.department_name , d.department_id
from employees e join departments d
on ( e.department_id= d.department_id);
select e.employee_id, e.last_name, e.first_name, d.department_name , department_id
from employees e natural join departments d;
select e.employee_id, e.last_name, e.first_name, d.department_name , department_id
from employees e join departments d
using (department_id, manager_id);
select * from employees where employee_id=178;
select employee_id, last_name, first_name , department_name
from employees e, departments d
where e.department_id=d.department_id
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM
employees e JOIN departments d
ON
(e.department_id = d.department_id)
AND
e.manager_id = 149 ;
select worker.last_name||' works for '||manager.last_name
from employees worker, employees manager
where worker.manager_id=manager.employee_id;
select * from user_ind_columns uic where uic.TABLE_NAME='EMPLOYEES';
create index isalary on employees(salary) compute statistics;
create index isalaryl on job_grades( lowest_sal) compute statistics;
create index isalaryh on job_grades(highest_sal) compute statistics;
select worker.last_name||' works for '||manager.last_name
from employees worker, employees manager
where worker.manager_id=manager.employee_id(+);
select worker.last_name||' works for '||manager.last_name
from employees worker left outer join employees manager
on worker.manager_id=manager.employee_id;
select worker.last_name||' works for '||manager.last_name
from employees manager right outer join employees worker
on worker.manager_id=manager.employee_id;
select * from departments;

复制代码