Pemrograman Sistem Basis Data ( Oracle )

Create table barang (
KODE_BARANG char(6),
NAMA_BARANG varchar2(25),
SATUAN_BARANG varchar2(20),
STOK_BARANG number(4),
Constraint pk_barang primary key (KODE_BARANG)
);

Create table supplier (
KODE_SUPPLIER char(5),
NAMA_SUPPLIER varchar2(30),
ALAMAT_SUPPLIER varchar2(30),
KOTA_SUPPLIER varchar2(15),
TELEPON_SUPPLIER varchar2(15),
Constraint pk_supplier primary key (KODE_SUPPLIER)
);

Create table customer (
KODE_CUSTOMER char(6),
NAMA_CUSTOMER varchar2(30),
ALAMAT_CUSTOMER varchar2(30),
KOTA_CUSTOMER varchar2(15),
TELEPON_CUSTOMER varchar2(15),
Constraint pk_customer primary key (KODE_CUSTOMER)
);

Create table pasok (
KODE_PASOK char(10),
KODE_BARANG char(6),
KODE_SUPPLIER char(5),
TANGGAL_PASOK date,
JUMLAH_PASOK number(4),

Constraint pk_pasok primary key (KODE_PASOK,
KODE_BARANG, KODE_SUPPLIER),
Constraint fk_pasok_barang foreign key (KODE_BARANG)
references barang (KODE_BARANG),
Constraint fk_pasok_supplier foreign key (KODE_SUPPLIER)
references supplier (KODE_SUPPLIER)
);

Create table pembelian (
KODE_PEMBELIAN char(10),
KODE_BARANG char(6),
KODE_CUSTOMER char(6),
TANGGAL_PEMBELIAN date,
JUMLAH_PEMBELIAN number(4),
Constraint pk_pembelian primary key (KODE_PEMBELIAN,
KODE_BARANG, KODE_CUSTOMER),
Constraint fk_pembelian_barang foreign key (KODE_BARANG)
references barang (KODE_BARANG),
Constraint fk_pembelian_customer foreign key (KODE_CUSTOMER)
references customer (KODE_CUSTOMER)
);

select * from tab;

alter table barang modify satuan_barang char(5);
alter table barang add ( keterangan varchar2(15) );
alter table barang modify ( stok_barang number(2) );
alter table barang add ( stok_minimun number(4) );

desc barang;

Menampilkan Struktur Tabel
Desc barang;
Desc supplier;
Desc customer;

insert into barang values('ELK-01','Rice Cooker','Buah','20');
insert into barang values('ELK-02','Lemari ES','Buah','20');
insert into barang values('ELK-03','Televisi','Buah','20');
insert into barang values('ELK-04','Radio/Tipe','Buah','20');
delete from barang where KODE_BARANG='ELK-01';

insert into supplier values('EJ-01','PT.Actron','Jl.Thamrin 12','Jakarta','021-8552301');
insert into supplier values('EJ-02','PT.Mulya Elektron','Jl.Sudirman 12','Jakarta','021-8554262');
insert into supplier values('EB-01','PT.Ultrasound','Jl.Sukarno-hatta','Bandung','022-5223305');
insert into supplier values('EB-02','PT.Supertrond','Jl.Industri 37','Bandung','022-6604091');
delete from supplier where KODE_SUPPLIER='EJ-03';

select * from supplier;

insert into customer values('J-0001','TOKO KARISMA','Jl.Cimanggis 34','Jakarta','021-856-4209');
insert into customer values('J-0002','TOKO AYU','Jl.Dimanggis 12','Jakarta','021-856-4209');
insert into customer values('B-0001','TOKO SURYA','Jl.ABC 234','Jakarta','021-856-4209');
insert into customer values('B-0002','TOKO WARNA','Jl.ABC 309 34','Jakarta','021-856-4209');

select * from customer;

