Querying through views in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of querying a simple view

Given a table Employees with columns id, name, and department, and a view View_DeptSales defined as SELECT id, name FROM Employees WHERE department = 'Sales', what is the output of SELECT * FROM View_DeptSales ORDER BY id; if the Employees table contains:

id | name | department
1 | Alice | Sales
2 | Bob | HR
3 | Carol | Sales

SQL

CREATE VIEW View_DeptSales AS SELECT id, name FROM Employees WHERE department = 'Sales';

SELECT * FROM View_DeptSales ORDER BY id;

A

1 | Alice
2 | Bob

B

2 | Bob
3 | Carol

C

1 | Alice
3 | Carol

D

1 | Alice
2 | Bob
3 | Carol

💡 Hint

Remember the view filters only employees in the Sales department.

The view selects only rows where department is 'Sales'. So only Alice (id 1) and Carol (id 3) appear.

📝 Syntax

intermediate

Identify the syntax error in view creation

Which option contains a syntax error when creating a view that selects name and salary from Employees where salary is above 50000?

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

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

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

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

💡 Hint

Check the SELECT clause for missing commas.

Option D misses a comma between 'name' and 'salary', causing a syntax error.

❓ optimization

advanced

Optimizing queries using indexed views

You have a large Sales table with millions of rows. You create a view View_TotalSales that sums sales by region. Which option best improves query performance when selecting total sales per region?

A Create an indexed view (materialized view) with SUM aggregation on region and query it.

B Create a regular view with SUM aggregation and query it directly.

C Create a view without aggregation and perform SUM in each query.

D Avoid views and always query the base table with SUM aggregation.

💡 Hint

Think about pre-computing aggregates for faster queries.

Indexed views store pre-aggregated data, speeding up queries on large datasets.

🔧 Debug

advanced

Debugging unexpected results from a view

A view View_ActiveUsers is defined as SELECT id, name FROM Users WHERE active = TRUE. However, querying SELECT * FROM View_ActiveUsers; returns no rows, even though the Users table has active users. Which option explains the most likely cause?

A The <code>active</code> column is stored as '1' and '0' strings, not boolean TRUE/FALSE.

B The view definition is missing a semicolon at the end.

C The <code>Users</code> table is empty.

D The query on the view is missing a WHERE clause.

💡 Hint

Check data types and values in the active column.

If active uses strings '1'/'0' instead of boolean TRUE/FALSE, the filter fails.

🧠 Conceptual

expert

Understanding view update limitations

Which of the following statements about updating data through views is correct?

A All views can be updated directly regardless of complexity.

B Views that include joins or aggregations generally cannot be updated directly.

C Views created with SELECT * are never updatable.

D Views with WHERE clauses are never updatable.

💡 Hint

Think about what makes a view updatable or not.

Views with joins or aggregations usually do not allow direct updates because the database cannot map changes back to base tables unambiguously.