6.15. SQL Select Where

  • Order clauses to filter out the most data first!

6.15.1. Selection

  • = - equals

  • != - not equal

  • <> - not equal

  • > - greater then

  • >= - greater or equal

  • < - less than

  • <= - less or equal

SELECT *
FROM astronauts
WHERE `lastname` = 'Watney';
SELECT *
FROM astronauts
WHERE agency != 'NASA';
SELECT *
FROM astronauts
WHERE age > 30;

6.15.2. Contains

  • IN - contains

  • NOT IN - not contains

SELECT *
FROM astronauts
WHERE career IN ('Pilot', 'Engineer', 'Scientist', 'Medical Doctor');
SELECT *
FROM astronauts
WHERE `lastname` NOT IN ('Watney', 'Lewis', 'Martinez');

6.15.3. Identity

  • IS - identity check

  • IS NOT - negation of an identity check

SELECT *
FROM astronauts
WHERE mission IS NULL;
SELECT *
FROM astronauts
WHERE mission IS NOT NULL;

6.15.4. Like

  • LIKE

  • % - Any character (many)

  • _ - Any character (one)

SELECT *
FROM astronauts
WHERE `lastname` LIKE 'Wat%';
SELECT *
FROM astronauts
WHERE `lastname` LIKE '%ney';
SELECT *
FROM astronauts
WHERE `lastname` LIKE '%tn%';
SELECT *
FROM astronauts
WHERE `lastname` LIKE 'Watne_';
SELECT *
FROM astronauts
WHERE `lastname` LIKE '_tn%';

6.15.5. Assignments

# %% About
# - Name: Database Where Equal
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% English
# 1. Write SQL query to select data:
#    - table: `users`
#    - columns: `firstname`, `lastname`
#    - where: `group` is 'users'
#    - use: == or =
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wybrać dane:
#    - tabela: `users`
#    - kolumny: `firstname`, `lastname`
#    - gdzie: `group` to 'users'
#    - użyj: == or =
# 2. Uruchom doctesty - wszystkie muszą się powieść

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pprint import pprint
>>> from pathlib import Path
>>> import sqlite3

>>> database = Path(__file__).parent.parent / 'shop.db'
>>> with sqlite3.connect(database) as db:
...    db.row_factory = sqlite3.Row
...    data = map(dict, db.execute(result).fetchall())

>>> pprint(list(data), sort_dicts=False, width=79)
[{'firstname': 'Mark', 'lastname': 'Watney'},
 {'firstname': 'Rick', 'lastname': 'Martinez'},
 {'firstname': 'Chris', 'lastname': 'Beck'}]
"""

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% Imports

# %% Types
result: str

# %% Data

# %% Result
result = """

SELECT `firstname`, `lastname`
FROM `users`

"""

# %% About
# - Name: Database Where NotEqual
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% English
# 1. Write SQL query to select data:
#    - table: `users`
#    - columns: `firstname`, `lastname`
#    - where: `group` is not 'users'
#    - use: != or <>
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wybrać dane:
#    - tabela: `users`
#    - kolumny: `firstname`, `lastname`
#    - gdzie: `group` to nie 'users'
#    - użyj: != or <>
# 2. Uruchom doctesty - wszystkie muszą się powieść

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pprint import pprint
>>> from pathlib import Path
>>> import sqlite3

>>> database = Path(__file__).parent.parent / 'shop.db'
>>> with sqlite3.connect(database) as db:
...    db.row_factory = sqlite3.Row
...    data = map(dict, db.execute(result).fetchall())

>>> pprint(list(data), sort_dicts=False, width=79)
[{'firstname': 'Melissa', 'lastname': 'Lewis'},
 {'firstname': 'Alex', 'lastname': 'Vogel'},
 {'firstname': 'Beth', 'lastname': 'Johanssen'}]
"""

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% Imports

# %% Types
result: str

# %% Data

# %% Result
result = """

SELECT `firstname`, `lastname`
FROM `users`
WHERE `group`

"""

# %% About
# - Name: Database Where GreaterThan
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% English
# 1. Write SQL query to select data:
#    - table: `users`
#    - columns: `firstname`, `lastname`, `birthdate`
#    - where: `birthdate` is later than '2000-01-01' (exclusive)
#    - use: >
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wybrać dane:
#    - tabela: `users`
#    - kolumny: `firstname`, `lastname`, `birthdate`
#    - gdzie: `birthdate` jest późniejsza niż '2000-01-01' (rozłącznie)
#    - użyj: >
# 2. Uruchom doctesty - wszystkie muszą się powieść

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pprint import pprint
>>> from pathlib import Path
>>> import sqlite3

>>> database = Path(__file__).parent.parent / 'shop.db'
>>> with sqlite3.connect(database) as db:
...    db.row_factory = sqlite3.Row
...    data = map(dict, db.execute(result).fetchall())

>>> pprint(list(data), sort_dicts=False, width=79)
[{'firstname': 'Beth', 'lastname': 'Johanssen', 'birthdate': '2006-05-09'}]
"""

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% Imports

# %% Types
result: str

# %% Data

# %% Result
result = """

SELECT `firstname`, `lastname`, `birthdate`
FROM `users`
WHERE `birthdate`

"""

# %% About
# - Name: Database Where GreaterOrEqual
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% English
# 1. Write SQL query to select data:
#    - table: `users`
#    - columns: `firstname`, `lastname`, `birthdate`
#    - where: `birthdate` is later than '2000-01-01' (inclusive)
#    - use: >=
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wybrać dane:
#    - tabela: `users`
#    - kolumny: `firstname`, `lastname`, `birthdate`
#    - gdzie: `birthdate` jest późniejsza niż '2000-01-01' (włącznie)
#    - użyj: >=
# 2. Uruchom doctesty - wszystkie muszą się powieść

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pprint import pprint
>>> from pathlib import Path
>>> import sqlite3

>>> database = Path(__file__).parent.parent / 'shop.db'
>>> with sqlite3.connect(database) as db:
...    db.row_factory = sqlite3.Row
...    data = map(dict, db.execute(result).fetchall())

>>> pprint(list(data), sort_dicts=False, width=79)
[{'firstname': 'Beth', 'lastname': 'Johanssen', 'birthdate': '2006-05-09'}]
"""

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% Imports

# %% Types
result: str

# %% Data

# %% Result
result = """

SELECT `firstname`, `lastname`, `birthdate`
FROM `users`
WHERE `birthdate`

"""

# %% About
# - Name: Database Where LessThan
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% English
# 1. Write SQL query to select data:
#    - table: `users`
#    - columns: `firstname`, `lastname`, `birthdate`
#    - where: `birthdate` is earlier than '2000-01-01' (exclusive)
#    - use: <
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wybrać dane:
#    - tabela: `users`
#    - kolumny: `firstname`, `lastname`, `birthdate`
#    - gdzie: `birthdate` jest wcześniejsza niż '2000-01-01' (rozłączenie)
#    - użyj: <
# 2. Uruchom doctesty - wszystkie muszą się powieść

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pprint import pprint
>>> from pathlib import Path
>>> import sqlite3

>>> database = Path(__file__).parent.parent / 'shop.db'
>>> with sqlite3.connect(database) as db:
...    db.row_factory = sqlite3.Row
...    data = map(dict, db.execute(result).fetchall())

>>> pprint(list(data), sort_dicts=False, width=79)
[{'firstname': 'Mark', 'lastname': 'Watney', 'birthdate': '1994-10-12'},
 {'firstname': 'Melissa', 'lastname': 'Lewis', 'birthdate': '1995-07-15'},
 {'firstname': 'Rick', 'lastname': 'Martinez', 'birthdate': '1996-01-21'},
 {'firstname': 'Alex', 'lastname': 'Vogel', 'birthdate': '1994-11-15'},
 {'firstname': 'Chris', 'lastname': 'Beck', 'birthdate': '1999-08-02'}]
"""

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% Imports

# %% Types
result: str

# %% Data

# %% Result
result = """

SELECT `firstname`, `lastname`, `birthdate`
FROM `users`
WHERE `birthdate`

"""

# %% About
# - Name: Database Where LessOrEqual
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% English
# 1. Write SQL query to select data:
#    - table: `users`
#    - columns: `firstname`, `lastname`, `birthdate`
#    - where: `birthdate` is earlier than '2000-01-01' (inclusive)
#    - use: <=
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wybrać dane:
#    - tabela: `users`
#    - kolumny: `firstname`, `lastname`, `birthdate`
#    - gdzie: `birthdate` jest wcześniejsza niż '2000-01-01' (włącznie)
#    - użyj: <=
# 2. Uruchom doctesty - wszystkie muszą się powieść

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pprint import pprint
>>> from pathlib import Path
>>> import sqlite3

>>> database = Path(__file__).parent.parent / 'shop.db'
>>> with sqlite3.connect(database) as db:
...    db.row_factory = sqlite3.Row
...    data = map(dict, db.execute(result).fetchall())

>>> pprint(list(data), sort_dicts=False, width=79)
[{'firstname': 'Mark', 'lastname': 'Watney', 'birthdate': '1994-10-12'},
 {'firstname': 'Melissa', 'lastname': 'Lewis', 'birthdate': '1995-07-15'},
 {'firstname': 'Rick', 'lastname': 'Martinez', 'birthdate': '1996-01-21'},
 {'firstname': 'Alex', 'lastname': 'Vogel', 'birthdate': '1994-11-15'},
 {'firstname': 'Chris', 'lastname': 'Beck', 'birthdate': '1999-08-02'}]
"""

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% Imports

# %% Types
result: str

# %% Data

# %% Result
result = """

SELECT `firstname`, `lastname`, `birthdate`
FROM `users`
WHERE `birthdate`

"""