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.