Data Definition Language untuk Database JualanDB

create database jualanDB;

use jualandb;

drop table msbarang;

drop table mssatuan;

drop table mskasir;

drop table trheader;

drop table trdetail;

drop table mscarabayar;

drop table mscustomer;

create table msbarang (

  kdbrg varchar(10) primary key,

  nmbrg varchar(50),

  kdsat varchar(3),

  hrgsat int,

  jmlstok int

);

create table mssatuan (

  kdsat varchar(10) primary key,

  nmsat varchar(50)

);

create table mskasir (

  kdksr varchar(10) primary key,

  nmksr varchar(50),

  almksr varchar(50),

  tlpksr varchar(50),

  emlcust varchar(50),

  tgllhrksr date,

  noIDksr varchar(50),

  tglmsk date

);

create table trheader (

  kdtr varchar(10) primary key,

  tgltr datetime,

  kdcust varchar(10),

  kdksr varchar(10),

  kdbyr tinyint

);

create table trdetail (

  kdtr varchar(10),

  kdbrg varchar(10),

  hrgsat int,

  qtybrg int

);

create table mscarabayar (

  kdbyr varchar(10) primary key,

  descbyr varchar(50)

);

create table mscustomer (

  kdcust varchar(10) primary key,

  nmcust varchar(50),

  almcust varchar(50),

  tlpcust varchar(50),

  emlcust varchar(50),

  tgllhrcust date,

  noIDcust varchar(50),

  tglinp date

);

insert into msbarang values

  ('B0001','Semen Padang','S0001',70000,500),

  ('B0002','Pasir Cimangkok','S0002',300000,6),

  ('B0003','Bata Merah','S0003',500,2500),

  ('B0004','Bata Kuning','S0003',1000,600),

  ('B0005','Batako','S0003',1500,3000),

  ('B0006','Bata Putih','S0003',2500,100),

  ('B0007','Semen Tiga Roda','S0001',80000,220),

  ('B0008','Batu Pondasi','S0002',500000,5),

  ('B0009','Genteng Press','S0003',2500,120),

  ('B0010','Batu Split','S0002',400000,7),

  ('B0011','Genteng Beton','S0001',2000,60),

  ('B0012','Tripleks KW 2','S0004',60000,30),

  ('B0013','Martil Cap Bango','S0001',40000,24),

  ('B0014','Semen Kujang','S0001',74500,30),

  ('B0015','Cat Tembok cap Jalak 1 ltr','S0005',75000,13);

insert into mskasir values

  ('K0001','Jennifer Lupis','Pd. Gede',null,null,'1980-11-11','1234','2010-11-11'),

  ('K0002','Tuti Martuti','Jatiwaringin',null,null,'1981-11-11','5323','2010-11-11'),

  ('K0003','Fiona','Klp. Gading',null,null,'1980-12-11','5353','2010-11-11'),

  ('K0004','Lenny Marleni','Pluit',null,null,'1981-11-1','1113','2010-11-11'),

  ('K0005','Usilowati','Kramat Jati',null,null,'1980-1-12','2234','2010-11-11');

 

insert into mscustomer values

  ('C0001','Joni','Pd.Ranggon',null,null,null,null,'2012-12-14'),

  ('C0002','Didi','Jatimakmur',null,null,null,null,'2012-12-14'),

  ('C0003','Harry','Lubang Buaya',null,null,null,null,'2012-12-14');

 

insert into trheader values

  ('TR0001','2012-12-14','C0001','K0002',null),

  ('TR0002','2012-12-14','C0002','K0001',null),

  ('TR0003','2012-12-14','C0003','K0004',null);

insert into trdetail values

  ('TR0001','B0004',1000,2),

  ('TR0001','B0001',70000,1),

  ('TR0002','B0003',500,7),

  ('TR0003','B0010',400000,2),

  ('TR0003','B0006',2500,3),

  ('TR0003','B0003',500,10);

 

insert into mssatuan values

  ('S0001','sak'),

  ('S0002','m kubik'),

  ('S0003','biji'),

  ('S0004','lembar'),

  ('S0005','liter');