Information Technology > STUDY GUIDE > The Ch08 Construct Co database stores data (All)

The Ch08 Construct Co database stores data

Document Content and Description Below

The Ch08_ConstructCo database stores data for a consulting company that tracks all charges to projects. The charges are based on the hours each employee works on each project. The structure and cont ... ents of the Ch08_ConstructCo database are shown in Figure P8.1. Use this database to answer the following problems. Problem 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 EMP_NUM as the primary key. Note that the JOB_CODE is the FK to JOB so be certain to enforce referential integrity. Your code should also prevent null entries in EMP_LNAME and EMP_FNAME. CREATE TABLE EMP_1( EMP_NUM VARCHAR(3) PRIMARY KEY, EMP_LNAME VARCHAR(15) NOT NULL, EMP_FNAME VARCHAR(15) NOT NULL, EMP_INITIAL VARCHAR(1), EMP_HIREDATE DATE, JOB_CODE VARCHAR(3), FOREIGN KEY (JOB_CODE) REFERENCES JOB(JOB_CODE)); Problem 2 Having created the table structure in Problem 1, write the SQL code to enter the first two rows for the table shown in Figure P8.2. Each row should be inserted individually, without using a subquery. Insert the rows in the order that they are listed in the figure. INSERT INTO EMP_1 VALUES('101','News','John','G','2000-11-08','502'); INSERT INTO EMP_1 VALUES('102','Senior','David','H','1989-07-12','501'); Problem 4 Write the SQL code that will save the changes made to the EMP_1 table (if supported by your DBMS). INSERT INTO EMP_1 SELECT EMP_NUM,EMP_LNAME,EMP_FNAME,EMP_INITIAL,EMP_HIREDATE,jOB_CODE FROM EMPLOYEE WHERE EMP_NUM BETWEEN 103 AND 109; [Show More]

Last updated: 3 years ago

Preview 1 out of 4 pages

Buy Now

Instant download

We Accept: