Concepts / Defining Database Tables with CREATE TABLE

Defining Database Tables with CREATE TABLE

The table name and column names must line up across operations. The source SELECT reads title and plays from Track, so those are the pieces of each returned row that the program can inspect.

  • Programming

One Table, Four Operations

A database table changes through a sequence of operations. INSERT adds rows, SELECT reads data, and DELETE removes rows that match a condition. The source program uses these operations with a music database and a Track table: it inserts two tracks, reads their title and play count, and then deletes the rows it created so the program can be run again without accumulating duplicate data.

Track tabletable structureINSERTtwo rows addedSELECTrows readDELETEmatching rows removedstores rowsmakes rows availableafter reading
How do CREATE TABLE, INSERT, SELECT, and DELETE relate to the same table over time?

The Table Structure

CREATE TABLE establishes a table that later operations can use. In the source program, the table is named Track. The stored records include a title and a play count, because SELECT later requests the title and plays columns. The important distinction is that creating or having a table establishes where rows belong, while INSERT, SELECT, and DELETE work with the rows in that table.

Table definitionTracktitlecolumntrack rowtitle and play countplayscolumncontainscontainsholds a value inholds a value in
How does a table provide a structure for columns and stored rows?
Key point

The table name and column names must line up across operations. The source SELECT reads title and plays from Track, so those are the pieces of each returned row that the program can inspect.

Adding Rows and Saving Them

INSERT adds rows to a table. In the source program, the first two executable operations insert Thunderstruck with 20 plays and My Way with 15 plays. Each INSERT uses question-mark placeholders, and the actual values are supplied separately as a tuple to execute(). This parameterized form should be used instead of building a SQL statement through string concatenation, because parameterized queries help prevent SQL injection vulnerabilities.

Track tableno newly inserted rowsTransaction buffertwo inserted rowsDatabase filetwo rows savedINSERTcommit()
What changes when INSERT adds rows, and how does commit() make that change permanent in the database file?

INSERT first places the new rows in the transaction buffer. Calling commit() is the step that permanently saves those changes to the database file. If the program crashes before commit() is called, the inserted rows are lost rather than being written permanently to the file.

Worked example

Saving Two Music Tracks

Trace the source program after it inserts Thunderstruck with 20 plays and My Way with 15 plays.

  1. 1
    Insert the first row

    INSERT adds the Thunderstruck row to the transaction buffer.

  2. 2
    Insert the second row

    INSERT adds the My Way row to the transaction buffer.

  3. 3
    Commit the transaction

    commit() permanently saves both inserted rows to the database file.

Result

The database file permanently contains the two inserted Track rows after commit() completes.

Reading Results Through a Cursor

SELECT retrieves data from a table. The source SELECT requests the title and plays columns from Track, rather than describing the result as an undifferentiated collection of all table data. A cursor then supplies the returned rows during iteration. Each returned row is a tuple containing two values: the track title and the play count. For example, the source explains that a row can appear as ('Thunderstruck', 20).

SELECT resulttitle and playsCursoron-demand readerThunderstruck20My Way15returns rowsfirst iterationnext iteration
How does a cursor retrieve one row at a time instead of loading the entire result set into memory?

The cursor does not immediately load every matching row into memory when SELECT executes. Instead, it reads data on demand as the program loops through the results. The cursor fetches rows one at a time, or in small batches, as iteration continues. This is memory-efficient, especially when a table contains millions of rows, because the entire result set does not have to be loaded at once.

Tracktabletitle, playsselected columnsThunderstruck20My Way15SELECTfirst rownext iteration
How does SELECT choose particular columns, and how does the cursor move through the returned rows?

What do you think happens?

After SELECT requests title and plays from Track, how does the cursor provide the two matching rows?

  • It loads every possible table value before iteration begins

  • It provides the rows during iteration, on demand

  • It permanently deletes each row after reading it

  • It changes the selected columns into a single value

Reveal answer
Answer:

It provides the rows during iteration, on demand

The cursor reads rows as the loop progresses rather than loading the entire result set into memory at once.

Filtering Deletions Safely

DELETE removes rows from a table. Its WHERE clause identifies the rows that should be removed. In the source program, the condition is plays less than 100. Thunderstruck has 20 plays and My Way has 15, so both rows match and are deleted.

Thunderstruck20 playsMy Way15 playsplays < 100WHERE conditionTrack tableno matching source rowsmatchesmatchesDELETE removes matches
Which rows are selected by the WHERE condition, and which rows remain after DELETE runs?

Deletion also needs commit() when the removal must be permanent. Before that commit(), the rows are removed from the transaction buffer, but the database file still contains them. The source program commits after DELETE so the rows it created are permanently removed and the program can be run repeatedly without accumulating duplicates.

Trace the Complete Sequence

Worked example

From Insert to Cleanup

