實驗二 數(shù)據(jù)的查詢、更新.doc

_實驗二 數(shù)據(jù)的查詢、更新 一、實驗目的1、掌握用戶自定義數(shù)據(jù)類型的方法2、掌握用T-SQL語句進行數(shù)據(jù)的插入、修改、刪除的方法3、熟練掌握SELECT語句，能夠運用該語句完成各種查詢二、實驗要求1、實驗前做好上機實驗的準備，針對實驗內(nèi)容，認真復習與本次實驗有關(guān)的知識，完成實驗內(nèi)容的預習準備工作；2、能認真獨立完成實訓內(nèi)容；3、實驗后做好實驗總結(jié)，根據(jù)實驗情況完成總結(jié)報告。三、實驗內(nèi)容1、用T-SQL語句，創(chuàng)建一用戶自定義數(shù)據(jù)類型：名稱為“char20”，數(shù)據(jù)類型為varchar，長度為20，允許為空。sp_addtype char20, varchar(20),null提示：sp_addtype typename=用戶自定義類型的名字 , phystype=系統(tǒng)類型名 , nulltype= not null | null , owner= 擁有該類型的用戶名 例：自定義一個名為address的類型，其所屬系統(tǒng)類型為varchar，長度為80，不能為空。sp_addtype address, varchar(80), not null 2、用T-SQL語句，建立一個“學生課程數(shù)據(jù)庫”，在此基礎(chǔ)上建立該數(shù)據(jù)庫包含的學生表，課程表，學生選修表，并向各表插入如下相應(yīng)的數(shù)據(jù)。create database 學生課程數(shù)據(jù)庫學生表:Student(Sno,Sname,Ssex,Sage,Sdept) 其中Sno 為主鍵、Ssex取值為男或女、Sage在15到30之間:SnoSnameSsexSageSdept95001李敏勇男20CS95002劉晨女19IS95003王敏女18MA95004張立男18IScreate table student(sno int primary key, sname char(10), ssex char(2) constraint ssex_ch check(ssex in(男,女), sage int constraint sage_ch check(sage=15 and sage=30), sdept char(10) ) insert into studentvalues(95001,李敏勇,男,20,CS)insert into studentvalues(95002,劉晨,女,19,IS)insert into studentvalues(95003,王敏,女,18,MA)insert into studentvalues(95004,張立,男,18,IS)課程表:Course(Cno,Cname,Cpno,Credeit,remarks) 其中Cno為主鍵、Teacher的類型為char20即為用戶定義的數(shù)據(jù)類型;CnoCnameCpnoCredit Teacher1數(shù)據(jù)庫54王芳2數(shù)學NULL2劉新3信息系統(tǒng)14劉新4操作系統(tǒng)63高升5數(shù)據(jù)結(jié)構(gòu)74宋明6數(shù)據(jù)處理NULL2張彬7Pascal語言64李磊create table course(cno int primary key, cname char(15), cpno int null, credit int,teacher char20 ) insert into coursevalues(1,數(shù)據(jù)庫,5,4,王芳)insert into coursevalues(2,數(shù)學,NULL,2,劉新)insert into coursevalues(3,信息系統(tǒng),1,4,劉新)insert into coursevalues(4,操作系統(tǒng),6,3,高升)insert into coursevalues(5,數(shù)據(jù)結(jié)構(gòu),7,4,宋明)insert into coursevalues(6,數(shù)據(jù)處理,NULL,2,張彬)insert into coursevalues(7,PASCAL語言,6,4,李磊)學生選修表:SC(Sno,Cno,Grade) 其中Sno,Cno為主鍵同時又為外鍵、Grade值在0到100;SnoCnoGrade950011929500128595001388950022909500325595004270create table sc(sno int, cno int, grade int constraint grade_ch check(grade between 0 and 100),primary key(sno,cno),constraint fk_sno foreign key (sno) references student(sno),constraint fk_cno foreign key (cno) references course(cno), ) insert into scvalues(95001,1,92)insert into scvalues(95001,2,85)insert into scvalues(95001,3,88)insert into scvalues(95002,2,90)insert into scvalues(95003,2,55)insert into scvalues(95004,2,70)3、用T-SQL語句，修改上面所建學生課程數(shù)據(jù)庫中數(shù)據(jù):1) 向?qū)W生表:Student中加入一條記錄：(95030,謝非,男,22,CS)并保存insert into studentvalues(95030,謝非,男,22,CS)2) 將李敏勇的數(shù)據(jù)庫的成績改為98分update sc set grade=98where o=(select o from course where ame=數(shù)據(jù)庫 and sno=(select sno from student where sname=李敏勇 ) )3) 刪除學生表Student中謝非的記錄并保存delete from studentwhere sname=謝非4) 能不能從Student表中刪除李敏勇學生的記錄,為什么?能不能刪除王敏, 張立兩個學生的記錄?不能， sc表中列sno是外碼，參照student表的sno列。4、用T-SQL語句，完成下面簡單的查詢1）查詢?nèi)w學生的學號、姓名及年齡.use 學生課程數(shù)據(jù)庫select *from student2）查詢?nèi)w學生的姓名, 年齡及所在系（要用小寫字母表示系名，并用“系名”來表示列名）。select sname,sage, lower(sdept) 系名from student3）查詢選修了課程的學生學號select distinct sno from sc4）查詢信息系全體學生的姓名select snamefrom studentwhere sdept=IS5）查詢所有年齡在20歲以下的學生姓名及其年齡select sname,sagefrom studentwhere sage=70order by grade desc16)查詢每個學生的學號、課程號及分數(shù)，同時統(tǒng)計每個學生的總分select sno,cno,gradefrom scorder by snocompute sum(grade) by sno17)查詢每個學生的各科分數(shù)、最高分、最低分、總分、平均分select sno,cno,gradefrom scorder by snocompute max(grade),min(grade),sum(grade),avg(grade) by sno5、用T-SQL語句完成下面的查詢1)查詢學生的總?cè)藬?shù)select COUNT(sno) 人數(shù)from student2)查詢選修了課程的學生人數(shù)select COUNT(distinct sno) 選課人數(shù)from sc3)計算選2號課程的學生平均成績select AVG(grade)平均分from scwhere cno=24)查詢選修2號課程的學生最高分數(shù)select MAX(grade) 最高分from scwhere cno=25)求各個課程號及相應(yīng)的選課人數(shù)select cno,COUNT(sno) 人數(shù)from scgroup by cno6)查詢選修了2門以上的課程的學生學號select snofrom scgroup by sno having (COUNT(cno)2)7)查詢每個學生及其選修課程的情況select student.sno,o,cnamefrom student,sc,coursewhere student.sno=sc.sno and o=o8)查詢每一門課的間接先修課(即先修課的先修課)select o,c2.cpnofrom course c1,course c2where o=o9)查詢選修2號課程且成績在90分以上（包括90分）的所有學生。select student.sno,snamefrom studentwhere student.sno in(select sc.sno from sc where cno=2 and grade=90)6. 用T-SQL語句完成下面的查詢 1)查詢與“劉晨”在同一個系學習的學生select sno,snamefrom studentwhere sname劉晨 and sdept=(select sdept from student where sname=劉晨)2)查詢選修了課程名為“數(shù)學”的學生學號和姓名select sno,snamefrom studentwhere sno in(select sno from sc where cno =(select cno from course where cname=數(shù)學 )3)查詢其它系中比信息系中某一學生年齡小的學生姓名和年齡select sname,sagefrom studentwhere sdeptIS and sage any(select sagefrom student where sdept=IS)4)查詢其它系中比計算機系所有學生年齡都小的學生姓名及年齡select sname,sagefrom studentwhere sdeptIS and sage(select sno from student where sname like劉%)and sage(select avg(sage)from studentwhere ssex=女)and ssex=男9)求年齡大于所有女同學年齡的男學生姓名和年齡。select sname,sagefrom studentwhere ssex=男 and sageall(select sage from student where ssex=女)10)檢索每一門課程成績都大于等于80分的學生學號、姓名和性別，并把檢索到的值送往另一個已存在的基本表S（SNO,SNAME,SEX）。select sno,sname,ssexinto sfrom studentwhere sno in(select sno from sc where grade=80)11)把選課數(shù)學課不及格的成績?nèi)臑榭罩怠pdate scset grade=where sno in(select sno from sc where grade60) and cno=(select cno from course where cname=數(shù)學)12)把王同學的選課信息全部刪去。delete from scwhere sno = (select sno from student where sname like 王%)13)把低于總平均成績的男同學成績提高5。update scset grade=grade*1.05where grade (select AVG(grade) from sc) and cno in(select o from student,sc where ssex = 男 and student.sno = sc.sno)14)檢索沒有選修1課程的學生學號和姓名select sno,snamefrom studentwhere sno not in(select sc.sno from sc,student where cno=1 and student.sno=sc.sno)15)檢索至少有一門課程超過學生95001一門成績的學生學號select distinct sc.snofrom sc,studentwhere sc.sno95001and sc.sno=student.snoand gradeany(select grade from sc where sno=95001)16)向?qū)W生選修課程表中插入元組“學生95003選修課程1”。insert into sc(sno,cno)values(95003,1)17)求出女同學的每一年齡組（超過10人）有多少人？要求查詢結(jié)果按人數(shù)升序排列，人數(shù)相同的按年齡降序排列。select sage,COUNT(sno) 人數(shù)from studentwhere sse