Why views simplify complex queries in MySQL - See It in Action

Why Views Simplify Complex Queries

📖 Scenario: You work at a bookstore that keeps track of books, authors, and sales. The database has multiple tables, and some queries to get sales information are long and complicated.To make it easier for the staff to get sales reports, you will create a view that simplifies these complex queries.

🎯 Goal: Create a view that combines book titles, author names, and total sales so that staff can easily query sales data without writing complex joins every time.

📋 What You'll Learn

Create tables books, authors, and sales with specified columns.

Insert sample data into these tables.

Create a view called book_sales_view that joins these tables and calculates total sales per book.

Write a simple query to select all data from book_sales_view.

💡 Why This Matters

🌍 Real World

Views are used in real databases to make complex data easier to access for users who do not need to know the full database structure.

💼 Career

Database developers and analysts use views to simplify reporting and improve query maintainability.

1

Create the initial tables and insert data

Create three tables called books, authors, and sales with these columns:
- books: book_id (INT), title (VARCHAR), author_id (INT)
- authors: author_id (INT), name (VARCHAR)
- sales: sale_id (INT), book_id (INT), quantity (INT)
Insert these exact rows:
books: (1, 'Learn SQL', 1), (2, 'Mastering MySQL', 2)
authors: (1, 'Alice'), (2, 'Bob')
sales: (1, 1, 10), (2, 2, 5), (3, 1, 7)

MySQL

-- Create tables books, authors, sales and insert data
-- Your code here

Hint

Use CREATE TABLE statements with the exact column names and types. Then use INSERT INTO with the exact values.

2

Define the view to simplify sales queries

Create a view called book_sales_view that joins books, authors, and sales tables. The view should show:
- title from books
- name from authors
- total quantity sold per book as total_sales
Use GROUP BY on books.book_id.

MySQL

CREATE TABLE books (book_id INT, title VARCHAR(100), author_id INT);
CREATE TABLE authors (author_id INT, name VARCHAR(100));
CREATE TABLE sales (sale_id INT, book_id INT, quantity INT);

INSERT INTO books VALUES (1, 'Learn SQL', 1), (2, 'Mastering MySQL', 2);
INSERT INTO authors VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO sales VALUES (1, 1, 10), (2, 2, 5), (3, 1, 7);

-- Create the view book_sales_view here
-- Your code here

Hint

Use CREATE VIEW with a SELECT that joins the tables and groups by book ID.

3

Query the view to get simplified sales data

Write a query to select all columns from the view book_sales_view.

MySQL

CREATE TABLE books (book_id INT, title VARCHAR(100), author_id INT);
CREATE TABLE authors (author_id INT, name VARCHAR(100));
CREATE TABLE sales (sale_id INT, book_id INT, quantity INT);

INSERT INTO books VALUES (1, 'Learn SQL', 1), (2, 'Mastering MySQL', 2);
INSERT INTO authors VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO sales VALUES (1, 1, 10), (2, 2, 5), (3, 1, 7);

CREATE VIEW book_sales_view AS
SELECT b.title, a.name, SUM(s.quantity) AS total_sales
FROM books b
JOIN authors a ON b.author_id = a.author_id
JOIN sales s ON b.book_id = s.book_id
GROUP BY b.book_id, b.title, a.name;

-- Select all from book_sales_view
-- Your code here

Hint

Use a simple SELECT * FROM book_sales_view to get all data from the view.

4

Explain how the view simplifies queries

Add a comment explaining why using the view book_sales_view makes querying sales data easier compared to writing the full join and aggregation every time.

MySQL

CREATE TABLE books (book_id INT, title VARCHAR(100), author_id INT);
CREATE TABLE authors (author_id INT, name VARCHAR(100));
CREATE TABLE sales (sale_id INT, book_id INT, quantity INT);

INSERT INTO books VALUES (1, 'Learn SQL', 1), (2, 'Mastering MySQL', 2);
INSERT INTO authors VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO sales VALUES (1, 1, 10), (2, 2, 5), (3, 1, 7);

CREATE VIEW book_sales_view AS
SELECT b.title, a.name, SUM(s.quantity) AS total_sales
FROM books b
JOIN authors a ON b.author_id = a.author_id
JOIN sales s ON b.book_id = s.book_id
GROUP BY b.book_id, b.title, a.name;

SELECT * FROM book_sales_view;

-- Add a comment explaining why views simplify complex queries
-- Your code here

Hint

Write a comment starting with -- that explains how views hide complexity and make querying easier.

Practice

1. What is the main benefit of using a VIEW in MySQL?

