SQLテスト - 上級編 (病院データベース)

このテストは、患者の治療履歴、過去の診察回数、特定の条件に基づく患者抽出、複雑な集計や更新など、実践的なSQLスキルを測定することを目的としています。

注: 各問題の解答は、SQL Serverの構文に基づいて記述してください。

問題

以下のテーブル定義とデータ（テストデータで投入されるもの）を考慮して解答してください。

テーブル定義: Patients (患者情報 - 再掲)

CREATE TABLE Patients (
    PatientID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    DateOfBirth DATE,
    Gender NVARCHAR(10),
    ContactPhone NVARCHAR(20),
    LastVisitDate DATETIME, -- 最終診察日 (新規追加)
    Enabled BIT DEFAULT 1,
    InsertDate DATETIME DEFAULT GETDATE(),
    UpdateDate DATETIME DEFAULT GETDATE(),
    DeleteDate DATETIME,
    UpdateUser NVARCHAR(50)
);

テーブル定義: Doctors (医師情報 - 再掲)

CREATE TABLE Doctors (
    DoctorID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Specialty NVARCHAR(100),
    LicenseNumber NVARCHAR(50) UNIQUE,
    Enabled BIT DEFAULT 1,
    InsertDate DATETIME DEFAULT GETDATE(),
    UpdateDate DATETIME DEFAULT GETDATE(),
    DeleteDate DATETIME,
    UpdateUser NVARCHAR(50)
);

テーブル定義: MedicalRecords (診察記録 - 再掲)

CREATE TABLE MedicalRecords (
    RecordID INT PRIMARY KEY IDENTITY(1,1),
    PatientID INT NOT NULL,
    DoctorID INT NOT NULL,
    ExaminationDate DATETIME NOT NULL,
    Diagnosis NVARCHAR(255),
    TreatmentNotes NVARCHAR(MAX),
    Enabled BIT DEFAULT 1,
    InsertDate DATETIME DEFAULT GETDATE(),
    UpdateDate DATETIME DEFAULT GETDATE(),
    DeleteDate DATETIME,
    UpdateUser NVARCHAR(50),
    FOREIGN KEY (PatientID) REFERENCES Patients(PatientID),
    FOREIGN KEY (DoctorID) REFERENCES Doctors(DoctorID)
);

テーブル定義: LabResults (検査結果)

CREATE TABLE LabResults (
    ResultID INT PRIMARY KEY IDENTITY(1,1),
    RecordID INT NOT NULL,
    TestName NVARCHAR(100) NOT NULL,
    ResultValue NVARCHAR(255), -- 数値、文字列、範囲など
    ResultUnit NVARCHAR(50),
    ResultDate DATETIME NOT NULL,
    Enabled BIT DEFAULT 1,
    InsertDate DATETIME DEFAULT GETDATE(),
    UpdateDate DATETIME DEFAULT GETDATE(),
    DeleteDate DATETIME,
    UpdateUser NVARCHAR(50),
    FOREIGN KEY (RecordID) REFERENCES MedicalRecords(RecordID)
);

テーブル定義: HospitalRooms (病室情報)

CREATE TABLE HospitalRooms (
    RoomID INT PRIMARY KEY IDENTITY(1,1),
    RoomNumber NVARCHAR(20) NOT NULL UNIQUE,
    RoomType NVARCHAR(50), -- 'Private', 'Shared'
    Capacity INT,
    CurrentOccupancy INT,
    Enabled BIT DEFAULT 1,
    InsertDate DATETIME DEFAULT GETDATE(),
    UpdateDate DATETIME DEFAULT GETDATE(),
    DeleteDate DATETIME,
    UpdateUser NVARCHAR(50)
);
問題 3.1 (CTEとウィンドウ関数 - 連続する訪問): 共通テーブル式（CTE）とウィンドウ関数（LEAD/LAGまたはROW_NUMBERと日付関数）を使用して、同じ患者が30日以内に連続して診察を受けた記録のペア（最初の診察日と次の診察日）を全て取得するSQL文を記述してください。有効な診察記録のみを対象とします。

解答欄:

問題 3.2 (UPDATE FROMとサブクエリ): PatientsテーブルのLastVisitDateカラムを、各患者の最新の有効な診察日（MedicalRecords.ExaminationDateの最新値）で更新するSQL文を記述してください。診察記録がない患者はNULLのままにするか、変更しないようにしてください。UpdateUserも適切に設定してください。

解答欄:

問題 3.3 (条件付き集計とPIVOT/CASE): 各患者（PatientID, FirstName, LastName）について、特定の検査結果（`血糖値`, `コレステロール`, `血圧`）の最新のResultValueを列として表示するSQL文を記述してください。該当する検査結果がない場合はNULLと表示してください。有効な検査結果のみを対象とし、`PIVOT`句または`CASE`式と集計関数を組み合わせて使用してください。（ResultValueは文字列型ですが、ここでは単純に表示するだけでよいとします。）

解答欄:

問題 3.4 (複雑なEXISTS/NOT EXISTS): 以下の条件を全て満たす有効な医師のFirstName、LastName、Specialtyを取得するSQL文を記述してください。
- 2025年中に少なくとも5人の異なる患者を診察した。
- 診察した患者の中に、性別が'Male'でかつ診断名が'骨折'である患者がいない。
- 専門分野が'内科'である。

解答欄:

問題 3.5 (トランザクションと複数テーブル更新): 新しい病室（`RoomNumber`: 'A-101', `RoomType`: 'Private', `Capacity`: 1, `CurrentOccupancy`: 0）をHospitalRoomsテーブルに登録します。同時に、この新しい病室タイプ（'Private'）の部屋の料金を20%値上げする更新をMedicalRecords（ただし、診察記録には病室情報は直接ないため、ここでは概念的な問題とします。例えば、`TreatmentNotes`に料金を記述していると仮定するか、新しい`Bills`テーブルのようなものがある場合。）**ここでは、概念的な課題として、`HospitalRooms`への挿入と、特定の条件を満たす既存の`Doctors`の給与を更新する、という2つの操作をトランザクションで行うことに変更します。**
新しい病室を登録し、**専門分野が'内科'の有効な医師全員の給与を5%増額する**一連の処理を、**トランザクション**を用いて記述してください。両方の操作が成功した場合のみ確定し、片方でも失敗した場合は全てロールバックされるようにしてください。

解答欄:

目次へ戻る