Jawaban Tugas Belajar SQL

myjawaban.txt

Dicoba dulu ya kak di cmd atau Command Prompt nya supaya mengerti

1) Buat Database : 

   CREATE DATABASE myshop;
   
2) Buat tabel : 
  
  - Table users :
 
  CREATE TABLE users(
    id int(3) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    password VARCHAR(255) 
  );

  - Table items:

  CREATE TABLE items(
    id INT(3) AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    description VARCHAR(255),
    price INT(10),
    stock INT(10),
    category_id int,
    FOREIGN KEY(category_id) REFERENCES categories(id)
  );

  - Table categories:

  CREATE TABLE categories(
    id INT(3) AUTO_INCREMENT PRIMARY KEY,
    kategori varchar(255)
  );

3) Memasukkan data : 
  
  - Memasukkan data di users :
 
  INSERT INTO users VALUES
   ('', 'John Doe', 'john@doe.com', 'john123'),
   ('', 'Jane Doe', 'jane@doe.com', 'jenita123');
  
  - Memasukkan data di categories:

  INSERT INTO categories VALUES
   ('', 'gadget'),
   ('', 'cloth'),
   ('', 'men'),
   ('', 'women'),
   ('', 'branded');

  - Memasukkan data di items : 

  INSERT INTO items VALUES
   ( '', 'Sumsang b50', 'hape keren dari merek sumsang', 4000000, 100, '1'),
   ( '', 'Uniklooh', 'baju keren dari brand ternama', 500000, 50, '2'),
   ( '', 'IMHO Watch', 'jam tangan anak yang jujur banget', 2000000, 10, '1');	

4) Mengambil data dari database 

   a. SELECT id, name, email FROM users;

   b. 1. SELECT * FROM items WHERE price > 1000000;
      2. SELECT * FROM items WHERE name LIKE '%uniklooh';

   c. SELECT items.name, items.description, items.price, items.stock, items.category_id,
      categories.kategori FROM items, categories WHERE items.category_id=categories.id;

5) Mengubah Data dari DATABASE : 

   UPDATE items SET price=2500000 WHERE name='Sumsang b50';