实 验 报 告
课程名称 数据库技术实践 实验项目 SQL语言基础、架构与基本表、高级查询 实验仪器 SQL Server 2008
系 别____计算机科学与技术系 _ 专 业____计算机科学与技术____ 班级/学号__xxxxxxxxxxxxxxxxxxx
学生姓名 ____xxxxxxxxxxxxxxxxxxx 实验日期
成 绩 _______________________ 指导教师 ___ 梁琦 _______ _______
一．内容说明
本次实验的内容有学习了SQL语言的基础，架构与基本表的修改，还有高级查询，涉及到相关子查询、其他形式子查询、查询结果的并，交，差运算等，其中还涉及到一些查询功能，例如：开窗函数，公式表达等。
二.SQL语言基础
1． 在students数据库中创建一个用户定义的数据类型：类型名为：my_type，对应的基本数据类型为：char(10)，允许空。
2． 声明一个字符串型的局部变量，并对其赋初值：‘My First Var’,然后在屏幕上显示此值。
declare @str as char(100)='My First Var' print @str
3． 编写实现如下功能的脚本，并将编写好的脚本保存到磁盘文件中。
（1） 声明两个整型的局部变量：@i1和@i2， @i1的初值为10, @i2的值为：
@i1乘以5，最后在屏幕上显示@i2的值。 declare @i1 as int =10; declare @i2 as int =@i1*5; print @i2
（2） 用While语句实现计算5000减1、减2、减3 … ，一直减到50的结果，
并显示最终结果。
declare @i1 as int =1;
declare @sum as int =5000; while @i1<=50 begin
set @sum = @sum-@i1; set @i1 =@i1+1; end
print @sum;
三．架构与基本表
1． 在第3章建立的Students数据库中，创建满足如下要求的架构。
准备工作：首先在SSMS中，以系统管理员身份执行下列脚本，创建登录账户User1和User2，并让这两个登录账户成为Students数据库中的合法用户。
CREATE LOGIN User1 WITH PASSWORD = '123456',
DEFAULT_DATABASE = Students
go
CREATE LOGIN User2 WITH PASSWORD = '123456',
DEFAULT_DATABASE = students
go
USE Students
go
CREATE USER User1 go
CREATE USER User2
（1） 为用户User1定义一个架构，架构名为Base。
（2） 为用户User2定义一个架构，架构名为Inform，并在该架构中定义一个关
系表Teacher，结构为： Tno char(8) -- 教师号 Tname varchar(10) -- 教师名
（3） 将Inform架构中的Teacher表传输到Base架构中。 alter schema Base transfer Inform.Teacher
（4） 删除Inform架构。
drop schema Inform
2． 在Students数据库中，用图形化方法创建满足下述要求的关系表。
Student
列名 Sno Sname Sex Birthdate Dept 说明 学号 数据类型 普通编码定长字符串，长度为7 姓名 普通编码定长字符串，长度为10 性别 普通编码定长字符串，长度为2 出生日日期类型 期 所在系 普通编码不定长字符串，长度为20 Course 说明 数据类型 课程号 普通编码定长字符串，长度为10 课程名 普通编码不定长字符串，长度为20 学分 微整型 开课学微整型 期 SC 说明 数据类型 普通编码定长字符串，学号 长度为7 普通编码定长字符串，课程号 长度为10 成绩 小整型 约束 主键 非空 取值范围为：{男，女} 列名 Cno Cname Credit Semester 列名 Sno Cno Grade
约束 主键 非空 大于0 约束 主键，引用Student的外键 主键，引用Course的外键 取值范围：0～100
3． 在Students数据库中，用T-SQL语句创建满足要求的表：
销售表 数据类型 普通编码定长字符型，长度为10 小日期时间型 整型 小整型 整型 列名 商品号 销售时间 销售价格 销售数量 销售总价 约束 非空 非空 非空 非空 等于本次销售价格*销售数量 其中（商品号，销售时间）为主键
订购表 列名 货单号 约束 整型 标识列，初值为1，自动增长，每次增加1，主键 订购时间 小日期时间型 非空 普通编码定长字符 顾客号 型，长度为10 订购明细表
数据类型 列名 货单号 商品号 约束 整型 外键，引用订购表的“货单号” 普通编码定长字符型，长度为非空 10 订购数量 整型 订购价格 整型 其中（货单号，商品号）为主键。
销售表：
CREATE TABLE [dbo].[销售](
[商品号] [char](10) NOT NULL,
[销售时间] [smalldatetime] NOT NULL, [销售价格] [int] NOT NULL,
[销售数量] [smallint] NOT NULL, [销售总价] [int] NOT NULL,
CONSTRAINT [PK_销售] PRIMARY KEY CLUSTERED (
[商品号] ASC, [销售时间] ASC )) ON [PRIMARY]
ALTER TABLE [dbo].[销售] WITH CHECK ADD CONSTRAINT [CK_销售] CHECK (([销售总价]=[销售价格]*[销售数量])) GO
数据类型
订购表：
CREATE TABLE [dbo].[订购](
[货单号] [int] IDENTITY(1,1) NOT NULL, [订购时间] [smalldatetime] NOT NULL, [顾客号] [char](10) NULL,
CONSTRAINT [PK_订购] PRIMARY KEY CLUSTERED (
[货单号] ASC
)
) ON [PRIMARY]
订购明细表：
CREATE TABLE [dbo].[订购明细]( [货单号] [int] NOT NULL,
[商品号] [char](10) NOT NULL, [订购数量] [int] NULL, [订购价格] [int] NULL,
CONSTRAINT [PK_订购明细] PRIMARY KEY CLUSTERED (
[货单号] ASC, [商品号] ASC )) ON [PRIMARY]
ALTER TABLE [dbo].[订购明细] WITH CHECK ADD CONSTRAINT [FK_订购明细_订购] FOREIGN KEY([货单号])
REFERENCES [dbo].[订购] ([货单号])
4． 创建满足如下要求的分区函数：
（1） 在int列上创建右侧分区函数，该分区函数将数据分为3个区：小于
1000、1000~3000和大于3000。 create partition function 右侧分区 (int) as range right
for values(1000,3000)
（2） 在smalldatetime列上创建左侧分区函数，该分区函数将数据按月份分
区，只针对2011年数据，每月一个区。
create partition function 左侧分区 (smalldatetime) as range left for values( 2011-02-01, 2011-03-01, 2011-04-01, 2011-05-01, 2011-06-01, 2011-07-01, 2011-08-01, 2011-09-01, 2011-10-01, 2011-11-01, 2011-12-01 （3）
5． 在Sudents数据库中增加两个新的文件组：MyGroup1和MyGroup2，然后利用第4题（1）建立的分区函数，建立分区方案，使得每个分区分别存放在PRIMARY、MyGroup1和MyGroup2文件组中 create partition scheme fa1 as partition 右侧分区
to (Group1,Group2,Group3)
6． 创建使用第4题（2）创建的分区方案的表：Sales_2011，结构为： GID：普通编码定长字符型，长度为10，主键； Sales_date：小日期时间型，非空； Sales_Total：整型。
该表按Sales_date进行分区。 CREATE TABLE dbo.Sales_2011 (
GID char(10) NOT NULL,
Sales_date datetime NOT NULL, Sales_Total int NOT NULL ) ON fa1(Sales_Total)
四. 高级查询
根据第6章给出的Student、Course和SC表，编写实现如下操作的SQL语句。
1．查询计算机系每个学生的JAVA考试情况，列出学号、姓名、成绩和成绩情况，
其中成绩情况的显示规则为：
如果成绩大于等于90，则成绩情况为“好”； 如果成绩在80～89，则成绩情况为“较好”； 如果成绩在70～79，则成绩情况为“一般”； 如果成绩在60～69，则成绩情况为“较差”；
如果成绩小于60，则成绩情况为“差”。
select
sc.sno as 学号,
student.Sname as 姓名, sc.Grade as 成绩, case
when sc.Grade >=90 then '好'
when ((sc.Grade >=80) and (sc.Grade <89)) then '较好' when ((sc.Grade >=70) and (sc.Grade <79)) then '一般' when ((sc.Grade >=60) and (sc.Grade <69)) then '较差' else '差'
end as '成绩情况'
from dbo.sc,dbo.student,dbo.course where
sc.Sno=student.Sno and sco=courseo and courseame='VB'
2. 统计每个学生的选课门数（包括没有选课的学生），列出学号、选课门数和选课情况，其中选课情况显示规则为：
如果选课门数大于等于6门，则选课情况为“多”； 如果选课门数超过在3～5门，则选课情况为“一般”； 如果选课门数在1~2门，则选课情况为“偏少”。 如果没有选课，则选课情况为“未选课”。
select
sc.Sno as 学号,
count(sco) 选课门数, case
when (count(sco) >=6) then '多'
when ((count(sco) >=3) and (count(sco) <5)) then '一般' when ((count(sco) >=1) and (count(sco) <2)) then '偏少' else '未选课' end
as 选课情况 from dbo.sc
group by sc.sno
3. 统计每个系JAVA课程的考试情况，列出系名和考试情况，其中考试情况为：
如果JAVA平均成绩大于等于90，则考试情况为“好”； 如果JAVA平均成绩在80～89，则考试情况为“良好”； 如果JAVA平均成绩在70～79，则考试情况为“一般”； 如果JAVA平均成绩低于70，则考试情况为“较差”。
select
student.Dept as 系名 , case
when avg(sc.Grade) >=90 then '好'
when ((avg(sc.Grade) >=80) and (avg(sc.Grade) <89)) then '较好' when ((avg(sc.Grade) >=70) and (avg(sc.Grade) <79)) then '一般' else '差'
end as '成绩情况'
from dbo.sc,dbo.student,dbo.course where
sc.Sno=student.Sno and sco=courseo and courseame='VB'
group by student.Dept
4. 修改全部课程的学分，修改规则如下：
如果是第1～2学期开设的课程，则学分增加5分； 如果是第3～4学期开设的课程，则学分增加3分； 如果是第5～6学期开设的课程，则学分增加1分； 对其他学期开设的课程，学分不变。
update dbo.course set
course.Credit= case
when ((course.Semester>=1)and(course.Semester<=2)) then course.Credit+5
when ((course.Semester>=3)and(course.Semester<=4))
then course.Credit+3
when ((course.Semester>=5)and(course.Semester<=6)) then course.Credit+1 else 0 end
5．统计第2学期开设的课程的总学分，列出该学期开设的课程名、学分和总学分。
select cname,credit,(select sum(Credit) from COURSE where Semester = 2)as 总学分 from COURSE where Semester = 2
6．统计考试平均成绩大于等于80分的学生的姓名、考试的课程号、考试成绩和
平均成绩，并将结果按平均成绩从高到低排序。
select Sname, Cno, Grade,(select AVG(Grade) from sc join student c on c.Sno = sc.Sno
where sc.Sno=student.Sno) as AVGGrad from student join sc c2 on c2.Sno = student.Sno
where (select AVG(Grade) from sc join student c on c.Sno = sc.Sno where sc.Sno=student.Sno)>= 80
order by AVGGrad desc
7． 查询计算机系年龄小于信息管理系全体学生年龄的学生的姓名和年龄。 select
t1.Sname as 姓名,
DATEPART(yy,getdate()-t1.Birthday)-1900 as 年龄 from dbo.student as t1 where
t1.Birthday> (
select
max(student.Birthday) from dbo.student where
student.Dept='信息管理系' )and
t1.Dept='计算机系'
8． 查询计算机系年龄大于信息管理系某个学生年龄的学生的姓名和年龄。 select
t1.Sname as 姓名,
DATEPART(yy,getdate()-t1.Birthday)-1900 as 年龄 from dbo.student as t1 where
t1.Birthday< (
select
max(student.Birthday) from dbo.student where
student.Dept='信息管理系' )and
t1.Dept='计算机系'
9． 查询哪些课程没有学生选，列出课程号和课程名。(用EXISTS子查询实现) select
courseo as 课程号, courseame as 课程名 from dbo.course where
not exists( select * from dbo.sc
where sco=courseo )
10.查询计算机系哪些学生没有选课，列出学生姓名。(用EXISTS子查询实现) select
student.Sname from dbo.student where
not exists( select * from dbo.sc
where sc.Sno=student.Sno )and
student.Dept='计算机系'
11．查询没有选修第2学期开设的全部课程的学生的学号、其所选的课程号和该课程的开课学期。
select
t1.Sno as 学号,
t2o as 其所选的课程号,
t3.Semester as 该课程的开课学期 from
dbo.student as t1, dbo.sc as t2, dbo.course as t3 where
not exists( select *
from dbo.sc,dbo.course where
sco=courseo and
course.Semester=2and sco=t2o )and
t1.Sno=t2.Sno and
t2o=t3o
12．查询至少选了第4学期开设的全部课程的学生的学号和所在系。
select sno,dept from student s
where not exists (select * from Course c
where semester =4 and not exists(select * from SC where SCo=co and SC.Sno=s.sno))
13．查询至少选了“0831102”号学生所选的全部课程的学生的学号。
select sno from student s
where not exists (select * from SC c where c.Sno ='0831102' and not exists(select * from SC where SCo=co and SC.Sno=s.sno))
14．查询至少选了“张海”所选的全部课程的学生的学号、所在系和所选的课程号。 select s.Sno,dept,cno from Student s join SC on s.Sno=SC.Sno
where not exists (
select * from SC join Student s on s.Sno=SC.Sno where Sname='张海' and not exists ( ) )
select * from SC
join Student s on SC.sno=s.sno
15．查询至少选了全部学分大于3分的课程的学生的学号、所在系和所选的课程
号、课程名以及学分。
select sc.Sno,Dept,sco,cname,credit from student s join SC on s.Sno=SC.Sno join Course c on co=SC.Sno where not exists (select * from Course c where c.Credit >3 and not exists(select * from SC where SCo=co and SC.Sno=s.sno))
16．查询在第4学期开设课程中与第1学期开设的课程学分相同的课程，列出课程名和学分。
select cname,credit from Course where Semester=4 and Credit in( select credit from Course where Semester=1)
17．查询“李勇”和“王大力”所选的相同课程，列出课程名、开课学期和学分。 select cname,semester,credit from Course join SC on Courseo=SCo join
Student on Student.Sno=SC.Sno where sname='李勇' intersect
select cname,semester,credit from Course join SC on Courseo=SCo join Student on Student.Sno=SC.Sno where sname='王大力'
18．查询“李勇”选了但“王大力”没有选的课程，列出课程名、开课学期和学分。 select Cname,semester,credit from Course join SC on Courseo=SCo join Student on Student.Sno=SC.Sno where sname='李勇' except
select Cname,semester,credit from Course join SC on Courseo=SCo join Student on Student.Sno=SC.Sno where sname='王大力'
19．查询至少同时选了“C001”和“C002”两门课程的学生的学号和所选的课程号。 select SC.Sno,SCo from Course join SC on Courseo=SCo join Student on Student.Sno=SC.Sno where SC.Sno in(
select t1.sno from (select * from SC where Cno='C001') as t1 join (select * from SC where Cno='C002') as t2 on t1.Sno=t2.Sno)
20．查询学生学号、姓名、所在系及该系的学生人数。
select sno,sname,dept,COUNT(*) over(partition by dept) 该系的学生人数 from Student
21．查询学生姓名、年龄、所在系及该系的平均年龄、最大年龄和最小年龄。
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 Student
22. 查询学号、姓名、性别、所在系以及该系的学生总人数、男女生人数及男女
生百分比。查询结果样式如图7-46所示。
图7-46 22题的查询结果样式
select sno,sname,sex,dept,
COUNT(*) over(partition by dept) as 系总人数,
COUNT(Sex) over(partition by sex,dept) as 男女生人数, cast(1.0*count(sex)over(partition by sex,dept)/COUNT(*) over(partition by dept) as decimal(5,2))as男女生百分比 from Student
order by dept
23. 查询计算机系学生的考试情况，列出学号、姓名、考试课程名、考试成绩及
成绩排名。该查询的部分结果形式如图7-47所示。
图7-47 23题的查询结果样式
select sc.Sno,sname,Cname,grade,
dense_RANK()over(order by grade desc) as 成绩排名
from Course join SC on Courseo=SCo join Student on Student.Sno=SC.Sno
24. 查询学生学号、选的课程号、考试成绩及考试成绩在该门课程中的排名。该
查询的部分结果形式如图7-48所示。
图7-48 24题的查询结果样式
select sc.Sno,sco,grade,
dense_RANK()over(partition by sco order by grade desc) as RANK from Course join SC on Courseo=SCo join Student on Student.Sno=SC.Sno
25. 查询学生姓名、所在系、出生日期及该学生在该系中的年龄排名（按从大到
小）。该查询的部分结果形式如图7-49所示。
图7-49 25题的查询结果样式
select Sname,Dept,birthday,
dense_RANK()over(partition by dept order by birthday asc) as RANK from Student
26．定义一个统计每门课程的考试平均成绩和选课人数的CTE，并利用该CTE
查询选课人数超过2人的课程。
with avg_count(avgs,counts) as (
select AVG(grade)over(partition by cno) as avgs, COUNT(Sno)over(partition by cno) as counts from SC )
select DISTINCT avgs,counts from avg_count where counts>2
27. （选做）查询Course表中的全部数据并将其保存到一个新表NewCourse
中，然后为NewCourse表增加一个先修课程列PriorCno，该列表明了当前课程的先修课程编号，NULL表示该门课程没有先修课程。NewCourse表的结构和数据如表7-6所示。
表7-6 NewCourse表数据
Cno C001 C002 C003 C004 C005 C006 C007 C008 C009 Cname 高等数学 大学英语 大学英语 计算机文化学 JAVA 数据库基础 数据结构 计算机网络 Java语言 Credit 4 3 3 2 2 4 4 4 4 Semester 1 1 2 2 3 5 4 4 3 PriorCno NULL NULL C002 NULL C004 C007 C009 C004 C004 用NewCourse表查询每门课程的信息及其先修课程信息，查询结果样式如图7-50所示。
图7-50 27题的查询结果样式
28．（选做）利用NewCourse表，查询课程号、课程名、先修课程号及课程所在的
层次。如果该课程没有先修课，则层次为1，如果有1层先修课，则层次为2，依次类推。查询结果样式如图7-51所示。
图7-51 28题的查询结果样式
四. 实验总结
通过这学期的上机实验，其实是对上学期数据库基础的一个复习和拓展应用，在上学期对数据库理论学习的基础上，在这次上机实践中学习了SQL语言基础、架构与基本表的修改，还有高级查询，涉及到相关子查询、其他形式子查询、查询结果的并，交，差运算等，其中还涉及到一些查询功能，例如：开窗函数，公式表达等。更加深入学习了高级查询的应用，在高级查询中学到了之前不会的东西，例如如何应用开窗函数，查询结果的并交差运算，以及公式表达等，可以以不同方式来进行数据的查询，语句虽然比之前复杂了，但是查询结果更清楚了。
其中有许多知识老师在课上是讲过的，但是我发现光听是不够的，必须是自己动手做，还需要自己课下摸索，这次试验中可能还存在着问题，我会在老师的指导下慢慢学会改正，最大的收获就是熟能生巧，多做多练才能有速度和质量。
因篇幅问题不能全部显示，请点此查看更多更全内容