Trace the source music-database program as it creates, reads, and removes two Track rows.

  1. 1
    Start with the Track table

    The program works with the Track table and its title and plays data.

  2. 2
    Run both INSERT operations

    Thunderstruck with 20 plays and My Way with 15 plays are placed in the transaction buffer.

  3. 3
    Call commit()

    The two inserted rows are permanently saved to the database file.

  4. 4
    Run SELECT

    The program requests title and plays from Track and iterates through the returned tuples with a cursor.

  5. 5
    Run DELETE with plays less than 100

    Both source rows match because 20 and 15 are each less than 100.

  6. 6
    Call commit() again

    The deletion is permanently written to the database file.

Result

Both source tracks are deleted, leaving the table without those rows so the program can be run again without accumulating duplicate copies.

StageTable or transaction effectPersistent file effect
INSERTTwo rows enter the transaction bufferNot permanent yet
First commit()Inserted rows remain availableRows are saved to the database file
SELECT and cursor iterationRows are read on demandNo table contents are changed
DELETE with plays less than 100Both source rows are removed from the transaction bufferRemoval is not permanent yet
Second commit()Deleted rows remain removedRemoval is saved to the database file

The source program's state changes in sequence

Mistakes to Catch Early

  • Common mistake

    Assuming INSERT is permanent without calling commit().

    What it looks like

    Rows appear in the transaction buffer, but the program crashes before commit().

    Why it fails

    The rows have not been written permanently to the database file and can be lost.

    Do this instead

    Call commit() after the INSERT operations when the changes must persist.

  • Common mistake

    Treating cursor iteration as if every result row is loaded immediately.

    What it looks like

    Expecting SELECT to place an entire very large result set in memory before the loop starts.

    Why it fails

    The cursor reads rows on demand during iteration rather than loading all rows at once.

    Do this instead

    Think of the cursor as providing one row at a time, or in small batches, as the loop progresses.

  • Common mistake

    Running DELETE without a WHERE clause.

    What it looks like

    Using DELETE when only rows satisfying a particular condition should be removed.

    Why it fails

    Without a WHERE condition, the operation can accidentally delete all data.

    Do this instead

    Use a WHERE clause that identifies the intended rows.

  • Common mistake

    Forgetting to commit a deletion.

    What it looks like
    DELETE removes rows from the transaction buffer, but the program ends before commit().
    Why it fails

    The database file can still contain the rows.

    Do this instead

    Call commit() after DELETE when the removal must be permanently saved.

  • Common mistake

    Building SQL through string concatenation instead of parameterized queries.

    What it looks like

    Combining input values directly into a SQL statement.

    Why it fails

    String concatenation can create SQL injection vulnerabilities.

    Do this instead

    Use question-mark placeholders and pass the actual values separately to execute().

Best practice

When diagnosing an unexpected database state, check the operation order, whether commit() was called after INSERT or DELETE, which columns SELECT requested, whether the cursor was iterated, and whether the DELETE condition matched the rows you expected.

Check Your Understanding

MEDIUM

Trace this situation in words: the Track table contains Thunderstruck with 20 plays and My Way with 15 plays. SELECT requests title and plays, the cursor iterates through the results, and DELETE uses the condition plays less than 20. Which source row matches the condition, which row remains, and what additional operation is needed to make the deletion permanent in the database file?

Hints
  • Compare each play count with 20.
  • A WHERE condition selects only rows that satisfy it.
  • DELETE changes the transaction state first.
  • commit() is needed to persist the deletion to the database file.
Worked example

Practice Answer

Determine the result of DELETE with the condition plays less than 20 for Thunderstruck with 20 plays and My Way with 15 plays.

  1. 1
    Test Thunderstruck

    Its play count is 20, which does not satisfy the condition of being less than 20.

  2. 2
    Test My Way

    Its play count is 15, which satisfies the condition of being less than 20.

  3. 3
    Apply the deletion

    DELETE removes My Way and leaves Thunderstruck in the transaction state.

  4. 4
    Persist the result

    commit() permanently saves the deletion to the database file.

Result

My Way is removed, Thunderstruck remains, and commit() is required to make that removal permanent.

Key Takeaways

  1. 1
    INSERT adds rows to a table, while commit() makes inserted changes permanent in the database file.
  2. 2
    SELECT can request particular columns, such as title and plays, from the Track table.
  3. 3
    A cursor returns rows during iteration and reads them on demand rather than loading the entire result set at once.
  4. 4
    DELETE should use a WHERE clause to identify the rows that should be removed.
  5. 5
    Deletion also requires commit() when the removal must be permanently written to the database file.
  6. 6
    Parameterized queries with question-mark placeholders should be used instead of string concatenation.

At a glance

  • INSERT places new rows into a table, and commit() permanently saves them to the database file.
  • SELECT chooses columns and lets a cursor provide result rows during iteration.
  • Cursors read data on demand, which avoids loading an entire large result set into memory at once.
  • DELETE uses WHERE to select rows for removal, and commit() persists the deletion.
  • Parameterized queries with question-mark placeholders are safer than string concatenation.

Related Topics

Related concepts

More concepts starting with D