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