數(shù)據(jù)庫原理及應(yīng)用實驗作業(yè)參考答案

1、數(shù)據(jù)庫原理與應(yīng)用實驗報告參考答案實驗1 數(shù)據(jù)庫的建立修改與刪除實驗2 表結(jié)構(gòu)的建立修改刪除及完整性約束條件定義實驗3 數(shù)據(jù)查詢與更新完成以下實驗報告：數(shù)據(jù)庫原理與應(yīng)用實驗報告院名專業(yè)年級班級學(xué)號姓名教師成績一、實驗題目實驗3 數(shù)據(jù)查詢與更新二、實驗環(huán)境操作系統(tǒng)：Windows XP。數(shù)據(jù)庫管理系統(tǒng)：MS SQL Server 2000或MS SQL Server 2008。三、實驗?zāi)康?1掌握SELECT語句的語法、語義及語用。2掌握基于單表的查詢方法。3掌握基于多表的查詢方法。4掌握相關(guān)與不相關(guān)的嵌套查詢。5掌握集合查詢。6掌握插入(INSERT)、修改(UPDATE)和刪除(DELETE)

2、語句的使用。四、實驗內(nèi)容 (一) 規(guī)定內(nèi)容1. 單表查詢USE ST-3_1 查詢?nèi)w學(xué)生的詳細記錄。select * from student-3_2 查詢選修了課程的學(xué)生學(xué)號。SELECT DISTINCT sno FROM sc-3_3 將“學(xué)生”表中的sno、sname這2列合并為1列snosname輸出(不改變表中存儲的內(nèi)容)，其余列不變。select sno+sname snosname,ssex,sage,sdeptfrom student-3_4 查詢年齡不在2023歲之間的學(xué)生姓名、系別和年齡。方法1：SELECT sname,sdept,sageFROM studentWH

3、ERE sage NOT BETWEEN 20 AND 23; 方法2： SELECT sname,sdept,sage FROM student WHERE sage23;-3_5 查詢計算機科學(xué)系(cs)、數(shù)學(xué)系(ma)和信息系(is)學(xué)生的姓名和性別。SELECT sname,ssexFROM studentWHERE sdept IN ( cs,ma,is );-3_6 查詢所有姓“劉”學(xué)生的姓名、學(xué)號和性別。SELECT sname,sno,ssexFROM studentWHERE sname LIKE 劉%;-3_7 查詢名字中第2個字為陽字的學(xué)生的姓名和學(xué)號。SELECT sn

4、ame,snoFROM studentWHERE sname LIKE _陽%;-3_8 某些學(xué)生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績。 -查詢?nèi)鄙俪煽兊膶W(xué)生的學(xué)號和相應(yīng)的課程號。SELECT sno,cno FROM scWHERE grade IS NULL;-3_9 查詢計算機系年齡在20歲以下的學(xué)生姓名。SELECT snameFROM studentWHERE sdept=cs AND sage=3 -也可為:COUNT(cno)=3-3_19 查詢有2門以上課程是80分以上的學(xué)生的學(xué)號及(80分以上的)課程數(shù)SELECT sno 學(xué)號,COUNT(*) 80分以上

5、的課程數(shù)FROM scWHERE grade=80GROUP BY sno HAVING COUNT(*)=2 2. 連接查詢-自然連接-3_20 查詢每個學(xué)生的學(xué)號、姓名、課號及成績。SELECT student.sno,sname,cno,grade FROM student,sc WHERE student.sno = sc.sno-左外連接-3_21 查詢每個學(xué)生的學(xué)號、姓名、課號及成績(包括沒有選修課程的學(xué)生)。方法1：SELECT student.sno,sname,cno,gradeFROM student,scwhere student.sno *= sc.sno方法2：SEL

6、ECT student.sno,sname,cno,gradeFROM student LEFT JOIN sc ON student.sno = sc.sno運行結(jié)果：sno sname cno grade - - - - 200215121 李勇 1 97.0200215121 李勇 2 40.0200215121 李勇 3 93.0200215122 劉晨 2 95.0200215122 劉晨 3 55.0200215123 王敏 NULL NULL200215124 張立 NULL NULL-3_22 查詢所有學(xué)生選修課程的成績。包括沒有選課的學(xué)生。列出學(xué)號、姓名、課號、課名、成績。S

