数据库基础与应用复习一.docx
《数据库基础与应用复习一.docx》由会员分享，可在线阅读，更多相关《数据库基础与应用复习一.docx（22页珍藏版）》请在冰豆网上搜索。
数据库基础与应用复习一
学生号
课程号
成绩
0101001
C001
78
0101001
C004
62
0102005
E002
73
0202003
C001
94
0202003
C004
65
0202003
X003
80
0303001
C001
76
0304006
E002
72
学生号
姓名
性别
专业
0101001
王明
男
计算机
0102005
刘芹
女
电子
0202003
张鲁
男
电子
0303001
赵红
女
电气
0304006
刘川
男
通信
课程号
课程名
课程学分
C001
C++语言
4
C004
操作系统
3
E002
电子技术
5
X003
信号原理
4
数据库基础与应用复习
（一）
必须要熟悉书上的两个数据库实例
1. 教学库：
包括学生、课程和选课三个基本表
它们的定义分别为：
学生（学生号 char（7）,姓名 char（6）,性别 char
（2）, 专业 char（10））
课程（课程号 char（4）,课程名 char（10）,课程学分 int）
选课（学生号 char（7）,课程号 char（4）,成绩 int）
学生表：
课程表：
选课表：
2. 商品库：
包括商品表 1 和商品表 2 两个基本表
它们的定义分别为：
商品代号
分类名
单价
数量
DBX-134
电冰箱
1456
8
DSJ-120
电视机
1865
15
DSJ-180
电视机
2073
10
DSJ-340
电视机
3726
5
KTQ-12
空调器
2800
12
WBL-6
微波炉
640
10
XYJ-13
洗衣机
468
20
XYJ-20
洗衣机
873
12
商品代号
产地
品牌
DBX-134
北京
雪花
DSJ-120
南京
熊猫
DSJ-180
南京
熊猫
DSJ-340
北京
牡丹
KTQ-12
无锡
春兰
WBL-6
青岛
海信
XYJ-13
无锡
小天鹅
XYJ-20
山西
海棠
商品表 1（商品代号 char（8）,分类名 char（8）,单价 float,数量 int）
商品表 2（商品代号 char（8）,产地 char（6）,品牌 char（6））
商品表 1：
商品表 2：
根据主教材第四章所给的商品库和教学库，按照下列所给的每条 SQL 查询语句写出相应的
功能
1．select x.商品代号,分类名,数量,品牌
from 商品表 1 x,商品表 2 y
where x.商品代号=y.商品代号
从商品库中查询出每一种商品的商品代号、分类名、数量和品牌等信息
2．select distinct 产地
from 商品表 2
从商品库中查询出所有商品的不同产地
3．select distinct 品牌
from 商品表 2
从商品库中查询出所有商品的不同品牌
4．select count（distinct 产地） as 产地数
from 商品表 2
从商品库中查询出所有商品的不同产地的总数
5．select x.商品代号,分类名,产地,品牌
from 商品表 1 x,商品表 2 y
where x.商品代号=y.商品代号 and （品牌='熊猫' or 品牌='春兰'）
从商品库中查询出品牌为熊猫或春兰的所有商品的商品代号、分类名、产地和品牌等信
息
6．select 学生.学生号,count（*） as 选课门数
from 学生,选课
where 学生.学生号=选课.学生号
group by 学生.学生号
从教学库中查询出每个学生的选课门数
7．select 课程.课程号,课程.课程名,count（*） as 选课人数
from 课程,选课
where 课程.课程号=选课.课程号
group by 课程.课程号,课程.课程名
从教学库中查询出每门课程的选课学生数
8．select distinct x.*
from 学生 x,选课 y,选课 z
where y.学生号=z.学生号 and y.课程号<>z.课程号 and x.学生号=y.学生号
从教学库中查询出至少选修了两门课程的全部学生
9．select *
from 学生
where 学生号 in （select 学生号
from 选课
group by 学生号 having count（*）=1）
从教学库中查询出只选修了一门课程的全部学生
10．select x.学生号,y.学生号,y.课程号
from 选课 x,选课 y
where x.学生号=@s1 and y.学生号=@s2 and x.课程号=y.课程号
注：
一个由字母开头的标识符再加上@字符前缀，则表示为一个变量，假定已保存着
一个相应值。
从教学库中查询出学生号为@s1 的学生和学生号为@s2 的学生所选修的共同课程的课
程号
11．select x.*
from 课程 x,选课 y
where x.课程号=y.课程号 and y.学生号=@s1
and y.课程号 not in （select 课程号
from 选课
where 选课.学生号=@s2）
从教学库中查询出学生号为@s1 的学生所选修、而学生号为@s2 的学生没有选修的全
部课程
12．select *
from 课程
where not exists （select *
from 选课
where 课程.课程号=选课.课程号）
从教学库中查询出所有未被学生选修的课程
13．select *
from 课程
where exists （select *
from 选课
where 课程.课程号=选课.课程号）
从教学库中查询出所有已被学生选修的课程
14．select *
from 课程
where exists （select 课程号
from 选课
where 课程.课程号=选课.课程号
group by 课程号 having count（*） between 2 and 4）
从教学库中查询出被 2 至 4 名学生选修的所有课程
15．select *
from 学生
where exists （select *
from 选课
where 学生.学生号=选课.学生号
group by 选课.学生号 having count（*）=3）
从教学库中查询出同时选修了 3 门课程的全部学生
16．select *
from 学生
where 学生号 in （select 学生号
from 选课
group by 学生号 having count（*）<=2） or
not exists （select *
from 选课
where 学生.学生号=选课.学生号）
从教学库中查询出最多选修了 2 门课程（含未选任何课程）的全部学生
17．select 专业,count（*） as 专业人数
from 学生
group by 专业
order by 专业人数 desc
从教学库中查询出每个专业的学生人数，并按人数多少降序排列
18．select 专业,性别,count（*） as 人数
from 学生
group by 专业,性别
order by 专业
从教学库中查询出每个专业每种性别的学生人数，并按专业升序排列
19．select x.*,课程名,课程学分,成绩
from 学生 x,课程 y,选课 z
where x.学生号=z.学生号 and y.课程号=z.课程号
order by x.学生号,z.成绩
从教学库中查询出每个学生选课的全部情况，并依次按学生号和成绩升序排列
20．select *
from 学生
where not exists（select y.课程号
from 学生 x inner join 选课 y on x.学生号=y.学生
号
where x.姓名=@a and not exists （select *
from 选课
where 学生.学生号=选课.学生号
and y.课程号=选课.课程号）
）
从教学库中查询出选修了姓名为@a 的学生的全部选课的所有学生
根据教材第四章所给的商品库和教学库，按照下列所给的每种功能写出相应的查询语句。
在名称为商品库的数据库中包含有商品表 1 和商品表 2，它们的定义分别为：
商品表 1（商品代号 char（8）,分类名 char（8）,单价 float,数量 int）
商品表 2（商品代号 char（8）,产地 char（6）,品牌 char（6）,）
在名称为教学库的数据库中包含有学生、课程和选课三个表，它们的定义分别为：
学生（学生号 char（7）,姓名 char（6）,性别 char
（2）,出生日期 datetime, 专业 char（10）,
年级 int）
课程（课程号 char（4）,课程名 char（10）,课程学分 int）
选课（学生号 char（7）,课程号 char（4）,成绩 int）
1．从商品库中查询出每种商品的商品代号、单价、数量和产地。
select 商品表 1.商品代号,单价,数量,产地
from 商品表 1,商品表 2
where 商品表 1.商品代号=商品表 2.商品代号
2．从商品库中查询出数量大于 10 的商品种数。
select count（*）
from 商品表 1
where 数量>10
3．从商品库中查询出数量在 10 和 20 之间的所有商品。
select *
from 商品表 1
where 数量 between 10 and 20 （或 where 数量>=10 and 数量<=20）
4．从商品库中查询出每类（即分类名相同）商品的最高单价。
select 分类名,max（单价） as 最高单价
from 商品表 1
group by 分类名
5．从商品库中查询出每类（即分类名相同）商品的平均数量。
select 分类名,avg（数量） as 平均数量
from 商品表 1
group by 分类名
6．从商品库中查询出每类（即分类名相同）商品的总数量。
select 分类名,sum（数量） as 总数量
from 商品表 1
group by 分类名
7．从商品库中查询出单价大于等于 2000 元、或者数量大于等于 15 的所有商品
select *
from 商品表 1
where 单价>=2000 or 数量>=15
8．从商品库中查询出比所有电视机的单价都高的每种商品。
select *
from 商品表 1
where 单价>all（select 单价
from 商品表 1
where 分类名='电视机'）
或
select *
from 商品表 1
where 单价>all（select max（单价）
from 商品表 1
where 分类名='电视机'）
9．从商品库中查询出比所有商品单价的平均值要高的全部商品。
select *
from 商品表 1
where 单价>all（select avg（单价）
from 商品表 1）
10．从商品库中查询出数量最多的一种商品。
select *
from 商品表 1
where 数量=some（select max（数量）
from 商品表 1）
11．从商品库中查询出同一类商品多于一种的所有分类名。
select distinct 分类名
from 商品表 1
group by 分类名 having count（*）>1
12．从商品库中查询出同一产地的商品只有一种的所有商品。
select 商品表 1.*,产地
from 商品表 1,商品表 2
where 商品表 1.商品代号=商品表 2.商品代号 and
产地 in （select 产地
from 商品表 1 x,商品表 2 y
where x.商品代号=y.商品代号
group by 产地 having count（*）=1）
13．从商品库中查询出每种商品的总价值，并按降序排列出来。
select *,单价*数量 as 总价值
from 商品表 1
order by 总价值 desc
14．从教学库中查询出至少有两名学生所选修的全部课程。
select distinct x.*
from 课程 x,选课 y,选课 z
where y.课程号=z.课程号 and y.学生号<>z.学生号 and x.课程号=y.课程号
或：
select *
from 课程
where 课程号 in （select 课程号
from 选课
group by 课程号 having count（*）>=2）
15．从教学库中查询出至少选修了姓名为@m1 学生所选课程中一门课的全部学生。
select distinct 学生.*
from 学生,选课
where 学生.学生号=选课.学生号 and 课程号=any（select 课程号
from 学生,选课
where 学生.学生号=选课.学生号
and 姓名=@m1 ）
16．从教学库中查询出每门课程被选修的学生人数，并按所选人数的升序排列出课程号、
课程名和选课人数。
select 课程.课程号,课程名,count（课程.课程号） as 人数
from 课程,选课
where 课程.课程号=选课.课程号
group by 课程.课程号,课程名
order by 人数
写出下列每条语句或程序段的功能
假设存在名为 AAA 的数据库，包括 Students（学号char（8），姓名varchar（8），年龄
int，专业varchar（20），入学日期DateTime）和 Score（学号char（8），课程名
varchar（10）,成绩 numeric（5,2））两张表。
1．SELECT *
FROM Students
WHERE DATEPART（year,入学日期） =DATEPART（year,GETDATE（））
从 Students 表中查询出所有当年（系统时间）入学的学生记录。
2．DECLARE @MyNO CHAR（8）
SET @MyNO='20030001'
IF （SELECT 专业 FROM Students WHERE 学号=@MyNO）='计算机软件'
BEGIN
SELECT AVG（成绩） AS 平均成绩
FROM Score
WHERE 学号=@MyNO
END
ELSE
PRINT '学号为' +@MyNO+'的学生不存在或不属于软件专业'
GO
首先定义一个名为@MyNo 的局部变量，并给它赋初值，如果@MyNo 属于计算机软件专
业，则显示出平均成绩，否则显示“学号为@MyNo 的学生不存在或不属于软件专业”。
3．declare @a numeric（5,2）
set @a=（select avg（成绩） from score）
select *
from score
where 成绩>=@a
从 score 表中查询出成绩大于等于平均成绩的所有记录。
4．declare @a numeric（5,2）,@b numeric（5,2）
set @a=（select max（成绩） from score）
set @b=（select min（成绩） from score）
print @a-@b
求出 score 表中最高成绩与最低成绩的分数之差。
5．declare @a char（6）
set @a='刘亮'
if（exists（select * from students where 姓名=@a））
print '姓名为'+@a+'的同学存在！
'
else
print '姓名为'+@a+'的同学不存在！
'
从 students 表中查询姓名为@a 的值（即“刘亮”）的同学是否存在，根据不同情况
显示出相应信息。
6．declare @a char（8）
set @a='计算机'
select 计算机专业人数=count（*）
from students
where left（专业,3）=@a
从 students 表中统计出专业名开头为@a 的值（即“计算机”）的所有学生人数。
7．select year（入学日期） as 入学年份,count（*） as 人数
from students
group by year（入学日期）
从 students 表中分组统计出每个年份入学的学生人数。
8．select month（入学日期） as 入学月份,count（*） as 人数
from students
group by month（入学日期）
从 students 表中分组统计出每个月份入学的学生人数。
9．select day（入学日期） as 入学日号,count（*） as 人数
from students
group by day（入学日期）
从 students 表中分组统计出每个日子入学的学生人数。
10．create procedure xxk1
as
begin
select *
from students x,score y
where x.学号=y.学号
end
显示出 AAA 库中所有学生的记录信息及选课成绩
11.create procedure xxk2
as
begin
select x.学号,x.姓名,x.专业,count（*） as 门数
from students x,score y
where x.学号=y.学号
group by x.学号,x.姓名,x.专业
end
显示出 AAA 库中每个学生的学号、姓名、专业等信息及选课门数
12.create procedure xxk3
as
begin
select 学号,avg（成绩） as 平均成绩
from score
group by 学号
end
显示出 AAA 库中每个学生的平均成绩
13.createprocedurexxk4（@achar（8）,@bvarchar（10）,@c
numeric（5,2））
as
begin
update score
set 成绩=@c
where 学号=@a and 课程名=@b
end
修改 score 表中学号为@a 的值、课程名为@b 的值的学生的成绩为@c 的值。
14.createprocedurexxk5（@achar（8）,@bvarchar（10）,@c
numeric（5,2））
as
begin
insert into score
values（@a,@b,@c）
end
向 score 表中插入学号为@a 的值、课程名为@b 的值、成绩为@c 的值的学生成绩记录。
15.create procedure xxk6（@a char（8）,@b varchar（10））
as
begin
delete from score
where 学号=@a and 课程名=@b
end
从 score 表中删除学号为@a 的值、课程名为@b 的值的学生成绩记录。
16.declare @a char（8）,@b varchar（10）,@c numeric（5,2）
declare @d int
set @d=0
declare xxx cursor
for select 学号,课程名,成绩
from score
open xxx
fetch xxx into @a,@b,@c
while @@fetch_status=0
begin
set @d=@d+1
fetch from xxx into @a,@b,@c
end
close xxx
deallocate xxx
print @d
从 score 表中统计并显示出记录总数
17.declare @a char（8）,@b varchar（10）,@c numeric（5,2）
declare xxx cursor
for select 学号,课程名,成绩
from score
open xxx
fetch xxx into @a,@b,@c
while @@fetch_status=0
begin
print @a+replicate（' ',3）+@b+str（@c）+replicate（' ',3）
+（case
when @c>=90 then '优秀'
when @c>=70 then '良好'
when @c>=60 then '及格'
else '不及格'
end）
fetch from xxx into @a,@b,@c
end
close xxx
deallocate xxx
显示出 score 表中每个成绩记录，并根据具体成绩在每条记录最后给出优秀、良好、
及格、不及格等相应等级。
18.declare @c numeric（5,2）
declare @c1 int, @c2 int, @c3 int, @c4 int
set @c1=0; set @c2=0; set @c3=0; set @c4=0
declare xxx cursor
for select 成绩 from score
open xxx
fetch xxx into @c
while @@fetch_status=0
begin
if（@c>=90） set @c1=@c1+1;
else if（@c>=70） set @c2=@c2+1;
else if（@c>=60） set @c3=@c3+1;
else set @c4=@c4+1
fetch from xxx into @c
end
close xxx
deallocate xxx
print '优秀生人数:
'+str（@c1,5）;
print '良好生人数:
'+str（@c2,5）;
print '及格生人数:
'+str（@c3,5）;
print '及格生人数:
'+str（@c4,5）
从 score 表中按成绩统计并显示出优秀、良好、及格、不及格各多少人。
19.declare @a char（8）,@b varchar（10）
declare @c numeric（5,2）
declare @d int
set @d=80
declare xxx cursor
for select 学号,课程名,成绩 from score
open xxx
fetch xxx into @a,@b,@c
while @@fetch_status=0
begin
if（@c>=@d） print @a+replicate（' ',3）+@b+str（@c,5）
fetch from xxx into @a,@b,@c
end
clo
升级会员 