サンプルテーブル生成スクリプト

以下のスクリプトを実行してテーブルを生成してください。

同じ名前の既存のテーブルがある場合、そのテーブルが削除されてしまうので、ご注意ください。

サンプルテーブル生成スクリプト


IF OBJECT_ID('dbo.TestResultSummary', 'U') IS NOT NULL 
  DROP TABLE dbo.TestResultSummary;

IF OBJECT_ID('dbo.TestResult', 'U') IS NOT NULL 
  DROP TABLE dbo.TestResult; 

IF OBJECT_ID('dbo.Test', 'U') IS NOT NULL 
  DROP TABLE dbo.Test; 

IF OBJECT_ID('dbo.StudentGPA', 'U') IS NOT NULL 
  DROP TABLE dbo.StudentGPA;
    
IF OBJECT_ID('dbo.Student', 'U') IS NOT NULL 
  DROP TABLE dbo.Student; 

-----------------------------------------------

CREATE TABLE Student (
   StudentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
   FirstName VARCHAR(50) NULL,
   LastName VARCHAR(50) NULL,
   Birthday DATE NULL,
   Gender CHAR(1) NULL
);

INSERT INTO Student
  (FirstName, LastName, Birthday, Gender)
 VALUES
  ('Taro', 'Yamada', '1980-02-15', 'M'),
  ('Hanako', 'Tanaka' ,'1979-12-30', 'F'),
  ('Yuko', 'Suzuki', '1979-07-07', 'F'),
  ('Takao', 'Sato', '1980-03-12', 'M'),
  ('Hiroki', 'Takagi', '1979-04-05', 'M'),
  ('Yuka', 'Kimura', '1981-03-27', 'F');

-----------------------------------------------

CREATE TABLE Test (
    TestID      INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    TestNameEn  NVARCHAR(50) NOT NULL,
    TestNameJp  NVARCHAR(50) NOT NULL,
);

INSERT INTO Test 
    ( TestNameEn, TestNameJp )
VALUES  
    ('Math 1', N'数学１'),
    ('English 1', N'英語１'),
    ('History 1', N'歴史１');

-----------------------------------------------

CREATE TABLE TestResult (
    TestResultID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    TestID INT NULL REFERENCES Test (TestID),
    StudentID INT NULL REFERENCES Student (StudentID), 
    Score INT NULL
);

INSERT INTO TestResult
    ( TestID, StudentID, Score )
VALUES
    ( 1, 1, 85),
    ( 1, 2, 60),
    ( 1, 4, 98),
    ( 1, 5, 73),
    ( 2, 1, 77),
    ( 2, NULL, NULL),
    ( 2, 3, 92),
    ( 2, 4, 81);

-----------------------------------------------

CREATE TABLE TestResultSummary (
    TestResultSummaryID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    TestID INT NULL REFERENCES Test (TestID),
    MinScore DECIMAL(3,0) NULL,
    MaxScore decimal(3,0) NULL,
    AvgScore DECIMAL(5,2) NULL
);