View as a saved query mental model 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, and salary, and the view defined as:

CREATE VIEW HighEarners AS SELECT name, salary FROM Employees WHERE salary > 50000;

What will be the output of SELECT * FROM HighEarners; if the Employees table contains:

id | name    | salary
1  | Alice   | 60000
2  | Bob     | 45000
3  | Charlie | 70000

SQL

CREATE VIEW HighEarners AS SELECT name, salary FROM Employees WHERE salary > 50000;

A [{"name": "Alice", "salary": 60000}, {"name": "Charlie", "salary": 70000}]

B [{"id": 1, "name": "Alice", "salary": 60000}, {"id": 3, "name": "Charlie", "salary": 70000}]

C [{"name": "Bob", "salary": 45000}]

D []

💡 Hint

The view filters employees with salary greater than 50000 and only selects name and salary columns.

The view HighEarners selects only the name and salary columns from Employees where salary is greater than 50000. Bob is excluded because his salary is 45000.

🧠 Conceptual

intermediate

Understanding view updates

Which of the following statements about SQL views is TRUE?

A A view can only be created on a single table and cannot join multiple tables.

B A view stores data physically and updates automatically when the base table changes.

C A view cannot be used in a SELECT statement.

D A view is a saved query and does not store data physically; it reflects the current data in the base tables when queried.

💡 Hint

Think about whether views hold data or just a query definition.

Views are saved queries that do not store data themselves. When you query a view, it runs the underlying query on the base tables and shows current data.

📝 Syntax

advanced

Identify the syntax error in view creation

Which option contains a syntax error when creating a view that lists product names and their categories from tables Products and Categories?

SQL

Tables:
Products(product_id, product_name, category_id)
Categories(category_id, category_name)

A CREATE VIEW ProductCategories SELECT product_name, category_name FROM Products JOIN Categories ON Products.category_id = Categories.category_id;

B CREATE VIEW ProductCategories AS SELECT product_name, category_name FROM Products JOIN Categories ON Products.category_id = Categories.category_id;

C CREATE VIEW ProductCategories AS SELECT product_name, category_name FROM Products, Categories WHERE Products.category_id = Categories.category_id;

D CREATE VIEW ProductCategories AS SELECT product_name, category_name FROM Products INNER JOIN Categories ON Products.category_id = Categories.category_id;

💡 Hint

Check the syntax for the CREATE VIEW statement carefully.

Option A is missing the keyword 'AS' after the view name, which is required in standard SQL syntax for creating views.

❓ optimization

advanced

Performance impact of views

Consider a view defined as:

CREATE VIEW LargeSales AS SELECT * FROM Sales WHERE amount > 10000;

If the Sales table has millions of rows, which approach is generally better for performance when querying large sales data?

A Query the view directly each time to get the filtered data.

B Create an indexed materialized view that stores the filtered data physically.

C Create a temporary table with all Sales data and query it instead of the view.

D Avoid filtering and select all rows from Sales every time.

💡 Hint

Think about how data storage and indexing affect query speed.

Materialized views store the query result physically and can be indexed, improving performance for large datasets compared to querying a standard view which runs the query each time.

🔧 Debug

expert

Diagnose the error when querying a view

A view is created as:

CREATE VIEW ActiveUsers AS SELECT id, name FROM Users WHERE status = 'active';

When running SELECT * FROM ActiveUsers;, the error ERROR: relation "activeusers" does not exist appears. What is the most likely cause?

A The SELECT statement inside the view has a syntax error.

B The Users table does not have a column named status.

C The view was created in a different schema or database than the one currently queried.

D The view name is case-sensitive and must be quoted exactly.

💡 Hint

Consider how database schemas and search paths affect object visibility.

The error indicates the view does not exist in the current search path. It is likely created in another schema or database, so the query cannot find it without schema qualification.