Course Catalogue Summary by Department and Level

Medium
139.

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 (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

ColumnType
course_codevarchar
titlevarchar
creditsint

Primary key: course_code.

Every code has exactly one hyphen followed by three digits; department names have 2–4 capital letters.

Example

The rows must come back in the order shown.

CatalogueCourse

course_codetitlecredits
CSE-101Programming in C4
CSE-301Operating Systems4
CSE-305Compiler Design3
ME-105Engineering Drawing3
ME-210Thermodynamics4
MA-101Calculus4
ECE-301Digital Signal Processing4

Output

departmentlevelcoursestotal_credits
CSE10014
CSE30027
ECE30014
MA10014
ME10013
ME20014
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
course_codevarchar
titlevarchar
creditsint

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_codetitlecredits
CSE-101Programming in C4
CSE-301Operating Systems4
CSE-305Compiler Design3
ME-105Engineering Drawing3
ME-210Thermodynamics4
MA-101Calculus4
ECE-301Digital Signal Processing4

Output

departmentlevelcoursestotal_credits
CSE10014
CSE30027
ECE30014
MA10014
ME10013
ME20014

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, level

Another 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 →