Build Your First PostgreSQL Database

Design and manage a helpdesk database with SQL, roles, and backups.

Introduction

30 Second Summary

Every time you submit a support ticket, check your order status, or reset a password, a database is handling that request behind the scenes. Database administrators are the people who build, secure, and maintain those databases.

In this project, you will build an IT helpdesk ticket system database from scratch using PostgreSQL. You'll design tables, populate them with data, write queries, manage user permissions, and create a backup.

What You'll Build

You'll create a fully functional PostgreSQL database running in a Docker container, complete with a relational schema, role-based access control, and a disaster recovery backup.

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

  • A running PostgreSQL server inside a Docker container, managed through DBeaver.
  • A helpdesk database with 4 related tables connected by primary key and foreign key relationships.
  • Core DBA skills in action: SQL queries for real business questions, a restricted user role with limited permissions, and a full database backup.
  • Secret Mission: A priority escalation view and an audit trigger that automatically logs ticket status changes.

Do I need Docker and DBeaver installed?

Yes. This project assumes you already have Docker Desktop and DBeaver Community Edition installed on your machine. You'll set up PostgreSQL inside Docker during Step 1.

Launch PostgreSQL in Docker

Every IT helpdesk system needs a database server running behind the scenes. Before you can design tables or write queries, you need PostgreSQL up and running.

Docker makes this simple. Instead of installing PostgreSQL directly on your machine, you'll spin up a container that has everything pre-configured. In this step, you'll launch a PostgreSQL 17 server and connect to it with DBeaver.

In this step, get ready to:
  • Run a PostgreSQL 17 container with Docker.
  • Verify the database is running and accepting connections.
  • Connect DBeaver to your new database server.
Run the PostgreSQL 17 container

You need a running database server before you can create any databases or tables. The postgres:17 Docker image provides a fully configured PostgreSQL server ready to accept connections.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or Command Prompt (Windows) and press Enter to open it.
  • Launch the PostgreSQL container by running this command:
docker run --name helpdesk-pg -e POSTGRES_PASSWORD=postgres123 -p 5432:5432 -v helpdesk-data:/var/lib/postgresql/data -d postgres:17

What does this command do?

  • --name helpdesk-pg gives the container a memorable name you can reference later.
  • -e POSTGRES_PASSWORD=postgres123 sets the superuser password for the postgres user. This is required to start the server.
  • -p 5432:5432 maps the container's PostgreSQL port to your machine so tools like DBeaver can connect through localhost.
  • -v helpdesk-data:/var/lib/postgresql/data attaches a Docker volume so your data persists even if the container restarts.
  • -d postgres:17 runs the official PostgreSQL 17 image in detached (background) mode.

You should see a long string of characters printed in the terminal. This is the container ID confirming your container was created.

Seeing a port conflict error?

If you see a message about port 5432 already being in use, another PostgreSQL instance might be running on your machine. Stop it first, or change the host port to 5433:5432 in the command above.

If you see a name conflict (the name helpdesk-pg is already in use), remove the old container first by running docker rm helpdesk-pg and try again.

help me fix a Docker port conflict or name conflict error

Verify the container is running

A container can be created without actually running. Let's confirm your PostgreSQL server started successfully and is ready for connections.

  • Check running containers by running this command:
docker ps

You should see a table with helpdesk-pg listed under the NAMES column, postgres:17 under IMAGE, and 0.0.0.0:5432->5432/tcp under PORTS.

  • Now test that PostgreSQL is actually accepting connections by running:
docker exec -it helpdesk-pg psql -U postgres

What does this command do?

docker exec -it runs a command inside a running container interactively. psql -U postgres launches the PostgreSQL command-line client as the postgres superuser.

You should see a postgres=# prompt. This confirms PostgreSQL is running and ready to accept SQL commands.

  • Exit psql by typing:
\q

Container not showing in docker ps?

If docker ps shows nothing, the container may have crashed on startup. Run docker ps -a to see all containers including stopped ones. Then run docker logs helpdesk-pg to see what went wrong.

help me debug why my PostgreSQL Docker container is not running

Connect DBeaver to the PostgreSQL container

DBeaver is a graphical database tool that makes it easier to browse tables, run queries, and inspect data. You'll use it throughout this project instead of the plain psql command line.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type DBeaver and press Enter to open it.
  • Click the plug icon in the top-left toolbar (or go to Database in the menu bar, then New Database Connection).
  • Select PostgreSQL from the list of database drivers and click Next.

First time connecting?

DBeaver may prompt you to download the PostgreSQL driver. Click Download to allow it. This only happens once.

  • In the Host field, enter localhost.
  • In the Port field, confirm it shows 5432.
  • In the Database field, enter postgres.
  • In the Username field, enter postgres.
  • In the Password field, enter postgres123 (the password you set in the docker run command).
  • Click Test Connection at the bottom of the dialog.

You should see a green Connected message confirming DBeaver can reach your PostgreSQL server.

  • Click Finish to save the connection.

Test connection failing?

Make sure your helpdesk-pg container is still running. Run docker ps in your terminal to confirm.

Double-check that you entered localhost for the host and 5432 for the port. If you changed the host port earlier (due to a conflict), use that port number instead.

help me connect DBeaver to my PostgreSQL Docker container

Your database server is live and connected. Next up, you'll create the helpdesk database and design its tables from scratch.

Design and Create the Helpdesk Database

Your PostgreSQL server is running and DBeaver is connected. Now it is time to build the actual database that will power your IT helpdesk system.

