Written a PL/SQL loop, you need to test if the current FETCH was successful. Which SQL cursor attribute would you use to accomplish this task?

A: SQL%ISOPEN
B: SQL%ROWCOUNT
C: SQL%FOUND
D: This task cannot be accomplished with a SQL cursor attribute.
E: A SQL cursor attribute cannot be used within a PL/SQL loop.

ANSWER: C

EXPLANATION: Answer C is correct because of SQL%FOUND attribute returns TRUE if current FETCH have been successful.

INCORRECT ANSWERS:

A: SQL%ISOPEN attribute returns TRUE if cursor is OPEN and ready for use.
B: SQL%ROWCOUNT returns the number of rows that were processed by the statement.
D: This task can be successfully accomplished with a SQL%FOUND cursor attribute.
E: PL/SQL loop can contain a SQL cursor attribute.

Which statement about SQL is true?

A: Null values are displayed last in the ascending sequences.
B: Data values are displayed in descending order by default.
C: You cannot specify a column alias in an ORDER BY clause.
D: You cannot sort query results by a column that is not included in the SELECT list.
E: The results are sorted by the first column in the SELECT list, if the ORDER BY clause is not provided.

ANSWER: A

EXPLANATION: Answer A is correct because of null values are displayed last in the ascending sequences.

INCORRECT ANSWERS:

B: Data values are displayed in ASCENDING order by default.
C: It is possible to specify a column alias in an ORDER BY clause (but not in a GROUP BY clause).
D: You can sort query results by a column that is not included in the SELECT list.
E: The results will not be sorted at all, if the ORDER BY clause was not used in query.

You need to store currency data and you know that data will always have two digits to the right of the decimal points. However the number of digits to the left of the decimal place will vary greatly. Which data type would be most appropriate to store the data?

A: NUMBER
B: NUMBER(T)
C: LANG
D: LANGRA

ANSWER: A

EXPLANATION: Answer A is correct because by default NUMBER is NUMBER(L, P) type, which is always stored as variabl length data, where 1 byte is used to store the exponent, 1 byte is used to store for every two significant digits of the number’s mantissa, and 1 byte is used for negative numbers if the number of significant digits is less than 38 bytes.

INCORRECT ANSWERS:

B: NUMBER(T) type will not allow to keep more than T numbers of digits to the left of the decimal place, but question says that it will vary greatly.
C: There is no type LANG in Oracle 8i.
D: Type LANGRA does not exists too.

You have decided to permanently remove all the data from the STUDENT table and you need the table structure in the future. Which single command performs this?

A: DROP TABLE student;
B: TRUNCATE TABLE student;
C: DELETE* FROM student;
D: TRUNCATE TABLE student KEEP STRUCTURE;
E: DELETE * FROM student KEEP STRUCTURE.

ANSWER: B

EXPLANATION: Answer B is correct because after truncating table you delete all data and keep table and its structure for future use. Also command TRUNCATE reset highwatermark level to zero for table.

INCORRECT ANSWERS:

A: This DDL operation will drop table and its structure without possibility to rollback this operation.
C: This operation can be used to perform question task, but it works slowly and usually used when you need to delete NOT ALL table rows. It does not reset high water mark for table.
D: Incorrect clause KEEP STRUCTURE inside TRUNCATE TABLE command.
E: Incorrect clause KEEP STRUCTURE inside DELETE command.