北邮数据库第四次实验.doc

数据库第四次实验报告学 院： 班 级： 姓 名： 学 号： 实验四 数据库的查询实验（两次内容）1. 简单查询:（第一部分的实验）实验要求：(1) 查询班号为g00401班的学生的学号和姓名；(2) 查询“数据库开发技术”课程的学分；(3) 查询选修了课程编号为“dep04_s003”的学生的学号和成绩，并将成绩按降序输出；(4) 查询学号为“g9940205”的学生选修的课程编号和成绩；(5) 查询选修了课程编号为“dep04_s001”且成绩高于85分的学生的学号和成绩。实验步骤：（下面用代码、截图以及注释来体现）(1) 查询班号为g00401班的学生的学号和姓名；代码为：select student_id,student_namefrom student where class_id=g00401; 截图为：因为 student 表里并没有class_id=g00401这一项，所以查询结果为空，没有显示出该元组的值。(2) 查询“数据库开发技术”课程的学分；代码为： select creditfrom course where course_name=SQL Server数据库开发技术;截图为：可以看到结果查询结果是正确的。（3）查询选修了课程编号为“dep04_s003”的学生的学号和成绩，并将成绩按降序输出；代码为：select student_id,gradefrom student_course where course_id=dep04_s003order by grade desc截图为：可以看到总共输出了7个符合条件的结果，分数按降序排列，实验完成。(4) 查询学号为“g9940205”的学生选修的课程编号和成绩；代码为：select course_id,gradefrom student_course where student_id=g9940205;截图为：结果正确(5) 查询选修了课程编号为“dep04_s001”且成绩高于85分的学生的学号和成绩。代码为：select student_id,gradefrom student_course where course_id=dep04_s001 and grade85;截图为：由下图可以看出，查询成功。2.在多表连接的查询实验中，用Transact SQL语句完成以下查询操作：（第二部分的实验）实验要求：(1)查询选修了课程编号为“dep04_s002”且成绩高于85分的学生的学号、姓名和成绩；(2)查询所有学生的学号、姓名、选修的课程名称和成绩；(3)查询计算机科学系林红同学选修的课程名称、学分和成绩。(考试成绩60有学分，否则无学分。)实验步骤：（下面用代码、截图以及注释来体现）(1)查询选修了课程编号为“dep04_s002”且成绩高于85分的学生的学号、姓名和成绩；代码为：select student.student_id,student_name,gradefrom student_course ,student where student.student_id=student_course.student_id and course_id=dep04_s002 and grade85;截图为：(2)查询所有学生的学号、姓名、选修的课程名称和成绩；代码为：select student.student_id,student_name,course_name,gradefrom student_course ,student ,coursewhere student.student_id=student_course.student_id and course.course_id=student_course.course_id;截图为：(3) 查询计算机科学系林红同学选修的课程名称、学分和成绩。(考试成绩60有学分，否则无学分。)代码为：use studentsdb;select course_name,credit,gradefrom student,class,department,student_course,coursewhere student.class_id=class.class_id and class.department_id=department.department_id andstudent.student_id=student_course.student_id and student_course.course_id=course.course_iddepartment_name=计算机科学 and student_name=林红 and grade=60;截图为：3. 在复杂查询实验中，用Transact SQL语句完成以下查询操作：(1)查询至少选修了三门课程的学生的学号和姓名；代码为：use studentsdb;select student.student_id,student_namefrom student ,student_coursewhere student.student_id=student_course.student_id group by student.student_id having count(*)3; 截图为： (2)查询选修课程号为“dep04_b001”的学生的平均成绩；代码为：use studentsdb;select avg(grade)from student_coursewhere course_id=dep04_b001;截图为：(3)查询所有学生的学号和他选修课程的最高成绩，要求他的选修课程中没有成绩为空的。代码为：use studentsdb;select student_id,max(grade)from student_coursewhere grade is not nullgroup by student_id ;截图为：(4)查询严为老师2001/2002学年教的软件开发技术课程的最高成绩及此学生的学号、姓名、班级。代码为： use studentsdb; select student.student_id,student_name,student.class_id,max(grade) from student,student_course,teacher,teacher_course_class,course where student.student_id=student_course.student_id and teacher.teacher_id=teacher_course_class.teacher_id and teacher_course_class.course_id=course.course_id and student_course.course_id=teacher_course_class.course_id and teacher_name=严为 and student_course.school_year=2001/2002 and course_name=软件开发技术 and student_name=林红group by student.student_id,student_name,student.class_id;截图为：(5) 查询数据库开发技术课程用过的教材名称，作者和出版社。代码为：use studentsdb;select book_name,author,publish_companyfrom book ,coursewhere book.book_id=course.book_id and course_name=SQL Server数据库开 发;执行结果如下：(6)查询计算机科学系讲授过数据库开发技术的老师姓名和职称。截图为：use studentsdb;select teacher_name, professionfrom teacher,teacher_course_class,course,departmentwhere teacher.teacher_id=teacher_course_class.teacher_id and teacher_course_class.course_id=course.course_id and department.department_id=teacher.department_id and department_name=计算机科学 and course_name=SQL Server数据库开发;执行结果如下：4.在嵌套查询实验中，用Transact SQL语句完成以下查询操作，要求写嵌套查询语句：(1)查询选修了电磁波工程的学生的学号和姓名； 代码为： use studentsdb; select student_id ,student_name from student where student_id in(select student_id from student_course where course_id in(select course_id from course where course_name=电磁波工程);截图为：(2)查询没有选修电磁波工程的学生的学号和姓名；代码为： use studentsdb; select student_id ,student_name from student where student_id not in(select student_id from student_course where course_id in(select course_id from course where course_name=电磁波工程);截图为： 5.建立如下视图：学生选修课程信息视图，包括以下内容：1） 学生学号、姓名、所在系、授课老师姓名、课程名称、课程教材名称、出版社、学分、选课成绩 代码为： create view 学生选修课程信息视图(学生学号,姓名,所在系,授课老师姓名,课程名称,课程教材名称,出版社,学分,选课成绩) as select student.student_id,student_name,department.department_id,teacher_name,course_name,book_name,publish_company,course.credit,gradefrom student,department,teacher,teacher_course_class,student_course,class,course,bookwhere student.class_id=class.class_id and class.department_id=department.department_id and student.class_id=teacher_course_class.class_id and teacher_course_class.teacher_id =teacher.teacher_id and student.student_id=student_course.student_id and student_course.course_id=course.course_id and course.book_id=book.book_id ;截图为： 如果是使用企业管理器来进行视图的创建，只要右键点击视图，选择新 建视图，并按照要求创建即可。2）修改以上视图，增加学生所在班级信息。对以上内容用企业管理器和SQL语句方式分别完成。 代码为： ALTER view dbo.学生选修课程信息视图(学生学号,姓名,所在系,授课老师姓名,课程名称,课程教材名称,出版社,学分,选课成绩,学生班级)as select student.student_id,student_name,department.department_id,teacher_name,course_name,book_name,publish_company,course.credit,grade,student.class_idfrom student,department,teacher,teacher_course_class,student_course,class,course,bookwhere student.class_id=class.class_id and class.department_id=department.department_id and student.class_id=teacher_course_class.class_id and teacher_course_class.teacher_id =teacher.teacher_id and student.student_id=student_course.student_id and student_course.course_id=course.course_id and course.book_id=book.book_id ; 截图为： 使用企业管理器的修改方法：右键单击学生选课课程信息视图，选择设计，在 添加上class_id这一列即可3）对以上视图进行