SQLServerSQL實驗與練習題參考答案

1、數據庫原理與應用(SQL Server)練習題1 -實驗1數據庫操作1. 創(chuàng)建數據庫：操作1.1：創(chuàng)建一個test數據庫，其主數據文件邏輯名test_data,物理文件名test_data.mdf,初始人小 10MB,最大尺寸為無限大，增長速度1MB：數據庫口志文件邏輯名稱為test Jog,物理文件名為testjog.ldf, 初始大小為1ME,最大尺寸為5ME,增長速度為10%。參考答案：create database TestON primary(name = test_data,filename = 1d:testmdf1,size = 5MB,maxsize = unlimitedf

2、filegrowth = 1MB)LOG ON(name = test_log,filename = 1d:testtest_1ogldf1,size = lMBfmaxsize = 5MB,filegrowth = 10%)GO2. 查看數據庫屬性：操作1.2：使用T-SQL語句查看數據庫test屬性參考答案：EXEC sp_helpdb t est3. 刪除數據庫：操作1.3：使用T-SQL語句刪除數據庫test參考答案：drop database Test實驗2表操作創(chuàng)建表：數據庫原理與應用(SQL Server)練習題# -數據庫原理與應用(SQL Server)練習題# -操作2.1

3、：創(chuàng)建學生表:表名：student說明：學生基本信息表數據庫原理與應用(SQL Server)練習題3 -屬性列數據類型長度空值列約束說明st_idnVarChai9Not NullPK學生學號st_nmnVarChai8Not Null學生姓名st_sexnVarChai2Null學生性別st_birthdatetimeNull出生日期st_scoreintNull入學成績st_datedatetimeNull入學日期st_fiomnChar20Null學生來源st_dpidnVarChai2Null所在系編號st_mnttinvint/Null學生職務參考答案:USE testGOCREA

4、TE TABLE studentstidnVarChar(9)primary key NOT NULL ,stnmnVarChar(8)NOT NULL ,stsexnVarChar(2)NULL zstbirthdatetimeNULL zstscoreintNULL zstdatedatetimeNULL zstfromnVarChar(20)NULL zstdpidnVarChar(2)NULL zstmnttinyintNULL)GO操作2.2：創(chuàng)建課程信息表:表名：couse說明：課程信息表屬性列數據類型長度空值列約束說明cs_idnVarChai4Not NullPK課程編號數據庫

5、原理與應用(SQL Server)練習題11 -cs_nmnVarChai20Not Null課程名稱cs_tmintNull課程學時cs_scintNull課程學分參考答案：USE testGOCREATE TABLE couseCS_idnVarChar(4)primary key NOT NULL .CSnmnVarChar(20)NOT NULL ,CStmintNULL zCSscintNULL)GO操作2.3：創(chuàng)建選課表：表名：slt_couse說明：選課表屬性列娠類型長度空值列約束說明cs_idnVarChar4Not NullFK課程編號st_idnVarChar9Not Nu

6、llFK學生編號scoreintNull課磁績sitdatedatetimeNull選課日期參考答案:USE testGOCREATE TABLE cousecs idnVarChar(4)NOT NULL ,st idnVarChar(9)NOT NULL ,scoreintNULL zsitdatedatetimeNULL)GO表名:dept操作24創(chuàng)建院系信息表:說明：院系信息表屬性列數據類型長度空值列約束說明dp_idnVarChai2Not Null系編號dp_nmnVarChai20Not Null院系名稱dp_drtnVarChai8Null院系主任dt_telnVarChai1

7、2Null聯系電話參考答案：USE testGONOT NULL ,NOT NULL ,NULL zNULLCREATE TABLE deptdp_id nVarChar(2) dp_nm nVarChar(20) dp_drt nVarChar(8) dp_tel nVarChar(12)GO1. 修改表結構：(1) 向表中添加列：操作2.5：為“dept"表添加“dp_count”列(數據類型為nvaichai-,長度為3,允許為空) 參考答案：ALTER TABLE dept ADD dp_count nvarchar(3) NULL(2) 修改列數據類型：操作2.6：修改“d

