Run and Query Two Databases with Docker

Spin up MariaDB and PostgreSQL in Docker and build a schema in both.

Introduction

30 Second Summary

Every app you use stores its data somewhere. Whether it's your profile on a social network or your order history on a shopping site, a database is working behind the scenes to keep that information organized and retrievable.

In this project, you will launch two real database servers side-by-side using Docker containers, then build the same employee directory in both. You will write SQL to create tables, insert data, and run queries to see exactly where MariaDB and PostgreSQL differ.

What You'll Build

You will spin up MariaDB and PostgreSQL in Docker containers, build an identical employee directory schema in each, and compare how the two databases differ in syntax and behaviour.

By the end of this project, you'll have:

  • A running MariaDB container with an employee directory database you built from scratch.
  • A running PostgreSQL container with the same employee directory database for side-by-side comparison.
  • Hands-on experience with database administration (creating users, granting permissions) and developer queries (filtering, sorting, and aggregating data).
  • Secret Mission: Add a departments table with foreign keys and write JOIN queries across both databases.

Are there any prerequisites?

You should be comfortable with basic terminal navigation (cd and ls). You will also need Docker Desktop, VS Code, and a terminal already installed on your machine.

Launch Your Database Containers

Databases need a server process to run. Instead of installing MariaDB and PostgreSQL directly onto your machine, you will run each one inside its own Docker container.

Each container is an isolated environment with its own filesystem, network port, and configuration. In this step, you will launch both database servers and confirm they are running.

In this step, get ready to:
  • Pull and run a MariaDB container with a root password and port mapping.
  • Pull and run a PostgreSQL container with a superuser password and port mapping.
  • Verify both containers are running.
Pull and run your MariaDB container

MariaDB is a popular open-source relational database. You will run it as a container and configure it with a root password so you can log in later.

  • Launch a MariaDB container by running this command in your terminal:
docker run --detach --name mariadb-server --env MARIADB_ROOT_PASSWORD=rootpassword -p 3306:3306 mariadb:latest

What does this command do?

  • docker run downloads the image (if needed) and starts a new container from it.
  • --detach runs the container in the background so your terminal stays free.
  • --name mariadb-server gives the container a friendly name you can reference later.
  • --env MARIADB_ROOT_PASSWORD=rootpassword sets the root user's password inside the container. MariaDB requires this variable to start.
  • -p 3306:3306 maps port 3306 on your machine to port 3306 inside the container, so you can connect to the database from your host.
  • mariadb:latest is the official MariaDB image from Docker Hub.
  • You should see a long string of characters (the container ID) printed in your terminal. This confirms Docker created and started the container.

Seeing a port conflict error?

If you see an error about port 3306 already being in use, another service on your machine is using that port. Stop the conflicting service or change the host port to something like -p 3307:3306.

If Docker says the container name is already in use, remove the old container first by running docker rm mariadb-server.

help me fix my docker run error for MariaDB

Pull and run your PostgreSQL container

PostgreSQL is another widely used relational database, known for strict standards compliance. You will run it alongside MariaDB on a different port.

  • Launch a PostgreSQL container by running this command:
docker run --detach --name postgres-server --env POSTGRES_PASSWORD=rootpassword -p 5432:5432 postgres:latest

What does this command do?

  • --env POSTGRES_PASSWORD=rootpassword sets the superuser password. PostgreSQL requires this variable to start.
  • -p 5432:5432 maps PostgreSQL's default port. Since MariaDB uses 3306, there is no conflict.
  • postgres:latest is the official PostgreSQL image from Docker Hub.
  • You should see another long container ID string printed in your terminal.

Seeing a port conflict or name error?

If port 5432 is already in use, another PostgreSQL instance or service is running on your machine. Stop it or change the host port to -p 5433:5432.

If the container name is taken, remove the old one with docker rm postgres-server and try again.

help me fix my docker run error for PostgreSQL

Verify both containers are running

Both containers should now be running in the background. You can confirm this with a single command that lists all active containers.

  • Check your running containers by running this command:
docker ps
  • You should see a table with two rows. One row shows mariadb-server with port 3306 and the other shows postgres-server with port 5432.
  • The STATUS column should say something like Up followed by a duration. This confirms both databases are running.

Only seeing one container or none?

If a container is missing from the list, it may have crashed on startup. Run docker ps -a to see all containers including stopped ones. Check the STATUS column for an Exited status.

If a container exited, check its logs with docker logs mariadb-server or docker logs postgres-server to see what went wrong.

help me figure out why my container is not running

