Results 1 to 2 of 2

Thread: SQL Query

  1. #1
    Join Date
    Oct 2007
    Posts
    13

    Unanswered: SQL Query

    Hi, I am studying SQL at the mom and can deal with most simple query. I am having problems with more complex ones like joining tables etc. A prime example is the following

    3 tables

    CREATE TABLE Student
    (RegNo NUMBER
    CONSTRAINT PK_Student PRIMARY KEY,
    Name VARCHAR2(20)
    CONSTRAINT NOT_NULL_Student_Name NOT NULL) ;

    CREATE TABLE Unit
    (UnitCode CHAR(4)
    CONSTRAINT PK_Unit PRIMARY KEY,
    Name VARCHAR2(20)
    CONSTRAINT NOT_NULL_Unit_Name NOT NULL) ;

    CREATE TABLE Result
    (UnitCode CHAR(4)
    CONSTRAINT FK_Result_Unit REFERENCES Unit,
    Regno NUMBER
    CONSTRAINT FK_Result_Regno REFERENCES Student,
    AcadYear NUMBER
    CONSTRAINT NOT_NULL_Acad_Year NOT NULL,
    Percent NUMBER,
    CONSTRAINT PK_Result PRIMARY KEY
    (UnitCode,Regno,AcadYear)) ;

    Query = Show the average marks for each student, listing the students by their Names

    My attempt=

    SELECT name, AVG(Percent)
    FROM Student s, Result r
    WHERE s.Regno = r.Regno
    ORDER BY s.name

    I know this is wrong but could someone tell me Y

    Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    check your manual for the difference between ORDER BY and GROUP BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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