Amazon SQL Interview Question -3 Solutions

SQL Hindi Playlist :    • SQL Tutorials-Hindi  
MS SQL English Playlist :    • SQL Tutorial-English  

#mssqlserver , #sqlserver , #mssql , #database , #technology , #nosql , #informationtechnology , #sqldeveloper , #dba , #mysqldeveloper , #sqlservergeeks , #sqlservers, #sqlserveres , #mysql , #databaseadministrator , #mysqlserver , #trends ,
#sql #sqlserver #education #sqlinterviewquestions #mssqlserver #sqlforbeginners #sqltutorial #sqltraining #sqlcourse

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)
--select * from Student

----CTE
with ct2(student_name,total_marks,[Year],Prev_yr_Marks) AS
(
select student_name,total_marks,[Year],
LAG(Total_Marks) Over(Partition By Student_Name Order By Year)
As Prev_yr_Marks From Student
)
select * from ct2 where Total_Marks (greatet than equal - convert into symbol) Prev_yr_Marks
--Temp Table
Create Table #temp
(
student_name Varchar(100),
total_marks INT,
[Year] INT,
Prev_yr_Marks INT
)
Insert Into #temp(student_name,total_marks,[Year],Prev_yr_Marks)
select student_name,total_marks,[Year],
LAG(Total_Marks) Over(Partition By Student_Name Order By Year) From Student

select * from #temp where Total_Marks (greatet than equal - convert into symbol) Prev_yr_Marks
---UDF
CREATE FUNCTION ff ()
RETURNS TABLE
AS
RETURN
select student_name,total_marks,[Year],
LAG(Total_Marks) Over(Partition By Student_Name Order By Year) AS Prev_yr_Marks From Student

select * from dbo.ff() where Total_Marks (greatet than equal - convert into symbol) Prev_yr_Marks