8、ept"表的"dp_count”列數據類型為int參考答案：ALTER TABLE dept ALTER COLUMN dp_count int NULL(3) 刪除表中指定列：操作2.7：刪除“dept”表的“dp_count”列參考答案：ALTER TABLE dept DROP COLUMN dp_count3刪除表操作2.8：刪除“dept”表 參考答案：DROP TABLE student4. 向表中輸入數據記錄操作29 分別向"student”表、couse”表、lt_couse"表、"dept”表中輸入數據記錄實驗3數據完整性1空

9、值約束(NULL )操作3.1 ：將student表中的st_sex列屬性更改為NOT NULL參考答案：ALTER TABLE student ALTER COLUME st_nm nVarChar(8) NOT NULL2. 默認值約束(DEFAULT )操作3.2：將student表中的st.fiom列默認值設置為“陜西省”參考答案：ALTER TABLE student ADD DEFAULT '陜西省，FOR st_from3. 默認值對象操作3.3：創(chuàng)建默認值對象dOoday為當前口期，并將其綁定到slt.couse表中的sitdate列，然后取消 綁定，最后刪除默認值對彖

10、dfltodayo參考答案：CREATE DEFAULT df_today AS Getdate()GOEXEC sp_bindefault df_todayz 1slt_couse sitdate1GOEXEC sp_unbindefault 1slt_couse sitdate 1GODROP DEFAULT df_todayGO4. 檢查約束(CHECK )操作3.4：將slt_couse表中的score列的檢查約束設置為>=0且<=100參考答案：ALTER TABLE slt_couse ADD CHECK (score>=0 AND score<=100)5

11、規(guī)則約束對象操作3.5：創(chuàng)建規(guī)則約束對彖H_sex,用于檢查性別的取值僅限于“男”和“女”，并將其綁定到student 表中的st_sex列，然后取消綁定，最后刪除規(guī)則約束對彖rl_sexo參考答案：CREATE RULE rl sex AS Qchksex '男'OR chksex='女'或CREATE RULE rl_sex AS Gchksex IN ('男'女')GOEXEC sp_bindrule rl_sexz 1 student st_sex1GOEXEC sp_unbindrule 1 student st_sex1GOD

12、ROP RULE rl_sexGO6. 主鍵操作3.6：將dept表中的dp_id列設置為主鍵參考答案：ALTER TABLE dept ADD PRIMARY KEY (dp_id)7. 唯一性約束(UNIQUE )操作3.7：將dept表中的dp_mn列設置為唯一性約束 參考答案：ALTER TABLE dept ADD UNIQUE (dp_nm)&標識列操作3.8：向slt.couse表中添加標識列id,第1行默認值為1,相鄰兩個標識列間的增量為1 參考答案：ALTER TABLE slt_couse ADD id INT IDENTITY 1,1) NOT NULL9.外鍵(

13、FOREIGN KEY )操作3.9：被參照表為dept,參照表為student參考答案：ALTER TABLE studentADD FOREIGN KEY (st_dpid) REFERENCES dept(dp_id)實驗4數據更新1. 表中插入數據操作4.1：向dept表插入一條記錄，系號11,系名自動控制系，系主任為李其余，電話81234567 INSERT INTO dept VALUES * 11 ，自動控制系'，李其余'，,81234567,)操作4.2：向student表插入一條記錄，學號070201001,姓名為王小五，性別為男，出生口期為1990 年9月9

