Concepts

SELECT columns FROM table reads rows. Name the columns you need. * means all of them, which is fine for poking around and wasteful in an app.
WHERE condition keeps only matching rows. Combine with AND / OR. ILIKE '%word%' is a case-insensitive "contains". Test for missing values with IS NULL, never = NULL.
ORDER BY column DESC sorts. Without ORDER BY, row order is not guaranteed, even if it looks stable.
LIMIT n returns the first n rows. OFFSET n skips n first. Together they give you pages.
A JOIN follows a foreign key to pull in columns from another table: FROM posts JOIN users ON users.id = posts.user_id. Each post row gets its author's columns attached. Give tables short aliases (FROM posts p JOIN users u ON ...) and prefix columns with them.
To cross a many-to-many, join twice: through the junction table and out the other side.
The clauses always go in this order: SELECT ... FROM ... JOIN ... WHERE ... ORDER BY ... LIMIT ...