Student Enrollment SQL Challenge

How many questions can you answer?

The Challenge

Given the database tables below, use your SQL skills to answer as many of the questions that follow.

To get started, build the schema provided in SQL Fiddle or DB Fiddle using PostgreSQL v9.6.

Database Definition

DROP TABLE IF EXISTS teachers;
CREATE TABLE teachers (
  teacher_id BIGINT PRIMARY KEY
  , teacher_name VARCHAR(64)
);
DROP TABLE IF EXISTS courses;
CREATE TABLE courses (
  course_id VARCHAR(16) PRIMARY KEY
  , course_name VARCHAR(128) NOT NULL
  , teacher_id BIGINT
  , FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)                       );
DROP TABLE IF EXISTS students;
CREATE TABLE students (
  student_id BIGINT PRIMARY KEY
  , student_name VARCHAR(64)
);
DROP TABLE IF EXISTS student_courses;
CREATE TABLE student_courses (
  course_id VARCHAR(16)
  , student_id BIGINT
  , FOREIGN KEY (course_id) REFERENCES courses(course_id)
  , FOREIGN KEY (student_id) REFERENCES students(student_id)
  , PRIMARY KEY (course_id, student_id)
);

Questions

1. Draw an entity relationship diagram (or state in words the entity relationships) that accurately describes this database.
2. Implement a query to get a list of all students and how many courses each student is enrolled in.
3. Implement a query that shows the number of full-time and part-time students A full-time student is enrolled in at least 4 courses. A part-time student is enrolled in at least 1 course, but no more than 3.
4. Write a query that shows which teacher(s) are teaching the most number of courses.
5. Write a query that shows which teacher(s) are teaching the least number of courses.
6. Write a query that shows which teacher(s) are teaching the most number of students.
7. Write a query that shows which teacher(s) are teaching the least number of students.
8. Write a query that shows what the average number of courses taught by a teacher.
9. Write a query that tells us how many students are not enrolled. Who are these unenrolled students?
10. Write a query that lists the courses in order of most popular to least popular.

Image by Tumisu from Pixabay

Pause! If you haven’t answered any of the questions above, try to answer as many as you can on your own before looking at the solutions below.

Solutions

There are multiple ways to write the SQL queries that answer this challenge’s questions, so they may not exactly match your own. Here are my answers to the questions. Check out the solutions Gist, if you prefer to view them there.

1/ Draw an entity relationship diagram (or state in words the entity relationships) that accurately describes this database.

Student Enrollment Database Entity Relationship Diagram

student is enrolled in 0 or more courses
teacher teaches 0 or more courses
course has 0 or more students enrolled
there is a many-to-many relationship between students and courses
there is a one-to-many relationship between teachers and courses

2/ Implement a query to get a list of all students and how many courses each student is enrolled in.

SELECT
  s.student_id
  , s.student_name
  , COUNT(sc.course_id) AS course_count
FROM students s
  LEFT JOIN student_courses sc
  ON s.student_id = sc.student_id
GROUP BY
  s.student_id
  , s.student_name
;

3/ Implement a query that shows the number of full-time and part-time students. A full-time student is enrolled in at least 4 courses. A part-time student is enrolled in at least 1 course, but no more than 3.

WITH enrolled_student_course_counts AS (
  SELECT
    s.student_id
    , s.student_name
    , COUNT(sc.course_id) AS course_count
  FROM students s
    LEFT JOIN student_courses sc
    ON s.student_id = sc.student_id
  GROUP BY
    s.student_id
    , s.student_name
  HAVING COUNT(sc.course_id) > 0
)
, student_enrollment_statuses AS (
  SELECT
    student_id
    , student_name
    , CASE WHEN course_count >= 4 THEN 'full-time'
           WHEN course_count BETWEEN 1 AND 3 THEN 'part-time'
      END AS student_enrollment_status
  FROM enrolled_student_course_counts
)
SELECT
  UPPER(student_enrollment_status) AS student_enrollment_status
  , COUNT(student_enrollment_status) AS student_enrollment_status_count
