what is the difference between group and having
give an example with query and sample output
Answer Posted / krishna murari chaubey
You Have two table person and friend
create table person(id int,pname varchar(20),gender varchar(3))
insert into person(id,pname,Gender)values(1,'krishna','m')
insert into person(id,pname,Gender)values(2,'Radha','m')
insert into person(id,pname,Gender)values(3,'Anamika','m')
insert into person(id,pname,Gender)values(4,'raj','m')
insert into person(id,pname,Gender)values(5,'suhani','m')
insert into person(id,pname,Gender)values(6,'ravi','m')
create table friend(id int,fid int)
insert into friend(id,fid)values(1,2)
insert into friend(id,fid)values(1,3)
insert into friend(id,fid)values(1,5)
insert into friend(id,fid)values(2,3)
insert into friend(id,fid)values(1,4)
insert into friend(id,fid)values(1,6)
insert into friend(id,fid)values(6,2)
insert into friend(id,fid)values(6,3)
insert into friend(id,fid)values(3,2)
insert into friend(id,fid)values(3,2)
insert into friend(id,fid)values(3,1)
find person who is male and having more than two female friend
Asnswer : -
select id,count(fid) as numberOfFemaleFriend from friend where fid in(select id from person where gender='f')
and id in (select id from person where gender='m' )
group by id having count(fid) >2
OR You can use Inner Join
select f.id,p.pname,count(f.fid) as numberOfFemaleFriend
from person p
inner join friend f
on p.id=f.id and p.gender='m' and f.fid in
(select id from person where gender='f')
group by f.id,p.pname having(count(f.fid)>2)
Is This Answer Correct ? | 2 Yes | 0 No |
Post New Answer View All Answers
how to create a scrollable cursor with the scroll option? : Sql server database administration
Describe in brief system database.
How real and float literal values are rounded?
Tell me what is de-normalization and what are some of the examples of it?
what is the maximum size of a row? : Sql server database administration
what is a schema in sql server 2005? Explain how to create a new schema in a database? : Sql server database administration
Where the sql logs gets stored?
Tell me what is the stuff and how does it differ from the replace function?
how you can list all the tables in a database?
Can a trigger be created on a view?
what is the sql equivaent of the dataset relation object ?
What do you mean by acid?
what is sql server? : Sql server database administration
List few advantages of stored procedure.
How many types of built in functions are there in sql server 2012?