Updating and Deleting Data: Modifying Existing Records

SELECT retrieves specific columns and rows from a database table using WHERE to filter and ORDER BY to sort results.

Programming

From Table to Result Set

A database table may contain many rows and columns, but a query does not have to return everything. A SELECT statement chooses the columns to retrieve, names the table, and can optionally restrict which rows appear. WHERE removes rows that do not match a condition, while ORDER BY controls the order of the rows that remain.

Which columns and rows remain after SELECT and WHERE are applied, and how does ORDER BY arrange them?

Reading Query Structure

Definition

The basic structure of a SELECT statement is SELECT columns FROM table. The columns part identifies the values to retrieve, and the table part identifies where those values come from. An asterisk means all columns. WHERE is optional and filters rows by a condition. ORDER BY is optional and sorts the matching rows in ascending or descending order.

sql

SELECT * FROM Track WHERE title = 'My Way';

SELECT * FROM Track WHERE title = 'My Way';

This query retrieves every column from the Track table, but only for rows whose title equals My Way. If you need only particular values, list column names separated by commas instead of using the asterisk. Conditions can use comparison operators such as equals, less than, greater than, less than or equal to, greater than or equal to, and not equal to.

sql

SELECT title, plays
FROM Track
WHERE title != 'My Way'
ORDER BY plays DESC;

SELECT title, plays
FROM Track
WHERE title != 'My Way'
ORDER BY plays DESC;

In this generated example, SELECT chooses title and plays, WHERE keeps rows whose title is not My Way, and ORDER BY requests a descending sort by plays. The important sequence is conceptual: choose columns, identify the table, filter rows when needed, and sort the result when needed.

Following Rows Through a Cursor

After a SELECT statement is executed, the result is represented by a cursor. A cursor is a pointer to the result set. It does not immediately place every matching row in memory. Instead, it can fetch rows on demand as your program moves through the result.

How does control move from one database row to the next as a Python for loop processes the cursor?

Code walkthrough

for row in cursor:
    title, plays = row
    print(title, plays)

1
for row in cursor tells Python to iterate through the cursor.
2
title, plays = row unpacks the tuple into two variables.
3
print(title, plays) uses the values from the current row.

The for loop obtains the next row from the cursor for each iteration.
The row is a tuple, so tuple unpacking assigns its values to title and plays.
The loop body can process the current row before the cursor advances.

Tuples and Column Order

Each row fetched from the cursor arrives as a Python tuple. The tuple is an ordered sequence, and its values appear in the same left-to-right order as the columns in the SELECT statement. If the query selects title and plays, the first tuple element is title and the second is plays.

SELECT column order	Tuple position	Access method
title	0	row[0]
plays	1	row[1]

A row returned from SELECT title, plays follows the same order as the SELECT list.

You can retrieve tuple elements by index, starting at index 0, or unpack the tuple into separate variables. Unpacking is useful when the meaning of each selected column is clear. Index access is useful when you need to refer to a particular position directly.

Why On-Demand Retrieval Matters

How does data move from the database through the cursor to Python one row at a time?

On-demand retrieval means the program can begin processing rows without first loading the entire result set. This keeps memory usage low, which is especially important when a query may return thousands or millions of rows. The program processes the current tuple, then the cursor supplies the next row.

Sorting also belongs in the query when possible. ORDER BY lets the database sort the result instead of making the Python program sort the rows after retrieval. This can be more efficient, especially for a large result set.

Mistakes with Queries and Cursors

Common mistake
Selecting every column when only a few are needed
What it looks like
```
SELECT * FROM Track;
```
Why it fails
The asterisk retrieves all columns, even when the program needs only particular values.
Do this instead
List the required columns, such as SELECT title, plays FROM Track;
Common mistake
Expecting WHERE to sort rows
What it looks like
```
WHERE title != 'My Way'
```
Why it fails
WHERE filters rows but does not specify their order.
Do this instead
Use ORDER BY when the result must be sorted.
Common mistake
Assuming a fetched row is a single value
What it looks like
```
title = row
```
Why it fails
A database row arrives as a tuple containing the selected column values.
Do this instead
Use tuple unpacking or index access, such as title, plays = row or row[0].
Common mistake
Loading all rows without considering result size
What it looks like
```
cursor.fetchall() for a potentially huge result
```
Why it fails
Fetching all rows at once places the complete result set in memory.
Do this instead
Iterate through the cursor so rows are fetched and processed on demand.

Practice the Retrieval Flow

Worked example

From Query to Python Variables

Suppose a query selects title and plays from Track, filters rows by a WHERE condition, and sorts the matching rows with ORDER BY. Explain what Python receives during cursor iteration.

1
Choose columns
Because the SELECT list contains title followed by plays, every returned tuple places the title value first and the plays value second.
2
Filter rows
The WHERE clause allows only rows that satisfy its condition to enter the result set.
3
Sort rows
The ORDER BY clause determines the order in which the matching rows appear in the cursor.
4
Iterate
A Python for loop receives one tuple at a time from the cursor and can unpack each tuple into title and plays.

Result

The program processes only matching rows, in database-defined order, with each row represented as a tuple whose positions follow the SELECT column order.

EASY

Describe what changes when SELECT title, plays is changed to SELECT plays, title. Identify which value appears at index 0, and explain why the order of rows may still be controlled separately with ORDER BY.

Hints

Tuple positions follow the order of columns in SELECT.
Filtering and column selection are separate from row sorting.
ORDER BY names the column or columns used to sort the result.

Your attempt (not saved)

Key Takeaways

SELECT chooses columns and identifies the table from which rows are retrieved.
WHERE filters rows, while ORDER BY sorts the rows that remain.
A cursor represents the result set and supplies rows on demand instead of loading everything at once.
A Python for loop can process each cursor row as it arrives.
Each returned row is a tuple whose values follow the SELECT column order.

More concepts starting with U