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

Question : What value is returned after executing the following statement?
LENGTH(' ')

1. 0
2. 1
3. Access Mostly Uused Products by 50000+ Subscribers
4. Null

Correct Answer : Get Lastest Questions and Answer :
Exp: The syntax for the Oracle/PLSQL LENGTH function is:

LENGTH( string1 )
Parameters or Arguments

string1 is the string to return the length for.

NOTE

If string1 is NULL, then the LENGTH function will return NULL.
et's look at some Oracle LENGTH function examples and explore how to use the LENGTH function in Oracle/PLSQL.

For example:
LENGTH(NULL)
Result: NULL

LENGTH('')
Result: NULL

LENGTH(' ')
Result: 1

LENGTH('Tech on the Net')
Result: 15

LENGTH('Tech on the Net ')
Result: 16

Question : What value is returned after executing the following statement?
SUBSTR('This is a test', 6, 2)

1. si
2. is
3. Access Mostly Uused Products by 50000+ Subscribers
4. Null

Correct Answer : Get Lastest Questions and Answer :
Exp: The syntax for the Oracle/PLSQL SUBSTR function is:

SUBSTR( string, start_position, [ length ] )
Parameters or Arguments

string is the source string.

start_position is the position for extraction. The first position in the string is always 1.

length is optional. It is the number of characters to extract. If this parameter is omitted, the SUBSTR function will return the entire string.

NOTE

If start_position is 0, then the SUBSTR function treats start_position as 1 (ie: the first position in the string).
If start_position is a positive number, then the SUBSTR function starts from the beginning of the string.
If start_position is a negative number, then the SUBSTR function starts from the end of the string and counts backwards.
If length is a negative number, then the SUBSTR function will return a NULL value.

Question : What value is returned after executing the following statement?
SUBSTR('This is a test', 6)

1. i
2. is a test
3. Access Mostly Uused Products by 50000+ Subscribers
4. a

Correct Answer : Get Lastest Questions and Answer :
Exp: The syntax for the Oracle/PLSQL SUBSTR function is:

SUBSTR( string, start_position, [ length ] )
Parameters or Arguments

string is the source string.

start_position is the position for extraction. The first position in the string is always 1.

length is optional. It is the number of characters to extract. If this parameter is omitted, the SUBSTR function will return the entire string.

NOTE

If start_position is 0, then the SUBSTR function treats start_position as 1 (ie: the first position in the string).
If start_position is a positive number, then the SUBSTR function starts from the beginning of the string.
If start_position is a negative number, then the SUBSTR function starts from the end of the string and counts backwards.
If length is a negative number, then the SUBSTR function will return a NULL value.

Related Questions

Question : View the Exhibit and examine the
structure of the SALES table.
The following query is written to retrieve all
those product IDs from the SALES table that have
more than 55000 sold and have been ordered more than 10 times.
Which statement is true regarding this SQL statement?

1. It executes successfully and generates the required result.
2. It produces an error because count(*) should be specified in the SELECT clause also.
3. Access Mostly Uused Products by 50000+ Subscribers
4. It executes successfully but produces no result because COUNT (prod_id) should be used instead of COUNT (*).

Question : View the Exhibit and examine the structure of the customers table.
Using the customers table, you need to generate a report that shows an increase in the credit limit
by 15% for all customers. Customers whose credit limit has not been entered should have the
message "Not Available" displayed.
Which SQL statement would produce the required result?

1. A
2. B
3. Access Mostly Uused Products by 50000+ Subscribers
4. D

Question : View the Exhibit and examine the structure of the promotions table.
Evaluate the given SQL statement:
Which statement is true regarding the outcome of the above query?

1. It shows COST_REMARK for all the promos in the table.
2. It produces an error because the SUBQUERY gives an error.
3. Access Mostly Uused Products by 50000+ Subscribers
4. It produces an error because SUBQUERIES cannot be used with the case expression.

Question : Examine the structure and data of the CUST_TRANS table:
Dates are stored in the default date format dd-mon-rr in the CUST_TRANS table. Which three
SQL statements would execute successfully?
1. SELECT transdate + '10' FROM cust_trans;
2. SELECT * FROM cust_trans WHERE transdate = '01-01-07';
3. Access Mostly Uused Products by 50000+ Subscribers
4. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';
5. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;

1. 2, 4, 5
2. 2, 1, 4, 3, 5
3. Access Mostly Uused Products by 50000+ Subscribers
4. 1, 2, 4, 5
5. 1, 3,4

Question : View the Exhibit and examine the structure of the customers table.
NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY
that have the same data types and size as the corresponding columns in the customers table.
Evaluate the following insert statement:
The insert statement fails when executed.
What could be the reason?

1. The values clause cannot be used in an INSERT with a subquery.
2. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match.
3. Access Mostly Uused Products by 50000+ Subscribers
4. The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS table.

Question : YOU need to display the date ll-oct- in words as 'Eleventh of October, Two Thousand
Seven'.
Which SQL statement would give the required result?

1. A
2. B
3. Access Mostly Uused Products by 50000+ Subscribers
4. D