DataBase Views

When kids want to play a specific game, say cricket, and they have a lot of toys, badminton, a ball, a tennis racket, etc., in their storage box, should they get all the toys with them to play cricket? Absolutely not. They will only get the bat and ball that are required to play cricket, and the rest of the toys that are not required to play that game will be left in the box. This is exactly what happens in giant companies like Google, Microsoft, etc.

The database of the company is huge, and everyone does not need all the information from the database that is not related to their job profiles. For example, a person in the advertisement department of a bank requires only the customer's name, mobile number, email address, and permanent address. Such a table does not exist in the original database, and it should be displayed to the person as a virtual table. Such a table is called a view. Hence, the part of the database which is relevant to their job profiles is provided to their systems.

This is possible using database views, which take out the required information from the base database and display it to others.

Creating a View:

CREATE VIEW v1 AS SELECT name, rollno FROM Students; //This is the view created for the Students database table which displays only the name and roll no of the students.

Displaying a View:

SELECT * FROM v1; //It displays the view v1 created before.

The temporary tables that are generated by views are not stored, and whenever the view is used in any query, its name is replaced by the view definition. Thus, whenever the query is evaluated, the view definition is recomputed to generate a new table.

There are two types of database views:

Materialized view: Some databases allow the tables generated by views to be stored, and in this case, the database needs to ensure that if actual tables are modified, then it has to modify the view also. Such views are known as materialized views.

Updatable view: In most cases, it is not required to update a view, and if the views need to be updated, then it has to follow some criteria:

a) If an insert operation is to be performed, then the primary key and all the not null columns must be part of the view.

b) The view definition should not include the aggregate functions, groups and distinct, subqueries, unions, intersect, and except.

Deleting a View:

DROP VIEW v1; //v1 is the view that was created before.

Happy Learning!!!! :)