Scribd Logo
Upload

Welcome to Scribd!

  • 706 views

    Database Programming With PL/SQL 3-1: Practice Activities: Review of SQL DML

    Uploaded by

    Naski Kuafni

    Copyright:

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

    Database Programming With PL/SQL 3-1: Practice Activities: Review of SQL DML

    Uploaded by

    Naski Kuafni
    706 views4 pages

    Original Title

    PLSQL_3_1_Practice

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    706 views4 pages

    Database Programming With PL/SQL 3-1: Practice Activities: Review of SQL DML

    Uploaded by

    Naski Kuafni

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 4
    At a glance
    Powered by AI
    The document covers SQL statements like INSERT, UPDATE, DELETE and MERGE. It also discusses DDL and DML.

    The SQL statements covered are INSERT, UPDATE, DELETE, MERGE. It also discusses DDL for creating/altering objects and DML for manipulating data.

    The grocery_items table was created and populated with sample product data like Colgate toothpaste, Ivory soap, and Heinz ketchup.

    academy.oracle.

    com

    Database Programming with


    PL/SQL 3-1: Review of SQL DML
    Practice Activities
    Vocabulary
    Identify the vocabulary word for each definition below:

    DELETE Statement used to remove existing rows in a table.

    INSERT Statement used to add new rows to a table.

    MERGE Statement used to INSERT and/or UPDATE a target table, based


    on matching values in a source table.

    UPDATE Statement used to modify existing rows in a table.

    Data Definition Language (DDL) When you create, change, or delete an object in a database.

    Data Manipulation Language When you change data in an object (for example, by inserting
    (DML) or deleting rows).

    Try It / Solve It
    1. Evaluate the following SQL statement.

    DELETE FROM students;

    This SQL statement will:

    A. Not execute due to wrong syntax


    B. Delete the first row from STUDENTS
    C. Delete all rows from STUDENTS
    D. None of the above

    Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their
    respective owners.
    2

    2. Evaluate the following SQL statement.

    INSERT INTO STUDENTS (id, last_name, first_name)


    VALUES (29,'Perez','Jessica');

    This SQL statement:

    A. Does an explicit insert


    B. Does an implicit insert

    Use the following table for questions 3 through 8.

    grocery_items

    product_id brand description

    110 Colgate Toothpaste

    111 Ivory Soap

    112 Heinz Ketchup

    3. Write a SQL statement to create the above table.


    CREATE TABLE grocery_item(
    product_id INTEGER NOT NULL,
    brand VARCHAR2(50),
    description VARCHAR2(50)
    );

    4. Write and execute three SQL statements to explicitly add the above data to the table.

    INSERT INTO grocery_item


    VALUES (110,'Colgate','Toothpaste');
    INSERT INTO grocery_item
    VALUES (111,'Ivory','Soap');
    INSERT INTO grocery_item
    VALUES (112,'Heinz','Ketchup');

    5. Write and execute a SQL statement that will explicitly add your favorite beverage to the table.

    INSERT INTO grocery_item


    VALUES (113,'Pepsi','Beverage');
    6. Write and execute a SQL statement that modifies the description for Heinz ketchup to “tomato
    catsup”.
    3

    UPDATE grocery_item
    SET description = ‘tomato catsup’
    WHERE product_id=112

    7. Write and execute a SQL statement that will implicitly add your favorite candy to the table.
    INSERT INTO grocery_item
    VALUES (114,'KitKat','Candy');

    8. Write and execute a SQL statement that changes the soap brand from “Ivory” to “Dove.”

    UPDATE grocery_item
    SET BRAND = 'Dove'
    WHERE product_id=111
    Use the following table for questions 9 through 14.

    new_items

    product_id brand description

    110 Colgate Dental paste

    175 Dew Soda

    275 Palmolive Dish detergent

    9. Write and execute SQL statements to create the new_items table and populate it with the data in
    the table.

    10. Write a SQL statement that will update the grocery_items table with the brand and description from
    the new_items table when the product ID values match. If they don’t match, add a new row to the
    grocery_items table. DO NOT EXECUTE YOUR STATEMENT YET.

    Insert into grocery_item (product_id, brand, description)


    SELECT product_id, brand, description
    From new_items
    11. How many rows will be updated by the SQL statement in question 10?
    No one
    12. How many rows will be inserted by the SQL statement in question 10?
    3
    13. Which of the following is true about the SQL statement in question 10?
    A. new_items is the source table and grocery_items is the target table.
    B. grocery_items is the source table and new_items is the target table.
    14. Execute the SQL statement you wrote in question 10, and then SELECT all data from the
    grocery_items table to verify your answers to questions 11 and 12.

    You might also like