Interview Question 1 - Print only those ParentID which are having 1 Female Child and 1 Male Chile both

Create table in sqlserver database using below query

 

select * into test from (
select '1' p,'M' as g union all
 select '1','M' as g union all
 select '1','F' as g union all
 select '2','F' as g union all
 select '2','F' as g union all
 select '2','M' as g union all
 select '3','F' as g union all
 select '3','M' as g union all
 select '4','M' as g union all
 select '5','F' as g union all
 select '6','F' as g union all
 select '6','M' as g union all
 select '7','F' as g union all
 select '7','F') temp

Table Looks like as below:

Col1 Col2

1 M

1 M

1 F

2 F

2 F

2 M

3 F

3 M

4 M

5 F

6 F

6 M

7 F

7 F

Query 

select 

t1.p 

from (

select 

count(*) F_Count

, p 

from 

test 

where 

g = 'F' group by p

)t1 

join

(

select 

count(*) M_Count

, p 

from 

test 

where 

g = 'M' group by p

) t2 on t1.p = t2.p

where 

F_Count = 1 and M_Count =1 

Query 2

select distinct

    p

from

    (

    select

        p,

        g,

        count(*) over (partition by p) count,

        count(case when g='M' then 1 end)

            over (partition by p) male,

        count(case when g='F' then 1 end)

            over (partition by p) female

    from

        test

    )as t

where

    t.male=1

    and

    t.female=1