華工數(shù)據(jù)庫(kù)實(shí)驗(yàn)題

1、1,創(chuàng)建Student數(shù)據(jù)庫(kù)，包括Students,Courses,SC表，表結(jié)構(gòu)如下：Students(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT)Courses(CNO,CNAME,LHOUR,CREDIT,SEMESTER)SC(SNO,CNO,GRADE)(注：下劃線表示主鍵，斜體表示外鍵)，并插入一定數(shù)據(jù)。答：create table Students(SNO varchar(100) primary key ,SNAME varchar(100) null,SEX varchar(100) null,BDATE datetime null,HEIGH

2、T decimal null,DEPARTMENT varchar(100) null)gocreate table Courses(CNO varchar(100) primary key ,CNAME varchar(100) null,LHOUR int null,CREDIT int null,SEMESTER varchar(100) null)goCREATE TABLE dbo.SC(SNO varchar(100) NOT NULL,CNO varchar(100) NOT NULL,GRADE int NULL, CONSTRAINT PK_SC PRIMARY KEY CL

3、USTERED (SNO ASC,CNO ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOALTER TABLE dbo.SC WITH CHECK ADD CONSTRAINT FK_SC_Courses FOREIGN KEY(CNO)REFERENCES dbo.Courses (CNO)GOALTER TABLE dbo.SC CHECK C

4、ONSTRAINT FK_SC_CoursesGOALTER TABLE dbo.SC WITH CHECK ADD CONSTRAINT FK_SC_Students FOREIGN KEY(SNO)REFERENCES dbo.Students (SNO)GOALTER TABLE dbo.SC CHECK CONSTRAINT FK_SC_Students2完成如下的查詢要求及更新的要求。（1）查詢身高大于1.80m的男生的學(xué)號(hào)和姓名；答：select SNO,SNAME from Students where HEIGHT1.8（2）查詢計(jì)算機(jī)系秋季所開(kāi)課程的課程號(hào)和學(xué)分?jǐn)?shù)；答：sel

5、ect CNO,CREDIT from Courses where SEMESTER=秋季（3）查詢選修計(jì)算機(jī)系秋季所開(kāi)課程的男生的姓名、課程號(hào)、學(xué)分?jǐn)?shù)、成績(jī)；答：select s.SNAME,SC.CNO,c.CREDIT,SC.GRADE from students sinner join SC on sc.SNO=s.SNOinner join Courses c on sc.CNO=c.CNOwhere s.DEPARTMENT=計(jì)算機(jī)系 and s.SEX=男 and c.SEMESTER=秋季（4）查詢至少選修一門電機(jī)系課程的女生的姓名（假設(shè)電機(jī)系課程的課程號(hào)以EE開(kāi)頭）；答：se

6、lect distinct s.sname from Students s,sc where s.sno=sc.sno and s.sex=女 and o like EE%（5）查詢每位學(xué)生已選修課程的門數(shù)和總平均成績(jī)；答：select count(c.CNO) as 課程門數(shù),avg(SC.GRADE) as 總平均成績(jī)from students sinner join SC on sc.SNO=s.SNOinner join Courses c on sc.CNO=c.CNOgroup by s.SNO（6）查詢每門課程選課的學(xué)生人數(shù),最高成績(jī),最低成績(jī)和平均成績(jī)；答：select cna

7、me,count(cno),max(grade),min(grade),avg(grade)from students natural join sc natural join coursesgroup by chane;（7）查詢所有課程的成績(jī)都在80分以上的學(xué)生的姓名、學(xué)號(hào)、且按學(xué)號(hào)升序排列；答：select sname,sno from students natural join (select sno.min(grade)as mini from sc group by sno)where mini80 order by(sno);（8）查詢?nèi)背煽?jī)的學(xué)生的姓名，缺成績(jī)的課程號(hào)及其學(xué)分?jǐn)?shù)

8、；答：select sname,o,creditfrom students,courses,scwhere students.sno=sc.sno and o=o and grade is null;（9）查詢有一門以上(含一門)三個(gè)學(xué)分以上課程的成績(jī)低于70分的學(xué)生的姓名；答：select sname from students,courses,scwhere students.sno=sc.sno and o=sc.cho and credit=3 and grade70;（10）查詢1984年1986年出生的學(xué)生的姓名,總平均成績(jī)及已修學(xué)分?jǐn)?shù)。答：select smane,avg(gra

9、de),sun(credit)from students natural join sc natural join courseswhere bdate between 1984-00-00 and 1987-00-00group by sname;（11） 在STUDENT和SC關(guān)系中，刪去SNO以01開(kāi)關(guān)的所有記錄。答：delete sc where SNO like %01%delete Students where SNO like %01%（12）在關(guān)系中增加以下記錄：答：insert into students values(0409101,何平,女,1987-03-02,1.62

10、,)insert into students values(0408130,向陽(yáng),男,1986-12-11,1.75,)（13）將課程CS-221的學(xué)分?jǐn)?shù)增為，講課時(shí)數(shù)增為答：update courses set credit=3 where cno=GS-221update courses set credit=60 where cno=GS-2213補(bǔ)充題：(1) 統(tǒng)計(jì)各系的男生和女生的人數(shù)。答：select department,sum(case wgen sex=男 then 1 else 0 end),sum(case wgen sex=女 then 1 else 0 end),co

