應(yīng)用隨機(jī)過(guò)程習(xí)題

1、使用使用scott/tiger用戶(hù)下的用戶(hù)下的emp表和表和dept表完成下列練習(xí)表完成下列練習(xí)1列出至少有一個(gè)員工的所有部門(mén)。2列出薪金比“SMITH”多的所有員工。3列出所有員工的姓名及其直接上級(jí)的姓名。1.select distinct dnamefrom dept where deptno in (select distinct deptno from emp);2.select ename,job,mgr,hiredate,salfrom empwhere sal (select sal from emp where ename=SMITH);3.select e.ename, e.

2、mgr , w.enamefrom emp e, emp wwhere e.mgr=w.empno;4列出受雇日期早于其直接上級(jí)的所有員工。5列出部門(mén)名稱(chēng)和這些部門(mén)的員工信息，同時(shí)列出那些沒(méi)有員工的部門(mén)。6列出所有“CLERK”（辦事員）的姓名及其部門(mén)名稱(chēng)。4.select e.ename, e.mgr , e.hiredate,w.ename,w.hiredatefrom emp e, emp wwhere e.mgr=w.empnoand e.hiredate1500;8.select e.ename,d.dname from emp e,dept dwhere e.deptno=d.de

3、ptno and d.dname=upper(sales);或者：select ename from emp where deptno = (select deptno from dept where dname=upper(SALES);9.select ename, job, sal from empwhere sal (select avg(sal) from emp );10列出與“SCOTT”從事相同工作的所有員工。11列出薪金等于部門(mén)30中員工的薪金的所有員工的姓名和薪金。12列出薪金高于在部門(mén)30工作的所有員工的薪金的員工姓名和薪金。 10.select ename, job f

4、rom empwhere job = (select job from emp where ename=upper(scott);11.select ename, sal,job from empwhere sal in (select sal from emp where deptno=30);12.select ename, sal,job from empwhere sal all (select sal from emp where deptno=30);或者select ename ,sal from emp where sal(select max(sal) from emp wh

5、ere deptno=30);13列出在每個(gè)部門(mén)工作的員工數(shù)量、平均工資和平均服務(wù)期限。14列出所有員工的姓名、部門(mén)名稱(chēng)和工資。15列出從事同一種工作但屬于不同部門(mén)的員工的一種組合。13.select d.deptno,d.dname, count(e.ename), avg(e.sal), avg(months_between(sysdate,e.hiredate) from emp e, dept dwhere e.deptno(+)= d.deptnogroup by d.deptno,dname;或者下面的方法，這種方法更好理解，把需要匯總的生成一個(gè)視圖e。select e.*,d.d

6、name from (select deptno, count(ename) ,avg(sal),avg(months_between(sysdate,hiredate)/12) from emp group by deptno) e, dept dwhere e.deptno =d.deptno;14.select d.dname, e.ename, sal+nvl(comm,0) from emp e, dept dwhere d.deptno=e.deptno;15. select e.ename,e.job, e.deptno ,d.job, d.deptno from emp e,

7、emp dwhere e.job=d.job and e.deptnod.deptno;16列出所有部門(mén)的詳細(xì)信息和部門(mén)人數(shù)。17列出各種工作的最低工資。18列出各個(gè)部門(mén)的MANAGER（經(jīng)理）的最低薪金。19列出所有員工的年工資,按年薪從低到高排序。 16.select count(e.ename),d.dnamefrom emp e, dept dwhere e.deptno(+)= d.deptnogroup by e.deptno,d.dname;或者寫(xiě)成select d.deptno,e.*,d.dname from (select deptno,count(ename) from

8、emp group by deptno) e,dept dwhere e.deptno(+)= d.deptno;17.select job,min(sal+nvl(comm,0)from emp group by job;18.select deptno,min(sal) from emp where job=upper(manager)group by deptno ;19.select ename , (sal+nvl(comm,0)*12 yearsalfrom emporder by yearsal ;20.查找EMP表中前5條記錄 21查找EMP表中10條以后的記錄 22查找EMP

9、表中薪水第5高的員工 20 SELECT * FROM emp WHERE ROWNUM10; 22 SELECT * FROM (SELECT a.*,rownum AS rn FROM emp a ORDER BY sal DESC ) WHERE rn=5; 1、創(chuàng)建表myemp和emp表具有相同的結(jié)構(gòu)和記錄。（若只是結(jié)構(gòu)而沒(méi)有數(shù)據(jù)呢？只復(fù)制部分?jǐn)?shù)據(jù)呢？）2、給myemp的empno列添加主建約束。1.create table myemp as select * from emp;表的約束是復(fù)制不過(guò)來(lái)的，需要自己去添加表的約束是復(fù)制不過(guò)來(lái)的，需要自己去添加create table myd

10、ept1 as select * from dept where 1=2insert into mydept1 select * from dept where deptno20;或者create table mydept1 as select * from dept where deptno20;2.alter table myempadd constraint myemp_empno_pk primary key (empno);創(chuàng)建以下表teachercreate table teacher( teacherxh varchar2(10) primary key, teachername

11、 varchar2(20) ); 插入記錄，要求:教師的編號(hào)的格式是TH00001，TH00002.建立序列 create sequence teacher_seq maxvalue 99999; insert into teacher values (TH| ltrim(to_char(teacher_seq.nextval,00000), 張三); insert into teacher values (TH| ltrim(to_char(teacher_seq.nextval,00000), 李);使用使用PL/SQL塊編程實(shí)現(xiàn)，注意必要的異常處理塊編程實(shí)現(xiàn)，注意必要的異常處理1.輸入一

12、個(gè)員工號(hào)，輸出該員工的姓名、薪金和大概的服務(wù)年限（按年月日顯示）2.接收一個(gè)員工號(hào)，輸出該員工所在部門(mén)的名稱(chēng)3.接收一個(gè)員工號(hào)，如果該員工職位是MANAGER，并且在DALLAS工作那么就給他薪金加15；如果該員工職位是CLERK，并且在NEW YORK工作那么就給他薪金扣除5;其他情況不作處理。4.接收一個(gè)員工號(hào)，輸出這個(gè)員工所在部門(mén)的平均工資5.以交互的方式給部門(mén)表插入一條記錄，如果出現(xiàn)主鍵沖突的異常，請(qǐng)顯示“部門(mén)號(hào)已被占用”的字樣declare CURSOR c_emp(p_empno emp.empno%TYPE) IS SELECT emp.job,emp.empno,dept.lo

13、c,emp.deptno FROM emp,dept WHERE emp.deptno=dept.deptno and emp.empno=p_empno; eno emp.empno%type;begineno:=&no; for v_emp in c_emp(eno) loop if v_emp.job=MANAGER and v_emp.loc=DALLAS then update emp set sal = sal *1.15 where empno=eno; end if; if v_emp.job=CLERK and v_emp.loc=NEW YORK then upda

14、te emp set sal = sal *0.95 where empno=eno; end if; end loop;end;3declare cursor mycur is select emp.empno,emp.job,dept.loc from emp ,dept where emp.deptno=dept.deptno;begin for v_mycur in mycur loop if v_mycur.job=MANAGER and v_mycur.loc=DALLAS then update emp set sal = sal *1.15 where empno=v_mycu

15、r.empno; end if; if v_mycur.job=CLERK and v_mycur.loc=NEW YORK then update emp set sal = sal *0.95 where empno=v_mycur.empno; end if; end loop;end;declare cursor mycur is select * from emp for update; dno1 dept.deptno%type; dno2 dept.deptno%type; begin select deptno into dno1 from dept where loc=DAL

16、LAS;select deptno into dno2 from dept where loc=NEW YORK;for v_mycur in mycur loop if v_mycur.deptno=dno1 then update emp set sal = sal *1.15 where current of mycur; end if; if v_mycur.deptno=dno2 then update emp set sal = sal *0.95 where current of mycur; end if; end loop; end;創(chuàng)建員工表create table emp

17、 (emp_id number(5), emp_name varchar2(20), emp_salary number(4),job varchar2(20), dept_id number(3);部門(mén)表create table dept (dept_id number(3), dept_name varchar2(20), loc varchar2(20);編寫(xiě)一個(gè)數(shù)據(jù)庫(kù)觸發(fā)器，當(dāng)任何時(shí)候某個(gè)部門(mén)從dept中刪除時(shí)，該觸發(fā)器將從emp表中刪除該部門(mén)的所有雇員。 create or replace trigger tr_update_dept after delete on dept for

18、 each row begin delete from emp where deptno=:old.deptno; end;a)建立一個(gè)新的用戶(hù)mk 密碼m123;b)登錄到用戶(hù)mk 建立一張表 test;c)希望在scott用戶(hù)下能看到mk用戶(hù)中test表的數(shù)據(jù)?創(chuàng)建一個(gè)函數(shù)，以部門(mén)號(hào)為參數(shù)，返回該部門(mén)的平均工資。FUNCTION return_avgsal(p_deptno emp.deptno%TYPE) RETURN emp.sal%TYPE AS v_sal emp.sal%TYPE; BEGIN SELECT avg(sal) INTO v_sal FROM emp WHERE d

19、eptno=p_deptno; RETURN v_sal; END return_avgsal;創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，以部門(mén)號(hào)為參數(shù)，查詢(xún)?cè)摬块T(mén)的平均工資，并輸出該部門(mén)中比平均工資高的員工號(hào)、員工名。CREATE OR REPLACE PROCEDURE show_emp(p_deptno emp.deptno%TYPE)AS v_sal emp.sal%TYPE;BEGINSELECT avg(sal) INTO v_sal FROM emp WHERE deptno=p_deptno;DBMS_OUTPUT.PUT_LINE(p_deptno| |average salary is:|v_sa

20、l);FOR v_emp IN (SELECT * FROM emp WHERE deptno=p_deptno AND salv_sal) LOOP DBMS_OUTPUT.PUT_LINE(v_emp.empno| |v_emp.ename);END LOOP;EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(The department doesnt exists!);END show_emp;有某個(gè)學(xué)生運(yùn)動(dòng)會(huì)比賽信息的數(shù)據(jù)庫(kù)，保存了如下的表：運(yùn)動(dòng)員sporter（運(yùn)動(dòng)員編號(hào)sporterid，運(yùn)動(dòng)員姓名name，運(yùn)動(dòng)員性別se

21、x，所屬系號(hào)department）項(xiàng)目item（項(xiàng)目編號(hào)itemid，項(xiàng)目名稱(chēng)itemname，項(xiàng)目比賽地點(diǎn)location）成績(jī)grade（運(yùn)動(dòng)員編號(hào)id，項(xiàng)目編號(hào)itemid，積分mark）請(qǐng)用SQL語(yǔ)句完成如下功能：1. 建表，注意滿(mǎn)足如下要求：a) 定義各個(gè)表的主鍵外鍵約束b) 運(yùn)動(dòng)員的姓名和所屬系別不能為空值。c) 積分要么為空值，要么為6,4,2,0, 分別代表第一，二，三名和其他名次的積分，注意名次可以有并列名次，后面的排名不往前提升，例如，如果有兩個(gè)并列第一，則沒(méi)有第二名。2. 往表中插入數(shù)據(jù)：運(yùn)動(dòng)員（ 1001, 李明，男，計(jì)算機(jī)系 1002，張三，男，數(shù)學(xué)系 1003, 李

