dbdbexercises
Practice Free
← Syllabus|
Medium

15. Operating Room Surgical Procedure Staffing Matrix

MediumJoinsPostgreSQL 16PRO
Table: operating_suites
+-------------+-------------+
| Column Name | Type        |
+-------------+-------------+
| suite_id    | int         |
| suite_name  | varchar(50) |
+-------------+-------------+

suite_id is the unique key for this table. Each row contains record details for this enterprise dataset.

Table: surgeons
+--------------+--------------+
| Column Name  | Type         |
+--------------+--------------+
| surgeon_id   | int          |
| surgeon_name | varchar(100) |
| specialty    | varchar(50)  |
+--------------+--------------+

surgeon_id is the unique key for this table. Each row contains record details for this enterprise dataset.

Table: surgical_cases
+-----------------+-------------+
| Column Name     | Type        |
+-----------------+-------------+
| case_id         | varchar(16) |
| suite_id        | int         |
| surgeon_id      | int         |
| scheduled_hours | numeric(4   |
+-----------------+-------------+

case_id is the unique key for this table. Each row contains record details for this enterprise dataset.

Hospital operating room directors track scheduled surgical operations by linking cases to the assigned surgical theater room and the lead surgeon.

Join `surgical_cases` with `operating_suites` on `suite_id` and with `surgeons` on `surgeon_id`. Return: 1. `c.case_id` 2. `s.surgeon_name` 3. `s.specialty` 4. `o.suite_name` 5. `c.scheduled_hours` Order results by `c.scheduled_hours` descending, then by `c.case_id` ascending.

Return the result table ordered as specified. The result format is in the following example.

Example 1:
Input:
Preparing sample rows...
Output:
Calculating benchmark target...
Explanation:

Operating room surgical schedule cross-referencing room and attending surgeon.

Constraints:
  • Query must utilize clause(s): INNER JOINONORDER BY
  • Result order matters (tie-breakers and sort order must be strictly preserved).
  • Engine: PostgreSQL 16 standard syntax (in-browser WASM).
query.sql|PostgreSQL 16 WASM
Loading SQL editor...
You must run your code first

Write your SQL query in the editor and click Run or Submit to test against in-browser PostgreSQL 16 WASM.

About the “Operating Room Surgical Procedure Staffing Matrix” SQL Problem

Business Scenario

Hospital operating room directors track scheduled surgical operations by linking cases to the assigned surgical theater room and the lead surgeon.

Expected Output & Criteria

Operating room surgical schedule cross-referencing room and attending surgeon.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM
CREATE TABLE operating_suites (
        suite_id INT PRIMARY KEY,
        suite_name VARCHAR(50) NOT NULL
      );

      CREATE TABLE surgeons (
        surgeon_id INT PRIMARY KEY,
        surgeon_name VARCHAR(100) NOT NULL,
        specialty VARCHAR(50) NOT NULL
      );

      CREATE TABLE surgical_cases (
        case_id VARCHAR(16) PRIMARY KEY,
        suite_id INT REFERENCES operating_suites(suite_id),
        surgeon_id INT REFERENCES surgeons(surgeon_id),
        scheduled_hours NUMERIC(4, 1) NOT NULL
      );

Target SQL Clauses & Operators

INNER JOINONORDER BY

Want more Joins practice?

Explore the full syllabus of 16 exercises and syntax guides.

View Joins Track