1. Junior Member
Join Date
Aug 2001
Posts
4

Hi all!
I got these four tables: (staff, courses, students, enroll)

STAFF:
STAFFID STAFFNAME DEPT POSITION
------- ------------------------- --------------- --------------------
t202 Smith History Lecturer
t105 Tanaka CSE Tutor
t110 Byrne Maths Lecturer
t221 Smith CSE Professor

COURSES:
COURSENO STAFFID SCHED ROOM
------- ------- --------- ----
art103a t101 mwf9 h221
cse201a t105 tuthf10 m110
mth101b t110 mtuth9 h225
hst205a t202 mwf11 h221
mth103c t110 mwf11 h225
cse203a t105 mthf12 m110

Students:
STUID STUNAME MAJOR CREDITS
----- ------------------------------ --------------- ----------
s1001 Smith, Tom History 90
s1010 Burns, Edward Art 63
s1015 Jones, Mary Maths 42
s1002 Chin, Ann Maths 36
s1020 Rivera, Jane CSC 15
s1013 McCarthy, Owen Maths 9

Enroll:
------- ----- -----
art103a s1001 A
cse201a s1020 b
cse201a s1002 f
art103a s1010
art103a s1002 d
mth101b s1020 a
hst205a s1001 c
mth103c s1010
mth103c s1002 b

Q1. can anyone tell me how I could show the count for ALL staff(including the ones who have zero room count)?

I did this but can't show zero:
SELECT STAFF.STAFFID, STAFF.POSITION, STAFF.STAFFNAME, COUNT(DISTINCT COURSES.ROOM) AS RM_COUNT
FROM STAFF, COURSES
WHERE STAFF.STAFFID=COURSES.STAFFID
GROUP BY STAFF.STAFFID;

Q2. can anyone tell me how to write the query the find the total number of students who either take at most one course (over the course of the week) or do not attend a class on tuesday?

Thanks a lot.

2. Senior Member
Join Date
May 2000
Location
Portsmouth, NH, USA
Posts
378

## ???

I'm confused.

Do you want a total of students AND staff?

Staff = professors, etc.

while Students = uh ... students

not the same thing.

OR, do you want to show all staff, AND their class sizes (including 0).

- magnus

if second, then:

select a.staffid, count(b.staffid) Total_Courses
from staff a, courses b
where a.staffid = b.staffid
group by a.staffid;

that should work, plus you can add whatever other columns you would like to query.

3. Junior Member
Join Date
Aug 2001
Posts
4

## Sorry

I am sorry,
The question is actually:
for each staff member, show the count of the number of different rooms that staff member teaches in.
(showing zero room count as well)

4. SELECT STAFF.STAFFID, STAFF.POSITION, STAFF.STAFFNAME, COUNT(DISTINCT COURSES.ROOM) AS RM_COUNT
FROM STAFF, COURSES
WHERE STAFF.STAFFID=COURSES.STAFFID (+)
GROUP BY STAFF.STAFFID;

Join Date
Jan 2001
Posts
2,828
hello

SELECT STAFF.STAFFID, STAFF.POSITION, STAFF.STAFFNAME, COUNT(DISTINCT COURSES.ROOM) AS RM_COUNT
FROM STAFF, COURSES
WHERE STAFF.STAFFID=COURSES.STAFFID (+)
GROUP BY STAFF.STAFFID,COURSES.ROOm;

regards
hrishy

6. Junior Member
Join Date
Aug 2001
Posts
4
thanks for ur reply, but actually, I want to show the zero room count and the question is to find number of DIFFERENT rooms that staff member teaches in.

The problem I have is I can't use count(room) to display zero.

The result should be something like:

STAFFID count(room)
------- ----------------
t101 1
t202 1
t105 1
t110 1
t221 0

7. I'm not sure I understand where the problem is...

8. Junior Member
Join Date
Aug 2001
Posts
4

## OH...APOLOGY AGAIN!

To marist89 and hrishy,

actually both of ur queries could work! I am sorry!
but if it's possible could u please explain what this (+) sign do?
and if u can, could u answer my second question which is:
how to write the query the find the total number of students who either take at most one course (over the course of the week) or do not attend a class on tuesday?

Sorry to both or u and thanks for ur help!!
I really appreciate!

9. It is an outer join operator. See http://technet.oracle.com/docs/produ...03sch.htm#1142 for details.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•