Data types define which values a column accepts and affect validation, storage, sorting, and calculations.
CREATE TABLE products (\n id INTEGER PRIMARY KEY,\n name VARCHAR(120) NOT NULL,\n price DECIMAL(10, 2),\n created_at TIMESTAMP\n);Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use.
An online shop stores customers, products, orders, and order items. The schema must prevent duplicate identities, missing required values, and orders that reference customers that do not exist.
BEGIN;\n-- Preview the exact target set first.\nSELECT id FROM orders WHERE status = 'pending';\n-- Apply the SQL Data Types operation, verify affected rows, then commit.\nCOMMIT;The schema or operation satisfies the stated rule, rejects invalid data, and can be verified with a repeatable query.
This lesson reuses picostore. Relevant tables: customers, products, orders, order_items. Keep the starter rows from the Introduction lesson so results remain comparable.
ALTER TABLE products\n ADD COLUMN active BOOLEAN NOT NULL DEFAULT TRUE;\n\nSELECT product_id, name, active FROM products;Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct.
This catalog covers the practical public SQL surface. Check the exact server-version documentation before using a vendor-specific item.
SMALLINT — small whole numbersINTEGER / INT — ordinary whole numbersBIGINT — large whole numbersDECIMAL(p,s) / NUMERIC(p,s) — exact fixed-point values such as moneyREAL — single-precision approximate numberDOUBLE PRECISION — double-precision approximate numberFLOAT(p) — precision-based approximate numberCHAR(n) — fixed-length textVARCHAR(n) — variable-length bounded textTEXT — large variable text; common vendor extensionNCHAR / NVARCHAR — national character types, mainly SQL Server/MySQL compatibilityBINARY(n) — fixed-length bytesVARBINARY(n) — variable-length bytesBLOB — large binary object; MySQLBYTEA — variable binary data; PostgreSQLDATE — calendar dateTIME — time of dayTIME WITH TIME ZONE — time with offset supportTIMESTAMP — date and timeTIMESTAMP WITH TIME ZONE — absolute instant; PostgreSQL timestamptzINTERVAL — duration; PostgreSQL and standard SQLYEAR — year value; MySQLBOOLEAN / BOOL — true or falseJSON — validated JSON documentJSONB — binary searchable JSON; PostgreSQLXML — XML document; PostgreSQL/SQL ServerUUID — 128-bit identifier; native PostgreSQLARRAY — typed array; PostgreSQLENUM — restricted label set; both vendors with different implementationsSET — multiple labels; MySQLRANGE / MULTIRANGE — ranges of values; PostgreSQLINET / CIDR / MACADDR — network values; PostgreSQLGEOMETRY / GEOGRAPHY — spatial values with vendor extensionsAUTO_INCREMENT — MySQL integer generation attributeGENERATED ... AS IDENTITY — standard identity syntax supported by PostgreSQLSERIAL / BIGSERIAL — legacy PostgreSQL sequence shorthandSEQUENCE — independent number generator; PostgreSQL and standard SQLDECIMAL, not FLOAT — for exact money and accounting valuesTIMESTAMP WITH TIME ZONE — for absolute events shared across regionsVARCHAR/TEXT — for human-readable textINTEGER/BIGINT — for counters and numeric identifiersJSON — only when attributes are genuinely flexible and do not need ordinary relational constraintsStart with a small customer table and retrieve active customers in a predictable order.
SELECT customer_id, name, email\nFROM customers\nWHERE status = 'active'\nORDER BY name;Use a CTE and a window function to rank customer revenue while keeping the query readable and testable.
WITH customer_revenue AS (\n SELECT customer_id, SUM(total_amount) AS revenue\n FROM orders\n WHERE order_status = 'completed'\n GROUP BY customer_id\n)\nSELECT customer_id, revenue,\n DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank\nFROM customer_revenue\nORDER BY revenue_rank, customer_id;Use standard SQL first, then document any MySQL or PostgreSQL-specific syntax. Verify the statement with small sample data, an edge case, and the expected result before using it in production.
Data types define which values a column accepts and affect validation, storage, sorting, and calculations.
CREATE TABLE products (\n id INTEGER PRIMARY KEY,\n name VARCHAR(120) NOT NULL,\n price DECIMAL(10, 2),\n created_at TIMESTAMP\n);Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use.
An online shop stores customers, products, orders, and order items. The schema must prevent duplicate identities, missing required values, and orders that reference customers that do not exist.
BEGIN;\n-- Preview the exact target set first.\nSELECT id FROM orders WHERE status = 'pending';\n-- Apply the SQL Data Types operation, verify affected rows, then commit.\nCOMMIT;The schema or operation satisfies the stated rule, rejects invalid data, and can be verified with a repeatable query.
This lesson reuses picostore. Relevant tables: customers, products, orders, order_items. Keep the starter rows from the Introduction lesson so results remain comparable.
ALTER TABLE products\n ADD COLUMN active BOOLEAN NOT NULL DEFAULT TRUE;\n\nSELECT product_id, name, active FROM products;Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct.
This catalog covers the practical public SQL surface. Check the exact server-version documentation before using a vendor-specific item.
SMALLINT — small whole numbersINTEGER / INT — ordinary whole numbersBIGINT — large whole numbersDECIMAL(p,s) / NUMERIC(p,s) — exact fixed-point values such as moneyREAL — single-precision approximate numberDOUBLE PRECISION — double-precision approximate numberFLOAT(p) — precision-based approximate numberCHAR(n) — fixed-length textVARCHAR(n) — variable-length bounded textTEXT — large variable text; common vendor extensionNCHAR / NVARCHAR — national character types, mainly SQL Server/MySQL compatibilityBINARY(n) — fixed-length bytesVARBINARY(n) — variable-length bytesBLOB — large binary object; MySQLBYTEA — variable binary data; PostgreSQLDATE — calendar dateTIME — time of dayTIME WITH TIME ZONE — time with offset supportTIMESTAMP — date and timeTIMESTAMP WITH TIME ZONE — absolute instant; PostgreSQL timestamptzINTERVAL — duration; PostgreSQL and standard SQLYEAR — year value; MySQLBOOLEAN / BOOL — true or falseJSON — validated JSON documentJSONB — binary searchable JSON; PostgreSQLXML — XML document; PostgreSQL/SQL ServerUUID — 128-bit identifier; native PostgreSQLARRAY — typed array; PostgreSQLENUM — restricted label set; both vendors with different implementationsSET — multiple labels; MySQLRANGE / MULTIRANGE — ranges of values; PostgreSQLINET / CIDR / MACADDR — network values; PostgreSQLGEOMETRY / GEOGRAPHY — spatial values with vendor extensionsAUTO_INCREMENT — MySQL integer generation attributeGENERATED ... AS IDENTITY — standard identity syntax supported by PostgreSQLSERIAL / BIGSERIAL — legacy PostgreSQL sequence shorthandSEQUENCE — independent number generator; PostgreSQL and standard SQLDECIMAL, not FLOAT — for exact money and accounting valuesTIMESTAMP WITH TIME ZONE — for absolute events shared across regionsVARCHAR/TEXT — for human-readable textINTEGER/BIGINT — for counters and numeric identifiersJSON — only when attributes are genuinely flexible and do not need ordinary relational constraintsStart with a small customer table and retrieve active customers in a predictable order.
SELECT customer_id, name, email\nFROM customers\nWHERE status = 'active'\nORDER BY name;Use a CTE and a window function to rank customer revenue while keeping the query readable and testable.
WITH customer_revenue AS (\n SELECT customer_id, SUM(total_amount) AS revenue\n FROM orders\n WHERE order_status = 'completed'\n GROUP BY customer_id\n)\nSELECT customer_id, revenue,\n DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank\nFROM customer_revenue\nORDER BY revenue_rank, customer_id;Use standard SQL first, then document any MySQL or PostgreSQL-specific syntax. Verify the statement with small sample data, an edge case, and the expected result before using it in production.