Creating views in MySQL - Try It Yourself

Creating Views in MySQL

📖 Scenario: You work at a bookstore that keeps track of books and their sales in a database. You want to create a simple way to see the best-selling books without writing complex queries every time.

🎯 Goal: Build a MySQL view named best_sellers that shows the book_id, title, and total_sales for books with more than 100 sales.

📋 What You'll Learn

Create a table named books with columns book_id (integer), title (varchar), and sales (integer).

Insert exactly three books with these values: (1, 'Learn SQL', 150), (2, 'Python Basics', 90), (3, 'Database Design', 200).

Create a view named best_sellers that selects book_id, title, and sales as total_sales from books where sales is greater than 100.

💡 Why This Matters

🌍 Real World

Views help simplify complex queries and provide easy access to filtered or summarized data in business databases.

💼 Career

Database developers and analysts often create views to improve data accessibility and maintainability in real projects.

1

Create the books table and insert data

Create a table called books with columns book_id as INT, title as VARCHAR(100), and sales as INT. Then insert these three rows exactly: (1, 'Learn SQL', 150), (2, 'Python Basics', 90), and (3, 'Database Design', 200).

MySQL

-- Create the books table and insert data
-- Your code here

Hint

Use CREATE TABLE to make the table, then INSERT INTO to add the rows.

2

Set the sales threshold

Create a variable or setting named sales_threshold and set it to 100. This will be used to filter best-selling books.

MySQL

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100),
  sales INT
);

INSERT INTO books (book_id, title, sales) VALUES
(1, 'Learn SQL', 150),
(2, 'Python Basics', 90),
(3, 'Database Design', 200);

-- Set the sales threshold
-- Your code here

Hint

Use SET @sales_threshold = 100; to create a variable in MySQL.

3

Create the best_sellers view

Create a view named best_sellers that selects book_id, title, and sales as total_sales from the books table where sales is greater than the variable @sales_threshold.

MySQL

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100),
  sales INT
);

INSERT INTO books (book_id, title, sales) VALUES
(1, 'Learn SQL', 150),
(2, 'Python Basics', 90),
(3, 'Database Design', 200);

SET @sales_threshold = 100;

-- Create the best_sellers view
-- Your code here

Hint

Use CREATE VIEW view_name AS SELECT ... syntax and filter with WHERE sales > @sales_threshold.

4

Verify the view structure

Add a comment line that describes the view best_sellers as showing books with sales greater than 100. This completes the database artifact.

MySQL

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100),
  sales INT
);

INSERT INTO books (book_id, title, sales) VALUES
(1, 'Learn SQL', 150),
(2, 'Python Basics', 90),
(3, 'Database Design', 200);

SET @sales_threshold = 100;

CREATE VIEW best_sellers AS
SELECT book_id, title, sales AS total_sales
FROM books
WHERE sales > @sales_threshold;

-- Add a comment describing the view
-- Your code here

Hint

Use a SQL comment starting with -- to describe the view.

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