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

小白做题



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

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

有问题下面评论处提

3.11 使用大学模式，用SQL写出如下查询

a.超出所有选修了至少一门Comp. SCI.课程的学生姓名，保证结果中没有重复的姓名。
SELECT DISTINCT NAME FROM student NATURAL JOIN takes NATURAL JOIN course WHERE
dept_name= 'Comp. Sci.'
b.找出所有没有选修在2009年春季之前开设的任何课程的学生的ID和姓名
SELECT ID,NAME FROM student WHERE ID NOT IN ( SELECT ID FROM takes WHERE YEAR <
2009 )
c.找出每个系教师的最高工资。可以假设每个系至少有一位教师。
SELECT dept_name,MAX(salary) FROM instructor GROUP BY dept_name
d.从前述查询所计算出的每个系最高工资中选出最低值
WITH max_salary ( dept_name,max_salary) AS ( SELECT dept_name,MAX(salary) FROM
instructorGROUP BY dept_name) SELECT MIN(max_salary) FROM max_salary
3.12使用大学模式用SQL写出如下查询

a.创建一门课程“CS-001”，其名称为“Weekly Seminar”，学分为0
INSERT INTO course VALUES ('CS-001','Weekly Seminar','Comp. Sci.',0);
与之前类似的，网站的DDL对course表的credits项有检查不能为0，故需用去掉该项检查。

b.创建该课程在2009年秋季的一个课程段，sec_id为1.
INSERT INTO SECTION(course_id,sec_id,Semester,Year) VALUES('CS-001','1','Fall',
'2009');
c.让Comp. Sci.系的每个学生都选修上述课程段
INSERT INTO takes (ID,course_id,sec_id,semester,year) SELECT ID,'CS-001','1',
'Fall','2009'FROM student WHERE dept_name='Comp. Sci.' ;
d.删除名为Chavez的学生选修上述课程段的信息
DELETE FROM takes WHERE ID=( SELECT ID FROM student WHERE NAME='Chavez' ) AND
course_id='CS-001'
e.删除课程CS-001。如果在运行此语句之前没有先删除这门课程授课信息（课程段），会发生什么事情。
DELETE FROM course WHERE course_id = 'CS-001' // 由于DDL
中设定的course_id外健为级联删除,故section表中对应的CS-001元组会一同被删除
f.删除课程名中包含“database”的任意课程的任意课程段所对应的所有takes元组，在课程名的匹配中忽略大小写
DELETE FROM takes WHERE course_id =( SELECT course_id FROM course WHERE LOWER(
title) LIKE '%database%' )
3.13 写出对应于图3-18中模式的SQL DDL。在数据类型上做合理的假设，确保声明主码和外码。
CREATE TABLE person (driver_id NUMBER(13,0), NAME VARCHAR2(20) NOT NULL,
addressVARCHAR2(100), PRIMARY KEY (driver_id) ); CREATE TABLE car (license
VARCHAR2(8), MODEL VARCHAR2(10), YEAR NUMBER(4,0), PRIMARY KEY (license) );
CREATE TABLE accident (report_number NUMBER(6,0), datetime DATE, LOCATION
VARCHAR2(100), PRIMARY KEY (report_number) ); CREATE TABLE owns (driver_id
NUMBER(13,0), license VARCHAR2(8), PRIMARY KEY (driver_id), FOREIGN KEY (
driver_id) REFERENCES person on delete CASCADE, FOREIGN KEY (license) REFERENCES
car ondelete set NULL ); CREATE TABLE participated (report_number NUMBER(6,0),
licenseVARCHAR2(8), driver_id NUMBER(13,0), damage_amount NUMBER(15,0), PRIMARY
KEY (report_number ,license), FOREIGN KEY (report_number) REFERENCES accident ON
DELETE CASCADE, FOREIGN KEY (license) REFERENCES car ON DELETE SET NULL )
3.14考虑图3-18中的保险公司数据库，其中加下划线的是主码。对这个关系数据库构造如下的SQL查询。

a.找出和“John Smith”的车有关的交通事故数量
select count(report_number) from participated natural join person where name =
'John Smith'
b.对事故报告编号为“AR2197”中的车牌是“AABB2000”的车辆损坏保险费用更新到3000美元
updata participated set damage_amount = '3000' where report_number='AR2197'
and license='AABB2000'
3.15考虑图3-19中的银行数据库，其中加下划线的是主码。为这个关系数据库构造出如下SQL查询：

a.找出在“Brooklyn”的所有支行都有账户的所有客户。
WITH s_br(num1) AS ( SELECT COUNT(1) FROM branch WHERE branch_city='Brooklyn'
) (SELECT customer_name FROM customer a WHERE ( SELECT COUNT(DISTINCT
branch_name) FROM (SELECT * from customer NATURAL JOIN depositor NATURAL JOIN
ACCOUNT NATURAL JOIN branch ) b WHERE a.customer_name=b.customer_name AND
branch_city='Brooklyn' )=( SELECT num1 FROM s_br))
b.找出银行的所有贷款额的总和
select sum(amount) from loan
c.找出总资产至少比位于Brooklyn的某一家支行要多的所有支行名字
select customer_name from depositor a where Brooklyn_count =( select count(
distinct branch_name) from (SELECT * FROM customer natural join depositor
narual join account natural join branch) b where a.name=b.name)
3.16考虑图3-20中的雇员数据库，其中加下划线的是主码。给出下面每个查询对应的SQL语句

