Create and save a query in DB Studio

Filter and sort Makerspace records in DB Studio, save the query, and inspect its SQL.

Watch
2:38 · English narration; English captions are on by default.
What you'll learn

What you'll learn

You will make a query for ready tool kits, filter and sort its results, save it, and reopen it.

Before you begin

Before you begin

In the library, click All samples and choose Prototype workshop (the built-in Makerspace sample). This guide follows the query steps shown in the video and uses its fictional Tool kits and Work areas data.

Steps

Steps

  1. In the database navigation, click New query, choose Design, open the SQL tab, and click Edit SQL.
  2. Enter SELECT "Kit", "Units", "Unit cost" FROM "Tool kits"; and click Run. The query returns the selected columns for 24 tool kits.
  3. Add WHERE "Ready" = 1 AND "Units" >= 10 before the semicolon, then click Run. Both conditions must be true; nine records match.
  4. Add ORDER BY "Unit cost" DESC after the conditions and click Run. The same nine records appear, ordered from highest unit cost to lowest.
  5. Replace the statement with SELECT k."Kit", a."Area name", k."Units", k."Unit cost" FROM "Tool kits" AS k JOIN "Work areas" AS a ON k."AreaID" = a."ID" WHERE k."Ready" = 1 AND k."Units" >= 10 ORDER BY k."Unit cost" DESC; and click Run. The result includes each work area's name.
  6. Type Ready kits by area in the query name box at the top (Query name), then click Save.
  7. Click New query, choose Design, open the SQL tab and click Edit SQL. Run SELECT COUNT(*) AS "Kits", SUM("Units") AS "Total units" FROM "Tool kits"; to see a separate summary (24 kits, 251 units).
  8. Open Ready kits by area from the database navigation and click Run to see its saved result again.
Expected result / Common mistakes

Expected result

The saved query returns nine ready kits with at least ten units, ordered by unit cost and shown with their work-area names. A saved select query is stored in the database and also becomes a SQLite view.

Common mistakes

  • In SQLite, put table and field names in double quotes when they contain spaces.
  • Use AND when both conditions must be true. The query filters results; it does not delete records from the table.
  • The Design tab also has a visual query designer where you add tables, pick fields and set criteria and sort. This guide uses the SQL tab, like the video.
Try it in DB Studio
Try it in DB StudioStart with a sample database
Open DB Studio
Related tutorials