Multiple joins display student course name-CodePudding

I have the following setup, which is working perfectly. I am difficulty figuring out the syntax how to display the course name in the output. In my test CASE all the rows should have the value Geometry.

In addition, how could I use rank or rank_dense to limit the output to display only 1 row with the highest average

Thanks in advance to all who answer


CREATE TABLE students(student_id, first_name,  last_name) AS
   SELECT 1, 'Faith', 'Aaron'  FROM dual UNION ALL
  SELECT 2,  'Lisa',  'Saladino' FROM dual UNION ALL
  SELECT 3,  'Leslee',  'Altman'   FROM dual UNION ALL
  SELECT 4, 'Patty',  'Kern'    FROM dual UNION ALL
  SELECT 5,  'Betty',  'Bowers'    FROM dual;


CREATE TABLE courses(course_id, course_name) AS
SELECT 1, 'Geometry' FROM dual UNION ALL
SELECT 2, 'Trigonometry' FROM dual UNION ALL
SELECT 3, 'Calculus' FROM DUAL;

CREATE TABLE grades(student_id,
course_id, grade) AS
SELECT 1, 1, 75 FROM dual UNION ALL
SELECT 1, 1, 81 FROM dual UNION ALL
SELECT 1, 1, 76 FROM dual UNION ALL
SELECT 2, 1, 100 FROM dual UNION ALL
SELECT 2, 1, 95 FROM dual UNION ALL
SELECT 2, 1, 96 FROM dual UNION ALL
SELECT 3, 1, 80 FROM dual UNION ALL
SELECT 3, 1, 85 FROM dual UNION ALL
SELECT 3, 1, 86 FROM dual UNION ALL
SELECT 4, 1, 88 FROM dual UNION ALL
SELECT 4, 1, 85 FROM dual UNION ALL
SELECT 4, 1, 91 FROM dual UNION ALL
SELECT 5, 1, 98 FROM dual UNION ALL
SELECT 5, 1, 74 FROM dual UNION ALL
SELECT 5, 1, 81 FROM dual;

/* average grade of each student */

select s.student_id
          , s.first_name 
          , s.last_name
         , round(avg(g.grade), 1) as student_avg
from students s 
     join grades g
         on s.student_id = g.student_id
group by s.student_id, s.first_name, s.last_name
ORDER BY avg(g.grade) DESC;

CodePudding user response：

Something like this?

SQL> with temp as
  2    (select s.student_id
  3          , s.first_name
  4          , s.last_name
  5          , c.course_name
  6          , round(avg(g.grade), 1) as student_avg
  7          , rank() over (order by avg(g.grade) desc) rnk
  8     from students s join grades g  on s.student_id = g.student_id
  9                     join courses c on c.course_id  = g.course_id
 10     group by s.student_id, s.first_name, s.last_name, c.course_name
 11    )
 12  select student_id, first_name, last_name, course_name, student_avg
 13  from temp
 14  where rnk <= 3
 15  order by rnk;

STUDENT_ID FIRST_ LAST_NAM COURSE_NAME  STUDENT_AVG
---------- ------ -------- ------------ -----------
         2 Lisa   Saladino Geometry              97
         4 Patty  Kern     Geometry              88
         5 Betty  Bowers   Geometry            84.3

SQL>