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)

Does partitioning ssd reduce performance?

479

---

how can you select rexcord(rows) from table A which is not present in Table B . Id being the FK in Table B referencing to ID Table A

1202

---

What are the differences between decimal and float in ms sql server?

556

---

What is NOT NULL Constraint in sql server 2012?

589

---

What are the database objects? : SQL Server Architecture

534

---

---

Why do we backup Active Directory ?

601

---

Can a database be shrunk to 0 bytes, if not, why?

634

---

Explain concepts of analysis services?

503

---

Do you know what is xpath?

548

---

How to Insert multiple rows with a single insert statement?

539

---

What type of locking occurs during the snapshot generation? : sql server replication

854

---

what method you can use to reduce the overhead of Reporting Services data sources?

145

---

Is sql server a database?

521

---

What is the difference between online clustering and Offline clustering?

1904

---

What is unique key constraint?

626

---