北郵數(shù)據(jù)庫實驗報告

1、數(shù)據(jù)庫試驗報告（四）1.簡潔查詢：姓名：學號：班級 : 1 查詢“ 數(shù)據(jù)庫開發(fā)技術” 課程的學分；SQL語句：select credit from course where course_name =SQL Server數(shù)據(jù)庫開發(fā)技術 ; 或者模糊查詢：select credit from course where course_name like%數(shù)據(jù)庫開發(fā)技術 ; 執(zhí)行結果：2 查詢選修了課程編號為“按降序輸出；SQL語句：select student_id , grade from student_course where course_id =dep04_s003order by gr

2、ade desc; 執(zhí)行結果：dep04_s004” 的同學的學號和成果,并將成果3 查詢學號為“g9940205” 的同學選修的課程編號和成果；SQL語句：select course_id , grade from student_course where student_id =g9940205 ; 執(zhí)行結果：4 查詢選修了課程編號為“dep04_s001” 且成果高于 85 分的同學的學號和成果；SQL語句：select student_id , grade from student_course where course_id=dep04_s001and grade 85 ; 執(zhí)行結果

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 ; 執(zhí)行結果：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 ; 執(zhí)行結果：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; 執(zhí)行結果：2 查詢選修課程號為“SQL語句：select avg grade from student_course dep04_b001” 的同學的平均成果；where c

7、ourse_id =dep04_b001 ; 執(zhí)行結果：3 查詢全部同學的學號和他選修課程的最高成果,要求他的選修課程中沒有成果為空的；SQL語句：select student_id , max grade from student_course where exists select grade from student_course group by student_id ; 執(zhí)行結果：4 查詢嚴為老師 2022/2022 學年教的軟件開發(fā)技術課程的最高成果及此學生的學號、姓名、班級；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 = 軟件開發(fā)技術 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 = 軟件開發(fā)技術 ; 執(zhí)行結果：5 查詢數(shù)據(jù)庫開發(fā)技術課程用過的教材名稱,作者和出版社；SQL語句：select book_name , author , publish_company from book , course where course . book_id =book. book_id and course_name =SQL SERVER數(shù)據(jù)庫開發(fā)技術 ; 執(zhí)行結果：6 查詢運算機科學系講授過數(shù)據(jù)庫開發(fā)技術的老師姓名和職稱；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 數(shù)據(jù)庫開發(fā)技術 ; 執(zhí)行結果：4. 在嵌套

12、查詢試驗中, 用 Transact SQL語句完成以下查詢操作, 要求寫嵌套查詢語句：1 查詢選修了軟件開發(fā)技術的同學的學號和姓名；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 = 軟件開發(fā)技術 ; 執(zhí)行結果：2 查詢沒有選修軟件開發(fā)技術的同學的學號和姓名；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 = 軟件開發(fā)技術 ; 執(zhí)行結果：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 ; 執(zhí)行結果：5. 建立如下視圖：同學選修課程信息視圖,包括以下內容：對（1）（2）內容用企業(yè)治理器和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企業(yè)治理器：. course_id =course . course_id Step1:右鍵視圖,挑選新建視圖；Step2:添加涉及到的表；Step3:挑選需要顯示的列；Step4:右鍵視圖 view1,挑

18、選查看前 1000行；執(zhí)行結果：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