欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-04-05 17:10:57
...


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

大学模式数据库的表格:
数据库系统概念第6版第3章习题答案

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

表格示例

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

习题

3.11

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

# 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

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

# 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

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

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))

待续

相关标签: 数据库 SQL sql