(作业与答案)课件.ppt

1、练习：设如下四个表：student(学生信息表）sno sname sex birthday class108 曾华 男 09/01/77 95033105 匡明 男 10/02/75 95031107 王丽 女 01/23/76 95033101 李军 男 02/20/76 95033109 王芳 女 02/10/75 95031103 陆军 男 06/03/74 95031teacher(老师信息表）tno tname sex birthday prof depart804 李诚 男 12/02/58 副教授 计算机系856 李旭 男 03/12/69 讲师 电子工程系825 王萍 女 0

2、5/05/72 助教 计算机系831 刘冰 女 08/14/77 助教 电子工程系course(课程表）cno cname tno3-105 计算机导论 825 3-245 操作系统 8046-166 数字电路 856 9-888 高等数学 825score(成绩表）sno cno degree103 3-245 86 105 3-245 75109 3-245 68 103 3-105 92 105 3-105 88 109 3-105 76 101 3-105 64 107 3-105 91 108 3-105 78 101 6-166 85 107 6-166 79 108 6-166

3、81请写出下列查询语句并给出结果1、列出student表中所有记录的sname、sex和class列。2、显示教师所有的单位即不重复的depart列。3、显示学生表的所有记录。4、显示score表中成绩在60到80之间的所有记录。5、显示、显示score表中成绩为表中成绩为85，86或或88的记录。的记录。6、显示student表中“95031”班或性别为“女”的同学记录。7、以class降序显示student表的所有记录。8、以、以cno升序、升序、degree降序显示降序显示score表的所有记录。表的所有记录。9、显示“98031”班的学生人数。10、显示、显示score表中的最高分的学

4、生学号和课程号。表中的最高分的学生学号和课程号。11、显示、显示“3-105”号课程的平均分。号课程的平均分。请写出下列查询语句并给出结果12、显示、显示score表中至少有表中至少有5名学生选修的并以名学生选修的并以3开头的课程开头的课程 号的平均分数。号的平均分数。13、显示最低分大于70，最高分小于90 的sno列。14、显示所有学生的 sname、cno和degree列。15、显示所有学生的 sname、cname和degree列。16、列出、列出“95033”班所选课程的平均分。班所选课程的平均分。17、显示选修、显示选修“3-105”课程的成绩高于课程的成绩高于“109”号同学成绩

5、的号同学成绩的 所有同学的记录。所有同学的记录。18、显示、显示score中选修多门课程的同学中分数为非最高分成中选修多门课程的同学中分数为非最高分成 绩的记录。绩的记录。19、显示成绩高于学号为“109”、课程号为“3-105”的成绩 的所有记录。请写出下列查询语句并给出结果20、显示出和学号为、显示出和学号为“108”的同学同年出生的所有学生的的同学同年出生的所有学生的 sno、sname和和 birthday列。列。21、显示、显示“张旭张旭”老师任课的学生成绩。老师任课的学生成绩。22、显示选修某课程的同学人数多于、显示选修某课程的同学人数多于5人的老师姓名。人的老师姓名。23、显示“

6、95033”班和“95031”班全体学生的记录。24、显示存在有、显示存在有85分以上成绩的课程分以上成绩的课程cno。25、显示“计算机系”老师所教课程的成绩表。26、显示“计算机系”和“电子工程系”不同职称的老师的 tname和prof。27、显示选修编号为“3-105”课程且成绩至少高于“3-245”课程的同学的cno、sno和degree，并按degree从高到低次序排列。请写出下列查询语句并给出结果28、显示选修编号为“3-105”课程且成绩高于“3-245”课程的同 学的cno、sno和degree。29、列出所有任课老师的tname和depart。30、列出所有未讲课老师的、列出

7、所有未讲课老师的tname和和depart。31、列出所有老师和同学的、列出所有老师和同学的 姓名、性别和生日。姓名、性别和生日。*32、检索所学课程包含学生“103”所学课程的学生学号。*33、检索选修所有课程的学生姓名。1、列出student表中所有记录的sname、sex和class列。Select name,sex,class from studnt;2、显示教师所有的单位即不重复的depart列。Select distinct depart from teacher;3、显示学生表的所有记录。Select sno as 学号学号,name as 姓名姓名,sex as 性别性别,bi

8、rthday as 出生日期出生日期 from student;4、显示score表中成绩在60到80之间的所有记录。Select*from score where degree between 60 and 80;5、显示、显示score表中成绩为表中成绩为85，86或或88的记录。的记录。Select*from score where degree in(85,86,88);第三章作业部分答案6、显示student表中“95031”班或性别为“女”的同学记Select*from student where class=98031 or sex=女女;7、以class降序显示student表

9、的所有记录。Select*from student order by class desc;8、以、以cno升序、升序、degree降序显示降序显示score表的所有记录。表的所有记录。Select*from score order by cno,degree desc;9、显示“98031”班的学生人数。Select count(*)from student where class=95031;10、显示、显示score表中的最高分的学生学号和课程号。表中的最高分的学生学号和课程号。select sno,cno,degree as 最高分最高分 from score where degree

10、=(select max(degree)from score)SNO CNO 最高分最高分 103 3-105 92 11、显示、显示“3-105”号课程的平均分。号课程的平均分。Select avg(degree)as 课程平均分课程平均分 from score where cno=3-10512、显示、显示score表中至少有表中至少有5名学生选修的并以名学生选修的并以3开头的课程号的平开头的课程号的平均分数。均分数。Select cno,avg(degree)from score where cno like 3%Group by cno having count(*)=5;13、显示最

11、低分大于70，最高分小于90 的sno列。Select sno from score group by sno Having min(degree)70 and max(degree)select cno,avg(degree)from score 2 where sno in(select sno from student where 3 class=95033)group by cno;CNO AVG(DEGREE)-3-105 77.6666667 6-166 81.6666667 16另解 select avg(degree)from score where sno in(select

12、 sno from student where class=95033)group by cno 17、显示选修、显示选修“3-105”课程的成绩高于课程的成绩高于“109”号同学成绩的所有号同学成绩的所有同学的记录。同学的记录。17.select o,x.sno,x.degree from score x,score y where o=3-105 and x.degreey.degree and y.sno=109 and o=3-105;17(另解）select sno,cno,degree from score where cno=3-105 and degree(select deg

13、ree from score where cno=3-105 and sno=109)18、显示、显示score中选修多门课程的同学中分数为非最高分成绩中选修多门课程的同学中分数为非最高分成绩的记录。的记录。Select a.sno,a.degree,o from score a,score b Where a.sno=b.sno and a.degreey.degree and y.sno=109 and o=3-105;20、显示出和学号为、显示出和学号为“108”的同学同年出生的所有学生的的同学同年出生的所有学生的 sno、sname和和 birthday列。列。Select sno,s

14、name,birthday from student Where to_char(birthday,yy)=(select to_char(birthday,yy)from student where sno=108)21、显示、显示“张旭张旭”老师任课的学生成绩。老师任课的学生成绩。Select cno,sno,degree from score Where cno=(select o from course x,teacher y where x.tno=y.tno and y.tname=张旭张旭）22、显示选修某课程的同学人数多于、显示选修某课程的同学人数多于5人的老师姓名。人的老师姓

15、名。Select tname from teacher Where tno in(select x.tno from course x,score y where o=o group by x.tno having count(x.tno)5)子查询临时表的一部分内容子查询临时表的一部分内容 3-105 计算机导论 825 105 3-105 88 3-105 计算机导论 825 109 3-105 76 3-105 计算机导论 825 101 3-105 64 3-105 计算机导论 825 107 3-105 91 3-105 计算机导论 825 108 3-105 7824、显示存在有、

16、显示存在有85分以上成绩的课程分以上成绩的课程cno。select distinct cno from score where degree in (select degree from score where degree85)或者或者 select cno from score where degree85 group by cno;或者或者 select cno from score group by cno having max(degree)85;23、显示“95033”班和“95031”班全体学生的记录。Select*from student where class in(9503

17、3,95031);25、显示“计算机系”老师所教课程的成绩表。Select cno,sno,degree from score Where cno IN (select o from course x,teacher y where y.tno=x.tno and y.depart=计算机系计算机系）26、显示“计算机系”和“电子工程系”不同职称的老师的 tname和prof。Select tname,prof from teacher where depart=计算机系计算机系 and prof NOT IN (select prof from teacher where depart=电子

18、工程系电子工程系）27、显示选修编号为“3-105”课程且成绩至少高于“3-245”课程的同学的cno、sno和degree，并按degree从高到低次序排列。Select cno sno,degree from score Where cno=3-105 and degreeany (select degree from score where cno=3-245)Order by degree desc;28、显示选修编号为“3-105”课程且成绩高于“3-245”课程的同学的cno、sno和degree。Select cno sno,degree from score Where cno

19、=3-105 and degreeALL (select degree from score where cno=3-245)29、列出所有任课老师的tname和depart。Select tname,depart from teacher a where EXIST (select*from course b where a.tno=b.tno)或者或者select tname,depart from teacher where tno in (select tno from course);30、列出所有未讲课老师的、列出所有未讲课老师的tname和和depart。Select name,

20、depart from teacher a where NOT EXIST (select*from course b where a.tno=b.tno)31、列出所有老师和同学的、列出所有老师和同学的 姓名、性别姓名、性别和生日。和生日。select name,sex,birthday from teacher union select name,sex,birthday from student*32、检索所学课程包含学生“103”所学课程的学生学号。Select distinct sno from score x Where not exists (select*from score

21、y where y.sno=103 and not exists (select*from score z where z.sno=x.sno and o=o)105 3-105 75105 3-225 68 103 3-245 86103 3-105 92105 3-105 75105 3-225 68 103 3-245 86103 3-105 92105 3-105 75 105 3-225 68 103 3-245 86103 3-105 92XYZ105 3-245 75 105 3-245 75105 3-245 75*33、检索选修所有课程的学生姓名。(与例题4.23类似,可用s

22、core)Select sname from student where NOT EXISTS(SELECT*FROM course WHERE NOT EXISTS (SELECT*FROM score WHERE sno=student.sno AND cno=o)to_char(d,format);to_char(sysdate,yy);to_char(sysdate,mm);to_char(sysdate,dd);create table test(d1 date);insert into test values(19-10月月-89);select*from test where(t

23、o_char(d1,yy)=89)19-10月月-89 D1Select empno,initcap(ename),sal+nvl(comm,0)as totalsal From emp Order by totalsal;EMPNO INITCAP(EN TOTALSAL 7369 Smith 800 7900 James 950 7876 Adams 1100 7934 Miller 1300 7844 Turner 1500 7521 Ward 1750 7499 Allen 1900 7782 Clark 2450.Select user from dual;Select sysdat

24、e from dual;decode 函数介绍函数介绍 语法：语法：DECODE(expression,value1,returned_value1,.,valueN,returned_valueN defaultreturned_value)含义：含义：当当expression取值为取值为 valueN时，时，DECODE函数的返回值为函数的返回值为 returned_valueN defaultreturned_value是可选项，当是可选项，当expression不等于不等于 Value1至至valueN的值时，的值时，DECODE函数的返回值为函数的返回值为defaultreturne

25、d_value。select sum(sal)from emp group by decode(&xx,1,job,2,deptno)输入 xx 的值:2 原值 2:group by decode(&xx,1,job,2,deptno)新值 2:group by decode(2,1,job,2,deptno)SUM(SAL)-8750 10875 9400例例1：Select cno,cname,tno,decode(tno,825,王萍,804,李诚,856,李旭,）From course;cno cname tno DEC3-105 计算机导论 825 王萍3-245 操作系统 804

26、李诚6-166 数字电路 856 李旭9-888 高等数学 825 王萍例例2下面是否有 错？(1).create table yy(hh number(3)as select sno from score;(2)22.select tname from teacher where tno in (select tno from course where 55)讨论12题 一个同学的解法：select avg(degree)from score where 5=(select count(sno)from score where cno=like 3%);讨论22题 一个同学的解法：selec

27、t tname from teacher Where tno=any(select tno from course where cno=any(select cno from score where 5 any(select count(*)from score group by cno)讨论10 题 一个同学的解法：select sno from score where degree=max(degree)13题另解：Create view test(sno,maxdegree,mindegree)as select sno,max(degree),min(degree)from score

28、 group by sno;Select sno from test where maxdegree70;SNO-105 108下面语句实现什么功能？下面语句实现什么功能？SQL select avg(degree)from score group by cno order by avg(degree);AVG(DEGREE)-76.3333333 81.5 81.666666710.select sno as 学号学号,cno as 课程号课程号,degree as 最高分最高分 from score where degree=(select max(degree)from score)学号学号 课程号课程号 最高分最高分-103 3-105 92