SQL Views

SQL View

An SQL View is a specific representation of data from one or more tables. The tables referred in the views are known as Base tables. Creating a view does not take any storage space as only the query is stored in the data dictionary and the actual data is not stored anywhere.

The maximum number of columns that can be defined in a SQL View are 1000 as in tables.

Benefits / Advantages of SQL Views

The reasons for using views (benefits / advantages of views) in applications can be many like;

• Reducing complexity.
• Improving security.
• Renaming the table columns.

Creating SQL View

Use Create View statement to create a view:

SQL> create view emp_info as
2 select first_name ' ' last_name Name, employee_id
3 from employees where department_id = 20;
View created.

SQL> desc emp_info;

Name	Null?	Type
NAME		VARCHAR2(46)
Employee_ID	NOT NULL	NUMBER(6)

SQL> select * from emp_info;

Name	Employee_ID
Michael Hartstein	201
Pat Fay	202

You can also define altogether a new column in a view, as column commission below:

SQL> create view emp_info(Name, employee_id, commission) as
2 select first_name ' ' last_name ,employee_id, salary * .01
3 from employees where department_id = 20;
View created.

SQL> desc emp_info;

Name	Null?	Type
NAME		VARCHAR2(46)
Employee_ID	NOT NULL	NUMBER(6)
COMMISSION	NOT NULL	NUMBER

SQL> select * from emp_info;

Name	Employee_ID	COMMISSION
Michael Hartstein	201	130
Pat Fay	202	60

You can also create Read-Only views. On the read-only views no DML operations can be performed.

Create view emp_info(Name, employee_id, commission) as
select first_name ' ' last_name ,employee_id, salary * .01
from employees where department_id = 20
With Read only;