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.

INPUT:



OUTPUT :







Table and Insert SQL Script : CREATE TABLE Student( [Student_Name] varchar(30), [Total_Marks] int , [Year] int) INSERT INTO Student VALUES('Rahul',90,2010) INSERT INTO Student VALUES('Sanjay',80,2010) INSERT INTO Student VALUES('Mohan',70,2010) INSERT INTO Student VALUES('Rahul',90,2011) INSERT INTO Student VALUES('Sanjay',85,2011) INSERT INTO Student VALUES('Mohan',65,2011) INSERT INTO Student VALUES('Rahul',80,2012) INSERT INTO Student VALUES('Sanjay',80,2012) INSERT INTO Student VALUES('Mohan',90,2012)



Solution 1:

SELECT * FROM ( SELECT Student_Name, Total_Marks, Year, LAG(Total_Marks) OVER(PARTITION BY Student_Name ORDER BY Year ASC) Prev_Yr_Marks FROM Student ) as A WHERE Total_Marks>=Prev_Yr_Marks ORDER BY Year;