Basic SQL: SELECT, WHERE by id and Simple Filtering

What will we learn?#

SELECT = the Read 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:

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#

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#

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.

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#

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.

Syntax#

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:

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):

SELECT * FROM students;

Result:

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

SELECT name, email FROM students;

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

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

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

Breaking down the code line by line#

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.

SELECT name, age FROM students WHERE age > 23;

name | age
Dana |  24
Avi  |  31

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

name | age
Noa  |  22
Dana |  24

SELECT name FROM students ORDER BY name;

name
Avi
Dana
Noa

SELECT id, name FROM students WHERE id = 99;

(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 =.

SELECT * FROM students WHERE id == 2;

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.

SELECT * FROM students WHERE name = Avi;

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.

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

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!)

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:

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:

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

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()

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

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:

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

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()

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

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:

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

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()

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

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:

[]
[('Tom',)]

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()

[]
[('Tom',)]

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.

SELECT name FROM products
WHERE stock < 5;

SELECT name, price FROM products
ORDER BY price;

SELECT name, price FROM products
ORDER BY price DESC;

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.

Official documentation#

• MySQL — SELECT
• MySQL Tutorial — Selecting Particular Rows