Both database servers are now running and ready to accept connections. Next up, you will connect to each one and create your first database.

Connect and Create Databases

Your database containers are running, but they're like sealed boxes right now. To work with the databases inside, you need to open a connection using each database's command-line client.

Each database system ships with its own CLI tool built into the container. MariaDB uses the mariadb client. PostgreSQL uses psql. You'll connect to each and create the employee_dir database.

In this step, get ready to:
  • Connect to MariaDB and create the employee_dir database.
  • Connect to PostgreSQL and create the employee_dir database.
Connect to MariaDB and create the database

The mariadb CLI client lets you type SQL commands directly into the database server. You'll open an interactive session inside the running container to access it.

  • Connect to your MariaDB container by running this command:
docker exec -it mariadb-server mariadb -u root -prootpassword

What does this command do?

  • docker exec -it opens an interactive terminal session inside a running container.
  • mariadb-server is the name of your MariaDB container from Step 1.
  • mariadb -u root -prootpassword launches the MariaDB client as the root user with the password you set when creating the container.

Your terminal prompt changes to MariaDB [(none)]>. This means you're inside the MariaDB command-line client.

Seeing "Access denied" or "No such container"?

  • Make sure there's no space between -p and rootpassword in the command. The password must be directly attached to the flag.
  • Confirm your container is still running with docker ps. If mariadb-server is not listed, restart it with docker start mariadb-server.

help me connect to my MariaDB container

  • Create the employee_dir database by running this SQL command:
CREATE DATABASE employee_dir;

What does this command do?

CREATE DATABASE tells MariaDB to set up a brand new, empty database with the name you specify. Think of it like creating a new folder to organize your tables and data inside.

  • Confirm the database was created by running:
SHOW DATABASES;

You should see employee_dir in the list alongside default system databases.

  • Exit the MariaDB client by running:
EXIT;

Your prompt returns to the regular terminal. You're back outside the container.

Connect to PostgreSQL and create the database

Now you'll do the same thing with your PostgreSQL container. The process is similar, but psql uses different flags and a different prompt style.

  • Connect to your PostgreSQL container by running this command:
docker exec -it postgres-server psql -U postgres

What does this command do?

  • docker exec -it opens an interactive session inside the running container, same as before.
  • postgres-server is the name of your PostgreSQL container from Step 1.
  • psql -U postgres launches the PostgreSQL client as the postgres superuser. No password prompt appears because you're connecting from inside the container itself.

Your terminal prompt changes to postgres=#. This means you're inside the PostgreSQL command-line client.

Seeing "No such container" or connection refused?

  • Run docker ps to confirm postgres-server is listed as running. If not, start it with docker start postgres-server.
  • Check for typos in the container name. It must be exactly postgres-server.

help me connect to my PostgreSQL container

  • Create the employee_dir database by running this SQL command:
CREATE DATABASE employee_dir;

Same SQL, different engine

The CREATE DATABASE command is standard SQL that works identically in both MariaDB and PostgreSQL. You'll start to see syntax differences in later steps when creating users and tables.

  • Confirm the database was created by running:
\l

What is \l?

In psql, commands starting with a backslash are meta-commands that control the client itself. \l lists all databases on the server. It's the PostgreSQL equivalent of MariaDB's SHOW DATABASES;.

You should see employee_dir in the list alongside default databases like template0 and template1.

  • Exit the PostgreSQL client by running:
\q

Your prompt returns to the regular terminal.

Both databases are created and ready. Next up, you'll create dedicated users with specific permissions so you don't have to use the all-powerful root account for everyday work.

Create Users and Grant Permissions

Your databases are created and ready. Now you need to think about who can access them and what they're allowed to do inside.

In production, you never let applications connect as the root (admin) user. Instead, you create dedicated users with only the permissions they need. In this step, you will create an application user in both databases and grant it privileges on your employee_dir database.

In this step, get ready to:
  • Create a dedicated application user in MariaDB with specific permissions.
  • Create the same user in PostgreSQL and grant equivalent privileges.
  • Compare the syntax differences between the two systems.
Create a dedicated application user in MariaDB

The root account in MariaDB has unrestricted access to everything. Applications should connect with a limited user that can only touch the specific database it needs.

  • Connect to your MariaDB container by running this command:
docker exec -it mariadb-server mariadb --user root -prootpassword

What does this command do?

docker exec -it opens an interactive session inside the running container. mariadb --user root -prootpassword launches the MariaDB CLI client and logs in as root.

Notice there is no space between -p and the password. That is the correct format for MariaDB's password flag.

