2016年數(shù)據(jù)庫(kù)的卷二練習(xí)答案

-1、創(chuàng)建數(shù)據(jù)庫(kù)

createdatabasetest

onprimary

(

name='test_data',

filename='D:\SQL期末作業(yè)\test_data.mdf',

size=10MB,

maxsize=10MB,

filegrowth=llVIB)

logon

(

name=,test_log\

filename='D:\SQ.L期末作業(yè)\test_log.ldf',

size=lMB,

maxsize=5MB,

filegrowth=10%

)

go

-2、刪除數(shù)據(jù)庫(kù)

dropdatabasetest

-3>創(chuàng)建student表

usetest

go

createtablestudent

(

st_idnvarchar(9)notnullconstraintpkprimarykey,

st_nmnvarchar(8)notnull,

st_sexnvarchar(2)z

st_birthdatetimenull,

st_scoreintnull,

st_datedatetimenull,

st_fromnchar(20)7

st_dpidnvarchar⑵，

st_mnttinyint

)

--3、創(chuàng)建course表

usetest

go

createtablecourse

(csjdnvarchar(4)notnullconstraintpk_idprimarykey,

cs_nmnvarchar(20)notnull,

cs_tmint,

cs_scint)

--4、創(chuàng)建slt_course

usetest

go

createtableslt_course

(

cs_idnvarchar(4)notnullconstraintfk_idreferencescourse(cs_id),

st_idnvarchar(9)notnullconstraintfk_st_idreferencesstudent(stjd),

scoreint,

sitdatedatetime

)

-5、創(chuàng)建院系(dept)

usetest

go

createtabledept

(

dp_idnvarchar(2)notnull,

dp_nmnvarchar(20)notnull,

dp_drtnvarchar(8),

dt_telnvarchar(12)

)

-修改表結(jié)構(gòu)

-1)向表中添加列

usetest

go

altertabledept

adddp_countnvarchar(3)

go

-2)修改列數(shù)據(jù)

usetest

go

altertabledept

altercolumndp_countint

go

-3＞刪除表指定列(column)

usetest

go

altertabledept

dropcolumndp_count

go

--4、刪除dept表

usetest

go

droptabledept

go

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

usetest

go

insertstudent

values

（'20151090101,「鄧建娥）,女？1996,,,400,,

20157山東省濰坊,JOL'學(xué)習(xí)部副部，）

go

-3、數(shù)據(jù)完整性

-1）空值約束

usetest

go

altertablestudent

altercolumnst_sexvarchar(2)notnull

-默認(rèn)值約束(default)

usetest

go

altertablestudent

addconstraintdf_fromdefault'陜西省'forst_from

go

-檢查約束(check)

usetest

go

altertableslt_course

addconstraintck_coursecheck(score>=0andscore<=100)

go

-主鍵clustered為聚集索引，nonclustered為非聚集索引

usetest

altertabledept

addconstraintpk_dp_idprimarykeyclustered(dp_id)

go

一唯一，性約束(unique)

usetest

altertabledept

addconstraintuq_nmuniquenonclustered(dp_nm)

go

-創(chuàng)建外鍵

usetest

altertablestudent

addforeignkey(st_dpid)referencesdept(dpjd)

-數(shù)據(jù)更新

-4.1、dept表中插入數(shù)據(jù)

usetest

go

insertdept(dp_id,dp_nm,dp_drt,dt_tel)

values('ll；自動(dòng)控制系李啟宇','81234567')

go

--4.2、student表中插入記錄

usetest

go

insertstudent(st_id,st_nm,st_sex,st_birth,st_dpid)

