数据库实验答案分总结.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 second.