View Management Flashcards
6 cards from real SQL practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.
Read the first 6 View Management flashcards as text
Which of the following is a primary benefit of using a SQL view?
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.
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?
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.
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?
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.
What is the effect of executing the `DROP VIEW V_Product_Summary;` command on a database?
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.
A user attempts to execute an `INSERT` statement on a view. Under which of the following conditions is the operation most likely to fail?
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.
Which of the following statements about SQL views is TRUE?
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).