Lecture 2: SQL Language & Data Definition (DDL)

Speaking the Language of Data: Creating Structure

🎓 Concept Overview

  • Declarative Language: You tell SQL what result you want, not the steps to get there.
  • DDL vs DML: The difference between building the house (Structure) and moving the furniture (Data).
  • Strict Typing: Databases are rigid to ensure data integrity.
  • Constraints: Rules that prevent bad data from entering your system.

2.1 SQL Language Categories

SQL is not just one thing; it's a collection of sub-languages, each with a specific purpose.

Category Full Name Commands Purpose
DDL Data Definition Language CREATE, ALTER, DROP, TRUNCATE Defines the structure (schema) of the database.
DML Data Manipulation Language INSERT, UPDATE, DELETE Manages the data inside the tables.
DQL Data Query Language SELECT Retrieves data from the database.
DCL Data Control Language GRANT, REVOKE Controls access and permissions.
TCL Transaction Control Language COMMIT, ROLLBACK, SAVEPOINT Manages transaction integrity.

2.2 Managing Databases (DDL)

Everything starts with a database container.

2.2.1 Creating a Database

When creating a database, always specify the character set to avoid emoji issues later.

-- Simple creation
CREATE DATABASE my_app;

-- Professional creation (Best Practice)
CREATE DATABASE IF NOT EXISTS my_app 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- Select the database to use
USE my_app;

utf8mb4 vs utf8

Always use utf8mb4. MySQL's utf8 is a "fake" UTF-8 that only supports 3 bytes per character, meaning it cannot store emojis (which require 4 bytes). utf8mb4 is the true standard.

2.3 Data Types: The Containers

Choosing the right data type is critical for performance and data integrity.

2.3.1 Numeric Types

Type Storage Range / Behavior Best For
TINYINT 1 byte -128 to 127 Small flags, status codes.
INT 4 bytes -2 Billion to +2 Billion Standard IDs, counts.
BOOLEAN 1 byte 0 (False) or 1 (True) Specific to MySQL: It is just an alias for TINYINT(1).
BIGINT 8 bytes ~9 Quintillion Global IDs (Twitter Snowflakes), Scientific data.
FLOAT 4 bytes Approximate (Floating Point) Scientific calculations where slight precision loss is okay.
DECIMAL(M,D) Variable Exact Fixed Point Money, Financial data. Never use FLOAT for money!

2.3.2 String Types

Type Description Performance Use Case
CHAR(n) Fixed length. Pads with spaces. Very Fast Country Codes (US, UK), Hashes.
VARCHAR(n) Variable length. Good Names, Emails, Usernames.
TEXT Huge variable length (64KB+). Slow (stored off-page) Blog posts, Comments.
ENUM Restricted list of strings. Compact (Saved as Int) 'Small', 'Medium', 'Large'.
BLOB Binary Large Object. Slow Images, Files (Though S3 is better for this).

2.3.3 Date & Time

Type Format Timezone Aware? Use Case
DATE YYYY-MM-DD No Birthdays.
DATETIME YYYY-MM-DD HH:MM:SS No (Stored as is) Event start times, fixed historical events.
TIMESTAMP Unix Timestamp Yes (Converts to UTC) created_at, updated_at records.

2.4 Creating Tables (DDL)

The CREATE TABLE command defines a new entity. It requires a name and a set of column definitions.

CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY -- The Identity email VARCHAR(100) NOT NULL UNIQUE -- The Rules joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- The Automation ); 
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    age TINYINT CHECK (age >= 18),
    bio TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2.5 Constraints: The Rules of the Game

Constraints enforce data integrity. They are the bouncers of your database club.

Constraint Purpose Example
PRIMARY KEY Uniquely identifies each record. Cannot be NULL. user_id (ID Card)
FOREIGN KEY Links to a Primary Key in another table. Enforces relationships. author_id in Books table.
UNIQUE Ensures all values in column are distinct. email, username.
NOT NULL Prevents empty values. password.
DEFAULT Provides a value if none is specified. created_at = NOW().
CHECK Validates data against a condition. age > 18.
INDEX Not strictly a constraint, but a structure for speed. Speed up searches on lastname.

