CSci 284 - Homework 1

Due: Tuesday, Feb. 04, 2025

This assignment is worth 25 points. For full credit:

Resources. The Aggregate Functions reference. String Functions may also be useful.

More SQL Queries (25 points - 5 points each).

For each problem, write an SQL query that computes the requested information. All such queries will come from the class_schedule table in the cs284_spring25 database. Use describe to familiarize yourself with the columns it contains.

One thing to know is that GROUP BY can be used with multiple fields.

When you turn this in, please include your code and the output (or just part of the output if the whole set is really long). Be sure to match the output shown, including column names.

  1. Write a query that shows the five subjects that had the smallest number of students.

    +---------+--------------+
| subject | tot_students |
+---------+--------------+
| MDST    |            7 |
| AMST    |            8 |
| ESCI    |            9 |
| GREK    |           11 |
| ASIA    |           14 |
+---------+--------------+

  2. Write a query that shows the five subjects that had the most students.

    +---------+--------------+
| subject | tot_students |
+---------+--------------+
| PSYC    |          500 |
| POLS    |          430 |
| HIST    |          428 |
| ENGL    |          388 |
| ECON    |          369 |
+---------+--------------+

  3. Write a query that displays the five classes with the most students across all sections. Include the number of sections of the class.

    +---------+--------+--------------+--------------+
| subject | number | num_sections | tot_students |
+---------+--------+--------------+--------------+
| STAT    |    204 |            5 |          128 |
| ENGL    |    101 |           12 |          127 |
| ECON    |    120 |            4 |           99 |
| BUSI    |    215 |            1 |           94 |
| BIOL    |    130 |            4 |           79 |
+---------+--------+--------------+--------------+

  4. Find the five most popular time slots (in terms of number of classes). Once you get it, rewrite the query so the result looks like the next table down, with just two columns.

    +--------------+--------------------+------------------+-------------+
| meeting_days | meeting_start_time | meeting_end_time | num_classes |
+--------------+--------------------+------------------+-------------+
| TR           | 1100               | 1215             |          35 |
| TR           | 930                | 1045             |          32 |
| MWF          | 1100               | 1150             |          31 |
| MWF          | 900                | 950              |          28 |
| MW           | 1400               | 1515             |          26 |
+--------------+--------------------+------------------+-------------+

    The result of the rewritten query (use the CONCAT function):

    +---------------+-------------+
| time_slot     | num_classes |
+---------------+-------------+
| TR 1100-1215  |          35 |
| TR 930-1045   |          32 |
| MWF 1100-1150 |          31 |
| MWF 900-950   |          28 |
| MW 1400-1515  |          26 |
+---------------+-------------+

  5. List each professor who teaches a HIST class, along with all the classes that prof teaches. 
    For this, you'll need the GROUP_CONCAT function. 

    +--------------+----------------------+
| professor    | list_of_HIST_classes |
+--------------+----------------------+
| A Donaldson  | 232 and 318          |
| A Maginn     | 328                  |
| A Mansker    | 271 and 352 and 380  |
| C Cooper     | 128 and 211 and 212  |
| C McEvoy     | 224 and 381          |
| J Willis     | 322 and 327          |
| K Whitmer    | 122 and 335          |
| M Brookfield | 133 and 202          |
| M Mitchell   | 206 and 234          |
| N Roberts    | 297 and 319          |
| R Levine     | 215 and 283 and 421  |
| S Ridyard    | 111 and 302 and 304  |
| S Weygandt   | 383                  |
| T Momon      | 343                  |
+--------------+----------------------+

Deliverables