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,         -- 有効/無効フラグ
    InsertDate DATETIME DEFAULT GETDATE(), -- 登録日時
    UpdateDate DATETIME DEFAULT GETDATE(), -- 更新日時
    DeleteDate DATETIME,           -- 削除日時
    UpdateUser NVARCHAR(50)         -- 更新ユーザー
);

テーブル定義: Products (商品情報)

CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    ProductName NVARCHAR(100) NOT NULL,
    Price DECIMAL(10, 2) NOT NULL,
    StockQuantity INT,
    Enabled BIT DEFAULT 1,
    InsertDate DATETIME DEFAULT GETDATE(),
    UpdateDate DATETIME DEFAULT GETDATE(),
    DeleteDate DATETIME,
    UpdateUser NVARCHAR(50)
);

問題

問題 1.1: Employees テーブルに、以下の情報を新規登録するSQL文を記述してください。
名: '太郎', 姓: '山田', 部署: '開発部', 給与: 60000.00, 採用日: '2023-04-01'

解答欄:

問題 1.2: Employees テーブルから、全ての従業員のFirstNameとLastNameを取得するSQL文を記述してください。

解答欄:

問題 1.3: Employees テーブルで、Departmentが'開発部'の従業員のSalaryを70000.00に更新するSQL文を記述してください。（UpdateUserも適切に設定してください）

解答欄:

問題 1.4: Products テーブルから、Priceが5000以上の商品のProductNameとPriceを取得するSQL文を記述してください。

解答欄:

問題 1.5: Employees テーブルから、EmployeeIDが3の従業員を論理削除するSQL文を記述してください。（Enabledを0に更新し、DeleteDateとUpdateUserも適切に設定してください）

解答欄:

問題 1.6: Products テーブルに、以下の情報を持つ商品を複数件一括で登録するSQL文を記述してください。
商品名: 'ノートPC', 価格: 120000.00, 在庫数: 50
商品名: 'ワイヤレスマウス', 価格: 3500.00, 在庫数: 200

解答欄:

２．応用編

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

テーブル定義: Orders (注文情報)

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY IDENTITY(1,1),
    EmployeeID INT NOT NULL,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2),
    Enabled BIT DEFAULT 1,
    InsertDate DATETIME DEFAULT GETDATE(),
    UpdateDate DATETIME DEFAULT GETDATE(),
    DeleteDate DATETIME,
    UpdateUser NVARCHAR(50),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

テーブル定義: OrderDetails (注文詳細)

CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY IDENTITY(1,1),
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT,
    UnitPrice DECIMAL(10, 2),
    Enabled BIT DEFAULT 1,
    InsertDate DATETIME DEFAULT GETDATE(),
    UpdateDate DATETIME DEFAULT GETDATE(),
    DeleteDate DATETIME,
    UpdateUser NVARCHAR(50),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

問題

問題 2.1: Employees テーブルと Orders テーブルを結合し、各従業員のFirstName, LastNameと、その従業員が行った注文のOrderID, OrderDateを全て取得するSQL文を記述してください。注文がない従業員も表示されるようにしてください。

解答欄:

問題 2.2: 各部署（Department）ごとの従業員の平均給与（Salary）を計算するSQL文を記述してください。

解答欄:

問題 2.3: Orders テーブルと OrderDetails テーブルを結合し、各注文（OrderID）の総額（Quantity * UnitPriceの合計）を計算し、TotalAmountが10000を超える注文のみを抽出するSQL文を記述してください。

解答欄:

問題 2.4: Products テーブルから、在庫数量（StockQuantity）が最も少ない商品名（ProductName）を取得するSQL文を記述してください。（複数の商品が同じ最小在庫の場合も考慮してください）

解答欄:

問題 2.5: Employees テーブルに存在するが、Orders テーブルに一度も注文を登録していない従業員のFirstNameとLastNameを取得するSQL文を記述してください。

解答欄:

問題 2.6: 各商品の合計販売数（OrderDetailsのQuantityの合計）を計算し、販売数が多い順に商品名（ProductName）と合計販売数を表示するSQL文を記述してください。販売実績がない商品も表示し、その場合は合計販売数を0としてください。

解答欄:

問題 2.7: Employees テーブルで、給与（Salary）が部署内（Department）の平均給与より高い従業員のFirstName, LastName, Department, Salary、およびその部署の平均給与を取得するSQL文を記述してください。

解答欄:

問題 2.8: Products テーブルから、価格（Price）が全体の平均価格よりも高い商品名（ProductName）と価格を取得するSQL文を記述してください。

解答欄:

目次へ戻る