Scribd Logo
Upload

Welcome to Scribd!

  • 1K views

    SQL Fundamentals - Practice 08

    Uploaded by

    Abby AR
    The document provides instructions for creating SQL statements to insert, update, delete, and control transactions on employee data in a MY_EMPLOYEE table. It includes steps to insert sample data, update employee names and salaries, delete a record, commit changes, add more data from a script, mark and discard transactions, and make changes permanent.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd

    SQL Fundamentals - Practice 08

    Uploaded by

    Abby AR
    1K views4 pages
    The document provides instructions for creating SQL statements to insert, update, delete, and control transactions on employee data in a MY_EMPLOYEE table. It includes steps to insert sample data, update employee names and salaries, delete a record, commit changes, add more data from a script, mark and discard transactions, and make changes permanent.

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document provides instructions for creating SQL statements to insert, update, delete, and control transactions on employee data in a MY_EMPLOYEE table. It includes steps to insert sample data, update employee names and salaries, delete a record, commit changes, add more data from a script, mark and discard transactions, and make changes permanent.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    1K views4 pages

    SQL Fundamentals - Practice 08

    Uploaded by

    Abby AR
    The document provides instructions for creating SQL statements to insert, update, delete, and control transactions on employee data in a MY_EMPLOYEE table. It includes steps to insert sample data, update employee names and salaries, delete a record, commit changes, add more data from a script, mark and discard transactions, and make changes permanent.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 4
    At a glance
    Powered by AI
    The passage describes how to perform insert, update, and delete operations on a sample MY_EMPLOYEE table using SQL statements.

    The steps include describing the table structure, inserting sample data using INSERT statements with and without explicitly listing columns, populating the table by running an INSERT script, and confirming the additions.

    The steps include changing the last name of an employee, updating salaries less than $900, verifying the changes, and deleting a row from the table.

    Practice 8

    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.
    Insert data into the MY_EMPLOYEE table.
    1. Run the statement in the lab_08_01.sql script to build the MY_EMPLOYEE table to be
    used for the lab.
    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.
    ID LAST_NAME FIRST_NAME USERID SALARY
    1 Patel Ralph rpatel 895
    2 Dancs Betty bdancs 860
    3 Biri Ben bbiri 1100
    4 Newman Chad cnewman 750
    5 Ropeburn Audrey aropebur 1550

    4. Populate the MY_EMPLOYEE table with the second row of sample data from the preceding
    list. This time, list the columns explicitly in the INSERT clause.
    5. Confirm your addition to the table.

    Oracle Database 10g: SQL Fundamentals I 8 - 41


    Practice 8 (continued)
    6. Write an insert statement in a dynamic reusable script file named loademp.sql to load
    rows into the MY_EMPLOYEE table. Concatenate the first letter of the first name and the
    first seven characters of the last name to produce the user ID. Save this script to a file named
    lab_08_06.sql.
    7. Populate the table with the next two rows of 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.

    Oracle Database 10g: SQL Fundamentals I 8 - 42


    Practice 8 (continued)
    15. Commit all pending changes.
    Control data transaction to the MY_EMPLOYEE table.
    16. Populate the table with the last row of 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. Empty the entire 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.

    Oracle Database 10g: SQL Fundamentals I 8 - 43

    You might also like