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)

Explain log shipping?

1030

---

What is table value parameters (tvp)?

1108

---

If you want to send some data from access database to sql server database. What are different component of ssis will you use?

988

---

how you can list all the tables in a database?

1034

---

What is a partition key?

962

---

What are the steps you can take to avoid “deadlocks”?

981

---

What are “lost updates”?

1008

---

What languages bi uses to achieve the goal?

1007

---

What is difference between delete & truncate commands?

1089

---

What is PROJECTION Operation?

1030

---

Where the sql logs gets stored? : sql server database administration

974

---

How we create SQL Server 2005 Reporting Services ? Give me Sample

2000

---

What is the difference between system objects and user objects?

1169

---

How to test subquery results with the exists operator?

1112

---

tell me what is blocking and how would you troubleshoot it? : Sql server database administration

911

---