SQL Interview Questions And Answers Part 61 | SQL questions for Product based companies

SQL Interview Questions & Answer - Part 56 | Google SQL Question | Consulting Bench Time

SQL Interview Questions Part 55 | FACEBOOK/META Interview Question & Answers

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- 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.