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

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

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

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

Hint

Use DEALLOCATE PREPARE stmt; to free resources after execution.

Practice

1. What does the SELECT statement do in MySQL?

easy

A. It retrieves data from a database table.

B. It deletes data from a database table.

C. It updates data in a database table.

D. It creates a new database table.

Solution

1. Step 1: Understand the purpose of SELECT

   The SELECT statement is used to get or read data from a table in a database.

2. Step 2: Compare with other SQL commands

   DELETE removes data, UPDATE changes data, and CREATE makes new tables, so they are different from SELECT.

3. Final Answer:

   It retrieves data from a database table. -> Option A

4. Quick Check:

   SELECT = retrieve data [OK]

Hint: SELECT means get data from a table [OK]

Common Mistakes:

• Confusing SELECT with DELETE or UPDATE
• Thinking SELECT creates tables
• Mixing SELECT with data modification commands

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

easy

A. SELECT users FROM *;

B. SELECT * FROM users;

C. SELECT * users FROM;

D. FROM users SELECT *;

Solution

1. Step 1: Recall the basic SELECT syntax

   The correct syntax to get all columns is: SELECT * FROM table_name;

2. Step 2: Check each option

   SELECT * FROM users; matches the correct syntax. Others have wrong order or misplaced keywords.

3. Final Answer:

   SELECT * FROM users; -> Option B

4. Quick Check:

   SELECT * FROM table_name; is correct [OK]

Hint: SELECT * FROM table_name; gets all columns [OK]

Common Mistakes:

• Swapping SELECT and FROM keywords
• Omitting the FROM keyword
• Placing * in wrong position

3. Given a table products with columns id, name, and price, what will this query return?

SELECT name FROM products;

medium

A. All product names from the products table.

B. All product prices from the products table.

C. All columns from the products table.

D. An error because price column is missing.

Solution

1. Step 1: Understand the SELECT clause

   The query selects only the 'name' column from the products table.

2. Step 2: Check what is returned

   It returns all rows but only the 'name' column values, not price or id.

3. Final Answer:

   All product names from the products table. -> Option A

4. Quick Check:

   SELECT name returns names only [OK]

Hint: SELECT column_name returns that column's data [OK]

Common Mistakes:

• Assuming all columns are returned
• Thinking missing columns cause errors
• Confusing column names in SELECT

4. What is wrong with this query?

SELECT FROM users;

medium

A. FROM keyword should be after WHERE.

B. Table name is incorrect.

C. Missing column names or * after SELECT.

D. SELECT cannot be used with FROM.

Solution

1. Step 1: Check the SELECT syntax

   SELECT must be followed by column names or * to specify what to retrieve.

2. Step 2: Identify the error in the query

   The query has SELECT but no columns or * after it, causing a syntax error.

3. Final Answer:

   Missing column names or * after SELECT. -> Option C

4. Quick Check:

   SELECT needs columns or * [OK]

Hint: Always specify columns or * after SELECT [OK]

Common Mistakes:

• Leaving SELECT empty
• Misplacing FROM keyword
• Thinking FROM is optional

5. You want to see all columns from a table employees but only for employees with the job title 'Manager'. Which query should you use?

hard

A. SELECT * WHERE job_title = 'Manager' FROM employees;

B. SELECT job_title FROM employees WHERE job_title = 'Manager';

C. SELECT * FROM employees;

D. SELECT * FROM employees WHERE job_title = 'Manager';

Solution

1. Step 1: Identify the need to filter rows

   We want all columns but only rows where job_title is 'Manager', so we need a WHERE clause.

2. Step 2: Check query syntax and logic

   SELECT * FROM employees WHERE job_title = 'Manager'; correctly uses SELECT * FROM employees WHERE job_title = 'Manager'; which returns all columns filtered by job title.

3. Final Answer:

   SELECT * FROM employees WHERE job_title = 'Manager'; -> Option D

4. Quick Check:

   Use WHERE to filter rows [OK]

Hint: Use WHERE to filter rows after FROM [OK]

Common Mistakes:

• Omitting WHERE clause for filtering
• Placing WHERE before FROM
• Selecting only job_title instead of all columns