♦As many as you want (*)
♦They must match the same number as the number of ELSE statements.
♦None; the command is ELSE IF;
a VARCHAR2(6) := NULL;
b VARCHAR2(6) := NULL;
BEGIN
IF a = b THEN
DBMS_OUTPUT.PUT_LINE(‘EQUAL’);
ELSIF a != b THEN
DBMS_OUTPUT.PUT_LINE(‘UNEQUAL’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘OTHER’);
END IF;
END;
Which word will be displayed?
♦UNEQUAL
♦EQUAL
♦Nothing will be displayed
♦OTHER (*)
3. What is the correct form of a simple IF statement?
♦IF condition THEN statement;
♦IF condition THEN statement;
END IF; (*)
♦IF condition;
THEN statement;
END IF;
♦IF condition
THEN statement
ENDIF;
4. You can use a control structure to change the logical flow of the execution of SQL statements. True or False?
♦True
♦False (*)
5. What is the correct name for CASE, LOOP, WHILE, and IF-THEN-ELSE structures ?
♦Control structures (*)
♦Array structures
♦Memory structures
♦Cursor structures
6. Examine the following code:
DECLARE
a BOOLEAN := TRUE;
b BOOLEAN := FALSE;
c BOOLEAN := TRUE;
d BOOLEAN := FALSE;
game char(4) := ‘lost’;
BEGIN
IF ((a AND b) AND (c OR d))
THEN game := ‘won’;
END IF;
What is the value of GAME at the end of this block?
♦NULL
♦’won’
♦’lost’ (*)
♦False
7. Examine the following code:
DECLARE
v_salary NUMBER(6);
v_constant NUMBER(6) := 15000;
v_result VARCHAR(6) := ‘MIDDLE’;
BEGIN
IF v_salary != v_constant THEN
v_result := ‘HIGH’;
ELSE
v_result := ‘LOW’;
END IF;
END;
What is the final value of v_result?
♦HIGH
♦LOW (*)
♦MIDDLE
♦Null
8. Which one of these is NOT a kind of loop?
♦ASCENDING loop (*)
♦FOR loop
♦Basic loop
♦WHILE loop
9. The EXIT statement can be located anywhere inside a basic loop. True or False?
♦True (*)
♦False
10. What kind of statement is best suited for displaying the multiplication table for “sixes”: 6×1=6, 6×2=12 … 6×12=72?
♦CASE expression
♦IF statement
♦CASE statement
♦LOOP statement (*)
11. Which kind of loop is this?
v_count := 1;
LOOP
v_count := v_count + 1;
EXIT WHEN i > 20;
END LOOP;
♦FOR loop
♦IF-THEN loop
♦Basic loop (*)
♦WHILE loop
♦CASE loop
♦Calculating and displaying the sum of all integers from 1 to 100 (*)
♦Testing if a condition is true, false or null
♦Fetching and displaying an employee’s last name from the database
1 x 1 = 1
1 x 2 = 2
…..
1 x 12 = 12
2 x 1 = 2
2 x 2 = 4
…..
2 x 12 = 24
3 x 1 = 3
…..
…..
12 x 12 = 144
Which of the following is an efficient way to do this in PL/SQL?
♦Use two nested FOR loops. (*)
♦Store all the numbers from 1 to 144 in a table, then fetch and display them using a cursor.
♦Create a function which accepts two numbers as IN parameters and returns their product. Invoke the function 144 times.
♦Write an anonymous block which contains 144 calls to DBMS_OUTPUT, each looking like: DBMS_OUTPUT.PUT_LINE(‘7 x 9 = 63’);
14. When coding two nested loops, both loops must be of the same type. For example, you cannot code a FOR loop inside a WHILE loop. True or False?
♦True
♦False (*)
15. Examine the following code:
BEGIN
FOR i IN 1..5 LOOP
FOR j IN 1..8 LOOP
EXIT WHEN j = 7;
DBMS_OUTPUT.PUT_LINE(i || j);
END LOOP;
END LOOP;
END;
How many lines of output will be displayed when this code is executed?
♦35
♦6
♦30 (*)
♦40
16. What clause will leave the outer loop at Point A?
DECLARE
i INTEGER := 0;
BEGIN
<< i_loop >>
WHILE i <= 10 LOOP
i := i+1;
<< j_loop >>
FOR j IN 1..5 LOOP
_______ WHEN i = j*2; — Point A
DBMS_OUTPUT.PUT_LINE(i || j);
END LOOP;
END LOOP;
END;
♦EXIT <<outerloop>>
♦EXIT outerloop
♦EXIT i_loop (*)
♦EXIT j_loop
17. Examine the following code:
DECLARE
v_bool BOOLEAN := FALSE;
v_counter NUMBER(4) := 0;
BEGIN
… Line A
…
END;
Which of the following is NOT valid at line A?
♦WHILE NOT v_boolean LOOP
♦WHILE v_boolean AND v_counter < 6 LOOP
♦WHILE v_counter > 8 LOOP
♦WHILE v_counter IN 1..5 LOOP (*)
18. Which statement best describes when a FOR loop should be used?
♦When the number of iterations is known (*)
♦When testing the value in a Boolean variable
♦When the controlling condition must be evaluated at the start of each iteration
19. What will happen when the following code is executed?
BEGIN
FOR i in 1 ..3 LOOP
DBMS_OUTPUT.PUT_LINE (i);
i := i + 1;
END LOOP;
END;
♦It will display 1, 2, 3.
♦It will display 2, 3, 4.
♦It will result in an error because you cannot modify the counter in a FOR loop. (*)
♦It will result in an error because the counter was not explicitly declared.
20. In a FOR loop, an implicitly declared counter automatically increases or decreases with each iteration. True or False?
♦True (*)
♦False
21. Which statement best describes when a WHILE loop shouild be used?
♦When the number of iterations is known
♦When repeating a sequence of statements until the controlling condition is no longer true (*)
♦When assigning a value to a Boolean variable
♦When testing whether a variable is null
22. You want to display a message which depends on the value of v_grade: if v_grade = ‘A’ display ‘Very Good’, if v_grade = ‘B’ then display ‘Good’, and so on.
DECLARE
v_grade CHAR(1);
BEGIN
CASE v_grade
The next line should be
♦WHEN ‘A’ THEN (*)
♦WHEN v_grade = ‘A’ THEN
♦WHEN ‘A’ THEN;
♦IF ‘A’ THEN
23. You want to assign a value to v_result which depends on the value of v_grade: if v_grade = ‘A’ set v_result to ‘Very Good’ and so on.
DECLARE
v_grade CHAR(1);
v_result VARCHAR2(10);
BEGIN
v_result :=
CASE v_grade
The next line should be
♦WHEN v_grade = ‘A’ THEN ‘Very Good’
♦WHEN ‘A’ THEN ‘Very Good’;
♦WHEN ‘A’ THEN v_result := ‘Very Good’;
♦WHEN ‘A’ THEN ‘Very Good’ (*)
24. What will be the value of variable c after the following code is executed?
DECLARE
a BOOLEAN := TRUE;
b BOOLEAN := FALSE;
c NUMBER;
BEGIN
c :=
CASE
WHEN a AND b THEN 10
WHEN NOT a THEN 20
WHEN a OR b THEN 30
ELSE 40
END CASE;
END;
♦20
♦40
♦10
♦It can be a complete PL/SQL block
♦It returns a value (*)
♦It evaluates a condition and performs an action
v_grade CHAR(1) := NULL;
v_result VARCHAR2(10);
BEGIN
CASE v_grade
WHEN (‘A’ OR ‘B’) THEN v_result := ‘Very Good’;
WHEN (‘E’ OR ‘F’) THEN v_result := ‘Poor’;
ELSE v_result := ‘In Between’;
END CASE;
END;
♦In Between (*)
♦Null
♦Very Good
SELECT * FROM mytable
WHERE mycolumn = p_param;
Which of the following will open the cursor successfully?
♦OPEN c_curs(p_param = ABC);
♦OPEN c_curs(‘ABC’); (*)
♦OPEN c_curs USING (“ABC”);
OPEN c_curs(p_param);
♦You can declare the cursor FOR UPDATE.
♦You do not need to DECLARE the cursor at all.
♦You can use a single cursor to fetch a different set of rows each time the cursor is opened. (*)
♦It will execute much faster than a cursor without parameters.
FOR emp_record IN emp_cursor LOOP ……
Which of the following do NOT need to be coded explicitly? (Choose three.)
♦OPEN emp_cursor; (*)
♦DECLARE CURSOR emp_cursor IS …
♦emp_record emp_cursor%ROWTYPE; (*)
♦FETCH emp_cursor INTO emp_record; (*)
♦END LOOP;
30. When using a cursor FOR loop, OPEN, CLOSE and FETCH statements should not be explicitly coded. True or False?
♦True (*)
♦False
31. There are no employees in department_id 75.
Which of the following is NOT a valid cursor FOR loop with a subquery?
♦FOR emp_rec IN
(SELECT last_name, salary FROM employees) LOOP …
♦FOR emp_rec IN
(SELECT * FROM employees) LOOP …
♦FOR emp_rec IN
(SELECT last_name, salary FROM employees ORDER BY last_name) LOOP …
♦FOR emp_rec IN
(SELECT * FROM employees WHERE department_id = 75) LOOP …
♦None of the above. They are all valid. (*)
32. The employees table contains 20 rows. What will happen when the following code is executed?
DECLARE
CURSOR emp_curs IS
SELECT job_id FROM employees;
v_job_id employees.job_id%TYPE;
BEGIN
OPEN emp_curs;
LOOP
FETCH emp_curs INTO v_job_id;
DBMS_OUTPUT.PUT_LINE(v_job_id);
EXIT WHEN emp_curs%NOTFOUND;
END LOOP;
CLOSE emp_curs;
END;
♦The block will fail and an error message will be displayed.
♦21 rows of output will be displayed; the first job_id will be displayed twice.
♦21 rows of output will be displayed; the last job_id will be displayed twice. (*)
SELECT salary FROM employees;
v_salary employees.salary%TYPE;
BEGIN
OPEN emp_curs;
FETCH emp_curs INTO v_salary;
CLOSE emp_curs;
FETCH emp_curs INTO v_salary;
END;
♦The first employee row will be fetched twice.
♦The first two employee rows will be fetched.
♦The block will fail and a TOO_MANY_ROWS exception will be raised.
♦False (*)
CURSOR emp_curs IS
SELECT last_name, salary
FROM employees
ORDER BY salary;
v_last_name employees.last_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
…
Which of the following statements successfully opens the cursor and fetches the first row of the active set?
FETCH emp_curs INTO v_last_name, v_salary;
(*)
♦OPEN emp_curs;
FETCH emp_curs INTO v_salary, v_last_name;
♦OPEN emp_curs;
FETCH FIRST emp_curs INTO v_last_name, v_salary;
♦OPEN emp_curs;
FETCH emp_curs;
36. After a cursor has been closed, it can be opened again in the same PL/SQL block. True or False?
♦True (*)
♦False
37. Place the following statements in the correct sequence:
CLOSE my_curs;
CURSOR my_curs IS SELECT my_column FROM my_table;
FETCH my_curs INTO my_variable;
♦C,A,D,B (*)
♦A,C,D,B
♦C,A,B,D
♦False (*)
♦DML statements that process more than one row.
♦Queries that return more than one row. (*)
♦Data Definition Language (DDL) statements.
♦Queries that return a single row.
40. What will happen when the following code is executed?
DECLARE
CURSOR emp_curs IS
SELECT salary FROM employees;
v_salary employees.salary%TYPE;
BEGIN
FETCH emp_curs INTO v_salary;
DBMS_OUTPUT.PUT_LINE(v_salary);
CLOSE emp_curs;
END;
♦The first employee’s salary will be fetched and displayed.
♦All employees’ salaries will be fetched and displayed.
♦The execution will fail and an error message will be displayed. (*)
♦The lowest salary value will be fetched and displayed.
41. Consider the following cursor:
CURSOR c IS
SELECT e.last_name, e.salary, d.department_name
FROM employees e JOIN departments d
USING(department_id)
WHERE e.last_name=’Smith’
FOR UPDATE;
When the cursor is opened and rows are fetched, what is locked?
♦The whole EMPLOYEES table is locked.
♦In the EMPLOYEES table, only the ‘Smith’ rows are locked. Nothing in the DEPARTMENTS table is locked.
♦Each ‘Smith’ row is locked and Smith’s matching rows in DEPARTMENTS are locked. No other rows are locked in either table. (*)
♦The whole EMPLOYEES and DEPARTMENTS tables are locked.
♦Nothing is locked because the cursor was not declared with NOWAIT.
42. Examine the following code:
DECLARE
CURSOR c IS SELECT * FROM employees FOR UPDATE;
c_rec c%ROWTYPE;
BEGIN
OPEN c;
FOR i IN 1..20 LOOP
FETCH c INTO c_rec;
IF i = 6 THEN
UPDATE employees SET first_name = ‘Joe’
WHERE CURRENT OF c;
END IF;
END LOOP;
CLOSE c;
END;
Which employee row or rows will be updated when this block is executed?
♦The first 6 fetched rows will be updated.
♦No rows will be updated because you locked the rows when the cursor was opened.
♦The 6th fetched row will be updated. (*)
♦The block will not compile because the cursor should have been declared …. FOR UPDATE WAIT 5;
♦None of the above.
43. User TOM has locked a row in the WORKERS table. Now, user DICK wants to open the following cursor:
CURSOR c IS
SELECT * FROM workers FOR UPDATE NOWAIT;
What will happen when DICK opens the cursor and tries to fetch rows?
♦TOM’s session is rolled back. DICK’s session successfully fetches rows from the cursor.
♦DICK’s session waits indefinitely.
♦Both sessions wait for a few seconds; then the system breaks all locks and both sessions raise an exception.
♦DICK’s session immediately raises an exception. (*)
♦The c%NOWAIT attribute is set to TRUE.
44. Which of the following statements about the %ISOPEN cursor attribute is true?
♦You can issue the %ISOPEN cursor attribute only when a cursor is open.
♦You can issue the %ISOPEN cursor attribute only when more than one record is returned.
♦You can issue the %ISOPEN cursor attribute when a cursor is open or closed. (*)
♦If a cursor is open, then the value of %ISOPEN is false.
45. Which of the following cursor attributes is set to the total number of rows returned so far?
♦%ISOPEN
♦%NOTFOUND
♦%FOUND
♦%ROWCOUNT (*)
46. Examine the following code fragment:
DECLARE
CURSOR emp_curs IS
SELECT first_name, last_name FROM employees;
v_emp_rec emp_curs%ROWTYPE;
BEGIN
…
FETCH emp_curs INTO v_emp_rec;
DBMS_OUTPUT.PUT_LINE(.. Point A …);
…
To display the fetched last name, what should you code at Point A?
♦v_emp_rec.last_name (*)
♦v_emp_rec(last_name)
♦v_emp_rec
♦last_name
♦None of the above
47. The following cursor has been declared:
CURSOR emp_curs IS
SELECT first_name, last_name, job_id, salary
FROM employees;
Which of the following correctly declares a composite record with the same structure as the cursor?
♦emp_rec emp_rec%ROWTYPE;
♦emp_rec emp_curs%TYPE;
♦emp_rec emp_curs%ROWTYPE; (*)
♦emp_rec cursor%ROWTYPE;
48. Which of the following is a good reason to declare and use multiple cursors in a single PL/SQL block?
♦Multiple cursors improve performance. They are faster than using a single cursor.
♦Multiple cursors use less memory than a single cursor.
♦Multiple cursors allow us to fetch rows from two or more related tables without using a JOIN. (*)
♦Multiple cursors are the only way to use cursors with parameters.
♦Multiple cursors can be opened many times, while a single cursor can be opened only once.
49. You want to display all locations, and the departments in each location. Examine the following code:
DECLARE
CURSOR loc_curs IS SELECT * FROM locations;
CURSOR dept_curs(p_loc_id NUMBER) IS
SELECT * FROM departments WHERE location_id = p_loc_id;
BEGIN
FOR loc_rec IN loc_curs LOOP
DBMS_OUTPUT.PUT_LINE(loc_rec.city);
FOR dept_rec IN dept_curs(– Point A –) LOOP
DBMS_OUTPUT.PUT_LINE(dept_rec.department_name);
END LOOP;
END LOOP;
END;
What should you code at Point A?
♦p_loc_id
♦location_id
♦null
♦LOOP … END LOOP;
♦loc_rec.location_id (*)
50. When using multiple nested cursors, what kinds of loops can you use?
♦Cursor FOR loops only
♦Basic loops only.
♦WHILE loops only.
♦None of the above.
♦All of the above. (*)
InfoCad Blog 