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

 It is one of the most common SQL Scenario based Interview Question. In this video, it has been shown how to get the Net Balance derived from Amount depending upon Debit and Credit.

Input Table :

Output Table :

Table Script ---------------------------------------- Create Table Account_Table( TranDate DateTime, TranID Varchar(20), TranType Varchar(10), Amount Float)

Insert Script

---------------------------------------

INSERT [dbo].[Account_Table] ([TranDate], [TranID], [TranType], [Amount]) VALUES ('2020-05-12T05:29:44.120', 'A10001','Credit', 50000) INSERT [dbo].[Account_Table] ([TranDate], [TranID], [TranType], [Amount]) VALUES ('2020-05-13T10:30:20.100', 'B10001','Debit', 10000) INSERT [dbo].[Account_Table] ([TranDate], [TranID], [TranType], [Amount]) VALUES ('2020-05-13T11:27:50.130', 'B10002','Credit', 20000) INSERT [dbo].[Account_Table] ([TranDate], [TranID], [TranType], [Amount]) VALUES ('2020-05-14T08:35:30.123', 'C10001','Debit', 5000) INSERT [dbo].[Account_Table] ([TranDate], [TranID], [TranType], [Amount]) VALUES ('2020-05-14T09:43:51.100', 'C10002','Debit', 5000) INSERT [dbo].[Account_Table] ([TranDate], [TranID], [TranType], [Amount]) VALUES ('2020-05-15T05:51:11.117', 'D10001','Credit', 30000)

Kindly refer to YouTube video for full Questions and it's solution with explanation and Don't forget to like and subscribe.

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.