SQL and PostgreSQL basics
Write everyday SQL queries and joins, design a small database, then install, secure and back up PostgreSQL on a Linux server.
Beginner, 10 steps, about 10 hours
- Your first SQL queries: SELECT, WHERE, ORDER BY, LIMIT and GROUP BY Start with reading data; these five clauses cover most daily queries. Article CMP 0067
- NULL in SQL: why = NULL never matches and how to handle missing values Learn the NULL rules early, before they quietly break your results. Article CMP 0069
- SQL joins explained: INNER, LEFT and finding rows with no match Joins are where SQL gets powerful; focus on the difference between INNER and LEFT. Article CMP 0068
- Fix: column “x” must appear in the GROUP BY clause or be used in an aggregate function A classic GROUP BY error that teaches how aggregation really works. Article CMP 0071
- Design and build a library database in SQLite: tables, keys, joins, reports and backups Practice design and queries in SQLite, which needs no server at all. Article CMP 0260
- Install PostgreSQL and connect with psql on Debian, Ubuntu and RHEL Move to a real database server and connect to it from the command line. Article CMP 0198
- psql meta-commands cheat sheet Learn the backslash commands for listing tables, users and databases. Article CMP 0199
- PostgreSQL roles, privileges and default privileges Create an application role that can only do what it needs. Article CMP 0200
- Back up and restore PostgreSQL with pg_dump, pg_restore and pg_dumpall Take a backup and restore it once, so you know it works before you need it. Article CMP 0202
- Run PostgreSQL properly on a Linux server: install, lock down, back up, monitor and patch Capstone: run the whole server lifecycle from install to patching. Article CMP 0257