All lessons

Module 1: Start here · Lesson 1.3 · about 20 min

Tables, rows, columns and types

What a row and a column really are, the handful of data types you need, why dates are stored as text in SQLite, and the integer-division trap that catches everyone once.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 1 · about 20 minutes · read first: 1.2. You’ll be able to say what one row and one column of a table mean, check the type of any value, and avoid two classic type traps.

A column holds one kind of fact with one type, a row holds one thing the table records, and the type decides how values compare, sort and divide.

In everyday terms: a paper form. Each box asks for one kind of answer (a date, a number, a name) and each filled-in form is one row. Write a number in the “name” box and sorting the forms later goes wrong.

1. Why this matters

A surprising number of wrong numbers in reports come from types, not logic: an average that rounds down to a whole number, a “10” that sorts before a “9”, a date stored as text in a format that doesn’t sort. Analysts who check the types of their columns first catch these before their manager does.

2. Rows and columns

A column is one kind of fact, with a name and a type: order_date, channel, status. Every value in a column is the same kind of thing.

A row is one thing the table records, with one value for each column. In orders, one row is one checkout.

-- try
SELECT order_id, order_date, channel, status
FROM orders
LIMIT 4

Unlike a spreadsheet, a table has no “row 1” or “cell B7”. Rows have no built-in order (you’ll ask for one with ORDER BY in Lesson 2.5), and you find a row by its values, such as order_id = 1010, not by its position.

3. The types you need

SQLite stores every value as one of five storage classes. Four matter here:

Type Holds Northbay example
INTEGER whole numbers quantity, store_id
REAL numbers with decimals unit_price, discount
TEXT words, codes, dates city, status, order_date
NULL no value at all store 12’s city

The function typeof() tells you which one a value is. Run this and read each column:

-- try
SELECT store_id, typeof(store_id),
       city, typeof(city),
       size_sqft, typeof(size_sqft)
FROM stores
WHERE store_id IN (1, 12)

Store 12 (online) shows null for its city and size: no value, not an empty word.

To list a table’s columns and declared types, SQLite has a special command:

-- noviz
PRAGMA table_info(order_items)

SQL Server uses EXEC sp_columns 'order_items' and PostgreSQL uses \d order_items (or the information_schema.columns view, which works in both).

Column
One kind of fact with a name and a type, such as quantity (whole numbers).
Row
One thing the table records, with one value per column.
INTEGER / REAL
Whole numbers / numbers with decimals.
TEXT
Words, codes and (in SQLite) dates. Compared letter by letter.
typeof(x)
Tells you which type a value really is.
Integer division
Whole number ÷ whole number drops the remainder: 7 / 2 = 3.
Physical meaning
The type is stored with each value and decides what the database does with it: TEXT '10' sorts before '9', INTEGER 10 after 9, and 7 / 2 gives 3 unless one side has a decimal.

“A number is a number, whatever type it is stored as.” The type changes the answer. Text ‘10’ sorts before ‘9’, and SUM(quantity) / COUNT(*) on whole numbers gives 1 instead of 1.51. Check types with typeof(), and write * 1.0 (or 2.0) when you divide whole numbers.

Watch the integer-division trap happen, one step at a time:

A tiny order_items.
linequantity
11
23
31
SUM(quantity) = 5, COUNT(*) = 3. Both are INTEGER.
unitslines
53
5 / 3 as whole numbers. The remainder is dropped.
wrong_avg
1
5 * 1.0 / 3. One side is REAL, so the decimals stay.
right_avg
1.67

Any live box’s Show how it runs button animates a real query this way. Now predict what SQLite says:

4. Dates are text here

SQLite has no date type, so Northbay stores dates as TEXT in the form YYYY-MM-DD. That form is chosen on purpose: because the year comes first and months and days always have two digits, comparing the text character by character gives the same answer as comparing the dates.

-- try
SELECT '2024-12-01' < '2025-01-15' AS iso_dates,
       '01/12/2024' < '15/01/2023' AS day_first

