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

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

《oracle湘潭大学数据库数据查询实验报告.docx》由会员分享，可在线阅读，更多相关《oracle湘潭大学数据库数据查询实验报告.docx（54页珍藏版）》请在冰豆网上搜索。

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

湘潭大学

实验报告

课程：

Oracle数据库

实验题目：

数据查询

学院：

信息工程学院

专业：

计算机科学与技术2班

学号：

17

姓名：

韩林波

指导教师：

郭云飞

完成日期：

一．上机目的

1.掌握Select语句的运用，

2.掌握一些函数的应用，

3.掌握子查询的运用，

4.掌握连接和分组的应用，

5.掌握视图的创建。

二．实验内容

常用oracle语句的学习，与相应视图的创建

三．上机作业

写出下列应用对应的SQL语句，并将查询语句定义为视图，视图名根据题号依次命名为V1、V2、…，如果一个应用要定义多个视图，则视图名根据题号依次命名为V1_1、V1_2、…。

针对基本表EMP和DEPT完成下列查询

1）检索EMP中所有的记录。

createorreplaceviewv1asselect*fromemp;

2）列出工资在1000到2000之间的所有员工的ENAME，DEPTNO，SAL。

createorreplaceviewv2asselectename,deptno,salfromempwheresalbetween1000and2000;

3）显示DEPT表中的部门号和部门名称，并按部门名称排序。

createorreplaceviewv3asselectdname,deptnofromdeptgroupbyDNAME,deptno;

4）显示所有不同的工作类型。

createorreplaceviewv4asselectdistinctjobfromemp;

5）列出部门号在10到20之间的所有员工，并按名字的字母排序。

createorreplaceviewv5asselectenamefromempwheredeptnobetween10and20orderbyename;

6）列出部门号是20，工作是“CLERK”（办事员）的员工。

createorreplaceviewv6asselectenamefromempwheredeptno='20'andjob='CLERK';

7）显示名字中包含TH和LL的员工名字。

createorreplaceviewv7asselectenamefromempwhereenamelike'%TH%'orenamelike'%LL%';

8）显示所有员工的名字和各项收入总和。

createorreplaceviewv8asselectename,sal+commassal_commfromemp;

9）查询每个部门的平均工资。

createorreplaceviewv9asselectjob,avg（sal）asavg_salfromempgroupbyjob;

10）查询出每个部门中工资最高的职工。

createorreplaceviewv10as

selectename,job,max（sal）asmax_salfromempgroupbyjob,ename;

11）查询出每个部门比本部门平均工资高的职工人数。

Createorreplaceviewv11（deptno,count）asselectdeptno,count（*）from（select,fromempa,（selectavg（sal）c,deptnofromempgroupbydeptno）bwhere=and>groupbydeptno;

12）列出至少有一个员工的所有部门。

Createorreplaceviewv12asselectjob,count（ename）fromempgroupbyjobhavingcount（ename）>0;

13）列出薪金比“SMITH”多的所有员工。

Createorreplaceviewv13asselectenamefromempwheresal>（selectsalfromempwhereename='SMITH'）;

14）列出所有员工的姓名及其直接上级的姓名。

Createorreplaceviewv14asselectdistinctaswork1,（selectenamefromempwhere=）aswork2fromempA;

15）列出受雇日期早于其直接上级的所有员工。

Createorreplaceviewv15asselectfromempAwhere<（selectfromempBwhere=;

16）列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

Createorreplaceviewv16asselectdname,empno,ename,job,mgr,hiredate,comm,deptno,sal,deptnofromempnaturalrightouterjoindept;

17）列出所有“CLERK”（办事员）的姓名及其部门名称。

Createorreplaceviewv17asselectename,dnamefromempnaturaljoindeptwhereJOB='CLERK';

18）列出最低薪金大于3500的各种工作。

Createorreplaceviewv17asselectdistinctjobfromempwheresal>3500;

19）列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

Createorreplaceviewviewv19asselectename,dnamefromempnaturaljoindeptwheredname='SALES';

20）列出薪金高于公司平均薪金的所有员工。

Createorreplaceviewv20asselectenamefromempwheresal>（selectavg（sal）fromemp）;

21）列出与“SCOTT”从事相同工作的所有员工。

Createorreplaceviewv21asselectenamefromempwherejob=（selectjobfromempwhereename='SCOTT'）andename!

='SCOTT';

22）列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

