Chapter 4, Problem 2PE

Explanation of Solution

a.

Query for the given instruction:

select ID, name, count (course_id, sec_id, year, semester) as “Sections taught” from instructor natural left outer join teaches group by ID, name.

Explanation:

• Here, get a list with instructor’s ID, name, and the number of sections that they have taught using an outer join.
• Use the natural outer join between instructor and teaches tables.
• Get the instructor’s ID, name, and the number of sections taught by the instructor by using a group by clause on the ID and name attribute...

Explanation of Solution

b.

Query without using outer join:

Select ID, name, (where count (*) as “Section taught” from teaches T where T.id = I.id) from instructor I.

Explanation:

• Display a list with instructor’s ID, name, and the number of sections that they have taught using a sub-query...

Explanation of Solution

c.

Query for the given instruction:

select course_id, sec_id, ID; decode (name, NULL, ‘-’, name) from (section natural left outer join teaches) natural left outer join instructor where semester = ‘Spring’ and year = 2018.

Explanation:

• Initially perform a natural left outer between section, teaches and instructor table...

Explanation of Solution

d.

Query for the given instruction:

select dept_name, count (ID) from department natural left outer join instructor group by dept_name

Explanation:

• Initially perform a natural left outer join between department and instructor table...