Why views are needed in SQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Purpose of Views in Databases

Why do databases use views? Choose the best reason.

A To simplify complex queries by saving them as reusable virtual tables

B To physically store large amounts of data for faster access

C To replace indexes for speeding up data retrieval

D To permanently delete data from multiple tables at once

💡 Hint

Think about how views help users work with data without changing the original tables.

Views act like saved queries that look like tables but do not store data themselves. They help simplify complex queries and provide a consistent way to see data.

🧠 Conceptual

intermediate

Security Benefit of Views

How do views help improve database security?

A By allowing users to see only specific columns or rows without accessing the full table

B By encrypting all data stored in the database automatically

C By blocking all user access except for administrators

D By deleting sensitive data after a set time

💡 Hint

Think about how views can limit what data a user can see.

Views can show only selected parts of a table, so users can access just the data they need without seeing sensitive information.

❓ query_result

advanced

Output of Query Using a View

Given the view definition and table data below, what will this query return?

SQL

CREATE VIEW ActiveCustomers AS
SELECT CustomerID, Name FROM Customers WHERE Status = 'Active';

-- Table Customers:
-- CustomerID | Name    | Status
-- 1          | Alice   | Active
-- 2          | Bob     | Inactive
-- 3          | Carol   | Active

SELECT * FROM ActiveCustomers;

A CustomerID: 2, Name: Bob

B CustomerID: 1, Name: Alice; CustomerID: 2, Name: Bob; CustomerID: 3, Name: Carol

C CustomerID: 1, Name: Alice; CustomerID: 3, Name: Carol

D Empty result set

💡 Hint

Look at the WHERE clause in the view definition.

The view selects only customers with Status 'Active'. Bob is 'Inactive', so he is excluded.

🧠 Conceptual

advanced

Why Views Improve Maintainability

How do views help maintain database applications when the underlying tables change?

A They automatically update all application code to match table changes

B They store backup copies of tables to restore if needed

C They prevent any changes to the underlying tables

D They provide a stable interface so queries don’t need to change even if table structures do

💡 Hint

Think about how views act as a layer between users and tables.

Views can hide changes in table structure by presenting data in a consistent way, so applications using views don’t break when tables change.

🧠 Conceptual

expert

Performance Impact of Views

Which statement about the performance impact of views is true?

A Views always improve performance by caching data automatically

B Views do not store data, so querying a view runs the underlying query each time, which can affect performance

C Views create physical copies of data that speed up queries

D Views prevent the database from using indexes on underlying tables

💡 Hint

Consider what happens when you query a view.

Since views are virtual tables defined by queries, the database runs the view’s query each time it is accessed, which can slow down performance if the query is complex.