《数据库系统原理》.doc

三、数据库操作题（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_scDeta il。视图包括：学号、学生姓名、课程名称、考试成绩信息。并在视图的基础上查 询选修了课程名称为'数据库原理'课程的学生。 Create view v_scDetail As Select student.sno,student.sname,course.cname,sc.grade From student,course,sc Where student.sno = sc.sno and course.cno = sc.cno 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 GAv 《数据库系统原理》这篇文档涉及了数据库操作的基础知识，主要涵盖SQL语言的使用，包括查询、更新、插入和视图的创建。以下是这些操作的详细解释： 1. **日期范围查询**：`BETWEEN`操作符用于选取介于两个值之间的数据。在这里，它用于查询1990年出生的学生。 2. **字符串匹配查询**：`LIKE`操作符配合通配符 `%` 用于查询名字以特定字符开头的学生，例如所有姓“王”的学生。 3. **多条件查询与排序**：`IN`操作符用于选取满足多个条件之一的数据，`ORDER BY`用于对结果进行排序，本例中是按照学生姓名降序排列。 4. **级联删除**：在存在外键约束的情况下，需要先删除学生选课关系表中的记录，再删除课程信息表中的记录，以避免违反约束。 5. **插入记录**：`INSERT INTO`语句用于向表中插入新的记录，确保字段与值的对应。 6. **计算年龄**：`DATEDIFF`函数计算两个日期之间的差值，这里用于计算学生的年龄。 7. **连接查询**：通过`JOIN`操作将学生基本信息表与系信息表连接，筛选出计算机系的学生信息。 8. **分组与聚合函数**：`GROUP BY`用于将数据按某个字段分组，`AVG`函数计算每个组的平均值，`HAVING`过滤满足特定条件的组，这里查找平均成绩高于75分的学生。 9. **子查询**：嵌套`SELECT`查询，找到与指定学生相同系的学生。 10. **创建视图**：视图是虚拟表，基于查询结果创建。`CREATE VIEW`语句定义了一个包含学生信息、课程信息和选课成绩的视图，并在视图上进一步查询选修特定课程的学生。 11. **计数与排序**：`COUNT`函数计算每个组的元素数量，`GROUP BY`与`HAVING`结合找出女生数量超过20的系，并按女生数量降序排列。 12. **统计分析**：创建一个视图`v_Stat`来统计每门课程的成绩统计，包括总分、平均分、最高分和最低分。即使某些课程无人选修，也会在结果中显示NULL值。 这些操作展示了数据库管理的基本操作，包括查询（SELECT）、更新（INSERT/DELETE）、排序（ORDER BY）、连接（JOIN）、分组（GROUP BY）、过滤（WHERE/HAVING）、聚合函数（AVG/COUNT）以及视图的创建和使用。这些技能对于理解和操作关系型数据库至关重要。