SELECT and FROM: columns, expressions, aliases

Most queries in this track have the same two clauses at their heart: SELECT says which columns to return, and FROM says which table to read them from. This lesson covers both, with the products table of the mart shop from the previous lesson: choosing columns, computing new ones, naming them, the quoting rules that trip people up in every engine, and comments and layout.

Choose the columns

-- The products table of the mart shop.
CREATE TABLE products (
  product_id  INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  price_paise INTEGER NOT NULL      -- 100 paise = 1 rupee
);
INSERT INTO products (product_id, name, category, price_paise) VALUES
  (1, 'Basmati rice 1 kg', 'Grains',    14999),
  (2, 'Toor dal 500 g',    'Pulses',     8950),
  (3, 'Masala chai 250 g', 'Beverages', 12000),
  (4, 'Coconut oil 1 L',   'Oils',      21500),
  (5, 'Ragi flour 1 kg',   'Grains',     6800);

-- The columns you list, in the order you list them.
SELECT name, price_paise FROM products;

-- Every column, in the table's own order.
SELECT * FROM products;

┌───────────────────┬─────────────┐
│       name        │ price_paise │
├───────────────────┼─────────────┤
│ Basmati rice 1 kg │ 14999       │
│ Toor dal 500 g    │ 8950        │
│ Masala chai 250 g │ 12000       │
│ Coconut oil 1 L   │ 21500       │
│ Ragi flour 1 kg   │ 6800        │
└───────────────────┴─────────────┘
┌────────────┬───────────────────┬───────────┬─────────────┐
│ product_id │       name        │ category  │ price_paise │
├────────────┼───────────────────┼───────────┼─────────────┤
│ 1          │ Basmati rice 1 kg │ Grains    │ 14999       │
│ 2          │ Toor dal 500 g    │ Pulses    │ 8950        │
│ 3          │ Masala chai 250 g │ Beverages │ 12000       │
│ 4          │ Coconut oil 1 L   │ Oils      │ 21500       │
│ 5          │ Ragi flour 1 kg   │ Grains    │ 6800        │
└────────────┴───────────────────┴───────────┴─────────────┘

FROM products names the table to read. The list after SELECT fixes which columns come back and in which order: the first query asks for name and then price_paise, and gets exactly those two. There is no WHERE yet, so every row of the table comes back; the next module covers choosing rows.

* means every column, in the order the table defines them. It is the quickest way to look at a table you do not know yet. In queries that a program depends on, list the columns instead:

• The result keeps the same columns when somebody adds a column to the table later; with *, a new column changes what the program receives.
• The program gets only the data it uses, which matters once tables have many columns or large values.

Compute new columns

Each item of the SELECT list can be an expression: arithmetic, a function call such as round(), or a fixed value. The engine works it out once for every row.

CREATE TABLE products (
  product_id  INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  price_paise INTEGER NOT NULL      -- 100 paise = 1 rupee
);
INSERT INTO products (product_id, name, category, price_paise) VALUES
  (1, 'Basmati rice 1 kg', 'Grains',    14999),
  (2, 'Toor dal 500 g',    'Pulses',     8950),
  (3, 'Masala chai 250 g', 'Beverages', 12000),
  (4, 'Coconut oil 1 L',   'Oils',      21500),
  (5, 'Ragi flour 1 kg',   'Grains',     6800);

SELECT name,
       price_paise / 100                  AS whole_rupees,
       price_paise / 100.0                AS rupees,
       round(price_paise * 0.9 / 100, 2)  AS sale_rupees
FROM products;

┌───────────────────┬──────────────┬────────┬─────────────┐
│       name        │ whole_rupees │ rupees │ sale_rupees │
├───────────────────┼──────────────┼────────┼─────────────┤
│ Basmati rice 1 kg │ 149          │ 149.99 │ 134.99      │
│ Toor dal 500 g    │ 89           │ 89.5   │ 80.55       │
│ Masala chai 250 g │ 120          │ 120.0  │ 108.0       │
│ Coconut oil 1 L   │ 215          │ 215.0  │ 193.5       │
│ Ragi flour 1 kg   │ 68           │ 68.0   │ 61.2        │
└───────────────────┴──────────────┴────────┴─────────────┘

