Premium

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?
SUBSTR('TechOnTheNet', -3, 3)
: What value is returned after executing the following statement?
1. Tec
2. Net
 3. Access Mostly Uused Products by 50000+ Subscribers
4. None of the above



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.
Let's look at some Oracle SUBSTR function examples and explore how to use the SUBSTR function in Oracle/PLSQL.
For example:
SUBSTR('This is a test', 6, 2)
Result: 'is'
SUBSTR('This is a test', 6)
Result: 'is a test'
SUBSTR('TechOnTheNet', 1, 4)
Result: 'Tech'
SUBSTR('TechOnTheNet', -3, 3)
Result: 'Net'
SUBSTR('TechOnTheNet', -6, 3)
Result: 'The'
SUBSTR('TechOnTheNet', -8, 2)
Result: 'On'






Question : What value is returned after executing the following statement?
INSTR('Tech on the net', 'e')
: What value is returned after executing the following statement?
1. the first occurrence of 'e'
2. the last occurrence of 'e'
 3. Access Mostly Uused Products by 50000+ Subscribers
4. None of the above



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

INSTR( string, substring [, start_position [, nth_appearance ] ] )
Parameters or Arguments

string is the string to search. string can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

substring is the substring to search for in string. substring can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

start_position is the position in string where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the INSTR function counts back start_position number of characters from the end of string and then searches towards the beginning of string.

nth_appearance is the nth appearance of substring. This is optional. If omitted, it defaults to 1.

NOTE

If substring is not found in string, then the INSTR function will return 0.






Question : What value is returned after executing the following statement?
INSTR('Tech on the net', 'e', -3, 2)
: What value is returned after executing the following statement?
1. the first occurrence of 'e'
2. the last occurrence of 'e'
 3. Access Mostly Uused Products by 50000+ Subscribers
4. None of the above



Correct Answer : Get Lastest Questions and Answer :
Exp: The syntax for the Oracle/PLSQL INSTR function is: INSTR( string, substring [, start_position [, nth_appearance ] ] )
Parameters or Arguments string is the string to search. string can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
substring is the substring to search for in string. substring can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
start_position is the position in string where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the INSTR function counts back start_position number of characters from the end of string and then searches towards the beginning of string. nth_appearance is the nth appearance of substring. This is optional. If omitted, it defaults to 1. If substring is not found in string, then the INSTR function will return 0. Let's look at some Oracle INSTR function examples and explore how to use the INSTR function in Oracle/PLSQL.

For example:
INSTR('Tech on the net', 'e')
Result: 2 (the first occurrence of 'e')
INSTR('Tech on the net', 'e', 1, 1)
Result: 2 (the first occurrence of 'e')
INSTR('Tech on the net', 'e', 1, 2)
Result: 11 (the second occurrence of 'e')
INSTR('Tech on the net', 'e', 1, 3)
Result: 14 (the third occurrence of 'e')
INSTR('Tech on the net', 'e', -3, 2)
Result: 2




Related Questions

Question : Examine the data in the ORD_ITEMS table:
Evaluate the given query:
Which statement is true regarding the outcome of the above query?
 : Examine the data in the ORD_ITEMS table:
1. It gives an error because the having clause should be specified after the group by clause.
2. It gives an error because all the aggregate functions used in the having clause must be specified in the select list.
3. Access Mostly Uused Products by 50000+ Subscribers
double the minimum quantity of that item in the table.
4. It displays the item nos with their average quantity where the average quantity is more than
double the overall minimum quantity of all the items in the table.





Question : View the Exhibit and examine the data in the promotions table.
PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr.
You need to produce a report that provides the name, cost, and start date of all promos in the post
category that were launched before January 1, 2000.
Which SQL statement would you use?
 : View the Exhibit and examine the data in the promotions table.
1. A
2. B
 3. Access Mostly Uused Products by 50000+ Subscribers
4. D



Question : You need to create a table with the following column specifications:
1. Employee ID (numeric data type) for each employee
2. Employee Name (character data type) that stores the employee name
 3. Access Mostly Uused Products by 50000+ Subscribers
4. Status (character data type), that contains the value 'active1 if no data is entered
5. Resume (character large object [CLOB] data type), which contains the resume submitted by the
employee
Which is the correct syntax to create this table?
: You need to create a table with the following column specifications:
1. It fails because the DB_8K_CACHE_SIZE parameter cannot be changed dynamically.
2. It succeeds only if memory is available from the autotuned components if SGA.
 3. Access Mostly Uused Products by 50000+ Subscribers
4. It fails because an increase in DB_8K_CACHE_SIZE cannot be accommodated within SGA_MAX_SIZE.


Question : Examine the structure of the sales table:
Evaluate the given create table statement:
Which two statements are true about the creation of the SALES1 table?

 : Examine the structure of the sales table:
1. The SALES1 table is created with no rows but only a structure.
2. The SALES1 table would have primary key and unique constraints on the specified columns.
 3. Access Mostly Uused Products by 50000+ Subscribers
4. The SALES1 table would have not null and unique constraints on the specified columns.
5. The SALES1 table would not be created because column-specified names in the select and
create table clauses do not match


Question : Which two statements are true regarding subqueries?
A. A subquery can retrieve zero or more rows.
B. Only two subqueries can be placed at one level.
C. A subquery can be used only in SQL query statements.
D. A subquery can appear on either side of a comparison operator.
E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT
statement.
 : Which two statements are true regarding subqueries?
1. A,C
2. B,D
 3. Access Mostly Uused Products by 50000+ Subscribers
4. C,D
5. A,E


Question : Examine the structure of the products table:
You want to display the names of the products that have the highest total value for UNIT_PRICE * QTY_IN_HAND.
Which SQL statement gives the required output?
 : Examine the structure of the products table:
1. A
2. B
 3. Access Mostly Uused Products by 50000+ Subscribers
4. D