Oracle數(shù)據(jù)庫復(fù)習(xí)題

1、Oracle數(shù)據(jù)庫上機(jī)復(fù)習(xí)題第一題創(chuàng)立數(shù)據(jù)庫和表總分40分1、啟動(dòng)SQL*Plus,在sys帳戶下創(chuàng)立學(xué)生根本信息表student、課程根本信息表 course和成績表score,通過限制條件實(shí)現(xiàn)數(shù)據(jù)的完整性,并顯示表結(jié)構(gòu).30分學(xué)生根本信息表 stude nt定義列名數(shù)據(jù)類型大小約束含義snoCHAR9主鍵學(xué)號(hào)sn ameCHAR10非空姓名ssexCHAR2為男或女性別birthdayDATE出生日期課程根本信息表 course定義列名數(shù)據(jù)類型大小約束含義enochar9主鍵】田千口 i=r. 課程號(hào)cn amevarchar250唯一課程名ccreditnu mber1非空學(xué)分成績表sc

2、ore定義列名數(shù)據(jù)類型大小約束含義snochar9主鍵,外鍵學(xué)號(hào)enochar9主鍵,外鍵】田千口 i=r. 課程號(hào)gradenu mber3非空成績2、向表中插入記錄.10分1 向?qū)W生根本信息表中添加兩條記錄,并顯示表中所有記錄. 第一條記錄為自己的學(xué)號(hào)、 姓名、性別和出生日期,另一條記錄任意編寫.2向課程根本信息表中添加如下五條記錄,并顯示全部記錄.enocn ameccredit12021003H計(jì)算機(jī)導(dǎo)論312021006HSQL Server數(shù)據(jù)庫技術(shù)312021021HOracle數(shù)據(jù)庫技術(shù)312021021HLinux操作系統(tǒng)312021021H人工智能33 向成績表中插入自己的

3、五門課程成績,并顯示全部記錄內(nèi)容.第二題觸發(fā)器的創(chuàng)立和使用每題20分,任意其一1、 在scott.emp表上創(chuàng)立一個(gè)觸發(fā)器,不允許刪除雇員號(hào)為7934雇員,并提示“禁止刪除 該名雇員.2、在scott.emp表上創(chuàng)立一個(gè)觸發(fā)器,當(dāng)插入、刪除或修改員工信息時(shí),統(tǒng)計(jì)各個(gè)部門的人 數(shù)及平均工資,并輸出部門名稱.第二題 創(chuàng)立PL/SQL塊,結(jié)合游標(biāo)編程.每題20分,任意其一1、 利用帶參的顯示游標(biāo),任意輸入一個(gè)員工的名字,在scott.emp表中查詢?cè)搯T工信息,并 輸出其員工號(hào)、工資,所在部門.如果該員工不存在,那么提示該員工不存在.2、 利用帶參的顯式游標(biāo),任意輸入一個(gè)員工的員工號(hào),在scott.e

4、mp表中查詢,并返回某員 工所在部門的平均工資.3、 利用帶參的顯式游標(biāo),任意輸入某個(gè)工作名稱,從scott.emp表中逐條輸出從事這一工作 的每位員工的編號(hào)、姓名和受雇如期.4、列出scott.emp表中工資高于平均工資的所有雇員的員工號(hào),員工名和工資值.5、 利用帶參的顯式游標(biāo),任意輸入一個(gè)部門號(hào),逐條輸出scott.emp表中該部門每位員工的 編號(hào)、姓名和受雇日期.6、輸出scott.emp表中10部門所有員工的員工號(hào)、員工名、工資和部門名稱.第三題 視圖、同義詞、序列.總分15分,視圖和序列二選一1、 創(chuàng)立以下視圖：數(shù)據(jù)源：scott.emp表和scott.dept表.視圖名 v_em

5、p_dept,視圖列包含雇員號(hào),雇員名,性別,部門號(hào),部門名,并顯示視圖內(nèi)容.10分2、 創(chuàng)立序列并利用序列向scott.dept表中插入部門號(hào) 80,90,100,部門名和地址見下表,顯示插入后的表記錄,最后刪除序列.10分dep tnodn ameLoc80ComputerCh ina90En glishCh ina100Chin eseChina3、為scott用戶的emp表創(chuàng)立一個(gè)公有同義詞,名為 scottemp,并練習(xí)刪除.5分第四題 平安治理總分25分步驟1創(chuàng)立以口令認(rèn)證的數(shù)據(jù)庫用戶U1和U2,口令分別為 usera, userb,默認(rèn)表空間為users,配額為10M,初始帳戶為

