SQL Interview questions

Scenario's:

1. I have an employee table 4 of them are male candidates and 4 of them are female candidates. I need result in such a way that alternatively I have to show male and female employees

Employee Table:

Expected Result:

Query:

SELECT NAME,GENDER FROM (

SELECT

    NAME,GENDER,DECODE(GENDER,'MALE',1,0) ORD_VAL,ROW_NUMBER() OVER (PARTITION BY GENDER ORDER BY NAME) RNK

FROM

    employee) ORDER BY RNK,DECODE(GENDER,'MALE',1,0);

Output:

2. I have Table A (Driving Table) and Table B (Lookup table).I have to pick all the values from Table-A

but if I came across any duplicates when I join with Table-B then I have to pick only one value from Table-B.

For Example:

Table A(Employee):

Table B(Department):

Expected Result:

Query:

select name,department_name from (

select e.name,d.name department_name,row_number() over (partition by rn order by d.name) rn from

(select name,department_id,rownum rn from employee) e left outer join department d on e.department_id = d.department_id)

where rn=1

Output: