Join Query count with various scenario

 Assume table t1 and t2 table construct as below:

create table t1(column1 number);

create table t2(column1 number);

Added Rows to t1: 

insert into t1 values(1);

insert into t1 values(1);

insert into t1 values(1);

Added Rows to t2: 

insert into t2 values(1);

insert into t2 values(1); 

select * from t1;

Output:  column1

1

1

1

select * from t2;

Output:  column1

1

1

Join : Inner process count of rows as below:

select * from t1 natural join t2; --Total count of rows are 6

select * from t1 join t2 using (column1); --Total count of rows are 6

select * from t1 cross join t2 ; --Total count of rows are 6

select * from t1  join t2 on (t1.column1=t2.column1); --Total count of rows are 6

Join : Outer process count of rows as below:

select * from t1 left outer join t2 on (t1.column1=t2.column1); --Total count of rows are 6

select * from t1 right outer join t2 on (t1.column1=t2.column1); --Total count of rows are 6

select * from t1 full outer join t2 on (t1.column1=t2.column1); --Total count of rows are 6

Analytical Function as combo Lead | Lag

 Write a query to show salary, last_salary, next_salary, difference between last vs current salary, cumulative sum of salary.

select salary, lag(salary,1,0) over (order by employee_id desc) lag_sal,

       lead(salary,1,0) over (order by employee_id desc) lead_sal,

       salary -lag(salary,1,salary) 

       over (order by employee_id desc) diff_last_vs_current,

       sum(salary) over (order by employee_id desc)cumulative_sum

       from employees;