SQL学习03---数据库系统概念第6版第3章习题答案

答案仅供参考，有错误望在评论区提出

大学模式数据库的表格：

表格示例

习题

3.1

# a.
SELECT title
FROM course
WHERE dept_name='Comp. Sci.' AND credits=3;

# b.
SELECT DISTINCT takes.ID
FROM (instructor NATURAL JOIN teaches) JOIN takes
WHERE `name` = 'Einstein' AND teaches.course_id = takes.course_id AND teaches.sec_id = takes.sec_id AND teaches.semester = takes.semester AND teaches.year = takes.year; 

# c.
SELECT MAX(salary)
FROM instructor

# d.
SELECT NAME
FROM instructor
WHERE salary = (
SELECT MAX(salary)
FROM instructor)

# e.
SELECT takes.course_id, takes.sec_id, COUNT(*)
FROM section NATURAL JOIN takes
WHERE takes.semester = 'Fall' AND takes.year = 2009
GROUP BY takes.course_id, takes.sec_id

# f.
SELECT MAX(stu_num)
FROM (
SELECT COUNT(*) AS stu_num
FROM section NATURAL JOIN takes
WHERE takes.semester = 'Fall' AND takes.year = 2009
GROUP BY takes.course_id, takes.sec_id
) AS stu_nums

# g.
WITH stu_nums AS(
SELECT COUNT(*) AS stu_num, takes.course_id, takes.sec_id
FROM section NATURAL JOIN takes
WHERE takes.semester = 'Fall' AND takes.year = 2009
GROUP BY takes.course_id, takes.sec_id
) 
SELECT course_id, sec_id
FROM stu_nums
WHERE stu_num = (
SELECT MAX(stu_num)
FROM stu_nums)

3.3

# a.
UPDATE instructor
SET salary = 1.1*salary
WHERE dept_name = 'Comp. Sci.'

# b.
DELETE FROM course
WHERE course_id NOT IN(
SELECT course_id
FROM section)

# c.
INSERT INTO instructor
SELECT ID, `name`, dept_name, 10000
FROM student
WHERE tot_cred > 100 

3.11

# a.
SELECT `name`
FROM student NATURAL JOIN takes NATURAL JOIN course
WHERE dept_name = 'Comp. Sci.'

# b.
SELECT `ID`, `name`
FROM student
EXCEPT
SELECT `ID`, `name`
FROM student NATURAL JOIN takes
WHERE `year` < 2009  

# c.
SELECT `dept_name`, MAX(salary)
FROM instructor
GROUP BY dept_name

#d.
SELECT MAX(salary)
FROM
(SELECT `dept_name`, MAX(salary)
FROM instructor
GROUP BY dept_name)

3.12

# a.
INSERT INTO course
VALUES('CS-001', 'Weekly Seminar', 'Comp. Sci.', 0)

# b.
INSERT INTO section(`year`, semester, sec_id)
VALUES('2009', 'Fall', 1)

# c.
INSERT INTO takes
SELECT ID, 'CS-001', 1, 'Fall', '2009', NULL
FROM student
WHERE dept_name = 'Comp. Sci.'

# d.
DELETE FROM takes
WHERE course_id = 'CS-001' AND sec_id = 1 AND semester = 'Fall' AND `year` = 2009
AND ID IN 
(SELECT ID
FROM student
WHERE `name` = 'Chavez')

# e.
DELETE FROM takes
WHERE course_id = 'CS-001'

DELETE FROM section
WHERE course_id = 'CS-001'

DELETE FROM course
WHERE course_id = 'CS-001'

#上面的顺序不能改变，因为takes的course_id外码依赖于section，
# section的course_id外码依赖于course，改变顺序会导致外码冲突

# f.
DELETE FROM takes
WHERE course_id IN
(SELECT course_id
FROM course
WHERE LOWER(title) LIKE '%database%')  

3.13

CREATE TABLE person
(driver_id VARCHAR(50),
`name` VARCHAR(50),
address VARCHAR(50),
PRIMARY KEY (driver_id))

CREATE TABLE car
(license VARCHAR(50),
model VARCHAR(50),
`year` INTEGER,
PRIMARY KEY (license))

CREATE TABLE accident
(report_number INTEGER,
`date` DATE,
location VARCHAR(50),
PRIMARY KEY (report_number))

CREATE TABLE owns
(driver_id VARCHAR(50),
license VARCHAR(50),
PRIMARY KEY (driver_id,license)
foriegn KEY (driver_id) REFERENCES person
foriegn KEY (license) REFERENCES car)

CREATE TABLE participated
(report_number INTEGER,
license VARCHAR(50),
driver_id VARCHAR(50),
damage_amount INTEGER,
PRIMARY KEY (report_number,license)
foriegn KEY (license) REFERENCES car
foriegn KEY (report_number) REFERENCES accident))

3.14

# a.
SELECT COUNT(DISTINCT report_number)
FROM person NATURAL JOIN owns NATURAL JOIN participated NATURAL JOIN accident
WHERE `name` = 'John Smith'
# 这里用distinct是因为同一个事故可能有多个人参与，而一辆车有可能被多个人多拥有(owns)

# b.
UPDATE participated 
SET damage_amount = 3000
WHERE report_number = 'AR2197' AND license = 'AABB2000'

3.23

# 再自然连接一个section表也不会对结果产生影响，原因有二：
# 1.select语句后跟的前四个属性都来自takes表，且它们都外码依赖于section表，所以自然连接之后，不会使原来不连接时的元组少
# 2.上面四个属性是section表的主码，具有唯一性，因此连接section之后不会增加结果原来元组的数目

3.24

上面代码的意思是找出这些院系，它们各自的总工资比院系的平均工资多或者相等。（找出有钱的院系）

# 使用标量子查询
SELECT DISTINCT dept_name
FROM instructor i1
WHERE
(SELECT SUM(salary)
FROM instructor i2
WHERE i2.dept_name = i1.dept_name)
>=
(SELECT AVG(s)
FROM
(SELECT SUM(salary) AS s
FROM instructor
GROUP BY dept_name))