整理大公司的oracle筆試題附參考答案

nvl(name,0) 空則取0。 sustrb(manth，1,4)第一位開始取4位。 Decode(je，1，1，2)，je為1的時候顯示1，否則顯示2.

select classno, avg(score) from student group by classno having avg(score) = (select max(avg(score)) from student group by classno);

建立資料表空間neuspace，資料檔案命名為neudata.dbf，存放在d:data目錄下，檔案大小為200MB，設為自動成長，增量5MB，檔案最大為500MB。(8分)

答：create tablespace neuspace datafile ‘d:dataneudata.dbf’ size 200m auto extend on next 5m maxsize 500m;

2. 假設資料表空間neuspace已用盡500MB空間，現要求增加一個資料檔案，存放在e:appdata目錄下，檔案名稱為appneudata，大小為500MB，不自動成長。(5分)

答：alter tablespace neuspace add datafile ‘e:appdataappneudata.dbf’ size 500m;

3. 以系統管理員身份登入，建立帳號tom，設定tom的預設資料表空間為neuspace。為tom分配connect和resource系統角色，擷取基本的系統許可權。然後為tom分配對使用者scott的表emp的select許可權和對SALARY, MGR屬性的update許可權。(8分)

答：create user tom identified by jack default tablespace neuspace;

Grant connect, resource to tom;

Grant select, update(salary, mgr) on scott.emp to tom;

4. 按如下要求建立表class和student。(15分)

屬性類型(長度)預設值約束含義

CLASSNO數值 (2)無主鍵班級編號

CNAME變長字元 (10)無非空班級名稱

屬性類型(長度)預設值約束含義

STUNO數值 (8)無主鍵學號

SNAME變長字元 (12)無非空姓名

SEX字元 (2)男無性別

BIRTHDAY日期無無生日

EMAIL變長字元 (20)無唯一電子郵件

SCORE數值 (5, 2)無檢查成績

CLASSNO數值 (2)無外鍵，關聯到表CLASS的CLASSNO主鍵班級編號

答：create table class

(classno number(2) constraint class_classno_pk primary key,

cname varchar2(10) not null);

create table student

(stuno number(8) constraint student_stuno_pk primary key,

sname varchar2(12) not null,

sex char(2) default ‘男’,

birthday date,

email varchar2(20) constraint student_email_uk unique,

score number(5,2) constraint student_score_ck check(score>=0 and score<=100),

classno number(2) constraint student_classno_fk references class(classno)

);

5. 在表student的SNAME屬性上建立索引student_sname_idx(5分)

答：create index student_sname_idx on student(sname);

6. 建立序列stuseq，要求初值為20050001，增量為1，最大值為20059999。(6分)

答：create sequence stuseq increment by 1 start with 20050001 maxvalue 20059999 nocache nocycle;

7. 向表student中插入如下2行。(5分)

STUNOSNAMESEXBIRTHDAYEMAILSCORECLASSNO

從stuseq取值tom男1979-2-3 14:30:25tom@163.net89.501

從stuseq取值jerry預設值空空空2

答：insert into student values(stuseq.nextval, ’tom’, ’男’, to_date(‘1979-2-3

14:30:25’, ’yyyy-mm-dd fmhh24:mi:ss’), ’tom@163.net’, 89.50, 1);

insert into student (stuno, sname, classno) values(stuseq.nextval, ’jerry’, 2);

8. 修改表student的資料，將所有一班的學產生績加10分。(4分)

答：update student set score=score+10 where classno=1;

9. 刪除表student的資料，將所有3班出生日期小於1981年5月12日的記錄刪除。(4分)

答：delete from student where classno=3 and birthday > ’12-5月-81’;

10. 完成以下SQL語句。(40分)

(1) 按班級升序排序，成績降序排序，查詢student表的所有記錄。

答：select * from student order by classno, score desc;

(2) 查詢student表中所有二班的成績大於85.50分且出生日期大於1982-10-31日的男生的記錄。

答：select * from student where classno=2 and score>85.50 and birthday < ’31-10月-82’ and sex=’男’;

(3) 查詢student表中所有三班成績為空白的學生記錄。

答：select * from student where classno=3 and score is null;

(4) 表student與class聯集查詢，要求查詢所有學生的學號，姓名，成績，班級名稱。(使用oracle與SQL 99兩種格式)

答：select s.stuno, s.sname, s.score, c.cname from student s, class c where s.classno=c.classno;