FROM student_enrollment_statuses
GROUP BY student_enrollment_status
;

4/ Write a query that shows which teacher(s) are teaching the most number of courses.

WITH teacher_course_rankings AS (
  SELECT
    t.teacher_id
    , t.teacher_name
    , COUNT(c.course_id) AS teacher_course_count
    , RANK() OVER(ORDER BY COUNT(c.course_id) DESC) AS teacher_course_rank
  FROM teachers t
    LEFT JOIN courses c
    ON t.teacher_id = c.teacher_id
  GROUP BY
    t.teacher_id
    , t.teacher_name
)
SELECT
  teacher_id
  , teacher_name
FROM teacher_course_rankings
WHERE teacher_course_rank = 1
;

5/ Write a query that shows which teacher(s) are teaching the least number of courses.

WITH teacher_course_rankings AS (
  SELECT
    t.teacher_id
    , t.teacher_name
    , COUNT(c.course_id) AS teacher_course_count
    , RANK() OVER (ORDER BY COUNT(c.course_id)) AS teacher_course_rank
  FROM teachers t
    LEFT JOIN courses c
    ON t.teacher_id = c.teacher_id
  GROUP BY
    t.teacher_id
    , t.teacher_name
)
SELECT
  teacher_id
  , teacher_name
FROM teacher_course_rankings
WHERE teacher_course_rank = 1
;

6/ Write a query that shows which teacher(s) are teaching the most number of students.

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
;

7/ Write a query that shows which teacher(s) are teaching the least number of students.

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)) 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
;

8/ Write a query that shows what the average number of courses taught by a teacher.

WITH teacher_course_counts AS (
  SELECT
    t.teacher_id
    , t.teacher_name
    , COUNT(c.course_id) AS teacher_course_count
  FROM teachers t
    LEFT JOIN courses c
    ON t.teacher_id = c.teacher_id
  GROUP BY
    t.teacher_id
    , t.teacher_name
)
SELECT
  AVG(teacher_course_count) avg_courses_taught
FROM teacher_course_counts
;

9/ Write a query that tells us how many students are not enrolled. Who are these unenrolled students?

WITH student_course_counts AS (
  SELECT
    s.student_id
    , s.student_name
    , COUNT(sc.course_id) AS course_count
  FROM students s
    LEFT JOIN student_courses sc
    ON s.student_id = sc.student_id
  GROUP BY s.student_id
)
, student_enrollment_statuses AS (
  SELECT
    student_id
    , student_name
    , CASE WHEN course_count = 0 THEN 'unenrolled'
           ELSE 'enrolled'
      END AS student_enrollment_status
  FROM student_course_counts
)
SELECT
  UPPER(student_enrollment_status) AS student_enrollment_status
  , COUNT(student_enrollment_status) AS student_enrollment_status_count
FROM student_enrollment_statuses
WHERE student_enrollment_status = 'unenrolled'
GROUP BY student_enrollment_status
; 

10/ Write a query that lists the courses in order of most popular to least popular.

SELECT
  c.course_id
  , c.course_name
  , COUNT(sc.student_id) AS student_count
FROM courses c
  LEFT JOIN student_courses sc
  ON c.course_id = sc.course_id
GROUP BY
  c.course_id
  , c.course_name
ORDER BY 3 DESC
;

In terms of habits and style, I tend to capitalize SQL keywords and place commas at the front when listing columns, and make heavy use of common table expressions. I also try to maintain consistent indentation and generously use reasonable column and table aliases.

Hungry for more?

I highly recommend Mode Analytics’ SQL Tutorial for Data Analysis. It was “designed for people who want to answer questions with data,” and explains the beginner, intermediate, and advanced SQL techniques for doing that quite well. Additionally, their tutorials have interactive elements, which allow you to write queries against existing data sets.

Also, take a look at this 4-hour long SQL tutorial found on freeCodeCamp’s YouTube channel. The tutorial provides a solid introduction to SQL for beginners.

Thank you for reading!

If you learned something new or enjoyed reading this article, please clap it up 👏 and share it so that others will see it. Feel free to leave a comment too.