/* 
 * SQL scripts for CS61 Intro to SQL lectures
 * including KEYs
 * FILENAME SOCCER3.SQL
 */

DROP TABLE  IF EXISTS Player2;
DROP TABLE  IF EXISTS Tryout2;
DROP TABLE  IF EXISTS College2;

CREATE TABLE 	College2 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  );

CREATE TABLE 	Player2
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  );

CREATE TABLE Tryout2
  ( pID INT REFERENCES Player(pid) ON DELETE SET NULL, 
	  cName VARCHAR(20) REFERENCES College(cName) ON UPDATE CASCADE, 
	  pPos VARCHAR(8),
    decision VARCHAR(3),
    UNIQUE (Pid, Cname),
    UNIQUE (Pid, Ppos),
    CHECK  (
           pID IN (SELECT pID FROM Player2)
           pPos = 'goalie'
           OR pPos = 'mid'
           OR pPos = 'striker'
           )
  );

/* note that "left" and "right" are reserved words in SQL */

INSERT INTO College2 VALUES ('LSU', 'LA', 18000);
INSERT INTO College2 VALUES ('ASU', 'AZ', 12000);  
INSERT INTO College2 VALUES ('OU', 'OK', 22000);
INSERT INTO College2 VALUES ('FSU', 'FL', 19000);

INSERT INTO Player2 VALUES (10001, 'Andrew', 'no', 1200);
INSERT INTO Player2 VALUES (20002, 'Blake', 'no', 1600);
INSERT INTO Player2 VALUES (30003, 'Charles', 'no', 600);
INSERT INTO Player2 VALUES (40004, 'David', 'yes', 1600);
INSERT INTO Player2 VALUES (40004, 'David', 'yes', 1600);
INSERT INTO Player2 VALUES (50005, 'Eddie', 'yes', 300);

INSERT INTO Tryout2 VALUES (10001, 'LSU', 'goalie', 'no');
INSERT INTO Tryout2 VALUES (10001, 'ASU', 'goalie', 'yes');
INSERT INTO Tryout2 VALUES (20002, 'FSU', 'striker', 'yes');
INSERT INTO Tryout2 VALUES (30003, 'OU', 'mid', 'no');
INSERT INTO Tryout2 VALUES (40004, 'ASU', 'goalie', 'no');
INSERT INTO Tryout2 VALUES (50005, 'LSU', 'mid', 'no');

SELECT * FROM Player2;
SELECT * FROM College2;
SELECT * FROM Tryout2;

