Everything You Need To Know About SQL Views

What is View ?

Views in SQL are a form of digital tables. A view additionally has rows and columns as they're in a actual desk withinside the database. We can create a view with the aid of using choosing fields from one or greater tables gift withinside the database. A View can both have all of the rows of a desk or particular rows primarily based totally on positive condition.

Here we will take two tables.

1. Student 

2. Marks

Creating Views : -

* We can create View the use of CREATE VIEW statement. A View may be comprised of a unmarried desk or a couple of 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

Creating View from a single table:-

* We will create a View named DetailsView from the table Student.

CREATE VIEW DetailsView AS SELECT Name, Address FROM Student WHERE  Student_id < 5;

We can see the record,

SELECT * FROM DetailsView;

Creating View from multiple tables:

* We will create a View named MarksView from two tables Student and Marks.

CREATE VIEW MarksView AS
SELECT Student.NAME, Student.ADDRESS, Marks.MARKS
FROM Student, Marks
WHERE Student.NAME = Marks.NAME;

Data of View MarksView:-

SELECT * FROM MarksView;

DELETING VIEWS

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 :-

There are certain conditions demanded to be satisfied to modernize aview.However, also we won't be allowed to modernize the view, If any one of these conditions isn't met.

1. The SELECT statement which is used to produce the view shouldn't include GROUP in clause or ORDER in clause.

2. SELECT statement should not have the DISTINCT keyword.

3.  View should have all NOT NULL values.

4. View should not be created using nested queries or complex queries.

5. View should be created from a single table.

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;