SQL VIEWS

SQL CREATE VIEW Statement

A SQL VIEW can be considered as virtual data table based on the query result of composition of a table in the form of an SQL SELECT or predefined SQL statement.

A SQL VIEW will look like a real database table with all or selected rows and fields. The columns and records in a view are data from one or more real data tables in the database.

You can add any valid SQL SELECT statement like SQL functions, SQL WHERE, and SQL JOINS statements to a view and present the data as if the data were coming from one single data table or multiple data table.

SQL CREATE VIEW Syntax

The basic syntax is:

CREATE VIEW view_name AS
SELECT column_name_list(s)
FROM table_name
[WHERE condition]

Note: A view always display latest information! The database system engine recreates the information, using the view's SQL statement, every time a user queries a view.

SQL CREATE VIEW Example

Following is an example table named "Books" look like this:

Here, we have to create a view from BOOKS table. This view would be used to have bookname, authorname and bookprice from BOOKS table:

We use the following sql statement to create view.

CREATE VIEW My_Book_View AS
SELECT BookName, AuthorName, BookPrice
FROM Books;

We can query the view above as follows:

SELECT * FROM My_Book_View;

After executing above statement, the result will look like this:

Another example, The following statement will fetch all records with domain "Database" from "Books" table.

CREATE VIEW Database_Books AS
SELECT * FROM Books
WHERE Domain = 'Database';

We can query the view above as follows:

SELECT * FROM Database_Books;

After executing above statement, the result will look like this:

SQL Updating a View

We can update a view by using following sql statement:

SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name AS
SELECT column_name_list(s)
FROM table_name
[WHERE condition]

Now we want to apply a condition which bookprice less than 200 to the "My_Book_View" view. We will update the view with the following SQL:

CREATE VIEW My_Book_View AS
SELECT BookName, AuthorName, BookPrice
FROM Books
WHERE BookPrice < 200;

After executing above statement, the result will look like this:

SQL Droping a View

We can delete or remove a view using SQL DROP VIEW statement.

SQL DROP VIEW Syntax

The basic syntax is:

DROP VIEW viewname;

SQL DROP VIEW Example

Now we want to delete the "My_Book_View" view:

DROP VIEW My_Book_View;