Creating and Using Complex Views
Complex views contain subqueries that:
- Retrieve rows from multiple base tables.
- Group rows using a GROUP BY or DISTINCT clause.
- Contain a function call.
CREATE TABLE departments
(department_id number(10) not null,
department_name varchar2(50) not null
);
CREATE TABLE employees
( employee_id number(10) not null,
last_name varchar2(50) not null,
job_id varchar2(30),
department_id number(10),
salary number(6),
manager_id number(6)
);
SQL> CREATE VIEW emp_and_dept_view AS
2 SELECT p.employee_id,
3 p.last_name,
4 d.department_name
5 FROM employees p FULL OUTER JOIN departments d USING (department_id)
6 ORDER BY p.employee_id;
View created.
SQL>
The following example queries the view just created:
SELECT * FROM emp_and_dept_view;
Home »
Oracle »
Table »
Oracle »
Table »
Views:
- Creating and Using a View
- Creating and Using Simple Views
- Performing an INSERT Using a View
- A View with a CHECK OPTION Constraint
- View with a READ ONLY Constraint
- Getting Information on View Definitions with DESCRIBE command
- Getting Information on View Definitions with user_views view
- Retrieving Information on View Constraints
- Creating and Using Complex Views
- Modifying a View
- Alter the constraints on a view using ALTER VIEW
- Dropping a View
Related: