Sistem Basis Data : SQL Penjualan

1. MEMBUAT TABEL DAN SEQUENCE

1. Membuat Tabel Kategori Barang

create table kat_barang (idkat number primary key, namakat varchar(50) not null, deskket varchar(100));

2. Membuat Tabel Barang

create table barang (kdbrg varchar(6) primary key, namabrg varchar(50) not null, hrgbrg long, idkat number not null, stokbrg number not null);

3. Membuat Tabel Pelanggan

create table pelanggan (idplgn varchar(6) primary key, namaplgn varchar(50) not null, almtplgn varchar(100), noktkplgn varchar(13));

4. Membuat Tabel Penjual
   
   

create table penjual (idpjl varchar(6) primary key, namapjl varchar(50) not null, almtpjl varchar(100), noktkpjl varchar(13));

5. Membuat Tabel Faktur

create table faktur (kdfaktur varchar(6) primary key, idpjl varchar(6) not null, idplgn varchar(6), tglfaktur date not null, wktfaktur varchar(5) not null, totfaktur long not null);

6. Membuat Tabel Detail Penjualan

create table detail_penjualan (iddtl number primary key, kdfaktur varchar(6) not null, kdbrg varchar(6) not null, qty integer not null);

7. Membuat Sequence Detail Penjualan

create sequence detail_penjualan_seq

start with 1

maxvalue 999999999999999999999999999

minvalue 1

nocycle

nocache

noorder;

2. INPUT, UPDATE, DELETE

1. Input Data kategori barang

insert into kat_barang (idkat, namakat, deskkat)

values('1', 'CD/DVD', 'CD/DVD blank read, read-write');

insert into kat_barang (idkat, namakat, deskkat)

values('2', 'Flashdisk', 'Flashdisk 1gb,2gb,4gb,8gb,16gb');

insert into kat_barang (idkat, namakat, deskkat)

values('3', 'Peripheral', 'Perlengkapan komputer');

insert into kat_barang (idkat, namakat, deskkat)

values('4', 'Notebook', 'Notebook / Laptop');

insert into kat_barang (idkat, namakat, deskkat)

values('5', 'Netbook', 'Netbook');

insert into kat_barang (idkat, namakat, deskkat)

values('6', 'Desktop PC', 'Komputer desktop');

2. Input Data Barang

insert into barang (kdbrg, namabrg, hrgbrg, idkat, stokbrg)

values('CDMX01', 'CD R Matrix', 2500, 1, 200);

insert into barang (kdbrg, namabrg, hrgbrg, idkat, stokbrg)

values('DVBQ01', 'DVD R Ben-Q', 3500, 1, 200);

insert into barang (kdbrg, namabrg, hrgbrg, idkat, stokbrg)

values('FDKS05', 'Flashdisk Kingstone 4 GB', 70000, 2, 20);

insert into barang (kdbrg, namabrg, hrgbrg, idkat, stokbrg)

values('FDHP01', 'Flashdisk HP v155w 8 GB', 140000, 2, 25);

insert into barang (kdbrg, namabrg, hrgbrg, idkat, stokbrg)

values('MSPT03', 'Mouse Protouch', 36000, 3, 20);

insert into barang (kdbrg, namabrg, hrgbrg, idkat, stokbrg)

values('KBLT03', 'Keyboard Logitech standard ', 25000, 3, 10);

insert into barang (kdbrg, namabrg, hrgbrg, idkat, stokbrg)

values('ACR001', 'Acer Aspire 4315', 3500000, 4, 3);

insert into barang (kdbrg, namabrg, hrgbrg, idkat, stokbrg)

values('ACR002', 'Acer Aspire One', 3000000, 5, 5);

insert into barang (kdbrg, namabrg, hrgbrg, idkat, stokbrg)

values('KOM001', 'Komputer Paket 1 Ekonomis', 1200000, 6, 2);

3. Input Data Pelanggan

insert into pelanggan (idplgn, namaplgn, almtplgn, noktkplgn)

values('TK0001', 'Toko Komputer Pantang Rusak', 'jl. Naripan no.23 Bandung', '0224235675');

