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

Question : Select the correct statement which applies for below query.
SELECT supplier_id
FROM suppliers
INTERSECT
SELECT supplier_id
FROM orders;

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

Correct Answer : Get Lastest Questions and Answer : Exp: The Oracle INTERSECT operator is used to return the results of 2 or more SELECT statements. However, it only returns the rows selected by all queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.

Each SELECT statement within the Oracle INTERSECT must have the same number of fields in the result sets with similar data types.





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);

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

Correct Answer : Get Lastest Questions and Answer : Exp: The Oracle EXISTS condition is used in combination with a subquery and is considered "to be met" if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX

The syntax for the Oracle EXISTS condition is:

WHERE EXISTS ( subquery );
Parameters or Arguments

subquery is a SELECT statement.

NOTE

Oracle SQL statements that use the Oracle EXISTS Condition are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query's table. There are more efficient ways to write most queries, that do not use the EXISTS Condition.




Question : 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.



Correct Answer : Get Lastest Questions and Answer :

Explanation: Subquery nesting can be done to many levels.

Related Questions

---

Question : What value is returned after executing the following statement?
select CAST( '22-Aug-2003' AS varchar2(30) ) from dual;

1. This would convert the date (ie: 22-Aug-2003) into a varchar2(30) value.
2. This would convert the date (ie: 22-Aug-2003) into a Number(30) value.
3. Access Mostly Uused Products by 50000+ Subscribers
4. Error




Question : What value is returned after executing the following statement?
CEIL(-32.65)

1. -33
2. -32
3. Access Mostly Uused Products by 50000+ Subscribers
4. -33.0




Question : Select the statement which is equivalent to below block
IF address1 is not null THEN
result := address1;

ELSIF address2 is not null THEN
result := address2;

ELSIF address3 is not null THEN
result := address3;

ELSE
result := null;

END IF;

1. SELECT COMPOSE( address1, address2, address3 ) result
FROM suppliers;
2. SELECT COALESCE( address1, address2, address3 ) result
FROM suppliers;
3. Access Mostly Uused Products by 50000+ Subscribers
FROM suppliers;
4. SELECT NVL( address1, address2, address3 ) result
FROM suppliers;




Question : What value is returned after executing the following statement?
LENGTH2('HadoopExam.com')

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




Question : What value is returned after executing the following statement?
SELECT supplier_id, supplier_name
FROM suppliers
WHERE supplier_id >= 500
UNION
SELECT company_id, company_name
FROM companies
WHERE company_name = 'Apple'
ORDER BY 2;

1. First Half data would be sorted based on supplier_name
2. Data would be sorted based on supplier_name but could be random because of different column names
3. Access Mostly Uused Products by 50000+ Subscribers
4. Error




Question : What value is returned after executing the following statement?
SELECT supplier_id, supplier_name
FROM suppliers
WHERE state = 'California'
UNION ALL
SELECT company_id, company_name
FROM companies
WHERE company_id > 1000
ORDER BY 2;

1. First Half data would be sorted based on supplier_name
2. Data would be sorted based on supplier_name but could be random because of different column names
3. Access Mostly Uused Products by 50000+ Subscribers
4. Error