北邮数据库实验报告

1、数据库试验报告（四）1.简洁查询：姓名：学号：班级 : 1 查询“ 数据库开发技术” 课程的学分；SQL语句：select credit from course where course_name =SQL Server数据库开发技术 ; 或者模糊查询：select credit from course where course_name like%数据库开发技术 ; 执行结果：2 查询选修了课程编号为“按降序输出；SQL语句：select student_id , grade from student_course where course_id =dep04_s003order by gr

2、ade desc; 执行结果：dep04_s004” 的同学的学号和成果,并将成果3 查询学号为“g9940205” 的同学选修的课程编号和成果；SQL语句：select course_id , grade from student_course where student_id =g9940205 ; 执行结果：4 查询选修了课程编号为“dep04_s001” 且成果高于 85 分的同学的学号和成果；SQL语句：select student_id , grade from student_course where course_id=dep04_s001and grade 85 ; 执行结果

3、：2.在多表连接的查询试验中,用Transact SQL 语句完成以下查询操作：1 查询选修了课程编号为“dep04_s002” 且成果高于85 分的同学的学号、姓名和成果；SQL语句：select student . student_id , student_name , grade from student , student_course where student . student_id =student_course . student_id and student_course . course_id =dep04_s002and student_course . grade 8

4、5 ; 执行结果：2 查询全部同学的学号、姓名、选修的课程名称和成果；SQL语句：select student . student_id , student_name , course_name, grade from student , course , student_course where student . student_id =student_course . student_id and student_course . course_id =course . course_id ; 执行结果：3 查询林红同学选修的课程名称、 学分和成果； 考试成果 60 有学分,否就无学分；

5、 SQL语句：select course_name , student_course . credit , grade from student , student_course , course where student_name = 林红 and student . student_id =student_course . student_id and student_course . course_id =course . course_id ;3.在复杂查询试验中,用Transact SQL 语句完成以下查询操作：1 查询至少选修了三门课程的同学的学号和姓名；SQL语句：select

6、 student . student_id , student_name from student , student_course where student . student_id =student_course . student_id group by student . student_id , student_name having count student_course . course_id = 3; 执行结果：2 查询选修课程号为“SQL语句：select avg grade from student_course dep04_b001” 的同学的平均成果；where c

7、ourse_id =dep04_b001 ; 执行结果：3 查询全部同学的学号和他选修课程的最高成果,要求他的选修课程中没有成果为空的；SQL语句：select student_id , max grade from student_course where exists select grade from student_course group by student_id ; 执行结果：4 查询严为老师 2022/2022 学年教的软件开发技术课程的最高成果及此学生的学号、姓名、班级；SQL语句：select student . student_id , student_name , st

8、udent . class_id , grade from teacher_course_class , teacher , course , student , student_course where teacher_course_class . teacher_id = teacher . teacher_id and teacher . teacher_name = 严为 and teacher_course_class . course_id = course . course_id and course . course_name = 软件开发技术 and teacher_cour

9、se_class . course_id = student_course . course_id and student_course . student_id = student . student_id and teacher_course_class . school_year = 2022/2022 and student_course. grade =allselect grade course . course_id andfrom student_course, course where student_course. course_id = course. course_na

10、me = 软件开发技术 ; 执行结果：5 查询数据库开发技术课程用过的教材名称,作者和出版社；SQL语句：select book_name , author , publish_company from book , course where course . book_id =book. book_id and course_name =SQL SERVER数据库开发技术 ; 执行结果：6 查询运算机科学系讲授过数据库开发技术的老师姓名和职称；SQL语句：select teacher_name , profession from teacher , course , teacher_cour

11、se_class , department where teacher . teacher_id = teacher_course_class . teacher_id and course . course_id = teacher_course_class . course_id and department . department_id = teacher . department_id and department . department_name = 运算机科学 and course . course_name = SQL Server 数据库开发技术 ; 执行结果：4. 在嵌套

12、查询试验中, 用 Transact SQL语句完成以下查询操作, 要求写嵌套查询语句：1 查询选修了软件开发技术的同学的学号和姓名；SQL语句：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 查询没有选修软件开发技术的同学的学号和姓名；SQL语句：select stude

13、nt_id , student_name from student where notexistsselect student_id from student_course where course_id in select course_id from course where course_name = 软件开发技术 ; 执行结果：3 查询至少选修了学号为 “ g9940201” 的同学所选修的全部课程的同学的学号和姓名；SQL语句：select student_id , student_name from student where not exists select *from stu

14、dent_course student_course1 where student_course1. student_id = g9940201and notexists select*from student_course student_course2 where student . student_id =student_course2 . student_id and student_course2 . course_id = student_course1 . course_id ; 执行结果：5. 建立如下视图：同学选修课程信息视图,包括以下内容：对（1）（2）内容用企业治理器和S

15、QL语句方式分别完成；1）同学学号、姓名、所在系、授课老师姓名、课程名称、课程教材名称、出版社、学分、选课成果SQL语句：Create viewview1 student_id , student_name , department_name , teacher_name , course_name, book_name , publish_name , credit , grade as select distinctstudent . student_id , student . student_name , department_name , teacher_name ,course_n

16、ame, book_name, publish_company , student_course . credit , student_course . grade fromstudent , course , department , student_course, teacher , teacher_course_class, book, class where student . student_id =student_course . student_id and student . class_id =class . class_id and class . department_i

17、d =department . department_id and student_course. course_id =course . course_id and course . book_id =book. book_id and teacher. teacher_id=teacher_course_class. teacher_id and teacher_course_class企业治理器：. course_id =course . course_id Step1:右键视图,挑选新建视图；Step2:添加涉及到的表；Step3:挑选需要显示的列；Step4:右键视图 view1,挑

18、选查看前 1000行；执行结果：2）修改以上视图,增加同学所在班级信息；SQL语句：alter viewview1 student_id , student_name , department_name , teacher_name , course_name, book_name, publish_name , credit , grade , class_id AS SELECT DISTINCTstudent . student_id , student_name , department_name , teacher_name , course_name , book_name, publish_company , student_course . credit , student_course . grade , student . class_id FROMstudent , student_course, course , teacher , teacher_course_class, book, department , class WHERE student . student_id =student_course . student_id and student . class_id =class . cl