You should see the MariaDB [(none)]> prompt, meaning you are connected.

  • Create the application user and grant it privileges on your database by running these SQL statements:
-- Create a dedicated user for our application
CREATE USER 'app_user'@'%' IDENTIFIED BY 'apppassword';

-- Grant full privileges on the employee_dir database only
GRANT ALL PRIVILEGES ON employee_dir.* TO 'app_user'@'%';

What do these statements do?

CREATE USER 'app_user'@'%' creates a user named app_user that can connect from any host. The '%' is a wildcard meaning "any IP address."

GRANT ALL PRIVILEGES ON employee_dir.* gives this user full access to every table in the employee_dir database, but nothing else on the server. The user cannot touch any other database.

  • Verify the user was created by running this query:
SELECT User, Host FROM mysql.user WHERE User = 'app_user';

You should see one row with app_user in the User column and % in the Host column.

Not seeing app_user in the results?

Check that your CREATE USER statement ended with a semicolon. Without it, MariaDB waits for more input instead of executing the command.

Make sure you included the quotes around both 'app_user' and '%' in the original statement.

Help me debug my MariaDB CREATE USER statement.

  • Exit the MariaDB client by running:
EXIT;
Create a dedicated application user in PostgreSQL

PostgreSQL handles users differently from MariaDB. In PostgreSQL, users and groups are both "roles." A role with login permission is what other databases call a user.

  • Connect to your PostgreSQL container by running this command:
docker exec -it postgres-server psql -U postgres

You should see the postgres=# prompt, meaning you are connected as the superuser.

  • Create the application user and grant it privileges by running these SQL statements:
-- Create a dedicated user for our application
CREATE USER app_user WITH PASSWORD 'apppassword';

-- Grant full privileges on the employee_dir database
GRANT ALL PRIVILEGES ON DATABASE employee_dir TO app_user;

What do these statements do?

CREATE USER app_user WITH PASSWORD 'apppassword' creates a role with login permission. Notice there are no quotes around the username and no @'host' pattern. PostgreSQL does not tie users to specific hosts the way MariaDB does.

GRANT ALL PRIVILEGES ON DATABASE employee_dir TO app_user gives this user full access to the employee_dir database. The syntax is slightly different from MariaDB. PostgreSQL uses ON DATABASE instead of ON database_name.*.

  • Verify the user was created by running:
\du app_user

You should see a table showing app_user listed as a role with no special attributes (no Superuser, no Create DB). That is correct for a limited application user.

Not seeing app_user in the list?

If you see an error about syntax, check that your CREATE USER statement ended with a semicolon. PostgreSQL requires it to execute the statement.

Make sure you typed \du with a backslash, not a forward slash. The backslash prefix is how PostgreSQL recognizes its meta-commands.

Help me debug my PostgreSQL CREATE USER statement.

  • Exit the PostgreSQL client by running:
\q
Compare the syntax differences

You just did the same task in two different databases. Take a moment to notice how the syntax differed.

Key differences in user management

  • MariaDB ties users to hosts with the 'username'@'host' pattern. PostgreSQL just uses the username with no host binding.
  • MariaDB uses IDENTIFIED BY to set the password. PostgreSQL uses WITH PASSWORD.
  • MariaDB grants on database_name.* (all tables in a database). PostgreSQL grants on DATABASE database_name (the database as a whole).
  • MariaDB requires single quotes around the username in SQL statements. PostgreSQL does not quote usernames.

Why create a separate user instead of using root?

The root account can drop databases, delete other users, and change server settings. If your application has a bug or gets hacked, an attacker with root access can destroy everything.

A limited user like app_user can only work within its assigned database. This limits the blast radius of any mistake or attack.

Your databases now have dedicated application users with the right level of access. Next up, you will design a table schema and create your first real table in both databases.

Design and Create Your Employee Table

You have two databases ready with dedicated users. Now you need a structure to actually store data in.

Tables are the core building blocks of any relational database. In this step, you'll design an employees table with columns for names, emails, departments, salaries, and hire dates. You'll write the same table in both MariaDB and PostgreSQL to see where their syntax diverges.

In this step, get ready to:
  • Write and run a CREATE TABLE statement in MariaDB using AUTO_INCREMENT.
  • Write and run the equivalent CREATE TABLE statement in PostgreSQL using SERIAL.
  • Compare how each database handles primary keys and data types.
Create the employees table in MariaDB

A table defines the shape of your data. Each column has a name, a data type, and optional constraints that enforce data quality.

  • Open schema_mariadb.sql in VS Code and add the following schema:
