数据库基础与应用复习一.docx
《数据库基础与应用复习一.docx》由会员分享，可在线阅读，更多相关《数据库基础与应用复习一.docx（16页珍藏版）》请在冰豆网上搜索。
数据库基础与应用复习一
数据库基础与应用复习
（一）
必须要熟悉书上的两个数据库实例
1.教学库：
包括学生、课程和选课三个基本表
它们的定义分别为：
学生（学生号char（7）,姓名char（6）,性别char
（2）,专业char（10））
课程（课程号char（4）,课程名char（10）,课程学分int）
选课（学生号char（7）,课程号char（4）,成绩int）
学生表：
学生号
姓名
性别
专业
王明
男
计算机
刘芹
女
电子
张鲁
男
电子
赵红
女
电气
刘川
男
通信
课程表：
课程号
课程名
课程学分
C001
C++语言
4
C004
操作系统
3
E002
电子技术
5
X003
信号原理
4
选课表：
学生号
课程号
成绩
C001
78
C004
62
E002
73
C001
94
C004
65
X003
80
C001
76
E002
72
2.商品库：
包括商品表1和商品表2两个基本表
它们的定义分别为：
商品表1（商品代号char（8）,分类名char（8）,单价float,数量int）
商品表2（商品代号char（8）,产地char（6）,品牌char（6））
商品表1：
商品代号
分类名
单价
数量
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
商品表2：
商品代号
产地
品牌
DBX-134
北京
雪花
DSJ-120
南京
熊猫
DSJ-180
南京
熊猫
DSJ-340
北京
牡丹
KTQ-12
无锡
春兰
WBL-6
青岛
海信
XYJ-13
无锡
小天鹅
XYJ-20
山西
海棠
根据主教材第四章所给的商品库和教学库，按照下列所给的每条SQL查询语句写出相应的功能
1．selectx.商品代号,分类名,数量,品牌
from商品表1x,商品表2y
wherex.商品代号=y.商品代号
从商品库中查询出每一种商品的商品代号、分类名、数量和品牌等信息
2．selectdistinct产地
from商品表2
从商品库中查询出所有商品的不同产地
3．selectdistinct品牌
from商品表2
从商品库中查询出所有商品的不同品牌
4．selectcount（distinct产地）as产地数
from商品表2
从商品库中查询出所有商品的不同产地的总数
5．selectx.商品代号,分类名,产地,品牌
from商品表1x,商品表2y
wherex.商品代号=y.商品代号and（品牌='熊猫'or品牌='春兰'）
从商品库中查询出品牌为熊猫或春兰的所有商品的商品代号、分类名、产地和品牌等信息
6．select学生.学生号,count（*）as选课门数
from学生,选课
where学生.学生号=选课.学生号
groupby学生.学生号
从教学库中查询出每个学生的选课门数
7．select课程.课程号,课程.课程名,count（*）as选课人数
from课程,选课
where课程.课程号=选课.课程号
groupby课程.课程号,课程.课程名
从教学库中查询出每门课程的选课学生数
8．selectdistinctx.*
from学生x,选课y,选课z
wherey.学生号=z.学生号andy.课程号<>z.课程号andx.学生号=y.学生号
从教学库中查询出至少选修了两门课程的全部学生
9．select*
from学生
where学生号in（select学生号
from选课
groupby学生号havingcount（*）=1）
从教学库中查询出只选修了一门课程的全部学生
10．selectx.学生号,y.学生号,y.课程号
from选课x,选课y
wherex.学生号=@s1andy.学生号=@s2andx.课程号=y.课程号
注：
一个由字母开头的标识符再加上@字符前缀，则表示为一个变量，假定已保存着一个相应值。
从教学库中查询出学生号为@s1的学生和学生号为@s2的学生所选修的共同课程的课程号
11．selectx.*
from课程x,选课y
wherex.课程号=y.课程号andy.学生号=@s1
andy.课程号notin（select课程号
from选课
where选课.学生号=@s2）
从教学库中查询出学生号为@s1的学生所选修、而学生号为@s2的学生没有选修的全部课程
12．select*
from课程
wherenotexists（select*
from选课
where课程.课程号=选课.课程号）
从教学库中查询出所有未被学生选修的课程
13．select*
from课程
whereexists（select*
from选课
where课程.课程号=选课.课程号）
从教学库中查询出所有已被学生选修的课程
14．select*
from课程
whereexists（select课程号
from选课
where课程.课程号=选课.课程号
groupby课程号havingcount（*）between2and4）
从教学库中查询出被2至4名学生选修的所有课程
15．select*
from学生
whereexists（select*
from选课
where学生.学生号=选课.学生号
groupby选课.学生号havingcount（*）=3）
从教学库中查询出同时选修了3门课程的全部学生
16．select*
from学生
where学生号in（select学生号
from选课
groupby学生号havingcount（*）<=2）or
notexists（select*
from选课
where学生.学生号=选课.学生号）
从教学库中查询出最多选修了2门课程（含未选任何课程）的全部学生
17．select专业,count（*）as专业人数
from学生
groupby专业
orderby专业人数desc
从教学库中查询出每个专业的学生人数，并按人数多少降序排列
18．select专业,性别,count（*）as人数
from学生
groupby专业,性别
orderby专业
从教学库中查询出每个专业每种性别的学生人数，并按专业升序排列
19．selectx.*,课程名,课程学分,成绩
from学生x,课程y,选课z
wherex.学生号=z.学生号andy.课程号=z.课程号
orderbyx.学生号,z.成绩
从教学库中查询出每个学生选课的全部情况，并依次按学生号和成绩升序排列
20．select*
from学生
wherenotexists（selecty.课程号
from学生xinnerjoin选课yonx.学生号=y.学生号
wherex.姓名=@aandnotexists（select*
from选课
where学生.学生号=选课.学生号
andy.课程号=选课.课程号）
）
从教学库中查询出选修了姓名为@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的商品种数。
selectcount（*）
from商品表1
where数量>10
3．从商品库中查询出数量在10和20之间的所有商品。
select*
from商品表1
where数量between10and20（或where数量>=10and数量<=20）
4．从商品库中查询出每类（即分类名相同）商品的最高单价。
select分类名,max（单价）as最高单价
from商品表1
groupby分类名
5．从商品库中查询出每类（即分类名相同）商品的平均数量。
select分类名,avg（数量）as平均数量
from商品表1
groupby分类名
6．从商品库中查询出每类（即分类名相同）商品的总数量。
select分类名,sum（数量）as总数量
from商品表1
groupby分类名
7．从商品库中查询出单价大于等于2000元、或者数量大于等于15的所有商品
select*
from商品表1
where单价>=2000or数量>=15
8．从商品库中查询出比所有电视机的单价都高的每种商品。
select*
from商品表1
where单价>all（select单价
from商品表1
where分类名='电视机'）
或
select*
from商品表1
where单价>all（selectmax（单价）
from商品表1
where分类名='电视机'）
9．从商品库中查询出比所有商品单价的平均值要高的全部商品。
select*
from商品表1
where单价>all（selectavg（单价）
from商品表1）
10．从商品库中查询出数量最多的一种商品。
select*
from商品表1
where数量=some（selectmax（数量）
from商品表1）
11．从商品库中查询出同一类商品多于一种的所有分类名。
selectdistinct分类名
from商品表1
groupby分类名havingcount（*）>1
12．从商品库中查询出同一产地的商品只有一种的所有商品。
select商品表1.*,产地
from商品表1,商品表2
where商品表1.商品代号=商品表2.商品代号and
产地in（select产地
from商品表1x,商品表2y
wherex.商品代号=y.商品代号
groupby产地havingcount（*）=1）
13．从商品库中查询出每种商品的总价值，并按降序排列出来。
select*,单价*数量as总价值
from商品表1
orderby总价值desc
14．从教学库中查询出至少有两名学生所选修的全部课程。
selectdistinctx.*
from课程x,选课y,选课z
wherey.课程号=z.课程号andy.学生号<>z.学生号andx.课程号=y.课程号
或：
select*
from课程
where课程号in（select课程号
from选课
groupby课程号havingcount（*）>=2）
15．从教学库中查询出至少选修了姓名为@m1学生所选课程中一门课的全部学生。
selectdistinct学生.*
from学生,选课
where学生.学生号=选课.学生号and课程号=any（select课程号
from学生,选课
where学生.学生号=选课.学生号
and姓名=@m1）
16．从教学库中查询出每门课程被选修的学生人数，并按所选人数的升序排列出课程号、课程名和选课人数。
select课程.课程号,课程名,count（课程.课程号）as人数
from课程,选课
where课程.课程号=选课.课程号
groupby课程.课程号,课程名
orderby人数
写出下列每条语句或程序段的功能
假设存在名为AAA的数据库，包括Students（学号char（8），姓名varchar（8），年龄int，专业varchar（20），入学日期DateTime）和Score（学号char（8），课程名varchar（10）,成绩numeric（5,2））两张表。
1．SELECT*
FROMStudents
WHEREDATEPART（year,入学日期）=DATEPART（year,GETDATE（））
从Students表中查询出所有当年（系统时间）入学的学生记录。
2．DECLARE@MyNOCHAR（8）
SET@MyNO=''
IF（SELECT专业FROMStudentsWHERE学号=@MyNO）='计算机软件'
BEGIN
SELECTAVG（成绩）AS平均成绩
FROMScore
WHERE学号=@MyNO
END
ELSE
PRINT'学号为'+@MyNO+'的学生不存在或不属于软件专业'
GO
首先定义一个名为@MyNo的局部变量，并给它赋初值，如果@MyNo属于计算机软件专业，则显示出平均成绩，否则显示“学号为@MyNo的学生不存在或不属于软件专业”。
3．declare@anumeric（5,2）
set@a=（selectavg（成绩）fromscore）
select*
fromscore
where成绩>=@a
从score表中查询出成绩大于等于平均成绩的所有记录。
4．declare@anumeric（5,2）,@bnumeric（5,2）
set@a=（selectmax（成绩）fromscore）
set@b=（selectmin（成绩）fromscore）
print@a-@b
求出score表中最高成绩与最低成绩的分数之差。
5．declare@achar（6）
set@a='刘亮'
if（exists（select*fromstudentswhere姓名=@a））
print'姓名为'+@a+'的同学存在！
'
else
print'姓名为'+@a+'的同学不存在！
'
从students表中查询姓名为@a的值（即“刘亮”）的同学是否存在，根据不同情况显示出相应信息。
6．declare@achar（8）
set@a='计算机'
select计算机专业人数=count（*）
fromstudents
whereleft（专业,3）=@a
从students表中统计出专业名开头为@a的值（即“计算机”）的所有学生人数。
7．selectyear（入学日期）as入学年份,count（*）as人数
fromstudents
groupbyyear（入学日期）
从students表中分组统计出每个年份入学的学生人数。
8．selectmonth（入学日期）as入学月份,count（*）as人数
fromstudents
groupbymonth（入学日期）
从students表中分组统计出每个月份入学的学生人数。
9．selectday（入学日期）as入学日号,count（*）as人数
fromstudents
groupbyday（入学日期）
从students表中分组统计出每个日子入学的学生人数。
10．createprocedurexxk1
as
begin
select*
fromstudentsx,scorey
wherex.学号=y.学号
end
显示出AAA库中所有学生的记录信息及选课成绩
11.createprocedurexxk2
as
begin
selectx.学号,x.姓名,x.专业,count（*）as门数
fromstudentsx,scorey
wherex.学号=y.学号
groupbyx.学号,x.姓名,x.专业
end
显示出AAA库中每个学生的学号、姓名、专业等信息及选课门数
12.createprocedurexxk3
as
begin
select学号,avg（成绩）as平均成绩
fromscore
groupby学号
end
显示出AAA库中每个学生的平均成绩
13.createprocedurexxk4（@achar（8）,@bvarchar（10）,@cnumeric（5,2））
as
begin
updatescore
set成绩=@c
where学号=@aand课程名=@b
end
修改score表中学号为@a的值、课程名为@b的值的学生的成绩为@c的值。
14.createprocedurexxk5（@achar（8）,@bvarchar（10）,@cnumeric（5,2））
as
begin
insertintoscore
values（@a,@b,@c）
end
向score表中插入学号为@a的值、课程名为@b的值、成绩为@c的值的学生成绩记录。
15.createprocedurexxk6（@achar（8）,@bvarchar（10））
as
begin
deletefromscore
where学号=@aand课程名=@b
end
从score表中删除学号为@a的值、课程名为@b的值的学生成绩记录。
16.declare@achar（8）,@bvarchar（10）,@cnumeric（5,2）
declare@dint
set@d=0
declarexxxcursor
forselect学号,课程名,成绩
fromscore
openxxx
fetchxxxinto@a,@b,@c
while@@fetch_status=0
begin
set@d=@d+1
fetchfromxxxinto@a,@b,@c
end
closexxx
deallocatexxx
print@d
从score表中统计并显示出记录总数
17.declare@achar（8）,@bvarchar（10）,@cnumeric（5,2）
declarexxxcursor
forselect学号,课程名,成绩
fromscore
openxxx
fetchxxxinto@a,@b,@c
while@@fetch_status=0
begin
print@a+replicate（'',3）+@b+str（@c）+replicate（'',3）
+（case
when@c>=90then'优秀'
when@c>=70then'良好'
when@c>=60then'及格'
else'不及格'
end）
fetchfromxxxinto@a,@b,@c
end
closexxx
deallocatexxx
显示出score表中每个成绩记录，并根据具体成绩在每条记录最后给出优秀、良好、及格、不及格等相应等级。
18.declare@cnumeric（5,2）
declare@c1int,@c2int,@c3int,@c4int
set@c1=0;set@c2=0;set@c3=0;set@c4=0
declarexxxcursor
forselect成绩fromscore
openxxx
fetchxxxinto@c
while@@fetch_status=0
begin
if（@c>=90）set@c1=@c1+1;
elseif（@c>=70）set@c2=@c2+1;
elseif（@c>=60）set@c3=@c3+1;
elseset@c4=@c4+1
fetchfromxxxinto@c
end
closexxx
deallocatexxx
print'优秀生人数:
'+str（@c1,5）;
print'良好生人数:
'+str（@c2,5）;
print'及格生人数:
'+str（@c3,5）;
print'及格生人数:
'+str（@c4,5）
从score表中按成绩统计并显示出优秀、良好、及格、不及格各多少人。
19.declare@achar（8）,@bvarchar（10）
declare@cnumeric（5,2）
declare@dint
set@d=80
declarexxxcursor
forselect学号,课程名,成绩fromscore
openxxx
fetchxxxinto@a,@b,@c
while@@fetch_status=0
begin
if（@c>=@d）print@a+replicate（'',3）+@b+str（@c,5）
fetchfromxxxinto@a,@b,@c
end
closexxx
deallocatexxx
显示出score表中成绩大于等于@d值的所有记录。
20.declare@achar（8）,@bvarchar（10）,@cnumeric（5,2）
declare@schar（8）,@rvarchar（10）
set@s=''
set@r='数学'
set@c=84
declarexxxcursor
forselect
升级会员 