SQL Views

The SQL Views are virtual tables. Views are the set of SQL statements that are stored in the database with a unique name. The view contains all data (all rows)  from a table or specific data(a couple of rows) from the table. We can use all columns from a table or only the required columns while creating the views.

SQL Views

 

Why use views SQL

below are reasons for the use of the SQL views :

  1. The SQL views provide easy access to our SQL queries without having to rewrite the SQL statements repetitively which improves code reusability.
  2. The SQL Views provide restricted access to users. Security is achieved by limiting access to the database tables. only required data is shared with the help of views. Users don’t receive access to all data of their respective views.
  3. For business users, we provide alias names to columns in views, for a better understanding in terms of business.
  4. Various reporting tools such as powerBI, tableau, etc use the sql views for reporting purposes.

 

Types of SQL Views

Simple view

A simple view is created on a single table. The view created on a single table is called a simple view. It contains only one base table. the base table is the table from which we are getting data for the view. We cannot use group functions like min(), max(), count(*), etc.DML operation can be performed through a simple view. insert, update, and delete operation is possible on a simple view.

Simple view syntax

CREATE OR REPLACE VIEW viewname AS
SELECT column1,column2,column3,..
FROM tablename
WHERE condition;

Example

CREATE VIEW  productinformation AS
SELECT productname, store,city
FROM  product;
 
use below statement to see view data
select * from productinformation

 

Complex View

The view which is created from more than one table is called a complex view. We can use group functions in complex views. We cannot apply to insert, update, or delete operations complex views. It may contain a group or a distinct pseudo column.

complex view syntax

CREATE OR REPLACE VIEW viewname
 AS 
SELECT a.column1,a.column2,b.column3,.. 
FROM tablename a
inner join tablename b
on a.keycolumn=b.keycolumn

Note:-keycolumn is the primary key or foregin key column whcih is used to establish retaionship

Example

Create VIEW vw_Employee_Details
As
Select e.empid,e.empname,p.DOB,p.address
From employee e 
INNER JOIN employeedetails p
On e.empid= p. empid


select  * from vw_Employee_Details

Materialized Views
The  Materialized Views are the logical virtual tables whose results are stored physically on a database/disk. It gets updated when the underlying table updates. The view definitions and their results are stored on disk.
Materialized View is used in scenarios where frequent data access is required but the underlying table data update is not frequent basis.
for example employee data, department data, retail store data, etc.

Statements in SQL Views

Create View 

Create view statement is used to create simple or complex SQL views. we can create a view without filter conditions. Or we can use filter conditions such as with check option or using where clause.

with check option is an optional statement used with create view statement. The below example denys null product name data it fetches only those product data that have product names

example

CREATE VIEW productview AS
SELECT productname, productstore
FROM  product
where productname is not null
with check option;

 

creating views using where clause

CREATE VIEW testview as SELECT * FROM product where sales>3000;

 

Update View

update view statement used to update the views. Below are the conditions which we have to take care of, before updating the views

  1. The SELECT clause should not contain the keyword DISTINCT.
  2. The SELECT clause should not contain a summary or set functions.
  3. The SELECT clause should not contain set operators or ORDER BY clause or multiple tables in FROM clause
  4. The WHERE clause should not contain subqueries or GROUP BY or HAVING calculated columns.

example

update productview set productname='book' where productname='ebook';
Drop  views

The Drop view statement is used to drop the views.

example.

Drop view productview;