Database Programming with PL/SQL
3-1: Review of SQL DML
Practice Activities
Vocabulary
Statement used to remove existing rows in a table. - DELETE
Statement used to add new rows to a table. - INSERT
Statement used to INSERT and/or UPDATE a target table,based on matching values in a source table. - MERGE
Statement used to modify existing rows in a table. - UPDATE
When you create, change, or delete an object in a database. - DDL
When you change data in an object (for example, by inserting or deleting rows). - DML
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
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.
3. Write a SQL statement to create the above table.
CREATE TABLE GROCERY_ITEMS(
PRODUCT_ID NUMBER PRIMARY KEY,
BRAND VARCHAR2(25),
DESCRIPTION VARCHAR2(25) );
4. Write and execute three SQL statements to explicitly add the above data to the table.
INSERT INTO GROCERY_ITEMS (PRODUCT_ID, BRAND, DESCRIPTION) VALUES (110, 'Colgate', 'Thoothpaste');
INSERT INTO GROCERY_ITEMS (PRODUCT_ID, BRAND, DESCRIPTION) VALUES (111, 'Ivory', 'Soap');
INSERT INTO GROCERY_ITEMS (PRODUCT_ID, BRAND, DESCRIPTION) VALUES (112, 'Heinz','Ketchup');
5. Write and execute a SQL statement that will explicitly add your favorite beverage to the table.
INSERT INTO GROCERY_ITEMS (PRODUCT_ID, BRAND, DESCRIPTION) VALUES (113, 'Cola','Drink');
6. Write and execute a SQL statement that modifies the description for Heinz ketchup to “tomato catsup”.
UPDATE GROCERY_ITEMS SET
DESCRIPTION = 'TOMATO CATSUP'
WHERE brand = 'Heinz';
7. Write and execute a SQL statement that will implicitly add your favorite candy to the table.
INSERT INTO GROCERY_ITEMS VALUES (114, 'Kinder Bueno', 'Chocolate');
8. Write and execute a SQL statement that changes the soap brand from “Ivory” to “Dove.”
UPDATE GROCERY_ITEMS SET
BRAND = 'DOVE'
WHERE BRAND = 'Ivory';
Use the following table for questions 9 through 14.
9. Write and execute SQL statements to create the new_items table and populate it with the data in the table.
CREATE TABLE NEW_ITEMS(
PRODUCT_ID NUMBER PRIMARY KEY,
BRAND VARCHAR2(25),
DESCRIPTION VARCHAR2(25));
INSERT INTO new_items VALUES (110, 'Colgate', 'Dental paste');
INSERT INTO new_items VALUES (175, 'Dew', 'Soda');
INSERT INTO new_items VALUES (275, 'Palmolive ', 'Dish detergent');
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.
MERGE INTO grocery_items g USING new_items i ON (g.product_id = i.product_id)
WHEN MATCHED THEN UPDATE SET g.brand = i.brand, g.description = i.description
WHEN NOT MATCHED THEN INSERT VALUES(i.product_id, i.brand, i.description) ;
11.How many rows will be updated by the SQL statement in question 10?
1 (the one with id 110)
12.How many rows will be inserted by the SQL statement in question 10?
2 (id 175 and 275)
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.
3 rows updated
SELECT * FROM grocery_items;
0 Comments