-- MariaDB schema for employee directory

CREATE DATABASE IF NOT EXISTS employee_dir;
USE employee_dir;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE NOT NULL
);

What does this schema do?

  • CREATE DATABASE IF NOT EXISTS safely creates the database if it does not already exist. Since you created it in the previous step, this line is a no-op but makes the file a complete standalone reference.
  • USE employee_dir tells MariaDB which database to run the following statements against.
  • AUTO_INCREMENT automatically assigns a unique integer to each new row. You never need to pick an ID yourself.
  • NOT NULL forces a column to always have a value. Columns without it (like department) can be left empty.
  • UNIQUE on the email column prevents two employees from sharing the same email address.
  • Save schema_mariadb.sql.

The file is saved for reference. Next you will run the CREATE TABLE statement inside your MariaDB container.

  • Connect to MariaDB by running this command:
docker exec -it mariadb-server mariadb --user root -prootpassword
  • Once you see the MariaDB [(none)]> prompt, paste the following SQL to select the database and create the table:
USE employee_dir;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE NOT NULL
);

What should I see?

MariaDB responds with Query OK, 0 rows affected for each statement. The prompt changes to MariaDB [employee_dir]> after the USE command, confirming you are inside the right database.

  • Verify the table was created by running:
DESCRIBE employees;

You should see a table listing each column with its Field, Type, Null, Key, Default, and Extra values. The id row shows auto_increment in the Extra column.

Seeing "Table already exists"?

This means you ran the CREATE TABLE statement before. You can drop the table with DROP TABLE employees; and then re-run the CREATE TABLE statement.

If you see a syntax error, double-check that every line ends with the correct punctuation and that the closing ); is present.

help me troubleshoot my MariaDB CREATE TABLE statement

  • Leave the MariaDB session open or type EXIT; to return to your terminal. You will not need the MariaDB CLI again until the next step.

✔️ Awesome, I've got everything!

Great. Double check you have saved schema_mariadb.sql and that DESCRIBE employees showed all seven columns.

ⓧ I'd like to double check the full code

-- MariaDB schema for employee directory

CREATE DATABASE IF NOT EXISTS employee_dir;
USE employee_dir;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE NOT NULL
);
Create the employees table in PostgreSQL

Now you will create the same table in PostgreSQL. The column definitions are nearly identical, but PostgreSQL uses a different keyword for auto-incrementing IDs.

  • Open schema_postgres.sql in VS Code and add the following schema:
-- PostgreSQL schema for employee directory

CREATE DATABASE employee_dir;
\c employee_dir

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE NOT NULL
);

What changed compared to MariaDB?

  • SERIAL replaces INT AUTO_INCREMENT. SERIAL is a PostgreSQL shorthand that creates an auto-incrementing four-byte integer behind the scenes.
  • \c employee_dir is a psql meta-command that switches your connection to a different database. MariaDB uses the SQL statement USE instead.
  • CREATE DATABASE employee_dir does not have IF NOT EXISTS. PostgreSQL supports this clause in newer versions, but the plain form is more common in documentation and tutorials.
  • Save schema_postgres.sql.

Now run the CREATE TABLE statement inside your PostgreSQL container.

  • Connect to PostgreSQL's employee_dir database by running this command:
docker exec -it postgres-server psql -U postgres -d employee_dir

Why connect directly to employee_dir?

The -d employee_dir flag tells psql to connect straight to that database. This avoids needing to run \c employee_dir after connecting.

  • Once you see the employee_dir=# prompt, paste the following SQL to create the table:
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE NOT NULL
);

What should I see?

PostgreSQL responds with CREATE TABLE on its own line. This confirms the table was created successfully.

  • Verify the table structure by running:
\d employees

You should see a table listing each column with its type and modifiers. The id column shows integer with a default value from a sequence. That sequence is what SERIAL created for you automatically.

Seeing "relation already exists"?

This means the table was already created. You can drop it with DROP TABLE employees; and then re-run the CREATE TABLE statement.

If you see a syntax error, make sure you are connected to employee_dir (the prompt should read employee_dir=#). If the prompt says postgres=#, run \c employee_dir first.

help me troubleshoot my PostgreSQL CREATE TABLE statement

  • Type \q to exit the PostgreSQL session.

✔️ Awesome, I've got everything!

Great. Double check you have saved schema_postgres.sql and that \d employees showed all seven columns.

ⓧ I'd like to double check the full code

-- PostgreSQL schema for employee directory

CREATE DATABASE employee_dir;
\c employee_dir

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE NOT NULL
);
Compare how each database handles primary keys and data types

