Databases, tables and your first SELECT

Why this matters

Most people who need numbers from a database ask someone else for them, wait, and then discover the report answers a slightly different question. Being able to open the database yourself and ask the question directly is the whole point of this course. The language for that is SQL, and the tool you will use on the lab machine is psql, the PostgreSQL command-line client.

Throughout the course you are the new analyst at Ultishop, a small (fictional) online retailer of home, outdoor and hobby goods. Its operational data lives in a PostgreSQL database called ultishop on your lab machine db01. Nobody has documented it, the sales team wants a quarterly review, and the data has the kind of rough edges real data has. By the end of the course you will produce that review.

This first lesson is deliberately small: connect, look around, run a handful of queries and save one to a file. Everything else builds on these habits.

Concepts

A relational database stores facts in tables. A table is a grid: each row is one thing (one customer, one order), each column is one attribute of that thing (a name, a date, a price) with a fixed data type (integer, text, date, numeric). Tables are related to each other through keys: a primary key is a column that uniquely identifies a row (customer_id), and a foreign key is a column in one table that holds a primary key from another (orders.customer_id points at a row in customers). Tables are grouped into a schema, which is just a named folder inside the database.

SQL (Structured Query Language) is how you talk to the database. A query is a statement that asks for data; the database returns a result set, which is itself a grid of rows and columns. The most important statement is SELECT. Its minimal form names the columns you want and the table they come from, and ends with a semicolon:

SELECT first_name, last_name, signup_date FROM customers LIMIT 5;

 first_name | last_name | signup_date
------------+-----------+-------------
 Isla       | Lindqvist | 2024-03-31
 Vera       | Nakamura  | 2023-11-07
 Quinn      | Gallagher | 2023-11-22
 Lena       | Santos    | 2024-02-28
 Hana       | Delgado   | 2024-03-26
(5 rows)

The block above shows the query and the first five customers exactly as psql printed them: a header row, a separator, the data, and a footer with the row count. LIMIT 5 stops the database after five rows. Without an ORDER BY clause the database returns rows in whatever order is convenient for it, which is usually insertion order on a fresh table but is not guaranteed. When order matters, say so:

SELECT customer_id, first_name, last_name, signup_date
FROM customers
ORDER BY customer_id
LIMIT 5;

SELECT * means "every column" and is fine for a quick look, but name columns in anything you keep. SQL keywords are case-insensitive (select, SELECT and Select all work) and so are unquoted table and column names; the convention in this course is upper-case keywords and lower-case names. A statement can span several lines; psql only sends it when it sees the semicolon.

psql adds its own commands, which start with a backslash and do not need a semicolon. The ones you will use constantly: \dt shop.* lists the tables in the shop schema, \d orders describes a table (columns, types, keys), \x toggles an expanded one-column-per-line display that is easier to read for wide rows, \w file writes the last query to a file, \i file runs a file, and \q quits. Here is what \d customers prints; read it as the table's contract: which columns exist, what type each has, which may be empty (Nullable blank means NULL is allowed), and which other tables refer to it.

                    Table "shop.customers"
      Column      |   Type   | Collation | Nullable | Default
------------------+----------+-----------+----------+---------
 customer_id      | integer  |           | not null |
 first_name       | text     |           | not null |
 last_name        | text     |           | not null |
 email            | text     |           |          |
 region_id        | smallint |           |          |
 signup_date      | date     |           | not null |
 marketing_opt_in | boolean  |           | not null | false
Indexes:
    "customers_pkey" PRIMARY KEY, btree (customer_id)
Foreign-key constraints:
    "customers_region_id_fkey" FOREIGN KEY (region_id) REFERENCES regions(region_id)
Referenced by:
    TABLE "orders" CONSTRAINT "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    TABLE "support_tickets" CONSTRAINT "support_tickets_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

Two things about your lab account. You connect as the role analyst, which can read every table in the shop schema but cannot change them, and which owns a schema of its own, also called analyst, where anything you create will go. Your search path is set to analyst, shop, so you can write customers instead of shop.customers and the database finds it.

Guided exercise

1. Open the lab terminal on db01 and connect. Your lab has already saved the analyst password in ~/.pgpass, so psql does not ask for it; the lab console shows the password if you ever need to type it yourself.

``bash psql -h localhost -U analyst -d ultishop

