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

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

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

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

Hint

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

Practice

1. What is a view in MySQL?

easy

A. A virtual table based on a stored query

B. A physical table storing data permanently

C. A backup of a database

D. A user account with special permissions

Solution

1. Step 1: Understand the definition of a view

   A view is not a physical table but a saved query that acts like a table.

2. Step 2: Differentiate from other options

   Options A, B, and C describe physical tables, backups, or users, which are not views.

3. Final Answer:

   A virtual table based on a stored query -> Option A

4. Quick Check:

   View = virtual table [OK]

Hint: Remember: Views are saved queries, not real tables [OK]

Common Mistakes:

• Thinking views store data physically
• Confusing views with backups
• Assuming views are user accounts

2. Which of the following is the correct syntax to select all columns from a view named employee_view?

easy

A. SELECT ALL FROM employee_view;

B. SELECT * FROM VIEW employee_view;

C. SELECT * FROM employee_view;

D. GET * FROM employee_view;

Solution

1. Step 1: Recall standard SELECT syntax for views

   Views are queried like tables using SELECT * FROM view_name;

2. Step 2: Check each option's syntax

   SELECT * FROM employee_view; uses correct syntax. Options B, C, and D use invalid keywords or order.

3. Final Answer:

   SELECT * FROM employee_view; -> Option C

4. Quick Check:

   SELECT * FROM view_name; is correct [OK]

Hint: Query views like tables using SELECT * FROM view_name [OK]

Common Mistakes:

• Using VIEW keyword in SELECT statement
• Replacing SELECT with GET
• Using SELECT ALL instead of SELECT *

3. Given a view sales_view defined as SELECT product_id, SUM(quantity) AS total_qty FROM sales GROUP BY product_id, what will the query SELECT * FROM sales_view WHERE total_qty > 100; return?

medium

A. All products with total quantity sold greater than 100

B. All sales records with quantity greater than 100

C. Syntax error because WHERE cannot be used on views

D. Empty result because total_qty is not a column

Solution

1. Step 1: Understand the view definition

   The view groups sales by product_id and sums quantity as total_qty.

2. Step 2: Analyze the query on the view

   The query filters rows where total_qty > 100, so it returns products with total sales over 100.

3. Final Answer:

   All products with total quantity sold greater than 100 -> Option A

4. Quick Check:

   Filtering on view columns works normally [OK]

Hint: Filter views like tables using WHERE on view columns [OK]

Common Mistakes:

• Thinking WHERE can't be used on views
• Confusing total_qty with original quantity column
• Assuming syntax error due to aggregation

4. You have a view customer_view defined as SELECT id, name, email FROM customers; but running SELECT phone FROM customer_view; gives an error. What is the problem?

medium

A. Views cannot be queried for single columns

B. The column phone does not exist in the view

C. You must use SELECT * to query views

D. The view is corrupted and needs to be recreated

Solution

1. Step 1: Check the view's defined columns

   The view only includes id, name, and email columns, no phone column.

2. Step 2: Understand the error cause

   Querying a column not in the view causes an error because it doesn't exist in the virtual table.

3. Final Answer:

   The column phone does not exist in the view -> Option B

4. Quick Check:

   Query columns must exist in view definition [OK]

Hint: Check view columns before selecting them [OK]

Common Mistakes:

• Assuming views allow any column selection
• Thinking views are corrupted if column missing
• Believing SELECT * is mandatory for views

5. You want to create a view active_users_view that shows only users with status = 'active' from the users table. Which query correctly creates this view?

hard

A. CREATE VIEW active_users_view AS SELECT * FROM users WHERE status == 'active';

B. CREATE VIEW active_users_view SELECT * FROM users WHERE status = 'active';

C. CREATE VIEW active_users_view AS SELECT * FROM users HAVING status = 'active';

D. CREATE VIEW active_users_view AS SELECT * FROM users WHERE status = 'active';

Solution

1. Step 1: Recall correct CREATE VIEW syntax

   The syntax requires CREATE VIEW view_name AS SELECT ... WHERE ...

2. Step 2: Check each option's correctness

   CREATE VIEW active_users_view AS SELECT * FROM users WHERE status = 'active'; uses correct syntax with WHERE clause. CREATE VIEW active_users_view SELECT * FROM users WHERE status = 'active'; misses AS keyword. CREATE VIEW active_users_view AS SELECT * FROM users HAVING status = 'active'; wrongly uses HAVING without GROUP BY. CREATE VIEW active_users_view AS SELECT * FROM users WHERE status == 'active'; uses invalid '==' operator.

3. Final Answer:

   CREATE VIEW active_users_view AS SELECT * FROM users WHERE status = 'active'; -> Option D

4. Quick Check:

   CREATE VIEW ... AS SELECT ... WHERE ... is correct [OK]

Hint: Use WHERE, not HAVING, and '=' for conditions in views [OK]

Common Mistakes:

• Omitting AS keyword
• Using HAVING without GROUP BY
• Using '==' instead of '=' in conditions