easy

A. It increases the speed of all database operations automatically.

B. It permanently stores data like a table does.

C. It simplifies complex queries by saving them as a named object.

D. It deletes duplicate rows from tables automatically.

Solution

1. Step 1: Understand what a VIEW is

   A VIEW is a saved SQL query that acts like a virtual table.

2. Step 2: Identify the main benefit of a VIEW

   It helps by simplifying complex queries into a single named object you can query easily.

3. Final Answer:

   It simplifies complex queries by saving them as a named object. -> Option C

4. Quick Check:

   Views simplify queries = B [OK]

Hint: Views save complex queries as simple names [OK]

Common Mistakes:

• Thinking views store data permanently
• Assuming views speed up all queries automatically
• Confusing views with tables

2. Which of the following is the correct syntax to create a view named employee_view that selects all columns from employees?

easy

A. CREATE employee_view VIEW AS SELECT * FROM employees;

B. CREATE VIEW employee_view AS SELECT * FROM employees;

C. VIEW CREATE employee_view SELECT * FROM employees;

D. CREATE VIEW employee_view SELECT * 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 * FROM employees; matches the correct syntax exactly.

3. Final Answer:

   CREATE VIEW employee_view AS SELECT * FROM employees; -> Option B

4. Quick Check:

   CREATE VIEW ... AS SELECT ... = A [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 view CREATE VIEW recent_orders AS SELECT order_id, customer_id FROM orders WHERE order_date > '2024-01-01';, what will the query SELECT * FROM recent_orders; return?

medium

A. Only orders with order_date after January 1, 2024.

B. Only customer IDs without order IDs.

C. An error because views cannot filter data.

D. All orders from the orders table regardless of date.

Solution

1. Step 1: Understand the view definition

   The view selects order_id and customer_id from orders where order_date is after 2024-01-01.

2. Step 2: Analyze the SELECT query on the view

   Querying the view returns only rows matching the filter condition in the view.

3. Final Answer:

   Only orders with order_date after January 1, 2024. -> Option A

4. Quick Check:

   View filters data = D [OK]

Hint: View returns filtered data as defined in its SELECT [OK]

Common Mistakes:

• Assuming view returns all table data
• Thinking views cannot filter data
• Confusing columns returned

4. You created a view with CREATE VIEW top_customers AS SELECT customer_id, product_id, SUM(amount) AS total FROM sales GROUP BY customer_id; but get an error when running SELECT * FROM top_customers;. What is the likely cause?

medium

A. MySQL requires all non-aggregate columns in SELECT to be in GROUP BY.

B. The view lacks a GROUP BY clause.

C. The view is missing a WHERE clause.

D. The view is missing an alias for the SUM() column.

Solution

1. Step 1: Check the view definition for GROUP BY rules

   MySQL requires all non-aggregated columns in SELECT to appear in GROUP BY.

2. Step 2: Identify if the view follows this rule

   customer_id is in GROUP BY, SUM(amount) is aggregated, but product_id is neither, violating the rule.

3. Step 3: Consider MySQL SQL mode

   If ONLY_FULL_GROUP_BY mode is enabled, all selected columns must be in GROUP BY or aggregated.

4. Final Answer:

   MySQL requires all non-aggregate columns in SELECT to be in GROUP BY. -> Option A

5. Quick Check:

   GROUP BY rules cause error = C [OK]

Hint: Check GROUP BY and ONLY_FULL_GROUP_BY mode [OK]

Common Mistakes:

• Ignoring SQL mode restrictions
• Assuming missing WHERE causes error
• Confusing alias with error cause

5. You want to create a view that shows each product's total sales and average price from a complex join of products and sales. Which approach best uses views to simplify this?

hard

A. Create a view only for products and calculate sales totals in application code.

B. Write one huge query without views to get totals and averages directly.

C. Create separate views for products and sales tables without joins or aggregation.

D. Create one view joining products and sales, then another view aggregating totals and averages from the first view.

Solution

1. Step 1: Understand the problem complexity

   Calculating totals and averages from joined tables is complex and benefits from breaking down.

2. Step 2: Use views to simplify stepwise

   Create a view for the join, then another view to aggregate results, making queries easier to manage.

3. Final Answer:

   Create one view joining products and sales, then another view aggregating totals and averages from the first view. -> Option D

4. Quick Check:

   Use layered views to simplify complex queries = A [OK]

Hint: Layer views: join first, then aggregate [OK]

Common Mistakes:

• Trying to do everything in one query without views
• Not joining tables before aggregation
• Calculating aggregates outside database