# SELECT, WHERE & ORDER BY

Code With Purpose · English · Free starter lesson

## Concept

A relational table holds rows and named columns. SELECT chooses the columns to return; FROM names the table. WHERE filters individual rows using conditions such as score >= 70. ORDER BY sorts the resulting rows; DESC means descending.

Use single quotes for text literals. AND requires both conditions to be true; OR requires at least one. SQL NULL represents missing or unknown information. Use IS NULL or IS NOT NULL to test for it, rather than = NULL.

The example uses SQLite and Python’s built-in sqlite3 module so you can run it without a separate database server. The database lives in memory for this run. Other SQL dialects may differ. For real user input, use query parameters instead of building SQL by joining strings.

## Example

```python
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE learners (name TEXT, score INTEGER)")
db.executemany("INSERT INTO learners VALUES (?, ?)",
               [("Asha", 82), ("Ravi", 65), ("Meera", 91)])
rows = db.execute("""
    SELECT name, score FROM learners
    WHERE score >= 70 ORDER BY score DESC
""").fetchall()
print(rows)
db.close()
```

## Expected output

```text
[('Meera', 91), ('Asha', 82)]
```

## Exercise

Using the same table, return only names of learners with scores from 65 to 85 inclusive, sorted by name.

## Suggested solution

```
SELECT name
FROM learners
WHERE score BETWEEN 65 AND 85
ORDER BY name;
-- Asha, Ravi
```

## Takeaway

Filter rows first, then choose a deliberate ordering.

Reference: https://www.sqlite.org/lang_select.html

https://codewithpurposeai.in/lesson-sql-select-filter.html?lang=english