1. Write the SQL code that will create only the table structure for a table named...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
1. Write the SQL code that will create only the table structure for a table named EMP_1. This table will be a subset of the EMPLOYEE table. The basic EMP_1 table structure is summarized in the following table. Use EMP_NUM as the primary key. Note that the JOB_CODE is the FK to JOB so be certain to enforce referential integrity. Your code should also prevent null entries in EMP_LNAME and EMP_FNAME. Relational diagram JOB 500 501 502 503 504 505 506 507 508 Table name: JOB JOB_CODE JOB DESCRIPTION Programmer Systems Analyst Database Designer Electrical Engineer Mechanical Engineer 509 510 JOB CODE JOB DESCRIPTION JOB_CHG_HOUR JOB_LAST_UPDATE EMPLOYEE EMP NUM EMP_LNAME 15 18 22 25 Civil Engineer Clerical Support DSS Analyst Applications Designer Bio Technician General Support EMP_FNAME EMP INITIAL EMP HIREDATE JOB_CODE EMP YEARS JOB_CHO_HOUR 35.75 96.75 125.00 84.50 67.90 55.78 26.87 45.95 48.10 34.55 18.36 Table name: PROJECT PROJ_NUM PROJ_NAME PROJ_VALUE Evergreen 1453500.00 Amber Wave Rolling Tide 3500500.00 805000.00 2650500.00 Starflight ASSIGNMENT ASSIGN_NUM ASSIGN DATE PROJ_NUM EMP_NUM ASSIGN JOB ASSIGN_CHG_HR ASSIGN HOURS ASSIGN_CHARGE JOB_LAST_UPDATE | 20-Nov-17 20-Nov-17 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 DO PROJ_BALANCE EMP_NUM 1002350.00 103 2110346.00 108 500345.20 102 2309880.00 107 PROJECT PROJ NUM PROJ_NAME PROJ_VALUE PROJ_BALANCE EMP NUM 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 Table name: EMPLOYEE EMP_NUM EMP_LNAME EMP_FNAME EMP_INITIAL EMP_HIREDATE JOB_CODE EMP_YEARS 08-Nov-00 502 12-Jul-89 501 01-Dec-96 500 15-Nov-87 501 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 News Senior John David June Anne Alice William Maria Washington Ralph Larry Gerald Geoff Darlene Delbert Annelise Travis Gerald Angie James Arbough Ramoras Johnson Smithfield Alonzo Smith Olenko Wabash Smithson Joenbrood Jones Bawangi Pratt Williamson Frommer 22-Mar-18 18 22-Mar-18 22 22-Mar-18 18 22-Mar-18 18 22-Mar-18 25 22-Mar-18 22 22-Mar-1B 25 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-10 15 24-Mar-18 18 103 117 117 103 108 104 113 103 115 117 105 108 115 104 103 105 117 117 104 101 108 115 105 103 117 Table name: ASSIGNMENT ASSIGN NUM ASSIGN_DATE PROJ_NUM EMP_NUM ASSIGN_JOB ASSIGN_CHG_HR ASSIGN HOURS ASSIGN_CHARGE 1001 503 509 509 503 501 501 500 503 501 509 502 501 501 501 503 502 Database name: Ch08_ConstructCo 509 509 501 502 501 501 502 503 509 G H E K K D B W A 8 M K B L H J 84.50 34.55 34.55 84.50 93.75 96.75 50.75 84.50 96.75 34.55 105.00 96.75 95.75 96.75 84.50 105.00 34.55 34.55 110.50 125.00 110.50 110.50 125.00 01-Feb-93 502 22-Jun-04 500 10-Oct-93 500 84.50 34.55 22-Aug-91 501 18-Jul-97 501 11-Dec-95 505 04-Apr-91 506 23-Oct-94 507 15-Nov-96 508 20-Aug-93 508 25-Jan-92 501 05-Mar-97 510 19-Jun-96 509 04-Jan-05 510 3.5 4.2 2.0 5.9 2.2 4.2 3.8 0.9 5.6 2.4 4.3 3.4 2.0 2.8 6.1 4.7 3.8 2.2 4.9 3.1 2.7 4.9 3.5 3.3 4.2 295.75 145.11 69.10 498.55 212.05 406.35 192.85 76.05 541.80 82.92 451.50 328.95 193.50 270.90 515.45 493.50 131.29 76.01 541.45 387.50 298.35 541.45 437.50 270.05 145.11 12 23 18 25 19 8 19 21 15 17 21 18 16 19 20 15 16 7 8. Using the 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 PROJ_NUM NUMBER(4,2) CHAR(3) Note: If your SQL implementation requires it, you may use DECIMAL(4,2) or NUMERIC(4,2) rather than NUMBER(4,2). 9. Using the EMP_2 table, write the SQL code to change the EMP_PCT value to 3.85 for the person whose employee number (EMP_NUM) is 103. 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. 11. Using the EMP_2 table, write a single SQL command to change the EMP_PCT value to 10.00 for all employees who do not currently have a value for EMP_PCT. 12. Using the EMP_2 table, write the SQL command to add .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.) 13. Using a single command sequence with the EMP_2 table, write the SQL code that will change the project number (PROJ_NUM) to 18 for all employees whose job classification (JOB_CODE) is 500. 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 502 or higher. 1. Write the SQL code that will create only the table structure for a table named EMP_1. This table will be a subset of the EMPLOYEE table. The basic EMP_1 table structure is summarized in the following table. Use EMP_NUM as the primary key. Note that the JOB_CODE is the FK to JOB so be certain to enforce referential integrity. Your code should also prevent null entries in EMP_LNAME and EMP_FNAME. 2. Having created the table structure in Problem 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. Figure P8.2 The Contents of the EMP_1 Table EMP_NUM EMP_LNAME EMP_FNAME EMP_INITIAL EMP_HIREDATE JOB_CODE 08-Nov-00 502 12-Jul-89 501 01-Dec-96 500 15-Nov-87 501 01-Feb-93 502 101 102 103 104 105 106 107 108 109 News Senior John David June Anne Alice William Maria Washington Ralph Smith Larry Arbough Ramoras Johnson Smithfield Alonzo G XXMI H E K K D B W 22-Jun-04 500 10-Oct-93 500 22-Aug-91 501 18-Jul-97 501 3. Using the EMPLOYEE table that already exists, use a subquery to insert the remaining rows from the EMPLOYEE table into the EMP_1 table. Remember, your subquery should only retrieve the columns needed for the EMP_1 table and only the employees shown in the figure. 4. Write the SQL code that will save the changes made to the EMP_1 table (if supported by your DBMS). 5. Write the SQL code to change the job code to 501 for the person whose employee number (EMP_NUM) is 107. 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. Remember, if you are using MySQL, you will have to first disable "safe mode.") 7. Write the SQL code to create a copy of EMP_1, including all of its data, and naming the copy EMP_2. 15. Write the SQL code that will change the PROJ_NUM to 14 for employees who were hired before January 1, 1994, and whose job code is at least 501. When you finish Problems 7, 8, 9, 10, 11, 12, 13, 14, and 15, the EMP_2 table will contain the data shown in Figure P8.15. Figure P8.15 The EMP_2 Table Contents EMP_NUM EMP_LNAME EMP_FNAME EMP_INITIAL 101 News Senior 102 103 104 105 107 108 109 Arbough Ramoras Johnson Alonzo Washington Smith John David June Anne Alice Maria Ralph Larry G H E K K D B W EMP_HIREDATE JOB_CODE EMP_PCT | PROJ_NUM + 5 25 10 14 08-Nov-00 502 12-Jul-89 501 01-Dec-96 500 15-Nov-87 501 01-Feb-93 502 10-Oct-93 501 22-Aug-91 501 18-Jul-97 501 3.85 18 10 14 5 14 5.15 14 10 14 10 Table P8.1 Constructco Database Structure ATTRIBUTE (FIELD) DATA DECLARATION NAME EMP_NUM EMP_LNAME EMP_FNAME EMP_INITIAL EMP_HIREDATE JOB_CODE CHAR(3) VARCHAR(15) VARCHAR(15) CHAR(1) DATE CHAR(3) 1. Write the SQL code that will create only the table structure for a table named EMP_1. This table will be a subset of the EMPLOYEE table. The basic EMP_1 table structure is summarized in the following table. Use EMP_NUM as the primary key. Note that the JOB_CODE is the FK to JOB so be certain to enforce referential integrity. Your code should also prevent null entries in EMP_LNAME and EMP_FNAME. Relational diagram JOB 500 501 502 503 504 505 506 507 508 Table name: JOB JOB_CODE JOB DESCRIPTION Programmer Systems Analyst Database Designer Electrical Engineer Mechanical Engineer 509 510 JOB CODE JOB DESCRIPTION JOB_CHG_HOUR JOB_LAST_UPDATE EMPLOYEE EMP NUM EMP_LNAME 15 18 22 25 Civil Engineer Clerical Support DSS Analyst Applications Designer Bio Technician General Support EMP_FNAME EMP INITIAL EMP HIREDATE JOB_CODE EMP YEARS JOB_CHO_HOUR 35.75 96.75 125.00 84.50 67.90 55.78 26.87 45.95 48.10 34.55 18.36 Table name: PROJECT PROJ_NUM PROJ_NAME PROJ_VALUE Evergreen 1453500.00 Amber Wave Rolling Tide 3500500.00 805000.00 2650500.00 Starflight ASSIGNMENT ASSIGN_NUM ASSIGN DATE PROJ_NUM EMP_NUM ASSIGN JOB ASSIGN_CHG_HR ASSIGN HOURS ASSIGN_CHARGE JOB_LAST_UPDATE | 20-Nov-17 20-Nov-17 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 DO PROJ_BALANCE EMP_NUM 1002350.00 103 2110346.00 108 500345.20 102 2309880.00 107 PROJECT PROJ NUM PROJ_NAME PROJ_VALUE PROJ_BALANCE EMP NUM 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 Table name: EMPLOYEE EMP_NUM EMP_LNAME EMP_FNAME EMP_INITIAL EMP_HIREDATE JOB_CODE EMP_YEARS 08-Nov-00 502 12-Jul-89 501 01-Dec-96 500 15-Nov-87 501 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 News Senior John David June Anne Alice William Maria Washington Ralph Larry Gerald Geoff Darlene Delbert Annelise Travis Gerald Angie James Arbough Ramoras Johnson Smithfield Alonzo Smith Olenko Wabash Smithson Joenbrood Jones Bawangi Pratt Williamson Frommer 22-Mar-18 18 22-Mar-18 22 22-Mar-18 18 22-Mar-18 18 22-Mar-18 25 22-Mar-18 22 22-Mar-1B 25 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-10 15 24-Mar-18 18 103 117 117 103 108 104 113 103 115 117 105 108 115 104 103 105 117 117 104 101 108 115 105 103 117 Table name: ASSIGNMENT ASSIGN NUM ASSIGN_DATE PROJ_NUM EMP_NUM ASSIGN_JOB ASSIGN_CHG_HR ASSIGN HOURS ASSIGN_CHARGE 1001 503 509 509 503 501 501 500 503 501 509 502 501 501 501 503 502 Database name: Ch08_ConstructCo 509 509 501 502 501 501 502 503 509 G H E K K D B W A 8 M K B L H J 84.50 34.55 34.55 84.50 93.75 96.75 50.75 84.50 96.75 34.55 105.00 96.75 95.75 96.75 84.50 105.00 34.55 34.55 110.50 125.00 110.50 110.50 125.00 01-Feb-93 502 22-Jun-04 500 10-Oct-93 500 84.50 34.55 22-Aug-91 501 18-Jul-97 501 11-Dec-95 505 04-Apr-91 506 23-Oct-94 507 15-Nov-96 508 20-Aug-93 508 25-Jan-92 501 05-Mar-97 510 19-Jun-96 509 04-Jan-05 510 3.5 4.2 2.0 5.9 2.2 4.2 3.8 0.9 5.6 2.4 4.3 3.4 2.0 2.8 6.1 4.7 3.8 2.2 4.9 3.1 2.7 4.9 3.5 3.3 4.2 295.75 145.11 69.10 498.55 212.05 406.35 192.85 76.05 541.80 82.92 451.50 328.95 193.50 270.90 515.45 493.50 131.29 76.01 541.45 387.50 298.35 541.45 437.50 270.05 145.11 12 23 18 25 19 8 19 21 15 17 21 18 16 19 20 15 16 7 8. Using the 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 PROJ_NUM NUMBER(4,2) CHAR(3) Note: If your SQL implementation requires it, you may use DECIMAL(4,2) or NUMERIC(4,2) rather than NUMBER(4,2). 9. Using the EMP_2 table, write the SQL code to change the EMP_PCT value to 3.85 for the person whose employee number (EMP_NUM) is 103. 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. 11. Using the EMP_2 table, write a single SQL command to change the EMP_PCT value to 10.00 for all employees who do not currently have a value for EMP_PCT. 12. Using the EMP_2 table, write the SQL command to add .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.) 13. Using a single command sequence with the EMP_2 table, write the SQL code that will change the project number (PROJ_NUM) to 18 for all employees whose job classification (JOB_CODE) is 500. 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 502 or higher. 1. Write the SQL code that will create only the table structure for a table named EMP_1. This table will be a subset of the EMPLOYEE table. The basic EMP_1 table structure is summarized in the following table. Use EMP_NUM as the primary key. Note that the JOB_CODE is the FK to JOB so be certain to enforce referential integrity. Your code should also prevent null entries in EMP_LNAME and EMP_FNAME. 2. Having created the table structure in Problem 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. Figure P8.2 The Contents of the EMP_1 Table EMP_NUM EMP_LNAME EMP_FNAME EMP_INITIAL EMP_HIREDATE JOB_CODE 08-Nov-00 502 12-Jul-89 501 01-Dec-96 500 15-Nov-87 501 01-Feb-93 502 101 102 103 104 105 106 107 108 109 News Senior John David June Anne Alice William Maria Washington Ralph Smith Larry Arbough Ramoras Johnson Smithfield Alonzo G XXMI H E K K D B W 22-Jun-04 500 10-Oct-93 500 22-Aug-91 501 18-Jul-97 501 3. Using the EMPLOYEE table that already exists, use a subquery to insert the remaining rows from the EMPLOYEE table into the EMP_1 table. Remember, your subquery should only retrieve the columns needed for the EMP_1 table and only the employees shown in the figure. 4. Write the SQL code that will save the changes made to the EMP_1 table (if supported by your DBMS). 5. Write the SQL code to change the job code to 501 for the person whose employee number (EMP_NUM) is 107. 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. Remember, if you are using MySQL, you will have to first disable "safe mode.") 7. Write the SQL code to create a copy of EMP_1, including all of its data, and naming the copy EMP_2. 15. Write the SQL code that will change the PROJ_NUM to 14 for employees who were hired before January 1, 1994, and whose job code is at least 501. When you finish Problems 7, 8, 9, 10, 11, 12, 13, 14, and 15, the EMP_2 table will contain the data shown in Figure P8.15. Figure P8.15 The EMP_2 Table Contents EMP_NUM EMP_LNAME EMP_FNAME EMP_INITIAL 101 News Senior 102 103 104 105 107 108 109 Arbough Ramoras Johnson Alonzo Washington Smith John David June Anne Alice Maria Ralph Larry G H E K K D B W EMP_HIREDATE JOB_CODE EMP_PCT | PROJ_NUM + 5 25 10 14 08-Nov-00 502 12-Jul-89 501 01-Dec-96 500 15-Nov-87 501 01-Feb-93 502 10-Oct-93 501 22-Aug-91 501 18-Jul-97 501 3.85 18 10 14 5 14 5.15 14 10 14 10 Table P8.1 Constructco Database Structure ATTRIBUTE (FIELD) DATA DECLARATION NAME EMP_NUM EMP_LNAME EMP_FNAME EMP_INITIAL EMP_HIREDATE JOB_CODE CHAR(3) VARCHAR(15) VARCHAR(15) CHAR(1) DATE CHAR(3)
Expert Answer:
Answer rating: 100% (QA)
Answer According to the question need to write a sql code which is used for ... View the full answer
Related Book For
Database Systems Design Implementation and Management
ISBN: 978-1285196145
11th edition
Authors: Carlos Coronel, Steven Morris
Posted Date:
Students also viewed these programming questions
-
Analyze whether you believe the Dobbs case was correctly decided and discuss which of the opinions in the Dobbs case (majority, concurring, dissenting) is correct in your personal opinion and why. Be...
-
Address how and in what ways legal research and writing should glorify God. Thoughtful analysis, required citations, biblical worldview, and examples must be used throughout the thread to support the...
-
For the conceptual schemata of the database, establish an environment for the execution of simple and advanced queries in Oracle. The global schemata is as below EMP (ENUM, NAME, SAL, TAX, MGR, DNUM)...
-
A city starts a solid waste landfill in 2012. When the landfill was opened the city estimated that it would fill to capacity within 5 years and that the cost to cover the facility would be $1.5...
-
A student needs to measure the drag on a prototype of characteristic length dp moving at velocity Up in air at sea-level conditions. He constructs a model of characteristic length dm, such that the...
-
Which project would you choose if the opportunity cost of capital were 16%? Year Project A Project B -$200 100 100 100 0 $200 80 80 80 80 4
-
The clutch used in mopeds is (a) single plate clutch (b) multi-plate clutch (c) cone clutch (d) centrifigal clutch
-
In this problem, we continue the accounting for Fitness Equipment Doctor, Inc., from Chapter 3. On June 1, Fitness Equipment Doctor, Inc., expanded its business and began selling and installing gym...
-
On Monday, April 5, 2010, just before 3:00 in the afternoon, miners at Massey Energy Corporation's Upper Big Branch coal mine in southern West Virginia were in the process of a routine shift change....
-
This case study explores two networks, an outsourced group of housing benefit caseworkers and production workers in a specialist chemicals company, and considers the effect that each network had on...
-
The connection between cost and production in the short run do you understand it? Marginal cost falls and then rises in the short run, as production is increased from a low level to a higher level....
-
Solve the following equation: 6x + 4x7 - 24 x 16 x5 0 =
-
Apple currently trades at $576. You bought a call option on Apple stock with a strike price of $571 for $3.3 three months ago, which expires today. Part1 What is the payoff? Part2 What is your total...
-
5. A short reinforced column of section 300 mm x 400 mm is to be reinforced with 6 nos. of steel bars and is required to carry an axial load of 1350 kN. The stress in the concrete must not exceed 8...
-
The difference between the VaR and historical CVaR is very huge and the worst drawdown exceeds the VaR estimate 432 times in a period of 10 years. What are the impacts to the risk management decision...
-
Please provide a description of explicit and implicit financing cost implications associated with a venture's need for additional funds. How is the cost of equity capital for private ventures...
-
A patient had a bilateral strabismus surgery involving the medial and lateral rectus muscles. The surgeon explored and repaired a detached extraocular muscle in the right eye and placed bilateral...
-
Given the table below, about how much force does the rocket engine exert on the 4.0 kg payload? Distance traveled with rocket engine firing (m) Payload final velocity (m/s) 500 320 490 310 1020 450...
-
What is an Oracle sequence? Write its syntax.
-
Create the ERD to show the relationships among EMPLOYEE, BENEFIT, JOB, and PLAN.
-
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...
-
Describe the equitable parent doctrine and explain how it was applied by the Wisconsin Supreme Court in Ay val.
-
Explain what it means to disestablish paternity. Identify some of the common arguments in favor of and against disestablishment.
-
Identify three circumstances in which assisted reproductive technology is most likely to be utilized as a reproductive option.
Study smarter with the SolutionInn App