Wednesday, October 23, 2013

Psuedo Columns in Oracle


----------- Row ID ---------------
SELECT ROWID, Ename, Job
FROM Emp
WHERE Deptno = 20;


SELECT Ename, Sal, Job
FROM Emp
WHERE ROWID < 'AAAMoOAAEAAAAzXAAM';

SELECT B.Sal, Sum(A.Sal) "Cum Sal"
FROM Emp A, Emp B
WHERE A.ROWID < = B.ROWID
GROUP BY B.ROWID, B.Sal;

SELECT B.Ename, B.Sal, Sum(A.Sal) "Cum Sal"
FROM Emp A, Emp B
WHERE A.ROWID < = B.ROWID
GROUP BY B.ROWID, B.Sal, B.Ename
ORDER BY "Cum Sal";

---------------Rownum --------------------
SELECT
Ename,
LPAD('*' , ROWNUM, '*' ) SelectGraph
FROM Emp;

SELECT
Ename,
LPAD('*' , ROWNUM, '*' ) SelectGraph,
LPAD('*', Sal/500, '*') SalGraph,
LPAD('*', (SYSDATE-HireDate)/500, '*') RecGraph
FROM Emp;

SELECT ROWNUM, Ename, Sal
FROM Emp;

SELECT ROWNUM, Ename, Sal
FROM Emp
WHERE ROWNUM < 6
ORDER BY Sal DESC;

SELECT LEVEL, ROWNUM, Ename, Sal
FROM Emp
WHERE ROWNUM < 6
START WITH Ename = 'KING'
CONNECT BY PRIOR Empno = MGR
ORDER BY Sal DESC;

SELECT *
FROM (SELECT Ename, Sal, Deptno, Job
        FROM Emp
        ORDER BY Sal DESC)
WHERE ROWNUM < 6;

SELECT RN, Ename, Sal
FROM (SELECT ROWNUM RN, Ename, Sal
      FROM Emp)
WHERE RN = &GiveRn;

SELECT RN, E2.Ename, E2.Sal
FROM (SELECT ROWNUM RN, E1.*
   FROM (SELECT Ename, Sal, Deptno, Job
           FROM Emp
           ORDER BY Sal DESC) E1) E2
WHERE RN = &GiveSalNum;

SELECT Ename, Sal, E.Deptno, Dname, Grade
FROM Emp E, Dept D, Salgrade S
WHERE Sal IN (SELECT E2.Sal
        FROM (SELECT ROWNUM RN, E1.*
            FROM (SELECT Ename, Sal, Deptno, Job
                        FROM Emp
                        ORDER BY Sal DESC) E1) E2
        WHERE E2.RN = &Rn)
AND E.Deptno = D.Deptno AND
E.Sal BETWEEN S.Losal AND S.HiSal;

SELECT RN, Ename, Sal
FROM (SELECT ROWNUM RN, Ename, Sal
      FROM Emp)
WHERE MOD(RN, 2) = 0;

SELECT RN, Ename, Sal
FROM (SELECT ROWNUM RN, Ename, Sal
      FROM Emp)
WHERE MOD(RN, 2) = 1;

SELECT RN, Ename, Sal
FROM (SELECT ROWNUM RN, Ename, Sal
      FROM Emp)
WHERE RN BETWEEN &GineRn1 AND &GiveRn2;

SELECT ROWNUM, Ename, Sal
FROM Emp
GROUP BY ROWNUM, Ename, Sal
HAVING ROWNUM BETWEEN 2 AND 7;

SELECT Ename, Sal, E.Deptno, Dname, Grade
FROM Emp E, Dept D, Salgrade S
WHERE Sal IN (SELECT E2.Sal
  FROM (SELECT ROWNUM RN, E1.*
      FROM (SELECT Ename, Sal, Deptno, Job
               FROM Emp
               ORDER BY Sal DESC) E1) E2
  WHERE E2.RN BETWEEN &RN1 AND &RN2)
AND E.Deptno = D.Deptno AND
E.Sal BETWEEN S.Losal AND S.HiSal;

SELECT Ename, Sal, E.Deptno, Dname, Grade
FROM Emp E, Dept D, Salgrade S
WHERE Sal IN (SELECT E2.Sal
  FROM (SELECT ROWNUM RN, E1.*
      FROM (SELECT Ename, Sal, Deptno, Job
               FROM Emp
               ORDER BY Sal DESC) E1) E2
  WHERE E2.RN <= &RN1)
AND E.Deptno = D.Deptno AND
E.Sal BETWEEN S.Losal AND S.HiSal;

SELECT Ename, HireDate, E.Deptno, Dname
FROM Emp E, Dept D
WHERE HireDate IN (SELECT E2.HireDate
  FROM (SELECT ROWNUM RN, E1.*
      FROM (SELECT Ename, Sal, HireDate
               FROM Emp
               ORDER BY HireDate) E1) E2
  WHERE E2.RN = &RN1)
AND E.Deptno = D.Deptno;

SELECT Ename, HireDate, E.Deptno, Dname
FROM Emp E, Dept D
WHERE HireDate IN (SELECT E2.HireDate
  FROM (SELECT ROWNUM RN, E1.*
      FROM (SELECT Ename, Sal, HireDate
               FROM Emp
               ORDER BY HireDate) E1) E2
  WHERE E2.RN <= &RN1)
AND E.Deptno = D.Deptno;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect