TUR04 CISC3000 Tutorial 04

Question 1

Complete
Not graded
Flag question

Question text

Consider the following SQL query that seeks to find a list of titles of all courses taught in Spring 2017 along with the name of the instructor.
select name,title
from instructor natural join teaches natural join section natural join course
where semester = ‘Spring’ and year=2017
What is wrong with this query?

Feedback

Although the query is syntactically correct, it does not compute the expected answer because dept name is an attribute of both course and instructor. As a result of the natural join, results are shown only when an instructor teaches a course in her or his own department.

Question 2

Complete
Not graded
Flag question

Question text

Write the following queries in SQL:

a. Display a list of all instructors, showing each instructor’s ID and the number of sections taught.
Make sure to show the number of sections as 0 for instructors who have not taught any section. Your query should use an outer join, and should not use subqueries.

a.
select ID, count(sec_id) as Number_of_sections
from instructor natural left outer join teaches
group by ID

The above query should not be written using count(*) since that would count null values also. It could be written using any 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, and not using outer join.

select ID,
      (select count(*) as Number_of_sections
      from teaches T
      where T.id = I.id)
from instructor I

Feedback

a.
select ID, count(sec_id) as Number_of_sections
from instructor natural left outer join teaches
group by ID
The above query should not be written using count() since that would count null values also. It could be written using any 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.
select ID,
(select count() as Number_of_sections
from teaches T
where T.id = I.id)
from instructor I

Question 3

Complete
Not graded
Flag question

Question text

Consider the query:

select course id, semester, year, sec id, avg (tot cred)
from takes natural join student
where year = 2017
group by course id, semester, year, sec id
having count (ID) > = 2;

Explain why appending natural join section in the from clause would not change the result.

Feedback

Adding a natural join with section would remove from the result each tuple in takes whose values for (course_id, semester, year, sec_id) do not appear in section. However, since takes has the constraint: foreign key (course_id, semester, year, sec id) references section there cannot be a tuple in takes whose values for (course_id, semester, year, sec_id) do not appear in section.

Question 4

Complete
Not graded
Flag question

Question text

Rewrite the query select *from section natural join classroom** without using a natural join but instead using an inner join with a using condition.

select *
from section
inner join classroom
on(section.building = classroom.building and section.room_number = section.room_number);

select *
from section join classroom using (building, room_number)

Feedback

select * from section join classroom using (building, room number)