SQL - Structured Query Language SQL View Management Questions and Answers 1 — Questions and Answers
Question 1: Which of the following is a primary benefit of using a SQL view?
- To create a physical, permanent copy of the data from one or more tables for faster writes.
- To simplify complex queries and restrict access to the underlying base tables. (Correct answer)
- To enforce foreign key constraints between tables.
- To improve the performance of DELETE operations on the underlying tables.
Correct answer: To simplify complex queries and restrict access to the underlying base tables.
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?
- DEFINE VIEW V_Employee_Department AS SELECT e.FullName, d.DepartmentName FROM Employees e JOIN Departments d ON e.DeptID = d.DeptID;
- CREATE VIEW V_Employee_Department AS SELECT e.FullName, d.DepartmentName FROM Employees e JOIN Departments d ON e.DeptID = d.DeptID; (Correct answer)
- MAKE VIEW V_Employee_Department SELECT e.FullName, d.DepartmentName FROM Employees e JOIN Departments d ON e.DeptID = d.DeptID;
- CREATE V_Employee_Department AS SELECT e.FullName, d.DepartmentName FROM Employees, Departments;
Correct answer: CREATE VIEW V_Employee_Department AS SELECT e.FullName, d.DepartmentName FROM Employees e JOIN Departments d ON e.DeptID = d.DeptID;
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?
- MODIFY VIEW V_Active_Users AS ...
- UPDATE VIEW V_Active_Users SET ...
- REPLACE VIEW V_Active_Users WITH ...
- ALTER VIEW V_Active_Users AS ... (Correct answer)
Correct answer: ALTER VIEW V_Active_Users AS ...
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?
- The view's definition is removed, and all data from the underlying tables is deleted.
- The view is marked as obsolete but remains in the database for historical queries.
- The view's definition is removed from the database, but the data in the underlying base tables is unaffected. (Correct answer)
- Only the data currently represented by the view is deleted; the view definition and base tables remain.
Correct answer: The view's definition is removed from the database, but the data in the underlying base tables is unaffected.
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?
- The view is based on a single table and references all columns with NOT NULL constraints.
- The view is defined with a simple WHERE clause to filter rows.
- The view is based on a join of multiple tables or contains an aggregate function like COUNT(). (Correct answer)
- The view has column aliases that are different from the base table's column names.
Correct answer: The view is based on a join of multiple tables or contains an aggregate function like COUNT().
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.
Question 6: Which of the following statements about SQL views is TRUE?
- Views are primarily used to create indexes on complex queries.
- A view always stores a physical copy of its data, which is refreshed periodically.
- Once a view is defined with `SELECT *`, it automatically includes any new columns added to the base table later.
- A view can be used to provide a consistent data interface even if the underlying table schemas change. (Correct answer)
Correct answer: A view can be used to provide a consistent data interface even if the underlying table schemas change.
A view provides a layer of abstraction. If an underlying table is restructured (e.g., a column is split), the view can be altered to reconstruct the original structure, ensuring that applications querying the view do not break. This provides a backward-compatible interface. Views do not automatically update to include new columns from a `SELECT *` definition; they are static at creation. Standard views do not store data physically (unlike materialized views). Indexes are created on tables, not standard views (though some systems have indexed/materialized views).
SQL - Structured Query Language SQL View Management Questions and Answers 1
Which of the following is a primary benefit of using a SQL view?