oracle湘潭大学数据库数据查询实验报告

1、湘潭大学实验报告课 程：Oracle数据库实验题目:据査询学 院：信息工程学院专 业： 计算机科学与技术2班学 号：_J7姓 名：韩林波指导教师：郭云飞完成日期:一. 上机目的1. 掌握Select语句的运用,2. 掌握一些函数的应用，3. 掌握子查询的运用，4. 掌握连接和分组的应用,5. 掌握视图的创建。二. 实验内容常用oracle语句的学习，与相应视图的创建三. 上机作业写出下列应用对应的SQL语句，并将查询语句定义为视图，视图名根据题 号依次命名为VI、V2、，如果一个应用要定义多个视图，则视图名根据 题号依次命名为VI、Vl_2、。针对基本表EMP和DEPT完成下列査询1）检索EM

2、P中所有的记录。create or replace view vl as select * from emp;1 select * from emp;A结果 结果：區脚本输出|奄1解釋|漣】自动跟晾| DBMS输出| 0WA输出!1 EMPirolf ENAMEI JOBID MGRtn HTKEDATE SALD COMMD DEPTNO1T369 SMITHCIJERK7902 17-12 月-802300 Gull)202T499 ALLEff SALESMAN7698 20-2月-8131003003037521 WARDSALESMAN7698 22-2月-812750500304T

