数据库系统概念第6版第3章习题11，12题答案

最新推荐文章于 2022-04-06 17:52:58 发布

付渐渐

最新推荐文章于 2022-04-06 17:52:58 发布

阅读量534

点赞数 3

文章标签： sql

原文链接：https://blog.csdn.net/qq_43212582/article/details/105392877

版权

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%')
————————————————
版权声明：本文为CSDN博主「_kirakira_」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_43212582/article/details/105392877