gsd-questions/queries/task3-ddl.sql

CREATE TABLE STUDENT (
  STUDENT_ID INT PRIMARY KEY,
  LASTNAME VARCHAR(40) NOT NULL,
  FIRSTNAME VARCHAR(40) NOT NULL,
  GENDER VARCHAR(1) NOT NULL,
  CONSTRAINT CHK_STUDENT_ID
  CHECK (STUDENT_ID>=100000 AND STUDENT_ID<=999999),
  CONSTRAINT CHK_GENDER
  CHECK (GENDER='m' OR GENDER='f')
);
CREATE TABLE EXAM (
  STUDENT_ID INT,
  COURSE VARCHAR(40),
  GRADE DECIMAL(2,1),
  ATTEMPT INT,
  CONSTRAINT PK PRIMARY KEY(STUDENT_ID, ATTEMPT, COURSE),
  CONSTRAINT CHK_ATTEMPT CHECK (ATTEMPT IN (1,2,3)),
  CONSTRAINT CHK_GRADE CHECK (
    GRADE IN (1.0,1.3,1.7,2.0,2.3,2.7,3.0,3.3,3.7,4.0,5.0)
  ),
  CONSTRAINT FK_STUDENT_ID FOREIGN KEY (STUDENT_ID)
  REFERENCES STUDENT(STUDENT_ID) ON DELETE CASCADE
);