Concepts/Basic/

BETWEEN

An introduction to the keyword BETWEEN.

The keyword BETWEEN returns a range of data between two values. It can be used with numbers, text strings and dates, and is most commonly found in the WHERE clause.

If we wanted to know what months the animal center took in between 900 and 1000 animals, we could write the following query:

SELECT year,
       month,
       animal_type,
       COUNT
  FROM austin_animal_center_intakes_by_month
 WHERE count BETWEEN 900 AND 2000
 ORDER BY month
siyeh/analysis-of-live-statistical-data-from-austin-animal-center Run query Copy code

And the results would look like:

year month animal_type COUNT
2014 5 Cat 901
2014 5 Dog 966
2015 5 Cat 1009
2015 5 Dog 988
2016 5 Cat 921
2016 5 Dog 1020
2017 5 Cat 914
2015 6 Cat 1103
2015 6 Dog 1014
2014 7 Dog 926
2014 9 Dog 917
2017 9 Dog 943
2013 10 Dog 965

Though it isn’t clearly illustrated by the last query, note that BETWEEN returns everything between two values inclusive of the specified vales.

Exercises (Continued from previous section)

There are two ways to do these exercises. The first is to use the “Try query” links to test your queries without saving them. The second is to 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 the SQL tutorial which has instructions for setting up your project and links to all the current exercises.

Exercise 17

Using the patient, description and value columns in the observations table, write a query that returns all the patients expected to have between one and five quality years of life left. (Use the description column and “Quality adjusted life years”).

Try query

Hint

SELECT the patient, description, and value columns FROM the observations table WHERE the description column = “Quality adjusted life years” AND the value is BETWEEN 1 AND 5 years.

See solution