3、566 JOOSMAlTkGER7839 02-4 月-814475(null)2057654 MARTINSALESMAN7698 28-9 月-812750140030&7698 BLAKEMAllkGER7839 01-5月-814350(null)3077782 CLAMFflAllkGER7839 09-6 月-813950(null)1087788 SCOTTWIALYST756G 19-4 月-874500(null)2097844 TUR1IERSALESMAN7698 08-9 月-81300003010T876 ADAMSCIJERK7788 23-5月-872600(nu

4、ll)20117902 FORDWIALYST756G 03-12 月-814500(null)2012T934 MILLERCIJERK7782 23T 月-822800(null)10137839 KINGPRESIDEHT(nulll 17-11-816500(null)102）列出工资在1000到2000之间的所有员工的ENAME, DEPTNO, SAL。create or replace view v2 as select ename, deptno, sal from emp where sal between 1000 and 2000;1 select enaiae9 dep

5、tno f sal fron emp vdiere sal between 1000 and 2000;结果i m.1出丨闫解释|.）自动跟踪| - 园 1% l金呵坦 30.00323545 seconds1 select ename from eiap Aere deptno between 10 and 20 order by enane;園矽 尽 闿珂 30.00704195 seconds1select ename from em.p vrihere deptno=1 20 1 and job= 1 CLERK1 ;|i结果脚本输出|）解蓉|场自动跟喇 切別s输出| .附編出 结果：

6、 Hl ENAfflE SMITH ADANS7）显示名字中包含TH和LL的员工名字。create or replace view v7 as select ename from emp where enamelike %TH% or ename like J %LL% ;/ 因 J诲C3J 5SJ y U.UUJarUbb seconds1select ename from em.p vSiere enane like or enane like LL% ;|as題題o 0.00365214 secondsselect jobavg(2al) as avg_sal fron emp gxoi

7、Q? by job;D结果 结果：输出1 CLERK253T.52 SALESMMT29003 FRESIDEH65004 何AGER4258.3333333333333333333333333333333333335 ANALYST450010）查询出每个部门中工资最高的职工。create or replace view vlO asselect ename, job, max (sal) as max_sal from emp group by job, ename ;凰 忌 尽 題関 &0.00969115 seconds1 select enane；3objinax(sal) as i

8、aax_sal from emp grot by job,enaiae ;|结果结果:區脚本输出I齒解释|塾自动跟踪I3DBMS输出I 0OWA输出H EHAME |囲 JOB 目 BIAX SAL1 WARDSALESMAN27502 CLARKMANAGER39503 FORDANALYST45004 MILLERCLEKK28005 SfflITHCLEKK23006 SCOTTANALYST45007 TURNERSALESMAN30008 BLAKEMANAGER43509 ADAMSCLEKK260010 KINGPRESIDEHT650011 JONESMANAGER447511

9、）查询出每个部门比本部门平均工资高的职工人数。Create or replace view vll (deptno,count) as select deptno,count (*) from (select , from emp a, (select avg(sal) c, deptno from emp groupby deptno) b where 二 and group by deptno;12）列出至少有一个员工的所有部门。Create or replace view vl2 as select job, count(ename) from empgroup by job havin

10、g count(ename) 0;l 凰忍 齒須“ o. 00424989 seconds1 select job,count(enaiae) from emp group by job having count (enarne) 0;|(select sal from emp where enameSMITH);1select enaiae from emp vrftere sal (select sal from emp 5?here ename= 1 SMITH1 ) ;|(hull)GSWTIXFOFD7 MAFJIKBLAKE15）列出受雇口期早于其直接上级的所有员工。Create

11、or replace view vl5 as select from emp A where (selectfrom emp B where 二1 select h ensue from enp A xitere n. hi r edate3500;1 select distinct job from eiap vSiere sal3500;结果 脚本输岀览解释|自动跟踪|fBMS输出| -0WA 凜：阳 JOB1 PRESIDEHT2 MAN丸 GER3 ANALYST19）列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销 售部的部门编号。Create or replace

12、view view vl9 as select ename, dname from empnatural join dept where dname SALES(select avg (sal) from emp );1select enarne rom emp vrtiere sal(select ayg-(sal) from emp ) ;| (select nidLX(sal) fruju eiap where depvno=30);1 scloct enarae from eap dicrc job-(solect job from eup dicrc enane- SCOTT1) a

13、nd enaiie ! -iSCOTTi; A第果.疋狷出| : W 三龙隗疋丨ME临| . CWA筋出宰：TORD22）列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。Create or replace view v22 as select ename, sal from emp wheresal in(select sal from emp where deptno二30);1 select enaiae,sal freon emp Aere sal in (select sal from emp ?here deptno=30);D结果i聊本输出丨1解释I J自动跟|-3dbf/

14、s输出I OWA输出结果：oo1 ALLEN 31002 NXRTIH 27503 WARD27504 BLAKE43505 TURNER30006 JAMES245023）列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。Create or replace view v23 as select ename, sal from emp wheresal(select max(sal) from emp where deptno二30);0. 02245148 seconds1select dnamezcount (enaiae),avg (sal) from emp matural

15、join deptgrotj) by dnaiae;|结果脚本输岀1茴篦释|匚自动跟踪1D跚S输出|0WA输出结果:HI DNAME| COUHT 3HAf.1E)H) AVG (SAL)1 ACC0UWTIHG34416. 6666666666666666666666666666666666672 RESEARCH536753 SALES6 3066. 66666666666666666666666666666666666725）列出所有员工的姓名、部门名称和工资。Create or replace view v25 as select ename, dname, sal from emp

16、natural join dept;结果士里S脚本输岀1遡解創刼自动跟踪113BBMS输出| Qon输出S EHW.1Effi DNAME SAL1 CLAEKACC0U1TTIHG39502 KINGACCOUWTIHG65003 MILLERACCOUNTING28004 SCOTTRESEARCH45005 JOBESRESEARCH44756 FIXEDRESEARCH45007 ADAMSRESEARCH26008 SMITHRESEARCH2300SALES310010 WAHISALES275011 JAfilESSALES2450select en.aiaezdnaiae；sa

17、l from ernp natural join dept;26）列出所有部门的详细信息和部门人数。Createor replaceviewv26as selectnatural rightdname, count(ename), avg(sal), loc, deptno from emp outer join dept group by dname, loc, deptno ;1select dnone,coimt (cnonc),nvg( ol) zLoczdeptno fron cnp notui al riht outer 30m dept gioiip by dnoxicz丄0c,

