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.

Practice

1. What is the main purpose of creating a view in MySQL?

easy

A. To delete data from multiple tables at once

B. To save a SELECT query as a virtual table for easy reuse

C. To permanently store data physically in the database

D. To create a backup of the database

Solution

1. Step 1: Understand what a view is

   A view is a virtual table that stores a SELECT query, not data itself.

2. Step 2: Identify the purpose of views

   Views simplify complex queries and allow reuse without storing data physically.

3. Final Answer:

   To save a SELECT query as a virtual table for easy reuse -> Option B

4. Quick Check:

   Views = virtual tables for queries [OK]

Hint: Views store queries, not data, for easy reuse [OK]

Common Mistakes:

• Thinking views store data physically
• Confusing views with backups
• Assuming views delete data

2. Which of the following is the correct syntax to create a view named employee_view showing id and name from employees table?

easy

A. VIEW CREATE employee_view AS SELECT id, name FROM employees;

B. CREATE employee_view VIEW AS SELECT id, name FROM employees;

C. CREATE VIEW employee_view AS SELECT id, name FROM employees;

D. CREATE VIEW employee_view SELECT id, name FROM employees;

Solution

1. Step 1: Recall the correct CREATE VIEW syntax

   The syntax is: CREATE VIEW view_name AS SELECT ...

2. Step 2: Match the syntax with options

   CREATE VIEW employee_view AS SELECT id, name FROM employees; matches the correct syntax exactly.

3. Final Answer:

   CREATE VIEW employee_view AS SELECT id, name FROM employees; -> Option C

4. Quick Check:

   CREATE VIEW ... AS SELECT ... [OK]

Hint: Remember: CREATE VIEW name AS SELECT ... [OK]

Common Mistakes:

• Swapping keywords CREATE and VIEW
• Omitting AS keyword
• Incorrect keyword order

3. Given the table products with columns id, name, and price, what will be the output of SELECT * FROM cheap_products; if cheap_products is created as CREATE VIEW cheap_products AS SELECT id, name FROM products WHERE price < 50;?

medium

A. Only id and name columns of products with price less than 50

B. All columns (id, name, price) of products with price less than 50

C. Only products with price greater than or equal to 50

D. Syntax error because price column is missing in view

Solution

1. Step 1: Analyze the view definition

   The view selects only id and name columns where price is less than 50.

2. Step 2: Understand the SELECT * from the view

   Since the view only has id and name, SELECT * returns only these columns for matching rows.

3. Final Answer:

   Only id and name columns of products with price less than 50 -> Option A

4. Quick Check:

   View columns = selected columns only [OK]

Hint: View shows only columns defined in its SELECT [OK]

Common Mistakes:

• Expecting all original table columns
• Confusing filter condition with columns selected
• Assuming syntax error if columns omitted

4. What is wrong with this statement?
CREATE VIEW sales_view AS SELECT order_id, total FROM orders WHERE;

medium

A. Using SELECT instead of INSERT

B. Missing AS keyword before SELECT

C. Incorrect view name syntax

D. Missing condition after WHERE clause

Solution

1. Step 1: Check the syntax after WHERE

   The WHERE clause must have a condition; here it is empty.

2. Step 2: Confirm other parts are correct

   AS keyword is present, view name is valid, SELECT is correct for views.

3. Final Answer:

   Missing condition after WHERE clause -> Option D

4. Quick Check:

   WHERE requires a condition [OK]

Hint: WHERE must always have a condition [OK]

Common Mistakes:

• Leaving WHERE empty
• Confusing AS placement
• Using wrong SQL command for views

5. You want to create a view active_customers that shows customer id, name, and their total orders from customers and orders tables. Which query correctly creates this view?

hard

A. CREATE VIEW active_customers AS SELECT c.id, c.name, COUNT(o.order_id) AS total_orders FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.id, c.name;

B. CREATE VIEW active_customers AS SELECT id, name, total_orders FROM customers;

C. CREATE VIEW active_customers AS SELECT c.id, c.name, SUM(o.price) FROM customers c JOIN orders o ON c.id = o.customer_id;

D. CREATE VIEW active_customers AS SELECT c.id, c.name, COUNT(o.order_id) FROM customers c, orders o WHERE c.id = o.customer_id;

Solution

1. Step 1: Understand the requirement

   The view must show customer id, name, and total orders count, including customers with zero orders.

2. Step 2: Analyze each option

   CREATE VIEW active_customers AS SELECT c.id, c.name, COUNT(o.order_id) AS total_orders FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.id, c.name; uses LEFT JOIN to include all customers and counts orders grouped by customer, which is correct.
   CREATE VIEW active_customers AS SELECT id, name, total_orders FROM customers; misses orders data.
   CREATE VIEW active_customers AS SELECT c.id, c.name, SUM(o.price) FROM customers c JOIN orders o ON c.id = o.customer_id; sums price but misses GROUP BY and alias.
   CREATE VIEW active_customers AS SELECT c.id, c.name, COUNT(o.order_id) FROM customers c, orders o WHERE c.id = o.customer_id; uses implicit join without GROUP BY, causing incorrect aggregation.

3. Final Answer:

   CREATE VIEW active_customers AS SELECT c.id, c.name, COUNT(o.order_id) AS total_orders FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.id, c.name; -> Option A

4. Quick Check:

   LEFT JOIN + GROUP BY + COUNT = correct aggregation [OK]

Hint: Use LEFT JOIN and GROUP BY to count related rows [OK]

Common Mistakes:

• Forgetting GROUP BY with aggregation
• Using INNER JOIN and missing customers without orders
• Not aliasing aggregated columns