Title: Selecting Rows Based On Conditionals
Slug: select_rows_based_on_conditionals
Summary: Selecting Rows Based On Conditionals in SQL.
Date: 2017-01-16 12:00
Category: SQL
Tags: Basics
Authors: Chris Albon

Note: This tutorial was written using Catherine Devlin's SQL in Jupyter Notebooks library. If you have not using a Jupyter Notebook, you can ignore the two lines of code below and any line containing %%sql. Furthermore, this tutorial uses SQLite's flavor of SQL, your version might have some differences in syntax.

For more, check out Learning SQL by Alan Beaulieu.



In [1]:

    
# Ignore
%load_ext sql
%sql sqlite://
%config SqlMagic.feedback = False

Create Data



In [2]:

    
%%sql

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill Bayes', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Jack Killer', 23, 'F', 'San Francisco', 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'San Francisco', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Petaluma', 0);









    Out[2]:





[]

View All Rows



In [3]:

    
%%sql

--  Select all
SELECT *

-- From the criminals table
FROM criminals









    Out[3]:





    
        pid
        name
        age
        sex
        city
        minor
    
    
        412
        James Smith
        15
        M
        Santa Rosa
        1
    
    
        234
        Bill Bayes
        22
        M
        Santa Rosa
        0
    
    
        632
        Jack Killer
        23
        F
        San Francisco
        0
    
    
        901
        Gordon Ado
        32
        F
        San Francisco
        0
    
    
        512
        Bill Byson
        21
        M
        Petaluma
        0

View Rows Where Age Is Greater Than 30



In [4]:

    
%%sql

--  Select all
SELECT distinct *

-- From the criminals table
FROM criminals

-- Where age is greater than 30
WHERE age > 30









    Out[4]:





    
        pid
        name
        age
        sex
        city
        minor
    
    
        901
        Gordon Ado
        32
        F
        San Francisco
        0

View Rows Where Age Is Greater Than Or Equal To 23



In [5]:

    
%%sql

--  Select all
SELECT distinct *

-- From the criminals table
FROM criminals

-- Where age is greater than 23
WHERE age >= 23









    Out[5]:





    
        pid
        name
        age
        sex
        city
        minor
    
    
        632
        Jack Killer
        23
        F
        San Francisco
        0
    
    
        901
        Gordon Ado
        32
        F
        San Francisco
        0

View Rows Where Age Is 23



In [6]:

    
%%sql

--  Select all
SELECT distinct *

-- From the criminals table
FROM criminals

-- Where age is greater than 23
WHERE age = 23









    Out[6]:





    
        pid
        name
        age
        sex
        city
        minor
    
    
        632
        Jack Killer
        23
        F
        San Francisco
        0

View Rows Where Age Is Not 23



In [7]:

    
%%sql

--  Select all
SELECT distinct *

-- From the criminals table
FROM criminals

-- Where age is greater than 23
WHERE age <> 23









    Out[7]:





    
        pid
        name
        age
        sex
        city
        minor
    
    
        412
        James Smith
        15
        M
        Santa Rosa
        1
    
    
        234
        Bill Bayes
        22
        M
        Santa Rosa
        0
    
    
        901
        Gordon Ado
        32
        F
        San Francisco
        0
    
    
        512
        Bill Byson
        21
        M
        Petaluma
        0

View Rows Where Name Begins With 'J'



In [8]:

    
%%sql

--  Select all
SELECT distinct *

-- From the criminals table
FROM criminals

-- Where name starts with 'J'
WHERE name LIKE 'J%'









    Out[8]:





    
        pid
        name
        age
        sex
        city
        minor
    
    
        412
        James Smith
        15
        M
        Santa Rosa
        1
    
    
        632
        Jack Killer
        23
        F
        San Francisco
        0

View Rows Where Name Contains The String 'ames'



In [9]:

    
%%sql

--  Select all
SELECT distinct *

-- From the criminals table
FROM criminals

-- Where name contains the string 'ames'
WHERE name LIKE '%ames%'









    Out[9]:





    
        pid
        name
        age
        sex
        city
        minor
    
    
        412
        James Smith
        15
        M
        Santa Rosa
        1

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
234	Bill Bayes	22	M	Santa Rosa	0
632	Jack Killer	23	F	San Francisco	0
901	Gordon Ado	32	F	San Francisco	0
512	Bill Byson	21	M	Petaluma	0