DBS-lab2.doc

1、1生命科学 学院 数据库系统概论 实验报告年级 2006 级 学号 2006441005 姓名 刘威 成绩 专业 生物信息 实验地点 生科院-409 指导教师 孙娟 实验项目 SQL 语言的基本操作 实验日期 2008.10.30 一、实验目的 掌握利用 SQL 语句完成各种 查询操作的能力。二、实验内容 给定表结构如下：学生表：student（主键 Sno）学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept95001 李勇 男 20 CS 95002 刘晨 女 21 IS 95003 王敏 女 18 MA 95004 张力 男 19 IS 课程表：Course（主键 Cno）课

2、程号Cno课程名Cname先行课Cpno学分Ccredit1 数据库 5 4 2 数学 2 3 信息系统 1 4 4 操作系统 6 3 5 数据结构 7 4 6 数据处理 2 7 PASCAL语言 6 4 选课表：SC（主键 Sno,Cno，外部 键 Sno，Cno）学号Sno课程表Cno成绩Grade95001 1 9295001 2 8595001 3 8895002 2 90295002 3 8595003 3 59用 SQL 语句完成以下的要求四、试验结果1.查询信息系（IS）的所有学生信息select *from studentwhere sdept=IS;2.查询选修了“数学”课的

3、所有学生名单select snamefrom student,sc,coursewhere o=o and sc.sno=student.sno and ame=数学3.查询至少选修了一门其直接先行课为 5 号课程的学生的姓名。select snamefrom student,sc,coursewhere o=o and sc.sno=student.sno and course.cpno=54.查询全体学生的姓名和出生年份。select sname,Year of Birth BIRTH,2008-sage BIRTHDAYfrom student;5.查询所有姓王的学生。select *f

4、rom studentwhere sname like 王%;6.查询选修了 3 号课程的学生姓名及成绩，并按成绩降序排序。select sname,gradefrom student,scwhere sc.sno=student.sno and o=3order by grade desc; /可有可无，缺省为升序7.查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。select *from studentorder by sdept,sage desc;38.计算 2 号课程的平均成绩。select avg(grade) AVGfrom scwhere cno

5、=2;9.查询选修了 2 号课程的学生的最高成绩。select max(grade) MAXfrom scwhere cno=2;10.求各个课程号及相应的选课人数。select o,count(sno)from course left join sc /外连接on o=ogroup by o;11.查询至少选修了 3 门课程以上的学生学号。select snofrom scgroup by snohaving count(*)=3;12.查询“数据库”的间接先行课。select amefrom course a,course bwhere a.cpno=o and ame=数据库;13.查询

6、平均成绩最高的学生的学号和姓名。select sno,snamefrom student where sno in(select snofrom scgroup by snohaving avg(grade)=all(select avg(grade)from scgroup by sno)14.查询数学成绩最高的学生的学号和姓名。select student.sname,student.sno4from student,scwhere sc.sno=student.sno and grade in(select max(grade)from sc,coursewhere o=o and am

7、e=数学)15.查询出成绩最低学号最大的学生学号。select snofrom scwhere grade in(select min(grade)from sc) and sno in(select max(sno)from sc)16.查询成绩高于学生平均成绩的记录。select *from sc xwhere grade (select avg(grade)from sc ywhere y.sno=x.sno)17.查询至少选修了 1 号课程和 3 号课程的学生学号。select x.snofrom sc x,sc ywhere o=1and o=3 and x.sno=y.sno18.

8、查询只选修了 1 号课程和 3 号课程的学生学号。select snofrom scwhere cno=1 and sno in(select snofrom scwhere cno=3) and sno in(select snofrom scgroup by snohaving count(*)=25)19.查询没有选修 1 号课程的学生姓名。select snamefrom studentwhere not exists(select *from scwhere sno=student.sno and cno=1)20.查询选修了全部课程的学生姓名。select snamefrom st

9、udentwhere not exists(select *from coursewhere not exists(select * from scwhere sno=student.sno and cno=o)21.查询至少选修了 95002 所选修的全部课程的学生学号。select distinct snofrom sc xwhere not exists(select *from sc ywhere y.sno=95002 andnot exists (select *from sc zwhere z.sno=x.sno and o=o)22.查询没有不及格课程的学生的学号和姓名。sel

10、ect distinct student.sno,snamefrom studentwhere exists 6(select *from scwhere grade60and student.sno=sc.sno)23.查询没有不及格学生的课程的课程号和课程名。select distinct o,cnamefrom course,scwhere o=o and o not in(select cnofrom scwhere grade60)24.建立信息系学生视图，并从视图中查询年龄最大的学生记录。 建立视图：create view IS_studentas select *from studentwhere sdept=IS查询：select *from IS_studentwhere sage=(select max(sage)from IS_student)