SQL Data Types and Command Types
Every column has a data type: it decides what values the column accepts and how they behave. The SQL standard groups types into a few families, numbers, text, dates and times, true/false and binary data, and every database has them, though some names differ. Every SQL statement belongs to a command type, depending on whether it changes the structure, changes rows, reads rows, grants access or controls a transaction.
Syntax
Order of a database's life: DDL defines the tables → DML fills and changes their rows → DQL reads them → DCL decides who may do what → TCL groups changes into transactions.
CodeCREATE TABLE table_name (column_name TYPE, ...); -- DDL INSERT INTO table_name VALUES (...); -- DML SELECT column_name FROM table_name; -- DQL GRANT SELECT ON table_name TO role_name; -- DCL BEGIN; ... COMMIT; -- TCL CAST(value AS TYPE) -- turn a value into another type
| Family | Standard types | Holds |
|---|---|---|
| Whole numbers | SMALLINT, INTEGER, BIGINT | whole numbers, each type with a bigger range |
| Exact decimals | DECIMAL(p, s), NUMERIC(p, s) | p digits in all, s after the point: money |
| Approximate numbers | REAL, FLOAT, DOUBLE PRECISION | measurements; stored in binary, so slightly rounded |
| Text | CHAR(n), VARCHAR(n), long text | exactly n characters (padded with spaces), up to n characters, any length |
| Date and time | DATE, TIME, TIMESTAMP, INTERVAL | a day, a time of day, a day and a time, a length of time |
| True/false | BOOLEAN | TRUE, FALSE, or NULL for unknown |
| Binary | BINARY(n), VARBINARY(n), BLOB | raw bytes, such as images and files |
| Others | JSON, XML, UUID | documents and unique ids |
| Standard | MySQL | SQL Server | Oracle | PostgreSQL |
|---|---|---|---|---|
INTEGER | INT | INT | NUMBER(38) | INTEGER |
DECIMAL(10, 2) | DECIMAL(10, 2) | DECIMAL(10, 2) | NUMBER(10, 2) | NUMERIC(10, 2) |
VARCHAR(n) | VARCHAR(n) | VARCHAR(n), NVARCHAR(n) | VARCHAR2(n) | VARCHAR(n) |
| long text | TEXT | VARCHAR(MAX) | CLOB | TEXT |
BOOLEAN | BOOLEAN (stored as TINYINT(1)) | BIT | NUMBER(1); BOOLEAN from 23ai | BOOLEAN |
TIMESTAMP | DATETIME | DATETIME2 | TIMESTAMP (DATE keeps a time too) | TIMESTAMP |
BLOB | BLOB | VARBINARY(MAX) | BLOB | BYTEA |
| Command type | Commands | Works on |
|---|---|---|
| DDL, data definition | CREATE, ALTER, DROP, TRUNCATE | tables and other objects |
| DML, data manipulation | INSERT, UPDATE, DELETE | rows |
| DQL, data query | SELECT | reading rows |
| DCL, data control | GRANT, REVOKE | permissions |
| TCL, transaction control | BEGIN, COMMIT, ROLLBACK, SAVEPOINT | transactions |
Examples
Whole numbers have a range
SMALLINT holds up to 32,767; INTEGER about 2.1 billion (Oracle stores both as NUMBER(38)).
CodeSELECT CAST(32767 AS SMALLINT) AS small_max; -- Fails: 32,768 is too big for a SMALLINT SELECT CAST(32768 AS SMALLINT);
Exact and approximate decimals
DECIMAL keeps decimals exactly and rounds to its scale; approximate types round in binary.
CodeSELECT CAST(12.345 AS DECIMAL(5, 2)) AS rounded, -- 12.35 CAST(0.1 AS DECIMAL(3, 2)) + CAST(0.2 AS DECIMAL(3, 2)) = 0.3 AS exact_equal, -- true CAST(0.1 AS DOUBLE PRECISION) + CAST(0.2 AS DOUBLE PRECISION) = CAST(0.3 AS DOUBLE PRECISION) AS approx_equal; -- false -- Fails: DECIMAL(5, 2) has room for only 3 digits before the point SELECT CAST(123456.789 AS DECIMAL(5, 2));
Fixed and varying text
CHAR(n) pads its value with spaces to n characters; VARCHAR(n) keeps it as it is. Most
databases ignore the padding when comparing, so 'IN' still equals the padded value.
CodeSELECT OCTET_LENGTH(CAST('IN' AS CHAR(5))) AS fixed_size, -- 5: padded with spaces OCTET_LENGTH(CAST('IN' AS VARCHAR(5))) AS varying_size, -- 2 CAST('IN' AS CHAR(5)) = 'IN' AS same; -- true: the padding is ignored -- Fails: category_name is VARCHAR(60) INSERT INTO categories (category_name) VALUES (repeat('x', 61));
Dates, times and true/false
In PostgreSQL and Oracle, subtracting two dates gives the days between them; MySQL and SQL Server
use DATEDIFF instead.
CodeSELECT DATE '2025-11-01' - DATE '2025-10-01' AS days_apart, -- 31 EXTRACT(HOUR FROM TIMESTAMP '2025-10-31 18:30:00') AS hour, -- 18 TIME '18:30:00' AS clock, TRUE AS yes, CAST(NULL AS BOOLEAN) AS unknown;
The types of a table's columns
information_schema is part of the SQL standard, so MySQL and SQL Server have it too. Oracle
uses its own views, such as ALL_TAB_COLUMNS.
CodeSELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'products' ORDER BY ordinal_position;
DDL, DML and DQL
Define a table, fill it, then read it.
CodeCREATE TABLE notes (note_id INTEGER, body VARCHAR(200)); -- DDL: a new table INSERT INTO notes VALUES (1, 'Restock the rice'); -- DML: a new row SELECT * FROM notes; -- DQL: read the rows
TCL and DCL
TCL decides which changes are kept. DCL hands out permissions; a table's owner or an administrator
runs it. SQL Server writes BEGIN TRANSACTION, and Oracle starts a transaction by itself.
CodeBEGIN; UPDATE products SET stock = 0; ROLLBACK; -- TCL: the update is undone -- Fails in the lab: the lab doesn't allow GRANT GRANT SELECT ON products TO PUBLIC;
Remember
- Use whole-number types for counts,
DECIMAL/NUMERICfor money, neverREALorFLOAT. - A value too big for its type is refused, not cut short.
- Type names differ between databases, but the families are the same: numbers, text, dates and times, true/false, binary.
- DDL changes structure, DML changes rows, DQL reads rows, DCL grants access, TCL controls transactions.
- Lists differ between books: some count
SELECTas DML, andTRUNCATEis usually DDL because it empties the whole table at once.