MySQL Problems 8.1-8.15

Using the EMP_2 table, write the SQL code that will add the attributes EMP_PCT and PROJ_NUM to EMP_2. The EMP_PCT is the bonus percentage to be paid to each employee. The new attribute characteristics are: EMP_PCT NUMBER(4,2)PROJ_NUM CHAR(3)

ALTER TABLE EMP_2 ADD EMP_PCT DECIMAL(4,2), ADD PROJ_NUM CHAR(3);

Write the SQL code that will save the changes made to the EMP_1 table (if supported by your DBMS).

COMMIT;

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));

Write the SQL code to create a copy of EMP_1, including all of its data, and naming the copy EMP_2.

CREATE TABLE EMP_2 AS SELECT * FROM EMP_1;

Write the SQL code to delete the row for William Smithfield, who was hired on June 22, 2004, and whose job code is 500.

DELETE FROM EMP_1 WHERE EMP_LNAME = 'Smithfield' AND EMP_FNAME = 'William' AND EMP_HIREDATE = '2004-06-22' AND JOB_CODE = '500'

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);

Using the EMPLOYEE table that already exists, use a subquery to insert the remaining rows from the EMPLOYEE table into the EMP_1 table. Remember, your subquery should only retrieve the columns needed for the EMP_1 table and only the employees shown in the figure.

INSERT INTO EMP_1SELECT EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODEFROM EMPLOYEE WHERE EMP_NUM BETWEEN 103 AND 109

Write the SQL code to change the job code to 501 for the person whose employee number (EMP_NUM) is 107.

UPDATE EMP_1 SET JOB_CODE = 501 WHERE EMP_NUM = 107;

Using a single command sequence with the EMP_2 table, write the SQL code that will change the project number (PROJ_NUM) to 18 for all employees whose job classification (JOB_CODE) is 500.

UPDATE EMP_2 SET EMP_PCT = '3.85' WHERE EMP_NUM = 103; UPDATE EMP_2 SET PROJ_NUM='18' WHERE JOB_CODE='500';

Using the EMP_2 table, write the SQL code to change the EMP_PCT value to 3.85 for the person whose employee number (EMP_NUM) is 103.

UPDATE EMP_2 SET EMP_PCT = 3.85 WHERE EMP_NUM = '103'

Using the EMP_2 table, write a single SQL command to change the EMP_PCT value to 5.00 for the people with employee numbers 101, 105, and 107.

UPDATE EMP_2 SET EMP_PCT = 5.00 WHERE EMP_NUM IN(101,105,107);

Using the EMP_2 table, write a single SQL command to change the EMP_PCT value to 10.00 for all employees who do not currently have a value for EMP_PCT.

UPDATE EMP_2 SET EMP_PCT=10.00 WHERE EMP_PCT IS NULL;

Write the SQL code that will change the PROJ_NUM to 14 for employees who were hired before January 1, 1994, and whose job code is at least 501. When you finish Problems 7-15, the EMP_2 table will contain the data shown in Figure P8.15.

UPDATE EMP_2SET PROJ_NUM='14'WHEREEMP_HIREDATE<'01/01/1994'AND(JOB_CODE='501' OR JOB_CODE>'501');

Using a single command sequence with the EMP_2 table, write the SQL code that will change the project number (PROJ_NUM) to 25 for all employees whose job classification (JOB_CODE) is 502 or higher.

UPDATE EMP_2SET PROJ_NUM='25'WHERE JOB_CODE='502'OR JOB_CODE>'502';

Using the EMP_2 table, write the SQL command to add .15 to the EMP_PCT of the employee whose name is Maria D. Alonzo. (Use the employee name in your command to determine the correct employee.)

UPDATE EMP_2set EMP_PCT = EMP_PCT + 0.15where EMP_LNAME like 'Alonzo'and EMP_FNAME like 'Maria'and EMP_INITIAL like 'D';