MariaDB Tutorial / Create a Product Table, Insert Data, and Query It

Create a products table with CREATE TABLE, add three rows with INSERT, and read them with SELECT. This complete exercise uses shopcourse, explicit column types, and ordered results so that you can check both the table structure and the saved values.

Prepare a fresh practice table

First follow the database-creation guide and connect as the learning account. Check the selected database and whether the table already exists:

USE shopcourse;
SELECT DATABASE() AS current_database;
SHOW TABLES LIKE 'products';

This exercise assumes shopcourse exists and products does not. If the table is present, inspect its contents and purpose before continuing. The example deliberately does not silently replace an existing table. A dedicated tutorial database helps keep practice work separate from unrelated data.

Choose the product columns and constraints

DECIMAL(10, 2) allows ten digits in total, with two to the right of the decimal point—not ten integer digits plus two fractional digits. It is suitable for exact decimal amounts. For this exercise the prices are sample values in one consistent unit, not claims about actual retail prices.

NOT NULL requires a value rather than a missing-value marker. A stock value of zero is a known quantity and differs from NULL. DEFAULT 0 supplies zero when that column is omitted from an insert. Range validation and responses to invalid values also depend on data types and SQL mode; these introductory constraints are not a complete business-rule system. Categories are strings here, with relational modeling introduced separately later.

Run the complete create-and-insert example

Save this whole example as UTF-8 in lesson06-products.sql. Execute it with the full local path using the SQL-file instructions, or enter its statements in order:

USE shopcourse;

CREATE TABLE products (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  PRIMARY KEY (product_id)
) ENGINE=InnoDB
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

INSERT INTO products (product_name, category, price, stock)
VALUES
  ('Notebook', 'Stationery', 3500.00, 25),
  ('Mug', 'Home', 12000.00, 8),
  ('Desk lamp', 'Home', 28000.00, 0);

SELECT product_id, product_name, category, price, stock
FROM products
ORDER BY product_id;

SELECT COUNT(*) AS product_count FROM products;

The column list after INSERT INTO products makes the meaning of each value explicit. String literals use single quotes; numeric values do not need them. product_id is omitted because the server generates it. ENGINE=InnoDB and the character-set clauses explicitly define the storage engine and text defaults. The CREATE TABLE guide and INSERT reference describe the syntax.

If an error occurs while sourcing the file, --abort-source-on-error stops subsequent input. It does not undo a table or rows already created. Read the error and inspect what exists before repeating the file.

Check the rows and table definition

On a newly created table with its first three inserts, the ordered query should return:

The final count should be 3. The identifiers 1–3 assume a fresh table and no earlier inserts; do not expect every previously used table to generate that same sequence. Inspect the stored definition as well:

USE shopcourse;
DESCRIBE products;
SHOW CREATE TABLE products;

DESCRIBE summarizes columns, nullability, defaults, and keys. SHOW CREATE TABLE gives the table definition, which the server may normalize differently between versions. ORDER BY product_id makes the row order intentional. Without ORDER BY, do not depend on the observed order being stable.

Compare queries by changing one condition

Read products whose sample price is at least 10000:

SELECT product_id, product_name, price
FROM products
WHERE price >= 10000
ORDER BY product_id;

The result contains Mug and Desk lamp. Change only 10000 to 20000 and repeat: only Desk lamp remains. Restore 10000 afterward to return to the original comparison.

Now find products with no stock:

SELECT product_id, product_name, stock
FROM products
WHERE stock = 0
ORDER BY product_id;

This returns Desk lamp. Change only stock = 0 to stock > 0 to return Notebook and Mug, then restore the original condition. These SELECT statements change the rows included in a result, not the stored stock values. The SELECT reference describes filtering and ordering.

Handle reruns and reset only verified tutorial data

• Table already exists: CREATE TABLE does not automatically overwrite products. Inspect the existing table before deciding what to do.
• More than three rows: repeating the INSERT adds another set of products. A primary key prevents duplicate identifiers, not repeated product names.
• Unexpected data: check the server endpoint and SELECT DATABASE(), not just the table name.
• Text problems: verify the file’s encoding and the connection and column character sets.

If you want to repeat the entire exercise, first confirm that this is the tutorial table and that it contains no data you need to keep. Back up any needed data, then inspect the target:

USE shopcourse;
SELECT DATABASE() AS current_database;
SELECT COUNT(*) AS rows_to_remove FROM products;

Only after confirming that removing this particular tutorial table is appropriate, run:

DROP TABLE shopcourse.products;

Then rerun the full file to create a fresh table and its three rows. DROP TABLE removes both the structure and the data and is not reversed by an ordinary ROLLBACK. Deleting rows alone need not reset the generated identifier sequence. Keep the reset scoped to this verified table; do not expand it to all tables or the entire database. See the DROP TABLE guide for its behavior.