SQL Ch 12 - Creating Views

4 Requirements for creating updatable Views

1. The select list can't include a DISTINCT clause 2. The select list can't include aggregate functions 3. The SELECT statement can't include a GROUP BY or HAVING 4. The view can't include the UNION operator

Read-Only View

A view that IS NOT updatable

Updatable View

A view that can be used in an INSERT, UPDATE, or DELETE statement to update the data in the base table

Nested View

A view that is based on another view rather than a table

View

a SELECT statement thats stored in the database as a database object

CREATE VIEW example

CREATE VIEW vendors_min AS SELECT vendor_name, vendor_state FROM vendors

DROP VIEW

Delete a view from the database

DELETE FROM

Deletes data from a database, table, OR view

4 benefits of using Views

Design Independence - can update without affecting view Data Security - restricting access Simplified Queries - Uses simple SELECT queries to retrieve Updatability - can be used to update, insert, delete data from a base table

A view _______ store data

Does NOT, it refers back to its base tables

When you update data through a view, you can only update data ________

In a SINGLY base table at a time, even if the view refers to more than 1 table

INSERT INTO

Inserts data into a new database, table, OR view

Because a View is stored an an object in a database, _____

It can only be used by anyone who has appropriate privileges

If you name the columns in a CREATE VIEW clause, you must _______

Name ALL of the columns

Views are stored as a database _______

Objects

WITH CHECK OPTION

Prevents an update if it causes the row to be excluded from the view

Base Tables

Tables that are listed in the FROM cause

OR REPLACE

Used to automatically drop an existing view that has the same name as the view you are creating Format: 'CREATE OR REPLACE VIEW'

CREATE VIEW

Used to create views

A View is a ________ that consists only of the rows and columns specified in its CREATE VIEW statement

Virtual Table