Write a SQL Query to find Student details who scored marks greater than equal to previous year

 

 

 

 

CREATE TABLE STUDENT_MARKS(

STUDENT_NAME VARCHAR(50),

TOTAL_MARKS NUMBER,

YEAR NUMBER(4)

);INSERT INTO STUDENT_MARKS VALUES('Ram','90','2010');

INSERT INTO STUDENT_MARKS VALUES('Neena','80','2010');

INSERT INTO STUDENT_MARKS VALUES('John','70','2010');

INSERT INTO STUDENT_MARKS VALUES('Ram','90','2011');

INSERT INTO STUDENT_MARKS VALUES('Neena','85','2011');

INSERT INTO STUDENT_MARKS VALUES('John','65','2011');

INSERT INTO STUDENT_MARKS VALUES('Ram','80','2012');

INSERT INTO STUDENT_MARKS VALUES('Neena','80','2012');

INSERT INTO STUDENT_MARKS VALUES('John','90','2012');

commit;

 

SELECT

a.*,

LEAD(TOTAL_MARKS) OVER(PARTITION BY STUDENT_NAME ORDER BY STUDENT_NAME, YEAR DESC) AS PREV_YEAR_MARKS

FROM STUDENT_MARKS a;

 

WITH TEMP AS(

SELECT

a.*,

LEAD(TOTAL_MARKS) OVER(PARTITION BY STUDENT_NAME ORDER BY STUDENT_NAME, YEAR DESC) AS PREV_YEAR_MARKS

FROM STUDENT_MARKS a

)

SELECT * FROM TEMP WHERE TOTAL_MARKS >= PREV_YEAR_MARKS;

 

Alternate Solution:

 

SELECT

a.STUDENT_NAME,

a.TOTAL_MARKS,

a.YEAR,

b.TOTAL_MARKS AS PREV_YEAR_MARKS

FROM STUDENT_MARKS a

JOIN STUDENT_MARKS b

ON a.STUDENT_NAME = b.STUDENT_NAME

AND a.YEAR = b.YEAR + 1

WHERE a.TOTAL_MARKS >= b.TOTAL_MARKS;