Test Your Knowledge of Oracle Database Views with This Quiz

Progress

True / False

In ORACLE, a view is a virtual table that is based on the result of a SELECT query.

True False

A view is a virtual table in ORACLE that is defined by a SELECT query. It does not store data itself but retrieves data from the underlying tables.

True / False

In ORACLE, views can be used to simplify complex queries.

True False

Views can encapsulate complex queries, making it easier for users to access and work with the data without having to understand the underlying complexity.

True / False

In ORACLE, you can insert, update, and delete data through a view.

True False

In many cases, you can perform DML operations (INSERT, UPDATE, DELETE) on views, although there are restrictions, especially if the view includes joins, group by, or distinct clauses.

True / False

In ORACLE, a view can be created using a SELECT statement that includes a JOIN.

True False

Views can be created using SELECT statements that include joins, allowing you to combine data from multiple tables in a single view.

True / False

In ORACLE, views can have indexes created on them.

True False

Views themselves cannot have indexes. However, you can create indexed views by indexing the underlying tables.

True / False

In ORACLE, views can be used to enforce security by restricting access to specific columns or rows.

True False

Views can provide a way to restrict access to sensitive data by exposing only specific columns or rows to the users.

True / False

In ORACLE, a materialized view stores the result of the query physically.

True False

A materialized view is a type of view that stores the result of the query physically, which can improve performance for complex queries at the cost of storage.

True / False

In ORACLE, changes to the underlying tables are automatically reflected in a materialized view without any additional steps.

True False

Changes to the underlying tables are not automatically reflected in a materialized view. The materialized view needs to be refreshed to reflect the changes.

True / False

In ORACLE, views can include a WHERE clause to filter data.

True False

Views can include a WHERE clause in their SELECT statement to filter the data that is presented to the user.

Q10

True / False

In ORACLE, a view can be created with the WITH CHECK OPTION clause to ensure that all DML operations on the view conform to the view's WHERE clause.

True False

The WITH CHECK OPTION clause ensures that any DML operations performed through the view adhere to the constraints defined by the view's WHERE clause, maintaining data integrity.

Q11

Multiple Choice

Identify the SQL statements that correctly create a simple view based on a single table in Oracle.

SQL Code

CREATE TABLE employees (
 employee_id NUMBER PRIMARY KEY,
 employee_name VARCHAR2(100),
 department_id NUMBER
);

CREATE VIEW emp_view AS SELECT employee_name, department_id FROM employees; CREATE VIEW emp_view (employee_name, department_id) AS SELECT employee_name, department_id FROM employees; CREATE VIEW emp_view AS SELECT employee_name, department_id FROM employees WHERE department_id = 10;CREATE VIEW emp_view (employee_name, department_id) AS SELECT * FROM employees;

A simple view is created by selecting specific columns from a single table. The correct SQL statements will use the CREATE VIEW command with a SELECT statement specifying the desired columns.

Q12

Multiple Choice

Select the SQL statements that correctly create an updatable view in Oracle.

CREATE VIEW emp_updatable_view AS SELECT employee_name, department_id FROM employees; CREATE VIEW emp_updatable_view AS SELECT employee_name, department_id FROM employees WHERE department_id = 10 WITH CHECK OPTION; CREATE VIEW emp_updatable_view AS SELECT employee_name, department_id FROM employees WHERE employee_id > 100;CREATE VIEW emp_updatable_view AS SELECT employee_name FROM employees WHERE department_id = 10;

An updatable view allows DML operations (INSERT, UPDATE, DELETE) on the underlying table. The correct SQL statements will ensure that the view includes only columns from a single base table without any aggregate functions or group by clauses.

Q13

Multiple Choice

Which of the following SQL statements correctly create this complex view?

