Re: Help

If you don't want to take a proper instructor led course .....

Suggest you purchase the Self Test Exams (link from Prometric site) which goes through explanations. Also suggest you look at Oracle docco, avaiulable at http://docs.oracle.com. And consider at least looking at the free & for-purchase online courses at http://education.oracle.com (Online Library)

You can get most of the answers from docs.oracle.com using the search capability on the site. Google is also reasonable.

/Hans

Mel wrote:
>
> I am in the process of studying for the Oracle certification and I am
> studying some practice exams. I would like to know if anyone can
> please help me with these questions or provide me with a link or books
> that can help me.
>
> Thanks much,
> Mel
>
> 1. A complete query nested inside another query is called a(n)
> ____________.
>
> a. inner view c. child view
> b. subquery d. all of the above
>
> 2. Which of the following subqueries returns more than one row of
> results to the outer query?
>
> a. multiple-column subquery c. multiple-row subquery
> b. single-row subquery d. correlated subquery
>
> 3. Which of the following is the only type of query that can be used
> in a WHERE clause?
>
> a. single-row subquery c. multiple-row subquery
> b. multiple-column subquery d. all of the above
>
> 4. Any type of subquery can be used in the _________ clause of a
> SELECT statement.
>
> a. WHERE c. FROM
> b. HAVING d. all of the above
>
> 5. An outer query is also referred to as a(n) ______________ query.
>
> a. parent query c. outline view
> b. outer view d. all of the above
>
> 6. The outer query receives its input from the ___________.
>
> a. inner view c. nested function
> b. outer view d. subquery
>
> 7. The results of a subquery are passed back as input to the _______
> query.
>
> a. inner c. correlated
> b. outer d. uncorrelated
>
> 8. Which of the following must be used to separate a subquery from
> the outer query?
>
> a. / / c. | |
> b. " " d. ( )
>
> 9. A subquery must include a(n) __________ clause.
>
> a. SELECT c. WHERE
> b. FROM d. both a and b
>
> 10. A subquery cannot contain a(n) _______ clause.
>
> a. GROUP BY c. ORDER BY
> b. HAVING d. WHERE
>
> 11. The operators =, <, >, <=, >=, and <> are referred to as_______
> operators.
>
> a. multiple-row c. correlated
> b. single-row d. multiple column
>
> 12. The = operator is referred to as a(n) ___________ operator.
>
> a. single-row c. correlated
> b. multiple-row d. uncorrelated
>
> 13. The <= operator is referred to as a(n) _____________ operator.
>
> a. multiple-row c. uncorrelated
> b. correlated d. single-row
>
> 14. The <> operator is referred to as a(n) ___________ operator
>
> a. single-row c. multiple-column
> b. multiple row d. correlated
>
> 15. The > operator is referred to as a(n) _________ operator.
>
> a. multiple-row c. single-row
> b. multiple-column d. none of the above
>
> 16. Which clause is used when the results of a subquery need to be
> restricted, based on some group condition?
>
> a. GROUP BY c. WHERE
> b. HAVING d. ORDER BY
>
> 17. The following SQL statement contains what type of subqueries?
> SELECT isbn, title FROM books WHERE pubid =
> (SELECT pubid FROM books WHERE title = 'SHORTEST POEMS')
> AND retail-cost >
> (SELECT AVG(retail-cost) FROM books);
>
> a. single-row c. multiple-column
> b. multiple-row d. inline view
>
> 18. If the result returned from a subquery must be compared to a
> group function, then the inner query must be nested in the outer
> query's _________ clause.
>
> a. GROUP BY c. HAVING
> b. WHERE d. FROM
>
> 19. The following SQL statement contains which type of subquery?
> SELECT title, retail, (SELECT AVG(retail) FROM books)
> AVERAGE
> FROM books;
>
> a. single-row c. multiple-column
> b. multiple-row d. inline view
>
> 20. Which of the following operators is used with a multiple-row
> subquery?
>
> a. IN c. ALL
> b. ANY d. all of the above
>
> 21. A(n) __________ subquery is one that can return several rows of
> results.
>
> a. correlated c. multiple-row
> b. single-row d. uncorrelated
>
> 22. The _____ operator indicates that the records processed by the
> outer query must match one of the values returned by the subquery.
>
> a. IN c. <ALL
> b. >ANY d. >ALL
>
> 23. Which operators can be combined with other comparison operators
> to treat the results of a subquery as a set of values, rather than as
> individual values?
>
> a. IN and ANY c. ALL and ANY
> b. IN and ALL d. EXISTS and IN
>
> 24. The following SQL statement contains which type of subquery?
> SELECT title, retail, category FROM books
> WHERE retail IN (SELECT MAX(retail) FROM books
> GROUP BY category);
>
> a. single-row c. multiple-column
> b. multiple-row d. correlated
>
> 25. Which operator will instruct Oracle9i to list all records with a
> value that is more than the highest value returned by the subquery?
>
> a. <ANY c. >ALL
> b. <ALL d. >ANY
>
> 26. The >ALL operator indicates that a value must be ______________
> value returned by the subquery.
>
> a. more than the highest c. more than the lowest
> b. less than the highest d. less than the lowest
>
> 27. The <ALL operator indicates that a value must be ____________
> value returned by the subquery.
>
> a. more than the highest c. more than the lowest
> b. less than the highest d. less than the lowest
>
> 28. Which operator will instruct Oracle9i to list all records with a
> value that is less than the highest value returned by the subquery?
>
> a. >ANY c. <ALL
> b. >ALL d. <ANY
>
> 29. Which of the following operators is the equivalent of the IN
> comparison operator?
>
> a. <ANY c. =ANY
> b. >ANY d. =ALL
>
> 30. The _______ operator is used to determine whether a condition is
> present in a subquery.
>
> a. IN c. ANY
> b. ALL d. EXISTS
>
> 31. The EXISTS operator must be listed _________________.
>
> a. on the left side of the comparison operator
> b. on the right side of the comparison operator
> c. in the SELECT clause
> d. none of the above
>
> 32. Which operator will return subquery results that are the
> equivalent of a Boolean false?
>
> a. IS NOT NULL c. DOES NOT EXIST
> b. NOT EXISTS d. <> EXISTS
>
> 33. A temporary table that is created when a multiple-column subquery
> is used in the FROM clause of an outer query is called a(n)________.
>
> a. inner view c. inline view
> b. outer view d. natural view
>
> 34. When a multiple-column subquery is included in the WHERE or
> HAVING clause of the outer query, which operator is used by the outer
> query to evaluate the results of the subquery?
>
> a. > c. IN
> b. BETWEEN d. none of the above
>
> 35. The following SQL statement contains what type of subquery?
> SELECT b.title, b.retail, a.category, a.cataverage
> FROM books b, (SELECT category, AVG(retail) cataverage
> FROM books GROUP BY category) a
> WHERE b.category = a.category;
>
> a. single-row subquery c. inline view
> b. multiple-row subquery d. none of the above
>
> 36. If it is possible for a subquery to return a NULL value to the
> outer query for comparison, the ________ function should be used to
> substitute an actual value for the NULL.
>
> a. IS NULL c. !NVL
> b. NVL d. NVLS
>
> 37. The following SQL statement contains which type of subquery?
> SELECT title, retail, category, cataverage FROM books NATURAL JOIN
> (SELECT category, AVG(retail) cataverage FROM books GROUP BY
> category);
>
> a. correlated c. multiple-row
> b. single-row d. multiple-column
>
> 38. Which comparison operator allows you to search for NULL values in
> a subquery?
>
> a. IS NULL c. =NULL
> b. NVL d. NVLS
>
> 39. The following SQL statement contains which type of subquery?
> SELECT title, retail, category, cataverage FROM books NATURAL JOIN
> (SELECT category, AVG(retail) cataverage FROM books GROUP BY
> category);
>
> a. single-row c. correlated
> b. multiple-row d. uncorrelated
>
> 40. Which of the following terms refers to a type of subquery that is
> processed, or executed, once for each row in the outer query?
>
> a. correlated subquery c. multiple-column subquery
> b. uncorrelated subquery d. single-row subquery
>
> 41. The following SQL statement contains which type of subquery?
> SELECT title FROM books WHERE EXISTS
> (SELECT isbn FROM orderitems WHERE books.isbn = orderitems.isbn);
>
> a. multiple-column c. correlated
> b. inline view d. uncorrelated
>
> 42. In Oracle9i, subqueries in a WHERE clause can be nested to a
> maximum depth of ______ subqueries.
>
> a. 25 c. 2
> b. 255 d. there is no limit
>
> Contents of the BOOKS table
>
> 43. Based upon the contents of the BOOKS table, which line of the
> following SQL statement contains an error?
> 1 SELECT title, pubid, cost, retail
> 2 FROM books
> 3 WHERE (pubid, cost)
> 4 (SELECT pubid, cost)
> 5 FROM books
> 6 WHERE pubid = 3);
>
> a. Line 1 c. Line 5
> b. Line 4 d. Line 6
>
> 44. Based on the contents of the BOOKS table, which line in the
> following SQL statement contains an error?
> 1 SELECT title
> 2 FROM books
> 3 WHERE pubid EXISTS IN
> 4 (SELECT pubid
> 5 FROM books
> 6 WHERE retail > 41.95);
>
> a. Line 1 c. Line 4
> b. Line 3 d. Line 6
>
> 45. Based on the contents of the BOOKS table, which line of the
> following SQL statement contains an error?
> 1 SELECT isbn, title
> 2 FROM books
> 3 WHERE pubid =
> 4 (SELECT pubid
> 5 FROM books
> 6 WHERE title = 'SHORTEST POEMS')
> 7 AND retail-cost >
> 8 (SELECT AVG(retail-cost)
> 9 FROM books);
>
> a. Line 3 c. Line 7
> b. Line 5 d. none of the above
>
> 46. Based on the contents of the BOOKS table, which of the following
> SQL statements will display the title of all books published by the
> publisher of SHORTEST POEMS?
>
> a. SELECT title FROM books WHERE pubid >ANY
> (SELECT pubid FROM books WHERE title = 'SHORTEST POEMS');
> b. SELECT title FROM books WHERE pubid =ANY
> SELECT pubid FROM books WHERE title = 'SHORTEST POEMS';
> c. SELECT title FROM books WHERE pubid IN
> (SELECT pubid, title FROM books WHERE title = 'SHORTEST POEMS');
> d. none of the above
>
> 47. Based on the contents of the BOOKS table, which of the following
> SQL statements will return an error message?
>
> a. SELECT title FROM books WHERE retail =
> (SELECT MAX(retail) FROM books);
> b. SELECT title FROM books WHERE retail IN
> (SELECT MAX(retail) FROM books);
> c. SELECT title FROM books WHERE retail >
> (SELECT MAX(retail) FROM books);
> d. none of the above
>
> Contents of the CUSTOMERS table
>
> 48. Based upon the contents of the CUSTOMERS table, which of the
> following would be the most appropriate use of a subquery?
>
> a. When searching for all customers who live in the same state as
> customer# 1007.
> b. When searching for all customers who were referred by customer#
> 1003.
> c. When searching for all customers with the last name of Thompson.
> d. When search for all customers assigned customer# 1010.
>
> 49. Based on the contents of the CUSTOMERS table, which SQL statement
> will display the customers residing in the same state as
> customer#1013?
>
> a. SELECT customer# FROM customers WHERE customer# =
> (SELECT state FROM customers WHERE state = 'NJ');
> b. SELECT customer# FROM customers WHERE state =
> (SELECT state FROM customers WHERE customer#=1013);
> c. SELECT customer# FROM customers WHERE customer# IN
> (SELECT state FROM customers WHERE state = 'NJ');
> d. SELECT customer# FROM customers WHERE state EXISTS
> (SELECT state FROM customers WHERE customer#=1013);
>
> 50. Based on the contents of the CUSTOMERS table, which of the
> following SQL statements will display the customer# of all customers
> who were referred by the same individual that referred customer# 1003?
>
> a. SELECT customer# FROM customers WHERE referred =
> (SELECT referred FROM customers WHERE customer# = 1003);
> b. SELECT customer# FROM customers WHERE referred EXISTS
> (SELECT referred FROM customers WHERE customer# = 1003);
> c. SELECT customer# FROM customers WHERE NVL(referred, 0) =
> (SELECT NVL(referred,0) FROM customers WHERE customer# = 1003);
> d. SELECT customer# FROM customers WHERE NVL(referred,0) =
> (SELECT referred FROM customers WHERE customer# = 1003);

-- 
/Hans
[mailto:`echo $from" | sed "s/yahoo/telusplanet/g"`]