c.找错所有为“First Bank Corporation”工作的雇员的名字
select employee_name from works where company_name='First Bank Corporation'
b.找出数据库中所有居住城市和公司所在城市相同的雇员
with em_c(name,city) as (select employee_name,city from works natural join
company) select employee_name from employee,em_c where employee.employee_name=
em_c.name and employee.city=em_c.city
c.找出数据库中所有居住的街道和城市与其经理相同的雇员
select a.employee_name from employee a,employee b,managers where a.
employee_name=managers.employee_name and managers.manager_name = b.managers and
a.city=b.city and a.street =b.street
d.找出工资高于其所在公司雇员平均工资工资的所有雇员
select employee_name from works w1 where salary > (select avg(salary) from
works group by company_name having works.company_name =w1.company_name )
e.找出工资总和最小的公司
with salary_sum (name,numb) as (select company_name,sum(salary) from works
group by company_name) select name from salary_sum where numb = ( select min(
numb) from salary_sum )
3.17 考虑图3-20中的关系数据库。给出下面每个查询对应的SQL表达式。

a.为“First Bank Corporation”的所有雇员增长10%的工资
update works set salary=salary*1.1 where company_name = 'First Bank
Corporation'
b.为“First Bank Corporation”的所有经理增长10%的工资
update works set salary=salary*1.1 where company_name = 'First Bank
Corporation' and employee_name in (select manager_name from managers )
c.删除“Small Bank Corporation”的雇员在works关系中的所有元组
delete from works where company_name = 'Small Bank Corporation'
3.18 列出两个原因，说明为什么空值可以被引入数据库

客观世界中，有时描述事物的信息并不全面，引入空值就可以表示记录的这个属性缺乏，便于数据库的维护和管理，那么引入空值就有两个原因：属性未知，属性不存在

3.19 证明在SQL中，<>all 等价于not in

在SQL中，<>all 即表示与全部元组均不相符，not in表示在全部元组中没有相匹配的，故<>all与not in 等价

3.20 给出图3-20中雇员数据库的SQL模式定义。为每个属性选择合适的域，并为每个关系模式选择合适的主码。
CREATE TABLE employee (employee_name VARCHAR2(20), street VARCHAR2(20), city
VARCHAR2(20), PRIMARY KEY (employee_name) ); CREATE TABLE company (company_name
VARCHAR2(20), city VARCHAR2(20), PRIMARY KEY (company_name) ); CREATE TABLE
works (employee_name VARCHAR2(20), company_name VARCHAR2(20), salary NUMBER(8,2)
, PRIMARY KEY (employee_name), FOREIGN KEY (employee_name) REFERENCES employee
ON DELETE CASCADE, FOREIGN KEY (company_name) REFERENCES company ON DELETE
CASCADE ); CREATE TABLE managers (employee_name VARCHAR2(20), manager_name
VARCHAR2(20), PRIMARY KEY (employee_name), FOREIGN KEY (employee_name)
REFERENCES employee ON DELETE CASCADE, FOREIGN KEY (manager_name) REFERENCES
employeeON DELETE SET NULL )
3.21考虑图3-21中图书馆数据库。用SQL写出如下查询

a.打印借阅了任意由“McGraw-Hill”出版的书的会员名字
select distinct name from member natural join borrowed natural join book where
publisher='McGraw-Hill'
b.打印借阅了所有由“McGraw-Hill”出版的书的会员名字
with Mc_num(m_no,is) as (select memb_no,isbn from borrowed natural join where
publisher='McGraw-Hill') select name from member where memb_no in (select m_no
from Mc_num group by m_no having count(is) = (select count(isbn) from book
where publisher= 'McGraw-Hill') )
c.对于每个出版商，打印借阅了多于5本由该出版商出版的书的会员名字
select name,publisher from member natural join book natural join borrowed
group by name,publisher having count(isbn)>5
d.打印每位会员借阅书籍数量的平均值。考虑这样的情况，如果某会员没有借阅任何书籍，那么该会员根本不会出现在borrowed关系中
select(select count(*) from borrowed)/ (select count(*)from member) FROM dual
3.22不使用unique结构，重写下面的where子句
where unique (select title from course) where 1>=(select count(title) from
course)
3.23考虑查询：
select course_id,semeter,year,sec_id,avg(tot_cred) from takes natural join
student where year=2000 group by course_id,semester,year,sec_id having
count(ID) >=2
解释为什么在from子句中还加上与section的连接不会改变查询结果

takes 和 section 是通过一些共同的外键相连系，每一个takes的某一个元组不会因为增加额外的元祖。

3.24 考虑查询：
with dept_total (dept_name,value) as (select dept_name,sum(salary) from
instructor group by dept_name), dept_total_avg (value) as (select avg(value)
from dept_total) select dept_name from dept_total,dept_total_avg where
dept_total.value>=dept_total_avg.value
不使用with结构，重写此查询
SELECT dept_name FROM ( SELECT dept_name,SUM(salary) sa_sum FROM instructor
GROUP BY dept_name ) WHERE sa_sum>=( SELECT AVG(SUM(salary)) FROM instructor
GROUP BY dept_name )
终于码完了。。。。

• « 上一篇：程序员进阶之路(C、C++经典书籍及学习顺序)
• » 下一篇：行为型模式：中介者模式