CREATE VIEW emp_dept_view AS SELECT e.employee_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; CREATE VIEW emp_dept_view AS SELECT e.employee_name, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id; CREATE VIEW emp_dept_view AS SELECT employee_name, department_name FROM employees JOIN departments USING (department_id);CREATE VIEW emp_dept_view AS SELECT employee_name, department_name FROM employees NATURAL JOIN departments;

A complex view can include multiple tables joined together. The correct SQL statements will use JOIN clauses in the SELECT statement within the CREATE VIEW command.

Q14

Multiple Choice

Identify the SQL statements that correctly create a materialized view in Oracle.

CREATE MATERIALIZED VIEW dept_salary_mv AS SELECT department_id, SUM(salary) FROM employees GROUP BY department_id; CREATE MATERIALIZED VIEW dept_salary_mv AS SELECT department_id, SUM(salary) FROM employees GROUP BY department_id REFRESH FAST; CREATE MATERIALIZED VIEW dept_salary_mv AS SELECT department_id, SUM(salary) FROM employees;CREATE MATERIALIZED VIEW dept_salary_mv AS SELECT department_id, SUM(salary) FROM employees GROUP BY department_id REFRESH COMPLETE ON DEMAND;

A materialized view stores the result of a query physically and can be refreshed periodically. The correct SQL statements will use the CREATE MATERIALIZED VIEW command and include an aggregation function.

Q15

Multiple Choice

Choose the SQL statements that correctly demonstrate the creation of a view with a WHERE clause in Oracle.

CREATE VIEW hr_employees_view AS SELECT employee_name, department_id FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'HR'); CREATE VIEW hr_employees_view AS SELECT employee_name, department_id FROM employees WHERE department_id = 10; CREATE VIEW hr_employees_view AS SELECT employee_name, department_id FROM employees WHERE department_name = 'HR';CREATE VIEW hr_employees_view AS SELECT employee_name, department_id FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'HR') WITH CHECK OPTION;

Including a WHERE clause in a view allows you to filter the rows returned by the view. The correct SQL statements will use the WHERE clause within the SELECT statement of the CREATE VIEW command.

Q16

Multiple Choice

Which SQL statements demonstrate the creation of an inline view in Oracle?

