Scenario Based Question | Oracle Database SQL Tricky Interview Questions

[@TechXplorePro]

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

[@sankar2885]

Thank you ji...well explained

[@pankajkharade6936]

Excellent Explanation Sir

[@shaliniguha1822]

This is excellent. Thanks a lot!

[@pritammishra9850]

Most amazing

[@deepakjagdale6215]

Good video.. Thanks !!

[@shikhaacholia4820]

wow, loving it

[@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

[@farhamsh7858]

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?

[@trickyinterviewquestions]

Point is Distinct is not associated with only this query. Interviewer can ask same thing for any question or even in general. There might be various approaches to tackle the performance problem due to distinct. It depends upon query, table, partitions... Usually indexing is the good idea to start with.

[@famlady]

I had been asked the alternative way to be used instead of using distinct

[@devanaidu9406]

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

[@trickyinterviewquestions]

Sure, that is in my list.

[@devanaidu9406]

@@trickyinterviewquestions thank you sir..we are eagerly waiting

[@ganeshsial]

simple use decode or case statement, not require lenghy code

[@devanaidu9406]

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

[@trickyinterviewquestions]

These are very specific questions. Well, go through google at this time. I will try to cover in my next videos. Thanks.

[@devanaidu9406]

@@trickyinterviewquestions thank you sir..please make it asap

[@Nikhil-jj7xf]

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

[@trickyinterviewquestions]

Not yet but will soon upload new videos.

[@sameerhussain6060]

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

[@trickyinterviewquestions]

Make your fundamentals strong and practice.

[@pankajkharade6936]

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;

[@SakthiVel-pi5lj]

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)

[@DiptiThakursmile]

Can I get query in sql server

[@trickyinterviewquestions]

Just check if there are any equivalent functions available

[@abhrajyotichanda6476]

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 totDelivered and Delivered >=1 then 'In Delivery' when totDelivered and Submitted >=1 then 'Awaiting for Submission' else 'Awaiting for Entry' END Status FROM TEMP order by Quote_Id;

[@ishaan12vlog]

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

[@SakthiVel-pi5lj]

Complete

[@kuldeepgupta8526]

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 )