DBS-lab2.doc

生命科学 学院 数据库系统概论 实验报告年级 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）课程号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成绩Grade950011929500128595001388950022909500238595003359用SQL语句完成以下的要求四、试验结果1.查询信息系（IS）的所有学生信息select *from studentwhere sdept=IS;2.查询选修了“数学”课的所有学生名单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 *from 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;8.计算2号课程的平均成绩。select avg(grade) AVGfrom scwhere cno=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.查询平均成绩最高的学生的学号和姓名。select sno,snamefrom student where sno in(select sno from sc group by sno having avg(grade)=all (select avg(grade) from sc group by sno )14.查询数学成绩最高的学生的学号和姓名。select student.sname,student.snofrom student,scwhere sc.sno=student.sno and grade in(select max(grade)from sc,coursewhere o=o and ame=数学)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 y where y.sno=x.sno)17.查询至少选修了1号课程和3号课程的学生学号。select x.snofrom sc x,sc ywhere o=1and o=3 and x.sno=y.sno18.查询只选修了1号课程和3号课程的学生学号。select snofrom scwhere cno=1 and sno in(select sno from sc where cno=3) and sno in(select sno from sc group by sno having count(*)=2)19.查询没有选修1号课程的学生姓名。select snamefrom studentwhere not exists(select * from scwhere sno=student.sno and cno=1)20.查询选修了全部课程的学生姓名。select snamefrom studentwhere not exists(select * from coursewhere not exists (select * from sc where sno=student.sno and cno=o )21.查询至少选修了95002所选修的全部课程的学生学号。select distinct snofrom sc xwhere not exists(select * from sc y where y.sno=95002 and not exists (select * from sc zwhere z.sno=x.sno and o=o)22.查询没有不及格课程的学生的学号和姓名。select distinct student.sno,snamefrom studentwhere exists (select * from sc where grade60and student.sno=sc.sno)23.查询没有不及格学生的课程的课程号和课程名。select distinct o,cnamefrom course,scwhere o=o and o not in(select cno from sc where grade60)24.建立