大數(shù)據庫SQL語句實驗報告材料

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

2、績SnoCnograde5 .將以上創(chuàng)建表 S、C、SC的SQL命令以.SQL文件的形式保存在磁盤上。6、在表S上增加“出生日期”屬性列。7、 刪除表S的“年齡”屬性列。&刪除S列的唯一值約束。9、修改SC表成績屬性列為精確數(shù)字型。10、在表S上，按“ Sno”屬性列的唯一值方式建立索引。11、 刪除表SC,利用磁盤上保存的.SQL文件重新創(chuàng)建表 SC。12、 創(chuàng)建教材P74頁習題5中SPJ數(shù)據庫中的四個關系：S、P、J、SPJ。三、實驗結果：1 創(chuàng)建學生課程數(shù)據庫create database Stude nd ;2 .創(chuàng)建學生關系表 S :學號性別年齡所在系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)建課程關系表 C :】田千口 i=r. 課程號課程名先行課學分CnoCn ameCpnoccreditcreate table C(Cno char (4) primary keyCname CHAR(40),Cpno char (4),Ccredit SMALLINT ,);4 創(chuàng)建課程關系表 SC :學號】田千口 i=r. 課程號成績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 國 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表成績屬性列為精確數(shù)字型。alter table SC alter colu mn Grade int3鉤束_J列國 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頁習題5中SPJ數(shù)據庫中的四個關系：S、P、J、SPJ。實驗二數(shù)據更新實驗實驗二成績：一、實驗目的1、熟悉和掌握數(shù)據表中數(shù)據的插入、修改、刪除操作和命令的使用2、加深理解表的定義對數(shù)據更新的作用二、實驗步驟及容（一）利用查詢分析器在表中插入數(shù)據 打開在實驗一中建立的“學生課程”數(shù)據庫；在"SCDB”數(shù)據庫中的 student、course、sc表中插入數(shù)據； stude nt表的容：SNOSNAMESSEXSAGESDE

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

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

10、 nt表的容:SNOSNAMESSEXSAGESDEPT95001勇男20CS95002晨女19IS95003王敏女18MAcourse表的容CNOCNAMECPNOCREDIT1數(shù)據庫542數(shù)學NULL23信息系統(tǒng)144操作系統(tǒng)NULL35數(shù)據處理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ù)據庫'，'5', 4);in sertinto C ( Cno , Cname , Cpno , Ccredit ) values (2， 數(shù)學',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ù)據處理',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);二結果消息95002劉晨95003 王敏sdcslscs 悶起1?加Ssn李95001 395002 195QD1 j 19500? J 2Crioderad31m"E422數(shù)學NULL233ttJS-1444攝作NULL355HULL

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

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

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

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

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

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

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

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

23、郛月胃21CSwhere Sdept ='CS'29S003壬鎖20CSg9500920CS495012疫1GCS3、查詢“ CS”系學生年齡不在 19到21之間的學生的學號、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”系年齡最大的學生，顯示其學號、

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

25、系學生的平均年齡，結果按降序排列；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錯杲消息from CCnaniegroupby Cname ;1Minimi nana m|Z2數(shù)據處理3藪據庫4教學5信息累統(tǒng)10、統(tǒng)計無先修課的課程的學分總數(shù);select sum ( Ccredit )from C where Cpno is null;號課程

26、的學生學號和;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”號課程的學生學號和；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ù)據庫系統(tǒng)”且成績在60分以下的學生的學號、和成績;select S . Sno , Sname , Gradefrom S , SC, Cwhere SC . Sno = S. Sno and C . Cno =SC. Cno and Cname ='數(shù)據庫and Grade <60;14、查詢每位學生選修了課程的學生信息(顯示：學號,select S . Sno , Sname , C. Cno , Cname , Grade from SC , C, SWHERES . Sno =SC. Sn

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

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

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

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

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

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

34、CwhereS Sno =SC. Snoand SC Cno =C Cno;3將各系學生人數(shù)，平均年齡定義為視圖V_NUM_A VG并查詢結果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定義一個反映學生出生年份的視圖V_YEAR并查詢結果結果.消息SnafTiESbiithday1闔1翔21M33劉晨1W3d1W3519916垂浩1W51王

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