Create and save a query in DB Studio
Filter and sort Makerspace records in DB Studio, save the query, and inspect its SQL.
Watch
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
- In the database navigation, click New query, choose Design, open the SQL tab, and click Edit SQL.
- Enter
SELECT "Kit", "Units", "Unit cost" FROM "Tool kits";and click Run. The query returns the selected columns for 24 tool kits. - Add
WHERE "Ready" = 1 AND "Units" >= 10before the semicolon, then click Run. Both conditions must be true; nine records match. - Add
ORDER BY "Unit cost" DESCafter the conditions and click Run. The same nine records appear, ordered from highest unit cost to lowest. - 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. - Type Ready kits by area in the query name box at the top (Query name), then click Save.
- 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). - 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
ANDwhen 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 StudioRelated tutorials