• price_paise / 100 divides one whole number by another, so SQLite keeps only the whole rupees: 14999 / 100 is 149. PostgreSQL does the same; MySQL keeps the fraction.
• price_paise / 100.0 has a decimal point on one side, so the paise survive: 149.99.
• round(price_paise * 0.9 / 100, 2) is the price with 10% off, rounded to two decimal places. SQLite prints such numbers as briefly as it can, 89.5 rather than 89.50, and whole ones with .0, as in 120.0; formatting numbers for display is a job for the text functions of a later module.

A fixed value repeats on every row: 'per pack' AS unit would add a column holding that text in each row.

Name the columns with AS

CREATE TABLE products (
  product_id  INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  price_paise INTEGER NOT NULL      -- 100 paise = 1 rupee
);
INSERT INTO products (product_id, name, category, price_paise) VALUES
  (1, 'Basmati rice 1 kg', 'Grains',    14999),
  (2, 'Toor dal 500 g',    'Pulses',     8950),
  (3, 'Masala chai 250 g', 'Beverages', 12000),
  (4, 'Coconut oil 1 L',   'Oils',      21500),
  (5, 'Ragi flour 1 kg',   'Grains',     6800);

-- Without AS, the engine names a computed column as it likes.
SELECT name, price_paise / 100.0 FROM products;

-- With AS, you choose. Quotes allow spaces and capitals, but plain names
-- in lower case are easier to type in every later query.
SELECT name                AS product,
       price_paise / 100.0 AS "Price in rupees",
       price_paise / 100.0 AS price_rupees
FROM products;

┌───────────────────┬─────────────────────┐
│       name        │ price_paise / 100.0 │
├───────────────────┼─────────────────────┤
│ Basmati rice 1 kg │ 149.99              │
│ Toor dal 500 g    │ 89.5                │
│ Masala chai 250 g │ 120.0               │
│ Coconut oil 1 L   │ 215.0               │
│ Ragi flour 1 kg   │ 68.0                │
└───────────────────┴─────────────────────┘
┌───────────────────┬─────────────────┬──────────────┐
│      product      │ Price in rupees │ price_rupees │
├───────────────────┼─────────────────┼──────────────┤
│ Basmati rice 1 kg │ 149.99          │ 149.99       │
│ Toor dal 500 g    │ 89.5            │ 89.5         │
│ Masala chai 250 g │ 120.0           │ 120.0        │
│ Coconut oil 1 L   │ 215.0           │ 215.0        │
│ Ragi flour 1 kg   │ 68.0            │ 68.0         │
└───────────────────┴─────────────────┴──────────────┘

Without AS, the engine names a computed column itself. SQLite used the expression’s text here, PostgreSQL would call it ?column?, and SQLite’s own documentation says that the name of a column without an AS is not promised and may change from one version to the next. Give every computed column a name with AS; a query that a program reads by column name depends on it.

An alias with spaces needs double quotes, as "Price in rupees" shows, and in PostgreSQL so does one whose capital letters must be kept. Names in lower case with underscores, such as price_rupees, need no quotes (unless the name is one of SQL’s own words, as the section on quotes shows), so they are easier to use in the rest of the query and in the code that reads the result.

A missing comma makes an alias

The word AS is optional: price_paise / 100.0 price_rupees names the column too. That is why this mistake raises no error:

CREATE TABLE products (
  product_id  INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  price_paise INTEGER NOT NULL      -- 100 paise = 1 rupee
);
INSERT INTO products (product_id, name, category, price_paise) VALUES
  (1, 'Basmati rice 1 kg', 'Grains',    14999),
  (2, 'Toor dal 500 g',    'Pulses',     8950),
  (3, 'Masala chai 250 g', 'Beverages', 12000),
  (4, 'Coconut oil 1 L',   'Oils',      21500),
  (5, 'Ragi flour 1 kg',   'Grains',     6800);

