Home »
SQL
SQL Views
SQL | Views: In this tutorial, we are going to learn about the views in SQL, creating views, updating views, etc.
Submitted by Abhishek Goel, on April 05, 2020
SQL | Views
Views in SQL are virtual tables. A view also has rows and columns as they're during a real table within the database. We will create a view by selecting fields from one or more tables present within the database. A View can either have all the rows of a table or specific rows supported under certain conditions. A view is nothing quite a SQL statement that's stored within the database with an associated name. A view is a composition of a table within the sort of a predefined SQL query. All rows of a table or select rows from a table can be contained inside the view. A view is often created from one or many tables which depend on the written SQL query to make a view.
Views, which are a kind of virtual tables allow users to try to the subsequent:
- Structure data during a way that users or classes of users find natural or intuitive.
- Restrict access to the info in such a way that a user can see and (sometimes) modify exactly what they have and no more.
- Summarize data from various tables which may be wont to generate reports.
Here we will discuss creating, deleting and updating views.
Creating the view
Views can be created in SQL by using the CREATE VIEW command. This order gives the name to the view and determines the rundown of credits and tuples to be incorporated utilizing a subquery.
The syntax to create a view is given here,
CREATE VIEW <view_name>
As <subquery>;
For example, the command to create a view containing details of books which belong to text-book and Language Book can be specified as:
CREATE VIEW Book_1
As SELECT *
FROM BOOK
WHERE Category IN ('Textbook','LanguageBook');
Output
This command creates the view, named Book_1, having details of books satisfying the condition specified in the WHERE clause. The view created like this consists of all the attributes of Book relation also.
For example, consider the command given below:
CREATE VIEW Book_2(B_code,B_title,B_category,B_price)
As SELECT ISBN,Book_Tiltle,Category,Price
FROM Book
WHERE Category IN ('Textbook','LanguageBook');
Output
This command creates a view Book_2, which consists of the attributes, ispn, book-name, categori, and price from the relation Book with new names, namely, b_code, b_title, b_category, and b_price respectively. Now queries can be performed on these views as they are performed on other relations.
Consider the example given below:
SELECT *
FROM Book_1;
Output
SELECT *
FROM Book_2
WHERE price>300;
Output
SELECT b_title, b_category
FROM Book_2
WHERE price BETWEEN 200 and 350;
Output
Views can contain more than one relation. The views that depend on more than one relation are known as complex views. These types of views are inefficient as they are time-consuming to execute, especially if multiple queries are involved in the view definition. Since their content is not physically stored, they are executed whenever their reference is done inside the program.
Updating Views
A view can be updated based upon several conditions as mentioned below,
- Keyword DISTINCT should not be present in the SELECT statement.
- The summary function should not be there in the SELECT statement.
- Set function and Set Operations should not be present in the SELECT statement.
- ORDER BY clause should not be present in the SELECT statement.
- Multiple tables should not be contained in the FROM statement.
- Subqueries should not be present inside the WHERE clause.
- A query written in SQL must not contain GROUP BY or HAVING.
- Columns that are calculated may not get updated.
If the view satisfies the above-mentioned conditions then the user or programmer is able to update the view. The code written below can be used for serving the purpose.
UPDATE <Name of VIEW>
SET <parameter to be updated>=<value>
WHERE <condition>;
Dropping View
The view needs to be dropped (i.e. Deleted permanently) when not in further use. The syntax for doing so is,
DROP VIEW <view_name>;