Chapter 7 Only Question 1 And 7 Need To Be Completed For Cha

Chapter 7only Question 1 And 7 Need To Be Completed For Chapter 7 Th

Write the SQL code that will create the table structure for a table named EMP_1. This table is a subset of the EMPLOYEE table. The basic EMP_1 table structure is summarized in the following table. (Note that the JOB_CODE is the FK to JOB.)

Write the SQL code to change the job code to 501 for the person whose employee number (EMP_NUM) is 107. After you have completed the task, examine the results and then reset the job code to its original value.

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

Write the SQL code that will restore the data to its original status; that is, the table should contain the data that existed before you made the changes in Problems 5 and 6.

Sample Paper For Above instruction

Creating a subset table and manipulating data within its structure are fundamental SQL operations used in database management. In this context, the tasks involve creating a new table structure, modifying specific data entries, deleting a particular row, and finally restoring the original data to maintain database integrity. This sequence ensures a comprehensive understanding of SQL commands such as CREATE TABLE, UPDATE, DELETE, and ROLLBACK or data restoration techniques.

Creating the EMP_1 Table Structure

The first step involves creating the EMP_1 table, which is a subset of the employee data. The table structure must include relevant attributes, ensuring the job code (JOB_CODE) functions as a foreign key referencing the JOB table. Assuming the EMPLOYEE table has attributes like EMP_NUM, EMP_NAME, HIRE_DATE, JOB_CODE, DEPARTMENT, and SALARY, the SQL for creating EMP_1 might look like:

CREATE TABLE EMP_1 (

EMP_NUM INT PRIMARY KEY,


EMP_NAME VARCHAR(100),


HIRE_DATE DATE,


JOB_CODE INT,


DEPARTMENT VARCHAR(50),


SALARY DECIMAL(10, 2),


FOREIGN KEY (JOB_CODE) REFERENCES JOB(JOB_CODE)


);

Updating the Job Code for Employee 107

Next, to change the job code to 501 for the employee with EMP_NUM 107, the SQL statement is:

UPDATE EMP_1 SET JOB_CODE = 501 WHERE EMP_NUM = 107;

After verifying the change, it is essential to reset the job code to its original value, which requires knowing the previous JOB_CODE. Assuming the original is, for example, 500, the reset command is:

UPDATE EMP_1 SET JOB_CODE = 500 WHERE EMP_NUM = 107;

Deleting William Smithfield's Record

To delete William Smithfield's record, given she was hired on June 22, 2004, and her JOB_CODE is 500, the SQL command is:

DELETE FROM EMP_1 WHERE EMP_NAME = 'William Smithfield' AND HIRE_DATE = '2004-06-22' AND JOB_CODE = 500;

This uses logical operators to ensure precise deletion based on multiple conditions.

Restoring the Original Data

Finally, to restore the data to its original state after the modifications, you have several options, including transactions with COMMIT and ROLLBACK, backups, or re-insertion of original data. Here's an example using a stored backup, assuming original data was saved:

-- Assuming a backup table exists with original data

INSERT INTO EMP_1 SELECT * FROM EMPLOYEE_BACKUP WHERE EMP_NUM = ... ;

If no backup exists, you could re-update the data with original values or rerun a script that restores the data from an initial snapshot.

Conclusion

These SQL operations demonstrate effective management of database records, allowing for data modification, deletion, and restoration. Proper use of foreign keys, precise conditionals, and backup strategies are critical in ensuring data integrity during such manipulations.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.

Related Assignments