SQL - Introduction to DML Script by HadiMaster

-- Create the products table
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    description TEXT,
    category VARCHAR(50),
    price NUMERIC(10, 2),
    inventory_quantity INT
);

-- Insert data into the products table
INSERT INTO products (product_id, name, description, category, price, inventory_quantity) VALUES
(1, 'Laptop', '15.6" Intel Core i5, 8GB RAM, 512GB SSD', 'Electronics', 899.99, 50),
(2, 'Smartphone', '6.5" OLED Display, 128GB Storage, 12MP Camera', 'Electronics', 699.99, 100),
(3, 'Headphones', 'Wireless Over-Ear Headphones with ANC', 'Electronics', 199.99, 75),
(4, 'Smartwatch', 'Fitness Tracker with Heart Rate Monitor', 'Electronics', 149.99, 30),
(5, 'Backpack', 'Waterproof Laptop Backpack with USB Charging', 'Fashion', 49.99, 200),
(6, 'Sneakers', 'Men''s Running Shoes with Breathable Mesh', 'Fashion', 79.99, 150),
(7, 'Dress', 'Women''s Floral Print Summer Dress', 'Fashion', 39.99, 80),
(8, 'Watch', 'Stainless Steel Analog Watch', 'Fashion', 129.99, 100),
(9, 'Tablet', '10" Android Tablet with Quad-Core Processor', 'Electronics', 299.99, 50),
(10, 'Camera', 'DSLR Camera Kit with 18-55mm Lens', 'Electronics', 799.99, 20),
(11, 'Keyboard', 'Mechanical Gaming Keyboard with RGB Lighting', 'Electronics', 129.99, 50),
(12, 'Mouse', 'Wireless Optical Mouse', 'Electronics', 29.99, 100),
(13, 'Speaker', 'Bluetooth Portable Speaker', 'Electronics', 79.99, 150),
(14, 'T-shirt', 'Men''s Cotton T-shirt', 'Fashion', 19.99, 200),
(15, 'Jeans', 'Women''s Skinny Jeans', 'Fashion', 49.99, 100),
(16, 'Sunglasses', 'Polarized UV Protection Sunglasses', 'Fashion', 59.99, 50),
(17, 'Wallet', 'Genuine Leather Bifold Wallet', 'Fashion', 39.99, 120),
(18, 'Printer', 'Wireless All-in-One Printer', 'Electronics', 149.99, 40),
(19, 'Monitor', '27" LED Monitor with Full HD Resolution', 'Electronics', 249.99, 60),
(20, 'External HDD', '2TB Portable External Hard Drive', 'Electronics', 89.99, 80),
(21, 'Power Bank', '20000mAh Portable Power Bank', 'Electronics', 49.99, 100),
(22, 'Fitness Tracker', 'Waterproof Activity Tracker', 'Electronics', 79.99, 150),
(23, 'Yoga Mat', 'Non-Slip Exercise Mat', 'Fitness', 29.99, 200),
(24, 'Dumbbells', 'Set of 10 lb Rubber-Coated Dumbbells', 'Fitness', 39.99, 100),
(25, 'Protein Powder', 'Whey Protein Isolate Powder', 'Fitness', 49.99, 50);

-- ambil semua kolom dari tabel products
SELECT * FROM products;

-- urutkan data berdasarkan price secara descending
SELECT * FROM products
ORDER BY price DESC;

-- ambil nilai unique dari data
SELECT DISTINCT category FROM products

-- ambil 5 barang termahal
SELECT * FROM products
ORDER BY price DESC
LIMIT 5;

-- ambil semua data yang harganya lebih kecil dari 100
SELECT * FROM products
WHERE price < 100;

-- memilih produk dengan kategori 'Elektronik' atau 'Fashion'
SELECT *
FROM products
WHERE category IN ('Electronics', 'Fashion');

-- memilih produk dengan harga antara $50 dan $100
SELECT *
FROM products
WHERE price BETWEEN 50 AND 100;

-- memilih produk dengan nama yang mengandung kata 'Speaker'
SELECT *
FROM products
WHERE name LIKE '%Speaker%';

-- memilih produk dengan deskripsi yang tidak kosong
SELECT *
FROM products
WHERE description IS NOT NULL;

-- masukkan nilai ke dalam suatu tabel
INSERT INTO products (name, description, category, price, inventory_quantity)
VALUES ('Wireless Earbuds', 'Bluetooth Earphones with Charging Case', 'Electronics', 59.99, 80);

-- update harga dari produk dengan product_id = 3
UPDATE products
SET price = 69.99
WHERE product_id = 3;

-- hapus produk dengan product_id = 10
DELETE FROM products
WHERE product_id = 10;