大數(shù)據(jù)庫(kù)SQL語句實(shí)驗(yàn)報(bào)告材料

1、數(shù)據(jù)庫(kù)原理及應(yīng)用實(shí)驗(yàn)報(bào)告SQL語句部分總成績(jī)：實(shí)驗(yàn)一 熟悉SQL SERVER，數(shù)據(jù)定義實(shí)驗(yàn)實(shí)驗(yàn)一成績(jī)：一、實(shí)驗(yàn)?zāi)康?、利用查詢分析器創(chuàng)建數(shù)據(jù)庫(kù)2、利用查詢分析器用 SQL語言定義基本表、索引并進(jìn)行相關(guān)操作二、實(shí)驗(yàn)步驟及容在以下實(shí)驗(yàn)中，使用學(xué)生-課程數(shù)據(jù)庫(kù)，它描述了學(xué)生的基本信息、 課程的基本信息及學(xué)生選修課程的信息。1 創(chuàng)建學(xué)生課程數(shù)據(jù)庫(kù)2 .創(chuàng)建學(xué)生關(guān)系表 S :學(xué)號(hào)性別年齡所在系SnoSn ameSsexSagesdept3 創(chuàng)建課程關(guān)系表 C :】田千口 i=r. 課程號(hào)課程名先行課學(xué)分CnoCn ameCpnoccredit4 創(chuàng)建課程關(guān)系表 SC :學(xué)號(hào)】田千口 i=r. 課程號(hào)成

2、績(jī)SnoCnograde5 .將以上創(chuàng)建表 S、C、SC的SQL命令以.SQL文件的形式保存在磁盤上。6、在表S上增加“出生日期”屬性列。7、 刪除表S的“年齡”屬性列。&刪除S列的唯一值約束。9、修改SC表成績(jī)屬性列為精確數(shù)字型。10、在表S上，按“ Sno”屬性列的唯一值方式建立索引。11、 刪除表SC,利用磁盤上保存的.SQL文件重新創(chuàng)建表 SC。12、 創(chuàng)建教材P74頁習(xí)題5中SPJ數(shù)據(jù)庫(kù)中的四個(gè)關(guān)系：S、P、J、SPJ。三、實(shí)驗(yàn)結(jié)果：1 創(chuàng)建學(xué)生課程數(shù)據(jù)庫(kù)create database Stude nd ;2 .創(chuàng)建學(xué)生關(guān)系表 S :學(xué)號(hào)性別年齡所在系SnoSn ameSse

3、xSagesdeptcreate table S(Sno CHAR( 9) PRIMARY KEY,Sname CHAR( 20) UNIQUE,Ssex CHAR(2),Sage smalli nt,Sdept char ( 20),);3 創(chuàng)建課程關(guān)系表 C :】田千口 i=r. 課程號(hào)課程名先行課學(xué)分CnoCn ameCpnoccreditcreate table C(Cno char (4) primary keyCname CHAR(40),Cpno char (4),Ccredit SMALLINT ,);4 創(chuàng)建課程關(guān)系表 SC :學(xué)號(hào)】田千口 i=r. 課程號(hào)成績(jī)SnoCnog