Every support tool you have ever used stores tickets, users, and teams in a relational database. In this step, you will design a schema with four interconnected tables and create them using SQL.

In this step, get ready to:
  • Create a dedicated helpdesk database.
  • Build the teams and users tables with primary keys and constraints.
  • Build the tickets and ticket_comments tables with foreign key relationships.
Create the helpdesk database

PostgreSQL starts with a default database called postgres. You need a separate database to keep your helpdesk schema organized and isolated.

  • In DBeaver's Database Navigator panel on the left, right-click your PostgreSQL connection.
  • Select SQL Editor, then click Open SQL Script.
  • A new SQL editor tab opens. Confirm the toolbar at the top shows postgres as the active database.
  • Create the helpdesk database by pasting the following SQL and pressing Ctrl+Enter (Windows/Linux) or Ctrl+Return (macOS) to execute it:
-- Connect to the default postgres database first, then run:
CREATE DATABASE helpdesk;

What does this command do?

CREATE DATABASE helpdesk tells PostgreSQL to create a brand new, empty database named helpdesk. All the tables you build in this project will live inside this database.

  • You should see a success message in the results panel at the bottom of the SQL Editor.
  • Right-click your PostgreSQL connection in the Database Navigator and click Refresh.
  • You should see helpdesk appear in the database list.

Don't see helpdesk in the list?

Make sure you refreshed the connection (right-click the connection node, then Refresh). If you see an error about the database already existing, that means it was created successfully on a previous attempt.

Help me troubleshoot why my new database is not showing up

Now you need to switch your SQL Editor so that it runs queries against the new helpdesk database instead of postgres.

  • In the Database Navigator, expand your connection and find the helpdesk database.
  • Right-click helpdesk and select SQL Editor, then click Open SQL Script.
  • A new SQL editor tab opens, connected directly to the helpdesk database. Confirm the toolbar shows helpdesk as the active database.
Create the teams and users tables

Your helpdesk needs to know who works on tickets and how those people are organized. The teams table groups agents by department. The users table stores everyone who can create or resolve tickets.

  • In your new SQL editor (connected to helpdesk), create the teams table by pasting this SQL and pressing Ctrl+Enter (Windows/Linux) or Ctrl+Return (macOS):
CREATE TABLE teams (
    team_id SERIAL PRIMARY KEY,
    team_name VARCHAR(100) NOT NULL,
    description TEXT
);

What does this code do?

  • SERIAL PRIMARY KEY creates an auto-incrementing integer column that uniquely identifies each team.
  • VARCHAR(100) NOT NULL means the team name is required and limited to 100 characters.
  • TEXT allows an unlimited-length optional description.
  • You should see a success message confirming the table was created.
  • Now create the users table by pasting this SQL into the same editor and executing it:
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    role VARCHAR(20) NOT NULL CHECK (role IN ('agent', 'requester', 'admin')),
    team_id INTEGER REFERENCES teams(team_id)
);

What does this code do?

  • UNIQUE on username ensures no two users can share the same username.
  • CHECK (role IN ('agent', 'requester', 'admin')) restricts the column to only those three values. PostgreSQL rejects anything else.
  • REFERENCES teams(team_id) creates a foreign key. This links each user to a team and prevents orphaned records.
  • You should see another success message confirming the users table was created.

Seeing a relation already exists error?

This means you already ran the command once. The table exists and you can move on safely. If you want to start fresh, run DROP TABLE users; first, then re-run the CREATE statement.

Help me fix my relation already exists error

Create the tickets and ticket_comments tables

Tickets are the core of any helpdesk system. Each ticket belongs to a requester, may be assigned to an agent, and can accumulate comments over time.

  • Create the tickets table by pasting this SQL into your editor and executing it:
CREATE TABLE tickets (
    ticket_id SERIAL PRIMARY KEY,
    subject VARCHAR(200) NOT NULL,
    description TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'in_progress', 'resolved', 'closed')),
    priority VARCHAR(10) NOT NULL DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high', 'critical')),
    requester_id INTEGER NOT NULL REFERENCES users(user_id),
    assigned_agent_id INTEGER REFERENCES users(user_id),
    team_id INTEGER REFERENCES teams(team_id),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

What does this code do?

  • DEFAULT 'open' means new tickets automatically start with an open status if you do not specify one.
  • CHECK (status IN ('open', 'in_progress', 'resolved', 'closed')) ensures only valid statuses are stored.
  • requester_id is marked NOT NULL because every ticket must have someone who filed it. assigned_agent_id is nullable because a ticket can be unassigned.
  • DEFAULT NOW() on the timestamp columns automatically records when each ticket is created.
  • You should see a success message confirming the tickets table was created.
  • Now create the ticket_comments table by pasting and executing this SQL:
CREATE TABLE ticket_comments (
    comment_id SERIAL PRIMARY KEY,
    ticket_id INTEGER NOT NULL REFERENCES tickets(ticket_id),
    author_id INTEGER NOT NULL REFERENCES users(user_id),
    body TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

What does this code do?

  • REFERENCES tickets(ticket_id) ties every comment to a specific ticket. You cannot add a comment for a ticket that does not exist.
  • body TEXT NOT NULL requires every comment to have actual content.
  • You should see a final success message confirming ticket_comments was created.
  • In the Database Navigator, right-click the helpdesk database and click Refresh.
  • Expand Schemas, then public, then Tables.
  • You should see all four tables: teams, users, tickets, and ticket_comments.

Tables not appearing in the navigator?

Make sure you refreshed the helpdesk database node specifically (not just the connection). Right-click helpdesk and click Refresh. Then expand the public schema.

Help me find my tables in DBeaver

Why do tables reference each other?

Foreign keys enforce referential integrity. This means PostgreSQL guarantees that every requester_id in the tickets table actually points to a real user. If someone tries to insert a ticket with a user ID that does not exist, PostgreSQL blocks it.

This is why you created teams before users, and users before tickets. Each table must exist before another table can reference it.

✔️ Awesome, I've got everything!

Great. Double check you have all four tables showing in the navigator, then move on.

ⓧ I'd like to double check the full code

Here is the full SQL for creating the database and all four tables. Compare this against what you ran.

-- Connect to the default postgres database first, then run:
CREATE DATABASE helpdesk;
-- Connect to the helpdesk database, then run:

CREATE TABLE teams (
    team_id SERIAL PRIMARY KEY,
    team_name VARCHAR(100) NOT NULL,
    description TEXT
);

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    role VARCHAR(20) NOT NULL CHECK (role IN ('agent', 'requester', 'admin')),
    team_id INTEGER REFERENCES teams(team_id)
);

