Creating views in MySQL - Quick Revision & Summary

Recall & Review

beginner

What is a view in a database?

A view is like a saved query that looks like a table. It shows data from one or more tables but does not store data itself.

beginner

How do you create a simple view in MySQL?

Use the syntax:
CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;

intermediate

Can you update data through a view?

Sometimes yes, if the view is simple and directly maps to one table. Complex views with joins or aggregations usually cannot be updated.

beginner

Why use views instead of tables?

Views help simplify complex queries, hide sensitive data, and provide a consistent interface to data without duplicating it.

intermediate

What happens if you drop a table that a view depends on?

The view will stop working because it depends on that table's data. You must drop or recreate the view after dropping the table.

Which SQL command creates a view?

A SELECT VIEW

B CREATE TABLE

C CREATE VIEW

D INSERT VIEW

The CREATE VIEW command is used to create a view in SQL.

A view stores data separately from the tables it uses. True or False?

A True

B Depends on the database

C Only sometimes

D False

Views do not store data themselves; they show data from underlying tables.

Which of these is a benefit of using views?

A Simplify complex queries

B Store large amounts of data

C Replace tables permanently

D Increase database size

Views simplify complex queries by saving them as reusable virtual tables.

Can you create a view that joins multiple tables?

A Yes

B No

C Only in MySQL 8+

D Only if tables have same columns

Views can include joins from multiple tables to combine data.

What happens if you try to update data through a complex view with joins?

A Update always succeeds

B Update usually fails

C View automatically converts to table

D Data is duplicated

Complex views with joins usually cannot be updated directly.

Explain what a view is and why you might use one in a database.

Think about how views help you see data without copying it.

Describe the basic steps and syntax to create a view in MySQL.

Remember the command starts with CREATE VIEW followed by a SELECT query.

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