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),
    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)
);

テーブル定義: Medications (薬品情報)

CREATE TABLE Medications (
    MedicationID INT PRIMARY KEY IDENTITY(1,1),
    MedicationName NVARCHAR(100) NOT NULL,
    Manufacturer NVARCHAR(100),
    PricePerUnit DECIMAL(10, 2),
    StockQuantity INT,
    Enabled BIT DEFAULT 1,
    InsertDate DATETIME DEFAULT GETDATE(),
    UpdateDate DATETIME DEFAULT GETDATE(),
    DeleteDate DATETIME,
    UpdateUser NVARCHAR(50)
);

テーブル定義: Prescriptions (処方箋)

CREATE TABLE Prescriptions (
    PrescriptionID INT PRIMARY KEY IDENTITY(1,1),
    RecordID INT NOT NULL,
    MedicationID INT NOT NULL,
    Dosage NVARCHAR(50), -- 用量
    Quantity INT NOT NULL, -- 処方量
    PrescriptionDate 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),
    FOREIGN KEY (MedicationID) REFERENCES Medications(MedicationID)
);
問題 2.1: 各医師（DoctorID, FirstName, LastName）が担当した有効な診察記録の件数を専門分野（Specialty）ごとに取得し、診察件数が多い専門分野順に表示するSQL文を記述してください。

解答欄:

問題 2.2: Patients、MedicalRecords、Doctorsの3つのテーブルを結合し、'インフルエンザ'と診断された患者のFirstName、LastName、診察を行った医師のFirstName、LastName、および診察日（ExaminationDate）を取得するSQL文を記述してください。有効な診察記録のみを対象とします。

解答欄:

問題 2.3: 2025年中に少なくとも1回の診察記録があり、かつ50歳以上の有効な患者のPatientID、FirstName、LastNameを取得するSQL文を記述してください。サブクエリを使用して、条件を満たす患者を抽出してください。

解答欄:

問題 2.4: 各薬品（MedicationName）ごとに、処方された総数量（Quantityの合計）と、その薬品の在庫数量（StockQuantity）の合計を取得するSQL文を記述してください。処方されていない薬品も表示し、その場合は処方量を0と表示してください。有効な薬品のみを対象とします。

解答欄:

問題 2.5: Medicationsテーブルの在庫数量（StockQuantity）を更新します。各薬品について、その薬品の総処方量（PrescriptionsテーブルのQuantity合計）が現在の在庫数量の半分を超えている場合に、在庫数量を**2倍**にするSQL文を記述してください。（UpdateUserも適切に設定してください）。有効な薬品のみを対象とします。

解答欄:

目次へ戻る