小白做题——数据库系统概念（第六版）第三章实践习题答案

小白做题



——数据库系统概念（第六版）第三章实践习题

数据库版本：oracle 11g； 工具：PL/ SQL

博主自做，基本可以执行，任何问题可以回复处提问

欢迎雅正

3.1 使用大学模式，用SQL写出如下查询
a.找出Comp. Sci. 系开设的具有3个学分的课程名称
SELECT title FROM course WHERE credits=3 AND dept_name='Comp. Sci.'
b.找出名为Einstein的教师所教的所有学生的标识，保证结果中没有重复
SELECT DISTINCT t.ID FROM instructor i,takes t,teaches c WHERE i.Name=
'Einstein' AND i.id=c.id AND c.course_id=t.course_id AND c.sec_id=t.sec_id AND c
.semester=t.semester AND c.year=t.year
c.找出教师的最高工资
SELECT MAX(salary) from instructor
d.找出工资最高的所有教师（可能不止一位教师具有相同的工资）
SELECT NAME FROM instructor WHERE salary =( SELECT MAX(salary) FROM instructor
)
e.找出2009年秋季开设的每个课程段的选课人数
SELECT course_id,sec_id,COUNT(DISTINCT ID) AS NUM FROM takes WHERE semester=
'Fall' AND YEAR=2009 GROUP BY course_id,sec_id
f.从2009年秋季开设的所有课程段中，找出最多的选课人数
（1）嵌套语句
SELECT MAX (NUM) FROM ( SELECT course_id,sec_id,COUNT(DISTINCT ID) AS NUM FROM
takesWHERE semester='Fall' AND YEAR=2009 GROUP BY course_id,sec_id)
（2）with临时关系语句
WITH stnum(NUM,course_id,sec_id) AS ( SELECT COUNT(ID) ,course_id,sec_id FROM
takesWHERE semester='Fall' AND YEAR=2009 GROUP BY course_id,sec_id) SELECT MAX(
NUM) FROM stnum
g.找出在2009年秋季拥有最多选课人数的课程段
（1）嵌套语句
SELECT course_id FROM ( SELECT course_id,sec_id,COUNT(DISTINCT ID) AS NUM FROM
takesWHERE semester='Fall' AND YEAR=2009 GROUP BY course_id,sec_id) WHERE NUM =
( SELECT MAX (NUM) FROM ( SELECT course_id,sec_id,COUNT(DISTINCT ID) AS NUM FROM
takesWHERE semester='Fall' AND YEAR=2009 GROUP BY course_id,sec_id) )
（2）with临时关系语句
WITH stnum(NUM,course_id,sec_id) AS ( SELECT COUNT(ID) ,course_id,sec_id FROM
takesWHERE semester='Fall' AND YEAR=2009 GROUP BY course_id,sec_id) SELECT
course_idFROM stnum WHERE NUM=(SELECT MAX(NUM) FROM stnum)
3.2 假定给你一个关系grade_points(grad_e,points),它提供从takes关系中用字母表示的成绩等级到数字表示的得分之间的转换。…
  给定上述关系和我们的大学模式，用SQL写出下面的每个查询。为简单起见，可以假设没有任何takes元组在grade上取null值。

※建表grade_points(grad_e,points)
CREATE TABLE grade_points (grad_e varchar(2), points NUMBER(3,1), PRIMARY KEY (
grad_e) ); INSERT INTO grade_points VALUES ( 'A','4'); INSERT INTO grade_points
VALUES ( 'A-','3.7'); INSERT INTO grade_points VALUES ( 'B+','3.3'); INSERT INTO
grade_pointsVALUES ( 'B','3'); INSERT INTO grade_points VALUES ( 'B-','2.7');
INSERT INTO grade_points VALUES ( 'C+','2.3'); INSERT INTO grade_points VALUES (
'C','2'); INSERT INTO grade_points VALUES ( 'C-','1.7'); INSERT INTO
grade_pointsVALUES ( 'D+','1.3'); INSERT INTO grade_points VALUES ( 'D','1');
INSERT INTO grade_points VALUES ( 'E','0.5'); INSERT INTO grade_points VALUES (
'F','0');
a.根据ID为12345的学生所选修的所有课程，找出该生所获得的等级分值的总和
WITH stgrade(ID,grad_e,credits) AS ( SELECT ID,grade,credits FROM takes NATURAL
JOIN course WHERE ID ='12345') SELECT SUM(credits*points) FROM stgrade NATURAL
JOIN grade_points
注意，自建表的列名与官网提供的数据库中需要的表的列名不同，故不能用自然连接

b.找出上述学生等级分值的平均值（GPA），即用等级分值的总和除以相关课程学分的总和
WITH stgrade(ID,grad_e,credits,course_id) AS ( SELECT ID,grade,credits,
course_idFROM takes NATURAL JOIN course WHERE ID ='12345') SELECT SUM(credits*
points)/COUNT(course_id) FROM stgrade NATURAL JOIN grade_points
c.找出每个学生的ID和等级分值的平均值
WITH stgrade(ID,grad_e,credits,course_id) AS ( SELECT ID,grade,credits,
course_idFROM takes NATURAL JOIN course ) SELECT ID,SUM(credits*points)/COUNT(
course_id) AS average FROM stgrade NATURAL JOIN grade_points GROUP BY ID
3.3 使用大学模式，用SQL写出如下插入、删除和更新语句

为不影响原数据库，建议运行之后回滚事务

