Scribd
Upload
7 views3 pages

Understanding SQL Views and Their Usage

A view is a virtual table in SQL that displays data based on a query rather than storing rows explicitly. Views offer advantages such as joining multiple tables, hiding data complexity, and saving storage space. They can be created, updated, queried, and deleted using specific SQL statements like CREATE VIEW, CREATE OR REPLACE VIEW, and DROP VIEW.

Uploaded by

gloomy.grl.anitha001
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
7 views3 pages

Understanding SQL Views and Their Usage

A view is a virtual table in SQL that displays data based on a query rather than storing rows explicitly. Views offer advantages such as joining multiple tables, hiding data complexity, and saving storage space. They can be created, updated, queried, and deleted using specific SQL statements like CREATE VIEW, CREATE OR REPLACE VIEW, and DROP VIEW.

Uploaded by

gloomy.grl.anitha001
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

A view is a table whose rows are not explicitly stored, a view is a virtual table based on the result-set

of an SQL statement. A view can contain all rows of a table or select rows from a table. A view can be
created from one or many tables which depends on the written SQL query to create a view.

A view is generated to show the information that the end-user requests the data according to
specified needs rather than complete information of the table.

Advantages of View over database tables

 Using Views, we can join multiple tables into a single virtual table.

 Views hide data complexity.

 In the database, views take less space than tables for storing data because the database
contains only the view definition.

 Views indicate the subset of that data, which is contained in the tables of the database.

Creating Views

Database views are created using the CREATE VIEW statement. Views can be created from a single
table, multiple tables or another view.
To create a view, a user must have the appropriate system privilege according to the specific
implementation.

Syntax in Mysql

CREATE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

CREATE VIEW Students_CSE AS

SELECT Roll_no,Name

FROM Students

WHERE Branch = 'CSE';

Updating a View

A view can be updated with the CREATE OR REPLACE VIEW statement.

Syntax in Mysql

CREATE OR REPLACE VIEW view_name AS

SELECT column1, column2, ...


FROM table_name

WHERE condition;

The following SQL adds the "Mobile" column to the "Students_CSE" view:

Example:

CREATE OR REPLACE VIEW Students_CSE AS

SELECT Roll_no,Name,Mobile

FROM Students

WHERE Branch = 'CSE';

CREATE VIEW defines a view on a set of tables or views or both.


REPLACE VIEW redefines an existing view or, if the specified view does not exist,

Inserting a row in a view

We can insert a row in a View in a same way as we do in a table. We can use the INSERT INTO
statement of SQL to insert a row in a View.

Syntax in Mysql

INSERT INTO view_name(column1, column2, ...)

VALUES(value1,value2,.....);

Example:

INSERT INTO Students_CSE(Roll_no,Name,Mobile)

VALUES(521,'ram',9988776655);

Deleting a row in a view

Deleting rows from a view is also as simple as deleting rows from a table. We can use the DELETE
statement of SQL to delete rows from a view.

Syntax in Mysql

DELETE FROM view_name

WHERE condition;
Example:

DELETE FROM Students_CSE

WHERE Name="ram";

Querying a View

We can query the view as follows

Syntax in Mysql

SELECT * FROM view_name;

Example:

SELECT * FROM Students_CSE;

Dropping a View

In order to delete a view in a database, we can use the DROP VIEW statement.

Syntax in Mysql

DROP FROM view_name

Example:

DROP FROM Students_CSE;

You might also like