Querying from views in MySQL - Mini Project: Build & Apply

Querying from Views in MySQL

📖 Scenario: You work at a bookstore that keeps track of books and their sales. You want to create a simple way to see the total sales for each book without writing complex queries every time.

🎯 Goal: Create a view that shows each book's title and total sales, then write a query to get all data from this view.

📋 What You'll Learn

Create a table called books with columns book_id (integer), title (varchar), and price (decimal).

Create a table called sales with columns sale_id (integer), book_id (integer), and quantity (integer).

Create a view called book_sales_view that shows title and total sales amount (price * quantity) as total_sales.

Write a query to select all columns from book_sales_view.

💡 Why This Matters

🌍 Real World

Views help simplify repeated complex queries in business reporting, making it easier to get summarized data quickly.

💼 Career

Database developers and analysts use views to create reusable query layers that improve efficiency and maintainability.

1

Create the books and sales tables with sample data

Create a table called books with columns book_id (integer), title (varchar(100)), and price (decimal(5,2)). Insert these exact rows: (1, 'Learn SQL', 29.99), (2, 'Database Basics', 39.99). Then create a table called sales with columns sale_id (integer), book_id (integer), and quantity (integer). Insert these exact rows: (1, 1, 3), (2, 2, 2), (3, 1, 1).

MySQL

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

Need a hint?

Use CREATE TABLE statements for both tables and INSERT INTO to add the rows exactly as given.

2

Create the view book_sales_view

Create a view called book_sales_view that shows each book's title and the total sales amount as total_sales. Calculate total_sales by multiplying price by the sum of quantity sold for each book. Use a JOIN between books and sales and group by title.

MySQL

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

INSERT INTO books (book_id, title, price) VALUES
(1, 'Learn SQL', 29.99),
(2, 'Database Basics', 39.99);

CREATE TABLE sales (
  sale_id INT,
  book_id INT,
  quantity INT
);

INSERT INTO sales (sale_id, book_id, quantity) VALUES
(1, 1, 3),
(2, 2, 2),
(3, 1, 1);

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

Need a hint?

Use CREATE VIEW with a SELECT that joins books and sales, groups by title, and sums the product of price and quantity.

3

Query all data from the view book_sales_view

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

MySQL

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

INSERT INTO books (book_id, title, price) VALUES
(1, 'Learn SQL', 29.99),
(2, 'Database Basics', 39.99);

CREATE TABLE sales (
  sale_id INT,
  book_id INT,
  quantity INT
);

INSERT INTO sales (sale_id, book_id, quantity) VALUES
(1, 1, 3),
(2, 2, 2),
(3, 1, 1);

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

-- Query all data 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

Add an ORDER BY clause to sort the view query by total_sales descending

Modify the query selecting from book_sales_view to order the results by total_sales in descending order.

MySQL

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

INSERT INTO books (book_id, title, price) VALUES
(1, 'Learn SQL', 29.99),
(2, 'Database Basics', 39.99);

CREATE TABLE sales (
  sale_id INT,
  book_id INT,
  quantity INT
);

INSERT INTO sales (sale_id, book_id, quantity) VALUES
(1, 1, 3),
(2, 2, 2),
(3, 1, 1);

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

-- Modify the query to order by total_sales descending
-- Your code here

Need a hint?

Add ORDER BY total_sales DESC to the query selecting from the view.