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

Assignment Help Database Management System

Reference no: EM13815710

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.

1676_The Database and The Tables.png

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 E141³iNTIAL			JOB CODE	JOB DESCRIPTION	JCO_CHG_HOUR
	805003 00	5003z5.20 Senior Cavid H				501	Systems Analyst	96.75
	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

Reference no: EM13815710

Questions Cloud

Elements of total compensation : If you were considering a job offer, which elements of total compensation would be more important to you: base pay, incentives, or benefits and services? Why? What are the implications of your answer for employers seeking to attract people like yo..

Find example of both northern and southern renaissance art : Find an example of both Northern and Southern Renaissance art by women from the same time period. Compare and contrast the points of view/ideas expressed through each style.

Which quantities are bigger cournot or monopoly : In a market with duopoly, if market demand is P=90-Q find the cournot reaction curves and the qournot quantity solutions in the case where marginal cost (MC=0) and the case where MC=30, what are the monopoly quantities in both cases? and which quanti..

Description of the issue of controversy : Description of the issue of controversy

Write sql code that will list all attributes for a job code : 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 - write the SQL code that will list all attributes for a job code of 502.

Identify factors that could increase the estimated costs : Provide a cost estimate for the solution. Use a table or spreadsheet format to display your numerical estimates. Identify factors that could increase the estimated costs

Describe repression in president kennedys american policy : Describe the mix of reforms and repression in President Kennedy's Latin American policy. How consistent was FDR in the application of "his" Good Neighbor policy?

Data analytical undertaking is to conduct basic data : The first step in any data analytical undertaking is to conduct basic data due diligence, the focus of which is on assessing the scope and limitations of your data.

Alternative perspectives to generational equity : Alternative Perspectives to Generational Equity

User Account

All Pages