Answer / bunty

IN - used to select multiple rows based on any of the key
provided

SQL - select distinct employeeid from orders where orderid
in ( select orderid from orderdetails where discount >= 10)


ANY - used in case of relational queries to compare result
with any of the key.

SQL - select custID from orders where regionID != "E" and
discount > any (select discount from orders where regionID
= "E" and discount > 3)

ALL - used in case of relational queries to compare result
with all of the keys.

SQL - select custID from orders where regionID != "E" and
discount > all (select discount from orders where regionID
= "E" and discount > 3)

Cheers -
Bunty

Answer / s. syam sundar

IN,ANY,ALL ARE MULTIPLE OPERATORS
THESE ARE USED IN MULTIPLE ROW SUBQUERIES

Answer / satyajit patel

-> in------------ Looks to all values returned in sub-query

-> <any---------- looks to highest value in sub-query
-> >any---------- looks to smallest value in sub-query

-> <all---------- looks to smallest value in sub-query
-> >all---------- looks to highest value in sub-query

Answer / manoj

IN- It will return the value based on the parameter;
e.g. select * from emp where salary in ('1000','3000');

ANY-It will compare with any value that has been returned by
the parameter;

select * from emp where salary > any(select salary from emp
where deptno=10)
the salary will be compared with any value that has been
returned by the subquery.

ALL-It will compare with max/min value that has been
returned by the subquery;

select * from emp where salary > all(select salary from emp
where deptno=10)
the salary will be compared with the longest value that has
been returned by the subquery.

Answer / mohamed shahid(broadline)

IN:-EQUAL TO ANY MEMBER
EX:-
---
SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL IN(SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);

ANY(<):-COMPARES VALUE TO EACH VALUE RETURNED BY THE SUBQUERY
EX:-
SELECT EMPNO,ENAME,JOB FROM EMP WHERE SAL<ANY(SELECT SAL FROM EMP WHERE JOB='CLAERK');

NOTE:-IT MEANS LESS THAN MINIMUNM VALUE.
ANY(>):-
------

SELECT EMPNO,ENAME,JOB FROM EMP WHERE SAL>ANY(SELECT SAL FROM EMP WHERE JOB='CLERK');

NOTE:-
-----
>ANY:-IT MEANS MORE THAN MINIMUM
=ANY:- IT IS EQUIVALENT TO IN OPERATOR

ALL:-COMPARES VALUE TO EVERY VALUE RETURNED BY THE SUBQUERY

SELECT EMPNO,ENAME,JOB FROM EMP WHERE SAL>ALL(SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO);
NOTE:-
---
>ALL IT MEANS MORE TAHN MAXIMUM


SELECT EMPNO,ENAME,JOB FROM EMP WHERE SAL<ALL(SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO);
NOTE:-
---
ALL<:-IT MEANS LESS THAN MINIMUM

What is difference between mysql and postgresql?

0 Answers  

What is user defined functions?

0 Answers  

Mention what does the hierarchical profiler does?

0 Answers  

Define concurrency control. : Transact sql

0 Answers  

What is integrity in sql?

0 Answers  

what are integrity rules?

0 Answers  

Hi, I am new in oracle(SQL), could anyone help me in writing a correct SQL. Below is the table structure. Table: Subsc Fields: 1. Sub_no (this field will hold values of subscriber nos, for e.g. S111111, S222222, S333333, S444444, etc.) 2. s_status (this field will hold values for different status of subscriber, for e.g. 'A', 'S', 'C', etc.) 3. cus_id (this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.) Table: Bill Fields: 1. Bill_no this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.) 2. b_status = (this field will hold values for different status of bill for e.g. 'O', 'C', 'S', etc.) Note: 1. The Sub_no is a Primary key of Subsc table. 2. The cus_id is a foreign in Subsc table (referred from Bill_no field of Bill table) 3. The Bill_no field is the Primary key of Bill table. Query A --> I wrote a query to select cus_id/Bill_no which is in status open (b_status = 'O') and having more than two active subscriber (i.e. S_status = 'A') in it ( i.e. more the two subscribers in same bill). select s.cus_id from subsc s where exists (select 1 from bill where bill_no = s.cus_id and b_status = 'O') and s_status = 'A' group by s.cus_id having count(sub_no) = 2 Problem : The above query will give the cus_id (or rather bill_no) which are in open status (b_status ='O) and which are having TWO ACTIVE Subscribers (s_status ='A') in it. However, this query will also lists the cus_id/bill_no which are having more than TWO subscribers in it (but only two subscriber will be in Active status (s_status = 'A') and the others will be in s_status = 'C' or s_status = 'S'. Help needed: I want to write a query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it. B--> If I include the sub_no in the above query then NO row are returned. select s.cus_id, s.sub_no from subsc s where exists (select 1 from bill where bill_no = s.cus_id and b_status = 'O') and s_status = 'A' group by s.cus_id, s.sub_no having count(sub_no) = 2 Help needed: I want to modify the above query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it ALONG with the sub_no. Thanks a lot in advance. Regards, Nitin

0 Answers  

What is primary key sql?

0 Answers  

Does it possible to pass object (or) table as an argument to a remote procedure?

1 Answers   TCS,

Is a foreign key always unique?

0 Answers  

What is a trigger in pl/sql?

1 Answers  

How does cross join work?

0 Answers