SQL | Views

In SQL, a view is a virtual table based on the result-set of an SQL statement. A view also has rows and columns as they are in a real table in the database. We can create a view by selecting fields from one or more tables present in the database.

• Views in SQL are kind of virtual tables.
• A View can either have all the rows of a table or specific rows based on certain condition.
• The changes made in a View are not reflected back to the actual table in the database.
• SQL functions like WHERE, and JOIN can be applied to a view and present the data as if the data were coming from one single table.
• A view of table always shows up-to-date data. Everytime a uswer queries a view, the table is  recreated.

StudentDetails

StudentMarks

CREATING VIEWS

We can create View using CREATE VIEW statement. A View can be created from a single table or multiple tables. Syntax:

CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;

view_name: Name for the View
table_name: Name of the table
condition: Condition to select rows

Examples:

• Creating View from a single table: In this example we will create a View named DetailsView from the table StudentDetails. Query:

  CREATE VIEW DetailsView AS
  SELECT NAME, ADDRESS
  FROM StudentDetails
  WHERE S_ID < 5;
  

  To see the data in the View, we can query the view in the same manner as we query a table.

  SELECT * FROM DetailsView;
  

  Output:

 

• Creating View from multiple table: In this example we will create a View named MarksView from two tables StudentDetails and StudentMarks. To create a View from multiple tables we can simply include multiple tables in the SELECT statemen.Query:

  CREATE VIEW MarksView AS
  SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS
  FROM StudentDetails, StudentMarks
  WHERE StudentDetails.NAME = StudentMarks.NAME;
  

  To display data of View MarksView:

  SELECT * FROM MarksView;
  

  Output:

DELETING VIEWS

We have learned about creating a View, but what if a created View is not needed any more? Obviously we will want to delete it. SQL allows us to delete an existing View. We can delete or drop a View using the DROP statement. Syntax:

DROP VIEW view_name;

view_name: Name of the View which we want to delete.

For example, if we want to delete the View MarksView, we can do this as:

DROP VIEW MarksView;

UPDATING VIEWS

We can use the CREATE OR REPLACE VIEW statement to add or remove fields from a view. Syntax:

CREATE OR REPLACE VIEW view_name AS
SELECT column1,coulmn2,..
FROM table_name
WHERE condition;

For example, if we want to update the view MarksView and add the field AGE to this View from StudentMarks Table, we can do this as:

CREATE OR REPLACE VIEW MarksView AS
SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS, StudentMarks.AGE
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME;

If we fetch all the data from MarksView now as:

SELECT * FROM MarksView;

Output: