SQL VIEWS
A SQL VIEW is a virtual database table.
You can learn about how to create, update, and delete a sql view by this lessons.
Related Links
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:
BookId | BookName | BookPrice | AuthorName | Domain |
---|---|---|---|---|
101 | Sql Complete Reference | 250.5 | Suresh Babu | Database |
102 | Sql Commands | 120 | Haris Karthik | Database |
103 | Pl / Sql Quick Notes | 150 | Siva Kumar | Programming |
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:
BookName | AuthorName | BookPrice |
---|---|---|
Sql Complete Reference | Suresh Babu | 250.5 |
Sql Commands | Haris Karthik | 120 |
Pl / Sql Quick Notes | Siva Kumar | 150 |
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:
BookId | BookName | BookPrice | AuthorName | Domain |
---|---|---|---|---|
101 | Sql Complete Reference | 250.5 | Suresh Babu | Database |
102 | Sql Commands | 120 | Haris Karthik | Database |
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:
BookName | AuthorName | BookPrice |
---|---|---|
Sql Commands | Haris Karthik | 120 |
Pl / Sql Quick Notes | Siva Kumar | 150 |
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;
Related Links