SQL Interview - Seats Availability

 Given following set of integers, write an SQL statement to determine the expected outputs:

Source :

Expected Output:

Source:

Output:

Scripts:

WITH NUMBER_TRICK AS (

SELECT 0 NUM FROM DUAL

UNION ALL

SELECT 7 NUM FROM DUAL

UNION ALL

SELECT 18 NUM FROM DUAL

UNION ALL

SELECT 19 NUM FROM DUAL

UNION ALL

SELECT 24 NUM FROM DUAL

UNION ALL

SELECT 27 NUM FROM DUAL

UNION ALL

SELECT 31 NUM FROM DUAL

UNION ALL

SELECT 35 NUM FROM DUAL

UNION ALL

SELECT 36 NUM FROM DUAL

UNION ALL

SELECT 37 NUM FROM DUAL

UNION ALL

SELECT 38 NUM FROM DUAL

UNION ALL

SELECT 41 NUM FROM DUAL

UNION ALL

SELECT 47 NUM FROM DUAL

UNION ALL

SELECT 50 NUM FROM DUAL

) 

SELECT

    gap_start + 1 gap_start,

    gap_end - 1 gap_end

FROM

    (

        SELECT

            num gap_start,

            LEAD(num, 1) OVER(

                ORDER BY

                    num

            ) gap_end,

            LEAD(num, 1) OVER(

                ORDER BY

                    num

            ) - num gap

        FROM

            number_trick

    )

WHERE

    gap > 1;

SQL Interview - Match grand Total

 You work for a software company that released a 2-player game and you need to tally the scores.

Given the following table , write a SQL statement to determine the reciprocals and calculate their aggregate score. In the data below, players 3 and 4 have two valid entries, but their scores need to be aggregated together.

Source:

Expected Output:

Source:

Output:

Script:

with game as (

select 1 playera, 2 playerb, 124 score from dual

union all

select 3 playera, 4 playerb, 112 score from dual

union all

select 4 playera, 3 playerb, 32 score from dual)

SELECT 

    playera,

    playerb,

    score

FROM

    ( SELECT

            CASE

                WHEN playera >= playerb THEN

                    playerb

                ELSE

                    playera

            END playera,

            CASE

                WHEN playera >= playerb THEN

                    playera

                ELSE

                    playerb

            END playerb, SUM(score) AS score

        FROM game

        GROUP BY

                CASE WHEN playera >= playerb THEN playerb ELSE playera END,

                CASE WHEN playera >= playerb THEN playera ELSE playerb END

    );

SQL Query - Indeterminate Process Log

 Your process log has several workflows broken step by numbers with the possible status values of Complete , Running or Error.

Your task is write SQL statement that creates overall status based upon the following requirements.

1. If all the workflow steps have a status of complete , set overall status as complete. (ex. Bravo)

2. If all the workflow steps have a status of Error , set overall status as Error. (ex. Foxtrot)

3. If all the workflow steps have the combination of error and complete , set overall status should be Indeterminate. (ex. Alpha , Charlie and Echo)

4. If all the workflow steps have the combination of Running and complete , set overall status should be Running. (ex. Delta)

Source:

Expected Output:

Source:

Output:

Script:

WITH PROCESS_LOG AS (

SELECT 'Alpha' Workflow,1 stepno, 'Error' Status FROM DUAL

UNION ALL

SELECT 'Alpha' Workflow,2 stepno, 'Complete' Status FROM DUAL

UNION ALL

SELECT 'Bravo' Workflow,1 stepno, 'Complete' Status FROM DUAL

UNION ALL

SELECT 'Bravo' Workflow,2 stepno, 'Complete' Status FROM DUAL

UNION ALL

SELECT 'Charlie' Workflow,1 stepno, 'Complete' Status FROM DUAL

UNION ALL

SELECT 'Charlie' Workflow,2 stepno, 'Error' Status FROM DUAL

UNION ALL

SELECT 'Delta' Workflow,1 stepno, 'Complete' Status FROM DUAL

UNION ALL

SELECT 'Delta' Workflow,2 stepno, 'Running' Status FROM DUAL

UNION ALL

SELECT 'Echo' Workflow,1 stepno, 'Running' Status FROM DUAL

UNION ALL

SELECT 'Echo' Workflow,2 stepno, 'Error' Status FROM DUAL

UNION ALL

SELECT 'Foxtrot' Workflow,1 stepno, 'Error' Status FROM DUAL)

--decode(status,'Error','Indetermine',status) 

select workflow,status from (

select workflow,

case when status='Error' 

and count(rnk) over (partition by workflow) > 1

then 'Indetermine' else status end  Status,

rnk

from (

SELECT WORKFLOW, STEPNO, STATUS,

DENSE_RANK() OVER (PARTITION BY WORKFLOW ORDER BY 

                        CASE WHEN STATUS='Error' then 1 

                        When Status = 'Running' then 2

                        when status='Complete' then 3 end,stepno) rnk

FROM PROCESS_LOG))

where rnk = 1

SQL Query - Inventory Tracking

     You can work for manufacturing company and need to track inventory adjustments from the warehouse. Some days inventory increases, on other days the inventory decreases.

Write an SQL statement that will provide a running balance of the inventory.

Source:

Expected Output:

Source Table:

Output:

Script:

WITH INVENTORY AS (

SELECT TO_DATE('28-APR-2021') INV_DATE, 100 QUANTITY_ADJ FROM DUAL

UNION ALL

SELECT TO_DATE('29-APR-2021') INV_DATE, -50 QUANTITY_ADJ FROM DUAL

UNION ALL

SELECT TO_DATE('30-APR-2021') INV_DATE, 75 QUANTITY_ADJ FROM DUAL

UNION ALL

SELECT TO_DATE('01-MAY-2021') INV_DATE, -50 QUANTITY_ADJ FROM DUAL

UNION ALL

SELECT TO_DATE('02-MAY-2021') INV_DATE, 89 QUANTITY_ADJ FROM DUAL

UNION ALL

SELECT TO_DATE('03-MAY-2021') INV_DATE, 120 QUANTITY_ADJ FROM DUAL

UNION ALL

SELECT TO_DATE('04-MAY-2021') INV_DATE, -150 QUANTITY_ADJ FROM DUAL

UNION ALL

SELECT TO_DATE('05-MAY-2021') INV_DATE, 40 QUANTITY_ADJ FROM DUAL)

SELECT

    inv_date,

    quantity_adj quantity_adjusted,

    SUM(quantity_adj) OVER(

    ORDER BY

        inv_date

    ) quantity_available

FROM

    inventory;

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;