Now that you have a database server installed, let’s walk through creating your first database and table. I’ll show you how to do this both directly with SQL (recommended for learning) and briefly mention the programmatic approach.

Part 1: Create Your First Database

Step 1: Connect to Your Database Server

Open a terminal and connect using the CLI:

MySQL:

mysql -u root -p

PostgreSQL:

psql -U postgres

Enter the password you set during installation.

Step 2: Create the Database

Once connected, run:

CREATE DATABASE bookstore;

You should see a confirmation like Query OK, 1 row affected.

Step 3: Verify It Was Created

SHOW DATABASES;   -- MySQL
-- \l            -- PostgreSQL

You should see bookstore in the list.

Step 4: Switch to Your New Database

Before creating tables, tell the server which database to work with:

USE bookstore;   -- MySQL
-- \c bookstore  -- PostgreSQL

Part 2: Create Your First Table

A table is where actual data lives. It has columns (fields) and rows (records).

Step 1: Design Your Table

Let’s create a simple book table. Before writing SQL, think about:

• What data do you want to store? (title, author, price, etc.)
• What type is each field? (text, number, date)
• Which field uniquely identifies a row? (the primary key)

Step 2: Write the CREATE TABLE Statement

CREATE TABLE book (
    id             BIGINT       NOT NULL AUTO_INCREMENT,
    isbn           VARCHAR(50)  NOT NULL,
    title          VARCHAR(100) NOT NULL,
    author         VARCHAR(100) NOT NULL,
    published_year INT,
    price          DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    PRIMARY KEY (id)
);

Understanding Each Part

Element	Meaning
id	Column name
BIGINT	Data type — a large integer
AUTO_INCREMENT	Database auto-generates the next number (MySQL syntax)
NOT NULL	This field is required
VARCHAR(100)	Variable-length text, up to 100 characters
DECIMAL(10, 2)	Number with 10 digits total, 2 after the decimal point
DEFAULT 0.00	Default value if none is provided
PRIMARY KEY (id)	Marks id as the unique identifier for each row

PostgreSQL note: Replace BIGINT ... AUTO_INCREMENT with BIGSERIAL or BIGINT GENERATED ALWAYS AS IDENTITY.

Step 3: Verify the Table Was Created

SHOW TABLES;              -- MySQL
DESCRIBE book;            -- MySQL — shows column details

-- \dt                    -- PostgreSQL — list tables
-- \d book                -- PostgreSQL — describe a table

Part 3: Add Some Data

Insert Rows

INSERT INTO book (isbn, title, author, published_year, price)
VALUES
    ('978-0134685991', 'Effective Java',        'Joshua Bloch',    2018, 45.00),
    ('978-0132350884', 'Clean Code',            'Robert C. Martin', 2008, 39.99),
    ('978-0596009205', 'Head First Design Patterns', 'Eric Freeman', 2004, 49.95);

Query Your Data

SELECT * FROM book;

You should see all three rows with their auto-generated id values.

Part 4: Common Beginner Operations

Filter with WHERE

SELECT title, price FROM book WHERE price < 45.00;

Sort with ORDER BY

SELECT title, published_year FROM book ORDER BY published_year DESC;

Update a Row

UPDATE book SET price = 42.00 WHERE isbn = '978-0134685991';

Delete a Row

DELETE FROM book WHERE id = 3;

Part 5: Doing This from Java (Optional)

Since your project uses Java, you can also create databases and tables programmatically through JDBC. The general pattern is:

1. Get a Connection via DriverManager.getConnection(url, user, password)
2. Create a Statement or PreparedStatement
3. Execute your CREATE DATABASE / CREATE TABLE SQL
4. Use try-with-resources so connections close automatically

However, in a modern Spring Data JPA project, you usually don’t create tables manually with JDBC. Instead:

• Hibernate/JPA can auto-generate tables from your @Entity classes (via spring.jpa.hibernate.ddl-auto)
• Flyway or Liquibase manage schema migrations with versioned SQL scripts

But learning the raw SQL first (as shown above) gives you the foundation to understand what these tools do under the hood.

Recommended Next Steps

1. Create the bookstore database and book table using the SQL above
2. Insert 5–10 sample rows
3. Practice SELECT with different WHERE conditions
4. Learn about relationships — create a second table (e.g., author) and link it with a foreign key
5. Explore JOINs to combine data from multiple tables