數(shù)據(jù)庫原理SQL語句查詢練習

1、西女財經(jīng)學阮信息學院姓名龔雨學號1305170125班級計本1301數(shù)據(jù)庫原理實驗報告年級2013 級指導教師張志剛實驗名稱數(shù)據(jù)庫對象的建立與數(shù)據(jù)更新查詢 實驗室實驗日期、實驗目的及要求1、掌握數(shù)據(jù)庫對象的設(shè)計及建立；2、針對給定的實際問題，完成數(shù)據(jù)庫對象的設(shè)計，包括各表的結(jié)構(gòu)（屬性名、類型、 約束等）及表之間的關(guān)系，在選定的DBMSt建立數(shù)據(jù)庫表，用SQL命令和可視化環(huán)境（可 選）分別建立數(shù)據(jù)庫表，體會兩種方式的特點；3、熟悉和掌握數(shù)據(jù)表中數(shù)據(jù)的插入、修改、刪除操作和命令的使用；加深理解表的定 義對數(shù)據(jù)更新的作用；4、對已經(jīng)建立的數(shù)據(jù)庫進行修改、刪除、插入等操作；5、掌握關(guān)系數(shù)據(jù)庫的各種查詢

2、方法，理解其含義，包括嵌套查詢、連接查詢等；6對關(guān)系數(shù)據(jù)庫進行查詢，包括嵌套查詢、連接查詢；二、實驗環(huán)境硬件平臺：PC軟件平臺：Win dows 2000 / MICROSOFT SQLSERVER三、實驗內(nèi)容對象的設(shè)計與建立用SQL命令和可視化工具（可選）完成以下操作：1 創(chuàng)建學生-課程數(shù)據(jù)庫：create database xueshengkechengl2.創(chuàng)建學生關(guān)系表S,其中Sno是主碼學號姓傘性別年齡所在系SnoSn ameSsexSageSdept- x |MQ-3 Lweshe dboLTble 1*1酎聖菽曰丁 3.Tits裁IE理型Hi午創(chuàng) (ku 吋輒諭冗 1 10 3.

3、 16D0-HAJ iinhlresnoxu J REporrserver 王 I RnzortSerpJmDfl j zuEshmn 卄 thengjnencvadiaGQ)p:-c-vtfditfCO)f?訝】evariliorOO)“sdept悄由餐uj“廠create table S(sno varchar(30) primary key, sname varchar(30), ssexvarchar(30), sagevarchar(30), sdept varchar(30);J02-3 1-MCThr dbtkTobi jll *40 : 】*Li5*lic mg L -3.創(chuàng)建

4、課程關(guān)系表C，其中Cno是主碼，Cpno是外碼:課程號課程洛先行課學分CnoCn ameCpnoccredit費色魁 F鼻、(IIJFIJ) 3QL SrrxEii 10. 0. L4D0 -J盂眾劇E屋出數(shù)毎庫伙眾 J RwcttSar*IE J RwCftSrTriplJeF I.Kurshffiid-jcch&iq- I MJshcidjHhienglF 一create table C(cnovarchar(50) primary key, cname varchar(50), cpno varchar(50), ccredit varchar(50), foreign key (cpn

5、o) referencesC (eno);4創(chuàng)建課程關(guān)系表SC,其中（Sno,Cno）是主碼，Sno和Cno分別是外碼:學號課程號成績SnoCnoGrade3術(shù)【皿I5QL 5辭就F山血】杠0襁3 丫 4折 “沏耳口舸&喬” L*和帶哥岸卑N燧庫覦示 U Rrrwtxr-cr Repwlbef*-etrffTfCb亠 _ xur*FM 仇 hr-u *uKrw*crhcrq尸姒雨卓手因1IQ J14HK51W.MJ. IabiQ V跟 JL MThE V： L !(.T_ItiuPG1 十TE-J1 xuesherMherci対起hMSO)IE J系賽create table SC (sno

6、varchar (30),eno varchar (50),grate varchar(30), primary key (sno,eno),foreign key (sno)referencesS(sno), foreign key (eno) references C(cno);5在表S上增加“出生日期”屬性列;AMV-PC-xuwhe ngkechengl - dbaSl*snare呼 sdeoiJU*yTha3Ci)yharfJOJ 時h訊at 障加6 Ya-mntX：MB*alter table S add birth varchar(30);6 刪除表S的“年齡”屬性列;UIY P

7、Crjcfbailuchaigl 曲必 LnaneiWYardinOlL arha(3) gip!)3圖血HcvOOO1鋪 M11Ealter table S drop column sage7 在表S,按“ Sno屬性列的AU+PCjTJMhetigke806 把選修了課程名為“數(shù)據(jù)結(jié)構(gòu)”的學生的成績提高10%update SC setgrate =grate * 1. 1 where gratein(selectSC grate from SC, C whereSC eno 二C eno and cname in ( select cname from Cwhere cname 二數(shù)據(jù)庫)

8、7 把選修了 “ C3號課程，且成績低于該門課程的平均成績的學生成績提高5%update SC set grate =grate * 1. 05 where grate (select avg (grate ) from SC where eno =3) and eno =38把選修了 “ C2號課程，且成績低于該門課程的平均成績的學生成績刪除掉；delete from SC where grate(select avg (grate) from SC where eno=2) and cno=29 刪除“ CS系全體學生的選課記錄；delete from SC where計算制I=(sele

9、te sdept from S where S sno二SC sno)10刪除學號為“ S2的相關(guān)信息；delete from SC where sn o=13051701delete from S where sn o=1305170111 將表S C SC中的數(shù)據(jù)全部刪除;在已做過的實驗基礎(chǔ)上，練習查詢語句的使用，包括簡單查詢、嵌套查詢、連接查詢、分組查詢、排序查詢、聚合查詢等；練習，體會各種查詢的異同及相互之間的轉(zhuǎn)換，體會各種查 詢的執(zhí)行過程，為簡單綜合應用打下良好的基礎(chǔ)。具體內(nèi)容，在表s, C, SC上完成以下查詢：1查詢學生的基本信息；SELECT * from S2.查詢“ CS系

10、學生的基本信息；select * from S where sdept =計算機X 查詢“ CS系學生年齡不在19到21之間的學生的學號、姓名;select snosnamefrom S where sdepl二#算機andsage not between 19 and 214找出所有學生中的最大年齡；select MAX (sage) from S5. 找出“ CS系年齡最大的學生，顯示其學號、姓名；select sno, sname from S where sdept =計算機and sage in ( select MAX (sage) from S where sdept =計算機)

11、6. 找出各系年齡最大的學生，顯示其學號、姓名；select sno, sn ame , sdept ,sage from S xwhere sage=( select max(sage) from S y where y. sdept二x. sdept )7. 統(tǒng)計“ CS系學生的人數(shù)；-select COUNT (sno ) from S where ( sdept 二計算機)統(tǒng)計各系學生的人數(shù)，結(jié)果按升序排列;select sdept , COUNT( s no) from S group by sdept order by COUNT( s no) asc9. 按系統(tǒng)計各系學生的平均年

12、齡，結(jié)果按降序排列;select sdept , avg (sage ) from S group by sdept order by avg (sage ) desc10. 查詢每門課程的課程名;select eno , cname from C11. 查詢無先修課的課程的課程名和學時數(shù);select ename , ceredit from C where epno is null12. 統(tǒng)計無先修課的課程的學時總數(shù);Select sum (ccredit ) from C where epno is null13. 統(tǒng)計每位學生選修課程的門數(shù)、學分及其平均成績；select SC. sn

13、o , COUNT( SC. eno ), avg (SC grate ), sum ( C ccredit ) from SC, C where SC. eno 二C eno group by SC. sno14. 統(tǒng)計選修每門課程的學生人數(shù)及各門課程的平均成績；select eno , COUNT( sno ), AVG( grate ) from SC group by eno15. 查詢選修了 “1”或“ 2”號課程的學生學號和姓名；select S sno , sname from S, SC where S sno =SC sno and eno in (1, 2)16查詢選修了

14、“ 1”和“2”號課程的學生學號和姓名;select S sno , sname from S, SC where S sno =SC sno and eno =1 and SC. sno in( select sno from SC where eno =2)17. 查詢選修了 “數(shù)據(jù)庫系統(tǒng)”且成績在 60分以下的學生的學號、姓名和成績；select S sno , sname , SC. grate from S, C, SC where S sno 二SC sno and SC. eno 二C eno and ename 二數(shù)據(jù)丿車and SC. grate 321. 查詢選修課程成績至

15、少有一門在 80分以上的學生學號；selectdistinetsno fromSC where grate8022. 查詢選修課程成績均在80分以上的學生學號；selectsno fromSC groupby sno havingmin(grate ) 8023. 查詢選修課程平均成績在80分以上的學生學號；select sno from SC group by sno having AVG( grate ) 80針對SPJ數(shù)據(jù)庫中的四個表S, P, J, SPJ,完成教材P74-75習題5中的查詢及教材P127 習題5中的查詢（可選）。P75:(1) 、select sno from SPJ

16、 where jno=，jl(2 八 select sno from SPJ where jno= jl* and pno=，pT(4)、 select jno from SPJ , S, P where S sno=SPJ sno and SPJ pno 二P pno andP pno in(select P pno from P where color 二紅)and S sno in(select S sno from S where city 天聿):(5 八 select jno from SPJ where sno =，siP127(1 ) select sname , city f

17、rom S(2) 、 select pname , color , weight from P(3) 、select jno from SPJ where sno 二sT(4). select pname , qty from SPJ , P where SPJ pno 二P pno and SPJ jno 二j2(5)selectpno from SPJ , Swhere S sno =SPJ. sno and Ssno in( selectS sno fromSwhereS city二上海，)（6 ）、selectjn amefrom SPJ,S , J where S sno =SPJ

18、sno and SPJ.jno=J jno andS snoin( selects sno fromS where S. city =,上海,)(7 )selectdisti netSPJ . jnofrom SPJ , S , J whereS sno 二SPJ snoandSPJ jno 二J.jnoand S sno not in( selectS.sno from S where S cityy天津，）(8 )updateP setcolor =,藍,where color二紅(9 )deletefromSPJ wheresno 二s5and jno 二j4and pno 二p6in sertintoSPJ values(s3，,，p6，j4, 500 )(10 ).del