4)SubQuery-1-1

1) Which query will list the customer living in the same state as the customer named leila smith. Note:There are several people with the name smith. a) select customer from customers where state=(select state from customers where lastname =’smith’); b) select customer from customers where state=(select state from customers where lastname =’smith’ and firstname=’leila’) c) select customer from customers where state=(select state from customers where lastname =’smith’ or firstname=’leila’ ) d) none of the above 2) Which of the following is valid considering that there are several records in the orders table with order number 1010. a) select order from orders where shipdate=select shipdate from orders where order=1010 b) select order from orders where shipdate=(select shipdate from orders) and order=1010 c) select order from orders where shipdate=(select shipdate from orders where order=1010) d) select order from orders having shipdate=(select shipdate from where order=1010) e) none of the above 3) Which of the following is considered a multiple row operator a) IN b) = c) <> d) All the above e) none of the above 4) What is the purpose of the following query select isbn, title from books where (pubid, category) in (select pubid, category from books where title like ‘%oracle%’); a) it lists all the publishers and categories containing the value oracle b) it lists the isbn and title of all books belonging to the (same category and having the same publisher) as any book with the letters oracle in its title c) All the above d) None of the above 5) Given the following query considering that there is one order# with isbn ’99-99-99’ in the orderitems table and that order# appears once in in the orders table. select order from orders where order in (select order# from orderitems where isbn=’99-99-99’) a) the statement will not execute since the subquery and the outer query do not reference the same table b) the outer query is not necessary since it has no effect on the results displayed c) the query will fail if only one result is returned to the outer query since the where clause of the outer query uses the In comparison operator d) No rows will be displayed since the isbn in the where clause is enclosed in single quotation marks e) None of the above 6) A complete query nested inside another query is called a(n) ____. a. inner view c. child view b. Subquery d. all of the above f) g) 7) Which of the following must be used to separate a subquery from the outer query? a. / / c. | | b. " " d. ( ) 8) The <> operator is referred to as a(n) ____ operator. a. single-row c. multiple-column b. multiple row d. Correlated 9) 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