数据库系统概念第六版（机械工业出版社） 第四章 中级SQL 实践习题 部分习题

数据库系统概念第六版（机械工业出版社） 第四章 中级SQL 实践习题 部分习题
部分习题：
4.12

select employee_name
from employee natural left outer join manages
where manager_name is NULL;

4.13
course的属性title不匹配（student全外连接takes）的所有元组，由于是全外连接，所以添null
4.14

create view tot_credits(year,num_credits)
as 
(select year,sum(credits)
from takes natural join course
group by year)；

4.17
保证安全性

实践习题：
英文原版答案：
4.1 Write the following queries in SQL:
a. Display a list of all instructors, showing their ID, name, and the number of sections that they have taught. Make sure to show the number of sections as 0 for instructors who have not taught any section. Your query should use an outerjoin, and should not use scalar subqueries. b. Write the same query as above, but using a scalar subquery, without outerjoin. c. Display the list of all course sections offered in Spring 2010, along with the names of the instructors teaching the section. If a section has morethan one instructor, itshould appearas many timesin the result as it has instructors. If it does not have any instructor, it should still appear in the result with the instructor name set to “—”. d. Displaythelistofalldepartments,withthetotalnumberofinstructors in each department, without using scalar subqueries. Make sure to correctly handle departments with no instructors.
Answer:
a. Display a list of all instructors, showing their ID, name, and the number of sections that they have taught. Make sure to show the number of sections as 0 for instructors who have not taught any section. Your query should use an outerjoin, and should not use scalar subqueries.
select ID, name, count(course id, section id, year,semester) as ’Number of sections’ from instructor natural left outer join teaches group by ID, name
The above query should not be written using count(*) since count * counts null values also. It could be written using count(section id), or

any other attribute from teaches which does not occur in instructor, which would be correct although it may be confusing to the reader. (Attributes that occur in instructor would not be null even if the instructor has not taught any section.) b. Write the same query as above, but using a scalar subquery, without outerjoin.
select ID, name, (select count(*) as ’Number of sections’ from teaches T where T.id = I.id) from instructor I
c. Display the list of all course sections offered in Spring 2010,