You just wrote nearly identical schemas in two databases. The differences are small but important to recognize.

Key differences between MariaDB and PostgreSQL

  • Auto-incrementing IDs: MariaDB uses INT AUTO_INCREMENT as two separate keywords. PostgreSQL uses SERIAL, a single pseudo-type that creates a sequence object behind the scenes.
  • Database switching: MariaDB uses the SQL statement USE dbname. PostgreSQL uses the psql meta-command \c dbname.
  • Data type strictness: MariaDB is more lenient and may silently convert values to fit a column. PostgreSQL strictly enforces types and rejects mismatches with an error.
  • Inspecting structure: MariaDB uses DESCRIBE tablename. PostgreSQL uses \d tablename.

Extra for Experts: Why does PostgreSQL use sequences?

When you define a column as SERIAL, PostgreSQL creates a separate sequence object that generates the next ID. This is more flexible than MariaDB's approach because you can share one sequence across multiple tables or reset it independently of the table data.

Your tables are ready and waiting for data. Next up, you will insert employee records and start querying them.

Insert Sample Data

Your tables are ready and waiting for data. A database schema without records is like a filing cabinet with empty drawers.

In this step, you will populate both databases with the same set of employee records using INSERT statements. This is where your employee directory comes to life.

In this step, get ready to:
  • Write INSERT statements to add 6 employees to MariaDB.
  • Run the same INSERT statements in PostgreSQL.
  • Verify the data with SELECT * queries in both databases.
Insert employees into MariaDB

The INSERT statement adds new rows to a table. You will insert 6 employees covering three departments: Engineering, Marketing, and Sales.

  • Connect to your MariaDB container by running this command:
docker exec -it mariadb-server mariadb --user root -pMyRootPass123
  • Switch to the employee_dir database by running:
USE employee_dir;
  • Insert 6 employees into the employees table by running this statement:
INSERT INTO employees (first_name, last_name, email, department, salary, hire_date) VALUES
('Alice', 'Johnson', 'alice.johnson@company.com', 'Engineering', 95000.00, '2022-03-15'),
('Bob', 'Smith', 'bob.smith@company.com', 'Marketing', 72000.00, '2021-07-01'),
('Carol', 'Williams', 'carol.williams@company.com', 'Engineering', 105000.00, '2020-01-10'),
('David', 'Brown', 'david.brown@company.com', 'Sales', 68000.00, '2023-06-20'),
('Eva', 'Martinez', 'eva.martinez@company.com', 'Engineering', 98000.00, '2022-11-05'),
('Frank', 'Taylor', 'frank.taylor@company.com', 'Marketing', 75000.00, '2021-09-12');

What does this statement do?

INSERT INTO specifies which table receives the data. The column list in parentheses tells the database which fields you are filling.

Notice you are not providing an id value. MariaDB's AUTO_INCREMENT handles that automatically, assigning 1, 2, 3, and so on.

Each row in the VALUES clause is one employee record. The comma-separated format lets you insert multiple rows in a single statement.

  • You should see a message confirming 6 rows were affected.

Seeing a duplicate entry error?

This means the data was already inserted in a previous attempt. The email column has a UNIQUE constraint, so you cannot insert the same email twice.

Run DELETE FROM employees; to clear the table, then try the INSERT again.

help me fix a duplicate entry error in MariaDB

Now add this INSERT statement to your SQL file so you have a complete record of your schema and data.

  • Open schema_mariadb.sql in VS Code.
  • Add the INSERT statement below the CREATE TABLE block, with a blank line separating them.
  • Save schema_mariadb.sql.
Run the same inserts in PostgreSQL

The INSERT syntax is standard SQL, so the exact same statement works in PostgreSQL. The only difference is how you connect and select the database.

  • Open a new terminal tab or exit MariaDB first by typing exit.
  • Connect to your PostgreSQL container by running this command:
docker exec -it postgres-server psql -U postgres
  • Switch to the employee_dir database by running:
\c employee_dir
  • Insert the same 6 employees by running this statement:
