数据库系统概念机械工程出版社第四章部分习题答案

答案有的是自己写的，以注明。仅作参考。
题一：
使用大学模式（参见教材第6版第2章），用SQL写出如下查询。

显示所有教师的列表，列出他们的ID、姓名以及所讲授课程段的编号。

对于没有讲授任何课程段的
教师， 确保结果中出现他们的ID与姓名。

#自己写的
SELECT i.id,i.name,t.course_id
FROM instructor i
LEFT OUTER JOIN teaches t
ON i.id=t.id;
#参考答案，与作业答案可能相同但实测存在问题仅供参考
select ID, name,sec_id
from instructor natural left outer join teaches；

题二：
使用大学模式（参见教材第6版第2章），用SQL写出如下查询。

显示所有教师的列表，列出他们的ID、姓名以及所讲授课程段的数量。
对于没有讲授任何课程段的
教师，确保将课程段数量显示为0。
在你的查询中应该使用外连接，不能使用标量子查询。

#自己写的
SELECT i.id,i.name,COUNT(course_id)
FROM instructor i
LEFT OUTER JOIN teaches t
ON i.id=t.id
GROUP BY NAME;
#参考答案，与作业答案可能相同但实测存在问题仅供参考
select ID,name, count(sec_id) as Numberofsetions
from instructor natural left outer join teaches
group by ID,name

题三：
使用大学模式（参见教材第6版第2章），用SQL写出如下查询。

使用标量子查询，不使用外连接写出上述查询。

#自己写的
SELECT i.id,i.name,(SELECT COUNT(course_id) FROM teaches t WHERE t.id=i.id) AS counts
FROM instructor i;
#参考答案，与作业答案可能相同但实测存在问题仅供参考
select  ID,name,
(select count(*) a from teaches T where T.id = I.id) Numberofsetions
from instructor I;

题四：
使用大学模式（参见教材第6版第2章），用SQL写出如下查询。

显示2020年春季开设的所有课程的列表，包括讲授课程段的教师的姓名。如果一个课程段有不止一位教师讲授，那么有多少位教师，此课程段在结果中就出现多少次。如果一个课程段没有任何教师，它也要出现在结果中，相应的教师名置为“-"。

#自己写的
SELECT c.*,
IF((SELECT NAME FROM instructor i WHERE i.id=t.id) IS NULL,'-',(SELECT NAME FROM instructor i WHERE i.id=t.id)) AS 'name'
FROM course c
LEFT OUTER JOIN teaches t
ON c.course_id=t.course_id
WHERE t.year=2020 AND t.semester='spring';
#参考答案，与作业答案可能相同但实测存在问题仅供参考
select course_id, seC_id, ID,decode(name, null, '-', name) as name
from (section natural left outer join teaches)
natural left outer join instructor
where semester='Spring' and year= 2020;

题五：
使用大学模式（参见教材第6版第2章），用SQL写出如下查询。

显示所有系的名称，以及该系中教师的总数，不能使用标量子查询。确保正确处理没有教师的系。

#自己写的
SELECT d.dept_name,COUNT(id)
FROM department d
LEFT OUTER JOIN instructor i
ON d.dept_name=i.dept_name
GROUP BY d.dept_name;
#参考答案，与作业答案可能相同但实测存在问题仅供参考
select dept_name, count(ID)
from department natural left outer join instructor
group by dept_name;

题六：
使用大学模式（参见教材第6版第2章），用SQL写出如下查询。

使用标量子查询，不使用外连接写出上述查询。

#自己写的
SELECT d.dept_name,
(SELECT COUNT(id) FROM instructor i WHERE d.dept_name=i.dept_name) AS 'count'
FROM department d;
#参考答案，与作业答案可能相同但实测存在问题仅供参考
select dept_name, 
(select count(*) a from instructor i where i.dept_name=d.dept_name ) Numberofinstructors
from department d;

题七八九使用的表

CREATE TABLE grade_points
  (grad_e      VARCHAR(2),
   points      DOUBLE,
   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_points VALUES ( '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_points VALUES ( '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');

题七：
假设给你一个关系grade_poins(grad_e,points)，它提供从takes关系中用字母表示的成绩等级到数字表示的得分之间的转换。例如，“A"等级可指定为对应于4分，“A-“对应于3.7分，“B+”对应于3.3分，“B”对应于3分，等等。学生在某门课程(课程段)上所获得的等级分值被定义为该课程段的学分乘以该生得到的成绩等级所对应的数字表示的得分。
给定上述关系和我们的大学模式，用SQL写出下面的每个查询。为简单起见，可以假设没有任何takes元组在grade上取null值。

根据ID为12345的学生所选修的所有课程，找出该生所获得的等级分值的总和。

#自己写的
SELECT
      (SUM(credits*(SELECT points FROM grade_points g WHERE t.grade=g.grad_e))) AS 'avg'
FROM takes t
LEFT OUTER JOIN course c
ON c.course_id=t.course_id
WHERE t.id=12345;

题八：
假设给你一个关系grade_poins(grad_e,points)，它提供从takes关系中用字母表示的成绩等级到数字表示的得分之间的转换。例如，“A"等级可指定为对应于4分，“A-“对应于3.7分，“B+”对应于3.3分，“B”对应于3分，等等。学生在某门课程(课程段)上所获得的等级分值被定义为该课程段的学分乘以该生得到的成绩等级所对应的数字表示的得分。
给定上述关系和我们的大学模式，用SQL写出下面的每个查询。为简单起见，可以假设没有任何takes元组在grade上取null值。

找出上述学生等级分值的平均值(GPA)，即用等级分值的总和除以相关课程学分的总和。

#自己写的
SELECT
      (SUM(credits*(SELECT points FROM grade_points g WHERE t.grade=g.grad_e))/SUM(credits)) AS 'avg'
FROM takes t
LEFT OUTER JOIN course c
ON c.course_id=t.course_id
WHERE t.id=12345;

题九：
假设给你一个关系grade_poins(grad_e,points)，它提供从takes关系中用字母表示的成绩等级到数字表示的得分之间的转换。例如，“A"等级可指定为对应于4分，“A-“对应于3.7分，“B+”对应于3.3分，“B”对应于3分，等等。学生在某门课程(课程段)上所获得的等级分值被定义为该课程段的学分乘以该生得到的成绩等级所对应的数字表示的得分。
给定上述关系和我们的大学模式，用SQL写出下面的每个查询。为简单起见，可以假设没有任何takes元组在grade上取null值。

找出每个学生的ID和等级分值的平均值。

#自己写的
SELECT t.id,
          (SUM(credits*(SELECT points FROM grade_points g WHERE t.grade=g.grad_e))/SUM(credits)) AS 'avg'
FROM takes t
LEFT OUTER JOIN course c
ON c.course_id=t.course_id
GROUP BY t.id;