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','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)

Write a program using SQL queries to find a unique entry in a table.

977

---

Explain microsoft sql server functions?

1089

---

Where are SQL server users names and passwords are stored in sql server?

1241

---

How do I find the default sql server instance?

928

---

Explain about sql server login?

1067

---

What is filter index?

938

---

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

1013

---

Is it possible to run multiple publications and different type of publications from the same distribution database? : sql server replication

972

---

What is BCP? When does it used in sql server 2012?

1045

---

What happens if strings are casted into wrong code pages in ms sql server?

1098

---

What is the difference between function and stored procedure in sql server?

1046

---

what kind of lan types do you know? : Sql server database administration

967

---

How you can change a cross join into an inner join?

947

---

What is the significance of master, tempdb and model databases?

1066

---

How to send email from database?

997

---