ShoutMix chat widget

Minggu, 15 April 2012

Latihan BDD


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:

Unknown mengatakan...

mantab gan

Posting Komentar

 
Design by Free WordPress Themes | Bloggerized by Lasantha - Premium Blogger Themes | Online Project management