CREATE TABLE tickets (
    ticket_id SERIAL PRIMARY KEY,
    subject VARCHAR(200) NOT NULL,
    description TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'in_progress', 'resolved', 'closed')),
    priority VARCHAR(10) NOT NULL DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high', 'critical')),
    requester_id INTEGER NOT NULL REFERENCES users(user_id),
    assigned_agent_id INTEGER REFERENCES users(user_id),
    team_id INTEGER REFERENCES teams(team_id),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE ticket_comments (
    comment_id SERIAL PRIMARY KEY,
    ticket_id INTEGER NOT NULL REFERENCES tickets(ticket_id),
    author_id INTEGER NOT NULL REFERENCES users(user_id),
    body TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Your database schema is ready. Next up, you will populate these tables with realistic sample data so you can start running meaningful queries.

Populate the Database with Sample Data

You've built the skeleton of your helpdesk database with four related tables. Tables without data are like filing cabinets without files. Now it's time to fill them with realistic sample data.

Sample data is what makes your database come alive. It lets you test queries, verify that foreign key relationships work, and practice the kind of reporting a DBA does every day.

In this step, get ready to:
  • Insert teams and users into the database.
  • Insert tickets with varying statuses and priorities.
  • Add comments on tickets to simulate real conversation threads.
Insert teams and users

Foreign key constraints mean you must insert data in the right order. The users table references teams, so teams must go in first.

  • In DBeaver's SQL editor (still connected to the helpdesk database), insert the three IT support teams by running this SQL:
-- Insert teams
INSERT INTO teams (team_name, description) VALUES
('Network', 'Handles network connectivity and infrastructure issues'),
('Desktop Support', 'Handles hardware, software, and workstation issues'),
('Security', 'Handles access control, permissions, and security incidents');

What does this code do?

This INSERT INTO statement adds three rows to the teams table. Each row has a team_name and a description.

You don't need to specify team_id because the SERIAL type auto-generates sequential IDs starting from 1.

  • Click the Execute button (or press Ctrl+Enter on Windows, Cmd+Enter on macOS) to run the statement.
  • Verify the teams were inserted by running this query:
SELECT * FROM teams;

You should see 3 rows: Network, Desktop Support, and Security, each with an auto-generated team_id.

Not seeing 3 rows?

  • Make sure you're connected to the helpdesk database, not the default postgres database. Check the database name in DBeaver's connection panel.
  • If you see a syntax error, make sure you copied the full statement including the closing semicolon.
  • Help me debug my INSERT statement for the teams table.

Now that teams exist, you can insert users that reference them. Agents belong to a team, while requesters and admins have a NULL team.

  • Insert seven users (3 agents, 1 admin, 3 requesters) by running this SQL:
-- Insert users
INSERT INTO users (username, full_name, email, role, team_id) VALUES
('jsmith', 'John Smith', 'jsmith@company.com', 'agent', 1),
('mwilson', 'Maria Wilson', 'mwilson@company.com', 'agent', 2),
('achen', 'Alex Chen', 'achen@company.com', 'agent', 3),
('admin_kate', 'Kate Johnson', 'kjohnson@company.com', 'admin', NULL),
('requester_bob', 'Bob Davis', 'bdavis@company.com', 'requester', NULL),
('requester_lisa', 'Lisa Park', 'lpark@company.com', 'requester', NULL),
('requester_tom', 'Tom Garcia', 'tgarcia@company.com', 'requester', NULL);

What does this code do?

  • The first three users are agents, each assigned to one of the teams you just created (team_id 1, 2, 3).
  • The role column uses the CHECK constraint from your table definition. Only agent, requester, or admin are accepted.
  • Requesters and the admin have NULL for team_id because they don't belong to a support team.
  • Execute the statement.
  • Verify the users were inserted by running:
SELECT * FROM users;

You should see 7 rows with auto-generated user_id values from 1 to 7.

Seeing a foreign key violation?

  • This means the team_id values (1, 2, 3) don't match any rows in the teams table. Make sure you ran the teams INSERT first.
  • If you inserted and then deleted teams earlier, the auto-increment may have advanced past 1, 2, 3. Run SELECT * FROM teams; to check the actual IDs.
  • Help me fix a foreign key error when inserting users.
Insert tickets with different statuses and priorities

A realistic helpdesk always has tickets in different states. You'll insert a mix of open, in-progress, resolved, and closed tickets to simulate a live system.

  • Insert six sample tickets by running this SQL:
-- Insert tickets
INSERT INTO tickets (subject, description, status, priority, requester_id, assigned_agent_id, team_id, created_at) VALUES
('Cannot connect to VPN', 'Getting timeout errors when trying to connect to corporate VPN from home.', 'open', 'high', 5, 1, 1, NOW() - INTERVAL '2 hours'),
('Laptop screen flickering', 'Screen flickers every few seconds, especially when opening multiple windows.', 'in_progress', 'medium', 6, 2, 2, NOW() - INTERVAL '1 day'),
('Need access to finance shared drive', 'Requesting read access to the finance team shared drive for quarterly report.', 'resolved', 'low', 7, 3, 3, NOW() - INTERVAL '3 days'),
('Email not syncing on phone', 'Outlook app on iPhone stopped syncing emails since yesterday morning.', 'open', 'medium', 6, NULL, 2, NOW() - INTERVAL '5 hours'),
('Suspicious login alert', 'Received alert about login from unknown location on my account.', 'in_progress', 'critical', 5, 3, 3, NOW() - INTERVAL '30 minutes'),
('Printer on 3rd floor not working', 'The shared printer near the break room shows offline status.', 'closed', 'low', 7, 2, 2, NOW() - INTERVAL '5 days');

What's happening here?

  • Each ticket has a requester_id (the person who submitted it) and an optional assigned_agent_id (the agent working on it). Ticket 4 has NULL for the agent because it's unassigned.
  • The NOW() - INTERVAL '2 hours' expression creates a timestamp relative to the current time. This makes the sample data feel realistic no matter when you run it.
  • The numbers in requester_id and assigned_agent_id match the user_id values from the users table. This is how foreign keys link tables together.
  • Execute the statement.
  • Verify the tickets were inserted by running:
SELECT ticket_id, subject, status, priority FROM tickets;

You should see 6 rows showing a mix of statuses (open, in_progress, resolved, closed) and priorities (low, medium, high, critical).

Seeing a CHECK constraint violation?

  • The status column only accepts open, in_progress, resolved, or closed. Double-check for typos in the status values.
  • The priority column only accepts low, medium, high, or critical. Make sure you copied the values exactly.
  • Help me troubleshoot a CHECK constraint error on my tickets table.
Insert comments to simulate conversations

Real helpdesk tickets have back-and-forth conversations between agents and requesters. Comments are how those conversations are stored in the database.

  • Insert six sample comments across multiple tickets by running this SQL:
-- Insert comments
INSERT INTO ticket_comments (ticket_id, author_id, body, created_at) VALUES
(1, 1, 'Hi Bob, can you confirm which VPN client version you are using?', NOW() - INTERVAL '1 hour'),
(1, 5, 'I am using version 4.2.1 on Windows 11.', NOW() - INTERVAL '45 minutes'),
(2, 2, 'I have scheduled a hardware diagnostic for your laptop. Can you bring it to IT desk at 2 PM?', NOW() - INTERVAL '20 hours'),
(3, 3, 'Access has been granted. Please check and confirm you can see the finance drive.', NOW() - INTERVAL '2 days'),
(3, 7, 'Confirmed, I can see it now. Thank you!', NOW() - INTERVAL '2 days'' + INTERVAL ''1 hour'),
(5, 3, 'I am investigating this immediately. Please do not click any links in suspicious emails.', NOW() - INTERVAL '25 minutes');

What does this code do?

  • The ticket_id column links each comment to a specific ticket. Tickets 1, 2, 3, and 5 all have comments. Tickets 4 and 6 have none.
  • The author_id references the users table. Notice how ticket 1 has comments from both the agent (user 1) and the requester (user 5). This creates a realistic back-and-forth conversation.
  • Execute the statement.
  • Verify the comments were inserted by running:
SELECT * FROM ticket_comments;

You should see 6 rows of comments, with timestamps that show when each message was added.

Seeing a foreign key error on ticket_id?

  • The ticket_id values (1, 2, 3, 5) must match actual ticket IDs in the tickets table. Run SELECT ticket_id FROM tickets; to check that tickets exist first.
  • If you ran the tickets INSERT multiple times after errors, the IDs may not start at 1. You may need to adjust the ticket_id values in the comments INSERT to match your actual ticket IDs.
  • Help me fix a foreign key error when inserting comments.

✔️ Awesome, I've got everything!

Great. Make sure all four SELECT queries return the expected row counts: 3 teams, 7 users, 6 tickets, and 6 comments.

ⓧ I'd like to double check the full code

Here is the complete 03-insert-data.sql with all INSERT statements combined:

-- Insert teams
INSERT INTO teams (team_name, description) VALUES
('Network', 'Handles network connectivity and infrastructure issues'),
('Desktop Support', 'Handles hardware, software, and workstation issues'),
('Security', 'Handles access control, permissions, and security incidents');

-- Insert users
INSERT INTO users (username, full_name, email, role, team_id) VALUES
('jsmith', 'John Smith', 'jsmith@company.com', 'agent', 1),
('mwilson', 'Maria Wilson', 'mwilson@company.com', 'agent', 2),
('achen', 'Alex Chen', 'achen@company.com', 'agent', 3),
('admin_kate', 'Kate Johnson', 'kjohnson@company.com', 'admin', NULL),
('requester_bob', 'Bob Davis', 'bdavis@company.com', 'requester', NULL),
('requester_lisa', 'Lisa Park', 'lpark@company.com', 'requester', NULL),
('requester_tom', 'Tom Garcia', 'tgarcia@company.com', 'requester', NULL);

-- Insert tickets
INSERT INTO tickets (subject, description, status, priority, requester_id, assigned_agent_id, team_id, created_at) VALUES
('Cannot connect to VPN', 'Getting timeout errors when trying to connect to corporate VPN from home.', 'open', 'high', 5, 1, 1, NOW() - INTERVAL '2 hours'),
('Laptop screen flickering', 'Screen flickers every few seconds, especially when opening multiple windows.', 'in_progress', 'medium', 6, 2, 2, NOW() - INTERVAL '1 day'),
('Need access to finance shared drive', 'Requesting read access to the finance team shared drive for quarterly report.', 'resolved', 'low', 7, 3, 3, NOW() - INTERVAL '3 days'),
('Email not syncing on phone', 'Outlook app on iPhone stopped syncing emails since yesterday morning.', 'open', 'medium', 6, NULL, 2, NOW() - INTERVAL '5 hours'),
('Suspicious login alert', 'Received alert about login from unknown location on my account.', 'in_progress', 'critical', 5, 3, 3, NOW() - INTERVAL '30 minutes'),
('Printer on 3rd floor not working', 'The shared printer near the break room shows offline status.', 'closed', 'low', 7, 2, 2, NOW() - INTERVAL '5 days');

-- Insert comments
INSERT INTO ticket_comments (ticket_id, author_id, body, created_at) VALUES
(1, 1, 'Hi Bob, can you confirm which VPN client version you are using?', NOW() - INTERVAL '1 hour'),
(1, 5, 'I am using version 4.2.1 on Windows 11.', NOW() - INTERVAL '45 minutes'),
(2, 2, 'I have scheduled a hardware diagnostic for your laptop. Can you bring it to IT desk at 2 PM?', NOW() - INTERVAL '20 hours'),
(3, 3, 'Access has been granted. Please check and confirm you can see the finance drive.', NOW() - INTERVAL '2 days'),
(3, 7, 'Confirmed, I can see it now. Thank you!', NOW() - INTERVAL '2 days' + INTERVAL '1 hour'),
(5, 3, 'I am investigating this immediately. Please do not click any links in suspicious emails.', NOW() - INTERVAL '25 minutes');

Your helpdesk database is now full of realistic data. Next up, you'll write SQL queries that answer real business questions like "which team has the most tickets?" and "who submitted the critical security alert?"

Query Your Data Like a DBA

Your helpdesk database is populated with realistic data. Now comes the part that makes a DBA valuable: pulling answers out of that data.

In a real IT environment, managers ask questions like "how many tickets does each team have?" or "which critical tickets are still open?" You answer those questions with SQL queries. In this step, you'll write three progressively more powerful queries that pull meaningful reports from your helpdesk data.

In this step, get ready to:
  • Write a SELECT query with WHERE and ORDER BY to find open tickets sorted by priority.
  • Write an aggregate query with GROUP BY and COUNT to summarize ticket counts per team.
  • Write a multi-table JOIN query that produces a human-readable ticket report.
Find open tickets sorted by priority

The most common question an IT manager asks is "what's open right now and what's most urgent?" A simple SELECT with a WHERE filter and custom ORDER BY answers that in one shot.

  • Switch back to DBeaver and open a new SQL editor tab connected to the helpdesk database.
  • Paste the following query into the editor and run it by clicking the orange play button (or pressing Ctrl+Enter):
-- Find all open tickets, ordered by priority
SELECT ticket_id, subject, priority, created_at
FROM tickets
WHERE status = 'open'
ORDER BY
    CASE priority
        WHEN 'critical' THEN 1
        WHEN 'high' THEN 2
        WHEN 'medium' THEN 3
        WHEN 'low' THEN 4
    END;

What does this query do?

This query filters the tickets table to only rows where status = 'open'. That's the WHERE clause doing its job.

The ORDER BY CASE expression assigns a number to each priority level so that critical tickets appear first and low tickets appear last. Without this, PostgreSQL would sort alphabetically, putting "critical" before "high" but "medium" after "low."

You should see 2 rows in the Results panel. The high priority VPN ticket appears first, followed by the medium priority email syncing ticket.

Seeing zero rows?

Make sure you're connected to the helpdesk database, not the default postgres database. Check the database name in the DBeaver connection dropdown at the top of your SQL editor.

Also confirm you completed Step 3 and inserted sample data. Run SELECT COUNT(*) FROM tickets; to check. You should see 6.

Why is my query returning no results from the helpdesk database?

Count tickets per team and status

Managers also want summaries, not just individual rows. Aggregate queries collapse many rows into counts, averages, or totals. This next query answers "how is the workload distributed across teams?"

  • Clear your SQL editor and paste this aggregate query, then run it:
-- Count tickets per team and status
SELECT
    t.team_name,
    tk.status,
    COUNT(*) AS ticket_count
FROM tickets tk
JOIN teams t ON tk.team_id = t.team_id
GROUP BY t.team_name, tk.status
ORDER BY t.team_name, tk.status;

What does this query do?

JOIN teams t ON tk.team_id = t.team_id connects each ticket to its team record so you can display the team name instead of a raw ID number.

GROUP BY t.team_name, tk.status collapses rows that share the same team and status into one group. COUNT(*) then tells you how many tickets are in each group.

You should see multiple rows showing each team's ticket breakdown by status. For example, Desktop Support should show tickets across several statuses.

Getting a GROUP BY error?

PostgreSQL requires every non-aggregated column in your SELECT to also appear in the GROUP BY clause. If you modified the query and added a column to SELECT, make sure it's also in GROUP BY.

Help me fix my GROUP BY query in PostgreSQL.

Write a JOIN query for a full ticket report

Raw ticket data stores IDs like requester_id = 5. That's useless to a human reader. A JOIN query pulls names from the users table so your report says "Bob Davis" instead of "5."

  • Clear your SQL editor and paste this multi-table JOIN query, then run it:
-- Full ticket report with user and team names
SELECT
    tk.ticket_id,
    tk.subject,
    tk.status,
    tk.priority,
    req.full_name AS requester,
    COALESCE(agent.full_name, 'Unassigned') AS assigned_to,
    t.team_name,
    tk.created_at
FROM tickets tk
JOIN users req ON tk.requester_id = req.user_id
LEFT JOIN users agent ON tk.assigned_agent_id = agent.user_id
LEFT JOIN teams t ON tk.team_id = t.team_id
ORDER BY tk.created_at DESC;

What does this query do?

This query joins the tickets table to the users table twice: once to get the requester's name (req) and once to get the assigned agent's name (agent). It also joins the teams table for the team name.

LEFT JOIN is used for the agent and team because some tickets have no assigned agent (the value is NULL). A regular JOIN would hide those unassigned tickets entirely. COALESCE(agent.full_name, 'Unassigned') replaces NULL with the text "Unassigned" so the report stays readable.

You should see all 6 tickets listed with full names instead of IDs. The "Email not syncing on phone" ticket should show "Unassigned" in the assigned_to column.

Seeing fewer than 6 rows?

If you used JOIN instead of LEFT JOIN for the agent or team, tickets with NULL values in those columns get excluded. Change those to LEFT JOIN to include all tickets regardless of whether they have an assigned agent.

Help me understand why my JOIN query is hiding some tickets.

Why LEFT JOIN instead of JOIN?

A regular JOIN (also called INNER JOIN) only returns rows where both sides have a match. A LEFT JOIN keeps every row from the left table (tickets) even if there's no matching row on the right (no assigned agent).

In helpdesk systems, unassigned tickets are the ones that need attention most. Hiding them from reports would defeat the purpose of the query.

✔️ Awesome, I've got everything!

Great. You can save all three queries into a single file called 04-queries.sql for future reference, or keep them in your DBeaver SQL editor tabs.

ⓧ I'd like to double check the full code

Here are all three queries together as they should appear in 04-queries.sql:

-- Find all open tickets, ordered by priority
SELECT ticket_id, subject, priority, created_at
FROM tickets
WHERE status = 'open'
ORDER BY
    CASE priority
        WHEN 'critical' THEN 1
        WHEN 'high' THEN 2
        WHEN 'medium' THEN 3
        WHEN 'low' THEN 4
    END;

-- Count tickets per team and status
SELECT
    t.team_name,
    tk.status,
    COUNT(*) AS ticket_count
FROM tickets tk
JOIN teams t ON tk.team_id = t.team_id
GROUP BY t.team_name, tk.status
ORDER BY t.team_name, tk.status;

-- Full ticket report with user and team names
SELECT
    tk.ticket_id,
    tk.subject,
    tk.status,
    tk.priority,
    req.full_name AS requester,
    COALESCE(agent.full_name, 'Unassigned') AS assigned_to,
    t.team_name,
    tk.created_at
FROM tickets tk
JOIN users req ON tk.requester_id = req.user_id
LEFT JOIN users agent ON tk.assigned_agent_id = agent.user_id
LEFT JOIN teams t ON tk.team_id = t.team_id
ORDER BY tk.created_at DESC;

You can now pull real answers from your database with a single query. Next up, you'll lock down access so that not everyone can run queries like these.

Manage Users and Permissions

In the previous step, you queried your helpdesk data to answer real business questions. Now it's time to think about who should be allowed to access that data.

In production, you never give everyone full admin access to a database. A support agent needs to read and update tickets, but they should never be able to delete tables or view sensitive user records. PostgreSQL handles this through roles and permissions.

In this step, get ready to:
  • Create a restricted database role for support agents.
  • Grant specific permissions on selected tables only.
  • Test the restricted access to confirm the permissions work.
Create the support_agent role

A role in PostgreSQL is like a user account with configurable abilities. You can control whether it can log in, create databases, or access specific tables.

  • In DBeaver, make sure you are connected to the helpdesk database as postgres (your existing connection from earlier).
  • Open a new SQL editor tab by pressing Ctrl+] (macOS) or Ctrl+] (Windows), or click SQL Editor in the menu bar and select New SQL Editor.
  • Create the restricted role by running this SQL:
