Writing Nested SQL queries

Question

I have 3 tables as StudentData which includes data of students, Subjects table which has data of all subjects offered and Marks which has the marks obtained by students for each subject.Marks table maps to StudentData table by StudentId and Subjects table by SubjectId

What I want to do is select the maximum marks for each subject and the name of the student like follows

So I wrote a Oracle PL/SQL Query as follows,

select MAX(marks)
from
    (select Marks ,subjects.name as SJN ,studentdata.name
    from (studentdata inner Join marks On studentdata.studentid = marks.studentid)
    Inner Join subjects On subjects.subjectid = marks.subjectid)
where   SJN in (select name from subjects);

But it gives only one result.Please help me to develop a query to get my expected result set.

You have no GROUP BY clause so your query aggregates over the entire dataset. You need to add GROUP BY SJN, name to the query — Nick
– Nick, Commented Apr 2, 2020 at 7:00

Littlefoot · Accepted Answer · 2020-04-02 07:08:02Z

Something like this? Lines #1 - 26 represent sample data (you don't type that); query you need begins at line #28.

SQL> with
  2  -- sample data
  3  studentdata (studentid, name, course) as
  4    (select 1, 'olivier', 'it'    from dual union all
  5     select 2, 'noah', 'business' from dual union all
  6     select 3, 'jack', 'business' from dual union all
  7     select 4, 'mason', 'it'      from dual union all
  8     select 5, 'julion', 'it'     from dual),
  9  subjects (subjectid, name) as
 10    (select 1, 'java'           from dual union all
 11     select 2, 'business stg'   from dual union all
 12     select 3, 'python'         from dual union all
 13     select 4, 'statistics'     from dual union all
 14     select 5, 'mgt accounting' from dual union all
 15     select 7, 'social studies' from dual union all
 16     select 8, 'ess english'    from dual),
 17  marks (id, studentid, subjectid, marks) as
 18    (select 1, 1, 1, 56 from dual union all
 19     select 2, 1, 2, 78 from dual union all
 20     select 3, 1, 7, 83 from dual union all
 21     select 4, 1, 3, 45 from dual union all
 22     select 5, 1, 5, 63 from dual union all
 23     --
 24     select 6, 2, 1, 99 from dual union all
 25     select 7, 3, 2, 10 from dual union all
 26     select 8, 4, 7, 83 from dual)
 27  --
 28  select b.name subject, s.name student, m.marks
 29  from marks m join subjects b on b.subjectid = m.subjectid
 30  join studentdata s on s.studentid = m.studentid
 31  where m.marks = (select max(m1.marks)
 32                   from marks m1
 33                   where m1.subjectid = m.subjectid
 34                  )
 35  order by b.name, s.name;

SUBJECT        STUDENT      MARKS
-------------- ------- ----------
business stg   olivier         78
java           noah            99
mgt accounting olivier         63
python         olivier         45
social studies mason           83
social studies olivier         83

6 rows selected.

SQL>

Thanks the query gives the expected result.Can you please tell what is the advantage or what is the aim of joining the same table to itself (the marks table)????
It is used - as a subquery - to fetch maximum marks per subject.

Popeye · Accepted Answer · 2020-04-02 07:10:04Z

You can use the analytical function ROW_NUMBER as follows:

SELECT SJN, MARKS, STUNAME FROM
(SELECT
    MARKS.MARKS,
    SUBJECTS.NAME   AS SJN,
    STUDENTDATA.NAME AS STUNAME,
    ROW_NUMBER() OVER (PARTITION BY SUBJECTS.SUBJECTID 
                          ORDER BY MARKS.MARKS DESC NULLS LAST) AS RN
FROM
    STUDENTDATA
    INNER JOIN MARKS ON STUDENTDATA.STUDENTID = MARKS.STUDENTID 
    INNER JOIN SUBJECTS ON SUBJECTS.SUBJECTID = MARKS.SUBJECTID)
WHERE RN = 1;

Thorsten Kettner · Accepted Answer · 2020-04-02 07:30:09Z

The first thing that comes to mind: select the best mark per subject, then select the student(s) with that mark in that subject:

select s.name as subject, m.marks, sd.name as studentname
from marks m
join studentdata sd on sd.studentid = m.studentid
join subjects s on s.subjectid = m.subjectid
where (m.subjectid, m.marks) in
(
  select subjectid, max(marks)
  from marks
  group by subjetid
);

As you see, we select twice from marks. This can be avoided with a window function:

select s.name as subject, m.marks, sd.name as studentname
from
(
  select
    subjectid,
    marks,
    max(marks) over (partition by subjectid) as max_marks
  from marks
) m
join studentdata sd on sd.studentid = m.studentid
join subjects s on s.subjectid = m.subjectid
where m.marks = m.max_marks;

Another option is to join and to check that no better marks exist for the subject:

select s.name as subject, m.marks, sd.name as studentname
from marks m
join studentdata sd on sd.studentid = m.studentid
join subjects s on s.subjectid = m.subjectid
where not exists
(
  select null
  from marks m2
  where m2.subjectid = m.subjectid
  and m2.marks > m.marks
);

Which of these options is best, I cannot tell. Decide for the one that you find most readable. Regardless of which query you choose, this index should help the DBMS finding the highest marks quickly:

create index idx on marks(subjectid, marks);

Collectives™ on Stack Overflow

Writing Nested SQL queries

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related