SQL for Absolute Beginners

We have heard it so many times now, that data is the new currency in the day and age that we live in. We leave lots of data around on the internet, with the people we follow, our orders on shopping platforms, etc. There has therefore never been a better time to learn how to work with data; more so, to gain insights from this data. Today, we delve into( well more like crack the surface) with SQL. Let’s get started shall we!

SQL (Structured Query Language) is how we talk to databases- and not just any databases; relational databases. We have two types of databases: relational and non-relational databases.

Relational databases are structured, organizing data in tables, rows and columns while non-relational databases store data in various schema such as key-value pairs(hello dictionaries :)), graphs, documents, etc. Think of how your bank stores data in regards to relational databases and social media data when it comes to non-relational databases.

In the same breath we talk about SQL, another concept takes the stage, and that is DBMS; Database Management System. So what’s the difference.

A DBMS (Database Management System) stores, organizes, and secures that data so we can add, find, update, and delete it reliably, while SQL is the language that we use to interact with that system and manipulate the data that it has.

We’ll use SQLite—a lightweight DBMS that saves everything to a single file—plus a few lines of Python. By the end you’ll have a working database with real queries you can run today

1) Core ideas in 60 seconds

• Table → like a spreadsheet.

• Row / Tuple → one record (e.g., a single client).

• Column / Attribute → one field (e.g., Name, Email).

• Data types → tell the database what lives in a column (e.g., INTEGER, TEXT, REAL).

• Constraints → rules that protect data quality and relationships.

  Why constraints are important:

  1. Data accuracy,

  2. Faster queries (indexes behind the scenes),

  3. Reliable relationships between tables

• PRIMARY KEY (unique ID)

• FOREIGN KEY (links rows across tables)

2. Set up SQ LITE

import sqlite3
# 1) Create/connect to a database. I have named our database test
fileconnection = sqlite3.connect("test.db")
# 2) Create a cursor to run SQL commands
cursor = connection.cursor()

3) Create your first table

Let’s create a clients table.

client_table_query = """
CREATE TABLE IF NOT EXISTS clients (
ClientId INTEGER PRIMARY KEY, -- unique row id
Name TEXT NOT NULL, -- can't be left empty
Address TEXT,
Phone TEXT, -- store as TEXT
Email TEXT,
Age INTEGER
);
"""
cursor.execute(client_table_query)
connection.commit()

4) Insert data

insert_data_query = """
INSERT INTO clients (
    ClientId, 
    Name, 
    Address, 
    Phone, 
    Email, 
    Age)
VALUES
    (32192456, 'Ben Luka', 'Willson Rd 53', '3286902345', 'b.mule@gmail.com', 28),
    (32192459, 'Kay Soro', 'Corby Drive 5', '3288987659', 'kaysoro@o-l.nl', 34);
"""
cursor.execute(insert_data_query)
connection.commit()

5) Read (SELECT) your data

# All clients
cursor.execute("SELECT * FROM clients;")
print(cursor.fetchall())# Only names and emailscursor.execute
("SELECT Name, Email FROM clients;")
print(cursor.fetchall())# Filter + sort
cursor.execute("""SELECT Name, AgeFROM clientsWHERE Age >= 30ORDER BY Age DESC;""")
print(cursor.fetchall())

6) Update and Delete

# Update
cursor.execute("""UPDATE clientsSET Address = 'Willson Rd 55'WHERE ClientId = 32192456;""")
# Delete
cursor.execute("""
DELETE FROM clients
WHERE ClientId = 32192459;
""")

connection.commit()

7) Relate tables with FOREIGN KEYs

Relationships let you connect records across tables.

Example: each order belongs to a client. If we delete a client, we might want their orders deleted automatically— cue cascade constraint to help with this.

What are other rules we can set for deletions you ask? Great question:

• CASCADE- repeating for emphasis dear reader: this deletes the child row automatically. This is used when the child has no meaning without the parent, eg, order item data without the order

• SET NULL- This sets the foreign key column to NULL in the child.

• SET DEFAULT- this sets the foreign key column to its DEFAULT value. The column must have a DEFAULT that’s a valid referenced key.

• RESTRICT: This blocks the deletion if any child exists

• NO ACTION- In most cases this behaves similar to the RESTRICT and the delete fails if children exist. The difference is that NO ACTION is a sort of deferred constraint that checks at the end of the statements while RESTRICT checks immediately.

# Turn on foreign keys in SQLite
cursor.execute("PRAGMA foreign_keys = ON;")
# Orders table references clients(ClientId)
cursor.execute("""
    CREATE TABLE IF NOT EXISTS orders (
        OrderId INTEGER PRIMARY KEY,
        ClientId INTEGER NOT NULL,
        Item TEXT NOT NULL,
        Amount REAL NOT NULL,
        CreatedAt TEXT DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (ClientId) REFERENCES clients(ClientId)
            ON DELETE CASCADE -- remove a client's orders if the client is deleted
);""")
connection.commit()

8) Common beginner mistakes (and fixes)

• Forgetting to commit(): your changes won’t persist.

• Using numbers for phone: store as TEXT.

• Missing indexes on big lookups: primary keys are indexed automatically; consider indexing frequently searched columns later.

• No foreign keys: always define relationships- enable them in SQLite with PRAGMA foreign_keys = ON;.

9) Cheat-sheet (copy, paste, tweak)

Create table

CREATE TABLE IF NOT EXISTS clients (
    ClientId INTEGER PRIMARY KEY,
    Name TEXT NOT NULL,
    Address TEXT,
    Phone TEXT,
    Email TEXT,
    Age INTEGER);

Verify tables

SELECT name FROM sqlite_master WHERE type='table';

Insert

INSERT INTO clients (Name, Address, Phone, Email, Age)
VALUES ('New Person', 'Main St 1', '5551234', 'new@ex.com', 27);

Select

SELECT Name, Email FROM clients WHERE Age >= 30 ORDER BY Name;

Update

UPDATE clients SET Address = 'New Address' WHERE ClientId = 1;

Delete

DELETE FROM clients WHERE ClientId = 1;

Foreign key

CREATE TABLE orders (
OrderId INTEGER PRIMARY KEY,
ClientId INTEGER NOT NULL,
Item TEXT NOT NULL,
Amount REAL NOT NULL,
FOREIGN KEY (ClientId) REFERENCES clients(ClientId) ON DELETE CASCADE);

Okay, now that the 'theoretical stuff is out of the way, let’s get our hands dirty by practising what we have learned with real data and see just how well we do!

See you on the next article data warrior!