22、四，男，計(jì)算機(jī)系 1004, 王二，男，物理系 1005, 李娜，女，心理系 1006, 孫麗，女，數(shù)學(xué)系）項(xiàng)目（ x001, 男子五千米，一操場(chǎng) x002，男子標(biāo)槍?zhuān)徊賵?chǎng) x003, 男子跳遠(yuǎn)，二操場(chǎng) x004, 女子跳高，二操場(chǎng) x005, 女子三千米，三操場(chǎng)）積分（ 1001, x001, 6 1002, x001, 4 1003, x001, 2 1004, x001, 0 1001, x003, 4 1002, x003, 6 1004, x003, 2 1005, x004, 6 1006, x004, 4)建立sporter 表create table sporter(spor

23、terid number(4) primary key,name varchar2(20) not null,sex char(2),department varchar2(20) not null);建立item 表create table item(itemid varchar2(6) primary key,itemname varchar2(20), location varchar2(20);建立grade表( 記住id 不是主鍵，否則違反唯一性了）create table grade1(itemid varchar2(6),mark number(2),constraint fk_

24、itemidforeign key( itemid) references item( itemid),constraint mark_ck check ( mark in( 6,4,2,0);insert into sporter( sporterid, name,sex,department)values ( 1001, 李明, 男,計(jì)算機(jī));insert into sporter( sporterid, name,sex,department)values ( 1002, 張三, 男,數(shù)學(xué)系);insert into sporter( sporterid, name,sex,department)values ( 1003, 李四, 男,計(jì)算機(jī));insert into sporter( sporterid, name,sex,department)values ( 1004, 王二, 男,物理系);insert into sporter( sporterid, name,sex,department)values ( 1005, 李娜, 女,心理系);insert into sporter( sporterid, name,sex,department)values ( 1006, 孫麗, 女,數(shù)學(xué)系);insert into