SQL Basics, DDL, and DML

Teachers

Core program

What is SQL?

SQL (Structured Query Language) is the standard language for talking to databases.

It is declarative: You tell the database what you want, not how to get it.
It usually ignores capital letters (SELECT is the same as select). However, we usually write keywords in UPPERCASE to make them readable, or at least Capitalise them.
Statements usually end with a semicolon ;, and if you are making queries not adding this will throw an error.

Categories of SQL Commands

1. DDL (Data Definition Language)

These commands define the structure (Schema) of the database. Think of this as building the house.

CREATE: Creates a new table, view, or other object.
ALTER: Changes an existing object.
DROP: Deletes an entire object (table, view, etc.).

2. DML (Data Manipulation Language)

These commands handle the actual data. Think of this as moving furniture and people into the house.

INSERT: Adds new rows.
UPDATE: Changes existing rows.
DELETE: Removes rows.
SELECT: Gets data.

Creating Tables (DDL)

Here is how we create the tables we discussed in chapter 2 using SQLite syntax.

-- One-to-many example: One author can write many books.

-- Create the Authors table ("one" side)
CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT UNIQUE
);

-- Create the Books table ("many" side)
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    published_year INTEGER,

    -- Foreign key to the author who wrote the book
    author_id INTEGER NOT NULL,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

-- Optional: index to speed up looking up an author's books
CREATE INDEX idx_books_author_id ON books(author_id);

Deleting Tables

Warning: This deletes the table AND all data inside it permanently.

DROP TABLE books;
DROP TABLE authors;

Modifying Data (DML)

INSERT (Adding data)

-- Insert an author
INSERT INTO authors (first_name, last_name, email)
VALUES ('Ursula', 'Le Guin', '[email protected]');

-- Insert books for that author (assumes the author got id = 1)
INSERT INTO books (title, published_year, author_id)
VALUES
    ('A Wizard of Earthsea', 1968, 1),
    ('The Tombs of Atuan', 1970, 1);

UPDATE (Changing data)

ALWAYS use a WHERE clause. If you forget it, you will update every row in the table.

-- Update an author's email
UPDATE authors
SET email = '[email protected]'
WHERE id = 1;

-- Update a book's published year (GOOD: targets one row)
UPDATE books
SET published_year = 1969
WHERE id = 1;

-- BAD example: missing WHERE clause
UPDATE books
SET published_year = 0;

-- What just happened?
-- You updated EVERY row in the books table.
-- All books now have published_year = 0.
-- This is one of the most common (and most expensive) SQL mistakes.

DELETE (Removing data)

Again, ALWAYS use a WHERE clause, or you will wipe the table.

-- Delete a specific book
DELETE FROM books
WHERE id = 1;

-- Delete all books for a given author
DELETE FROM books
WHERE author_id = 1;

-- BAD example: missing WHERE clause
DELETE FROM books;

-- What just happened?
-- You deleted EVERY row from the books table.
-- The table still exists (schema is still there), but it's now empty.
-- This is why we always use a WHERE clause for UPDATE/DELETE.

The Basic Query: SELECT

The SELECT statement is how you read data. It does not change anything in the database.

-- Select all authors
SELECT * FROM authors;

-- Select all books
SELECT * FROM books;

-- Find all books written by a specific author (by author_id)
SELECT b.*
FROM books b
WHERE b.author_id = 1;

-- Filter books by published year (exact match)
-- This returns only rows where published_year equals exactly 1968
SELECT
    b.title,
    b.published_year
FROM books b
WHERE b.published_year = 1968;

--- Below we get fancy, but I, personally, find them very practical uses that I've used a lot

-- Filter books by author name using a case-insensitive wildcard match
-- 1) We join books to authors so we can filter on author name fields.
-- 2) We build a full name string.
-- 3) We use LOWER(...) so the match is case-insensitive.
-- 4) We use LIKE with % wildcards so a partial search like 'leguin' matches
--    e.g. 'Le Guin', 'LE GUIN', etc.
SELECT
    b.title,
    b.published_year,
    a.first_name || ' ' || a.last_name AS author
FROM books b
JOIN authors a ON a.id = b.author_id
WHERE LOWER(a.first_name || ' ' || a.last_name) LIKE '%leguin%';

-- Join to show books with their author's name (no filtering)
SELECT
    b.title,
    b.published_year,
    a.first_name || ' ' || a.last_name AS author
FROM books b
JOIN authors a ON a.id = b.author_id;

The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0 *https://hackyourfuture.net/*

CC BY-NC-SA 4.0 Icons

Built with ❤️ by the HackYourFuture community · Thank you, contributors

Found a mistake or have a suggestion? Let us know in the feedback form.