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)