=== How To Grouping, Sorting & Criteria Data ===
You can download The Study Case & solution Lab Oracle & SQL Expert
== 5.1 pengelompokan data (group by) dan aggregation function ==
Sintak SQL
== 5.1.1 min() ==
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SELECT field 1,...,field n, MIN(name_field)
FROM TABLE
GROUP BY field 1,...,field m
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
== 5.1.2 max() ==
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SELECT field 1,...,field n, MAX(name_field)
FROM TABLE
GROUP BY field 1,...,field m
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
== 5.1.3 avg() ==
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SELECT field 1,...,field n, AVG(name_field)
FROM TABLE
GROUP BY field 1,...,field m
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
== 5.1.4 sum() ==
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SELECT field 1,...,field n, SUM(name_field)
FROM TABLE
GROUP BY field 1,...,field m
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
== 5.1.5 count()==
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SELECT field 1,...,field n, COUNT(name_field)
FROM TABLE
GROUP BY field 1,...,field m
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Sample Log :
== Grouping (group by) dan aggregation function Pasok ==
SQL> select * from pasok;
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK KETERANGAN
---------- ------ ----- --------- ------------ ---------------
PAS-E001 ELK-01 EJ-01 01-JAN-02 8 xxx
PAS-E003 ELK-02 EJ-01 01-FEB-02 2
PAS-E005 ELK-02 EB-02 01-JAN-02 2
PAS-E006 ELK-03 EJ-01 03-MAR-02 5
PAS-E007 ELK-03 EJ-01 04-MAR-02 2
PAS-E008 ELK-03 EJ-01 03-MAR-02 3
PAS-E009 ELK-03 EB-01 13-MAR-02 4
PAS-E010 ELK-03 EB-01 13-MAR-02 3
PAS-E011 ELK-04 EB-01 22-APR-02 12
PAS-E012 ELK-04 EB-02 30-APR-02 9
10 rows selected.
SQL>
SQL> select KODE_BARANG as KD_BRG, min(JUMLAH_PASOK) as MINIMUM_PASOK from pasok group by KODE_BARANG;
KD_BRG MINIMUM_PASOK
------ -------------
ELK-02 2
ELK-04 9
ELK-01 8
ELK-03 2
SQL>
SQL> select KODE_PASOK as KD_PSK, min(JUMLAH_PASOK) as MINIMUM_PASOK from pasok group by KODE_PASOK;
KD_PSK MINIMUM_PASOK
---------- -------------
PAS-E005 2
PAS-E006 5
PAS-E008 3
PAS-E009 4
PAS-E010 3
PAS-E007 2
PAS-E003 2
PAS-E001 8
PAS-E011 12
PAS-E012 9
10 rows selected.
SQL>
SQL> select KODE_BARANG as KD_BRG, max(JUMLAH_PASOK) as MAXIMUM_PASOK from pasok group by KODE_BARANG;
KD_BRG MAXIMUM_PASOK
------ -------------
ELK-02 2
ELK-04 12
ELK-01 8
ELK-03 5
SQL>
SQL> select KODE_BARANG as KD_BRG, avg(JUMLAH_PASOK) as AVERAGE_PASOK from pasok group by KODE_BARANG;
KD_BRG AVERAGE_PASOK
------ -------------
ELK-02 2
ELK-04 10.5
ELK-01 8
ELK-03 3.4
SQL>
SQL> select KODE_BARANG as KD_BRG, sum(JUMLAH_PASOK) as JUMLAH_PASOK from pasok group by KODE_BARANG;
KD_BRG JUMLAH_PASOK
------ ------------
ELK-02 4
ELK-04 21
ELK-01 8
ELK-03 17
SQL>
SQL> select KODE_BARANG as KD_BRG, count(JUMLAH_PASOK) as COUNT_PASOK from pasok group by KODE_BARANG;
KD_BRG COUNT_PASOK
------ -----------
ELK-02 2
ELK-04 2
ELK-01 1
ELK-03 5
SQL>
== Grouping (group by) dan aggregation function Suplier ==
SQL> select * from suplier;
KODE_ NAMA_SUPLIER ALAMAT_SUPLIER KOTA_SUPLIER TELEPON_SUPLIER KETERANGAN
----- ------------------------------ ------------------------------ --------------- --------------- ---------------
EJ-01 PT ACTRON JL THAMRIN 12 JAKARTA (021) 850-2301
EB-01 PT ULTRASOUND JL SUKARNO HATTA 103 BANDUNG (021) 522-3305
EB-02 PT SUPERTRON JL INDUSTRI 37 BANDUNG (021) 660-4091
EJ-02 PT MULYA ELEKTRONIK JL SUDIRMAN 45 JAKARTA (021) 855-4262
SQL>
SQL> select KODE_SUPLIER as KD_SUP, min(JUMLAH_PASOK) as MINIMUM_PASOK from pasok group by KODE_SUPLIER;
KD_SU MINIMUM_PASOK
----- -------------
EB-02 2
EB-01 3
EJ-01 2
SQL>
SQL> select KODE_SUPLIER as KD_SUP, max(JUMLAH_PASOK) as MAXIMAL_PASOK from pasok group by KODE_SUPLIER;
KD_SU MAXIMAL_PASOK
----- -------------
EB-02 9
EB-01 12
EJ-01 8
SQL> select KODE_SUPLIER as KD_SUP, avg(JUMLAH_PASOK) as AVERAGE_PASOK from pasok group by KODE_SUPLIER;
KD_SU AVERAGE_PASOK
----- -------------
EB-02 5.5
EB-01 6.33333333
EJ-01 4
SQL>
SQL> select KODE_SUPLIER as KD_SUP, sum(JUMLAH_PASOK) as JUMLAH_PASOK from pasok group by KODE_SUPLIER;
KD_SU JUMLAH_PASOK
----- ------------
EB-02 11
EB-01 19
EJ-01 20
SQL>
SQL> select KODE_SUPLIER as KD_SUP, count(JUMLAH_PASOK) as COUNT_PASOK from pasok group by KODE_SUPLIER;
KD_SU COUNT_PASOK
----- -----------
EB-02 2
EB-01 3
EJ-01 5
SQL>
== Grouping (group by) dan aggregation function Pembelian ==
SQL> select * from pembelian;
KODE_PEMBE KODE_B KODE_C TANGGAL_P JUMLAH_PEMBELIAN KETERANGAN
---------- ------ ------ --------- ---------------- ---------------
BEL-E001 ELK-01 J-0001 20-MAY-02 3 xxx
BEL-E002 ELK-01 J-0001 21-MAY-02 4
BEL-E003 ELK-01 J-0002 20-MAY-02 2
BEL-E004 ELK-01 B-0001 20-MAY-02 2
BEL-E005 ELK-01 B-0002 22-MAY-02 3
BEL-E006 ELK-02 J-0001 24-JUN-02 1
BEL-E007 ELK-02 J-0002 24-JUN-02 1
7 rows selected.
SQL>
SQL> select KODE_CUSTOMER as KD_CUS, min(JUMLAH_PEMBELIAN) as MINIMUM_PEMBELIAN from PEMBELIAN group by KODE_CUSTOMER;
KD_CUS MINIMUM_PEMBELIAN
------ -----------------
J-0001 1
J-0002 1
B-0001 2
B-0002 3
SQL>
SQL> select KODE_CUSTOMER as KD_CUS, max(JUMLAH_PEMBELIAN) as MAXIMAL_PEMBELIAN from PEMBELIAN group by KODE_CUSTOMER;
KD_CUS MAXIMAL_PEMBELIAN
------ -----------------
J-0001 4
J-0002 2
B-0001 2
B-0002 3
SQL> select KODE_CUSTOMER as KD_CUS, avg(JUMLAH_PEMBELIAN) as AVERAGE_PEMBELIAN from PEMBELIAN group by KODE_CUSTOMER;
KD_CUS AVERAGE_PEMBELIAN
------ -----------------
J-0001 2.66666667
J-0002 1.5
B-0001 2
B-0002 3
SQL>
SQL> select KODE_CUSTOMER as KD_CUS, sum(JUMLAH_PEMBELIAN) as JUMLAH_PEMBELIAN from PEMBELIAN group by KODE_CUSTOMER;
KD_CUS JUMLAH_PEMBELIAN
------ ----------------
J-0001 8
J-0002 3
B-0001 2
B-0002 3
SQL> select KODE_CUSTOMER as KD_CUS, count(JUMLAH_PEMBELIAN) as COUNT_PEMBELIAN from PEMBELIAN group by KODE_CUSTOMER;
KD_CUS COUNT_PEMBELIAN
------ ---------------
J-0001 3
J-0002 2
B-0001 1
B-0002 1
SQL>
== Grouping (group by) dan aggregation function Pasok ==
SQL> select * from pasok;
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK KETERANGAN
---------- ------ ----- --------- ------------ ---------------
PAS-E001 ELK-01 EJ-01 01-JAN-02 8 xxx
PAS-E003 ELK-02 EJ-01 01-FEB-02 2
PAS-E005 ELK-02 EB-02 01-JAN-02 2
PAS-E006 ELK-03 EJ-01 03-MAR-02 5
PAS-E007 ELK-03 EJ-01 04-MAR-02 2
PAS-E008 ELK-03 EJ-01 03-MAR-02 3
PAS-E009 ELK-03 EB-01 13-MAR-02 4
PAS-E010 ELK-03 EB-01 13-MAR-02 3
PAS-E011 ELK-04 EB-01 22-APR-02 12
PAS-E012 ELK-04 EB-02 30-APR-02 9
10 rows selected.
SQL>
SQL> select min(JUMLAH_PASOK) as MINIMUM_PASOK from pasok;
MINIMUM_PASOK
-------------
2
SQL> select max(JUMLAH_PASOK) as MAXIMUM_PASOK from pasok;
MAXIMUM_PASOK
-------------
12
SQL> select max(JUMLAH_PASOK) as AVERAGE_PASOK from pasok;
AVERAGE_PASOK
-------------
12
SQL> select avg(JUMLAH_PASOK) as AVERAGE_PASOK from pasok;
AVERAGE_PASOK
-------------
5
SQL> select sum(JUMLAH_PASOK) as JUMLAH_PASOK from pasok;
JUMLAH_PASOK
------------
50
SQL> select count(JUMLAH_PASOK) as COUNT_PASOK from pasok;
COUNT_PASOK
-----------
10
SQL>
SQL> select KODE_PASOK as KD_PSK, min(JUMLAH_PASOK) as MINIMUM_PASOK from pasok group by KODE_PASOK;
KD_PSK MINIMUM_PASOK
---------- -------------
PAS-E005 2
PAS-E006 5
PAS-E008 3
PAS-E009 4
PAS-E010 3
PAS-E007 2
PAS-E003 2
PAS-E001 8
PAS-E011 12
PAS-E012 9
10 rows selected.
SQL>
SQL> select KODE_PASOK as KD_PSK, max(JUMLAH_PASOK) as MAXIMAL_PASOK from pasok group by KODE_PASOK;
KD_PSK MAXIMAL_PASOK
---------- -------------
PAS-E005 2
PAS-E006 5
PAS-E008 3
PAS-E009 4
PAS-E010 3
PAS-E007 2
PAS-E003 2
PAS-E001 8
PAS-E011 12
PAS-E012 9
10 rows selected.
SQL>
SQL> select KODE_PASOK as KD_PSK, avg(JUMLAH_PASOK) as AVERAGE_PASOK from pasok group by KODE_PASOK;
KD_PSK AVERAGE_PASOK
---------- -------------
PAS-E005 2
PAS-E006 5
PAS-E008 3
PAS-E009 4
PAS-E010 3
PAS-E007 2
PAS-E003 2
PAS-E001 8
PAS-E011 12
PAS-E012 9
10 rows selected.
SQL>
SQL> select KODE_PASOK as KD_PSK, sum(JUMLAH_PASOK) as JUMLAH_PASOK from pasok group by KODE_PASOK;
KD_PSK JUMLAH_PASOK
---------- ------------
PAS-E005 2
PAS-E006 5
PAS-E008 3
PAS-E009 4
PAS-E010 3
PAS-E007 2
PAS-E003 2
PAS-E001 8
PAS-E011 12
PAS-E012 9
10 rows selected.
SQL>
SQL> select KODE_PASOK as KD_PSK, count(JUMLAH_PASOK) as COUNT_PASOK from pasok group by KODE_PASOK;
KD_PSK COUNT_PASOK
---------- -----------
PAS-E005 1
PAS-E006 1
PAS-E008 1
PAS-E009 1
PAS-E010 1
PAS-E007 1
PAS-E003 1
PAS-E001 1
PAS-E011 1
PAS-E012 1
10 rows selected.
SQL>
=== 5.2 Pengurutan data (order by) ===
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SELECT field 1,...,field n
FROM table
ORDER BY field
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Sample Log :
== Table Barang ==
SQL> select * from barang;
KODE_B NAMA_BARANG SATUAN_BARANG STOK_BARANG KETERANGAN
------ ------------------------- -------------------- ----------- ---------------
ELK-01 RICE COOKER BUAH 20
ELK-02 LEMARI ES UNIT 8
ELK-03 TELEVISI UNIT 30
ELK-04 RADIO/TAPE BUAH 35
SQL> select * from barang order by NAMA_BARANG ;
KODE_B NAMA_BARANG SATUAN_BARANG STOK_BARANG KETERANGAN
------ ------------------------- -------------------- ----------- ---------------
ELK-02 LEMARI ES UNIT 8
ELK-04 RADIO/TAPE BUAH 35
ELK-01 RICE COOKER BUAH 20
ELK-03 TELEVISI UNIT 30
SQL>
SQL> select * from barang order by STOK_BARANG ;
KODE_B NAMA_BARANG SATUAN_BARANG STOK_BARANG KETERANGAN
------ ------------------------- -------------------- ----------- ---------------
ELK-02 LEMARI ES UNIT 8
ELK-01 RICE COOKER BUAH 20
ELK-03 TELEVISI UNIT 30
ELK-04 RADIO/TAPE BUAH 35
SQL>
== Table Customer ==
SQL> select * from customer;
KODE_C NAMA_CUSTOMER ALAMAT_CUSTOMER KOTA_CUSTOMER TELEPON_CUSTOME KETERANGAN
------ ------------------------------ ------------------------------ --------------- --------------- ---------------
J-0001 TOKO KARISMA JL CIMANGGIS 34 JAKARTA (021) 856-4209
J-0002 TOKO AYU JL CIMANGGIS 12 JAKARTA (021) 856-1321
B-0001 TOKO WARNA JL ABC 234 BANDUNG (021) 432-6635
B-0002 TOKO SURYA JL ABC 309 BANDUNG (021) 432-6024
SQL>
SQL> select * from customer order by NAMA_CUSTOMER;
KODE_C NAMA_CUSTOMER ALAMAT_CUSTOMER KOTA_CUSTOMER TELEPON_CUSTOME KETERANGAN;
------ ------------------------------ ------------------------------ --------------- --------------- ---------------
J-0002 TOKO AYU JL CIMANGGIS 12 JAKARTA (021) 856-1321
J-0001 TOKO KARISMA JL CIMANGGIS 34 JAKARTA (021) 856-4209
B-0002 TOKO SURYA JL ABC 309 BANDUNG (021) 432-6024
B-0001 TOKO WARNA JL ABC 234 BANDUNG (021) 432-6635
SQL>
SQL> select * from customer order by KODE_CUSTOMER;
KODE_C NAMA_CUSTOMER ALAMAT_CUSTOMER KOTA_CUSTOMER TELEPON_CUSTOME KETERANGAN
------ ------------------------------ ------------------------------ --------------- --------------- ---------------
B-0001 TOKO WARNA JL ABC 234 BANDUNG (021) 432-6635
B-0002 TOKO SURYA JL ABC 309 BANDUNG (021) 432-6024
J-0001 TOKO KARISMA JL CIMANGGIS 34 JAKARTA (021) 856-4209
J-0002 TOKO AYU JL CIMANGGIS 12 JAKARTA (021) 856-1321
SQL>
SQL> select * from customer order by NAMA_CUSTOMER desc;
KODE_C NAMA_CUSTOMER ALAMAT_CUSTOMER KOTA_CUSTOMER TELEPON_CUSTOME KETERANGAN
------ ------------------------------ ----------------------------- --------------- --------------- ---------------
B-0001 TOKO WARNA JL ABC 234 BANDUNG (021) 432-6635
B-0002 TOKO SURYA JL ABC 309 BANDUNG (021) 432-6024
J-0001 TOKO KARISMA JL CIMANGGIS 34 JAKARTA (021) 856-4209
J-0002 TOKO AYU JL CIMANGGIS 12 JAKARTA (021) 856-1321
SQL>
== Table Pasok ==
SQL> select * from pasok;
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK KETERANGAN
---------- ------ ----- --------- ------------ ---------------
PAS-E001 ELK-01 EJ-01 01-JAN-02 8 xxx
PAS-E003 ELK-02 EJ-01 01-FEB-02 2
PAS-E005 ELK-02 EB-02 01-JAN-02 2
PAS-E006 ELK-03 EJ-01 03-MAR-02 5
PAS-E007 ELK-03 EJ-01 04-MAR-02 2
PAS-E008 ELK-03 EJ-01 03-MAR-02 3
PAS-E009 ELK-03 EB-01 13-MAR-02 4
PAS-E010 ELK-03 EB-01 13-MAR-02 3
PAS-E011 ELK-04 EB-01 22-APR-02 12
PAS-E012 ELK-04 EB-02 30-APR-02 9
10 rows selected.
SQL> select * from pasok order by JUMLAH_PASOK;
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK KETERANGAN
---------- ------ ----- --------- ------------ ---------------
PAS-E007 ELK-03 EJ-01 04-MAR-02 2
PAS-E005 ELK-02 EB-02 01-JAN-02 2
PAS-E003 ELK-02 EJ-01 01-FEB-02 2
PAS-E008 ELK-03 EJ-01 03-MAR-02 3
PAS-E010 ELK-03 EB-01 13-MAR-02 3
PAS-E009 ELK-03 EB-01 13-MAR-02 4
PAS-E006 ELK-03 EJ-01 03-MAR-02 5
PAS-E001 ELK-01 EJ-01 01-JAN-02 8 xxx
PAS-E012 ELK-04 EB-02 30-APR-02 9
PAS-E011 ELK-04 EB-01 22-APR-02 12
10 rows selected.
SQL> select * from pasok order by JUMLAH_PASOK desc;
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK KETERANGAN
---------- ------ ----- --------- ------------ ---------------
PAS-E011 ELK-04 EB-01 22-APR-02 12
PAS-E012 ELK-04 EB-02 30-APR-02 9
PAS-E001 ELK-01 EJ-01 01-JAN-02 8 xxx
PAS-E006 ELK-03 EJ-01 03-MAR-02 5
PAS-E009 ELK-03 EB-01 13-MAR-02 4
PAS-E010 ELK-03 EB-01 13-MAR-02 3
PAS-E008 ELK-03 EJ-01 03-MAR-02 3
PAS-E005 ELK-02 EB-02 01-JAN-02 2
PAS-E003 ELK-02 EJ-01 01-FEB-02 2
PAS-E007 ELK-03 EJ-01 04-MAR-02 2
10 rows selected.
SQL>
== 5.3 kriteria data ==
kriteria data adalah data yang akan ditampilkan merupakan data yang memenuhi kriteria yang ada dan ditentukan
== 5.3.1 kriteria dengan having ==
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SELECT field 1,...,field n, agregation_function
FROM table
GROUP BY field 1,...,field n
HAVING criteria_agregation_function
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
== pasok ==
SQL> select * from pasok;
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK KETERANGAN
---------- ------ ----- --------- ------------ ---------------
PAS-E001 ELK-01 EJ-01 01-JAN-02 8 xxx
PAS-E003 ELK-02 EJ-01 01-FEB-02 2
PAS-E005 ELK-02 EB-02 01-JAN-02 2
PAS-E006 ELK-03 EJ-01 03-MAR-02 5
PAS-E007 ELK-03 EJ-01 04-MAR-02 2
PAS-E008 ELK-03 EJ-01 03-MAR-02 3
PAS-E009 ELK-03 EB-01 13-MAR-02 4
PAS-E010 ELK-03 EB-01 13-MAR-02 3
PAS-E011 ELK-04 EB-01 22-APR-02 12
PAS-E012 ELK-04 EB-02 30-APR-02 9
10 rows selected.
SQL>
SQL> select KODE_SUPLIER, count(JUMLAH_PASOK) as CACAH_PASOK from pasok group by KODE_SUPLIER having count(*)=3;
KODE_ CACAH_PASOK
----- -----------
EB-01 3
SQL>
SQL> select KODE_SUPLIER, sum(JUMLAH_PASOK) as SUM_PASOK from pasok group by KOD
E_SUPLIER having sum(JUMLAH_PASOK) >15;
KODE_ SUM_PASOK
----- ----------
EB-01 19
EJ-01 20
SQL>
SQL> select KODE_SUPLIER, sum(JUMLAH_PASOK) as SUM_PASOK from pasok group by KOD
E_SUPLIER having sum(JUMLAH_PASOK) >5;
KODE_ SUM_PASOK
----- ----------
EB-02 11
EB-01 19
EJ-01 20
SQL>
== PEMBELIAN ==
SQL> select * from pembelian;
KODE_PEMBE KODE_B KODE_C TANGGAL_P JUMLAH_PEMBELIAN KETERANGAN
---------- ------ ------ --------- ---------------- ---------------
BEL-E001 ELK-01 J-0001 20-MAY-02 3 xxx
BEL-E002 ELK-01 J-0001 21-MAY-02 4
BEL-E003 ELK-01 J-0002 20-MAY-02 2
BEL-E004 ELK-01 B-0001 20-MAY-02 2
BEL-E005 ELK-01 B-0002 22-MAY-02 3
BEL-E006 ELK-02 J-0001 24-JUN-02 1
BEL-E007 ELK-02 J-0002 24-JUN-02 1
7 rows selected.
SQL>
SQL> select KODE_CUSTOMER, count(JUMLAH_PEMBELIAN) as CACAH_PEMBELIAN from pembelian group by KODE_CUSTOMER;
KODE_C CACAH_PEMBELIAN
------ ---------------
J-0001 3
J-0002 2
B-0001 1
B-0002 1
SQL> select KODE_CUSTOMER, count(JUMLAH_PEMBELIAN) as CACAH_PEMBELIAN from pembelian group by KODE_CUSTOMER having count(*)=4;
no rows selected
SQL>
SQL> select KODE_CUSTOMER, count(JUMLAH_PEMBELIAN) as CACAH_PEMBELIAN from pembelian group by KODE_CUSTOMER having count(*)=2;
KODE_C CACAH_PEMBELIAN
------ ---------------
J-0002 2
SQL> select KODE_CUSTOMER, count(JUMLAH_PEMBELIAN) as CACAH_PEMBELIAN from pembelian group by KODE_CUSTOMER having count(*)=3;
KODE_C CACAH_PEMBELIAN
------ ---------------
J-0001 3
SQL> select KODE_CUSTOMER, count(JUMLAH_PEMBELIAN) as CACAH_PEMBELIAN from pembelian group by KODE_CUSTOMER having count(*)=4;
no rows selected
SQL> select KODE_CUSTOMER, count(JUMLAH_PEMBELIAN) as CACAH_PEMBELIAN from pembelian group by KODE_CUSTOMER having count(*)=1;
KODE_C CACAH_PEMBELIAN
------ ---------------
B-0001 1
B-0002 1
SQL>
SQL> select KODE_CUSTOMER, sum(JUMLAH_PEMBELIAN) as TOTAL_PEMBELIAN from pembelian group by KODE_CUSTOMER;
KODE_C TOTAL_PEMBELIAN
------ ---------------
J-0001 8
J-0002 3
B-0001 2
B-0002 3
SQL>
SQL> select KODE_CUSTOMER, sum(JUMLAH_PEMBELIAN) as TOTAL_PEMBELIAN from pembelian group by KODE_CUSTOMER having sum(JUMLAH_PEMBELIAN) >15;
no rows selected
SQL> select KODE_CUSTOMER, sum(JUMLAH_PEMBELIAN) as TOTAL_PEMBELIAN from pembelian group by KODE_CUSTOMER having sum(JUMLAH_PEMBELIAN) >10;
no rows selected
SQL> select KODE_CUSTOMER, sum(JUMLAH_PEMBELIAN) as TOTAL_PEMBELIAN from pembelian group by KODE_CUSTOMER having sum(JUMLAH_PEMBELIAN) >5;
KODE_C TOTAL_PEMBELIAN
------ ---------------
J-0001 8
SQL> select KODE_CUSTOMER, sum(JUMLAH_PEMBELIAN) as TOTAL_PEMBELIAN from pembelian group by KODE_CUSTOMER having sum(JUMLAH_PEMBELIAN) >3;
KODE_C TOTAL_PEMBELIAN
------ ---------------
J-0001 8
SQL>
== 5.3.2 kriteria dengan where ==
== 5.3.2.1 comparison (=,<>,>,<,>= atau <=)
Comparison berfungsi untuk membandingkan dua nilai
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SELECT field 1,...,field n
FROM table
WHERE field =|<>|<|>|<=|>= ...
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
== pasok ==
SQL> select * from pasok;
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK KETERANGAN
---------- ------ ----- --------- ------------ ---------------
PAS-E001 ELK-01 EJ-01 01-JAN-02 8 xxx
PAS-E003 ELK-02 EJ-01 01-FEB-02 2
PAS-E005 ELK-02 EB-02 01-JAN-02 2
PAS-E006 ELK-03 EJ-01 03-MAR-02 5
PAS-E007 ELK-03 EJ-01 04-MAR-02 2
PAS-E008 ELK-03 EJ-01 03-MAR-02 3
PAS-E009 ELK-03 EB-01 13-MAR-02 4
PAS-E010 ELK-03 EB-01 13-MAR-02 3
PAS-E011 ELK-04 EB-01 22-APR-02 12
PAS-E012 ELK-04 EB-02 30-APR-02 9
10 rows selected.
SQL>
SQL> select * from pasok where JUMLAH_PASOK=3;
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK KETERANGAN
---------- ------ ----- --------- ------------ ---------------
PAS-E008 ELK-03 EJ-01 03-MAR-02 3
PAS-E010 ELK-03 EB-01 13-MAR-02 3
SQL> select * from pasok where JUMLAH_PASOK=9;
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK KETERANGAN
---------- ------ ----- --------- ------------ ---------------
PAS-E012 ELK-04 EB-02 30-APR-02 9
SQL> select * from pasok where JUMLAH_PASOK=2;
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK KETERANGAN
---------- ------ ----- --------- ------------ ---------------
PAS-E003 ELK-02 EJ-01 01-FEB-02 2
PAS-E005 ELK-02 EB-02 01-JAN-02 2
PAS-E007 ELK-03 EJ-01 04-MAR-02 2
SQL>
SQL> select * from pasok where JUMLAH_PASOK>=5;
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK KETERANGAN
---------- ------ ----- --------- ------------ ---------------
PAS-E001 ELK-01 EJ-01 01-JAN-02 8 xxx
PAS-E006 ELK-03 EJ-01 03-MAR-02 5
PAS-E011 ELK-04 EB-01 22-APR-02 12
PAS-E012 ELK-04 EB-02 30-APR-02 9
SQL> select * from pasok where JUMLAH_PASOK<=5;
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK KETERANGAN
---------- ------ ----- --------- ------------ ---------------
PAS-E003 ELK-02 EJ-01 01-FEB-02 2
PAS-E005 ELK-02 EB-02 01-JAN-02 2
PAS-E006 ELK-03 EJ-01 03-MAR-02 5
PAS-E007 ELK-03 EJ-01 04-MAR-02 2
PAS-E008 ELK-03 EJ-01 03-MAR-02 3
PAS-E009 ELK-03 EB-01 13-MAR-02 4
PAS-E010 ELK-03 EB-01 13-MAR-02 3
7 rows selected.
SQL> select * from pasok where JUMLAH_PASOK<=5;
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK KETERANGAN
---------- ------ ----- --------- ------------ ---------------
PAS-E003 ELK-02 EJ-01 01-FEB-02 2
PAS-E005 ELK-02 EB-02 01-JAN-02 2
PAS-E006 ELK-03 EJ-01 03-MAR-02 5
PAS-E007 ELK-03 EJ-01 04-MAR-02 2
PAS-E008 ELK-03 EJ-01 03-MAR-02 3
PAS-E009 ELK-03 EB-01 13-MAR-02 4
PAS-E010 ELK-03 EB-01 13-MAR-02 3
7 rows selected.
SQL>
SQL> select * from pasok where JUMLAH_PASOK<>12;
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK KETERANGAN
---------- ------ ----- --------- ------------ ---------------
PAS-E001 ELK-01 EJ-01 01-JAN-02 8 xxx
PAS-E003 ELK-02 EJ-01 01-FEB-02 2
PAS-E005 ELK-02 EB-02 01-JAN-02 2
PAS-E006 ELK-03 EJ-01 03-MAR-02 5
PAS-E007 ELK-03 EJ-01 04-MAR-02 2
PAS-E008 ELK-03 EJ-01 03-MAR-02 3
PAS-E009 ELK-03 EB-01 13-MAR-02 4
PAS-E010 ELK-03 EB-01 13-MAR-02 3
PAS-E012 ELK-04 EB-02 30-APR-02 9
9 rows selected.
SQL>
== barang ==
SQL> select * from barang;
KODE_B NAMA_BARANG SATUAN_BARANG STOK_BARANG KETERANGAN
------ ------------------------- -------------------- ----------- ---------------
ELK-01 RICE COOKER BUAH 20
ELK-02 LEMARI ES UNIT 8
ELK-03 TELEVISI UNIT 30
ELK-04 RADIO/TAPE BUAH 35
SQL>
SQL> select * from barang where STOK_BARANG>=20;
KODE_B NAMA_BARANG SATUAN_BARANG STOK_BARANG KETERANGAN
------ ------------------------- -------------------- ----------- ---------------
ELK-01 RICE COOKER BUAH 20
ELK-03 TELEVISI UNIT 30
ELK-04 RADIO/TAPE BUAH 35
SQL>
== pembelian ==
SQL> select * from pembelian;
KODE_PEMBE KODE_B KODE_C TANGGAL_P JUMLAH_PEMBELIAN KETERANGAN
---------- ------ ------ --------- ---------------- ---------------
BEL-E001 ELK-01 J-0001 20-MAY-02 3 xxx
BEL-E002 ELK-01 J-0001 21-MAY-02 4
BEL-E003 ELK-01 J-0002 20-MAY-02 2
BEL-E004 ELK-01 B-0001 20-MAY-02 2
BEL-E005 ELK-01 B-0002 22-MAY-02 3
BEL-E006 ELK-02 J-0001 24-JUN-02 1
BEL-E007 ELK-02 J-0002 24-JUN-02 1
7 rows selected.
SQL> select * from pembelian where JUMLAH_PEMBELIAN<>2;
KODE_PEMBE KODE_B KODE_C TANGGAL_P JUMLAH_PEMBELIAN KETERANGAN
---------- ------ ------ --------- ---------------- ---------------
BEL-E001 ELK-01 J-0001 20-MAY-02 3
BEL-E002 ELK-01 J-0001 21-MAY-02 4
BEL-E005 ELK-01 B-0002 22-MAY-02 3
BEL-E006 ELK-02 J-0001 24-JUN-02 1
BEL-E007 ELK-02 J-0002 24-JUN-02 1
BEL-E010 ELK-03 J-0001 20-JUN-02 5
BEL-E011 ELK-03 J-0002 02-JUL-02 4
BEL-E012 ELK-03 B-0001 04-JUL-02 6
BEL-E013 ELK-03 J-0001 10-JUL-02 5
BEL-E014 ELK-04 J-0002 15-JUL-02 12
BEL-E015 ELK-04 B-0002 17-JUL-02 15
11 rows selected.
SQL>
SQL> select * from pembelian where JUMLAH_PEMBELIAN>5;
KODE_PEMBE KODE_B KODE_C TANGGAL_P JUMLAH_PEMBELIAN KETERANGAN
---------- ------ ------ --------- ---------------- ---------------
BEL-E012 ELK-03 B-0001 04-JUL-02 6
BEL-E014 ELK-04 J-0002 15-JUL-02 12
BEL-E015 ELK-04 B-0002 17-JUL-02 15
SQL>
== 5.3.2.2 Between ==
digunakan untuk membandingkan apakah suatu nilai berada dalam range nilai tertentu
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SELECT field 1,...,field n
FROM TABLE
WHERE field between...and...
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
== pasok ==
SQL> select * from pasok;
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK KETERANGAN
---------- ------ ----- --------- ------------ ---------------
PAS-E001 ELK-01 EJ-01 01-JAN-02 8 xxx
PAS-E003 ELK-02 EJ-01 01-FEB-02 2
PAS-E005 ELK-02 EB-02 01-JAN-02 2
PAS-E006 ELK-03 EJ-01 03-MAR-02 5
PAS-E007 ELK-03 EJ-01 04-MAR-02 2
PAS-E008 ELK-03 EJ-01 03-MAR-02 3
PAS-E009 ELK-03 EB-01 13-MAR-02 4
PAS-E010 ELK-03 EB-01 13-MAR-02 3
PAS-E011 ELK-04 EB-01 22-APR-02 12
PAS-E012 ELK-04 EB-02 30-APR-02 9
10 rows selected.
SQL>
SQL> select * from pasok where JUMLAH_PASOK between 5 and 12 order by JUMLAH_PASOK;
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK KETERANGAN
---------- ------ ----- --------- ------------ ---------------
PAS-E006 ELK-03 EJ-01 03-MAR-02 5
PAS-E001 ELK-01 EJ-01 01-JAN-02 8 xxx
PAS-E012 ELK-04 EB-02 30-APR-02 9
PAS-E011 ELK-04 EB-01 22-APR-02 12
SQL>
== pembelian ==
SQL> select * from pembelian;
KODE_PEMBE KODE_B KODE_C TANGGAL_P JUMLAH_PEMBELIAN KETERANGAN
---------- ------ ------ --------- ---------------- ---------------
BEL-E001 ELK-01 J-0001 20-MAY-02 3
BEL-E002 ELK-01 J-0001 21-MAY-02 4
BEL-E003 ELK-01 J-0002 20-MAY-02 2
BEL-E004 ELK-01 B-0001 20-MAY-02 2
BEL-E005 ELK-01 B-0002 22-MAY-02 3
BEL-E006 ELK-02 J-0001 24-JUN-02 1
BEL-E007 ELK-02 J-0002 24-JUN-02 1
BEL-E008 ELK-02 B-0001 25-JUN-02 2
BEL-E009 ELK-02 B-0002 25-JUN-02 2
BEL-E010 ELK-03 J-0001 20-JUN-02 5
BEL-E011 ELK-03 J-0002 02-JUL-02 4
BEL-E012 ELK-03 B-0001 04-JUL-02 6
BEL-E013 ELK-03 J-0001 10-JUL-02 5
BEL-E014 ELK-04 J-0002 15-JUL-02 12
BEL-E015 ELK-04 B-0002 17-JUL-02 15
15 rows selected.
SQL>
SQL> select * from pembelian where JUMLAH_PEMBELIAN between 5 and 15;
KODE_PEMBE KODE_B KODE_C TANGGAL_P JUMLAH_PEMBELIAN KETERANGAN
---------- ------ ------ --------- ---------------- ---------------
BEL-E010 ELK-03 J-0001 20-JUN-02 5
BEL-E012 ELK-03 B-0001 04-JUL-02 6
BEL-E013 ELK-03 J-0001 10-JUL-02 5
BEL-E014 ELK-04 J-0002 15-JUL-02 12
BEL-E015 ELK-04 B-0002 17-JUL-02 15
SQL> select * from pembelian where JUMLAH_PEMBELIAN between 5 and 15 order by KODE_PEMBELIAN;
KODE_PEMBE KODE_B KODE_C TANGGAL_P JUMLAH_PEMBELIAN KETERANGAN
---------- ------ ------ --------- ---------------- ---------------
BEL-E010 ELK-03 J-0001 20-JUN-02 5
BEL-E012 ELK-03 B-0001 04-JUL-02 6
BEL-E013 ELK-03 J-0001 10-JUL-02 5
BEL-E014 ELK-04 J-0002 15-JUL-02 12
BEL-E015 ELK-04 B-0002 17-JUL-02 15
SQL>
== 5.3.2.3 In ==
digunakan untuk melakukan pengecekan apakah suatu nilai terdapat suatu himpunan tertentu
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SELECT name_field 1,...name_field n
FROM name_table
WHERE name_field IN(...,...,...)
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
== pasok ==
SQL> select * from pasok;
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK KETERANGAN
---------- ------ ----- --------- ------------ ---------------
PAS-E001 ELK-01 EJ-01 01-JAN-02 8 xxx
PAS-E003 ELK-02 EJ-01 01-FEB-02 2
PAS-E005 ELK-02 EB-02 01-JAN-02 2
PAS-E006 ELK-03 EJ-01 03-MAR-02 5
PAS-E007 ELK-03 EJ-01 04-MAR-02 2
PAS-E008 ELK-03 EJ-01 03-MAR-02 3
PAS-E009 ELK-03 EB-01 13-MAR-02 4
PAS-E010 ELK-03 EB-01 13-MAR-02 3
PAS-E011 ELK-04 EB-01 22-APR-02 12
PAS-E012 ELK-04 EB-02 30-APR-02 9
10 rows selected.
SQL>
SQL> select * from pasok where JUMLAH_PASOK in(2,8,12);
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK KETERANGAN
---------- ------ ----- --------- ------------ ---------------
PAS-E001 ELK-01 EJ-01 01-JAN-02 8 xxx
PAS-E003 ELK-02 EJ-01 01-FEB-02 2
PAS-E005 ELK-02 EB-02 01-JAN-02 2
PAS-E007 ELK-03 EJ-01 04-MAR-02 2
PAS-E011 ELK-04 EB-01 22-APR-02 12
SQL> select * from pasok where JUMLAH_PASOK in(2,8,100);
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK KETERANGAN
---------- ------ ----- --------- ------------ ---------------
PAS-E001 ELK-01 EJ-01 01-JAN-02 8 xxx
PAS-E003 ELK-02 EJ-01 01-FEB-02 2
PAS-E005 ELK-02 EB-02 01-JAN-02 2
PAS-E007 ELK-03 EJ-01 04-MAR-02 2
SQL>
== pembelian ==
SQL> select * from pembelian where JUMLAH_PEMBELIAN in(2,4,6,12);
KODE_PEMBE KODE_B KODE_C TANGGAL_P JUMLAH_PEMBELIAN KETERANGAN
---------- ------ ------ --------- ---------------- ---------------
BEL-E002 ELK-01 J-0001 21-MAY-02 4
BEL-E003 ELK-01 J-0002 20-MAY-02 2
BEL-E004 ELK-01 B-0001 20-MAY-02 2
BEL-E008 ELK-02 B-0001 25-JUN-02 2
BEL-E009 ELK-02 B-0002 25-JUN-02 2
BEL-E011 ELK-03 J-0002 02-JUL-02 4
BEL-E012 ELK-03 B-0001 04-JUL-02 6
BEL-E014 ELK-04 J-0002 15-JUL-02 12
8 rows selected.
SQL>
SQL> select * from pembelian where KODE_CUSTOMER in('J-0001','B-0002');
KODE_PEMBE KODE_B KODE_C TANGGAL_P JUMLAH_PEMBELIAN KETERANGAN
---------- ------ ------ --------- ---------------- ---------------
BEL-E001 ELK-01 J-0001 20-MAY-02 3
BEL-E002 ELK-01 J-0001 21-MAY-02 4
BEL-E005 ELK-01 B-0002 22-MAY-02 3
BEL-E006 ELK-02 J-0001 24-JUN-02 1
BEL-E009 ELK-02 B-0002 25-JUN-02 2
BEL-E010 ELK-03 J-0001 20-JUN-02 5
BEL-E013 ELK-03 J-0001 10-JUL-02 5
BEL-E015 ELK-04 B-0002 17-JUL-02 15
8 rows selected.
SQL>
== 5.3.2.4 like / not like ==
digunakan untuk membandingkan data dengan pola tertentu
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SELECT name_field 1,...name_field n
FROM name_table
WHERE name_field LIKE|NOT LIKE
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SQL> select * from customer;
KODE_C NAMA_CUSTOMER ALAMAT_CUSTOMER KOTA_CUSTOMER TELEPON_CUSTOME KETERANGAN
------ ------------------------------ ------------------------------ --------------- --------------- ---------------
J-0001 TOKO KARISMA JL CIMANGGIS 34 JAKARTA (021) 856-4209
J-0002 TOKO AYU JL CIMANGGIS 12 JAKARTA (021) 856-1321
B-0001 TOKO WARNA JL ABC 234 BANDUNG (021) 432-6635
B-0002 TOKO SURYA JL ABC 309 BANDUNG (021) 432-6024
SQL>
SQL> select * from customer where ALAMAT_CUSTOMER like '%CIM%';
KODE_C NAMA_CUSTOMER ALAMAT_CUSTOMER KOTA_CUSTOMER TELEPON_CUSTOME KETERANGAN
------ ------------------------------ ------------------------------ --------------- --------------- ---------------
J-0001 TOKO KARISMA JL CIMANGGIS 34 JAKARTA (021) 856-4209
J-0002 TOKO AYU JL CIMANGGIS 12 JAKARTA (021) 856-1321
SQL>
SQL> select * from customer where ALAMAT_CUSTOMER not like '%CIM%';
KODE_C NAMA_CUSTOMER ALAMAT_CUSTOMER KOTA_CUSTOMER TELEPON_CUSTOME KETERANGAN
------ ------------------------------ ------------------------------ --------------- --------------- ---------------
B-0001 TOKO WARNA JL ABC 234 BANDUNG (021) 432-6635
B-0002 TOKO SURYA JL ABC 309 BANDUNG (021) 432-6024
SQL>
== suplier ==
SQL> select * from suplier;
KODE_ NAMA_SUPLIER ALAMAT_SUPLIER KOTA_SUPLIER TELEPON_SUPLIER KETERANGAN
----- ------------------------------ ------------------------------ --------------- --------------- ---------------
EJ-01 PT ACTRON JL THAMRIN 12 JAKARTA (021) 850-2301
EB-01 PT ULTRASOUND JL SUKARNO HATTA 103 BANDUNG (021) 522-3305
EB-02 PT SUPERTRON JL INDUSTRI 37 BANDUNG (021) 660-4091
EJ-02 PT MULYA ELEKTRONIK JL SUDIRMAN 45 JAKARTA (021) 855-4262
SQL>
SQL> select * from suplier where NAMA_SUPLIER like '%TRON';
KODE_ NAMA_SUPLIER ALAMAT_SUPLIER KOTA_SUPLIER TELEPON_SUPLIER KETERANGAN
----- ------------------------------ ------------------------------ --------------- --------------- ---------------
EJ-01 PT ACTRON JL THAMRIN 12 JAKARTA (021) 850-2301
EB-02 PT SUPERTRON JL INDUSTRI 37 BANDUNG (021) 660-4091
SQL> select * from suplier where NAMA_SUPLIER not like '%TRON';
KODE_ NAMA_SUPLIER ALAMAT_SUPLIER KOTA_SUPLIER TELEPON_SUPLIER KETERANGAN
----- ------------------------------ ------------------------------ --------------- --------------- ---------------
EB-01 PT ULTRASOUND JL SUKARNO HATTA 103 BANDUNG (021) 522-3305
EJ-02 PT MULYA ELEKTRONIK JL SUDIRMAN 45 JAKARTA (021) 855-4262
SQL>
SQL> select * from suplier where NAMA_SUPLIER like '%TRON';
KODE_ NAMA_SUPLIER ALAMAT_SUPLIER KOTA_SUPLIER TELEPON_SUPLIER KETERANGAN
----- ------------------------------ ------------------------------ --------------- --------------- ---------------
EJ-01 PT ACTRON JL THAMRIN 12 JAKARTA (021) 850-2301
EB-02 PT SUPERTRON JL INDUSTRI 37 BANDUNG (021) 660-4091
SQL> select * from suplier where NAMA_SUPLIER like '%TRON%';
KODE_ NAMA_SUPLIER ALAMAT_SUPLIER KOTA_SUPLIER TELEPON_SUPLIER KETERANGAN
----- ------------------------------ ------------------------------ --------------- --------------- ---------------
EJ-01 PT ACTRON JL THAMRIN 12 JAKARTA (021) 850-2301
EB-02 PT SUPERTRON JL INDUSTRI 37 BANDUNG (021) 660-4091
EJ-02 PT MULYA ELEKTRONIK JL SUDIRMAN 45 JAKARTA (021) 855-4262
SQL>
== barang ==
SQL> select * from barang;
KODE_B NAMA_BARANG SATUAN_BARANG STOK_BARANG KETERANGAN
------ ------------------------- -------------------- ----------- ---------------
ELK-01 RICE COOKER BUAH 20
ELK-02 LEMARI ES UNIT 8
ELK-03 TELEVISI UNIT 30
ELK-04 RADIO/TAPE BUAH 35
SQL> select * from barang where NAMA_BARANG like 'R%';
KODE_B NAMA_BARANG SATUAN_BARANG STOK_BARANG KETERANGAN
------ ------------------------- -------------------- ----------- ---------------
ELK-01 RICE COOKER BUAH 20
ELK-04 RADIO/TAPE BUAH 35
SQL> select * from barang where NAMA_BARANG not like 'R%';
KODE_B NAMA_BARANG SATUAN_BARANG STOK_BARANG KETERANGAN
------ ------------------------- -------------------- ----------- ---------------
ELK-02 LEMARI ES UNIT 8
ELK-03 TELEVISI UNIT 30
SQL>
SQL> select * from barang where NAMA_BARANG not like '%R%';
KODE_B NAMA_BARANG SATUAN_BARANG STOK_BARANG KETERANGAN
------ ------------------------- -------------------- ----------- ---------------
ELK-03 TELEVISI UNIT 30
SQL>
Referensi
- Menguasai Oracle, SQL & PL/SQL karya Imam Heryanto, Budi Raharjo, Penerbit Informatika Bandung
- blog http://fakta-dan-unik.blogspot.com
- http://fakta-dan-unik.blogspot.com/search/label/SQL
- http://fakta-dan-unik.blogspot.com/search/label/Oracle
source http://fakta-dan-unik.blogspot.com & http://teguhtriharto.blogspot.com