SQL*Plus

1 Which command in SQL*Plus is used to save the query output to a file?
SPOOL

2 How would you extract a SQL statement in the SQL buffer of SQL*Plus?
Enter a SLASH (/)

3 What is the default display length of the DATE Datatype column?
Eight

4 Which Clause in a query limits the rows selected?
WHERE

5 Which SQL*Plus command always overwrites a file?
SPOOL

6 Which single-row function could you use to return a specific portion of a character?
SUBSTR

7 Which of the following is not a Group Function? (AVG, COUNT, LEAST, STDDEV, VARIANCE)
LEAST

8 When using multiple tables to query information, in which Clause do you specify the table names?
FROM

9 What are the special characters allowed in a table name? (&, #, @, $)
#, $

10 What is the default length of a CHAR Data type column, if no length is specified in the table definition?
1

11 Which named PL/Sql program must return a value?
FUNCTION must include a RETURN statement and must return a value

12 How do you return multiple values from a procedure?
Use OUT parameter
OUT parameters pass values out of a procedure
IN parameters pass values into the procedure

13 Which section of the PL/Sql block handles errors and abnormal conditions?
EXCEPTION SECTION

14 What is the mandatory clause in a SELECT statement when used inside a PL/Sql block?
INTO

15 In which section of a PL/Sql block is a constant assigned value?
DECLARATION SECTION

16 What is the name of the Pl/Sql block that is associated with a table and executes automatically when an event occurs?
TRIGGER

17 What type of constant can be defined when you declare a variable?
NOT NULL

18 What is the PL/Sql stored program that fire when an event occurs?
TRIGGER

19 In a PL/Sql block, what is the value of SQL%NOTFOUND before executing any DML Statement?
NULL

20 Which of the following requires an explicit cursor if processing more than one row? (SELECT, UPDATE, DELETE, INSERT)
SELECT

21 Which commands are allowed inside a PL/Sql block? (TRUNCATE, DELETE, SAVEPOINT, ALTER TABLE)
DELETE, SAVEPOINT
DDL Statements are not allowed inside a PL/Sql block
TRUNCATE and ALTER TABLE are DDL statements

22 What is the PRAGMA exception_init used for?
To associate an exception name with and exception number
Exception_init is used to associate (give a name) to an error number

23 What command do you use to induce an error condition?
RAISE

24 What causes a TOO_MANY_ROWS exception?
A SELECT INTO returned more than one row

25 Which of the following types of exceptions cannot be handled in an exception? (Syntax Errors, database Errors, Datatype Mismatch Errors, Divide By Zero Errors)
Syntax Errors
Syntax Errors are handled by PL/Sql compiler and not the runtime engine
The exception section cannot handle them

26 What function can be used to retrieve the error number for the current exception?
SQLCODE

27 What function can be used to retrieve the text associated with the current exception?
SQLERRM

28 What Clause do you need to include in your exception section to handle any exception not previously specified?
WHEN OTHERS

29 At a minimum, how many join conditions should be there in the WHERE Clause to avoid a Cartesian join if there are Three Tables in the FROM Clause?
2
There should be at least (n - 1) join conditions when joining (n) tables to avoid a Cartesian join

30 A view can only be used to query and update data, you cannot Insert into or delete from a view. True or false
FALSE

31 Which option is not available in Oracle when modifying tables? (Add new Columns, Rename an Existing Column, Drop an Existing Column)
Rename an Existing Column
You cannot rename an Existing column using ALTER TABLE