Burak Nurçiçek Burak Nurçiçek - 14 days ago 5
MySQL Question

how can i find this query in mysql workbench

For each student, find the number of courses they take and sort
the rows in descending order. (e.g. student id, the number of
courses taken by that student)

STUDENT TABLE

| ID | name | dept_name | tot_cred |
| S0901 | Alice | Comp.Sci. | 83 |
| S0902 | Martha | Comp.Sci. | 75 |
| S0903 | Micheal | Comp.Sci. | 45 |
| S0904 | Rose | Comp.Sci. | 77 |
| S0905 | Alfie | Comp.Sci. | 91 |
| S1901 | Brad | Biology | 23 |

TAKES TABLE

| ID | course_id | sec_id | semester | year | grade
| S0901 | CS-101 | 1 | Fall | 2009 | A
| S0901 | CS-315 | 1 | Spring | 2010 | B+
| S0901 | HIS-351 | 1 | Spring | 2010 | A-
| S0901 | MTH-101 | 1 | Fall | 2009 | A
| S0901 | MTH-102 | 1 | Spring | 2009 | B+
| S0902 | CS-101 | 1 | Fall | 2009 | A
| S0902 | CS-315 | 1 | Spring | 2010 | B+
| S0902 | CS-319 | 1 | Spring | 2010 | B
| S0902 | HIS-351 | 1 | Spring | 2010 | A-
| S0902 | MTH-101 | 1 | Fall | 2009 | A
| S0902 | MTH-102 | 1 | Spring | 2009 | B+
| S1901 | CS-101 | 1 | Fall | 2009 | B+
| S1901 | CS-190 | 1 | Spring | 2009 | C
| S1901 | CS-315 | 1 | Spring | 2010 | A-
| S1901 | HIS-351 | 1 | Spring | 2010 | A-

Answer

Just join and group up the result.

SELECT COUNT(*), s.id FROM student s, takes t where t.id = s.id group by s.id order by count(*) desc
Comments