Ask for One Column with SELECT

Suppose you only want to see which categories appear in the lending catalog. You do not need the item IDs or names, so you can ask for the category column by itself:

SELECT category FROM items;

Run that request and SQLite returns one column:

Columns: category
electronics
events
tools

This request is a SQL query. A query asks a database for a result. SQL stands for Structured Query Language.

Read the query from left to right. SELECT category says which column you want back. FROM items says which table should supply the rows.

Ask for the item names

The editor has opened query.sql. Its starter query asks for each item’s id. Change that one column name to name, then press Run.

Look for this result:

Columns: name
Cordless drill
Folding table
Projector

You have not changed the items table. You have asked SQLite to shape an answer with one column and one result row for each stored item. If two items share a name, that name appears twice. This query does not request a row order, so the same names may appear in a different order.

When you submit your solution, the course system will run your query with a different catalog to make sure you are not taking shortcuts. If you haven’t done so, now is a good time to submit your solution!

A few details worth noticing

SQL keywords are often written in uppercase so they stand apart from table and column names. SQLite also accepts lowercase keywords. The semicolon marks the end of the statement.

Note that the editor on this site will color query.sql to make the text more readable, just like it can color Python code.

If an error appears, compare the column and table names in your query with the schema from the previous lesson. Make one change and run it again. To restore the original files, use Reset project in the project toolbar.

You have written your first SQL query and used it to get exactly one part of the catalog. Python has been opening the database out of sight so far. It is time to write that line yourself.