數(shù)據(jù)庫原理SQL語句查詢練習[驕陽教育]

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

2、掌握關系數(shù)據(jù)庫的各種查詢方法，理解其含義，包括嵌套查詢、連接查詢等；6、對關系數(shù)據(jù)庫進行查詢，包括嵌套查詢、連接查詢；二、實驗環(huán)境硬件平臺：PC；軟件平臺：Windows 2000 / MICROSOFT SQLSERVER；三、實驗內容對象的設計與建立用SQL命令和可視化工具（可選）完成以下操作：1. 創(chuàng)建學生-課程數(shù)據(jù)庫:create database xueshengkecheng12創(chuàng)建學生關系表S，其中Sno是主碼學號姓名性別年齡所在系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)建課程關系表C，其中Cno是主碼，Cpno是外碼：課程號課程名先行課學分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)建課程關系表SC，其中(Sno,Cno)是主碼，Sno和Cno分別是外碼：學號課程號成績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ù)更新：對已建好的各表輸入適當?shù)臄?shù)據(jù)并練習數(shù)據(jù)的刪除和修改，注意檢測鍵碼約束、外碼約束、非空約束、唯一約束、取值約束等各種約束在輸入數(shù)據(jù)及數(shù)據(jù)的刪除、修改時的作用，結合課堂教學體會其含義。具體內容：1 將示例數(shù)據(jù)分別插入表S、C、SC；insert into S values(13051701,夏雨,女,計算機,1995.1.1,20)insert into S values(13051702,蘇皇,男,軟件,1996.11.12,19)insert into S value

6、s(13051703,王五,男,計算機,1995.8.4,20)insert into S values(13051704,趙茜,女,計科,1994.3.3,21)insert into S values(13051705,錢呈,男,計科,1993.4.7,22)insert into C values(1,數(shù)據(jù)庫,null,4)insert into C values(2,離散,1,3)insert into C values(3,操作系統(tǒng),2,4)insert into C values(4,程序設計,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中插入某學生的選課信息（如：學號為“S1”，課程號為“c1”，成績待定）； insert into SC values(13051703,3,null)3 將“CS”系全體學生的成績置零； update SC set grate=0 where 計算

8、機=(select sdept from S where S.sno=SC.sno)4 將學號為“S1”的學生的學號修改為“S001” Delete from SC where sno=13051705 delete from S where sno=13051705;insert into S values(1305170005,錢呈,男,計科,1993.4.7,22)5 把平均成績大于80分的男生學號和平均成績存入表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ù)結構”的學生的成績提高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ù)庫)7 把選修了“C3”號課程，且成績低于該門課程的平均成績的學生成績提高5%； update

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

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ù)查詢：在已做過的實驗基礎上，練習查詢語句的使用，包括簡單查詢、嵌套查詢、連接查詢、分組查詢、排序查詢、聚合查詢等；練習，體會各種查詢的異同及相互之間的轉換，體會各種查詢的執(zhí)行過程，為簡單綜合應用打下良好的基礎。具體內容，在表S，C，SC上完成以下查詢：1 查詢學生的基本信息； SELECT * from S2 查詢“CS”系學生的基

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

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

14、 select sdept,avg(sage) from S group by sdept order by avg(sage) desc10 查詢每門課程的課程名； select cno,cname from C11 查詢無先修課的課程的課程名和學時數(shù)； select cname,ccredit from C where cpno is null12 統(tǒng)計無先修課的課程的學時總數(shù)； Select sum(ccredit) from C where cpno is null 13 統(tǒng)計每位學生選修課程的門數(shù)、學分及其平均成績； 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)計選修每門課程的學生人數(shù)及各門課程的平均成績； select cno,COUNT(sno),AVG(grate) from SC group by cno15 查詢選修了“1”或“2”號課程的學生學號和姓名； select S.sno,sname from S,SC where S.sno=SC.sno and cno in (1,2)16 查詢選修了“1”和“2”號課程的學生學號和姓名； 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 查詢選修了“數(shù)據(jù)庫系統(tǒng)”且成績在60分以下的學生的學號、姓名和成績； select S.sno,sname, SC.grate from S,C,SC where S.sno=SC.sno and SC.cno=C.cno and cname=數(shù)據(jù)庫and SC.grate321 查詢選修課程成績至少有一門在80分以上的學生學號； select distinct sno from SC where grate 8022 查詢選修課程成績均在80分

17、以上的學生學號； select sno from SC group by sno having min(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 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