Answer / 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','f')
insert into person(id,pname,Gender)values(3,'Anamika','f')
insert into person(id,pname,Gender)values(4,'raj','m')
insert into person(id,pname,Gender)values(5,'suhani','f')
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)

Answer / veeresh kethari

Where: 'WHERE' will filter the data before grouping

Having:'HAVING' will filter,after the grouping the data.

Answer / soorai ganesh

create table lovepair (boyfrndname varchar(10),girlfrndname
varchar(10))

insert into lovepair values ('BF1','GF1')
insert into lovepair values ('BF1','GF2')
insert into lovepair values ('BF1','GF3')
insert into lovepair values ('BF2','GF1')
insert into lovepair values ('BF2','GF2')
insert into lovepair values ('BF3','GF3')
insert into lovepair values ('BF3','GF3')
insert into lovepair values ('BF3','GF3')

-- Here BF1 have 3 Girl Friends. like the BF2 and BF3 have
2 and 3 Girl Friends Simultaneously.

-- Here is the query for, how many girls friends each boys
have ???? USING group by

SELECT boyfrndname, COUNT (*)
FROM lovepair
GROUP BY boyfrndname

// Here another qry for who have more than 2 girl
friends ?? USING GroupBy and Having .

// Having is used for applying some condition in Aggregate
function

SELECT boyfrndname,COUNT(*)
FROM lovepair
Group BY boyfrndname
having count(*)>2


--- Now u clear...........

Answer / dinesh sharma

Group by is used To Group Result According To Group Name
Having Is Always Is Used With Group by Clause.
Having Contain Aggregate Function or Simple Sub Query .
as Show In Above Example

Answer / 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)

Answer / pawan378

in addition to the above correct answers ..

Having Clause allows to add conditions using functions

Ex: COUNT(*) > 2

at on the same phrase of the query. this will be basically
to avoid sub queries.

Answer / manub22

- GROUP BY clause works on the rows returned by a SELECT Query. This clause summaries identical rows into a single/distinct group and returns a single row with the summary for each group, by using appropriate Aggregate function in the SELECT list, like COUNT(), SUM(), MIN(), MAX(), AVG(), etc.

- HAVING clause works as a Filter on top of the Grouped rows returned by the Query containing the GROUP BY clause. This clause cannot be replaced by a WHERE clause and vice-versa.

Check the difference b/w GROUP BY & HAVING, link: http://sqlwithmanoj.com/2015/05/23/sql-basics-difference-between-where-group-by-and-having-clause/

Explain isolation levels that sql server supports?

0 Answers  

Distinguish between commit and rollback?

0 Answers  

List the various tools available for performance tuning?

0 Answers  

What is the difference between migration and upgradation in sql server?

0 Answers  

Where sql server usernames and passwords are stored in a sql server?

0 Answers  

What is a not null constraint?

0 Answers  

How to create a dml trigger using create trigger statements?

0 Answers  

Is mysql the same as sql server?

0 Answers  

When does the auto update index statistics feature in sql server turn itself on?q) what specific conditions database should meet, before you can bulk copy data into it using bcp?

0 Answers  

How to drop an existing table with "drop table" statements in ms sql server?

0 Answers  

How many types of the database links?

0 Answers   MCN Solutions,

What is sql injection and why is it a problem? : sql server security

0 Answers