Scenario Based Question | Oracle Database SQL Tricky Interview Questions

@trickyinterviewquestions

Got this question on one for the forum, seems bit complex hence created a video so that it is easy to understand the query and approach.

[TechXplorePro]

🔥🔥🔥Very complicated scenario explained in a detailed manner. This is the best way of Quick Learn SQL. Keep Going👍👍👍🔥🔥🔥

[thisisvikaas]

MYSQL Query for the same


select distinct(quote_id),
case
when count_id = Delivered then "Complete"
when count_id <> Delivered AND Delivered > 0 then "In Delivery"
when Submitted>1 then "Awaiting for submission"
else "Awaiting for Entry"
end as status
from
(select
quote_id,
count(quote_id) as count_id,
ifnull(sum(case when order_status = "Delivered" then 1 end), 0) as Delivered,
ifnull(sum(case when order_status = "Submitted" then 1 end), 0) as Submitted,
ifnull(sum(case when order_status = "Created" then 1 end), 0) as Created
from
order_table_new
group by quote_id)temp

[abhrajyotichanda]

WITH TEMP AS
(
select
Quote_Id,
COUNT (ORDER_ID) TOT,
count(Delivered) Delivered,
count(Submitted) Submitted,
count(Created) Created
FROM
(
Select Quote_Id, ORDER_ID,
decode (order_status, 'Delivered', 'Delivered',null) Delivered,
decode (order_status, 'Submitted', 'Submitted',null) Submitted,
decode (order_status, 'Created', 'Created',null) Created
from OrderStatus3
)
GROUP BY Quote_Id
)

SELECT Quote_Id,
case when tot=Delivered then 'Complete'
when tot<>Delivered and Delivered >=1 then 'In Delivery'
when tot<>Delivered and Submitted >=1 then 'Awaiting for Submission'
else
'Awaiting for Entry'
END Status
FROM TEMP order by Quote_Id;

[farhamsh]

thanks for explaining so well. When we use distinct in a query, interviewers mostly ask about performance, like, if there are millions of records, will you use same approach. wat cud be a possible approach?

[SakthiVel-pilj]

SELECT ID, CASE WHEN DEL>=3 THEN 'Complete'
WHEN DEL>=1 AND SUB>=1 THEN 'In Delivery'
WHEN SUB>=1 AND CRE>=1 THEN 'Pen Submit'
WHEN SUB=0 AND CRE>=1 THEN 'In Entry' END sts FROM(
SELECT * FROM SCHK
PIVOT
(
Count(STATUS)
FOR STATUS IN ( 'DELIVERED' "DEL", 'SUBMITTED' "SUB", 'CREATED' "CRE") )
ORDER BY ID)

[pankajkharade]

Hi Sir Using Case

With DS AS
(
Select QUOTE_ID, ORDER_ID, ORDER_STATUS,
(CASE
WHEN ORDER_STATUS='Delivered' THEN '0'
WHEN ORDER_STATUS='Submitted' THEN '1'
WHEN ORDER_STATUS='Created' THEN '2'
END) CS
From OrderStatus
)
Select QUOTE_ID,
(CASE
WHEN SUM(CS)=0 THEN 'COMPLETE'
When COUNT(CASE WHEN CS=0 THEN 1 ELSE 0 END)>=1 THEN 'IN DELIVERY'
When COUNT(CASE WHEN CS=1 THEN 1 ELSE 0 END)>=1 THEN 'AWAITING FOR SUBMISSION'
WHEN COUNT(CASE WHEN CS=2 THEN 1 ELSE 0 END)>=1 THEN 'AWAITING FOR ENTRY'
END) Status
From DS
GROUP BY QUOTE_ID;

[ganeshsial]

simple use decode or case statement, not require lenghy code

[devanaidu]

1)what is collection, why we have to use collections, 2) when you use nested table ?what is dense and parse in collections? can you please explain it? please explain main usages

[devanaidu]

Hi Bro very nice explanation..thank you somuch for sharing can you please make some videos on performance tuning concepts asap

[sameerhussain]

Super sir, my question is the approach and mindset… how to built that ??

[Nikhil-jjxf]

do you have any blog so we can have text verion of this query

[ishaanvlog]

plz instr ya substr m ap n like instr(sta, '|') ye condition smjha do

[kuldeepgupta]

select QUOTE_ID, case when DELIVERED_COUNT = ORDER_COUNT then 'COMPLETE'
when DELIVERED_COUNT > =1 then 'IN DELIVERY'
when SUBMITTED_COUNT > = 1 then 'AWAITING FOR SUBMISSION'
else 'AWAITING FOR ENTRY'
end status

from
(SELECT QUOTE_ID,
COUNT (ORDER_ID)
order_count,
SUM (CASE WHEN ORDER_STATUS = 'DELIVERED' THEN 1 ELSE 0 END)
DELIVERED_COUNT,
SUM (CASE WHEN ORDER_STATUS = 'SUBMITTED' THEN 1 ELSE 0 END)
SUBMITTED_COUNT,
SUM (CASE WHEN ORDER_STATUS = 'CREATED' THEN 1 ELSE 0 END)
CREATED_COUNT
FROM order_status
GROUP BY QUOTE_ID

)