-- Create a restricted support_agent role
CREATE ROLE support_agent WITH LOGIN PASSWORD 'agent_pass_123';

What does this code do?

CREATE ROLE creates a new database role called support_agent.

The WITH LOGIN option allows this role to connect to the database (without it, the role exists but cannot log in). The PASSWORD sets the credentials needed to authenticate.

  • Run the statement by clicking the execute button or pressing Ctrl+Enter (macOS/Windows).
  • You should see a success message in the results panel confirming the role was created.

Seeing "role already exists"?

If you ran this statement before, the role already exists. You can drop it first with DROP ROLE support_agent; and then re-run the CREATE statement.

help me fix a role already exists error in PostgreSQL

Grant permissions on the helpdesk database

Creating the role is only the first half. By default, a new role has no access to anything. You need to explicitly grant each permission using the GRANT command.

  • Grant the support agent access to the database, schema, tables, and sequences by running this SQL in the same editor:
-- Grant connect privilege on the helpdesk database
GRANT CONNECT ON DATABASE helpdesk TO support_agent;

-- Grant usage on the public schema
GRANT USAGE ON SCHEMA public TO support_agent;

-- Grant SELECT, INSERT, UPDATE on tickets and ticket_comments only
GRANT SELECT, INSERT, UPDATE ON tickets, ticket_comments TO support_agent;

