This is as clear as the pictures can be.

I HAVE COPY & PASTED THE QUESTIONS.

Please help with as many as you can and correct answers please!

The Ch08_ConstructCo database stores data for a consulting company that tracks all charges to projects. The charges are based on the hours each employee works on each project. The structure of the database are shown in Figure P8.1.

Note that the ASSIGNMENT table in Figure P8.1 stores 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 be reflected in the ASSIGNMENT table. Naturally, the employee primary job assignment might also change, so the ASSIGN_JOB is also stored. Because those attributes are required to maintain the historical accuracy of the data, they are NOT redundant.

Given the structure and contents of the Ch08_ConstructCo database shown in Figure P8.1, use SQL commands to answer the following problems.

2. Having created the table structure shown in TABLE 8.1, write the SQL code to enter the first two rows for the table shown in Figure P8.2. Each row should be inserted individually, without using a subquery. Insert the rows in the order that they are listed in the figure.

4. Write the SQL code that will save the changes made to the EMP_1 table (if supported by your DBMS - assuming in Oracle DBMS).

6. write the SQL code to delete the row for William Smithfield, who was hired on June 22, 2004, and whose job code is 500. (Hint: use logical operators to include all of the information given in this problem.)

8. (EMP_2 table is a copy of EMP_1, please refers to Problem 7) Using EMP_2 table, write the SQL code that will add the attributes EMP_PCT and PROJ_NUM to EMP_2. The EMP_PCT is the bonus percentage to be paid to each employee. The new attribute characteristics are:

EMP_PCT NUMBER (4, 2)

PROJ_NUM CHAR(3)

10. Using the EMP_2 table, write a single SQL command to change the EMP_PCT value to 5.00 for the people with employee numbers 101, 105, and 107.

12. Using the EMP_2 table, write the SQL command to add 0.15 to the EMP_PCT of the employee whose name is Maria D. Alonzo. (Use the employee name in your command to determine the correct employee.)

14. Using a single command sequence with the EMP_2 table, write the SQL code that will change the project number (PROJ_NUM) to 25 for all employees whose job classification (JOB_CODE) is 500.

Thanks in advance!

