6.9. SQL Delete
Write your statement starting with
--after you're done, make sure there is no mistake, then remove comment and execute
6.9.1. Delete One
Removes data from table
Leaves table structure intact
Can be narrowed down by a
WHERE
DELETE FROM astronauts
WHERE id = 1;
6.9.2. Delete Many
Removes data from table
Leaves table structure intact
Can be narrowed down by a
WHERE
DELETE FROM astronauts
WHERE agency = 'NASA';
6.9.3. Delete Query
Removes data from table
Leaves table structure intact
Can be narrowed down by a
WHERE
DELETE FROM astronauts
WHERE firstname = 'Mark'
AND lastname = 'Watney';
6.9.4. Truncate
Removes all the data
Leaves table structure intact
TRUNCATE TABLE astronauts;
6.9.5. Drop
Removes all the data
Removes table too
DROP TABLE astronauts;
6.9.6. 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 Delete One
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3
# %% English
# 1. Write SQL query to delete one record:
# - table: `contacts`
# - where: `id` == 1
# 2. Run doctests - all must succeed
# %% Polish
# 1. Napisz zapytanie SQL aby skasować rekord:
# - tabela: `contacts`
# - gdzie: `id` == 1
# 2. Uruchom doctesty - wszystkie muszą się powieść
# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pathlib import Path
>>> import sqlite3
>>> database = Path(__file__).parent.parent / 'shop.db'
>>> db = sqlite3.connect(database)
>>> _ = db.execute(result)
>>> db.commit()
>>> USERS = 'SELECT COUNT(*) FROM `contacts` WHERE `id` == 1'
>>> users = db.execute(USERS).fetchone()[0]
>>> assert users == 0
>>> db.close()
"""
# Write SQL query to delete one record:
# - table: `contacts`
# - where: `id` == 1
# type: 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 Delete Many
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3
# %% English
# 1. Write SQL query to delete many records:
# - table: `contacts`
# - where: `lastname` == 'Watney'
# 2. Run doctests - all must succeed
# %% Polish
# 1. Napisz zapytanie SQL aby skasować wiele rekordów:
# - tabela: `contacts`
# - gdzie: `lastname` == 'Watney'
# 2. Uruchom doctesty - wszystkie muszą się powieść
# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pathlib import Path
>>> import sqlite3
>>> database = Path(__file__).parent.parent / 'shop.db'
>>> db = sqlite3.connect(database)
>>> _ = db.execute(result)
>>> db.commit()
>>> USERS = 'SELECT COUNT(*) FROM `contacts` WHERE `lastname` == "Watney"'
>>> users = db.execute(USERS).fetchone()[0]
>>> assert users == 0
>>> db.close()
"""
# Write SQL query to delete many records:
# - table: `contacts`
# - where: `lastname` == 'Watney'
# type: 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 Delete All
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3
# %% English
# 1. Write SQL query to delete all records:
# - table: `contacts`
# - record: all
# 2. Run doctests - all must succeed
# %% Polish
# 1. Napisz zapytanie SQL aby skasować wszystkie rekordy:
# - tabela: `contacts`
# - rekord: wszystkie
# 2. Uruchom doctesty - wszystkie muszą się powieść
# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pathlib import Path
>>> import sqlite3
>>> database = Path(__file__).parent.parent / 'shop.db'
>>> db = sqlite3.connect(database)
>>> _ = db.execute(result)
>>> db.commit()
>>> USERS = 'SELECT COUNT(*) FROM `contacts`'
>>> users = db.execute(USERS).fetchone()[0]
>>> assert users == 0
>>> db.close()
"""
# Write SQL query to delete all records:
# - table: `contacts`
# - record: all
# type: str
result = """
-- replace this comment
-- with your sql query
"""