DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Please help! Urgent

  1. #1
    Join Date
    Aug 2001
    Posts
    4

    Question Please help me with this query problem! Urgent!

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

    STAFF:
    STAFFID STAFFNAME DEPT POSITION
    ------- ------------------------- --------------- --------------------
    t101 Adams Art Professor
    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:
    COURSENO STUID GRADE
    ------- ----- -----
    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. #2
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Question ???


    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. #3
    Join Date
    Aug 2001
    Posts
    4

    Unhappy 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. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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;
    Jeff Hunter

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    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. #6
    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. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I'm not sure I understand where the problem is...
    Jeff Hunter

  8. #8
    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. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    It is an outer join operator. See http://technet.oracle.com/docs/produ...03sch.htm#1142 for details.
    Jeff Hunter

Posting Permissions

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


Click Here to Expand Forum to Full Width