數(shù)據(jù)庫(kù)原理SQL語(yǔ)句查詢(xún)練習(xí)[驕陽(yáng)教育]

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

2、掌握關(guān)系數(shù)據(jù)庫(kù)的各種查詢(xún)方法，理解其含義，包括嵌套查詢(xún)、連接查詢(xún)等；6、對(duì)關(guān)系數(shù)據(jù)庫(kù)進(jìn)行查詢(xún)，包括嵌套查詢(xún)、連接查詢(xún)；二、實(shí)驗(yàn)環(huán)境硬件平臺(tái)：PC；軟件平臺(tái)：Windows 2000 / MICROSOFT SQLSERVER；三、實(shí)驗(yàn)內(nèi)容對(duì)象的設(shè)計(jì)與建立用SQL命令和可視化工具（可選）完成以下操作：1. 創(chuàng)建學(xué)生-課程數(shù)據(jù)庫(kù):create database xueshengkecheng12創(chuàng)建學(xué)生關(guān)系表S，其中Sno是主碼學(xué)號(hào)姓名性別年齡所在系SnoSnameSsexSageSdept create table S(sno varchar(30) primary key,sname varc

3、har(30),ssex varchar(30),sage varchar(30),sdept varchar(30);3創(chuàng)建課程關(guān)系表C，其中Cno是主碼，Cpno是外碼：課程號(hào)課程名先行課學(xué)分CnoCnameCpnoccreditcreate table C(cno varchar(50) primary key,cname varchar(50),cpno varchar(50),ccredit varchar(50),foreign key (cpno) references C(cno);4創(chuàng)建課程關(guān)系表SC，其中(Sno,Cno)是主碼，Sno和Cno分別是外碼：學(xué)號(hào)課程號(hào)成績(jī)Sn

4、oCnoGradecreate table SC(sno varchar(30),cno varchar(50),grate varchar(30),primary key (sno,cno),foreign key(sno)references S(sno),foreign key (cno) references C(cno);5在表S上增加“出生日期”屬性列； alter table S add birth varchar(30);6刪除表S的“年齡”屬性列；alter table S drop column sage;7在表S上，按“Sno”屬性列的unique方式建立索引；creat

5、e unique index Ssno on S(sno);8刪除表SC；drop table SC cascade;數(shù)據(jù)更新：對(duì)已建好的各表輸入適當(dāng)?shù)臄?shù)據(jù)并練習(xí)數(shù)據(jù)的刪除和修改，注意檢測(cè)鍵碼約束、外碼約束、非空約束、唯一約束、取值約束等各種約束在輸入數(shù)據(jù)及數(shù)據(jù)的刪除、修改時(shí)的作用，結(jié)合課堂教學(xué)體會(huì)其含義。具體內(nèi)容：1 將示例數(shù)據(jù)分別插入表S、C、SC；insert into S values(13051701,夏雨,女,計(jì)算機(jī),1995.1.1,20)insert into S values(13051702,蘇皇,男,軟件,1996.11.12,19)insert into S value

6、s(13051703,王五,男,計(jì)算機(jī),1995.8.4,20)insert into S values(13051704,趙茜,女,計(jì)科,1994.3.3,21)insert into S values(13051705,錢(qián)呈,男,計(jì)科,1993.4.7,22)insert into C values(1,數(shù)據(jù)庫(kù),null,4)insert into C values(2,離散,1,3)insert into C values(3,操作系統(tǒng),2,4)insert into C values(4,程序設(shè)計(jì),1,5)insert into C values(5,匯編,4,4)insert int

7、o SC values(13051701,2,80)insert into SC values(13051701,4,90)insert into SC values(13051702,3,85)insert into SC values(13051702,5,96)insert into SC values(13051703,1,88)2 在SC中插入某學(xué)生的選課信息（如：學(xué)號(hào)為“S1”，課程號(hào)為“c1”，成績(jī)待定）； insert into SC values(13051703,3,null)3 將“CS”系全體學(xué)生的成績(jī)置零； update SC set grate=0 where 計(jì)算

8、機(jī)=(select sdept from S where S.sno=SC.sno)4 將學(xué)號(hào)為“S1”的學(xué)生的學(xué)號(hào)修改為“S001” Delete from SC where sno=13051705 delete from S where sno=13051705;insert into S values(1305170005,錢(qián)呈,男,計(jì)科,1993.4.7,22)5 把平均成績(jī)大于80分的男生學(xué)號(hào)和平均成績(jī)存入表SGRADE（SNO，AVGGRADE）； create table SGRATE (sno varchar(30), avggrate float); insert into

9、 SGRATE(sno,avggrate) select sno,avg(grate) from SC group by sno having avg(grate)806 把選修了課程名為“數(shù)據(jù)結(jié)構(gòu)”的學(xué)生的成績(jī)提高10%； update SC set grate=grate*1.1 where grate in (select SC.grate from SC,C where SC.cno=C.cno and cname in (select cname from C where cname=數(shù)據(jù)庫(kù))7 把選修了“C3”號(hào)課程，且成績(jī)低于該門(mén)課程的平均成績(jī)的學(xué)生成績(jī)提高5%； update

10、SC set grate=grate*1.05 where grate (select avg(grate) from SC where cno=3) and cno=38 把選修了“C2”號(hào)課程，且成績(jī)低于該門(mén)課程的平均成績(jī)的學(xué)生成績(jī)刪除掉；delete from SC where grate (select avg(grate) from SC where cno=2) and cno=29 刪除“CS”系全體學(xué)生的選課記錄； delete from SC where 計(jì)算機(jī)=(selete sdept from S where S.sno=SC.sno)10 刪除學(xué)號(hào)為“S2”的相關(guān)信息

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

12、本信息； select * from S where sdept =計(jì)算機(jī)3 查詢(xún)“CS”系學(xué)生年齡不在19到21之間的學(xué)生的學(xué)號(hào)、姓名； select sno ,sname from S where sdept=計(jì)算機(jī) and sage not between 19 and 214 找出所有學(xué)生中的最大年齡； select MAX(sage) from S 5 找出“CS”系年齡最大的學(xué)生，顯示其學(xué)號(hào)、姓名； select sno, sname from S where sdept=計(jì)算機(jī) and sage in ( select MAX(sage) from S where sdept=計(jì)

13、算機(jī))6 找出各系年齡最大的學(xué)生，顯示其學(xué)號(hào)、姓名； select sno,sname ,sdept ,sage from S x where sage=( select max(sage) from S y where y.sdept=x.sdept )7 統(tǒng)計(jì)“CS”系學(xué)生的人數(shù)； select COUNT(sno) from S where(sdept=計(jì)算機(jī))8 統(tǒng)計(jì)各系學(xué)生的人數(shù)，結(jié)果按升序排列； select sdept, COUNT(sno) from S group by sdept order by COUNT(sno) asc9 按系統(tǒng)計(jì)各系學(xué)生的平均年齡，結(jié)果按降序排列；

14、 select sdept,avg(sage) from S group by sdept order by avg(sage) desc10 查詢(xún)每門(mén)課程的課程名； select cno,cname from C11 查詢(xún)無(wú)先修課的課程的課程名和學(xué)時(shí)數(shù)； select cname,ccredit from C where cpno is null12 統(tǒng)計(jì)無(wú)先修課的課程的學(xué)時(shí)總數(shù)； Select sum(ccredit) from C where cpno is null 13 統(tǒng)計(jì)每位學(xué)生選修課程的門(mén)數(shù)、學(xué)分及其平均成績(jī)； select SC.sno ,COUNT(SC.cno),avg(

15、SC.grate),sum(C.ccredit)from SC,C where SC.cno=C.cno group by SC.sno14 統(tǒng)計(jì)選修每門(mén)課程的學(xué)生人數(shù)及各門(mén)課程的平均成績(jī)； select cno,COUNT(sno),AVG(grate) from SC group by cno15 查詢(xún)選修了“1”或“2”號(hào)課程的學(xué)生學(xué)號(hào)和姓名； select S.sno,sname from S,SC where S.sno=SC.sno and cno in (1,2)16 查詢(xún)選修了“1”和“2”號(hào)課程的學(xué)生學(xué)號(hào)和姓名； select S.sno,sname from S,SC wh

16、ere S.sno=SC.sno and cno=1 and SC.sno in(select sno from SC where cno=2)17 查詢(xún)選修了“數(shù)據(jù)庫(kù)系統(tǒng)”且成績(jī)?cè)?0分以下的學(xué)生的學(xué)號(hào)、姓名和成績(jī)； select S.sno,sname, SC.grate from S,C,SC where S.sno=SC.sno and SC.cno=C.cno and cname=數(shù)據(jù)庫(kù)and SC.grate321 查詢(xún)選修課程成績(jī)至少有一門(mén)在80分以上的學(xué)生學(xué)號(hào)； select distinct sno from SC where grate 8022 查詢(xún)選修課程成績(jī)均在80分

17、以上的學(xué)生學(xué)號(hào)； select sno from SC group by sno having min(grate)8023 查詢(xún)選修課程平均成績(jī)?cè)?0分以上的學(xué)生學(xué)號(hào)；select sno from SC group by sno having AVG(grate)80 針對(duì)SPJ數(shù)據(jù)庫(kù)中的四個(gè)表S，P，J，SPJ，完成教材P74-75-習(xí)題5中的查詢(xún)及教材P127-習(xí)題5中的查詢(xún)（可選）。P75： (1)、select sno from SPJ where jno=j1 （2）、select sno from SPJ where jno=j1 and pno=p1 （3）、select s

18、no from SPJ,P where P.pno=SPJ.pno and jno=j1 and P.pno in (select pno from P where color=紅 ) （4）、select jno from SPJ,S,P where S.sno=SPJ.sno and SPJ.pno=P.pno and P.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=s1P127（1）、

19、select sname,city from S（2）、select pname,color,weight from P（3） 、select jno from SPJ where sno=s1 （4） 、select pname,qty from SPJ,P where SPJ.pno=P.pno and SPJ.jno=j2(5) 、select pno from SPJ,S where S.sno=SPJ.sno and S.sno in(select S.sno from S where S.city=上海 )（6）、select jname from SPJ ,S ,J where S.sno=SPJ.sno an