Amazon SQL Interview Question -3 Solutions

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