1Z0-006 Database Objects and Constraints 2 — Questions and Answers
Question 1: What is a view in Oracle Database?
- A physical copy of a table stored separately
- A named query stored in the database that behaves as a virtual table (Correct answer)
- A backup of a table captured at a point in time
- A type of index that speeds up queries
Correct answer: A named query stored in the database that behaves as a virtual table
A view is a stored SELECT query that acts as a virtual table; it does not store data itself but derives results from underlying tables each time it is queried.
Question 2: Which pseudo-column retrieves the NEXT value from an Oracle sequence and increments the counter?
- sequence_name.CURRVAL
- sequence_name.NEXTVAL (Correct answer)
- sequence_name.INCREMENT
- sequence_name.NEXT
Correct answer: sequence_name.NEXTVAL
The NEXTVAL pseudo-column retrieves the next available value from a sequence and advances the sequence counter each time it is accessed.
Question 3: What does the CURRVAL pseudo-column of an Oracle sequence return?
- The next value the sequence will generate
- The maximum value the sequence can reach
- The most recently generated value from the sequence in the current session (Correct answer)
- The starting value of the sequence
Correct answer: The most recently generated value from the sequence in the current session
CURRVAL returns the most recently generated sequence value within the current session; NEXTVAL must be called at least once before CURRVAL can be referenced.
Question 4: Which clause in a CREATE VIEW statement ensures that INSERT and UPDATE operations through the view only affect rows the view can still select?
- WITH READ ONLY
- WITH CHECK OPTION (Correct answer)
- WITH CONSTRAINT
- WITH FILTER OPTION
Correct answer: WITH CHECK OPTION
WITH CHECK OPTION ensures that any row inserted or updated through the view must satisfy the view's WHERE condition, preventing data from being modified outside the view's scope.
Question 5: Which CREATE VIEW clause prevents any INSERT, UPDATE, or DELETE operations through the view?
- WITH CONSTRAINT
- WITH NO DML
- WITH READ ONLY (Correct answer)
- WITH CHECK OPTION
Correct answer: WITH READ ONLY
The WITH READ ONLY clause creates a view that blocks all DML operations, making the view strictly a read-only window into the underlying data.
Question 6: Which CREATE SEQUENCE option defines the lowest value a descending sequence can reach?
- START WITH
- MINVALUE (Correct answer)
- INCREMENT BY
- CACHE
Correct answer: MINVALUE
The MINVALUE option sets the floor for a sequence; a descending sequence stops (or cycles) once it reaches this minimum value.
Question 7: Which of the following is a key advantage of using views in Oracle Database?
- Views always pre-compute results for faster retrieval
- Views store redundant copies of data for backup purposes
- Views can simplify complex queries and restrict user access to specific columns or rows (Correct answer)
- Views automatically create indexes on the underlying tables
Correct answer: Views can simplify complex queries and restrict user access to specific columns or rows
Views simplify complex queries by encapsulating them as reusable named objects and can limit user exposure to only specific columns or rows of underlying tables, improving both usability and security.
1Z0-006 Database Objects and Constraints 2
What is a view in Oracle Database?