HI520 Unit 5 Basic Query Set Example

Seven Single-Table Queries, Each Preceded by the Population It Claims to Return: A Unit 5 Basic Query Set on a Pediatric Asthma Database

[Student Name]

Purdue University Global

HI520: Database Design and SQL

Unit 5 Basic Query Set

[Instructor Name]

[Date]

Calder Bay Pediatrics and every row and count are synthetic; the sources cited are real.

Conventions

All queries run against the Unit 4 schema loaded with synthetic data: 1,640 patients, 4 sites, 23 providers and 9,812 encounters across 18 months. A fixed report date of June 30, 2026, is used wherever age or recency matters, so that rerunning a query next week returns the same rows. Every query names its columns instead of using an asterisk, so a change to the table does not silently change the output, and every query that implies an order includes ORDER BY. Each is followed by its row count from the synthetic data and two lines on what it includes and excludes. The queries are grouped by the kind of mistake they guard against rather than by difficulty. Equality and list filters come first because they rarely go wrong. Date filters come next because a boundary error returns plausible results that are quietly incomplete. Comparisons involving nulls close the set, because that is where a filter can look correct and still return the wrong children.

Equality and List Filters

Query 1. Population: every diagnosis code in the table that represents persistent asthma, mild, moderate or severe, listed by code. SELECT code, description FROM diagnosis_code WHERE code LIKE 'J45.3%' OR code LIKE 'J45.4%' OR code LIKE 'J45.5%' ORDER BY code; Rows returned: 12. Includes every persistent asthma code with any fifth character, such as exacerbation or status asthmaticus. Excludes intermittent asthma and unspecified asthma, which the practice's measure counts separately, following the national severity categories (National Asthma Education and Prevention Program [NAEPP], 2007). Query 2. Population: spirometry and peak flow orders whose status is canceled, newest first. SELECT order_id, encounter_id, test_code, order_time FROM test_order WHERE test_code IN ('SPIRO', 'PEAKFLOW') AND status = 'canceled' ORDER BY order_time DESC; Rows returned: 41. Includes both test types. Excludes orders in any other status; because status is constrained to four lowercase values, no variant spellings can slip through.

Date Logic

Query 3. Population: children who were at least 5 and no older than 11 on the report date, by last name. SELECT patient_id, mrn, last_name, first_name, birth_date FROM patient WHERE birth_date BETWEEN DATE '2014-07-01' AND DATE '2021-06-30' ORDER BY last_name, first_name; Rows returned: 806. Includes a child born on July 1, 2014, who turned 12 the day after the report date. Excludes a child born June 30, 2014, already 12 on the report date. Query 4. Population: encounters that began in the second quarter of 2026, April 1 through June 30, by start time. SELECT encounter_id, patient_id, site_id, start_time FROM encounter WHERE start_time >= TIMESTAMP '2026-04-01 00:00' AND start_time < TIMESTAMP '2026-07-01 00:00' ORDER BY start_time; Rows returned: 1,724. Includes encounters at any time on June 30. Excludes encounters beginning at midnight on July 1. A filter written as BETWEEN April 1 and June 30 on a timestamp would silently drop every encounter after midnight on June 30, which is why the quarter uses a half-open range.

Comparisons That Meet Nulls

Query 5. Population: encounters in the second quarter where the recorded asthma control score indicates asthma that is not well controlled, a score of 19 or lower. SELECT encounter_id, patient_id, control_score FROM encounter WHERE start_time >= TIMESTAMP '2026-04-01 00:00' AND start_time < TIMESTAMP '2026-07-01 00:00' AND control_score <= 19 ORDER BY control_score; Rows returned: 288. The cutoff of 19 follows the validation study for the childhood control instrument (Liu et al., 2007). Excludes encounters with no score, because a comparison with a null is never true; those are counted separately in query 6. Query 6. Population: second-quarter encounters at which no control score was recorded. SELECT encounter_id, patient_id, site_id, visit_type FROM encounter WHERE start_time >= TIMESTAMP '2026-04-01 00:00' AND start_time < TIMESTAMP '2026-07-01 00:00' AND control_score IS NULL ORDER BY site_id; Rows returned: 611. Writing control_score = NULL instead would return zero rows, because nothing equals null, not even another null. Query 7. Population: patients whose primary language is missing or recorded as unknown, by medical record number. SELECT patient_id, mrn, primary_language FROM patient WHERE primary_language IS NULL OR primary_language = 'Unknown' ORDER BY mrn; Rows returned: 97. Includes both forms of missing data. A filter on primary_language = 'Unknown' alone would miss the 63 rows that are simply null.

What the Set Shows

Queries 5 and 6 together account for every second-quarter encounter with or without a low score; neither alone describes the population accurately. Treating missing values as a distinct category rather than as zero or as normal is a basic rule of handling clinical data, and the relational model treats null as unknown rather than as a value (Codd, 1970). The practice's quality team now knows that 611 of 1,724 second-quarter encounters, about 35 percent, have no control score at all, a finding that matters more than the 288 low scores among those that do.

References

Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387. https://doi.org/10.1145/362384.362685

Liu, A. H., Zeiger, R., Sorkness, C., Mahr, T., Ostrom, N., Burgess, S., Rosenzweig, J. C., & Manjunath, R. (2007). Development and cross-sectional validation of the Childhood Asthma Control Test. Journal of Allergy and Clinical Immunology, 119(4), 817-825. https://doi.org/10.1016/j.jaci.2006.12.662

National Asthma Education and Prevention Program. (2007). Expert panel report 3: Guidelines for the diagnosis and management of asthma. National Heart, Lung, and Blood Institute.