Reference no: EM13794650

Task Description

Question 1

Exercise 1. Write the SQL code that will create the table structure for a table named EMP 1. This table is a subset of the EMPLOYEE table. The basic EMP 1 table structure is summarized in Fig. 1. (Note that the JOB CODE is the FK to JOB.)

ATTRIBUTE               (FIELD) NAME	DATA DECLARATION
EMP_NUM	CHAR(3)
EMP LNAME	VARCHAR(15)
EMP FNAME	VARCHAR(15)
EMI' INITIAL	CHARM
EMP HIREDATE	DATE
JOB CODE	CHAR(3)

Exercise 2. Having created the table structure in previous exercise, write the SQL code to enter the following rows for the table shown below:

	EMP_NUM	EMP_LNAME	EMP_FNAME	EMP_INITIAL	EW/P_HiREDATE	JOB_CODE
»	101	News	John	G	8-Nov-98	502
	102	Senior	David	H	12-Jul-87	501
	103	Arbough	June	E	1-Dec-94	500
	104	Ramoras	Anne	K	15-Noy-85	501
	105	Johnson	Alice	K	1-Feb-91	502
	106	Smithfield	Willtain		22-Jun-02	500
	107	Alonzo	Maria	D	10-Oct-91	500
	108	Washington	Ralph	B	22-Aug-89	501
	109	Smith	Larry	W	18-Jul-95	501

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

Exercise 4. Write the SQL code to change the job code to 501 for the person whose personnel number is 106. After you have completed the task, examine the results, and then reset the job code to its original value.

Exercise 5. Write the SQL code to delete the row for the person named William Smithfield, who was hired on June 22, 2002 and whose job code classification is 500.

Exercise 6. 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 its structure. The EMP PCT is the bonus percentage to be paid to each employee. The new attribute characteristics are shown next:

EMP_PCT NUMBER(4,2)

PROJ_NUM CHAR(3)

Exercise 7.Write the SQL code to enter an EMP PCT value of 3.85 for the person whose employee number (EMP NUM) is 103. Next, enter the remaining EMP PCT values shown in Fig. 1.3:

	EMP_NUM	EMP_LNAME	EMP_FNAME	EMP_INITIAL	EW/P_HiREDATE	JOB_CODE	EMP_PCI	PROJ_NUM
»	101	News	John	G	8-Nov-98	502	5
	102	Senior	David	H	12-Jul-87	501	8
	103	Arbough	June	E	1-Dec-94	500	3.65
	104	Ramoras	Anne	K	15-Noy-85	501	10
	105	Johnson	Alice	K	1-Feb-91	502	5
	106	Smithfield	William		22-Jun-02	500	6.2
	107	Alonzo	Maria	D	10-Oct-91	500	5.15
	108	Washington	Ralph	B	22-Aug-89	501	10
	109	Smith	Larry	W	18-Jul-95	501	2

Exercise 8. Using a single command sequence, write the SQL code that will enter the project number (PROJ NUM) = 18 for all employees whose job classification (JOB CODE) is 500.

Exercise 9. Using a single command sequence, write the SQL code that will enter the project number (PROJ NUM) = 25 for all employees whose job classification (JOB CODE) is 502 or higher.

Exercise 10. Write the SQL code that will enter a PROJ NUM of 14 for those employees who were hired before January 1, 1992 and whose job code is at least 501

Question 2

Exercise 11. You are given the EMPLOYEE, JOB, PROJECT and ASSIGNMENT tables in a database as in Fig. 1.4.

The table schemes are:

EMPLOYEE (EMP NUM, EMP LNAME, EMP FNAME, EMP INITIAL, EMP HIREDATE, JOB CODE, EMP YEARS)

JOB (JOB CODE, JOB DESCRIPTION, JOB CHG HOUR, JOB LAST UPDATE)

PROJECT (PROJ NUM, PROJ NAME, PROJ VALUE, PROJ BALANCE, EMP NUM)

ASSIGNMENT (ASSIGN NUM, ASSIGN DATE, PROJ NUM, EMP NUM,

ASSIGN JOB, ASSIGN CHG HR, ASSIGN HOURS, ASSIGN CHARGE)

Using the EMPLOYEE, JOB, and PROJECT tables in the database (see above), write the SQL code that will produce the results shown in Fig. 1.5.

PRO_NAME	PROJ_VALUE	PRO_EALANCE	EMP_LNAME	EMP_FNAME	EMP_INITIAL	JOB_CODE	EMP_PCI	JOB_CHG_HOUR
Rolling Tide	805000	500345.2	Senior	David	H	501	Systems Analyst	96.75
Evergreen	1453500	1002350	Arbougn	June	E	500	Programme'	35.8
Starfight	2650500	230988	Alcnzo	Maria	D	500	Programme'	35.75
AmbeWave	3500500	2110346	Washington	Ralph	B	501	;Systems Analyst	96.8

Exercise 12. Write the SQL code that will produce a virtual table named REP 1. The virtual table should contain the same information that was shown in the previous exercise.

Exercise 13.Write the SQL code to find the average project value in the table PROJECT.

Exercise 14.Write the SQL code that will produce a listing for the data in the PROJECT table in ascending order by the project value.

Exercise 15. Write the SQL code that will list only the di?erent assignment jobs found in the ASSIGNMENT table.

Exercise 16.Write the SQL code to calculate the ASSIGN CHARGE values in the ASSIGNMENT table. Note that ASSIGN CHARGE is a derived attribute that is calculated by multiplying the ASSIGN CHG HR and the ASSIGN HOURS.

Exercise 17. Using the data in the ASSIGNMENT table, write the SQL code that will, for each employee, yield the total number of hours worked and the total charges stemming from those hours worked. The results of running that query are shown in Fig. 1.6.

EMP_NUM	EMP_LNAME	SUMOfASSIGN_HOURS	SUMOfASSIGN_CHARGE
101	News	3.1	387.5
103	Arbough	19.7	1664.65
104	Ramoras	11.9	1218.7
105	Johnson	12.5.	1382.5
108	Washington	8.3	84015
113	Joenbrood	3.8	192.85
115	Bawangi	12.5	1276.75
117	Williamson	18.8	649.54

Exercise 18.Write a query to produce the total number of hours and charges for each of the projects represented in the ASSIGNMENT table. The output is shown in Fig. 1.7.

PROJ_NUM	SumOFASSIGN_HOURS	SumOFASSIGN_CHARGE
15	20.5	1806.52
18	23.7	1544.6
22	27	2593.16
25	19.4	1668.16