BU 453 Unit 5 single-table queries example

Six Questions, Six Statements, the Rows That Came Back and Why They Are Right: Single-Table Queries Against a Composite Boise Music School's Database

Student Name

Business, Herzing University

BU 453: Database Management

Instructor Name

March 11, 2026

Six Questions, Six Statements, the Rows That Came Back and Why They Are Right: Single-Table Queries Against a Composite Boise Music School's Database

How Each Item Is Presented

These six queries run against the test database built for Harmony Hill Music School, the invented Boise lesson school in this course, in PostgreSQL 16. Each touches one table only. Every item follows the same form: the question restated in one line as the office manager asked it, the statement that answers it, the rows that actually came back, and a short argument for why those are the right rows. The last part matters most. A statement can run without error and still answer a different question, for example by returning all instruments when the manager asked only about those needing repair. Beaulieu (2020) stresses reading a query's result against the question it was meant to answer, not merely checking that it ran.

The test data hold 40 students, 14 teachers, 52 spring enrollments, 20 instruments, 12 rentals and 31 invoices, loaded in the schema unit.

Query 1: Instruments Needing Repair

Question: which instruments are marked as needing repair, so the repair shop can be booked? Statement: SELECT serial_number, type, make FROM instrument WHERE condition = 'needs repair' ORDER BY type, serial_number; Result: three rows, CL-1044 clarinet, VN-2210 violin and VN-2215 violin. Why these are right: the condition column takes values from a fixed list, so the exact comparison cannot miss a variant spelling; a direct check of the table showed exactly three instruments with that condition.

Query 2: Teachers Above a Pay Rate

Question: which teachers earn more than $40 an hour, highest first, for the budget review? Statement: SELECT first_name, last_name, hourly_rate FROM teacher WHERE hourly_rate > 40 ORDER BY hourly_rate DESC; Result: four rows, with rates of $52, $48, $45 and $42. Why these are right: the question says more than $40, so the comparison is strictly greater; two teachers paid exactly $40 are correctly excluded. Had the manager said $40 or more, the statement would use greater than or equal to, and the answer would be six rows. Restating the question before writing the statement is what surfaces that distinction.

Query 3: Lessons by Weekday

Question: how many spring lessons fall on each weekday, to plan front-desk staffing? Statement: SELECT weekday, COUNT(*) AS lessons FROM enrollment WHERE term_id = 7 GROUP BY weekday ORDER BY weekday;

Why these are right: the counts add to 52, the number of spring enrollments, so no enrollment was lost or counted twice. Sunday does not appear because the school is closed, not because the query dropped it; a check that no enrollment has weekday 7 confirmed this. Filtering on the term's identifier, 7, rather than on dates keeps the answer tied to the term as the school defines it.

Query 4: Overdue Invoices

Question: which invoices issued before March 1 are still unpaid? Statement: SELECT invoice_id, account_id, issued_date, amount FROM invoice WHERE status = 'open' AND issued_date < DATE '2026-03-01' ORDER BY issued_date; Result: five rows totaling $1,284. Why these are right: both conditions must hold, so the statement joins them with AND; using OR would have returned every open invoice plus every old one, 27 rows. Invoices issued on March 1 itself are excluded because the question says before March 1.

Query 5: Youngest Students

Question: which students will be under ten on the first day of the spring term, January 20, 2026, for the beginners' recital? Statement: SELECT first_name, last_name, birth_date FROM student WHERE birth_date > DATE '2016-01-20' ORDER BY birth_date; Result: nine rows. Why these are right: a child born after January 20, 2016 has not reached ten by January 20, 2026; a child born exactly on that date turns ten that day and is correctly excluded. The comparison uses the date type, so it is not confused by text formats.

Query 6: Rentals Open More Than a Year

Question: which rentals have been open for more than twelve months, so the families can be offered a purchase? Statement: SELECT rental_id, serial_number, student_id, start_date FROM rental WHERE end_date IS NULL AND start_date < DATE '2025-03-11' ORDER BY start_date; Result: two rows, rentals started in October 2024 and January 2025. Why these are right: an open rental has no end date, which the database stores as null, and null must be tested with IS NULL. A first draft wrote end_date = NULL, which ran without error and returned no rows, because a comparison with null is never true. The PostgreSQL documentation warns of this behavior explicitly (PostgreSQL Global Development Group, 2024), and it is the clearest example in this set of a statement that runs, looks reasonable and answers nothing.

Formatting and Alternatives

Every statement is written with keywords in capitals and one clause per line in the query file, which makes each easy to check against its question. Formatting does not change the rows returned, but it changes how quickly a reviewer can see that a condition is missing. Two statements could be written other ways that return the same rows. Query 2 could use a subquery, and Query 3 could count a specific column instead of rows; where alternatives return identical rows, the simpler one is kept. Hoffer et al. (2019) recommend testing a query against data whose correct answer is known in advance, which is why each result here was checked against a direct count. Harmony Hill and its test data are composites for this course.

All six queries were run against the test database only, never against the school's live records, even though some of the questions came from the office manager's real needs. Reading queries do not change data, but running practice statements against a live system still risks slowing it for the staff using it and exposes families' details to someone who does not need them. The test data were built to contain the cases each query must handle: teachers paid exactly $40, a child born exactly ten years before the term began and an open rental with no end date. Designing the test data around the edges of each question is what made the justifications checkable.

References

Beaulieu, A. (2020). Learning SQL: Generate, manipulate, and retrieve data (3rd ed.). O'Reilly Media.

Hoffer, J. A., Ramesh, V., & Topi, H. (2019). Modern database management (13th ed.). Pearson.

PostgreSQL Global Development Group. (2024). PostgreSQL 16 documentation. https://www.postgresql.org/docs/16/