Installing PostgreSQL and Writing First Queries

What Is PostgreSQL and Why Does It Matter for Data Analytics?

PostgreSQL is a free, open-source relational database management system (RDBMS) with a strong reputation for standards compliance, reliability, and advanced data types. Many employers in data analytics use PostgreSQL or a cloud variant of it (such as Amazon Aurora or Google Cloud SQL). Learning it prepares you for the Google Data Analytics Certificate, CompTIA Data+, IBM Data Science Professional, and Microsoft PL-300 environments, all of which expect you to read and write SQL fluently.

A relational database organizes data into tables made of rows and columns. Every table has a defined structure called a schema, which names each column and declares its data type (for example, `INTEGER`, `TEXT`, or `DATE`). SQL—Structured Query Language—is the standard language for asking questions of, and making changes to, those tables.

Installing PostgreSQL Safely on a Workstation VM

You are working inside a virtual machine (VM), not on your host computer. This is intentional: VMs provide an isolated environment so that a misconfigured database service cannot affect your host OS or other students' machines on the network.

Before you begin any installation:

• Confirm with your instructor that the VM snapshot has been taken. If your install fails, the snapshot lets you roll back without losing work.
• Do not run the installer as a shared network admin account. Use the local VM credentials your instructor provided.
• PostgreSQL runs as a background service. On Windows it registers as a Windows Service; on Linux it runs as a systemd unit. Either way, it listens on a network port (default 5432). Keep the firewall rule restricted to `localhost` only—never expose a lab database to the open internet.
• Choose a strong password for the `postgres` superuser during installation. Write it in your lab notebook; you will need it throughout the course.

After the installer finishes, open a terminal and run:

```

psql -U postgres -c "SELECT version();"

```

A successful response prints the PostgreSQL version string. If you see an error, stop and raise your hand—do not move forward until the service is confirmed running. Your instructor will inspect each machine at this checkpoint.

pgAdmin 4: The Graphical Tool

pgAdmin 4 is the official web-based GUI for PostgreSQL. It runs in your browser (pointed at `localhost`) and lets you browse database objects, write queries in an editor, and view results in a table. Use it alongside the `psql` command-line tool—professional analysts switch between the two depending on the task. The command line is faster for scripting and bulk data loads; pgAdmin is clearer for exploring an unfamiliar schema.

Creating a Database and Loading Data

Before you can query data, you need a database to hold it. Inside `psql`:

```sql

CREATE DATABASE library_checkouts;

\c library_checkouts

```

The `\c` meta-command connects you to that database. Your instructor will provide a `CREATE TABLE` script that defines the schema for the library-checkout records. Run it, then load the CSV with `\copy`:

```

\copy checkouts FROM '/path/to/library_checkouts.csv' CSV HEADER;

```

`\copy` runs on the client side—it reads the file from your local machine rather than requiring the file to be on the server. After the load completes, always verify the row count:

```sql

SELECT COUNT(*) FROM checkouts;

```

The result should match the row count printed on the CSV's README. If it does not, stop and consult your instructor before writing any analysis queries—bad input data produces bad results.

The Core SELECT Statement

The four clauses you will practice today form the backbone of analytical querying:

SELECT names the columns (or expressions) you want to see.

FROM names the table.

WHERE filters rows before they are returned.

ORDER BY sorts the result set.

LIMIT caps the number of rows returned.

General form:

```sql

SELECT column1, column2

FROM table_name

WHERE condition

Order BY column1 DESC

LIMIT n;

```

PostgreSQL evaluates these clauses in a logical order that differs from the written order: FROM → WHERE → SELECT → ORDER BY → LIMIT. Understanding this order helps you predict what each clause can and cannot reference.

Working Through the Query Sprint

For each worksheet challenge, follow this discipline:

1. Read the question carefully and identify which columns you need.

2. Write the query in your `.sql` file with a comment line (`-- Challenge 3`) above it.

3. Run it in pgAdmin or `psql` and inspect the first several rows for reasonableness.

4. Save the file before moving to the next challenge.

This habit—comment, write, run, inspect, save—mirrors professional workflow and makes it easy for your instructor to review your work.

Data Integrity and Lab Notebook Records

Record every important step in your lab notebook: the PostgreSQL version installed, the row count after the load, and any error messages with their solutions. Good documentation is a professional habit and will help you when you sit certification exams that ask about troubleshooting scenarios.