6、解鎖狀態(tài).步驟2為U1和U2用戶授予 create session權(quán)限.步驟3將scott.emp的select權(quán)限賦予U1,并允許將該權(quán)限間接授予其他用戶.步驟4以U1身份登錄數(shù)據(jù)庫,查詢scott.emp中的數(shù)據(jù).并將 scott.emp的select權(quán)限授予U2.步驟5禁止用戶U1將scott.emp的select權(quán)限再轉(zhuǎn)授予其他用戶.Oracle數(shù)據(jù)庫上機(jī)復(fù)習(xí)題參考答案第一題創(chuàng)立數(shù)據(jù)庫和表(總分40分)1、 創(chuàng)立學(xué)生根本信息表student、課程根本信息表 course和成績表score,通過限制條件實(shí) 現(xiàn)數(shù)據(jù)的完整性.(1 )創(chuàng)立學(xué)生根本信息表.(10分)程序CREATE TABLE

7、 stude nt(sno CHAR(9) PRIMARY KEY ,sn ame CHAR(10) NOT NULL,ssex CHAR(2) CHECK(ssex in('男','女'),birthday DATE);(2 )創(chuàng)立課程根本信息表.(8分)程序CREATE TABLE course(cno CHAR(9) PRIMARY KEY,cname VARCHAR2(50) UNIQUE,ccredit NUMBER(1) NOT NULL );(3) 創(chuàng)立成績表,學(xué)號(hào)和課程號(hào)為外鍵,二者聯(lián)合構(gòu)成主鍵,成績不能為空,成績值在0到100之間.(10分)程

8、序CREATE TABLE score(sno CHAR(9) REFERENCES stude nt(s no),cno CHAR(9) REFERENCES course(cno),grade NUMBER(3) NOT NULL,PRIMARY KEY(sno,cno);全部創(chuàng)立成功2分,顯示表結(jié)構(gòu)2分.2、 向表中插入記錄.(10分)(1)向?qū)W生根本信息表中添加兩條記錄,并顯示.(2分)insert into student values('202152760','梁貴毅','男',to_date ('1991-10-16'

9、;,'yyyy-mm-dd');insert into student values('202152753','張小花','女',to_date ('1990-06-01','yyyy-mm-dd');(2 )向課程根本信息表中添加五條記錄.(3分)INSERT INTO course V ALUES('12021003H','計(jì)算機(jī)導(dǎo)論',3);INSERT INTO course V ALUES('12021006H','SQL Serve

10、r 數(shù)據(jù)庫技術(shù)',3);INSERT INTO course V ALUES('12021021H',' Oracle 數(shù)據(jù)庫技術(shù)',3);INSERT INTO course V ALUES('12021021H','Linux 操作系統(tǒng)',3);INSERT INTO course V ALUES('12021021H','人工智能',2);(3)向成績表中插入自己的五門課程成績.(3分)in sert into score values('202152760','

11、;12021003H',99);in sert into score values('202152760','12021006H',99);in sert into score values('202152760','12021021H',99);in sert into score values('202152760','12021021H',99);in sert into score values'202152760','12021021H',99;顯示

12、記錄內(nèi)容2分.select*from stude nt;select*from course; select*from score;第二題觸發(fā)器的創(chuàng)立和使用每題 20分,任意其一1、 在scott.emp表上創(chuàng)立一個(gè)觸發(fā)器,不允許刪除雇員號(hào)為7934雇員,并提示“禁止刪除 該名雇員.1創(chuàng)立觸發(fā)器18分程序CREATE OR REPLACE TRIGGER trg_emp_delBEFORE delete ON scott.empFOR EACH ROWWHEN old.emp no=7934BEGINRAISE_APPLICATI0N_ERR0R-20000,'禁止刪除該雇員'

13、END tri_emp_del;丄2 執(zhí)行觸發(fā)操作：2分說明：插入、刪除或更新操作均可,答案不唯一.delete from scott.emp where emp no=7934;2、在scott.emp表上創(chuàng)立一個(gè)觸發(fā)器,當(dāng)插入、刪除或修改員工信息時(shí),統(tǒng)計(jì)各個(gè)部門的人 數(shù)及平均工資,并輸出部門名稱.1創(chuàng)立觸發(fā)器18分程序CREATE OR REPLACE TRIGGER trg_emp_dmlAfter in sert or update or delete on scott.emp declarev_dn ame dept.d name%type;beginfor v_dept in se

14、lect dept no ,co un t* nu m,avgsal avgsal from emp groupby dept noloopselect dn ame into v_dn ame from dept where dept no=v_dept.dept no; dbms_output.put_li nev_dept.dept no'|v_d name'|v_dept .nu m'|v_dept.avgsal;end loop;end trg_emp_dml;/(2)執(zhí)行觸發(fā)操作(2分) 插入、更新、刪除均可,答案不唯一.delete from scott.

15、emp where emp no=7934;第二題 創(chuàng)立PL/SQL塊,結(jié)合游標(biāo)編程.(每題20分,任意其一)說明：程序18分,翻開顯示開關(guān) SET SERVEROUTPUT ON并顯示執(zhí)行結(jié)果(2分)1、利用帶參的顯示游標(biāo),任意輸入一個(gè)員工的名字,在scott.emp表中查詢?cè)搯T工信息,并 輸出其員工號(hào)、工資,所在部門.如果該員工不存在,那么提示該員工不存在.程序DECLAREv_en ame emp.e name%TYPE;CURSOR c_emp (p_e name emp.e name%TYPE) IS SELECT emp no,e name,sal,dept no FROMemp

16、WHERE en ame=p_ v_emp c_emp%ROWTYPE;BEGINv_en ame:=&x;OPEN c_emp(v_e name);FETCH c_emp INTO v_emp;IF c_emp%FOUND THEN LOOPDBMS_OUTPUT.PUT_LINE(v_emp.emp no'|v_emp.e name|v_emp.sal'|v_emp.dept no);FETCH c_emp INTO v_emp;EXIT WHEN c_emp%NOTFOUND;END LOOP;ELSERAISE_APPLICATION_ERROR(-2OO1O,

17、'該員工不存在)；END IF;CLOSE c_emp;END;2、利用帶參的顯式游標(biāo),任意輸入一個(gè)員工的員工號(hào),在scott.emp表中查詢,并返回某員工所在部門的平均工資.執(zhí)行程序BEGINV_EMPNO:=&X;OPEN c_emp(v_emp no);FETCH c_emp INTO v_emp;select avg(sal) into avgsal from emp havi ng dept no=v_emp.dept no group by dept no; DBMS_OUTPUT.PUT_LINE(avgsal);CLOSE c_emp;END;/執(zhí)行結(jié)果(2分)

18、答案不唯一.3、 利用帶參的顯式游標(biāo),任意輸入某個(gè)工作名稱,從scott.emp表中逐條輸出從事這一工作 的每位員工的編號(hào)、姓名和受雇如期.程序DECLAREv_emp no emp.emp no%TYPE;CURSOR c_emp(p_emp no emp.emp no%type) ISSELECT dept no,A VG(sal) avgsal FROM emp HA VING dept no IN (SELECT dep tno FROM emp WHERE empn o=v_emp no) GROUP BY dept no;v_emp c_emp%ROWTYPE;BEGINv_emp

19、 no:=&x;OPEN c_emp(v_emp no);LOOPFETCH c_emp INTO v_emp;EXIT WHEN c_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp.dept no'|v_emp.avgsal);END LOOP;CLOSE c_emp;END;I4、列出scott.emp表中工資高于平均工資的所有雇員的員工號(hào),員工名和工資值.程序declarecursor c_emp is select emp no,en ame,sal from emp;avgsal emp.sal%type;v_emp c_emp%row

20、type;beginselect avg(sal) into avgsal from emp;ope n c_emp;loopfetch c_emp into v_emp;exit whe n c_emp% notfound;if v_emp.sal>avgsal the ndbms_output.put_li ne(v_emp.e name '|v_emp.emp no'|v_emp.sal);end if;end loop;en d;5、 利用帶參的顯式游標(biāo),任意輸入一個(gè)部門號(hào),逐條輸出scott.emp表中該部門每位員工的 編號(hào)、姓名和受雇日期.程序DECLAREv

21、_dept no emp.dept no%TYPE;CURSOR c_emp(p_dept no emp.dept no%type) ISSELECT emp no,e name, hiredate FROM emp WHERE dept no=p_dept no; v_emp c_emp%ROWTYPE;BEGINv_deptno:=&請(qǐng)輸入部門號(hào)；OPEN c_emp(v_dept no);LOOPFETCH c_emp INTO v_emp;EXIT WHEN c_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp.empno|' '|

22、v_emp.ename|' '|v_emp.hiredate);END LOOP;CLOSE c_emp;END;I6、輸出scott.emp表中10部門所有員工的員工號(hào)、員工名、工資和部門名稱.程序DECLAREv_dn ame dept.d name%TYPE;CURSOR c_emp ISSELECT emp no,e name,sal FROM emp WHERE dept no=10;v_emp c_emp%ROWTYPE;BEGINSELECT dn ame INTO v_dn ame FROM dept WHERE dept no=10;OPEN c_emp;LO

23、OPFETCH c_emp INTO v_emp;EXIT WHEN c_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp.emp no'|v_emp.e name '|v_emp.sal '|v_d name); END LOOP;CLOSE c emp;END;/說明：組卷方案中,觸發(fā)器和游標(biāo)分值相同,難度相似,二選一.第三題 視圖、同義詞、序列.(總分15分,視圖和序列二選一)1、 創(chuàng)立以下視圖：數(shù)據(jù)源：emp表和dept表.視圖名v_emp_dept,視圖列包含雇員號(hào),雇員名,性別,部門號(hào),部門名.顯示視圖內(nèi)容并使用DROP VIE

24、W命令刪除該視圖.2、 創(chuàng)立序列并利用序列向 dept表中插入部門號(hào)50、60、70、80,90,100,并刪除序列.3、 為scott用戶的emp表創(chuàng)立一個(gè)公有同義詞,名為scottemp,并練習(xí)刪除.4、 創(chuàng)立簇student_course,并在該簇中創(chuàng)立 student和course表.1、在scott用戶下創(chuàng)立視圖Create user usera_exer ide ntified by usera default tablespace users quota 10M on users acco unt lock;2、在scott用戶下創(chuàng)立序列程序CREATE SEQUENCE dep

25、t_sequenee INCREMENT BY 10 START WITH 80 MAXVALUE 100;INSERT INTO scott.dept(dept no,d name,loc)VALUES(dept_sequence.nextval, ' Computer', 'China');INSERTINTOscott.dept(dept no,d name,loc)VALUES(dept_sequence.nextval, 'English )'China');SELECT * FROM dept;DROP SEQUENCE de

26、pt_seque nee;3、在系統(tǒng)用戶sys下創(chuàng)立同義詞程序CREATE PUBLIC SYNONYM scottemp FOR scott.emp;SELECT * FROM scottemp;DROP PUBLIC SYNONYM scottemp;第四題 平安治理(總分25分)步驟1創(chuàng)立以口令認(rèn)證的數(shù)據(jù)庫用戶U1和U2,口令分別為 usera, userb,默認(rèn)表空間為users,配額為10M,初始帳戶為解鎖狀態(tài).(5分)以系統(tǒng)帳戶sys或system連接數(shù)據(jù)庫創(chuàng)立用戶U1和U2.Create user usera_exer ide ntified by usera default t

27、ablespace users quota 10M on users acco unt lock;creater user userb_exer ide ntified by userb default tablespace users quota 10M on users acco unt unl ock;步驟2為U1和U2用戶授予 create session權(quán)限.5分grant create sessi on to u1,u2;步驟3將scott.emp的select權(quán)限賦予U1,并允許將該權(quán)限間接授予其他用戶.5分grant select on scott.emp to u1 with grant option;步驟4以U1身份登錄數(shù)據(jù)庫,查詢scott.emp中的數(shù)據(jù).并將 scott.emp的select權(quán)限授予U2. 5 分以u(píng)1賬戶連接數(shù)據(jù)庫,驗(yàn)證是否擁有關(guān)于scott.emp表的查詢權(quán)限.conn u1/usera;select emp no,en ame,sal,dept no from scott.emp where emp no=7844;顯示結(jié)果說明,查詢和更新權(quán)限已經(jīng)被成功賦予.grant select on emp to u2;以u(píng)2帳戶連接數(shù)據(jù)庫,驗(yàn)證是否擁有相關(guān)權(quán)限.conn u2/userbselect * from scott.emp;執(zhí)行