PBD pert 12

metode out

menghitung karyawan dari tabel karyawan

delimiter //
create pprocedure jml(OUT jumlah int)
select count(id_kar) into jumlah from karyawan;
//

delimiter :

cara memenggil metode out berbeda dengan metode in.
untuk metode out harus menggunakan sebuah variable penampung.

call jml(@jml_karyawan);

select @jml_karyawan;


INOUT

menampilkan jumlah gaji karyawan berdasarkan jabatan
dimana jabatan = XX

delimiter //
create procedure jumgaji(IN jb varchar(20), OUT gj double)
select sum(gaji) iinto gj from karyawan
where jabatan = jb;
//

delimiter :

call jumgaji('op',@jml_gaji);

select @jml_gaji

delimiter /
create procedure gajab(In gj double)
select jabatan, gaji from karyawan
where gaji > gj;
/

delimiter ;
call gajab(2000000);


delimiter //
create procedure allgaji(out gj double)
select sum(gaji) into gj from karyawan;
//

delimiter ;

call allgaji(@gaji_semuanya);

select @gaji_semuanya;

READ MORE - PBD pert 12

PBD pert 11

SUBQUERY
-->BENTUK QUERY YG TERLETAK DALAM QUERY YG LAIN ATAU LEBIH MUDAHNYA SELECT DIADALAM SELECT.

UNTUK MEMPRAKTEKKAN SUBQUERY BUAT DUA TABEL
1. KARYAWAN : ID_KAR, NAMA, ALAMAT, GOLONGAN, STATUS, JK
2. GOLONGAN : ID_GOL, GOLONGAN, GAJI

KEMUDIAN ISIKAN DATANYA MINIMAL 5

-------------- MEMBUAT TABEL KARYAWAN ----------------

mysql> create table karyawan
-> (id_kar int not null primary key auto_increment,
-> nama varchar(20), alamat varchar(20), golongan char(2),
-> status varchar(20), JK CHAR(2));

--------------- MEMBUAT TABEL GOLOGAN ----------------

mysql> create table golongan
-> (id_gol int not null primary key auto_increment,
-> golongan char(2), gaji double);

--------------- MENGISI DATA KE TABEL KARYAWAN -------

mysql> insert into karyawan
-> (nama, alamat, golongan, status, jk) values
-> ('antie','sei.panas','A','Lajang','PR'),
-> ('budi','bengkong','B','Lajang','LK'),
-> ('tika','nagoya','B','Lajang','LK'),
-> ('andi','jodoh','C','Menikah','PR'),
-> ('slamet','sengkuang','A','Menikah','LK');

--------------- MENGISI DATA KE TABEL GOLONGAN -------

mysql> insert into golongan
-> (golongan, gaji) values
-> ('A',1000000), ('B',2000000),
-> ('C',3000000), ('D',4000000),
-> ('E',5000000), ('F',6000000);


SUBQUERY DASAR --------------------------------------

1. MENAMPILKAN FIELD GOLONGAN DAN GAJI PALING BESAR DARI TABEL GOLONGAN

COBA LAKUKAN DENGAN PERINTAH BERIKUT (TANPA SUB QUERY)

mysql> SELECT GOLONGAN, MAX(GAJI) FROM GOLONGAN;

KEMUDIAN PERINTAH BERIKUT DENGAN SUBQUERY

mysql> SELECT GOLONGAN, GAJI FROM GOLONGAN
-> WHERE GAJI =
-> (SELECT MAX(GAJI) FROM GOLONGAN);

APA HASILNYA???

SUBQUERY BARIS TUNGGAL ------------------------------

mysql> SELECT GOLONGAN, GAJI FROM GOLONGAN
-> WHERE GAJI =
-> (SELECT GAJI FROM GOLONGAN WHERE GAJI > 3000000);

APA YG TERJADI?

UNTUK MENGATASINYA MAKA PERLU DIBUAT SEPERTI DIATAS


mysql> SELECT GOLONGAN, GAJI FROM GOLONGAN
-> WHERE GAJI =
-> (SELECT MAX(GAJI) FROM GOLONGAN);

SUBQUERY BARIS GANDA ------------------------------

UHTUK MENGGUNAKAN SUBQUERY BARIS GANDA HARUS MENGGUNAKAN KALUSA
1. IN
2. EXISTS
3. ANY
4. ALL


IN--------------

mysql> SELECT GOLONGAN, GAJI FROM GOLONGAN
-> WHERE GAJI IN
-> (SELECT GAJI FROM GOLONGAN WHERE GAJI > 3000000);

EXISTS----------
mysql> SELECT GOLONGAN, GAJI FROM GOLONGAN
-> WHERE EXISTS
-> (SELECT GOLONGAN FROM KARYAWAN
-> WHERE GOLONGAN = GOLONGAN.GOLONGAN);

mysql> SELECT GOLONGAN, GAJI FROM GOLONGAN
-> WHERE NOT EXISTS
-> (SELECT GOLONGAN FROM KARYAWAN
-> WHERE GOLONGAN = GOLONGAN.GOLONGAN);

ENY--------------
UNTUK MENGGUNAKAN ANY MAKA HARUS MENGGUNAKAN OPERATOR PEMBANDING (<,>,=)

mysql> SELECT ID_KAR, NAMA FROM KARYAWAN
-> WHERE GOLONGAN = ANY
-> (SELECT GOLONGAN FROM GOLONGAN);

ALL-------------
mysql> SELECT ID_KAR, NAMA FROM KARYAWAN
-> WHERE GOLONGAN > ALL
-> (SELECT GOLONGAN FROM GOLONGAN);

SUBQUERY PADA EKSPRESI CASE----------------------

mysql> SELECT ID_KAR, NAMA, CASE
-> WHEN GOLONGAN =
-> (SELECT GOLONGAN FROM GOLONGAN
-> WHERE GOLONGAN = 'F')
-> THEN 'BOSS'
-> WHEN GOLONGAN = (SELECT GOLONGAN FROM GOLONGAN
-> WHERE GOLONGAN = 'E')
-> THEN 'HAMPIR BOS'
-> ELSE 'ANAK BUAH'
-> END AS 'JABATAN'
-> FROM KARYAWAN;

READ MORE - PBD pert 11

PBD pert 10

PERTEMUAN 10

JOIN TABLE
--> MENGGABUNGKAN DUA TABEL YANG BERBEDA DENGAN KRITERIA TERTENTU

JOIN TABLE DI BAGI 4
1. NATURAL JOIN
--> PENGGABUNGAN DUA BUAH TABEL YANG MENGHASILKAN TABEL BARU DIMANA TERDAPAT KESAMAAN DATA DIANTARA KEDUANYA. (DATA YANG TIDAK SAMA TIDAK DI TAMPILKAN).

2. LEFT JOIN
--> PENGGABUNGAN DUA BUAH TABEL YANG MENGHASILKAN TABEL BARU DIMANA DATA PADA TABEL KIRI DI TAMPILKAN SEMUA WALAUPUN DATA TERSEBUT TIDAK TERDAPAT PADA TABEL KANAN

3. RIGHT JOIN
--> PENGGABUNGAN DUA BUAH TABEL YANG MENGHASILKAN TABEL BARU DIMANA DATA PADA TABEL KANAN DI TAMPILKAN SEMUA WALAUPUN DATA TERSEBUT TIDAK TERDAPAT PADA TABEL KIRI

TABEL KIRI = TABEL SETELAH FROM
TABEL KANAN = TABEL SETELAH JOIN

4. INNER JOIN
--> SAMA DENGAN NATURAL JOIN TP HASILNYA TELAH DI URUTKAN

SINTAX NATURAL JOIN

