SELECT basics

SELECT is just "give me these fields from that table". Projection, if you want the fancy word.

For this chapter, imagine we've got a tiny table already (we'll create tables properly later). You can paste this into sqlite3 playground.db:

CREATE TABLE contacts (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  city TEXT
);

INSERT INTO contacts (name, email, city) VALUES
  ('Ada Lovelace', 'ada@example.com', 'London'),
  ('Grace Hopper', 'grace@example.com', 'New York'),
  ('Alan Turing', 'alan@example.com', 'Manchester');

All columns

SELECT * FROM contacts;

* means every column. Fine for exploring. In real APIs / production queries, prefer naming the columns you need - less data over the wire, clearer intent.

Named columns (projection)

SELECT name, email FROM contacts;

FE analogy: .map(c => ({ name: c.name, email: c.email })).

Aliases

Rename columns in the result (handy for APIs that want camelCase, or shorter names):

SELECT
  name AS full_name,
  email AS email_address
FROM contacts;

Or table aliases when things get bigger:

SELECT c.name, c.email
FROM contacts AS c;

Expressions

You're not limited to raw columns:

SELECT
  name,
  upper(email) AS email_upper,
  length(name) AS name_len
FROM contacts;

Comments for the code above:

• upper / length are functions - SQLite has a bunch; Postgres/MySQL have their own catalogs
• AS names the computed column in the result set

DISTINCT

Kill duplicates in the result:

SELECT DISTINCT city FROM contacts;

Like [...new Set(contacts.map(c => c.city))], with the usual NULL caveats.

Order of the clauses (preview)

A full select usually looks like:

SELECT …
FROM …
WHERE …
GROUP BY …
HAVING …
ORDER BY …
LIMIT …;

We'll fill those in. For now: SELECT + FROM is enough to peek at data.

As you can see - reading rows is the friendly part. Filtering is next: Filter, sort, limit.