Results 1 to 4 of 4

Thread: Lowest Marks

  1. #1
    Join Date
    Jul 2003
    Posts
    2

    Question Unanswered: Lowest Marks

    I have the following 3 tables.
    Student Table : S_ID, FNAME, LNAME
    UNIT : U_ID, UNITNAME
    Marks Table : S_ID, U_ID, YEAR, MARKS
    A student can enrol in multiple units of study each year. At enrolment by default their marks is zero.

    Given a certain year we need to output the lowest marks obtained by a student in each unit. Students with zero marks should be excluded.
    The SQL query shall output the following :
    FNAME, LNAME, UNITNAME, MARKS
    Example find the lowest marks obtained for all units in 2002. (The unitname can only appear once in the result).

    Can the above query be performed in a single select statement? If yes how ? If no what is the alternate?

  2. #2
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Thumbs down Lowest Marks

    Hi
    Kindly use the following query to get the required results.

    select t1.sid, t1.fname, t1.u_id, l2.u_name, t1.marks from (select l1.sid, l1.fname, l3.u_id, l3.marks from L1, L3 where L1.sid=l3.sid and l3.marks!=0) t1, l2 where t1.u_id=l2.u_id;

    Thanx and Regards
    Aruneesh

  3. #3
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Talking All in one query

    Everything that you wanted is coming packaged in just one query.
    Hope it works for you fine.

  4. #4
    Join Date
    Jul 2003
    Posts
    2

    Question Re: All in one query rephrased

    I suppose the question was not clear therefore the reply by aruneeshsalhotr did not answer the qestion. I will try & simplify with an example.

    Student Table : S_ID, FNAME, LNAME
    Student Data :
    001, Jack, Russel
    002, Mark, Benny
    003, John, Wayne

    Unit Table : U_ID, UNITNAME
    Unit Data :
    MA, Maths
    EN, English
    SC, Science

    Marks Table : S_ID, U_ID, YEAR, MARKS
    Marks Data :
    001, MA, 2002, 80
    001, EN, 2002, 60
    001, SC, 2002, 0
    002, MA, 2002, 50
    002, EN, 2002, 70
    002, SC, 2002, 60
    003, MA, 2002, 0
    003, EN, 2002, 0
    003, SC, 2002, 55
    003, MA, 2003, 50
    003, EN, 2003, 70
    001, SC, 2003, 50

    The Output required for the query -> find the lowest marks obtained for all units in 2002 is as follows:
    FNAME, LNAME, UNITNAME, MARKS
    Jack, Russel, English, 60
    Mark, Benny, Maths, 50
    John, Wayne, Science, 55
    In the above result all those with 0 marks have been eliminated.

    Is it possible to have a all in one query for the above output?

Posting Permissions

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