values('070201001','王小五'」男?1990-09-09','ll')

go

-4.3course表中插入記錄

usetest

go

insertcourse(cs_id,cs_nm)

values('1234','操作系統(tǒng)')

go

-4.4slt_course表中添加數(shù)據(jù)

usetest

go

insertslt_course(cs_id,st_id)

values('12347070201001')

go

-2修改表中數(shù)據(jù)

--4.5修改student表中記錄

usetest

go

updatestudent

setst_score='88'

wherest_nm='王小五，

go

-4.6修改course表記錄

usetest

go

updatecourse

setcs_sc=4,cs_tm=64

-4.7修改slt_course表中記錄

usetest

go

updateslt_course

setscore=77

wherecs_id=1234andst_id=070201001

go

-刪除表中數(shù)據(jù)

usetest

deleteslt_course

wherecs_id='1234'andst_id='070201001,

go

--刪除course記錄

usetest

deletecourse

wherecsjd='1234'

go

-5數(shù)據(jù)查詢(xún)()簡(jiǎn)單查詢(xún)

-5.1查詢(xún)所有系的信息

usetest

go

select*

fromdept

go

-5.2查詢(xún)所有的課程號(hào)與課程名稱(chēng)

usetest

go

selectcs_id,cs_nm

fromcourse

go

-5.3查詢(xún)student時(shí)使用列表達(dá)式：入學(xué)成績(jī)+400

usetest

go

selectst_score+400

fromstudent

go

-5.4用as關(guān)鍵字dept表指定列名：系號(hào)、系名、系主任、聯(lián)系電話

usetest

go

selectdp_idas系號(hào),dp_nmas系名,dp_drtas系主任,dt_telas聯(lián)系電

話

fromdept

go

-5.5使用號(hào)為course表中屬性指定列名：課程號(hào)、課程名、學(xué)時(shí)

(=cs_sc*16)>學(xué)分

usetest

go

select課程號(hào)=cs_id,課程名=cs_nm,學(xué)分=cs_sc,學(xué)時(shí)=cs_sc*16

fromcourse

go

-5.6查詢(xún)列表中使用系統(tǒng)函數(shù)顯示所有學(xué)生的學(xué)號(hào)、姓名、性別和入

學(xué)年份

usetest

go

selectst_id,st_nm,st_sex,st_date

fromstudent

groupbyst_id

go

-5.7消除查詢(xún)結(jié)果中的重復(fù)項(xiàng)顯示所有學(xué)生班級(jí)

usetest

selectdistinctst_id

fromstudent

go

-6數(shù)據(jù)查詢(xún)（）-條件查詢(xún)

-L使用關(guān)系表達(dá)式查詢(xún)條件

-6.1查詢(xún)dept表中系號(hào)為的院系信息

usetest

go

select*

fromdept

wheredp_id='ll,

go

-6.2查詢(xún)student表中系的學(xué)生學(xué)號(hào)、姓名、性別和所在系編號(hào)

usetest

go

selectst_id,st_nm,st_sex,st_dpid

fromstudent

wherest_dpid='11'

go

-6.3查詢(xún)student表中年及以后入學(xué)的學(xué)生信息

usetest

select*

fromstudent

wherest_date>'2008-12-31'

go

-6.4在查詢(xún)student表班學(xué)生的學(xué)號(hào)、姓名、性別和入學(xué)成績(jī)

usetest

go

selectst_id,st_nm,st_sex,st_score

fromstudent

wherest_bj='080808,

go

-使用邏輯表達(dá)式表示查詢(xún)條件

-6.5查詢(xún)選修了號(hào)課程且成績(jī)?cè)谝韵碌膶W(xué)生學(xué)號(hào)

usetest

go

selectst_id

fromslt_course

wherecs_id=,1002'andscore<'60'

go

-6.6查詢(xún)系和系的學(xué)生信息

usetest

go

select*

fromstudent

wherest_dpid='ll'orst_dpid='12'

go

-6.7查詢(xún)所有“計(jì)算機(jī)"開(kāi)頭的課程信息

usetest

go

select*

fromcourse

wherecsnmlike'計(jì)算機(jī)％'

go

-6.8查詢(xún)?cè)?7」到630之間出生的學(xué)生信息

usetest

go

select*

fromstudent

wherest_birthbetween'1989-07-01'and'1990-06-30'

go

-6.9查詢(xún)選修了號(hào)課程且成績(jī)?cè)诘街g的學(xué)生選課信息

usetest

go

select*

fromslt_course

wherecs_id='1001'and(scorebetween60and80)

go

-6.10使用IN關(guān)鍵字進(jìn)行查詢(xún)查詢(xún)系、系、系的學(xué)生信息

usetest

go

select*

fromstudent

