An important aspect of database systems is creation of external schemas for different users and their needs. These are also known as external views for users. One way of doing this is by creation of database views with SQL. In this post we will discuss SQL Views, how to create an SQL view and how to use it.
Definition- SQL View
A database view is a snapshot of data from a table or multiple tables identified by a name. A view is always associated with a SELECT query. So, a view can be understood as a logical table that gets its data from underlying SELECT statement. Since the SQL view always gets its data from a query so, it is called a stored query as well.
A view is created with a CREATE command and accessed with a SELECT command like you do with a table.
Syntax of SQL View
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view-name[ (column names for the view)] AS (SELECT statement)[WITH [CHECK OPTION][READ ONLY][CONSTRAINT]]
- OR REPLACE is optional. It will work when the view already exists. Addition of OR REPLACE will ensure that if the view is not existing it is created else the existing view definition is replaced with the new definition.
- FORCE option creates a view even if the base tables are not created yet. Later when the tables are created and filled with date, the predefined view can be used to access data.
- NOFORCE option is default option. It inhibits creation of a view if the base table is not available.
- view-name is the name of the view you want to create.
- Column names are the names of the columns you wish to use in the view if you do not want to use the names of the columns of the underlying table(s). Make sure that the number of column names specified after name of the view is same as the number of column specified in the SELECT statement of the view.
- SELECT statement is the query that fills the rows in a view.
- WITH READ ONLY option creates a read-only view that cannot be updated, rows cannot be deleted from it and no new rows can be inserted in it.
- Using WITH CHECK OPTION you can allow the rows that can be updated through the view. You can specify the constraints on the inserted or updated values by using CONSTRAINT clause along with WITH CHECK OPTION.
Note: When a view is created, no data is actually stored in it unlike a table. The view is a name given to a query or you can also say that a view is a stored query that can be executed whenever required. The rows returned by the underlying query are temporarily stored. Whenever a SELECT command is used with the view name, the query is executed and the virtual table is filled with rows.
Example of a SQL View
Create a view that allows anyone to see the date of birth of any employee in the organization
create table empMaster( empid int, Firstname varchar(20), Last_name varchar(20), dob date, managerid int, deptt varchar(20), desig varchar(20) );
Data in table
CREATE or REPLACE VIEW emp_birthdays (First_name,Last_Name, Date_of_Birth) AS SELECT firstname, last_name, dob FROM empmaster;
Here in the above example you can see that the names of columns in the view emp_birthdays are different than the column names of the underlying table. The SELECT query is created with an ORDER BY clause so that the view gets the sorted data.
Output of the View
SELECT * from emp_birthdays;
Example without READ ONLY option
If the view is created without READ ONLY option and the underlying table doesn’t have any constraints, the INSERT, DELETE or UPDATE commands can be executed normally. The columns which are not included in the view definition will remain null after the INSERT command.
insert into emp_birthdays values ('Beth','Ching','09-Apr-1972')
Output after insert in view
Example with READ ONLY option
If the view is created with READ ONLY option the INSERT, DELETE or UPDATE commands cannot be executed.
CREATE or REPLACE VIEW emp_birthdays (First_name,Last_Name, Date_of_Birth) AS SELECT firstname, last_name, dob FROM empmaster WITH READ ONLY;
insert into emp_birthdays values ('Harry','Potter','02-Feb-1998')
Error message on INSERT command
Underlying Table with CHECK constraint
The same table used in previous examples is recreated with a check constraint. The deptt column has CHECK constraint. When an INSERT statement is executed violating the CHECK constraint of the underlying table of the view, an error is reported. The violation error is generated for the base table of the view.
CREATE TABLE empMaster( empid int, Firstname varchar(20), Last_name varchar(20), dob date, managerid int, deptt varchar(20) CHECK( deptt IN ('Sales','HR')), desig varchar(20));
CREATE OR REPLACE VIEW emp_deptt (First_name,Last_Name, Department) AS SELECT firstname, last_name, deptt from empmaster;
insert into emp_deptt values('Neena','Banks','Production')