/* Create Employee Table
   Employee Record Includes
       Unique ID (Primary Key)
       Last Name
       First Name
       Dept Number
*/
PROC SQL;
CREATE TABLE EMP_TABLE
    (EMP_ID NUM LABEL='Employee ID' UNIQUE,
	 LAST_NAME CHAR(30) LABEL='Last Name' NOT NULL,
	 FIRST_NAME CHAR(20) LABEL='First Name',
         DEPT_ID NUM LABEL='Department ID');
QUIT;

/* Create Department Table 
   Department Record Includes
       Unique ID (Primary Key)
       Department Name
*/
PROC SQL;
CREATE TABLE DEPT_TABLE
    (DEPT_ID NUM LABEL='Department ID' UNIQUE,
     DEPT_NAME CHAR(20) LABEL='Department Name');
QUIT;


/* Create Manager Table
   Department Manager Table Includes
       Unique Index (Primary Key)
       Dept Number (Foreign Key -- dept_table)
       Emp Number (Foreign Key -- emp_table)
*/
PROC SQL;
CREATE TABLE MGR_TABLE
        (EMP_ID NUM LABEL='Manager ID' NOT NULL,
         DEPT_ID NUM LABEL='Department ID' NOT NULL);
QUIT;

PROC SQL;
    CREATE INDEX MGR_INDEX ON MGR_TABLE(EMP_ID,DEPT_ID);
QUIT;

/*
  Define a Primary Key to index Employee Table
  Note: the Primary Key constraint overrides the Unique Constraint
        for a given field.
*/
PROC SQL;
  ALTER TABLE EMP_TABLE
      ADD CONSTRAINT PRIM_EMP_KEY PRIMARY KEY (EMP_ID);
QUIT;
/* Establish DEPT_ID as Primary Key */
/* Notice that Primary Key overrides Unique in Constraints */
PROC SQL;
  ALTER TABLE DEPT_TABLE
      ADD CONSTRAINT PRIM_DEPT_KEY PRIMARY KEY (DEPT_ID);
QUIT;

/* Define Foreign Keys
  Note: Need to remove constraints before deleting tables.
        Keep an eye on cascade. */
PROC SQL;
  ALTER TABLE MGR_TABLE
      ADD CONSTRAINT FOREIGN_EMP_KEY FOREIGN KEY (EMP_ID)
        REFERENCES EMP_TABLE
          ON DELETE RESTRICT
          ON UPDATE CASCADE;
QUIT;

PROC SQL;
  ALTER TABLE MGR_TABLE
      ADD CONSTRAINT FOREIGN_DEPT_KEY FOREIGN KEY (DEPT_ID)
        REFERENCES DEPT_TABLE
          ON DELETE RESTRICT
          ON UPDATE CASCADE;
QUIT;

/*
  Every Employee Belongs to a Dept
  Note: the Primary Key constraint overrides the Unique Constraint
        for a given field.
*/
PROC SQL;
  ALTER TABLE EMP_TABLE
      ADD CONSTRAINT FOREIGN_EMP_DEPT_KEY FOREIGN KEY (DEPT_ID)
        REFERENCES DEPT_TABLE
          ON DELETE RESTRICT
          ON UPDATE CASCADE;
QUIT;

/* Add some Departments */
PROC SQL;
  INSERT INTO DEPT_TABLE
    (DEPT_ID, DEPT_NAME)
    VALUES(101, 'Engineering')
    VALUES(102, 'Maintenance')
    VALUES(103, 'Sales')
    VALUES(104, 'Microbiology')
    VALUES(105, 'Quality Assurance');
    
    SELECT *
        FROM DEPT
          ORDER BY DEPT_ID;
    
QUIT;

/* Add some Employees */
PROC SQL;
  INSERT INTO EMP_TABLE
    (EMP_ID, LAST_NAME, FIRST_NAME, DEPT_ID)
    VALUES(2001, 'Cosenza', 'Carlo', 101)
    VALUES(2002, 'Johnson', 'Bob', 102)
    VALUES(2003, 'Smith', 'Jack', 103)
    VALUES(2004, 'Lee', 'Mary', 104)
    VALUES(2005, 'Patel', 'Vijay', 105);    
    SELECT *
        FROM EMP
          ORDER BY EMP_ID;    
QUIT;

/* Add some Managers */
PROC SQL;
  INSERT INTO MGR_TABLE
    (EMP_ID, DEPT_ID)
    VALUES(2001, 101)
    VALUES(2002, 102)
    VALUES(2003, 103)
    VALUES(2004, 104)
    VALUES(2005, 105);    
    SELECT *
        FROM MGR_TABLE
          ORDER BY EMP_ID;    
QUIT;

PROC SQL;
    CREATE TABLE TEMP_EMPLOYEES
	    LIKE EMP_TABLE;
QUIT;