a.给Comp. Sci.系的每位教师涨10%的工资
UPDATE instructor SET salary = salary *1.1 WHERE dept_name ='Comp. Sci.';
b.删除所有未开设过的（即没有出现在section关系中）的课程
DELETE from course WHERE course_id NOT IN (SELECT course_id FROM SECTION)
c.把每个在tot_cred属性上超过100的同学作为同系的教师插入，工资为10000美元
INSERT INTO instructor SELECT ID,NAME,dept_name,10000 FROM student WHERE
tot_cred>100
若使用的DDL是从支持网站上下载的，注意修改instructor表的属性，原定义中有限制salary>29000,需要解除限制，不然会报错。

3.4 考虑图3-18中的保险公司数据库，其中加下划线的是主码，为这个关系数据库构造出如下的SQL查询：

a.找出2009年期车辆出过交通事故的人员总数
select count(distinct driver_id) from participated natural join accident where
date like'2009%'
本题的表需要自己创建，注意创建时数据类型的定义,date类型的数据在下一章会讲到，故建议都设定为varchar类型

b.向数据库中插入一个新的事故，对每个必需的属性可以设定任意值
insert into accident values ('103245',‘2019-02-03’,'Steven Street'); insert
into participated select'103245',license,driver_id,'1500' from car person join
owns natural join person where derver_id= '10000000000'
c.删除“John Smith”拥有的马自达车（Mazda）
delete from car where model ='Mazda' and license in ( select license from owns
natural join person where name='John Smith' );
3.5
假设有关系marks（ID，score），我们希望计入基于标准为学生评定等级：如果score<40得F；如果40<=score<60得C；如果60<=score<80得B；如果80<=score得A。写出SQL查询完成下列操作：

a.基于marks关系显示每个学生的等级
select id, case when score<40 then 'F' when score<60 then 'C' when score<80
then'B' else 'A' end as scale from marks
b.找出各等级的学生数
select count(id) as num,scale from (select id, case when score<40 then 'F'
when score<60 then 'C' when score<80 then 'B' else 'A' end as scale from marks )
group by scale
3.6
SQL的like运算符是大小写敏感的，但字符串上的lower（）函数可以用来实现大小写不敏感的匹配。为了说明是怎么用的，写出这样一个查询：找出名称中包含了“sci”子串的系，忽略大小写
SELECT DISTINCT dept_name FROM department WHERE LOWER(dept_name) LIKE '%sci%'
3.7 考虑SQL查询
select distinct p.a1 from p,r1,r2 where p.a1=r1.a1 or p.a1 =r2.a1
在什么条件下这个查询选择的p.a1值要么在r1中，要么在r2中？仔细考察r1或r2可能为空的情况。

p,r1,r2均不为空。若三个表中任意一个表的a1为空，则where子句得出的结果为unknown，不能加入结果集中。

3.8考虑图3-19中的银行数据库，其中加下划线的是主码，为这个关系数据库构建出如下查询：

a.找出银行中所有有账户但无贷款的客户
select customer_name from depositor where customer_name not in ( select
customer_namefrom borrower )
b.找出与“Smith”居住在同一城市、同一街道的所有客户的名字
select d.customer_name from customer c,customer u where c.customer_name='Smith'
and c.customer_street=d.customer_street and c.customer_city=d.customer_city
或者
select c.customer_name from (customer as c) join (customer as d) using (
customer_street,customer_city) where d.customer_name = 'Smith'
3.9 考虑图3-20 的雇员数据库，其中加下划线的是主码，为下面每个查询写出SQL表达式：

a.找出所有为“First Bank Corporation”工作的雇员名字及其居住城市
select employee_name,city from employee natural join works where company_name=
'First Bank Corporation'
b.找出所有为“First Bank Corporation”工作且薪金超过10000美元的雇员名字、居住街道和城市。
select employee_name,street,city from employee natural join works where
company_name='First Bank Corporation' and salary>10000
c.找出数据库中所有不为“First Bank Corporation”工作的雇员
(select employee_name from employee) except (select employee_name from
employee natural join works where company_name='First Bank Corporation')
d.找出数据库中工资高于“Small Bank Corporation”的每个雇员的所有雇员
select employee_name from employee natural join works where salary >all(
select salaryfrom works where company_name='Small Bank Corporation')
e.假设一个公司可以在好几个城市有分部。找出位于“Small Bank Corporation”所有所在城市的所有公司
select distinct company_name from company where city in (select city from
company where company_name= 'Small Bank Corporation')
f.找出雇员最多的公司
with com_num(company_name,num) as ( select company_name,count(employee_name)
from works group by company_name ) select company_name from com_num where
com_num.num =(select max(num) from com_num)
g.找出平均工资高于“First Bank Corporation”平均工资的那些公司
with avg_salary(company_name,a_salary) as ( select company_name,avg(salary)
from works group by company_name ) select company_name from avg_salary where
a_salary> ( select a_salary from avg_salary where company_name ='First Bank
Corporation')
3.10 考虑图3-20的关系数据库，给出下面每个查询的SQL表达式

a.修改数据库使“Jones”现在居住在“Newtown”市
update empolyee set city ='Newtown' where employee_name ='Jones'
b.为“First Bank Corporation”所有工资不超过100000美元的经理增长10%的工资，对工资超过100000美元的只增长3%
update works set salary = case when salary <=100000 then salary*1.1 else salary
*1.03 end where employee_name in (select employee_name from managers) and
company_name= 'First Bank Corporation';

• « 上一篇：apache zeppelin 初次使用体验.
• » 下一篇：认识容器和Docker（一）