mysql> CREATE
DATABASE PRAK2;
mysql> use prak2;
1. Membuat table
Command Line
mysql>
CREATE TABLE TABLE1(
=> id INT
AUTO_INCREMENT,
=> name VARCHAR(30) NOT
NULL,
=> salary FLOAT(10,2)
DEFAULT 1000000);.
Hasil
describe table1
2. Membuat table
dengan Primary Key
Comand line
mysql> CREATE TABLE TABLE2(
=> id INT AUTO_INCREMENT PRIMARY KEY,
=> name VARCHAR(30));
Hasil
describe table2
mysql>
CREATE TABLE TABLE3(
=> id INT AUTO_INCREMENT,
=> name VARCHAR(30),
=> PRIMARY KEY (id));
Hasil
describe table3
3. Membuat tabel dengan nilai unique
Command line
mysql>
CREATE TABLE cars(
=>
id INT PRIMARY KEY AUTO_IN
=> plate VARCHAR (10),
=> brand VARCHAR (10),
=> UNIQUE (plate));
Hasil describe
table dengan nilai unique
4.
Membuat
table employee
Command
line
mysql> CREATE TABLE employee(
=>
id INT AUTO_INCREMENT PRIMARY
=> first_name VARCHAR(15),
=>
last_name VARCHAR(15),
=>
start_date DATE,
=> end_date DATE,
=> salary FLOAT(8,2),
=> city VARCHAR(10),
=> description VARCHAR(15));
Hasil
describe dengan statement select
Mengisi
Tabel employee
5. Menyalin
data dengan statement select
Command
line
mysql>
CREATE TABLE employee_copy AS
=> SELECT *
=> FROM employee
=> WHERE start_date BETWEEN '1970-01-01' AND '1990-12-31';
Hasil
describe tabel copy employee
6. Membuat tabel Temporary.
Command line
mysql>
CREATE TEMPORARY TABLE TEMPTBL(
=> id INT PRIMARY KEY AUTO_INCREMENT,
=> name VARCHAR(30));
7. Insert Menggunakan data dari Orang lain
Command
line
mysql> CREATE TABLE employee2
(
=> id INT
AUTO_INCREMENT PRIMARY KEY,
=> name VARCHAR(15),
=> city
VARCHAR(20));
Hasil
Describe tabel employee 2
Memasukan
data dari tabel employee ke tabel employee2
Command line
mysql> INSERT INTO employee2
=> (id, name, city)
=> SELECT id, CONCAT(first_name,' ',last_name), city
=> FROM employee;
Hasil
Select dari tabel employee2
8. Mengubah data dengan menggunakan update
Command
Line
mysql> UPDATE
employee2
=> SET city = 'Ottawa'
=> WHERE city = 'Vancouver';
Hasil
isi data tabel employee2 setelah di update
Mengubah nilai berdasarkan nilai yang di
miliki sekarang
a. Data
sebelum diubah
Command
line
mysql> SELECT salary FROM employee;
Isi
Tabel
b. Data
Sesudah diubah
Command
line
mysql> SELECT salary FROM employee;
Isi
Tabel
9. Mengganti data dari tabel
Command
line
mysql> REPLACE INTO employee2
(id,name, city) VALUES (12,'Sherlock Holmes','Manchester');
Hasil tabel setelah
data di ubah
10. Menghapus
data dari tabel
Command
line
mysql>
DELETE FROM employee2 WHERE city = 'Toronto';
Hasil tabel setelah
data di hapus
