LOGICAL
OPERATORS
"Select all books NOT published in 2017"
"Select all birthdays between 1990 and 1992"
"Select all items that are in stock AND priced below $19.99"
!=
Not Equal
SELECT title FROM books
WHERE year != 2017;Select books that were not released in 2017
NOT LIKE
SELECT title FROM books
WHERE title NOT LIKE 'W%';Select books with titles that don't start with 'W'
>
Greater Than
SELECT * FROM books
WHERE released_year > 2000;Select books released after the year 2000
A Small Side Note
SELECT 99 > 1;What Do You Expect?
<
Less Than
SELECT * FROM books
WHERE released_year < 2000;Select books released before the year 2000
<=
Less Than Or Equal To
LOGICAL AND
&&
SELECT books written by Dave Eggers,
published after the year 2010
SELECT books written by Dave Eggers,
published after the year 2010
SELECT books written by Dave Eggers,
published after the year 2010
SELECT * FROM books
WHERE author_lname='Eggers';SELECT * FROM books
WHERE released_year > 2010;SELECT * FROM books
WHERE author_lname='Eggers';SELECT * FROM books
WHERE released_year > 2010;AND
SELECT * FROM books
WHERE author_lname='Eggers' AND
released_year > 2010;
SELECT * FROM books
WHERE author_lname='Eggers' AND
released_year > 2010 AND
title LIKE '%novel%';
LOGICAL OR
||
LOGICAL OR
||
The 'pipe' character
SELECT * FROM books
WHERE author_lname='Eggers' &&
released_year > 2010;
Let's Try Something!
SELECT * FROM books
WHERE author_lname='Eggers' ||
released_year > 2010;
Condition 1
Condition 2
&&
BOTH SIDES MUST BE TRUE
Condition 1
Condition 2
||
ONLY ONE SIDE MUST BE TRUE
BETWEEN
Before we see BETWEEN...
How can we accomplish the same thing using what we already know?
Select all books published between 2004 and 2015
SELECT title, released_year FROM
books WHERE released_year >= 2004 &&
released_year <= 2015;USE LOGICAL AND!
Or...Use BETWEEN
SELECT title, released_year FROM books
WHERE released_year BETWEEN 2004 AND 2015;BETWEEN x AND y
BETWEEN
NOT
Is Also A Thing
SELECT title, released_year FROM books
WHERE released_year NOT BETWEEN 2004 AND 2015;NOT BETWEEN x AND y
A Note About Comparing Dates
IN
Select all books written by...
Carver
Lahiri
Smith
OR...
OR...
We Can Already Do This
SELECT title, author_lname FROM books
WHERE author_lname='Carver' OR
author_lname='Lahiri' OR
author_lname='Smith';IN makes it much easier
SO MUCH SHORTER!
SELECT title, author_lname FROM books
WHERE author_lname IN ('Carver', 'Lahiri', 'Smith');IN
NOT
I'm really superstitious and have a problem with even numbers...
(I'm not actually, I promise)
Select all books not published in
- 2000,
- 2002,
- 2004,
- 2006,
- 2008,
- 2010,
- 2012,
- 2014,
- 2016
SELECT title, released_year FROM books
WHERE released_year != 2000 AND
released_year != 2002 AND
released_year != 2004 AND
released_year != 2006 AND
released_year != 2008 AND
released_year != 2010 AND
released_year != 2012 AND
released_year != 2014 AND
released_year != 2016;Well...that was fun to type
There's a better way!
SELECT title, released_year FROM books
WHERE released_year NOT IN
(2000,2002,2004,2006,2008,2010,2012,2014,2016);Taking It To The Next Level!
I only want books released after 2000
SELECT title, released_year FROM books
WHERE released_year >= 2000
AND released_year NOT IN
(2000,2002,2004,2006,2008,2010,2012,2014,2016);Theres still a better way
It doesn't use IN or NOT IN
%
SELECT title, released_year FROM books
WHERE released_year >= 2000 AND
released_year % 2 != 0;MODULO
CASE STATEMENTS
+-----------------------------------------------------+---------------+------------------+
| title | released_year | GENRE |
+-----------------------------------------------------+---------------+------------------+
| The Namesake | 2003 | Modern Lit |
| Norse Mythology | 2016 | Modern Lit |
| American Gods | 2001 | Modern Lit |
| Interpreter of Maladies | 1996 | 20th Century Lit |
| A Hologram for the King: A Novel | 2012 | Modern Lit |
| The Circle | 2013 | Modern Lit |
| The Amazing Adventures of Kavalier & Clay | 2000 | Modern Lit |
| Just Kids | 2010 | Modern Lit |
| A Heartbreaking Work of Staggering Genius | 2001 | Modern Lit |
| Coraline | 2003 | Modern Lit |
| What We Talk About When We Talk About Love: Stories | 1981 | 20th Century Lit |
| Where I'm Calling From: Selected Stories | 1989 | 20th Century Lit |
| White Noise | 1985 | 20th Century Lit |
| Cannery Row | 1945 | 20th Century Lit |
| Oblivion: Stories | 2004 | Modern Lit |
| Consider the Lobster | 2005 | Modern Lit |
| 10% Happier | 2014 | Modern Lit |
| fake_book | 2001 | Modern Lit |
| Lincoln In The Bardo | 2017 | Modern Lit |
+-----------------------------------------------------+---------------+------------------+SELECT title, released_year,
CASE
WHEN released_year >= 2000 THEN 'Modern Lit'
ELSE '20th Century Lit'
END AS GENRE
FROM books; +-----------------------------------------------------+----------------+-------+
| title | stock_quantity | STOCK |
+-----------------------------------------------------+----------------+-------+
| The Namesake | 32 | * |
| Norse Mythology | 43 | * |
| American Gods | 12 | * |
| Interpreter of Maladies | 97 | ** |
| A Hologram for the King: A Novel | 154 | *** |
| The Circle | 26 | * |
| The Amazing Adventures of Kavalier & Clay | 68 | ** |
| Just Kids | 55 | ** |
| A Heartbreaking Work of Staggering Genius | 104 | *** |
| Coraline | 100 | ** |
| What We Talk About When We Talk About Love: Stories | 23 | * |
| Where I'm Calling From: Selected Stories | 12 | * |
| White Noise | 49 | * |
| Cannery Row | 95 | ** |
| Oblivion: Stories | 172 | *** |
| Consider the Lobster | 92 | ** |
| 10% Happier | 29 | * |
| fake_book | 287 | *** |
| Lincoln In The Bardo | 1000 | *** |
+-----------------------------------------------------+----------------+-------+SELECT title, stock_quantity,
CASE
WHEN stock_quantity BETWEEN 0 AND 50 THEN '*'
WHEN stock_quantity BETWEEN 51 AND 100 THEN '**'
ELSE '***'
END AS STOCK
FROM books; SELECT title, stock_quantity,
CASE
WHEN stock_quantity <= 50 THEN '*'
WHEN stock_quantity <= 100 THEN '**'
ELSE '***'
END AS STOCK
FROM books; A Little More Succinct
YOUR TURN
This is the last time we'll work with the books table!!!
Evaluate the following...
SELECT 10 != 10;SELECT 15 > 14 && 99 - 5 <= 94;SELECT 1 IN (5,3) || 9 BETWEEN 8 AND 10;Select All Books Written Before 1980 (non inclusive)
Select All Books Written By Eggers Or Chabon
Select All Books Written By Lahiri, Published after 2000
Select All books with page counts between 100 and 200
Select all books where author_lname starts with a 'C' or an 'S''
+-----------------------------------------------------+----------------+---------------+
| title | author_lname | TYPE |
+-----------------------------------------------------+----------------+---------------+
| The Namesake | Lahiri | Novel |
| Norse Mythology | Gaiman | Novel |
| American Gods | Gaiman | Novel |
| Interpreter of Maladies | Lahiri | Novel |
| A Hologram for the King: A Novel | Eggers | Novel |
| The Circle | Eggers | Novel |
| The Amazing Adventures of Kavalier & Clay | Chabon | Novel |
| Just Kids | Smith | Memoir |
| A Heartbreaking Work of Staggering Genius | Eggers | Memoir |
| Coraline | Gaiman | Novel |
| What We Talk About When We Talk About Love: Stories | Carver | Short Stories |
| Where I'm Calling From: Selected Stories | Carver | Short Stories |
| White Noise | DeLillo | Novel |
| Cannery Row | Steinbeck | Novel |
| Oblivion: Stories | Foster Wallace | Short Stories |
| Consider the Lobster | Foster Wallace | Novel |
| 10% Happier | Harris | Novel |
| fake_book | Harris | Novel |
| Lincoln In The Bardo | Saunders | Novel |
+-----------------------------------------------------+----------------+---------------+If title contains 'stories' -> Short Stories
Just Kids and A Heartbreaking Work -> Memoir
Everything Else -> Novel
+-----------------------------------------------------+----------------+---------+
| title | author_lname | COUNT |
+-----------------------------------------------------+----------------+---------+
| What We Talk About When We Talk About Love: Stories | Carver | 2 books |
| The Amazing Adventures of Kavalier & Clay | Chabon | 1 book |
| White Noise | DeLillo | 1 book |
| A Hologram for the King: A Novel | Eggers | 3 books |
| Oblivion: Stories | Foster Wallace | 2 books |
| Norse Mythology | Gaiman | 3 books |
| 10% Happier | Harris | 1 book |
| fake_book | Harris | 1 book |
| The Namesake | Lahiri | 2 books |
| Lincoln In The Bardo | Saunders | 1 book |
| Just Kids | Smith | 1 book |
| Cannery Row | Steinbeck | 1 book |
+-----------------------------------------------------+----------------+---------+Bonus: Make This Happen
BURN ALL
THE BOOKS
We're Done With Them!
Logical Operators
By Colt Steele
Logical Operators
- 21,089