-- Meant to return two columns, name and price_paise. Spot the missing comma.
SELECT name price_paise FROM products;

┌───────────────────┐
│    price_paise    │
├───────────────────┤
│ Basmati rice 1 kg │
│ Toor dal 500 g    │
│ Masala chai 250 g │
│ Coconut oil 1 L   │
│ Ragi flour 1 kg   │
└───────────────────┘

Without the comma, price_paise is no longer a second column but the new name of the first one, so the query returns the product names under the heading price_paise. The MySQL manual warns about exactly this and recommends writing AS every time. If you always do, a name that follows another name without AS or a comma stands out.

Quotes: single for text, double for names

The SQL standard, PostgreSQL and SQLite follow one rule:

• Single quotes make text (a string literal): 'Pune'. To put a single quote inside text, write it twice: 'Asha''s order'.
• Double quotes make a name (an identifier) of a table, column or alias: "order".

You need to quote a name when it is one of the words SQL reserves for itself, when it contains spaces, or, in PostgreSQL, when it has capital letters that must be kept. A column called order is a classic case, because ORDER starts ORDER BY:

-- "order" is the position of an aisle on the shop's map. ORDER is also a
-- word of SQL itself (ORDER BY), so the column's name must be quoted.
CREATE TABLE aisles (aisle_id INTEGER PRIMARY KEY, name TEXT NOT NULL, "order" INTEGER NOT NULL);
INSERT INTO aisles (aisle_id, name, "order") VALUES (1, 'Grains', 2), (2, 'Beverages', 1), (3, 'Oils', 3);

SELECT name, "order" FROM aisles;   -- double quotes: the column called order
SELECT name, 'order' FROM aisles;   -- single quotes: the text order on every row
SELECT name, order FROM aisles;     -- no quotes: SQL reads the start of ORDER BY

┌───────────┬───────┐
│   name    │ order │
├───────────┼───────┤
│ Grains    │ 2     │
│ Beverages │ 1     │
│ Oils      │ 3     │
└───────────┴───────┘
┌───────────┬─────────┐
│   name    │ 'order' │
├───────────┼─────────┤
│ Grains    │ order   │
│ Beverages │ order   │
│ Oils      │ order   │
└───────────┴─────────┘

Parse error near line 8: near "order": syntax error

With double quotes, "order" is the column. With single quotes, 'order' is just the text “order”, repeated on every row. With no quotes, SQLite reads the start of an ORDER BY and reports a syntax error.

MySQL differs here. Its quote for names is the backtick, as in `order`, and by default it treats double-quoted words as text; only with the ANSI_QUOTES SQL mode do double quotes make names. SQLite also accepts backticks and square brackets around names, for compatibility, but the standard double quotes are the ones to use.

The double-quote trap in SQLite

CREATE TABLE products (
  product_id  INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  price_paise INTEGER NOT NULL      -- 100 paise = 1 rupee
);
INSERT INTO products (product_id, name, category, price_paise) VALUES
  (1, 'Basmati rice 1 kg', 'Grains',    14999),
  (2, 'Toor dal 500 g',    'Pulses',     8950),
  (3, 'Masala chai 250 g', 'Beverages', 12000),
  (4, 'Coconut oil 1 L',   'Oils',      21500),
  (5, 'Ragi flour 1 kg',   'Grains',     6800);

-- A typo inside double quotes: "nmae" instead of "name".
SELECT "nmae" FROM products;

┌────────┐
│ "nmae" │
├────────┤
│ nmae   │
│ nmae   │
│ nmae   │
│ nmae   │
│ nmae   │
└────────┘

There is no column called nmae, so you might expect an error. Instead, sql.js turned "nmae" into the text nmae and printed it on every row. SQLite’s documentation explains that SQLite copied this from early versions of MySQL: a double-quoted word that matches no name is read as text. Its authors write that, in hindsight, they should never have allowed it, and keep it only so that old programs go on working. Where the query runs decides what happens:

