♦Procedural (*)
♦Primary
♦Proprietary
♦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. (*)
3. A program which specifies a list of operations to be performed sequentially to achieve the desired result can be called:
♦declarative
♦nondeclarative
♦procedural (*)
♦low level
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. Which of the following statements about exception handling in PL/SQL is false?
♦You can prepare for database exceptions by creating exception handlers.
♦You can prepare for application exceptions by creating exception handlers.
♦Exception handling code tells your program what to do when an error is encountered.
♦Exception handling code can be grouped together in a PL/SQL block.
♦None of the above (*)
7. What kind of block is defined by the following PL/SQL code?
DBMS_OUTPUT.PUT_LINE(‘My first quiz’);
END;
♦subroutine
♦function
♦anonymous (*)
♦To allow a set of statements to be executed repeatedly
♦To display results to check if our code is working correctly (*)
♦To store new rows in the database
2. EXCEPTION
3. DECLARE
4. BEGIN
Arrange the parts in order.
♦2,1,4,3
♦3,4,2,1 (*)
♦3,2,4,1
♦4,3,2,1
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 keywords must be included in every PL/SQL block? (Choose two.)
♦DECLARE
♦END; (*)
♦EXCEPTION
♦BEGIN (*)
♦DBMS_OUTPUT.PUT_LINE
12. In which part of the PL/SQL block are declarations of variables defined?
♦Executable
♦Exception
♦Declarative (*)
♦Definition
13. Errors are handled in the Exception part of the PL/SQL block. True or False?
♦True (*)
♦False
14. Evaluate the following declaration. Determine whether or not it is legal.
maxsalary NUMBER(7) = 5000;
♦Not correct. (*)
15. Variables can be used in the following ways in a PL/SQL block. (Choose two.)
♦To store data values. (*)
♦To rename tables and columns.
♦To refer to a single data value several times. (*)
♦To comment code.
16. When a variable is defined using the NOT NULL keywords, the variable must contain a value. True or False?
♦True (*)
♦False
17. 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’; (*)
18. Is the following variable declaration correct or not ?
display_qty CONSTANT NUMBER;
♦Not correct. (*)
19. 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
20. Examine the following code:
2 x NUMBER;
3 BEGIN
4 x:= ‘300’;
5 END;
After line 4, what is the value of x?
♦’300′
♦300 (*)
♦NULL
21. If today’s date is 14th June 2007, which statement will correctly convert today’s date to the value: June 14, 2007 ?
♦TO_CHAR(sysdate)
♦TO_DATE(sysdate)
♦TO_DATE(sysdate,’Month DD, YYYY’)
♦TO_CHAR(sysdate, ‘Month DD, YYYY’) (*)
22. The DECODE function is available in PL/SQL procedural statements. True or False?
♦True
♦False (*)
23. When you use a function to convert data types in a PL/SQL program, it is called ______ conversion.
♦Explicit (*)
♦Implicit
♦TO_CHAR
24. What is the output when the following program is executed?
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;
♦c=777 and d=333444 (*)
♦c=777 and d=777
♦c=333444 and d=777
♦Explicit conversion functions (*)
♦Character functions
♦Operators
v_myvar NUMBER;
BEGIN
v_myvar := 1 + 2 * 3;
v_myvar := v_myvar * 2;
END;
49
14 (*)
18
2. False
3. True
4. 0
Which of the above can be assigned to a Boolean variable?
♦2 and 3
♦2, 3 and 4
♦1, 2 and 3 (*)
♦1, 2, 3 and 4
28. You need to declare a variable to hold a value which has been read from the SALARY column of the EMPLOYEES table. Which of the following is an advantage of declaring the variable as: employees.salary%TYPE ?
♦It is shorter than coding NUMBER(8,2)
♦If the SALARY column is ALTERed later, the PL/SQL code need not be changed. (*)
♦It executes much faster than using NUMBER(8,2)
♦It allows the software to perform implicit data type conversions.
29. If you are using the %TYPE attribute, you can avoid hard coding the:
♦Data type (*)
♦Table name
♦Column name
♦Constraint
30. What is the data type of the variable V_DEPT_TABLE in the following declaration?
TYPE dept_table_type IS TABLE OF departments%ROWTYPE INDEX BY PLS_INTEGER; v_dept_table dept_table_type; …
♦Composite (*)
♦LOB
♦Composite
♦Reference
♦LOB (*)
♦False
♦variables
♦symbols (*)
♦They are named objects stored in the database
♦They are the building blocks of every PL/SQL program (*)
♦They are optional but can make a PL/SQL block execute faster
♦They are sequences of characters including letters, digits, tabs, returns and symbols (*)
35. Reserved words can be used as identifiers. True or False?
♦True
♦False (*)
36. When an exception occurs within a PL/SQL block, the remaining statements in the executable section of the block are skipped. True or False?
♦True (*)
♦False
37. When nested blocks are used, which blocks can or must be labeled?
♦The inner block must be labeled, the outer block can be labeled.
♦Both blocks must be labeled
♦Nested blocks cannot be labeled
♦The outer block must be labeled if it is to be referred to in the inner block. (*)
38. What will be displayed when the following code is executed?
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
39. An exception occurs within the inner block of two nested blocks. The inner block does not have an EXCEPTION section. What always happens?
♦Both blocks fail and an error message is displayed by the calling environment
♦The exception is propagated to the outer block (*)
♦Oracle automatically tries to re-execute the inner block
♦The user’s database session is automatically disconnected
40. What will be displayed when the following code is executed?
varA NUMBER := 12;
BEGIN
DECLARE
varB NUMBER := 8;
BEGIN
varA := varA + varB;
END;
DBMS_OUTPUT.PUT_LINE(varB);
END;
♦12
♦Nothing, the block will fail with an error (*)
♦20
♦VarB
FROM employees
WHERE employee_id=100;
What is the value of SQL%ISOPEN immediately after the SELECT statement is executed?
♦True
♦False (*)
♦Null
♦Error. That attribute does not apply for implicit cursors.
42. There are no employees in Department 77. What will happen when the following block is executed?
BEGIN
DELETE FROM employees
WHERE department_id=77;
END;
♦A NULL is displayed.
♦A zero (0) is displayed. (*)
♦An exception is raised because the block does not contain a COMMIT statement.
43. Which is the correct way to erase one row from a table?
♦REMOVE employee_id=100
FROM employees;
WHERE employee_id=100;
WHERE employee_id=100;
WHERE employee_id=100;
(*)
44. Which one of these SQL statements can be directly included in a PL/SQL executable block?
♦DELETE FROM employees
WHERE department_id=60;
(*)
♦SELECT salary FROM employees
WHERE department_id=60;
salary employees.salary%TYPE := 12000;
BEGIN
DELETE FROM employees
WHERE salary > salary;
END;
♦All rows in the table.
♦No rows. (*)
♦All rows whose SALARY column value is equal to 12000.
WHERE employee_id=100;
SET last_name=’Smith’;
(*)
♦DROP TABLE employees;
47. A variable is declared as:
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;
48. A variable is declared as:
v_salary employees.salary%TYPE;
BEGIN
Which of the following is a correct use of the INTO clause?
♦SELECT salary
INTO v_salary
FROM employees
WHERE employee_id=100;
(*)
♦SELECT v_salary
INTO salary
FROM employees
WHERE employee_id=100;
♦SELECT salary
FROM employees
INTO v_salary;SELECT salary
FROM employees
WHERE employee_id=100
INTO v_salary;
49. How many DML statements can be included in a single transaction?
♦Only one
♦None. A transaction cannot include DML statements.
♦A maximum of four DML statements
♦As many as needed (*)
50. The following anonymous block of code is run:
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.