Creating and Populating a Database

9 min read•Learning SQL

Creating and Populating a Database

Now that you understand the relational model, let's create an actual database. This chapter covers the Data Definition Language (DDL) statements used to create and modify database structures, along with the basics of inserting data.

Creating a Database

Before creating tables, you need a database to hold them. The syntax varies slightly between database systems:

SQL

-- MySQL / PostgreSQL
CREATE DATABASE bookstore;
 
-- Then connect to it
USE bookstore;  -- MySQL
\c bookstore   -- PostgreSQL command-line

Note

SQLite doesn't require explicit database creation—opening a connection to a file creates the database automatically.

Data Types

Every column in a table must have a data type that defines what kind of values it can store. Here are the most common types:

Character Types

Type	Description	Example
`CHAR(n)`	Fixed-length string, padded with spaces	`CHAR(2)` for state codes
`VARCHAR(n)`	Variable-length string up to n characters	`VARCHAR(100)` for names
`TEXT`	Variable-length string, very large capacity	Blog post content

SQL

-- Use CHAR for fixed-length data like country codes
country_code CHAR(2)        -- Always 2 characters: 'US', 'CA', 'UK'
 
-- Use VARCHAR for variable-length data
email VARCHAR(255)          -- Email addresses vary in length

Numeric Types

Type	Description	Range/Precision
`INT`/`INTEGER`	Whole numbers	-2 billion to 2 billion
`SMALLINT`	Smaller whole numbers	-32,768 to 32,767
`BIGINT`	Very large whole numbers	Extremely large range
`DECIMAL(p,s)`	Exact decimal numbers	p digits, s after decimal
`FLOAT`/`REAL`	Approximate floating-point	Scientific calculations

SQL

-- Use INT for IDs and counts
customer_id INT
quantity INT
 
-- Use DECIMAL for money (never use FLOAT for currency!)
price DECIMAL(10,2)         -- Up to 99999999.99
tax_rate DECIMAL(5,4)       -- Up to 9.9999 (99.99%)

Warning

Never use FLOAT or REAL for monetary values. Floating-point arithmetic can introduce tiny rounding errors that accumulate over time. Use DECIMAL instead.

Date and Time Types

Type	Description	Example
`DATE`	Date only (no time)	'2024-01-15'
`TIME`	Time only (no date)	'14:30:00'
`DATETIME`	Date and time combined	'2024-01-15 14:30:00'
`TIMESTAMP`	Date/time, often with timezone	'2024-01-15 14:30:00+00'

SQL

birth_date DATE
appointment_time TIME
created_at TIMESTAMP
order_datetime DATETIME

Boolean Type

SQL

-- Boolean values (true/false)
is_active BOOLEAN           -- TRUE, FALSE, or NULL
is_verified BOOLEAN DEFAULT FALSE

Note

MySQL uses TINYINT(1) for boolean values, where 0 is false and 1 is true. PostgreSQL has a native BOOLEAN type.

Creating Tables

The CREATE TABLE statement defines a new table with its columns and constraints:

SQL

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Let's break down each part:

Column Definitions

Each column needs a name and data type:

SQL

column_name DATA_TYPE [constraints]

Common Constraints

Constraints enforce rules on your data:

Constraint	Purpose
`PRIMARY KEY`	Uniquely identifies each row
`NOT NULL`	Column cannot contain NULL values
`UNIQUE`	All values in column must be different
`DEFAULT`	Provides a default value if none specified
`CHECK`	Ensures values meet a condition
`FOREIGN KEY`	Links to another table's primary key

Table with Multiple Constraints

SQL

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    stock_quantity INT DEFAULT 0 CHECK (stock_quantity >= 0),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Auto-Incrementing Primary Keys

Most databases can automatically generate unique IDs:

SQL

-- MySQL
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE NOT NULL
);
 
-- PostgreSQL
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL
);
 
-- Standard SQL (PostgreSQL, others)
CREATE TABLE orders (
    order_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_date DATE NOT NULL
);

Foreign Key Constraints

Foreign keys maintain referential integrity—ensuring that relationships between tables remain valid.

SQL

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2),
 
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

This constraint ensures:

You cannot insert an order for a non-existent customer
You cannot delete a customer who has orders (by default)

Foreign Key Actions

You can specify what happens when the referenced row is updated or deleted:

SQL

CREATE TABLE order_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL,
 
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON DELETE RESTRICT
);

Action	Behavior
`CASCADE`	Delete/update child rows automatically
`RESTRICT`	Prevent deletion if children exist
`SET NULL`	Set foreign key to NULL when parent deleted
`SET DEFAULT`	Set foreign key to default value

Inserting Data

The INSERT statement adds new rows to a table:

Basic Insert

SQL

INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES (1, 'John', 'Smith', 'john.smith@email.com');

Multiple Rows

SQL

INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES
    (2, 'Sarah', 'Johnson', 'sarah.j@email.com'),
    (3, 'Michael', 'Williams', 'm.williams@email.com'),
    (4, 'Emily', 'Brown', 'emily.b@email.com');

