Querying from views in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of querying a simple view

Given a view EmployeeView defined as CREATE VIEW EmployeeView AS SELECT id, name, salary FROM Employees WHERE salary > 50000;, what will be the output of SELECT * FROM EmployeeView WHERE name = 'Alice'; if the Employees table contains:

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

A No rows returned

B 2 rows: id=1, name='Alice', salary=60000 and id=3, name='Carol', salary=70000

C 3 rows: all employees regardless of salary

D 1 row: id=1, name='Alice', salary=60000

💡 Hint

Remember the view filters employees with salary greater than 50000.

The view only includes employees with salary above 50000. Among them, only Alice matches the name filter.

📝 Syntax

intermediate

Identify the syntax error in querying a view

Which of the following queries will cause a syntax error when querying a view named ProductView?

A SELECT * FROM ProductView WHERE price > 100;

B SELECT * ProductView WHERE price > 100;

C SELECT * FROM ProductView GROUP BY category;

D SELECT price, name FROM ProductView;

💡 Hint

Check the basic SELECT syntax for missing keywords.

Option B is missing the FROM keyword, causing a syntax error.

❓ optimization

advanced

Optimizing queries on views with joins

Consider a view OrderSummary defined as:
CREATE VIEW OrderSummary AS SELECT o.id, c.name, o.total FROM Orders o JOIN Customers c ON o.customer_id = c.id;
Which query will be the most efficient to get orders with total > 1000?

A SELECT o.id, c.name, o.total FROM Orders o JOIN Customers c ON o.customer_id = c.id WHERE o.total > 1000;

B SELECT * FROM OrderSummary HAVING total > 1000;

C SELECT * FROM OrderSummary WHERE total > 1000;

D SELECT * FROM OrderSummary WHERE total < 1000;

💡 Hint

Filtering before joining can improve performance.

Option A filters orders before joining, reducing data processed. Option A filters after the view's join, which may be less efficient.

🔧 Debug

advanced

Debugging incorrect results from a view query

A view ActiveUsers is defined as:
CREATE VIEW ActiveUsers AS SELECT id, username FROM Users WHERE status = 'active';
Running SELECT * FROM ActiveUsers WHERE username LIKE '%admin%'; returns no rows, but you know there are active users with 'admin' in their username. What is the most likely cause?

A The query is case-sensitive and usernames have different case.

B The Users table has no active users with 'admin' in username.

C The view definition filters out users with 'admin' in username.

D The LIKE operator is not supported in views.

💡 Hint

Consider how string matching works in SQL.

The LIKE operator is case-sensitive in many MySQL configurations, so 'Admin' won't match '%admin%'.

🧠 Conceptual

expert

Understanding limitations of updatable views

Which of the following statements about updatable views in MySQL is TRUE?

A Views with GROUP BY clauses are always updatable.

B Views that join multiple tables can never be updated directly.

C Views that select from a single table without aggregation can be updatable.

D Views without a primary key cannot be updated.

💡 Hint

Think about when MySQL allows updates through views.

MySQL allows updates on views that select from a single table without aggregation or grouping.