insert into pelanggan (idplgn, namaplgn, almtplgn, noktkplgn)

values('KT0013', 'Kantor Percetakan buku Michan', 'jl. Sunda no.13 Bandung', '087821948748');

4. Input data Penjual

insert into penjual (idpjl, namapjl, almtpjl, noktkpjl)

values('KSR001', 'Ichan', 'jl. Girimukti no.14 Bandung', '08562004839');

insert into penjual (idpjl, namapjl, almtpjl, noktkpjl)

values('MNJ001', 'Ihsan Fauzi Rahman', 'jl. Pajagalan no.13 Cihampelas Bandung', '02276083330');

5. Input data Penjualan

insert into faktur (kdfaktur , idpjl , idplgn , tglfaktur , wktfaktur , totfaktur)

values ('JL0001', 'KSR001', 'TK0001', '05/11/2010', '14:23', 1000000);

insert into detail_penjualan (iddtl , kdfaktur , kdbrg, qty)

values (detail_penjualan_seq.nexval,'JL0001','FDHP01',5);

insert into detail_penjualan (iddtl , kdfaktur , kdbrg, qty)

values (detail_penjualan_seq.nexval,'JL0001','CDMX01',120);

6. Update Stok Barang ketika melakukan penjualan (opsi 1)

update barang set stokbrg = 20

where kdbrg = 'FDHP01';

update barang set stokbrg = 80

where kdbrg = 'CDMX01';

7. Trigger Update stok barang setelah penjualan (opsi 2)

create or replace trigger update_barang

after update of iddtl, qty on detail_penjualan

for each row

begin

update_stokbrg (:barang.stokbrg,:detail_penjualan.qty);

end;

update_stokbrg (:barang.stokbrg,:detail_penjualan.qty)

barang.stokbrg = barang.stokbrg-detail_penjualan.qty

where barang.kdbrg = detail_penjualan.kdbrg;

3. QUERY MENAMPILKAN DATA

1. Menampilkan data kat_barang

select k.idkat as ID, k.namakat as Nama, k.deskkat as Deskripsi

from kat_barang k;

2. Menampilkan data Barang

select b.kdbrg as Kode_Barang, b.namabrg as Nama_Barang, b.hrgbrg as Harga, b.stokbrg as Stok

from barang b;

3. Menampilkan data Pelanggan

select p.idplgn as ID, p.namaplgn as Nama_Pelanggan, p.almtplgn as Alamat, p.noktkplgn as No_Kontak

from pelanggan p;

4. Menampilkan data Penjual

select pj.idpjl as ID, pj.namapjl as Nama_Penjual, pj.almtpjl as Alamat, pj.noktkpjl as No_Kontak

from penjual pj;

5. Menampilkan data Faktur

select f.kdfaktur as Kode_Faktur, f.idpjl as ID_Penjual, f.idplgn as ID_Pelanggan, f.tglfaktur as Tanggal, f.wktfaktur as Waktu, f.totfaktur as Total

from faktur f;

6. Menampilkan Seluruh Transaksi

select j.iddtl as ID, j.kdfaktur as Kode_Faktur, j.kdbrg as Kode_Barang, b.namabrg as Nama_Barang, j.qty as Qty, b.hrgbrg as Harga, (b.hrgbrg * j.qty) as Jumlah

from detail_penjualan j, barang b

where j.kdbrg = b.kdbrg

order by j.iddtl;

7. Laporan transaksi tanggal dari – sampai

select j.iddtl as ID, j.kdfaktur as Kode_Faktur, f.tglfaktur as Tanggal, f.wktfaktur as Waktu, j.kdbrg as Kode_Barang, b.namabrg as Nama_Barang, j.qty as Qty, b.hrgbrg as Harga, (b.hrgbrg * j.qty) as Jumlah

from detail_penjualan j, barang b, faktur f

where j.kdbrg = b.kdbrg and

j.kdfaktur = f.kdfaktur

order by f.tglfaktur

limit tglawal,tglakhir;