Write sql code that will list all attributes for a job code

SOLUTION AT Australian Expert Writers

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

QUALITY: 100% ORIGINAL PAPERNO PLAGIARISM – CUSTOM PAPER