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

Need a 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

Need a 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

Need a 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

Need a hint?

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