(5) 按班級編號分組統計每個班的人數，最高分，最低分，平均分，並按平均分降序排序。

答：select classno, count(*), max(score), min(score), avg(score) from student group by classno order by avg(score) desc;

(6) 查詢一班學生記錄中所有成績高於本班學生平均分的記錄。

答：select * from student where classno=1 and score > (select avg(score) from student where classno=1);

(7) 統計二班學生中所有成績大於所有班級平均分的人數。

答：select count(*) from student where classno=2 and score > all (select avg(socre) from student group by classno);

(8) 查詢平均分最高的班級編號與分數。

答：select classno, avg(score) from student group by classno having avg(score) = (select max(avg(score)) from student group by classno);

(9) 查詢所有學生記錄中成績前十名的學生的學號、姓名、成績、班級編號。

答：select stuno, sname, score, classno from (select * from student order by score desc) where rownum<=10;

(10) 建立視圖stuvu，要求視圖中包含student表中所有一班學生的stuno, sname, score, classno四個屬性，並具有with check option限制。

答：create view stuvu

as

select stuno, sname,score,classno from student where classno=1 with check option;

1、比較大小

select decode(sign(變數1-變數2),-1,變數1,變數2) from dual; –取較小值

sign()函數根據某個值是0、正數還是負數，分別返回0、1、-1

例如：

變數1=10，變數2=20

則sign(變數1-變數2)返回-1，decode解碼結果為“變數1”，達到了取較小值的目的。

2、表、視圖結構轉化

現有一個商品銷售表sale，表結構為：

month　　　 char(6)　　　　　 –月份

sell　　　　number(10,2)　　　–月銷售金額

現有資料為：

200001　　1000

200002　　1100

200003　　1200

200004　　1300

200005　　1400

200006　　1500

200007　　1600

200101　　1100

200202　　1200

200301　　1300

想要轉化為以下結構的資料：

year　　　char(4)　　　　　 –年份

month1　　number(10,2)　　　–1月銷售金額

month2　　number(10,2)　　　–2月銷售金額

month3　　number(10,2)　　　–3月銷售金額

month4　　number(10,2)　　　–4月銷售金額

month5　　number(10,2)　　　–5月銷售金額

month6　　number(10,2)　　　–6月銷售金額

month7　　number(10,2)　　　–7月銷售金額

month8　　number(10,2)　　　–8月銷售金額

month9　　number(10,2)　　　–9月銷售金額

month10　　number(10,2)　　　–10月銷售金額

month11　　number(10,2)　　　–11月銷售金額

month12　　number(10,2)　　　–12月銷售金額

結構轉化的SQL語句為：

create or replace view

v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)

as

select

substrb(month,1,4),

sum(decode(substrb(month,5,2),’01′,sell,0)),

sum(decode(substrb(month,5,2),’02′,sell,0)),

sum(decode(substrb(month,5,2),’03′,sell,0)),

sum(decode(substrb(month,5,2),’04′,sell,0)),

sum(decode(substrb(month,5,2),’05′,sell,0)),

sum(decode(substrb(month,5,2),’06′,sell,0)),

sum(decode(substrb(month,5,2),’07′,sell,0)),

sum(decode(substrb(month,5,2),’08′,sell,0)),

sum(decode(substrb(month,5,2),’09′,sell,0)),

sum(decode(substrb(month,5,2),’10′,sell,0)),

sum(decode(substrb(month,5,2),’11′,sell,0)),

sum(decode(substrb(month,5,2),’12′,sell,0))

from sale

group by substrb(month,1,4);

79、CASE語句的用法?

Oracle用法很簡單：

SELECT last_name, job_id, salary

CASE job_id

WHEN ‘IT_PROG’ THEN 1.10*salary

WHEN ‘ST_CLERK’ THEN 1.15*salary

WHEN ‘SA_REP’ THEN 1.20*salary

ELSE salary END “REVISED_SALARY”

FROM employees

80、 truncate和delete的區別?

1、TRUNCATE在各種表上無論是大的還是小的都非常快。如果有ROLLBACK命令DELETE將被撤銷，而TRUNCATE則不會被撤銷。

2、TRUNCATE是一個DDL語言而DELETE是DML語句，向其他所有的DDL語言一樣，他將被隱式提交，不能對TRUNCATE使用ROLLBACK命令。

3、TRUNCATE將重新設定高水平線和所有的索引。在對整個表和索引進行完全瀏覽時，經過TRUNCATE操作後的表比DELETE操作後的表要快得多。

