6.12. SQL Select From

6.12.1. All Columns

SELECT * FROM astronauts;

6.12.2. Selected Columns

SELECT `firstname`, `lastname`
FROM astronauts;

6.12.3. Column Name Aliases

SELECT firstname AS fname,
       lastname AS lname
FROM astronauts;

SELECT firstname,
       lastname,
       previous_job as career
FROM astronauts;

6.12.4. Assignments

# %% 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

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

# %% About
# - Name: Database Select All
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% English
# 1. Write SQL query to select data:
#    - table: `users`
#    - columns: all
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wybrać dane:
#    - tabela: `users`
#    - kolumny: wszystkie
# 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=250)
[{'id': 1, 'firstname': 'Mark', 'lastname': 'Watney', 'group': 'users', 'gender': 'male', 'birthdate': '1994-10-12', 'ssn': '94101212345', 'email': 'mwatney@nasa.gov', 'phone': '+1 (234) 555-0000'},
 {'id': 2, 'firstname': 'Melissa', 'lastname': 'Lewis', 'group': 'admins', 'gender': 'female', 'birthdate': '1995-07-15', 'ssn': '95071512345', 'email': 'mlewis@nasa.gov', 'phone': '+1 (234) 555-0001'},
 {'id': 3, 'firstname': 'Rick', 'lastname': 'Martinez', 'group': 'users', 'gender': 'male', 'birthdate': '1996-01-21', 'ssn': '96012112345', 'email': 'rmartinez@nasa.gov', 'phone': '+1 (234) 555-0010'},
 {'id': 4, 'firstname': 'Alex', 'lastname': 'Vogel', 'group': 'staff', 'gender': 'male', 'birthdate': '1994-11-15', 'ssn': '94111512345', 'email': 'avogel@esa.int', 'phone': '+49 (234) 555-0011'},
 {'id': 5, 'firstname': 'Beth', 'lastname': 'Johanssen', 'group': 'admins', 'gender': 'female', 'birthdate': '2006-05-09', 'ssn': '06250912345', 'email': 'bjohanssen@nasa.gov', 'phone': '+1 (234) 555-0100'},
 {'id': 6, 'firstname': 'Chris', 'lastname': 'Beck', 'group': 'users', 'gender': 'male', 'birthdate': '1999-08-02', 'ssn': '99080212345', 'email': 'cbeck@nasa.gov', 'phone': '+1 (234) 555-0101'}]
"""

# Write SQL query to select data:
# - table: `users`
# - columns: all
# result: str
result = """

-- replace this comment
-- with your sql query

"""

# %% 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

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

# %% About
# - Name: Database Select Column
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% English
# 1. Write SQL query to select data:
#    - table: `users`
#    - columns: `firstname`, `lastname`
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wybrać dane:
#    - tabela: `users`
#    - kolumny: `firstname`, `lastname`
# 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': 'Chris', 'lastname': 'Beck'},
 {'firstname': 'Beth', 'lastname': 'Johanssen'},
 {'firstname': 'Melissa', 'lastname': 'Lewis'},
 {'firstname': 'Rick', 'lastname': 'Martinez'},
 {'firstname': 'Alex', 'lastname': 'Vogel'},
 {'firstname': 'Mark', 'lastname': 'Watney'}]
"""

# Write SQL query to select data:
# - table: `users`
# - columns: `firstname`, `lastname`
# type: str
result = """

SELECT *
FROM `users`

"""

# %% 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

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

# %% About
# - Name: Database Select Alias
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% English
# 1. Write SQL query to select data:
#    - table: `users`
#    - columns: `firstname`, `lastname`
#    - alias: `firstname` as `fname`
#    - alias: `lastname` as `lname`
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wybrać dane:
#    - tabela: `users`
#    - kolumny: `firstname`, `lastname`
#    - alias: `firstname` jako `fname`
#    - alias: `lastname` jako `lname`
# 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)
[{'fname': 'Chris', 'lname': 'Beck'},
 {'fname': 'Beth', 'lname': 'Johanssen'},
 {'fname': 'Melissa', 'lname': 'Lewis'},
 {'fname': 'Rick', 'lname': 'Martinez'},
 {'fname': 'Alex', 'lname': 'Vogel'},
 {'fname': 'Mark', 'lname': 'Watney'}]
"""

# Write SQL query to select data:
# - table: `users`
# - columns: `firstname`, `lastname`
# - alias: `firstname` as `fname`
# - alias: `lastname` as `lname`
# result: str
result = """

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

"""