SQL Query - Employee Hierarchy Level

Managers and Employees:

Given the following table, write a SQL statement that determines level of depth each employee has from the president

Expected Output:

Source Table:

Output:

Script:

SELECT

    employee_id,

    manager_id,

    job_id,

    salary,

    level - 1 depth

FROM

    hr.employees

START WITH

    manager_id IS NULL

CONNECT BY

    PRIOR employee_id = manager_id

order by 1;

SQL Query - Picking Dance partner (Scenario)

 1.   Picking Dance Partners

Pick the dance partners from the following table:

Provide the SQL statement that matches each student Id with an individual of the opposite gender.

Note: There is mismatch in the number of students, as one male student will be left without a dance partner. Please include this individual in your list as well.

Expected Output:

Source Table:

SQL Query:

Script:

with sample as (

select 1001 studentId, 'M' Gender from dual

union

select 2002 studentId, 'M' Gender from dual

union

select 3003 studentId, 'M' Gender from dual

union

select 4004 studentId, 'M' Gender from dual

union

select 5005 studentId, 'M' Gender from dual

union

select 6006 studentId, 'F' Gender from dual

union

select 7007 studentId, 'F' Gender from dual

union

select 8008 studentId, 'F' Gender from dual

union

select 9009 studentId, 'F' Gender from dual

)

SELECT

    male_partner,

    female_partner

FROM

    (

        SELECT

            studentid,

            gender,

            DENSE_RANK() OVER(

                PARTITION BY gender

                ORDER BY

                    studentid

            ) rnk

        FROM

            sample

    ) PIVOT (

        MIN ( studentid )

        FOR ( gender )

        IN ( 'M' AS male_partner, 'F' AS female_partner )

    )

ORDER BY 1;

How to insert duplicate records into one table and non-duplicate records into another table using single DML operation (Oracle DB)?

 Input:

NONDUP_REC:

DUP_REC:

Query:

DUP_RECORDS_SPLIT Data:

INSERT FIRST 

WHEN RNK = 1 THEN 

    INTO NONDUP_REC VALUES (SID,SNAME,MARKS)

ELSE 

    INTO DUP_REC VALUES (SID,SNAME,MARKS)

SELECT * FROM

(SELECT SID,SNAME,MARKS, COUNT(SID) OVER (PARTITION BY SID ORDER BY SID) RNK FROM DUP_RECORDS_SPLIT);

COMMIT;

How to convert rows to columns in SQL (Oracle DB)?

 Input:

Expected Output:

Query:

UNPIVOT_TABLE Data:

SELECT * FROM 

    (SELECT * 

        FROM UNPIVOT_TABLE) 

        PIVOT (MAX(SALES) FOR QUARTER IN ('Q1' AS Q1,'Q2' AS Q2,'Q3' AS Q3,'Q4' AS Q4));

How to convert columns into rows in SQL (Oracle Database)?

 Input:

Expected Output:

    

Query:

PIVOT_TABLE Data:

SELECT * FROM 

    (SELECT * 

        FROM PIVOT_TABLE)

        UNPIVOT(SALES FOR QUARTER IN (Q1, Q2, Q3, Q4));    

SQL Interview Question - Product Company

Scenario 1:

Source Table:

Expected Result:

SQL Query: Consider source table as Sample

Using Pivot keyword:

Without using Pivot Keyword:

Scenario 2:

Source:

Expected Result:

SQL:

Here I am sharing one of the way. If someone come cross better solution you can share it in the comments it will be useful to everyone.

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: