Northwind › SQL Console
SQL Console
Type a SELECT and run it against the Northwind database. This is SQLite 3.45, so SQLite's own functions, window functions, recursive CTEs, EXPLAIN QUERY PLAN and PRAGMA table_info(…) all work. Table names that contain a space need double quotes: "Order Details".
Result
| CategoryID | CategoryName | Description | Picture |
|---|---|---|---|
| 1 | Beverages | Soft drinks, coffees, teas, beers, and ales | BLOB · 10,746 bytes |
| 2 | Condiments | Sweet and savory sauces, relishes, spreads, and seasonings | BLOB · 10,746 bytes |
| 3 | Confections | Desserts, candies, and sweet breads | BLOB · 10,746 bytes |
| 4 | Dairy Products | Cheeses | BLOB · 10,746 bytes |
| 5 | Grains/Cereals | Breads, crackers, pasta, and cereal | BLOB · 10,746 bytes |
| 6 | Meat/Poultry | Prepared meats | BLOB · 10,746 bytes |
| 7 | Produce | Dried fruit and bean curd | BLOB · 10,746 bytes |
| 8 | Seafood | Seaweed and fish | BLOB · 10,746 bytes |
Example queries
Row countsUNION ALL stacks five counts into one result.
Order Details columnspragma_table_info shows the two-column primary key.
Territories per employeeFour tables joined through a junction table.
Uncovered territoriesLEFT JOIN + IS NULL finds rows with no match.
Org chart (recursive)A recursive CTE walks the ReportsTo tree.
Price history of Queso CabralesOrder lines keep the price charged on the day.
IS NULLThe only reliable test for a missing value.
count(*) vs count(column)Why counting after a LEFT JOIN goes wrong.
Revenue by categoryGROUP BY with a derived revenue column.
Shipper performancejulianday() differences give days to ship.
Sales by quarterstrftime() and integer division make quarters.
Priciest product per categoryA correlated subquery, run per row.
Lost customersEXISTS and NOT EXISTS together.
Category Sales for 1997A view built on a view built on a view.
+ versus ||Why T-SQL string joins return 0 in SQLite.
Ten Most Expensive ProductsMicrosoft's procedure, as ORDER BY + LIMIT.
Rank within countryrank() OVER (PARTITION BY ...).
Running total by monthsum() OVER and lag() for month-on-month.
Top two products per categoryrow_number() for a top-N per group.
Plan: indexed lookupEXPLAIN QUERY PLAN: SEARCH via an index.
Plan: a joinA join that uses a covering index.
BLOB sizestypeof() and length() on stored pictures.
What you can't do here, and why
INSERT, UPDATE, DELETE, CREATE, DROP, ATTACH, BEGIN and any PRAGMA that changes a setting are all refused, because this is a shared demonstration. On your own copy everything works: download northwind.db and run sqlite3 northwind.db. Try DELETE FROM Orders; here to see the refusal.