Createorreplaceviewv22asselectename,salfromempwheresalin（selectsalfromempwheredeptno=30）;

23）列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

Createorreplaceviewv23asselectename,salfromempwheresal>（selectmax（sal）fromempwheredeptno=30）;

24）列出在每个部门工作的员工数量、平均工资。

Createorreplaceview24asselectdname,count（ename）,avg（sal）fromempnaturaljoindeptgroupbydname;

25）列出所有员工的姓名、部门名称和工资。

Createorreplaceviewv25asselectename,dname,salfromempnaturaljoindept;

26）列出所有部门的详细信息和部门人数。

Createorreplaceviewv26asselectdname,count（ename）,avg（sal）,loc,deptnofromempnaturalrightouterjoindeptgroupbydname,loc,deptno;

27）列出各种工作的最低工资。

Createorreplaceviewv27asselectjob,min（sal）fromempgroupbyjob;

28）列出各个部门的MANAGER（经理）的最低薪金。

Createorreplaceviewv28asselectdname,min（sal）fromempnaturaljoindeptwhereempnoin（selectmgrfromemp）groupbydname;

29）列出所有员工的年工资，按年薪从低到高排序。

Createorreplaceviewv29asselectename,sal*12asyear_salaryfromemporderbyyear_salary;

30）给出有学生的系的名单。

createorreplaceviewv30（dept_name,id_num）asselectdept_name,count（id）fromstudentgroupbydept_name;

31）给出有学生的系的名单，按升序排列

createorreplaceviewv31（dept_name,id_num）asselectdept_name,count（id）fromstudentgroupbydept_nameorderbycount（id）;

32）查询考试成绩有不及格的学生的学号。

createorreplaceviewV32asselectdistinctidfromtakeswheregrade<60;

33）查询选了但还没有登记考试成绩的学生的学号。

Createorreplaceviewv33asselectidfromtakeswheregradeisnullandcourse_idisnotnull;

34）列出计算机科学系与物理系的学生。

（三种方式）

createorreplaceviewv34_1asselectid,dept_namefromstudentwheredept_name='Comp.Sci.'ordept_name='Physics';

createorreplaceviewv34_2asselectid,dept_namefromstudentwhere（dept_name）=（'Comp.Sci.'）or（dept_name）=（'Physics'）;

createorreplaceviewv34_3asselectid,dept_namefromstudentwheredept_namein（selectdept_namefromstudentwheredept_name=（'Comp.Sci.'）or（dept_name）=（'Physics'））;

35）列出除计算机科学系与物理系外其他系的学生。

（三种方式）

createorreplaceviewv35_1asselect*fromstudentwheredept_name!

='Comp.Sci.'anddept_name!

='Physics';

createorreplaceviewv35_3asselect*fromstudentwhereidnotin（selectidfromstudentwheredept_name='Comp.Sci.'ordept_name='Physics'）;

36）列出名称中含有"计算机"的课程的名称与开课系。

createorreplaceviewv36asselecttitle,dept_namefromcoursewheretitlelike'%计算机%';

37）列出所有姓名以"李"开头且只有3个字的学生的学号、姓名与所在系。

createorreplaceviewv37asselectID,name,dept_namefromstudentwherenamelike'李__';

38）列出所有姓名以"李"开头、以"军"结束且只有3个字的学生的学号、姓名。

createorreplaceviewv38asselectID,name,dept_namefromstudentwherenamelike'李_军';

39）查询所有姓名中第2个字为"小"的学生的姓名与所在系。

createorreplaceviewv39asselectID,name,dept_namefromstudentwherenamelike'_小%';

40）列出2010年春季选修了CS013号课程的学生学号及其成绩。

createorreplaceviewv40asselectID,gradefromtakeswherecourse_id='CS013'andyear='2010'andsemester='Spring';

41）FindthetitlesofcoursesintheComp.Sci.departmentthathave3credits.

createorreplaceviewv41asselecttitlefromcoursewheredept_name='Comp.Sci.'andcredits=3;

42）统计学生总人数。

createorreplaceviewv42（id_num）asselectcount（id）fromstudent;

43）统计选修了CS013号课程的学生人数。

createorreplaceviewv43（id_num）asselectcount（id）fromtakeswheresec_id='CS013';

44）统计每年选修了课程的学生人数。

createorreplaceviewv44（id_num）asselectcount（id）fromtakesgroupbyyear;