INSERT INTO employees (first_name, last_name, email, department, salary, hire_date) VALUES
('Alice', 'Johnson', 'alice.johnson@company.com', 'Engineering', 95000.00, '2022-03-15'),
('Bob', 'Smith', 'bob.smith@company.com', 'Marketing', 72000.00, '2021-07-01'),
('Carol', 'Williams', 'carol.williams@company.com', 'Engineering', 105000.00, '2020-01-10'),
('David', 'Brown', 'david.brown@company.com', 'Sales', 68000.00, '2023-06-20'),
('Eva', 'Martinez', 'eva.martinez@company.com', 'Engineering', 98000.00, '2022-11-05'),
('Frank', 'Taylor', 'frank.taylor@company.com', 'Marketing', 75000.00, '2021-09-12');

Why does the same INSERT work here?

INSERT INTO with a VALUES clause is standard SQL that both databases understand identically. The differences between MariaDB and PostgreSQL show up in schema definitions (like AUTO_INCREMENT vs SERIAL), not in basic data manipulation.

PostgreSQL's SERIAL column handles the auto-generated id values just like MariaDB's AUTO_INCREMENT did.

  • You should see INSERT 0 6 confirming all 6 rows were inserted.

Seeing a duplicate key error?

The same fix applies here. Run DELETE FROM employees; to clear existing data, then try the INSERT again.

Also confirm you ran \c employee_dir before inserting. If you are still on the default postgres database, the employees table does not exist there.

help me fix a duplicate key error in PostgreSQL

Now update your PostgreSQL SQL file with the same INSERT statement.

  • Open schema_postgres.sql in VS Code.
  • Add the INSERT statement below the CREATE TABLE block, with a blank line separating them.
  • Save schema_postgres.sql.
Verify your data in both databases

A quick SELECT * confirms that both databases contain the same records. This is the simplest query you can run. It returns every column and every row from a table.

  • In your PostgreSQL session (you should still be connected), run this query:
SELECT * FROM employees;
  • You should see a table with 6 rows showing all your employee data, including the auto-generated id values 1 through 6.
  • Exit PostgreSQL by typing exit.
  • Reconnect to MariaDB by running:
docker exec -it mariadb-server mariadb --user root -pMyRootPass123 employee_dir
  • Run the same SELECT query in MariaDB:
SELECT * FROM employees;
  • You should see the same 6 employees with matching data in both databases.

What should I see?

Both databases display a formatted table with 7 columns: id, first_name, last_name, email, department, salary, and hire_date.

The output formatting differs slightly between the two CLIs, but the data itself is identical. MariaDB uses a box-drawing border while PostgreSQL uses a simpler pipe-separated layout.

Seeing an empty result set?

Make sure you are connected to the employee_dir database, not the default database. In MariaDB, run USE employee_dir; first. In PostgreSQL, run \c employee_dir first.

If you switched databases and still see no rows, your INSERT did not run successfully. Scroll up in your terminal to check for error messages from when you ran the INSERT.

help me figure out why my SELECT returns empty results

✔️ Awesome, I've got everything!

Great. Double check you have saved both schema_mariadb.sql and schema_postgres.sql with the INSERT statements included.

ⓧ I'd like to double check the full code

Here is the complete schema_mariadb.sql file:

-- MariaDB schema for employee directory

CREATE DATABASE IF NOT EXISTS employee_dir;
USE employee_dir;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE NOT NULL
);

INSERT INTO employees (first_name, last_name, email, department, salary, hire_date) VALUES
('Alice', 'Johnson', 'alice.johnson@company.com', 'Engineering', 95000.00, '2022-03-15'),
('Bob', 'Smith', 'bob.smith@company.com', 'Marketing', 72000.00, '2021-07-01'),
('Carol', 'Williams', 'carol.williams@company.com', 'Engineering', 105000.00, '2020-01-10'),
('David', 'Brown', 'david.brown@company.com', 'Sales', 68000.00, '2023-06-20'),
('Eva', 'Martinez', 'eva.martinez@company.com', 'Engineering', 98000.00, '2022-11-05'),
('Frank', 'Taylor', 'frank.taylor@company.com', 'Marketing', 75000.00, '2021-09-12');

Here is the complete schema_postgres.sql file:

-- PostgreSQL schema for employee directory

CREATE DATABASE employee_dir;
\c employee_dir

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE NOT NULL
);

INSERT INTO employees (first_name, last_name, email, department, salary, hire_date) VALUES
('Alice', 'Johnson', 'alice.johnson@company.com', 'Engineering', 95000.00, '2022-03-15'),
('Bob', 'Smith', 'bob.smith@company.com', 'Marketing', 72000.00, '2021-07-01'),
('Carol', 'Williams', 'carol.williams@company.com', 'Engineering', 105000.00, '2020-01-10'),
('David', 'Brown', 'david.brown@company.com', 'Sales', 68000.00, '2023-06-20'),
('Eva', 'Martinez', 'eva.martinez@company.com', 'Engineering', 98000.00, '2022-11-05'),
('Frank', 'Taylor', 'frank.taylor@company.com', 'Marketing', 75000.00, '2021-09-12');

