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

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

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

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

Need a hint?

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