You should see a prompt ending in ultishop=>. The > (rather than #) tells you that you are not a superuser, which is exactly right for an analyst. Confirm where you are:

``sql SELECT current_user, current_database();

``text current_user | current_database --------------+------------------ analyst | ultishop (1 row)

1. Check the server version. This course was verified on PostgreSQL 16.15; the lab runs PostgreSQL 16 from the Ubuntu 24.04 archive, so your minor version may differ, and that is fine.

``sql SELECT version();

``text PostgreSQL 16.15 (Debian 16.15-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit

1. List the schemas and the tables, then describe orders. The \dn output shows three schemas: yours (analyst), the shared public and the data (shop). \dt shop.* lists eight tables: categories, customers, order_items, orders, payments, products, regions and support_tickets.

``sql \dn \dt shop.* \d orders

In the orders description, notice order_date | timestamp without time zone (a date and a time) and shipping_fee | numeric(8,2) (an exact decimal with two places). The Referenced by section tells you that order_items, payments and support_tickets all point at an order.

1. Run your first three queries and read the footer of each one.

``sql SELECT * FROM regions; SELECT count(*) FROM customers; SELECT count(*) FROM orders;

regions has 6 rows (Northgate, Eastmoor, Southbank, Westfield, Central Plains, Island Territories), customers has 2000 and orders has 12000. count(*) returns the number of rows; you will meet it properly in lesson 4.

1. Look at ten orders in order, then switch to expanded display for a single wide row and back.

``sql SELECT order_id, customer_id, order_date, status FROM orders ORDER BY order_id LIMIT 10; \x SELECT * FROM products WHERE product_id = 1; \x

``text -[ RECORD 1 ]---+-------------------------- product_id | 1 sku | KIT-0001 product_name | Compact Cast-Iron Skillet category_id | 1 unit_price | 10.00 cost_price | 4.97 introduced_on | 2024-01-18 discontinued_on | active | t

The expanded record shows every column of product 1 on its own line; discontinued_on is empty because the product is still on sale. That empty value is NULL, the subject of the next lesson.

1. Save a query to a file and run it back. \w writes the most recently executed query, and \! runs a shell command without leaving psql.

``sql \! mkdir -p ~/sql SELECT product_id, sku, product_name, unit_price FROM products ORDER BY unit_price DESC LIMIT 10; \w ~/sql/01-first-select.sql \i ~/sql/01-first-select.sql

The query lists the ten most expensive products, starting with the Camping Table at 194.99 and the Sleeping Bag at 155.99. Running \i prints the same ten rows again, this time from the file.

1. Type \q to leave psql. Reconnect whenever you like; nothing you did here changed any data.

Troubleshooting

`psql: error: connection to server at "localhost" ... failed: Connection refused` → the PostgreSQL service is not running or not listening on the port → check systemctl status postgresql@16-main in the lab terminal and use the lab reset if it is stopped. Look at that unit, not plain postgresql: on Ubuntu postgresql is only an umbrella unit that shows active (exited) even while the database server is down.

`password authentication failed for user "analyst"` → wrong password or wrong role name → psql only asks when ~/.pgpass is missing or edited; copy the password from the lab console again, and remember that role names are case-sensitive when quoted.

`ERROR: relation "customer" does not exist` → the table name is wrong (here the singular) → run \dt shop.* and use the name exactly as listed. The same message appears when your search path does not include the schema; \d shop.customers with the schema prefix always works.

`ERROR: column "firstname" does not exist` with HINT: Perhaps you meant to reference the column "customers.first_name" → a column name typo → PostgreSQL's hint is usually right; \d customers shows the real names.

The prompt changed to `ultishop->` or `ultishop-#` and nothing happens → psql is waiting for the end of the statement → type ; and press Enter. If the prompt shows ultishop'> you have an unclosed quote; type ' then ;. Press <kbd>Ctrl</kbd>+<kbd>C</kbd> to abandon the current input.

Check your understanding

1. Why does SELECT * FROM customers LIMIT 5; not guarantee the same five rows every time, and what do you add to make it predictable?
2. What is the difference between a primary key and a foreign key in the orders table?

Summary and next step

• A relational database is tables of typed rows linked by keys; psql is your window into it.
• SELECT columns FROM table ORDER BY column LIMIT n; is the shape of every query you will write.
• \dt, \d, \x, \w and \i save you from guessing and from retyping.

Next, in *Filtering, sorting and NULL*, you learn to ask for exactly the rows you want with WHERE, and you meet NULL, the value that is not a value.

References

• PostgreSQL 16 Documentation, psql — https://www.postgresql.org/docs/16/app-psql.html (accessed 2026-09-13)
• PostgreSQL 16 Documentation, Querying a Table (tutorial) — https://www.postgresql.org/docs/16/tutorial-select.html (accessed 2026-09-13)
• PostgreSQL 16 Documentation, SELECT — https://www.postgresql.org/docs/16/sql-select.html (accessed 2026-09-13)
• PostgreSQL 16 Documentation, Concepts (tutorial) — https://www.postgresql.org/docs/16/tutorial-concepts.html (accessed 2026-09-13)