Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2014
    Posts
    7

    Unanswered: The problem relating to SQL

    Question:
    Every subject has more than three credits. (Credit>=3)
    Every subject is majored by more than 50 students. (Students>=50)
    List the name of these courses, the tutor and the number of students whose grades are more than 95.
    Assume there aren't two tutors majoring the same classes.

    The answer is:

    SELECT CTitle, Tname, COUNT (Score>=95)
    FROM Take, Course, Teacher
    GROUP BY CID, TID
    HAVING COUNT (*)>= 50
    WHERE Course.CID= Take.CID AND Take.TID= Teacher.TID AND Credit>=3
    GROUP BY CID, TID
    HAVING COUNT (*)>=50

    I can't understand this sentence:

    WHERE Course.CID= Take.CID AND Take.TID= Teacher.TID AND Credit>=3
    Can everyone help me?
    Thanks!!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your query doesn't actually parse.

    I'm going to assume that you've copy-pasted an extra two lines in there (the extra GROUP BY), the column names in the select should match the group by and the Count(Score>=95) is just pseduo code.

    The WHERE clause is basically using the old, outdated JOIN syntax. You should not be writing queries in this way any more.

    Your query is equivalent to:
    Code:
    SELECT <MISSING_ALIAS>.CTitle
         , <MISSING_ALIAS>.Tname
         , Sum(CASE WHEN <MISSING_ALIAS>.Score >= 95 THEN 1 ELSE 0 END)
    FROM   Take
     INNER
      JOIN Course
        ON Course.CID = Take.CID 
     INNER
      JOIN Teacher
        ON Take.TID = Teacher.TID
    WHERE  <MISSING_ALIAS>.Credit >= 3
    GROUP
        BY <MISSING_ALIAS>.CTitle /* CID */
         , <MISSING_ALIAS>.Tname  /* TID */
    HAVING Count(*) >= 50
    Is that clearer?

    Ask any questions you might have.
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2014
    Posts
    7

    Cool

    Yes, it's very clear.
    Thank you very much.

Posting Permissions

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