Oracle 12c:SQL - Chapter 13
WITH CHECK OPTION
The ____ constraint ensures that any DML operations performed on the view (e.g., adding rows, changing data) will not prevent the row from being accessed by the view because it no longer meets the condition in the WHERE clause.
inline
The subquery used to create a(n) ____ view can contain an ORDER BY clause.
materialized
The type of view that actually replicates data is called a(n) ____ view.
all of the above
Which statement is true about the view created with the following command? CREATE VIEW inventory AS SELECT isbn, title, retail price FROM books WITH READ ONLY;
all of the above
A user can perform a DML operation (add, modify, delete) on a simple view if it does not violate which type of existing constraint on the underlying base table?
complex
A view based on the contents of one table that uses an expression for one of the columns is considered a(n) ____ view.
view
A(n) ____ stores a query and is used to access data in the underlying tables.
non key-preserved
A(n) ____ table is a table that does not contain the primary key that a view uses to uniquely identify each record being displayed by the view.
key-preserved
A(n) ____ table is the table that contains the primary key the view uses to uniquely identify each record being displayed by the view.
ROWNUM
DML operations are not allowed on a view that includes the pseudo column ____.
DISTINCT
DML operations are not allowed on a view that is created with the ____ keyword.
re-create the view without the option
If a view was created with the WITH CHECK OPTION constraint, to remove the constraint you will need to ____.
re-create the view without the option
If a view was created with the WITH READ ONLY constraint, to remove the constraint you will need to ____.
FORCE
If you want to create a view based upon a table or tables that do not yet exist, or are currently unavailable (e.g.,off-line), what keyword can you use to avoid receiving an error message?
another view does not exist with the same name
The OR REPLACE clause is not required if ____.
NOFORCE
What is the default mode for the CREATE VIEW command?
It is a temporary data source that exists only while a command is being executed.
What is the definition of an inline view?
all of the above
What is the procedure for assigning new names for the columns that are displayed by a view?
INSERT INTO prices (title, cost, retail) VALUES ('A NEW BOOK', 49.99, 69.99);
Which SQL statement can be executed based upon the view created from the following command, assuming no constraints exist on the underlying table? CREATE VIEW prices AS SELECT isbn, title, cost, retail, retail-cost profit FROM books;
DROP VIEW
Which command will delete a view?
WITH READ ONLY
Which constraint ensures that the data in a view cannot be changed?
inline view
Which of the following describes a subquery used in a FROM clause to create a ""temporary"" table that can be referenced by the SELECT and WHERE clauses of the outer query?
complex view
Which of the following describes a type of view that is based on a subquery that retrieves or derives data from one or more tables, and may also contain functions or grouped data?
simple view
Which of the following describes a type of view that is based upon a subquery that only references one table and does not include any group functions, expressions, or GROUP BY clauses?
WITH READ ONLY
Which of the following options will prevent any DML operations from being performed on the underlying table of a view?
"All DML operations can be performed on complex views, just like simple views."
Which of the following statements about complex views is incorrect?
DML operations cannot be performed on a non key-preserved table.
Which of the following statements about performing DML operations on complex views is correct?
Values cannot be inserted into columns that are based on arithmetic expressions.
Which of the following statements about performing DML operations on complex views is correct?
Views are database objects that actually store data.
Which of the following statements about views is incorrect?
Views can be modified by using the ALTER VIEW...MODIFY command.
Which of the following statements is incorrect?
simple
Which of the following types of views cannot contain grouped data?
simple view
Which of the following types of views cannot include a GROUP BY clause?
simple view
Which of the following types of views cannot include a group function?
simple view
Which of the following types of views cannot include an arithmetic expression?
DML operations can only be performed on the columns belonging to the BOOKS table.
Which statement about the view created from the following SQL command is correct, assuming ISBN from the BOOKS table is the primary key used by the view? CREATE OR REPLACE VIEW prices AS SELECT isbn, title, cost, retail, retail-cost profit, name FROM books NATURAL JOIN publisher;
No DML operations can be performed through the view.
Which statement about the view created from the following SQL statement is correct? CREATE VIEW balancedue AS SELECT customer#, order#, SUM(quantity*retail) amtdue FROM customers NATURAL JOIN orders NATURAL JOIN orderitems NATURAL JOIN books GROUP BY customer#, order#;
The retail price of a book can be changed.
Which statement about the view created from the following command is correct, assuming no constraints exist on the underlying table? CREATE VIEW prices AS SELECT isbn, title, cost, retail, retail-cost profit FROM books;
DML operations are not allowed on the data displayed by the view.
Which statement is not true about the view created with the following command? CREATE VIEW inventory AS SELECT isbn, title, retail price FROM books;
A database object named INVENTORY may already exist.
Which statement is true about the view created with the following command? CREATE OR REPLACE VIEW inventory AS SELECT isbn, title, retail price FROM books;
all of the above
Which statement is true about the view created with the following command? CREATE VIEW OR REPLACE outstanding AS SELECT customer#, order#, orderdate, shipdate FROM orders WHERE shipdate IS NULL WITH READ ONLY;
all of the above
Which statement is true about the view created with the following command? CREATE VIEW OR REPLACE outstanding AS SELECT customer#, order#, orderdate, shipdate FROM orders WHERE shipdate IS NULL;
The shipping date of an order cannot be changed through the view.
Which statement is true about the view created with the following command? CREATE VIEW outstanding AS SELECT customer#, order#, orderdate, shipdate FROM orders WHERE shipdate IS NULL WITH CHECK OPTION;
simple
Which type of view is created by the following command? CREATE VIEW OR REPLACE outstanding AS SELECT customer#, order#, orderdate, shipdate FROM orders WHERE shipdate IS NULL;
complex
Which type of view is created from the following SQL command? CREATE OR REPLACE VIEW prices AS SELECT isbn, title, cost, retail, retail-cost profit, name FROM books NATURAL JOIN publisher;
complex
Which type of view is created from the following SQL statement? CREATE VIEW balancedue AS SELECT customer#, order#, SUM(quantity*retail) amtdue FROM customers NATURAL JOIN orders NATURAL JOIN orderitems NATURAL JOIN books GROUP BY customer#, order#;
complex
Which type of view is created from the following command? CREATE VIEW prices AS SELECT isbn, title, cost, retail, retail-cost profit FROM books;
simple
Which type of view is created with the following command? CREATE VIEW OR REPLACE outstanding AS SELECT customer#, order#, orderdate, shipdate FROM orders WHERE shipdate IS NULL WITH READ ONLY;
simple
Which type of view is created with the following command? CREATE VIEW inventory AS SELECT isbn, title, retail price FROM books WITH READ ONLY;
simple
Which type of view is created with the following command? CREATE VIEW outstanding AS SELECT customer#, order#, orderdate, shipdate FROM orders WHERE shipdate IS NULL WITH CHECK OPTION;
ORDER BY clauses
____ aren?t allowed in the CREATE VIEW command.
