Creating a Product Information Table

Introduction

In this project, you will learn how to create a database and a table to store product information. This project aims to provide you with a hands-on experience in working with MySQL, one of the most popular relational database management systems.

👀 Preview

🎯 Tasks

In this project, you will learn:

How to start the MySQL server and access the MySQL command-line interface
How to create a new database and switch to it
How to create a new table with specific columns and properties
How to verify the table creation and its structure

🏆 Achievements

After completing this project, you will be able to:

Understand the basic concepts of database management
Create and manage databases and tables using SQL commands
Familiarize yourself with the MySQL command-line interface
Apply your knowledge to build simple database applications

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("`MySQL`")) -.-> mysql/BasicKeywordsandStatementsGroup(["`Basic Keywords and Statements`"]) sql(("`SQL`")) -.-> sql/DataManipulationandQueryingGroup(["`Data Manipulation and Querying`"]) mysql(("`MySQL`")) -.-> mysql/DatabaseFunctionsandDataTypesGroup(["`Database Functions and Data Types`"]) sql(("`SQL`")) -.-> sql/BasicSQLCommandsGroup(["`Basic SQL Commands`"]) sql(("`SQL`")) -.-> sql/DataDefinitionandIntegrityGroup(["`Data Definition and Integrity`"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/source("`External Code Execution`") sql/DataManipulationandQueryingGroup -.-> sql/in("`IN clause`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_table("`Table Creation`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("`Database Selection`") mysql/DatabaseFunctionsandDataTypesGroup -.-> mysql/int("`Integer Type`") sql/BasicSQLCommandsGroup -.-> sql/create_table("`CREATE TABLE statements`") sql/DataDefinitionandIntegrityGroup -.-> sql/data_types("`Data Types`") sql/DataDefinitionandIntegrityGroup -.-> sql/constraints("`Constraints`") subgraph Lab Skills mysql/source -.-> lab-301323{{"`Creating Product Information Table`"}} sql/in -.-> lab-301323{{"`Creating Product Information Table`"}} mysql/create_table -.-> lab-301323{{"`Creating Product Information Table`"}} mysql/use_database -.-> lab-301323{{"`Creating Product Information Table`"}} mysql/int -.-> lab-301323{{"`Creating Product Information Table`"}} sql/create_table -.-> lab-301323{{"`Creating Product Information Table`"}} sql/data_types -.-> lab-301323{{"`Creating Product Information Table`"}} sql/constraints -.-> lab-301323{{"`Creating Product Information Table`"}} end

Start MySQL and Access the Database

In this step, you will learn how to start the MySQL server and access the database.

Start the MySQL server using the following command:

sudo service mysql start

Access the MySQL command-line interface using the following command:

sudo mysql

This will allow you to interact with the MySQL database directly from the command line.

Create a New Database and Table

In this step, you will learn how to create a new database and a table to store product information.

Create a new database named product using the following SQL statement:

CREATE SCHEMA product;

Switch to the product database using the following SQL statement:

USE product;

Create a new table named product_info with the following columns and properties:

CREATE TABLE product_info (
  id INT NOT NULL,
  name VARCHAR(10),
  price DECIMAL(8, 2),
  intro VARCHAR(256)
);

This table will store the product information, including the product ID, name, price, and a brief introduction.

Verify the Table Creation

In this step, you will learn how to verify that the product_info table has been created correctly.

Use the following SQL statement to describe the structure of the product_info table:

DESC product_info;

The output should look similar to the following:

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(10)  | YES  |     | NULL    |       |
| price | decimal(8,2) | YES  |     | NULL    |       |
| intro | varchar(256) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

This confirms that the product_info table has been created with the correct columns and properties.

Congratulations! You have successfully created a new database and a table to store product information. In the next steps, you will learn how to insert, update, and retrieve data from the product_info table.

Summary

Congratulations! You have completed this project. You can practice more labs in LabEx to improve your skills.

Creating Product Information Table