Source:
Given following set of integers, write an SQL statement to determine the expected outputs:
Source :
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;
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:
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
);
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:
Output:
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
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:
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.
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.
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;
Managers and Employees:
Given the following table, write a SQL statement that determines level of depth each employee has from the president
Source Table:
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;
1. Picking Dance Partners
Pick the dance partners from the following table:
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:
SQL Query:
Input:
Input:
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));
Input:
Expected Output:
Query:
PIVOT_TABLE Data:
SELECT * FROM
(SELECT *
FROM PIVOT_TABLE)
UNPIVOT(SALES FOR QUARTER IN (Q1, Q2, Q3, Q4));
