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>