SQL View

A view is a logical subset of a real table, which selects some rows or some columns from the actual table.Views are pseudo-tables.It does not have its own physical memory except for materialized views.A view can even represent joined tables.Views represent data that is selected from one or more tables using the SELECT statement.
There are two types of views :-

Simple Views:-Views created using only one table without using a group by function are known as simple views.This type of views can be modified.
Complex Views:-Views created using more than one table with using a group by function are known as simple views.This type of views can not be modified.


Syntax View:

The basic syntax is as follows :-
CREATE VIEW : It defines a new view.
OR REPLACE: This will drop the existing view with the same name.
view_name: The name has to be unique in the database.
WITH CHECK OPTION: If the view is updateable then the changes are limited to the WHERE clause.


CREATE OR REPLACE VIEW  view_name
AS 
subquery;
WITH CHECK OPTION
 

Example :

Consider the COMPANY table to be created-


select * from COMPANY;


id | name     | age | address    | salary
----+-------+-----+------------+--------
  1 | Prateek  |  22 | Mysore     |  20000
  2 | Vaibhav  |  20 | Udaipur    |  15528
  3 | Yash     |  24 | Mumbai     |  22000
  4 | Jugal    |  23 | Delhi      |  25000
  5 | Prasun   |  19 | Kolkata    |  30000

-----------------------------------------

\\Now, following is an example to create a view from COMPANY table.

CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM  COMPANY;

------------------------------------------

\\SQL statement will produce the following result −

 SELECT * FROM COMPANY_VIEW;

 id | name     | age  
----+-------+-----+---
  1 | Prateek  |  22 
  2 | Vaibhav  |  20 
  3 | Yash     |  24 
  4 | Jugal    |  23 
  5 | Prasun   |  19 



 

Dropping Views

To drop a view, use the DROP VIEW statement with the view_name.

\\Syntax

DROP VIEW view_name;

-----------------------------

\\Example

DROP VIEW COMPANY_VIEW;
 





Visit :


Discussion


* You must be logged in to add comment.