Question

Summary

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 contents of the Ch08_ConstructCo database are shown in Figure P8.1. Use this database to answer the following problems.

Database Schema

The schema for the Ch08_ConstructCo database is shown below and should be used to answer the next several problems. Click this image to view it in its own tab.

Note that the ASSIGNMENT table in Figure P8.1 stores the JOB_CHG_HOUR values as an attribute (ASSIGN_CHG_HR) to maintain historical accuracy of the data. The JOB_CHG_HOUR values are likely to change over time. In fact, aJOB_CHG_HOUR change will be reflected in the ASSIGNMENT table. Naturally, the employee primary job assignment might also change, so the ASSIGN_JOB is also stored. Because those attributes are required to maintain the historical accuracy of the data, they are not redundant.

Instructions

Given the structure of the Ch08_ConstructCo database shown above, use SQL commands to answer the problems in the following steps.

Write your SQL statement in the editor on the right, then click the Run Query button to execute your statement in the interactive MySQL shell.

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.

No matter what I do I get some kind of error.

CREATE TABLE EMP_1(
EMP_NUM varchar(3),
EMP_LNAME varchar(15) NOT NULL,
EMP_FNAME varchar(15) NOT NULL,
EMP_INITIAL varchar(15),
EMP_HIREDATE date,
JOB_CODE varchar(3),
PRIMARY KEY(EMP_NUM),
FOREIGN KEY (JOB_CODE) REFERENCES JOB(JOB_CODE));

ERROR 1050 (42S01) at line 1: Table 'EMP_1' already exists

1.a

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

What I have:

CREATE TABLE EMP_2 AS SELECT * FROM EMP_1;

and the ERROR: ERROR 1050 (42S01) at line 1: Table 'EMP_2' already exists

1.b

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)

Note: If your SQL implementation requires it, you may use DECIMAL(4,2) or NUMERIC(4,2) rather than NUMBER(4,2).

My Code: ALTER TABLE EMP_2 ADD EMP_PCT NUMBER (4,2);
ALTER TABLE EMP_2 ADD PROJ_NUM CHAR(3);

The ERROR I get: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NUMBER (4,2)' at line 1

1.c

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.

My Code: UPDATE EMP_2 SET EMP_PCT='3.85'
WHERE EMP_NUM='103';

The ERROR I get:

ERROR 1054 (42S22) at line 1: Unknown column 'EMP_PCT' in 'field list'

1.d

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.

My code: UPDATE EMP_2 SET EMP_PCT=5.00 WHERE EMP_NUM IN ('100','105','107');

The ERROR I get: ERROR 1054 (42S22) at line 1: Unknown column 'EMP_PCT' in 'field list'

1.e

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.

My cod:

UPDATE EMP_2

SET EMP_PCT=10.00

WHERE EMP_PCT IS NULL;

The ERROR I get:

ERROR 1054 (42S22) at line 1: Unknown column 'EMP_PCT' in 'where clause'

1.f

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

My code:

UPDATE EMP_2

SET EMP_PCT=EMP_PCT+0.15

WHERE EMP_LNAME=’Alonzo’ AND EMP_FNAME=’Maria’ AND EMP_INITIAL=’D’;

My ERROR:

ERROR 1054 (42S22) at line 1: Unknown column 'âAlonzoâ' in 'where clause'

1.g

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.

My code:

UPDATE EMP_2

SET PROJ_NUM=’18’

WHERE JOB_CODE=’500’;

My ERROR: ERROR 1054 (42S22) at line 1: Unknown column 'â500â' in 'where clause'

1.h

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.

My code:

UPDATE EMP_2

SET PROJ_NUM=’25’

WHERE JOBCODE>=’502’;

My ERROR: ERROR 1054 (42S22) at line 1: Unknown column 'JOBCODE' in 'where clause'

1.i

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.

My code:

UPDATE EMP_2

SET PROJ_NUM=’14’

WHERE EMP_HIREDATE<=’01-Jan-94’

AND JOB_CODE>=501;

My error: ERROR 1054 (42S22) at line 1: Unknown column 'â01' in 'where clause'

Talk to me like I'm really stupid. This is making me nuts. Thank you. Thank you. Thank you.

Answer 1

From your Question It seems youe are using mySQL.

Believe me this had also happend to me certain number of times.

The issue here is the Table named EMP_1 and EMP_2 already exists most probably.

To check the existence of those tables. You need to run these commands.

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbName'

** Here dbName is the name of your database. So make sure you know that.

This is going to list all the table names existing in the database. Check if EMP_1 and EMP_2 exists. I f it exists simply drop those table to proceed further otherwise it is going to throw those errors.

drop command: DROP TABLE EMP_1

DROP TABLE EMP_2

Next run all your command . Your code seems to be totally correct. The problem was occuring due to the above issue resolve it and hopefully all the code is going to work.