數(shù)據(jù)庫第二部分.doc

實 驗 報 告課程名稱 數(shù)據(jù)庫技術(shù)實踐 實驗項目 SQL語言基礎(chǔ)、架構(gòu)與基本表、高級查詢 實驗儀器 SQL Server 2008 系 別_計算機科學與技術(shù)系 _專 業(yè)_計算機科學與技術(shù)_ 班級/學號_xxxxxxxxxxxxxxxxxxx學生姓名 _xxxxxxxxxxxxxxxxxxx 實驗日期 _2013.04.17 _成 績 _ 指導教師 _ 梁琦 _ _一內(nèi)容說明本次實驗的內(nèi)容有學習了SQL語言的基礎(chǔ)，架構(gòu)與基本表的修改，還有高級查詢，涉及到相關(guān)子查詢、其他形式子查詢、查詢結(jié)果的并，交，差運算等，其中還涉及到一些查詢功能，例如：開窗函數(shù)，公式表達等。二.SQL語言基礎(chǔ)1 在students數(shù)據(jù)庫中創(chuàng)建一個用戶定義的數(shù)據(jù)類型：類型名為：my_type，對應的基本數(shù)據(jù)類型為：char(10)，允許空。2 聲明一個字符串型的局部變量，并對其賦初值：My First Var,然后在屏幕上顯示此值。declare str as char(100)=My First Varprint str3 編寫實現(xiàn)如下功能的腳本，并將編寫好的腳本保存到磁盤文件中。（1） 聲明兩個整型的局部變量：i1和i2， i1的初值為10, i2的值為：i1乘以5，最后在屏幕上顯示i2的值。declare i1 as int =10;declare i2 as int =i1*5;print i2（2） 用While語句實現(xiàn)計算5000減1、減2、減3 ，一直減到50的結(jié)果，并顯示最終結(jié)果。declare i1 as int =1;declare sum as int =5000;while i1=90 then 好when (sc.Grade =80) and (sc.Grade =70) and (sc.Grade =60) and (sc.Grade =6) then 多when (count(sc.Cno) =3) and (count(sc.Cno) =1) and (count(sc.Cno) =90 then 好when (avg(sc.Grade) =80) and (avg(sc.Grade) =70) and (avg(sc.Grade) =1)and(course.Semester=3)and(course.Semester=5)and(course.Semester= 80order by AVGGrad desc7 查詢計算機系年齡小于信息管理系全體學生年齡的學生的姓名和年齡。selectt1.Sname as 姓名,DATEPART(yy,getdate()-t1.Birthday)-1900 as 年齡from dbo.student as t1wheret1.Birthday(select max(student.Birthday)from dbo.studentwhere student.Dept=信息管理系)andt1.Dept=計算機系8 查詢計算機系年齡大于信息管理系某個學生年齡的學生的姓名和年齡。selectt1.Sname as 姓名,DATEPART(yy,getdate()-t1.Birthday)-1900 as 年齡from dbo.student as t1where t1.Birthday3 and not exists(select * from SCwhere SC.Cno=o and SC.Sno=s.sno)16查詢在第4學期開設(shè)課程中與第1學期開設(shè)的課程學分相同的課程，列出課程名和學分。select cname,credit from Coursewhere Semester=4 and Credit in(select credit from Course where Semester=1)17查詢“李勇”和“王大力”所選的相同課程，列出課程名、開課學期和學分。select cname,semester,credit from Course join SC on Course.Cno=SC.Cno join Student on Student.Sno=SC.Snowhere sname=李勇intersectselect cname,semester,credit from Course join SC on Course.Cno=SC.Cno join Student on Student.Sno=SC.Snowhere sname=王大力 18查詢“李勇”選了但“王大力”沒有選的課程，列出課程名、開課學期和學分。select Cname,semester,credit from Course join SC on Course.Cno=SC.Cno join Student on Student.Sno=SC.Snowhere sname=李勇exceptselect Cname,semester,credit from Course join SC on Course.Cno=SC.Cno join Student on Student.Sno=SC.Snowhere sname=王大力19查詢至少同時選了“C001”和“C002”兩門課程的學生的學號和所選的課程號。select SC.Sno,SC.Cno from Course join SC on Course.Cno=SC.Cno join Student on Student.Sno=SC.Snowhere SC.Sno in(select t1.sno from (select * from SC where Cno=C001) as t1join (select * from SC where Cno=C002) as t2on t1.Sno=t2.Sno)20查詢學生學號、姓名、所在系及該系的學生人數(shù)。select sno,sname,dept,COUNT(*) over(partition by dept) 該系的學生人數(shù) from Student21查詢學生姓名、年齡、所在系及該系的平均年齡、最大年齡和最小年齡。select Sname, year(getdate() - year(birthday), Dept, avg(year(getdate() - year(birthday) over(partition by Dept) as 平均年齡,max(year(getdate() - year(birthday) over(partition by Dept) as 最大年齡,min(year(getdate() - year(birthday) over(partition by Dept) as 最小年齡 from Student22. 查詢學號、姓名、性別、所在系以及該系的學生總?cè)藬?shù)、男女生人數(shù)及男女生百分比。查詢結(jié)果樣式如圖7-46所示。圖7-46 22題的查詢結(jié)果樣式select sno,sname,sex,dept,COUNT(*) over(partition by dept) as 系總?cè)藬?shù),COUNT(Sex) over(partition by sex,dept) as 男女生人數(shù),cast(1.0*count(sex)over(partition by sex,dept)/COUNT(*) over(partition by dept) as decimal(5,2)as男女生百分比from Studentorder by dept23. 查詢計算機系學生的考試情況，列出學號、姓名、考試課程名、考試成績及成績排名。該查詢的部分結(jié)果形式如圖7-47所示。圖7-47 23題的查詢結(jié)果樣式select sc.Sno,sname,Cname,grade,dense_RANK()over(order by grade desc) as 成績排名from Course join SC on Course.Cno=SC.Cno join Student on Student.Sno=SC.Sno24. 查詢學生學號、選的課程號、考試成績及考試成績在該門課程中的排名。該查詢的部分結(jié)果形式如圖7-48所示。圖7-48 24題的查詢結(jié)果樣式select sc.Sno,sc.Cno,grade,dense_RANK()over(partition by o order by grade desc) as RANKfrom Course join SC on Course.Cno=SC.Cno join Student on Student.Sno=SC.Sno25. 查詢學生姓名、所在系、出生日期及該學生在該系中的年齡排名（按從大到?。?。該查詢的部分結(jié)果形式如圖7-49所示。圖7-49 25題的查詢結(jié)果樣式select Sname,Dept,birthday,dense_RANK()over(partition by dept order by birthday asc) as RANKfrom Student26定義一個統(tǒng)計每門課程的考試平均成績和選課人數(shù)的CTE，并利用該CTE查詢選課人數(shù)超過2人的課程。with avg_count(avgs,counts) as(select AVG(grade)over(partition by cno) as avgs,COUNT(Sno)over(partition by cno) as countsfrom SC)select DISTINCT avgs,counts from avg_countwhere counts227. （選做）查詢Course表中的全部數(shù)據(jù)并將其保存到一個新表NewCourse中，然后為NewCourse表增加一個先修課程列PriorCno，該列表明了當前課程的先修課程編號，NULL表示該門課程沒有先修課程。NewCourse表的結(jié)構(gòu)和數(shù)據(jù)如表7-6所示。表7-6 NewCourse表數(shù)據(jù)CnoCnameCreditSemesterPriorCnoC001 高等數(shù)學41NULLC002 大學英語31NULLC003 大學英語32C002C004 計算機文化學22NULLC005 JAVA23C004C006 數(shù)據(jù)庫基礎(chǔ)45C007C007 數(shù)據(jù)結(jié)構(gòu)44C009C008 計算機網(wǎng)絡(luò)44C004C009Java語言43C004 用NewCourse表查詢每門課程的信息及其先修課程信息，查詢結(jié)果樣式如圖7-50所示。圖7-50 27題的查詢結(jié)果樣式28（選做）利用NewCourse表，查詢課程號、課程名、先修課程號及課程所在的層次。如果該課程沒有先修課，則層次為1，如果有1層先修課，則層次為2，依次類推。查詢結(jié)果樣式如圖7-51所示。圖7-51 28題的查詢結(jié)果樣式四. 實驗總結(jié)通過這學期的上機實驗，其實是對上學期數(shù)據(jù)庫基礎(chǔ)的一個復習和拓展應用，在上學期對數(shù)據(jù)庫理論學習的基礎(chǔ)上，在這次上機實踐中學習了SQL語言基礎(chǔ)、架構(gòu)與基本表的修改，還有高級查詢，涉及到相關(guān)子查詢、其他形式子查詢、查