AUTO_INCREMENT behavior in SQL - Mini Project: Build & Apply

Understanding AUTO_INCREMENT Behavior in SQL

📖 Scenario: You are managing a small online store database. You want to create a table to store product information. Each product should have a unique ID that automatically increases whenever a new product is added, so you don't have to manually assign IDs.

🎯 Goal: Build a SQL table with an AUTO_INCREMENT column for product IDs, insert some products, and observe how the IDs are assigned automatically.

📋 What You'll Learn

Create a table named products with columns id, name, and price

Set the id column as an AUTO_INCREMENT primary key

Insert three products with names and prices but without specifying id

Query the table to see the automatically assigned id values

💡 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 handle unique identifiers efficiently without manual input, a key 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 these columns: id as an integer primary key with AUTO_INCREMENT, name as a VARCHAR(50), and price as a DECIMAL(5,2).

SQL

-- Write the CREATE TABLE statement for products with id as AUTO_INCREMENT primary key
-- Your code here

Need a hint?

Use CREATE TABLE with id INT AUTO_INCREMENT PRIMARY KEY to make the ID auto-increment.

2

Insert products without specifying IDs

Write three SQL INSERT INTO products (name, price) statements to add these products: 'Pen' priced 1.20, 'Notebook' priced 2.50, and 'Eraser' priced 0.80. Do not include the id column in the inserts.

SQL

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(5,2)
);
-- Insert three products without id
-- Your code here

Need a hint?

Use INSERT INTO products (name, price) VALUES (...) without the id column.

3

Query the products table to see AUTO_INCREMENT IDs

Write a SQL SELECT statement to get all columns from the products table to see the automatically assigned id values.

SQL

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(5,2)
);

INSERT INTO products (name, price) VALUES ('Pen', 1.20);
INSERT INTO products (name, price) VALUES ('Notebook', 2.50);
INSERT INTO products (name, price) VALUES ('Eraser', 0.80);

-- Select all columns from products
-- Your code here

Need a hint?

Use SELECT * FROM products; to see all rows and columns.

4

Add a new product and observe AUTO_INCREMENT continues

Write a SQL INSERT INTO products (name, price) statement to add a new product 'Marker' priced 1.50. Then write a SELECT * FROM products statement to see the new product with the next id.

SQL

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(5,2)
);

INSERT INTO products (name, price) VALUES ('Pen', 1.20);
INSERT INTO products (name, price) VALUES ('Notebook', 2.50);
INSERT INTO products (name, price) VALUES ('Eraser', 0.80);

SELECT * FROM products;

-- Insert 'Marker' priced 1.50
-- Select all products again
-- Your code here

Need a hint?

Insert the new product without id and then select all rows again.