7、ELECT student.sno,sname,o,cname,gradeFROM sc JOIN course ON o=o RIGHT JOIN student ON student.sno = sc.sno運行結(jié)果：sno sname cno cname grade - - - - - 200215121 李勇 1 數(shù)據(jù)庫 97.0200215121 李勇 2 數(shù)學(xué) 40.0200215121 李勇 3 信息系統(tǒng) 93.0200215122 劉晨 2 數(shù)學(xué) 95.0200215122 劉晨 3 信息系統(tǒng) 55.0200215123 王敏 NULL NULL NULL200215124 張

8、立 NULL NULL NULL-自身連接: 一個表與其自己進行連接，稱為表的自身連接-3_23 查詢每一門課的間接先修課（即先修課的先修課）SELECT FIRST.cno 課號,SECOND.cpno 間接先修課FROM course FIRST,course SECONDWHERE FIRST.cpno = SECOND.cno-復(fù)合條件連接-3_24 查詢選修2號課程且成績在90分以上的所有學(xué)生的學(xué)號、姓名。SELECT student.sno, sname FROM student,sc WHERE student.sno = sc.sno /* 連接謂詞*/ AND o=2 AND

9、 sc.grade 90 /* 其他限定條件*/-多表連接-3_25 查詢每個學(xué)生的學(xué)號、姓名、課名及成績。SELECT student.sno,sname,cname,gradeFROM student,sc,courseWHERE student.sno = sc.sno and o = o運行結(jié)果：sno sname cname grade - - - - 200215121 李勇 數(shù)據(jù)庫 97.0200215121 李勇 數(shù)學(xué) 40.0200215121 李勇 信息系統(tǒng) 93.0200215122 劉晨 數(shù)學(xué) 95.0200215122 劉晨 信息系統(tǒng) 55.0 3 嵌套查詢-3_26

