2014数据库实验答案分析总结.docVIP

实验三 用SQL语句完成以下的要求（键表及插入数据的SQL语句见）：create table student( Sno char(5) primary key, Sname char(10), Ssex char(2), Sage int, Sdept char(2)); create table Course( Cno char(1) primary key, Cname char(20), Cpno char(1), Ccredit int); create table SC( Sno char(5), Cno char(1), Grade int, primary key (sno,cno)); insert into student values(95001,李勇,男,20,CS); insert into student values(95002,刘晨,女,21,IS); insert into student values(95003,王敏,女,18,MA); insert into student values(95004,张力,男,19,IS); insert into Course values(1,数据库,5,4); insert into Course values(2,数学,NULL,2); insert into Course values(3,信息系统,1,4); insert into Course values(4,操作系统,6,3); insert into Course values(5,数据结构,7,4); insert into Course values(6,数据处理,NULL,2); insert into Course values(7,PASCAL语言,6,4); insert into SC values(95001,1,92); insert into SC values(95001,2,85); insert into SC values(95001,3,88); insert into SC values(95002,2,90); insert into SC values(95003,3,85); 1.查询信息系（IS）的所有学生信息select * from Student where Sdept=IS; 2.查询选修了“数学”课的所有学生名单select * from Student,Course,SC where Student.Sno=SC.Sno And Course.Cno=SC.Cno And Course.Cname=数学3.查询至少选修了一门其直接先行课为5号课程的学生的姓名。select Student.Sname from Student,Course,SC where Student.Sno=SC.Sno And Course.Cno=SC.Cno And Course.Cpno=5 4.查询全体学生的姓名和出生年份。select sname,2013-Student.Sage from Student 5.查询所有姓王的学生。select * from Student where Sname like 王%; 6.查询选修了3号课程的学生姓名及成绩，并按成绩降序排序。select Student.Sname,SC.Grade from Student,Course,SC where Student.Sno=SC.Sno and Course.Cno=Sc.cno and course.cno=3 order by sc.grade desc 7.查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。select * from student order by sdept, sage desc 8.计算2号课程的平均成绩。select AVG(grade) from SC where cno=2; 9.查询选修了2号课程的学生的最高成绩。select MAX(grade) from SC where cno=2; 10.求各个课程号及相应的选课人数。select cno,COUNT(distinct sno) from SC group by cno 11.查询至少选修了3门课程以上的学生序号。select sno from SC group by sno having COUNT(*)=3; 12.查询“数据库”的间接先行课。select second.cpno from Course as first,Course as second where first.cpno