databases/your-first-sql-query-ask-a-table-of-pets-a-question.md

Your first SQL query: ask a table of pets a question

Databases · 9 min read ·

SQL is the language you use to ask a database questions, and five words get you surprisingly far: SELECT, FROM, WHERE, ORDER BY and LIMIT.

On a small table of pets, you'll build queries by clicking, see the SQL and the result update live, and learn how to filter rows, sort them and keep only the top few.

Then comes the twist that confuses almost everyone: the database doesn't run your query in the order you wrote it. Step through any query and watch the clauses run as FROM, WHERE, SELECT, ORDER BY, LIMIT.

$ ls databases/

see all →
databases/sql-window-functions-how-every-row-gets-a-rank-or-running-total-withou.md SQL window functions: how every row gets a rank or running total without collapsing the table A window function adds a calculated column to every row without collapsing rows the way GROUP BY does. Learn what OVER, PARTITION BY and… 4 min read · 8 Nov databases/pagination-why-next-page-shows-a-post-twice-and-how-a-bookmark-fixes-i.md Pagination: why 'next page' shows a post twice, and how a bookmark fixes it Why does "next page" sometimes show the same post twice, and why does page 90,000 load slowly? Both come from paging with LIMIT and OFFSET… 5 min read · 1 Nov databases/caching-why-the-second-read-is-fast-and-how-a-copy-goes-stale.md Caching: why the second read is fast, and how a copy goes stale Why does a page load slowly the first time and fast the second? Usually a cache: a quick copy of an answer the database already gave. Run a… 5 min read · 29 Oct databases/the-n-1-query-problem-why-a-page-that-asks-the-database-501-questions.md The N+1 query problem: why a page that asks the database 501 questions is slow A page that lists authors and their books can quietly send one query per author. With 500 authors that is 501 trips to the database, and… 4 min read · 26 Oct databases/transactions-why-a-money-transfer-must-save-both-changes-or-neither.md Transactions: why a money transfer must save both changes or neither Sending £50 between two accounts takes two changes to a database. If the power fails between them, the money vanishes. A transaction groups… 5 min read · 23 Oct databases/sql-indexes-why-finding-one-row-among-200-000-takes-about-18-steps.md SQL indexes: why finding one row among 200,000 takes about 18 steps A database with 200,000 customers can answer "find this email" by reading every row, or by reading about 18 entries. The difference is an… 4 min read · 20 Oct