Try 10 focused Oracle 1Z0-071 questions on Manage Views, with explanations, then continue with IT Mastery.
Open the matching IT Mastery practice page for timed mocks, topic drills, progress tracking, explanations, and full practice.
Try Oracle 1Z0-071 on Web View full Oracle 1Z0-071 practice page
| Field | Detail |
|---|---|
| Exam route | Oracle 1Z0-071 |
| Topic area | Manage Views |
| Blueprint weight | 4% |
| Page purpose | Focused sample questions before returning to mixed practice |
Use this page to isolate Manage Views for Oracle 1Z0-071. Work through the 10 questions first, then review the explanations and return to mixed practice in IT Mastery.
| Pass | What to do | What to record |
|---|---|---|
| First attempt | Answer without checking the explanation first. | The fact, rule, calculation, or judgment point that controlled your answer. |
| Review | Read the explanation even when you were correct. | Why the best answer is stronger than the closest distractor. |
| Repair | Repeat only missed or uncertain items after a short break. | The pattern behind misses, not the answer letter. |
| Transfer | Return to mixed practice once the topic feels stable. | Whether the same skill holds up when the topic is no longer obvious. |
Blueprint context: 4% of the practice outline. A focused topic score can overstate readiness if you recognize the pattern too quickly, so use it as repair work before timed mixed sets.
These questions are original IT Mastery practice items aligned to this topic area. They are designed for self-assessment and are not official exam questions.
Topic: Manage Views
Examine the view definition:
CREATE OR REPLACE VIEW dept20_v AS
SELECT employee_id emp_id, last_name, salary
FROM employees
WHERE department_id = 20
AND salary >= 5000;
Which statement correctly describes what dept20_v exposes?
Options:
A. Employees whose department is 20 or whose salary is at least 5,000
B. Only EMP_ID, LAST_NAME, and SALARY for employees in department 20 with salary at least 5,000
C. All columns from EMPLOYEES for employees in department 20 with salary at least 5,000
D. Only employees in department 20, with salaries below 5,000 shown as NULL
Best answer: B
Explanation: dept20_v contains exactly three exposed columns: EMP_ID, LAST_NAME, and SALARY. It also restricts rows to those where department_id = 20 and salary >= 5000 because both conditions appear in the view’s WHERE clause.
A view behaves like a stored query. The columns visible through the view come only from the SELECT list, so this view exposes employee_id under the alias EMP_ID, plus LAST_NAME and SALARY. The rows visible through the view are limited by the WHERE clause, which requires both department_id = 20 and salary >= 5000.
So the view does not expose every column from EMPLOYEES, and it does not include rows that fail either filter condition. The closest trap is confusing selected columns with all base-table columns.
SELECT list.NULL.AND, so both conditions must be true.Topic: Manage Views
A developer creates this view:
CREATE OR REPLACE VIEW open_orders_v AS
SELECT order_id,
customer_id,
order_total + NVL(shipping_fee, 0) AS total_due
FROM orders
WHERE shipped_date IS NULL
AND cancel_date IS NULL;
Which statement correctly describes what the view exposes?
Options:
A. Only ORDER_ID, CUSTOMER_ID, and TOTAL_DUE for unshipped, uncanceled orders.
B. Only rows with a non-NULL SHIPPING_FEE.
C. All ORDERS columns for unshipped, uncanceled orders.
D. The selected columns plus SHIPPED_DATE and CANCEL_DATE.
Best answer: A
Explanation: This view exposes only the three columns named in its SELECT list: ORDER_ID, CUSTOMER_ID, and TOTAL_DUE. Its WHERE clause restricts rows to orders that are neither shipped nor canceled, and NVL keeps NULL shipping fees from excluding rows.
In Oracle, a view exposes the columns produced by its SELECT list, not every column from the base table. Here, the view returns three columns: ORDER_ID, CUSTOMER_ID, and the expression alias TOTAL_DUE.
The WHERE clause controls which rows appear in the view: only rows where SHIPPED_DATE is NULL and CANCEL_DATE is NULL. Those filter columns do not become visible through the view unless they are also selected. The NVL(shipping_fee, 0) call affects only the computed value of TOTAL_DUE; it substitutes 0 when SHIPPING_FEE is NULL, so such rows can still appear.
The key takeaway is that a view shows selected columns for rows that satisfy its query conditions.
WHERE clause are not included unless they also appear in the SELECT list.NVL changes the expression result, not whether the row qualifies for the view.Topic: Manage Views
A reporting team needs a reusable object that shows only employee_id, last_name, and department_id from EMPLOYEES. The base table must remain unchanged.
Exhibit:
DESC employees
Name Null? Type
--------------- ----- ------------
EMPLOYEE_ID NUMBER
LAST_NAME VARCHAR2(25)
EMAIL VARCHAR2(25)
HIRE_DATE DATE
JOB_ID VARCHAR2(10)
SALARY NUMBER
DEPARTMENT_ID NUMBER
Which SQL statement best meets the requirement?
Options:
A. CREATE TABLE emp_rpt_vw AS SELECT employee_id, last_name, department_id FROM employees;
B. CREATE VIEW emp_rpt_vw AS SELECT employee_id, last_name, department_id FROM employees;
C. ALTER VIEW emp_rpt_vw AS SELECT employee_id, last_name, department_id FROM employees;
D. CREATE VIEW emp_rpt_vw (employee_id, last_name, department_id) AS SELECT * FROM employees;
Best answer: B
Explanation: A view is the correct object when you need to present only selected columns from an existing table without changing or copying the table itself. Selecting just the required columns in the CREATE VIEW ... AS SELECT ... statement satisfies the reporting requirement directly.
The core concept is that a view is a stored SELECT statement that can restrict which columns are exposed from a base table. Here, the requirement is to provide only employee_id, last_name, and department_id, so the view definition must explicitly select only those columns.
CREATE VIEW emp_rpt_vw AS SELECT employee_id, last_name, department_id FROM employees;
This leaves EMPLOYEES unchanged and gives report users a simpler projection of the table. Creating a table would copy data instead of defining a reusable logical object, and redefining a view is not done with ALTER VIEW ... AS SELECT .... The closest distractor is the option using SELECT *, because it would not limit the exposed columns to the reporting requirement.
CREATE TABLE ... AS SELECT creates a new table, not a view.SELECT * does not restrict exposure to only the required columns.ALTER VIEW does not use an AS SELECT clause to define the view query.Topic: Manage Views
The EMPLOYEES table contains EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID, SALARY, and HIRE_DATE. A reporting tool needs a reusable object named EMP_REPORT_V that always shows current EMPLOYEES data and exposes only EMPLOYEE_ID, FULL_NAME, and DEPARTMENT_ID. Which SQL statement is the best solution?
Options:
A. CREATE TABLE emp_report_v AS SELECT employee_id, first_name || ' ' || last_name AS full_name, department_id FROM employees;
B. CREATE VIEW emp_report_v AS SELECT employee_id, first_name || ' ' || last_name AS full_name, department_id FROM employees;
C. CREATE VIEW emp_report_v (employee_id, full_name, department_id) AS SELECT * FROM employees;
D. CREATE SYNONYM emp_report_v FOR employees;
Best answer: B
Explanation: A view is the correct schema object when you need a reusable query result that shows current base-table data without storing a separate copy. Limiting the view’s select list to the required columns exposes only those columns, and the expression alias provides FULL_NAME.
The core concept is that a view presents data from a query, not a stored copy of rows. For this requirement, the object must both reflect current EMPLOYEES data and hide unneeded columns such as SALARY and HIRE_DATE. A CREATE VIEW statement with only EMPLOYEE_ID, a derived FULL_NAME, and DEPARTMENT_ID in the select list meets that exactly.
A synonym would only create another name for EMPLOYEES; it would not restrict the visible columns. A table created from a query stores data separately, so it would not automatically stay synchronized with later table changes. Using SELECT * with only three view column names is also invalid here because the number of view column names must match the number of expressions returned by the query.
The key takeaway is to use a view when you need a live, column-restricted reporting interface over a base table.
CREATE TABLE AS SELECT creates a separate table snapshot, not a live reporting layer.SELECT * misuse fails because the view defines three column names while the query returns all six EMPLOYEES columns.Topic: Manage Views
An existing view named dept10_pay_v may already exist. You must define it so that it returns only employees in department 10, with columns named employee_id, emp_name, and annual_salary, where annual_salary is salary * 12. Which statement best meets the requirement?
Options:
A. CREATE OR REPLACE VIEW dept10_pay_v (employee_id, emp_name, annual_salary) AS SELECT employee_id, last_name, salary * 12 FROM employees;
B. CREATE OR REPLACE VIEW dept10_pay_v AS SELECT employee_id, last_name, salary * 12 FROM employees WHERE department_id = 10;
C. CREATE OR REPLACE VIEW dept10_pay_v (employee_id, emp_name, annual_salary) AS SELECT employee_id, last_name, salary * 12 FROM employees WHERE department_id = 10;
D. CREATE VIEW dept10_pay_v (employee_id, emp_name, annual_salary) AS SELECT employee_id, last_name, salary * 12 FROM employees WHERE department_id = 10;
Best answer: C
Explanation: The correct statement uses CREATE OR REPLACE VIEW and a view column list to satisfy all stated requirements. It replaces any existing definition, restricts the rows correctly, and gives the output columns the required names.
For this task, the key syntax is CREATE OR REPLACE VIEW. OR REPLACE lets Oracle redefine an existing view without requiring you to drop it first. The WHERE department_id = 10 clause is needed to return only the required rows, and the view column list (employee_id, emp_name, annual_salary) gives the view the exact column names requested.
A column list is especially useful here because salary * 12 is an expression, so the derived column must be named for the view definition to match the requirement cleanly. A statement that omits OR REPLACE, omits the row filter, or fails to provide the required column names does not fully meet the task.
The safest takeaway is to match replacement behavior, row restriction, and exposed column names in the same CREATE OR REPLACE VIEW statement.
OR REPLACE can create the view, but it does not satisfy the requirement to replace an existing one.Topic: Manage Views
The view dept10_v already exists. You must change its query so it also returns email, while keeping the same view name and without dropping the view first.
Which statement correctly applies the Oracle SQL rule?
Options:
A. CREATE OR REPLACE VIEW dept10_v AS SELECT employee_id, last_name, email, salary FROM employees WHERE department_id = 10
B. ALTER VIEW dept10_v AS SELECT employee_id, last_name, email, salary FROM employees WHERE department_id = 10
C. RENAME dept10_v TO old_dept10_v; CREATE VIEW dept10_v AS SELECT employee_id, last_name, email, salary FROM employees WHERE department_id = 10
D. CREATE VIEW dept10_v AS SELECT employee_id, last_name, email, salary FROM employees WHERE department_id = 10
Best answer: A
Explanation: Oracle uses CREATE OR REPLACE VIEW to redefine an existing view without removing it first. This is the direct object-management rule for updating a view definition while keeping the same name.
The core rule is that an existing view can be redefined with CREATE OR REPLACE VIEW. Oracle replaces the stored query for that view name in a single SQL statement, so you do not need to issue DROP VIEW first. This is the standard way to modify the columns or query logic returned by a view.
ALTER VIEW is not used to supply a new SELECT definition. A plain CREATE VIEW attempts to create a new view and fails if the name already exists. Renaming the old view and then creating a new one changes object management unnecessarily and does not follow the stated requirement to redefine the existing view directly.
The key takeaway is: to change an existing view definition in place, use CREATE OR REPLACE VIEW.
ALTER VIEW misuse fails because Oracle does not use ALTER VIEW ... AS SELECT ... to replace a view query.CREATE VIEW fails because the view name already exists.Topic: Manage Views
An existing view named DEPT_SAL_V must be redefined without being dropped first. The new view must return one row per department with the department ID and the average salary from EMPLOYEES.
Which statement meets this requirement?
Options:
A. CREATE OR REPLACE VIEW dept_sal_v AS SELECT department_id, AVG(salary) FROM employees;
B. CREATE VIEW OR REPLACE dept_sal_v (department_id, avg_salary) AS SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;
C. CREATE OR REPLACE VIEW dept_sal_v (department_id, avg_salary) AS SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;
D. CREATE OR REPLACE dept_sal_v VIEW (department_id, avg_salary) AS SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;
Best answer: C
Explanation: To redefine an existing view without dropping it, Oracle uses CREATE OR REPLACE VIEW. Because the query returns department_id with AVG(salary), it must also include GROUP BY department_id to produce one row per department.
The key rule is Oracle view DDL syntax: CREATE OR REPLACE VIEW view_name AS subquery. The OR REPLACE clause lets you redefine the existing view in one statement instead of dropping and recreating it first. In this requirement, the subquery must also be valid aggregate SQL, because it returns a nonaggregated column (department_id) together with AVG(salary). That means GROUP BY department_id is required.
Column aliases can be supplied in the view definition, so naming the projected columns as department_id and avg_salary is valid. The closest distractors misuse the position of OR REPLACE or omit the required grouping clause. The correct choice satisfies both the Oracle DDL syntax rule and the aggregate query rule.
CREATE VIEW OR REPLACE; OR REPLACE must come right after CREATE.VIEW fails because CREATE OR REPLACE dept_sal_v VIEW is not valid Oracle syntax.department_id appears with AVG(salary) without a GROUP BY department_id clause.Topic: Manage Views
Consider this repeated query:
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;
Analysts run this query often. They should see only these three columns and should not query the base tables directly. Which action best meets the requirement?
Options:
A. Create an index on EMPLOYEES(department_id), then grant SELECT on the base tables.
B. Create a view that selects only the three required columns, then grant SELECT on the view.
C. Create a view with SELECT * from the join, then grant SELECT on that view.
D. Create a synonym for EMPLOYEES, then grant SELECT on the base tables.
Best answer: B
Explanation: A regular view is the best fit when the same join is reused and users should see only a subset of columns. Defining the query once in a view and granting access to that view simplifies reporting and limits visible columns without exposing the base tables directly.
A view is a stored SELECT statement that can present data from one or more tables. Here, the join between EMPLOYEES and DEPARTMENTS is reused, and the analysts need only employee_id, last_name, and department_name. Creating a view with exactly those columns centralizes the repeated query logic and limits what users can see. You can then grant SELECT on the view instead of granting access to the base tables.
This is the standard Oracle SQL choice when the goal is both query simplification and column restriction. A synonym only provides another object name, an index addresses performance rather than presentation or access scope, and a view built with SELECT * would expose more columns than required.
SELECT * in a view simplifies the join but violates the requirement to expose only the three specified columns.Topic: Manage Views
A developer creates this view:
CREATE VIEW dept50_v AS
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE department_id = 50;
Later, the developer runs:
UPDATE dept50_v
SET salary = salary + 500
WHERE employee_id = 124;
COMMIT;
Assume employee_id is unique and the UPDATE succeeds. Which Oracle SQL rule applies?
Options:
A. The UPDATE fails because any view with a WHERE clause is read-only.
B. All rows visible in the view are updated.
C. The EMPLOYEES row for employee 124 is updated.
D. Only the view result changes; the base table is unchanged.
Best answer: C
Explanation: dept50_v is a simple view based on one table and contains no grouping, set operators, or other features that make it non-updatable. A successful UPDATE through such a view modifies the corresponding row in the underlying table, and COMMIT makes that change permanent.
Oracle allows DML through a simple view when the view is based on a single table and does not include constructs that prevent direct row mapping. Here, the view selects columns directly from employees, so each row in the view corresponds to one row in the base table.
The statement updates only the row where employee_id = 124, and because the UPDATE succeeds, Oracle changes that employee’s salary in employees. The view does not store separate data; it shows data from the base table. COMMIT then saves the table change.
A WHERE clause in the view definition does not by itself make the view read-only.
WHERE is allowed: the option claiming the view is read-only fails because a simple single-table view can still be updatable even with a filter.UPDATE has its own WHERE employee_id = 124 condition.Topic: Manage Views
A team repeatedly runs the same SELECT against EMPLOYEES and wants users to see only employee ID, name, and department data, not salary. Which Oracle database object is best suited for this requirement?
Options:
A. A view
B. A sequence
C. An index
D. A synonym
Best answer: A
Explanation: A view is the correct choice because it encapsulates a query and can expose only the columns you want users to access. This makes repeated query use simpler while also restricting visible data such as salary columns.
In Oracle SQL, a view is a stored query that behaves like a virtual table. It is commonly used when the same query is needed repeatedly or when users should see only a subset of columns or rows from a base table. In this case, the requirement is both to simplify reuse of the same SELECT and to hide salary data, which matches the purpose of a view.
A synonym only provides an alternate name for an object, a sequence generates numeric values, and an index improves query access speed. None of those objects is designed to present a filtered column list from a query.
The key idea is that a view is for query abstraction and controlled data presentation.
Use the Oracle 1Z0-071 Practice Test page for the full IT Mastery route, mixed-topic practice, timed mock exams, explanations, and web/mobile app access.
Try Oracle 1Z0-071 on Web View Oracle 1Z0-071 Practice Test
Read the Oracle 1Z0-071 Cheat Sheet on Tech Exam Lexicon, then return to IT Mastery for timed practice.