PL/SQL Section 3 Quiz

 1. The following code will return the last name of the employee whose employee id is equal to 100: True or False?

DECLARE
    v_last_name employees.last_name%TYPE;
    employee_id employees.employee_id%TYPE := 100;
BEGIN
    SELECT last_name INTO v_last_name
    FROM employees
    WHERE employee_id = employee_id;
END;

Tandai untuk Ditinjau

(1) Point

True

False (*)

Correct

---

2. It is good programming practice to create identifiers having the same name as column names. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

Correct

---

3. Which rows will be deleted from the EMPLOYEES table when the following code is executed?

DECLARE
    salary employees.salary%TYPE := 12000;
BEGIN
    DELETE FROM employees
    WHERE salary > salary;
END;

Tandai untuk Ditinjau

(1) Point

All rows whose SALARY column value is greater than 12000.

All rows in the table.

No rows. (*)

All rows whose SALARY column value is equal to 12000.

Correct

---

4. A variable is declared as:

DECLARE
    v_salary employees.salary%TYPE;
BEGIN

Which of the following is a correct use of the INTO clause?

Tandai untuk Ditinjau

(1) Point

SELECT salary
INTO v_salary
FROM employees
WHERE employee_id=100; (*)

SELECT salary
FROM employees
WHERE employee_id=100
INTO v_salary;

SELECT v_salary
INTO salary
FROM employees
WHERE employee_id=100;

SELECT salary
FROM employees
INTO v_salary;

Incorrect. Refer to Section 3 Lesson 2.

---

5. Which one of these SQL statements can be directly included in a PL/SQL executable block?

Tandai untuk Ditinjau

(1) Point

INSERT INTO...; (*)

SHOW USER;

SELECT * FROM DUAL;

IF... THEN...;

6. Which of the following use an implicit cursor?

Tandai untuk Ditinjau

(1) Point

SELECT statements only.

DML statements and SELECT statements which return a single row. (*)

DML statements only.

COMMIT and ROLLBACK statements only.

Correct

---

7. Assume there are 5 employees in Department 10. What happens when the following statement is executed?

UPDATE employees
SET salary=salary*1.1;

Tandai untuk Ditinjau

(1) Point

All employees get a 10% salary increase. (*)

An error message is displayed because you must use the INTO clause to hold the new salary.

A TOO_MANY_ROWS exception is raised.

No rows are modified because you did not specify "WHERE department_id=10"

Correct

---

8. You declare an implicit cursor in the DECLARE section of a PL/SQL block. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

Correct

---

9. To modify an existing row in a table, you can use the ________ statement.

Tandai untuk Ditinjau

(1) Point

ALTER

INSERT

MODIFY

UPDATE (*)

Correct

---

10. What will happen when the following statement is executed?

UPDATE employees
SET salary = 5000, commission_pct = NULL WHERE department_id = 50; ;

Assume there are no integrity constraint issues.

Tandai untuk Ditinjau

(1) Point

All employees in department 50 have their salary changed to 5000 and their commission percentage set to NULL. (*)

The statement fails because you cannot use NULL in the SET clause.

All employees who have a salary of 5000 have their commission percentage set to NULL.

The statement fails because you cannot modify more than one column in a single UPDATE statement.

11. 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, ....); 

Tandai untuk Ditinjau

(1) Point

The SET clause is trying to update the source table from the target table. (*)

The UPDATE clause must include the target table name: UPDATE emps SET ....

Nothing is wrong, the statement will execute correctly.

The INSERT clause must include a column list as well as a list of column values.

Correct

---

12. 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);

Tandai untuk Ditinjau

(1) Point

MERGE (*)

DELETE

UPDATE

INSERT

Correct

---

13. When INSERTing a row, the NULL keyword can be included in the VALUES (....) list. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

14. The following anonymous block of code is run:

BEGIN
    INSERT INTO countries (id, name)
    VALUES ('XA', 'Xanadu');
    SAVEPOINT XA;
    INSERT INTO countries (id, name)
    VALUES ('NV','Neverland');
    COMMIT;
    ROLLBACK TO XA;
END;

What happens when the block of code finishes?

Tandai untuk Ditinjau

(1) Point

Two rows are inserted. (*)

No data is inserted.

No data is inserted and an error occurs.

One record of data is inserted.

Correct

---

15. How many INSERTs can you have in one transaction?

Tandai untuk Ditinjau

(1) Point

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.