SQL for QA: Tutorial#11 – Views

In simple words, View is a xSubsetx 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:

CREATE [OR REPLACE] VIEW xview_Namex AS
SELECT xColumnsx
FROM xTablesx
[WHERE Conditions];

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 

E_id | E_name   |  E_dept        |  E_branch    |  E_sal
-----+----------+----------------+--------------+--------
1      Anita       IT                Pune         10000
2      Nita        IT                Mumbai       80000
3      Anil        HR                Banglore     89000
4      Mayur       Database          Banglore     450000
5      Janhavi     Maintainance      Pune         70000
6      Ajay        HR                Pune         40000

CREATE VIEW Emp_view AS
SELECT E_Name, E_Sal
FROM Employee
WHERE E_Sal x 30000;

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 : 

SELECT * FROM Emp_View;

E_name       E_sal
---------+------------
Nita         80000
Anil         89000
Mayur        450000
Janhavi      70000
Ajay         40000

Example #2:  Create a view on multiple tables

Suppose we have following tables

Table #1: Class 

C_ID | C_Name
-----+-------
 1   | FYBCA
 2   | SYBCA
 3   | TYBCA

Table #2 : Student

Rollno |  S_Name | Percentage | Class_ID
-------+---------+------------+------
 101   | Nita    |    67      |    1
 102   | Gita    |    57      |    2
 103   | Sangita |    87      |    1
 104   | Amol    |    77      |    3
 105   | Aakash  |    81      |    3
 106   | Sahil   |    55      |    2

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

CREATE OR REPLACE VIEW Show_Students
AS SELECT C_Name,S_Name
FROM Class,Student
WHERE Class.C_ID=Student.Class_Id
ORDER BY C_Name;

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

SELECT * FROM Show_Students;

 C_Name | S_Name
--------+-------
 FYBCA  | Nita
 FYBCA  | Sangita
 SYBCA  | Gita
 SYBCA  | Sahil
 TYBCA  | Amol
 TYBCA  | Aakash

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

UPDATE Emp_View1
SET E_Sal = 50000
WHERE E_Name = 'Mayur';

Example #4: Delete data From View

DELETE FROM Emp_view1
WHERE E_Sal = 80000;

x

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:

CREATE OR REPLACE VIEW View_Name AS
SELECT xcolumnsx
FROM xtablex
WHERE xconditionsx;

Example:

CREATE or REPLACE VIEW Emp_View  AS
SELECT E_Name, E_Sal, E_Branch
FROM Employee
WHERE E_Sal x 30000 AND E_Branch='Pune';

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.

SELECT * FROM Emp_View;

E_Name       E_Sal      E_Branch
-----------+----------+------------
Janhavi      70000      Pune
Ajay         40000      Pune

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:

DROP VIEW xView_Namex;

DROP VIEW  IF EXISTS  xView_Namex;

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:

DROP VIEW Employee_View;

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.