SQL for QA: Tutorial#11 – Views

In simple words, View is a ‘Subset’ of  one or multiple tables. A View can be a Virtual Table which contains all OR some of the rows and columns of the given table.

To create a View one can use one or more tables. View can represent joined tables too. So a View will not store data physically, it is just a logical table which gives a materialized view

Why Testers / QA need View?

If data is present in database tables and then why there is a necessity of views? So the reason is, by using views we can restricts particular users and gives them access only on a particular data as views displays only selected data.

So in short we can say that Views are used for security purpose in the database which provides abstraction on the database tables by hiding the complexity of database tables to the end users.

We can Create, Update and Drop views.

Let’s see following examples to see how it works in PostgresSQL:

Create VIEWS

Syntax : In PostgreSQL, the syntax for the CREATE VIEW statement is:

OR REPLACE : It is Optional. If we do not write this clause and if the VIEW is already exists, the CREATE VIEW statement will return an error.

view_name : The name of the VIEW

SELECT:  write down the column names from which you want to fetch the data

FROM: table names from which you want to fetch the data

WHERE:  It is Optional. It provide some condition, so the query will extract only those records from table who will full fill the specified condition and only those records to be included in the VIEW.

Example:

Consider following Employee table 

Above CREATE VIEW example would create a virtual table based on the result set of the SELECT statement.

To see the result of a view write down this query : 

Example #2: Create a view on multiple tables

Suppose we have following tables

Table #1: Class 

Table #2 : Student

Now we will see how two (or more) tables can be used and join them to make a view by using CREATE VIEW statement.

To see the result of a view write down this query :

You can consider View as a table and Update or Delete data from View the way you do it for normal Table.

Example #3: Update data from View

Example #4: Delete data From View

 

Update VIEWS

By using the CREATE OR REPLACE VIEW Statement we can modify the definition of a VIEW in PostgreSQL.

Syntax:

The syntax for the Update VIEW in PostgreSQL is:

Example:

In above example we are updating definition of Emp_View by just adding REPLACE keyword in the query.

To check the result of a updated view write down following query.

NOTE:  For updating view if we are adding columns to the view at the end of the column list then it will work, it would give error if you are trying to add new columns in between or start of the columns. (For this purpose it’s better to DROP the view and create new view using CREATE VIEW statement)

Drop VIEWS

Once a VIEW is created, we can drop it with the DROP VIEW statement.

Syntax: The syntax for the DROP VIEW statement in PostgreSQL is:

IF EXISTS: this keyword is Optional, but If this keyword is not written in the query and the VIEW does not exist, the DROP VIEW statement will return an error.

Example:

Above DROP VIEW statement will drop the VIEW called  Employee_View;

Advantages of Views:

  1. The main advantage of view is to provide security to your database, by providing abstraction on database tables view hides complexity of tables from end users.
  2. Views provides only limited access to the tables, because views uses only few columns from tables(sensitive columns are not exposed to users).
  3. View is a virtual table where data is not store permanently so it needs very less space as they are not storing actual data.
  4. We can create a view by joining more tables and another view so it provides a consolidated view.

Disadvantages of Views:

  1. If the view is created from other view then querying data from such views will be slow in performance.
  2. If views are created from multiple tables and if you are changing the structure of tables, you need to change the view as well.
  3. If data in the original table gets updated , so obviously data in the view too get updated.

Add a Comment

Your email address will not be published. Required fields are marked *