REMARK
/*
Database SPORTS CLUB per esercitazione n. 2 di BASIDATI.
Definizione delle tabelle del database SPORTS CLUB.
!!!!!!!!!!!
!ATTENZIONE: Nella tabella PENALTIES il campo DATE e' stato sostituito con CDATE.
!!!!!!!!!!!
*/

drop table penalties;
drop table games;
drop table teams;
drop table players;

CREATE TABLE PLAYERS
(PLAYERNO  SMALLINT NOT NULL,
 NAME   CHAR(15) NOT NULL,
 INITIALS  CHAR(3),
 YEAR_OF_BIRTH SMALLINT,
 SEX   CHAR(1),
 YEAR_JOINED  SMALLINT NOT NULL,
 STREET  CHAR(15) NOT NULL,
 HOUSENO  CHAR(4),
 POSTCODE  CHAR(6),
 TOWN   CHAR(10) NOT NULL,
 PHONENO  CHAR(10),
 LEAGUENO CHAR(4),
 PRIMARY KEY(PLAYERNO)
 );

CREATE TABLE TEAMS
 (TEAMNO  SMALLINT NOT NULL,
 PLAYERNO  SMALLINT NOT NULL,
 DIVISION  CHAR(6) NOT NULL,
 PRIMARY KEY(TEAMNO),
 FOREIGN KEY(PLAYERNO) REFERENCES PLAYERS(PLAYERNO)
 );

CREATE TABLE GAMES
 (TEAMNO  SMALLINT NOT NULL ,
 PLAYERNO  SMALLINT NOT NULL,
 WON   SMALLINT NOT NULL,
 LOST   SMALLINT NOT NULL,
 PRIMARY KEY (TEAMNO,PLAYERNO),
 FOREIGN KEY(PLAYERNO) REFERENCES PLAYERS(PLAYERNO),
 FOREIGN KEY(TEAMNO) REFERENCES TEAMS(TEAMNO)
 );

CREATE TABLE PENALTIES
 (PAYMENTNO INTEGER NOT NULL,
 PLAYERNO  SMALLINT NOT NULL,
 CDATE   DATE NOT NULL,
 AMOUNT  DECIMAL(7,2)   NOT NULL,
 PRIMARY KEY(PAYMENTNO),
 FOREIGN KEY(PLAYERNO) REFERENCES PLAYERS(PLAYERNO)
  );

REMARK

/*
Inserimento dati nelle tabelle.
*/

INSERT INTO PLAYERS (PLAYERNO,NAME,INITIALS,YEAR_OF_BIRTH,SEX,YEAR_JOINED,STREET,HOUSENO,POSTCODE,TOWN,PHONENO,LEAGUENO)
       VALUES (2,'Everett','R',1948,'M',1975,'Stoney Road','43','3575NH','Stratford','070-237893','2411');

INSERT INTO PLAYERS (PLAYERNO,NAME,INITIALS,YEAR_OF_BIRTH,SEX,YEAR_JOINED,STREET,HOUSENO,POSTCODE,TOWN,PHONENO,LEAGUENO)
       VALUES (6,'Parmenter','R',1964,'M',1977,'Haseltine Lane','80','1234KK','Stratford','070-476537','8467');

INSERT INTO PLAYERS (PLAYERNO,NAME,INITIALS,YEAR_OF_BIRTH,SEX,YEAR_JOINED,STREET,HOUSENO,POSTCODE,TOWN,PHONENO,LEAGUENO)
       VALUES (8,'Newcastle','B',1962,'F',1980,'Station Road','4','6584WO','Inglewood','070-476573','2983');

INSERT INTO PLAYERS (PLAYERNO,NAME,INITIALS,YEAR_OF_BIRTH,SEX,YEAR_JOINED,STREET,HOUSENO,POSTCODE,TOWN,PHONENO,LEAGUENO)
       VALUES (27,'Collins','DD',1964,'F',1983,'Long Drive','804','8457DK','Eltham','079-234857','2513');

INSERT INTO PLAYERS (PLAYERNO,NAME,INITIALS,YEAR_OF_BIRTH,SEX,YEAR_JOINED,STREET,HOUSENO,POSTCODE,TOWN,PHONENO,LEAGUENO)
       VALUES (44,'Baker','E',1963,'M',1980,'Lewis Street','23','4444LJ','Inglewood','070-368753','1124');

INSERT INTO PLAYERS (PLAYERNO,NAME,INITIALS,YEAR_OF_BIRTH,SEX,YEAR_JOINED,STREET,HOUSENO,POSTCODE,TOWN,PHONENO,LEAGUENO)
       VALUES (57,'Brown','M',1971,'M',1985,'Edgecombe Way','16','4377CB','Stratford','070-473458','6409');

INSERT INTO PLAYERS (PLAYERNO,NAME,INITIALS,YEAR_OF_BIRTH,SEX,YEAR_JOINED,STREET,HOUSENO,POSTCODE,TOWN,PHONENO,LEAGUENO)
       VALUES (83,'Hope','PK',1956,'M',1982,'Magdalene Road','16a','1812UP','Stratford','070-353548','1608');

INSERT INTO PLAYERS (PLAYERNO,NAME,INITIALS,YEAR_OF_BIRTH,SEX,YEAR_JOINED,STREET,HOUSENO,POSTCODE,TOWN,PHONENO,LEAGUENO)
       VALUES (100,'Parmenter','P',1963,'M',1979,'Haseltine Lane','80','1234KK','Stratford','070-476537','6524');

INSERT INTO PLAYERS (PLAYERNO,NAME,INITIALS,YEAR_OF_BIRTH,SEX,YEAR_JOINED,STREET,HOUSENO,POSTCODE,TOWN,PHONENO,LEAGUENO)
       VALUES (104,'Moorman','D',1970,'F',1984,'Stout Street','65','9437AO','Eltham','079-987571','7060');

INSERT INTO PLAYERS (PLAYERNO,NAME,INITIALS,YEAR_OF_BIRTH,SEX,YEAR_JOINED,STREET,HOUSENO,POSTCODE,TOWN,PHONENO,LEAGUENO)
       VALUES (112,'Bailey','IP',1963,'F',1984,'Vixen Road','8','6392LK','Plymouth','010-54874','1319');

INSERT INTO PLAYERS (PLAYERNO,NAME,INITIALS,YEAR_OF_BIRTH,SEX,YEAR_JOINED,STREET,HOUSENO,POSTCODE,TOWN,PHONENO)
       VALUES (7,'Wise','GWS',1963,'M',1981,'Edgecombe Way','39','9758VB','Stratford','070-347689');

INSERT INTO PLAYERS (PLAYERNO,NAME,INITIALS,YEAR_OF_BIRTH,SEX,YEAR_JOINED,STREET,HOUSENO,POSTCODE,TOWN,PHONENO)
       VALUES (28,'Collins','C',1963,'F',1983,'Old Main Road','10','1294QK','Midhurst','010-659599');

INSERT INTO PLAYERS (PLAYERNO,NAME,INITIALS,YEAR_OF_BIRTH,SEX,YEAR_JOINED,STREET,HOUSENO,POSTCODE,TOWN,PHONENO)
       VALUES (39,'Bishop','D',1956,'M',1980,'Eaton Square','78','9629CD','Stratford','070-393435');

INSERT INTO PLAYERS (PLAYERNO,NAME,INITIALS,YEAR_OF_BIRTH,SEX,YEAR_JOINED,STREET,HOUSENO,POSTCODE,TOWN,PHONENO)
       VALUES (95,'Miller','P',1934,'M',1972,'High Street','33a','5746OP','Douglas','070-867564');

INSERT INTO TEAMS (TEAMNO, PLAYERNO, DIVISION)
       VALUES ( 1, 6, 'first');

INSERT INTO TEAMS (TEAMNO, PLAYERNO, DIVISION)
       VALUES ( 2, 27, 'second');

INSERT INTO GAMES
       VALUES (1,2,4,8);

INSERT INTO GAMES
       VALUES (1,6,9,1);

INSERT INTO GAMES
       VALUES (1,8,0,1);

INSERT INTO GAMES
       VALUES (1,44,7,5);

INSERT INTO GAMES
       VALUES (1,57,5,0);

INSERT INTO GAMES
       VALUES (1,83,3,3);

INSERT INTO GAMES
       VALUES (2,8,4,4);

INSERT INTO GAMES
       VALUES (2,27,11,2);
	   
INSERT INTO GAMES
       VALUES (2,104,8,4);
	   
INSERT INTO GAMES
       VALUES (2,112,4,8);

INSERT INTO PENALTIES 
       VALUES (1,6,TO_DATE('12-08-1980','DD-MM-YYYY'),100);
	   
INSERT INTO PENALTIES 
      VALUES (2,44,TO_DATE('05/05/1981','DD/MM/YYYY'),75);
	   
INSERT INTO PENALTIES 
       VALUES (3,27,TO_DATE('10-09-1983','DD-MM-YYYY'),100);
	   
INSERT INTO PENALTIES 
       VALUES (4,104,TO_DATE('08/12/1984','DD/MM/YYYY'),50);
	   
INSERT INTO PENALTIES 
       VALUES (5,44,TO_DATE('8/12/1980','DD/MM/YYYY'),25);
	   
INSERT INTO PENALTIES 
       VALUES (6,8,TO_DATE('08/12/1980','DD/MM/YYYY'),25);
	   
INSERT INTO PENALTIES 
       VALUES (7,44,TO_DATE('30/12/1982','DD/MM/YYYY'),30);
	   
INSERT INTO PENALTIES 
       VALUES (8,27,TO_DATE('12/11/1984','DD/MM/YYYY'),75);