Nothing Special   »   [go: up one dir, main page]
Scribd Logo
Upload

Welcome to Scribd!

  • 274 views

    HANDS-ON-CHAPTER-5 ANSWER KEY (ORACLE 11g JOAN CASTEEL)

    Uploaded by

    Deadpool
    This document provides instructions for hands-on assignments to practice SQL commands like INSERT, UPDATE, DELETE, and ROLLBACK. It includes examples adding new rows and modifying data in tables, setting values for substitution variables, committing and rolling back transactions, and describes errors that can occur when data violates constraints.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    HANDS-ON-CHAPTER-5 ANSWER KEY (ORACLE 11g JOAN CASTEEL)

    Uploaded by

    Deadpool
    274 views3 pages
    This document provides instructions for hands-on assignments to practice SQL commands like INSERT, UPDATE, DELETE, and ROLLBACK. It includes examples adding new rows and modifying data in tables, setting values for substitution variables, committing and rolling back transactions, and describes errors that can occur when data violates constraints.

    Original Description:

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides instructions for hands-on assignments to practice SQL commands like INSERT, UPDATE, DELETE, and ROLLBACK. It includes examples adding new rows and modifying data in tables, setting values for substitution variables, committing and rolling back transactions, and describes errors that can occur when data violates constraints.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    274 views3 pages

    HANDS-ON-CHAPTER-5 ANSWER KEY (ORACLE 11g JOAN CASTEEL)

    Uploaded by

    Deadpool
    This document provides instructions for hands-on assignments to practice SQL commands like INSERT, UPDATE, DELETE, and ROLLBACK. It includes examples adding new rows and modifying data in tables, setting values for substitution variables, committing and rolling back transactions, and describes errors that can occur when data violates constraints.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 3

    HANDS ON CHAPTER 5

    Hands-On Assignments

    To perform the following assignments, refer to the tables created in the JLDB_Build_5.sql scriptat
    the beginning of the chapter.

    1. Add a new row in the ORDERS table with the following data: Order# = 1021, Customer# =
    1009, and Order date = July 20, 2009.

    INSERT INTO orders (order#, customer#, orderdate)


    VALUES (1021, 1009, '20-JUL-05');

    2. Modify the zip code on order 1017 to 33222.


    UPDATE orders
    SET shipzip = '33222'
    WHERE order# = 1017;

    3. Save the changes permanently to the database.

    COMMIT;

    4. Add a new row in the ORDERS table with the following data: Order# = 1022, Customer# =
    2000, and Order date = August 6, 2009. Describe the error raised and what caused the
    error.
    INSERT INTO orders (order#, customer#, orderdate)
    VALUES (1022, 2000, '06-AUG-05');
    **Foreign key error due to customer 2000 not existing in customers
    table

    5. Add a new row in the ORDERS table with the following data: Order# = 1023 and Customer# =
    1009. Describe the error raised and what caused the error.
    INSERT INTO orders (order#, customer#, orderdate)
    VALUES (1023, 1009);
    **Constraint error due to orderdate having a NOT NULL constraint

    6. Create a script using substitution variables that allows a user to set a new cost amount fora
    book based on the ISBN.
    UPDATE books
    SET cost = &cost
    WHERE isbn = '&isbn';
    7. Execute the script and set the following values: isbn = 1059831198 and cost = $20.00.
    Use START or @ on client SQL*Plus client tool or Load
    script in internet SQL*Plus
    interface.

    8. Execute a command that undoes the change in Step 7.


    ROLLBACK;

    9. Delete Order# 1005. You need to address both the master order record and the related detail
    records.
    DELETE FROM orderitems
    WHERE order# = 1005;

    10. Execute a command that undoes the previous deletion.


    ROLLBACK;

    Review Questions

    1. Which command should you use to copy data from one table and have it added to an existing
    table?

    The Insert Into Command


    2. Which command can you use to change the existing data in a table?
    Update Command
    3. When do changes generated by DML operations become stored in database tables
    permanently?
    When the commit command is used
    4. Explain the difference between explicit and implicit locks.

    Shared(implicit) locks by rows exclusive (explicit) locks the whole table


    5. If you add a record to the wrong table, what’s the simplest way to remove the record from the
    table?
    The rollback command
    6. How does Oracle 11g identify a substitution variable in an SQL command?
    '&Region' You use the ampersand followed by the variable

    7. How are NULL values included in a new record being added to a table?
    You can use the word Null and entering ('') single quotes in the position

    8. When should the VALUES clause be omitted from the INSERT INTO command?
    When rows are copied from existing table by using a subquery

    9. What happens if a user attempts to add data to a table, and the addition would cause the record
    to violate an enabled constraint?
    The data is not added

    10. What two methods can be used to activate a column’s DEFAULT option in an INSERT command?
    A column must be excluded from the column list or Default should be used as a value

    You might also like