18、deptno ;A结卑桝轲出1：傭稈1对自换雕 貞切祐箱出入泗自covmi cemvieO |UJ 砒 gal)|(1| udcdekisdt XESEAFfM53BT5DALLAS2D? OFEFUOKS0(null)EOSIOK H 期 V 0.0057504 secondsI select jol),iwLn(saL) from emp gro嘔 by job：l结果脚本输岀I铜解强I勸自动跟踪I 防粘输出丨，0M丸爺结果：H JOB | MI：N（SAL）|1 CLERK23002 SALESMAN27503 FRESIDEBT65004 MANAGER39505 ANALYST450

19、028）列出各个部门的MANAGER （经理）的最低薪金。Create or replace view v28 as select dname, min (sal) from emp natural join dept where empno in (select mgr from emp ) group by dname;1 select dnaneznd.n|3al) fran enp natural join, dept Scto eapno in (select ngr fiMTi eup ) qroup by dnane;结杲临魁：|懈经|幻自动眼踪9加昭输出| ?6Yk報出 结果i

20、 顾e id Mgu:】AUOUfTI339502 KESEAKCK44753 SMJ5435029）列出所有员工的年工资，按年薪从低到高排序。Create or replace view v29 as select ename, sal*12 as year_salary from emp order by year_salary;1 selectas year salary from eiap order by year salary;園 必屆裁 喝麹 V 0.00571345 seconds1 select dept_naiae,count (id) from student grouj

21、) by dept_naiae order by count (id);结果.脚本输出丨解释| . !自动跟踪|DBMS输出丨 0WA输出结果:11 DEPTJTAf.1E目 COUMTdD)1 Chemistry22022 Civil Eng.22043 Comp. Sci.27214 Physics272532) 查询考试成绩有不及格的学生的学号。create or replace view V32 as select distinct id from takes where grade凰 越凤 总厨 30.03025334 scnds1 select (distinct id from

22、takes idiere gca.de60;A结果嗣本输出丨熾释卜自动跟踪丨4hs输出丨応结果：m id1 011422 011753 010244 010965 01509_6 023987 026458 026569 0123410 0126011 0132912 0134713 0135414 0130733) 查询选了但还没有登记考试成绩的学生的学号。Create or replace view v33 as select id from takes where grade isnull and course id is not null:1 select id from takes

23、ere grade is null and course_id is not null;结果.脚本输出I闻解释丨自动跟孫I別眈 输出丨OWk输出 结果：0 ID1 010852 025493 025544 010105 014866 014457 014478 014949 0144310 0251711 0245112 0133113 0136814 0123634）列出计算机科学系与物理系的学生。（三种方式）create or replace view v34_l as select id, dept_name from studentwhere dept_name二Comp Sci or