Omitting the Column List

If you provide values for all columns in order, you can omit the column list:

SQL

-- Risky: depends on column order never changing
INSERT INTO customers
VALUES (5, 'David', 'Lee', 'david.lee@email.com', '555-0123', NOW());

Warning

Always specify column names explicitly. If someone adds or reorders columns later, your INSERT statements will break or insert data into the wrong columns.

Using DEFAULT Values

SQL

-- Let auto-increment and defaults do their job
INSERT INTO products (product_name, category, price)
VALUES ('SQL Handbook', 'Books', 29.99);
 
-- stock_quantity defaults to 0
-- is_active defaults to TRUE
-- created_at defaults to current timestamp

Modifying Tables

After creating a table, you may need to modify its structure:

Adding Columns

SQL

ALTER TABLE customers
ADD COLUMN loyalty_points INT DEFAULT 0;

Removing Columns

SQL

ALTER TABLE customers
DROP COLUMN phone;

Modifying Columns

SQL

-- Change data type (MySQL syntax)
ALTER TABLE customers
MODIFY COLUMN email VARCHAR(150);
 
-- Rename column (PostgreSQL)
ALTER TABLE customers
RENAME COLUMN email TO email_address;

Adding Constraints

SQL

ALTER TABLE products
ADD CONSTRAINT chk_price CHECK (price > 0);
 
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

Deleting Tables

Destructive Operation

DROP TABLE permanently deletes the table and all its data. This cannot be undone!

SQL

-- Delete a table
DROP TABLE order_items;
 
-- Delete only if it exists (prevents errors)
DROP TABLE IF EXISTS order_items;

Sample Database: Bookstore

Let's create a complete sample database we'll use throughout this guide:

SQL

-- Create the database
CREATE DATABASE bookstore;
USE bookstore;
 
-- Authors table
CREATE TABLE authors (
    author_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_date DATE,
    nationality VARCHAR(50)
);
 
-- Books table
CREATE TABLE books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    author_id INT NOT NULL,
    isbn VARCHAR(13) UNIQUE,
    publication_year INT,
    price DECIMAL(8,2) NOT NULL CHECK (price > 0),
    stock_quantity INT DEFAULT 0,
 
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);
 
-- Customers table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    join_date DATE DEFAULT (CURRENT_DATE)
);
 
-- Orders table
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'pending',
 
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
 
-- Order items (junction table)
CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    book_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(8,2) NOT NULL,
 
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

Note

Save these CREATE TABLE statements. We'll populate this database with sample data and use it for query examples in the following chapters.

Populating the Sample Database

SQL

-- Insert authors
INSERT INTO authors (first_name, last_name, birth_date, nationality) VALUES
    ('George', 'Orwell', '1903-06-25', 'British'),
    ('Jane', 'Austen', '1775-12-16', 'British'),
    ('Gabriel', 'García Márquez', '1927-03-06', 'Colombian'),
    ('Haruki', 'Murakami', '1949-01-12', 'Japanese');
 
-- Insert books
INSERT INTO books (title, author_id, isbn, publication_year, price, stock_quantity) VALUES
    ('1984', 1, '9780451524935', 1949, 15.99, 45),
    ('Animal Farm', 1, '9780451526342', 1945, 12.99, 30),
    ('Pride and Prejudice', 2, '9780141439518', 1813, 14.99, 25),
    ('One Hundred Years of Solitude', 3, '9780060883287', 1967, 18.99, 20),
    ('Norwegian Wood', 4, '9780375704024', 1987, 16.99, 35),
    ('Kafka on the Shore', 4, '9781400079278', 2002, 17.99, 28);
 
-- Insert customers
INSERT INTO customers (first_name, last_name, email) VALUES
    ('Alice', 'Chen', 'alice.chen@email.com'),
    ('Bob', 'Martinez', 'bob.m@email.com'),
    ('Carol', 'Taylor', 'carol.t@email.com');
 
-- Insert orders
INSERT INTO orders (customer_id, order_date, total_amount, status) VALUES
    (1, '2024-01-15 10:30:00', 31.98, 'completed'),
    (2, '2024-01-16 14:45:00', 18.99, 'completed'),
    (1, '2024-01-17 09:15:00', 46.97, 'shipped');
 
-- Insert order items
INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES
    (1, 1, 1, 15.99),  -- Order 1: 1984
    (1, 2, 1, 12.99),  -- Order 1: Animal Farm
    (2, 4, 1, 18.99),  -- Order 2: One Hundred Years of Solitude
    (3, 3, 1, 14.99),  -- Order 3: Pride and Prejudice
    (3, 5, 1, 16.99),  -- Order 3: Norwegian Wood
    (3, 2, 1, 12.99);  -- Order 3: Animal Farm

What's Next

With our database created and populated, we're ready to start querying data. The next chapter introduces the SELECT statement—the foundation of data retrieval in SQL.