數(shù)據(jù)庫課程——設(shè)計報告.docx_第1頁
數(shù)據(jù)庫課程——設(shè)計報告.docx_第2頁
數(shù)據(jù)庫課程——設(shè)計報告.docx_第3頁
數(shù)據(jù)庫課程——設(shè)計報告.docx_第4頁
數(shù)據(jù)庫課程——設(shè)計報告.docx_第5頁
已閱讀5頁,還剩28頁未讀, 繼續(xù)免費閱讀

下載本文檔

版權(quán)說明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請進(jìn)行舉報或認(rèn)領(lǐng)

文檔簡介

數(shù)據(jù)庫課程設(shè)計報告附件1: 經(jīng)濟管理學(xué)院實驗報告 姓名:XX 班級:11電子商務(wù)1班 學(xué)號:XXXXXX 實驗成績: 課程名稱: 數(shù)據(jù)庫應(yīng)用課程設(shè)計 指導(dǎo)教師: 賴玉霞 實驗名稱: SQL Server2008數(shù)據(jù)庫應(yīng)用課程設(shè)計 2012 年 6 月 25 日 實驗?zāi)康? 1(熟練掌握數(shù)據(jù)庫設(shè)計技術(shù)。 2(熟練掌握數(shù)據(jù)庫查詢語句及其應(yīng)用。 3(掌握數(shù)據(jù)庫綜合應(yīng)用能力。 實驗環(huán)境:,數(shù)據(jù)庫管理系統(tǒng)SQL Server2008 實驗內(nèi)容與步驟: 實驗內(nèi)容: 已知選課數(shù)據(jù)庫myGrade包含學(xué)生、課程、教師、選課和授課等5個關(guān)系表,各表及其列名含義如下(主鍵已用下畫線標(biāo)記): 學(xué)生(學(xué)號,姓名,性別,出生日期,班級) 課程(課程編號,課程名稱,前修課程,課程性質(zhì),學(xué)分) 教師(教師編號,姓名,性別,出生日期,職稱) 選課(學(xué)號,課程編號,選課學(xué)期,成績) 授課(教師編號,課程編號,授課學(xué)期) 1(創(chuàng)建數(shù)據(jù)庫myGrade,在數(shù)據(jù)庫中創(chuàng)建上述5個關(guān)系數(shù)據(jù)表,要求使用非中文的表名和列名,并在各表中插入模擬數(shù)據(jù)。在建表時必須定義各表的主鍵、外鍵、CHECK等約束條件。具體要求如下: (1) 學(xué)生表和教師表中的性別取值“M”或“F”分別表示“男”或“女”;學(xué)號長度為8位,第一位以字母開頭,最后一位為性別(即F或M),其他6位為數(shù)字。 (2) 課程表中的前修課程為外鍵,它參照自己所在表中的主鍵列(即“課程編碼”);課程性質(zhì)分為“必修課”和“選修課”兩類,必修課用字母A表示,選修課用字母B表示;學(xué)分取值0.510之間。 (3) 選課學(xué)期和授課學(xué)期都為11位字符串,例如“2010-2011-1”。其中前9位表示學(xué)年(年份之間用橫桿分隔),最后一位表示某個學(xué)年中的學(xué)期序號,取值1或2。 (4) 其他列的類型、長度、外鍵及CHECK等約束條件根據(jù)選課數(shù)據(jù)庫語義自行定義。 (5) 在插入模擬數(shù)據(jù)之后,為各個外鍵中的每一列創(chuàng)建非聚集索引。 實驗步驟: 1.建立學(xué)生成績管理數(shù)據(jù)庫mygrade create database mygrade 2.數(shù)據(jù)庫mygrade的各個表的創(chuàng)建 1)學(xué)生表 Students create table students (sno nvarchar(8) constraint pk1 primary key clustered check (sno like A-Z0-90-90-90-90-90-9FM), sname nvarchar(20), gender char(2) check (gender like FM), birthdate datetime, class nvarchar(20) insert into students values (A246357F,liuxiao,F,1993-5-6,10) insert into students values (B462237M,hongtao,M,1993-6-2,5) insert into students values (C346723M,xiaren,M,19932-6-30,7) insert into students values (D345854F,fanfan,F,1993-8-14,9) insert into students values (E463849F,geguitar,F,1993-7-26,8) insert into students values (E888338M,xuanxu,M,1993-1-30,8) insert into students values (D673945F,dawei,F,1993-4-6,1) insert into students values (A362956M,fengdong,M,1993-7-6,10) insert into students values (C346234M,heigirl,M,1993-12-4,13) insert into students values (F345956F,qianjiang,F,1991-10-3,7) create nonclustered index indexfk1 on courses(pcno) 2)課程表Courses create table courses (cno nvarchar(10) constraint pk2 primary key clustered, cname char(20), pcno nvarchar(10) constraint fk1 references courses(cno), ctype char(2) check (ctype like AB), credit float check (credit between 0.5 and 10) insert into courses values (c4,shujuku,c2,A,4) insert into courses values (c3,cyuyan,c1,A,4) insert into courses(cno,cname,ctype,credit) values (c2,jichujineng,A,2) insert into courses(cno,cname,ctype,credit) values (c1,jizhu,A,2) insert into courses values (c6,gaoshuA2,c5,A,5) insert into courses(cno,cname,ctype,credit) values (c5,gaoshuA1,A,5) insert into courses(cno,cname,ctype,credit) values (c7,yingyu1,B,4) insert into courses values (c8,yingyu2,c7,A,4) insert into courses values (c9,yingyu3,c8,A,4) insert into courses values (c10,yingyu4,c9,B,4) 3)教師表 teachers create table teachers (tno nvarchar(10) constraint pk3 primary key clustered, tname nvarchar(20), gender char(2) check (gender like FM), birthdate datetime, title char(8) insert into teachers values (t1,jianming,M,1975-12-9,教授) insert into teachers values (t2,yuxia,F,1983-10-9,教授) insert into teachers values (t3,gejun,M,1971-8-26,教授) insert into teachers values (t4,jiangyao,M,1977-3-7,副教授) insert into teachers values (t5,liuling,F,1982-6-21,副教授) insert into teachers values (t6,moyan,F,1975-4-19,教授) insert into teachers values (t7,xiaoming,M,1965-1-15,講師) insert into teachers values (t8,ruiyong,M,1975-8-22,教授) insert into teachers values (t9,yangli,F,1965-2-9,副教授) insert into teachers values (t10,mengzi,M,1990-12-29,講師) 4)選課表 sc create table sc (sno nvarchar(8) constraint fk2 references students(sno), cno nvarchar(10) constraint fk3 references courses(cno), period char(11) check(period like 0-90-90-90-9-0-90-90-90-9-12), grade int constraint pk4 primary key(sno,cno) insert into sc values(A246357F,c1,2010-2011-1,84) insert into sc values(A246357F,c2,2010-2011-1,76) insert into sc values(A246357F,c3,2010-2011-2,89) insert into sc values(B462237M,c1,2010-2011-1,92) insert into sc values(B462237M,c3,2010-2011-2,64) insert into sc values(C346723M,c7,2011-2012-1,74) insert into sc values(C346723M,c8,2011-2012-2,53) insert into sc values(E463849F,c9,2012-2013-1,95) insert into sc values(E888338M,c6,2012-2013-1,91) insert into sc values(E888338M,c5,2011-2012-2,88) create nonclustered index indexfk2 on sc(sno) create nonclustered index indexfk3 on sc(cno) 5)授課表 tc create table tc (tno nvarchar(10) constraint fk4 references teachers(tno), cno nvarchar(10) constraint fk5 references courses(cno), period char(11) check(period like 0-90-90-90-9-0-90-90-90-9-12) constraint pk5 primary key(tno,cno) insert into tc values (t1,c1,2010-2011-1) insert into tc values (t1,c2,2010-2011-1) insert into tc values (t2,c2,2010-2011-1) insert into tc values (t3,c5,2011-2012-2) insert into tc values (t4,c6,2012-2013-1) insert into tc values (t5,c6,2012-2013-1) insert into tc values (t4,c7,2011-2012-1) insert into tc values (t6,c7,2011-2012-1) insert into tc values (t7,c8,2011-2012-2) insert into tc values (t9,c9,2012-2013-1) create nonclustered index indexfk4 on tc(tno) create nonclustered index indexfk5 on tc(cno) 3. 編寫T-SQL語句,完成下列各項功能 (1) 根據(jù)學(xué)生成績表中的數(shù)據(jù)顯示全部學(xué)生的成績要求按班級、學(xué)號的次序顯示輸出內(nèi)容中包括學(xué)生姓名和課程名稱并使用中文標(biāo)題。 select c.sno 學(xué)號,sname 學(xué)生姓名,cname 課程名稱,class 班級,grade 成績 from students a,courses b,stucourses c where c.sno=a.sno and o=o order by class ,c.sno (2) 根據(jù)學(xué)生基本信息表與成績表中的數(shù)據(jù)顯示姓名為x的這個學(xué)生第一學(xué)期的全部課程成績。 select cno,grade from students a jion stucourses b on a.sno=b.sno where sname=x and period like 0-90-90-90-9-0-90-90-90-9-1 (3) 根據(jù)課程表與成績表中的數(shù)據(jù)顯示輸出課程名稱為x.的該課程的平均成績及格人數(shù)比例。 select a.grade into #tmp from sc as a join courses as b on o=o where cname=x select AVG(grade) as avg ,100*(select COUNT (*)from #tmp where grade=60)/COUNT(*) as ratefrom #tmp (4) 根據(jù)課程表與成績表中的數(shù)據(jù)顯示學(xué)號為x的這個學(xué)生最近這個學(xué)期所得到的學(xué)分?jǐn)?shù)。 select SUM(credit) from courses where cno in (select cno from stucourses where sno=x and grade =60) (5) 根據(jù)各表數(shù)據(jù)列出最近這個學(xué)期哪些學(xué)生選修的課程數(shù)量最多要求列出姓名。 select sname from students where sno in (select top 1 sno from sc where period in (select MAX(period) from sc) group by sno order by COUNT(*) desc) (6) 根據(jù)各表數(shù)據(jù)列出最近這個學(xué)期哪些學(xué)生至少選了兩門或兩門以上的選修課程要求列出姓名。 with tmp2 as (select o from courses a,sc b where o=o and ctype=B), tmp3 as (select COUNT(*) num from sc c,tmp2 where o=o group BY sno) select sname from students where sno in (select sno from sc c, tmp2,tmp3 where o=o and num=2 and period in (select MAX(period) from sc) group by sno) (7) 根據(jù)課程表與成績表數(shù)據(jù)列出哪些選修課程學(xué)生選修的學(xué)生人數(shù)最多。 with tmp4 as (select o from courses a,sc b where o=o and ctype=B), tmp5 as (select top 1 o from stud courses c, tmp4 where o=o group by o order by COUNT(*) desc) select o,cname from courses d,tmp5 where o=o (8) 分別列出課程名稱為X的這門課程考試成績排名前5位與后5位的學(xué)生姓名。 select * from (select top 5 sname from students a,courses b,sc c where a.sno=c.sno and o=o and cname=x order by grade desc) as p union all select * from (select top 5 sname from students a,courses b,sc c where a.sno=c.sno and o=o and cname=x (1) order by grade) as b列出必修課程成績不及格其累計學(xué)分超過15分的那些學(xué)生的姓名。 with tp1 as (select cno,credit from courses where ctype=A), tp2 as (select sno,sum(credit) num from sc a,tp1 where o=o and grade15 (2) 列出課程名稱為x的這門課考試成績平均分最高的班級。 select top 1 class,AVG(grade) from stucourses a,courses b,students c where o=o and a.sno=c.sno and cname=jizhu group by class order by AVG(grade) desc (3) 分班級和性別輸出最近這個學(xué)期全部必修課課程的平均考試成績。 select avg(a.grade) ,class ,gender from sc a join students b on a.sno =b.sno where period =2011-2012-2 group by class ,gender (4) 根據(jù)成績表中的數(shù)據(jù)計算學(xué)生基本信息表中每個學(xué)生每個學(xué)期的平均考試成績。 select sno,period,AVG(grade) as avg from sc group by sno,period (5) 列出第2011-1學(xué)期中各個班級平均考試成績排名前5位學(xué)生的姓名要求按各班按成績排序。 select top 5 sname ,class,AVG(grade) as avg from sc as a join students as b on a.sno=b.sno where period =2011-2012-1 group by sname,class order by class,avg desc (6) 將成績表中選修課的成績按五級制,優(yōu)、良、中、及格、不及格,形式進(jìn)行輸出。 select sno,o,grade,成績=case when grade between 90 and 100 then 優(yōu) when grade between 80 and 89 then 良 when grade between 70 and 79 then 中 when grade between 60 and 69 then 及格 else 不及格 end from sc a,courses b where o=o and ctype=B (7) 將成績表中必修課成績按實際分?jǐn)?shù)輸出同時選修課的成績按五級制形式輸出。 select Sno,a.Cno,Period,Grade=CASE when Grade between 60 and 70 then 及格 when Grade between 70 and 80 then 中 when Grade between 80 and 90 then 良 when Grade between 90 and 100 then 優(yōu) else 不及格 end from Sc a join Courses b on a.Cno=b.Cno where b.Type=B union all select Sno,a.Cno,Period,cast(Grade as varchar(8) from Sc as a join Courses b on a.Cno=b.Cno where b.Type=A (8) 在學(xué)生表中添加4個列分別存儲20082011四個學(xué)年的學(xué)生綜合智育成績,利用系統(tǒng)表判斷這4列是否已經(jīng)存在如果已經(jīng)存在則不必添加,。已知每個學(xué)生每個學(xué)年的綜合智育成績計算公式如下: 綜合智育成績=該學(xué)期全部必修課成績的平均分+每門選修課成績的檔次值其中選修課成績檔次值規(guī)定為: =90分:加4分,80,89分:加3分,70,79分:加2分,60,69分:加1分,60分:加0分。 (9) 在學(xué)生表中添加4個列分別存儲20082011四個學(xué)年的學(xué)生綜合智育成績根據(jù)以上公式使用相關(guān)子查詢和UPDATE語句計算每個學(xué)生每個學(xué)年的綜合智育成績。 (10) 根據(jù)各表數(shù)據(jù)與前面的計算結(jié)果統(tǒng)計列出第2011學(xué)年綜合智育成績排名前40%且該學(xué)年每門課程成績都及格的學(xué)生的名單。 (11) 查詢每個學(xué)期中平均成績都在班級排名前30%的學(xué)生的姓名。 select Sname,AVG(Grade) as a,Period from Students a join Sc b on a.Sno=b.Sno where a.Sno in (select top 30 percent c.Sno from Sc c join Students d on c.Sno=d.Sno where b.Period=c.Period and d.class=a.class group by c.Sno,Period,class order by AVG(Grade) desc) group by Sname,Period,class (12) 計算姓名為x的這個學(xué)生第2011-1學(xué)期平均成績在班級中的排名名次。 with tmp as (select class,s.Sname,avg(Grade) as 平均分, Row_Number()over(partition by class order by avg(Grade) desc) as 名次 from Sc st join students s on s.Sno = st.Sno join courses c on c.Cno = st.Cno where period = 2010-2011-1 group by s.Sname,class) select 名次 from tmp where Sname = mark (13) 查詢選修過“數(shù)據(jù)庫”和“數(shù)據(jù)結(jié)構(gòu)”這兩門課程的學(xué)生姓名。 with tmp as(select sname from students a join sc b on a.sno =b.sno join courses c on o =o where cname=數(shù)據(jù)庫) select sname from students a join sc b on a.sno =b.sno join courses c on o =o where cname=數(shù)據(jù)結(jié)構(gòu) and sname =(select sname from tmp ) (14) 查詢沒有選修過“數(shù)據(jù)庫”這門課程的學(xué)生姓名。 select sname from students where sno not in (select sno from sc a join courses b on o=o where cname=數(shù)據(jù)庫) (15) 查詢選修過“數(shù)據(jù)庫”但沒有選修其先行課的學(xué)生姓名。 with tmp as (select sname,a.sno from students a join stucourses b on a.sno=b.sno join courses c on o=o where cname=數(shù)據(jù)庫) select sname from tmp where sno not in( select sno from stucourses a join courses b on o=o where cname =數(shù)據(jù)結(jié)構(gòu)) (16) 查詢所有課程成績?nèi)考案竦膶W(xué)生姓名。 select sname from sc as a join students as b on a.sno=b.sno where a.sno not in (select sno from sc where grade60) (17) 查詢每個學(xué)期必修課成績?nèi)考案竦膶W(xué)生姓名。 select sname from students a join sc b on a.sno=b.sno join courses c on o=o where a.sno not in(select sno from sc where grade60) and ctype=A (18) 查詢選修過教師“達(dá)爾文”所授的全部課程的學(xué)生姓名。 select Cno into #tmp10 from Tc a join Teachers as b on a.Tno=b.Tno where Tname=達(dá)爾文 select Sno,count(*)asamount into #tmp11 from Sc as a Join Tc b on a.Cno=b.Cno join Teachers c on b.Tno=c.Tno where c.Tname=達(dá)爾文 group by Sno declare a int set a =(select count(*) from #tmp10) select Sname from Students as a join #tmp11 b on a.Sno=b.Sno where b.amount=a (19) 查詢哪些學(xué)生選修的課程中其前修課程還沒有選修過。 with tmp1 as (select Sno,b.Pcno,a.Cno from Sc a join Courses b on a.Cno=b.Cno), tmp2 as (select Sno,b.Pcno,a.Cno from Sc a join Courses b on a.Cno=b.Cno) select distinct a.Sno into #tmp7 from tmp1 as a join tmp2 b on a.Cnob.Pcno select Sname from Students a join Sc b on a.Sno=b.Sno where b.Sno in (select * from #tmp7) (20) 查詢哪些學(xué)生至少選修了學(xué)號為“S105401F”這個學(xué)生選修的全部課程。 select Cno into #tmp8 from Sc where Sno=S105401F select a.Sno,count(*) as amount into #tmp9 from Sc a join #tmp8 b on a.Cno=b.Cno group by a.Sno select Sname from Students a join #tmp9 b on a.Sno=b.Sno where b.amount=(select count(*) from #tmp8) (21) 查詢哪些學(xué)生沒有選修過教師“達(dá)爾文”所授的任何一門課程。 select Sname from Students as a join Sc b on a.Sno=b.Sno where b.Sno not in(select distinct Sno from Sc a join Tc b on a.Cno=b.Cno join Teachers c on b.Tno=c.Tno where c.Tname=達(dá)爾文 ) (22) 查詢哪些學(xué)生至少選修了教師“達(dá)爾文”所授的兩門不同的課程。 select Sno,count(*)asamount into #tmp13 from Sc as a join Tc b on a.Cno=b.Cno join Teachers c on b.Tno=c.Tno where c.Tname=達(dá)爾文 group by Sno select Sname from Students as a join #tmp13 b on a.Sno=b.Sno where b.amount=2 (23) 查詢2011學(xué)年哪些老師授課們數(shù)最多。 with tmp as (select Tno,count(*) as amount from Tc where period between 2011-2012-1and2011-2012-2 group by Tno) select Tname from Teachers as a join tmp b on a.tno=b.Tno where b.amount=(select max(amount) from tmp) (24) 查詢2011學(xué)年哪些老師選修課學(xué)生選課人數(shù)最多。 with tmp as (select a.Cno,count(*)as amountfrom Sc a join Courses b on a.Cno=b.Cno where b.Type=B group by a.Cno) select Tname from Teachers a join Tc b on a.Tno=b.Tno join Tmp c on c.Cno=b.Cno where c.amount=(select max(amount) from tmp) (25) 查詢哪些學(xué)生已經(jīng)獲得的必修課學(xué)分不少于150選修課學(xué)分不少于100。 with tmp1 as (select sno,sum(a.Grade)assum1 from Sc a join Courses b on a.Cno=b.Cno where b.type=A group by Sno),tmp2 as (select sno,sum(a.Grade)assum2 from Sc a join Courses b on a.Cno=b.Cno where b.type=B group by Sno) select a.Sno into #tmp14 from Tmp1 a join tmp2 b on a.sno=b.sno where a.sum1150 and b.sum2100 select Sname from Students a join #tmp14 b on a.Sno=b.Sno (26) 建立一個存儲過程輸入一個學(xué)生的姓名返回該學(xué)生全部必修課課程的平均成績。 create procedure myproc1 name varchar(8) as select avg(Grade) from Sc as a join Students b on a.Sno=b.Sno join Courses c on c.Cno=a.Cno where c.Type=A and b.Sname=name (27) 建立一個存儲過程輸入一門課程的編號以及要求查詢成績的區(qū)間,xy)輸出該課程所有在該區(qū)間內(nèi)的學(xué)生姓名要求成績從高到低排序。 create procedure myproc2 cno varchar(12),a1 int ,a2 int as select b.Sname,a.Grade from Sc a join Students b on a.Sno=b.Sno where Cno=cno and Grade between a1 and a2 order by a.Grade desc (28) 建立一個存儲過程輸入一個學(xué)生的學(xué)號列出該學(xué)生最近這個學(xué)期的全部課程的成績并通過調(diào)用該存儲過程編寫程序輸出所有學(xué)生最近這個學(xué)期全部課程的成績。 if(OBJECT_ID(myproc3) is not null) drop procedure myproc3 create procedure myproc3 sno varchar(8) as select Sname,Cno,Grade from Sc a join Students b on a.Sno=b.Sno where a.Sno=sno and a.Period=(select max(Period) from Sc) select distinct Sno into #tmp15 from Sc declare Sno1 varchar(8) declare mycursor Cursor scroll for select Sno from #tmp15 open mycursor fetch next from mycursor into Sno1 while FETCH_STATUS=0 begin execute myproc3 Sno1 fetch next from mycursor into Sno1 end close mycursor deallocate mycursor (29) 建立一個是用戶定義表值函數(shù)輸入一個課程名稱輸出該課程考試成績最高的這些學(xué)生的姓名。 if (OBJECT_ID(myfun1)is not null) drop function myfun1 create function myfun1 (cname char(20) returns table as return (select Sname from Students where sno in (select b.Sno from Sc b,Courses c where b.Cno=c.Cno and Cname=cname and Grade=(select max(Grade) from Sc b,Courses c where b.Cno=c.Cno and Cname=cname) (30) 編寫一個用戶定義函數(shù)要求:輸入一個課程編碼根據(jù)成績表計算并返回該課程全部學(xué)生考試成績的平均值與及格率并通過調(diào)用該函數(shù)編寫一個存儲過程計算列出課程表中全部課程考試成績的平均值與及格率。 if (OBJECT_ID(myg)is not null) drop function myg go create function myg(cno nvarchar(10) returns table as return(select a.Cno,avg(Grade)as avg,100*(select COUNT(*) from Sc a join Courses b on a.Cno=b.Cno where a.Cno=cno and Grade=60)/COUNT(*) asrate% from Courses a join Sc b on a.Cno=b.Cno where a.Cno=cno group by a.Cno) go if(OBJECT_ID(myc) is not null) drop procedure myc create procedure myc cno nvarchar(10) as if(cno is null or cno= ) begin declare cno1 nvarchar(10) declare mycursor cursor scroll for select distinct Cno from Sc open mycursor fetch next from mycursor into cno1 while fetch_status=0 begin select*from dbo.myg(cno1) fetch next from mycursor into cno1 end deallocate mycursor end go execute myc (31) 編寫一個用戶定義函數(shù)輸入一個學(xué)號和學(xué)年號計算返回該學(xué)生該學(xué)年的綜合智育成績計算公式如下16題所示。要求借助該用戶定義函數(shù)使用UPDATE語句計算每個學(xué)生每個學(xué)年的綜合智育成績。 (32) 編寫一個存儲過程輸入一個學(xué)年號和學(xué)生學(xué)號返回該學(xué)生在該學(xué)年中綜合智育成績的排名名次。 if(OBJECT_ID(myProc5) is not null) drop procedure myProc5 create procedure myProc5 sno nchar (8), Term nchar(11) as declare rank int ;with tmp1 as (select sno,sum(Grade) as grade from Sc where Period like 2009-2010-% group by Sno ), tmp2 as (select *,rank() over (Order by Grade desc) as Rank from tmp1) select rank=Rank from tmp2 where Sno=sno if rank is null set rank=-1 select rank go execute myProc5 s000001F,2009-2010 (33) 編寫一個用戶定義表值函數(shù)輸入一個學(xué)號與學(xué)期號輸出返回該學(xué)生該學(xué)期的全部課程及其成績。 create function myfun2(sno nvarchar(8),period char(11) returns table as return (select cno,grade from stucourses where period=period and sno=sno) (34) 創(chuàng)建一個存儲過程輸入一門課程的編號利用遞歸CTE輸出該課程的所有前修課程,包括前修課程的前修課程,。 (35) 建立一個存儲過程輸入一門課程的編號以及要求查詢成績的區(qū)間,xy)使用游標(biāo)逐條輸出該課程所有在該區(qū)間內(nèi)的學(xué)生姓名要求成績從高到低排序輸出格式如下: 課程編號:xxx 課程名稱:xxx - 學(xué)號 姓名 成績 x1 xx1 xxx1 x2 xx2 xxx2 create proc myproc2 cno char(8),min int,max int as declare c cursor scroll for select sname,b.sno,grade from students a,stucourses b,courses c where a.sno=b.sno and o=o and grade between min and max and o=cno order by grade open c declare sn

溫馨提示

  • 1. 本站所有資源如無特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
  • 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
  • 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁內(nèi)容里面會有圖紙預(yù)覽,若沒有圖紙預(yù)覽就沒有圖紙。
  • 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
  • 5. 人人文庫網(wǎng)僅提供信息存儲空間,僅對用戶上傳內(nèi)容的表現(xiàn)方式做保護處理,對用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對任何下載內(nèi)容負(fù)責(zé)。
  • 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請與我們聯(lián)系,我們立即糾正。
  • 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時也不承擔(dān)用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。

評論

0/150

提交評論