Section 3: Using SQL in PL/SQL Statements
3.01. Review of SQL DML
1. Look at this SQL statement: MERGE INTO old_trans ot USING new_trans nt ON (ot.trans_id = nt.trans_id) …. ; OLD_TRANS is the source table and NEW_TRANS is the target table. True or false?
♦True
♦False (*)
2. You want to modify existing rows in a table. Which of the following are NOT needed in your SQL statement? (Choose Two)
♦A MODIFY clause (*)
♦An UPDATE clause
♦The name of the table
♦The name of the column(s) you want to modify.
♦A new value for the column you want to modify (this can be an expression or a subquery).
♦A WHERE clause. (*)
3. Is it possible to insert more than one row at a time using an INSERT statement with a VALUES clause?
♦No, you can only create one row at a time when using the VALUES clause. (*)
♦Yes, you can list as many rows as you want, just remember to separate the rows with commas.
♦No, there is no such thing as INSERT … VALUES.
4. What is wrong with the following statement?
DELETE from employees WHERE salary > (SELECT MAX(salary) FROM employees);
♦You cannot code a subquery inside a DELETE statement.
♦You cannot use inequality operators such as “<” and “>” inside a DELETE statement.
♦Nothing is wrong, the statement will execute correctly. (*)
5. What is wrong with the following statement? MERGE INTO emps e USING new_emps ne ON (e.employee_id = ne.employee_id) WHEN MATCHED THEN UPDATE SET ne.salary = e.salary WHEN NOT MATCHED THEN INSERT VALUES (ne.employee_id, ne.first_name, ne.last_name, …. ne.salary, ….);
♦The UPDATE clause must include the target table name: UPDATE emps SET ….
♦The INSERT clause must include a column list as well as a list of column values.
♦The SET clause is trying to update the source table from the target table. (*)
♦Nothing is wrong, the statement will execute correctly.
6. To modify an existing row in a table, you can use the ________ statement.
♦MODIFY
♦INSERT
♦ALTER
♦UPDATE (*)
7. What would be the result of the following statement: DELETE employees;
♦Nothing, no data will be changed.
♦All rows in the employees table will be deleted. (*)
♦The statement will fail because it contains a syntax error.
♦The row with EMPOYEE_ID=100 will be deleted.
8. When inserting a row into a table, the VALUES clause must include a value for every column of the table. True or False?
♦True
♦False (*)
3.02. Retrieving data in PL/SQL
1. It is good programming practice to create identifiers having the same name as column names. True or False?
♦True
♦False (*)
2. Does PL/SQL allow you to have a variable with the same name as a database column?
♦No
♦Yes (*)
3. Which SQL statements can be used directly in a PL/SQL block? (Choose two.)
♦GRANT EXECUTE ON …
♦SELECT * INTO … (*)
♦REVOKE SELECT ON …
♦UPDATE employees SET… (*)
♦ALTER TABLE employees …
4. What will happen when the following block is executed? DECLARE v_last employees.last_name%TYPE; v_first employees.first_name%TYPE; v_salary employees.salary%TYPE; BEGIN SELECT first_name, last_name INTO v_first, v_last, v_salary FROM employees WHERE employee_id=100; END;
♦The block will fail because the SELECT statement returns more than one row.
♦The block will fail because the SELECT is trying to read two columns into three PL/SQL variables. (*)
♦The block will fail because V_LAST was declared before V_FIRST.
♦The block will execute successfully, and the V_SALARY variable will be set to NULL.
5. Look at this PL/SQL block: DECLARE v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM employees WHERE salary > 50000; END; No employees earn more than $50000. Which of the following statements are true? (Choose two).
♦The SELECT will return value 0 into V_COUNT. (*)
♦The SELECT will fail because it does NOT return exactly one row.
♦The block will fail because variable V_SALARY was not declared.
♦The SELECT returns exactly one row. (*)
♦The block will fail because no results are displayed to the user.
6. Which of the following is NOT a valid guideline for retrieving data in PL/SQL?
♦Terminate the SQL statement with a semicolon (;)
♦Do NOT use a WHERE clause in SELECT statements. (*)
♦Where possible, declare variables using the %TYPE attribute.
♦Specify the same number of variables in the INTO clause as database columns in the SELECT clause.
7. Which one of these SQL statements can be directly included in a PL/SQL executable block?
♦IF… THEN…;
♦INSERT INTO…; (*)
♦SELECT * FROM DUAL;
♦SHOW USER;
8. When used in a PL/SQL block, which SQL statement must return exactly one row?
♦INSERT
♦UPDATE
♦SELECT (*)
♦MERGE
♦DELETE
3.03. Manipulating data in PL/SQL
1. Which of the following SQL DML commands can be used inside a PL/SQL block?
♦INSERT and UPDATE only.
♦UPDATE and DELETE only.
♦INSERT, UPDATE and DELETE only.
♦INSERT, UPDATE, DELETE and MERGE. (*)
2. A PL/SQL block contains the following DML statement: UPDATE wf_countries SET population = population * 1.1 WHERE country_id = 229; Which kind of cursor is used for this statement?
♦An implicit cursor named “WF_COUNTRIES”.
♦An implicit cursor named “SQL”. (*)
♦An explicit cursor named “SQL”.
♦An explicit cursor which must be declared and named by the PL/SQL programmer.
3. There are three employees in department 90. What will be displayed when the following code is executed? DECLARE v_open CHAR(3) := ‘NO’; BEGIN UPDATE employees SET job_id = ‘ST_CLERK’ WHERE department_id = 90; IF SQL%FOUND THEN v_open := ‘YES’; END IF; DBMS_OUTPUT.PUT_LINE(v_open || ‘ ‘ || SQL%ROWCOUNT); END;
♦NO 3
♦YES 1
♦YES 3 (*)
♦Nothing will be displayed. The block will fail because you cannot use implicit cursor attributes directly in a call to DBMS_OUTPUT.PUT_LINE.
4. Which of the following use an implicit cursor?
♦DML statements only.
♦SELECT statements only.
♦DML statements and SELECT statements which return a single row. (*)
♦COMMIT and ROLLBACK statements only.
5. You can use implicit cursor attributes such as SQL%ROWCOUNT directly inside a DML statement. For example: INSERT INTO log_table VALUES (SYSDATE, USER, SQL%ROWCOUNT); True or False?
♦True
♦False (*)
6. Employee_id 999 does not exist. What will happen when the following code is executed? DECLARE employee_id employees.employee_id%TYPE := 999; BEGIN UPDATE employees SET salary = salary * 1.1 WHERE employee_id = employee_id; END;
♦No rows are updated but the block completes successfully.
♦Every employee row is updated. (*)
♦An exception is raised because you cannot give a variable the same name as a table column.
♦An exception is raised because the UPDATE statement did not modify any rows.
3.04. Using Transaction Control Statements
INSERT INTO animals VALUES (‘aa’,’aardvarks’);
SAVEPOINT sp_1;
INSERT INTO animals VALUES (‘bb’,’big birds’);
SAVEPOINT sp_2;
ROLLBACK TO sp_1;
INSERT INTO animals VALUES (‘cc’,’cool cats’);
COMMIT;
END;
Which row(s) will be in the ANIMALS table after this block is executed?
♦big birds and cool cats
♦aardvaarks and cool cats (*)
♦aardvaarks, big birds and cool cats
♦One
♦As many as you want until you do a COMMIT or ROLLBACK. (*)
♦As many as you can execute before the database does an AUTOSAVE.
♦As many as you want until a different DML statement (UPDATE, DELETE or MERGE) is executed.
♦Only the Executable section.
♦In the Executable and/or the Exception sections. (*)
♦Nowhere; the COMMIT statement must be outside the block.
4. How many transactions are in the following block?
BEGIN
INSERT INTO countries (country_id, country_name)
VALUES (‘XA’, ‘Xanadu’);
INSERT INTO countries (country_id, country_name)
VALUES (‘NV’, ‘Neverland’);
UPDATE countries SET country_name=’Deutchland’
WHERE country_id=’DE’;
UPDATE countries SET region_id=1
WHERE country_name LIKE ‘%stan’;
END;
How many transactions are shown above?
♦Four; each DML is a separate transaction
♦Two; both the INSERTs are one transaction and both the UPDATEs are a second transaction.
♦It depends on how many rows are updated – there will be a separate transaction for each row.
♦One (*)
InfoCad Blog 