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.

How do I view a stored procedure in sql server?

728

---

You have modified 100 store procedures and want to replicate these changes from development to prodution, and production can have users using the Server/DB, how would you replicate without causing issues?

1901

---

what is the main function of a query parameter?

126

---

What is a dbms wizard?

767

---

What are the High-Availability solutions in SQL Server and differentiate them briefly?

896

---

---

Why do we use sql limitations? Which constraints can we use while making a database in sql?

740

---

Thanks to some maintenance being done, the sql server on a failover cluster needs to be brought down. How do you bring the sql server down?

707

---

What does ss stand for sexually?

794

---

there is a trigger defined for insert operations on a table, in an oltp system. The trigger is written to instantiate a com object and pass the newly inserted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better? : Sql server database administration

661

---

Define right outer join?

714

---

How to create hyperlink from returned sql query ?

821

---

How to create view in stored procedure sql server?

688

---

What is store procedure? How do they work? When do you use?

690

---

what are cursors? : Sql server database administration

717

---

my name is sejal I have cleared the sbi clerk exam. I am BCA graduate My favorite subject RDBMS Relational database management system. so please send me rRDBMS related interview questions

1708

---