Fall Classes

Challenge:

Without counting duplicates, how many courses are being offered in the FALL2020 term at Shallow Grave University? Submit the flag in the following format: flag{#}

Use the file from Address Book.
Max attempts: 10

Solution:

Once again this challenge uses the database I created during the Address Book challenge.

I review the tables again to get an idea of how to start my SQL query:

mysql> SHOW TABLES;
+---------------------+
| Tables_in_westridge |
+---------------------+
| countries           |
| courses             |
| degree_types        |
| enrollments         |
| passwords           |
| payment_statuses    |
| programs            |
| roles               |
| roles_assigned      |
| states              |
| term_courses        |
| terms               |
| users               |
+---------------------+

Given the challenge description reviewing the courses table was the logical next step:

mysql> SHOW COLUMNS IN courses;
+------------------+---------------+------+-----+---------+----------------+
| Field            | Type          | Null | Key | Default | Extra          |
+------------------+---------------+------+-----+---------+----------------+
| course_id        | int           | NO   | PRI | NULL    | auto_increment |
| title            | varchar(128)  | NO   |     | NULL    |                |
| level            | varchar(32)   | NO   |     | NULL    |                |
| description      | varchar(128)  | NO   | UNI | NULL    |                |
| long_description | varchar(1024) | YES  |     | NULL    |                |
| sem_hours        | int           | NO   |     | NULL    |                |
+------------------+---------------+------+-----+---------+----------------+

The challenge specifically asks for courses from the "FALL2020 term" and it doesn't appear that there is a field for that data in the courses table. Reviewing the table listing again I see there are terms and term_courses tables. I reviewed the table structures to gain a further understanding of how to create my SQL query.

mysql> SHOW COLUMNS IN terms;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| term_id     | int          | NO   | PRI | NULL    | auto_increment |
| term_name   | varchar(56)  | NO   | UNI | NULL    |                |
| start_date  | date         | NO   |     | NULL    |                |
| end_date    | date         | NO   |     | NULL    |                |
| description | varchar(128) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

mysql> SHOW COLUMNS IN term_courses;
+-------------+------+------+-----+---------+----------------+
| Field       | Type | Null | Key | Default | Extra          |
+-------------+------+------+-----+---------+----------------+
| term_crs_id | int  | NO   | PRI | NULL    | auto_increment |
| course_id   | int  | NO   | MUL | NULL    |                |
| term_id     | int  | NO   | MUL | NULL    |                |
| instructor  | int  | NO   | MUL | NULL    |                |
+-------------+------+------+-----+---------+----------------+

I then ran a simple query to see the data within the terms table:

mysql> SELECT * FROM terms;
+---------+------------+------------+------------+----------------------+
| term_id | term_name  | start_date | end_date   | description          |
+---------+------------+------------+------------+----------------------+
|       1 | SPRING2020 | 2020-04-06 | 2020-07-20 | Spring semester 2020 |
|       2 | FALL2020   | 2020-08-03 | 2020-11-20 | Fall semester 2020   |
+---------+------------+------------+------------+----------------------+

So now it makes sense why "FALL2020" was written that way in the description and I have a rough idea on how the SQL query should look. In simple text:

"FALL2020" -> terms.term_id -> term_courses.term_id -> term_courses.course_id

Since course_id is a primary key in the courses table I don't have to include a SQL join to that table, I can just count the number of unique values in term_courses.course_id.

mysql> SELECT COUNT(DISTINCT course_id) FROM term_courses INNER JOIN terms ON term_courses.term_id = terms.term_id WHERE terms.term_name = "FALL2020";
+---------------------------+
| COUNT(DISTINCT course_id) |
+---------------------------+
|                       401 |
+---------------------------+

The completed flag is flag{401}.