4、radecreate table SC(Sno char ( 9),Cno char ( 4),Grade smalli nt,)；5 .將以上創(chuàng)建表 S、C、SC的SQL命令以.SQL文件的形式保存在磁盤上。6、在表S上增加“出生日期”屬性列。El LJ Studendb) i 垂3 口談丟3 3 dbo.S41 _J dbo.Calter table S add Sbirthday datetimeGname czhar(2QS-sex (char© niSJ Sdept (char(2O)PLl Sbirthday (dartetSno (PK. chartsLfl-aJalt

5、er table S dropcolu mnSage7、刪除表S的“年齡” 屬性列。日口 Tbs呂曰二列£ Sno (PK.匸hmr 3 Snanw char(20' Esex (亡harm 國(guó) Sdept th 日E Sbirdnday date!r Sro (PK, char(9), not null_2 Snarne char(2QP null)&刪除S列的唯一值約束。alter table S drop UQ_S_7D78A4E73 Sse-x (char(2), null)| 5age I'smallint! null) i_3 Sdept char

6、(20)_ null) 曰匸讎右 PK_S_7C8490A£9、修改SC表成績(jī)屬性列為精確數(shù)字型。alter table SC alter colu mn Grade int3鉤束_J列國(guó) Sno (thar91 null)H Cno (rhar4X null0 Grade (int, null10、在表S上，按“ Sno”屬性列的唯一值方式建立索引。create uni quein dexStus noon S ( Sno );1= LJ索引ik PK_S_7匚&480E醱） 缶Stusno （臨一，勻嚥集）十4、I "-也11、刪除表SC,利用磁盤上保存的.SQ

7、L文件重新創(chuàng)建表 SC。drop table SC ;D Li垂嬌表 +j _ dbo.C 刃二 dbo.S口施表田dbo.CdboSdbo.SC12、創(chuàng)建教材P74頁習(xí)題5中SPJ數(shù)據(jù)庫(kù)中的四個(gè)關(guān)系：S、P、J、SPJ。實(shí)驗(yàn)二數(shù)據(jù)更新實(shí)驗(yàn)實(shí)驗(yàn)二成績(jī)：一、實(shí)驗(yàn)?zāi)康?、熟悉和掌握數(shù)據(jù)表中數(shù)據(jù)的插入、修改、刪除操作和命令的使用2、加深理解表的定義對(duì)數(shù)據(jù)更新的作用二、實(shí)驗(yàn)步驟及容（一）利用查詢分析器在表中插入數(shù)據(jù) 打開在實(shí)驗(yàn)一中建立的“學(xué)生課程”數(shù)據(jù)庫(kù)；在"SCDB”數(shù)據(jù)庫(kù)中的 student、course、sc表中插入數(shù)據(jù)； stude nt表的容：SNOSNAMESSEXSAGESDE

8、PT95001勇男20CS95002晨女19IS95003王敏女18MAcourse表的容CNOCNAMECPNOCREDIT1數(shù)據(jù)庫(kù)542數(shù)學(xué)NULL23信息系統(tǒng)144操作系統(tǒng)NULL35數(shù)據(jù)處理NULL2sc表的容SNOCNOGRADE9500119295001265950013889500229095002380（二）修改表中的數(shù)據(jù)在插入數(shù)據(jù)后的三表 S、C、SC中進(jìn)行相關(guān)的更新操作：1、將student表中勇的名字改為明；2、將student表中王敏的年齡改為 20歲；3、 將所有學(xué)生的年齡增加1歲；4、 修改學(xué)生的學(xué)號(hào)95002'為95005',注意表之間的關(guān)系，保持

9、一致性;5、 把選修了“ 2”號(hào)課程，且成績(jī)低于 70分的成績(jī)提高5% ；6、將“ CS”系全體學(xué)生的成績(jī)置零；（三）刪除表中的數(shù)據(jù)1、把選修了“ 2”號(hào)課程，且成績(jī)低于 70分的學(xué)生的成績(jī)刪除掉；2、刪除學(xué)號(hào)為“ 95003 ”的相關(guān)信息；3、刪除"IS"系選修了 "2"號(hào)課程的選課記錄；4、刪除“ CS”系的全體學(xué)生的選課記錄；5、刪除整表的數(shù)據(jù)，注意表之間的關(guān)系，保持一致性。三、實(shí)驗(yàn)結(jié)果：（一）利用查詢分析器在表中插入數(shù)據(jù) 打開在實(shí)驗(yàn)一中建立的“學(xué)生課程”數(shù)據(jù)庫(kù)；在“ SCDB”數(shù)據(jù)庫(kù)中的 student、course、sc表中插入數(shù)據(jù);stude

10、 nt表的容:SNOSNAMESSEXSAGESDEPT95001勇男20CS95002晨女19IS95003王敏女18MAcourse表的容CNOCNAMECPNOCREDIT1數(shù)據(jù)庫(kù)542數(shù)學(xué)NULL23信息系統(tǒng)144操作系統(tǒng)NULL35數(shù)據(jù)處理NULL2sc表的容SNOCNOGRADE9500119295001265950013889500229095002380in sert into S ( Sno , Sname , Ssex , Sage , Sdept ) values ('95001'， 勇','男',20 , 'CS'

11、);in sertinto S ( Sno , Sname , Ssex , Sage , Sdept ) values ('95002'， 晨','女'，19, 'IS');in sertinto S ( Sno , Sname , Ssex , Sage , Sdept ) values ('95003','王敏','男',20 , 'CS');select *from S ;in sertinto C ( Cno , Cname , Cpno , Ccredit ) v

12、alues ('1','數(shù)據(jù)庫(kù)'，'5', 4);in sertinto C ( Cno , Cname , Cpno , Ccredit ) values (2， 數(shù)學(xué)',NULL, 2);in sertinto C ( Cno , Cname , Cpno , Ccredit ) values (3，'信息系統(tǒng)','1', 4);in sertinto C ( Cno , Cname , Cpno , Ccredit ) values ('4'， 操作系統(tǒng)',NULL, 3);i

13、n sertinto C ( Cno , Cname , Cpno , Ccredit ) values ('5'， 數(shù)據(jù)處理',NULL, 2);in sertinto SC ( Sno , Cno , Grade ) values ('95001', '1',92);in sertinto SC ( Sno , Cno , Grade ) values ('95001', '2', 65);in sertinto SC ( Sno , Cno , Grade ) values ('95001&#

14、39;, '3',88);in sertinto SC ( Sno , Cno , Grade )values ('95002', '2', 90);in sertinto SC ( Sno , Cno , Grade )values ('95002', '3', 80);二結(jié)果消息95002劉晨95003 王敏sdcslscs 悶起1?加Ssn李95001 395002 195QD1 j 19500? J 2Crioderad31m"E422數(shù)學(xué)NULL233ttJS-1444攝作NULL355HULL

15、295002 3Crio Criarne Cpno Ccredit（二）修改表中的數(shù)據(jù)在插入數(shù)據(jù)后的三表S、C、SC中進(jìn)行相關(guān)的更新操作:1、將student表中勇'的名字改為明'update Sset Sname ='明'where Sno ='95001'* I弟結(jié)果J涓息Sno Snane+i lllk-HII IHH Illi b-HI j1 j 9500夢(mèng)月2 *融廠戈嗔J M003 王敏d9flsr 1 elS3男女男2、將student表中王敏的年齡改為20歲;update Sset Sage =20where Sname =

16、9;王敏SnaSname Ssex 5ageSdepl1測(cè)01 李明 男20CS255002劃晨 女 1SIS3旳）03王融 男2DLesj3、將所有學(xué)生的年齡增加update Sset Sage =Sage +1;1歲;4、修改學(xué)生的學(xué)號(hào)95002'為95005注意表之間的關(guān)系，保持一致性;update Sset Sno ='95005'where Sno ='95002'-rQ|SnoSnameSsacSageSdept195001李明21cs235M3王敏21cs39500520IS5、把選修了“ 2”號(hào)課程，且成績(jī)低于update SCset G

17、rade =Grade *1.05where Cno ='2' and Grade <70;70分的成績(jī)提高5% ;g邛"巧l；冃后6、將“CS”系全體學(xué)生的成績(jī)置零；update SCset Grade =0where 'CS' =( select Sdept from S where S . Sno =SC. Sno);（三）刪除表中的數(shù)據(jù)1、把選修了“ 2 ”號(hào)課程，且成績(jī)低于delete from SCwhere Cno ='2' and Grade <70 ;70分的學(xué)生的成績(jī)刪除掉；| 一鉛杲| _J消息乩Cno

18、 Grade丄 jgwoi1 029&M130355W22 W4950023B02、刪除學(xué)號(hào)為“ 95003 ”的相關(guān)信息;deletefrom SSno5nam&SsexSage射eptwhereSno ='95003'1 j &&ooi i 李明21CS2曬005劃晨20IS3、刪除"IS"系選修了 "2"號(hào)課程的選課記錄；delete from SCwhere Cno ='2' and Sno in (select Sno from S where Sdept ='IS'

19、;);delete from SC4、刪除“ CS”系的全體學(xué)生的選課記錄;Sn<? Cno GradeI：JUS 1 i 95M2 j 2SO2 方可匝"J&Q where 'CS' =( select Sdept from S where S . Sno =SC. Sno);5、刪除整表的數(shù)據(jù)，注意表之間的關(guān)系，保持一致性。delete from SC ;一,Snp Crio Grde實(shí)驗(yàn)三數(shù)據(jù)查詢實(shí)驗(yàn)實(shí)驗(yàn)三成績(jī)： 一、實(shí)驗(yàn)?zāi)康氖煜ず驼莆諏?duì)數(shù)據(jù)表中數(shù)據(jù)的查詢操作和SQL命令的使用，學(xué)會(huì)靈活熟練的使用SQL語句的各種形式;加深理解關(guān)系運(yùn)算的各種操作（尤

20、其的關(guān)系的選擇，投影，連接和除運(yùn)算）二、實(shí)驗(yàn)方法及步驟1、在表 student, course, sc上進(jìn)行簡(jiǎn)單查詢；2 .在表 student, course, sc上進(jìn)行連接查詢；3 .在表 student, course, sc上進(jìn)行嵌套查詢；4. 使用聚合函數(shù)的查詢；5. 對(duì)數(shù)據(jù)的分組查詢；6. 對(duì)數(shù)據(jù)的排序查詢。三、實(shí)驗(yàn)容在表 student, course, sc上完成以下查詢：1查詢學(xué)生的基本信息；2、查詢“ CS”系學(xué)生的基本信息；3、 查詢“ CS”系學(xué)生年齡不在 19到21之間的學(xué)生的學(xué)號(hào)、；4、找出最大年齡；5、找出“ CS”系年齡最大的學(xué)生，顯示其學(xué)號(hào)、 ；6、統(tǒng)計(jì)“

21、CS”系學(xué)生的人數(shù)；7、統(tǒng)計(jì)各系學(xué)生的人數(shù)，結(jié)果按升序排列；8、按系統(tǒng)計(jì)各系學(xué)生的平均年齡，結(jié)果按降序排列；9、查詢每門課程的課程名；10、統(tǒng)計(jì)無先修課的課程的學(xué)分總數(shù)；11、查詢選修了“ 1”或“ 2”號(hào)課程的學(xué)生學(xué)號(hào)和；12、查詢選修了“ 1”和“ 2”號(hào)課程的學(xué)生學(xué)號(hào)和；13、 查詢選修了課程名為“數(shù)據(jù)庫(kù)系統(tǒng)”且成績(jī)?cè)?0分以下的學(xué)生的學(xué)號(hào)、和成績(jī)；14、 查詢每位學(xué)生選修了課程的學(xué)生信息（顯示：學(xué)號(hào)，課程號(hào)，課程名，成績(jī)）；15、查詢沒有選修課程的學(xué)生的基本信息；16、查詢選修了 3門以上課程的學(xué)生學(xué)號(hào)；17、 查詢選修課程成績(jī)至少有一門在80分以上的學(xué)生學(xué)號(hào)；18、 查詢選修課程平均

22、成績(jī)?cè)?0分以上的學(xué)生學(xué)號(hào)；19、（選做）統(tǒng)計(jì)每位學(xué)生選修課程的門數(shù)、學(xué)分及其平均成績(jī)；20、 （選做）找出平均成績(jī)?cè)?85分以上的學(xué)生，結(jié)果按系分組，并按平均成績(jī)的升序排列。四、實(shí)驗(yàn)結(jié)果：1查詢學(xué)生的基本信息;select * from S ;StiqSnanreSagsSdept17 a. B IIS X-95001臭21CS296003王敏2Dcs3&5DO5女20IS49500920CS595010陳杰禺22IS695012季告女1Bcs195013女20IS2、查詢“ CS”系學(xué)生的基本信息;select*SnameSsexSageSdeptfrom S1jbi“I 95W1

23、郛月胃21CSwhere Sdept ='CS'29S003壬鎖20CSg9500920CS495012疫1GCS3、查詢“ CS”系學(xué)生年齡不在 19到21之間的學(xué)生的學(xué)號(hào)、5ria Snameselect Sno , Snamefrom Swhere sdept ='CS'4、找出最大年齡；select max ( Sage ) from S ;AND Sage not between19 and 21iiw-iiiaiauai1 22k.maSdept氐歹茗)jl.Ul IIMJIII Uli 111_j 45、找出“ CS”系年齡最大的學(xué)生，顯示其學(xué)號(hào)、

24、select Sno , Sname from S where Sage =(select max ( Sage )from Swhere Sdept ='CS');7、統(tǒng)計(jì)“ CS”系學(xué)生的人數(shù);SELECT Sdept , count (*) from Swhere Sdept ='CS'GROUP BY Sdept ;8、統(tǒng)計(jì)各系學(xué)生的人數(shù)，結(jié)果按升序排列;SELECT Sdept , COUNT(*)射申無劉&FROMS|ir-th , i ' cs r-iiaiiA-iiii f Mgroup by sdept ;2IS39、按系統(tǒng)計(jì)各

25、系學(xué)生的平均年齡，結(jié)果按降序排列；select Sdept , avg ( Sage )from SEdept氏列售group by Sdept|_miiibmi iiiukiii irf1 I IS I 20order by avg ( Sage ) desc ;2 CS1910、查詢每門課程的課程名;selectCn ame二1錯(cuò)杲消息from CCnaniegroupby Cname ;1Minimi nana m|Z2數(shù)據(jù)處理3藪據(jù)庫(kù)4教學(xué)5信息累統(tǒng)10、統(tǒng)計(jì)無先修課的課程的學(xué)分總數(shù);select sum ( Ccredit )from C where Cpno is null;號(hào)課程

26、的學(xué)生學(xué)號(hào)和;select S . Sno , Snamefrom S , SCWHERES . Sno =SC. SnoAND (Cno ='1'or Cno ='2');1菊告2&5010II m » MBiiiaaj 汴鈦j u iiiniaui iiubr3Sno bname12、查詢選修了“ 1”和“ 2”號(hào)課程的學(xué)生學(xué)號(hào)和；select S . Sno , Snamefrom SC , Swhere Cno ='1' AND S . Sno =SC. Sno and SC . Sno IN(select Snofro

27、m SCwhere Cno ='2');13、查詢選修了課程名為“數(shù)據(jù)庫(kù)系統(tǒng)”且成績(jī)?cè)?0分以下的學(xué)生的學(xué)號(hào)、和成績(jī);select S . Sno , Sname , Gradefrom S , SC, Cwhere SC . Sno = S. Sno and C . Cno =SC. Cno and Cname ='數(shù)據(jù)庫(kù)and Grade <60;14、查詢每位學(xué)生選修了課程的學(xué)生信息(顯示：學(xué)號(hào),select S . Sno , Sname , C. Cno , Cname , Grade from SC , C, SWHERES . Sno =SC. Sn

28、o and C . Cno =SC. Cno;，課程號(hào)，課程名，成績(jī))Sno Snarre Cna Crsrrie GradeJ .i1 i 9&012 i 季皓2_95009 陳酸g 9HJ10陳杰4 95010陳杰5 9M1D陳衣1埶搖庫(kù)闊4操作緊統(tǒng)M1 數(shù)據(jù)庫(kù)代2 數(shù)學(xué)703 信息系統(tǒng)關(guān)11、查詢選修了“ 1”或“ 2”15、查詢沒有選修課程的學(xué)生的基本信息;select *1=SnoSnarneS3E3CSageSdeptI 95003卑20CS295013王媚ISfrom SWHERE not exists(select *from SCWHERES . Sno=Sno );

29、16、查詢選修了 3門以上課程的學(xué)生學(xué)號(hào);select Snofrom SCgroup by Snohavingcount (*)> 3;17、查詢選修課程成績(jī)至少有一門在80分以上的學(xué)生學(xué)號(hào);selectdistinctS .Snofrom S ,SCWHERES.Sno =SC. Snoand Grade >80;18、查詢選修課程平均成績(jī)?cè)?0分以上的學(xué)生學(xué)號(hào);selectdistinetS . Snofrom S , SCwhere SC . Sno = S. Sno and SC . Sno in(select Snofrom SCGROUP BY Snohaving a

30、vg （ Grade ） 80）;19、（選做）SC20、 （選做）找出平均成績(jī)?cè)?5分以上的學(xué)生，結(jié)果按系分組，并按平均成績(jī)的升序排列。實(shí)驗(yàn)四視圖的定義、使用實(shí)驗(yàn)實(shí)驗(yàn)四成績(jī)：、實(shí)驗(yàn)?zāi)康氖煜ず驼莆諏?duì)數(shù)據(jù)表中視圖的定義操作和熟悉和掌握對(duì)數(shù)據(jù)表中視圖的查詢操作和熟悉和掌握對(duì)數(shù)據(jù)表中視圖的更新操作和SQL命令的使用；SQL命令的使用；SQL命令的使用，并注意視圖更新與基本表更新的區(qū)別與聯(lián)系;學(xué)習(xí)靈活熟練的進(jìn)行視圖的操作，認(rèn)識(shí)視圖的作用;二、實(shí)驗(yàn)方法及步驟1.視圖的定義2 .視圖的查詢3 .視圖的修改三、實(shí)驗(yàn)容：以students, courses, sc表為基礎(chǔ)完成以下視圖定義(使用 SQL和企業(yè)管

31、理器兩種方法)及使用1定義“ IS”系學(xué)生基本情況視圖V_IS并查詢結(jié)果；2將students, courses, sc表中學(xué)生的學(xué)號(hào)，課程號(hào)，課程名，成績(jī)定義為視圖V_S_C_G并查詢結(jié)果3將各系學(xué)生人數(shù)，平均年齡定義為視圖V_NUM_A VG并查詢結(jié)果；4定義一個(gè)反映學(xué)生出生年份的視圖V_YEAR并查詢結(jié)果；5將各位學(xué)生選修課程的門數(shù)及平均成績(jī)定義為視圖V_AVG_S_G并查詢結(jié)果；6建立視圖V_AVG平均成績(jī)?yōu)?0分以上的學(xué)生學(xué)號(hào)；7通過視圖V_IS，分別將學(xué)號(hào)為“ 95001 ”和“ 95004”的學(xué)生更改為“ S0001”，” S0004”并查詢結(jié)果8通過視圖V_IS，新增加一個(gè)學(xué)生

