Help

From: Mel <melvinbrooks2_at_sbcglobal.net>
Date: 26 Oct 2003 20:03:59 -0800
Message-ID: <33ca3639.0310262003.7513aa3d_at_posting.google.com>

[Quoted] 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) ____________. [Quoted]
2. inner view c. child view
3. subquery d. all of the above
4. Which of the following subqueries returns more than one row of [Quoted] results to the outer query?
5. multiple-column subquery c. multiple-row subquery
6. single-row subquery d. correlated subquery
7. Which of the following is the only type of query that can be used in a WHERE clause? [Quoted]
8. single-row subquery c. multiple-row subquery
9. multiple-column subquery d. all of the above
10. Any type of subquery can be used in the _________ clause of a [Quoted] SELECT statement.
11. WHERE c. FROM
12. HAVING d. all of the above
13. An outer query is also referred to as a(n) ______________ query. [Quoted]
14. parent query c. outline view
15. outer view d. all of the above
16. The outer query receives its input from the ___________.
17. inner view c. nested function
18. outer view d. subquery
19. The results of a subquery are passed back as input to the _______ query. [Quoted]
20. inner c. correlated
21. outer d. uncorrelated
22. Which of the following must be used to separate a subquery from the outer query?
23. / / c. | |
24. " " d. ( )
25. A subquery must include a(n) __________ clause.
26. SELECT c. WHERE
27. FROM d. both a and b
28. A subquery cannot contain a(n) _______ clause.
29. GROUP BY c. ORDER BY
30. HAVING d. WHERE
31. The operators =, <, >, <=, >=, and <> are referred to as_______ operators. [Quoted]
32. multiple-row c. correlated
33. single-row d. multiple column
34. The = operator is referred to as a(n) ___________ operator.
35. single-row c. correlated
36. multiple-row d. uncorrelated
37. The <= operator is referred to as a(n) _____________ operator. [Quoted]
38. multiple-row c. uncorrelated
39. correlated d. single-row
40. The <> operator is referred to as a(n) ___________ operator
41. single-row c. multiple-column
42. multiple row d. correlated
43. The > operator is referred to as a(n) _________ operator. [Quoted]
44. multiple-row c. single-row
45. multiple-column d. none of the above
46. Which clause is used when the results of a subquery need to be [Quoted] restricted, based on some group condition?
47. GROUP BY c. WHERE
48. HAVING d. ORDER BY
49. The following SQL statement contains what type of subqueries? [Quoted] SELECT isbn, title FROM books WHERE pubid =
    (SELECT pubid FROM books WHERE title = 'SHORTEST POEMS')
    AND retail-cost >
    (SELECT AVG(retail-cost) FROM books);
    
50. single-row c. multiple-column
51. multiple-row d. inline view
52. If the result returned from a subquery must be compared to a [Quoted] group function, then the inner query must be nested in the outer query's _________ clause.
53. GROUP BY c. HAVING
54. WHERE d. FROM
55. The following SQL statement contains which type of subquery? [Quoted] SELECT title, retail, (SELECT AVG(retail) FROM books) AVERAGE FROM books;
56. single-row c. multiple-column
57. multiple-row d. inline view
58. Which of the following operators is used with a multiple-row subquery?
59. IN c. ALL
60. ANY d. all of the above
61. A(n) __________ subquery is one that can return several rows of results.
62. correlated c. multiple-row
63. single-row d. uncorrelated
64. The _____ operator indicates that the records processed by the [Quoted] outer query must match one of the values returned by the subquery.
65. IN c. <ALL
66. >ANY d. >ALL
67. Which operators can be combined with other comparison operators [Quoted] to treat the results of a subquery as a set of values, rather than as individual values?
68. IN and ANY c. ALL and ANY
69. IN and ALL d. EXISTS and IN
70. The following SQL statement contains which type of subquery? [Quoted] SELECT title, retail, category FROM books WHERE retail IN (SELECT MAX(retail) FROM books GROUP BY category);
71. single-row c. multiple-column
72. multiple-row d. correlated
73. Which operator will instruct Oracle9i to list all records with a [Quoted] value that is more than the highest value returned by the subquery?
74. <ANY c. >ALL
75. <ALL d. >ANY
76. The >ALL operator indicates that a value must be ______________ value returned by the subquery.
77. more than the highest c. more than the lowest
78. less than the highest d. less than the lowest
79. The <ALL operator indicates that a value must be ____________ value returned by the subquery.
80. more than the highest c. more than the lowest
81. less than the highest d. less than the lowest
82. Which operator will instruct Oracle9i to list all records with a [Quoted] value that is less than the highest value returned by the subquery?
83. >ANY c. <ALL
84. >ALL d. <ANY
85. Which of the following operators is the equivalent of the IN comparison operator?
86. <ANY c. =ANY
87. >ANY d. =ALL
88. The _______ operator is used to determine whether a condition is [Quoted] present in a subquery.
89. IN c. ANY
90. ALL d. EXISTS
91. The EXISTS operator must be listed _________________.
92. on the left side of the comparison operator
93. on the right side of the comparison operator
94. in the SELECT clause
95. none of the above
96. Which operator will return subquery results that are the equivalent of a Boolean false?
97. IS NOT NULL c. DOES NOT EXIST
98. NOT EXISTS d. <> EXISTS
99. A temporary table that is created when a multiple-column subquery [Quoted] is used in the FROM clause of an outer query is called a(n)________.
100. inner view c. inline view
101. outer view d. natural view
102. When a multiple-column subquery is included in the WHERE or [Quoted] HAVING clause of the outer query, which operator is used by the outer query to evaluate the results of the subquery?
103. > c. IN
104. BETWEEN d. none of the above
105. The following SQL statement contains what type of subquery? [Quoted] SELECT b.title, b.retail, a.category, a.cataverage [Quoted] [Quoted] FROM books b, (SELECT category, AVG(retail) cataverage FROM books GROUP BY category) a WHERE b.category = a.category;
106. single-row subquery c. inline view
107. multiple-row subquery d. none of the above
108. If it is possible for a subquery to return a NULL value to the [Quoted] outer query for comparison, the ________ function should be used to substitute an actual value for the NULL.
109. IS NULL c. !NVL
110. NVL d. NVLS
111. 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);
112. correlated c. multiple-row
113. single-row d. multiple-column
114. Which comparison operator allows you to search for NULL values in a subquery? [Quoted]
115. IS NULL c. =NULL
116. NVL d. NVLS
117. 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);
118. single-row c. correlated
119. multiple-row d. uncorrelated
120. Which of the following terms refers to a type of subquery that is [Quoted] processed, or executed, once for each row in the outer query?
121. correlated subquery c. multiple-column subquery
122. uncorrelated subquery d. single-row subquery
123. The following SQL statement contains which type of subquery? SELECT title FROM books WHERE EXISTS
     [Quoted] (SELECT isbn FROM orderitems WHERE books.isbn = orderitems.isbn);
     
124. multiple-column c. correlated
125. inline view d. uncorrelated
126. In Oracle9i, subqueries in a WHERE clause can be nested to a [Quoted] maximum depth of ______ subqueries.
127. 25 c. 2
128. 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
[Quoted] 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
[Quoted] 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 =

[Quoted] 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

[Quoted] SQL statements will display the title of all books published by the publisher of SHORTEST POEMS?  

1. SELECT title FROM books WHERE pubid >ANY
   (SELECT pubid FROM books WHERE title = 'SHORTEST POEMS');
   
2. SELECT title FROM books WHERE pubid =ANY SELECT pubid FROM books WHERE title = 'SHORTEST POEMS';
3. SELECT title FROM books WHERE pubid IN
   (SELECT pubid, title FROM books WHERE title = 'SHORTEST POEMS');
   
4. none of the above
5. Based on the contents of the BOOKS table, which of the following [Quoted] SQL statements will return an error message?
6. SELECT title FROM books WHERE retail =
   (SELECT MAX(retail) FROM books);
   
7. SELECT title FROM books WHERE retail IN
   (SELECT MAX(retail) FROM books);
   
8. SELECT title FROM books WHERE retail >
   (SELECT MAX(retail) FROM books);
   
9. none of the above

Contents of the CUSTOMERS table

        48. Based upon the contents of the CUSTOMERS table, which of the [Quoted] following would be the most appropriate use of a subquery?  

1. When searching for all customers who live in the same state as customer# 1007.
2. When searching for all customers who were referred by customer# 1003.
3. When searching for all customers with the last name of Thompson.
4. When search for all customers assigned customer# 1010.
5. Based on the contents of the CUSTOMERS table, which SQL statement [Quoted] will display the customers residing in the same state as customer#1013?
6. SELECT customer# FROM customers WHERE customer# =
   (SELECT state FROM customers WHERE state = 'NJ');
   
7. SELECT customer# FROM customers WHERE state =
   (SELECT state FROM customers WHERE customer#=1013);
   
8. SELECT customer# FROM customers WHERE customer# IN
   (SELECT state FROM customers WHERE state = 'NJ');
   
9. SELECT customer# FROM customers WHERE state EXISTS
   (SELECT state FROM customers WHERE customer#=1013);
   
10. Based on the contents of the CUSTOMERS table, which of the [Quoted] following SQL statements will display the customer# of all customers who were referred by the same individual that referred customer# 1003?
11. SELECT customer# FROM customers WHERE referred =
    (SELECT referred FROM customers WHERE customer# = 1003);
    
12. SELECT customer# FROM customers WHERE referred EXISTS
    (SELECT referred FROM customers WHERE customer# = 1003);
    
13. SELECT customer# FROM customers WHERE NVL(referred, 0) =
    (SELECT NVL(referred,0) FROM customers WHERE customer# = 1003);
    
14. SELECT customer# FROM customers WHERE NVL(referred,0) =
    (SELECT referred FROM customers WHERE customer# = 1003);
    

Received on Mon Oct 27 2003 - 05:03:59 CET