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


Please Help Members By Posting Answers For Below Questions

how to create a scrollable cursor with the scroll option? : Sql server database administration

579


Describe in brief system database.

521


How real and float literal values are rounded?

563


Tell me what is de-normalization and what are some of the examples of it?

551


what is the maximum size of a row? : Sql server database administration

515






what is a schema in sql server 2005? Explain how to create a new schema in a database? : Sql server database administration

506


Where the sql logs gets stored?

520


Tell me what is the stuff and how does it differ from the replace function?

515


how you can list all the tables in a database?

503


Can a trigger be created on a view?

547


what is the sql equivaent of the dataset relation object ?

1537


What do you mean by acid?

549


what is sql server? : Sql server database administration

517


List few advantages of stored procedure.

517


How many types of built in functions are there in sql server 2012?

470