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 are the differences between char and nchar in ms sql server?

1061

---

How to create a large table with random data for index testing in ms sql server?

1012

---

What were the latest updates to SQL Azure service?

111

---

what’s the difference between Covering Indexes and Clustered Indexes ? how to use clustered index small ?

1951

---

How to delete duplicate rows from table except one?

971

---

Explain what are sparse columns?

970

---

What is the difference between update lock and exclusive lock?

960

---

What are the authentication modes in sql server? How can it be changed?

1107

---

What are the advantages of using third-party tools?

971

---

What structure can you implement for the database to speed up table reads?

1180

---

what is denormalization and when would you go for it? : Sql server database administration

1154

---

What is sql sandbox in sql server?

1193

---

What is the recovery model?

972

---

Explain transaction isolation levels in sql server?

1093

---

What is b tree index?

902

---