-- Grant usage on sequences so the role can insert rows
GRANT USAGE ON SEQUENCE tickets_ticket_id_seq, ticket_comments_comment_id_seq TO support_agent;

What does this code do?

  • GRANT CONNECT allows the role to connect to the helpdesk database at all.
  • GRANT USAGE ON SCHEMA public lets the role see objects in the default schema.
  • GRANT SELECT, INSERT, UPDATE on tickets and ticket_comments gives the role read and write access to those two tables only. Notice there is no DELETE or DROP permission.
  • GRANT USAGE ON SEQUENCE allows the role to use the auto-increment sequences when inserting new rows. Without this, INSERT would fail because SERIAL columns need sequence access.

Why not just grant ALL PRIVILEGES?

The principle of least privilege means giving users only the access they need to do their job. A support agent needs to read tickets and add comments, but they should never be able to delete data, drop tables, or view sensitive user information like emails.

This is a core DBA responsibility. If a support agent account gets compromised, the damage is limited to what that role can do.

  • Run all four GRANT statements.
  • You should see a success message for each statement in the results panel.

Seeing a permission denied error?

Make sure you are connected as postgres (the superuser). Only a superuser or the object owner can grant permissions on tables.

help me fix permission denied when granting privileges in PostgreSQL

✔️ Awesome, I've got everything!

