AUTO_INCREMENT behavior in MySQL - Mini Project: Build & Apply

Understanding AUTO_INCREMENT Behavior in MySQL

📖 Scenario: You are managing a small online store database. You want to create a table to store product information where each product has a unique ID that automatically increases whenever a new product is added.

🎯 Goal: Build a MySQL table called products with an id column that uses AUTO_INCREMENT to assign unique IDs automatically. Insert some products and observe how the IDs increment.

📋 What You'll Learn

Create a table named products with columns id (integer, primary key, auto-increment) and name (varchar).

Set the id column to use AUTO_INCREMENT.

Insert three products with names exactly: 'Pen', 'Notebook', and 'Eraser'.

Query the table to see the id and name of all products.

💡 Why This Matters

🌍 Real World

AUTO_INCREMENT is commonly used in databases to assign unique IDs to records automatically, such as user IDs, order numbers, or product IDs.

💼 Career

Understanding AUTO_INCREMENT helps in designing databases that require unique identifiers without manual input, a fundamental skill for database administrators and backend developers.

1

Create the products table with AUTO_INCREMENT

Write a SQL statement to create a table called products with two columns: id as an integer primary key with AUTO_INCREMENT, and name as a varchar(50).

MySQL

-- Your SQL code here to create the products table with id as AUTO_INCREMENT primary key and name as varchar(50)

Need a hint?

Use CREATE TABLE with id INT AUTO_INCREMENT PRIMARY KEY and name VARCHAR(50).

2

Insert products into the table

Write three SQL INSERT statements to add products with names 'Pen', 'Notebook', and 'Eraser' into the products table. Do not specify the id values.

MySQL

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);
-- Insert 'Pen', 'Notebook', and 'Eraser' into products
-- Your SQL insert statements here

Need a hint?

Use INSERT INTO products (name) VALUES ('ProductName'); for each product.

3

Query the products table to see AUTO_INCREMENT IDs

Write a SQL SELECT statement to retrieve the id and name columns from the products table to see the automatically assigned IDs.

MySQL

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

INSERT INTO products (name) VALUES ('Pen');
INSERT INTO products (name) VALUES ('Notebook');
INSERT INTO products (name) VALUES ('Eraser');

-- Select id and name from products
-- Your SQL select statement here

Need a hint?

Use SELECT id, name FROM products; to see the table contents.

4

Reset AUTO_INCREMENT value

Write a SQL statement to reset the AUTO_INCREMENT counter of the products table to start from 1 again.

MySQL

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

INSERT INTO products (name) VALUES ('Pen');
INSERT INTO products (name) VALUES ('Notebook');
INSERT INTO products (name) VALUES ('Eraser');

SELECT id, name FROM products;

-- Reset AUTO_INCREMENT to 1
-- Your SQL statement here

Need a hint?

Use ALTER TABLE products AUTO_INCREMENT = 1; to reset the counter.