Multiple criteria

Intermediate SQL

Jasmin Ludolf

Data Science Content Developer, DataCamp

Multiple criteria

Image showing a variety of colorful coats

Multiple criteria

Image showing a variety of colorful coats with circles around the two yellow coats

Multiple criteria

Image showing a variety of colorful coats with a circle around one coat that is both yellow and shorter

Multiple criteria

OR, AND, BETWEEN

SELECT *
FROM coats
WHERE color = 'yellow' OR length = 'short';

SELECT *
FROM coats
WHERE color = 'yellow' AND length = 'short';

SELECT *
FROM coats
WHERE buttons BETWEEN 1 AND 5;

OR operator

Use OR when you need to satisfy at least one condition

Image with a variety of colorful coats with a circle around the purple coat and green coat

OR operator

Correct:

SELECT title
FROM films
WHERE release_year = 1994
    OR release_year = 2000;

|title                    |
|-------------------------|
|3 Ninjas Kick Back       |
|A Low Down Dirty Shame   |
|Ace Ventura:Pet Detective|
...

Invalid:

SELECT title
FROM films
WHERE release_year = 1994 OR 2000;

argument of OR must be type boolean, 
not type integer
LINE 3: WHERE release_year = 1994 
OR 2000;
   ^

AND operator

Use AND if we need to satisfy all criteria
Correct:

SELECT title
FROM films
WHERE release_year > 1994
    AND release_year < 2000;

|title                        |
|-----------------------------|
|Ace Ventura:When Nature Calls|
|Apollo 13                    |
|Assassins                    |
|Babe                         |
...

Invalid:

SELECT title
FROM films
WHERE release_year > 1994 AND < 2000;

syntax error at or near "[removed] 
1994 AND < 2000;
   ^

AND, OR

Filter films released in 1994 or 1995, and certified PG or R
Enclose individual clauses in parentheses

SELECT title
FROM films
WHERE (release_year = 1994 OR release_year = 1995)
    AND (certification = 'PG' OR certification = 'R');

|title                 |
|----------------------|
|3 Ninjas Kick Back    |
|A Low Down Dirty Shame|
|Baby's Day Out        |
|Beverly Hills Cop III |
...

BETWEEN, AND

SELECT title
FROM films
WHERE release_year >= 1994
    AND release_year <= 2000;

|title                    |
|-------------------------|
|3 Ninjas Kick Back       |
|A Low Down Dirty Shame   |
|Ace Ventura:Pet Detective|
|Baby's Day Out           |
...

SELECT title
FROM films
WHERE release_year
    BETWEEN 1994 AND 2000;

|title                    |
|-------------------------|
|3 Ninjas Kick Back       |
|A Low Down Dirty Shame   |
|Ace Ventura:Pet Detective|
|Baby's Day Out           |
...

BETWEEN, AND, OR

SELECT title
FROM films
WHERE release_year
BETWEEN 1994 AND 2000 AND country='UK';

|title                      |
|---------------------------|
|Four Weddings and a Funeral|
|The Hudsucker Proxy        |
|Dead Man Walking           |
|GoldenEye                  |
...

Let's practice!

Intermediate SQL