source: liacs/db/opdr2/create_db.sql@ 422

--name: Rick van der Zwet

--sid:  0433373

--file: ex2.sql

--vak/opdracht: Databases/ opdracht 2



DROP TABLE Sleep;

DROP TABLE Coaches;

DROP TABLE Coach;

DROP TABLE Sportsman;



CREATE TABLE Sportsman(

  Sid integer NOT NULL,

  Sname char(20),

  Sage integer,

  Scountry char(20),

  PRIMARY KEY( Sid )

);



CREATE TABLE Coach(

  Cid integer NOT NULL,

  Cname char(20),

  Cage integer,

  Ccountry char(20),

  PRIMARY KEY( Cid )

);



CREATE TABLE Coaches(

  Cid integer,

  Sid integer,

  FOREIGN KEY ( Cid ) REFERENCES Coach( Cid ),

  FOREIGN KEY ( Sid ) REFERENCES SportsMan( Sid )

);





CREATE TABLE Sleep(

  Sid integer,

  Branch char(20),

  Day date,

  NumHours integer,

  PRIMARY KEY ( Sid, Day ),

  FOREIGN KEY ( Sid ) REFERENCES SportsMan( Sid )

);

  

INSERT INTO Sportsman VALUES ( 1, 'Rick', 15, 'nl' );

INSERT INTO Sportsman VALUES ( 2, 'Simoneer', 25, 'nl' );

INSERT INTO Sportsman VALUES ( 3, 'Simone', 38, 'nl' );

INSERT INTO Sportsman VALUES ( 4, 'Stefan', 20, 'de' );

INSERT INTO Sportsman VALUES ( 5, 'Anton', 25, 'de' );

INSERT INTO Sportsman VALUES ( 6, 'Henrich', 38, 'de' );

INSERT INTO Sportsman VALUES ( 7, 'Huibert', 18, 'fr' );

INSERT INTO Sportsman VALUES ( 8, 'Francios', 20, 'fr' );

INSERT INTO Sportsman VALUES ( 9, 'Nancy', 30, 'fr' );

INSERT INTO Sportsman VALUES ( 10, 'Rosa', 38, 'fr' );



INSERT INTO Coach VALUES (1, 'coach 1', 10, 'nl' );

INSERT INTO Coach VALUES (2, 'coach 2', 20, 'nl' );

INSERT INTO Coach VALUES (3, 'coach 3', 30, 'de' );

INSERT INTO Coach VALUES (4, 'coach 4', 40, 'de' );

INSERT INTO Coach VALUES (5, 'coach 5', 50, 'de' );

INSERT INTO Coach VALUES (6, 'coach 6', 60, 'fr' );

INSERT INTO Coach VALUES (7, 'coach 7', 70, 'fr' );

INSERT INTO Coach VALUES (8, 'coach 8', 80, 'fr' );



INSERT INTO Coaches VALUES ( 1, 1 );

INSERT INTO Coaches VALUES ( 2, 2 );

INSERT INTO Coaches VALUES ( 2, 5 );

INSERT INTO Coaches VALUES ( 3, 3 );

INSERT INTO Coaches VALUES ( 3, 2 );

INSERT INTO Coaches VALUES ( 4, 4 );

INSERT INTO Coaches VALUES ( 5, 5 );

INSERT INTO Coaches VALUES ( 6, 6 );

INSERT INTO Coaches VALUES ( 7, 7 );

INSERT INTO Coaches VALUES ( 8, 8 );



INSERT INTO Sleep VALUES ( 1, 'judo', SYSDATE, 5 );

INSERT INTO Sleep VALUES ( 1, 'judo', SYSDATE+1, 9 );

INSERT INTO Sleep VALUES ( 1, 'judo', SYSDATE+2, 3 );

INSERT INTO Sleep VALUES ( 2, 'judo', SYSDATE, 3 );

INSERT INTO Sleep VALUES ( 3, 'judo', SYSDATE, 8 );

INSERT INTO Sleep VALUES ( 4, 'zwemmen', SYSDATE, 6 );