Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t (id, points ) as
select 1, 100 from dual union all
select 2, 130 from dual union all
select 3, 400 from dual union all
select 4, 299 from dual union all
select 5, 50 from dual union all
select 6, 600 from dual union all
select 7, 800 from dual union all
select 22, 700 from dual union all
select 20, 800 from dual union all
select 21, 850 from dual union all
select 23, 970 from dual ;
11 rows affected

select * from t ;


ID
POINTS


1
100

2
130

3
400

4
299

5
50

6
600

7
800

22
700

20
800

21
850

23
970


select id, points
, row_number() over ( order by points ) rn_
from t 
;


ID
POINTS
RN_


5
50
1

1
100
2

2
130
3

4
299
4

3
400
5

6
600
6

22
700
7

7
800
8

20
800
9

21
850
10

23
970
11


create or replace view fourplayers 
as
select id, points 
from (
  select id, points
  , row_number() over ( order by points ) rn_
  from t
)
where rn_ <= 4 ;
select * from fourplayers ;


ID
POINTS


5
50

1
100

2
130

4
299


create or replace view rangeandplay
as
select 
  count(*) numberofplayers
, min( points ) minpoints
, max( points ) maxpoints
, max( points ) - min( points ) range
, case when max( points ) - min( points ) <= 300 then 'Y' else 'N' end play
from (
  select id, points from fourplayers
) ;
select * from rangeandplay ;


NUMBEROFPLAYERS
MINPOINTS
MAXPOINTS
RANGE
PLAY


4
50
299
249
Y


delete from t
where id in ( 
  select id from fourplayers 
) and (
  ( select play from rangeandplay ) = 'Y'
) ;
4 rows affected

-- remaining rows in the table
select id, points
, row_number() over ( order by points ) rn_
from t 
;


ID
POINTS
RN_


3
400
1

6
600
2

22
700
3

20
800
4

7
800
5

21
850
6

23
970
7


select * from fourplayers;


ID
POINTS


3
400

6
600

22
700

7
800


select * from rangeandplay ;


NUMBEROFPLAYERS
MINPOINTS
MAXPOINTS
RANGE
PLAY


4
400
800
400
N


insert into t ( id, points ) values ( 5000, 450 ) ;
1 rows affected

-- player with 450 points added
select id, points
, row_number() over ( order by points ) rn_
from t 
;


ID
POINTS
RN_


3
400
1

5000
450
2

6
600
3

22
700
4

20
800
5

7
800
6

21
850
7

23
970
8


select * from rangeandplay ;


NUMBEROFPLAYERS
MINPOINTS
MAXPOINTS
RANGE
PLAY


4
400
700
300
Y


-- several players with equal scores inserted
begin
  insert into t ( id, points ) values ( 5001, 400 ) ;
  insert into t ( id, points ) values ( 5002, 400 ) ;
  insert into t ( id, points ) values ( 5003, 400 ) ;
  insert into t ( id, points ) values ( 5004, 400 ) ;
  insert into t ( id, points ) values ( 5005, 400 ) ;
  insert into t ( id, points ) values ( 5006, 400 ) ;  
  insert into t ( id, points ) values ( 5007, 400 ) ;  
end ;
/
1 rows affected

select * from rangeandplay ;


NUMBEROFPLAYERS
MINPOINTS
MAXPOINTS
RANGE
PLAY


4
400
400
0
Y


select * from fourplayers ;


ID
POINTS


3
400

5003
400

5002
400

5001
400


delete from t
where id in ( 
  select id from fourplayers 
) and (
  ( select play from rangeandplay ) = 'Y'
) ;
4 rows affected

select * from fourplayers ;


ID
POINTS


5004
400

5007
400

5006
400

5005
400


delete from t
where id in ( 
  select id from fourplayers 
) and (
  ( select play from rangeandplay ) = 'Y'
) ;
4 rows affected

select * from fourplayers ;


ID
POINTS


5000
450

6
600

22
700

7
800


select * from rangeandplay ;


NUMBEROFPLAYERS
MINPOINTS
MAXPOINTS
RANGE
PLAY


4
450
800
350
N


-- delete has no effect if PLAY is set to N -> we still have the same 4 players in the table
delete from t
where id in ( 
  select id from fourplayers 
) and (
  ( select play from rangeandplay ) = 'Y'
) ;
select * from fourplayers ;


ID
POINTS


5000
450

6
600

22
700

7
800


select id, points
, row_number() over ( order by points ) rn_
from t 
;


ID
POINTS
RN_


5000
450
1

6
600
2

22
700
3

7
800
4

20
800
5

21
850
6

23
970
7
ID	POINTS	RN_
5	50	1
1	100	2
2	130	3
4	299	4
3	400	5
6	600	6
22	700	7
7	800	8
20	800	9
21	850	10
23	970	11
ID	POINTS	RN_
5	50	1
1	100	2
2	130	3
4	299	4
3	400	5
6	600	6
22	700	7
7	800	8
20	800	9
21	850	10
23	970	11