SQL - CROSS APPLY in SQL scripts

In this video, we would be going through on how to use “CROSS APPLY” clause in our SQL query.
Please use the sql script for self practice.
--*****************************************
-- Create Sample Test Table and Sample Data
--******************************************
if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='dbo' and TABLE_NAME='Student') drop table dbo.Student
create table dbo.Student
(
StudentID int
,StudentName varchar(10)
,StudentAge int
)


insert into dbo.Student (StudentID,StudentName,StudentAge) values (1,'Roger',15)
insert into dbo.Student (StudentID,StudentName,StudentAge) values (2,'Dave',16)
insert into dbo.Student (StudentID,StudentName,StudentAge) values (3,'David',14)
insert into dbo.Student (StudentID,StudentName,StudentAge) values (4,'Chow',15)
insert into dbo.Student (StudentID,StudentName,StudentAge) values (5,'Sid',16)
select * from dbo.Student

if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='dbo' and TABLE_NAME='ScodeCard') drop table dbo.ScodeCard
create table dbo.ScodeCard
(
StudentID int
,[Subject] varchar(50)
,Score int
)
insert into dbo.ScodeCard
select * from (
select StudentID='1', Subject = 'History', Score = '80' UNION ALL
select StudentID='1', Subject = 'Geography', Score = '75' UNION ALL
select StudentID='1', Subject = 'Litreture', Score = '70' UNION ALL
select StudentID='1', Subject = 'Science', Score = '90' UNION ALL
select StudentID='2', Subject = 'History', Score = '60' UNION ALL
select StudentID='2', Subject = 'Litreture', Score = '80' UNION ALL
select StudentID='2', Subject = 'Science', Score = '95' UNION ALL
select StudentID='3', Subject = 'History', Score = '95' UNION ALL
select StudentID='3', Subject = 'Geography', Score = '95' UNION ALL
select StudentID='4', Subject = 'History', Score = '100' UNION ALL
select StudentID='5', Subject = 'History', Score = '95' UNION ALL
select StudentID='5', Subject = 'Geography', Score = '90' UNION ALL
select StudentID='5', Subject = 'Litreture', Score = '80' UNION ALL
select StudentID='5', Subject = 'Science', Score = '100' ) a
select * from dbo.ScodeCard


--**************************************************************************
-- Scenario 1: Display the maximum and minimum score secured by each student
--***************************************************************************
-- Approach 1 - Inner Join

select st.StudentID,st.StudentName,st.StudentAge
,max(sc.Score) as Max_Score
,min(sc.Score) as Min_Score
from dbo.Student st
join dbo.ScodeCard sc
on st.StudentID = sc.StudentID
group by st.StudentID,st.StudentName,st.StudentAge
order by 1

-- Approach 2 - Inner Join with InLine Query

select st.StudentID,st.StudentName,st.StudentAge,nq.Max_Score,nq.Min_Score
from dbo.Student st
join
(
select StudentID,max(Score) as Max_Score,min(Score) as Min_Score
from dbo.ScodeCard
group by StudentID
)nq
on st.StudentID = nq.StudentID
order by 1


-- Approach 3 - CROSS APPLY

select st.StudentID,st.StudentName,st.StudentAge,ca.Max_Score,ca.Min_Score
from dbo.Student st
CROSS APPLY
(
select max(Score) as Max_Score,min(Score) as Min_Score
from dbo.ScodeCard sc
where sc.StudentID = st.StudentID
group by StudentID
)ca

order by 1

-- UDF Code
go


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnFetchSubjectScore]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
drop function dbo.fnFetchSubjectScore
go

CREATE FUNCTION dbo.fnFetchSubjectScore (@StudentID int)
RETURNS TABLE
AS
RETURN
(
SELECT * FROM dbo.ScodeCard
WHERE StudentID = @StudentID
)

go

-- INNER JOIN
select s.StudentID,s.StudentName,s.StudentAge,max(b.Score) as Max_Score,min(b.Score) as Min_Score
from dbo.Student(nolock) s
JOIN dbo.fnFetchSubjectScore(s.StudentID) b
on s.StudentID = b.StudentID
group by s.StudentID,s.StudentName,s.StudentAge


-- CROSS APPLY
select st.StudentID,st.StudentName,st.StudentAge,ca.Max_Score,ca.Min_Score
from dbo.Student st
CROSS APPLY
(
select max(Score) as Max_Score,min(Score) as Min_Score
from dbo.ScodeCard sc
where sc.StudentID = st.StudentID
group by StudentID
)ca

order by 1

select s.StudentID,s.StudentName,s.StudentAge,max(b.Score) as Max_Score,min(b.Score) as Min_Score
from dbo.Student(nolock) s
cross apply dbo.fnFetchSubjectScore(s.StudentID) b
group by s.StudentID,s.StudentName,s.StudentAge