SQL View

 

- Info:

- View is table-like structure that presents only selected columns and records from one or more tables.

It can present them in any way possible by SELECT statement.

- Once view is created you can use SELECT statement on it but you can't UPDATE, INSERT or DELETE records.

 

- Create From Subquery:

- New VIEW is created by using columns and records from SELECT statement.

CREATE VIEW SELECTED_PEOPLE AS

SELECT NAME, AGE

FROM PEOPLE

WHERE BORN > SYSDATE - 100

 

- If you define column names for new view, their number and type must match the number and type of columns in select.

CREATE VIEW SELECTED_PEOPLE (PERSON_NAME, PERSON_AGE) AS

SELECT NAME, AGE

FROM PEOPLE

WHERE BORN > SYSDATE - 100

 

- If you use CREATE OR REPLACE statement, view will be created even if it already exists.

CREATE OR REPLACE VIEW SELECTED_PEOPLE AS

SELECT NAME, AGE

FROM PEOPLE

WHERE BORN > SYSDATE - 100

 

- Delete:

- To delete view use DROP statement.

- When view is droped no data is lost unlike when droping a table.

DROP VIEW MYVIEW;

 

- Describe:

- To display column names and their types use DESCRIBE statement.

DESCRIBE MYVIEW;

 

- Comment Create:

- Each view and it's column can have a comment.

- Notice that you must use TABLE keyword although you are ssetting comment to a view.

COMMENT ON TABLE MYVIEW

IS 'Test view.';

COMMENT ON COLUMN MYVIEW.COLUMN1

IS 'Test column.';

 

- Comment Delete:

- View and column comments are deleted simply by setting them to an empty string.

- Notice that you must use TABLE keyword although you are ssetting comment to a view.

COMMENT ON TABLE MYVIEW

IS '';

 

COMMENT ON COLUMN MYVIEW.COLUMN1

IS '';