DB Exercises
0
← Exercises/
Joins•3 of 12
PrevNext
JoinsIntermediate

Inpatient Primary Attending Physician Roster

Scenario

During daily morning hospital rounds, clinical operations generates a roster cross-referencing active inpatients with their assigned attending physician and clinical specialty.

Task
Join the `hospital_patients` table with `physicians` on `attending_doctor_id = doctor_id`. Return: 1. `p.patient_mrn`: Medical record number 2. `p.patient_name` 3. `p.ward_room` 4. `d.doctor_name` 5. `d.specialty` Only include patients whose status is `'admitted'`. Order results by `p.ward_room` ascending, then by `p.patient_name` ascending.
Expected Output Format

Roster of admitted inpatients with attending physician details, sorted by ward room.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “Inpatient Primary Attending Physician Roster” SQL Problem

Business Scenario

During daily morning hospital rounds, clinical operations generates a roster cross-referencing active inpatients with their assigned attending physician and clinical specialty.

Expected Output & Criteria

Roster of admitted inpatients with attending physician details, sorted by ward room.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE physicians (
        doctor_id INT PRIMARY KEY,
        doctor_name VARCHAR(80) NOT NULL,
        specialty VARCHAR(50) NOT NULL
      );

      CREATE TABLE hospital_patients (
        patient_mrn VARCHAR(20) PRIMARY KEY,
        patient_name VARCHAR(100) NOT NULL,
        ward_room VARCHAR(20) NOT NULL,
        status VARCHAR(20) NOT NULL,
        attending_doctor_id INT REFERENCES physicians(doctor_id)
      );

Target SQL Clauses & Operators

JOINONORDER BY

Want more Joins practice?

Explore the full syllabus of 12 exercises, syntax guides, and interview pitfalls.

View Joins Track→