Oracle大型資料庫系統課程 — 期中實作練習題

實作題

每一題均需完成必要相關操作以達到題目要求。
練習題目請至 FTP 下載 (桌面進入，帳號:學號，密碼:身分證字號)。
一開始請務必 SPOOL。
第 6 題需完成執行程式並將程式顯示出來。
完成後上傳至 FTP (檔名:學號姓名)。

小題1

利用 user(user1) 登入完成下列問題:

  • DBA 必需給於正確的物件及系統權限，並針對 scott.emp 物件建一公用同義字 emp1。

小題2

修改 SQL*Plus 環境提示句為 DB1>，並分別用不同環境指令定義二個變數:

  • school－Takming
  • name－<你的姓名>

小題3

顯示如下資料:

  • 期中考日期
    112/10/31

小題4

顯示出員工資料表中員工姓名、薪水、佣金、主管代碼及其他二個虛擬欄位資料(薪水格式三位一撇、佣金若為 null vlue 則以 0 取代，主管代碼若為 null vlue 則以取代，其中員工姓名只顯示四碼且欄位標頭以 Name 顯示)

小題5

顯示下列 output:

  • Employee Data
    1－SMITH：salary=800，job is CLERK，1980 年進入公司
    2－ ………………

小題6

顯示下列報表:

​

實作解答

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
162
163
164
165
166
167
SQL> spool 1
SQL> set linesize 120
SQL> set pagesize 40
SQL> show user
USER 為 "SYSTEM"
SQL> create user userl identified by userl;

已建立使用者。

SQL> grant connect to user1;

順利授權。

SQL> grant select on scott.emp to userl;

順利授權。

SQL> create public synonym emp1 for scott.emp；

已建立同義字。

SQL> conn userl/userl
已連線.
SQL> set sqlprompt 'DB1>'
DB1> define school='Takming'
DB1> accept name prompt 'Enter your name:'
Enter your name:Error
DB1>define
DEFINE _DATE          = "24-10月-23" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER          = "ERIC" (CHAR)
DEFINE _PRIVILEGE     = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1201000200" (CHAR)
DEFINE _EDITOR        = "NotePad" (CHAR)
DEFINE _O_VERSION     = "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE     = "1201000200" (CHAR)
DEFINE SCHOOL         = "Takming" (CHAR)
DEFINE NAME           = "Error" (CHAR)
DB1> select sysdate
  2   from dual;

SYSDATE
----------------
24-10月-23

DB1> select substr(sysdate,1,4)-1911||'/'||substr(sysdate,6,2)||'/'||to_char(to_number (substr(sysdate,9,2))+7) 期中考日期
  2   from dual;

期中考日期
---------------------------------------------------------------------------------
112/10/31

DB1> col ename heading 'Name' format a4
DB1> set wrap off
DB1> col sal format '999,999'
DB1> select ename,sal,nvi(comm, 0) comm,nvl(to_char(mgr),'BOSS') ngr,rovnum,rowid
  2   from empl;

Name       SAL       COMM MGR                                           ROWNUM ROWID
---- --------- ---------- ----------------------------------------- ---------- ------------------
SMIT       800          0 7902                                               1 AAAWyEAAGAAAADEAAA
ALLE     1,600        300 7698                                               2 AAAWyEAAGAAAADEAAB
WARD     1,250        500 7698                                               3 AAAWyEAAGAAAADEAAC
JONE     2,975          0 7839                                               4 AAAWyEAAGAAAADEAAD
MART     1,250       1400 7698                                               5 AAAWyEAAGAAAADEAAE
BLAK     2,850          0 7839                                               6 AAAWyEAAGAAAADEAAF
CLAR     2,450          0 7839                                               7 AAAWyEAAGAAAADEAAG
TURN     1,500          0 7698                                               8 AAAWyEAAGAAAADEAAH
JAME       950          0 7698                                               9 AAAWyEAAGAAAADEAAI
KING     5,000          0 BOSS                                              10 AAAWyEAAGAAAADEAAJ
FORD     3,000          0 7566                                              11 AAAWyEAAGAAAADEAAK
MILL     1,300          0 7782                                              12 AAAWyEAAGAAAADEAAL

已選取 12 個資料列.

DB1> select rownum||'-'||ename||' : salary='||sall||' , job is '||job||' , '||substr(hiredate,1,4)||' 年進入公司' "Employee Data"
  2   from empl;

Employee Data
-------------------------------------------------------------------------------------------------------------------------
1-SMITH : salary=800 , job is CLERK , 1980 年進入公司
2-ALLEN : salary=1600 , job is SALESMAN , 1981 年進入公司
3-WARD : salary=1250 , job is SALESMAN , 1981 年進入公司
4-JONES : salary=2975 , job is MANAGER , 1981 年進入公司
5-MARTIN : salary=1250 , job is SALESMAN , 1981 年進入公司
6-BLAKE : salary=2850 , job is MANAGER , 1981 年進入公司
7-CLARK : salary=2450 , job is MANAGER , 1981 年進入公司
8-KING : salary=5000 , job is PRESIDENT , 1981 年進入公司
9-TURNER : salary=1500 , job is SALESMAN , 1981 年進入公司
10-JAMES : salary=950 , job is CLERK , 1981 年進入公司
11-FORD : salary=3000 , job is ANALYST , 1981 年進入公司
12-MILLER : salary=1300 , job is CLERK , 1982 年進入公司

已選取 12 個資料列.

DB1> select deptno,ename,sal
  2   from empl;

     DEPTNO Name      SAL
----------- ---- --------
         20 SMIT      800
         30 ALLE    1,600
         30 WARD    1,250
         20 JONE    2,975
         30 MART    1,250
         30 BLAK    2,850
         10 CLAR    2,450
         10 KING    5,000
         30 TURN    1,500
         30 JAME      950
         20 FORD    3,000
         10 MILL    1,300

已選取 12 個資料列.

DB1> save 6
已建立 file 6.sql
DB1> edit 6

DB1> @6
星期二 10月 24              第   1
          Google A. Corp.
          Employee Report
   DEPTNO Nane      SAL
--------- ---- --------
       10 KING    5,000
          CLAR    2,450
          MILL    1,300
*********      --------
sun               8,750
       20 FORD    3,000
          SMIT      800
          JONE    2,975
**********     --------
sun               6,775
       30 JANE      950
          TURN    1,500
          MART    1,250
          WARD    1,250
          ALLE    1,600
          BLAK    2,850
**********     --------
sun               9,400


          High Confidential

DB1> get 6
  1  set linesize 37
  2  set pagesize 32
  3  set feedback off
  4  ttitle 'Google A. Corp.|Employee Report'
  5  btitle 'High Confidential'
  6  break on deptno skip 1
  7  compute sum of sal on deptno
  8  select deptno,ename,sal
  9  from empl
 10  order by deptno
 11  /
 12  clear compute
 13  clear break
 14  title off
 15  btitle off
 16  set feedback on
 17  set linesize 120 
 18  set pagesize 40
DB1> spool off