Home » SQL & PL/SQL » SQL & PL/SQL » help me
help me [message #192411] Tue, 12 September 2006 04:16 Go to next message
quytc
Messages: 81
Registered: November 2005
Location: Viet Nam
Member

Hi all!!!!!
I have a question no.74 in 1z0-007:
QUESTION NO: 74
Which three statements about subqueries are true? (Choose three)
A. A single row subquery can retrieve only one column and one row.
B. A single row subquery can retrieve only one row but many columns.
C. A multiple row subquery can retrieve multiple rows and multiple columns.
D. A multiple row subquery can be compared by using the “>” operator.E. A single row subquery can use the IN operator.
F. A multiple row subquery can use the “=” operator.
Answer: B, C, D
blue]Can anybody tell me why answer D is correct.

[Updated on: Tue, 12 September 2006 04:17]

Report message to a moderator

Re: help me [message #192424 is a reply to message #192411] Tue, 12 September 2006 05:25 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
It's not:

SQL> select empno from EMP
  2  where sal >(
  3  select sal from EMP where job = 'CLERK');
select sal from EMP where job = 'CLERK')
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row


Unless you add either ANY or ALL to the > operator
[code]
SQL>
1 select empno from EMP
2 where sal > ANY(
3* select sal from EMP where job = 'CLERK')
SQL> /

EMPNO
----------
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900

EMPNO
----------
7902
7934

13 rows selected.

code]

icon6.gif  Re: help me [message #319248 is a reply to message #192424] Fri, 09 May 2008 08:28 Go to previous messageGo to next message
priyamalhotra
Messages: 43
Registered: July 2006
Location: none
Member

Hi,

So can we say that only B & C are correct.

Also Can the following statement be true:

F. A multiple row subquery can use the “=” operator.

select empno from EMP
where sal > ANY (
select sal from EMP where job = 'CLERK'); -- Equality Operator

As it justifies with the following statement as false:

E. A single row subquery can use the IN operator.

select empno from EMP
where sal = (
select sal from EMP where empid IN ('100', '101'); -- IN Operator

Which will result in an error.

Thanks,
Priya.

Re: help me [message #319253 is a reply to message #319248] Fri, 09 May 2008 08:43 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
E depends how you read it.
You can use IN to check on a single row subquery:
select empno from EMP
where sal IN (
select sal from EMP where empid = '100';
, and you can use IN in a single row sub-query, as long as it only returns a single row:
select empno from EMP
where sal = (
select sal from EMP where empid IN ('100', '101') and rownum = 1);


Previous Topic: ref cursor and count location wise (merged 4)
Next Topic: why to use purge
Goto Forum:
  


Current Time: Tue Dec 06 04:29:27 CST 2016

Total time taken to generate the page: 0.16818 seconds