SQLテスト - 中級編

このテストは、SQL ServerにおけるSQLの結合、集計、サブクエリなどの応用的な操作を測定することを目的としています。

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

問題

以下のテーブル定義をもとに、各問題に解答してください。

テーブル定義: Employees (従業員情報)

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Department NVARCHAR(50),
    Salary DECIMAL(10, 2),
    HireDate DATE,
    Enabled BIT DEFAULT 1,         -- 有効/無効フラグ (1:有効, 0:無効/論理削除)
    InsertDate DATETIME DEFAULT GETDATE(), -- 登録日時
    UpdateDate DATETIME DEFAULT GETDATE(), -- 更新日時
    DeleteDate DATETIME,           -- 削除日時 (論理削除用)
    UpdateUser NVARCHAR(50)         -- 更新ユーザー
);

テーブル定義: Projects (プロジェクト情報)

CREATE TABLE Projects (
    ProjectID INT PRIMARY KEY IDENTITY(1,1),
    ProjectName NVARCHAR(100) NOT NULL,
    StartDate DATE,
    EndDate DATE,
    Budget DECIMAL(18, 2),
    Status NVARCHAR(20), -- 'Active', 'Completed', 'On Hold' など
    Enabled BIT DEFAULT 1,
    InsertDate DATETIME DEFAULT GETDATE(),
    UpdateDate DATETIME DEFAULT GETDATE(),
    DeleteDate DATETIME,
    UpdateUser NVARCHAR(50)
);

テーブル定義: EmployeeProjects (従業員とプロジェクトの関連)

CREATE TABLE EmployeeProjects (
    EmployeeProjectID INT PRIMARY KEY IDENTITY(1,1),
    EmployeeID INT NOT NULL,
    ProjectID INT NOT NULL,
    Role NVARCHAR(50), -- 'Developer', 'Tester', 'Manager' など
    AssignedDate DATE,
    Enabled BIT DEFAULT 1,
    InsertDate DATETIME DEFAULT GETDATE(),
    UpdateDate DATETIME DEFAULT GETDATE(),
    DeleteDate DATETIME,
    UpdateUser NVARCHAR(50),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
    FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID)
);
問題 2.1: 各部署（Department）ごとの有効な従業員の数と平均給与を計算するSQL文を記述してください。

解答欄:

問題 2.2: EmployeesテーブルとEmployeeProjectsテーブルを結合し、各従業員のFirstName、LastName、およびその従業員が現在参加している有効なプロジェクトのProjectNameとRoleを取得するSQL文を記述してください。プロジェクトに参加していない従業員も表示されるようにしてください。

解答欄:

問題 2.3: 予算（Budget）が200000以上の、現在「Active」な有効なプロジェクトのリストを、プロジェクト名と予算降順で取得するSQL文を記述してください。

解答欄:

問題 2.4: 各プロジェクト（ProjectName）ごとに、参加している有効な従業員の数を計算し、従業員数が多い順に表示するSQL文を記述してください。従業員が一人も参加していないプロジェクトも表示し、その場合は従業員数を0としてください。

解答欄:

問題 2.5: Projectsテーブルから、有効なプロジェクトのうち、そのプロジェクトに参加している従業員（EmployeeProjectsテーブル参照）が全員「Developer」ロールであるプロジェクトのProjectNameを取得するSQL文を記述してください。（ただし、プロジェクトに誰も参加していない場合は対象外とする）

解答欄:

目次へ戻る