Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2009
    Posts
    7

    Unanswered: help need with queries

    i am very new to mysql . would appreciate help with following queries

    -List all students (S_ID, S_LName, S_FName) with the number of courses and the average that a student has been enrolled in (S_ID, S_LName, S_FName, # of courses, and the average ofthose courses). Use "Outer join"

    -List all courses (C_ID, C_Name) in COURSE table with the number of students enrolled in eachcourse

    -Using a subquery, list all students (S_ID, S_LName, S_FName) and average of each student,whose average grade is higher than the average grade of all stu

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so where have you got to so far?
    what have you tried?
    this has the whiff of coursework, so for me its time to show wht you've done so we can understand where you are having problems

    it would help if you supplied some test data
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by healdem
    this has the whiff of coursework
    just a whiff???

  4. #4
    Join Date
    Jul 2009
    Posts
    7
    Quote Originally Posted by mike_bike_kite
    just a whiff???

    you guys are funny....this is related to course work. I have done most of the queries...I will post what i came up with tonight when i get home.

    I am trying to learn not to cheat

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by mike_bike_kite
    just a whiff???
    have you never heard of British understatement?
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Mar 2004
    Posts
    480
    Quote Originally Posted by christy123
    I am trying to learn not to cheat
    It doesn't look that way when you post questions and don't post anything that you've attempted to do to come up with a solution.

    Folks won't mind guiding you when you've taken a shot at solving and then we know you're serious about learning.

  7. #7
    Join Date
    Jul 2009
    Posts
    7
    Ok..here is what I got so far. i tried to use outer join but kept getting error

    -List all students (S_ID, S_LName, S_FName) with all courses (C_ID, C_NAME) that a student
    has been enrolled in. Order the list by student information. Use “Outer join”.

    SELECT STUDENT.S_ID, STUDENT.S_LName, STUDENT.S_FName, COURSE.C_ID, COURSE.C_NAME
    FROM COURSE INNER JOIN (STUDENT INNER JOIN IS_REGISTERED ON CStr(STUDENT.S_ID)=IS_REGISTERED.S_ID) ON COURSE.C_ID=IS_REGISTERED.C_ID
    ORDER BY STUDENT.S_ID, STUDENT.S_LName, STUDENT.S_FName;

  8. #8
    Join Date
    Jul 2009
    Posts
    7
    - Using a subquery, list all students (S_ID, S_LName, S_FName) and average of each student,
    whose average grade is higher than the average grade of all students.

    SELECT STUDENT.S_ID, STUDENT.S_LName, STUDENT.S_FName, (SELECT SUM(GRADE) / COUNT(*)
    FROM IS_REGISTERED WHERE IS_REGISTERED.S_ID = CStr(STUDENT.S_ID)) AS [AVG]
    FROM STUDENT
    WHERE (SELECT SUM(GRADE) / COUNT(*)
    FROM IS_REGISTERED WHERE IS_REGISTERED.S_ID = CStr(STUDENT.S_ID)) >
    (SELECT SUM(GRADE) / COUNT(*)
    FROM IS_REGISTERED);

  9. #9
    Join Date
    Jul 2009
    Posts
    7
    -List all students (S_ID, S_LName, S_FName) with the number of courses and the average that
    a student has been enrolled in (S_ID, S_LName, S_FName, # of courses, and the average of
    those courses). Use “Outer join”.

    SELECT STUDENT.S_ID, STUDENT.S_LName, STUDENT.S_FName, (SELECT COUNT(*) FROM IS_REGISTERED
    WHERE IS_REGISTERED.S_ID = CStr(STUDENT.S_ID) AND
    NOT IS_REGISTERED.C_ID IS NULL ) AS CNT
    FROM STUDENT;

  10. #10
    Join Date
    Jul 2009
    Posts
    7
    - List all courses (C_ID, C_Name) in COURSE table with the number of students enrolled in each
    course.

    SELECT COURSE.C_ID, COURSE.C_Name, (SELECT COUNT(*) FROM IS_REGISTERED WHERE IS_REGISTERED.C_ID = COURSE.C_ID
    AND NOT IS_REGISTERED.S_ID IS NULL) AS CNT, (SELECT COUNT(*) FROM IS_REGISTERED WHERE IS_REGISTERED.C_ID = COURSE.C_ID
    AND NOT IS_REGISTERED.S_ID IS NULL) /
    (SELECT COUNT(*) FROM COURSE) AS [AVG]
    FROM COURSE;

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by christy123
    - List all courses (C_ID, C_Name) in COURSE table with the number of students enrolled in each
    course.

    SELECT COURSE.C_ID, COURSE.C_Name, (SELECT COUNT(*) FROM IS_REGISTERED WHERE IS_REGISTERED.C_ID = COURSE.C_ID
    AND NOT IS_REGISTERED.S_ID IS NULL) AS CNT, (SELECT COUNT(*) FROM IS_REGISTERED WHERE IS_REGISTERED.C_ID = COURSE.C_ID
    AND NOT IS_REGISTERED.S_ID IS NULL) /
    (SELECT COUNT(*) FROM COURSE) AS [AVG]
    FROM COURSE;
    SQL is not difficult to learn but I'm looking at your code I wonder whether you were awake during that particular class. It just looks like you've grabbed random bits of SQL and jumbled them together to try and produce an answer - for instance what is AVG doing in your example. If the aim of the course is for you to learn SQL then I'd strongly suggest finding a nice simple learn SQL site and reading those pages and doing their examples.

  12. #12
    Join Date
    Jul 2009
    Posts
    7
    there were no classes. i am taking an online course--so basically self study. these queries were not discussed at all. one session was for basic sql and one for advanced.

    I am not a techie ..have business background....i want to learn sql that is why i took this class...i cant learn that in 2 sessions..( 2 power points basically)..

  13. #13
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    This page seems to explain joins quite well - but there are millions of sites. I'd advise setting up MySQL on your PC at home to try queries out - you could use XAMPP to create the MySQL server. I'd give you the SQL to do these queries but I don't see how you'll learn very much - except how to do that particular query - and besides I'd always have the faint suspicion that I'm doing homework. You'd do far better actually reading a bit more and actually trying things out.

    I'll do one of them for you by just cutting out the bits that work from your SQL:
    Code:
    -- List all courses (C_ID, C_Name) in COURSE table with the number of students enrolled in each course.
    SELECT COURSE.C_ID, COURSE.C_Name, 
          (SELECT COUNT(*) FROM IS_REGISTERED 
          WHERE IS_REGISTERED.C_ID = COURSE.C_ID ) as CNT
    FROM COURSE;

Posting Permissions

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