Great. Double check you have saved your SQL file.

ⓧ I'd like to double check the full code

-- Create a restricted support_agent role
CREATE ROLE support_agent WITH LOGIN PASSWORD 'agent_pass_123';

-- Grant connect privilege on the helpdesk database
GRANT CONNECT ON DATABASE helpdesk TO support_agent;

-- Grant usage on the public schema
GRANT USAGE ON SCHEMA public TO support_agent;

-- Grant SELECT, INSERT, UPDATE on tickets and ticket_comments only
GRANT SELECT, INSERT, UPDATE ON tickets, ticket_comments TO support_agent;

-- Grant usage on sequences so the role can insert rows
GRANT USAGE ON SEQUENCE tickets_ticket_id_seq, ticket_comments_comment_id_seq TO support_agent;
Test the restricted access as support_agent

The best way to confirm permissions work is to connect as the restricted role and try things that should succeed and things that should fail.

  • In DBeaver, click Database in the menu bar.
  • Click New Database Connection.
  • Select PostgreSQL from the driver list and click Next.
  • Enter the following connection settings:
  • Host: localhost
  • Port: 5432
  • Database: helpdesk
  • Username: support_agent
  • Password: agent_pass_123
  • Click Test Connection to verify it works.
  • Click Finish to save the connection.

What should I see?

A new connection appears in the Database Navigator panel on the left side of DBeaver. You now have two connections: one as postgres and one as support_agent.

Now test what the restricted role can and cannot do.

  • Open a new SQL editor on the support_agent connection (right-click the new connection and select SQL Editor, then New SQL Editor).
  • Test a permitted query by running:
-- This should succeed (support_agent has SELECT on tickets)
SELECT ticket_id, subject, status FROM tickets LIMIT 3;
  • You should see 3 rows of ticket data returned successfully.
  • Now test an action that should be denied. Run this:
-- This should FAIL (support_agent has no DROP privilege)
DROP TABLE tickets;
  • You should see a permission denied error. This confirms the role cannot destroy tables.
  • Try one more restricted action:
-- This should FAIL (support_agent has no SELECT on users table)
SELECT * FROM users;
  • You should see another permission denied error. The support agent cannot view the users table at all.

What just happened?

You proved that your permission model works. The support_agent role can read and update tickets (their job), but cannot drop tables or access sensitive user data.

This is exactly how production databases are secured. Each team or application gets a role with only the permissions it needs.

Not seeing a permission denied error?

Make sure your SQL editor is connected to the support_agent connection, not the postgres connection. Check the connection name in the toolbar at the top of the SQL editor tab.

help me verify I'm connected as the correct user in DBeaver

Your database now has proper access controls. Next up, you'll learn how to back up your entire database so it can be recovered in a disaster.

Back Up Your Database

You now have a fully functional helpdesk database with tables, data, queries, and restricted user roles. The final skill every DBA needs is knowing how to back up a database for disaster recovery.

A backup captures the entire state of your database as a SQL file. If something goes wrong, you can restore from this file to get everything back exactly as it was. You'll use pg_dump, PostgreSQL's built-in backup tool, to create a plain SQL dump of your helpdesk database.

In this step, get ready to:
  • Create a full SQL backup of the helpdesk database using pg_dump.
  • Copy the backup file from the Docker container to your local machine.
  • Verify the backup contains all your tables and data.
Run pg_dump inside the Docker container

Since PostgreSQL is running inside a Docker container, you don't have pg_dump installed on your local machine. You'll use docker exec to run the backup command inside the container where PostgreSQL lives.

  • Switch back to your terminal.
  • Create a backup of the helpdesk database by running this command:
docker exec helpdesk-pg pg_dump -U postgres helpdesk > helpdesk_backup.sql

What does this command do?

  • docker exec helpdesk-pg runs a command inside the running helpdesk-pg container.
  • pg_dump -U postgres helpdesk tells PostgreSQL to export the entire helpdesk database as SQL statements.
  • > helpdesk_backup.sql saves the output to a file called helpdesk_backup.sql.

