How do I query 3 tables (STUDENTS, MODULES, MARKS) such that I return the module name and the lowest mark score for student John Doe?
SQL> DESC STUDENTS; Name Null? Type STUDENT_NO VARCHAR2(10) SURNAME VARCHAR2(20) FORENAME VARCHAR2(20) SQL> DESC MODULES; Name Null? Type MODULE_CODE VARCHAR2(8) MODULE_NAME VARCHAR2(20) SQL> DESC MARKS; Name Null? Type STUDENT_NO VARCHAR2(10) MODULE_CODE VARCHAR2(8) MARK NUMBER(38)
I have:
SQL> SELECT MOD.MODULE_NAME, MA.MARK FROM MODULES MOD INNER JOIN MARKS MA ON(MOD.MODULE_CODE = MA.MODULE_CODE) WHERE MA.STUDENT_NO = (SELECT S.STUDENT_NO FROM STUDENTS S WHERE S.SURNAME = 'Doe');MODULE_NAME MARK Databases 80 Programming 65 Operating Systems 50
but cannot get it to display the lowest value.
Trying to put a MIN(MA.MARKS) in the SELECT clause or after the WHERE clause causes an error.
Best Answer
Why would this not be sufficient?