CREATE DATABASE ud_jaya_makmur
USE ud_jaya_makmur
create table stok(KodeBrg char (6) primary key NOT NULL, NamaBarang varchar(25),StokAwal int (5),Harga varchar(7),KodeSup varchar(3))
create table Supplier(KodeSup char (3) primary key NOT NULL, NamaSupplier varchar(25))
create table Pembelian(Kodebrg char (6) , Jumlah int(4))
create table Penjualan(KodeBrg char (6), Jumlah int(4),HargaJual int(7))
insert into stok values('A-100','Sabun Colek','200','1500','UN1')
insert into stok values('A-200','Sabun Lux','300','1250','WG1')
insert into stok values('A-300','Rinso Detergen Bubuk','100','2200','UN1')
insert into stok values('A-400','Soklin Detergen Bubuk','150','11500','ID2')
insert into stok values('A-500','Molto Pewangi Green','400','5500','WG1')
insert into stok values('A-600','Pewangi Ruangan Fresh','600','4000','ID2')
insert into supplier values('UN1','Unilever')
insert into supplier values('WG1','Wings')
insert into supplier values('ID2','Indofood')
insert into pembelian values('A-100','500')
insert into pembelian values('A-200','400')
insert into pembelian values('A-300','0')
insert into pembelian values('A-400','600')
insert into pembelian values('A-500','200')
insert into pembelian values('A-600','0')
insert into pembelian values('A-100','500')
insert into pembelian values('A-200','400')
insert into Penjualan values('A-100','300','2000')
insert into Penjualan values('A-200','200','1500')
insert into Penjualan values('A-300','50','2500')
insert into Penjualan values('A-400','100','12500')
insert into Penjualan values('A-500','100','6000')
insert into Penjualan values('A-600','400','5000')
SELECT pembelian.KodeBrg,stok.NamaBarang,Pembelian.Jumlah,stok.harga FROM pembelian,stok WHERE pembelian.KodeBrg=stok.KodeBrg
SELECT stok.KodeBrg,stok.NamaBarang,pembelian.Jumlah,penjualan.HargaJual from stok,pembelian,penjualan WHERE stok.KodeBrg=pembelian.KodeBrg and stok.KodeBrg=penjualan.KodeBrg
SELECT stok.KodeBrg,stok.NamaBarang,pembelian.Jumlah,penjualan.HargaJual,supplier.NamaSupplier
FROM stok,pembelian,penjualan,supplier
WHERE stok.KodeBrg=pembelian.KodeBrg
and stok.KodeBrg=penjualan.KodeBrg and stok.KodeSup=supplier.KodeSup
Merubah yang pertama namanya Harga di ganti Harga Beli
SELECT stok.KodeBrg,stok.NamaBarang,pembelian.Jumlah,stok.Harga As HargaBeli,penjualan.HargaJual,supplier.NamaSupplier
FROM stok,pembelian,penjualan,supplier
WHERE stok.KodeBrg=pembelian.KodeBrg
and stok.KodeBrg=penjualan.KodeBrg and stok.KodeSup=supplier.KodeSup
Menampilkan isi table stok yang kusus namanya barang sabun
SELECT KodeBrg,NamaBarang,StokAwal,Harga,KodeSup from stok where NamaBarang Like 'Sabun%'
Menampilkan isi table stok yang kusus namanya barang Detergen
SELECT KodeBrg,NamaBarang,StokAwal,Harga,KodeSup from stok where NamaBarang Like '%Detergen%'
STOK AWAL
SELECT A.NamaSupplier,Sum(B.Stokawal) As totalStokawal
from supplier A,Stok B
where A.KodeSup=B.KodeSup group by NamaSupplier
Total Pembelian
SELECTsupplier.NamaSupplier, sum(pembelian.Jumlah) As totalPembelian
FROM supplier,pembelian,stok
WHERE supplier.KodeSup=stok.KodeSup and stok.KodeBrg=pembelian.KodeBrg group by NamaSupplier
Total Penjualan
SELECT supplier.NamaSupplier, sum(penjualan.HargaJual) As totalPenjualan
FROM supplier,penjualan,stok
WHERE supplier.KodeSup=stok.KodeSup and stok.KodeBrg=penjualan.KodeBrg group by NamaSupplier
Stok Awal * Harga
SELECTtKodeBrg,NamaBarang,StokAwal,Harga,(StokAwal*Harga) As TotalHarga
FROM stok
pembelian.jumlah diganti Beli
KodeBrg,NamaBarang,StokAwal,Beli(field jumlah diganti menjadi beli pada tabel pembelian)
SELECT stok.KodeBrg,stok.NamaBarang,stok.StokAwal,pembelian.jumlah As beli
FROM stok,pembelian
WHERE stok.KodeBrg=pembelian.KodeBrg
Penjualan.jumlah diganti Jual
KodeBrg,NamaBarang,StokAwal,Beli(field jumlah diganti menjadi beli pada tabel pembelian)
SELECT stok.KodeBrg,stok.NamaBarang,stok.StokAwal,penjualan.jumlah As jual
FROMstok,penjualan
WHERE stok.KodeBrg=penjualan.KodeBrg
STOK AKHIR
SELECT stok.KodeBrg,stok.NamaBarang,stok.StokAwal,pembelian.Jumlah As Beli,penjualan.jumlah As Jual,(StokAwal+pembelian.Jumlah-penjualan.Jumlah) As stokakhir
FROM stok,pembelian,penjualan
WHERE stok.KodeBrg = pembelian.KodeBrg and stok.KodeBrg = penjualan.KodeBrg
1 komentar:
mantab gan
Posting Komentar