Misc Queries

Find the Duplicate Salaries
---------------------------------
select a.empno,a.ename,a.sal,a.deptno,b.empno dup_empno,b.ename dup_ename,b.sal dup_sal,b.deptno dup_deptno
from emp a, emp b
where a.sal=b.sal
and   a.empno<>b.empno
and   a.deptno=b.deptno
and   a.rowid>b.rowid;

EMPNO	ENAME	SAL	DEPTNO	DUP_EMPNO	DUP_ENAME	DUP_SAL	DUP_DEPTNO
7654	MARTIN	1,250.00	30	7521	WARD	1,250.00	30
7902	FORD	3,000.00	20	7788	SCOTT	3,000.00	20
7789	SCOTT	5,000.00	10	7839	KING	5,000.00	10

Nth Value

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

select level, max(sal) from emp

connect by prior sal > sal

group by level

order by 1 desc;

Free Space

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

SELECT   s.tablespace_name TABLESPACE,

         ROUND (((s.ublocks * 8) * 1024) / (1024 * 1024), 0) \"USEDMB\",

         ROUND ((((f.fblocks * 8) * 1024) / (1024 * 1024)), 0) \"FREEMB\",

         ublocks + fblocks \"TOTAL BLOCKS\",

         ROUND (((((ublocks + fblocks) * 8) * 1024) / (1024 * 1024)),

                0

               ) \"TOTAL MB\"

    FROM (SELECT   tablespace_name, SUM (blocks) ublocks

              FROM dba_segments

          GROUP BY tablespace_name) s,

         (SELECT   tablespace_name, SUM (blocks) fblocks

              FROM dba_free_space

          GROUP BY tablespace_name) f

   WHERE s.tablespace_name = f.tablespace_name

ORDER BY TABLESPACE;

User Privileges and Roles

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

select

  lpad('  ', 3*level) || granted_role "Users and roles privileges"

from

  (

  /* THE USERS */

    select

      null     grantee,

      username granted_role

    from

      dba_users

    where

      username in (select username from dba_users)

  /* THE ROLES TO ROLES RELATIONS */

  union

    select

      grantee,

      granted_role

    from

      dba_role_privs

  /* THE ROLES TO PRIVILEGE RELATIONS */

  union

    select

      grantee,

      privilege

    from

      dba_sys_privs

       )

start with grantee is null

connect by grantee = prior granted_role;

Generate dates
with
    start_and_end ( start_date, end_date ) as (
      select TO_DATE ('2003/04/01', 'yyyy/mm/dd'), TO_DATE ('2003/04/07', 'yyyy/mm/dd') from dual
    )
-- end of input data; actual solution begins below this line
select start_date + level - 1 as dt
from   start_and_end
connect by level <= end_date - start_date + 1;

DT
----------
01/04/2003
02/04/2003
03/04/2003
04/04/2003
05/04/2003
06/04/2003
07/04/2003