Oracle in-line views

Oracle in-line views --------------------
The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM clause,
 just as if the query was a table name.
A common use for in-line views in Oracle SQL is to simplify complex queries by removing join operations
 and condensing several separate queries into a single query.

SQl> SELECT * FROM ( SELECT deptno, count(*) emp_count
                         FROM emp  GROUP BY deptno ) emp,dept
 WHERE dept.deptno = emp.deptno;

The Below query display the employees who earn the highest salary in each department.

SQl> SELECT a.last_name, a.salary, a.department_id, b.maxsal FROM employees a,
       ( SELECT department_id, max(salary) maxsal
         FROM employees
         GROUP BY department_id ) b
     WHERE a.department_id = b.department_id
     AND a.salary = b.maxsal;

Display the top 5 salaries from emp table

SQL> SELECT rownum ,emp.ename,emp.sal
     FROM ( SELECT ename,sal
         FROM scott.emp
         order by sal desc ) emp
         where rownum<=5

EXAMPLE
--------
This is a great report for display the actual amount of free space,used tablespace,total space within an Oracle tablespace.

select
   fs.tablespace_name                          "Tablespace",
   (df.totalspace - fs.freespace)              "Used MB",
   fs.freespace                                "Free MB",
   df.totalspace                               "Total MB",
   round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) TotalSpace
   from
      dba_data_files
   group by
      tablespace_name
   ) df,
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) FreeSpace
   from
      dba_free_space
   group by
      tablespace_name
   ) fs
where
   df.tablespace_name = fs.tablespace_name;


Ur's
AmarAlam