SELECT NT1.NF, NT2.NF DST
FROM NT1 NATURAL JOIN NT2;

NT1 = NAMA TABEL1
NF = NAMA FIELD
DST = DAN SETERUSNYA


SINTAX LEFT JOIN
SELECT NT1.NF, NT2.NF DST
FROM NT1 LEFT JOIN NT2
ON NT1.AA = NT2.AA;


AA = FIELD YANG SAMA ANTAR KEDUA TABEL


SINTAX LEFT JOIN
SELECT NT1.NF, NT2.NF DST
FROM NT1 RIGHT JOIN NT2
ON NT1.AA = NT2.AA;

SINTAX LEFT JOIN
SELECT NT1.NF, NT2.NF DST
FROM NT1 INNER JOIN NT2
ON NT1.AA = NT2.AA;

PENGGANTI KATA ON DALAM JOIN TABLE

1. NATURAL
2. USING
-> ;
ERROR 1046 (3D000): No database selected
mysql> CREATE DATABASE AA;
Query OK, 1 row affected (0.02 sec)

mysql> USE AA;
Database changed
mysql> CREATE TABLE BARANG
-> (ID_BAR INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> NAMA VARCHAR, HARGA DOUBLE, SATUAN VARCHAR(20));
ERROR 1064 (42000): You have an error in your SQL syntax; check thdoublee manual that
corresponds to your MySQL server version for the right syntax to use near ' HARG
A DOUBLE, SATUAN VARCHAR(20))' at line 3
mysql> CREATE TABLE BARANG
-> (ID_BAR INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> NAMA VARCHAR(20), HARGA DOUBLE, SATUAN VARCHAR(20));
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE JUAL
-> (ID_JUAL INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> ID_BAR INT, TOTAL DOUBLE, TANGGAL DATE,
-> ADMIN VARCHAR(20));
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO BARANG
-> (NAMA, HARGA, SATUAN) VALUES
-> ('BAJU',10000,'PCS'),
-> ('TOPI',5000,'PCS'),
-> ('SAPU',13000,'PCS'),
-> ('PANCI',13000,'PCS'),
-> ('PIRING',26000,'LUSIN');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> INSERT INTO JUAL
-> (ID_BAR, TOTAL, TANGGAL, ADMIN) VALUES
-> (1,100000,'2011-06-20','JOKO'),
-> (2,50000,'2011-06-21','SANTI'),
-> (3,26000,'2011-06-20','JOKO');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0


mysql> INSERT INTO JUAL
-> (ID_BAR, TOTAL, TANGGAL, ADMIN) VALUES
-> (1,100000,'2011-06-21','SANTI'),
-> (2,50000,'2011-06-23','ANTO');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM JUAL;
+---------+--------+--------+------------+-------+
| ID_JUAL | ID_BAR | TOTAL | TANGGAL | ADMIN |
+---------+--------+--------+------------+-------+
| 1 | 1 | 100000 | 2011-06-20 | JOKO |
| 2 | 2 | 50000 | 2011-06-21 | SANTI |
| 3 | 3 | 26000 | 2011-06-20 | JOKO |
| 4 | 1 | 100000 | 2011-06-21 | SANTI |
| 5 | 2 | 50000 | 2011-06-23 | ANTO |
+---------+--------+--------+------------+-------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM BARANG;
+--------+--------+-------+--------+
| ID_BAR | NAMA | HARGA | SATUAN |
+--------+--------+-------+--------+
| 1 | BAJU | 10000 | PCS |
| 2 | TOPI | 5000 | PCS |
| 3 | SAPU | 13000 | PCS |
| 4 | PANCI | 13000 | PCS |
| 5 | PIRING | 26000 | LUSIN |
+--------+--------+-------+--------+
5 rows in set (0.01 sec)

mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL FROM BARANG B, JUAL J
-> WHERE B.ID_BAR = J.ID_BAR;
+--------+------+-------+--------+------------+
| ID_BAR | NAMA | HARGA | TOTAL | TANGGAL |
+--------+------+-------+--------+------------+
| 1 | BAJU | 10000 | 100000 | 2011-06-20 |
| 2 | TOPI | 5000 | 50000 | 2011-06-21 |
| 3 | SAPU | 13000 | 26000 | 2011-06-20 |
| 1 | BAJU | 10000 | 100000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-23 |
+--------+------+-------+--------+------------+
5 rows in set (0.00 sec)

mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL FROM BARANG B, JUAL J
-> WHERE B.ID_BAR = J.ID_BAR;
+--------+------+-------+--------+------------+
| ID_BAR | NAMA | HARGA | TOTAL | TANGGAL |
+--------+------+-------+--------+------------+
| 1 | BAJU | 10000 | 100000 | 2011-06-20 |
| 2 | TOPI | 5000 | 50000 | 2011-06-21 |
| 3 | SAPU | 13000 | 26000 | 2011-06-20 |
| 1 | BAJU | 10000 | 100000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-23 |
+--------+------+-------+--------+------------+
5 rows in set (0.01 sec)

mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL FROM BARANG B, JUAL J
-> WHERE B.ID_BAR = J.ID_JUAL;
+--------+--------+-------+--------+------------+
| ID_BAR | NAMA | HARGA | TOTAL | TANGGAL |
+--------+--------+-------+--------+------------+
| 1 | BAJU | 10000 | 100000 | 2011-06-20 |
| 2 | TOPI | 5000 | 50000 | 2011-06-21 |
| 3 | SAPU | 13000 | 26000 | 2011-06-20 |
| 4 | PANCI | 13000 | 100000 | 2011-06-21 |
| 5 | PIRING | 26000 | 50000 | 2011-06-23 |
+--------+--------+-------+--------+------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM JUAL;
+---------+--------+--------+------------+-------+
| ID_JUAL | ID_BAR | TOTAL | TANGGAL | ADMIN |
+---------+--------+--------+------------+-------+
| 1 | 1 | 100000 | 2011-06-20 | JOKO |
| 2 | 2 | 50000 | 2011-06-21 | SANTI |
| 3 | 3 | 26000 | 2011-06-20 | JOKO |
| 4 | 1 | 100000 | 2011-06-21 | SANTI |
| 5 | 2 | 50000 | 2011-06-23 | ANTO |
+---------+--------+--------+------------+-------+
5 rows in set (0.00 sec)

mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL
-> FROM BARANG B NATURAL JOIN JUAL J;
+--------+------+-------+--------+------------+
| ID_BAR | NAMA | HARGA | TOTAL | TANGGAL |
+--------+------+-------+--------+------------+
| 1 | BAJU | 10000 | 100000 | 2011-06-20 |
| 2 | TOPI | 5000 | 50000 | 2011-06-21 |
| 3 | SAPU | 13000 | 26000 | 2011-06-20 |
| 1 | BAJU | 10000 | 100000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-23 |
+--------+------+-------+--------+------------+
5 rows in set (0.00 sec)

mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL FROM BARANG B
-> LEFT JOIN JUAL J
-> ON B.ID_BAR = J.ID_BAR;
+--------+--------+-------+--------+------------+
| ID_BAR | NAMA | HARGA | TOTAL | TANGGAL |
+--------+--------+-------+--------+------------+
| 1 | BAJU | 10000 | 100000 | 2011-06-20 |
| 1 | BAJU | 10000 | 100000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-23 |
| 3 | SAPU | 13000 | 26000 | 2011-06-20 |
| 4 | PANCI | 13000 | NULL | NULL |
| 5 | PIRING | 26000 | NULL | NULL |
+--------+--------+-------+--------+------------+
7 rows in set (0.00 sec)

mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL FROM BARANG B
-> RIGHT JOIN JUAL J
-> ON B.ID_BAR = J.ID_BAR;
+--------+------+-------+--------+------------+
| ID_BAR | NAMA | HARGA | TOTAL | TANGGAL |
+--------+------+-------+--------+------------+
| 1 | BAJU | 10000 | 100000 | 2011-06-20 |
| 2 | TOPI | 5000 | 50000 | 2011-06-21 |
| 3 | SAPU | 13000 | 26000 | 2011-06-20 |
| 1 | BAJU | 10000 | 100000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-23 |
+--------+------+-------+--------+------------+
5 rows in set (0.00 sec)

mysql> INSERT INTO JUAL
->
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 1
mysql> DESC JUAL;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| ID_JUAL | int(11) | NO | PRI | NULL | auto_increment |
| ID_BAR | int(11) | YES | | NULL | |
| TOTAL | double | YES | | NULL | |
| TANGGAL | date | YES | | NULL | |
| ADMIN | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.07 sec)

mysql> INSERT INTO JUAL
-> (ID_BAR, TOTAL, TANGGAL, ADMIN) VALUES
-> (10,100000,'2011-06-21','JOKO');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL FROM BARANG B
-> RIGHT JOIN JUAL J
-> ON B.ID_BAR = J.ID_BAR;
+--------+------+-------+--------+------------+
| ID_BAR | NAMA | HARGA | TOTAL | TANGGAL |
+--------+------+-------+--------+------------+
| 1 | BAJU | 10000 | 100000 | 2011-06-20 |
| 2 | TOPI | 5000 | 50000 | 2011-06-21 |
| 3 | SAPU | 13000 | 26000 | 2011-06-20 |
| 1 | BAJU | 10000 | 100000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-23 |
| NULL | NULL | NULL | 100000 | 2011-06-21 |
+--------+------+-------+--------+------------+
6 rows in set (0.01 sec)

mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL FROM BARANG B
-> INNER JOIN JUAL J
-> ON B.ID_BAR = J.ID_BAR;
+--------+------+-------+--------+------------+
| ID_BAR | NAMA | HARGA | TOTAL | TANGGAL |
+--------+------+-------+--------+------------+
| 1 | BAJU | 10000 | 100000 | 2011-06-20 |
| 1 | BAJU | 10000 | 100000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-23 |
| 3 | SAPU | 13000 | 26000 | 2011-06-20 |
+--------+------+-------+--------+------------+
5 rows in set (0.02 sec)

mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL FROM BARANG B
-> NATURAL LEFT JOIN JUAL J;
+--------+--------+-------+--------+------------+
| ID_BAR | NAMA | HARGA | TOTAL | TANGGAL |
+--------+--------+-------+--------+------------+
| 1 | BAJU | 10000 | 100000 | 2011-06-20 |
| 1 | BAJU | 10000 | 100000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-23 |
| 3 | SAPU | 13000 | 26000 | 2011-06-20 |
| 4 | PANCI | 13000 | NULL | NULL |
| 5 | PIRING | 26000 | NULL | NULL |
+--------+--------+-------+--------+------------+
7 rows in set (0.01 sec)

mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL FROM BARANG B
-> NATURAL LEFT JOIN JUAL J;
+--------+--------+-------+--------+------------+
| ID_BAR | NAMA | HARGA | TOTAL | TANGGAL |
+--------+--------+-------+--------+------------+
| 1 | BAJU | 10000 | 100000 | 2011-06-20 |
| 1 | BAJU | 10000 | 100000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-23 |
| 3 | SAPU | 13000 | 26000 | 2011-06-20 |
| 4 | PANCI | 13000 | NULL | NULL |
| 5 | PIRING | 26000 | NULL | NULL |
+--------+--------+-------+--------+------------+
7 rows in set (0.00 sec)

mysql>
mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL FROM BARANG B
-> LEFT JOIN JUAL J
-> USING (ID_BAR);

READ MORE - PBD pert 10

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>

READ MORE - PBD pert 9