Both answers are 1 (true), but the second is wrong: 1 December 2024 is not before 15 January 2023. Text in day/month/year order doesn’t sort as dates. SQL Server and PostgreSQL have real DATE types, so there you’d write DATE '2025-01-15'.

5. Text that looks like a number

Text is compared letter by letter, so '10' comes before '9' (because “1” comes before “9”). Numbers are compared by size.

-- try
SELECT '10' < '9' AS as_text,
       10 < 9 AS as_numbers

If a column of numbers was loaded as text, sorting and MAX will quietly go wrong. typeof() is how you find out.

6. The integer-division trap

When both sides of / are INTEGER, SQLite (and SQL Server) gives a whole number and drops the remainder. PostgreSQL does the same.

-- try
SELECT 7 / 2 AS whole, 7 / 2.0 AS decimal, 7 * 1.0 / 2 AS also_decimal

The fix is to make one side REAL: write 2.0, multiply by 1.0, or use CAST(x AS REAL).

Maths and principle

Principle: each column has a domain: the set of values it may hold. A row is one choice from each domain, so a table is a subset of the product of its columns’ domains.

Equation: for orders, a row is a tuple (order_id, customer_id, store_id, order_date, channel, status, promo_id) taken from Did × Dcust × … × Dpromo. Integer division is the floor (for positive numbers): 7 / 2 = ⌊3.5⌋ = 3.

Analogy: a paper form. Each box (column) only accepts one kind of answer, and each filled-in form is a row. It breaks down a little in SQLite, which lets you write text into an INTEGER column; stricter databases refuse.

Try it: what is 11019 / 7310 in SQLite? (1, because both are whole numbers. With 11019 * 1.0 / 7310 it is about 1.51.)

7. Worked example

Question: on average, how many units does one order line hold?

  1. Units sold: SUM(quantity) over order_items is 11,019.
  2. Lines: COUNT(*) is 7,310.
  3. Divide: 11,019 / 7,310. As whole numbers SQLite says 1, which hides the answer. With * 1.0 it says 1.51.
-- try
SELECT SUM(quantity) AS units,
       COUNT(*) AS lines,
       SUM(quantity) / COUNT(*) AS wrong_avg,
       ROUND(SUM(quantity) * 1.0 / COUNT(*), 2) AS right_avg
FROM order_items

In fact about one line in four (25%) has more than one unit. A report showing “1” would have hidden that.

8. Practice

-- exercise: Show each product's name and the type of its unit_price column (use typeof). First 5 products only.
-- hint: SELECT product_name, typeof(unit_price) FROM products LIMIT 5
SELECT product_name, unit_price
FROM products
LIMIT 5
-- answer:
SELECT product_name, typeof(unit_price)
FROM products
LIMIT 5
-- exercise: Work out the average store size: SUM(size_sqft) divided by COUNT(size_sqft), with decimals.
-- hint: Both are whole numbers, so multiply one by 1.0 first.
SELECT SUM(size_sqft) / COUNT(size_sqft) AS avg_size
FROM stores
-- answer:
SELECT SUM(size_sqft) * 1.0 / COUNT(size_sqft) AS avg_size
FROM stores
  • A column is one kind of fact with one type; a row is one thing recorded.
  • Rows have no built-in order or position; find them by their values.
  • SQLite’s types: INTEGER, REAL, TEXT and NULL (no value). typeof() shows which.
  • Dates are TEXT in YYYY-MM-DD form, which sorts correctly; other date formats do not.
  • Whole number ÷ whole number drops the remainder; multiply by 1.0 first.

New words: column, row, storage class, domain, integer division, ISO date.

Check yourself

1What does typeof(order_date) return in SQLite for Northbay?
2What is 7 / 2 in SQLite?
3Why does '2024-12-01' < '2025-01-15' give the right answer, even though both are text?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. Northbay Retail is a made-up company; its data is generated in your browser. Queries run on your device and nothing you type is sent anywhere.