Creating views in MySQL - Practice Exercises

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple view query

Given a table Employees with columns id, name, and salary, and a view HighEarners 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 table contains:

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

MySQL

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

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

B []

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

D [{"id": 1, "name": "Alice", "salary": 60000}, {"id": 3, "name": "Carol", "salary": 70000}]

💡 Hint

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

The view selects only employees with salary > 50000 and only the name and salary columns. Bob is excluded because his salary is 45000.

🧠 Conceptual

intermediate

Understanding view update restrictions

Which of the following statements about updating data through a MySQL view is correct?

A You can update data through any view regardless of how complex its SELECT statement is.

B Views always allow INSERT operations but never UPDATE or DELETE.

C You cannot update data through a view that includes GROUP BY or aggregate functions.

D Updating a view automatically creates a new table with the updated data.

💡 Hint

Think about what happens when a view groups or summarizes data.

Views with GROUP BY or aggregates summarize data, so updating them is not allowed because it is unclear how to apply changes to the underlying tables.

📝 Syntax

advanced

Identify the correct syntax to create a view

Which option correctly creates a view named TopProducts that shows product_name and price from the Products table where price is greater than 100?

A CREATE VIEW TopProducts SELECT product_name, price FROM Products WHERE price > 100;

B CREATE TopProducts VIEW AS SELECT product_name, price FROM Products WHERE price > 100;

C VIEW CREATE TopProducts AS SELECT product_name, price FROM Products WHERE price > 100;

D CREATE VIEW TopProducts AS SELECT product_name, price FROM Products WHERE price > 100;

💡 Hint

Remember the order: CREATE VIEW view_name AS SELECT ...

The correct syntax is CREATE VIEW view_name AS SELECT ... The other options have incorrect keyword order or missing AS.

❓ optimization

advanced

Improving performance of a view with joins

You have a view OrderDetailsView that joins Orders and Customers tables. The view is slow when queried. Which approach will most likely improve query performance?

A Drop the view and query the tables directly without any indexes.

B Add indexes on the join columns in both tables.

C Rewrite the view to use subqueries instead of joins.

D Create the view with SELECT * to include all columns.

💡 Hint

Indexes help speed up joins by quickly locating matching rows.

Adding indexes on join columns helps the database find matching rows faster, improving performance.

🔧 Debug

expert

Diagnosing error when creating a view

You run this statement:
CREATE VIEW RecentSales AS SELECT sale_id, sale_date FROM (SELECT * FROM Sales) AS tmp WHERE sale_date > NOW() - INTERVAL 30 DAY;
But MySQL returns an error: "ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause". What is the most likely cause?

A The view's SELECT contains a subquery in the FROM clause, which is not allowed.

B The WHERE clause uses an interval expression which is not supported in views.

C The view's SELECT uses a function (NOW()) which is not allowed in views.

D The view name RecentSales is a reserved keyword and cannot be used.

💡 Hint

Check if the SELECT statement has any subqueries inside FROM.

MySQL does not allow views to contain subqueries in the FROM clause. The error indicates such a subquery exists.