《数据库技术及应用》实验指导书答案

《数据库技术及应用》课内上机实验指导书
实验一：SQL SERVER的可视化操作
一、实验内容
1、学习启动SQL Server2000。

2、用SQL Server企业管理器建立SQL Server注册及注册属性的修改。

3、熟悉SQL Server的操作环境，了解主要菜单命令的功能和窗口，如新建
数据库，数据库表的建立，导入、导出数据等。

4、在某个已注册实例中，认识与体会SQL Server的体系结构。

5、在某个已注册实例的数据库范例中，认识数据库的组成。

二、实验步骤
1、启动本机的SQL Server服务；
2、利用企业管理器，在本地创建名为edu_d的数据库，并在edu_d数据库
中添加学生信息表STU_INFO，并随意在表中添加数据。

3、通过查询分析器，查询步骤2中添加的数据。

4、将本地master库中的表syscharsets导出，再导入到edu_d中。

5、进一步熟悉企业管理器以及查询分析器的其它相关操作。

实验二：用SQL实现单表查询
一、实验内容
1、对已有的数据库pubs中的表完成以下查询功能：
a)查询jobs表中所有属性列信息
Select * from jobs
b)查询employee表中的雇员号和雇员名信息
Select emp_id,fname,lname from employee
c)查询employee表中雇员工作年限信息
select emp_id,year(getdate())-year(hire_date) from dbo.employee
2、对employee数据表完成以下查询
a)查询名字首字母为F的雇员信息
select * from employee where fname like 'F%'
b)查询工种代号为11的所有雇员信息
select * from employee where job_id = 11
c)查询雇佣年限超过5年的雇员信息
select * from employee
where year(getdate())- year(hire_date) > 5
d)查询工种代号在5~8的雇员信息
select * from employee where job_id between 5 and 8
e)查询名字为Maria的雇员信息
select * from employee where fname = 'maria'
f)查询姓名中包含字符sh的所有雇员信息
select * from employee where fname like '%sh%' or lname like '%sh%'
3、查询sales表中1993-1-1前订货的订单信息
select * from sales where ord_date < '1993-1-1'
或者
select * from sales where year(ord_date)< 1993
4、查询authors表中以下相关信息：
a)Johnson white的电话号码
select phone from dbo.authors where au_fname = 'johnson' and au_lname = 'white'
b)住在oakland的所有作者的姓名以及住址
select au_lname,au_fname,address from dbo.authors where city = 'oakland'
c)住在10932 Bigge Rd.作者的详细信息
select * from dbo.authors where address = '10932 Bigge Rd.'
实验三：连接查询
一、实验目的
1、掌握多表之间的连接查询
2、掌握使用集函数完成特殊的查询
3、学会对查询结果排序
4、联系数据汇总查询
二、实验内容
1.从edu_d数据库中的stu_info、xk、gdept、gfied、gban、gcourse表
中做以下查询：
a)查询信息科学与工程学院的学生的学号、姓名、性别
select XH,XM,XBM from STU_INFO,GDEPT where STU_INFO.xsh =
GDEPT.xsh and xsm='信息科学与工程'
b)查询成绩在85分以上的学生的学号、姓名、课程名称
select STU_INFO.XH,XM,KCH from STU_INFO,XK where
STU_INFO.XH = XK.XH and KSCJ>85
c)查询学号的前4位是＇2001＇的学生的学号、姓名、学院名称
select XH,XM,XSM from STU_INFO,GDEPT where STU_INFO.XSH =
GDEPT.XSH and XH LIKE '2001%'
d)查询高等数学（kch=090101）成绩不及格的同学的学号、姓名、课
程名称
select XK.XH,XM,KSCJ,KM
from STU_INFO,XK,GCOURSE
where STU_INFO.xh = XK.xh and XK.kch = GCOURSE.kch and XK.kch =
'090101' and kscj < 60
e)查询每个同学在2001-2002学年第一学期（kkny=20011）的总分、
平均分
select xh,sum(kscj) as '总分',avg(kscj) as '平均分'
from xk
where kkny = '20011'
group by xh
f)查询与李明在同一个专业学习的同学的学号、姓名、性别、班级，
并按学号升序排序
select xh,xm,XBM,BH
from STU_INFO
where zyh in
(select zyh
from stu_info
where xm = '李明')
order by xh
g)使用嵌套查询语句查询信息科学与工程学院的男生中年龄最小的学
生的信息
select *
from STU_INFO
where nl =
(select min(nl)
from STU_INFO
) and xbm=’男’
h)查询化学化工学院的各班的人数
select bh,count(xh)
from STU_INFO,GDEPT
where STU_INFO.xsh = GDEPT.xsh and xsm = '信息科学与工程'
group by bh
实验四：嵌套查询
一、实验目的
1、掌握多表之间的嵌套查询
2、掌握使用集函数完成特殊的查询
3、学会对查询结果排序
4、练习集合查询
二、实验内容
1.从edu_d数据库中的stu_info、xk、gdept、gfied、gban、gcourse表
中做以下查询：
a)查询高等数学（kch=090101）成绩不及格的同学的学号、姓名、课
程代码
select XK.XH,XM,KSCJ,KM
from STU_INFO,XK,GCOURSE
where STU_INFO.xh = XK.xh and XK.kch = GCOURSE.kch and XK.kch =
'090101' and kscj < 60
b)查询每个同学在2001-2002学年第一学期（kkny=20011）的总分、
平均分
select xh,sum(kscj) as '总分',avg(kscj) as '平均分'
from xk
where kkny = '20011'
group by xh
c)使用嵌套查询语句查询信息科学与工程学院的男生中年龄最小的学
生的信息
select *
from STU_INFO
where nl =
(select min(nl)
from STU_INFO
)
d)查询化学化工学院的各班的人数
select bh,count(xh)
from STU_INFO
where xsh =
(select xsh
from GDEPT
where xsm = '信息科学与工程'
)
group by bh
实验五：统计查询
一、实验目的
二、实验内容
1.从edu_d的表stu_info中查询全校共有多少个班级。

