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)

What is triggers in ms sql server?

1181

---

What is acid db?

933

---

What is key attribute?

981

---

What samples and sample databases are provided by microsoft?

1089

---

How to stop a loop early with break statements in ms sql server?

1075

---

Do you know what are acid properties?

1122

---

What is the difference between count and distinct count?

931

---

What stored by the master?

1010

---

What does set rowcount do?

961

---

How to download and install microsoft sql server management studio express?

1093

---

How to transfer data from a cursor to variables with a "fetch" statement?

1145

---

What is the cartesian product of the table?

1004

---

what happens on checkpoint? : Sql server database administration

1157

---

How to return the second 5 rows in ms sql server?

1108

---

How to truncate the log in sql server 2012? : sql server database administration

1346

---