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.
Statements usually end with a semicolon ;, and if you are making queries not adding this will throw and 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.

-- Create the Students table
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER
);

-- Create the Classes table
CREATE TABLE classes (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);

-- Create the Enrollments table (The Join Table)
CREATE TABLE enrollments (
    id INTEGER PRIMARY KEY,
    student_id INTEGER,
    class_id INTEGER,
    grade INTEGER,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (class_id) REFERENCES classes(id)
);

Deleting Tables

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

DROP TABLE students;

Modifying Data (DML)

INSERT (Adding data)

-- Insert a single row
INSERT INTO students (first_name, last_name, email, age)
VALUES ('Ahmed', 'Muraat', '[email protected]', 25);

-- Insert multiple rows (SQLite supports this)
INSERT INTO classes (title)
VALUES
    ('Intro to SQL'),
    ('Advanced Javascript'),
    ('React Basics');

UPDATE (Changing data)

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

-- Change Ahmed's age
UPDATE students
SET age = 26
WHERE id = 1;

-- Give everyone an 'A' grade (Dangerous! This changes ALL grades)
UPDATE enrollments SET grade = 100;

DELETE (Removing data)

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

-- Delete a specific student
DELETE FROM students
WHERE id = 1;

-- Delete all students older than 30
DELETE FROM students
WHERE age > 30;

The Basic Query: SELECT

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

-- Select all columns (*) from a table
SELECT * FROM students;

-- Select specific columns (Better for performance)
SELECT first_name, email FROM students;

-- Filter results with WHERE
SELECT * FROM students
WHERE age >= 21;

-- Multiple conditions
SELECT * FROM students
WHERE age >= 21 AND first_name = 'Layla';

The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0

CC BY-NC-SA 4.0 Icons

*https://hackyourfuture.net/*

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