SQL試驗與練習(xí)題參考答案

本文格式為Word版，下載可任意編輯——SQL試驗與練習(xí)題參考答案數(shù)據(jù)庫原理與應(yīng)用（SQLServer）練習(xí)題-1-

試驗1數(shù)據(jù)庫操作

1．創(chuàng)立數(shù)據(jù)庫：

操作1.1：創(chuàng)立一個test數(shù)據(jù)庫，其主數(shù)據(jù)文件規(guī)律名test_data，物理文件名test_data.mdf，初始大小10MB，最大尺寸為無限大，增長速度1MB；數(shù)據(jù)庫日志文件規(guī)律名稱為test_log，物理文件名為test_log.ldf，初始大小為1MB，最大尺寸為5MB，增長速度為10%。

參考答案：

createdatabaseTestONprimary()LOGON()GO

name=test_log,

filename='d:\\test\\test_log.ldf',size=1MB,maxsize=5MB,filegrowth=10%name=test_data,

filename='d:\\test\\test_data.mdf',size=5MB,

maxsize=unlimited,filegrowth=1MB

2．查看數(shù)據(jù)庫屬性：

操作1.2：使用T-SQL語句查看數(shù)據(jù)庫test屬性參考答案：

EXECsp_helpdbtest

3．刪除數(shù)據(jù)庫：

操作1.3：使用T-SQL語句刪除數(shù)據(jù)庫test參考答案：

dropdatabaseTest

試驗2表操作

1．創(chuàng)立表：

操作2.1：創(chuàng)立學(xué)生表：

表名：student

長度9說明：學(xué)生基本信息表空值NotNull列約束PK說明學(xué)生學(xué)號屬性列st_id數(shù)據(jù)類型nVarChar數(shù)據(jù)庫原理與應(yīng)用（SQLServer）練習(xí)題-2-

st_nmst_sexst_birthst_scorest_datest_fromst_dpidst_mntnVarCharnVarChardatetimeintdatetimenCharnVarChartinyint82202NotNullNullNullNullNullNullNullNull學(xué)生姓名學(xué)生性別出生日期入學(xué)成績?nèi)雽W(xué)日期學(xué)生來源所在系編號學(xué)生職務(wù)參考答案：USEtestGO

CREATETABLEstudent(

st_idnVarChar(9)primarykeyNOTNULL,st_nmnVarChar(8)NOTNULL,st_sexnVarChar(2)NULL,st_birthdatetimeNULL,st_scoreintNULL,st_datedatetimeNULL,st_fromnVarChar(20)NULL,st_dpidnVarChar(2)NULL,st_mnttinyintNULL)GO操作2.2：創(chuàng)立課程信息表：

表名：couse長度420說明：課程信息表空值NotNullNotNullNullNull列約束PK說明課程編號課程名稱課程學(xué)時課程學(xué)分屬性列cs_idcs_nmcs_tmcs_sc數(shù)據(jù)類型nVarCharnVarCharintint參考答案：

USEtestGO

CREATETABLEcouse(

cs_idnVarChar(4)primarykeyNOTNULL,cs_nmnVarChar(20)NOTNULL,cs_tmintNULL,cs_scintNULL)GO操作2.3：創(chuàng)立選課表：

表名：slt_couse

說明：選課表

數(shù)據(jù)庫原理與應(yīng)用（SQLServer）練習(xí)題-3-

屬性列cs_idst_idscoresltdate數(shù)據(jù)類型nVarCharnVarCharintdatetime長度49空值NotNullNotNullNullNull列約束FKFK說明課程編號學(xué)生編號課程成績選課日期參考答案：

USEtestGO

CREATETABLEcouse(

cs_idnVarChar(4)NOTNULLcontraintfk_xxxreference課程信息表（cs.id）,

st_idnVarChar(9)NOTNULL,scoreintNULL,sltdatedatetime)GO

操作2.4：創(chuàng)立院系信息表：

表名：deptNULL

長度220812說明：院系信息表空值NotNullNotNullNullNull列約束說明系編號院系名稱院系主任聯(lián)系電話屬性列dp_iddp_nmdp_drtdt_tel數(shù)據(jù)類型nVarCharnVarCharnVarCharnVarChar參考答案：USEtestGO

CREATETABLEdept(

dp_idnVarChar(2)NOTNULL,dp_nmnVarChar(20)NOTNULL,dp_drtnVarChar(8)NULL,dp_telnVarChar(12)NULL)GO

2．修改表結(jié)構(gòu)：

（1）向表中添加列：

操作2.5：為“dept〞表添加“dp_count〞列（數(shù)據(jù)類型為nvarchar，長度為3，允許為空）參考答案：

ALTERTABLEdeptADDdp_countnvarchar(3)NULL（2）修改列數(shù)據(jù)類型：

操作2.6：修改“dept〞表的“dp_count〞列數(shù)據(jù)類型為int參考答案：

數(shù)據(jù)庫原理與應(yīng)用（SQLServer）練習(xí)題-4-

ALTERTABLEdeptALTERCOLUMNdp_countintNULL（3）刪除表中指定列：

操作2.7：刪除“dept〞表的“dp_count〞列參考答案：

ALTERTABLEdeptDROPCOLUMNdp_count

3．刪除表

操作2.8：刪除“dept〞表參考答案：

DROPTABLEstudent

4．向表中輸入數(shù)據(jù)記錄

操作2.9：分別向“student〞表、“couse〞表、“slt_couse〞表、“dept〞表中輸入數(shù)據(jù)記錄

試驗3數(shù)據(jù)完整性

1．空值約束(NULL)

