ANSI SQL and Major Vendor Dialects

CREATE TABLE customer (    customer_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,    email VARCHAR(320) NOT NULL UNIQUE,    display_name VARCHAR(200) NOT NULL,    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);SELECT customer_id, email, display_nameFROM customerWHERE created_at >= ?ORDER BY created_at, customer_idFETCH FIRST 100 ROWS ONLY;

-- Run in an isolated scratch schema during certification.WITH probe(value) AS (VALUES (1))SELECT value FROM probe;CREATE TEMPORARY TABLE capability_probe (    id INTEGER PRIMARY KEY,    payload TEXT NOT NULL);INSERT INTO capability_probe(id,payload)VALUES (1,'first')ON CONFLICT(id) DO UPDATE SET payload=excluded.payloadRETURNING id,payload;DROP TABLE capability_probe;

engine: postgresqlminimum_version: 16required:  - recursive_cte  - window_functions  - transactional_ddl  - returningoptional:  - json_path  - generated_columnsunsupported_path: fail_startup

PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS order_item;DROP TABLE IF EXISTS sales_order;DROP TABLE IF EXISTS product;DROP TABLE IF EXISTS customer;CREATE TABLE customer (  customer_id INTEGER PRIMARY KEY,  email TEXT NOT NULL UNIQUE,  display_name TEXT NOT NULL,  region TEXT NOT NULL CHECK(region IN ('north','south','east','west')),  created_at TEXT NOT NULL) STRICT;CREATE TABLE product (  product_id INTEGER PRIMARY KEY,  sku TEXT NOT NULL UNIQUE,  product_name TEXT NOT NULL,  unit_price_cents INTEGER NOT NULL CHECK(unit_price_cents>=0),  active INTEGER NOT NULL DEFAULT 1 CHECK(active IN (0,1))) STRICT;CREATE TABLE sales_order (  order_id INTEGER PRIMARY KEY,  customer_id INTEGER NOT NULL REFERENCES customer(customer_id),  status TEXT NOT NULL CHECK(status IN ('draft','submitted','paid','cancelled')),  ordered_at TEXT NOT NULL,  request_key TEXT UNIQUE) STRICT;CREATE TABLE order_item (  order_id INTEGER NOT NULL REFERENCES sales_order(order_id) ON DELETE CASCADE,  product_id INTEGER NOT NULL REFERENCES product(product_id),  quantity INTEGER NOT NULL CHECK(quantity>0),  unit_price_cents INTEGER NOT NULL CHECK(unit_price_cents>=0),  PRIMARY KEY(order_id,product_id)) STRICT;INSERT INTO customer VALUES(1,'ada@example.com','Ada Lovelace','north','2026-01-10'),(2,'grace@example.com','Grace Hopper','east','2026-02-15'),(3,'linus@example.com','Linus Torvalds','west','2026-03-01');INSERT INTO product VALUES(10,'DB-BOOK','Database Design Handbook',4200,1),(11,'SQL-CARD','SQL Reference Cards',1800,1),(12,'ARCHIVE','Archived Product',9900,0);INSERT INTO sales_order VALUES(1001,1,'paid','2026-08-01 09:00:00','req-1001'),(1002,1,'submitted','2026-08-02 10:30:00','req-1002'),(1003,2,'draft','2026-08-03 14:15:00','req-1003');INSERT INTO order_item VALUES(1001,10,1,4200),(1001,11,2,1800),(1002,11,3,1800),(1003,10,1,4200);SELECT c.region, COUNT(*) AS customersFROM customer AS cGROUP BY c.regionORDER BY c.region;INSERT INTO product(product_id,sku,product_name,unit_price_cents)VALUES(11,'SQL-CARD','SQL Cards, revised',1900)ON CONFLICT(product_id) DO UPDATE SET  product_name=excluded.product_name,  unit_price_cents=excluded.unit_price_centsRETURNING product_id,sku,unit_price_cents;