Why SELECT retrieves data in MySQL - See It in Action

Why SELECT Retrieves Data

📖 Scenario: You are working with a simple database of a bookstore. You want to see the list of books available along with their authors and prices.

🎯 Goal: Build a simple SQL query using SELECT to retrieve data from a table called books.

📋 What You'll Learn

Create a table called books with columns id, title, author, and price.

Insert three specific book records into the books table.

Write a SELECT query to retrieve all columns from the books table.

Add a condition to retrieve only books priced above 20.

💡 Why This Matters

🌍 Real World

Selecting data is how you get information from databases to show on websites, apps, or reports.

💼 Career

Knowing how SELECT works is essential for any job involving databases, data analysis, or backend development.

1

Create the books table

Write a SQL statement to create a table called books with columns: id as integer primary key, title as text, author as text, and price as decimal(5,2).

MySQL

-- Your code here to create the books table

Need a hint?

Use CREATE TABLE books and define the columns with their types.

2

Insert book records

Insert these three books into the books table: (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99), (2, '1984', 'George Orwell', 22.50), and (3, 'To Kill a Mockingbird', 'Harper Lee', 18.75).

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title TEXT,
  author TEXT,
  price DECIMAL(5,2)
);
-- Your code here to insert the three book records

Need a hint?

Use a single INSERT INTO books statement with multiple rows.

3

Write a SELECT query to retrieve all books

Write a SQL query using SELECT * FROM books to retrieve all columns and all rows from the books table.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title TEXT,
  author TEXT,
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, author, price) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99),
(2, '1984', 'George Orwell', 22.50),
(3, 'To Kill a Mockingbird', 'Harper Lee', 18.75);

-- Your code here to select all columns from books

Need a hint?

Use SELECT * FROM books; to get all data.

4

Add a condition to select books priced above 20

Modify the previous SELECT query to retrieve only books where the price is greater than 20 using WHERE price > 20.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title TEXT,
  author TEXT,
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, author, price) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99),
(2, '1984', 'George Orwell', 22.50),
(3, 'To Kill a Mockingbird', 'Harper Lee', 18.75);

-- Your code here to select books with price greater than 20

Need a hint?

Add WHERE price > 20 to filter the results.