Creating views in MySQL - Why You Should Know This

The Big Idea

What if you could save your favorite data filters once and never rewrite them again?

The Scenario

Imagine you have a big spreadsheet with thousands of rows and many columns. Every time you want to see just a few important columns or a specific summary, you have to copy and filter the data manually. This takes a lot of time and can easily lead to mistakes.

The Problem

Manually filtering or copying data is slow and tiring. You might forget to update your filtered data when the original changes. Also, doing the same filtering again and again wastes time and can cause errors if done differently each time.

The Solution

Creating views lets you save a custom filter or summary as a virtual table. You can use this view anytime to get the exact data you want without rewriting the filter. The view updates automatically when the original data changes, saving time and avoiding mistakes.

Before vs After

✗ Before

SELECT name, salary FROM employees WHERE department = 'Sales'; -- run this every time

✓ After

CREATE VIEW sales_team AS SELECT name, salary FROM employees WHERE department = 'Sales'; -- use sales_team like a table

What It Enables

Views make it easy to reuse complex queries and keep data consistent and up-to-date without extra work.

Real Life Example

A company wants to see only active customers with recent orders. Instead of writing the same query every time, they create a view called 'active_customers' that always shows the latest filtered list.

Key Takeaways

Manual filtering is slow and error-prone.

Views save and reuse queries like virtual tables.

Views update automatically with the original data.

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