11、unt(sno) from students group by department order by department;(2) 列出學(xué)習(xí)過(guò)編譯原理，數(shù)據(jù)庫(kù)或體系結(jié)構(gòu)課程，且這些課程的成績(jī)之一在90分以上的學(xué)生的名字。答：select snamefrom students natural join sc natural join courseswhere cname=編譯原理 or cname=數(shù)據(jù)庫(kù) or cname=體系結(jié)構(gòu) and grade90;(3) 列出未修選電子技術(shù)課程，但選修了數(shù)字電路或數(shù)字邏輯課程的學(xué)生數(shù)。答：select count(distinci sc.sno) f

12、rom courses,sc.sno not in(select sc.sno from o=o and cname=電子技術(shù))and o in (select cno from courses where cname=數(shù)字邏輯 or cname=數(shù)字電路)(4) 按課程排序列出所有學(xué)生的成績(jī)，尚無(wú)學(xué)生選修的課程，也需要列出，相關(guān)的學(xué)生成績(jī)用NULL表示。答：select distinct o,ame,sno,grade from courses left join sc on (o-o)group by o,ame,sno,gradeorder by o,ame,sno,grade;(5)

13、列出平均成績(jī)最高的學(xué)生名字和成績(jī)。(SELECT句中不得使用TOP n子句)答：select sname,rfrom(select sname,avg(grade)as from students,sc where students.sno=sc.sno group by sname,students.sno order by r desc)where rownum=1;4選做題：對(duì)每門課增加“先修課程”的屬性，用來(lái)表示某一門課程的先修課程，每門課程應(yīng)可記錄多于一門的先修課程。要求：1) 修改表結(jié)構(gòu)的定義，應(yīng)盡量避免數(shù)據(jù)冗余，建立必要的主鍵，外鍵。2) 設(shè)計(jì)并插入必要的測(cè)試數(shù)據(jù)，完成以下查詢：

14、列出有資格選修數(shù)據(jù)庫(kù)課程的所有學(xué)生。(該學(xué)生已經(jīng)選修過(guò)數(shù)據(jù)庫(kù)課程的所有先修課，并達(dá)到合格成績(jī)。)注意：須設(shè)計(jì)每個(gè)查詢的測(cè)試數(shù)據(jù)，并在查詢之前用INSERT語(yǔ)句插入表中。實(shí)驗(yàn)二：數(shù)據(jù)庫(kù)的安全和完整性約束實(shí)驗(yàn)要求：1采用實(shí)驗(yàn)一的建庫(kù)腳本和數(shù)據(jù)插入腳本創(chuàng)建Student數(shù)據(jù)庫(kù),并完成以下操作：1）新增表Credits(SNO,SumCredit,NoPass)，表示每學(xué)生已通過(guò)選修課程的合計(jì)學(xué)分?jǐn)?shù)，以及不及格的課程數(shù)。答：create table Credits(SNO varchar(100),SumCredit int,NoPass int)2）創(chuàng)建視圖Student_Grade(Sname,C

15、name,Grade)，表示學(xué)生選修課程及成績(jī)的詳細(xì)信息。答：create view Student_Gradeas select s.SNAME,c.CNAME,SC.GRADE from students sinner join SC on sc.SNO=s.SNOinner join Courses c on sc.CNO=c.CNO2. 在數(shù)據(jù)庫(kù)中創(chuàng)建以下觸發(fā)器：1） Upd_Credit要求：當(dāng)在SC表中插入一條選課成績(jī)，自動(dòng)觸發(fā)Upd_Credit，完成在Credits表中修改該學(xué)生的合計(jì)學(xué)分?jǐn)?shù)和不及格的課程數(shù)。答：create trigger Upd_Credit on SC f

16、or insertas declare SNO varchar(100),CNO varchar(100),GRADE int ,NoPass int ,CREDIT int select SNO=SNO,CNO=CNO,GRADE=GRADE,NoPass=(case when GRADE3group by s.SNO4) CAL_GPA (SNO，GPA output)要求：根據(jù)SNO參數(shù), 輸出并顯示該學(xué)生的GPA值。計(jì)算方法如下：GRADE(G) GRADEPOINT(GP)G=85 485G=75 375G=60 260G 1GPA= (GP*CREDIT)/ CREDIT)答：cr

17、eate procedure CAL_GPASNO varchar(100),GPA decimal outputasdeclare SUM_CREDIT int,AVG_GRADE int,All_CREDIT intselect SUM_CREDIT=SumCredit from Creditswhere SNO=SNOselect AVG_GRADE=avg(case when SC.GRADE=85 then 4 when 85SC.GRADE and SC.GRADE=75 then 3 when 75SC.GRADE and SC.GRADE=60 then 2 when 60SC

18、.GRADE then 1 end) from Students sinner join SC on sc.SNO=s.SNOwhere s.SNO=SNO and SC.GRADE3group by s.SNOselect GPA=(AVG_GRADE*SUM_CREDIT)/All_CREDIT3選做題： 使用其它程序設(shè)計(jì)語(yǔ)言編程，連接數(shù)據(jù)庫(kù)并提交SQL語(yǔ)句，顯示查詢結(jié)果。 要求：實(shí)現(xiàn)上題中的第3)小題要求，設(shè)計(jì)一個(gè)圖形界面來(lái)輸入查詢的參數(shù)SNO，及顯示查詢的結(jié)果。(如采用VC+，VB等)實(shí)驗(yàn)四：事務(wù)的管理（選作）實(shí)驗(yàn)要求：1 采用實(shí)驗(yàn)一的建庫(kù)腳本和數(shù)據(jù)插入腳本創(chuàng)建Student數(shù)據(jù)庫(kù)。2 測(cè)試事務(wù)隔離級(jí)別，要求： 分別設(shè)置不同的隔離級(jí)別,包括：SET TRANSACTION ISOLATION LEVEL SERIALIZAB