0

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.

1

The first query you have used returns Cartesian product of the rows from these three tables. For details: Join

The following is the simple example to join three tables.

SQL> select * from student;

STUDENT_ID FIRSTNAME        LASTNAME
---------- -------------------- --------------------
     1 Will         Smith

SQL> select * from module;

 MODULE_ID NAME
---------- --------------------
     1 Computing

SQL> select * from marks;

STUDENT_ID  MODULE_ID       MARK
---------- ---------- ----------
     1      1         40

SQL> 


SQL> select s.firstname, s.lastname, mo.name Module_Name, m.mark 
     from student s 
         join marks m on (s.student_id=m.student_id) 
         join module mo on (m.module_id=mo.module_id);

FIRSTNAME     LASTNAME     MODULE_NAME     MARK    
------------- ------------ --------------- --------
Will          Smith         Computing      40

SQL> 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.