1. PL/SQL extends SQL by including all of the following except:
♦variables
♦conditional statements
♦reusable program units
♦constants
♦nonprocedural constructs (*)
2. The P in PL/SQL stands for:
♦Processing
♦Procedural (*)
♦Primary
♦Proprietary
3. Which of the following statements about PL/SQL and SQL is true?
♦PL/SQL and SQL are both ANSI-compliant.
♦PL/SQL and SQL can be used with many types of databases, including Oracle.
♦PL/SQL and SQL are both Oracle proprietary programming languages.
♦PL/SQL allows basic program logic and control flow to be combined with SQL statements. (*)
4. Using Oracle Application Express, you can create Web applications that include PL/SQL. True or False?
♦True (*)
♦False
5. Comparing PL/SQL with other languages such as C and Java, which of the following statements is true?
♦PL/SQL is harder to learn
♦PL/SQL is easier to learn and more efficient (*)
♦PL/SQL is easier to learn but less efficient
♦PL/SQL is easier to learn and does not require an Oracle database or tool
6. The fact that PL/SQL is portable is a good thing because:
♦Exceptions can be ported to different operating systems
♦Blocks can be sent to the operating system.
♦PL/SQL code can be developed on one platform and deployed on another (*)
♦PL/SQL code can be run on any operating system without a database
7. Which lines of code will correctly display the message “The cat sat on the mat”? (Choose two.)
♦DBMS_OUTPUT.PUT_LINE(‘The cat sat on the mat’); (*)
♦DBMS_OUTPUT.PUT_LINE(The cat sat on the mat);
♦DBMS_OUTPUT.PUT_LINE(‘The cat’ || ‘sat on the mat’);
♦DBMS_OUTPUT.PUT_LINE(‘The cat sat ‘ || ‘on the mat’); (*)
8. Every PL/SQL anonymous block must start with the keyword DECLARE. True or False?
♦True
♦False (*)
9. Which of the following tools can NOT be used to develop and test PL/SQL code?
♦Oracle Jdeveloper
♦Oracle Application Express
♦Oracle JSQL (*)
♦Oracle iSQL*Plus
10. Which component of Oracle Application Express is used to enter and run SQL statements and PL/SQL blocks?
♦Application Builder
♦SQL Workshop (*)
♦Utilities
♦Object Browser
11. Which statements are optional in a PL/SQL block? (Choose two.)
♦DECLARE (*)
♦BEGIN
♦EXCEPTION (*)
♦END;
12. Which keywords must be included in every PL/SQL block? (Choose two.)
♦DECLARE
♦END; (*)
♦EXCEPTION
♦BEGIN (*)
♦DBMS_OUTPUT.PUT_LINE
13. PL/SQL can convert a VARCHAR2 value containing alphabetic characters to a NUMBER value. True or False?
♦True
♦False (*)
14. TO_NUMBER, TO_CHAR, and TO_DATE are all examples of:
♦Implicit conversion functions
♦Explicit conversion functions (*)
♦Character functions
♦Operators
15. Examine the following code:
1 DECLARE
2 x NUMBER;
3 BEGIN
4 x:= ‘300’;
5 END;
After line 4, what is the value of x?
♦’300′
♦300 (*)
♦NULL
16. Which of the following are disadvantages of implicit data type conversions? (Choose two.)
♦The code is harder to read and understand (*)
♦You cannot store alphabetic characters in a variable of data type NUMBER
♦If Oracle changes the conversion rules in the future, your code may not work any more (*)
♦Oracle cannot implicitly convert a number value to a character string
17. When you use a function to convert data types in a PL/SQL program, it is called ______ conversion.
♦Explicit (*)
♦Implicit
♦TO_CHAR
18. Single row character functions are valid SQL functions in PL/SQL. True or False?
♦True (*)
♦False
19. What is the output when the following program is executed?
set serveroutput on
DECLARE
a VARCHAR2(10) := ‘333’;
b VARCHAR2(10) := ‘444’;
c PLS_INTEGER;
d VARCHAR2(10);
BEGIN
c := TO_NUMBER(a) + TO_NUMBER(b);
d := a || b;
DBMS_OUTPUT.PUT_LINE(c);
DBMS_OUTPUT.PUT_LINE(d);
END;
♦Nothing. The code will result in an error.
♦c=777 and d=333444 (*)
♦c=777 and d=777
♦c=333444 and d=777
20. Examine the following code. What is the final value of V_MYBOOL ?
DECLARE
v_mynumber NUMBER;
v_mybool BOOLEAN ;
BEGIN
v_mynumber := 6;
v_mybool := (v_mynumber BETWEEN 10 AND 20);
v_mybool := NOT (v_mybool);
END;
♦False
♦Data type (*)
♦Table name
♦Column name
♦Constraint
22. Which of the following declarations is invalid?
♦v_count PLS_INTEGER:=0;
♦college_name VARCHAR2(20):=’Harvard’;
♦v_pages CONSTANT NUMBER; (*)
♦v_start_date DATE := sysdate+1;
23. Which of the following should NOT be used as the name of a variable?
♦A table name.
♦A table column name. (*)
♦The database name.
24. Valid identifiers begin with a
♦Number
♦Letter (*)
♦Special character
25. Which of the following are valid identifiers? (Choose two.)
♦yesterday (*)
♦yesterday’s date
♦number_of_students_in_the_class
♦v$testresult (*)
♦#students
26. Reserved words can be used as identifiers. True or False?
♦True
♦False (*)
27. Evaluate the following declaration. Determine whether or not it is legal.
DECLARE
maxsalary NUMBER(7) = 5000;
♦Correct.
♦Not correct. (*)
28. Variables can be assigned a value in both the Executable and Declaration sections of a PL/SQL program. True or False?
♦True (*)
♦False
29. Identify which of the following assignment statements are valid. (Choose three.)
♦v_last_name := Chandra;
♦v_blackout_date := ’31-DEC-2006′; (*)
♦v_population := 333444; (*)
♦v_music_type := ‘ROCK’; (*)
30. Is the following variable declaration correct or not ?
DECLARE
display_qty CONSTANT NUMBER;
♦Correct.
♦Not correct. (*)
31. A collection is a composite data type. True or False?
♦True (*)
♦False
32. Type of a variable determines the range of values the variable can have and the set of operations that are defined for values of the type.
♦True (*)
♦False
33. What is the data type of the variable V_DEPT_TABLE in the following declaration?
DECLARE
TYPE dept_table_type IS TABLE OF departments%ROWTYPE INDEX BY PLS_INTEGER; v_dept_table dept_table_type; …
♦Scalar
♦Composite (*)
♦LOB
34. In the following code, Line A causes an exception. What value will be displayed when the code is executed?
DECLARE
outer_var VARCHAR2(50) := ‘My’;
BEGIN
outer_var := outer_var || ‘ name’;
DECLARE
inner_var NUMBER;
BEGIN
inner_var := ‘Mehmet’; — Line A
outer_var := outer_var || ‘ is’;
END;
outer_var := outer_var || ‘ Zeynep’;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(outer_var);
END;
♦My
♦My name (*)
♦My name is
♦My name is Zeynep
35. PL/SQL does not look _________ in the child blocks.
♦Inward
♦Upward
♦Outward
♦Downward (*)
36. Examine the following code. Line A causes an exception. What will be displayed when the block is executed?
DECLARE
var_a NUMBER := 6;
var_b DATE;
BEGIN
var_a := var_a * 2;
var_b := ’28 December 2006′; — Line A
var_a := var_a * 2;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(var_a);
END;
♦12 (*)
♦24
♦6
♦Nothing will be displayed
37. What will be displayed when the following code is executed?
DECLARE
x VARCHAR2(6) := ‘Chang’;
BEGIN
DECLARE
x VARCHAR2(12) := ‘Susan’;
BEGIN
x := x || x;
END;
DBMS_OUTPUT.PUT_LINE(x);
END;
♦Chang (*)
♦ChangChang
♦SusanChang
♦The code will fail with an error
♦False
39. Which of the following will help to make code easier to read?
♦Naming variables.
♦Using %Type.
♦Including comments in the code. (*)
40. Which of the following is an example of using a case convention for good programming practice?
♦Assign variables by using functions.
♦Declare variables in the DECLARE section.
♦Declare data types in uppercase. (*)
♦Include an exception handler in every PL/SQL block.
41. Using standards for naming conventions is recommended. True or False?
♦True (*)
♦False
42. 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;
♦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.
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;
♦False (*)
WHERE department_id=60;
(*)
WHERE department_id=60;
45. 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 *
INTO v_holdit
FROM employees;
♦SELECT last_name
INTO v_holdit
FROM employees;
♦SELECT last_name
INTO v_holdit
FROM employees
WHERE employee_id=100;
(*)
♦SELECT salary
INTO v_holdit
FROM employees
WHERE employee_id=100;
46. 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?
♦No data is inserted and no errors occur.
♦No data is inserted and an error occurs
♦Two rows are inserted and no errors occur.
♦Two rows are inserted and an error occurs. (*)
47. The following anonymous block of code is run:
BEGIN
INSERT INTO countries (id, name)
VALUES (‘XA’, ‘Xanadu’);
INSERT INTO countries (id, name)
VALUES (‘NV’,’Neverland’);
COMMIT;
COMMIT;
ROLLBACK;
END;
What happens when the block of code finishes?
♦You have nothing new; the last ROLLBACK undid the INSERTs.
♦You have the rows added twice; there are four new rows.
♦You have the two new rows added. (*)
♦You get an error; you cannot COMMIT twice in a row.
48. Which SQL statement can NOT use an implicit cursor?
♦A DELETE statement
♦An UPDATE statement
♦A SELECT statement that returns multiple rows (*)
♦A SELECT statement that returns one row
49. You declare an implicit cursor in the DECLARE section of a PL/SQL block. True or False?
♦True
♦False (*)
50. Assume there are 5 employees in Department 10. What happens when the following statement is executed?
UPDATE employees
SET salary=salary*1.1;
♦All employees get a 10% salary increase. (*)
♦No rows are modified because you did not specify “WHERE department_id=10”
♦A TOO_MANY_ROWS exception is raised.
♦An error message is displayed because you must use the INTO clause to hold the new salary.
InfoCad Blog 