北郵數(shù)據(jù)庫(kù)實(shí)驗(yàn)報(bào)告

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

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

3、：2.在多表連接的查詢?cè)囼?yàn)中,用Transact SQL 語(yǔ)句完成以下查詢操作：1 查詢選修了課程編號(hào)為“dep04_s002” 且成果高于85 分的同學(xué)的學(xué)號(hào)、姓名和成果；SQL語(yǔ)句：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í)行結(jié)果：2 查詢?nèi)客瑢W(xué)的學(xué)號(hào)、姓名、選修的課程名稱和成果；SQL語(yǔ)句：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í)行結(jié)果：3 查詢林紅同學(xué)選修的課程名稱、 學(xué)分和成果； 考試成果 60 有學(xué)分,否就無(wú)學(xué)分；

5、 SQL語(yǔ)句：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.在復(fù)雜查詢?cè)囼?yàn)中,用Transact SQL 語(yǔ)句完成以下查詢操作：1 查詢至少選修了三門(mén)課程的同學(xué)的學(xué)號(hào)和姓名；SQL語(yǔ)句：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í)行結(jié)果：2 查詢選修課程號(hào)為“SQL語(yǔ)句：select avg grade from student_course dep04_b001” 的同學(xué)的平均成果；where c

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

12、查詢?cè)囼?yàn)中, 用 Transact SQL語(yǔ)句完成以下查詢操作, 要求寫(xiě)嵌套查詢語(yǔ)句：1 查詢選修了軟件開(kāi)發(fā)技術(shù)的同學(xué)的學(xué)號(hào)和姓名；SQL語(yǔ)句：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 = 軟件開(kāi)發(fā)技術(shù) ; 執(zhí)行結(jié)果：2 查詢沒(méi)有選修軟件開(kāi)發(fā)技術(shù)的同學(xué)的學(xué)號(hào)和姓名；SQL語(yǔ)句：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 = 軟件開(kāi)發(fā)技術(shù) ; 執(zhí)行結(jié)果：3 查詢至少選修了學(xué)號(hào)為 “ g9940201” 的同學(xué)所選修的全部課程的同學(xué)的學(xué)號(hào)和姓名；SQL語(yǔ)句：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í)行結(jié)果：5. 建立如下視圖：同學(xué)選修課程信息視圖,包括以下內(nèi)容：對(duì)（1）（2）內(nèi)容用企業(yè)治理器和S

15、QL語(yǔ)句方式分別完成；1）同學(xué)學(xué)號(hào)、姓名、所在系、授課老師姓名、課程名稱、課程教材名稱、出版社、學(xué)分、選課成果SQL語(yǔ)句：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í)行結(jié)果：2）修改以上視圖,增加同學(xué)所在班級(jí)信息；SQL語(yǔ)句：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