Bookmark

EMP_1

EMPLOYEE TABLE

JOB TABLE

Problem:

Use SQL commands to answer Problems 112. (Take screen shot of commands and tables)

1. Write the two SQL code command sequences required to: a. Create a temporary table named TEMP_1 whose structure is composed of the EMP_1 attributes EMP_NUM and EMP_HIREDATE b. Copy the matching EMP_1 values into the TEMP_1 table.

2. Write the SQL command that will delete the newly created TEMP_1 table from the database.

3. Write the SQL code required to list all employees from EMPLOYEE table whose last names start with Smith.

4. Write the SQL code to find the average employment years (EMP_YEARS) in the EMPLOYEE table you created before.

5. Write the SQL code that will produce a listing for the data in the JOB table in descending order by the JOB_CHG_HOUR.

6. Write the SQL code that will list only the distinct JOB_LAST_UPDATE found in the JOB table.

7. Write the SQL code to insert a column called JOB_HOUR_RATE into the JOB table. Assume the data type of JOB_ HOUR_RATE is float.

8. Write the SQL code to delete the newly created column JOB_ HOUR_RATE from the JOB table.

9. Write the SQL code to produce the total number of hours (JOB_CHG_HOUR) and the latest job update date (JOB_LAST_UPDATE) in the JOB table.

10. Write the SQL code to produce the job code from the JOB table, which has the corresponding minimal JOB_CHG_HOUR.

11. Write the SQL code to join JOB and EMPLOYEE table (natural join).

12. Write the SQL code to produce the total number of employment years for each of the jobs represented in the EMPLOYEE table.

mysq1> update EMP 1 SET EMP YEARS 20 WHERE JOB CoDE> 501; Query OK, 3 rows affected (0.13 sec Rows matched: 3 Changed: 3 Warnings 0 mysq1> SELECT FROM emp 1; EMP NUM I EMP NAME I EMP NAME I EMP INITIAL I EMP HIRE DATE I JOB CODE EMP YEARS I I David 501 I 102 Senior 1989-07-12 20 103 I Arbough June 1996-12-01 503 20 501 104 Ramon as 20 Anne 1987-11-15 106 Smithfield I William 500 I 2004-06-22 4 rows in set (0.00 sec