Scribd
Upload
1K views7 pages

DB211 - Unit 7 Assignment A

This document contains instructions for an assignment to solve 15 database design problems using SQL commands on a sample database called Ch07_ConstructCo. The problems involve creating tables, inserting and updating data, joining tables, and other common SQL tasks. Students are asked to submit their assignment file by the due date to receive 40 points.

Uploaded by

Tim Williams
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
1K views7 pages

DB211 - Unit 7 Assignment A

This document contains instructions for an assignment to solve 15 database design problems using SQL commands on a sample database called Ch07_ConstructCo. The problems involve creating tables, inserting and updating data, joining tables, and other common SQL tasks. Students are asked to submit their assignment file by the due date to receive 40 points.

Uploaded by

Tim Williams
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 7

DB211 Database Design ()

Instructor:
Unit 7, Assignment a
Student:
Assignment Instructions:
Solve Chapter 7 Problems 1-15, Pages 282-285. Submit your assignment file using the
link above (due Sunday of this unit, 40 points)

PROBLEMS
The Ch07_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 Ch07_ConstructCo database are shown
in Figure P7.1.
FIGURE P7.1: The Ch07_ConstructCo database

Note that the ASSIGNMENT table in Figure P7.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, a JOB_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.
Given the structure and contents of the Ch07_ConstructCo database shown in Figure
P7.1, use SQL commands answer Problems 125.
1. Write the SQL code that will create the table structure for a table named EMP_1. This
table is a subset the EMPLOYEE table. The basic EMP_1 table structure is
summarized in the following table. (Note that JOB_CODE is the FK to JOB.)
ATTRIBUTE (FIELD) NAME

DATA DECLARATION

EMP_NUM

CHAR(3)

EMP_LNAME

VARCHAR(15)

EMP_FNAME

VARCHAR(15)

EMP_INITIAL

CHAR(1)

EMP_HIREDATE

DATE

JOB_CODE

CHAR(3)

CREATE TABLE EMP_1 (


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,
JOB_CODE
CHAR(3),
FOREIGN KEY (JOB_CODE) REFERENCES JOB);
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 P7.2.
FIGURE P7.2: The contents of the EMP_1 table

INSERT INTO EMP_1 VALUES ('101', 'News', 'John', 'G', '08-Nov-00', '502');
INSERT INTO EMP_1 VALUES ('102', 'Senior', 'David', 'H', '12-Jul-89', '501');
3. Assuming that the data shown in the EMP_1 table have been entered, write the SQL
code that will list all attributes for a job code of 502.
SELECT *
FROM
EMP_1
WHERE JOB_CODE = '502';
4. Write the SQL code that will save the changes made to the EMP_1 table.
COMMIT;
5. Write the SQL code to change the job code to 501 for the person whose employee
number (EMP_NUM) is 107. After you have completed the task, examine the results
and then reset the job code to its original value.
Change Job Code for 107:
UPDATE EMP_1
SET
JOB_CODE = '501'
WHERE EMP_NUM = '107';
See Changes to 107:
SELECT *
FROM
EMP_1
WHERE EMP_NUM = '107';
Reset Job Code to Original Value:
ROLLBACK;

6. Write the SQL code to delete the row for William Smithfield, who was hired on June
22, 2004, and whose job code is 500. (Hint: Use logical operators to include all of the
information given in this problem.)
DELETE
WHERE
AND
AND
AND

FROM EMP_1
EMP_LNAME = 'Smithfield'
EMP_FNAME = 'William'
EMP_HIREDATE = '22-June-04'
JOB_CODE = '500';

7. Write the SQL code that will restore the data to its original status; that is, the table
should contain the data that existed before you made the changes in Problems 5 and 6.
ROLLBACK;
8. Write the SQL code to create a copy of EMP_1, naming the copy EMP_2. Then write
the SQL code that will add the attributes EMP_PCT and PROJ_NUM to the structure.
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 allows it, you may use DECIMAL(4,2) rather
than NUMBER(4,2).)
Copy Table:
CREATE TABLE EMP_2 AS
SELECT *
FROM
EMP_1;
Add New Rows:
ALTER TABLE EMP_2
ADD
EMP_PCT
ADD
PROJ_NUM

NUMBER(4,2),
CHAR(3);

9. Write the SQL code to change the EMP_PCT value to 3.85 for the person whose
employee number (EMP_NUM) is 103. Next, write the SQL command sequences to
change the EMP_PCT values, as shown inFigure P7.9.
FIGURE P7.9: The EMP_2 table after the modifications

Change Emp Pct for 103:


UPDATE EMP_2
SET
EMP_PCT = 3.85
WHERE EMP_NUM = '103';
Change Other Values:
UPDATE EMP_2
SET
EMP_PCT = 5.00
WHERE EMP_NUM = '101';
UPDATE EMP_2
SET
EMP_PCT = 8.00
WHERE EMP_NUM = '102';
10. Using a single command sequence, 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
PROJ_NUM = '18'
WHERE JOB_CODE = '500';
11. Using a single command sequence, 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. When you finish Problems 10 and 11, the EMP_2 table will contain

the data shown in Figure P7.11. (You may assume that the table has been saved again
at this point.)
FIGURE P7.11: The EMP_2 table contents after the modifications

UPDATE EMP_2
SET
PROJ_NUM = '25'
WHERE JOB_CODE > = '502'
12. 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. (You may assume
that the table will be restored to its condition preceding this question.)
UPDATE
SET
WHERE
AND

EMP_2
PROJ_NUM = '14'
EMP_HIREDATE <= '01-Jan-94'
JOB_CODE >= '501';

13. Write the two SQL command sequences required to:


a. Create a temporary table named TEMP_1 whose structure is composed of the
EMP_2 attributes EMP_NUM and EMP_PCT.
CREATE TABLE TEMP_1 AS
SELECT EMP_NUM, EMP_PCT
FROM
EMP_2;
b. Copy the matching EMP_2 values into the TEMP_1 table.
CREATE TABLE TEMP_1;
INSERT INTO TEMP_1
SELECT EMP_NUM, EMP_PCT
FROM
EMP_2;

14. Write the SQL command that will delete the newly created TEMP_1 table from the
database.
DROP TABLE TEMP_1;
15. Write the SQL code required to list all employees whose last names start with Smith.
In other words, the rows for both Smith and Smithfield should be included in the
listing. Assume case sensitivity.
SELECT *
FROM
EMP_2
WHERE EMP_LNAME LIKE 'Smith%';

Assignment Details:
See Red Text after each Question for answer.

You might also like