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 except clause is differs from not in clause?

947

---

What is the preferred way to create a clustered and non-clustered index? Which index should you create first the clustered or non-clustered?

901

---

What is recursion? Is it possible for a stored procedure to call itself or recursive stored procedure? How many levels of sp nesting is possible?

956

---

What does set rowcount do?

898

---

What is the command to change the recovery model?

1037

---

What is transact-sql ddl trigger?

1101

---

How many partitions a clustered index has by default in sql server 2012?

1083

---

What is a table called, if it has neither cluster nor non-cluster index? What is it used for?

1022

---

What is a db view?

889

---

What is transaction server auto commit?

1056

---

What is update_statistics command?

1057

---

What are the advantages to use stored procedures?

1099

---

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

253

---

How to modify an existing user defined function?

1097

---

Tell me what do we need queues in sql service broker?

981

---