操作3.1：將student表中的st_sex列屬性更改為NOTNULL參考答案：

ALTERTABLEstudentALTERCOLUMEst_nmnVarChar(8)NOTNULL

2．默認值約束(DEFAULT)

操作3.2：將student表中的st_from列默認值設(shè)置為“陜西省〞參考答案：

ALTERTABLEstudentADDDEFAULT'陜西省'FORst_from

3．默認值對象

操作3.3：創(chuàng)立默認值對象df_today為當前日期，并將其綁定到slt_couse表中的sltdate列，然后取消綁定，最終刪除默認值對象df_today。

參考答案：

CREATEDEFAULTdf_todayASGetdate()GO

EXECsp_bindefaultdf_today,'slt_couse.sltdate'GO

EXECsp_unbindefault'slt_couse.sltdate'GO

DROPDEFAULTdf_todayGO

4．檢查約束(CHECK)

操作3.4：將slt_couse表中的score列的檢查約束設(shè)置為>=0且=0ANDscore

數(shù)據(jù)庫原理與應(yīng)用（SQLServer）練習(xí)題-11-

操作9.2：用ANSI形式連接查詢學(xué)生學(xué)號、姓名、性別及其所選課程編號

SELECTa.st_id,st_nm,st_sex,cs_idFROMstudentaINNERJOINslt_cousebONa.st_id=b.st_idORDERBYa.st_id操作9.3：用SQLServer形式連接查詢學(xué)生學(xué)號、姓名及其所選課程名稱及成績

SELECTa.st_id,st_nm,cs_nm,scoreFROMstudenta,slt_couseb,cousec

WHEREa.st_id=b.st_idANDb.cs_id=c.cs_idORDERBYa.st_id操作9.4：用ANSI形式連接查詢學(xué)生學(xué)號、姓名及其所選課程名稱及成績

SELECTa.st_id,st_nm,cs_nm,score

FROMslt_couseaINNERJOINstudentbONa.st_id=b.st_idINNERJOINcousecONa.cs_id=c.cs_idORDERBYb.st_id操作9.5：查詢選修了1002課程的學(xué)生學(xué)號、姓名及1001課程成績

SELECTa.st_id,st_nm,score

FROMstudenta，slt_couseb

WHEREa.st_id=b.st_idANDb.cs_id='1002'ORDERBYb.st_id

操作9.6：查詢選修了“數(shù)據(jù)結(jié)構(gòu)〞課程的學(xué)生學(xué)號、姓名及課程成績

SELECTa.st_id,st_nm,score

FROMstudenta,slt_couseb,cousec

WHEREa.st_id=b.st_idANDb.cs_id=c.cs_idANDc.cs_nm='數(shù)據(jù)結(jié)構(gòu)'ORDERBYa.st_id

操作9.7：用左外連接查詢沒有選修任何課程的學(xué)生學(xué)號、姓名

SELECTa.st_id,st_nm,score

FROMstudentaLEFTOUTERJOINslt_cousebONa.st_id=b.st_idWHEREb.cs_idISNULLORDERBYb.st_id操作9.8：用右外連接查詢選修各個課程的學(xué)生學(xué)號

SELECTb.cs_id,a.st_id

FROMslt_couseaRightOUTERJOINcousebONa.cs_id=b.cs_idORDERBYb.cs_id

試驗10數(shù)據(jù)查詢（6）——子查詢

操作10.1：用子查詢對各班人數(shù)進行查詢（新增列）

SELECTDISTINCTLEFT(a.st_id,6)AS班級,人數(shù)=(SELECTCOUNT(st_id)FROMstudentb

WHERELEFT(a.st_id,6)=LEFT(b.st_id,6))

FROMstudentaORDERBYLEFT(a.st_id,6)ASC操作10.2：用子查詢對各課程的選課人數(shù)進行查詢（新增列）

SELECTDISTINCTa.cs_id,人數(shù)=

數(shù)據(jù)庫原理與應(yīng)用（SQLServer）練習(xí)題-12-

(SELECTCOUNT(st_id)FROMslt_cousebWHEREa.cs_id=b.cs_id)

FROMslt_couseaORDERBYa.cs_idASC

操作10.3：查詢選修了1002課程成績不及格的學(xué)生的學(xué)號、姓名和性別，并按姓名升序排序

通過子查詢實現(xiàn)：——使用IN關(guān)鍵字

SELECTst_id,st_nm,st_sexFROMstudentWHEREst_idIN

(SELECTst_idFROMslt_couse

WHEREcs_id='1002'ANDscoreANY

(SELECTscoreFROMslt_couse

WHEREcs_id='1002'ANDLEFT(st_id,6)='070511')ANDLEFT(st_id,6)'070511'ANDcs_id='1002'操作10.6：查詢其它班比070511班任一學(xué)生的1002號課程成績高的學(xué)生信息（ANY/ALL）

SELECT*FROMslt_couseWHEREscore>ALL

(SELECTscoreFROMslt_couse

WHEREcs_id='1002'ANDLEFT(st_id,6)='070511')ANDLEFT(st_id,6)'070511'ANDcs_id='1002'操作10.7：查詢大于等于60分且且比1003課程平均成績低的學(xué)生課程信息（Between?And）

SELECT*FROMslt_cousea

WHEREa.scoreBETWEEN60AND

數(shù)據(jù)庫原理與應(yīng)用（SQLServer）練習(xí)題-13-

(SELECTAVG(b.score)FROMslt_cousebWHEREb.cs_id='1003')

操作10.8：查詢系主任為“趙虎”的系的所有學(xué)生信息

通過子查詢實現(xiàn)：——IN運