# Basic SQL: SELECT, WHERE by id and Simple Filtering<no value>

## What will we learn?

**`SELECT`** = the **R**ead of CRUD: reading data from a table.

- **All the rows**: `SELECT * FROM students`.
- **Chosen columns**: `SELECT name, email FROM students`.
- **One row by id**: `SELECT ... WHERE id = 2`.
- **Simple filtering** with `WHERE` and conditions.
- **Sorting** with `ORDER BY`.

## Why do we need this?

The two most important queries in every API:

| Endpoint | SQL |
|---|---|
| `GET /students` | `SELECT ... FROM students` |
| `GET /students/7` | `SELECT ... FROM students WHERE id = 7` |

Every time you open a list in an app (products, messages, orders), a SELECT query runs behind the scenes.

## Explanation from the basics

### SELECT ... FROM

```sql
SELECT name, age FROM students;
```

"**Select** the columns `name` and `age` **from** the table `students`". The result is **a new (temporary) table**, with the rows and the columns you asked for. The database **does not change**: SELECT only reads, exactly like GET.

- `*` (an asterisk) = **all the columns**.
- The order in the list sets the order of the columns in the result.

### WHERE — filtering rows

```sql
SELECT name, age FROM students WHERE age > 23;
```

`WHERE` = "only the rows for which the condition is **true**". The database checks the condition on every row, and returns only the matching ones.

| Operator | Meaning | Example |
|---|---|---|
| `=` | Equal (**one sign!**, not `==`) | `WHERE id = 2` |
| `<>` (or `!=`) | Not equal | `WHERE name <> 'Avi'` |
| `>` `<` `>=` `<=` | Comparison | `WHERE age >= 18` |
| `AND` | Both conditions | `WHERE age > 20 AND age < 30` |
| `OR` | At least one | `WHERE name = 'Dana' OR name = 'Noa'` |

### WHERE id — the most important

`id` is a Primary Key, unique, so **`WHERE id = 2` returns at most one row**. This is the fetch of `GET /students/{student_id}`. And the database finds it very fast, even out of millions of rows (because a Primary Key is indexed automatically).

### ORDER BY — sorting

```sql
SELECT name, age FROM students ORDER BY age;
SELECT name, age FROM students ORDER BY age DESC;
```

The default is ascending order (`ASC`). `DESC` = descending. **Without `ORDER BY`, the order is not guaranteed**, so if the order matters, ask for it explicitly.

{{< box "analogy" "Analogy: a librarian" >}}
`SELECT name, age FROM students` = "bring me the name and the age of all the students". `WHERE age > 23` = "only those over 23". `ORDER BY age` = "arranged by age". `WHERE id = 2` = "file number 2". You say **what**, and **how** to find it is up to the librarian (declarative SQL).
{{< /box >}}

## Syntax

```sql
SELECT * FROM table_name;                                 -- all columns, all rows
SELECT col_a, col_b FROM table_name;                      -- chosen columns
SELECT col_a, col_b FROM table_name WHERE id = 3;         -- one row by primary key
SELECT col_a FROM table_name WHERE col_b > 10 AND col_c = 'x';
SELECT col_a FROM table_name ORDER BY col_b DESC;
```

`--` at the start of text in SQL = **a comment** (like `#` in Python).

## First code example

The table from the previous lesson:

```text
id | name | age | email
---+------+-----+--------------
 1 | Dana |  24 | dana@mail.com
 2 | Avi  |  31 | avi@mail.com
 3 | Noa  |  22 | noa@mail.com
```

Run in Database Client (in a `.sql` file: select → Run):

```sql
SELECT * FROM students;
```

**Result:**

```text
id | name | age | email
 1 | Dana |  24 | dana@mail.com
 2 | Avi  |  31 | avi@mail.com
 3 | Noa  |  22 | noa@mail.com
```

```sql
SELECT name, email FROM students;
```

```text
name | email
Dana | dana@mail.com
Avi  | avi@mail.com
Noa  | noa@mail.com
```

```sql
SELECT id, name, age, email FROM students WHERE id = 2;
```

```text
id | name | age | email
 2 | Avi  |  31 | avi@mail.com
```

## Breaking down the code line by line

| Query | What the database did |
|---|---|
| `SELECT * FROM students` | All the columns, all the rows. |
| `SELECT name, email FROM students` | All the rows, but only two columns, in the order we asked for. |
| `SELECT id, name, age, email ... WHERE id = 2` | Went over the rows (in practice, went straight to id 2 thanks to the Primary Key), and returned **only** the row where `id = 2`. |

**Notice:** in the third query we wrote the list of columns explicitly, and not `*`. This is the habit we will use in code: if a column is added to the table in the future (maybe with sensitive information, like a password), it will not "leak" by mistake into the API's responses.

