I am trying to return the first name, surname, subject and mark from the following tables:
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 tried the following SQL query within SQL*Plus
SELECT DISTINCT FORENAME AS "First Name", SURNAME, MODULE_NAME AS "Subject", MARK FROM STUDENTS, MODULES, MARKS;but this displays duplicate entries.
The following simply does not work.
SELECT st.FORENAME AS "First Name", st.SURNAME, mod.MODULE_NAME AS "Subject" FROM STUDENTS st JOIN MODULES mod ON (st.STUDENT_NO = mod.STUDENT_NO);
Am I missing something? Maybe a subquery? Please help, total database noob.