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

Question : Select correct sql statement


1. INSERT INTO suppliers
(supplier_id supplier_name)
SELECT account_no, name
FROM customers
WHERE customer_id > 5000;
2. INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no || name
FROM customers
WHERE customer_id > 5000;
3. INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE customer_id > 5000;
4. All of the above


Correct Answer : Get Lastest Questions and Answer :

Explanation:By placing a SELECT statement within the INSERT statement, you can perform multiples inserts quickly.

With this type of insert, you may wish to check for the number of rows being inserted. You can determine the number of rows that will be inserted by running the following Oracle SELECT statement before performing the insert.






Question : What result is returned by the following statement?
SELECT COUNT(*) FROM DUAL;


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


Correct Answer : Get Lastest Questions and Answer :

Explanation:The DUAL table has one row and one column. The COUNT(*) function returns the number of rows in a table or group.






Question : Select the correct statement ...

1. Group functions may only be used when a GROUP BY clause is present.
2. Group functions can operate on multiple rows at a time.
3. Access Mostly Uused Products by 50000+ Subscribers
4. Group functions can execute multiple times within a single group.


Correct Answer : Get Lastest Questions and Answer :

Explanation: Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.

If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view. Many (but not all) aggregate functions that take a single argument accept these clauses: DISTINCT and UNIQUE, which are synonymous, cause an aggregate function to consider only distinct values of the argument expression. The syntax diagrams for aggregate functions in this chapter use the keyword DISTINCT for simplicity. ALL causes an aggregate function to consider all values, including all duplicates.

For example, the DISTINCT average of 1, 1, 1, and 3 is 2. The ALL average is 1.5. If you specify neither, then the default is ALL.

Some aggregate functions allow the windowing_clause, which is part of the syntax of analytic functions. Refer to windowing_clause for information about this clause. In the listing of aggregate functions at the end of this section, the functions that allow the windowing_clause are followed by an asterisk (*)

All aggregate functions except COUNT(*), GROUPING, and GROUPING_ID ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null. COUNT and REGR_COUNT never return null, but return either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null. The aggregate functions MIN, MAX, SUM, AVG, COUNT, VARIANCE, and STDDEV, when followed by the KEEP keyword, can be used in conjunction with the FIRST or LAST function to operate on a set of values from a set of rows that rank as the FIRST or LAST with respect to a given sorting specification. Refer to FIRST for more information.

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