insert into pasok values('PAS-E001','ELK-01','EJ-01','01-JAN-02','8');
insert into pasok values('PAS-E002','ELK-01','EJ-02','01-JAN-02','5');
insert into pasok values('PAS-E003','ELK-02','EJ-01','01-FEB-02','2');
insert into pasok values('PAS-E004','ELK-02','EJ-02','02-FEB-02','3');
insert into pasok values('PAS-E005','ELK-02','EB-02','01-JAN-02','2');
insert into pasok values('PAS-E006','ELK-03','EJ-01','03-MAR-02','5');
insert into pasok values('PAS-E007','ELK-03','EJ-01','04-MAR-02','2');
insert into pasok values('PAS-E008','ELK-03','EJ-02','03-MAR-02','3');
insert into pasok values('PAS-E009','ELK-03','EB-01','13-MAR-02','4');
insert into pasok values('PAS-E010','ELK-03','EB-02','13-MAR-02','3');
insert into pasok values('PAS-E011','ELK-04','EB-01','22-APR-02','12');
insert into pasok values('PAS-E012','ELK-04','EB-02','30-APR-02','9');

select * from pasok;

insert into pembelian values('BEL-E001','ELK-01','J-0001','20-MAY-02','3');
insert into pembelian values('BEL-E002','ELK-01','J-0001','21-MAY-02','4');
insert into pembelian values('BEL-E003','ELK-01','J-0002','20-MAY-02','2');
insert into pembelian values('BEL-E004','ELK-01','B-0001','20-MAY-02','2');
insert into pembelian values('BEL-E005','ELK-01','B-0002','22-MAY-02','3');
insert into pembelian values('BEL-E006','ELK-02','J-0001','24-MAY-02','1');
insert into pembelian values('BEL-E007','ELK-02','J-0002','24-JUN-02','1');
insert into pembelian values('BEL-E008','ELK-02','B-0001','25-JUN-02','2');
insert into pembelian values('BEL-E009','ELK-02','B-0002','25-JUN-02','2');
insert into pembelian values('BEL-E010','ELK-02','J-0001','20-JUN-02','5');
insert into pembelian values('BEL-E011','ELK-03','J-0002','02-JUL-02','4');
insert into pembelian values('BEL-E012','ELK-03','B-0001','04-JUL-02','6');
insert into pembelian values('BEL-E013','ELK-03','B-0002','10-JUL-02','5');
insert into pembelian values('BEL-E014','ELK-03','J-0001','15-JUL-02','12');
insert into pembelian values('BEL-E015','ELK-03','B-0002','17-JUL-02','3');

update barang set satuan_barang = 'unit' where kode_barang = 'ELK-04';
update supplier set nama_supplier = 'Jaya Nusa',alamat_supplier = 'Jl. Damar 69',kota_supplier = 'padang',telepon_supplier = '0751-28984' where kode_supplier='EJ-01';
update customer set nama_customer = 'Toko Awak',alamat_customer = 'Jl. Kito 21',kota_customer = 'padang',telepon_customer = '0751-33057' where kode_customer='J-0002';

create view v_customer as select * from customer;
select * from v_customer;

select min(jumlah_pasok) from pasok order by kode_barang;

select min(jumlah_pembelian) from pembelian order by kode_customer;

select kode_barang as kd_barang, min(jumlah_pasok) as minimum from pasok group by kode_barang;

select kode_barang as kd_barang, max(jumlah_pasok) as maximum from pasok group by kode_barang;

select sum (jumlah_pembelian) from pembelian;
select count(jumlah_pembelian) from pembelian;

select kode_customer, sum(jumlah_pembelian) as jumlah_pembelian from pembelian group by kode_customer;

select kode_barang,kode_customer, sum(jumlah_pembelian) as jumlah_pembelian from pembelian group by kode_barang,kode_customer;

select kode_barang as kd_barang, count(jumlah_pasok) as cacah_pasok from pasok group by kode_barang;

select kode_barang as kd_barang, kode_supplier as kd_sup, count(jumlah_pasok) as cacah_pasok from pasok group by kode_barang,kode_supplier;

select kode_supplier, count(jumlah_pasok) as cacah_pasok from pasok group by kode_supplier having count(*)=2;

select kode_customer, sum(jumlah_pembelian) as total_pembelian from pembelian group by kode_customer having sum(jumlah_pembelian) > 15;

select * from pasok where jumlah_pasok = 2;

select * from barang where stok_barang >= 20;

select * from pasok where jumlah_pasok between 5 and 12 order by jumlah_pasok;

select * from pasok where jumlah_pasok in(2,8,12) order by jumlah_pasok;

select * from customer where kota_customer like '%Jak%';