This page has 0 threads | Add post

Criteria Using Numbers

Written by Andrew Gould

In this tutorial

Comparison Operators for Numbers
Finding Numbers Within a Range
Finding Numbers in a List of Values
What's Next?

Numbers are perhaps the easiest type of data for which to write criteria, which is why we're using them as a starting point.

Comparison Operators for Numbers

You can use a range of different symbols, known as operators, for comparing numbers: the table below shows you the main ones you're likely to use:

Operator	Alternative	What it means
=		Equal to
<>	!=	Not equal to
>		Greater than
<		Less than
>=	!<	Greater than or equal to (or not less than)
<=	!>	Less than or equal to (or not greater than)

The operator always sits between the two values you are comparing. For example, the query below compares the value of the FilmRunTimeMinutes field with the value 180:

SELECT

FilmName

,FilmRunTimeMinutes

FROM

tblFilm

WHERE

FilmRunTimeMinutes >= 180

The query shows all of the films whose running time is greater than or equal to 180:

The shortest film in this list is 180 minutes long.

Finding Numbers Within a Range

You can use the BETWEEN keyword to find numbers that fall between an upper and lower limit. The example below would find all of the films whose running time is between 90 and 100 minutes.

SELECT

FilmName

,FilmRunTimeMinutes

FROM

tblFilm

WHERE

FilmRunTimeMinutes BETWEEN 90 AND 100

A selection of the results from this query are shown below:

You can see from the diagram that films lasting exactly 90 and 100 minutes are included in the results of the query.

Finding Numbers in a List of Values

You can use the IN keyword to find records that match any values in a list of numbers. The example below looks for films whose running time is exactly 100, 150 or 200 minutes:

SELECT

FilmName

,FilmRunTimeMinutes

FROM

tblFilm

WHERE

FilmRunTimeMinutes IN (100,150,200)

Note that the list of numbers must be enclosed in a set of parentheses. The results of this query are shown in the diagram below:

There aren't any films with a running time of exactly 200 minutes.

What's Next?

Now that you've seen how easy it is to write criteria using numbers it's time to see a few of the quirks of writing criteria involving text.

Previous
tutorial

Writing queries

Previous
part

Using Criteria in SQL Queries

Top of page

Next
part

Criteria Using Text

Next
tutorial

WHERE criteria (version B)

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

WHERE criteria (version A)

Criteria Using Numbers

Comparison Operators for Numbers

Finding Numbers Within a Range

Finding Numbers in a List of Values

What's Next?

Head office

London

Manchester