SQL - Structured Query Language SQL View Management Questions and Answers 1

Question 1

Which of the following is a primary benefit of using a SQL view?

Accepted Answer

To simplify complex queries and restrict access to the underlying base tables.

Answer

SQL views act as virtual tables. Their primary benefits include encapsulating complex join and aggregation logic into a simple SELECT statement and providing a security layer by granting users access to the view instead of the sensitive base tables.

Question 2

A developer needs to create a simplified, read-only representation of employee data, joining the `Employees` table with the `Departments` table to show each employee's name and their department's name. Which SQL statement correctly creates this view?

Accepted Answer

CREATE VIEW V_Employee_Department AS SELECT e.FullName, d.DepartmentName FROM Employees e JOIN Departments d ON e.DeptID = d.DeptID;

Answer

The standard SQL syntax for creating a view is `CREATE VIEW view_name AS SELECT ...`. This statement defines a new view named `V_Employee_Department` based on the result set of the specified SELECT query that joins the Employees and Departments tables.

Question 3

A database administrator needs to modify the underlying query of an existing view named `V_Active_Users` without dropping it, to include a new column. Which SQL command is used for this purpose?

Accepted Answer

ALTER VIEW V_Active_Users AS ...

Answer

The `ALTER VIEW` statement is the standard SQL command used to change the definition of an existing view without dropping and recreating it. Some database systems also support `CREATE OR REPLACE VIEW`, which achieves a similar outcome.

Question 4

What is the effect of executing the `DROP VIEW V_Product_Summary;` command on a database?

Accepted Answer

The view's definition is removed from the database, but the data in the underlying base tables is unaffected.

Answer

The `DROP VIEW` command removes the view's definition from the database schema. Since a standard view is a virtual table and does not store data itself, this operation has no impact on the data within the underlying base tables.

Question 5

A user attempts to execute an `INSERT` statement on a view. Under which of the following conditions is the operation most likely to fail?

Accepted Answer

The view is based on a join of multiple tables or contains an aggregate function like COUNT().

Answer

DML operations (INSERT, UPDATE, DELETE) on a view are generally not permitted if the view is complex. This includes views built on multiple tables (as it's ambiguous which table to modify), or views that use aggregate functions (like SUM(), COUNT()), GROUP BY, or DISTINCT, because these operations create derived data that doesn't map to a single, specific row in a base table.

SQL Practice Test

SQL Practice Test

SQL - Structured Query Language SQL View Management Questions and Answers 1