First query execution in MySQL - Mini Project: Build & Apply

First query execution

📖 Scenario: You are working with a small store database. You want to see the list of products available in the store.

🎯 Goal: Write a simple SQL query to select all columns from the products table.

📋 What You'll Learn

Create a table called products with columns id, name, and price

Insert three products with exact values: (1, 'Pen', 1.20), (2, 'Notebook', 2.50), (3, 'Eraser', 0.80)

Write a SQL query to select all columns from the products table

💡 Why This Matters

🌍 Real World

Selecting data from a database is a common task in many applications like online stores, inventory systems, and more.

💼 Career

Knowing how to write and execute basic SQL queries is essential for database administrators, backend developers, and data analysts.

1

Create the products table and insert data

Create a table called products with columns id (integer), name (text), and price (decimal). Then insert these exact rows: (1, 'Pen', 1.20), (2, 'Notebook', 2.50), and (3, 'Eraser', 0.80).

MySQL

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

Need a hint?

Use CREATE TABLE to make the table, then INSERT INTO to add rows.

2

Set up a query to select all products

Write a SQL query and assign it to a variable called query that selects all columns from the products table.

MySQL

CREATE TABLE products (
  id INT,
  name VARCHAR(50),
  price DECIMAL(5,2)
);

INSERT INTO products (id, name, price) VALUES
(1, 'Pen', 1.20),
(2, 'Notebook', 2.50),
(3, 'Eraser', 0.80);

-- Write the query below
SET @query = ''; -- Your code here

Need a hint?

Use SELECT * FROM products to get all columns and rows.

3

Execute the query

Use the PREPARE statement to prepare the query from the variable @query with the name stmt. Then execute the prepared statement stmt.

MySQL

CREATE TABLE products (
  id INT,
  name VARCHAR(50),
  price DECIMAL(5,2)
);

INSERT INTO products (id, name, price) VALUES
(1, 'Pen', 1.20),
(2, 'Notebook', 2.50),
(3, 'Eraser', 0.80);

SET @query = 'SELECT * FROM products';

-- Prepare and execute the query
-- Your code here

Need a hint?

Use PREPARE stmt FROM @query; and then EXECUTE stmt; to run the query.

4

Clean up by deallocating the prepared statement

Deallocate the prepared statement named stmt using the DEALLOCATE PREPARE statement.

MySQL

CREATE TABLE products (
  id INT,
  name VARCHAR(50),
  price DECIMAL(5,2)
);

INSERT INTO products (id, name, price) VALUES
(1, 'Pen', 1.20),
(2, 'Notebook', 2.50),
(3, 'Eraser', 0.80);

SET @query = 'SELECT * FROM products';

PREPARE stmt FROM @query;
EXECUTE stmt;

-- Deallocate the prepared statement
-- Your code here

Need a hint?

Use DEALLOCATE PREPARE stmt; to free resources after execution.