Halaman

Monday, August 12, 2019

Answer Section 3 Quiz Database Programming with PL/SQL 2019 Learner - English


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

Final Exam Java Programming 2019 Learner - English

Final Exam