* SAS PROC SQL VIDEO SCRIPT;

LIBNAME STAT408 "/courses/d716b355ba27fe300";


* SELECT COMMAND;
/* Create a query from STAT408.player to obtain all the player 
first and last names for all players born in 1993. */
PROC SQL;
    SELECT name_first, name_last 
    FROM STAT408.player
    WHERE birth_year = 1993;
RUN;

/*write a query to identify the years, and total homeruns,
 for years with more than 5000 homeruns since 1980*/
PROC SQL;
    SELECT year, SUM(hr) as homeruns
    FROM STAT408.batting
    WHERE year GT 1979
    GROUP BY year
	HAVING homeruns GT 5000
    ORDER BY year;
RUN;

/*create table with last query*/

PROC SQL;
	CREATE TABLE annual_hr as
    SELECT year, SUM(hr) as homeruns
    FROM STAT408.batting
    WHERE year GT 1979
    GROUP BY year
	HAVING homeruns GT 5000
    ORDER BY year;
RUN;

* MODIFYING A TABLE;

* DELETE prior to 2003, when steriod testing started;
PROC SQL;
	DELETE FROM annual_hr 
	WHERE year LT 2003;
RUN;


* JOINING TABLES;

PROC SQL;
    CREATE TABLE born9495 as
    SELECT * FROM STAT408.player
    WHERE birth_year IN (1994, 1995);
RUN;

PROC SQL;
    CREATE TABLE pitching_recent as
    SELECT * FROM STAT408.pitching
    WHERE year GT 2014;
RUN;


*INNER JOIN;
PROC SQL;
    SELECT * 
    FROM born9495 as b
    INNER JOIN pitching_recent as s
    ON b.player_id = s.player_id;
RUN;

*LEFT JOIN;
PROC SQL;
    SELECT * 
    FROM born9495 as b
    LEFT JOIN pitching_recent as s
    ON b.player_id = s.player_id;
RUN;

*RIGHT JOIN;
PROC SQL;
    SELECT * 
    FROM born9495 as b
    RIGHT JOIN pitching_recent as s
    ON b.player_id = s.player_id;
RUN;

*FULL JOIN;
PROC SQL;
    SELECT * 
    FROM born9495 as b
    FULL JOIN pitching_recent as s
    ON b.player_id = s.player_id;
RUN;