Penugasan basis data(MySql)
Empno
|
Ename
|
job
|
Hiredate
|
Sal
|
comm
|
Deptno
|
7369
|
Smith
|
clerk
|
1980-12-17
|
1000
|
0
|
20
|
7499
|
Allen
|
salesman
|
1981-02-20
|
1600
|
300
|
30
|
7521
|
Wards
|
salesman
|
1981-02-22
|
1250
|
500
|
30
|
7566
|
Jones
|
manager
|
1981-04-02
|
2975
|
0
|
20
|
7654
|
Martin
|
salesman
|
1981-09-28
|
1250
|
1400
|
30
|
7698
|
Blake
|
manager
|
1981-03-01
|
2850
|
0
|
30
|
7698
|
Blake
|
manager
|
1981-03-01
|
2850
|
0
|
30
|
7782
|
Scott
|
manager
|
1981-06-09
|
2450
|
0
|
30
|
7788
|
Clark
|
analyst
|
1981-04-19
|
3000
|
0
|
20
|
7839
|
King
|
President
|
1981-10-17
|
5000
|
0
|
10
|
7844
|
Turner
|
salesman
|
1981-09-08
|
1500
|
0
|
30
|
Gunakan Mysql untuk
mengolah data ini!
Gunakan min, max, sum,
count.
- Langkah pertama aktifkan dahulu program “xampp”
- Pilih MySql dan kemudian aktifkan
- Setelah MySql dalam “xampp” aktif
- Buka start> pilih accessories> pilih Command prompt
- Maka akan tampil seperti ini :
Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:\Users\acer
- Yang berarti Command prompt ini aktif dalam “C” folder user dalam
computer merk “acer”
- Masukkan program yang akan digunakan seperti ini :
C:\Users\acer>cd\xampp
- Masukkan program yang dimaksud dalam “xampp”, karena kita akan
menggunakan “MySql”
C:\xampp>cd mysql
- Masukkan programnya :
C:\xampp\mysql>cd bin
C:\xampp\mysql\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.30-community MySQL
Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to
clear the buffer.
- Setelah tampil seperti diatas maka MySql siap untuk digunakan
- Perintah yang di gunakan untuk membuat suatu database dalam my sql
adalah :
mysql> create
database nisa;
Query OK, 1 row
affected (0.03 sec)
- Setelah query OK, maka priksalah isi database dalam computer anda
mysql> show
databases;
Database
|
information_schema
|
cdcol
|
Emp
|
Empl
|
Mysql
|
Nisa
|
Perhotelan
|
Phpmyadmin
|
Test
|
Webauth
|
10 rows in set (0.12 sec)
- Pilihlah database mana yang akan anda gunakan
mysql> use nisa;
Database changed
- Kemudian untuk memastikan bahwa batabase tersebut dapat di gunakan,
lakukan perintah ini:
mysql> select
database();
Database
|
Nisa
|
1 row in set (0.00 sec)
- Inputkan strutur nya
mysql> create
table nisa (empno char(5),ename varchar(10), job varchar(10),hired
ate date, sal
char(5), comm char(5), deptno char(3));
Query OK, 0 rows
affected (0.11 sec)
- Untuk melihat pendiscripsian dari input struktur table, menggunakan perintah :
mysql> describe
nisa;
Field
|
Type
|
null
|
Key
|
default
|
Extra
|
Empno
|
Char(5)
|
YES
|
NULL
|
||
Ename
|
Varchar(10)
|
YES
|
NULL
|
||
Job
|
Varchar(10)
|
YES
|
NULL
|
||
Hiredate
|
Date
|
YES
|
NULL
|
||
Sal
|
Char(5)
|
YES
|
NULL
|
||
Comm.
|
Char(5)
|
YES
|
NULL
|
||
Deptno
|
Char(3)
|
YES
|
NULL
|
7 rows in set (0.07 sec)
- Kemudian inputkan recordnya
mysql> insert into
nisa values
-> ('7369','smith','clerk','1980-12-17','1000','0','20'),
-> ('7499','allen','salesman','1981-02-20','1600','300','30'),
-> ('7521','wards','salesman','1981-02-22','1250','500','30'),
-> ('7566','jones','manager','1981-04-02','2975','0','20'),
-> ('7654','martin','salesman','1981-09-28','1250','1400','30'),
-> ('7698','blake','manager','1981-03-01','2850','0','30'),
-> ('7698','blake','manager','1981-03-01','2850','0','30'),
-> ('7782','scott','manager','1981-06-09','2450','0','30'),
-> ('7788','clark','analyst','1981-04-19','3000','0','20'),
-> ('7839','king','president','1981-10-17','5000','0','10'),
-> ('7844','turner','salesman','1981-09-08','1500','0','30');
Query OK, 11 rows
affected (0.00 sec)
Records: 11 Duplicates: 0
Warnings: 0
- Perintah untuk menampilkan keseluruhan field beserta recordnya
mysql> select *
from nisa;
Empno
|
Ename
|
job
|
Hiredate
|
Sal
|
comm
|
Deptno
|
7369
|
Smith
|
clerk
|
1980-12-17
|
1000
|
0
|
20
|
7499
|
Allen
|
salesman
|
1981-02-20
|
1600
|
300
|
30
|
7521
|
Wards
|
salesman
|
1981-02-22
|
1250
|
500
|
30
|
7566
|
Jones
|
manager
|
1981-04-02
|
2975
|
0
|
20
|
7654
|
Martin
|
salesman
|
1981-09-28
|
1250
|
1400
|
30
|
7698
|
Blake
|
manager
|
1981-03-01
|
2850
|
0
|
30
|
7698
|
Blake
|
manager
|
1981-03-01
|
2850
|
0
|
30
|
7782
|
Scott
|
manager
|
1981-06-09
|
2450
|
0
|
30
|
7788
|
Clark
|
analyst
|
1981-04-19
|
3000
|
0
|
20
|
7839
|
King
|
President
|
1981-10-17
|
5000
|
0
|
10
|
7844
|
Turner
|
salesman
|
1981-09-08
|
1500
|
0
|
30
|
11 rows in set (0.00
sec)
- perintah untuk menampilkan rata-rata dari field “comm”
mysql> select
avg(comm) from nisa;
Avg(comm.)
|
200
|
1 row in set (2.63 sec)
- Perintah untuk menampilkan nilai minimum dari field “comm”
mysql> select
min(comm) from nisa;
Min(comm.)
|
0
|
1 row in set (0.29 sec)
- Perintah untuk menampilakn nilai maksimal dari field “comm”
mysql> select
max(comm) from nisa;
Max(comm.)
|
500
|
1 row in set (0.00 sec)
- Perintah untuk menampilkan keseluruhan field “job” tanpa field
“ename dengan nama king”
mysql> select job
from nisa where ename<>'king';
Job
|
Clerk
|
Salesman
|
Salesman
|
Manager
|
Salesman
|
Manager
|
Manager
|
Manager
|
Analyst
|
Salesman
|
10 rows in set (1.15 sec)
- Perintah untuk menampilkan field job dan hiredate dari table “nisa”
mysql> select job,
hiredate from nisa;
Job
|
Hiredate
|
Clerk
|
1980-12-17
|
Salesman
|
1981-02-20
|
Salesman
|
1981-02-22
|
Manager
|
1981-04-02
|
Salesman
|
1981-09-28
|
Manager
|
1981-03-01
|
Manager
|
1981-03-01
|
Manager
|
1981-06-09
|
Analyst
|
1981-04-19
|
President
|
1981-10-17
|
Salesman
|
1981-09-08
|
11 rows in set (0.00 sec)
- Perintah yang menampilkan keseluruhan table nisa dimana field
“empno yang bernama turner”
Dan hasilnya tidak ada maka akan muncul “Empty set (0.00 sec)”
mysql> select *
from nisa where empno = 'turner';
Empty set (0.00 sec)
- Perintah yang menampilkan keseluruhan tabel nisa dimana field
“empno = 7369
mysql> select *
from nisa where empno = '7369';
Empno
|
Ename
|
Job
|
Hiredate
|
Sal
|
comm
|
Deptnp
|
7369
|
Smith
|
Clerk
|
1980-12-17
|
1000
|
0
|
20
|
1 row in set (0.00 sec)
- Perintah yang menampilkan “count (jumlah)” dalam field “job”
mysql>select job,
count(*) from nisa group by job;
Job
|
Count
|
Clerk
|
1
|
Salesman
|
4
|
Manager
|
4
|
President
|
1
|
Analyst
|
1
|
5
row in set (0.00 sec)
- Perintah yang menampilkan perhitungan jumlah keseluruhan dari field
“comm”
mysql> select sum
(comm) from nisa;
Sum(comm)
|
2200
|
1row in set(0.00)set
- Perintah yang menampilkan keseluruhan table nisa, dengan ascending
(abjad A-Z) menurut field “ename”
my sql> select * from
nisa order by ename asc;
Empno
|
ename
|
job
|
Hiredate
|
Sal
|
Comm.
|
Deptno
|
7499
|
Allen
|
salesman
|
1981-02-20
|
1600
|
300
|
30
|
7698
|
Blake
|
manager
|
1981-03-01
|
2850
|
0
|
30
|
7698
|
Blake
|
manager
|
1981-03-01
|
2850
|
0
|
30
|
7788
|
Clark
|
analyst
|
1981-04-19
|
3000
|
0
|
20
|
7566
|
Jones
|
manager
|
1981-04-02
|
2975
|
0
|
20
|
7839
|
King
|
President
|
1981-10-17
|
5000
|
0
|
10
|
7654
|
Martin
|
salesman
|
1981-09-28
|
1250
|
1400
|
30
|
7782
|
Scott
|
manager
|
1981-06-09
|
2450
|
0
|
30
|
7369
|
Smith
|
clerk
|
1980-12-17
|
1000
|
0
|
20
|
7844
|
Turner
|
salesman
|
1981-09-08
|
1500
|
0
|
30
|
7521
|
Wards
|
salesman
|
1981-02-22
|
1250
|
500
|
30
|
11row in set (0.05)
- Perintah yang menampilkan keseluruhan table nisa dengan discending
(Z-A), menurut field “ename”
mysql> select *
from order by ename desc;
Empno
|
ename
|
job
|
Hiredate
|
Sal
|
Comm.
|
Deptno
|
7521
|
Wards
|
salesman
|
1981-02-22
|
1250
|
500
|
30
|
7844
|
Turner
|
salesman
|
1981-09-08
|
1500
|
0
|
30
|
7369
|
Smith
|
clerk
|
1980-12-17
|
1000
|
0
|
20
|
7782
|
Scott
|
manager
|
1981-06-09
|
2450
|
0
|
30
|
7654
|
Martin
|
salesman
|
1981-09-28
|
1250
|
1400
|
30
|
7839
|
King
|
President
|
1981-10-17
|
5000
|
0
|
10
|
7566
|
Jones
|
manager
|
1981-04-02
|
2975
|
0
|
20
|
7788
|
Clark
|
analyst
|
1981-04-19
|
3000
|
0
|
20
|
7698
|
Blake
|
manager
|
1981-03-01
|
2850
|
0
|
30
|
7698
|
Blake
|
manager
|
1981-03-01
|
2850
|
0
|
30
|
7499
|
Allen
|
salesman
|
1981-02-20
|
1600
|
300
|
30
|
11row in set (0.05)
Anggota Kelompok
- Chairunisa Amalia Hapsari (B2A010007)
- Wantri Mukti Lestari (B2A010033)
- Faqih Al Umam (B2A010011)
- Ujang Maulana (B2A0100031)
- Fery NurJaman (B2A010012)
- Marlita Vebiriyana (B2A010017)
- Laila Nur Mahmudah (B2A010016)
Komentar
Posting Komentar