32、記錄('S0012' ,' YAN XI ' ,19,' IS'),并查詢結(jié)果；9通過視圖V_IS，新增加一個(gè)學(xué)生記錄('S0013' ,' YAN XI ' ,19,' MA '),并查詢結(jié)果；10通過視圖V_IS，刪除學(xué)號(hào)為“ S0012”和“ 95003”的學(xué)生信息，并查詢結(jié)果；四、實(shí)驗(yàn)結(jié)果 以students, courses, sc表為基礎(chǔ)完成以下視圖定義(使用 SQL和企業(yè)管理器兩種方法)及使用1定義“ IS”系學(xué)生基本情況視圖 V_IS并查詢結(jié)果;createviewV_ISSnoSn

33、amiBSsEXSdeptas1JIJI« Mail 111i Koos i女20ISselect*1'iwin里22ISfromS3時(shí)013王媚女20I5whereSdept='IS'2將students, courses, sc表中學(xué)生的學(xué)號(hào)，課程號(hào)，課程名，成績(jī)定義為視圖V_S_C_G并查詢結(jié)果createview V_S_C_GSnoSnameCnoCnaneGradeas1irB it nvaii! 9EO133信慝系統(tǒng)55selectS Sno , Sname ,C. Cno ,Cn ame,Grade2950092埶學(xué)50fromS , SC,

