Exists and IN, Not Exists and Not IN, LAG() and LEAD()

select distinct score_level from t_test order by score_level;

create table T_SCORE_LEVEL(level_id int, level_name varchar2(30));

insert into T_SCORE_LEVEL values(1,'Excelent');
insert into T_SCORE_LEVEL values(2,'Good');
insert into T_SCORE_LEVEL values(3,'Passed');
insert into T_SCORE_LEVEL values(4,'NonPassed');
insert into T_SCORE_LEVEL values(null,'Worse');

--The example of the subquery in ORDER BY statement
select score_level,max(score),min(score) from t_test group by score_level
--left outer join T_SCORE_LEVEL on T_SCORE_LEVEL.LEVEL_ID = t_test.score_level
order by (select T_SCORE_LEVEL.LEVEL_NAME from T_SCORE_LEVEL where T_SCORE_LEVEL.LEVEL_ID =t_test.score_level);

update t_test set score_level= 5 where score<40;

--Difference between EXISTS and IN, Not Exists and Not IN
select count(*) from t_test where not exists (select * from t_score_level where t_score_level.level_id = t_test.score_level);

select count(*) from t_test where t_test.score_level not in (select level_id from t_score_level);

select count(*) from t_test where exists (select * from t_score_level where t_score_level.level_id = t_test.score_level);

select count(*) from t_test where t_test.score_level in (select level_id from t_score_level);

select count(*) from t_test left outer join t_score_level on t_score_level.level_id = t_test.score_level;

select level_id from t_score_level order by level_id desc;

select * from t_score_level where exists (select null from dual);
select * from t_score_level where not exists (select null from dual);

select * from t_test where exists (select null from dual);
select * from t_test where not exists (select null from dual);

select * from t_score_level t1 where not exists (select null from t_score_level t2 where t1.level_id=t2.level_id);
select * from t_score_level t1 where  t1.level_id  not in (select t2.level_id from t_score_level t2);

select * from t_score_level t1 where exists (select 1 from t_score_level t2 where t1.level_id=t2.level_id);
select * from t_score_level t1 where  t1.level_id  in (select t2.level_id from t_score_level t2);

--the example for funcation LAG() and LEAD()
with a as (select 1 id,'a' name from dual
union
select 2 id,'b' name from dual
union
select 23 id,'c' name from dual
union
select 14 id,'d' name from dual
union
select 8 id,'e' name from dual
 )
select id,name,lag(id,2,'') over(order by name),lead(id,1,'')over(order by name) from a