PostgreSQL: How to Create a Demo Database

Welcome to The Coding College, where we make coding and database management easy to understand and implement! In this tutorial, we’ll guide you through creating a demo database in PostgreSQL, which you can use to practice and test your SQL queries.

Step 1: Connect to PostgreSQL

You can connect to PostgreSQL using:

  • The psql command-line interface
  • pgAdmin, a graphical interface
  • Your application’s database client

For this guide, we’ll use the psql command-line interface.

Step 2: Create a New Database

To create a new database, use the CREATE DATABASE statement.

Syntax

CREATE DATABASE database_name;

Example

CREATE DATABASE demo_db;

This command creates a new database named demo_db.

Step 3: Connect to the Demo Database

To start working with the newly created database, switch to it using the \c command in psql:

\c demo_db

You’ll see a message confirming the connection:

You are now connected to database "demo_db" as user "your_user".

Step 4: Create Tables

Now, let’s create some tables within the demo_db database.

Example: Students Table

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INTEGER NOT NULL,
    email VARCHAR(150)
);

Example: Courses Table

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    duration INTEGER CHECK (duration > 0)
);

Step 5: Insert Sample Data

Populate the tables with sample data to simulate real-world scenarios.

Insert Data into Students Table

INSERT INTO students (name, age, email) VALUES
('Alice', 22, '[email protected]'),
('Bob', 24, '[email protected]'),
('Charlie', 20, '[email protected]');

Insert Data into Courses Table

INSERT INTO courses (course_name, duration) VALUES
('Mathematics', 6),
('Physics', 4),
('Chemistry', 5);

Step 6: Query the Data

Run a SELECT query to view the data and ensure everything is set up correctly.

View Students

SELECT * FROM students;

View Courses

SELECT * FROM courses;

Additional Tips

  1. Backups: Always back up your database before experimenting.
  2. Practice: Use the demo database to test commands like SELECT, UPDATE, DELETE, and JOIN.
  3. pgAdmin Support: If using pgAdmin, you can create the database through the UI by navigating to the “Databases” section, right-clicking, and selecting “Create > Database.”

Learn More at The Coding College

For more PostgreSQL tutorials and resources, visit The Coding College. Our goal is to help you build confidence in coding and database management while adhering to Google’s E-E-A-T principles.

Conclusion

Setting up a demo database is the first step to mastering PostgreSQL. By practicing in a controlled environment, you can explore SQL commands and develop the skills needed for real-world database management.

Have questions or need assistance? Drop a comment or reach out, and the team at The Coding College is here to help!

Leave a Comment