How to check student enrollment across different course levels in MySQL?

Courses
1

Hey folks, I’m working on a database project and could use some help. I’ve got three tables: learners, registrations, and classes. Each learner can sign up for any class, and classes are ranked from 1 (basic) to 4 (expert).

I’m trying to pull learner info to see if they’re signed up for classes at each level. I want my output to look something like this:

learner_id | learner_name | basic | intermediate | advanced | expert
----------------------------------------------------------------
98765        John Doe       Yes      Yes            No        No
87654        Jane Smith     Yes      Yes            Yes       No

Here’s what my tables look like:

learners table:

learner_id | full_name | contact | school_code | year

classes table:

class_id | title | summary | rank | points | start_date | end_date

registrations table:

reg_id | learner_id | class_id | signup_date

Any ideas on how to write a query for this? Thanks in advance!

2

hey mate, i think i got a solution for ya. try this:

SELECT l.learner_id, l.full_name,
IF(SUM(c.rank=1)>0,‘Yes’,‘No’) basic,
IF(SUM(c.rank=2)>0,‘Yes’,‘No’) intermediate,
IF(SUM(c.rank=3)>0,‘Yes’,‘No’) advanced,
IF(SUM(c.rank=4)>0,‘Yes’,‘No’) expert
FROM learners l
LEFT JOIN registrations r ON l.learner_id=r.learner_id
LEFT JOIN classes c ON r.class_id=c.class_id
GROUP BY l.learner_id, l.full_name

this should do the trick! lmk if u need more help

3

To tackle this, you’ll need to use a combination of JOIN operations and conditional aggregation. Here’s a query that should do the trick:

SELECT 
    l.learner_id,
    l.full_name AS learner_name,
    MAX(CASE WHEN c.rank = 1 THEN 'Yes' ELSE 'No' END) AS basic,
    MAX(CASE WHEN c.rank = 2 THEN 'Yes' ELSE 'No' END) AS intermediate,
    MAX(CASE WHEN c.rank = 3 THEN 'Yes' ELSE 'No' END) AS advanced,
    MAX(CASE WHEN c.rank = 4 THEN 'Yes' ELSE 'No' END) AS expert
FROM 
    learners l
LEFT JOIN 
    registrations r ON l.learner_id = r.learner_id
LEFT JOIN 
    classes c ON r.class_id = c.class_id
GROUP BY 
    l.learner_id, l.full_name;

This query uses LEFT JOINs to include all learners, even if they haven’t registered for any classes. The CASE statements inside MAX functions create the Yes/No columns for each rank level. Hope this helps with your project!