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:
|
1 2 3 4 |
CREATE [OR REPLACE] VIEW <view_Name> AS SELECT <Columns> FROM <Tables> [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
|
1 2 3 4 5 6 7 8 |
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 |
|
1 2 3 4 |
CREATE VIEW Emp_view AS SELECT E_Name, E_Sal FROM Employee WHERE E_Sal > 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 :
|
1 |
SELECT * FROM Emp_View; |
|
1 2 3 4 5 6 7 |
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
|
1 2 3 4 5 |
C_ID | C_Name -----+------- 1 | FYBCA 2 | SYBCA 3 | TYBCA |
Table #2 : Student
|
1 2 3 4 5 6 7 8 |
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.
|
1 2 3 4 5 |
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 :
|
1 |
SELECT * FROM Show_Students; |
|
1 2 3 4 5 6 7 8 |
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
|
1 2 3 |
UPDATE Emp_View1 SET E_Sal = 50000 WHERE E_Name = 'Mayur'; |
Example #4: Delete data From View
|
1 2 |
DELETE FROM Emp_view1 WHERE E_Sal = 80000; |
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:
|
1 2 3 4 |
CREATE OR REPLACE VIEW View_Name AS SELECT <columns> FROM <table> WHERE <conditions>; |
Example:
|
1 2 3 4 |
CREATE or REPLACE VIEW Emp_View AS SELECT E_Name, E_Sal, E_Branch FROM Employee WHERE E_Sal > 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.
|
1 |
SELECT * FROM Emp_View; |
|
1 2 3 4 |
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:
|
1 |
DROP VIEW <View_Name>; |
|
1 |
DROP VIEW IF EXISTS <View_Name>; |
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:
|
1 |
DROP VIEW Employee_View; |
Above DROP VIEW statement will drop the VIEW called Employee_View;
Advantages of Views:
- 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.
- Views provides only limited access to the tables, because views uses only few columns from tables(sensitive columns are not exposed to users).
- View is a virtual table where data is not store permanently so it needs very less space as they are not storing actual data.
- We can create a view by joining more tables and another view so it provides a consolidated view.
Disadvantages of Views:
- If the view is created from other view then querying data from such views will be slow in performance.
- If views are created from multiple tables and if you are changing the structure of tables, you need to change the view as well.
- If data in the original table gets updated , so obviously data in the view too get updated.