## Another example

**Filtering, combined conditions, sorting, and an empty result.**

```sql
SELECT name, age FROM students WHERE age > 23;
```

```text
name | age
Dana |  24
Avi  |  31
```

```sql
SELECT name, age FROM students WHERE age > 20 AND age < 30 ORDER BY age;
```

```text
name | age
Noa  |  22
Dana |  24
```

```sql
SELECT name FROM students ORDER BY name;
```

```text
name
Avi
Dana
Noa
```

```sql
SELECT id, name FROM students WHERE id = 99;
```

```text
(no rows)
```

**Breakdown:**

- **`age > 23`** — Dana (24) and Avi (31). Noa (22) did not pass the condition.
- **`AND` + `ORDER BY`** — first filtering (only between 20 and 30), and then sorting of what is left. **The order in the query is fixed:** `SELECT ... FROM ... WHERE ... ORDER BY ...`.
- **`ORDER BY name`** — alphabetical sorting of text.
- **`WHERE id = 99`** — there is no such row. **This is not an error!** The result is simply empty: 0 rows. In Python, `fetchone()` will return `None`, and `fetchall()` will return an empty list. **In the API this will become a 404.**

## Common mistake

**Mistake 1: `==` instead of `=`.**

```sql
SELECT * FROM students WHERE id == 2;
```

```text
1064 (42000): You have an error in your SQL syntax; ... near '== 2' at line 1
```

**In SQL, comparison is a single `=`.** (And that is the opposite of Python, where `=` is assignment!)

**Mistake 2: a string without quotes.**

```sql
SELECT * FROM students WHERE name = Avi;
```

```text
1054 (42S22): Unknown column 'Avi' in 'where clause'
```

Without quotes, SQL thinks `Avi` is **the name of a column** (exactly like `print(Hello)` in Python looks for a variable). → `WHERE name = 'Avi'`.

**Mistake 3: a wrong order of the parts.**

```sql
SELECT name FROM students ORDER BY age WHERE age > 20;
```

```text
1064 (42000): You have an error in your SQL syntax; ... near 'WHERE age > 20' at line 1
```

The order: `SELECT` → `FROM` → `WHERE` → `ORDER BY`.

**Mistake 4: treating an empty result as an error, or the opposite.** `WHERE id = 99` that found nothing returns **0 rows**, without an Exception. Our code is the one that has to check ("not found") and return 404. If you don't check, `None[0]` will crash.

**Mistake 5: assuming text comparison is case-sensitive.** In MySQL, by default, `WHERE name = 'avi'` **does** find `Avi` — the comparison does **not** distinguish between capital and small letters. It is convenient for searching, but it has a price: with `UNIQUE` on `email`, the database will refuse to save `Dana@mail.com` if `dana@mail.com` already exists. (And this is different from Python, where `"avi" == "Avi"` is `False`!)

<!-- exercises:start — generated by scripts/intro6/sync_site_exercises.py; edit scripts/intro6/exercises/ -->
## Practice

The exercises use only what was taught in this lesson and the lessons before it. Try on your own first, and only then open the solution.

### Exercise 1: A basic SELECT

In all the exercises in this lesson, first create the table and the data:

```py
import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL, age INT NOT NULL, city VARCHAR(50))""")
cursor.execute("""
INSERT INTO students (name, age, city) VALUES
    ('Dana', 24, 'Haifa'), ('Avi', 31, 'Tel Aviv'),
    ('Noa', 22, 'Haifa'), ('Tom', 28, NULL)""")
connection.commit()
```

Write a query that returns the name and the age of **all** the students, and print every row.

**Expected output:**

```text
('Dana', 24)
('Avi', 31)
('Noa', 22)
('Tom', 28)
```

{{< solution >}}
```python
import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL, age INT NOT NULL, city VARCHAR(50))""")
cursor.execute("""
INSERT INTO students (name, age, city) VALUES
    ('Dana', 24, 'Haifa'), ('Avi', 31, 'Tel Aviv'),
    ('Noa', 22, 'Haifa'), ('Tom', 28, NULL)""")
connection.commit()

cursor.execute("SELECT name, age FROM students")
for row in cursor.fetchall():
    print(row)
connection.close()
```

```text
('Dana', 24)
('Avi', 31)
('Noa', 22)
('Tom', 28)
```

`SELECT name, age` — only the columns you need. `SELECT *` returns all of them.
{{< /solution >}}

### Exercise 2: WHERE by id

With the same data, fetch the student whose `id` is 2, and the student whose `id` is 99.

**Expected output:**

