Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    2

    Unanswered: Help with Student Database Schema Queries

    I seem to be having trouble with a query that I need to write for class. We are using the student database schema, which can be found below :
    http://authors.phptr.com/rischert/do.../AppendixD.pdf

    The query is as follows :


    Create a query that lists location, number of sections taught in that location and number of
    students enrolled in courses at that location. Sort by location.

    Here, the results are right, but it shows the number of sections taught under the student column, and I have no idea how to fix it.

    SELECT location, COUNT (*) AS Students
    FROM Student st, enrollment e, section se
    WHERE st.student_id = e.student_id
    AND e.section_id = se.section_id
    GROUP BY location
    UNION ALL
    SELECT location, COUNT (*) AS Sections
    FROM section
    GROUP BY location
    ORDER BY location;

    Any input on what I am doing wrong would be greatly appreciated. Thanks in advance!
    Last edited by SQLFan90; 03-19-12 at 04:16.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    select location,sum(students) students, sum(sections) sections
    from
    (
    SELECT location, COUNT (*) AS Students,0 sections
    FROM Student st, enrollment e, section se
    WHERE st.student_id = e.student_id
    AND e.section_id = se.section_id
    GROUP BY location
    UNION ALL
    SELECT location, 0 students, COUNT (*) AS Sections
    FROM section
    GROUP BY location
    )
    group by location
    ORDER BY location;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another idea.
    (Not tested.)
    Code:
    SELECT location
         , SUM(students) AS students
         , COUNT(*)      AS sections
     FROM (SELECT MAX(location) AS location
                , se.section_id
                , COUNT(*)      AS students
            FROM  section    se
                , enrollment e
                , student    st
            WHERE st.student_id = e .student_id
              AND e .section_id = se.section_id
            GROUP BY
                  se.section_id
          )
     GROUP BY
           location
     ORDER BY
           location
    ;

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Is it neccesary to join Student table?
    Because, student_id in Enrollement is a foreign key to Student table.
    So, there is one and only one row in student table which satisfy "st.student_id = e.student_id" for each row in enrollement table.
    As a consequence, the result of COUNT(*) wouldn't be influenced with/without joining student table.

  5. #5
    Join Date
    Mar 2012
    Posts
    2
    Quote Originally Posted by beilstwh View Post
    Code:
    select location,sum(students) students, sum(sections) sections
    from
    (
    SELECT location, COUNT (*) AS Students,0 sections
    FROM Student st, enrollment e, section se
    WHERE st.student_id = e.student_id
    AND e.section_id = se.section_id
    GROUP BY location
    UNION ALL
    SELECT location, 0 students, COUNT (*) AS Sections
    FROM section
    GROUP BY location
    )
    group by location
    ORDER BY location;
    Thanks a lot! That seemed to do the trick, I guess I needed a dummy column in there to fill the data. I appreciate it!

    Quote Originally Posted by tonkuma View Post
    Is it neccesary to join Student table?
    Because, student_id in Enrollement is a foreign key to Student table.
    So, there is one and only one row in student table which satisfy "st.student_id = e.student_id" for each row in enrollement table.
    As a consequence, the result of COUNT(*) wouldn't be influenced with/without joining student table.
    I looked that over and you are right, the student table is not needed at all in this query!

Tags for this Thread

Posting Permissions

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