2

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.

3

Why would this not be sufficient?

SELECT * FROM (
  SELECT MOD.MODULE_NAME, MA.MARK
  FROM MODULES MOD 
  JOIN MARKS MA ON (MOD.MODULE_CODE = MA.MODULE_CODE) 
  JOIN STUDENTS S ON (S.STUDENT_NO = MA.STUDENT_NO)
  WHERE S.SURNAME = 'Doe'
  ORDER BY 2) x
WHERE ROWNUM = 1;
2

I've gone for a simple sub-query approach partly because I like the ROW_NUMBER functionality. So this lists the marks per module is ascending order in the inner query and then just returns the values for the first row number returned.

SQL> select * from wg_student
2  /
STUDENT_NO SURNAME              FORENAME
---------- -------------------- --------------------
     1 Doe                  John
SQL> select * from wg_module
2  /
MODULE_C MODULE_NAME
-------- --------------------
DB       Databases
PR       Programming
OS       Operating Systems
SQL> select * from wg_mark
2  /
STUDENT_NO MODULE_C       MARK
---------- -------- ----------
     1 DB               80
     1 DB               90
     1 PR               65
     1 PR               80
     1 OS               75
     1 OS               50
6 rows selected.
SQL> SELECT sub_query.module_name, sub_query.mark
 2  FROM (SELECT mod.module_name, ma.mark, ROW_NUMBER() OVER(ORDER BY ma.mark ASC) rn
3            FROM wg_module mod
4                 INNER JOIN wg_mark ma ON (mod.module_code = ma.module_code)
5                 INNER JOIN wg_student st ON (ma.student_no = st.student_no)
6           WHERE st.surname = 'Doe') sub_query
7   WHERE sub_query.rn = 1
8  /
MODULE_NAME                MARK
-------------------- ----------
Databases                    80
Operating Systems            50
Programming                  65
1

I have done this:

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 DISTINCT S.STUDENT_NO 
    FROM STUDENTS S 
    WHERE S.SURNAME = 'Lothbrock'
) 
AND MARKS.MARK = 
(
    SELECT MIN(MARKS.MARK) 
    FROM MARKS
) 
ORDER BY MA.MARK;

Now I just have to figure out how to implement "LIMIT 1" in SQL*Plus...

0

Do you need to use a subquery? You could, but this may be a bit easier. Not tested this as I didn't have time to create the tables and fill data. You could try this:

select -- stu.name, 
   mod.module_name,
   min(mar.mark) mark_score
from modules mod
join marks mar
  on (mar.module_code = mod.module_code)
join students stu
  on (stu.student_no = mar.student_no)
where stu.name = 'John Doe'
group by mod.module_name;
0

Not sure what environment you're in, but this is a good case for windowing/partitioning functions. For you, if you wanted to find the lowest score for each student and in what module, it would just be

select x.*
from (
select student_no, module_code, row_number()over(partition by student_no order by mark asc) rownum from #test
) x
where x.rownum = 1;

but if you're not using a db environment that doesn't support windowing, then you could use subqueries.

EDIT: I would change this query to use the RANK() function just in case you have two modules with the same grade like this:

select x.*
from (
select student_no, module_code, RANK()over(partition by student_no order by mark asc) rownum from #test
) x
where x.rownum = 1;

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.