What does pg_dump capture?

pg_dump dumps only a single database. It does not dump roles or tablespaces (those are cluster-wide data). For full cluster backups you would use pg_dumpall instead.

  • You should see the command complete with no error output.

Seeing a permission error or empty output?

  • Make sure the container is still running. Run docker ps and confirm helpdesk-pg appears in the list.
  • Check that you typed the database name helpdesk correctly. A typo produces a "database does not exist" error.

Ask for help: Why is my pg_dump command failing inside the Docker container?

Copy the backup to your local machine

The docker cp command copies files between a container and your local filesystem. This is how you retrieve files that were generated inside a container.

  • Copy the backup file from the container to your current directory by running:
docker cp helpdesk-pg:/helpdesk_backup.sql ./helpdesk_backup.sql

What does this command do?

  • docker cp copies files to or from a container.
  • helpdesk-pg:/helpdesk_backup.sql is the source path inside the container.
  • ./helpdesk_backup.sql is the destination on your local machine (current directory).
  • Confirm the file exists on your local machine by running:
ls helpdesk_backup.sql
  • You should see helpdesk_backup.sql listed in the output.

File not found?

  • Make sure you spelled the container name helpdesk-pg and the file path /helpdesk_backup.sql exactly as shown.
  • Check that the container is still running with docker ps. The container must be running for docker cp to work.

Ask for help: Why can't I copy my backup file from the Docker container?

Verify the backup file contents

A good DBA never trusts a backup without verifying it. You'll open the file to confirm it contains the SQL statements needed to recreate your entire database.

  • View the first 50 lines of your backup file by running:
head -50 helpdesk_backup.sql
  • You should see SQL statements including CREATE TABLE commands for your tables (teams, users, tickets, ticket_comments).
  • Scroll down further in the file and you'll find INSERT statements containing all your sample data.

What should I see in the backup?

A plain SQL backup from pg_dump is a complete recipe to recreate your database. It contains:

  • CREATE TABLE statements that define your schema (columns, types, constraints).
  • INSERT statements that repopulate all your data.
  • Sequence settings, indexes, and constraints.

To restore this backup you would run psql -X dbname < dumpfile. That replays every SQL statement to rebuild the database from scratch.

Seeing an empty file or garbled output?

  • If the file is empty (0 bytes), the pg_dump command may have failed silently. Re-run the backup command from substep 1 and watch for error messages.
  • If you see binary/garbled text, you may have accidentally used a custom format. Re-run the command exactly as shown above to get plain SQL output.

Ask for help: My backup file doesn't look right. What should a pg_dump SQL backup contain?

✔️ Awesome, I've got everything!

Great. Make sure you've saved your backup file somewhere safe.

ⓧ I'd like to double check the full code

Here is the complete reference for the backup commands used in this step:

-- This is run from the command line, not inside psql:
-- docker exec helpdesk-pg pg_dump -U postgres helpdesk > helpdesk_backup.sql

-- To copy the backup to your local machine:
-- docker cp helpdesk-pg:/helpdesk_backup.sql ./helpdesk_backup.sql

Secret mission

Priority Escalation View and Audit Trigger

Real IT teams need to know when critical tickets are sitting too long without resolution. They also need a paper trail of every status change for compliance and accountability. In this secret mission, you'll build both: an escalation view that surfaces urgent tickets, and an automatic audit trigger that logs every status change without any human intervention.

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 inside a Docker container on your local machine, so there are no ongoing cloud costs.

Resources you used:

  • Docker container: helpdesk-pg (running PostgreSQL 17 on port 5432).
  • Docker volume: helpdesk-data (stores all your database files).
  • Local backup file: helpdesk_backup.sql (copied to your local machine).
  • DBeaver connection to localhost:5432.

Keep everything running

No action needed. Choose this if you're still actively building or want to practice more SQL queries against your helpdesk database.

  • Your helpdesk-pg container continues running in the background.
  • DBeaver stays connected and ready for new queries.
  • Docker Desktop uses some CPU and memory while the container is active.

Pause - I'll come back to this later

Stop the container to free up memory and CPU. Your data is preserved in the Docker volume, so everything will be exactly as you left it when you restart.

  • Stop the container by running this command in your terminal:
docker stop helpdesk-pg
  • When you're ready to come back, restart it with:
docker start helpdesk-pg
  • Reconnect in DBeaver by double-clicking your existing connection.

Delete - I don't want to use this again

Remove all project resources completely. This deletes the container, all database data in the volume, and your local backup file.

  • Stop and remove the Docker container by running these commands:
docker stop helpdesk-pg
docker rm helpdesk-pg
  • Remove the Docker volume that stores your database files:
docker volume rm helpdesk-data
  • Delete the local backup file:
rm helpdesk_backup.sql
  • In DBeaver, right-click your PostgreSQL connection and select Delete to remove the saved connection.

Nice Work!

Nice Work!

You just completed your first DBA project. You built a fully functional IT Helpdesk Ticket System database from scratch, complete with realistic data, business queries, security controls, and a disaster recovery backup.

You've learned how to:

  • Build a running PostgreSQL server in Docker and design a relational database schema with primary keys and foreign key relationships.
  • Write SQL queries with JOIN, GROUP BY, and aggregate functions to produce real business reports from your helpdesk data.
  • Create restricted database roles with GRANT permissions and perform a full database backup with pg_dump.
  • Secret Mission: Built a priority escalation view and an audit trigger that automatically logs ticket status changes.

Ready to quiz yourself?