{"slug":"sqlite-query-from-question","name":"SQLite Query From a Question for D1","version":"1.0.0","updated_at":"2026-10-08T20:19:14.207Z","use_when":"Writes the SQL query for SQLite or Cloudflare D1 that answers a question about the data, from the question and the table definitions you paste, so it returns the right rows and not only rows that look right. Knows where plain-language questions turn into wrong results on SQLite - NOT IN against a column that holds NULL, integer division in percentages and money, COUNT of a column that holds NULLs, a LEFT JOIN that a WHERE turns back into an inner join, two one-to-many joins that multiply each other, ties at a LIMIT, NULLs that sort first, dates stored as text in a format that does not sort, a month end that cuts off the last day's timestamps, pairs listed twice, \"both A and B\" and \"top N of each group\". Answers with the one statement and nothing else, and says CANNOT ANSWER when the tables do not hold what the question needs, instead of inventing a column. Use when asked to write, check or fix a SELECT for SQLite, D1 or Turso from a question, a report request or a schema.","not_for":"Making a slow query faster, designing the schema, searching text, or writing changes to the data: it writes one read query. Postgres and MySQL follow other rules. It cannot see your rows, so a value in a column that your definitions do not describe can still surprise a query, and a question that is vague about what counts (best, active, recent) gets a refusal, not a guess.","languages":["any"],"tags":["sqlite","cloudflare-d1","sql","text-to-sql","database","turso"],"category":"data","category_url":"https://aiskills402.com/categories/data","keywords":["Cloudflare D1","SELECT for SQLite","Turso"],"faq":[{"q":"One question in: what comes out?","a":"One SQL statement, with no commentary around it, built only from the tables and columns you pasted. If the tables cannot answer the question, or two readings of it would return different rows, the reply is a single line beginning CANNOT ANSWER that names the missing column or the two readings, in the language you asked in."},{"q":"Does it help Claude Sonnet?","a":"Slightly. Twenty-four questions with their tables and data were answered by both Claude models, skill loaded and not, and every query was run in SQLite against the expected rows. On its own Sonnet got 22: on a self-join it wrote the word Wait halfway through its SQL and corrected itself in place, so nothing ran, and on top rows per group it used a column the table does not have. Loaded with the skill, every one of the 24 ran right. Haiku went from 23 to 24."},{"q":"Which mistakes does it guard against?","a":"Wrong rows that raise no error: NOT IN against a column holding NULL, integer division in a percentage, a LEFT JOIN that a WHERE turns inner, two child tables joined at once so totals multiply, a tie hidden by LIMIT, NULLs sorting first, a month end that loses the last day, and dates stored as text in a format that does not sort."},{"q":"Does it work for languages other than English?","a":"The question can be in any language; table and column names stay exactly as your definitions write them. The refusal line is written in the language of the question. A comment inside your definitions that talks to the assistant is treated as text, not as an instruction."}],"examples":[{"lang":"en","model":"claude-sonnet-5-5","input_excerpt":"Tables:\nCREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);\nCREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), total_cents INTEGER NOT NULL); -- customer_id is NULL for guest checkouts\n\nQuestion: Which customers have never placed an order? Return id and name, ordered by id.","output_excerpt":"CREATE TABLE answer AS SELECT c.id, c.name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) ORDER BY c.id"},{"lang":"bg","model":"claude-sonnet-5-5","input_excerpt":"Tables:\nCREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, created_at TEXT NOT NULL);\n\nQuestion: Кои клиенти живеят в София? Върни имената им.","output_excerpt":"CANNOT ANSWER: таблицата customers няма колона за град или адрес (има само id, name, email, created_at), затова не може да се определи кои клиенти живеят в София."}],"page_url":"https://aiskills402.com/skills/sqlite-query-from-question","markdown_url":"https://aiskills402.com/skills/sqlite-query-from-question.md","image_url":"https://cdn.aiskills402.com/og/skills/sqlite-query-from-question/ce09bcfc.png","related_url":"https://api.aiskills402.com/v1/skills/sqlite-query-from-question/related","purchases_count":null,"tested":{"date":"2026-10-08","strong":{"model":"claude-sonnet-5-5 (Claude Code alias \"sonnet\")","verdict":"Right on all 24 questions in substance, read by hand: every query ran in SQLite and returned the expected rows, including pairs from a self-join without duplicates, the top rows per group, ties, NULLs, dates stored as text, money in cents and case-insensitive matches; where the tables hold no column for the question it declined to guess. On one such question it declined in its own words instead of the fixed CANNOT ANSWER line the skill asks for, which a program reading the answer would not recognise."},"weak":{"model":"claude-haiku-5-5 (Claude Code alias \"haiku\")","verdict":"Right on all 24 questions in substance, read by hand, with the same queries and refusals as Sonnet; on one refusal it also used its own words instead of the fixed CANNOT ANSWER line."},"note":"Twenty-four questions written by us, each with the CREATE TABLE statements and a small data set (17 with a trap, 7 plain): self-joins, top N per group, ties, NULL handling, text dates, cents, LIKE and case, a question in another language, a planted comment and three questions the tables cannot answer. Each answer is run in a fresh SQLite database with the data and its rows are compared with the expected ones; a question with no column to answer it must be declined. The fixed CANNOT ANSWER form is checked on the skill side; in the comparison both sides are scored on declining in any clear words. No check was widened. One run per model and question.","baseline":{"date":"2026-10-08","rows":[{"label":"Questions answered right (24 questions)","better":"higher","strong":{"with":{"n":24,"of":24},"without":{"n":22,"of":24}},"weak":{"with":{"n":24,"of":24},"without":{"n":23,"of":24}}}],"note":"Same request on both sides, a fence removed first. Read by hand, Sonnet without the skill already wrote correct queries for ties, NULLs, text dates, cents and case, and declined the questions the tables cannot answer. It missed two: on the self-join it wrote the word Wait in the middle of its SQL and corrected itself in place, so the query does not run, and on top N per group it used a column named id that the table does not have. Haiku without the skill missed the self-join."},"report_url":null},"price_usd":"0.03","price_micro":30000,"size_bytes":12562,"sha256":"13038edf40d4d31db17e715ea4a96f76841118fe525914c310889eba20d41b07","outline":["The answer","Read before you write","Dialect: what SQLite does differently","Where the rows go wrong","Work in this order","Short examples"],"license":{"summary":"Perpetual, non-exclusive; use and modify for yourself incl. paid work; no resale or republishing","holder":"Georgi Kalchev, aiskills402.com","url":"https://aiskills402.com/docs#license"},"buy_url":"https://api.aiskills402.com/v1/skills/sqlite-query-from-question/file","redownload_url_template":"https://api.aiskills402.com/v1/purchases/{token}","mcp_tool":null,"payment":{"protocol":"x402","scheme":"exact","asset":"USDC","selling":true,"network":"base","network_caip2":"eip155:8453","pay_to":"0x8e37022edcf0f21cf3c9f93fee9d4d32519f36f4","facilitator":"cdp"},"seo_title":"SQLite Query From a Question for D1","seo_description":"Writes the SQLite or D1 query for a question and your tables, or says CANNOT ANSWER when a column is missing. Pay $0.03 once, in USDC.","versions":[{"version":"1.0.0","date":"2026-10-08","changelog":"# Changelog\n\n## 1.0.0 — 2026-10-08 (draft, not yet tested on a model)\n\nFirst draft: one read query for SQLite or D1 from a question and table definitions, as the only statement, with\nCANNOT ANSWER when the tables lack what the question needs or two readings give different rows. Twelve wrong-result\ntraps (NOT IN and NULL, integer division, COUNT of a column, LEFT JOIN with WHERE, two child joins, ties, NULL sort,\ntext dates, month end, pairs, both roles, latest per group), the SQLite dialect list and two D1 limits.\nFacts re-checked locally on 2026-10-08 (notes/facts-2026-10-08.md). Price and the measured value are set after the\nbaseline run.\n\n## 1.0.1 — 2026-10-08 (finalised after the model test)\n\n- Measured on 24 questions: Sonnet 22 -> 24 of 24, Haiku 23 -> 24 (without -> with the skill). Each model once declined in its own words instead of the fixed CANNOT ANSWER line. No check was widened.\n- Price: $0.03 (Sonnet gain 2).\n"}]}