PBD pert 9

manipulasi multi table

syntax menggabungkan dua tabel:

select nt1.nf, nt2.nf dst;

from nt1,nt2
where nt1.aa = nt2.aa

*nt1 = nama tabel1
*nf = nama field
*aa = data yang sama pada kedua tabel

syarat agar dapat berelasi dengan baik maka harus menggunakan data yang sama dengan menggunakan klausa 'where'


----------------------

barang : id_barang, nama, harga, jumlah, satuan

jual : id_jual, id_barang, tanggal, total, kasir


masukkan data minimal 5 untuk barang dan 10 untuk jual


tampilkan field berikut dari penggabugan dua tabel tersebut:

jumlah total barang dan nama yg di kelompokkan berdasarkan nama barang

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\User>cd c:\

C:\>cd xampp\mysql\bin

C:\xampp\mysql\bin>mysql -h localhost -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.41 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database coba_ajah;
Query OK, 1 row affected (0.00 sec)

mysql> use coba_ajah;
Database changed
mysql> create table ktp
-> (id int not null primary key auto_increment,
-> nama varchar(20));
Query OK, 0 rows affected (0.11 sec)

mysql> create table telp
-> (id int, telp double);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into ktp
-> (nama) values
-> ('joko'),('budi'),('eka');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> insert into telp
-> (id, telp) values
-> (1,123321),(2,234432),(3,345543);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from ktp;
+----+------+
| id | nama |
+----+------+
| 1 | joko |
| 2 | budi |
| 3 | eka |
+----+------+
3 rows in set (0.03 sec)

mysql> select * from telp;
+------+--------+
| id | telp |
+------+--------+
| 1 | 123321 |
| 2 | 234432 |
| 3 | 345543 |
+------+--------+
3 rows in set (0.01 sec)

mysql> select ktp.id, ktp.nama, telp.telp
-> from ktp,telp;
+----+------+--------+
| id | nama | telp |
+----+------+--------+
| 1 | joko | 123321 |
| 2 | budi | 123321 |
| 3 | eka | 123321 |
| 1 | joko | 234432 |
| 2 | budi | 234432 |
| 3 | eka | 234432 |
| 1 | joko | 345543 |
| 2 | budi | 345543 |
| 3 | eka | 345543 |
+----+------+--------+
9 rows in set (0.01 sec)

mysql> select ktp.id, ktp.nama, telp.telp
-> from ktp, telp
-> where ktp.id = telp.id;
+----+------+--------+
| id | nama | telp |
+----+------+--------+
| 1 | joko | 123321 |
| 2 | budi | 234432 |
| 3 | eka | 345543 |
+----+------+--------+
3 rows in set (0.00 sec)

mysql> create table barang
-> (id_barang int not null auto_increment,
-> nama varchar(20), harga double, jumlah int,
-> satuan varchar(20));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto colum
n and it must be defined as a key
mysql> create table barang
-> (id_barang int not null primary key auto_increment,
-> nama varchar(20), harga double, jumlah int,
-> satuan varchar(20));
Query OK, 0 rows affected (0.03 sec)


mysql> create table jual
-> (id_jual int not null primary key auto_increment,
-> id_barang int, tanggal date, total double,
-> kasir varchar(20));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into barang
-> (nama, harga, jumlah,satuan) values
-> ('baju',10000,10,'set'),
-> ('celana',20000,50,'set'),
-> ('ember',5000,100,'pvs'),
-> ('panci',25000,5,'pcs'),
-> ('sapu',15000,25,'pcs');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0


mysql> insert into jual
-> (id_barang, tanggal, total, kasir) values
-> (1, '2011-6-1',50000,'isti'),
-> (1, '2011-6-2',20000,'tono'),
-> (1, '2011-6-3',10000,'selvi'),
-> (2, '2011-6-1',60000,'isti'),
-> (2, '2011-6-2',20000,'tono'),
-> (3, '2011-6-1',25000,'isti'),
-> (3, '2011-6-3',15000,'selvi'),
-> (4, '2011-6-1',250000,'isti'),
-> (5, '2011-6-1',15000,'isti'),
-> (5, '2011-6-2',30000,'tono'),
-> (5, '2011-6-3',60000,'selvi');
Query OK, 11 rows affected (0.01 sec)
Records: 11 Duplicates: 0 Warnings: 0

mysql> select * from barang;
+-----------+--------+-------+--------+--------+
| id_barang | nama | harga | jumlah | satuan |
+-----------+--------+-------+--------+--------+
| 1 | baju | 10000 | 10 | set |
| 2 | celana | 20000 | 50 | set |
| 3 | ember | 5000 | 100 | pvs |
| 4 | panci | 25000 | 5 | pcs |
| 5 | sapu | 15000 | 25 | pcs |
+-----------+--------+-------+--------+--------+
5 rows in set (0.01 sec)