4、TRUNCATE不能觸發觸發器，DELETE會觸發觸發器。

5、不能授予任何人清空他人的表的許可權。

6、當表被清空後表和表的索引講重新設定成初始大小，而delete則不能。

7、不能清空父表。

81、 資料表空間如何擴充?並用語句寫出?

兩種擴充方式：

a) 增加資料檔案

alter tablespace tablespace_name add datafile ‘’ xxMB

b) 擴充資料檔案大小

alter database datafile ‘’ resize newMB

82、 資料表空間區管理方式?哪種方式現在是推薦使用的?

a) 字典管理方式

extent management dictionary;預設

b) 本地管理方式

extent management local[autoallocate/uniform xxmb];

83、 用什麼函數獲得日期?和日期中的月，日，年

to_char(sysdate,’year’):tow thsound six to_char(sysdate,’yyyy’) :2006

to_char(sysdate,’month’):8月 to_char(sysdate,’mm’):08

to_char(sysdate,’day’):星期4 to_char(sysdate,’dd’):22

84、 分區表的應用?

a) 一個分區表有一個或多個分區，每個分區通過使用定界分割、散列分區、或組合分區分區的行

b) 分區表中的每一個分區為一個段，可各自位於不同的資料表空間中

c) 對於同時能夠使用幾個進程進行查詢或操作的大型表分區非常有用

85、 談談索引的用法及原理?

索引是若干資料行的關鍵字的列表，查詢資料時，通過索引中的關鍵字可以快速定位到要訪問的記錄所在的資料區塊，從而大大減少讀取資料區塊的I/O次數，因此可以顯著提高效能。

86、 預存程序的應用，如何既有輸入又有輸出?

Create procedure pro_name

(xxxx in/out type;

yyyy in/out/inout type;

) is/as

zzzz type;

begin

sqlpro;

exception

exceptionxxxxx;

commit;

end;

87、 常發生的異常有哪些?

常用預定義例外

CURSOR_ALREADY_OPEN — ORA-06511 SQLCODE = -6511 遊標已經開啟

DUP_VAL_ON_INDEX — ORA-00001 SQLCODE = -1 違反唯一性限制式

INVALID_CURSOR — ORA-01001 SQLCODE = -1001 非法遊標操作

INVALID_NUMBER — ORA-01722 SQLCODE = -1722 字元向數字轉換失敗

LOGIN_DENIED — ORA-01017 SQLCODE = -1017

NO_DATA_FOUND — ORA-01403 SQLCODE = +100 沒有找到資料

NOT_LOGGED_ON — ORA-01012 SQLCODE = -1012 沒有串連到資料庫

PROGRAM_ERROR — ORA-06501 SQLCODE = -6501 內部錯誤

STORAGE_ERROR — ORA-06500 SQLCODE = -6500

TIMEOUT_ON_RESOURCE — ORA-00051 SQLCODE = -51

TOO_MANY_ROWS — ORA-01422 SQLCODE = -1422 返回多行

TRANSACTION_BACKED_OUT — ORA-00061 SQLCODE = -61

VALUE_ERROR — ORA-06502 SQLCODE = -6502 數值轉換錯誤

ACCESS_INTO_NULL試圖為NULL對象的屬性賦值

ZERO_DIVIDE — ORA-01476 SQLCODE = -1476 被零除

OTHERS — 其它任何錯誤的處理

88、 如何使用異常?

在oracle中有三種類型的異常。預定義的異常 非預定義的異常 使用者定義的異常 第二種非預定義的異常是與特定的oracle錯誤關聯。並且用PRAGM EXCEPTION_INIT(EXCEPTION_NAME,ERROR_NUMBER)關聯一起的。但是到底有什麼用啊? 例如：declare dup_primary_key exception; pragma exception_init(dup_primary_key,-1); begin insert into itemfile values(‘i201′,’washer’,'spares’,100,50,250,12,30); exception when dup_primary_key then dbms_output.put_line(‘重複項編號-主鍵衝突’); end

第一種的使用方法：exception

when 異常名稱 then

異常處理代碼;

第三種的用法：if 條件 then

raise_application_error(-20000“““`-20999,提示資訊);

end if;

89、最佳化的策略一般包括:

• 記憶體最佳化

• 作業系統最佳化

• 資料存放區的最佳化

• 網路最佳化等方法

具體到不同的資料庫涉及到要調整不同的資料庫設定檔、不同的作業系統參數、網路參數等等, 不同的資料庫不同