《数据库系统原理》

三、数据库操作题（40分）

设数据库中有以下基本表:

学生基本信息表:student(sno,sname,sgender,sbirth,sdept) 课程信息表:course(cno,cname,credit) 学生选课关系表:SC(sno,cno,grade)

系信息表:dept(sdept,sdeptName,sdeptAddress)

其中各属性表示：sno:学号，sname:学生姓名，sgender:性别, sbirth:出身日期,sdept学生所在的系，cno:课程号，cname：课程名称,credit课程的学分；grade：考试成绩；sdeptName：系名称；sdeptAddress：系地址。（1-10题每题3分，11-12题每题5分）

1、查询出身日期在1990-1-1至1990-12-31日之间的学生信息。 select * from student where sbirth between ‘1990-1-1’ and ‘1990-12-31’

2、查询所有姓“王”的学生信息。

Select * from student where sname like ‘王%’

3、查询计算机系(cs)及信息系(info)所有学生信息，要求按学生姓名降序排序。

Select * from student where sdept in (‘cs’,’info’) order by sname desc.

4、删除课程信息表中课程号为1的课程（考虑外键约束条件）。 Delete sc where cno=’1’

Delete course where cno = ‘1’

5、在系信息表中插入一条记录，sdept:Art，sdepatName:艺术系，sdeptAddress:机电大楼。

Insert into sdept values(‘Art’,’ 艺术系’,’ 机电大楼’)

6、根据学生出生日期查询学生的年龄。

select sno,sname,datediff(yy,sbirth,getdate()) as sage from t_student

7、关联学生基本信息表及系信息表查询计算机系的所有学生信息，要求列出系名称，学号，学生名、出生日期。 Select

sdept.sdeptName,student.sno,student.sname,student.sbirth from student,sdept

where student.sdept = sdept.sdept and sdept.sdeptName = ‘计算机系’

8、查询课程的平均成绩在75分以上的学生。 Select sno,avg(grade) as ‘平均成绩’ from sc Group by sno

Having avg(grade) > 75

9、找出和学号为 2002151121 同在一个系学习的所有学生。 Select * from student where sdept = (select sdept from student where sno=’ 2002151121’)

10、关联学生基本信息表、课程信息表、学生选课关系表建立视图,视图名为v_scDetail。视图包括：学号、学生姓名、课程名称、考试成绩信息。并在视图的基础上查询选修了课程名称为 数据库原理 课程的学生。

Create view v_scDetail As

Select student.sno,student.sname,http://wendang.chazidian.comame,sc.grade From student,course,sc

Where student.sno = sc.sno and http://wendang.chazidian.como = http://wendang.chazidian.como

Select * from v_scDetail where cname = ‘数据库原理’

11、列出女生的人数大于20人的系及女生人数，并按女生人数从高

到低排序。

select sdeptname,count(sgender) as GNums from student t join sdept d on t.sdept = d.sdept where t.sgender = '女' group by sdeptname,sgender having count(sgender) >= 20 order by count(sgender) desc

12、统计每门课程的总分、平均分、最高分、最低分(注：列出所有课程，包括没有学生选择的课程，其总分、平均分、最高分、最低分用NULL表示)。

Create view as v_Stat As

Select cno,sum(grade) as GTotal,avg(grade) as GAvg,Max(grade) as GMax,min(grade) as GMin from t_sc group by cno select http://wendang.chazidian.comame, v_Stat.GTotal, v_Stat.GAvg, v_Stat.GMax, v_Stat.GMin

from course left join v_Stat on http://wendang.chazidian.como = v_http://wendang.chazidian.como