Download F1-P9 PDF

TitleF1-P9
TagsComputer Programming Sql Table (Database) Data Management
File Size142.4 KB
Total Pages4
Document Text Contents
Page 1

฀ ฀ ฀ ฀ ฀฀฀ ฀ ฀ ฀

Practice 9-1: Manipulating Data

The HR department wants you to create SQL statements to insert, update, and delete

employee data. As a prototype, you use the MY_EMPLOYEE table before giving the
statements to the HR department.

Note: For all the DML statements, use the Run Script icon (or press [F5]) to execute the
query. This way you get to see the feedback messages on the Script Output tabbed page.

For SELECT queries, continue to use the Execute Statement icon or press [F9] to get the
formatted output on the Results tabbed page.

Insert data into the MY_EMPLOYEE table.

1) Run the statement in the lab_09_01.sql script to build the MY_EMPLOYEE table
used in this practice.

2) Describe the structure of the MY_EMPLOYEE table to identify the column names.

3) Create an INSERT statement to add the first row of data to the MY_EMPLOYEE table

from the following sample data. Do not list the columns in the INSERT clause. Do not
enter all rows yet.

1550aropeburAudreyRopeburn5

750cnewmanChadNewman4

1100bbiriBenBiri3

860bdancsBettyDancs2

895rpatelRalphPatel1

SALARYUSERIDFIRST_NAMELAST_NAMEID

1550aropeburAudreyRopeburn5

750cnewmanChadNewman4

1100bbiriBenBiri3

860bdancsBettyDancs2

895rpatelRalphPatel1

SALARYUSERIDFIRST_NAMELAST_NAMEID

Page 2

Practice 9-1: Manipulating Data (continued)

฀ ฀ ฀ ฀ ฀฀฀ ฀ ฀ ฀

4) Populate the MY_EMPLOYEE table with the second row of the sample data from the

preceding list. This time, list the columns explicitly in the INSERT clause.

5) Confirm your addition to the table.

6) Write an INSERT statement in a dynamic reusable script file to load the remaining

rows into the MY_EMPLOYEE table. The script should prompt for all the columns (ID,

LAST_NAME, FIRST_NAME, USERID, and SALARY). Save this script to a

lab_09_06.sql file.

7) Populate the table with the next two rows of the sample data listed in step 3 by

running the INSERT statement in the script that you created.

8) Confirm your additions to the table.

9) Make the data additions permanent.

Update and delete data in the MY_EMPLOYEE table.

10) Change the last name of employee 3 to Drexler.

11) Change the salary to $1,000 for all employees who have a salary less than $900.

12) Verify your changes to the table.

13) Delete Betty Dancs from the MY_EMPLOYEE table.

14) Confirm your changes to the table.

Page 3

Practice 9-1: Manipulating Data (continued)

฀ ฀ ฀ ฀ ฀฀฀ ฀ ฀ ฀

15) Commit all pending changes.

Control data transaction to the MY_EMPLOYEE table.

16) Populate the table with the last row of the sample data listed in step 3 by using the
statements in the script that you created in step 6. Run the statements in the script.

17) Confirm your addition to the table.

18) Mark an intermediate point in the processing of the transaction.

19) Delete all the rows from the MY_EMPLOYEE table.

20) Confirm that the table is empty.

21) Discard the most recent DELETE operation without discarding the earlier INSERT
operation.

22) Confirm that the new row is still intact.

23) Make the data addition permanent.

If you have the time, complete the following exercise:

24) Modify the lab_09_06.sql script such that the USERID is generated automatically
by concatenating the first letter of the first name and the first seven characters of the

last name. The generated USERID must be in lowercase. Therefore, the script should

not prompt for the USERID. Save this script to a file named lab_09_24.sql.

1230manthonyMarkAnthony6

SALARYUSERIDFIRST_NAMELAST_NAMEID

1230manthonyMarkAnthony6

SALARYUSERIDFIRST_NAMELAST_NAMEID

Similer Documents