Premium

Oracle Advanced SQL and PL/SQL Developer Certification Questions and Answers (Dumps and Practice Questions)



Question : View the Exhibit and examine the structure of the products table.
What would be the outcome if all the parentheses are removed from the above statement?
: View the Exhibit and examine the structure of the products table.
1. It produces a syntax error.
2. The result remains unchanged.
3. The total price value would be lower than the correct value.
4. The total price value would be higher than the correct value.

Correct Answer : 2
Explanation:
Operator precedences are shown in the following list, from highest precedence to the lowest. Operators that are shown together on a line have the same precedence.

INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
, >>
&
|
= (comparison), , >=, >, !=, IS, LIKE, REGEXP, IN
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
&&, AND
XOR
||, OR
= (assignment), :=
The precedence of = depends on whether it is used as a comparison operator (=) or as an assignment operator (=). When used as a comparison operator, it has the same precedence as , >=, >, !=, IS, LIKE, REGEXP, and IN. When used as an assignment operator, it has the same precedence as :=. Section 13.7.4, "SET Syntax", and Section 9.4, "User-Defined Variables", explain how MySQL determines which interpretation of = should apply.






Question : Examine the data in the PROMO_BEGIN_DATE column of the promotions table:
: Examine the data in the PROMO_BEGIN_DATE column of the promotions table:
1. A
2. B
3. C
4. D

Correct Answer : 1

Explanation: DECODE compares expr to each search value one by one. If expr is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null. The arguments can be any of the numeric types (NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or character types. If expr and search are character data, then Oracle compares them using nonpadded comparison semantics. expr, search, and result can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 datatype and is in the same character set as the first result parameter.

If the first search-result pair are numeric, then Oracle compares all search-result expressions and the first expr to determine the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype. The search, result, and default values can be derived from expressions. Oracle Database uses short-circuit evaluation. That is, the database evaluates each search value only before comparing it to expr, rather than evaluating all search values before comparing any of them with expr. Consequently, Oracle never evaluates a search if a previous search is equal to expr. Oracle automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle automatically converts the return value to the same datatype as the first result. If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the datatype VARCHAR2. In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null.
The maximum number of components in the DECODE function, including expr, searches, results, and default, is 255.

The SUBSTR functions return a portion of char, beginning at character position, substring_length characters long. SUBSTR calculates lengths using characters as defined by the input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses Unicode complete characters. SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points.
If position is 0, then it is treated as 1.
If position is positive, then Oracle Database counts from the beginning of char to find the first character.
If position is negative, then Oracle counts backward from the end of char.
If substring_length is omitted, then Oracle returns all characters to the end of char. If substring_length is less than 1, then Oracle returns null.
char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. Both position and substring_length must be of datatype NUMBER, or any datatype that can be implicitly converted to NUMBER, and must resolve to an integer. The return value is the same datatype as char. Floating-point numbers passed as arguments to SUBSTR are automatically converted to integers.





Question : You want to display the date for the first Monday of the next month and issue the following
command:

SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), 'MON'),
'dd "is the first Monday for" fmmonth rrrr')
FROM DUAL;

What is the outcome?


: You want to display the date for the first Monday of the next month and issue the following
1. It executes successfully and returns the correct result.
2. It executes successfully but does not return the correct result.
3. It generates an error because TO_CHAR should be replaced with TO_DATE.
4. It generates an error because rrrr should be replaced by rr in the format string.
5. It generates an error because fm and double quotation marks should not be used in the format string.

Correct Answer : 1
Explanation: The Oracle/PLSQL NEXT_DAY function returns the first weekday that is greater than a date.

SYNTAX

The syntax for the Oracle/PLSQL NEXT_DAY function is:

NEXT_DAY( date, weekday )
Parameters or Arguments

date is used to find the next weekday.

weekday is a day of the week (ie: SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY)
NEXT_DAY('01-Aug-03', 'TUESDAY')
Result: '05-Aug-03'

NEXT_DAY('06-Aug-03', 'WEDNESDAY')
Result: '13-Aug-03'

LAST_DAY returns the date of the last day of the month that contains date. The return type is always DATE, regardless of the datatype of date. Examples The following statement determines how many days are left in the current month.

SELECT SYSDATE, LAST_DAY(SYSDATE) "Last", LAST_DAY(SYSDATE) - SYSDATE "Days Left" FROM DUAL;

SYSDATE Last Days Left
--------- --------- ----------
30-MAY-01 31-MAY-01 1


Related Questions

Question : You issue the following command to drop the products table:
SQL> DROP TABLE products;
Which three statements are true about the implication of this command?
A. All data along with the table structure is deleted.
B. A pending transaction in the session is committed.
C. All indexes on the table remain but they are invalidated.
D. All views and synonyms remain but they are invalidated.
E. All data in the table is deleted but the table structure remains.
 : You issue the following command to drop the products table:
1. A,B,C
2. B,C,E
3. A,C,E
4. D,E,F
4. A,B,D



Question : Evaluate the following SQL statement:
Which statement is true regarding the above query if one of the values generated by the subquery is null?
 : Evaluate the following SQL statement:
1. It produces an error.
2. It executes but returns no rows.
3. It generates output for null as well as the other values produced by the subquery.
4. It ignores the null value and generates output for the other values produced by the subquery.



Question :
:
1. A
2. B
3. C
4. D


Question : Which statement is true regarding the default behavior of the order by clause?
 : Which statement is true regarding the default behavior of the order by clause?
1. In a character sort, the values are case-sensitive.
2. NULL values are not considered at all by the sort operation.
3. Only those columns that are specified in the select list can be used in the order by clause.
4. Numeric values are displayed from the maximum to the minimum value if they have decimal positions.



Question : View the Exhibits and
examine the structures of
the products and sales tables.
Which two SQL statements
would give the same output?

 : View the Exhibits and
1. A,C
2. B,D
3. C,D
4. A,D
5. B,C



Question :
 :
1. A
2. B
3. C
4. D