SQL VIEWS

It provides limited access to specific columns in a database table rather than access entire table and thus gives a flexibility of controlling user access to table.

It also can be formed by combining multiple tables. It also has rows and columns to store data like tables

It is useful for generating reports from multiple tables

CREATE VIEWS

Syntax:

CREATE VIEW view_name AS
            SELECT column1, column2..
             FROM Table_Name
             WHERE [Condition];

Lets take this TRAINEES table

id	first_name	last_name	birth_date	gender
1	Peter	Parker	1998-03-04	M
2	Harry	Potter	2001-08-30	M
3	Peter	Pan	2004-09-19	M
4	Alice	Kingsleigh	1999-07-02	F
5	John	Connor	2002-01-15	M

CREATE VIEW TRAINEES_VIEW AS
SELECT first_name, birth_date
FROM  TRAINEES;

it will create the view TRAINEES_VIEW, now to view it run : SELECT * from TRAINEES_VIEW

Output:

first_name	birth_date
Peter	1998-03-04
Harry	2001-08-30
Peter	2004-09-19
Alice	1999-07-02
John	2002-01-15

With CHECK Option

This is used while view creation to add constraint to the specific fields

Example:

CREATE VIEW TRAINEES_VIEW AS SELECT first_name, birth_date FROM TRAINEES WHERE birth_date IS NOT NULL;

UPDATE in VIEWS:

UPDATE TRAINEES_VIEW SET birth_date = 2000-01-01 WHERE first_name ='Peter';

DELETE in VIEWS

DELETE FROM VIEWS WHERE first_name='John';

DROP in VIEWS

DROP VIEW TRAINEES_VIEW;

Learn more : SQL Tutorial , SQL Interview Questions