mysql> select * from jual;
+---------+-----------+------------+--------+-------+
| id_jual | id_barang | tanggal | total | kasir |
+---------+-----------+------------+--------+-------+
| 1 | 1 | 2011-06-01 | 50000 | isti |
| 2 | 1 | 2011-06-02 | 20000 | tono |
| 3 | 1 | 2011-06-03 | 10000 | selvi |
| 4 | 2 | 2011-06-01 | 60000 | isti |
| 5 | 2 | 2011-06-02 | 20000 | tono |
| 6 | 3 | 2011-06-01 | 25000 | isti |
| 7 | 3 | 2011-06-03 | 15000 | selvi |
| 8 | 4 | 2011-06-01 | 250000 | isti |
| 9 | 5 | 2011-06-01 | 15000 | isti |
| 10 | 5 | 2011-06-02 | 30000 | tono |
| 11 | 5 | 2011-06-03 | 60000 | selvi |
+---------+-----------+------------+--------+-------+
11 rows in set (0.00 sec)

mysql> select barang.id_barang, barang.nama,
-> barang.harga, jual.total, jual.tanggal
-> from barang, jual
-> where barang.id_barang = jual.id_barang;
+-----------+--------+-------+--------+------------+
| id_barang | nama | harga | total | tanggal |
+-----------+--------+-------+--------+------------+
| 1 | baju | 10000 | 50000 | 2011-06-01 |
| 1 | baju | 10000 | 20000 | 2011-06-02 |
| 1 | baju | 10000 | 10000 | 2011-06-03 |
| 2 | celana | 20000 | 60000 | 2011-06-01 |
| 2 | celana | 20000 | 20000 | 2011-06-02 |
| 3 | ember | 5000 | 25000 | 2011-06-01 |
| 3 | ember | 5000 | 15000 | 2011-06-03 |
| 4 | panci | 25000 | 250000 | 2011-06-01 |
| 5 | sapu | 15000 | 15000 | 2011-06-01 |
| 5 | sapu | 15000 | 30000 | 2011-06-02 |
| 5 | sapu | 15000 | 60000 | 2011-06-03 |
+-----------+--------+-------+--------+------------+
11 rows in set (0.02 sec)

mysql> select * from jual;
+---------+-----------+------------+--------+-------+
| id_jual | id_barang | tanggal | total | kasir |
+---------+-----------+------------+--------+-------+
| 1 | 1 | 2011-06-01 | 50000 | isti |
| 2 | 1 | 2011-06-02 | 20000 | tono |
| 3 | 1 | 2011-06-03 | 10000 | selvi |
| 4 | 2 | 2011-06-01 | 60000 | isti |
| 5 | 2 | 2011-06-02 | 20000 | tono |
| 6 | 3 | 2011-06-01 | 25000 | isti |
| 7 | 3 | 2011-06-03 | 15000 | selvi |
| 8 | 4 | 2011-06-01 | 250000 | isti |
| 9 | 5 | 2011-06-01 | 15000 | isti |
| 10 | 5 | 2011-06-02 | 30000 | tono |
| 11 | 5 | 2011-06-03 | 60000 | selvi |
+---------+-----------+------------+--------+-------+
11 rows in set (0.00 sec)

mysql> select sum(jual.total), barang.nama
-> from jual, barang
-> where jual.id_barang = barang.id_barang
-> group by barang.nama;
+-----------------+--------+
| sum(jual.total) | nama |
+-----------------+--------+
| 80000 | baju |
| 80000 | celana |
| 40000 | ember |
| 250000 | panci |
| 105000 | sapu |
+-----------------+--------+
5 rows in set (0.02 sec)

mysql> insert into barang
-> (nama,harga,jumlah,satuan)values
-> ('sepatu',10000,10,'pasang');
Query OK, 1 row affected (0.00 sec)

mysql> select *from barang;
+-----------+--------+-------+--------+--------+
| id_barang | nama | harga | jumlah | satuan |
+-----------+--------+-------+--------+--------+
| 1 | baju | 10000 | 10 | set |
| 2 | celana | 20000 | 50 | set |
| 3 | ember | 5000 | 100 | pcs |
| 4 | panci | 25000 | 10 | pcs |
| 5 | sapu | 15000 | 25 | pcs |
| 6 | sepatu | 10000 | 10 | pasang |
+-----------+--------+-------+--------+--------+
6 rows in set (0.00 sec)