14、 口，系號為11,其余字段為NULL或默認值INSERT INTO student(st_id/ st_nm, st_sexz st_birthz st_dpid)VALUES (,070201001f,，王小五 J，男 J 7990.9.9J)操作4.3：向couse表插入一條記錄，課程號1234,課程名為操作系統(tǒng)，其余字段為NULL或默認值 INSERT INTO couse (cs_idz cs_nm) VALUES (,1234 噪作系統(tǒng)冷操作4.4：向slt_couse表插入一條記錄，課程號1234,學名070201001,其余字段為NULL或默認值INSERT INTO slt_c

15、ouse(cs_idz st_id) VALUES (112341, ,070201001,)2. 修改表中數據操作4.5：修改student表記錄，將王小五的入學成績改為88UPDATE student SET st_score=88 WHERE st_nm='王小五'操作4.6：修改couse表記錄，將康有記錄的學分改為4,牽時改為64UPDATE couse SET cs_tm=64z cs_sc=4操作4.7：修改slt_couse表記錄，將課程號為1234,學名為070201001的記錄的成績改為77UPDATE sit couse SET score=77 WHER

16、E cs id=f12341 AND st id=107020100113. 刪除表中數據操作4.8：刪除slt_couse表記錄，將課程號為1234,學名為070201001的記錄刪除DELETE FROM slt_couse WHERE cs_id=*1234' AND st_id=* 0702010011 操作4.9：刪除couse表記錄，將課程號為1234的記錄刪除DELETE FROM couse WHERE cs_id=*12341實驗5數據查詢（1） 簡單查詢（1）查詢表中所有的列 操作5.1：查詢所有系的信息SELECT * FROM dept（2）查詢表中指定列的信息

17、操作5.2：查詢所有的課程號與課程名稱SELECT cs_id, cs_nm FROM couse（3）在查詢列表中使用列表達式操作5.3：在查詢student表時使用列表達式：入學成績+400SELECT st_id, st_nm, st_score, st_score+400 AS new_scoreFROM student（4）重新命名查詢結果操作5.4：使用AS關鍵字為dept表中屬性指定列名：系號、系名、系主任、聯系電話SELECT dp_id AS 系號，dp_nm AS 系名，dp_drt AS 系主任，dp_tel AS 聯系電話 FROM dept操作5.5：使用“=”號為c

18、ouse表中屬性指定列名：課程號、課程名、學時（=cs_sc*16）.學分 SELECT 課程?'=cs_id,課程名=cs_nm,學*=cs_sc,學時=cs_sc*16FROM couse（5）增加說明列操作5.6：查詢dept表的系號、系名和系主任，向查詢結果中插入說明列：系號、系名和系主任SELECT 1 系號st_id,，系名：，st_nm,，系主任st_drtFROM dept（6）查詢列表中使用系統(tǒng)函數操作5.7：顯示所有學生的學號、姓名、性別和入學年份SELECT st_id, st_nm, st_sex, DATE PART （yy, st_birth） AS 入學年

19、份FROM student操作5.&顯示所有學生學號、姓名、性別和班級（學號前6位）SELECT st_id, st_nm, st_sex, LEFT （st_id, 6） AS 班級FROM student（7）消除查詢結果中的重復項 操作5.9：顯示所有學生班級SELECT DISTINCT LEFT （st_id, 6） AS 班級 FROM student（8）取得查詢結果的部分行集 一 操作5.10：顯示前5條學生記錄信息SELECT TOP 5 * FROM student操作5.11：顯示前25%條學生記錄信息SELECT TOP 25 PERCENT * FROM st

20、udent操作5.12：顯示前11條學生記錄信息，n為局部變量DECLARE n INTSET n = 4SELECT TOP n * FROM student實驗6數據查詢(2)條件查詢1.使用關系表達式表示査詢條件操作6.1：查詢dept表中系號為11的院系信息SELECT * FROM dept WHERE dp_id = 操作6.2：查詢student表中11系的學生學可、姓名、性別和所在系編號SELECT st_id, st_nm, st_sexz st_dpid FROM studentWHERE st_dpid = 1llf操作6.3：查詢student表中2008年及以后入學的

21、學生信息SELECT * FROM studentWHERE DATEPART( yy, st_date )>= 2008操作6.4：在查詢student表080808班學生的學號、姓名、性別和入學成績SELECT st_id, st_nm, st_sex, st_score FROM studentWHERE Left(st_idz 6) = f080808 12-使用邏輯表達式表示查詢條件操作6.5：查詢student表中非11系的學生信息SELECT * FROM student WHERE NOT (st_dpid =操作6.6：查詢選修了 1002號課程且成績在60以下的學生學

22、號SELECT st_id FROM slt_couseWHERE (cs_id=f10021) AND (score<60)操作6.7：查詢2007年入學的11系所有男生信息SELECT * FROM studentWHERE DATEPART(yyz st_date)=2007 AND st_dpid=lllI AND st_sex=f 1 操作6.8：查詢11系和12系的事生信息一一SELECT * FROM studentWHERE st_dpid=,llf OR st_dpid=f121操作6.9：查詢11系和12系所有200?年入學的學生信息SELECT * FROM stu

23、dentWHERE (st_dpid=f111 OR st_dpid=f12f) AND DATEPART(yy,st_date)=20073使用LIKE關鍵字進行模糊查詢操作6.10：查詢所有“計算機”開頭的課程信息SELECT * FROM couse WHERE cs_nm LIKE '計算機字 操作6.11：查詢所有由三個字組成的“王”姓辜生信息SELECT * FROM student WHERE st_nm LIKE '王_1操作6.12：查詢所有課程名中包含“信息”的課無信息SELECT * FROM couse WHERE cs_nm LIKE 1操作6.13：

24、查詢學生姓名介于王姓到張姓的石息SELECT * FROM studentWHERE st_nm LIKE 7王-張字4.使用Between.And關鍵字進行査詢操作6.14：查詢在1989.7.1到1990.6.30之間出生的學生信息SELECT st_id, st_nm, st_sex, st_birth FROM studentWHERE st_birth BETWEEN 1198171' AND 119996.30操作6.15：查詢拡修了 1001號課程且成績在60到80之間的學生選課信息SELECT * FROM slt_couseWHERE cs_id=,1001f AND

25、 (score BETWEEN 60 AND 80)5使用IN關鍵字進行査詢操作6.16：查詢11系、12系、13系的學生信息SELECT * FROM student WHERE st_dpid IN ('ll1,12','13')操作6.17：查詢所有張，王，李，趙姓的學生&J學號、姓名、性別SELECT st_id, st_nm, st_sex FROM studentWHERE Left (st_nm, 1) IN ('張'J 王'J 李'J 趙')6.使用NOT NULL關鍵字進行査詢操作6.18：查詢所

26、有生源為非空的學生信息SELECT * FROM student WHERE st_from IS NOT NULL 操作6.19：查詢選修了 1001號課程且成績?yōu)榭彰髮W生選課信息SELECT * FROM slt_couseWHERE cs_id=,1001f AND score IS NULL實驗7數據查詢(3) 詢排序與查詢結果存儲操作7.1：查詢課程信息，按課程名稱降序排序SELECT * FROM couse ORDER BY cs_nm DESC操作7.2：查詢選修了 1001號課程成績非空的學生學號和成績，并按成績降序排序SELECT st_id, score FROM slt

27、_corseWHERE cs_id=,1001f AND score IS NOT NULLORDER BY score DESC操作7.3：查詢11系學生學號、姓名和年齡，按年齡升序排序SELECT st_idz st_nm,DATEPART(yyf GETDATE( )-DATEPART(yyz st_birth) AS age FROM studentORDER BY age ASC操作7.4：查詢學生信息，按姓名升序排序，再按系號降序排序SELECT * FROM student ORDER BY st_nm, st_dpid DESC 操作7.5：創(chuàng)建學生表副本studentOb僅保

28、留學生牽號、姓矗和性別SELECT st_id, st_nm, st_sex INTO studentOl FROM student操作7.6：查詢陜西籍學生，將結果保存在新表st_shanxiSELECT * INTO st_shanxiFROM studentWHERE st from=陜西省'數據庫原理與應用(SQL Server)練習題15 -操作7.7：查詢選修了 1001號課程學生的選課信息，按學號升序排序，將結果保存在新表sltlOOl SELECT * INTO sltlOOl FROM slt_corseWHERE cs_id=f1001f ORDER BY st_i

29、d操作7.8：用局部變量©stage保存學生張三的年齡DECLARE stage intSELECT Qstage = DATEPART(yy,GETDATE()-DATEPART(yyz st_birth)FROM studentWHERE張三.操作7.9：用局駆變量©name和stscore保存070101班按學號排序后最后一個學生的姓名和入學成績 DECLARE name nVarChar(8) z Qstscore intSELECT name = st_nm, Qstscore = st_scoreFROM studentWHERE LEFT(st_idz 6)

30、= ,070101lORDER BY st_id實驗8數據查詢(4) 查詢統(tǒng)計與匯總操作8.1：查詢課程總數SELECT COUNT ( * ) FROM couse操作8.2：查詢選修1001號課程的學生人數SELECT COUNT(st_id)FROM slt_couseWhere cs_id = 110011操作8.3：查詢贏修課程的數量SELECT COUNT( DISTINCT cs_id ) FROM slt_couse操作8.4：查詢選修070101班學生的平均入學成績SELECT AVG(st_score)FROM studentWHERE LEFT(st_idz 6) = ,

31、070101l操作8.5：查詢070101001號學生選修課程的數量、總分以及平均分SELECT COUNT (cs_id) AS 課程數量，SUM (score) AS 總分，AVG (score) AS 平均分 FROM slt_couseWHERE st_id= 10701010011操作8.6：查詢1001號課程的學生人數、最高分、最低分和平均分SELECT COUNT (*) AS 學生人數' MAX (score) AS 最高分，MIN (score) AS 最彳氐分， AVG (score) AS 平均分FROM slt_couseWHERE cs_id=,1001,操作

32、8.7：求各石梟程號和相應的選課人數SELECT cs_idz COUNT(st_id)FROM slt_couse GROUP BY cs_id操作8.8：統(tǒng)計各班人數SELECT LEFT(st_idz 6) AS 班級，COUNT (st_id) AS 人數FROM studentGROUP BY LEFT(st id, 6)操作8.9：依次按班級、系號對學生進行分類統(tǒng)計人數、入學平均分SELECT st_dpid AS 系號/ LEFT (st_idz 6) AS 班級，COUNT (st_nm) AS 人數，AVG (st_score) AS 均分FROM studentGROUP

33、BY LEFT(st_idz 6) z st_dpid操作8.10：查詢選修了均分在75以上的課程號及均分SELECT cs_id AS 課程編號，AVG (score) AS 均分FROM slt_couseGROUP BY cs_id HAVING AVG(score)>75操作8.11：查詢選修了 2門以上課程的學生學號SELECT st_id FROM slt_couseGROUP BY st_id HAVING COUNT(*)>2操作8.12：明細匯總年齡<20的學生，并匯總學生數量、平均年齡SELECT st_nm, DATEPART (yy, GETDATE

34、( ) ) -DATEPART (yyz st_birth) AS 年齡 FROM studentWHERE DATEPART(yy,GETDATE()-DATEPART(yy,st_birth)<20COMPUTE COUNT(st_nm)zAVG(DATEPART(yy,GETDATE()-DATEPART(yy,st_birth) 操作8.13：按班級明細匯總成績<85分的學生，匯總學生數、均分SELECT st_nm, LEFT (st_id, 6) AS 班級/ st_scoreFROM studentWHERE st_score<85ORDER BY 班級COMP

35、UTE COUNT (st_nm) , AVG (st_score) BY 班級實驗9數據查詢(5) 連接查詢操作9.1：用SQLServer形式連接查詢學生學號、姓名、性別及其所選課程編號SELECT a.st_id, st_nm, st_sexz cs_idFROM student az slt_couse bWHERE A.st_id = b.st_idORDER BY A st_id操作9.2：用ANSI形式忘接查詢學生學號、姓名、性別及其所選課程編號SELECT a.st_id, st_nm, st_sexz cs_idFROM student a INNER JOIN slt_co

36、use bON a.st_id = bst_idORDER BY a.st_id操作9.3：用SQLServer形式連接查詢學生學號、姓名及其所選課程名稱及成績SELECT a.st_id, st_nm, cs_nm, scoreFROM student az slt_couse bz couse cWHERE a.st_id = b.st_id AND bcs_id = c.cs_idORDER BY a.st_id操作9.4：用ANSI形式連接查詢學生學號、姓名及其所選課程名稱及成績SELECT a.st_id, st_nm, cs_nm, scoreFROM sit couse a IN

37、NER JOIN student b ON a.st id = b.st idINNER JOIN couse c ON a.cs_id = c.cs_idORDER BY b st_id操作9.5：查詢選修了 1002課程的學生學號、姓名及1001課程成績SELECT a.st_id, st_nm, scoreFROM student a, slt_couse bWHERE A.st_id = b.st_id AND bcs_id = 11002 1ORDER BY b.st_id操作9.6：查詢選修了 '破據結構”課程的學生學號、姓名及課程成績SELECT a.st_id, st_

38、nm, scoreFROM student az slt_couse bz couse cWHERE a. st_id=b st_id AND b cs_id=c cs_id AND c. cs_nm='數據結構' ORDER BY A st_id操作9.7：用左外連接查詢沒有選修任何課程的學生學號、姓名SELECT a.st_id, st_nm, scoreFROM student a LEFT OUTER JOIN slt_couse b ON Ast_id = bst_idWHERE b cs_id IS NULLORDER BY b.st_id操作9.8：用右外連接查詢

39、選修各個課程的學生學號SELECT bcs_id, a.st_idFROM slt_couse a Right OUTER JOIN couse b ON a.cs_id = bcs_id ORDER BY b cs_id實驗10數據查詢(6)子查詢操作10.1：用子查詢對各班人數進行查詢(新增列)SELECT DISTINCT LEFT (a St_id,6) AS 班級，人數 =( SELECT COUNT(st_id) FROM student bWHERE LEFT(a.st_idz 6) = LEFT(b.st_id,6)FROM student a ORDER BY LEFT(aS

40、t_id,6) ASC 操作10.2：用子查詢對各課程的選課人數進行查詢：新增列)SELECT DISTINCT a.cs_idz 人數=( SELECT COUNT(st_id) FROM slt_couse bWHERE Acs_id = bcs_id)FROM slt_couse a ORDER BY a.cs_id ASC操作10.3：查詢選修了 1002課程成績不及格的學生的學號、姓名和性別，并按姓名升序排序通過子查詢實現：一一使用IN關鍵字SELECT st_id, st_nm, st_sex FROM studentWHERE st_id IN( SELECT st_id FRO

41、M slt_couseWHERE cs_id=l1002, AND score < 60)ORDER BY st_nm通過子查詢實現：一一使用比較運算符SELECT st id, st nm, st sexFROM student aWHERE( SELECT score FROM slt_couse bWHERE A.st_id = bst_id AND cs_id = 110021 ) < 60 ORDER BY st_nm操作10.4：查詢“東另紅”同學所在班的學生信息，并按姓名降序排序通過子查詢實現：一一IN運算符SELECT st_id, st_nm, st_sex FR

42、OM studentWHERE LEFT(st_dpid,6) IN( SELECT Left (st_dpid, 6) FROM student WHERE st_nm='東方紅')ORDER BY st_nm DESC通過自連接查詢實現：一JOINSELECT sl.st_idz slst_nm, si.st_sexFROM student sl JOIN student s2ON LEFT(sl.st_dpid,6) = LEFT(s2 St_dpid，6)WHERE s2 st_nm= 1 東方紅 1ORDER BY s2st_nm操作10.5：查詢其它班比070511班某一學生的1002號課程成績高的學生信息(ANY/ALL)SELECT * FROM slt_couseWHERE score > ANY( SELECT score FROM slt_couseWHERE cs_id = ,1002, AND LEFT(st_idz 6) = 10705111)AND LEFT(st_idz 6) <> ' 070511' AND cs_id = 110021操作10.6：查詢其它班比070511班任一學生的1002號課程成績高的學生信息(ANY/A