Belajar SQL dan Database: Panduan Lengkap untuk Pemula

Apa Itu SQL dan Database?

SQL (Structured Query Language) adalah bahasa untuk mengelola dan memanipulasi data di relational database. Database adalah sistem penyimpanan data yang terstruktur. SQL dan database adalah skill fundamental bagi setiap developer, data analyst, dan data scientist.

Mengapa SQL Wajib Dipelajari?

• Universal – Digunakan di hampir semua aplikasi
• Data is Everything – Semua aplikasi butuh data
• High Demand – Lowongan SQL/database developer banyak
• Foundation – Prerequisite untuk data science, backend dev
• Transferable – Skill SQL berlaku di semua database (MySQL, PostgreSQL, SQL Server)

Roadmap Belajar SQL & Database

Level 1: Beginner (Dasar SQL)

• SELECT – Mengambil data dari tabel
• WHERE – Filter data
• ORDER BY – Mengurutkan data
• LIMIT/OFFSET – Membatasi hasil
• INSERT – Menambah data
• UPDATE – Mengubah data
• DELETE – Menghapus data
• CREATE TABLE – Membuat tabel
• Data Types – INT, VARCHAR, DECIMAL, DATE, BOOLEAN, TEXT

Level 2: Intermediate

• JOIN – INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF
• GROUP BY – Aggregasi data
• HAVING – Filter hasil agregasi
• Subquery – Query di dalam query
• Aggregate Functions – COUNT, SUM, AVG, MIN, MAX
• String Functions – CONCAT, SUBSTRING, UPPER, LOWER, TRIM
• Date Functions – NOW, DATE, YEAR, MONTH, DATEDIFF
• NULL Handling – IS NULL, IS NOT NULL, COALESCE, IFNULL

Level 3: Advanced

• Indexes – B-Tree, Hash, Composite, Covering
• Views – Virtual tables
• Stored Procedures – Reusable SQL code
• Triggers – Auto-execute on events
• Transactions – BEGIN, COMMIT, ROLLBACK, ACID
• Window Functions – ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD
• CTE – Common Table Expressions (WITH clause)

Level 4: Expert

• Query Optimization – EXPLAIN, index tuning, query rewrite
• Database Design – Normalization, ERD, schema design
• Replication – Master-slave, master-master
• Sharding – Horizontal partitioning
• Backup & Recovery – mysqldump, pg_dump, point-in-time recovery
• Security – User management, grants, SQL injection prevention

Contoh Kode SQL

-- Membuat tabel
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nama VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    umur INT,
    kota VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total DECIMAL(15,2) NOT NULL,
    status ENUM('pending','paid','shipped','delivered') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- INSERT
INSERT INTO users (nama, email, umur, kota) VALUES
('Budi', 'budi@mail.com', 25, 'Jakarta'),
('Ani', 'ani@mail.com', 22, 'Bandung'),
('Cici', 'cici@mail.com', 28, 'Surabaya');

-- SELECT dengan WHERE dan ORDER BY
SELECT nama, email, kota FROM users 
WHERE umur >= 23 
ORDER BY nama ASC 
LIMIT 10;

-- JOIN
SELECT u.nama, o.total, o.status
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid';

-- GROUP BY dengan agregasi
SELECT kota, COUNT(*) as jumlah, AVG(umur) as rata_umur
FROM users
GROUP BY kota
HAVING COUNT(*) > 1
ORDER BY jumlah DESC;

-- Subquery
SELECT nama, umur FROM users
WHERE umur > (SELECT AVG(umur) FROM users);

-- Window Function
SELECT nama, umur, kota,
    ROW_NUMBER() OVER (PARTITION BY kota ORDER BY umur DESC) as rank_umur,
    AVG(umur) OVER (PARTITION BY kota) as avg_umur_kota
FROM users;

-- CTE (Common Table Expression)
WITH user_orders AS (
    SELECT u.id, u.nama, COUNT(o.id) as total_orders, SUM(o.total) as total_spent
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.nama
)
SELECT * FROM user_orders WHERE total_orders > 0 ORDER BY total_spent DESC;

-- UPDATE
UPDATE users SET kota = 'Jakarta' WHERE id = 1;

-- DELETE
DELETE FROM users WHERE id = 3;

-- Transaction
START TRANSACTION;
UPDATE users SET umur = umur + 1 WHERE id = 1;
INSERT INTO orders (user_id, total) VALUES (1, 500000);
COMMIT;

-- Index
CREATE INDEX idx_users_kota ON users(kota);
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Tips Belajar SQL

1. Praktik dengan data nyata – Gunakan sample database (Sakila, Northwind)
2. Pahami JOIN – Ini konsep paling penting
3. Gunakan EXPLAIN – Untuk optimasi query
4. Pelajari normalisasi – Database design yang baik
5. Latihan di platform – SQLZoo, LeetCode, HackerRank
6. Baca dokumentasi – MySQL docs, PostgreSQL docs

Tools yang Dibutuhkan

Kesimpulan

SQL dan database adalah skill fundamental bagi setiap developer. Dengan menguasai query, join, indexing, dan optimization, Anda bisa bekerja dengan data secara efisien. Mulai dari dasar SELECT, dan terus belajar!

“SQL is the lingua franca of data.” – Data Community