1. DDL (Data Definition Language) vs DML (Data Manipulation Language)
SQL dibagi menjadi dua kategori operasi utama: - **DDL**: Perintah untuk mendefinisikan/mengubah struktur tabel dan skema (misal: `CREATE TABLE`, `ALTER TABLE`, `DROP TABLE`). - **DML**: Perintah untuk membaca dan memanipulasi data di dalam baris tabel (misal: `SELECT`, `INSERT`, `UPDATE`, `DELETE`).
2. DDL: Perancangan Tabel dengan Tipe Data & Constraints
query.sql
sql
| 1 | -- Membuat tabel users dengan UUID primary key dan validasi constraint |
| 2 | CREATE TABLE users ( |
| 3 | id UUID PRIMARY KEY DEFAULT gen_random_uuid(), |
| 4 | email VARCHAR(255) UNIQUE NOT NULL, |
| 5 | full_name VARCHAR(100) NOT NULL, |
| 6 | age INTEGER CHECK (age >= 13), |
| 7 | created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP |
| 8 | ); |
| 9 | |
| 10 | -- Membuat tabel orders dengan Foreign Key (Relasi ke users) |
| 11 | CREATE TABLE orders ( |
| 12 | id UUID PRIMARY KEY DEFAULT gen_random_uuid(), |
| 13 | user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| 14 | total_amount NUMERIC(12, 2) NOT NULL, |
| 15 | status VARCHAR(20) DEFAULT 'pending', |
| 16 | created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP |
| 17 | ); |
Apa itu ON DELETE CASCADE?
Atribut `ON DELETE CASCADE` memastikan integritas referensial. Jika satu baris `users` dihapus, seluruh data `orders` yang dimiliki oleh user tersebut akan otomatis ikut terhapus secara bersih tanpa meninggalkan data sampah (orphan records).
3. DML: Manipulasi Data dalam Tabel
query.sql
sql
| 1 | -- 1. INSERT (Menambah baris baru) |
| 2 | INSERT INTO users (email, full_name, age) |
| 3 | VALUES ('budi@gmail.com', 'Budi Santoso', 24); |
| 4 | |
| 5 | -- 2. SELECT dengan Filtering & Sorting |
| 6 | SELECT id, full_name, email |
| 7 | FROM users |
| 8 | WHERE age >= 20 |
| 9 | ORDER BY created_at DESC |
| 10 | LIMIT 10; |
| 11 | |
| 12 | -- 3. UPDATE (Memperbarui data) |
| 13 | UPDATE users |
| 14 | SET full_name = 'Budi S. Pratama' |
| 15 | WHERE email = 'budi@gmail.com'; |
| 16 | |
| 17 | -- 4. DELETE (Menghapus data) |
| 18 | DELETE FROM users |
| 19 | WHERE email = 'budi@gmail.com'; |
4. Simulasi Query Evaluator di Terminal
example.js
javascript
| 1 | // Simulasi engine in-memory SQL sederhana |
| 2 | class InMemoryDatabase { |
| 3 | constructor() { |
| 4 | this.users = []; |
| 5 | } |
| 6 | |
| 7 | insertUser(email, name, age) { |
| 8 | if (this.users.find(u => u.email === email)) { |
| 9 | throw new Error(`Unique constraint violation: Email ${email} already exists`); |
| 10 | } |
| 11 | const newUser = { id: this.users.length + 1, email, name, age }; |
| 12 | this.users.push(newUser); |
| 13 | return newUser; |
| 14 | } |
| 15 | |
| 16 | query(filterFn) { |
| 17 | return this.users.filter(filterFn); |
| 18 | } |
| 19 | } |
| 20 | |
| 21 | const db = new InMemoryDatabase(); |
| 22 | db.insertUser('andi@dev.io', 'Andi', 22); |
| 23 | db.insertUser('siti@dev.io', 'Siti', 28); |
| 24 | |
| 25 | console.log("Hasil query age > 25:", db.query(u => u.age > 25)); |