board conttnturse 20367 18content.d:595846 18mode reset The Chol ConsuCo databseres s dats tor a consulting company that bracks all charges to projecdts The charges are based on the hours each employee works on each project The structure of the database are shown in a FIGURE P8.1 THE CH08 CONSTRUCTCO DATABASE Table name: Relational diagram .coo OB DESCRIPTION ASSIGN NUM SSIGN DATE 102 ,LAST UPDATE Ramoras ASSGPLCHG.HR ASSIGN-HOURS ASSG CHARGE 105 PROLNU PROL NAME PROL,VALUE PROLBALANCE 109 EMP,NUM Smithfiel Alonzo Washingto 108 Smith EMP HOREDATE 08,CODE EMP,YEARS Joenbrood Table name: JOB 116 Pratt 20-Now-17 20-Nov-17 24-Mar-16 20-Nov-17 20-Nov-17 20-Nov-17 20-Nov-17 20-Nov-17 24-Mar-18 20-Now-117 20-Now-117 500 3575 96 75 125.00 84 50 67 90 55 78 26 87 45 95 Systems Anslyst Database Designer Frommer Electrical Engineer Mechanical Engineer Ci Engineer Clerical Suppot DSS Analyst Applications Designer Bio Technicisn General Support Table name: ASSIGNMEN ASSION NM ASSION DATE PROJ NUM EMP 506 1001 1002 22-Mar-18 22 1003 22-Mar 18 18 004 22-Mar-18 18 22-Mar-18 18 3455 8.36 1005 22-Mar-18 25 006 22-Mar-18 22 1007 008 22-Mar-18 18 1009 23-Mar 18 1 22-Mar-18 25 103 Table name: PROJECT 1010 23-Mar-18 15 1011 23-Mar-18 25 1012 23-Mar-18 18 1013 23-Mar-18 25 01423-Mar-18 22 PROJJUM PROJ NAME I pROJ-VALUE | PROJEALANCE EMPJUMI 105 108 Evergreen Amber Vave Rolling Tide Starflight 1453500 00 3500500 00 05000 00 2650500 00 1002350 00 103 2110346 00 108 500345 20 102 2309880 00 107 104 1015 23-Mar-18 15 1016 23". 18 22 101723-Mar-18 18 1016 23Mar-18 25 101924-Mar-18 25 1020 105 Database name: Ch08 ConstructCo Table name: EMPLOYEE EMP NUM EN LINAME EP FNAME ENP NITIAL EMP HREDATE JOB CODE MP YEARS 08-Nov-00 502 12-Jul-89 501 01-Dec-98 500 15-Noy-87 501 01-Feb-93 502 ArboughJune Ramoras Anne Johnson Alce Smithfield Williem PROIL NAME PRO.VALUEI 108 Weshington Ralh PROL BALANCE 109 18-ul-97 501 Gerald Smithson Darlene Joenbrood Delbert 04-Apr-91 506 23-Oct-94 507 15-Nov-96 508 Bawangi Travis Pratt Willamson Ange 25-Jan-92 501 05-Mar-97 510 19-Jun-96 509 04-Jan-05 510 Geraild Table name: ASSIGNMENT ASSION NM ASSION DATE PROJ NM EXP NM ASSION JB ASSION CHOR ASSON HOURS ASSION CHARGE 22-Mar-18 18 1002 22-Mar-18 22 1003 22-Mar-18 18 1004 22-Mar-18 18 00522-Mar-18 25 1006 2241.18 22 00722-Mar-18 25 100822-Mar-18 18 100923-Mar-18 15 101023-Mar-18 15 1011 23-Mar-18 25 1012 23-Mar-18 18 1013 23-Mar-18 25 1014 23 Mar-18 22 1015 23-Mar 18 15 1016 23-Mar-18 22 1017 23-Mar-18 16 1018 23-Mar-18 25 24-Mer-18 25 1020 24M 18 15 1021 24-Mar-18 22 1022 24-Mar-18 22 1023 24-Mar-18 22 1024 24-Mar-18 15 84 50 34.55 117 50 117 09 45.11 496.55 212.85 6.75 50 75 84 50 113 6 03 503 117509 76.05 541 80 82 92 105 502 105 96 75 328 95 193.50 270 90 515.45 493 50 31 29 76.01 103503 105 502 105 00 117 104 509 501 110 50 125 00 387 50 298 35 10850 025 24Mar-18 1 84 50 34 55 437 50 278 85 ro Pl 1 stores the JOB CHG HOUR values as an attribude (ASSIGN CHG HRI to maintain historical accuracy of the data The JOB CHG HOUR values are ely to change over time table Natra the employee pin ary bassign ent might also change, so the ASSIGN JOBS also stored Beas those attributes are required to ohe that the ASSIGNMENT table in Figu aintain the historical accuracy of the data, they are NOT redundant Siven the structure and contents of the Ch0B ConstruCo database 2 Having created Be table structure shown in TABLE & 1, write the SQL code to enter the fest too rows shown in Figure P 1, use SOL commands to answer the following problem for the table shown in Figure P82 Each row should be inceited individually, without using a subquery Insert the rows i the order that they are sted in the figure TABLE P8.1 CONSTRUCTCO DATABASE STRUCTURE ATTRIBUTE (FIELD) NAME EMP NUM EMP LNAME EMP FNAME EMP INITIAL EMP HIREDATE JOB_CODE DATA DECLARATION CHAR(3) VARCHAR(15) VARCHAR(15) CHAR(1) DATE CHAR(3) FIGURE P8.2 THE CONTENTS OF THE EMP 1 TABLE EMP NUM EMP LNAME EMP FNAME EMP NTIAL EMP HREDATE JOB CODE 08-Nov-00 502 1 12-Jul-89 501 101 News 102 103 Arbough June 104 105 106 Smithfield Miliam 107 108 Washington Ralph David 01-Dec-96 500 15-Nov-87 501 01-Feb-93 502 22-Jun-04 500 10-Oct-93 500 22-Aug-91 501 18-Jul-97 501 Ramoras Johnson Anne Alice Alonzo Smith Wis the SOL cod sav the changes made to the EMP 1 tbepoted by your DEMS auing in Oracle OUMS) o necude all f inlmatin glinis proble OJNUM to EMP 2 The EMP PCT is the bonus percentage to be paid t EMP pl ers to Problem Using EMP2tlw the SQL code hat wil EMP PCT and PRO ch emloyee The n MP PCTNUMDER42 PRONUM CHARS 10 Using te EMP 2 table ing SOL comad o change the EMP PCT vae to 5 00f the people with mployee nambers ae in your command to demise the correct amployee 4 Using a sing comand seene h th EMp 2 table wite the SQL.cods that vil chae the project nmber (PROU NUM) to 25 for all mployess whose job clasiicaion (JOB CODE) is 800 ro Pl 1 stores the JOB CHG HOUR values as an attribude (ASSIGN CHG HRI to maintain historical accuracy of the data The JOB CHG HOUR values are ely to change over time table Natra the employee pin ary bassign ent might also change, so the ASSIGN JOBS also stored Beas those attributes are required to ohe that the ASSIGNMENT table in Figu aintain the historical accuracy of the data, they are NOT redundant Siven the structure and contents of the Ch0B ConstruCo database 2 Having created Be table structure shown in TABLE & 1, write the SQL code to enter the fest too rows shown in Figure P 1, use SOL commands to answer the following problem for the table shown in Figure P82 Each row should be inceited individually, without using a subquery Insert the rows i the order that they are sted in the figure TABLE P8.1 CONSTRUCTCO DATABASE STRUCTURE ATTRIBUTE (FIELD) NAME EMP NUM EMP LNAME EMP FNAME EMP INITIAL EMP HIREDATE JOB_CODE DATA DECLARATION CHAR(3) VARCHAR(15) VARCHAR(15) CHAR(1) DATE CHAR(3) FIGURE P8.2 THE CONTENTS OF THE EMP 1 TABLE EMP NUM EMP LNAME EMP FNAME EMP NTIAL EMP HREDATE JOB CODE 08-Nov-00 502 1 12-Jul-89 501 101 News 102 103 Arbough June 104 105 106 Smithfield Miliam 107 108 Washington Ralph David 01-Dec-96 500 15-Nov-87 501 01-Feb-93 502 22-Jun-04 500 10-Oct-93 500 22-Aug-91 501 18-Jul-97 501 Ramoras Johnson Anne Alice Alonzo Smith Wis the SOL cod sav the changes made to the EMP 1 tbepoted by your DEMS auing in Oracle OUMS) o necude all f inlmatin glinis proble OJNUM to EMP 2 The EMP PCT is the bonus percentage to be paid t EMP pl ers to Problem Using EMP2tlw the SQL code hat wil EMP PCT and PRO ch emloyee The n MP PCTNUMDER42 PRONUM CHARS 10 Using te EMP 2 table ing SOL comad o change the EMP PCT vae to 5 00f the people with mployee nambers ae in your command to demise the correct amployee 4 Using a sing comand seene h th EMp 2 table wite the SQL.cods that vil chae the project nmber (PROU NUM) to 25 for all mployess whose job clasiicaion (JOB CODE) is 800 FIGURE P8.1 THE CH08 CONSTRUCTCO DATABASE Table name: Relational diagram .coo OB DESCRIPTION ASSIGN NUM SSIGN DATE 102 ,LAST UPDATE Ramoras ASSGPLCHG.HR ASSIGN-HOURS ASSG CHARGE 105 PROLNU PROL NAME PROL,VALUE PROLBALANCE 109 EMP,NUM Smithfiel Alonzo Washingto 108 Smith EMP HOREDATE 08,CODE EMP,YEARS Joenbrood Table name: JOB 116 Pratt 20-Now-17 20-Nov-17 24-Mar-16 20-Nov-17 20-Nov-17 20-Nov-17 20-Nov-17 20-Nov-17 24-Mar-18 20-Now-117 20-Now-117 500 3575 96 75 125.00 84 50 67 90 55 78 26 87 45 95 Systems Anslyst Database Designer Frommer Electrical Engineer Mechanical Engineer Ci Engineer Clerical Suppot DSS Analyst Applications Designer Bio Technicisn General Support Table name: ASSIGNMEN ASSION NM ASSION DATE PROJ NUM EMP 506 1001 1002 22-Mar-18 22 1003 22-Mar 18 18 004 22-Mar-18 18 22-Mar-18 18 3455 8.36 1005 22-Mar-18 25 006 22-Mar-18 22 1007 008 22-Mar-18 18 1009 23-Mar 18 1 22-Mar-18 25 103 Table name: PROJECT 1010 23-Mar-18 15 1011 23-Mar-18 25 1012 23-Mar-18 18 1013 23-Mar-18 25 01423-Mar-18 22 PROJJUM PROJ NAME I pROJ-VALUE | PROJEALANCE EMPJUMI 105 108 Evergreen Amber Vave Rolling Tide Starflight 1453500 00 3500500 00 05000 00 2650500 00 1002350 00 103 2110346 00 108 500345 20 102 2309880 00 107 104 1015 23-Mar-18 15 1016 23". 18 22 101723-Mar-18 18 1016 23Mar-18 25 101924-Mar-18 25 1020 105