Home » SQL & PL/SQL » SQL & PL/SQL » Teachers teaching the most students (19.2)
Teachers teaching the most students [message #686218] Sun, 03 July 2022 16:25 Go to next message
Unclefool
Messages: 59
Registered: August 2021
Member
I wrote a query that shows which teacher(s) are teaching the most number of students. This appears to be working fine but it appears to be verbose and clunky.

Can the query be rewritten to be succinct and easier to read?

Below is my test CASE and query. Thanks in advance to all who answer

 

CREATE TABLE teachers(teacher_id, first_name,  last_name) AS
   SELECT 101, 'Keith', 'Stein'  FROM dual UNION ALL
  SELECT 102,  'Roger',  'Wood' FROM dual UNION ALL
  SELECT 103,  'Douglas',  'Kern'   FROM dual UNION ALL
  SELECT 104, 'Paul',  'Weber'    FROM dual UNION ALL
  SELECT 105,  'Jeffrey',  'Lebowitz'    FROM dual UNION  ALL
  SELECT 106,  'Gabby',  'Orr'    FROM dual;


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,  'Beth',  'Cooper'    FROM dual UNION  ALL
  SELECT 95,  'Zak',  'Despart'    FROM dual UNION  ALL
  SELECT 96,  'Owen',  'Balbert'    FROM dual UNION  ALL
   SELECT 97,  'Jack',  'Aprile'    FROM dual UNION  ALL
  SELECT 98,  'Nicole',  'Kramer'    FROM dual UNION  ALL
   SELECT 99,  'Jill',  'Coralnick'    FROM dual;


CREATE TABLE courses(course_id, course_name, teacher_id, semester) AS
SELECT 1, 'Geometry', 101, '2022-2' FROM DUAL UNION ALL
SELECT 2, 'Trigonometry', 102, '2022-2' FROM DUAL UNION ALL
SELECT 3, 'Calculus', 103, '2022-2' FROM DUAL UNION ALL
SELECT 4, 'Chemistry', 104, '2022-2' FROM DUAL UNION ALL 
SELECT 5, 'Biology', 105, '2022-2' FROM DUAL UNION ALL 
SELECT 6, 'Physcology', 106, '2022-2' FROM DUAL; 

CREATE TABLE student_courses (student_id,course_id) AS
SELECT 1, 1 FROM dual UNION ALL
SELECT 2, 1 FROM dual UNION ALL
SELECT 3, 1 FROM dual UNION ALL
SELECT 4, 1 FROM dual UNION ALL
SELECT 5, 1 FROM dual UNION ALL
SELECT 1, 2 FROM dual UNION ALL
SELECT 2, 2 FROM dual UNION ALL
SELECT 3, 2 FROM dual UNION ALL
SELECT 4, 2 FROM dual UNION ALL
SELECT 5, 2 FROM dual UNION ALL
SELECT 1, 3 FROM dual UNION ALL
SELECT 2, 3 FROM dual UNION ALL
SELECT 3, 3 FROM dual UNION ALL
SELECT 4, 3 FROM dual UNION ALL
SELECT 5, 3 FROM dual UNION ALL
SELECT 97, 1 FROM dual UNION ALL 
SELECT 97, 3 FROM dual UNION ALL 
SELECT 97, 5 FROM dual UNION ALL
SELECT 97, 6 FROM dual UNION ALL
SELECT 98, 3 FROM dual UNION ALL 
SELECT 98, 4 FROM dual UNION ALL
SELECT 98, 5 FROM dual UNION ALL
SELECT 99, 2 FROM dual UNION ALL 
SELECT 99, 4 FROM dual UNION ALL
SELECT 99, 5 FROM dual UNION ALL
SELECT 99, 6 FROM dual;


WITH teacher_student_rankings AS (
  SELECT
    t.teacher_id
    , t.teacher_name
    , COUNT(DISTINCT sc.student_id) AS teacher_student_count
    , RANK() OVER (ORDER BY COUNT(DISTINCT sc.student_id) DESC) AS teacher_student_rank
  FROM teachers t
    LEFT JOIN courses c
    ON t.teacher_id = c.teacher_id
    LEFT JOIN student_courses sc
    ON c.course_id = sc.course_id
  GROUP BY
    t.teacher_id
    , t.teacher_name
)
SELECT
  teacher_id
  , teacher_name
FROM teacher_student_rankings
WHERE teacher_student_rank = 1:

Re: Teachers teaching the most students [message #686219 is a reply to message #686218] Mon, 04 July 2022 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68264
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"teacher_student_count" is never used, remove it and GROUP BY but add a PARTITION BY clause to RANK function.
What if you have 2 teachers with same names?
Why LEFT JOIN? INNER JOIN unless you think all teachers give no course to any student.
In the end, you need "teachers" table only in the main query.

Re: Teachers teaching the most students [message #686221 is a reply to message #686219] Mon, 04 July 2022 00:48 Go to previous messageGo to next message
Unclefool
Messages: 59
Registered: August 2021
Member
Thanks.
Re: Teachers teaching the most students [message #686222 is a reply to message #686221] Mon, 04 July 2022 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68264
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Did you succeed to modify the query? If so post the new one.

Re: Teachers teaching the most students [message #686223 is a reply to message #686222] Mon, 04 July 2022 01:31 Go to previous messageGo to next message
Unclefool
Messages: 59
Registered: August 2021
Member
No I can't seem to get it to work. I am Googling for examples as I'm a novice developer
Re: Teachers teaching the most students [message #686224 is a reply to message #686223] Mon, 04 July 2022 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68264
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

My bad, there is an error in what I said, as you have COUNT in RANK ORDER BY, you need to group by teacher in the subquery.
So:
TEST> WITH teacher_student_rankings AS (
  2    SELECT c.teacher_id,
  3           RANK() OVER (ORDER BY COUNT(DISTINCT sc.student_id) DESC)
  4             AS teacher_student_rank
  5    FROM courses c JOIN student_courses sc
  6           ON c.course_id = sc.course_id
  7    GROUP BY c.teacher_id
  8  )
  9  SELECT t.teacher_id, initcap(t.first_name)||' '||upper(t.last_name) teacher_name
 10  FROM teachers t JOIN teacher_student_rankings r
 11         ON r.teacher_id = t.teacher_id
 12  WHERE teacher_student_rank = 1
 13  ORDER BY t.teacher_id
 14  /
TEACHER_ID TEACHER_NAME
---------- ----------------
       103 Douglas KERN
Re: Teachers teaching the most students [message #686225 is a reply to message #686224] Mon, 04 July 2022 05:50 Go to previous messageGo to next message
Unclefool
Messages: 59
Registered: August 2021
Member
Thanks for your help. How would I include the student count in the output?
Re: Teachers teaching the most students [message #686226 is a reply to message #686225] Mon, 04 July 2022 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68264
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just add it in the subquery as you did in your first post.

Re: Teachers teaching the most students [message #686227 is a reply to message #686226] Mon, 04 July 2022 09:21 Go to previous messageGo to next message
Unclefool
Messages: 59
Registered: August 2021
Member
Sorry to bother you but I can't seem to get the count to display
Re: Teachers teaching the most students [message #686228 is a reply to message #686227] Mon, 04 July 2022 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68264
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TEST> WITH teacher_student_rankings AS (
  2    SELECT c.teacher_id,
  3           RANK() OVER (ORDER BY COUNT(DISTINCT sc.student_id) DESC)
  4             AS teacher_student_rank
  5  -----------------
  6           , COUNT(DISTINCT sc.student_id) AS teacher_student_count
  7  -----------------
  8    FROM courses c JOIN student_courses sc
  9           ON c.course_id = sc.course_id
 10    GROUP BY c.teacher_id
 11  )
 12  SELECT t.teacher_id, initcap(t.first_name)||' '||upper(t.last_name) teacher_name
 13  -----------------
 14         , r.teacher_student_count
 15  -----------------
 16  FROM teachers t JOIN teacher_student_rankings r
 17         ON r.teacher_id = t.teacher_id
 18  WHERE teacher_student_rank = 1
 19  ORDER BY t.teacher_id
 20  /
TEACHER_ID TEACHER_NAME     TEACHER_STUDENT_COUNT
---------- ---------------- ---------------------
       103 Douglas KERN                         7
Re: Teachers teaching the most students [message #686238 is a reply to message #686228] Sun, 10 July 2022 02:43 Go to previous message
Michel Cadot
Messages: 68264
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any feedback?

Previous Topic: Replace column Value
Next Topic: Sporadic Certification Validation Error While accessing the external portal
Goto Forum:
  


Current Time: Tue Aug 16 18:18:28 CDT 2022