SQL Queries Introduction

🗄️ Basic SQL Queries Guide

In this section, we will go over basic SQL queries using a simple Todo application as our example.

SQL keywords are case-insensitive, so SELECT * FROM table is the same as select * from TABLE

➕ Inserting Data Into a Database Table (TODOS)

Let’s begin by learning how to insert data into a database table.

Imagine we have a Todos table in our database. Each row in this table represents a single todo item.

📊 Todos Record Structure

A single Todo record in our database looks like the table below:

🧩 What Does a Todo Contain?

Each Todo record includes:

• ID – A primary key used to uniquely identify each record

• Title – The name or title of the todo

• Description – Additional details about the todo

• Priority – Indicates how important the todo is

• Complete – Shows whether the todo is completed or not

• Owner (FK) – A foreign key that links the todo to a specific user (added later)

🛠️ How Do We Add Data to a Todo? (SQL)

Before inserting data, we must decide which fields we want to populate.

INSERT INTO todos (title, description, priority, complete)
VALUES ('Go to store', 'To pickup eggs', 4, False);

Notice that we do not include the ID column. This is intentional.

The database automatically handles the ID using auto-increment, ensuring that each record has a unique primary key and preventing accidental duplicates.

Now, let’s provide values for the fields:

When this command runs, a new record is created in the database.

In SQL:

• False is stored as 0

• True is stored as 1

📋 Current Database State

➕ Adding More Records

INSERT INTO todos (title, description, priority, complete)
VALUES ('Haircut', 'Need to get length 1mm', 3, False);

The database automatically increments the ID for us, so we never have to manage it manually.

Now we have two records with IDs 1 and 2.

👀 Let’s add four more records.

📊 Todos Table With Multiple Records

🔍 SELECT SQL Queries

Now that we have data in our database, let’s learn how to retrieve (read) data.

To retrieve data, we use the SELECT statement.

SELECT * FROM todos;

The asterisk (*) means select all columns and all rows.

This query returns every record in the todos table.

🎯 Selecting Specific Columns

What if we only want the titles?

SELECT title FROM todos;

This query returns only the title column.

Title

Go to store

Haircut

Feed dog

Water Plant

Learn something New

Shower

📝 Selecting Descriptions Only

SELECT description FROM todos;

This query retrieves only the description column.

Description

To pickup eggs.

Need to get length 1mm.

Make sure to use new food brand.

inside and Outside.

Learn to program.

You have not showered in 14 days.

Selecting a single column is useful, but real-world applications usually require multiple fields.

📌 Selecting Multiple Columns

SELECT title, description, priority FROM todos;

This retrieves the title, description, and priority for all records.

🎯 WHERE SQL Queries

The WHERE clause filters records based on specific conditions.

Earlier, we used:

SELECT * FROM todos;

This returned all records.

Now, let’s add a condition.

SELECT * FROM todos WHERE priority=5;

This query returns only records with a priority of 5.

🔎 Filtering by Title

SELECT * FROM todos WHERE title='Feed dog';

🆔 Filtering by ID (Best Practice)

The most common use case for WHERE is identifying a record by its primary key.

SELECT * FROM todos WHERE id=2;

🔄 UPDATE SQL Queries

The UPDATE statement is used to modify existing records.

UPDATE todos SET complete=True WHERE id=5;

It means Update All rows & columns to now have complete = True Where id = 5

This updates the record with an ID of 5.

• 1 = True

• 0 = False

⚠️ Updating Without an ID

UPDATE todos SET complete=True WHERE title='Learn Something new';

This works, but it can be dangerous.

Titles are not guaranteed to be unique, so this query could update multiple records unintentionally.

Always prefer using the primary key when updating records.

❌ DELETE SQL Queries

The safest way to delete a record is by using the primary key.

DELETE FROM todos WHERE id=5;

After executing this query, the record with ID 5 is removed.

⚠️ Dangerous Delete Example

DELETE FROM todos WHERE complete=0;

This would delete almost the entire table.

This is why deleting records by primary key is strongly recommended.

🧠 Final Notes

We’ve covered the most common SQL commands used in CRUD operations:

• CREATE – INSERT

• READ – SELECT

• UPDATE – UPDATE

• DELETE – DELETE

Fortunately, tools like SQLAlchemy handle most of these operations at the application level, allowing us to work with Python objects instead of writing raw SQL for every database interaction.