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?
SELECT suppliers.name, subquery1.total_amt
FROM suppliers,
(SELECT supplier_id, SUM(orders.amount) AS total_amt
FROM orders
GROUP BY supplier_id) subquery1
WHERE subquery1.supplier_id = suppliers.supplier_id;

1. It would be cross product between suppliers and subquery1
2. All rows from suppliers and subquery1 where condition match
3. Access Mostly Uused Products by 50000+ Subscribers
4. Error



Correct Answer : Get Lastest Questions and Answer :
Exp: In Oracle, a subquery is a query within a query. You can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.

WHERE CLAUSE

Most often, the subquery will be found in the WHERE clause. These subqueries are also called nested subqueries.

For example:

SELECT *
FROM all_tables tabs
WHERE tabs.table_name IN (SELECT cols.table_name
FROM all_tab_columns cols
WHERE cols.column_name = 'SUPPLIER_ID');
Limitations

Oracle allows up to 255 levels of subqueries in the WHERE clause.






Question : What value is returned after executing the following statement?
SELECT last_name
FROM contacts
WHERE REGEXP_LIKE (last_name, '^A(*)');

1. This REGEXP_LIKE example will return all contacts whose last_name starts with 'A'.
2. This REGEXP_LIKE example will return all contacts whose last_name ends with 'A'.
3. Access Mostly Uused Products by 50000+ Subscribers
4. Error



Correct Answer : Get Lastest Questions and Answer :
Exp: ^ Matches the beginning of a string. If used with a match_parameter of 'm', it matches the start of a line anywhere within expression.





Question : What value is returned after executing the following statement?
SELECT last_name FROM contacts WHERE REGEXP_LIKE (last_name, '(*)A$');

1. This REGEXP_LIKE example will return all contacts whose last_name starts with 'A'.
2. This REGEXP_LIKE example will return all contacts whose last_name ends with 'A'.
3. Access Mostly Uused Products by 50000+ Subscribers
4. Error

Correct Answer : Get Lastest Questions and Answer : Exp: ^ Matches the beginning of a string. If used with a match_parameter of 'm', it matches the start of a line anywhere within expression.
$ Matches the end of a string. If used with a match_parameter of 'm', it matches the end of a line anywhere within expression.
* Matches zero or more occurrences.
+ Matches one of more occurrences.
? Matches zero or one occurrence.
. Matches any character except NULL.
| Used like an "OR" to specify more than one alternative.
[ ] Used to specify a matching list where you are trying to match any one of the characters in the list.
[^ ] Used to specify a nonmatching list where you are trying to match any character except for the ones in the list.
( ) Used to group expressions as a subexpression.
{m} Matches m times.
{m,} Matches at least m times.
{m,n} Matches at least m times, but no more than n times.
\n n is a number between 1 and 9. Matches the nth subexpression found within ( ) before encountering \n.
[..] Matches one collation element that can be more than one character.
[::] Matches character classes.
[==] Matches equivalence classes.
\d Matches a digit character.
\D Matches a nondigit character.
\w Matches a word character.
\W Matches a nonword character.
\s Matches a whitespace character.
\S matches a non-whitespace character.
\A Matches the beginning of a string or matches at the end of a string before a newline character.
\Z Matches at the end of a string.
*? Matches the preceding pattern zero or more occurrences.
+? Matches the preceding pattern one or more occurrences.
?? Matches the preceding pattern zero or one occurrence.
{n}? Matches the preceding pattern n times.
{n,}? Matches the preceding pattern at least n times.
{n,m}? Matches the preceding pattern at least n times, but not more than m times.

Related 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




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




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




Question : What value is returned after executing the following statement?
SUBSTR('TechOnTheNet', -3, 3)

1. Tec
2. Net
3. Access Mostly Uused Products by 50000+ Subscribers
4. None of the above




Question : What value is returned after executing the following statement?
INSTR('Tech on the net', 'e')

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




Question : What value is returned after executing the following statement?
INSTR('Tech on the net', 'e', -3, 2)

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