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?

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

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

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?