34、CwhereS Sno =SC. Snoand SC Cno =C Cno;3將各系學(xué)生人數(shù)，平均年齡定義為視圖V_NUM_A VG并查詢結(jié)果Sdept Sdepi_count Sage_avgcreate view V_NUM_AVG ( Sdept , Sdept_count , Sage_avg ) asselect Sdept , count ( Sdept ), avg (Sage )from Sgroup by Sdept ;4定義一個(gè)反映學(xué)生出生年份的視圖V_YEAR并查詢結(jié)果結(jié)果.消息SnafTiESbiithday1闔1翔21M33劉晨1W3d1W3519916垂浩1W51王

35、媚1&93create view V_YEAR ( Sname , Sbirthday )asselect Sname , 2013 - Sagefrom S ;5將各位學(xué)生選修課程的門數(shù)及平均成績(jī)定義為視圖V_AVG_S_G并查詢結(jié)果；createview V_AVG_S_G ( Sno , Cno_count , Grade_avg )asH結(jié)杲消息selectSno , count (Cno), avg ( Grade )5noCno.count Grade_avgfromSC11groupby Sno ;2&5001236.建立視圖V AVG平均成績(jī)?yōu)?0分以上的學(xué)生學(xué)號(hào)；39500515£createview V AVG ( Grade over90 Sno)495013155assele