Both databases now hold identical employee directories. Next up, you will write queries to filter, sort, and aggregate this data.

Query Your Data

Your employee directory has real data sitting in both databases. Six employees across three departments are waiting to be queried.

In this step, you'll write the queries that make a database useful. Filtering, sorting, and summarizing data are the bread-and-butter operations every developer runs daily.

In this step, get ready to:
  • Write SELECT queries with WHERE and ORDER BY to filter and sort employees.
  • Use aggregate functions to summarize salary data by department.
  • Run the same queries in both databases and compare results.
Write SELECT queries with WHERE and ORDER BY

A SELECT query pulls specific columns from a table. Adding WHERE filters rows by a condition. Adding ORDER BY sorts the results.

  • Open queries.sql in VS Code and add the following filter and sort queries:
-- These queries work in both MariaDB and PostgreSQL

-- Filter: Find all engineers
SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Engineering';

-- Sort: List employees by salary (highest first)
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

-- Filter + Sort: Engineers sorted by hire date
SELECT first_name, last_name, hire_date
FROM employees
WHERE department = 'Engineering'
ORDER BY hire_date ASC;

What do these queries do?

  • The first query uses WHERE department = 'Engineering' to return only rows where the department column matches exactly.
  • The second query uses ORDER BY salary DESC to sort results from highest salary to lowest.
  • The third query combines both. It filters to engineers first, then sorts those results by hire date in ascending order.
  • Save queries.sql.
  • Now connect to MariaDB to run these queries. Open your terminal and run:
docker exec -it mariadb-server mariadb --user root -pMyRootPass123
  • Switch to the employee_dir database by running:
USE employee_dir;
  • Run the first filter query to find all engineers:
SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Engineering';

You should see three rows returned: Alice Johnson, Carol Williams, and Eva Martinez with their salaries.

  • Run the sort query to list everyone by salary:
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

You should see all six employees listed from highest salary (Carol at 105000.00) down to lowest (David at 68000.00).

  • Run the combined filter and sort query:
SELECT first_name, last_name, hire_date
FROM employees
WHERE department = 'Engineering'
ORDER BY hire_date ASC;

You should see three engineers sorted by hire date: Carol (2020-01-10), Alice (2022-03-15), then Eva (2022-11-05).

Getting zero rows or an error?

  • Check that you ran USE employee_dir; before running any query. Without it, MariaDB doesn't know which database to look in.
  • Make sure 'Engineering' is capitalized exactly as you inserted it. SQL string matching is case-sensitive.
  • Check for a missing semicolon at the end of the query. MariaDB waits for more input until it sees one.

help me debug my MariaDB SELECT query

Summarize data with aggregate functions

Aggregate functions calculate a single value from many rows. COUNT counts rows, AVG calculates an average, and SUM adds values together. GROUP BY splits the results by a column before aggregating.

  • In VS Code, add the following aggregate queries to the bottom of queries.sql:
-- Aggregate: Count employees per department
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

-- Aggregate: Average salary per department
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- Aggregate: Total salary bill
SELECT SUM(salary) AS total_salary_bill
FROM employees;

What do these queries do?

  • COUNT(*) counts all rows in each group. Combined with GROUP BY department, it gives you the headcount per department.
  • AVG(salary) calculates the mean salary for each department group.
  • SUM(salary) without GROUP BY adds every salary in the table into one total.
  • The AS keyword creates a column alias so the output has a readable name instead of the raw function call.
  • Save queries.sql.
  • Back in your MariaDB session, run the count query:
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

You should see three rows: Engineering with 3, Marketing with 2, and Sales with 1.

  • Run the average salary query:
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
  • Run the total salary bill query:
SELECT SUM(salary) AS total_salary_bill
FROM employees;

You should see a single row showing 513000.00 as the total salary bill across all employees.

Seeing NULL or unexpected numbers?

  • If you see NULL, make sure you inserted all six employees in the previous step. Run SELECT COUNT(*) FROM employees; to verify you have 6 rows.
  • If GROUP BY results look wrong, double-check that department names are spelled consistently in your INSERT statements.

help me debug my aggregate query results

Run the same queries in PostgreSQL and compare

Everything you wrote in queries.sql is standard SQL. It should work identically in PostgreSQL. Let's verify that.

  • Exit MariaDB by typing:
