From a table to an answer

A table holds the data. A query asks it a question.

Most health data ends up in tables. SQL is the language you use to ask a table a question, and get back just the rows you need. This lesson shows the idea in three small steps.

A table is named rows and columns

A relational database stores data in tables. Each table is a named collection of rows. Each row in a table has the same set of named columns. And each column holds one specific data type, such as text, a number, or a date.

• Rows · A named collection of rows · layers
• Columns · The same named columns in every row · tag
• Data types · Each column has one specific type · key

Ask for everything in a table

Here is a made up table of clinic visits, with six rows and five columns. The query says: select star, from visits. The star means every column. The database returns every row of the table. The patient codes are made up. There is no patient data here.

• SELECT
• FROM visits;

A query has three parts

A select statement has parts, and the tutorial gives them names. The select list names the columns to return. The table list names the tables to read from. And the qualification, an optional part, limits which rows come back. Together, they say which columns, from where, and which rows.

• Part 1 · The select list · Which columns to return. · tag
• Part 2 · The table list · Which tables to read from. · box
• Part 3 · The qualification · Optional: which rows. · search

Ask only for the rows you need

Now a narrower question. Which patients had a visit at the North clinic with a systolic pressure over one hundred and forty? The select list asks for two columns, patient and seen on. The where clause holds a true or false test, and only rows where it is true are returned. Two rows come back: the same patient, on two dates.

• SELECT patient, seen_on
• FROM visits
• WHERE clinic = 'North'
• AND systolic > 140;

Stored order is not shown order

One habit to learn early. Columns have a fixed order in each row. But SQL does not guarantee the order of the rows in a table in any way. If order matters, you must sort the results explicitly, for display. Never rely on the order rows happen to come back in.

Check yourself

Pause and think. What does a where clause do? The answer: it says which rows are wanted, using a true or false test.

• It says which rows are wanted, using a true or false test
• It names the columns to return
• It names the table to read
• It sorts the table permanently

Keep these in mind

A table is named rows with the same named columns. Select names the columns, and from names the table. Where keeps only the rows where its test is true. And row order is not guaranteed unless you sort.

• A table is named rows with the same named columns
• SELECT names the columns; FROM names the table
• WHERE keeps only rows where its test is true
• Row order is not guaranteed unless you sort