SQL Query - Phone Directory

 Your Customer phone directory table allows individuals to setup a home, cellular, or a work phone number.

Write a SQL statement to transform the table into the expected output.

Expected Output:

Source:

Output:

SQL query - Two predicates

 Write an SQL statement given the following requirements.

For every customer that had a delivery to Hyderabad, provide a result set of the customer orders that were delivered to Bangalore.

Expected Output:

Source Table:

Output:

Script:

WITH CUSTOMER_ORDER AS (

SELECT 1001 CUSTOMER_ID,'Ord1234' ORDERID,'HYDERABAD' DELIVERY_CITY,100 AMOUNT FROM DUAL

UNION

SELECT 1001 CUSTOMER_ID,'Ord1235' ORDERID,'BANGALORE' DELIVERY_CITY,123 AMOUNT FROM DUAL

UNION

SELECT 1001 CUSTOMER_ID,'Ord1236' ORDERID,'HYDERABAD' DELIVERY_CITY,341 AMOUNT FROM DUAL

UNION

SELECT 2001 CUSTOMER_ID,'Ord1237' ORDERID,'HYDERABAD' DELIVERY_CITY,890 AMOUNT FROM DUAL

UNION

SELECT 2001 CUSTOMER_ID,'Ord1238' ORDERID,'BANGALORE' DELIVERY_CITY,44 AMOUNT FROM DUAL

UNION

SELECT 3001 CUSTOMER_ID,'Ord1244' ORDERID,'HYDERABAD' DELIVERY_CITY,99 AMOUNT FROM DUAL

UNION

SELECT 4001 CUSTOMER_ID,'Ord1245' ORDERID,'HYDERABAD' DELIVERY_CITY,1020 AMOUNT FROM DUAL

UNION

SELECT 4001 CUSTOMER_ID,'Ord1246' ORDERID,'CHENNAI' DELIVERY_CITY,234 AMOUNT FROM DUAL

) 

SELECT *

FROM customer_order o

WHERE

    2 = (

        SELECT count(distinct delivery_city)

        FROM customer_order i

        WHERE

            delivery_city IN (

                'HYDERABAD',

                'BANGALORE'

            )

            AND o.customer_id = i.customer_id

    )

ORDER BY

    1;

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));