SQL / ORACLE- Scenario Based Interview Questions & Answers PART- 17

Problem Statement:-

Order_Tbl has four columns namely ORDER_ID, PRODUCT_ID, QUANTITY and PRICE.

ORDER_Tbl Table

                            

                            

Write a SQL query that will explode the above data into single unit level records as shown below.

OUTPUT TABLE

                           

SOLUTION

 SELECT
    MT.Order_ID,
    MT.Product_ID,
    1 AS quantity
FROM
    ORDER_TABLE MT
INNER JOIN
    (
        SELECT 1 AS nbr UNION ALL SELECT 2 AS nbr UNION ALL
        SELECT 3 AS nbr UNION ALL SELECT 4 AS nbr UNION ALL SELECT 5 AS nbr

    ) N ON N.nbr <= MT.quantity

Using Recursive CTE

WITH  CTE_Order As
(
-- Anchor Query
Select Order_ID,Product_ID, 1 As Quantity,1 As Cnt
FROM ORDER_TABLE
UNION ALL
-- Recursive Part
Select A.Order_ID,A.Product_ID, B.Quantity,B.Cnt+1
FROM ORDER_TABLE As A INNER JOIN CTE_Order As B
ON  A.Product_ID=B.Product_ID WHERE B.Cnt+1 <= A.Quantity
)
Select Order_ID,Product_ID,  Quantity
FROM CTE_Order

ORDER BY Product_ID,Order_ID

Kindly refer to YouTube video for more details and Don't forget to like and subscribe.

SQL / ORACLE- Scenario Based Interview Questions & Answers PART- 16

Problem Statement:-

Order_Tbl has four columns namely ORDER_DAY, ORDER_ID, PRODUCT_ID, QUANTITY and PRICE

Order_Tbl Table        

                

PART A

Write a SQL query to get the highest sold Products (Quantity*Price) on both days.

OUTPUT TABLE

                 

PART B

Write a SQL query to get all product's total sales on 1st May and 2nd May adjacent to each other.

OUTPUT TABLE

                 

PART C

Write a SQL query to get all products day wise, that was ordered more than once.

OUTPUT TABLE

                

SOLUTION

PART A

SELECT A.ORDER_DAY,B.PRODUCT_ID ,A.Sold_Amount
FROM (
(SELECT ORDER_DAY, MAX(QUANTITY*PRICE)as Sold_Amount
FROM Order_Tbl GROUP BY ORDER_DAY) A
INNER JOIN
(SELECT ORDER_DAY ,PRODUCT_ID,QUANTITY*PRICE As Sold_Amount
FROM Order_Tbl ) B

ON A.ORDER_DAY =B.ORDER_DAY AND A.Sold_Amount=B.Sold_Amount)

PART B

SELECT PRODUCT_ID,
SUM(ISNULL(Sales_01,0)) As Total_Sales_01,
SUM(ISNULL(Sales_02,0)) As Total_Sales_02
FROM
(
SELECT PRODUCT_ID,
CASE WHEN ORDER_DAY ='2015-05-01' THEN Total_Sales END as 'Sales_01',
CASE WHEN ORDER_DAY ='2015-05-02' THEN Total_Sales END as 'Sales_02'
FROM(
SELECT ORDER_DAY,PRODUCT_ID, SUM(QUANTITY*PRICE) As Total_Sales
FROM  Order_Tbl
GROUP BY ORDER_DAY,PRODUCT_ID) A
)B

GROUP BY PRODUCT_ID

Using PIVOT Function

SELECT PRODUCT_ID,
ISNULL([2015-05-01],0) As Total_Sales_01,
ISNULL([2015-05-02],0) As Total_Sales_02
FROM
(
SELECT ORDER_DAY,PRODUCT_ID, QUANTITY*PRICE As Total_Sales
FROM  Order_Tbl )BaseTble
PIVOT(
     SUM(Total_Sales)
     FOR ORDER_DAY IN ([2015-05-01],[2015-05-02])

) As Pivot_Table

PART C

SELECT ORDER_DAY,PRODUCT_ID
FROM Order_Tbl
GROUP BY ORDER_DAY,PRODUCT_ID

HAVING COUNT(*) > 1

Kindly refer to YouTube video for more details and Don't forget to like and subscribe.

SQL / ORACLE- Scenario Based Interview Questions & Answers PART- 15

Problem Statement:-

Order_Tbl has four columns namely ORDER_DAY, ORDER_ID, PRODUCT_ID, QUANTITY, and PRICE

Order_Tbl Table

                               

PART A

Write a SQL query to get all the products that got sold on both the days and the number of times the product is sold.

    

OUTPUT Table

                             

PART B

Write a SQL query to get products that were ordered on 02-May-2015 but not on 01-May-2015.

