Search⌘ K
AI Features
Home
Courses
Getting Started with SQL and Relational Databases

Insert Product Data

Explore how to insert product data into a MySQL table with automatic ID assignment, then learn to count total and filtered records using SQL queries to verify your insertions and manage data effectively.

We'll cover the following...

Insert data

Now that the table is ready, let’s insert a new product:

MySQL
insert into products(name, price) values ('Game of Thrones-S01-DVD', 50.0);

In the insert statement above, we first specify the columns we’ll give values for. Then, we provide the values themselves and get a Succeeded response because there’s nothing to display.

Let’s double-check that the product has been created:

MySQL
select * from products;

When we run the code above, we get the following output:

+----+-----------------------------+-------+
| id | name                        | price |
+----+-----------------------------+-------+
| 1  | Game of Thrones-S01-DVD     | 50.00 |
+----+-----------------------------+-------+
1 row in set(0.01 sec)

The product has been created. Its id has been set to 1 automatically. The price is a real number with two decimal digits.

Let’s create one more product:

MySQL
insert into products(name, price) values ('Of Mice and Men', 19.80);

We get the Succeeded response by running the above code. We see the following structure:

+----+-----------------------------+-------+
| id | name                        | price |
+----+-----------------------------+-------+
| 1  | Game of Thrones-S01-DVD     | 50.00 |
| 2  | Of Mice and Men             | 19.80 |
+----+-----------------------------+-------+
1 row in set(0.01 sec)

The new product has an id of 2 assigned automatically. Let’s insert one more product:

MySQL
insert into products(name, price) values ('A Nice Story', 5.00);

We get the Succeeded response by running the above code. We see the following structure:

+----+-----------------------------+-------+
| id | name                        | price |
+----+-----------------------------+-------+
| 1  | GameofThrones-S01-DVD       | 50.00 |
| 2  | Of Mice and Men             | 19.80 |
| 3  | A Nice Story                | 5.00  |
+----+-----------------------------+-------+
1 row in set(0.01 sec)

Count total products

We now have three products in our product catalog.

Note: Counting can be used to verify the insert results for large tables.

Suppose that we have a very long product catalog, and we want to count the number of products in our catalog. We can do that with the following command:

1 mysql> select count(*) from products;
2 +----------+
3 | count(*) |
4 +----------+
5 |    3     |
6 +----------+
7 1 row in set(0.00 sec)

What if we want to count the products that have price greater than 10?

1 mysql> select count(*) from products where price > 10;
2 +----------+
3 | count(*) |
4 +----------+
5 |     2    |
6 +----------+
7 1 row in set(0.01 sec)

This returned 2 because we have only two products with price greater than 10.