mysql> select b.id_barang, b.nama, b.harga, j.total, j.tanggal
-> from barang b, jual j
-> where b.id_barang = j.id_barang;
+-----------+--------+-------+--------+------------+
| id_barang | nama | harga | total | tanggal |
+-----------+--------+-------+--------+------------+
| 1 | baju | 10000 | 50000 | 2011-06-01 |
| 1 | baju | 10000 | 20000 | 2011-06-02 |
| 1 | baju | 10000 | 10000 | 2011-06-03 |
| 2 | celana | 20000 | 60000 | 2011-06-01 |
| 2 | celana | 20000 | 20000 | 2011-06-02 |
| 3 | ember | 5000 | 25000 | 2011-06-01 |
| 3 | ember | 5000 | 15000 | 2011-06-03 |
| 4 | panci | 25000 | 250000 | 2011-06-01 |
| 5 | sapu | 15000 | 15000 | 2011-06-01 |
| 5 | sapu | 15000 | 30000 | 2011-06-02 |
| 5 | sapu | 15000 | 60000 | 2011-06-03 |
+-----------+--------+-------+--------+------------+
11 rows in set (0.00 sec)

mysql> select b.id_barang, b.nama, b.harga, j.total, j.tanggal
-> from barang b, jual j
-> where b.id_barang = j.id_barang;
+-----------+--------+-------+--------+------------+
| id_barang | nama | harga | total | tanggal |
+-----------+--------+-------+--------+------------+
| 1 | baju | 10000 | 50000 | 2011-06-01 |
| 1 | baju | 10000 | 20000 | 2011-06-02 |
| 1 | baju | 10000 | 10000 | 2011-06-03 |
| 2 | celana | 20000 | 60000 | 2011-06-01 |
| 2 | celana | 20000 | 20000 | 2011-06-02 |
| 3 | ember | 5000 | 25000 | 2011-06-01 |
| 3 | ember | 5000 | 15000 | 2011-06-03 |
| 4 | panci | 25000 | 250000 | 2011-06-01 |
| 5 | sapu | 15000 | 15000 | 2011-06-01 |
| 5 | sapu | 15000 | 30000 | 2011-06-02 |
| 5 | sapu | 15000 | 60000 | 2011-06-03 |
+-----------+--------+-------+--------+------------+
11 rows in set (0.00 sec)

mysql> select b.id_barang, b.nama, b.harga, j.total, j.tanggal
-> from barang b, jual j
-> where b.id_barang = j.id_barang
-> and j.total >15000;
+-----------+--------+-------+--------+------------+
| id_barang | nama | harga | total | tanggal |
+-----------+--------+-------+--------+------------+
| 1 | baju | 10000 | 50000 | 2011-06-01 |
| 1 | baju | 10000 | 20000 | 2011-06-02 |
| 2 | celana | 20000 | 60000 | 2011-06-01 |
| 2 | celana | 20000 | 20000 | 2011-06-02 |
| 3 | ember | 5000 | 25000 | 2011-06-01 |
| 4 | panci | 25000 | 250000 | 2011-06-01 |
| 5 | sapu | 15000 | 30000 | 2011-06-02 |
| 5 | sapu | 15000 | 60000 | 2011-06-03 |
+-----------+--------+-------+--------+------------+
8 rows in set (0.00 sec)

mysql> select b.id_barang, b.nama, b.harga, j.total, j.tanggal
-> from barang b, jual j
-> where b.id_barang = j.id_barang
-> and j.tanggal between '2011-06-01' and '2011-06-05';
+-----------+--------+-------+--------+------------+
| id_barang | nama | harga | total | tanggal |
+-----------+--------+-------+--------+------------+
| 1 | baju | 10000 | 50000 | 2011-06-01 |
| 1 | baju | 10000 | 20000 | 2011-06-02 |
| 1 | baju | 10000 | 10000 | 2011-06-03 |
| 2 | celana | 20000 | 60000 | 2011-06-01 |
| 2 | celana | 20000 | 20000 | 2011-06-02 |
| 3 | ember | 5000 | 25000 | 2011-06-01 |
| 3 | ember | 5000 | 15000 | 2011-06-03 |
| 4 | panci | 25000 | 250000 | 2011-06-01 |
| 5 | sapu | 15000 | 15000 | 2011-06-01 |
| 5 | sapu | 15000 | 30000 | 2011-06-02 |
| 5 | sapu | 15000 | 60000 | 2011-06-03 |
+-----------+--------+-------+--------+------------+
11 rows in set (0.03 sec)

mysql> create table jenis
-> (id_jenis int not null primary key auto_increment,
-> jenis varchar(20));
Query OK, 0 rows affected (1.06 sec)
mysql>