Oracle PL/SQL/View/Inline View

inline view

   <source lang="sql">

SQL> SQL> SQL> CREATE TABLE departments

 2  (department_id           number(10)            not null,
 3   department_name      varchar2(50)      not null,
 4   CONSTRAINT departments_pk PRIMARY KEY (department_id)
 5  );

Table created. SQL> SQL> SQL> SQL> insert into departments ( department_id, department_name )

 2                    values( 1,             "Data Group" );

1 row created. SQL> SQL> insert into departments ( department_id, department_name )

 2                    values( 2,             "Purchasing" );

1 row created. SQL> SQL> insert into departments ( department_id, department_name )

 2                    values( 3,             "Call Center" );

1 row created. SQL> SQL> insert into departments ( department_id, department_name )

 2                    values( 4,             "Communication" );

1 row created. SQL> SQL> SQL> SQL> CREATE TABLE employees

 2  ( employee_id          number(10)      not null,
 3    last_name            varchar2(50)      not null,
 4    email                varchar2(30),
 5    hire_date            date,
 6    job_id               varchar2(30),
 7    department_id        number(10),
 8    salary               number(6),
 9    manager_id           number(6),
10    CONSTRAINT           employees_pk PRIMARY KEY (employee_id),
11    CONSTRAINT           fk_departments FOREIGN KEY (department_id) REFERENCES departments(department_id)
12  );

Table created. SQL> SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)

 2                values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)

 2                values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)

 2                 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1004,  "Kyte", "tkyte@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)

 2                 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1008, "Oracle", "wvelasq@g.ru", SYSDATE, "DBA", 20000, 4, 1006);

1 row created. SQL> SQL> select department_name, count(*),

 2           to_char( (count(*)/total_emp.cnt)*100, "90.99" ) || "%" pct
 3      from departments,
 4           employees, ( select count(*) cnt from employees ) total_emp
 5     where departments.department_id = employees.department_id
 6     group by department_name, total_emp.cnt
 7     /

DEPARTMENT_NAME COUNT(*) PCT

---

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

Communication 2 25.00% Data Group 2 25.00% Purchasing 2 25.00% Call Center 2 25.00% SQL> SQL> drop table employees cascade constraints; Table dropped. SQL> drop table departments cascade constraints; Table dropped. SQL>

</source>