15 April 2017

How To Create View Better Way In Sql

A view is basically a logical view of one or more base tables. A view can be a read only or updatable. A view generally be created to avoid any unwanted changes into actual base tables. Mainly view is created for security reasons.
  • A view can be constructed with another view so it is called as nested view.
  • You can create or replace an existing view
  • A view can be created without having base tables. This is possible with FORCE option.
Learn SQL and handle databases confidently
Learn SQL and handle databases confidently
Read only views: The standard syntax for view is as follows:

CREATE OR REPLACE VIEW INVOICE_SUMMARY AS
SELECT VENDOR_NAME
COUNT(*) AS INVOICE_COUNT,
SUM(INVOICE_TOTAL) AS INVOICE_TOTAL_SUM
FROM VENDORS
JOIN INVOICES ON VENDORS.VENDOR_ID*INVOICES.VENDOR_ID
GROUP BY VENDOR_NAME;

How to use FORCE command:

CREATE FORCE VIEW PRODUCTS_LIST AS
SELECT PRODUCT_DESCRIPTION, PRODUCT_PRICE
FROM PRODUCTS;

Updatable Views: A view can also be updatable if a view follows certain rules. Read more to understand all about what is updatable view.  
  • A view when it is created for update purpose, you can give INSERT,UPDATE and DELETE
  • A read only view should contain WITH READ ONLY CLAUSE.
While updating a view, it is possible to update only one base table at a time. When you created a view from more than one table, then it is not possible to update two tables at a time.

What is ALTER view
This is possible with CREATE OR REPLACE statement. Just you can replace with existing statement.

Drop view
DROP VIEW VENDORS_SW


Last pointsA view with CHECK OPTION is surely help you to satisfy certain condition. Usually this option will be used in updatable views.

No comments:

Post a Comment

Thanks for your message. We will get back you.

© 2010-2017 Biganalytics.me. All rights reserved.. Powered by Blogger.

Total Pageviews

All material, files, logos and trademarks within this site are properties of their respective organizations.