10、 查詢與“劉晨”在一個系學(xué)習(xí)的學(xué)生。-方法1: 不相關(guān)子查詢(子查詢的查詢條件不依賴于父查詢)select sno,sname,sdeptfrom student where sdept in -當內(nèi)查詢結(jié)果最多只有一個值時可用=代替in (select sdept from student where sname=劉晨) -3_27 查詢所有姓名相同的學(xué)生。-方法1: 不相關(guān)子查詢(子查詢的查詢條件不依賴于父查詢)select *from studentwhere sname in (select sname from student group by sname having count(

11、*)1)order by sname,sno -3_28 查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號和姓名。-方法1：用嵌套查詢(不相關(guān)子查詢)SELECT sno,sname -最后在sudent關(guān)系中取出Sno和SnameFROM student WHERE sno IN (SELECT sno -然后在sc中找出選修了“信息系統(tǒng)”所在課號的學(xué)生學(xué)號 FROM sc WHERE cno IN (SELECT cno -首先在curse關(guān)系中找出“信息系統(tǒng)”的課程號 FROM course WHERE cname=信息系統(tǒng))-3_29 找出每個學(xué)生超過他選修課程平均成績的學(xué)號、課號及成績。SE

12、LECT x.* FROM sc x WHERE grade=(SELECT AVG(grade) FROM sc y WHERE y.sno=x.sno)運行結(jié)果：sno cno grade - - - 200215121 1 97.0200215121 3 93.0200215122 2 95.0-3_30 查詢其他系中比計算機科學(xué)系某一學(xué)生年齡小的學(xué)生學(xué)號、姓名和年齡。-方法1：用any謂詞 select sname,sage from student where sageany (select sage from student where sdept=cs) and sdeptcs

13、/* 注意這是父查詢塊中的條件*/-3_31 查詢其他系中比計算機科學(xué)系所有學(xué)生年齡都小的學(xué)生學(xué)號、姓名和年齡。select sname,sage from student where sageall (select sage from student where sdept=cs) and sdeptcs-3_32 查詢所有選修了1號課程的學(xué)生學(xué)號、姓名。SELECT sno,sname FROM student WHERE EXISTS (SELECT * FROM sc WHERE sno=student.sno AND cno=1); -相關(guān)子查詢-3_33 查詢沒有選修1號課程的學(xué)生

14、學(xué)號、姓名。-方法1：相關(guān)子查詢 SELECT sno,sname FROM student WHERE NOT EXISTS (SELECT * -此處*可換為sno FROM sc WHERE sno=student.sno AND cno=1); -3_34 查詢選修了全部課程的學(xué)生姓名。SELECT sname -查詢這樣的學(xué)生y FROM student WHERE NOT EXISTS -不存在課程x (SELECT * FROM course WHERE NOT EXISTS -學(xué)生y不選修x (SELECT * FROM sc WHERE sno=student.sno -表S

15、C與Student自然連接AND cno=o) -表SC與course自然連接-3_35 查詢至少選修了學(xué)生200215122選修的全部課程的學(xué)生號碼。SELECT DISTINCT sno -查詢這樣的學(xué)生的xFROM sc SCXWHERE NOT EXISTS -不存在這樣的課程y (SELECT * FROM sc SCY WHERE SCY.sno=200215122 AND -學(xué)生200215122選修了yNOT EXISTS -學(xué)生x沒有選修y (SELECT * FROM sc SCZ WHERE SCZ.sno=SCX.sno AND SCZ.cno=SCY.cno)-3_3

16、6 將表sc復(fù)制到sc1中。use STIF EXISTS(SELECT name FROM sysobjects WHERE name = sc1 AND type = U) DROP TABLE sc1 GOSelect * Into sc1 from sc -復(fù)制表-3_37 將表sc的結(jié)構(gòu)復(fù)制到sc2中。use STIF EXISTS(SELECT name FROM sysobjects WHERE name = sc2 AND type = U) DROP TABLE sc2 GOselect * into sc2 from sc -復(fù)制表結(jié)構(gòu)where sno is null-或

17、Select * into sc2 from sc -復(fù)制表結(jié)構(gòu)Where not exists (select * from sc) 4 集合查詢-3_38 查詢計算機系的學(xué)生及年齡不大于19歲的學(xué)生，并按年齡降序排列。-方法1：并操作(UNION)SELECT *FROM studentWHERE sdept=csUNION SELECT *FROM student WHERE sage=19Order by sage desc-3_39 查詢選修了課程1或者選修了課程2的學(xué)生學(xué)號。SELECT snoFROM scWHERE cno=1UNION SELECT snoFROM scWHE

18、RE cno=2-3_40 查詢既選修了1號又選修了2號課程的學(xué)生學(xué)號。本查詢可表述為：查詢至少選修了1、2號課程的學(xué)生學(xué)號。-方法1：集合交操作，查詢選修課程1的學(xué)生集合與選修課程2的學(xué)生集合的交集SELECT snoFROM scWHERE cno=1 INTERSECTSELECT snoFROM scWHERE cno=2-方法2：SELECT snoFROM scWHERE cno=1 AND sno IN (SELECT sno FROM sc WHERE cno=2)-3_41 查詢計算機系的學(xué)生與年齡不大于19歲的學(xué)生的交集。-方法1：交操作SELECT *FROM stude

19、ntWHERE sdept=cs INTERSECTSELECT *FROM studentWHERE sage=19-方法2：標準SQL中沒有提供集合交操作，但可用其他方法間接實現(xiàn)。-本例實際上是：查詢計算機系年齡不大于19歲的學(xué)生SELECT *FROM studentWHERE sdept=cs AND sage19-方法2：標準SQL中沒有提供集合差操作，但可用其他方法間接實現(xiàn)。-本例實際上是：查詢計算機系年齡不大于19歲的學(xué)生SELECT *FROM studentWHERE sdept=cs AND sage=60Group by student.sno,sname-3_44 學(xué)士

20、學(xué)位授予條件為：至少選修了1、3、4號3門學(xué)位課程，每門學(xué)位課必須及格且學(xué)位課平均成績在75分以上。查詢cs系可授予學(xué)士學(xué)位的學(xué)生名單。select *from studentwhere sdept=cs and sno in (select sno from sc where cno in(1,3,4) and grade=60 group by sno having count(sno)=3 and avg(grade)=75)-3_45 學(xué)士學(xué)位授予條件為：至少選修了數(shù)據(jù)結(jié)構(gòu),數(shù)據(jù)庫,操作系統(tǒng)3門學(xué)位課程，每門學(xué)位課必須及格且學(xué)位課平均成績在75分以上。查詢cs系可授予學(xué)士學(xué)位的學(xué)生名單

21、。select *from studentwhere sdept=cs and sno in (select sno from sc where grade=60 and cno in (select cno from course where cname in(數(shù)據(jù)結(jié)構(gòu),數(shù)據(jù)庫,操作系統(tǒng)) group by sno having count(sno)=3 and avg(grade)=75)5 插入(INSERT)、修改(UPDATE)和刪除(DELETE)語句的使用-3_46 插入一條選課記錄( 200215123,1)。 if not exists(select * from sc wh

22、ere sno=200215123 and cno=1) INSERT INTO sc(sno,cno) VALUES (200215123,1) -新插入的記錄在Grade列上取空值-3_47 對每一個系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫。 -第1步：建表，第2步：插入子查詢結(jié)果 -第一步：建表 use ST IF EXISTS(SELECT name FROM sysobjects WHERE name = DeptAge AND type = U) DROP TABLE DeptAge -若表Deptage已存在,則先刪除再重建 GO CREATE TABLE DeptAge (sd

23、ept CHAR(2), -系名 avgage SMALLINT) -學(xué)生平均年齡 -第二步：插入子查詢結(jié)果 INSERT INTO DeptAge(sdept,avgage) SELECT sdept,AVG(sage) FROM student GROUP BY sdept select * from DeptAge-3_48 將所有學(xué)生的年齡增加1歲。 UPDATE student SET sage=sage+1-3_49 對計算機科學(xué)系(cs)全體學(xué)生選修2號課程分數(shù)=36的按10*sqrt(grade)計算。 update sc set grade=10*sqrt(grade) wh

24、ere cno=2 and grade=36 and cs=(select sdept from student where student.sno=sc.sno) -相關(guān)子查詢-3_50 刪除is系所有學(xué)生的選課記錄。DELETE FROM sc WHERE is=(SELECT sdept FROM student WHERE student.sno=sc.sno);-3_51 刪除只選1門課且成績不及格學(xué)生的選課記錄。 DELETE FROM sc WHERE sno in (SELECT sno FROM sc WHERE grade60 GROUP BY sno HAVING COU

25、NT(*)=1)-3_52 刪除計算機系只選1門課且成績不及格學(xué)生的選課記錄。DELETE FROM sc WHERE sno IN (SELECT sno FROM student WHERE sdept=cs AND sno IN (SELECT sno FROM sc WHERE grade45 or 月工資45; UNION Select 姓名,年齡,月工資 from 職工 Where 月工資=90-4_10 先建立高考(考號，姓名，語文，數(shù)學(xué)，英語，綜合)基本表, 在此基礎(chǔ)上建立含總分列的視圖。IF EXISTS(SELECT name FROM sysobjects WHERE n

26、ame = GK) drop table GK -若GK已存在,則刪除 go create table GK (考號 char(5) primary key, 姓名 char(8), 語文 decimal(5,1) check(語文 between 0 and 150), 數(shù)學(xué) decimal(5,1) check(數(shù)學(xué) between 0 and 150), 英語 decimal(5,1) check(英語 between 0 and 150), 綜合 decimal(5,1) check(綜合 between 0 and 300)IF EXISTS(SELECT name FROM sys

27、objects WHERE name = V_gk) drop view V_gk -若GH_view已存在,則刪除 go create view V_gk (考號,姓名,語文,數(shù)學(xué),英語,綜合,總分) -總分為虛擬列 as select 考號,姓名,語文,數(shù)學(xué),英語,綜合,語文+數(shù)學(xué)+英語+綜合 from GK運行以上程序建立高考表“GK”及視圖“V_gk”。-4_11 將學(xué)生的學(xué)號及他的平均成績定義為一個視圖。IF EXISTS (SELECT name FROM sysobjects WHERE name=V_avg) drop view V_avg -若V_avg已存在,則刪除 GO

28、CREATE VIEW V_avg(sno,gavg) AS SELECT sno,AVG(grade) FROM sc GROUP BY sno -分組視圖-4_12 將student表中所有女生記錄定義為一個視圖。方法1：IF EXISTS(SELECT name FROM sysobjects WHERE name =V_fs) drop view V_fs -若V_fs已存在,則刪除 GO CREATE VIEW V_fs (sno,sname,sex,age,dept) AS SELECT sno,sname,ssex,sage,sdept FROM student WHERE ss

29、ex=女 -當基表Student增加屬性列時，不會破壞Student表與V_fs視圖的映象關(guān)系。-4_13 建立視圖V_good (修課成績在平均成績之上的元組)。IF EXISTS(SELECT name FROM sysobjects WHERE name = V_good) drop view V_good -若V_good已存在,則刪除 GO CREATE VIEW V_good AS SELECT sno,cno,grade FROM sc WHERE grade (SELECT AVG(grade) -該視圖應(yīng)屬行列子集視圖 FROM sc)-4_14 建立1號課程的選課視圖，并要

30、求透過該視圖進行的更新操作只涉及1號課程， -同時對該視圖的任何操作只能在工作時間進行。IF EXISTS(SELECT name FROM sysobjects WHERE name = V_sc) drop view V_sc -若V_sc已存在,則刪除 go CREATE VIEW V_sc AS SELECT sno,cno,grade FROM sc WHERE cno=1 and datepart(weekday,GETDATE() BETWEEN 2 and 6 -星期一至星期五 and datepart(hour,GETDATE() BETWEEN 9 and 17 -9:00

31、至17:00 WITH CHECK OPTION3視圖的查詢與更新-4_15 在信息系學(xué)生的視圖中找出所有女學(xué)生。SELECT * FROM V_is1 WHERE ssex=女-4_16 查詢信息系選修了1號課程的學(xué)生SELECT V_is1.* FROM V_is1,sc WHERE V_is1.sno=sc.sno AND o=1-4_17 在V_avg視圖中查詢平均成績在90分以上的學(xué)生學(xué)號和平均成績SELECT * FROM V_avg WHERE gavg=90-4_18 將信息系學(xué)生視圖V_is1中學(xué)號200215124的學(xué)生姓名改為“張三”。UPDATE V_is1 SET s

32、name= 張三 WHERE sno= 200215124-4_19 向信息系學(xué)生視圖V_is1中插入一個新的學(xué)生記錄：200215129，趙新，女，18，isINSERT INTO V_is1 VALUES(200215129,趙新,女,18,is)-4_20 刪除視圖V_is1中學(xué)號為200215124的記錄DELETE FROM V_is1 WHERE sno=200215124(二) 自定內(nèi)容五、出現(xiàn)的問題及解決方法實驗5 T_SQL基本程序設(shè)計完成以下實驗報告：數(shù)據(jù)庫原理與應(yīng)用實驗報告院名專業(yè)年級班級學(xué)號姓名教師成績一、實驗題目實驗5 Transact-SQL基本程序設(shè)計二、實驗環(huán)境

33、操作系統(tǒng)：Windows XP。數(shù)據(jù)庫管理系統(tǒng)：MS SQL Server 2000 或MS SQL Server 2008。三、實驗?zāi)康?1掌握Transact-SQL語言中順序、分支、循環(huán)三種控制結(jié)構(gòu)的語句。2掌握Transact-SQL語言中系統(tǒng)函數(shù)的使用方法。3學(xué)會使用Transact-SQL語句編寫基本程序。四、實驗內(nèi)容 (一) 規(guī)定內(nèi)容5_1 求方程ax2+bx+c=0的根。declare a real,b real,c realdeclare da real,p real,q realselect a=5,b=5,c=3set da=b*b-4*a*cif da=0 begin

34、print 方程有如下2個實根： print x1=+ltrim(str(-b+sqrt(da)/(2*a),10,2) print x2=+ltrim(str(-b-sqrt(da)/(2*a),10,2) endelse begin select p=-b/(2*a),q=sqrt(-da)/(2*a) print 方程有如下2個共軛虛根： print x1=+ltrim(str(p,10,2)+ltrim(str(q,10,2)+i print x2=+ltrim(str(p,10,2)+-+ltrim(str(q,10,2)+i end方程有如下2個共軛虛根：x1=-0.50+0.59

35、i 5_2 將某待查學(xué)生姓名賦給變量s，查詢該學(xué)生記錄，若不存在則輸出“查無此人！”。declare s char(8)set s=張三if exists (select * from student where sname=s) select * from student where sname=selse print 查無此人！ 5_3 查詢?nèi)w學(xué)生的sno、sname、ssex、sdept，對于ssex，若是“男” 則顯示“男生”，若是“女”則顯示“女生”。select sno,sname, ssex=case ssex when 男 then 男生 when 女 then 女生 end

36、,sdeptfrom student5_4 對選修表按成績的分數(shù)段顯示A(90100)、B(8089)、C(7079)、D(6069)、E(059)五個等級。SELECT sno 學(xué)號,cno 課號, 績等級=CASE When grade=90 then A When grade =80 then B When grade =70 then C When grade =60 then D Else E EndFROM sc 5_5 求100以內(nèi)所有奇數(shù)的和。declare i int, s intselect i=1,s=0while i=100 select s=s+i,i=i+2sele

37、ct s=s5_6 求語句“I am student.”中各字母的ASC碼。declare i int,n int, str char(60),s char(1)select str=I am student.,n=len(str),i=1print 字母 ASCwhile i=a and s=A and s=Z) continue print s+str(ascii(s) end 運行結(jié)果：字母 ASCI 73a 97m 109s 115t 116u 117d 100e 101n 110t 1165_8 在10秒鐘內(nèi)，每隔5秒查看活動數(shù)據(jù)庫文件的屬性。(提示：調(diào)用sp_helpfile)DE

38、CLARE start datetimeSET start=GETDATE()WHILE datediff(ss,start, GETDATE()=10 BEGIN WAITFOR DELAY 0:0:5; EXECUTE sp_helpfile; END; 5_9 對選修2號課分數(shù)作如下處理：若2號課平均分低于70分則普加5分，如此反復(fù)循環(huán)，當最高分為100分時退出循環(huán)。Use STDeclare gradeavg decimal(5,1)While (select avg(grade) from sc where cno=2)100 break Update sc set grade=gr

39、ade+5 where cno=2 endselect gradeavg=avg(grade) from sc where cno=2select 2號課平均成績=gradeavg5_10 若李平(12341288)有20000元以上，則李平向王中華(12342288)轉(zhuǎn)帳20000元。USE STIF OBJECT_ID(存款,U) IS NOT NULL DROP TABLE 存款CREATE TABLE 存款 (帳號 CHAR(8) PRIMARY KEY, 姓名 CHAR(8), 余額 SMALLMONEY)INSERT INTO 存款 VALUES(12341288,李平,30000

40、)INSERT INTO 存款 VALUES(12342288,王中華,20000)BEGIN TRANSACTION t1 IF (SELECT 余額 FROM 存款 WHERE 帳號=12341288)=20000 BEGIN UPDATE 存款 SET 余額=余額-20000 WHERE 帳號=12341288 UPDATE 存款 SET 余額=余額+20000 WHERE 帳號=12342288 END ELSE PRINT 李平(12345688)存款余額不足20000元，轉(zhuǎn)帳失??！ COMMIT TRANSACTION t15_11 馬鮮花與牛得草是一對好朋友，他們要求到同一所學(xué)校

41、讀書。USE STBEGIN TRANSACTIONIF EXISTS(SELECT sname FROM student WHERE sname IN(馬鮮花,牛得草)DELETE FROM student WHERE sname IN (馬鮮花,牛得草)insert into student values(200215128,馬鮮花, 女,1994-06-10,162,MA,null);insert into student values(200215129,牛得草, 男,1993-12-10,170,IS,null);COMMIT TRANSACTIONSELECT * FROM stu

42、dent(二) 自定內(nèi)容五、出現(xiàn)的問題及解決方法實驗6 存儲過程完成以下實驗報告：數(shù)據(jù)庫原理與應(yīng)用實驗報告院名專業(yè)年級班級學(xué)號姓名教師成績一、實驗題目實驗6 存儲過程二、實驗環(huán)境操作系統(tǒng)：Windows XP。數(shù)據(jù)庫管理系統(tǒng)：MS SQL Server 2000 或MS SQL Server 2008。三、實驗?zāi)康?1理解建立與調(diào)用存儲過程語句的格式和功能。2掌握各種存儲過程的建立與調(diào)用方法。3能根據(jù)數(shù)據(jù)庫應(yīng)用的需要編寫有實用價值的存儲過程。四、實驗內(nèi)容 (一) 規(guī)定內(nèi)容6_2 建立存儲過程“P2_某系學(xué)生學(xué)分”：查詢某系學(xué)生的學(xué)號、姓名、學(xué)分，只有及格課程才能拿學(xué)分。USE STIF EXI

43、STS(SELECT name FROM sysobjects WHERE name = P2_某系學(xué)生學(xué)分) DROP PROCEDURE P2_某系學(xué)生學(xué)分GO CREATE PROCEDURE P2_某系學(xué)生學(xué)分 x char(2) -輸入?yún)?shù)AS select sc.sno 學(xué)號,sname 姓名,sum(ccredit) 學(xué)分 from sc inner join course on o=o inner join student on sc.sno=student.sno where grade=60 and sdept=x group by sc.sno,sname運行以上程序建立

44、存儲過程：P2_某系學(xué)生學(xué)分存儲過程調(diào)用：EXECUTE P2_某系學(xué)生學(xué)分 cs運行結(jié)果：學(xué)號 姓名 學(xué)分 - - -200215121 李勇劉晨 26_3 建立存儲過程“P3_某系學(xué)生學(xué)分”：查詢某系學(xué)生的學(xué)號、姓名、學(xué)分，只有及格課程才能拿學(xué)分。未選修課程的學(xué)生及所選課程全部不及格的學(xué)生學(xué)分為0。set nocount onuse stIF EXISTS(SELECT name FROM sysobjects WHERE name = P3_某系學(xué)生學(xué)分) DROP PROCEDURE P3_某系學(xué)生學(xué)分GO CREATE PROCEDURE P3_某系學(xué)生學(xué)分

45、 x char(2) -輸入?yún)?shù)ASselect sc.sno 學(xué)號,sname 姓名,sum(ccredit) 學(xué)分 into 學(xué)生學(xué)分 from sc inner join course on o=o inner join student on sc.sno=student.sno where grade=60 and sdept=x group by sc.sno,snameinsert into 學(xué)生學(xué)分(學(xué)號,姓名) -沒有學(xué)分的學(xué)生其學(xué)分為0 select sno,sname from student where sdept=x and sno not in (select 學(xué)號

46、from 學(xué)生學(xué)分)update 學(xué)生學(xué)分 set 學(xué)分=0 where 學(xué)分 is nullselect * from 學(xué)生學(xué)分 order by 學(xué)號drop table 學(xué)生學(xué)分運行以上程序建立存儲過程：P3_某系學(xué)生學(xué)分存儲過程調(diào)用：EXECUTE P3_某系學(xué)生學(xué)分 cs運行結(jié)果：學(xué)號 姓名 學(xué)分 - - - 200215121 李勇劉晨 2200215124 張立 06_4 建立文本加密存儲過程“P4_所有系學(xué)生學(xué)分”：查詢所有系學(xué)生的學(xué)號、姓名、學(xué)分，只有及格課程才能拿學(xué)分。未選修課程的學(xué)生及所選課程全部不及格的學(xué)生學(xué)分為0。Set nocount on

47、USE STIF EXISTS(SELECT name FROM sysobjects WHERE name = P4_所有系學(xué)生學(xué)分) DROP PROCEDURE P4_所有系學(xué)生學(xué)分GO CREATE PROCEDURE P4_所有系學(xué)生學(xué)分 -無參過程 WITH ENCRYPTIONASBEGIN DECLARE i int,n int,dn char(2) DECLARE deptTableVar TABLE -定義臨時表變量 (no int identity(1,1) not null, Dept char(20) INSERT deptTableVar(Dept) SELECT

48、DISTINCT sdept FROM student -將學(xué)生表中的系名插入臨時表中 SELECT n=COUNT(*) FROM deptTableVar -統(tǒng)計系總數(shù) SET i=1 WHILE I=60 and cno in (select cno from course where cname in(數(shù)據(jù)結(jié)構(gòu),數(shù)據(jù)庫,操作系統(tǒng)) group by sno having count(sno)=3 and avg(grade)=75)運行以上程序建立存儲過程：P5_某系學(xué)士名單調(diào)用存儲過程：EXECUTE P5_某系學(xué)士名單 cs 6_7 建立一個用戶定義的系統(tǒng)存儲過程“sp_查某表索引

49、”：查詢某表的所有索引，包括表名和索引名。USE masterIF OBJECT_ID(sp_某表所有索引,P) IS NOT NULL DROP PROCEDURE sp_某表所有索引GOCREATE PROCEDURE sp_某表所有索引 table varchar(30) -輸入?yún)?shù)ASSELECT 表名, 索引名FROM sysindexes INNER JOIN sysobjects ON sysindexes.id= sysobjects.idWHERE =table運行以上程序建立存儲過

50、程：sp_某表所有索引調(diào)用存儲過程：sp_某表所有索引 student6_8 建立臨時存儲過程“#查看庫視圖”：查看數(shù)據(jù)庫ST的所有視圖。USE tempdbIF OBJECT_ID(#查看庫視圖,P) IS NOT NULL DROP PROCEDURE #查看庫視圖GO CREATE PROCEDURE #查看庫視圖AS select * from ST.dbo.sysobjects where type=V調(diào)用臨時存儲過程，查看數(shù)據(jù)庫ST的所有視圖：EXECUTE #查看庫視圖 6_9 查看未加密存儲過程“P3_某系學(xué)生學(xué)分”及加密存儲過程“P4_所有系學(xué)生學(xué)分”的文本。EXEC sp_

51、helptext P3_某系學(xué)生學(xué)分運行結(jié)果：顯示存儲過程“P3_某系學(xué)生學(xué)分”的定義文本。EXEC sp_helptext P4_所有系學(xué)生學(xué)分運行結(jié)果： 對象備注已加密。(二) 自定內(nèi)容五、出現(xiàn)的問題及解決方法附錄1 實驗報告模板數(shù)據(jù)庫原理與應(yīng)用實驗報告院名專業(yè)年級班級學(xué)號姓名教師成績一、實驗題目二、實驗環(huán)境三、實驗?zāi)康?四、實驗內(nèi)容 (一) 規(guī)定內(nèi)容(二) 自定內(nèi)容五、出現(xiàn)的問題及解決方法附錄2 建立學(xué)生-課程數(shù)據(jù)庫ST及表-例 建立學(xué)生-課程數(shù)據(jù)庫“ST”，將數(shù)據(jù)文件和日志文件均存入文件夾：D:SQL2000DB。-建立學(xué)生-課程數(shù)據(jù)庫ST.sqluse masterIF DB_ID(

52、NST) IS NOT NULL -若數(shù)據(jù)庫ST已存在,DROP DATABASE ST -則刪除。GOCREATE DATABASE STON (NAME=ST_Data, -主數(shù)據(jù)文件邏輯文件名 FILENAME = D:SQL2000DBST_Data.mdf, -物理文件名 SIZE = 3MB, -初始大小FILEGROWTH = 1MB, -自動增量 MAXSIZE = UNLIMITED) -最大容量LOG ON (NAME=ST_log, -日志文件邏輯文件名 FILENAME = D:SQL2000DBST_log.ldf, -物理文件名 SIZE = 2MB, -初始大小F

53、ILEGROWTH = 10%, -自動增量 MAXSIZE = UNLIMITED) -最大容量GOuse STif exists (select name from sysobjects where name=sc) drop table scif exists (select name from sysobjects where name=course) drop table courseif exists (select name from sysobjects where name=student) drop table student-建立學(xué)生表結(jié)構(gòu)create table student(sno char(9) prim