Grade based on employee salary

Grade based on employee salary(Subscriber Comments Reply Video 20 Grade based on employee salary)



CREATE TABLE emp_t (
    empno     number,
    ename     varchar2(100),
sal       number
);

insert into emp_t values (1,'KING',100);
insert into emp_t values (2,'BLAKE',500);
insert into emp_t values (3,'CLARK',1200);
insert into emp_t values (4,'JONES',2500);
insert into emp_t values (5,'SCOTT',3000);
insert into emp_t values (6,'FORD',700);
insert into emp_t values (7,'SMITH',1700);
insert into emp_t values (8,'ALLEN',2600);
insert into emp_t values (9,'WARD',400);
insert into emp_t values (10,'MARTIN',1500);
commit;

----------------------------------------------------------------
select EMPNO, ENAME, SAL,
       case when sal >0 and sal <=1000 then 'A'
            when sal >1000 and sal <=2000 then 'B'
            else 'C' end grade
from emp_t;
----------------------------------------------------------------

select EMPNO, ENAME, SAL,
       decode(ceil(SAL/1000),1,'A',2,'B','C')
from emp_t;
----------------------------------------------------------------
select EMPNO, ENAME, SAL,r.g
from emp_t,(select 0 mi,1000 mx,'A' g from dual
union
select 1001 mi,2000 mx,'B' g from dual
union
select 2001 mi,999999 mx,'C' g from dual) R
where sal >=mi and sal <=mx
order by 1;
----------------------------------------------------------------

Find the last two recharge amount for each customer

Find the last two recharge amount for each customer ( Subscriber Comments Reply Video 19 Get the last two recharged amount)

Data Setup Scripts:

CREATE TABLE recharge_detail (
    recharge_date     DATE,
    cust_name         VARCHAR2(100),
    recharge_amount   NUMBER
);

insert into recharge_detail values( to_date('01/01/2019','DD/MM/YYYY'), 'Ragu',100);
insert into recharge_detail values( to_date('01/02/2019','DD/MM/YYYY'), 'Ragu',150);
insert into recharge_detail values( to_date('01/03/2019','DD/MM/YYYY'), 'Ragu',120);
insert into recharge_detail values( to_date('01/04/2019','DD/MM/YYYY'), 'Ragu',170);

insert into recharge_detail values( to_date('01/01/2019','DD/MM/YYYY'), 'Ravi',299);
insert into recharge_detail values( to_date('01/02/2019','DD/MM/YYYY'), 'Ravi',399);
insert into recharge_detail values( to_date('01/03/2019','DD/MM/YYYY'), 'Ravi',150);
insert into recharge_detail values( to_date('01/04/2019','DD/MM/YYYY'), 'Ravi',199);

insert into recharge_detail values( to_date('01/01/2019','DD/MM/YYYY'), 'Siva',100);
insert into recharge_detail values( to_date('01/02/2019','DD/MM/YYYY'), 'Siva',200);
insert into recharge_detail values( to_date('01/03/2019','DD/MM/YYYY'), 'Siva',400);
insert into recharge_detail values( to_date('01/04/2019','DD/MM/YYYY'), 'Siva',200);

commit;

-------------------------------------------------------------------------------------------------------------
select RECHARGE_DATE, CUST_NAME, RECHARGE_AMOUNT,
       decode(rank() over(partition by cust_name order by Recharge_Date desc ),1,RECHARGE_AMOUNT,2,RECHARGE_AMOUNT) last_two_recharge_amount
from recharge_detail
order by CUST_NAME,RECHARGE_DATE;
-------------------------------------------------------------------------------------------------------------
select RECHARGE_DATE, CUST_NAME, RECHARGE_AMOUNT,
       case when rank() over(partition by cust_name order by Recharge_Date desc ) in(1,2) then RECHARGE_AMOUNT end
from recharge_detail
order by CUST_NAME,RECHARGE_DATE;
-------------------------------------------------------------------------------------------------------------
SELECT
    cust_name,
    MAX(DECODE(r,1,recharge_amount) ) last_recharge,
    MAX(DECODE(r,2,recharge_amount) ) last_2nd_recharge
FROM
    (
        SELECT
            cust_name,
            recharge_amount,
            RANK() OVER(PARTITION BY cust_name ORDER BY recharge_date DESC ) r
        FROM
            recharge_detail
    )
GROUP BY
    cust_name;