6. Write the SQL code to validate the ASSIGN_CHARGE values in the ASSIGNMENT table. Your query should retrieve the assignment number, employee number, project number, the stored assignment charge (ASSIGN_CHARGE), and the calculated assignment charge (calculated by multiplying ASSIGN_CHG_HR by ASSIGN_HOURS). Sort the results by the assignment number.

Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
icon
Related questions
icon
Concept explainers
Question
6. Write the SQL code to validate the ASSIGN_CHARGE values in the ASSIGNMENT table. Your
query should retrieve the assignment number, employee number, project number, the stored
assignment charge (ASSIGN_CHARGE), and the calculated assignment charge (calculated by
multiplying ASSIGN_CHG_HR by ASSIGN_HOURS). Sort the results by the assignment number.
7. Using the data in the ASSIGNMENT table, write the SQL code that will yield the total number of hours
worked for each employee and the total charges stemming from those hours worked, sorted by
employee number. The result of running that query is shown below:
EMP_NUM EMP LNAME SumOfASSIGN_HOURS SumOfASSIGN CHARGE
101
News
3.1
387.50
Arbough
19.7
11.9
12.5
8.3
3.8
103
1664.65
104
Ramoras
1218.70
105
Johnson
1382. 50
Washington
840.15
192.85
1276.75
108
113
Joenbrood
115
117
Bawangi
Williamson
12.5
18.8
649.54
Transcribed Image Text:6. Write the SQL code to validate the ASSIGN_CHARGE values in the ASSIGNMENT table. Your query should retrieve the assignment number, employee number, project number, the stored assignment charge (ASSIGN_CHARGE), and the calculated assignment charge (calculated by multiplying ASSIGN_CHG_HR by ASSIGN_HOURS). Sort the results by the assignment number. 7. Using the data in the ASSIGNMENT table, write the SQL code that will yield the total number of hours worked for each employee and the total charges stemming from those hours worked, sorted by employee number. The result of running that query is shown below: EMP_NUM EMP LNAME SumOfASSIGN_HOURS SumOfASSIGN CHARGE 101 News 3.1 387.50 Arbough 19.7 11.9 12.5 8.3 3.8 103 1664.65 104 Ramoras 1218.70 105 Johnson 1382. 50 Washington 840.15 192.85 1276.75 108 113 Joenbrood 115 117 Bawangi Williamson 12.5 18.8 649.54
1. Table : ASSIGNMENT
IASSIONVENT
ASON NM ASSIN SATEPROJ MM. EMP MM. ASSEN JO6 ASSIGN CHS ASSIGN HOURS ASSON CHAGE
2 14
22 Ma 14.22
2M14
22M 14
22M 142
21-14 22
22 M 1425
2Mar 14
2) Ma14
23Mer 14
1000
34.55
42
20
15 11
3456
100
100
1000
100
100
1000
6450
22
42
01
104
01
675
50.25
StartAPPCo database stores data for a consulting company that tracks all charges to projects. The
14
24
001
17
34.56
charges are based on the hours each employee works on each project. The ASSIGNMENT table stores
2Ma 14
21Mer 14
2Me 1425
2Me 142
501
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
1013
115
501
190 0
21
61
501
875
103
545
23-Mar14 22
23Ma-14
2Ma-14 25
24Mar 14 25
24Me 1415
10600
13 20
be reflected in the ASSIGNMENT table. The structure and contents of the StartAPPCo database are
301
502
101
31
shown below.
10.50
AM 1422
15
110.50
5.0
se 45
24M1422
24Ma14
2M14
33
42
1511
EMPLOYEE
PK EMP NIM
gor
EMP LNAME
PK JOn_coDE
2. TABLE: EMPLOYEE
EMP FNAME
JOU DESCHTION
EMP INITIAL
a
EMROVEE
JOB OO HOUR
EMP RCDATE
EN E . E
h MPA ,
hew
Ser
A re
Raas A
Jt Ae
Smtteit Wm
100
lent
JOB LAST UPOATE
JOB CODE
100
104
106
106
10
19
14
at fa s
EMP_VLARS
Akre
Waing Ru
Geh
Oeko
W
Var
13
15
100
22Aup1s01
HE110
dead
A
11-Deca5 10
+111
16
ASSIGNMENT
Set Dat
12
113
etrod
Detert
R114
13
T ASSGNNUM
25 01
tavr
Pst
Wamee nge
Franne
Tee
15
10
10
2
110
Gesd
ASSIGN DATE
4.117
19.in36500
Jume
PROJECT
PROJ NUM
PK PROI NUM
EMP MUM
3. TABLE: JOB
PROI NAME
ASSGN KOR
PROI VALUE
ASSGN CHG HR
Popame
e Ara
Datere Despre
Caca ge
355
PROI ALANCK
ASGN HOURS
hon
12500
24M
20w
EMP NUM
ASSIGN CHANGE
eche agre
20 t
20w
20oy 13
20
24Mar.
430 20
20o-
Clence Suppet
26
Aooimoe Desgrer
Tachscan
C pat
510
18
4. TABLE: PROJECT
E PROECT
PRO RO NAE PROJ ALUE PR ALANC E NL
1000 e 10
21DS E I
Amber
Saig
230 e10
Transcribed Image Text:1. Table : ASSIGNMENT IASSIONVENT ASON NM ASSIN SATEPROJ MM. EMP MM. ASSEN JO6 ASSIGN CHS ASSIGN HOURS ASSON CHAGE 2 14 22 Ma 14.22 2M14 22M 14 22M 142 21-14 22 22 M 1425 2Mar 14 2) Ma14 23Mer 14 1000 34.55 42 20 15 11 3456 100 100 1000 100 100 1000 6450 22 42 01 104 01 675 50.25 StartAPPCo database stores data for a consulting company that tracks all charges to projects. The 14 24 001 17 34.56 charges are based on the hours each employee works on each project. The ASSIGNMENT table stores 2Ma 14 21Mer 14 2Me 1425 2Me 142 501 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 1013 115 501 190 0 21 61 501 875 103 545 23-Mar14 22 23Ma-14 2Ma-14 25 24Mar 14 25 24Me 1415 10600 13 20 be reflected in the ASSIGNMENT table. The structure and contents of the StartAPPCo database are 301 502 101 31 shown below. 10.50 AM 1422 15 110.50 5.0 se 45 24M1422 24Ma14 2M14 33 42 1511 EMPLOYEE PK EMP NIM gor EMP LNAME PK JOn_coDE 2. TABLE: EMPLOYEE EMP FNAME JOU DESCHTION EMP INITIAL a EMROVEE JOB OO HOUR EMP RCDATE EN E . E h MPA , hew Ser A re Raas A Jt Ae Smtteit Wm 100 lent JOB LAST UPOATE JOB CODE 100 104 106 106 10 19 14 at fa s EMP_VLARS Akre Waing Ru Geh Oeko W Var 13 15 100 22Aup1s01 HE110 dead A 11-Deca5 10 +111 16 ASSIGNMENT Set Dat 12 113 etrod Detert R114 13 T ASSGNNUM 25 01 tavr Pst Wamee nge Franne Tee 15 10 10 2 110 Gesd ASSIGN DATE 4.117 19.in36500 Jume PROJECT PROJ NUM PK PROI NUM EMP MUM 3. TABLE: JOB PROI NAME ASSGN KOR PROI VALUE ASSGN CHG HR Popame e Ara Datere Despre Caca ge 355 PROI ALANCK ASGN HOURS hon 12500 24M 20w EMP NUM ASSIGN CHANGE eche agre 20 t 20w 20oy 13 20 24Mar. 430 20 20o- Clence Suppet 26 Aooimoe Desgrer Tachscan C pat 510 18 4. TABLE: PROJECT E PROECT PRO RO NAE PROJ ALUE PR ALANC E NL 1000 e 10 21DS E I Amber Saig 230 e10
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 2 steps

Blurred answer
Knowledge Booster
Query Syntax
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education