Sample Exam Questions
From the objective of OSS-DB Exam Silver
S3.1 SQL commands (View)
Sample Question
3.37
Choose two appropriate descriptions for a view.
-
A SELECT statement can be executed on a view in the same way as on a table.
-
Depending on the database version and the definition of the view, it may be possible to perform INSERT or UPDATE operations.
-
An index can be created on a column in a view.
-
If a table that serves as the basis for a view is dropped with DROP TABLE, the view becomes invalid.
-
When dropping a view with DROP VIEW, specifying the CASCADE option allows the table that the view is based on to be dropped as well.
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2024/05/02
Answer and Explanation
A view is defined using the format CREATE VIEW ... AS SELECT ... , and its primary uses include providing users with only certain columns or rows of a table, and simplifying the execution of complex queries that use aggregates or join multiple tables. The actual entity of a view is the SELECT statement stored in the database, and each time a SELECT statement is executed from a view, the SELECT statement that defines the view is also executed.
You can execute a SELECT statement from a view in exactly the same way as from a regular table. Users do not need to be aware of whether the target of the SELECT statement is a table or a view.
In earlier databases, views were read-only and could not be updated. However, starting from version 9.3, PostgreSQL has supported the functionality of updatable views. This allows for INSERT, UPDATE, and DELETE operations on views with relatively simple definitions, such as those that do not join multiple tables and do not use aggregate functions, set operations, or LIMIT/OFFSET, just like on tables.
Although views can be updated and may appear almost identical to tables, views do not contain actual data, so you cannot create an index on the columns of a view. If necessary, you can create an index on the original table from which the view is derived. On the other hand, because materialized views contain actual data, you can create an index on them.
Executing DROP TABLE on the table from which the view originates will result in an error, as the view is an object that depends on that table. If you execute DROP TABLE with the CASCADE option, the view will also be deleted. It is not possible for only the table to be deleted, leaving the view in an invalid state.
DROP VIEW also has a CASCADE option. This is used when there are objects that depend on the view (for example, another view that includes a SELECT statement from that view). In such cases, these dependent objects are deleted along with the view. However, the original table from which the view is derived is not deleted.
Therefore, the correct answers are A and B.
© EDUCO All Rights Reserved.