select count(distinct bh)
from STU_INFO
2.查询材料物理专业考试成绩大于80分的数目。

select count(kscj)
from XK
where xh in
(select xh
from stu_info
where zyh =
(select zyh
from GFIED
where zym = '材料物理')) and kscj > 80
3.查询材料物理专业有考试成绩不及格的人数。

select count(distinct xh)
from XK
where xh in
(select xh
from stu_info
where zyh =
(select zyh
from GFIED
where zym = '材料物理'))
and kscj < 60
4.查询范玉华老师和王金刚老师各自所授课程学生考试的平均分、最高
分。

select jsh,avg(kscj) as '平均分',max(kscj) as '最高分'
from XK
where jsh = '范玉华' or jsh = '王金刚'
group by jsh
或者
select jsh,avg(kscj) as '平均分',max(kscj) as '最高分'
from XK
where jsh = '范玉华'
group by jsh
union
select jsh,avg(kscj) as '平均分',max(kscj) as '最高分'
from XK
where jsh = '王金刚'
group by jsh
5.查询各课程的平均分，最高分。

select kch,avg(kscj) as '平均分',max(kscj) as '最高分'
from XK
group by kch
6.查询指导学生人数超过400人的老师。

select jsh,count(xh)
from XK
group by jsh
having count(xh)>400
实验六：SQL Server中视图的创建使用
一、实验目的
1、学会在SQL Server 2000中创建、更新、删除视图，并对视图执行各种情况的数据查询
2、了解视图的外模式特征
二、实验内容
1、在企业管理里，建立视图，查询所有学生的学号、姓名以及考试成绩
2、利用查询分析器建立视图，从edu_d的表stu_info中查询全校共有多少
个班级
3、利用查询分析器建立视图，从edu_d的表stu_info中查询全校各个班级
的名称
4、利用查询分析器建立视图，查询材料学院（xsh=01）和化学院（xsh=02）
学生的姓名、性别、班级等信息
5、利用查询分析器建立视图，查询材料学院姓张的学生
6、利用查询分析器建立视图，查询选修了“河流动力学”学生人数
7、利用查询分析器建立视图，查询材料学院“材料0168班”的每个学生及
其选修课程的情况
8、利用查询分析器建立视图，查询选修了高等数学的学生姓名
实验七：数据定义和数据更新
一、实验目的
1、学会用SQL语句创建数据表，包括插入、修改和删除等
2、掌握用SQL语句进行数据更新
二、实验内容
1、用SQL语句创建以下3张表：
S表
SNO SNAME STATUS CITY
S1 精益10 天津
S2 盛锡10 北京
S3 东方红10 北京
S4 丰泰盛20 天津
S5 为民10 上海
P表
PNO PNAME COLOR WEIGHT
1 螺母红12
2 螺栓绿17
3 螺丝刀蓝14
4 凸轮红20
5 齿轮蓝30
6 螺丝刀红14
PS表
SNO PNO QTY PRICE TOTAL S1 1 200 0.5
S1 2 100 0.8
S2 3 700 2.0
S3 1 400 0.5
S4 3 300 2.0
S5 5 700 8.0
S2 6 800 2.0
S4 2 500 0.8
S5 3 100 2.0
2、删除PS表中的S3所供应的所有商品信息。