Creating views in MySQL - Visual Walkthrough

Concept Flow - Creating views

Write SELECT query

↓

Create view with CREATE VIEW

↓

View stored in database

↓

Query view like a table

↓

Get results from view

You write a SELECT query, save it as a view with CREATE VIEW, then query the view like a table to get results.

Execution Sample

MySQL

CREATE VIEW top_customers AS
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;

SELECT * FROM top_customers;

This creates a view named top_customers showing customers who spent more than 1000, then selects all from it.

Execution Table

Step	Action	Query/Command	Result/State
1	Write SELECT query	SELECT customer_id, SUM(amount) AS total_spent FROM orders GROUP BY customer_id HAVING SUM(amount) > 1000;	Query ready to create view
2	Create view	CREATE VIEW top_customers AS [SELECT query]	View 'top_customers' stored in database
3	Query view	SELECT * FROM top_customers;	Returns rows of customers with total_spent > 1000
4	Exit	No more commands	Execution ends

💡 No more commands to execute, view creation and query complete

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
top_customers	Not defined	Defined as SELECT query	Stored as view in database	Queried to return result rows	View exists for future queries

Key Moments - 3 Insights

Why can't I see data immediately after CREATE VIEW?

CREATE VIEW only saves the query, it does not show data until you run SELECT on the view as shown in step 3 of execution_table.

Can I update data directly in a view?

Views are like saved queries; you cannot update data through them unless the view is updatable, which depends on the query. Here, the view is read-only as it uses aggregation.

What happens if the underlying table changes?

The view always shows current data from the underlying tables when queried, so changes in 'orders' affect the view results dynamically.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result after step 2?

A The view returns rows of customers

B The view 'top_customers' is stored in the database

C The SELECT query is written but not saved

D The execution ends

💡 Hint

Check the 'Result/State' column in row with Step 2 in execution_table

At which step do we get the actual data rows from the view?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look for when the SELECT * FROM top_customers query runs in execution_table

If the orders table changes after creating the view, what happens when you query the view?

A The view shows updated data from the orders table

B The view shows old data from when it was created

C The view breaks and shows error

D The view needs to be recreated to update

💡 Hint

Refer to key_moments about underlying table changes affecting view results

Concept Snapshot

CREATE VIEW view_name AS SELECT ...;
- Saves a SELECT query as a virtual table.
- Query the view like a normal table.
- View shows current data from underlying tables.
- Views can simplify complex queries.
- Not all views are updatable.

Full Transcript

Creating views in MySQL involves writing a SELECT query and saving it as a view using CREATE VIEW. The view acts like a virtual table that stores the query, not the data. When you query the view, it runs the saved SELECT query on the current data in the underlying tables and returns the results. Views help simplify complex queries and can be reused easily. However, views do not store data themselves and may not allow direct updates depending on the query. Changes in the underlying tables are reflected in the view results dynamically.

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