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

このテストは、学校の生徒の受講記録や成績データを使用し、結合、集計、サブクエリ、条件付き取得といった応用的な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)
);

テーブル定義: Teachers (教員情報 - 初級編より再掲)

CREATE TABLE Teachers (
    TeacherID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Specialty NVARCHAR(100), -- 専門分野
    HireDate DATE,
    Enabled BIT DEFAULT 1,
    InsertDate DATETIME DEFAULT GETDATE(),
    UpdateDate DATETIME DEFAULT GETDATE(),
    DeleteDate DATETIME,
    UpdateUser NVARCHAR(50)
);

テーブル定義: CourseAssignments (教員と科目の担当割り当て)

CREATE TABLE CourseAssignments (
    AssignmentID INT PRIMARY KEY IDENTITY(1,1),
    TeacherID INT NOT NULL,
    CourseID INT NOT NULL,
    AssignmentDate DATE,
    Semester NVARCHAR(20), -- 'Spring', 'Fall'
    Enabled BIT DEFAULT 1,
    InsertDate DATETIME DEFAULT GETDATE(),
    UpdateDate DATETIME DEFAULT GETDATE(),
    DeleteDate DATETIME,
    UpdateUser NVARCHAR(50),
    FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
問題 2.1: 各学年（Grade）ごとの有効な生徒数と、その学年の平均生年月日を計算するSQL文を記述してください。

解答欄:

問題 2.2: Students、Enrollments、Coursesの3つのテーブルを結合し、各生徒のFirstName、LastName、受講しているCourseName、およびその科目のGradeScoreを取得するSQL文を記述してください。有効な生徒の有効な受講記録のみを対象とします。

解答欄:

問題 2.3: 2024年「Fall」学期に登録され、かつ平均点（GradeScore）が80点以上になる有効な科目のCourseNameと平均点を取得するSQL文を記述してください。

解答欄:

問題 2.4: どの有効な科目にも割り当てられていない有効な教員（Teachersテーブル）のFirstNameとLastNameを取得するSQL文を記述してください。サブクエリを使用してください。

解答欄:

問題 2.5: 各教員（TeacherID）が担当した科目の総単位数（Creditsの合計）を計算し、総単位数が専門分野（Specialty）が同じ教員グループの平均総単位数を超える教員のFirstName、LastName、専門分野、その教員の総単位数、およびその専門分野の平均総単位数を取得するSQL文を記述してください。有効な教員と科目割り当てのみを対象とします。

解答欄:

目次へ戻る