title "Create Duplicate Table";
title2 "Using (LIKE)";
proc print data=temp_employees;
run;

/* ADD DATA WITH A SELECT QUERY */
PROC SQL UNDO_POLICY=REQUIRED;
  INSERT INTO TEMP_EMPLOYEES
    (EMP_ID, LAST_NAME, FIRST_NAME, DEPT_ID)
    VALUES(2006, 'Johnson', 'Bob', 101)
    VALUES(2007, 'Adams', 'Pat', 101)
    VALUES(2008, 'Roosevelt', 'Skip', 102)
    VALUES(2009, 'Madison', 'Peter', 102)
    VALUES(2010, 'Lincoln', 'Bernard', 103)    
    VALUES(2011, 'Washington', 'Eugene', 103)    
    VALUES(2012, 'Jefferson', 'Joseph', 104)    
    VALUES(2013, 'Wong', 'Jane', 104)    
    VALUES(2014, 'Smith', 'Emily', 105)    
    VALUES(2015, 'Jones', 'Karen', 105);    
QUIT;

/* CONVERT TEMP EMPLOYEES FROM DEPT 101 TO FULL TIME */
/* ADD DATA WITH A SELECT QUERY */
PROC SQL;
  INSERT INTO EMP_TABLE
    (EMP_ID, LAST_NAME, FIRST_NAME, DEPT_ID)
  SELECT EMP_ID, LAST_NAME, FIRST_NAME, DEPT_ID
      FROM TEMP_EMPLOYEES
	      WHERE DEPT_ID IN (104);
QUIT;

/* SEE EVERY EMPLOYEE WITH LISTED DEPT */
title "Show all Employees (SELECT)";
title2 "Using (WHERE)";
PROC SQL;
    SELECT DEPT_TABLE.DEPT_NAME, EMP_TABLE.FIRST_NAME, EMP_TABLE.LAST_NAME
	    FROM DEPT_TABLE, EMP_TABLE
	WHERE DEPT_TABLE.DEPT_ID = EMP_TABLE.DEPT_ID;
QUIT;

/* GROUP BY DEPT */
title "Show all Employees";
title2 "Using (GROUP BY)";
PROC SQL;
    SELECT DEPT_TABLE.DEPT_NAME, EMP_TABLE.FIRST_NAME, EMP_TABLE.LAST_NAME
	    FROM DEPT_TABLE, EMP_TABLE		
	WHERE DEPT_TABLE.DEPT_ID = EMP_TABLE.DEPT_ID
        GROUP BY DEPT_TABLE.DEPT_NAME;
QUIT;

/* GROUP BY DEPT */
title "Show Department Size";
title2 "Using COUNT (*)";
PROC SQL;
    SELECT DEPT_TABLE.DEPT_NAME, COUNT(*)
	    FROM DEPT_TABLE, EMP_TABLE		
	WHERE DEPT_TABLE.DEPT_ID = EMP_TABLE.DEPT_ID
        GROUP BY DEPT_TABLE.DEPT_NAME;
QUIT;

/* USE CASE STATEMENT */
title "Show Division";
title2 "Using CASE";
PROC SQL;
    SELECT DEPT_NAME,
        CASE DEPT_ID
            WHEN 101 THEN 'Manufacturing'
            WHEN 102 THEN 'Manufacturing'
            WHEN 103 THEN 'Global'
            WHEN 104 THEN 'Compliance'
            WHEN 105 THEN 'Compliance'
			ELSE 'Unknown'
	    END AS DIVISION
	FROM DEPT_TABLE;
QUIT;

title "Joseph Jefferson Leaves the Company";
title2 "Using DELETE";
PROC SQL;
    DELETE FROM EMP_TABLE
	  WHERE EMP_ID = 2012;
QUIT;

PROC PRINT DATA=EMP_TABLE;
RUN;

title "Engineering Absorbs Maintenance Department";
title2 "Using UPDATE";
PROC SQL;
    UPDATE EMP_TABLE
	  SET DEPT_ID = 101
	    WHERE DEPT_ID = 102;
QUIT;

PROC PRINT DATA=EMP_TABLE;
RUN;

title "Some Bugs";
title2 "Phantom Entries Prevent Delete";
PROC SQL;
    DELETE FROM DEPT_TABLE
	  WHERE DEPT_ID = 102;
QUIT;

PROC PRINT DATA=DEPT_TABLE;
RUN;

title "What's that guys name again?";
title2 "Using LIKE";
PROC SQL;
    SELECT EMP_ID, LAST_NAME, FIRST_NAME
	FROM EMP_TABLE
	   WHERE LAST_NAME LIKE 'Co%';
QUIT;

title "What's that guys name again?";
title2 "Using LIKE";
PROC SQL;
    SELECT EMP_ID, LAST_NAME, FIRST_NAME
	FROM EMP_TABLE
	   WHERE LAST_NAME LIKE '%za';
QUIT;