Section 3
Which
SQL statements can be used directly in a PL/SQL block? (Choose two.)
SELECT
* INTO ... (*)
ALTER
TABLE employees ...
REVOKE
SELECT ON ...
UPDATE
employees
SET... (*)
GRANT
EXECUTE ON ...
It is good programming practice
to create identifiers having the same name as column names. True or False?
True
False
(*)
Given this first section of
code:
DECLARE
v_result
employees.salary%TYPE;
BEGIN
Which statement will always return exactly one value?
SELECT
SUM(salary)
INTO v_result
FROM employees;
(*)
SELECT
salary
INTO v_result
FROM employees;
SELECT
salary
INTO v_result
FROM employees
WHERE last_name ='Smith';
SELECT
salary
INTO v_result
FROM employees
WHERE department_id = 80;
A variable is declared as:
DECLARE
v_holdit
employees.last_name%TYPE;
BEGIN ...
Which of the following is a correct use of the INTO clause?
SELECT
last_name
INTO v_holdit
FROM employees;
SELECT
*
INTO v_holdit
FROM employees;
SELECT
salary
INTO v_holdit
FROM employees
WHERE employee_id=100;
SELECT
last_name
INTO v_holdit
FROM employees
WHERE employee_id=100;
(*)
A variable is declared as:
DECLARE
v_salary
employees.salary%TYPE;
BEGIN
Which of the following is a correct use of the INTO clause?
SELECT
salary
FROM employees
WHERE employee_id=100
INTO v_salary;
SELECT
v_salary
INTO salary
FROM employees
WHERE employee_id=100;
SELECT
salary
INTO v_salary
FROM employees
WHERE employee_id=100;
(*)
SELECT
salary
FROM employees
INTO v_salary;
There are no employees in Department 77. What will happen
when the following block is executed?
BEGIN
DELETE FROM employees
WHERE department_id=77;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT)
END;
An
exception is raised because the block does not contain a COMMIT statement.
A
NO_DATA_FOUND exception is raised.
A NULL
is displayed.
A zero
(0) is displayed. (*)
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;
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.
Every
employee row is updated. (*)
No rows
are updated but the block completes successfully.
Which implicit cursor attribute
identifies the number of rows updated in the following statement?
…
DBMS_OUTPUT.PUT_LINE(__________ || ' rows updated.');
…
SQL%COUNT
SQLROW%COUNT
SQL%ROWCOUNT
(*)
SQL%NUMBER
If a DELETE statement does not
include a WHERE clause, all rows in the table are deleted. True or False?
True
(*)
False
Is it possible to insert more
than one row at a time using an INSERT statement with a VALUES clause?
Yes,
you can list as many rows as you want, just remember to separate the rows with
commas.
No, you
can only create one row at a time when using the VALUES clause. (*)
No,
there is no such thing as INSERT ... VALUES.
The
following table has been created:
CREATE TABLE student_table
(stud_id NUMBER(6),
last_name VARCHAR2(20),
first_name VARCHAR2(20),
lunch_num NUMBER(4));
Which one of the following INSERT statements will fail?
INSERT
INTO student_table (stud_id, last_name, lunch_num)
VALUES (143354, 'Roberts', 6543, 'Cameron');
(*)
INSERT
INTO student_table (stud_id, last_name, first_name, lunch_num)
VALUES (143354, 'Roberts', 'Cameron', 6543);
INSERT
INTO student_table
VALUES (143354, 'Roberts', 'Cameron', 6543);
INSERT
INTO student_table (stud_id, lunch_num, first_name, last_name)
VALUES (143352, 6543, 'Cameron', 'Roberts');
What keyword goes at the
beginning of the following SQL statement?
_____ INTO test1 a
USING all_objects
b
ON (a.object_id
= b.object_id)
WHEN MATCHED
THEN UPDATE
SET a.status =
b.status
WHEN NOT MATCHED
THEN
INSERT
(object_id, status)
VALUES
(b.object_id, b.status);
INSERT
UPDATE
MERGE
(*)
DELETE
To modify an existing row in a
table, you can use the ________ statement.
MODIFY
ALTER
INSERT
UPDATE
(*)
In a PL/SQL block, where can you
code a COMMIT statement?
In any
section of the block: Declaration, Executable, or Exception.
Only
the Executable section.
In the
Executable and/or the Exception sections. (*)
Nowhere;
the COMMIT statement must be outside the block.
Examine the following code: BEGIN
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
cool
cats
aardvaarks
and cool cats (*)
aardvaarks,
big birds and cool cats
No comments:
Post a Comment