SQLテスト - 上級編 (学校データベース)

このテストは、生徒の成績履歴や欠席情報など、より詳細なデータを使用し、ウィンドウ関数、共通テーブル式（CTE）、高度なデータ更新、複雑な条件でのデータ取得といった実践的なSQLスキルを測定することを目的としています。

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

問題

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

テーブル定義: Students (生徒情報 - 再掲)

CREATE TABLE Students (
    StudentID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    DateOfBirth DATE,
    EnrollmentDate DATE,
    Grade INT, -- 学年
    Enabled BIT DEFAULT 1,
    InsertDate DATETIME DEFAULT GETDATE(),
    UpdateDate DATETIME DEFAULT GETDATE(),
    DeleteDate DATETIME,
    UpdateUser NVARCHAR(50)
);

テーブル定義: Courses (科目情報 - 再掲)

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY IDENTITY(1,1),
    CourseName NVARCHAR(100) NOT NULL,
    Credits INT, -- 単位数
    Department NVARCHAR(50), -- 所属学科
    Enabled BIT DEFAULT 1,
    InsertDate DATETIME DEFAULT GETDATE(),
    UpdateDate DATETIME DEFAULT GETDATE(),
    DeleteDate DATETIME,
    UpdateUser NVARCHAR(50)
);

テーブル定義: Enrollments (受講登録情報 - 再掲)

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY IDENTITY(1,1),
    StudentID INT NOT NULL,
    CourseID INT NOT NULL,
    EnrollmentDate DATE,
    GradeScore INT, -- 点数 (0-100)
    Semester NVARCHAR(20), -- 'Spring', 'Fall'
    Enabled BIT DEFAULT 1,
    InsertDate DATETIME DEFAULT GETDATE(),
    UpdateDate DATETIME DEFAULT GETDATE(),
    DeleteDate DATETIME,
    UpdateUser NVARCHAR(50),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

テーブル定義: Attendance (出席情報)

CREATE TABLE Attendance (
    AttendanceID INT PRIMARY KEY IDENTITY(1,1),
    StudentID INT NOT NULL,
    CourseID INT NOT NULL,
    AttendanceDate DATE NOT NULL,
    Status NVARCHAR(20), -- 'Present', 'Absent', 'Late'
    Reason NVARCHAR(255), -- 欠席理由など
    Enabled BIT DEFAULT 1,
    InsertDate DATETIME DEFAULT GETDATE(),
    UpdateDate DATETIME DEFAULT GETDATE(),
    DeleteDate DATETIME,
    UpdateUser NVARCHAR(50),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
問題 3.1 (ウィンドウ関数 - LAG/LEAD): 各生徒について、受講した科目（CourseName）ごとに、その科目の前の学期（`Semester`と`EnrollmentDate`で順序付け）に取得したGradeScoreを隣接する行として表示するSQL文を記述してください。最初の学期の科目は前のスコアをNULLとします。有効な受講記録のみを対象とします。

解答欄:

問題 3.2 (CTEと条件付き更新): 共通テーブル式（CTE）を使用して、**合計欠席回数**が5回を超え、かつ**全ての科目の平均点**が70点未満の有効な生徒について、その生徒のGradeを1段階下げるSQL文を記述してください。`UpdateUser`も適切に設定してください。（学年が1の場合はそれ以上下げない）

解答欄:

問題 3.3 (複数条件のNOT EXISTS): 以下の条件を全て満たす有効な科目名（CourseName）を取得するSQL文を記述してください。
- 2024年に入ってから受講登録が1件もない。
- どの生徒からも「Absent」（欠席）と記録されたことがない。
- 担当教員が一人も割り当てられていない。

解答欄:

問題 3.4 (PIVOT/CASEと集計 - 多次元集計の簡易版): 各生徒（StudentID, FirstName, LastName）について、科目別（`国語I`, `数学A`, `英語コミュニケーション`, `物理基礎`）の最終成績（GradeScore）を列として表示するSQL文を記述してください。該当科目を未受講の場合はNULLと表示し、有効な受講記録のみを対象とします。`PIVOT`句または`CASE`式と集計関数を組み合わせて使用してください。

解答欄:

問題 3.5 (ウィンドウ関数 - NTILE): 全ての有効な生徒を、学年（Grade）ごとに成績（GradeScore）の高い順に3つのグループ（1:上位、2:中位、3:下位）に分割し、各生徒のFirstName、LastName、Grade、GradeScore、およびそのグループ（タイル番号）を表示するSQL文を記述してください。特定の科目に限定せず、受講した全ての科目の平均点でグループ分けしてください。

解答欄:

目次へ戻る