
use test;
/* 
 * SQL scripts for CS61 Intro to SQL lectures
 * FILENAME SOCCER.SQL
 */


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

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

CREATE TABLE 	Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3)
  );

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

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

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

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

SELECT * FROM Player;
SELECT * FROM College;
SELECT * FROM Tryout;

SELECT DISTINCT pName FROM Player;

SELECT * FROM Player WHERE pName="David";

/* Avoid dupicates */
SELECT DISTINCT * FROM Player WHERE pName="David";
SELECT DISTINCT HS FROM Player ;

/* Sort the SELECT output */
SELECT * FROM Tryout;
SELECT * FROM Tryout WHERE decision = "yes";
SELECT * FROM Tryout WHERE decision = "yes" ORDER BY pPos;
SELECT * FROM Player ORDER BY pName ASC, HS DESC;
SELECT * FROM Player ORDER BY HS DESC, pName DESC;

/* LIKE */
SELECT * FROM College WHERE cName LIKE "%SU";

/* AND */
SELECT * FROM Player WHERE HS < 1000;
SELECT * FROM Player WHERE HS < 1000 AND yCard = "yes";

/* arithmetic and Aggregate functions*/
SELECT * FROM Player;
SELECT COUNT(yCard) AS NumYellowCards FROM Player WHERE HS > 999 AND yCard="yes";

/* renaming with AS */
SELECT DISTINCT pName AS CityPlayer FROM Player WHERE HS > 1000;

/* Sub Query */
/* get the names of players who were not accepted on a team */
SELECT DISTINCT pName FROM Player WHERE pID NOT IN 
	(SELECT pID FROM Tryout WHERE decision="yes");

/* The next statement has an error */
SELECT DISTINCT pName FROM Player WHERE pID NOT IN 
	(SELECT * FROM Tryout WHERE decision="yes"); 

/* GROUP BY and HAVING */
/* How many goalie tryouts did each college have */
SELECT cName, COUNT(cName) FROM Tryout WHERE pPos = "goalie" GROUP BY cName;
/* Which colleges had exactly 1 goalie tryout */
SELECT cName, COUNT(cName) FROM Tryout WHERE pPos = "goalie" GROUP BY cName HAVING COUNT(cName) < 2;

/* PRIMARY KEY testing */
/* DROP and RECREATE the Player table with a PRIMARY KEY, then try to INSERT */

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

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

/* ok, put it back */

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

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

/* Now mess around with Tryout and its Foreign Keys */
CREATE TABLE 	Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  );
INSERT INTO Tryout VALUES (10001, 'LSU', 'goalie', 'no');
/* INSERT INTO Tryout VALUES (10001, 'ASU', 'goalie', 'yes');  */  /* Duplicate pID */
INSERT INTO Tryout VALUES (20002, 'FSU', 'striker', 'yes');
INSERT INTO Tryout VALUES (30003, 'OU', 'mid', 'no');
INSERT INTO Tryout VALUES (40004, 'ASU', 'goalie', 'no');
INSERT INTO Tryout VALUES (50005, 'LSU', 'mid', 'no');

SELECT * FROM College;
SELECT * FROM Tryout;

DROP TABLE College; 		/* NO WORKIE! */
SELECT * FROM College;		/* Still there */

UPDATE College SET cName="USC" WHERE cName="FSU";  /* No Workie either! */

/* OK, NULL the FOREIGN Keys first, then try it */
UPDATE Tryout SET cName=NULL WHERE cName="FSU";
UPDATE College SET cName="USC", State="CA" WHERE cName="FSU";
SELECT * FROM College;  /* and it's gone */

/* MySQL Doesn't have a DIFFERENCE operator 
	SELECT DISTINCT HS FROM Player - (SELECT Player.HS from (
	SELECT * WHERE Player.HS > s.HS FROM Player, Player AS s ); */
    
    
/* JOINs */
-- USE ccptestdb;
/* CROSS PRODUCT */
SELECT * FROM College, Tryout;

/* JOIN */
SELECT * FROM Player, Tryout WHERE Player.pID=Tryout.pID;

SELECT * FROM Player, Tryout WHERE Player.pID=Tryout.pID AND decision="yes";

/* NATURAL JOIN */
SELECT * FROM Player NATURAL JOIN Tryout;

SELECT * FROM Player NATURAL JOIN Tryout WHERE decision="yes";

/* Notice the equivalent output */
SELECT * FROM Player, Tryout WHERE Player.pID=Tryout.pID;
SELECT * FROM Player NATURAL JOIN Tryout;

/* Rename using AS */
SELECT pID, pName, HS/1000             FROM Player;
SELECT pID, pName, HS/1000 AS HSScaled FROM Player;

/* Rename is especially useful with Self-Joins */
/* Find the names of all the players who have yCards AND
came from a HS smaller than some other Player*/
SELECT * FROM Player;
SELECT DISTINCT A. pName
FROM Player as A , Player as B
WHERE A.yCard = "yes" AND A.HS < B.HS;

/* String functions */
/* LIKE:  '%' matches 0 or more characters, '_' matches exactly 1 character */
SELECT * FROM Player;
SELECT DISTINCT pName FROM Player WHERE pName LIKE '%a%';
SELECT DISTINCT pName FROM Player WHERE pName LIKE '__a%';  

SELECT * FROM Player;
SELECT * FROM Tryout;
SELECT pName, pPos FROM Player, Tryout 
   WHERE (Player.pID, yCard) = (Tryout.pID, "yes");

/* SET Operations */
SELECT * FROM College;
SELECT cName, enr, state FROM College WHERE enr > 12000 AND cName LIKE "_SU";
SELECT cName, enr, state FROM College WHERE enr > 10000 AND state="LA";
(SELECT cName, enr, state FROM College WHERE enr > 12000 AND cName LIKE "FS_")
UNION
(SELECT cName, enr, state FROM College WHERE enr > 10000 AND state="LA");

/* INTERSECTION - the hard way */
SELECT * FROM Tryout;
SELECT cName FROM Tryout WHERE pPos="goalie" AND (cName) IN 
  (SELECT cName FROM Tryout WHERE decision="yes");
/* Notice the duplication ... why did that happen? */

/* SUBTRACTION or MINUS or EXCEPT - the hard way */

SELECT * FROM Tryout;
SELECT cName FROM Tryout WHERE pPos="goalie";
SELECT cName FROM Tryout WHERE decision="yes";
/* Which school(s) had a goalie tryout that didn't get drafted? */
SELECT cName FROM Tryout WHERE pPos="goalie" AND (cName) NOT IN 
  (SELECT cName FROM Tryout WHERE decision="yes");
/* Notice that we're "subtracting" more than we have ... it's ok */