EXIT;
  • Connect to PostgreSQL by running:
docker exec -it postgres-server psql -U postgres
  • Switch to the employee_dir database:
\c employee_dir
  • Run the filter query to find engineers:
SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Engineering';

You should see the same three rows: Alice, Carol, and Eva. The data is identical to what MariaDB returned.

  • Run the count-per-department aggregate:
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
  • Run the total salary bill:
SELECT SUM(salary) AS total_salary_bill
FROM employees;

Again, 513000.00. The results match exactly because SELECT, WHERE, ORDER BY, GROUP BY, and aggregate functions are all part of standard SQL that both databases implement the same way.

Where do the differences show up?

The queries in this step are standard SQL, so both databases behave identically. The differences you noticed in earlier steps (AUTO_INCREMENT vs SERIAL, user creation syntax) live in the DDL (Data Definition Language) and administration layer.

For everyday SELECT queries, you can switch between MariaDB and PostgreSQL without changing a line.

  • Try running the remaining queries from queries.sql in PostgreSQL to confirm they all produce the same output.
  • Exit PostgreSQL when you're done:
\q

Getting a relation does not exist error?

  • Make sure you ran \c employee_dir to connect to the right database. Without it, PostgreSQL looks for the table in the default postgres database.
  • Check that your table name is lowercase. PostgreSQL folds unquoted identifiers to lowercase, so employees works but Employees (quoted) would not match.

help me fix a relation does not exist error in PostgreSQL

✔️ Awesome, I've got everything!

Great. Double check you've saved queries.sql with all six queries.

ⓧ I'd like to double check the full code

Here is the complete queries.sql file:

-- These queries work in both MariaDB and PostgreSQL

-- Filter: Find all engineers
SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Engineering';

-- Sort: List employees by salary (highest first)
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

-- Filter + Sort: Engineers sorted by hire date
SELECT first_name, last_name, hire_date
FROM employees
WHERE department = 'Engineering'
ORDER BY hire_date ASC;

-- Aggregate: Count employees per department
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

-- Aggregate: Average salary per department
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- Aggregate: Total salary bill
SELECT SUM(salary) AS total_salary_bill
FROM employees;

You've now written and executed filter, sort, and aggregate queries in both databases. Next up, you'll take on a secret mission to add table relationships and write JOIN queries.

Secret mission

Add Departments and JOIN Queries

Your employee directory works great with a single table. But real databases have relationships between tables. Can you add a departments table, link it to employees with a foreign key, and write JOIN queries that pull data from both tables at once?

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally using Docker containers, so there are no ongoing costs.

Resources you used:

  • Docker container: mariadb-server (uses local disk space and memory while running).
  • Docker container: postgres-server (uses local disk space and memory while running).
  • Local SQL files: schema_mariadb.sql, schema_postgres.sql, queries.sql.

Keep everything running

No action needed. Choose this if you are still actively building or want to keep practising queries.

  • Your containers use minimal resources when idle.
  • Your SQL files remain in your project folder for reference.
  • You can reconnect at any time using the same docker exec commands from the project.

Pause - I'll come back to this later

Shut down running containers to free up memory. Your data is preserved inside the stopped containers.

  • Stop both containers by running:
docker stop mariadb-server postgres-server
  • When you are ready to resume, restart them by running:
docker start mariadb-server postgres-server

All your databases, tables, and data will still be there when you reconnect.

Delete - I don't want to use this again

Remove all project resources and start fresh. This permanently deletes your containers and all data inside them.

  • Stop and remove both Docker containers by running:
docker stop mariadb-server postgres-server && docker rm mariadb-server postgres-server
  • Delete your local SQL files by running:

macOS

rm schema_mariadb.sql schema_postgres.sql queries.sql

Windows

Remove-Item schema_mariadb.sql, schema_postgres.sql, queries.sql

Once removed, all databases, tables, and data inside those containers are gone permanently.

That's a Wrap!

That's a Wrap!

You did it. You just ran two real database servers, built schemas, managed users, and queried data across both MariaDB and PostgreSQL.

You've learned how to:

  • Launched and configured MariaDB and PostgreSQL containers in Docker.
  • Created databases and managed user permissions with CREATE USER and GRANT statements.
  • Designed a table schema and wrote queries using SELECT, WHERE, ORDER BY, and aggregate functions to filter, sort, and summarize data.
  • Secret Mission: Built table relationships with foreign keys and wrote JOIN queries across both databases.

Ready to quiz yourself?