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

OR

SELECT * FROM books
WHERE author_lname='Eggers' AND
released_year > 2010;

Let's Try Something!

SELECT * FROM books
WHERE author_lname='Eggers' OR
released_year > 2010;

Condition 1

Condition 2

AND

BOTH SIDES MUST BE TRUE

Condition 1

Condition 2

OR

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 AND
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 AND 99 - 5 <= 94;
SELECT 1 IN (5,3) OR 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

+--------------+----------------+---------+
| author_fname | author_lname   | COUNT   |
+--------------+----------------+---------+
| Jhumpa       | Lahiri         | 2 books |
| Neil         | Gaiman         | 3 books |
| Dave         | Eggers         | 3 books |
| Michael      | Chabon         | 1 book  |
| Patti        | Smith          | 1 book  |
| Raymond      | Carver         | 2 books |
| Don          | DeLillo        | 1 book  |
| John         | Steinbeck      | 1 book  |
| David        | Foster Wallace | 2 books |
| Dan          | Harris         | 1 book  |
| Freida       | Harris         | 1 book  |
| George       | Saunders       | 1 book  |
+--------------+----------------+---------+

Bonus: Make This Happen

BURN ALL

THE BOOKS

We're Done With Them!

MySQL 2.0:Logical Operators

By Colt Steele

MySQL 2.0:Logical Operators

  • 5,491

More from Colt Steele