```text
(2, 'Avi', 31, 'Tel Aviv')
None
```

{{< solution >}}
```python
import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL, age INT NOT NULL, city VARCHAR(50))""")
cursor.execute("""
INSERT INTO students (name, age, city) VALUES
    ('Dana', 24, 'Haifa'), ('Avi', 31, 'Tel Aviv'),
    ('Noa', 22, 'Haifa'), ('Tom', 28, NULL)""")
connection.commit()

cursor.execute("SELECT * FROM students WHERE id = 2")
print(cursor.fetchone())
cursor.execute("SELECT * FROM students WHERE id = 99")
print(cursor.fetchone())
connection.close()
```

```text
(2, 'Avi', 31, 'Tel Aviv')
None
```

**An empty result is not an error**: `fetchone()` returns `None` when there is no such row.
{{< /solution >}}

### Exercise 3: Filtering and sorting

With the same data: fetch the names of the students from Haifa under age 30, and all the students sorted by age from the oldest to the youngest.

**Expected output:**

```text
[('Dana',), ('Noa',)]
[('Avi', 31), ('Tom', 28), ('Dana', 24), ('Noa', 22)]
```

{{< solution >}}
```python
import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL, age INT NOT NULL, city VARCHAR(50))""")
cursor.execute("""
INSERT INTO students (name, age, city) VALUES
    ('Dana', 24, 'Haifa'), ('Avi', 31, 'Tel Aviv'),
    ('Noa', 22, 'Haifa'), ('Tom', 28, NULL)""")
connection.commit()

cursor.execute("SELECT name FROM students WHERE city = 'Haifa' AND age < 30")
print(cursor.fetchall())
cursor.execute("SELECT name, age FROM students ORDER BY age DESC")
print(cursor.fetchall())
connection.close()
```

```text
[('Dana',), ('Noa',)]
[('Avi', 31), ('Tom', 28), ('Dana', 24), ('Noa', 22)]
```

Every row is a tuple — even when there is one column: `('Dana',)`.
{{< /solution >}}

### Exercise 4: NULL

With the same data: which student has no city? Write two queries — one with `city = NULL` and one with `city IS NULL` — and explain the difference.

**Expected output:**

```text
[]
[('Tom',)]
```

{{< solution >}}
```python
import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL, age INT NOT NULL, city VARCHAR(50))""")
cursor.execute("""
INSERT INTO students (name, age, city) VALUES
    ('Dana', 24, 'Haifa'), ('Avi', 31, 'Tel Aviv'),
    ('Noa', 22, 'Haifa'), ('Tom', 28, NULL)""")
connection.commit()

cursor.execute("SELECT name FROM students WHERE city = NULL")
print(cursor.fetchall())
cursor.execute("SELECT name FROM students WHERE city IS NULL")
print(cursor.fetchall())
connection.close()
```

```text
[]
[('Tom',)]
```

`NULL` means "no value", so `city = NULL` is never true — not even for Tom. You check a missing value **only** with `IS NULL`.
{{< /solution >}}

### Exercise 5: Queries on products

Write SQL queries on the table `products` (name, price, stock), and run them in Database Client:

1. The names of the products whose stock is below 5.
2. All the products sorted by price, from the cheapest to the most expensive — the name and the price.
3. All the products sorted by price, from the most expensive to the cheapest.

{{< solution >}}
```sql
SELECT name FROM products
WHERE stock < 5;

SELECT name, price FROM products
ORDER BY price;

SELECT name, price FROM products
ORDER BY price DESC;
```

- `ORDER BY` sorts from the smallest to the largest by default. `DESC` reverses the order.
{{< /solution >}}

<!-- exercises:end -->

## Summary

- **`SELECT cols FROM table`** — reading. `*` = all the columns, but in code it is better to list them explicitly.
- **`WHERE condition`** — only the rows that match. `=` (one sign), `<>`, `>`, `<`, `AND`, `OR`. Strings in `'...'`.
- **`WHERE id = 2`** — at most one row, by the Primary Key. This is `GET /students/{id}`.
- **`ORDER BY col [DESC]`** — sorting. Without ORDER BY the order is not guaranteed.
- The order of the parts: **SELECT → FROM → WHERE → ORDER BY**.
- **An empty result is not an error.** The code checks it (`None` / `[]`) and returns 404. For NULL, use `IS NULL`.

<!-- docs:start — generated by scripts/intro6/site_docs.py; edit the links there -->

## Official documentation

- [MySQL — SELECT](https://dev.mysql.com/doc/refman/8.4/en/select.html)
- [MySQL Tutorial — Selecting Particular Rows](https://dev.mysql.com/doc/refman/8.4/en/selecting-rows.html)

<!-- docs:end -->