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 pri mary key Note that the JOB CODE is the FK to JOB so be certain to enforce ref erential integrity Your code should also prevent null entries in EMP LNAME and EMP FNAME 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 indi vidually, without using a subquery Insert the rows in the order that they are listed in the figure Using the EMPLOYEE table that already exists, use a subquery to insert the remain ing rows from the EMPLOYEE table into the EMP 1 table Remember, your sub query should only retrieve the columns needed for the EMP 1 table and only the employees shown in the figure Write the SQL code that will save the changes made to the EMP 1 table (if sup ported by your DBMS) Write the SQL code to change the job code to 501 for the person whose employee number (EMP NUM) is 107 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 ) TABLE P8 1 CONSTRUCTCO DATABASE STRUCTURE ATTRIBUTE (FIELD) NAME DATA DECLARATION EMPNUM CHAR(3) EMP LNAME VARCHAR(15) EMP FNAME VARCHAR(15) EMP INITIAL CHAR(1) EMP HIREDATE DATE JOB CODE CHAR(3) FIGURE P8 2 THE CONTENTS OF THE EMP 1 TABLE EMP NUM EMP LNAME 101 News 102 Senior 103 Arbough 104 Ramoras 105 Johnson 106 Smithfield 107 Alonzo 108 Washington 109 Smith EMP FNAME EMP INITIAL John G David H June E Anne K Alice K William Maria D Ralph B Larry w EMP HIREDATE JOB CODE 08 Nov 00 502 12 Jul 89 501 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

The Answer is in the image, click to view ...

Question: 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

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 pri-

mary key. Note that the JOB_CODE is the FK to JOB so be certain to enforce ref-

erential integrity. Your code should also prevent null entries in EMP_LNAME and

EMP_FNAME.

Write the SQL code that will create only the table structure

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 indi-

vidually, without using a subquery. Insert the rows in the order that they are listed

in the figure.

for a table named EMP_1. This table will be a subset of

Using the EMPLOYEE table that already exists, use a subquery to insert the remain-

ing rows from the EMPLOYEE table into the EMP_1 table. Remember, your sub-

query should only retrieve the columns needed for the EMP_1 table and only the

employees shown in the figure.

Write the SQL code that will save the changes made to the EMP_1 table (if sup-

ported by your DBMS).

Write the SQL code to change the job code to 501 for the person whose employee

number (EMP_NUM) is 107.

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.)

TABLE P8.1 CONSTRUCTCO DATABASE STRUCTURE ATTRIBUTE (FIELD) NAME DATA DECLARATION EMPNUM CHAR(3) EMP_LNAME VARCHAR(15) EMP_FNAME VARCHAR(15) EMP_INITIAL CHAR(1) EMP_HIREDATE DATE JOB_CODE CHAR(3) FIGURE P8.2 THE CONTENTS OF THE EMP_1 TABLE EMP_NUM EMP_LNAME 101 News 102 Senior 103 Arbough 104 Ramoras 105 Johnson 106 Smithfield 107 Alonzo 108 Washington 109 Smith EMP_FNAME EMP_INITIAL John G David H June E Anne K Alice K William Maria D Ralph B Larry w EMP_HIREDATE JOB_CODE 08-Nov-00 502 12-Jul-89 501 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

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!

Create and submit a sql script to answer all the questions. Make sure to add comments in the document to add title for each question. Use Chapter 8 database script to create your database that you...

P 1 . Write the SQL code that will create only the table structure for a table named EMP _ 1 . This table P 7 . Using the EMP _ 2 table, write the SQL code that will add the attributes EMP _ PCT and...

4. The 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 and...

The 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 and contents...

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...

1 . Write the SQL code that will create only the table structure for a table named EMP _ 1 . The basic table structure is summarized in the following table. Use EMP _ NUM as the primary key ( 1 5...

Write the SQL code that will create only the table structure for a table nam ed EMPLOYEE. The basic table structure is summ anzed in the following table. Use EMP.NUM as the prim ary key. Your code...

For how to: https://www.freecodecamp.org/news/how-to-install-mysql-workbench-on-windows/ Take a screen shots of your installation and paste here Watch this tutorial Section on how to use MYSQL work...

The 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. proj _ Darance a ) Write the...

Repeat Exercise 6.32 using ML estimators. Let X, Y, and Z be the random vectors. (a) Find the LMMSE estimator of given {Y= y, Z= z}. (b) Find the LMMSE estimator of given {Y= x, Z= z}. (c) Find the...

Techcheng Prahok Trading and Export (Cambodia) Pte. Ltd. provided you with the following data: Year 2021 Capital, 1 January Sales revenue Cost of goods sold Operating expenses Interest expense...

BE6.1 (LO 1) Kraft Enterprises owns the following assets at December 31, 2025. Cash in banksavings account 68,000 Cash on hand 9,300 Tax refund due 31,400 Checking account balance 17,000 Postdated...

ment Practice Question 12 Which of the following did not result from the Sarbanes-Oxley Act? O Tax rates on corporations increased. O Top management must now certify the accuracy of financial...

9. Power and politics can be destructive forces in organizations.

6. To maintain power, you should not compromise, even when an issue is of minor importance to you.

10. If you have good relationships with the people in your unit, then it is not necessary to have good relationships with people in other parts and levels of the organization.