Views for security and abstraction in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple view query

Given the table Employees with columns id, name, salary, and a view HighEarners defined as CREATE VIEW HighEarners AS SELECT id, name FROM Employees WHERE salary > 70000;, what will be the output of SELECT * FROM HighEarners; if the table contains:

id | name | salary
1 | Alice | 80000
2 | Bob | 60000
3 | Carol | 90000

SQL

CREATE VIEW HighEarners AS SELECT id, name FROM Employees WHERE salary > 70000;
SELECT * FROM HighEarners;

A [{"id":1,"name":"Alice"},{"id":3,"name":"Carol"}]

B []

C [{"id":2,"name":"Bob"}]

D [{"id":1,"name":"Alice"},{"id":2,"name":"Bob"},{"id":3,"name":"Carol"}]

💡 Hint

Remember the view filters employees with salary greater than 70000.

The view selects only employees with salary above 70000, so Bob is excluded.

🧠 Conceptual

intermediate

Purpose of using views for security

Why are views often used to improve security in databases?

A They automatically encrypt data stored in the database.

B They allow restricting user access to specific columns or rows without giving full table access.

C They speed up query execution by storing data physically.

D They prevent users from running any queries on the database.

💡 Hint

Think about how views can limit what data users see.

Views can show only certain columns or rows, hiding sensitive data from users.

📝 Syntax

advanced

Identify the syntax error in view creation

Which option contains a syntax error when creating a view that shows only active customers from a Customers table with a boolean active column?

A CREATE VIEW ActiveCustomers AS SELECT * FROM Customers WHERE active == TRUE;

B CREATE VIEW ActiveCustomers AS SELECT * FROM Customers WHERE active = TRUE;

C CREATE VIEW ActiveCustomers AS SELECT * FROM Customers WHERE active IS TRUE;

D CREATE VIEW ActiveCustomers AS SELECT * FROM Customers WHERE active = 1;

💡 Hint

Check the comparison operator used in SQL.

SQL uses a single equals sign (=) for comparison, not double (==).

❓ optimization

advanced

Optimizing view performance with indexes

You have a view RecentOrders defined as SELECT * FROM Orders WHERE order_date > CURRENT_DATE - INTERVAL '30 days'. Which approach will best improve query performance when selecting from this view?

A Drop the view and query the <code>Orders</code> table without any indexes.

B Create an index on the view <code>RecentOrders</code> directly.

C Rewrite the view to select all orders without filtering.

D Create an index on the <code>order_date</code> column in the <code>Orders</code> table.

💡 Hint

Think about how databases use indexes to speed up filtering.

Indexes on the underlying table columns used in the view's WHERE clause speed up filtering.

🔧 Debug

expert

Why does this view cause an error on update?

Consider the view CREATE VIEW EmployeeNames AS SELECT id, name FROM Employees;. A user tries to run UPDATE EmployeeNames SET name = 'John' WHERE id = 5; but gets an error. Why?

A The user does not have permission to update the Employees table.

B Views cannot be updated in any database system.

C The view does not include all columns needed for an update, so it is not updatable.

D The UPDATE statement syntax is incorrect for views.

💡 Hint

Think about what makes a view updatable.

Views that do not include all key columns or have complex logic often cannot be updated directly.