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)

Mention what are the different types of ssrs reports?

144

---

Why use “nolock” in sql server?

985

---

What is xml datatype?

1035

---

1.what is the diff between nolock optimizer and read uncommitted isolation? 2.what is the diff between revoke and deny? 3.what is percieved down time? 4.whether password protection are required for backups?if yes why?if no why? 5.what is fill factor? 6.what is cost analysis? 7.what is mean by piece meal restore? 8.what is 'rowguidcol'? 9.impersonate permission? 10.what is selectivity?

2031

---

How does recursive cte works in sql server?

942

---

How to insert data with null values?

971

---

How to grant a permission in ms sql server using "grant execute" statements?

1027

---

What are the underflow and overflow behaviors on float literals?

1061

---

What are the types of subquery?

989

---

Can primary key be a foreign key?

999

---

Tell me what is a linked server?

989

---

How do you run a trace?

849

---

what is database replicaion? : Sql server database administration

1106

---

How to define output parameters in stored procedures?

960

---

List out the different types of locks available in sql server?

934

---