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.
5 answers
How would you like for us to help you?
This is a practice problem that the book has no answer for and needed to see how these two problems are done so i can be prepared for a test next week. The both need to be written in structured query langauge.
If you give it an attempt, we can help you comment on your answer.
Also, are you running MS SQL server or oracle?
Also, are you running MS SQL server or oracle?
Im am running oracle.
Here is what i have for the first query:select ID, name,
from instructor natural left
outer join section
where sec_id >= 0.
Here is what i have for the first query:select ID, name,
from instructor natural left
outer join section
where sec_id >= 0.
As a start, you may want to display the sections so you can manually count how many there are for each instructor for comparing with later results.
I believe the out join syntax looks like this:
select ID, name, section from instructor outer join on instructor.section = courses.section;
You will have to experiment with the different parameters of the outer join clause to get exactly what you want.
Once this is done, you can remove the many printed lines using count() function to return the number of sections each instructor teaches.
I believe the out join syntax looks like this:
select ID, name, section from instructor outer join on instructor.section = courses.section;
You will have to experiment with the different parameters of the outer join clause to get exactly what you want.
Once this is done, you can remove the many printed lines using count() function to return the number of sections each instructor teaches.