v Selecting Rows Based On Conditionals - SQL

Selecting Rows Based On Conditionals

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.

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

Create Data

%%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);
[]

View All Rows

%%sql

--  Select all
SELECT *

-- From the criminals table
FROM criminals
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

%%sql

--  Select all
SELECT distinct *

-- From the criminals table
FROM criminals

-- Where age is greater than 30
WHERE age > 30
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

%%sql

--  Select all
SELECT distinct *

-- From the criminals table
FROM criminals

-- Where age is greater than 23
WHERE age >= 23
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

%%sql

--  Select all
SELECT distinct *

-- From the criminals table
FROM criminals

-- Where age is greater than 23
WHERE age = 23
pid name age sex city minor
632 Jack Killer 23 F San Francisco 0

View Rows Where Age Is Not 23

%%sql

--  Select all
SELECT distinct *

-- From the criminals table
FROM criminals

-- Where age is greater than 23
WHERE age <> 23
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'

%%sql

--  Select all
SELECT distinct *

-- From the criminals table
FROM criminals

-- Where name starts with 'J'
WHERE name LIKE 'J%'
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'

%%sql

--  Select all
SELECT distinct *

-- From the criminals table
FROM criminals

-- Where name contains the string 'ames'
WHERE name LIKE '%ames%'
pid name age sex city minor
412 James Smith 15 M Santa Rosa 1