数据库技术实践2

1、实验报告课程名称数据库技术实践实验项目SQL语言基础、架构与基本表、高级查询实验仪器SQL Server 2008系另寸计算机科学与技术系 专业计算机科学与技术班级/学号,学生姓名实验日期成 绩指导教师一. 内容说明(1) 学习SQL语言的基础，架构与基本表的修改。(2) 熟悉高级查询，包括相关子查询、其他形式子查询，同时 涉及排序、查找等功能。二. SQL语言基础在students数据库中创建一个用户定义的数据类型：类型名为：my_type，对应 的基本数据类型为：char(10)，允许空。代码：create type my_type from char ( 10) null回逛逬皿直存储：

2、日字节1.声明一个字符串型的局部变量，并对其赋初值： My First Var ,然后在屏 幕上显示此值。 declare 8str as char(100) = 1 My First Var L print 0str2.编写实现如下功能的脚本，并将编写好的脚本保存到磁盘文件中。声明两个整型的局部变量：i1和i2, i1的初值为10, i2的值为：i1乘以5,最后在屏幕上显示i2的值。代码：declare i1 int = 10declare i2 int set i2 = i1 * 5 print i2用While语句实现计算5000减1、减2、减3 ,，一直减到50的结果，并显 示最终结果

3、。declarex int= 1declarey int= 5000while x =50beg inset y = y - xset x = x + 1endprint ydeclare ii as mt =1 ；|declareas int =5000;Wsum-Hilsumwhile 9il=90 then1好when(sc .Grade=80 )and( sc. Grade=70 )and( sc. Grade=60 )and( sc. Grade69)then 较差 else 差 end as 成绩情况 from sc join student on sc . Sno = stude

