One way to restrict the data presented from a query is to use a
WHERE is used to return data that matches
a specified condition and is combined with the comparison operators
Let’s say we wanted to know all the months where more than 1,000 of one type of animal was taken in. We could run a query like this one:
SELECT year, month, animal_type, count FROM austin_animal_center_intakes_by_month WHERE COUNT > 1000
and we would get these results:
You can also use a
!= to limit results for text
fields. E.g., if we wanted only to look at information about cats taken in by
the Austin Animal Center we could run this query:
SELECT year, month, count, animal_type FROM austin_animal_center_intakes_by_month WHERE animal_type = "Cat"
Notice that for numeric values, the number you compare (1000 in the first example) is not bounded by quotes. When you compare text strings, the compared text must be enclosed in double quotes (“Cat” in the second example). Queries involving text strings are also case sensitive. If we run the second query with “cat” instead of “Cat” we would get a message that our query returned no results.
The first seven rows returned would look like this:
While it is legal to run queries using the
<= against columns which contain strings of text, the results might not
be what you expect. Text strings are treated like numbers with alphabetic order
determining which is greater than and which is less than. If we were to run
a query against the Intakes table for all animal types
> “Cat” the results
would include all Dogs, Livestock, Other, and Wildlife, but no cats or
Exercises (Continued from previous section)
There are two ways to do these exercises. You can use the “Try query” links to test your queries without saving them, or you can create a data.world project and save your queries to it. If you are reading this documentation and completing the exercises as a tutorial, you will need to create your own project to save your work. Details and instructions are in SQL tutorial which has instructions for setting up your project and links to all the current exercises.
Using the description column in the allergies table, write a query that returns all of the patients with an allergy to mould.
SELECT all the records
“Allergy to mould”
Using the column occurrences from the all_prevalences table, write a query that shows all of the conditions that show up more than 100 times in your data.
SELECT all the records
An introduction to the
ORDER BY clause.