IS 420 Chapter 11 How to create views
What makes a view updateable?
-SELECT clause contains only fieldnames, no functions or calculations -cannot contain the ORDER BY, DISTINCT, or GROUP BY clauses, group functions, or set operators -search condition cannot contain a nested query •Views are used like tables for selecting, inserting, updating and deleting data (only updatable views can be modified)
Database View: Example
CREATE VIEW vendors_min AS SELECT vendor_name, vendor_state, vendor_phone FROM vendors;
Database Views: Syntax
CREATE VIEW view_name AS source_query; -Or CREATE OR REPLACE VIEW view_name AS source_query;
How to remove views?
DROP VIEW command syntax: DROP VIEW view_name;
Using views example
SELECT * FROM vendors_min WHERE vendor_state = 'CA' ORDER BY vendor_name
Update views
UPDATE vendors_min SET vendor_phone = '(800)5551234' WHERE vendor_name = 'Register of...'
What are the benefits of views?
•Design independence - If the base tables change, only the view definition needs to change, and not the applications that use the view •Data security -Hide columns with sensitive data (SSN) by creating views without them. Then use only the views •Simplified queries -A complicated query becomes simplified if it uses a view (because views can generate partial results) •Updatability -With certain restrictions views can be used to update, insert, and delete data from base tables
Database Views
•sort of pseudo tables that are created to present a particular "display" of database content •Views do not actually store data. They are used to display data in existing tables (referred as base tables)