schema.sql 1.09 KB
Newer Older
Dr. Martin Goik's avatar
Dr. Martin Goik committed
1 2 3
DROP TABLE IF EXISTS Competition;
DROP TABLE IF EXISTS Athlete;

4 5 6 7 8 9 10 11 12 13 14
CREATE TABLE Athlete (
   id INT NOT NULL PRIMARY KEY
  ,fullName CHAR(20) NOT NULL
);

CREATE TABLE Competition (
   id INT PRIMARY KEY
  ,athlete INT NOT NULL REFERENCES Athlete(id)
  ,time NUMERIC(6,2) NOT NULL
  ,badStart BOOLEAN NOT NULL
);
Dr. Martin Goik's avatar
Dr. Martin Goik committed
15 16 17 18 19 20 21

INSERT INTO Athlete VALUES(1, 'Jesse Owens');
INSERT INTO Athlete VALUES(2, 'Tim Culver');
INSERT INTO Athlete VALUES(3, 'Sid Gascoine');
INSERT INTO Athlete VALUES(4, 'John Hooker');
INSERT INTO Athlete VALUES(5, 'Brad Duncan');

22 23 24 25 26 27 28 29
/* 100 metre race */
INSERT INTO Competition VALUES(1, 1, 10.23, FALSE); /* Jesse Owens */
INSERT INTO Competition VALUES(2, 2, 11.55, FALSE); /* Tim Culver */
INSERT INTO Competition VALUES(3, 3, 11.94, FALSE); /* Sid Gascoine */
INSERT INTO Competition VALUES(4, 4, 10.02, FALSE); /* John Hooker */
INSERT INTO Competition VALUES(5, 5, 10.84, FALSE); /* Brad Duncan */
INSERT INTO Competition VALUES(6, 2, 10.02, FALSE); /* Tim Culver */
INSERT INTO Competition VALUES(7, 3, 10.49, FALSE); /* Sid Gascoine */
Dr. Martin Goik's avatar
Dr. Martin Goik committed
30
INSERT INTO Competition VALUES(8, 2,  9.35, TRUE); /* Tim Culver */