SELECT * FROM (SELECT employee_name, salary FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 5; SELECT * FROM (SELECT employee_name, salary FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY); SELECT employee_name, salary FROM (SELECT employee_name, salary FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 5;SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

An inline view is a subquery used within a FROM clause, acting like a temporary table for the outer query. The correct SQL statements will include a subquery in the FROM clause.

Q17

Multiple Choice

Determine the SQL statements that correctly create a view with a WITH CHECK OPTION clause in Oracle.

CREATE VIEW sales_employees_view AS SELECT employee_name, department_id FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales') WITH CHECK OPTION; CREATE VIEW sales_employees_view AS SELECT employee_name, department_id FROM employees WHERE department_id = 20 WITH CHECK OPTION; CREATE VIEW sales_employees_view AS SELECT employee_name, department_id FROM employees WHERE department_name = 'Sales';CREATE VIEW sales_employees_view AS SELECT employee_name, department_id FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');

The WITH CHECK OPTION clause ensures that any DML operations performed on the view do not violate the conditions defined in the view's WHERE clause. The correct SQL statements will include this clause.

Q18

Multiple Choice

Which of the following SQL statements correctly create this read-only view?

CREATE VIEW readonly_emp_view AS SELECT employee_name, salary FROM employees WITH READ ONLY; CREATE VIEW readonly_emp_view AS SELECT employee_name, salary FROM employees WHERE salary > 50000 WITH READ ONLY; CREATE VIEW readonly_emp_view AS SELECT employee_name, salary FROM employees WHERE salary > 50000;CREATE VIEW readonly_emp_view AS SELECT employee_name, salary FROM employees WITH READ ONLY OPTION;

A read-only view prevents any DML operations on the underlying table through the view. The correct SQL statements will use the WITH READ ONLY clause.

Q19

Multiple Choice

Choose the SQL statements that correctly demonstrate the creation of a view with a GROUP BY clause in Oracle.

CREATE VIEW dept_salary_view AS SELECT department_id, SUM(salary) AS total_salary FROM employees GROUP BY department_id; CREATE VIEW dept_salary_view AS SELECT department_id, total_salary FROM employees GROUP BY department_id; CREATE VIEW dept_salary_view AS SELECT department_id, SUM(salary) AS total_salary FROM employees GROUP BY department_id HAVING SUM(salary) > 100000;CREATE VIEW dept_salary_view AS SELECT department_id, SUM(salary) FROM employees;

Using the GROUP BY clause in a view allows you to aggregate data by specific columns. The correct SQL statements will include the GROUP BY clause within the SELECT statement of the CREATE VIEW command.

Q20

Multiple Choice

Select the SQL statements that demonstrate the creation of a view with a JOIN and a GROUP BY clause in Oracle.

CREATE VIEW dept_total_salary_view AS SELECT d.department_name, e.department_id, SUM(e.salary) AS total_salary FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY d.department_name, e.department_id; CREATE VIEW dept_total_salary_view AS SELECT d.department_name, SUM(e.salary) AS total_salary FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY d.department_name; CREATE VIEW dept_total_salary_view AS SELECT department_name, SUM(salary) AS total_salary FROM employees NATURAL JOIN departments GROUP BY department_id;CREATE VIEW dept_total_salary_view AS SELECT d.department_name, e.department_id, SUM(e.salary) AS total_salary FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY d.department_name;

Combining JOIN and GROUP BY in a view allows you to aggregate data across multiple tables. The correct SQL statements will include both a JOIN and a GROUP BY clause in the SELECT statement within the CREATE VIEW command.

Q21

Single Choice

How would you create a database view to display the employee names and their department names?

SQL Code

CREATE VIEW emp_dept_view AS
SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

CREATE VIEW emp_dept_view AS SELECT * FROM employees; CREATE VIEW emp_dept_view AS SELECT employee_name, department_name FROM employees; CREATE VIEW emp_dept_view AS SELECT e.employee_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;CREATE VIEW emp_dept_view AS SELECT department_name FROM departments;

A view is created using the CREATE VIEW statement, combining data from the employees and departments tables using a JOIN clause.

Q22

Single Choice

How can you create a view that filters records based on a specific condition?

SQL Code

CREATE VIEW active_employees_view AS
SELECT employee_id, employee_name
FROM employees
WHERE status = 'Active';

CREATE VIEW active_employees_view AS SELECT * FROM employees; CREATE VIEW active_employees_view AS SELECT employee_id, employee_name FROM employees WHERE status = 'Active'; CREATE VIEW active_employees_view AS SELECT employee_id FROM employees;CREATE VIEW active_employees_view AS SELECT * FROM employees WHERE status = 'Inactive';

A view with a WHERE clause allows you to filter the records based on a specific condition, such as the status of employees.

Q23

Single Choice

What happens when you try to create a view with a SELECT statement that includes a GROUP BY clause?

SQL Code

CREATE VIEW dept_salary_view AS
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

The view is created successfully, allowing data aggregation. The view creation fails because GROUP BY cannot be used in a view. The view is created, but the GROUP BY clause is ignored.The view creation fails unless an ORDER BY clause is also included.

Views can include a GROUP BY clause, allowing you to create summary views that aggregate data based on specific columns.

Q24

Single Choice

How would you create a view that includes a subquery in its SELECT statement?

SQL Code

CREATE VIEW high_salary_employees AS
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

CREATE VIEW high_salary_employees AS SELECT * FROM employees; CREATE VIEW high_salary_employees AS SELECT employee_id, salary FROM employees; CREATE VIEW high_salary_employees AS SELECT employee_id, employee_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);CREATE VIEW high_salary_employees AS SELECT * FROM employees WHERE salary < (SELECT AVG(salary) FROM employees);

