数据库技术与应用课后习题答案
- 格式：docx
- 大小：15.07 KB
- 文档页数：7
第一章习题答案
1.1 选择题
1. A 2. C 3. C 4. B 5. C 6. A 7. C 8. B 9. D 10. A 11. D 12. A 13. A
1.2 填空题
数据
数据的逻辑独立性
数据的物理独立性
层次数据模型，网状数据模型，关系数据模型
能按照人们的要求真实地表示和模拟现实世界、容易被人们理解、容易在计算机上实现
实体、记录
属性、字段
码
域
一对一、一对多、多对多
E-R模型
E-R模型
层次模型、网状模型、关系模型
数据操作、完整性约束
矩形、菱形、椭圆形
层次模型、一对多
网状模型
关系模型
关系
外模式、模式、内模式
三级模式、两级映像
外模式、模式、内模式
数据、程序
数据逻辑、数据物理
DBMS(数据库管理系统)、DBA(数据库管理员)
1.4 综合题
2.
(注：各实体的属性省略了)
3.
第二章习题答案
1.1 单项选择题
1. C 2. A 3. B 4. C 5. C 6. D 7. A 8. B
1.2 填空题
集合
2. 能唯一标识一个实体的属性
系编号， 学号，系编号
关系， 元组， 属性
关系模型，关系， 实体， 实体间的联系
投影
1.4 综合题
1. πsno(σcno=’2’(SC)) 2. πsno(σcname=’信息系统’(SCCOURSE))
3. πsno,SNAME,SAGE(STUDENT)
第三章习题答案
1.1
select * from jobs
1.2
select emp_id,fname+'-'+lname as 'Name' from employee
1.3
select emp_id,fname+'-'+lname as 'Name',Year(getdate())-Year(hire_date) as 'worke time' from employee
order by 'worke time'
2.1
select * from employee where fname like 'f%'
2.2
select * from employee where job_id='11'
2.3
select emp_id,fname+'-'+lname as 'Name', Year(getdate())-Year(hire_date) as worketime
from employee
where (Year(getdate())-Year(hire_date)) >5
order by worketime
2.4
select * from employee where cast(job_id as integer)>=5 and cast(job_id as integer)<=8
2.5
select * from employee where fname='Maria'
2.6
select * from employee where fname like '%sh%' or lname like '%sh%'
3.1
select * from sales where ord_date <'1993-1-1'
4.1
select distinct bh, zyh from stu_info where
bh in
(
select bh from stu_info
group by (bh)
having count(*)>30 and count(*)<40
)
order by bh
或者是
select bh,zyh from stu_info
group by zyh,bh
having count(bh)>30 and count(bh)<40
order by bh
4.2
select * from gban
where bh like '计%'
4.3 select * from gfied
where zym like '%管理%'
4.4
select xh,xm,zym,stu_info.bh,rxsj from stu_info,gfied,gban where nl>23
and stu_info.zyh=gfied.zyh and stu_info.bh=gban.bh
4.5
select zyh,count(*) from gban
where xsh='03'
group by zyh
第四章习题答案
4.1 单项选择题：
B 2、A 3、C 4、A 5、A 6、C 7、C 8、D 9、B 10、A 11、C(或B,即书上121页例题中from的写法) 12、A 13、C 14、C 15、C
4.2 填空题：
drop table
alter table add
with check option
基本表 基本表
distinct group by roder by
数据定义 数据操纵 数据控制
distinct
like % _
自含式 嵌入式
10、order by asc desc
4.3 综合题
1、SELECT XH, XM, ZYM, BH, RXSJ
FROM STU_INFO, GFIED
WHERE STU_INFO.ZYH = GFIED.ZYH AND NL > 23 AND XBM = '男'
2、SELECT ZYM 专业名, count(*) 人数 FROM STU_INFO, GFIED
WHERE STU_INFO.XSH = '03' AND STU_INFO.ZYH = GFIED.ZYH
GROUP BY ZYM
注意：该题目中给出的条件XSH = '03'中的03代表的是“控制科学与工程”学院，信息学院的代码是12，大家可根据具体情况来做该题。
3、SELECT bh,count(*) as 人数 FROM STU_INFO GROUP BY bh
4、SELECT XH, XM, XBM, BH FROM STU_INFO
WHERE ZYH IN
(
SELECT ZYH
FROM STU_INFO
WHERE XM = '李明'
)
ORDER BY XH
5、SELECT DISTINCT GCOURSE.KCH, KM FROM STU_INFO, XK, GCOURSE
WHERE XK.KCH = GCOURSE.KCH AND STU_INFO.XSH = '12'
AND STU_INFO.XH = XK.XH 该题中设计到的课程名称只有在GCOURSE表中存在，所以在题目开始的几个表中还应填加该表。另外把信息学院的代码改为12。
6、SELECT COUNT(DISTINCT KCH) AS 选课门数, AVG(KSCJ) AS 平均成绩
FROM STU_INFO, XK
WHERE STU_INFO.XH = XK.XH AND XSH = '12'
7、SELECT DISTINCT STU_INFO.XH, XM, BH, ZYM, KM
FROM STU_INFO, XK, GFIED, GCOURSE
WHERE KSCJ > 85 AND STU_INFO.XH = XK.XH AND XK.KCH = GCOURSE.KCH AND STU_INFO.ZYH =
GFIED.ZYH
ORDER BY ZYM, BH, STU_INFO.XH
8、SELECT STU_INFO.XH, XM, XSM, ZYM, BH, PYCCM
FROM STU_INFO, XK, GDEPT, GFIED
WHERE KKNY = '20011' AND STU_INFO.XH = XK.XH AND STU_INFO.XSH = GDEPT.XSH AND
STU_INFO.ZYH = GFIED.ZYH
GROUP BY STU_INFO.XH, XM, XSM, ZYM, BH, PYCCM
HAVING COUNT(*) > 10
9、SELECT DISTINCT bh
FROM STU_INFO
10、DELETE * FROM STU_INFO
WHERE XH LIKE '2000%'
或
DELETE * FROM STU_INFO
WHERE LEFT(XH,4) = '2000'
11、ALTER TABLE STU_INFO
ADD BYSJ varchar(8)
12、UPDATE XK
SET KSCJ = 60
WHERE KSCJ BETWEEN 55 AND 59 and
XH in
(
SELECT xh
FROM stu_info
WHERE zyh = '0501'
)
and
KCH in
(
SELECT kch
FROM gcourse
WHERE km = '大学英语'
)
前面已经考虑到在该题目中应该加入学生课程信息表（GCOURSE）
13、UPDATE GCOURSE
SET KCXF=6
WHERE KCH = '090101'