2.5.1 Visualizing Foreign Keys

USERS (Parent) id: 1 (PK) name: 'John' ORDERS (Child) order_id: 101 user_id: 1 (FK) References

2.6 Altering Tables (ALTER)

Requirements change. ALTER TABLE allows you to modify the structure of an existing table.

-- 1. Add a new column
ALTER TABLE users ADD COLUMN phone_number VARCHAR(20);

-- 2. Delete a column (Dangerous!)
ALTER TABLE users DROP COLUMN bio;

-- 3. Modify a column (Change type or constraint)
ALTER TABLE users MODIFY COLUMN username VARCHAR(100) NOT NULL;

-- 4. Rename a column
ALTER TABLE users RENAME COLUMN is_active TO status;

2.7 The Nuke Options: DELETE vs TRUNCATE vs DROP

There are three ways to remove data, and mixing them up can be catastrophic.

Command Action Speed Can Rollback?
DELETE Removes rows one by one. Can use WHERE. Slow ✅ Yes
TRUNCATE Removes ALL rows. Resets Auto Increment. Fast ❌ No (Usually)
DROP Deletes the table structure and data. Instant ❌ No

2.8 Teaching Notes

🎓 Instructor Guide

Key Analogies for Students:

  • Data Types = Containers:
    • TINYINT is a shot glass (small, efficient).
    • INT is a water bottle (standard).
    • BIGINT is an oil tanker (huge capacity).
    • Don't use an oil tanker to hold a shot of water!
  • Primary Key: Like a Social Security Number or Student ID. It never changes and uniquely identifies you.
  • Foreign Key: Like a hyperlink on a webpage. Does clicking the link take you to a real page? If yes, integrity is maintained.
  • NULL: It is not "Zero" and it is not "Empty String". It is void or unknown. NULL + 1 = NULL.
  • Drop vs Truncate: DROP burns down the house. TRUNCATE leaves the house standing but removes all furniture.

2.9 Debugging Workshop

Test your understanding by fixing these broken queries.

1. The NULL Trap

I tried to insert an author, but I only know their birth year.

INSERT INTO authors (birth_year) VALUES (1980);

2. Duplicate Keys

I want to add a second author with ID 1.

INSERT INTO authors (author_id, name) VALUES (1, 'New Author');

3. Too Long

My ISBN won't fit!

INSERT INTO books (isbn) VALUES ('978-1234567890-EXTRA');

4. The Ghost Author

I'm trying to add a book for an author that isn't in the database yet.

INSERT INTO books (title, author_id) VALUES ('Ghost Book', 999);

2.10 Practice Tasks

Task 1: The E-Commerce Weaver

Scenario: You need to design a products table. It needs:

  • A unique ID
  • A name (up to 150 chars)
  • A price (exact value, suitable for money)
  • A stock count (cannot be negative)
  • A description (could be very long)

Activity: Write the CREATE TABLE statement.

Show Answer 
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL CHECK (stock >= 0),
    description TEXT
);

Task 2: The Time Traveler

Question: You are building an app for a global team. You need to store when a task was created. If you use DATETIME, a user in Tokyo might see a different "concept" of time than a user in New York if the server is in London. What data type solves this?

Show Answer

TIMESTAMP. It converts the time to UTC for storage and converts it back to the local timezone of the client upon retrieval.

2.11 Quiz

📝 Lecture 2 Quiz

Select the correct answer for each question. (8 Questions)

1. Which command completely removes a table and its structure?
2. Which data type is best for storing a user's age?
3. Which constraint ensures a column cannot have duplicate values?
4. Which command is DDL (Data Definition Language)?
5. Why is DECIMAL preferred over FLOAT for money?
6. What happens if you try to insert NULL into a Primary Key column?
7. How do you add a column to an existing table?
8. Which DML command modifies existing rows?