Course Catalogue Summary by Department and Level
Course codes in the university catalogue look like CSE-301: the department's short name, a hyphen, and a three-digit number whose first digit is the level…
- Difficulty: Medium
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #139
Problem statement
Course codes in the university catalogue look like CSE-301: the department's short name, a hyphen, and a three-digit number whose first digit is the level (1 for first-year courses, 3 for third-year, …). The curriculum committee wants a count of courses and credits at each level of each department.
Return department (the part before the hyphen), level (the first digit of the number, times 100 — so 301 gives 300), courses and total_credits. Order the rows by department, then by level.
Tables
Table: CatalogueCourse
| Column | Type |
|---|---|
| course_code | varchar |
| title | varchar |
| credits | int |
Primary key: course_code.
Every code has exactly one hyphen followed by three digits; department names have 2–4 capital letters.
Examples
Example 1
CatalogueCourse
| course_code | title | credits |
|---|---|---|
| CSE-101 | Programming in C | 4 |
| CSE-301 | Operating Systems | 4 |
| CSE-305 | Compiler Design | 3 |
| ME-105 | Engineering Drawing | 3 |
| ME-210 | Thermodynamics | 4 |
| MA-101 | Calculus | 4 |
| ECE-301 | Digital Signal Processing | 4 |
Output
| department | level | courses | total_credits |
|---|---|---|---|
| CSE | 100 | 1 | 4 |
| CSE | 300 | 2 | 7 |
| ECE | 300 | 1 | 4 |
| MA | 100 | 1 | 4 |
| ME | 100 | 1 | 3 |
| ME | 200 | 1 | 4 |
How to solve Course Catalogue Summary by Department and Level
Each course code packs two facts. Split at the hyphen: SUBSTRING_INDEX(course_code, '-', 1) returns everything before the first hyphen — the department — and SUBSTRING_INDEX(course_code, '-', -1) everything after the last one — the three-digit number. Because the department has a variable length (ME, CSE), a fixed LEFT(code, 3) would not work.
The level is the number's hundreds digit. Take its first character, cast it to a number and multiply by 100, or cast the whole number and compute FLOOR(n / 100) * 100; both give 300 for 301 and 305. Casting matters for the sort — as text the levels would still sort correctly here, but they would be compared as strings, and CAST(… AS SIGNED) makes the column a proper integer.
MySQL lets you group by the select-list aliases, so GROUP BY department, level groups on the derived values. Then COUNT(*) and SUM(credits) summarise each group. One scan of the catalogue.
Reference solution (MySQL)
SELECT SUBSTRING_INDEX(course_code, '-', 1) AS department,
CAST(LEFT(SUBSTRING_INDEX(course_code, '-', -1), 1) AS SIGNED) * 100 AS level,
COUNT(*) AS courses,
SUM(credits) AS total_credits
FROM CatalogueCourse
GROUP BY department, level
ORDER BY department, levelAnother way
SELECT department, level, COUNT(*) AS courses, SUM(credits) AS total_credits FROM (SELECT LEFT(course_code, LOCATE('-', course_code) - 1) AS department, FLOOR(CAST(SUBSTRING(course_code, LOCATE('-', course_code) + 1) AS SIGNED) / 100) * 100 AS level, credits FROM CatalogueCourse) t GROUP BY department, level ORDER BY department, level← Late Coursework Uploads and Average Hours Late per Course · Backlogs Cleared on a Later Attempt →