CREATE TABLE JOB
(
	JOB_CODE INT AUTO_INCREMENT,
	JOB_DESCRIPTION VARCHAR(25),
	JOB_CHG_HOUR DECIMAL(8,2),
	JOB_LAST_UPDATE DATETIME,
	PRIMARY KEY(JOB_CODE)
);
ALTER TABLE JOB AUTO_INCREMENT = 500;


CREATE TABLE EMPLOYEE
(
	EMP_NUM INT AUTO_INCREMENT,
	EMP_LNAME VARCHAR(15),
	EMP_FNAME VARCHAR(15),
	EMP_INITIAL VARCHAR(1),
	EMP_HIREDATE DATETIME,
	EMP_YEARS INT,
	JOB_CODE INT,
	PRIMARY KEY (EMP_NUM),
	FOREIGN KEY(JOB_CODE)
		REFERENCES JOB(JOB_CODE)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
);

ALTER TABLE EMPLOYEE AUTO_INCREMENT = 100;

INSERT INTO JOB(JOB_DESCRIPTION, JOB_CHG_HOUR, JOB_LAST_UPDATE) VALUES ("Programmer", 35.75, "2017-11-20");
INSERT INTO JOB(JOB_DESCRIPTION, JOB_CHG_HOUR, JOB_LAST_UPDATE) VALUES ("Systems Analyst", 96.75, "2017-11-20");
INSERT INTO JOB(JOB_DESCRIPTION, JOB_CHG_HOUR, JOB_LAST_UPDATE) VALUES ("Database Designer", 125.00, "2018-03-24");

INSERT INTO EMPLOYEE(EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE, EMP_YEARS) VALUES ("News", "John", "G", "2000-11-08", 501, 4);
INSERT INTO EMPLOYEE(EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE, EMP_YEARS) VALUES ("Senior", "David", "H", "1989-07-12", 500, 15);
INSERT INTO EMPLOYEE(EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE, EMP_YEARS) VALUES ("Arbough", "June", "E", "1996-12-01", 502, 8);