SQL Server 2019 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE #Students(
[Name] varchar(50), 
[Surname] varchar(50),
[Period] int,
[Sport] int, 
[History] int, 
[English] int, 
[Geography] int)
INSERT INTO 
#Students([Name],[Surname],[Period],[Sport],[History],[English],[Geography])
VALUES
('Luke','Green',1,30,20,23,NULL),
('Luke','Green',1,30,20,23,NULL),
('Mary','Brown',1,17,15,NULL,30),
('John','Red',1,18,NULL,21,30),
('Walter','White',1,22,20,5,30),
('Luke','Green',2,30,20,23,NULL),
('Mary','Brown',2,NULL,15,17,30),
('John','Red',2,18,NULL,11,30),
('Walter','White',2,2,32,1,30),
('Luke','Green',3,20,15,15,12),
('Mary','Brown',3,0,3,NULL,4),
('Walter','White',2,2,32,1,30),
('Luke','Green',3,20,15,15,12),
('Mary','Brown',3,0,3,NULL,4),
('John','Red',3,18,40,21,30),
('Walter','White',3,17,19,15,30),
('Mary','Brown',1,17,15,NULL,30),
('John','Red',3,NULL,23,23,30);
18 rows affected

SELECT *
FROM #Students


Name
Surname
Period
Sport
History
English
Geography


Luke
Green
1
30
20
23
null

Luke
Green
1
30
20
23
null

Mary
Brown
1
17
15
null
30

John
Red
1
18
null
21
30

Walter
White
1
22
20
5
30

Luke
Green
2
30
20
23
null

Mary
Brown
2
null
15
17
30

John
Red
2
18
null
11
30

Walter
White
2
2
32
1
30

Luke
Green
3
20
15
15
12

Mary
Brown
3
0
3
null
4

Walter
White
2
2
32
1
30

Luke
Green
3
20
15
15
12

Mary
Brown
3
0
3
null
4

John
Red
3
18
40
21
30

Walter
White
3
17
19
15
30

Mary
Brown
1
17
15
null
30

John
Red
3
null
23
23
30


WITH DupliTable AS(
   SELECT [Name], [Surname], [Period],
   ROW_NUMBER() OVER(PARTITION BY [Name], [Surname], [Period] ORDER BY [Name]) AS [rn]
   FROM #Students
)
DELETE FROM DupliTable WHERE [rn] > 1
6 rows affected

SELECT * FROM #Students


Name
Surname
Period
Sport
History
English
Geography


Luke
Green
1
30
20
23
null

John
Red
1
18
null
21
30

Walter
White
1
22
20
5
30

Luke
Green
2
30
20
23
null

Mary
Brown
2
null
15
17
30

John
Red
2
18
null
11
30

Walter
White
2
2
32
1
30

Luke
Green
3
20
15
15
12

Mary
Brown
3
0
3
null
4

John
Red
3
18
40
21
30

Walter
White
3
17
19
15
30

Mary
Brown
1
17
15
null
30

Name	Surname	Period	Sport	History	English	Geography
Luke	Green	1	30	20	23	null
Luke	Green	1	30	20	23	null
Mary	Brown	1	17	15	null	30
John	Red	1	18	null	21	30
Walter	White	1	22	20	5	30
Luke	Green	2	30	20	23	null
Mary	Brown	2	null	15	17	30
John	Red	2	18	null	11	30
Walter	White	2	2	32	1	30
Luke	Green	3	20	15	15	12
Mary	Brown	3	0	3	null	4
Walter	White	2	2	32	1	30
Luke	Green	3	20	15	15	12
Mary	Brown	3	0	3	null	4
John	Red	3	18	40	21	30
Walter	White	3	17	19	15	30
Mary	Brown	1	17	15	null	30
John	Red	3	null	23	23	30