Views can include subqueries within their SELECT statements to filter or calculate data based on nested queries.

Q25

Single Choice

What is the impact of using the WITH CHECK OPTION clause when creating a view?

SQL Code

CREATE VIEW active_employees_view AS
SELECT employee_id, employee_name
FROM employees
WHERE status = 'Active'
WITH CHECK OPTION;

It allows rows to be inserted that do not meet the view's criteria. It prevents rows that do not meet the view's criteria from being inserted or updated. It automatically updates the base table to meet the view's criteria.It has no effect on DML operations.

The WITH CHECK OPTION clause ensures that all DML operations on the view adhere to the conditions specified in the view's WHERE clause, preventing the insertion or update of rows that don't meet the criteria.

Q26

Single Choice

How would you create a view that concatenates columns to display a full name?

SQL Code

CREATE VIEW employee_full_name_view AS
SELECT employee_id, first_name || ' ' || last_name AS full_name
FROM employees;

CREATE VIEW employee_full_name_view AS SELECT * FROM employees; CREATE VIEW employee_full_name_view AS SELECT employee_id, last_name FROM employees; CREATE VIEW employee_full_name_view AS SELECT employee_id, first_name || ' ' || last_name AS full_name FROM employees;CREATE VIEW employee_full_name_view AS SELECT first_name || ' ' || last_name FROM employees;

Concatenation of columns can be achieved using the || operator in Oracle, and this can be used within a view to create a calculated column.

Q27

Single Choice

What is the result of creating a view that includes an ORDER BY clause?

SQL Code

CREATE VIEW sorted_employees_view AS
SELECT employee_id, employee_name
FROM employees
ORDER BY employee_name;

The view is created with the specified ordering. The view is created, but the ORDER BY clause is ignored during query execution. The view creation fails because ORDER BY is not allowed in views.The view is created, but an ORDER BY clause must be included in every query that selects from the view.

In Oracle, the ORDER BY clause in a view is generally ignored when the view is queried unless it is part of the query that selects from the view.

Q28

Single Choice

How would you create a view that restricts access to only specific columns?

SQL Code

CREATE VIEW employee_summary_view AS
SELECT employee_id, employee_name, department_id
FROM employees;

CREATE VIEW employee_summary_view AS SELECT * FROM employees; CREATE VIEW employee_summary_view AS SELECT employee_id, employee_name, department_id FROM employees; CREATE VIEW employee_summary_view AS SELECT employee_name FROM employees;CREATE VIEW employee_summary_view AS SELECT department_id FROM employees;

A view can be created to restrict access to only specific columns by including only those columns in the SELECT statement.

Q29

Single Choice

What is the purpose of creating an inline view in Oracle?

SQL Code

SELECT department_id, AVG(salary) AS avg_salary
FROM (SELECT department_id, salary
 FROM employees)
GROUP BY department_id;

To create a permanent view in the database. To use a subquery as a temporary view within a SELECT statement. To combine multiple tables into a single view.To create a materialized view for performance improvements.

An inline view is a subquery in the FROM clause that serves as a temporary table or view, allowing for complex queries and calculations.

Q30

Single Choice

How would you create a materialized view that stores precomputed results?

SQL Code

CREATE MATERIALIZED VIEW sales_summary_mv
AS SELECT product_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_id;

CREATE MATERIALIZED VIEW sales_summary_mv AS SELECT product_id, sales_amount FROM sales; CREATE MATERIALIZED VIEW sales_summary_mv AS SELECT * FROM sales; CREATE MATERIALIZED VIEW sales_summary_mv AS SELECT product_id, SUM(sales_amount) AS total_sales FROM sales GROUP BY product_id;CREATE MATERIALIZED VIEW sales_summary_mv AS SELECT product_id, total_sales FROM sales_summary;

A materialized view stores precomputed query results, allowing for faster query execution by avoiding repetitive calculations.

Oracle Database Quiz Questions