• sql.js, behind this site’s Run buttons, does what you see above.
• The sqlite3 program from sqlite.org has turned the habit off since SQLite 3.41.0, so it reports an error instead. Copies built by others can differ: the sqlite3 that comes with macOS 26.7 still reads "nmae" as text.
• PostgreSQL reports that the column nmae does not exist.
• MySQL, without ANSI_QUOTES, reads "nmae" as text, as its rules say.

Text in single quotes, and names in lower case without quotes, avoid the trap in every engine.

Capital letters in names

PostgreSQL folds names without quotes to lower case: CREATE TABLE products (ProductName text) creates a column called productname, and ProductName, PRODUCTNAME and productname all find it. A name in double quotes keeps its capitals, but then it must be written in double quotes, with the same capitals, in every query. The PostgreSQL manual notes that the SQL standard folds to upper case instead, so this behaviour is PostgreSQL’s own. In MySQL, column names and column aliases do not depend on case on any system, while table names can, depending on the operating system. Lower-case names with underscores sidestep all of it.

Comments and layout

CREATE TABLE products (
  product_id  INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  price_paise INTEGER NOT NULL      -- 100 paise = 1 rupee
);
INSERT INTO products (product_id, name, category, price_paise) VALUES
  (1, 'Basmati rice 1 kg', 'Grains',    14999),
  (2, 'Toor dal 500 g',    'Pulses',     8950),
  (3, 'Masala chai 250 g', 'Beverages', 12000),
  (4, 'Coconut oil 1 L',   'Oils',      21500),
  (5, 'Ragi flour 1 kg',   'Grains',     6800);

/* A block comment starts with slash-star and ends with star-slash.
   It can span several lines. */
SELECT name,          -- a line comment runs to the end of the line
       price_paise    /* or sits inside a statement */
FROM products;

┌───────────────────┬─────────────┐
│       name        │ price_paise │
├───────────────────┼─────────────┤
│ Basmati rice 1 kg │ 14999       │
│ Toor dal 500 g    │ 8950        │
│ Masala chai 250 g │ 12000       │
│ Coconut oil 1 L   │ 21500       │
│ Ragi flour 1 kg   │ 6800        │
└───────────────────┴─────────────┘

Two dashes start a comment that runs to the end of the line, and /* … */ encloses a comment of any length. The engine ignores both, so use them to say why a query does what it does. PostgreSQL lets block comments nest; SQLite does not. MySQL also starts a comment to the end of the line with #, and adds one rule of its own:

-- In SQLite and PostgreSQL, --3 starts a comment, so the answer is 10.
-- MySQL needs a space after -- and reads 10 - -3 here instead.
SELECT 10--3
AS answer;

┌────────┐
│ answer │
├────────┤
│ 10     │
└────────┘

MySQL starts a -- comment only when the second dash is followed by a space, a tab or another white-space or control character. In MySQL the same two lines therefore compute 10 - (-3) and print 13, while SQLite and PostgreSQL print 10. Always write -- with a space.

Layout does not change what a query does, but it changes how quickly people can read it. The examples in this track follow a few habits:

• Key words such as SELECT and FROM in capitals. SQL accepts them in any case; the capitals only make them stand out from names.
• One clause per line, and one column per line once the SELECT list gets long, with the AS names lined up.
• Names in lower case with underscores, and never one of SQL’s own words, so they need no quotes.

Key takeaways

• SELECT lists the columns to return, in order; FROM names the table. * is fine for exploring, but programs should list their columns.
• Expressions compute new columns for every row. Divide paise by 100.0, not 100, or the paise are lost.
• Name every computed column with AS, and write the AS: a missing comma silently turns a column into an alias.
• Single quotes make text, double quotes make names (backticks in MySQL), and a double-quoted typo can become text in SQLite.
• Write -- with a space after the dashes; MySQL needs it.