Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
2. Using the EMPLOYEE, JOB, and PROJECT tables in the Ch07_ConstructCo data- base, write the SQL code that will join the EMPLOYEE and PROJECT tables using EMP_NUM as the common attribute. Display the attributes shown in the results presented in Figure P7.2, sorted by project value.

2. Using the EMPLOYEE, JOB, and PROJECT tables in the Ch07_ConstructCo data- base, write the SQL code that will join the EMPLOYEE and PROJECT tables using EMP_NUM as the common attribute. Display the attributes shown in the results presented in Figure P7.2, sorted by project value.

Systems Architecture
Systems Architecture
7th Edition
ISBN:9781305080195
Author:Stephen D. Burd
Publisher:Stephen D. Burd
Chapter3: Data Representation
Section: Chapter Questions
Problem 3RP
See similar textbooks
icon
Related questions
icon
Concept explainers
Question
100%
FIGURE P7.1 THE CH07_CONSTRUG UCICODVADA E Relational diagram Database name: Ch07_ConstructCo Table name: EMPLOYEE EMP NUM EMP LNAME EMP FNAME EMP MTIAL I EPJREDATE JOB CODE EMP YEARS 17 ASSIGNMENT PK Assign Num 101 News John 08-Nov-00 502 12-Jul-89 501 28 Assign Date FK2 Proj Num FK1 Emp Num EMPLOYEE 102 Senior David JO5 Arbcugh 01-Dec-96 500 21 PK Emp Num 103 June PK Job Code H---O 15-Nov-87 501 30 PROJECT Assign_Job Assign_Chg_Hr Assign_Hours Assign_Charge 104 Ramoras Anne Job Description Job Chg Hour Job Last Update Emp LName Emp FName Emp initial Emp HireDate FK1 Job Code Emp Years Proi Hum 105 Johnson Alice K 01-Feb-93 502 25 22-Jun-04 500 13 Smithfield Alonzo Washington Raiph Smith 106 William Proj Name Proj Value Proj Balance FK1 Emp Num H-- 107 Maria 10-Oct-93 500 24 108 22-Aug-91 501 26 18-Jul-97 501 20 Larry Gerald 109 22 11-Dec-95 505 04-Apr-91 506 110 Olenko A 27 111 Wabash Geoff B 112 Smithson Darlene M 23-Oct-94 507 23 21 15-Nov-96 508 20-Aug-93 508 25-Jan-92 501 113 Joenbrood Delbert K 24 114 Jones Annelise Table name: JOB 26 115 Bawangi Travis B 05-Mar-97 510 21 JOB CHG HOUR JOB LAST UPDATE 20-Nov-17 Pratt Gerald JOB DESCRIPTION Programmer Systems Analyst Database Designer Bectrical Engineer Mechanical Engineer Civil Engineer Clerical Support DSS Analyst Applications Designer 116 JOB CODE 500 501 502 Williamson Angie 19-Jun-96 509 21 35.75 117 96.75 20-Nov-17 118 Frommer James J 04-Jan-05 510 13 24-Mar-18 20-Nov-17 20-Nov-17 20-Nov-17 20-Nov-17 20-Nov-17 24-Mar-18 20-Nov-17 20-Nov-17 125.00 84.50 Table name: ASSIGNMENT 503 504 505 506 507 508 509 510 67.90 ASSIGN_NUM ASSIGN_DATE PROJ_NUM EMP_NUM ASSIGN_JOB ASSIGN_CHG HR ASSIGN_HOURS ASSIGN_CHARGE 503 55.78 84.5 3.5 295.75 26.87 1001 22-Mar-18 18 103 509 34.55 4.2 145.11 22-Mar-18 22 22-Mar-18 18 45.95 1002 117 21 69.1 34.55 84.5 48.10 1003 117 509 22-Mar-18 18 103 503 5.9 498.55 34.55 1004 Bio Technician 212.85 2.2 4.2 1005 22-Mar-18 25 108 501 96.75 General Support 18.36 1006 22-Mar-18 22 104 501 96.75 406.35 192.85 50.75 84.5 1007 22-Mar-18 25 113 508 3.8 503 0.9 76.05 22-Mar-18 18 23-Mar-18 15 23-Mar-18 15 23-Mar-18 25 23-Mar-18 18 23-Mar-18 25 23-Mar-18 22 23-Mar-18 15 23-Mar-18 22 23-Mar-18 18 23-Mar-18 25 24-Mar-18 25 24-Mar-18 15 24-Mar-18 22 24-Mar-18 22 24-Mar-18 22 24-Mar-18 15 24-Mar-18 18 1008 103 115 501 96.75 5.6 541.8 1009 117 509 34.55 2.4 82.92 Table name: PROJECT 1010 4.3 451.5 105 96.75 96.75 1011 105 502 328.95 193.5 270.9 PROJ BALANCE EMP NUM 1002350.00 103 2110346.00 108 500345.20 102 2309880.00 107 108 501 3.4 PROJ VALUE 1012 PROJ NUM PROJ NAME Evergreen Amber Wave Rolling Tide Starflight 115 501 1453500.00 3500500.00 805000.00 1013 1014 1015 15 104 501 96.75 2.8 18 103 503 84.5 6.1 515.45 4.7 493.5 105 34.55 22 1016 105 502 3.8 131.29 117 509 25 2650500.00 1017 117 509 34.55 2.2 76.01 1018 110.5 541.45 104 501 4.9 1019 1020 125 3.1 387.5 101 502 110.5 2.7 298.35 1021 108 110.5 4.9 541.45 115 501 1022 437.5 278.85 125 3.5 105 502 1023 84.5 3.3 103 503 1024 34.55 4.2 145.11 117 509 1025
Transcribed Image Text:FIGURE P7.1 THE CH07_CONSTRUG UCICODVADA E Relational diagram Database name: Ch07_ConstructCo Table name: EMPLOYEE EMP NUM EMP LNAME EMP FNAME EMP MTIAL I EPJREDATE JOB CODE EMP YEARS 17 ASSIGNMENT PK Assign Num 101 News John 08-Nov-00 502 12-Jul-89 501 28 Assign Date FK2 Proj Num FK1 Emp Num EMPLOYEE 102 Senior David JO5 Arbcugh 01-Dec-96 500 21 PK Emp Num 103 June PK Job Code H---O 15-Nov-87 501 30 PROJECT Assign_Job Assign_Chg_Hr Assign_Hours Assign_Charge 104 Ramoras Anne Job Description Job Chg Hour Job Last Update Emp LName Emp FName Emp initial Emp HireDate FK1 Job Code Emp Years Proi Hum 105 Johnson Alice K 01-Feb-93 502 25 22-Jun-04 500 13 Smithfield Alonzo Washington Raiph Smith 106 William Proj Name Proj Value Proj Balance FK1 Emp Num H-- 107 Maria 10-Oct-93 500 24 108 22-Aug-91 501 26 18-Jul-97 501 20 Larry Gerald 109 22 11-Dec-95 505 04-Apr-91 506 110 Olenko A 27 111 Wabash Geoff B 112 Smithson Darlene M 23-Oct-94 507 23 21 15-Nov-96 508 20-Aug-93 508 25-Jan-92 501 113 Joenbrood Delbert K 24 114 Jones Annelise Table name: JOB 26 115 Bawangi Travis B 05-Mar-97 510 21 JOB CHG HOUR JOB LAST UPDATE 20-Nov-17 Pratt Gerald JOB DESCRIPTION Programmer Systems Analyst Database Designer Bectrical Engineer Mechanical Engineer Civil Engineer Clerical Support DSS Analyst Applications Designer 116 JOB CODE 500 501 502 Williamson Angie 19-Jun-96 509 21 35.75 117 96.75 20-Nov-17 118 Frommer James J 04-Jan-05 510 13 24-Mar-18 20-Nov-17 20-Nov-17 20-Nov-17 20-Nov-17 20-Nov-17 24-Mar-18 20-Nov-17 20-Nov-17 125.00 84.50 Table name: ASSIGNMENT 503 504 505 506 507 508 509 510 67.90 ASSIGN_NUM ASSIGN_DATE PROJ_NUM EMP_NUM ASSIGN_JOB ASSIGN_CHG HR ASSIGN_HOURS ASSIGN_CHARGE 503 55.78 84.5 3.5 295.75 26.87 1001 22-Mar-18 18 103 509 34.55 4.2 145.11 22-Mar-18 22 22-Mar-18 18 45.95 1002 117 21 69.1 34.55 84.5 48.10 1003 117 509 22-Mar-18 18 103 503 5.9 498.55 34.55 1004 Bio Technician 212.85 2.2 4.2 1005 22-Mar-18 25 108 501 96.75 General Support 18.36 1006 22-Mar-18 22 104 501 96.75 406.35 192.85 50.75 84.5 1007 22-Mar-18 25 113 508 3.8 503 0.9 76.05 22-Mar-18 18 23-Mar-18 15 23-Mar-18 15 23-Mar-18 25 23-Mar-18 18 23-Mar-18 25 23-Mar-18 22 23-Mar-18 15 23-Mar-18 22 23-Mar-18 18 23-Mar-18 25 24-Mar-18 25 24-Mar-18 15 24-Mar-18 22 24-Mar-18 22 24-Mar-18 22 24-Mar-18 15 24-Mar-18 18 1008 103 115 501 96.75 5.6 541.8 1009 117 509 34.55 2.4 82.92 Table name: PROJECT 1010 4.3 451.5 105 96.75 96.75 1011 105 502 328.95 193.5 270.9 PROJ BALANCE EMP NUM 1002350.00 103 2110346.00 108 500345.20 102 2309880.00 107 108 501 3.4 PROJ VALUE 1012 PROJ NUM PROJ NAME Evergreen Amber Wave Rolling Tide Starflight 115 501 1453500.00 3500500.00 805000.00 1013 1014 1015 15 104 501 96.75 2.8 18 103 503 84.5 6.1 515.45 4.7 493.5 105 34.55 22 1016 105 502 3.8 131.29 117 509 25 2650500.00 1017 117 509 34.55 2.2 76.01 1018 110.5 541.45 104 501 4.9 1019 1020 125 3.1 387.5 101 502 110.5 2.7 298.35 1021 108 110.5 4.9 541.45 115 501 1022 437.5 278.85 125 3.5 105 502 1023 84.5 3.3 103 503 1024 34.55 4.2 145.11 117 509 1025
2. Using the EMPLOYEE, JOB, and PROJECT tables in the Ch07_ConstructCo data- base, write the SQL code that will join the EMPLOYEE and PROJECT tables using EMP_NUM as the common attribute. Display the attributes shown in the results presented in Figure P7.2, sorted by project value. FIGURE P7.2 THE QUERY RESULTS FOR PROBLEM 2 PROJ BALANCE EMP LNAME EMP FNAME EMP INITIAL JOB_CODE JOB_DESCRIPTION JOB_CHG_HOUR H. PROJ NAME PROJ_VALUE 96.75 500345.20 Senior 1002350.00 Arbough 2309880.00 Alonzo 2110346.00 Washington 501 Systems Analyst Programmer Programmer Systems Analyst David Rolling Tide Evergreen Starflight 805000.00 35.75 35.75 1453500.00 June E 500 2650500.00 Maria D 500 Ralph 501 96.75 Amber Wave 3500500.00 Write the SOL code that will produce the same information that was shown in
Transcribed Image Text:2. Using the EMPLOYEE, JOB, and PROJECT tables in the Ch07_ConstructCo data- base, write the SQL code that will join the EMPLOYEE and PROJECT tables using EMP_NUM as the common attribute. Display the attributes shown in the results presented in Figure P7.2, sorted by project value. FIGURE P7.2 THE QUERY RESULTS FOR PROBLEM 2 PROJ BALANCE EMP LNAME EMP FNAME EMP INITIAL JOB_CODE JOB_DESCRIPTION JOB_CHG_HOUR H. PROJ NAME PROJ_VALUE 96.75 500345.20 Senior 1002350.00 Arbough 2309880.00 Alonzo 2110346.00 Washington 501 Systems Analyst Programmer Programmer Systems Analyst David Rolling Tide Evergreen Starflight 805000.00 35.75 35.75 1453500.00 June E 500 2650500.00 Maria D 500 Ralph 501 96.75 Amber Wave 3500500.00 Write the SOL code that will produce the same information that was shown in
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 2 steps

SEE SOLUTIONCheck out a sample Q&A here
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
  • SEE MORE QUESTIONS
Recommended textbooks for you
Systems Architecture
Systems Architecture
Computer Science
ISBN:
9781305080195
Author:
Stephen D. Burd
Publisher:
Cengage Learning
SEE MORE TEXTBOOKS