--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 );