access和SQL数据库练习及答案

数据库技术及应用实践考核上机考试练习题B（201010）
给定的学生_选课库中数据库表结构及样本记录表1-表6数据如下：学生（学号，姓名，年龄，性别，所在系）；
课程（课程号，课程名，先行课）；
选课（学号，课程号，成绩）.
表1 学生表结构
字段名数据类型长度是否为空约束学号CHAR 5 否主键
姓名CHAR 8 否
年龄SMALLINT 默认20
性别CHAR 2 “男”或“女”
所在系CHAR 20
表2 课程表结构
字段名数据类型长度是否为空约束
课程号CHAR 5 否主键
课程名CHAR 20 否
先行课CHAR 5
表3 选课表结构
字段名数据类型长度是否为空约束学号CHAR 5 否主键，外键
课程号CHAR 5 否主键，外键
成绩SMALLINT 0～100
表4 学生表数据
序
学号姓名性别年龄所在系
号
1 S1 李明男21 计算机
2 S2 张小红男21 计算机
3 S3 李和明女22 计算机
4 S4 张三男21 计算机
5 S5 刘宏男23 计算机
6 S6 王红应女20 计算机
7 S7 吴非男19 数学
8 S8 丁玉男21 数学
9 S9 赵名女21 数学
10 S12 张共可男22 物理
表5 课程表数据
序号课程号课程名先行课
1 C1 计算机引论
2 C2 数据结构C3
3 C3 C语言编程C1
4 C4 软件工程C6
5 C
6 数据库C2
6 C5 计算机文化
7 C7 高等数学
8 C8 概率统计C9
9 C9 线性代数C7
10 C10 力学
表6 选课表数据
序号学号课程号成绩
1 S1 C1 60
2 S2 C1 93
3 S3 C1
4 S4 C1 89
5 S1 C2 79
6 S2 C2
7 S3 C2 80
8 S4 C3 90
9 S1 C3 92
10 S2 C3 81
11 S1 C7 85
12 S4 C7 75
一、根据给定的样本数据，按顺序完成下列操作：
1、数据定义和输入记录
（1）创建学生-课程数据库。

（2）创建学生表、课程表和选课表，并满足约束条件。

（3）输入样本记录。

（4）查询输入的样本记录，并保存结果。

（5）创建计算机系学生成绩视图。

属性包括：学号、姓名、课程名和成绩。

（6）查询已创建的视图，并保存结果。

（7）创建学生表、课程表和选课表的数据库关系图表。

2、数据更新
（1）输入一条新学生记录，例如：(‘S11’, ’李阳’, ’男’,21,’计算机’)
（2）删除一条学生记录，例如：删除学号=‘S5’的记录。

（3）修改一条学生记录，例如：将学号=‘S5’的记录的系修改为计算机（RSL
改了吴非）。

（4）查询当前的学生记录，并保存结果。

（5）求每个系的学生的平均年龄，并将结果存入到系平均年龄数据库中。

标准：
select所在系,avg(年龄)AS 平均年龄into平均年龄数据库from 学生
group by所在系;
SELECT 学生.所在系, Avg(学生.年龄) AS 平均年龄
FROM 学生
GROUP BY 学生.所在系;
3、单表查询
（1）求计算机系的学生学号和姓名。

（2）求选修了课程的学生学号。

标准：
select distinct学号from选课;
1.distinct 去除重复的数据
（3）求选修C1课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列。

SELECT 选课.学号, 选课.成绩, 选课.课程号
FROM 选课
WHERE 课程号="C1"
ORDER BY 成绩DESC ,学号;
（4）求数学系或计算机系姓张的学生的信息。

错：只求计算机
SELECT 学生.所在系, 学生.姓名, 学生.年龄, 学生.性别, 学生.学号
FROM 学生
WHERE (((学生.所在系)="计算机") AND ((学生.姓名) Like "张*"));
=============================================
结果：SELECT 学生.所在系, 学生.姓名, 学生.年龄, 学生.性别, 学生.学号FROM 学生
WHERE ((学生.姓名) Like"张*")AND 所在系IN ("计算机","数学");
=============================================
（5）求缺少了成绩的学生的学号和课程号。

SELECT 选课.学号, 选课.课程号
FROM 选课
WHERE (((选课.成绩) Is Null));
4、连接查询
（1）求学生的学号、姓名、选修的课程名及成绩。

SELECT 学生.学号, 学生.姓名, 课程.课程名, 选课.成绩
FROM 课程 INNER JOIN (学生 INNER JOIN 选课 ON 学生.学号 = 选课.学
号) ON 课程.课程号= 选课.课程号;
（2）求选修C1课程且成绩为90分以上的学生学号、姓名及成绩。

SELECT 学生.学号, 学生.姓名, 选课.成绩
FROM 学生INNER JOIN 选课ON 学生.学号= 选课.学号
WHERE (((选课.成绩)>=90) AND ((选课.课程号)="C1"));
（3）查询每一门课的间接先行课（即先行课的先行课）。

select a.课程号, b.先行课from curr a, curr b
where a.先行课=b.课程号;
（4）求学生表与选课表的左外连接。

select*from学生left join选课
on学生.学号=选课.学号;
（5）求选课表与课程表的右外连接。

5、嵌套查询
（1）求选修了高等数学的学生学号和姓名。

select学号,姓名from stu where学号in(
select学号from选课where课程号=(
select课程号from curr where课程名='高等数学'
));
错：以下属于连接查询
SELECT 课程.课程名, 学生.学号, 学生.姓名
FROM 课程 INNER JOIN (学生 INNER JOIN 选课 ON 学生.学号 = 选课.学号) ON 课程.课程号 = 选课.课程号
WHERE (((课程.课程名)="高等数学"));
（2）求C1课程的成绩高于张三的学生学号和成绩。

SELECT 学号,成绩
FROM 选课
WHERE 课程号="C1" AND 成绩 > (SELECT 成绩 FROM 选课 WHERE学号=(SELECT 学号 FROM 学生 WHERE 姓名="张三")AND 课程号="C1");
（3）求其他系中比计算机系学生年龄都小的学生。

select * from stu where 年龄<(
select min(年龄) from 学生 where
所在系='计算机');
（4）求没有选修C2课程的学生姓名。

select姓名from stu where学号not in(
select学号from select_curr where
课程号='C2');
（5）求至少选修了两门课程的学生信息。

SELECT *
FROM 学生
WHERE 学号 IN (SELECT 学号 FROM 选课 GROUP BY 学号 HAVING
COUNT(学号)>=2 );
6、组合查询
（1）求学生的总人数。

SELECT Count(学生.学号) AS 学号之计数
FROM 学生;
（2）求选修了课程的学生人数。

select count(学号) as 人数
FROM
(Select distinct 学号FROM选课);
（3）求计算机系学生的平均年龄定价，用GROUP BY表示。

SELECT AVG(年龄) AS平均年龄
FROM学生
WHERE所在系="计算机";
（4）求各课程号和选修该课程的人数。

SELECT 课程号,COUNT(学号)AS人数
FROM 选课
GROUP BY 课程号;
（5）求选修课超过3门课的学生学号。

select学号from select_curr group by
学号having count(课程号)>3;
二、关于程序验收
1、通过可视化工具，验收已创建的数据库、数据表、视图关系表和数据库关系表。

2、通过已保存的SQL程序代码，验收查询结果。

3、上机操作工作量：除了操作1的数据定义和样本数据输入必做外，其余操作2至操作6，考试时每种操作出1-2道小题，共约8-10题。