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)

How to run queries with sql server management studio express?

1098

---

wat wil hapn if we give the both read and deny read permission to user?

2104

---

What is unpivot?

1017

---

do views contain data ?

976

---

Explain ms sql server reporting services vs crystal reports?

928

---

What are dml triggers and types of dml triggers?

983

---

List the different types of joins?

1015

---

Mention the differences between substr and charindex in sql server.

1058

---

How to create a testing table with test data in ms sql server?

1089

---

What are various limitations of the views?

1058

---

What is similarity and difference between truncate and delete in sql?

1104

---

Explain the functionalities that views support?

1121

---

Explain the difference between control flow and data flow?

1008

---

How to create prepared statements using odbc_prepare()?

1070

---

How do I find the port number for sql server?

973

---