24、 dept_name二Physics;忌* 超罔 30 01653933 seconds1select idrdept_name fr(m student -rtiere der)t_n&me=,Coup Sci or dept_name= Physics:A结果 脚本输出|闵解释|自动跟踪IDBMS输出 、DWX输出结果:111 ID (II DEnjW.1I1 05210Physics2 02376Physics3 0G2CGPhysics4 05211Physics5 0237TPhysics6 01367Physi cs7 03376Physics8 01368Phy si cs9 0

25、2378Physics10 13343Physics11 K317Physi c =12 13212Physics13 0936Physi cs14 08371Phvsicscreate or replace view v34_2 as select id, dept_name from student where (dept_name)二(Comp Sci ) or (dept_name)二(Physics)1select. i(iz(ippt_naifte rrom smdent rtiere (dert_name)= | Ccmp 5ci)or (deptname)=|Physics1)

26、:结杲 购本输出|懈釋j自动跟踪|灰眈 縞出|触输出 隸： IDIl DEFT KWi131 05210Pkyzics2 0Z3TOPhysics3 08202Pkysics4 05211PhysicsS 02377PkysicsG 01367PKysics7 0337&Physics8 013&3Pkyzics9 0Z378Physics10 13343Pkysics11 U317Pkysics12 13212Physics13 093Pkyzicscreate or replace view v34_3 as select id, dept_name from studentwhere d

27、ept_name in (select dept_namefrom student where dept_name=( Comp Sci)or (dept_name)二(Physics);1select iddeptname2from student, izhere dept_naiae in. (select d.ept_naiae from student35rtiere dept_naine= (Comp Sci.) or (dept_nane) = C Physics);结果冒聊本输出低I解釋|號自动跟踪|和恢输出丨输岀结果:H ID i DEPTJIAfilE1 05210Physi

28、cs2 0237GPhysics3 06202Physics4 05211Physics5 02377Physics6 01367Physics7 03376Physics8 01368Physics9 02378Physics10 13343Physics11 14317Physics12 13212Physics13 AQ3RRPhwi PT35) 列出除计算机科学系与物理系外其他系的学生。(三种方式)create or replace view v35_l as select * from student wheredept_name!二Comp Sci and dept_name!二P

29、hysics;1 select * Crum scudenc where depc_nane ! = Comp Scl and depunaiae! = Physics ;|A结杲输出 陶解釋|厨自动跟踪I则S输出|_OWA输出 结呆. ID ID NAME 囲 DEPT KAME 囲 ZT1 06622李昂Chenistry110420102 01412杨东生Civil En110920053 06692伍每龙Chenistry110220104 05452陈堆Civil En113620095 06623张磊Chenistry19920106 04669胡麟Chemi stry110820

30、087 04544尹小莹Civil110520088 05S79 丁昌缓Chcni slry19820099 05K0莹星Chenistry1112200910 05S81 丁宇Chenistry1106200911 05453 肖萼Civil En1116200912 02601 杨威Chenistry289200813 01570向秀娟Chenistry288200714 06528 邓蓉Civil En11092010create or replace view v35_3 as select * from student where idnot in(select id from st

31、udent where dept_name二Comp Sci or dept_name二Physics);1 select a xroti student wJtere id not in (select id Xroai scudent wJtere depc_nane= Coxp 9 cl ur depc_naa.e= Physics);A结杲i临箝出J笛译 愆自迦雕财s徘h| 30枫%* 结果:I ID|g| 倔0 DEWJArtE 1 2T .Im .1 06622孚昂C i so*/110120102 01412悔东生Civil Zn?z11092CO53 06692伍縛龙C is

32、try110220104 0W52炼胜Civil Zrr11162C095 06623张磊C is19920106 04669Chenistry11082C0BT 04544尹小至Civil Zrr11052C0B8 05579丁吕绘Chai is tryI9B2109g 05583Chn is tryt1122CO9tO 05581丁宇ChenistryttOB2CO9tl 05453肖苕Civil Mg,t1182C0912 02001ChtnistryZ892C0B13 01570Chn is tryZ8B2CO7t4 06523祁苕Civil Zag,t1092D1036）列出名称中含

33、有计算机的课程的洛称与开课系。create or replace view v36 as select title, dept_name from course where title like1 %计算机livrvqjjbu.awcujuu、1select title,dept_naiae from course rfiere title likieG计聲机卸 ; ； n-amejdepv_n=aiae fron student ifliere name like_1;结果 圧SB 脚本输岀1円解創闵自动跟踪|$db能输出| Lion输岀 IB I NAME DEFTHANE1 02376李

34、方方Physics2 04429李秋白Civil Eng.3 01235李志成Physics4 03496李东升Civil Eng.5 04317李江廃Physics6 04431李俊渊Civil Eng.7 01329李绵鹏Physics8 01237李源淸Physics9 02014李丹丹Comp. Sci.10 06690李哲朋Ch色mis try11 0219S李林贵Physics12 06665李晓芸Chemistry13 05696李逐舟Chemistry14 05323李大为Physics38）列出所有姓名以李开头、以军结束且只有3个字的学生的学号.姓名。create or replace view v38 as selec