RSS Feed

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;

NameNull?Type
NAME
VARCHAR2(46)
Employee_IDNOT NULLNUMBER(6)


SQL> select * from emp_info;

NameEmployee_ID
Michael Hartstein201
Pat Fay202


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;

NameNull?Type
NAME
VARCHAR2(46)
Employee_IDNOT NULLNUMBER(6)
COMMISSIONNOT NULLNUMBER


SQL> select * from emp_info;

NameEmployee_IDCOMMISSION
Michael Hartstein201130
Pat Fay20260

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;

No comments:

Post a Comment