CASE, rank and some sub queries examples

EX-1
select ename, sal, deptno,
case
when deptno=10 then
case when sal>3000 then 'Grade1_10'
else 'Grade2_10' end
else
case
when deptno=20 then
case when sal>2500 then 'Grade1_20'
else 'Grade2_20' end
else
case
when deptno=30 then
case when sal>2000 then 'Grade1_30'
else 'Grade2_30' end
end
end
end emp_deot_grade
from emp
_________________
EX-2
====
select
case 1
when 1 then 'one'
when 2 then 'two'
else 'three' end
from dual
===
EX-3
=====
select
case
when 1=1 then 'one'
when 2=2 then 'two'
else 'Three' end
from dual
__________________

SELECT *
    FROM(SELECT Ename, Deptno, Sal,
      DENSE_RANK()
      OVER(PARTITION BY DeptNo
      ORDER BY Sal DESC)  "TOP Sal"
     FROM Emp)

-----------------------------------

SELECT *
   FROM(SELECT Ename, Deptno, Sal,
      DENSE_RANK()
      OVER(PARTITION BY DeptNo
      ORDER BY Sal DESC)  "TOP Sal"
      FROM Emp)
    WHERE "TOP Sal" IN(3)
   ORDER BY DeptNo, Sal DESC
________________

SELECT
    E.EmpCount,
    D.DeptCount,
    S.GradeCnt,
    E.EmpCount + D.DeptCount + S.GradeCnt TotalRecCnt
    FROM
        (SELECT COUNT(*) EmpCount
         FROM Emp) E,
        (SELECT COUNT(*) DeptCount
        FROM Dept) D,
       (SELECT COUNT(*) GradeCnt
        FROM SalGrade) S;

output:
---------
  EMPCOUNT  DEPTCOUNT   GRADECNT TOTALRECCNT                                   
---------- ---------- ---------- -----------                                   
        14          4          5          23