Write an SQL script to query or modify data (DML) which are stored in (at most two) database tables

8.3 Data Definition Language (DDL) & Data Manipulation Language (DML)

What is DDL? 🛠️

DDL (Data Definition Language) is like the blueprint of a building. It tells the database what tables exist, what columns they have, and what rules apply (like keys and types).

What is DML? 📊

DML (Data Manipulation Language) is like moving furniture inside that building. It lets you add, change, delete, and retrieve data.

DDL Example: Creating Two Tables

-- Create a table for students
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Age INT
);

-- Create a table for courses
CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100),
    Credits INT
);

DML Example: Inserting Data

INSERT INTO Students (StudentID, FirstName, LastName, Age) VALUES
    (1, 'Alice', 'Smith', 16),
    (2, 'Bob', 'Jones', 17);

INSERT INTO Courses (CourseID, CourseName, Credits) VALUES
    (101, 'Mathematics', 4),
    (102, 'English', 3);

DML Example: Querying Data

-- Show all students
SELECT * FROM Students;

-- Show all courses
SELECT * FROM Courses;

-- Find students older than 16
SELECT FirstName, LastName
FROM Students
WHERE Age > 16;

DML Example: Updating and Deleting

-- Update a student's age
UPDATE Students
SET Age = 18
WHERE StudentID = 1;

-- Delete a course
DELETE FROM Courses
WHERE CourseID = 102;

Analogy: The Library 📚

Think of the database as a library. DDL is the librarian who sets up new shelves (tables) and decides what kind of books (data types) go on each shelf. DML is the librarian who moves books around, adds new titles, removes old ones, and helps readers find what they need.

Quick Reference Table

Your Turn! 🚀

1. Create a new table called Enrollments that links students to courses.
2. Insert at least two rows into Enrollments.
3. Write a query to list all students and the courses they are enrolled in.