Premium

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



Question : Which of the following statements are syntactically correct?

: Which of the following statements are syntactically correct?
1. SELECT E.EMPLOYEE_ID, J.JOB_ID PREVIOUS_JOB, E.JOB_ID CURRENT_JOB FROM JOB_HISTORY J CROSS JOIN EMPLOYEES E ON (J.START_DATE=E.HIRE_DATE);
2. SELECT E.EMPLOYEE_ID, J.JOB_ID PREVIOUS_JOB, E.JOB_ID CURRENT_JOB FROM JOB_HISTORY J JOIN EMPLOYEES E ON (J.START_DATE=E.HIRE_DATE);
 3. Access Mostly Uused Products by 50000+ Subscribers
4. None of the above




Correct Answer : Get Lastest Questions and Answer :




Question : If the REGIONS table, which contains rows, is cross joined to the COUNTRIES table, which contains rows,
how many rows appear in the final results set? (Choose the best answer.)
: If the REGIONS table, which contains rows, is cross joined to the COUNTRIES table, which contains rows,
1. 100
2. 4
 3. Access Mostly Uused Products by 50000+ Subscribers
4. None of the above




Correct Answer : Get Lastest Questions and Answer :

Explanation: The cross join associates every four rows from the REGIONS table 25 times with the rows from the COUNTRIES table yielding a result set that contains 100 rows.



Question :Which three SQL statements would display the value . as $, .?
:Which three SQL statements would display the value . as $, .?
1. A,B,C
2. B,C,D
 3. Access Mostly Uused Products by 50000+ Subscribers
4. D,E,F
5. A,D,E



Correct Answer : Get Lastest Questions and Answer :
Explanation: TO_CHAR (number) converts n to a value of VARCHAR2 datatype, using the optional number format fmt. The value n can be of type NUMBER, BINARY_FLOAT, or BINARY_DOUBLE. If you omit fmt, then n is converted to a VARCHAR2 value exactly long enough to hold its significant digits.
Please refer to "Format Models" for information on number formats.
The 'nlsparam' argument specifies these characters that are returned by number format elements:
Decimal character , Group separator , Local currency symbol , International currency symbol , This argument can have this form:
'NLS_NUMERIC_CHARACTERS = ''dg'' NLS_CURRENCY = ''text'' NLS_ISO_CURRENCY = territory '
The characters d and g represent the decimal character and group separator, respectively. They must be different single-byte characters. Within the quoted string, you must use two single quotation marks around the parameter values. Ten characters are available for the currency symbol.
If you omit 'nlsparam' or any one of the parameters, then this function uses the default parameter values for your session.
Examples : The following statement uses implicit conversion to combine a string and a number into a number:
SELECT TO_CHAR('01110' + 1) FROM dual;
TO_C
----
Compare this example with the first example for TO_CHAR (character).
In the next example, the output is blank padded to the left of the currency symbol.
SELECT TO_CHAR(-10000,'L99G999D99MI') "Amount" FROM DUAL;
Amount
--------------
$10,000.00-
SELECT TO_CHAR(-10000,'L99G999D99MI', 'NLS_NUMERIC_CHARACTERS = '',.'' NLS_CURRENCY = ''AusDollars'' ') "Amount" FROM DUAL;
Amount
-------------------
AusDollars10.000,00-




Related Questions

Question : Select the correct statement which applies for below query.
SELECT supplier_id
FROM suppliers
INTERSECT
SELECT supplier_id
FROM orders;
 : Select the correct statement which applies for below query.
1. if a supplier_id appeared in the suppliers it would appear in your result set.
2. if a supplier_id appeared in orders table, it would appear in your result set.
3. Access Mostly Uused Products by 50000+ Subscribers
4. None of above


Question : Select the correct statement which applies for below query.
SELECT *
FROM customers
WHERE EXISTS (SELECT *
FROM order_details
WHERE customers.customer_id = order_details.customer_id);
 : Select the correct statement which applies for below query.
1. will return only first record from the customers table where there is at least one record in the order_details table with the matching customer_id.
2. will return all records from the customers table where there is at least one record in the order_details.
 3. Access Mostly Uused Products by 50000+ Subscribers
4. None of above


Question : A query can have a subquery embedded within it. Under what circumstances could there be more than one subquery?

: A query can have a subquery embedded within it. Under what circumstances could there be more than one subquery?
1. The outer query can include an inner query. It is not possible to have another query within the inner query.
2. It is possible to embed a single-row subquery inside a multiple-row subquery, but not the other way around.
 3. Access Mostly Uused Products by 50000+ Subscribers
4. Subqueries can be embedded within each other with no practical limitations on depth.




Question :

SELECT * FROM TABLE1 WHERE CLICKS > (SELECT AVG(CLICKS) FROM USERS);

When will the subquery be executed?


:
1. It will be executed before the outer query.
2. It will be executed after the outer query.
 3. Access Mostly Uused Products by 50000+ Subscribers
4. It will be executed once for every row in the EMPLOYEES table.



Question : SELECT A.USER_ID, A.LAST_NAME FROM TABLE A WHERE A.CLICKS> (SELECT avg(B.CLICKS) FROM TABLE B WHERE B.click_id=A.click_id);
When will the subquery be executed?



 : SELECT A.USER_ID, A.LAST_NAME FROM TABLE A WHERE A.CLICKS> (SELECT avg(B.CLICKS) FROM TABLE B WHERE B.click_id=A.click_id);
1. It will be executed before the outer query.
2. It will be executed after the outer query.
 3. Access Mostly Uused Products by 50000+ Subscribers
4. It will be executed once for every row in the EMPLOYEES table.





Question : What are the distinguishing characteristics of a scalar subquery?

: What are the distinguishing characteristics of a scalar subquery?
1. A scalar subquery returns one row.
2. A scalar subquery cannot be used in the SELECT LIST of the parent query.
 3. Access Mostly Uused Products by 50000+ Subscribers
4. 1 and 2
5. All 1,2 and 3