4、nt . Sno join course on sc . Cno =course . Cno wherecourse . Cname = JAVArz*=6) the n多whe n(cou nt(sc.Cno)=3) and(cou nt(sc .Cno)=1) and(cou nt(sc .Cno)=90 then 好whe n(avg ( sc . Grade)=80) and(avg ( sc .Grade )=70) and(avg ( sc .Grade )=1 )and(Semester=3)and(Semester=5)and(Semester= 80order by AvgG

5、rade descCrtoGradeAVGGrad1刘晨1 C0019288 B&86B6B86E6872刘晨C0Q29088 66666666666673刘晨C0048468.66666666666674李禺C001%B055李勇C0Q28080.56李勇C0038480.57李勇C005G280.57 查询计算机系年龄小于信息管理系全体学生年龄的学生的姓名和年龄口 select11. Sname as 姓名，DATEPART (yyt get date 1 -tl. Birthday) -1900 as 年龄 from dbo student as tlwheretl.Birthday(s

6、electmax(student Birthday)ftoni dbo. studentstudent, Dept=息管理系)andLtl.DepU计尊机系-1900 as 年龄 from dbo. student as 11 对 Birthday (datetime# nd) wheretL Birthday(selectmax(student. Birthday)from dbo. student wherestudent. Dept=* ffl!息管理系)and-tl.Dept二 计算机系322大学英语133丈学英谱234高尋啟学1419查询至少同时选了 号。Cno1 081110100

7、0120B111OT000230811101C00340811101000550811102000160811102匚00270811102C0G420.查询学生学号、姓名、所在系及该系的学生人数。snodep该系的学生人数1| 0911101 李勇计It机系420011102计童机系430811103计直机系440911104张小红计算机亲450831101M平週信工程系360831102壬大力遇信工程累370931103il信工程系3B0821101张立倍息管理系390821102吴宾信息昔理系3100821103信J&管理系321 查询学生姓名、年龄、所在系及该系的平均年龄、最大年龄和最

8、小年龄Stwm氐列名）Depi平均澤骷n犬第藹1J1 22计2122202Z1计覽机系Z122203壬22计H机累21222D4聲侶I20讨尊机系2122205龄平22Z12221B王大力22運律工程系2122217张21週億工程廉2122Z1e22212221921212221W21播音理累21222122.查询学号、姓名、性别、所在系以及该系的学生总人数、男女生人数及男女生百分比。查询结果样式如图7-46所示。StW阿 dept乘总人数男虫主人数男宜生百分比Mi 0311101 :李勇男i十宜机系420.5020S11102刘晨男i十算机系斗20.5030011103王敏女计算机乘42Q5

9、04QS11104张小红女计算机粟420 5350831102男通信工程系310 33_60831103张册册女通信工程系320.671 70831101钱小平女通信工程系320.6780921101张立男信息管鰹系32Q6790021103张海男信息管理粟320.67100Q21102吴宾虫信息管理系310.33图7-46 22题的查询结果样式select sno,sname, sex, dept, COUNT) over ( partitionby dept) as 系总人数,COUN(FSex)over ( partition by sex, dept) as 男女生人数 ,cast (

10、1.0 *count (sex) over (partitionbysex, dept)/ COUN(T) over ( partitionby dept) as decimal ( 5, 2) as 男女生百分比 fromStudent order by dept23. 查询计算机系学生的考试情况，列出学号、姓名、考试课程名、考试成绩及 成绩排名。该查询的部分结果形式如图7-47所示。SnoCnameGradeN umber1i 0S11101 1李勇高等数学%120S11102刘晨高等数学92230011102刘晨犬学英语90340811102刘晨计算机文化学844I 50011101李勇

11、犬学英语84560011101李勇犬学英语eo610811101李勇VB7图7-47 23题的查询结果样式select sc. Sno；srade,dense_RANK(:over(order by grade desc) as 成绩排名from Course join SC on Course. CnSC. Cno join Stu dent on Student. Sno=SC. Sno24. 查询学生学号、选的课程号、考试成绩及考试成绩在该门课程中的排名。该查询的部分结果形式如图7-48所示。SnoCnoGradeRANKE0811101C00196120811102C001922308

12、21102C00176340821103C00150450831101C001504G0811102C00290170811101C002eo2图7-48 24题的查询结果样式select sno,sname, sex, dept, COUNT) over ( partitionby dept) as 系总人数, COUN(TSex)over ( partitionby sex, dept) as 男女生人数 ,cast (1.0 *count (sex) over (partition bysex, dept)/ COUNT) over ( partition by dept) as dec

13、imal (5,2) as 男女生百分比 fromStudent order by dept25. 查询学生姓名、所在系、出生日期及该学生在该系中的年龄排名(按从大到小)。该查询的部分结果形式如图7-49所示。SnameDeptBirthDayRANK1王敏：|计筐机系1990-03-1812李勇计算机系1990-Q5-0623刘晨计算机系1991-06-0834张小红计馆机采1992-01-1045壬大力通信工程系1990-05-0616通信工程系1990-1V0927张姗册通信工程系1991-02-2630张立信息管理系1990-10-1219吴宾信息管理系1991-03-20210张海信

14、息管理系1991 06 033图7-49 25题的查询结果样式selectDept, birthid顶丹dense_RANK () over (part i t ion by dept ordEf by birthday ascj as RANK from Student26. 定义一个统计每门课程的考试平均成绩和选课人数的CTE并利用该CTE查询选课人数超过2人的课程。结臬厶消息IavgscountsV1nullj328 6666686666673 Q X372.85481.4S27. (选做)查询Course表中的全部数据并将其保存到一个新表 NewCourse中, 然后为NewCours

15、e表增加一个先修课程列 PriorCno，该列表明了当前课程 的先修课程编号，NULL表示该门课程没有先修课程。NewCourse表的结构和数 据如表7-6所示。表7-6 NewCourse表数据CnoCn ameCreditSemesterPriorC noC001r高等数学41NULL nC002大学英语31NULLC003大学英语32C002 TC004计算机文化学22NULLC005JAVA23C0041C006数据库基础45C007C007数据结构44C009C008计算机网络144P C004C009Java语言43C004用NewCourse表查询每门课程的信息及其先修课程信息，查询结果样式如图7-50 示。课程号谍程名开谍学期先修课程号先修燥程名先修谍开课学期1|C003:LI犬学英语2C002犬学英语12C005VB3C004计算机文化学23C006数据库基础5C007数据结构44C007数据皓构4C009J a v afia o35C008计篦机网络4C004计算机文化学26C009Jq3C004计算机文化学2图7-50 27题的查询结果样式28. （选做）利用NewCours表，查询课程号、课程