Silahkan masukkan instruksi SQL untuk:
- Mengcreate TABEL Transaksi SO Header (DDL)
– Mengcreate TABEL Transaksi SO Detail (DDL)
- Memasukkan Data pada Tabel SO Header dan SO Detail (DML)
Tuliskan instruksinya dibawah ini
melalui comment
Blogged with the Flock Browser
Tags: pieceof code
CREATE TABLE SalesPerson (
KdSales VARCHAR2(5) NOT NULL,
NmSales CHAR(50) NOT NULL ,
AlamatSales CHAR(300),
TelpSales CHAR(30),
PRIMARY KEY (“KDSALES”)
)
Insert Into SalesPerson VALUES (‘SP001′, ‘Merinda’, ‘Jl. Sumur bor’, ‘654654897′);
Insert Into SalesPerson VALUES (‘SP002′, ‘Janet’, ‘Jl. pisang batu’, ‘4567894′);
Insert Into SalesPerson VALUES (‘SP003′, ‘Barel’, ‘Jl. kemakmuran’, ‘146549872′);
create table MsTrans (
Tanggal date,
IDCustomer varchar2(20) constraint pk_fk3 references MsCustomer(IDCustomer),
IDBarang varchar2(20) constraint pk_fk4 references MsBarang(IDBarang),
Qty number )
yg diatas buat salesperson
jd create yg insert yg buat sales order yg dibawah ini :
CREATE TABLE SO_Header (
KdTrans VARCHAR2(5) NOT NULL,
KdCust VARCHAR2(5) NOT NULL,
Tgl DATE NOT NULL ,
PRIMARY KEY (“KDTRANS”, “KDCUST”),
FOREIGN KEY (“KDCUST”) REFERENCES CUSTOMER (“KDCUST”)
)
CREATE TABLE SO_Detail (
KdTrans VARCHAR2(5) NOT NULL,
KDPRODUK VARCHAR2(5) NOT NULL,
Qty NUMBER NOT NULL,
Harga NUMBER NOT NULL,
PRIMARY KEY (“KDTRANS”, “KDPRODUK”),
FOREIGN KEY (“KDPRODUK”) REFERENCES PRODUK (“KDPRODUK”)
)
Saya mau beri komentar sedikit.
Sebaiknya kita gunakan standarisasi. Contohnya kalau untuk master standardnya gunakan kode misalnya kode produk, tapi kalau untuk transaksi gunakan nomor, misalnya nomor sales order (no So)
Cheers,
Johan Setiawan
PS: ayo yang lain ditunggu code-nya!!
saya berasumsi transaksi terjadi tiap hari, jadi ada hari dimana tidak terjadi transaksi, jadi random dari 0-10 setiap hari.
CREATE TABLE SO_Header (
No_SO CHAR(7) NOT NULL,
KdCust VARCHAR2(5) NOT NULL,
Tgl DATE NOT NULL ,
PRIMARY KEY (“NO_SO”),
FOREIGN KEY (“KDCUST”) REFERENCES CUSTOMER (“KDCUST”)
);
CREATE TABLE SO_Detail (
No_SO CHAR(7) NOT NULL,
KDPRODUK VARCHAR2(5) NOT NULL,
Qty NUMBER NOT NULL,
Harga NUMBER NOT NULL,
PRIMARY KEY (“NO_SO”, “KDPRODUK”),
FOREIGN KEY (“KDPRODUK”) REFERENCES PRODUK (“KDPRODUK”),
FOREIGN KEY (“NO_SO”) REFERENCES SO_HEADER (“NO_SO”)
);
DECLARE
nPeople NUMBER;
nTrans NUMBER;
jenis NUMBER;
k INT;
flag INT;
kodeProduk VARCHAR2(5);
kodeBeli VARCHAR2(7);
tempKode INT;
kodeCustomer VARCHAR2(5);
tempKodeCustomer INT;
j INT;
jmlhBrs INT;
currDate DATE;
qty NUMBER;
harga NUMBER;
beliKode INT;
BEGIN
currDate := ‘1-jan-2008′;
beliKode:=1;
WHILE beliKode < 1000 LOOP
nPeople := mod(trunc(DBMS_RANDOM.VALUE *10), 11);
IF beliKode < 10 THEN
kodeBeli := ‘SO0000′ || to_char(beliKode);
ELSIF beliKode<100 THEN
kodeBeli := ‘SO000′ || to_char(beliKode);
ELSIF beliKode<1000 THEN
kodeBeli := ‘SO00′ || to_char(beliKode);
ELSIF beliKode<10000 THEN
kodeBeli := ‘SO0′ || to_char(beliKode);
END IF;
j:= 1;
WHILE j <= nPeople LOOP
tempKodeCustomer := mod(trunc(DBMS_RANDOM.VALUE *10), 10) + 1;
IF tempKodeCustomer < 10 THEN
kodeCustomer := ‘C000′ || to_char(tempKodeCustomer);
ELSIF tempKodeCustomer < 100 THEN
kodeCustomer := ‘C00′ || to_char(tempKodeCustomer);
END IF;
SELECT count(*) INTO tempKode FROM SO_Detail WHERE ‘Tgl’=currDate AND ‘KDCUST’=kodeCustomer;
IF tempKode < 1 THEN
INSERT INTO SO_Header VALUES (kodeBeli, kodeCustomer, currDate);
nTrans := mod(trunc(DBMS_RANDOM.VALUE *10), 4) + 1;
k:=1;
dbms_output.put_line(‘Transaksi’ || nTrans);
WHILE k <= nTrans LOOP
jenis := DBMS_RANDOM.VALUE;
flag:=0;
IF jenis <= 0.25 then
kodeProduk:=’P0001′;
ELSIF jenis <= 0.5 then
kodeProduk:=’P0002′;
ELSIF jenis <= 0.75 then
kodeProduk:=’P0003′;
ELSE
kodeProduk:=’P0004′;
END IF;
SELECT count(*) INTO jmlhBrs FROM SO_Detail WHERE No_SO=kodeBeli AND kdProduk=kodeProduk;
IF jmlhBrs < 1 THEN
dbms_output.put_line(‘Transaksi’ || kodeProduk);
SELECT harga INTO harga FROM Produk WHERE kdProduk=kodeProduk;
qty := mod(trunc(DBMS_RANDOM.VALUE *10), 10) + 1;
INSERT INTO SO_Detail VALUES (kodeBeli, kodeProduk, qty, harga);
k:=k+1;
END IF;
k:=k+1;
END LOOP;
j:= j + 1;
beliKode:=beliKode+1;
IF beliKode < 10 THEN
kodeBeli := ‘SO0000′ || to_char(beliKode);
ELSIF beliKode<100 THEN
kodeBeli := ‘SO000′ || to_char(beliKode);
ELSIF beliKode<1000 THEN
kodeBeli := ‘SO00′ || to_char(beliKode);
ELSIF beliKode<10000 THEN
kodeBeli := ‘SO0′ || to_char(beliKode);
END IF;
END IF;
END LOOP;
currDate := currDate + 1;
END LOOP;
END;
1000842252 / 06PGT
~ORACLE~
CREATE TABLE SOHeader
(
No_TrSOHeader CHAR(5),
KdSP CHAR(5),
KdCust CHAR(5),
Hari DATE,
CONSTRAINT trsoh_pk PRIMARY KEY(No_TrSOHeader),
CONSTRAINT spid_fk FOREIGN KEY (KdSP) REFERENCES SALESPERSON(KdSP),
CONSTRAINT custid_fk FOREIGN KEY (KdCust) REFERENCES customer(KdCust)
);
CREATE TABLE SODetail
(
No_TrSOHeader CHAR(5),
KdProd CHAR(5),
Qty NUMBER(3),
CONSTRAINT trsod_pk PRIMARY KEY(No_TrSOHeader,KdProd),
CONSTRAINT trsod_fk FOREIGN KEY(No_TrSOHeader) REFERENCES SOHeader(No_TrSOHeader),
CONSTRAINT trsod_prod_fk FOREIGN KEY(KdProd) REFERENCES product(KdProd)
);
—————————————————————–
[asumsi yang terjadi pada transaksi : tanggal transaksi diacak, customer diacak, produk diacak, sales person diacak, jumlah barang diacak berdasarkan jenis pelanggan. Pada tanggal yang sama, customer dapat membeli lebih dari 1 jenis produk(tapi tidak boleh jenis produk yang sama)]
—————————————————————–
declare
tgltrans date;
cust char(5);
jmlbrg int;
sales char(5);
jnscust char(5);
NoTrans char(5);
prod char(5);
qty int;
jmltrans int:=1000;
counter int:=jmltrans;
counter2 int;
temp int;
begin
while counter>0 loop
–Random data
tgltrans := to_date(‘1-Jan-08′,’DD-Mon-RR’) + round(dbms_random.value(0,365));
cust := ‘C’ || lpad(round(dbms_random.value(1,10)),4,’0′);
jmlbrg := round(dbms_random.value(1,4));
sales := ‘S’ || lpad(round(dbms_random.value(1,3)),4,’0′);
NoTrans := ‘T’ || lpad(jmltrans-counter+1,4,’0′);
–Mengambil jenis customer
select KdJnsCust into jnscust from customer where KdCust=cust;
–Insert data header
insert into SOHeader values (NoTrans, sales, cust, tgltrans);
–Persiapkan JmlBarang
counter2:=jmlbrg;
while counter2>0 loop
–Random Kode Barang yang dibeli dan qty
prod := ‘P’ || lpad(round(dbms_random.value(1,4)),4,’0′);
if(jnscust=’J0001′) then
qty := round(dbms_random.value(1,10));
else
qty := round(dbms_random.value(20,50));
end if;
–Cek di detail ada atau tidak customer membeli barang yang sama dalam 1 hari
select count(b.No_SOHeader) into temp from SoHeader a, SODetail b where
a.No_SOHeader=b.No_SOHeader(+) and
a.KdCust=cust and
b.KdProd=prod;
–Jika ada hapus transaksi, ulang loop
if temp!=0 then
delete from SODetail where No_SOHeader=NoTrans;
delete from SOHeader where No_SOHeader=NoTrans;
counter:=counter+1;
counter2:=0;
else
insert into SODetail values(NoTrans,prod,qty);
end if;
–Kurangi counter jumlah
counter2:=counter2-1;
end loop;
–Kurangi counter transaksi
counter:=counter-1;
end loop;
end;
CREATE TABLE HdSalesOrders
(
SalesOrderID int NOT NULL,
SalesOrderNo int NOT NULL,
SalesPID number(5) NOT NULL,
CustomerID number(10) NOT NULL,
SalesOrderDate date timestamp(6) NOT NULL,
CONSTRAINT hdSalesOrder_pk PRIMARY KEY (SalesOrderID),
CONSTRAINT fk1_SalesOrder FOREIGN KEY (SalesPID) REFERENCES SalesP(SalesPID),
CONSTRAINT fk2_SalesOrder FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
CONSTRAINT fk3_SalesOrder FOREIGN KEY (KdProduk) REFERENCES Products(KdProduk)
)
CREATE TABLE DtSalesOrders
(
SalesOrderID int NOT NULL,
SalesOrderNo int NOT NULL,
KdProduk number(5) NOT NULL,
Qty number(50) NOT NULL,
CONSTRAINT dtSalesOrder_pk PRIMARY KEY (SalesOrderID),
CONSTRAINT fk1_dtSalesOrder FOREIGN KEY (SalesOrderID) REFERENCES HdSalesOrders (SalesOrderID)
)
Transaksi
==============
create table headerSO
(
NoTransaksi char(5) primary key,
kdCustomer char(5),
kdSales char(5),
tanggal date,
foreign key (kdCustomer) references msCustomer,
foreign key (kdSales) references msSales
);
create table detailSO
(
noTransaksi char(5),
KdProduk char(5),
jumlah number,
primary key (noTransaksi,kdBarang),
foreign key (kdProduk) references msProduk
);
insert into headerSO values(‘T001′,’C001′,’S001′,’05/25/09′);
insert into headerSO values(‘T002′,’C003′,’S003′,’05/26/09′);
insert into headerSO values(‘T003′,’C009′,’S002′,’05/26/09′);
insert into detailSO values(‘T001′,’P001′,2);
insert into detailSO values(‘T002′,’P002′,1);
insert into headerSO values(‘T003′,’P004′,10);
Marlene (1000867860)
06PGT
Transaksi
==============
create table headerSO
(
NoTransaksi char(5) primary key,
kdCustomer char(5),
kdSales char(5),
tanggal date,
foreign key (kdCustomer) references msCustomer,
foreign key (kdSales) references msSales
);
create table detailSO
(
noTransaksi char(5),
KdProduk char(5),
jumlah number,
primary key (noTransaksi,kdProduk),
foreign key (kdProduk) references msProduk
);
insert into headerSO values(‘T001′,’C001′,’S001′,’05/25/09′);
insert into headerSO values(‘T002′,’C003′,’S003′,’05/26/09′);
insert into headerSO values(‘T003′,’C009′,’S002′,’05/26/09′);
insert into detailSO values(‘T001′,’P001′,2);
insert into detailSO values(‘T002′,’P002′,1);
insert into headerSO values(‘T003′,’P004′,10);
Marlene (1000867860)
06PGT
*maaf ada salah lagi.. jadi submit ulang..
CREATE TABLE SoHeader (
SalesNo VARCHAR2(5) NOT NULL,
CustNo VARCHAR2(5) NOT NULL,
SalesPersonNo VARCHaR2(5) NOT NULL,
DateSold DATE NOT NULL,
Total number(8,2) NOT NULL
PRIMARY KEY(SalesNo),
FOREIGN KEY(CustNo) REFERENCES MsCustomer(CustNo),
FOREIGN KEY(SalesPersonNo) REFERENCES MsSalesPerson(SalesPersonNo)
);
CREATE TABLE SoDetail (
SalesNo VARCHAR2(5) NOT NULL,
ProductNo VaRCHAR(5) NOT NULL,
Sum INT NOT NULL,
Price number(5,2) NOT NULL,
Subtotal number(8,2) NOT NULL,
PRIMARY KEY(SalesNo,ProductNo),
FOREIGN KEY(SalesNo) REFERENCES SoHeader(SalesNo),
FOREIGN KEY(ProductNo) REFERENcEs MsProduct(ProductNo)
);
INSERT INTO SoHeader VALUES(‘S001′,’C003′,’SP002′,’2009-02-24′,95000,00);
INSERT INTO SoDetail VALUES(‘S001′,’P002′,5,19000);
.. 1000878883 ..
create table HeaderSalesOrder (
idSO varchar(10) primary key
dateSO date,
idCust references Customer
idSP references SalesPerson
)
create table DetailSalesOrder (
idSO references SalesOrder
idProd references Product
qty number
)
* pak maaf saya belum bisa yg insert random.. jadi saya buat DDL ny aj..
NIM : 1000842290
Kelas : 06 PGT
CREATE TABLE SalesOrder
(
SalesOrderID CHAR(5),
KodeSalesPerson CHAR(5),
KodeCustomer CHAR(5),
TanggalSales DATE,
CONSTRAINT salesorder_pk PRIMARY KEY(SalesOrderID),
CONSTRAINT salesorder_fk1 FOREIGN KEY(KodeSalesPerson) REFERENCES MsSalesPerson(KodeSalesPerson) ON DELETE CASCADE,
CONSTRAINT salesorder_fk2 FOREIGN KEY(KodeCustomer) REFERENCES MsCustomer(KodeCustomer) ON DELETE CASCADE
);
CREATE TABLE SalesOrderDetail
(
SalesOrderID CHAR(5),
KodeProduk CHAR(5),
Jumlah NUMBER,
PRIMARY KEY(SalesOrderID,KodeProduk),
CONSTRAINT salesorderdetail_fk FOREIGN KEY(SalesOrderID) REFERENCES SalesOrder(SalesOrderID) ON DELETE CASCADE,
CONSTRAINT salesorderdetail_fk2 FOREIGN KEY(KodeProduk) REFERENCES MsProduct(KodeProduk) ON DELETE CASCADE
);
set serveroutput on
DECLARE
SalesOrderID Char(5);
KodeSalesPerson Char(5);
randKodeSales number;
KodeCustomer Char(5);
randKodeCustomer number;
Tanggal DATE;
i NUMBER;
jmlTrans NUMBER;
j NUMBER;
KodeProduk Char(5);
randKodeProduk NUMBER;
Jumlah NUMBER;
BEGIN
FOR i IN 1.. 10 LOOP
IF i < 10 THEN
SalesOrderID := ‘SO00′ || to_char(i);
ELSIF i < 100 THEN
SalesOrderID := ‘SO0′ || to_char(i);
ELSIF i < 1000 THEN
SalesOrderID := ‘SO’ || to_char(i);
END IF;
randKodeSales := trunc(DBMS_RANDOM.VALUE * 3) + 1;
KodeSalesPerson := ‘SP00′ || to_char(randKodeSales);
randKodeCustomer := trunc(DBMS_RANDOM.VALUE * 10) + 1;
IF randKodeCustomer < 10 THEN
KodeCustomer := ‘C000′ || to_char(randKodeCustomer);
ELSIF randKodeCustomer < 100 THEN
KodeCustomer := ‘C00′ || to_char(randKodeCustomer);
END IF;
Tanggal := TO_CHAR(SYSDATE,’dd-Mon-yyyy’);
INSERT INTO SalesOrder VALUES(SalesOrderID,KodeSalesPerson,KodeCustomer,Tanggal);
jmlTrans := trunc(DBMS_RANDOM.VALUE * 2) + 1;
/*FOR j IN 1.. jmlTrans LOOP
randKodeProduk := trunc(DBMS_RANDOM.VALUE * 4) + 1;
KodeProduk := ‘P000′ || to_char(randKodeProduk);
Jumlah := trunc(DBMS_RANDOM.VALUE * 10) + 1;
DBMS_OUTPUT.PUT_LINE(SalesOrderID || ‘ ‘ || KodeProduk || ‘ ‘ || Jumlah);
INSERT INTO SalesOrderDetail VALUES(SalesOrderID, KodeProduk, Jumlah);
END LOOP; */
END LOOP;
END;
/
DECLARE
SalesOrderID Char(5);
KodeSalesPerson Char(5);
randKodeSales number;
KodeCustomer Char(5);
randKodeCustomer number;
Tanggal DATE;
i NUMBER;
jmlTrans NUMBER;
j NUMBER;
KodeProduk Char(5);
randKodeProduk NUMBER;
Jumlah NUMBER;
BEGIN
FOR i IN 1.. 10 LOOP
IF i < 10 THEN
SalesOrderID := ‘SO00′ || to_char(i);
ELSIF i < 100 THEN
SalesOrderID := ‘SO0′ || to_char(i);
ELSIF i .<… jadi akhirnya saya pecah menjadi 2 begin dan end…
CMMIIW… THX
kenapa kata2 saya yang diatas terpotong yah?? mungkin karena terlalu panjang… maaf saya posting ulang codingnya
set serveroutput on
DECLARE
SalesOrderID Char(5);
KodeSalesPerson Char(5);
randKodeSales number;
KodeCustomer Char(5);
randKodeCustomer number;
Tanggal DATE;
i NUMBER;
jmlTrans NUMBER;
j NUMBER;
KodeProduk Char(5);
randKodeProduk NUMBER;
Jumlah NUMBER;
BEGIN
FOR i IN 1.. 10 LOOP
IF i < 10 THEN
SalesOrderID := ‘SO00′ || to_char(i);
ELSIF i < 100 THEN
SalesOrderID := ‘SO0′ || to_char(i);
ELSIF i < 1000 THEN
SalesOrderID := ‘SO’ || to_char(i);
END IF;
randKodeSales := trunc(DBMS_RANDOM.VALUE * 3) + 1;
KodeSalesPerson := ‘SP00′ || to_char(randKodeSales);
randKodeCustomer := trunc(DBMS_RANDOM.VALUE * 10) + 1;
IF randKodeCustomer < 10 THEN
KodeCustomer := ‘C000′ || to_char(randKodeCustomer);
ELSIF randKodeCustomer < 100 THEN
KodeCustomer := ‘C00′ || to_char(randKodeCustomer);
END IF;
Tanggal := TO_CHAR(SYSDATE,’dd-Mon-yyyy’);
INSERT INTO SalesOrder VALUES(SalesOrderID,KodeSalesPerson,KodeCustomer,Tanggal);
jmlTrans := trunc(DBMS_RANDOM.VALUE * 2) + 1;
/*FOR j IN 1.. jmlTrans LOOP
randKodeProduk := trunc(DBMS_RANDOM.VALUE * 4) + 1;
KodeProduk := ‘P000′ || to_char(randKodeProduk);
Jumlah := trunc(DBMS_RANDOM.VALUE * 10) + 1;
DBMS_OUTPUT.PUT_LINE(SalesOrderID || ‘ ‘ || KodeProduk || ‘ ‘ || Jumlah);
INSERT INTO SalesOrderDetail VALUES(SalesOrderID, KodeProduk, Jumlah);
END LOOP; */
END LOOP;
END;
/
DECLARE
SalesOrderID Char(5);
KodeSalesPerson Char(5);
randKodeSales number;
KodeCustomer Char(5);
randKodeCustomer number;
Tanggal DATE;
i NUMBER;
jmlTrans NUMBER;
j NUMBER;
KodeProduk Char(5);
randKodeProduk NUMBER;
Jumlah NUMBER;
BEGIN
FOR i IN 1.. 10 LOOP
IF i < 10 THEN
SalesOrderID := ‘SO00′ || to_char(i);
ELSIF i < 100 THEN
SalesOrderID := ‘SO0′ || to_char(i);
ELSIF i < 1000 THEN
SalesOrderID := ‘SO’ || to_char(i);
END IF;
jmlTrans := trunc(DBMS_RANDOM.VALUE * 2) + 1;
FOR j IN 1.. jmlTrans LOOP
randKodeProduk := trunc(DBMS_RANDOM.VALUE * 4) + 1;
KodeProduk := ‘P000′ || to_char(randKodeProduk);
Jumlah := trunc(DBMS_RANDOM.VALUE * 10) + 1;
/*DBMS_OUTPUT.PUT_LINE(SalesOrderID || ‘ ‘ || KodeProduk || ‘ ‘ || Jumlah);*/
INSERT INTO SalesOrderDetail VALUES(SalesOrderID, KodeProduk, Jumlah);
END LOOP;
END LOOP;
END;
/
maaf pak… kodingnya belum sempurna… karena masih kurang validasi sana sini… nanti akan saya tambahkan lagi…
1000877211
create table HeaderSO (
noSO varchar(5) not null primary key,
tanggalSO date,
kdcustomer varchar2(5),
kdsalesperson varchar2(5),
constraint SO_customer FOREIGN KEY (kdcustomer) REFERENCES mscustomer (kdcustomer),
constraint SO_salesperson FOREIGN KEY (kdsalesperson) REFERENCES mssalesperson (kdsalesperson)
);
create table DetailSO (
noSO varchar2(5),
kodeproduk varchar2(5),
qty number,
constraint SO_noSO FOREIGN KEY (noSO) REFERENCES HeaderSO (noso),
constraint SO_produk FOREIGN KEY (kodeproduk) REFERENCES msproduct (kodeproduk)
);
NB: Memasukkan Data pada Tabel SO Header dan SO Detail (DML)??
jawabannya nanti menyusul pak..
create table SOHeader
(
KdSO varchar2(10) Primary Key,
KdCustomer varchar(10),
KdSalesPerson varchar(10),
TanggalSO DATE,
constraint c_1 FOREIGN KEY(KdCustomer) REFERENCES Customer(KdCustomer),
constraint c_2 FOREIGN KEY(KdSalesPerson) REFERENCES SalesPerson(KdSalesPerson)
);
insert into SOHeader values(‘SO0001′,’C0001′,’SP0001′,TO_DATE(‘31-05-2009′,’DD-MM-YY’))
create table SODetail
(
KdSO varchar2(10),
KdProduk varchar(10),
Qty numeric,
constraint c_3 PRIMARY KEY(KdSO, KdProduk),
constraint c_4 FOREIGN KEY(KdProduk) REFERENCES Product(KdProduk)
);
insert into SODetail values(‘SO0001′,’P001′,15)
Julianto / 1000865262 / 06PGT
Insert random nya saya belum bisa Pak.
create table SOHeader
(
noSO varchar2(50) PRIMARY KEY,
idCustomer varchar(10),
idSalesPerson varchar(10),
tanggalSO DATE,
constraint c_1 FOREIGN KEY(idCustomer) REFERENCES customer(idCustomer),
constraint c_2 FOREIGN KEY(idsalesperson) REFERENCES salesPerson(idsalesperson)
);
create table SODetail
(
noSO varchar2(50),
kodeProduk varchar(50),
qty numeric,
constraint c_3 PRIMARY KEY(noSO, kodeProduk),
constraint c_4 FOREIGN KEY(kodeProduk) REFERENCES product(kodeProduk)
);
Nama : Rhio
NIM : 1000867942
kelas : 06PGT
–Untuk create table SOHeader dan SODetail
CREATE TABLE SOHeader
(
TrSOHeader CHAR(5),
KdSP CHAR(5),
KdCust CHAR(5),
Hari DATE,
CONSTRAINT trsoh_pk PRIMARY KEY(TrSOHeader),
CONSTRAINT spid_fk FOREIGN KEY (KdSP) REFERENCES SALESPERSON(KdSP),
CONSTRAINT custid_fk FOREIGN KEY (KdCust) REFERENCES customer(KdCust)
);
CREATE TABLE SODetail
(
TrSOHeader CHAR(5),
KdProd CHAR(5),
Qty NUMBER(3),
CONSTRAINT trsod_pk PRIMARY KEY(TrSOHeader,KdProd),
CONSTRAINT trsod_fk FOREIGN KEY(TrSOHeader) REFERENCES SOHeader(TrSOHeader),
CONSTRAINT trsod_prod_fk FOREIGN KEY(KdProd) REFERENCES product(KdProd)
);
– untuk insert data ke tabel SOHeader dan tabel SODetail dengan menggunakan program (datanya digenerate sendiri dengan menggunakan random)
declare
JmlTr int := 1000;
TrPerHr int;
DtPerCust int;
Cust char(5);
SP char(5);
KdTr char(5);
KdPro char(5);
TglTr date;
Ctr int := 0;
CtrHr int := 0;
temp int;
begin
while Ctr!=JmlTr loop
–Tentuin hari transaksi
select mod(CtrHr,366) into Temp from dual;
TglTr := to_date(‘1-Jan-08′,’DD-Mon-RR’) + Temp;
–Random byk Tr perhari min 3, max 10 per hari
TrPerHr := round(dbms_random.value(3,7));
while TrPerHr!=0 loop
Cust := ‘C’ || lpad(round(dbms_random.value(1,10)),4,’0′);
SP := ‘S’ || lpad(round(dbms_random.value(1,3)),4,’0′);
select count(*) into temp from SOHeader where KdCust=Cust and Hari=TglTr;
if temp=0 then
KdTr := ‘T’ || lpad((Ctr+1),4,’0′);
insert into SOHeader values(KdTr,SP,Cust,TglTr);
for temp in 1..4 loop
if round(dbms_random.value)=1 then
KdPro := ‘P’ || lpad(temp,4,’0′);
insert into SODetail values(KdTr,KdPro,1);
end if;
end loop;
TrPerHr:=TrPerHr-1;
Ctr := Ctr+1;
if Ctr=JmlTr then
TrPerHr:=0;
end if;
end if;
end loop;
CtrHr := CtrHr+1;
end loop;
end;
Wunleng Prawijogo
1000839283 (06PGT)
Oracle
CREATE TABLE TrSOHeader
(
KdTrSOHeader varchar(6),
KdCustomer varchar(6),
KdSalesPerson varchar(6),
Tanggal DATE,
CONSTRAINT trsoheaderpk PRIMARY KEY(KdTrSOHeader),
CONSTRAINT trsalespersonfk FOREIGN KEY (KdSalesPerson) REFERENCES MsSalesPerson(KdSalePerson),
CONSTRAINT custid_fk FOREIGN KEY (KdCustomer) REFERENCES MsCustomer(KdCustomer)
);
CREATE TABLE TrSODetail
(
KdTrSOHeader varchar (6),
KdProd varchar(6),
Jumlah number(3),
CONSTRAINT trsodetailpk PRIMARY KEY(KdTrSOHeader,KdProduct),
CONSTRAINT trsodetailfk FOREIGN KEY(KdTrSOHeader) REFERENCES TrSOHeader(KdTrSOHeader),
CONSTRAINT trsodetailproductfk FOREIGN KEY(KdProduct) REFERENCES MsProduct(KdProduct)
);
insert random… belum.. >.<
Erico 1000835650
06PGT
3
—————-
/*Saya bikin stored Function dahulu buat bantu generate kodenya pak*/
/*Bikin Function buat kode*/
CREATE OR REPLACE FUNCTION bikinkode
( nomor IN NUMBER, jenis IN VARCHAR2)
RETURN VARCHAR2
IS
hasil VARCHAR2(6);
BEGIN
IF nomor < 10 THEN
RETURN jenis||’000′||nomor;
ELSIF nomor < 100 THEN
RETURN jenis||’00′||nomor;
ELSIF nomor < 1000 THEN
RETURN jenis||’0′||nomor;
ELSE
RETURN jenis||nomor;
END IF;
END;
/
——————————-
/*Bikin Transaksi Sales Order*/
DECLARE
TYPE t_min IS VARRAY(4) of NUMBER;
TYPE t_max IS VARRAY(4) of NUMBER;
minProd t_min := t_min(1, 1, 1, 1);
maxProd t_max := t_max(5, 5, 5, 5);
tglmin VARCHAR2(12) := ‘01/01/2008′;
tglmax VARCHAR2(12) := ‘31/12/2008′;
hasil VARCHAR2(25);
insTgl DATE;
insCust NUMBER;
insSP NUMBER;
insProd NUMBER;
insQty NUMBER;
jnsCust VARCHAR(12);
fOrder NUMBER := 1;
fNItem NUMBER;
fNCount NUMBER;
fCheck NUMBER;
fDet NUMBER := 0;
BEGIN
WHILE fOrder <= 1000 LOOP
–Random Tanggal
insTgl := TO_DATE(ROUND(DBMS_RANDOM.VALUE(TO_CHAR(TO_DATE(tglmin, ‘dd/mm/yyyy’), ‘J’), TO_CHAR(TO_DATE(tglmax, ‘dd/mm/yyyy’), ‘J’))), ‘J’);
–Random Sales Person
insSP := ROUND(DBMS_RANDOM.VALUE(1,3));
–Random Customer
insCust := ROUND(DBMS_RANDOM.VALUE(1,10));
–Menentukan Min Max qty tiap product berdasarkan jenis customer
SELECT JenisCustomer INTO jnsCust FROM MsCustomer WHERE kodeCustomer = bikinkode(insCust, ‘C’);
CASE jnsCust
WHEN ‘Mahasiswa’ THEN minProd := t_min(1, 1, 1, 1);
maxProd := t_max(10, 10, 10, 10);
WHEN ‘Perusahaan’ THEN minProd := t_min(20, 20, 20, 20);
maxProd := t_max(50, 50, 50, 50);
END CASE;
–Insert Header
INSERT INTO TrHeaderSalesOrder VALUES(bikinkode(fOrder, ‘SO’), bikinkode(insCust, ‘C’), bikinkode(insSP, ‘SP’), insTgl);
–Insert Detail
fNItem := ROUND(DBMS_RANDOM.VALUE(1, 4));
fNCount := 1;
WHILE fNCount <= fNItem LOOP
fDet := 0;
WHILE fDet = 0 LOOP
insProd := ROUND(DBMS_RANDOM.VALUE(1, 4));
SELECT COUNT(*) INTO fCheck FROM TrDetailSalesOrder WHERE noSO = bikinkode(fOrder, ‘SO’) AND kodeProduk = bikinkode(insProd, ‘P’);
IF fCheck = 0 THEN
INSERT INTO TrDetailSalesOrder VALUES(bikinkode(fOrder, ‘SO’), bikinkode(insProd, ‘P’), ROUND(DBMS_RANDOM.VALUE(minProd(insProd), maxProd(insProd))));
fDet := 1;
END IF;
END LOOP;
fNCount := fNCount + 1;
END LOOP;
fOrder := fOrder + 1;
END LOOP;
END;
/
————————————
DDL Untuk PERUSAHAAN AIR MINUM
Create Table SO_Header
(
Kd_SO varchar(50) primary key,
Tanggal datetime,
KdPelanggan varchar(50),
KdKaryawan varchar(50),
Keterangan varchar(255),
Total money,
Diskon money,
GrandTotal money,
Addby varchar(50),
AddDate datetime,
UpdateBy varchar(50),
UpdateDate datetime,
Foreign key(KdPelanggan) Refrences Pelanggan(KdPelanggan),
Foreign key(KdKaryawan) Refrences Karyawan(KdKaryawan)
)
Create Table SO_Detail
(
Kd_SO varchar(50),
KdBarang varchar(50),
Qty int,
Harga money,
SubTotal money,
Diskon money,
Total money,
Foreign key(Kd_So) References SO_Header(Kd_So),
Foreign key(KdBarang) References Barang(KdBarang)
)
Create Table Barang
(
KdBarang varchar(50),
NamaBarang varchar(255),
KdJenisBarang varchar(50),
Stok int,
HargaBeli money,
HargaJual money,
Addby varchar(50),
AddDate datetime,
UpdateBy varchar(50),
UpdateDate datetime,
Foreign key(KdJenisBarang) References JenisBarang(KdJenisBarang)
)
Create Table JenisBarang
(
KdJenisBarang varchar(50),
NamaJenisBarang varchar(255),
Keterangan varchar(255),
Addby varchar(50),
AddDate datetime,
UpdateBy varchar(50),
UpdateDate datetime
)
Create Table Pelanggan
(
KdPelanggan varchar(50) primary key,
NamaPelanggan varchar(255),
Telp varchar(50),
Hp varchar(50),
Alamat varchar(255),
Addby varchar(50),
AddDate datetime,
UpdateBy varchar(50),
UpdateDate datetime
)
Create Table Karyawan
(
KdKaryawan varchar(50) primary key,
NamaKaryawan varchar(255),
KdJabatan varchar(50),
Telp varchar(50),
Hp varchar(50),
Alamat varchar(255),
Addby varchar(50),
AddDate datetime,
UpdateBy varchar(50),
UpdateDate datetime,
Foreign key(KdJabatan) References Jabatan(KdJabatan)
)
Create Table Jabatan
(
KdJabatan varchar(50) primary key,
NamaJabatan varchar(255),
Keterangan varchar(255),
Addby varchar(50),
AddDate datetime,
UpdateBy varchar(50),
UpdateDate datetime
)
Nim : 1000864291
Nama : Ervin Yulianto
Kelas : 06 PGT
–ORACLE
–Nb. Maaf pak, Saya Post 2x, ini yang last update thx
DDL Untuk PERUSAHAAN AIR MINUM
Create Table SO_Header
(
Kd_SO varchar(50) primary key,
Tanggal datetime,
KdPelanggan varchar(50),
KdKaryawan varchar(50),
Keterangan varchar(255),
Total money,
Diskon money,
GrandTotal money,
Addby varchar(50),
AddDate datetime,
UpdateBy varchar(50),
UpdateDate datetime,
Foreign key(KdPelanggan) Refrences Pelanggan(KdPelanggan),
Foreign key(KdKaryawan) Refrences Karyawan(KdKaryawan)
)
Create Table SO_Detail
(
Kd_SO varchar(50),
KdBarang varchar(50),
Qty int,
Harga money,
SubTotal money,
Diskon money,
Total money,
Foreign key(Kd_So) References SO_Header(Kd_So),
Foreign key(KdBarang) References Barang(KdBarang)
)
Create Table Barang
(
KdBarang varchar(50),
NamaBarang varchar(255),
KdJenisBarang varchar(50),
Stok int,
HargaBeli money,
HargaJual money,
Addby varchar(50),
AddDate datetime,
UpdateBy varchar(50),
UpdateDate datetime,
Foreign key(KdJenisBarang) References JenisBarang(KdJenisBarang)
)
Create Table JenisBarang
(
KdJenisBarang varchar(50),
NamaJenisBarang varchar(255),
Keterangan varchar(255),
Addby varchar(50),
AddDate datetime,
UpdateBy varchar(50),
UpdateDate datetime
)
Create Table Pelanggan
(
KdPelanggan varchar(50) primary key,
NamaPelanggan varchar(255),
KdJenisPelanggan varchar(50),
Telp varchar(50),
Hp varchar(50),
Alamat varchar(255),
Addby varchar(50),
AddDate datetime,
UpdateBy varchar(50),
UpdateDate datetime,
Foreign Key(KdJenisPelanggan) References JenisPelanggan(KdJenisPelanggan)
)
Create Table JenisPelanggan
(
KdJenisPelanggan varchar(50) primary key,
NamaJenisPelanggan varchar(255),
Keterangan varchar(255),
Addby varchar(50),
AddDate datetime,
UpdateBy varchar(50),
UpdateDate datetime
)
Create Table Karyawan
(
KdKaryawan varchar(50) primary key,
NamaKaryawan varchar(255),
KdJabatan varchar(50),
Telp varchar(50),
Hp varchar(50),
Alamat varchar(255),
Addby varchar(50),
AddDate datetime,
UpdateBy varchar(50),
UpdateDate datetime,
Foreign key(KdJabatan) References Jabatan(KdJabatan)
)
Create Table Jabatan
(
KdJabatan varchar(50) primary key,
NamaJabatan varchar(255),
Keterangan varchar(255),
Addby varchar(50),
AddDate datetime,
UpdateBy varchar(50),
UpdateDate datetime
)
–menginsert tabel transaksi sales order header dan detail
declare
Tanggal date;
Pelanggan varchar(50);
Qty int;
Karyawan varchar(50);
JenisPelanggan char(5);
Kd_SO varchar(50);
KdBarang varchar(50);
Jumlah int;
jumlahTransaksi int:=1000;
counter int:=jumlahTransaksi ;
counter2 int;
temp int;
begin
while counter>0 loop
–Random data
Tanggal := to_date(’1-Jan-08′,’DD-Mon-RR’) + round(dbms_random.value(0,365));
Pelanggan := ‘C’ || lpad(round(dbms_random.value(1,10)),4,’0′);
Qty := round(dbms_random.value(1,4));
Karyawan := ‘S’ || lpad(round(dbms_random.value(1,3)),4,’0′);
Kd_SO := ‘T’ || lpad(jmltrans-counter+1,4,’0′);
–Mengambil jenis Pelanggan
select KdJenisPelanggan into JenisPelanggan from Pelanggan where KdPelanggan=Pelanggan;
–Insert data header
insert into SOHeader values (Kd_SO, Karyawan, Pelanggan, Tanggal);
–Persiapkan Qty
counter2:=Qty;
while counter2>0 loop
–Random Kode Barang yang dibeli dan qty
KdBarang := ‘P’ || lpad(round(dbms_random.value(1,4)),4,’0′);
if(JenisPelanggan=’J0001′) then
Jumlah := round(dbms_random.value(1,10));
else
Jumlah := round(dbms_random.value(20,50));
end if;
–Cek di detail ada atau tidak customer membeli barang yang sama dalam 1 hari
select count(b.Kd_SO) into temp from So_Header a, SO_Detail b where
a.KD_SO=b.No_KD_SO(+) and
a.KdPelanggan=Pelanggan and
b.KdBarang=KdBarang;
–Jika ada hapus transaksi, ulang loop
if temp!=0 then
delete from SO_Detail where KD_SO=KD_SO;
delete from SO_Header where Kd_SO=KD_SO;
counter:=counter+1;
counter2:=0;
else
insert into SODetail values(KD_SO,KDBarang,Jumlah);
end if;
–Kurangi counter jumlah
counter2:=counter2-1;
end loop;
–Kurangi counter transaksi
counter:=counter-1;
end loop;
end;
Denni Efendi
06PGT
1000835751
CREATE TABLE TrHeaderSO
(
No_SO varchar2(10),
KdCustomer varchar2(10),
KdSales varchar2(10),
Tanggal date,
primary key(No_SO),
foreign key(KdCustomer) references MsCustomer(KdCustomer) on delete cascade,
foreign key(KdSales) references MsSalesPerson(KdSales) on delete cascade
);
CREATE TABLE TrDetailSO
(
No_SO varchar2(10),
KdProduk varchar2(10),
Qty int,
primary key(No_SO,KdProduk),
foreign key(No_SO) references TrHeaderSO(No_SO) on delete cascade,
foreign key(KdProduk) references MsProduk(KdProduk)
);
erfan melanaga (1000873503)
kelas: 06PGT
create table SOHeder
(
noSO varchar2(20) PRIMARY KEY,
idPelanggan varchar(20),
idSperson varchar(20),
tanggalSO DATE,
constraint c_1 FOREIGN KEY(idPelanggan) REFERENCES pelanggan(idPelanggan),
constraint c_2 FOREIGN KEY(idSperson) REFERENCES Sperson(idSperson)
);
insert into SOHeder values(‘SO001′,’PL001′,’SP001′,TO_DATE(‘15-05-2009′,’DD-MM-YY’))
create table SODetil
(
noSO varchar2(20),
kdProduk varchar(20),
qty numeric,
constraint c_3 PRIMARY KEY(noSO, kdProduk),
constraint c_4 FOREIGN KEY(kdProduk) REFERENCES produk(kdProduk)
);
insert into SODetil values(‘SO001′,’P001′,15)
CREATE TABLE SoHeader (
KodeSalesOrder VARCHAR2(6) PRIMARY KEY NOT NULL,
KodeCustomer VARCHAR2(5) NOT NULL,
KodeSalesPerson VARCHAR2(6) NOT NULL,
TanggalSalesOrder DATE,
FOREIGN KEY(KodeCustomer) REFERENCES MsCustomer(KodeCustomer),
FOREIGN KEY(KodeSalesPerson) REFERENCES MsSalesPerson(KodeSalesPerson)
);
CREATE TABLE SoDetail (
KodeSalesOrder VARCHAR2(6) PRIMARY KEY NOT NULL,
KodeProduct VARCHAR(5) PRIMARY KEY NOT NULL,
QuantitySales NUMBER,
FOREIGN KEY(KodeSalesOrder) REFERENCES SoHeader(KodeSalesOrder),
FOREIGN KEY(KodeProduct) REFERENCES MsProduct(KodeProduct)
);
INSERT INTO SoHeader VALUES(’SO0001′,’C0005′,’SP0002′,’2009-05-31′);
INSERT INTO SoDetail VALUES(’SO0002′,’P0008′,50);
create table SalesOrder
(
SalesOrderId char(7) primary key not null,
SalesOrderDate date not null,
CustomerID char(7) not null,
SalesID char(7) not null
CONSTRAINT salesorder_fk1 FOREIGN KEY(SalesOrderID) REFERENCES Sales(SalesID),
CONSTRAINT salesorder_fk2 FOREIGN KEY(CustomerID) REFERENCES Customer(CustomerID)
);
create table DetailSalesOrder
(
SalesOrderId char(7) not null,
ProductID char(7) not null,
Qty number,
PRIMARY KEY(SalesOrderID,ProductID),
CONSTRAINT DetailSalesOrder_fk1 FOREIGN KEY(SalesOrderID) REFERENCES SalesOrder(SalesOrderID),
CONSTRAINT DetailSalesOrder_fk2 FOREIGN KEY(ProductID) REFERENCES Product(ProductID)
)
jawaban untuk generate random nyusul pak
Yusup Martinus Freddi K/ 1000848073/ 06PGT
CREATE TABLE TrSOHeader(
NoTrans CHAR(10) NOT NULL,
KdCustomer CHAR(10) NOT NULL,
KdKaryawan CHAR(10) NOT NULL,
TanggalTran DATETIME NOT NULL,
PRIMARY KEY (NoTrans),
FOREIGN KEY (KdCustomer) REFERENCES MsCustomer
ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (KdKaryawan) REFERENCES MsKaryawan
ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE TrSODetail(
NoTrans CHAR(10) NOT NULL,
KdProduct CHAR(10) NOT NULL,
Jumlah INT NOT NULL,
PRIMARY KEY (NoTrans,KdProduct),
FOREIGN KEY (NoTrans) REFERENCES HeaderPembelian
ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (KdProduct) REFERENCES game
ON UPDATE CASCADE ON DELETE CASCADE
);
1000836306
06PGT
===========
CREATE DATABASE dwh_1000dummy
/* SalesOrders */
CREATE TABLE HdSalesOrders
(
SalesOrderID int NOT NULL,
SalesOrderNo int NOT NULL,
SalesPID number(5) NOT NULL,
CustomerID number(10) NOT NULL,
SalesOrderDate date timestamp(6) NOT NULL,
CONSTRAINT hdSalesOrder_pk PRIMARY KEY (SalesOrderID),
CONSTRAINT fk1_SalesOrder FOREIGN KEY (SalesPID) REFERENCES SalesP(SalesPID),
CONSTRAINT fk2_SalesOrder FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
CONSTRAINT fk3_SalesOrder FOREIGN KEY (KdProduk) REFERENCES Products(KdProduk)
)
CREATE TABLE DtSalesOrders
(
SalesOrderID int NOT NULL,
SalesOrderNo int NOT NULL,
KdProduk number(5) NOT NULL,
Qty number(50) NOT NULL,
CONSTRAINT dtSalesOrder_pk PRIMARY KEY (SalesOrderID),
CONSTRAINT fk1_dtSalesOrder FOREIGN KEY (SalesOrderID) REFERENCES HdSalesOrders (SalesOrderID)
)
CREATE OR REPLACE Function AddQty
( CustTypeID_in IN NUMBER )
RETURN NUMBER
IS
Qty number:=0;
Qty1 number:=0;
Qty2 number:=0;
cursor c1 is
select dbms_random.value(1,10) num from dual;
cursor c2 is
select dbms_random.value(20,50) num from dual;
BEGIN
open c1;
fetch c1 into Qty1;
close c1;
open c2;
fetch c2 into Qty2;
close c2;
IF CustTypeID_in == 1 THEN
Qty := Qty1;
ELSIF CustTypeID == 2 THEN
Qty := Qty2;
END IF;
RETURN Qty;
END;
CREATE SEQUENCE seq_SalesOrder
MINVALUE 00001
START WITH 00001
INCREMENT BY 00001
NOCACHE
INSERT INTO SalesOrders (SalesOrderID,SalesPID,CustomerID,KdProduk,Qty)
VALUES (seq_SalesOrder.nextval ,
SELECT SalesPID FROM
( SELECT * FROM SalesP
ORDER BY dbms_random.value )
WHERE rownum = 1 ,
SELECT CustomerID FROM
( SELECT * FROM Customer
ORDER BY dbms_random.value ) as A
WHERE rownum = 1 ,
SELECT KdProduk FROM
( SELECT * FROM Products
ORDER BY dbms_random.value )
WHERE rownum = 1 ,
AddQty(SELECT CustomerTypeID FROM A) )
1000843015
06 PGT
DDL
Create table TrHeaderSO
(
NoSo char(5),
Tanggal date,
KdCustomer char(5),
KdSales char(5),
constraint a primary key(NoSo)
);
Create table TrDetailSO
(
NoSO char(5),
KdProduct char(5),
Jumlah numeric,
constraint b primary key(NoSo,KdProduct)
);
NB:Untuk DML ny masih dalam proses pak…
Heriyanto
1000836262
06PGT
Oracle
CREATE TABLE TrHeaderSO(
KodeSO CHAR(10) PRIMARY KEY,
KodeSales CHAR(5) NOT NULL,
KodeCustomer VARCHAR(5) NOT NULL,
Tanggal DATE NOT NULL,
FOREIGN KEY (KodeSales) REFERENCES MsCustomer(KodeSales);
);
CREATE TABLE TrDetailSO(
KodeSO CHAR(10),
KodeProduk VARCHAR(5) NOT NULL,
Qty NUMBER NOT NULL,
PRIMARY KEY(KodeSo, KodeProduk),
FOREIGN KEY (KodeSo) REFERENCES TrHeaderSO(KodeSO),
FOREIGN KEY (KodeProduk) REFERENCES MsProduct(KodeProduk)
);
INSERT INTO TrHeaderSO VALUES(‘SO001′, ‘P001′, ‘C001′, ‘10-10-2008′);
INSERT INTO TrHeaderSO VALUES(‘SO002′, ‘P002′, ‘C002′, ‘09-09-2008′);
INSERT INTO TrHeaderSO VALUES(‘SO003′, ‘P003′, ‘C003′, ‘08-08-2008′);
INSERT INTO TrDetailSO VALUES(‘SO001′, ‘P001′, 50);
INSERT INTO TrDetailSO VALUES(‘SO002′, ‘P002′, 60);
INSERT INTO TrDetailSO VALUES(‘SO003′, ‘P003′, 70);
NIM: 1000854946
06PGT
CREATE HeaderTransaksiSO (
noSO VARCHAR2 (5)
CONSTRAINT header_noso_pk PRIMARY KEY,
KodeCust varchar(5)
CONSTRAINT header_kdcust_fk REFERENCES MsCustomer(KodeCust),
KodeSalesPerson VARCHAR2(5)
CONSTRAINT header_kdsp_fk REFERENCES MsSalesPerson(KodeSalesPerson),
TglUpdateSO DATE DEFAULT SYSDATE
CONSTRAINT tgl_update_so_nn NOT NULL);
);
CREATE DetailTransaksiSO(
noSO VARCHAR2 (5)
CONSTRAINT detail_noso_fk REFERENCES HeaderTransaksiSO(noSO),
KodeProduk VARCHAR2(5)
CONSTRAINT detail_kdproduk_fk REFERENCES MsProduk(KodeProduk),
Jumlah NUMBER(10)
CONSTRAINT detail_jumlah_nn NOT NULL
);
NB: itu insert nya harus dirandom ya pak?..masih belum bisa ngerandom..
NIm:1000847101 / 06PGT
Create table HeaderSO
(
KdSo varchar(5) primary key,
KdCustomer varchar(5),
KdSales varchar(5),
TanggalSO date
);
Create table DetailSO
(
KdSO varchar(5),
KdProduct varchar(5),
qty numeric,
constraint c1 primary key(KdSo,KdProduct),
constraint c2 foreign key(KdProduct) references product(kdProduct)
);
1000860652
06PGT
CREATE TABLE HeaderSO(
NoSO CHAR(7) PRIMARY KEY,
KdSales CHAR(5) NOT NULL,
KdCustomer CHAR(5) NOT NULL,
Tgl DATE NOT NULL,
FOREIGN KEY (KdCustomer) references MsCustomer,
FOREIGN KEY (KdSales) references MsSalesPerson
);
CREATE TABLE DetailSO(
NoSO CHAR(7),
KdProduk CHAR(5) NOT NULL,
Qty NUMBER NOT NULL,
PRIMARY KEY(NoSO, KdProduk),
FOREIGN KEY (NoSO) REFERENCES HeaderSO,
FOREIGN KEY (KdProduk) REFERENCES MsProduct
);
INSERT INTO HeaderSO VALUES(’SO001′, ‘SP001′, ‘C003′, ‘10/25/2008′);
INSERT INTO HeaderSO VALUES(’SO002′, ‘SP002′, ‘C007′, ‘02/14/2009′);
INSERT INTO HeaderSO VALUES(’SO003′, ‘SP003′, ‘C008′, ‘04/08/2009′);
INSERT INTO DetailSO VALUES(’SO001′, ‘P001′, 100);
INSERT INTO DetailSO VALUES(’SO002′, ‘P002′, 50);
INSERT INTO DetailSO VALUES(’SO003′, ‘P003′, 150);
Nama : Garry B
Kelas : 06PGT
NIM : 1000836281
CREATE TABLE TrHeaderSO(
KodeSO CHAR(10) PRIMARY KEY,
KodeSales CHAR(5) NOT NULL,
KodeCustomer CHAR(5) NOT NULL,
Tanggal DATE NOT NULL,
FOREIGN KEY (KodeSales) REFERENCES MsSalesPerson(KodeSales),
FOREIGN KEY (KodeCustomer) REFERENCES MsCustomer(KodeCustomer)
);
CREATE TABLE TrDetailSO(
KodeSO CHAR (10),
KodeProduk CHAR(5) NOT NULL,
Qty NUMBER NOT NULL,
PRIMARY KEY(KodeSo, KodeProduk),
FOREIGN KEY (KodeSo) REFERENCES TrHeaderSO (KodeSO),
FOREIGN KEY (KodeProduk) REFERENCES MsProduct (KodeProduk)
);
/*************Koding untuk insert data 1000****************/
DECLARE
counter PLS_INTEGER :=0;
KodeSO char(10);
KodeSales char(5) ;
KodeCustomer char(5);
tanggal DATE;
intTanggal PLS_INTEGER := 2454467 ; /* Tanggal 1-1-2008 dalam bentuk julian*/
KodeProduk char(5);
Qty PLS_INTEGER;
transPerHari PLS_INTEGER ;
counterHari PLS_INTEGER;
temp PLS_INTEGER;
counterDetail PLS_INTEGER;
jumlahDetail PLS_INTEGER;
validator PLS_INTEGER;
validee PLS_INTEGER;
BEGIN
WHILE intTanggal < 2454832 LOOP /*Tanggal 31-12-2008 dalam bentuk julian */
SELECT floor(dbms_random.value(0,5)) INTO transPerHari FROM DUAL;
counterHari :=0;
WHILE counterHari < transPerHari LOOP
/*Generate keluar pesan Kode Sales*/
IF counter < 10 THEN
KodeSO:= ‘SO’ || ‘0000′ || to_char(counter);
ELSIF counter < 100 THEN
KodeSO:= ‘SO’ || ‘000′ || to_char(counter);
ELSIF counter < 1000 THEN
KodeSO:= ‘SO’ || ‘00′ || to_char(counter);
ELSIF counter 10 THEN KodeCustomer := ‘C001′ ;
ELSE KodeCustomer := ‘C00′ || to_char(temp);
END IF;
SELECT TO_DATE(TRUNC(intTanggal), ‘J’) INTO Tanggal FROM DUAL;
INSERT INTO TrHeaderSO VALUES (KodeSO, KodeSales, KodeCustomer, Tanggal);
dbms_output.put_line(KodeSO);
/********Mulai Insert ke tabel detail ****************/
SELECT floor(dbms_random.value(1,4)) INTO jumlahDetail FROM DUAL;
counterDetail := 0;
validator := 1;
WHILE counterDetail 4 THEN
kodeProduk := ‘P001′;
temp := 1;
ELSE
KodeProduk := ‘P00′ || to_char(temp);
END IF;
IF temp=1 THEN validee:=2;
ELSIF temp=2 THEN validee:=3;
ELSIF temp=3 THEN validee:=5;
ELSIF temp=4 THEN validee:=7;
END IF;
IF mod(validator, validee) > 0 THEN
validator := validator * validee;
/*Insert qty*/
SELECT floor(dbms_random.value(1,10)) INTO temp FROM DUAL;
Qty := temp;
INSERT INTO TrDetailSO VALUES (KodeSO, KodeProduk, Qty);
dbms_output.put_line(KodeSO || ‘ ‘ ||KodeProduk);
counterDetail := CounterDetail + 1;
END IF;
END LOOP;
counter := counter + 1;
counterHari := counterHari + 1;
IF counter = 1000 THEN
EXIT;
END IF;
END LOOP;
IF counter = 1000 THEN
EXIT;
END IF;
intTanggal := intTanggal + 1;
END LOOP;
END;
/
CREATE TABLE SO_Det (
SalesNo VARCHAR2(5) NOT NULL,
ProductNo VaRCHAR(5) NOT NULL,
Sum INT NOT NULL,
Price number(5,2) NOT NULL,
Subtotal number(8,2) NOT NULL,
PRIMARY KEY(SalesNo,ProductNo),
FOREIGN KEY(SalesNo) REFERENCES SoHeader(SalesNo),
FOREIGN KEY(ProductNo) REFERENcEs MsProduct(ProductNo)
);
CREATE TABLE SO_Head (
SalesNo VARCHAR2(5) NOT NULL,
CustNo VARCHAR2(5) NOT NULL,
SalesPersonNo VARCHaR2(5) NOT NULL,
DateSold DATE NOT NULL,
Total number(8,2) NOT NULL
PRIMARY KEY(SalesNo),
FOREIGN KEY(CustNo) REFERENCES MsCustomer(CustNo),
FOREIGN KEY(SalesPersonNo) REFERENCES MsSalesPerson(SalesPersonNo)
);
INSERT INTO SO_Det VALUES(’S001′,’P002′,5,19000);
INSERT INTO SO_Heat VALUES(’S001′,’C003′,’SP002′,’2009-02-24′,95000,00);
- Mengcreate TABEL Transaksi SO Header (DDL)
CREATE TABLE SOHeader
(
KodeSO varchar(5) Primary Key,
Beny
1000867734
06PGT
KodeCustomer varchar(5),
KodeSales varchar(5),
TanggalSO DATE,
constraint c_1 FOREIGN KEY(KodeCustomer) REFERENCES MsCustomer(KodeCustomer),
constraint c_2 FOREIGN KEY(KodeSales) REFERENCES MsSales (KodeSales)
);
- Mengcreate TABEL Transaksi SO Detail (DDL)
CREATE TABLE SODetail
(
KodeSO varchar2(5),
KodeProduk varchar(5),
Qty numeric,
constraint c_3 PRIMARY KEY(KodeSO, KodeProduk),
constraint c_4 FOREIGN KEY(KodeProduk) REFERENCES MsProduct(KodeProduk)
);
- Memasukkan Data pada Tabel SO Header dan SO Detail (DML)
insert into SOHeader values(’S0001′,’C0001′,’S0001′,TO_DATE(’31-05-2009′,’DD-MM-YY’));
insert into SODetail values(’S0001′,’P0001′,50);
pak, yg masukin nilai randomnya belum. menyusul.
CREATE TABLE TrHeaderSO(
KodeSO CHAR(10) PRIMARY KEY,
KodeSales CHAR(5) NOT NULL,
KodeCustomer CHAR(5) NOT NULL,
Tanggal DATE NOT NULL,
FOREIGN KEY (KodeSales) REFERENCES MsSalesPerson(KodeSales),
FOREIGN KEY (KodeCustomer) REFERENCES MsCustomer(KodeCustomer)
);
CREATE TABLE TrDetailSO(
KodeSO CHAR (10),
KodeProduk CHAR(5) NOT NULL,
Qty NUMBER NOT NULL,
PRIMARY KEY(KodeSo, KodeProduk),
FOREIGN KEY (KodeSo) REFERENCES TrHeaderSO (KodeSO),
FOREIGN KEY (KodeProduk) REFERENCES MsProduct (KodeProduk)
);
/*************Koding untuk insert data 1000****************/
DECLARE
counter PLS_INTEGER :=0;
KodeSO char(10);
KodeSales char(5) ;
KodeCustomer char(5);
tanggal DATE;
intTanggal PLS_INTEGER := 2454467 ; /* Tanggal 1-1-2008 dalam bentuk julian*/
KodeProduk char(5);
Qty PLS_INTEGER;
transPerHari PLS_INTEGER ;
counterHari PLS_INTEGER;
temp PLS_INTEGER;
counterDetail PLS_INTEGER;
jumlahDetail PLS_INTEGER;
validator PLS_INTEGER;
validee PLS_INTEGER;
BEGIN
WHILE intTanggal < 2454832 LOOP /*Tanggal 31-12-2008 dalam bentuk julian */
SELECT floor(dbms_random.value(0,5)) INTO transPerHari FROM DUAL;
counterHari :=0;
WHILE counterHari < transPerHari LOOP
/*Generate keluar pesan Kode Sales*/
IF counter < 10 THEN
KodeSO:= ‘SO’ || ‘0000′ || to_char(counter);
ELSIF counter < 100 THEN
KodeSO:= ‘SO’ || ‘000′ || to_char(counter);
ELSIF counter 0 THEN
validator := validator * validee;
/*Insert qty*/
SELECT floor(dbms_random.value(1,10)) INTO temp FROM DUAL;
Qty := temp;
INSERT INTO TrDetailSO VALUES (KodeSO, KodeProduk, Qty);
dbms_output.put_line(KodeSO || ‘ ‘ ||KodeProduk);
counterDetail := CounterDetail + 1;
END IF;
END LOOP;
counter := counter + 1;
counterHari := counterHari + 1;
IF counter = 1000 THEN
EXIT;
END IF;
END LOOP;
IF counter = 1000 THEN
EXIT;
END IF;
intTanggal := intTanggal + 1;
END LOOP;
END;
/
Heriyanto
1000836262
06PGT
Oracle
/* Membuat Transaksi Sales Order */
create table HeaderSalesOrder
(
NoSo varchar2(10) primary key,
Tanggal date,
KodeCustomer varchar2(10),
KodeSalesPerson varchar2(10),
CONSTRAINT so_cust_fk FOREIGN KEY (KodeCustomer) references Customer(KodeCustomer),
CONSTRAINT so_sales_fk FOREIGN KEY (KodeSalesPerson) references SalesPerson(KodeSalesPerson)
);
create table DetailSalesOrder
(
NoSO varchar2(10),
KodeProduk varchar2(10),
Qty int,
primary key(NoSO,KodeProduk),
CONSTRAINT so_soso_fk FOREIGN KEY (NoSO) references HeaderSalesOrder(NoSO),
CONSTRAINT so_prod_fk FOREIGN KEY (KodeProduk) references Product(KodeProduk)
);
/*========================================================*/
/* Memasukan ke dalam Transaksi Sales Order */
DECLARE
jml INT:=1;
byk INT:=0;
tempkode INT:=0;
kode VARCHAR2(10);
ran INT;
rana INT;
cust varchar2(10);
sp varchar2(10);
qty INT:=0;
bykran INT:=0;
barang varchar2(10);
cek INT:=0;
BEGIN
WHILE jml<=1000 LOOP
tempkode:=jml;
IF tempkode < 10 THEN
kode := ‘SO0000′ || to_char(tempkode);
ELSIF tempkode<100 THEN
kode := ‘SO000′ || to_char(tempkode);
ELSIF tempkode<1000 THEN
kode := ‘SO00′ || to_char(tempkode);
ELSIF tempkode<10000 THEN
kode := ‘SO0′ || to_char(tempkode);
END IF;
/* Random KodeCustomer */
ran := mod(trunc(DBMS_RANDOM.VALUE *10), 10) + 1;
IF ran < 10 THEN
cust := ‘KC00′ || to_char(ran);
ELSIF ran < 100 THEN
cust := ‘KC0′ || to_char(ran);
END IF;
/* Random KodeSalesOrder */
ran := mod(trunc(DBMS_RANDOM.VALUE *10), 3) + 1;
sp := ‘SP00′ || to_char(ran);
insert into HeaderSalesOrder values(kode,TO_DATE(‘01-01-2001′,’dd-mm-yyyy’),cust,sp);
/* Random Byk Jenis Barang yang dibeli customer 1 – 5*/
rana := mod(trunc(DBMS_RANDOM.VALUE *10), 4) + 1;
byk:=0;
WHILE byk<rana LOOP
bykran := mod(trunc(DBMS_RANDOM.VALUE *10), 4) + 1;
barang := ‘P00′ || to_char(bykran);
select count(*) into cek from detailsalesorder WHERE NoSO = kode and KodeProduk = barang;
IF (cek < 1) THEN
/* Random Qty yg dibeli 1-100 */
qty := mod(trunc(DBMS_RANDOM.VALUE *100), 100) + 1;
insert into DetailSalesOrder values(kode,barang,qty);
END IF;
byk:=byk+1;
END LOOP;
jml:=jml+1;
END LOOP;
END;
create table SalesOrder
(
SalesOrderID char(5) primary key not null,
SalesID char(5) not null,
CustomerID char(5) not null,
SalesDate date not null,
CONSTRAINT salesorder_fk1 FOREIGN KEY(SalesID) REFERENCES Sales(SalesID),
CONSTRAINT salesorder_fk2 FOREIGN KEY(CustomerID) REFERENCES Customer(CustomerID)
);
create table DetailSalesOrder
(
SalesOrderID char(5) not null,
ProductID char(5) not null,
Qty number,
PRIMARY KEY(SalesOrderID,ProductID),
CONSTRAINT detailSalesOrder_fk FOREIGN KEY(SalesOrderID) REFERENCES Sales(SalesOrderID),
CONSTRAINT detailSalesOrder_fk2 FOREIGN KEY(ProductID) REFERENCES Product(ProductID)
);
With Random… (but error) T.T
DECLARE
IdSLRand number;
IdCSRand number;
IdProRand number;
Trans number;
qty number;
IdSO char(5);
IdSL char(5);
IdCS char(5);
IdPro char(5);
Tgl Date;
hari number;
bulan number;
tahun number;
BEGIN
FOR i IN 1.. 10 LOOP
IF i < 10 then
IdSO := ‘SO00′ || to_char(i);
ELSEIF i < 100 then
IdSO := ‘SO0′ || to_char(i);
ELSEIF i < 1000 then
IdSO := ‘SO’ || to_char(i);
END IF;
IdSLRand := trunc(DBMS_RANDOM.value * 3) + 1;
IdSL := ‘SL00′ || to_char(IdSLRand);
IdCSRand := trunc(DBMS_RANDOM.value * 10) + 1;
IF IdCSRand < 10 THEN
IdCS := ‘CS00′ + to_char(IdCSRand);
ELSEIF IdCSRand < 100 then
IdCS := ‘CS0′ + to_char(IdCSRand);
END IF;
hari := trunc(DBMS_RANDOM.value * 30) + 1;
bulan := trunc(DBMS_RANDOM.value * 12) + 1;
tahun := trunc(DBMS_RANDOM.value * 2) + 2009;
Tgl := TO_CHAR(hari) || ‘/’ || to_char(bulan) || ‘/’ || to_char(tahun);
INSERT INTO SalesORder values (IdSO, IdSL, IdCS, Tgl);
Trans := trunc(DBMS_RANDOM.VALUE * 2) + 1;
FOR J IN 1.. Trans LOOP
IdProRand := trunc(DBMS_RANDOM.value * 4) + 1;
qty := trunc(DBMS_RANDOM.value * 5) + 1;
IdPro := ‘P00′ || to_char(IdProRand);
insert into detailSalesOrder values(IdSO, IdPro, qty);
END LOOP;
END LOOP;
END;
With no Random
insert into SalesOrder values (‘SO001′,’SL001′,’CS001′,’01/01/2009′);
insert into SalesOrder values (‘SO002′,’SL002′,’CS002′,’01/05/2009′);
insert into SalesOrder values (‘SO003′,’SL002′,’CS003′,’01/07/2009′);
insert into SalesOrder values (‘SO004′,’SL003′,’CS004′,’01/08/2009′);
insert into SalesOrder values (‘SO005′,’SL001′,’CS005′,’01/10/2009′);
insert into SalesOrder values (‘SO006′,’SL002′,’CS006′,’01/12/2009′);
insert into SalesOrder values (‘SO007′,’SL001′,’CS007′,’01/01/2009′);
insert into SalesOrder values (‘SO008′,’SL003′,’CS008′,’01/01/2009′);
insert into detailSalesOrder values (‘SO001′,’P001′,2);
insert into detailSalesOrder values (‘SO001′,’P002′,1);
insert into detailSalesOrder values (‘SO002′,’P002′,3);
insert into detailSalesOrder values (‘SO003′,’P004′,4);
insert into detailSalesOrder values (‘SO004′,’P003′,5);
insert into detailSalesOrder values (‘SO005′,’P002′,6);
insert into detailSalesOrder values (‘SO006′,’P001′,2);
insert into detailSalesOrder values (‘SO006′,’P002′,1);
insert into detailSalesOrder values (‘SO007′,’P003′,1);
insert into detailSalesOrder values (‘SO008′,’P001′,2);
insert into detailSalesOrder values (‘SO008′,’P002′,2);
insert into detailSalesOrder values (‘SO008′,’P003′,2);
Kelompok SQL 06PBT
create table headerSalesOrder
(
No_So char(5) primary key,
kdCustomer char(5),
kdSales char(5),
tanggal date,
foreign key (kdCustomer) references msCustomer,
foreign key (kdSales) references msSales
);
create table detailSO
(
no_So char(5),
KdProduk char(5),
QTY NUMERIC,
primary key (noTransaksi,kdProduk),
foreign key (kdProduk) references msProduk
);
insert into headerSalesOrder values(’SO001′,’KC001′,’KS001′,’05/25/09′);
insert into headerSalesOrder values(’SO002′,’KC003′,’KS003′,’05/26/09′);
insert into headerSalesOrder values(’SO003′,’KC009′,’KS002′,’05/26/09′);
insert into detailSalesOrder values(’SO001′,’KP001′,2);
insert into detailSalesOrder values(’SO002′,’KP002′,1);
insert into headerSalesOrder values(’SO003′,’KP004′,10);
create table SalesOrder
(
KdSalesOrder varchar(6) primary key not null,
KdSales varchar(6) not null,
KdCustomer varchar(6) not null,
SalesDate date not null,
primary key(KdSalesOrder),
CONSTRAINT salesorder_fk1 FOREIGN KEY(KdSales) REFERENCES Sales(KdSales),
CONSTRAINT salesorder_fk2 FOREIGN KEY(CustomerID) REFERENCES Customer(KdCustomer)
);
create table DetailSalesOrder
(
KdSalesOrder varchar(6) not null,
KdProduct varchar(6) not null,
Qty int,
PRIMARY KEY(KdSalesOrder,KdProduct),
CONSTRAINT detailSalesOrder_fk FOREIGN KEY(KdSalesOrder) REFERENCES Sales(KdSalesOrder),
CONSTRAINT detailSalesOrder_fk2 FOREIGN KEY(KdProduct) REFERENCES Product(KdProduct)
);
insert into SalesOrder values (’KSO01′,’KS01′,’KC01′,’01/01/2009′);
insert into SalesOrder values (’KSO02′,’KS02′,’KC02′,’01/05/2009′);
insert into SalesOrder values (’KSO03′,’KS02′,’KC03′,’01/07/2009′);
insert into SalesOrder values (’KSO04′,’KS03′,’KC04′,’01/08/2009′);
insert into SalesOrder values (’KSO05′,’KS01′,’KC05′,’01/10/2009′);
insert into SalesOrder values (’KSO06′,’KS02′,’KC06′,’01/12/2009′);
insert into SalesOrder values (’KSO07′,’KS02′,’KC07′,’01/01/2009′);
insert into SalesOrder values (’KSO08′,’KS03′,’KC08′,’01/01/2009′);
insert into detailSalesOrder values (’KSO01′,’P01′,2);
insert into detailSalesOrder values (’KSO01′,’P02′,1);
insert into detailSalesOrder values (’KSO02′,’P02′,3);
insert into detailSalesOrder values (’KSO03′,’P04′,4);
insert into detailSalesOrder values (’KSO04′,’P03′,5);
insert into detailSalesOrder values (’KSO05′,’P02′,6);
insert into detailSalesOrder values (’KSO06′,’P01′,2);
insert into detailSalesOrder values (’KSO06′,’P02′,1);
insert into detailSalesOrder values (’KSO07′,’P03′,1);
insert into detailSalesOrder values (’KSO08′,’P01′,2);
insert into detailSalesOrder values (’KSO08′,’P02′,2);
insert into detailSalesOrder values (’KSO08′,’P03′,2);
INSERT INTO SO_Header VALUES(‘1′,’C0005′,’SP0002′,’2009-05-31′);
INSERT INTO SO_Detail VALUES(‘1′,’P0003′,50);
INSERT INTO SO_Detail VALUES(‘1′,’P0001′,50);
INSERT INTO SO_Detail VALUES(‘1′,’P0002′,50);
INSERT INTO SO_Detail VALUES(‘1′,’P0004′,50);
CREATE TABLE SO_Header (
NoSalesOrder int NOT NULL,
KdCust varchar(5) NOT NULL,
KdSales varchar (5) NOT NULL,
Tgl DATE NOT NULL ,
PRIMARY KEY (NoSalesOrder)
)
INSERT INTO SO_Header VALUES(1,’C0005′,’S0002′,’2009-05-31′);
CREATE TABLE SO_Detail (
NoSalesOrder int NOT NULL,
KdProduct varchar(5) NOT NULL,
Qty int NOT NULL,
)
INSERT INTO SO_Detail VALUES(1,’P0003′,50);
INSERT INTO SO_Detail VALUES(1,’P0002′,50);
INSERT INTO SO_Detail VALUES(1,’P0001′,50);
INSERT INTO SO_Detail VALUES(1,’P0004′,50);
Kelompok SQL 06PBT
create table headerSalesOrder
(
No_So char(5) primary key,
kdCustomer char(5),
kdSales char(5),
tanggal date,
foreign key (kdCustomer) references msCustomer,
foreign key (kdSales) references msSales
);
create table detailSO
(
no_So char(5),
KdProduk char(5),
QTY NUMERIC,
primary key (noTransaksi,kdProduk),
foreign key (kdProduk) references msProduk
);
insert into headerSalesOrder values(’SO001′,’KC001′,’KS001′,’05/25/09′);
insert into headerSalesOrder values(’SO002′,’KC003′,’KS003′,’05/26/09′);
insert into headerSalesOrder values(’SO003′,’KC009′,’KS002′,’05/26/09′);
insert into detailSalesOrder values(’SO001′,’KP001′,2);
insert into detailSalesOrder values(’SO002′,’KP002′,1);
insert into headerSalesOrder values(’SO003′,’KP004′,10)
create table HeaderSalesOrder (
KdSalesOrder varchar(8) primary key,
TanggalSalesOrder date,
KdCustomer references Customer,
kdSalesPerson references SalesPerson
)
create table DetailSalesOrder (
KdSalesOrder references SalesOrder,
KdProduk references Produk,
Qty number
)
INSERT INTO HeaderSalesOrder (KdSalesOrder,TanggalSalesOrder,KdCustomer,KdSalesPerson) VALUES (SO00001,’01-07-2009’,C00002, SP0001)
INSERT INTO HeaderSalesOrder (KdSalesOrder,TanggalSalesOrder,KdCustomer,KdSalesPerson) VALUES (SO00002,’24-08-2009’,C00008, SP0002)
INSERT INTO HeaderSalesOrder (KdSalesOrder,TanggalSalesOrder,KdCustomer,KdSalesPerson) VALUES (SO00003,’12-11-2009’,C00005, SP0003);
INSERT INTO DetailSalesOrder (KdSalesOrder,KdProduk,Qty) VALUES (SO00001,P001,15)
INSERT INTO DetailSalesOrder (KdSalesOrder,KdProduk,Qty) VALUES (SO00001,P002,10)
INSERT INTO DetailSalesOrder (KdSalesOrder,KdProduk,Qty) VALUES (SO00001,P003,17)
INSERT INTO DetailSalesOrder (KdSalesOrder,KdProduk,Qty) VALUES (SO00002,P001,27)
INSERT INTO DetailSalesOrder (KdSalesOrder,KdProduk,Qty VALUES (SO00002,P002,13)
INSERT INTO DetailSalesOrder (KdSalesOrder,KdProduk,Qty) VALUES (SO00002,P003,10)
INSERT INTO DetailSalesOrder (KdSalesOrder,KdProduk,Qty) VALUES (SO00003,P002,20)
INSERT INTO DetailSalesOrder (KdSalesOrder,KdProduk,Qty) VALUES (SO00003,P003,25)
INSERT INTO DetailSalesOrder (KdSalesOrder,KdProduk,Qty) VALUES (SO00003,P002,10)