45）统计每年选修了课程的学生人数，按年份升序排列。

createorreplaceviewv45（id_num）asselectcount（id）fromtakeswheresec_id='CS013'groupbyyearorderbyyear;

46）统计每年选修了CS013号课程的学生人数。

createorreplaceviewv46（id_num,year）asselectcount（id）,yearfromtakeswheresec_id='CS013'groupbyyear;

47）统计各个学期选修了课程的学生人数。

createorreplaceviewv47（semester,id_num）asselectsemester,count（id）fromtakesgroupbysemester;

48）统计各个学期选修了CS013号课程的学生人数。

createorreplaceviewv48（semester,id_num）asselectsemester,count（id）fromtakeswheresec_id='CS013'groupbysemester;

49）统计每个学期每门课程的选修的学生人数。

createorreplaceviewv49（semester,course_id,id_num）asselectsemester,course_id,count（id）fromtakesgroupbysemester,course_id;

50）按年、学期、课程与开课号统计选修学生人数。

createorreplaceviewv50（year,semester,course_id,sec_id,id_num）asselectyear,semester,course_id,sec_id,count（id）fromtakesgroupbyyear,semester,course_id,sec_id;

51）统计2008年春季各门课程不及格学生的人数。

createorreplaceviewv51（course_id,id_num）asselectcourse_id,count（id）fromtakeswheregrade<60andyear='2008'andsemester='Spring'groupbycourse_id;

52）统计每个系教师的平均工资。

createorreplaceviewv52（course_id,avg_salary）asselectdept_name,avg（salary）frominstructorgroupbydept_name;

53）哪些系教师的平均工资高于全校教师的平均工资给出这些系的名单。

createorreplaceviewv53asselectdept_namefrominstructorgroupbydept_namehavingavg（salary）>（selectavg（salary）frominstructor）;

54）统计每个系教师的人数、最高工资与最低工资。

createorreplaceviewv54（dept_name,id_num,max_salary,min_salary）asselectdept_name,count（id）,max（salary）,min（salary）frominstructorgroupbydept_name;

55）统计各个学期每位教师授课门数。

createorreplaceviewv55（teaches_id,semester,course）asselectid,semester,count（course_id）fromteachesgroupbyid,semester;

56）统计每个系任课教师的人数。

createorreplaceviewv56（dept_name,id_num）asselectdept_name,count（id）frominstructorgroupbydept_name;

57）统计计算机科学系每个学生有成绩的课程门数和平均成绩。

createorreplaceviewv57（id,avg_grade,course_id_num）asselectid,avg（grade）,count（course_id）fromtakeswheregradeisnotnullgroupbyid;

58）统计每门课程的平均成绩。

createorreplaceviewv58（course_id,avg_grade）asselectcourse_id,avg（grade）fromtakesgroupbycourse_id;

59）统计每个学生的平均成绩。

createorreplaceviewv59（id,avg_grade）asselectid,avg（grade）fromtakesgroupbyid;

60）统计每门课程的平均成绩、最高成绩与最低成绩。

createorreplaceviewv60（id,avg_grade,max_grade,min_grade）asselectid,avg（grade）,max（grade）,min（grade）fromtakesgroupbyid;

61）统计每门课程的选修人数、平均成绩、最高成绩与最低成绩。

createorreplaceviewv61（course_id,count_id,avg_agrade,max_grade,min_grade）asselectcourse_id,count（id）,avg（grade）,max（grade）,min（grade）fromtakesgroupbycourse_id;

62）统计每门课程有成绩的学生人数、平均成绩、最高成绩与最低成绩。

createorreplaceviewv62（course_id,count_id,avg_agrade,max_grade,min_grade）asselectcourse_id,count（id）,avg（grade）,max（grade）,min（grade）fromtakeswheregradeisnotnullgroupbycourse_id;

63）计算每个学生有成绩的课程门数和平均成绩。

createorreplaceviewv63（id,course_id_num,avg_grade）asselectid,count（course_id）,avg（grade）fromtakesgroupbyid;

64）查询选修了3门课程以上的学生的学号和姓名。

createorreplaceviewv64asselectid,namefromtakesnaturaljoinstudentgroupbyid,namehavingcount（course_id）>=3;

65）查询平均成绩大于90的学生学号。

createorreplaceviewv65asselectidfromtakesgroupbyidhavingavg（grade）>90;

66）查询选修人数多于198人的开课。

create