Answer Posted / yuvaraja
SQL: VIEWS
------------------------------------------------------------
--------------------
A view is, in essence, a virtual table. It does not
physically exist. Rather, it is created by a query joining
one or more tables.
Creating a VIEW
The syntax for creating a VIEW is:
CREATE VIEW view_name AS
SELECT columns
FROM table
WHERE predicates;
For example:
CREATE VIEW sup_orders AS
SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_name = 'IBM';
This would create a virtual table based on the result set
of the select statement. You can now query the view as
follows:
SELECT *
FROM sup_orders;
Updating a VIEW
You can update a VIEW without dropping it by using the
following syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT columns
FROM table
WHERE predicates;
For example:
CREATE or REPLACE VIEW sup_orders AS
SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_name = 'Microsoft';
Dropping a VIEW
The syntax for dropping a VIEW is:
DROP VIEW view_name;
For example:
DROP VIEW sup_orders;
Frequently Asked Questions
------------------------------------------------------------
--------------------
Question: Can you update the data in a view?
Answer: A view is created by joining one or more tables.
When you update record(s) in a view, it updates the records
in the underlying tables that make up the view.
So, yes, you can update the data in a view providing you
have the proper privileges to the underlying tables.
------------------------------------------------------------
--------------------
Question: Does the view exist if the table is dropped from
the database?
Answer: Yes, in Oracle, the view continues to exist even
after one of the tables (that the view is based on) is
dropped from the database. However, if you try to query the
view after the table has been dropped, you will receive a
message indicating that the view has errors.
If you recreate the table (that you had dropped), the view
will again be fine.
Is This Answer Correct ? | 2 Yes | 0 No |
Post New Answer View All Answers
How do I install sql server?
How many types of local tables are there in sql server?
How to define output parameters in stored procedures?
How do I run sql server 2014?
What is candidate key with example?
How do you delete a trigger?
what are constraints? Explain different types of constraints? : Sql server database administration
What is pivot and unpivot?
List out some of the requirements to set up a sql server failover cluster?
on line cluster can we make if yes tell me the procedure
What is the difference between DataRow.Delete() and DataRow.Remove()?
How do I perform an unattended install of sql server 2000?
In which format does an image save in SQL Server database ?
What is user-defined function? Explain its types i.e. Scalar and inline table value user-defined function?
What is the command used to recompile the stored procedure at run time?