Basic SQL Commands: Perintah SQL Dasar 🛠️

Halo SQL Explorers! 👋 Di materi ini kita bakal belajar perintah-perintah dasar SQL yang bakal sering kamu pake. Kita bakal mulai dari yang paling simple dulu!

Data yang Kita Pake 📊

Kita bakal pake database toko online sederhana. Pertama, kita bikin tabel dulu:

-- Bikin tabel categories
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nama VARCHAR(100) NOT NULL
);

-- Bikin tabel products
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nama VARCHAR(100) NOT NULL,
    harga DECIMAL(15,2) NOT NULL,
    stok INT NOT NULL DEFAULT 0,
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

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

-- Bikin tabel orders
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    tanggal DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

1. SELECT: Ambil Data 🔍

SELECT itu perintah buat ambil data dari tabel. Ini yang paling sering dipake!

-- Ambil semua kolom
SELECT * FROM products;

-- Ambil kolom tertentu
SELECT nama, harga FROM products;

-- Pake alias buat nama kolom
SELECT 
    nama AS nama_produk,
    harga AS harga_satuan
FROM products;

2. INSERT: Tambah Data ➕

INSERT buat nambahin data baru ke tabel.

-- Tambah satu data
INSERT INTO categories (nama) 
VALUES ('Elektronik');

-- Tambah banyak data sekaligus
INSERT INTO categories (nama) VALUES 
    ('Fashion'),
    ('Makanan'),
    ('Minuman');

-- Tambah produk
INSERT INTO products (nama, harga, stok, category_id) VALUES
    ('Laptop Gaming', 15000000, 5, 1),
    ('Smartphone', 5000000, 10, 1),
    ('Kemeja Casual', 200000, 20, 2),
    ('Celana Jeans', 350000, 15, 2),
    ('Snack Pack', 50000, 100, 3),
    ('Minuman Soda', 15000, 150, 3);

-- Tambah customer
INSERT INTO customers (nama, email, kota) VALUES
    ('Budi', 'budi@email.com', 'Jakarta'),
    ('Ani', 'ani@email.com', 'Bandung'),
    ('Caca', 'caca@email.com', 'Surabaya'),
    ('Dodi', 'dodi@email.com', 'Jakarta');

-- Tambah order
INSERT INTO orders (customer_id, tanggal, status) VALUES
    (1, '2024-12-19', 'completed'),
    (1, '2024-12-20', 'pending'),
    (2, '2024-12-20', 'completed'),
    (3, '2024-12-20', 'pending');

3. UPDATE: Ubah Data 🔄

UPDATE buat ngubah data yang udah ada.

-- Update satu kolom
UPDATE products 
SET harga = 4800000 
WHERE nama = 'Smartphone';

-- Update banyak kolom
UPDATE products 
SET 
    harga = harga * 0.9,  -- Diskon 10%
    stok = stok - 1       -- Kurangi stok
WHERE category_id = 1;    -- Cuma untuk elektronik

-- Update status order
UPDATE orders 
SET status = 'completed' 
WHERE customer_id = 3;

4. DELETE: Hapus Data ❌

DELETE buat hapus data. Hati-hati pakenya!

-- Hapus produk yang stoknya 0
DELETE FROM products 
WHERE stok = 0;

-- Hapus order yang pending
DELETE FROM orders 
WHERE status = 'pending';

-- HATI-HATI: Ini bakal hapus SEMUA data!
DELETE FROM products;  -- Jangan dicoba ya! 😅

Tips Penting 💡

  1. Selalu Pake WHERE

    -- Bahaya! Update semua data
UPDATE products SET harga = 0;

-- Aman, cuma update yang sesuai kondisi
UPDATE products 
SET harga = 0 
WHERE id = 1;

  2. Backup Dulu Sebelum DELETE/UPDATE

    -- Bikin backup dulu
CREATE TABLE products_backup AS 
SELECT * FROM products;

-- Baru update/delete
UPDATE products SET ...

  3. Pake Transaction

    -- Mulai transaction
START TRANSACTION;

-- Operasi yang mau dilakukan
UPDATE products SET stok = stok - 1 WHERE id = 1;
INSERT INTO orders (customer_id, tanggal) VALUES (1, '2024-12-21');

-- Kalo aman, commit
COMMIT;

-- Kalo ada masalah, rollback
ROLLBACK;

Latihan 🎯

  1. Tambah Produk Baru

    INSERT INTO products (nama, harga, stok, category_id)
VALUES ('Monitor Gaming', 3000000, 8, 1);

  2. Update Harga

    -- Naikan harga produk elektronik 5%
UPDATE products 
SET harga = harga * 1.05
WHERE category_id = 1;

  3. Hapus Order Lama

    -- Hapus order sebelum 2024
DELETE FROM orders 
WHERE tanggal < '2024-01-01';

Preview Materi Selanjutnya 🎯

Di materi filtering kita bakal belajar:

  • Filter data pake WHERE
  • Operator perbandingan (>, <, =, dll)
  • Pattern matching pake LIKE
  • Filter berdasarkan tanggal
  • Dan masih banyak lagi! 🚀

Siap belajar cara nyaring data? Yuk lanjut ke materi filtering! 🎯

Fatkul Nur Koirudin