Question

DROP TABLE EMPLOYEE;
DROP TABLE JOB;
DROP TABLE EMP;
DROP TABLE EMP_1;
DROP TABLE EMP_2;

CREATE TABLE JOB(JOB_CODE CHAR (3) PRIMARY KEY, JOB_DESCRIPTION VARCHAR (20) NOT NULL,JOB_CHG_HOUR NUMBER (5,2) NOT NULL,JOB_LAST_UPDATE DATE NOT NULL);

INSERT INTO JOB VALUES('500','Programmer','35.75','20-Nov-2017');
INSERT INTO JOB VALUES('501','System Analyst','96.75','20-Nov-2017');
INSERT INTO JOB VALUES('502','Database Designer','125.00','24-Mar-2018');

CREATE TABLE EMPLOYEE(EMP_NUM CHAR (3) PRIMARY KEY,EMP_LNAME VARCHAR (15) NOT NULL,EMP_FNAME VARCHAR (15) NOT NULL, EMP_INITIAL CHAR (1),EMP_HIREDATE DATE NOT NULL,JOB_CODE CHAR (3), EMP_YEARS NUMBER (2),FOREIGN KEY (JOB_CODE) REFERENCES JOB (JOB_CODE));

INSERT INTO EMPLOYEE VALUES('103','Arbough','June','E','01-Dec-96','500','18');
INSERT INTO EMPLOYEE VALUES('104','Ramaros','Anne','K','15-Nov-87','501','25');
INSERT INTO EMPLOYEE VALUES('105','Johson','Alice','K','01-Feb-93','502','19');
INSERT INTO EMPLOYEE VALUES('106','Smithfield','William','','22-June-04','500','8');
INSERT INTO EMPLOYEE VALUES('107','Alonzo','Maria','D','10-Oct-83','500','19');
INSERT INTO EMPLOYEE VALUES('108','Washington','Ralph','B','22-Aug-91','501','21');
INSERT INTO EMPLOYEE VALUES('109','Smith','Larry','W','18-Jul-97','501','15');

2. QUESTIONS Given the structures and contents of the ConstructCo database shown in the Figure below, use SQL commands to answer the questions. Database name: Ch08 ConstructCo Relational diagram 1. Write the SQL code that will create only the table structure for a table named EMP 1. This table will be a subset of the EMPLOYEE table. The basic EMP 1 table structure is summarized in the following table. Use EMPNUM as the primary key. Do not forget to add foreign key constraint and prevent null entries in EMPLNAME and EMP FNAME

Answer 1

1.

CREATE TABLE drinks.emp_1 (

  EMP_NUM CHAR(3) NOT NULL,

  EMP_LNAME VARCHAR(15) NULL,

  EMP_FNAME VARCHAR(15) NULL,

  EMP_INITIAL CHAR(1) NULL,

  EMP_HIREDATE DATE NULL,

  JOB_CODE CHAR(3) NULL,

  PRIMARY KEY (EMP_NUM));

2.

INSERT INTO emp_1 (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE) VALUES ('101', 'News', 'John', 'O', '2000-11-08', '502');

INSERT INTO emp_1 (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE) VALUES ('102', 'Senior', 'David', 'H', '1989-07-12', '501');

3.

INSERT INTO emp_1

select EMP_NUM,EMP_LNAME,EMP_FNAME,EMP_INITIAL,EMP_HIREDATE,JOB_CODE

FROM emp

where EMP_NUM IN (103,104,105,106,107,108,109)

5.

UPDATE drinks.emp_1 SET JOB_CODE = '502' WHERE (EMP_NUM = '107');

**************************************************************************
Please give an upvote,as it matters to me a lot :)

********************************************************************************************************************************
As HOMEWORKLIB RULES expert answering guidelines,Experts are supposed to answer only certain number of sub-parts.Please raise the remaining sub-parts as a new query as per HOMEWORKLIB RULES.
******************************************************************************************************************************