SQL Data Types: Integers, Decimal, Text, Dates and Boolean

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

Integer types hold whole numbers such as counts and identifiers. Their ranges vary by type and database. A code composed of digits is not automatically an integer: a postal code or phone number may need leading zeros and can contain nonnumeric characters. Choose text for identifiers whose digit spelling matters more than arithmetic. A primary key can use different types as long as it meets its identity requirements.

DECIMAL and NUMERIC describe exact decimal precision in common server databases. A declaration such as DECIMAL(10,2) commonly permits ten total digits with two after the decimal point. Floating-point types approximate a range of numbers and are useful for scientific measurements, but can represent decimal fractions imperfectly. Financial calculations should define currency, precision and rounding explicitly. SQLite’s DECIMAL declaration uses numeric affinity and does not supply the same fixed-decimal enforcement.

Text types include CHAR, VARCHAR and engine-specific large-text types. CHAR is fixed-width in many systems and can involve padding behaviour, while VARCHAR is variable-length subject to the engine’s length rules. Unicode support depends on encoding and type choices; SQL Server distinguishes common varchar and nvarchar use, although collation choices also matter. Character count, byte count and collation are separate concerns. Avoid a length limit chosen only because the four sample names happen to fit.

DATE represents a calendar date, while timestamp types describe date-and-time values with different timezone semantics. Boolean support varies: PostgreSQL has BOOLEAN, MySQL BOOLEAN is a synonym for a small integer type, SQL Server commonly uses BIT, and SQLite relies on its dynamic representations and affinity rules. Binary values and structured JSON also have dialect-specific types or functions. A portable schema may share a conceptual model while needing local type declarations to enforce that model correctly. Constraints add business rules that a type alone cannot express, such as a nonnegative amount or a required relationship.

Server database types often enforce range and representation rules that ordinary SQLite tables do not enforce the same way. SQLite STRICT tables provide stronger checks but accept a restricted set of type names; DECIMAL is not one of those names. BOOLEAN, timestamp-zone types, JSON and auto-generated identity declarations are not uniform across engines.

Check the engine-specific syntax and version requirements in SQLite datatypes and affinity, PostgreSQL data types.

Show sales with known amounts below twenty. Use a numeric literal rather than a quoted display label.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT id,amount FROM sales
WHERE amount<20 ORDER BY id;

id | amount
101 | 10
106 | 5

Ten and five pass the numeric threshold. The known twenties and thirty do not, and the unknown amount is not established as less than twenty. A client’s display of trailing decimal zeros does not change the numerical comparison.

Compare text sorting of the literal labels '100' and '20' with numeric sorting after an appropriate conversion. Next inspect the declared amount type in metadata and distinguish that declaration from SQLite’s actual stored value categories. For an engine with strong typed columns, test out-of-range and malformed inputs in a disposable table so validation expectations are based on observed enforcement.