OUTPUT Table

                             

SOLUTION

PART A

SELECT  PRODUCT_ID,COUNT(PRODUCT_ID) AS [COUNT],Count(distinct ORDER_DAY)

FROM Order_Tbl

GROUP BY PRODUCT_ID

HAVING Count(distinct ORDER_DAY) > 1

PART B

Using Subquery

SELECT DISTINCT(PRODUCT_ID) FROM Order_Tbl

WHERE ORDER_DAY = '2015-05-02'

AND PRODUCT_ID NOT in (

SELECT PRODUCT_ID from Order_Tbl where ORDER_DAY = 

'2015-05-01')

Using Join

SELECT A.PRODUCT_ID--,B.PRODUCT_ID

FROM (

(

SELECT PRODUCT_ID

FROM Order_Tbl WHERE ORDER_DAY='2015-05-02'

)A

LEFT JOIN

(

SELECT PRODUCT_ID

FROM Order_Tbl WHERE ORDER_DAY='2015-05-01'

)B

ON A.PRODUCT_ID=B.PRODUCT_ID

)

WHERE B.PRODUCT_ID IS NULL

Using EXCEPT Query

SELECT PRODUCT_ID

FROM Order_Tbl WHERE ORDER_DAY='2015-05-02'

EXCEPT

SELECT PRODUCT_ID

FROM Order_Tbl WHERE ORDER_DAY='2015-05-01'

Kindly refer to YouTube video for more details and Don't forget to like and subscribe.

SQL / ORACLE- Scenario Based Interview Questions & Answers PART- 14

Problem Statement:-

Student Table has three columns Student_Name, Total_Marks and Year. User has to write a SQL query to display Student_Name, Total_Marks, Year,  Prev_Yr_Marks for those whose Total_Marks are greater than or equal to the previous year   

Student Table

OUTPUT Table

SOLUTION

SELECT Student_Name,Total_Marks,Year,Prev_Yr_Marks

FROM

(

SELECT Student_Name,Year,Total_Marks,Prev_Yr_Marks ,

CASE WHEN Total_Marks > = Prev_Yr_Marks Then 1 Else 0 END as Flag

FROM

(

SELECT Student_Name,Year,Total_Marks,

LAG(Total_Marks) OVER(PARTITION BY Student_Name ORDER BY Year )

AS Prev_Yr_Marks

FROM Student)A

) B

WHERE Flag=1

SQL / ORACLE- Scenario Based Interview Questions & Answers PART- 13

SQL / ORACLE- Scenario Based Interview Questions & Answers PART- 13

Problem Statement:-

Given below table Emp as Input which has two columns ‘Group’ and ‘Sequence’, Write a SQL query to find the maximum and minimum values of continuous ‘Sequence’ in each ‘Group’

Emp Table

OUTPUT

SOLUTION :

SELECT [Group],

MIN([Sequence]) As Min_Seq,

MAX([Sequence]) As Max_Seq

FROM

(

SELECT [Group],

[Sequence],

[Sequence] - ROW_NUMBER() OVER(Partition BY [Group] ORDER BY [Sequence]) as [Split]

From Emp

) A

GROUP BY [Group],[Split]

ORDER BY [Group]

Above solution has been explained in below video

SQL / ORACLE- Scenario Based Interview Questions & Answers PART- 12

SQL / ORACLE- Scenario Based Interview Questions & Answers PART- 12

Problem Statement:-

Transatcion_tbl Table has four columns CustID, TranID, TranAmt, and  TranDate. User has to display all these fields along with maximum TranAmt for each CustID and ratio of TranAmt and maximum TranAmt for each transaction.

Transaction_Tbl

Output

Solution 1: By using Subquery

SELECT A.CustID,TranID,A.TranAmt,MaxTranAmt,(TranAmt/MaxTranAmt) AS Ratio,TranDate
FROM Transaction_Tbl A
INNER JOIN(SELECT CustID, Max(TranAmt) As MaxTranAmt FROM Transaction_Tbl
GROUP BY CustID) B
ON A.CustID=B.CustID

Solution 2: By using CTE (Common Table Expression )

WITH CTE (CustID, TranID, TranAmt) AS(SELECT CustID, TranID, TranAmt FROM Transaction_Tbl
),CTE_MaxTran(CustID, MaxTranAmt) AS(SELECT CustID, Max(TranAmt) As MaxTranAmt FROM Transaction_Tbl
GROUP BY CustID
)SELECT A.TranID,A.TranAmt,MaxTranAmt,(TranAmt/MaxTranAmt) AS Ratio
FROM CTE A
INNER JOIN CTE_MaxTran B
ON A.CustID=B.CustID

Above solution has been explained in below video.

Please do like , comment , share and subscribe my youtube channel