Wednesday, October 14, 2020

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:




No comments:

Post a Comment