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
- A complete query nested inside another query is called a(n)
____________.
- inner view c. child view
- subquery d. all of the above
- Which of the following subqueries returns more than one row of
results to the outer query?
- multiple-column subquery c. multiple-row subquery
- single-row subquery d. correlated subquery
- Which of the following is the only type of query that can be used
in a WHERE clause?
- single-row subquery c. multiple-row subquery
- multiple-column subquery d. all of the above
- Any type of subquery can be used in the _________ clause of a
SELECT statement.
- WHERE c. FROM
- HAVING d. all of the above
- An outer query is also referred to as a(n) ______________ query.
- parent query c. outline view
- outer view d. all of the above
- The outer query receives its input from the ___________.
- inner view c. nested function
- outer view d. subquery
- The results of a subquery are passed back as input to the _______
query.
- inner c. correlated
- outer d. uncorrelated
- Which of the following must be used to separate a subquery from
the outer query?
- / / c. | |
- " " d. ( )
- A subquery must include a(n) __________ clause.
- SELECT c. WHERE
- FROM d. both a and b
- A subquery cannot contain a(n) _______ clause.
- GROUP BY c. ORDER BY
- HAVING d. WHERE
- The operators =, <, >, <=, >=, and <> are referred to as_______
operators.
- multiple-row c. correlated
- single-row d. multiple column
- The = operator is referred to as a(n) ___________ operator.
- single-row c. correlated
- multiple-row d. uncorrelated
- The <= operator is referred to as a(n) _____________ operator.
- multiple-row c. uncorrelated
- correlated d. single-row
- The <> operator is referred to as a(n) ___________ operator
- single-row c. multiple-column
- multiple row d. correlated
- The > operator is referred to as a(n) _________ operator.
- multiple-row c. single-row
- multiple-column d. none of the above
- Which clause is used when the results of a subquery need to be
restricted, based on some group condition?
- GROUP BY c. WHERE
- HAVING d. ORDER BY
- 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);
- single-row c. multiple-column
- multiple-row d. inline view
- 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.
- GROUP BY c. HAVING
- WHERE d. FROM
- The following SQL statement contains which type of subquery?
SELECT title, retail, (SELECT AVG(retail) FROM books)
AVERAGE
FROM books;
- single-row c. multiple-column
- multiple-row d. inline view
- Which of the following operators is used with a multiple-row
subquery?
- IN c. ALL
- ANY d. all of the above
- A(n) __________ subquery is one that can return several rows of
results.
- correlated c. multiple-row
- single-row d. uncorrelated
- The _____ operator indicates that the records processed by the
outer query must match one of the values returned by the subquery.
- IN c. <ALL
- >ANY d. >ALL
- 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?
- IN and ANY c. ALL and ANY
- IN and ALL d. EXISTS and IN
- 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);
- single-row c. multiple-column
- multiple-row d. correlated
- Which operator will instruct Oracle9i to list all records with a
value that is more than the highest value returned by the subquery?
- <ANY c. >ALL
- <ALL d. >ANY
- The >ALL operator indicates that a value must be ______________
value returned by the subquery.
- more than the highest c. more than the lowest
- less than the highest d. less than the lowest
- The <ALL operator indicates that a value must be ____________
value returned by the subquery.
- more than the highest c. more than the lowest
- less than the highest d. less than the lowest
- Which operator will instruct Oracle9i to list all records with a
value that is less than the highest value returned by the subquery?
- >ANY c. <ALL
- >ALL d. <ANY
- Which of the following operators is the equivalent of the IN
comparison operator?
- <ANY c. =ANY
- >ANY d. =ALL
- The _______ operator is used to determine whether a condition is
present in a subquery.
- IN c. ANY
- ALL d. EXISTS
- The EXISTS operator must be listed _________________.
- on the left side of the comparison operator
- on the right side of the comparison operator
- in the SELECT clause
- none of the above
- Which operator will return subquery results that are the
equivalent of a Boolean false?
- IS NOT NULL c. DOES NOT EXIST
- NOT EXISTS d. <> EXISTS
- 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)________.
- inner view c. inline view
- outer view d. natural view
- 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?
- > c. IN
- BETWEEN d. none of the above
- 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;
- single-row subquery c. inline view
- multiple-row subquery d. none of the above
- 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.
- IS NULL c. !NVL
- NVL d. NVLS
- 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);
- correlated c. multiple-row
- single-row d. multiple-column
- Which comparison operator allows you to search for NULL values in
a subquery?
- IS NULL c. =NULL
- NVL d. NVLS
- 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);
- single-row c. correlated
- multiple-row d. uncorrelated
- Which of the following terms refers to a type of subquery that is
processed, or executed, once for each row in the outer query?
- correlated subquery c. multiple-column subquery
- uncorrelated subquery d. single-row subquery
- The following SQL statement contains which type of subquery?
SELECT title FROM books WHERE EXISTS
(SELECT isbn FROM orderitems WHERE books.isbn = orderitems.isbn);
- multiple-column c. correlated
- inline view d. uncorrelated
- In Oracle9i, subqueries in a WHERE clause can be nested to a
maximum depth of ______ subqueries.
- 25 c. 2
- 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?
- SELECT title FROM books WHERE pubid >ANY
(SELECT pubid FROM books WHERE title = 'SHORTEST POEMS');
- SELECT title FROM books WHERE pubid =ANY
SELECT pubid FROM books WHERE title = 'SHORTEST POEMS';
- SELECT title FROM books WHERE pubid IN
(SELECT pubid, title FROM books WHERE title = 'SHORTEST POEMS');
- none of the above
- Based on the contents of the BOOKS table, which of the following
SQL statements will return an error message?
- SELECT title FROM books WHERE retail =
(SELECT MAX(retail) FROM books);
- SELECT title FROM books WHERE retail IN
(SELECT MAX(retail) FROM books);
- SELECT title FROM books WHERE retail >
(SELECT MAX(retail) FROM books);
- 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?
- When searching for all customers who live in the same state as
customer# 1007.
- When searching for all customers who were referred by customer#
1003.
- When searching for all customers with the last name of Thompson.
- When search for all customers assigned customer# 1010.
- Based on the contents of the CUSTOMERS table, which SQL statement
will display the customers residing in the same state as
customer#1013?
- SELECT customer# FROM customers WHERE customer# =
(SELECT state FROM customers WHERE state = 'NJ');
- SELECT customer# FROM customers WHERE state =
(SELECT state FROM customers WHERE customer#=1013);
- SELECT customer# FROM customers WHERE customer# IN
(SELECT state FROM customers WHERE state = 'NJ');
- SELECT customer# FROM customers WHERE state EXISTS
(SELECT state FROM customers WHERE customer#=1013);
- 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?
- SELECT customer# FROM customers WHERE referred =
(SELECT referred FROM customers WHERE customer# = 1003);
- SELECT customer# FROM customers WHERE referred EXISTS
(SELECT referred FROM customers WHERE customer# = 1003);
- SELECT customer# FROM customers WHERE NVL(referred, 0) =
(SELECT NVL(referred,0) FROM customers WHERE customer# = 1003);
- SELECT customer# FROM customers WHERE NVL(referred,0) =
(SELECT referred FROM customers WHERE customer# = 1003);
Received on Sun Oct 26 2003 - 22:03:59 CST