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 DefinitionDROP 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.

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 is enrolled in 0 or more coursesteacher teaches 0 or more coursescourse has 0 or more students enrolled

there is a many-to-many relationship between students and coursesthere 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_countFROM students s  LEFT JOIN student_courses sc  ON s.student_id = sc.student_idGROUP 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_countFROM student_enrollment_statusesGROUP 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_nameFROM teacher_course_rankingsWHERE 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_nameFROM teacher_course_rankingsWHERE 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_nameFROM teacher_student_rankingsWHERE 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_nameFROM teacher_student_rankingsWHERE 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_taughtFROM 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_countFROM student_enrollment_statusesWHERE 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_countFROM courses c  LEFT JOIN student_courses sc  ON c.course_id = sc.course_idGROUP BY  c.course_id  , c.course_nameORDER 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.