wherest_dpidin('ll1,'12','13')

go

-6.11使用[NOT]NULL關(guān)鍵字進(jìn)行查詢(xún)查詢(xún)系、系、系的學(xué)生信息

usetest

go

select*

fromstudent

where(st_dpidin(,11712'；13,))and(st_bjisnull)

go

-6.11查詢(xún)所有生源為非空的學(xué)生信息

usetest

go

select*

fromstudent

wherest_fromisnotnull

go

-7數(shù)據(jù)查詢(xún)()一一查詢(xún)排序與查詢(xún)結(jié)果存儲(chǔ)

-7.1：查詢(xún)課程信息，按課程名稱(chēng)降序排序:desc升序：asc

usetest

go

select*

fromcourse

orderbycs_nmdesc

go

-7.2查詢(xún)選修了號(hào)課程成績(jī)非空的學(xué)生學(xué)號(hào)和成績(jī)，并按成績(jī)降序排序

usetest

go

selecta.st_id,b.score

fromstudentasajoinslt_courseasb

ona.stjd=b.stjd

orderbyb.scoredesc

go

-8數(shù)據(jù)查詢(xún)()—一查詢(xún)統(tǒng)計(jì)與匯總

-8.1查詢(xún)選修號(hào)課程的學(xué)生人數(shù)

usetest

go

selectCOUNT(*)as學(xué)生人數(shù)

fromstudent

wherestjdin(selectst_idfromslt_coursewherecs_id='1001')

go

-8.2查詢(xún)選修班學(xué)生的平均入學(xué)成績(jī)

usetest

go

selectAVG(st_score)as平均入學(xué)成績(jī)

fromstudent

wherest_bj='070101,

gor

-8.3查詢(xún)號(hào)學(xué)生選修課程的數(shù)量、總分以及平均分

usetest

go

selectCOUNT(*)as選修課數(shù)量,SUM(score)as總分,AVG(score)as平均

分

fromslt_course

wherest_id='070101001,

go

-8.4查詢(xún)選修號(hào)課程的學(xué)生人數(shù)、最高分、最低分和平均分

usetest

go

selectCOUNT(*)as學(xué)生人數(shù),max(score)as最高分,MIN(score)as最低

分,AVG(score)as平均分

fromslt_course

wherecs_id=1001

go

-8.5查詢(xún)選修了均分在以上的課程號(hào)及均分

usetest

go

selectcs_id,AVG(score)平均分

fromslt_course

groupbycs_id

havingAVG(score)>75

go

-9數(shù)據(jù)查詢(xún)()——連接查詢(xún)、子查

詢(xún)

-9.1查詢(xún)學(xué)生學(xué)號(hào)、姓名、性別及其所選課程編號(hào)

usetest

go

selectstudent.stjd,st_nm,st_sexzcs_id

fromstudentjoinslt_course

onstudent.st_id=slt_course.st_id

go

-9.2查詢(xún)學(xué)生學(xué)號(hào)、姓名及其所選課程名稱(chēng)及成績(jī)

usetest

go

selectstudent.stjd;st_nm,cs_id;score

fromstudentjoinslt_course

onstudent.st_id=slt_course.st_id

go

-9.3查詢(xún)選修了“數(shù)據(jù)結(jié)構(gòu)"課程的學(xué)生學(xué)號(hào)、姓名及課程成績(jī)

usetest

go

selectstudent.st_id,st_nm,score

fromstudentjoinslt_course

onstudent.st_id=slt_course.st_id

wherecs_idin(selectcs_idfromcoursewherecs_nm='數(shù)據(jù)結(jié)構(gòu)

go

-10數(shù)據(jù)查詢(xún)()——子查詢(xún)

-10.1查詢(xún)選修了課程成績(jī)不及格的學(xué)生的學(xué)號(hào)、姓名和性別，并按姓

名升序排序

usetest

go

selectst_id,st_nm,st_sex

fromstudent

wherest_idin(selectst_idfromslt_coursewherecs_id='1002'and

score<'60')

go

--11視圖和索引

-11.1基于student表創(chuàng)建一個(gè)視圖view_s2014,可以查看年及以后入學(xué)

的學(xué)生信息

usetest

go

createviewview_s2014

as

select*

fromstudent

wherest_date>'2014-12-30'

go

--11.2為course表創(chuàng)建基于課程編號(hào)列的聚集索引kcbhjndex

usete-77st

go

createclusteredindexkcbhjndexoncourse(cs_id)

go

execsp_helpindex'course'

-12游標(biāo)

-12」用游標(biāo)實(shí)現(xiàn)：輸出所有女學(xué)生的學(xué)號(hào)、姓名、性別和班級(jí)代碼。

usetest

go

declarec_xsxxcursorkeysetfor

selectst_id,st_nm,st_sex,st_bj

fromstudent

openc_xsxx

declare@xhnvarchar⑻,@xmnvarchar(8),@xbnvarchar(8),@bjdm

nvarchar(lO)

if@@ERROR=0-判斷游標(biāo)打開(kāi)是否成功

begin

if@@CURSOR_ROWS>0

begin

print'共有女學(xué)生,+rtrim(cast(@@cursor_rowsaschar⑶))+'名，分別是:'

print"

fetchnextfromc_xsxxinto@xh,@xm,@xb,@bjdm

while(@@FETCH_STATUS=O)

beginprint@xh+',,+@xm+'「+@xb+'「

fetchnextfromc_xhxxinto@xh,@xm,@xb,@bjdm

end

end