Write the sql code that will create the table structure for

Question

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.1. (Note that the JOB CODE is the FK to JOB.)

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

ATTRIBUTE              (FIELD) NAME		DATA DECLARATION
EM P_NUM		CHAR (3)
EMP LNAME		VARCHAR (15)
EMP_FNAlvIE		VARCHAR (15)
EMP_INITIAL		CHARM
EMP_HIREDATE		DATE
	JOB_CODE	CHAR (3)

Figure 1.1: The EMP_1 Table Structure

EMP_NUM	EMP_LNAME	EMP_FNAME	EMP_INITIAL	EMP_HERDATE	JOB_CODE
101	News	John	G	08-Nov-98	502
102	Senior	David	H	1 2-Jul-8 7	501
103	Arbough	June	E	01-Dec-94	500
104	Ramoras	Anne	K	15-Nov-65	501
105	Johnson	Ate	K	01-Feb-91	502
105	Smithfield	William		22-Jun-02	500
107	Alonzo	Mane	D	10.Oct-91	500
102	Washington	Ralph	B	22-Aug-89	501
109	Smith	Larry	W	18-Jul-95	501

Figure 1.2: The Contents of the EMP_1 Table 

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	EMP_HERDATE	JOB_CODE	EMP_PCT
101	News	John	G	08-Nov-98	502	5.00
102	Senior	David	H	1 2-Jul-8 7	501	8.00
103	Arbough	June	E	01-Dec-94	500	3.85
104	Ramoras	Anne	K	15-Nov-65	501	10.00
105	Johnson	Ate	K	01-Feb-91	502	5.00
105	Smithfield	William		22-Jun-02	500	6.20
107	Alonzo	Mane	D	10.Oct-91	500	5.15
102	Washington	Ralph	B	22-Aug-89	501	10.00
109	Smith	Larry	W	18-Jul-95	501	2.00

Figure 1.3: The Contents of the EMP_2 Table

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.

1.4:The Database and The Tables

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.

PRO._NAME	PFOLVALJE	PROD EALANCE	EMP_LNIAIVETEMPINAME                                  E1413iNTIAL			JOB CODE	JOB DESCRIPTION	JCO_CHG_HOUR
	805003 00	5003z5.20 Senior                       Cavid    H				501	Systems Analyst	96.75
Eitergten	1453503 00	1032350.00 Arbougi		Jane		500	Progranme'	35.75
Slar11011	2650503 00	2339880.00 Alcoro		Nana	D	503	Progranme.	35.75
Ankle' Wave	3500503.00	211 03L6.00 Washington		Ralph	B	501	Systems Analyst	96.75

Figure 1.5: The Expected Query Results

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

EMID_NUM	EMP_LNAME	SumOfASSIGN_HOURS	SumOfASSIGN_CHARGE
101	News	3.1	387.5
103	Arbough	19.7	16664.65
104	Remoras	11.9	1218.7
105	Johnson	12.5	13.82.50
108	Washington	8.3	840.15
113	Joenbrood	3.8	192.85
115	Bowangi	12.5	1276.75
117	Williamson	16.6	649.54

Figure 1.6: Total Hours and Charges by Employee

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.8
22	27	2593.16
25	19.4	168816

Figure 1.7: Total Hours and Charges by Project