Beranda > system database > Create database exsample

Create database exsample


CREATE DATABASE `The_Gank`;

USE `The_Gank`;

/* Tables */

 

CREATE TABLE `tbl_brg`

( `Id_Brng`    varchar(5) NOT NULL,

`Nm_Brng`  varchar(20) NOT NULL,

`PC`       varchar(10) DEFAULT ‘pcs’,

`Qty`     int(3),

`Price`        decimal(10,2),

`Remarks`   varchar(50),

PRIMARY KEY (`Id_Brng`))

TYPE = InnoDB;

CREATE TABLE `tbl_City`

( `Id_City`     char(3) NOT NULL,

`Nm_City`   varchar(20),

`Remarks`  varchar(50),

PRIMARY KEY (`Id_City`))

TYPE = InnoDB;

CREATE TABLE `tbl_cust`

( `Id_Cust`    varchar(5) NOT NULL,

`Nm_Cust`  varchar(25),

`Alamat`         varchar(50),

`No_Telp`      int(10),

`Id_City`        varchar(8),

`No_Fax`   int(10),

`Email`          varchar(50),

`Limit`          int(10),

`Remarks`     varchar(50),

PRIMARY KEY (`Id_Cust`))

TYPE = InnoDB;

CREATE TABLE `tbl_order_dtl`

( `Id_Order`    varchar(5) DEFAULT ‘IO’,

`Id_Brng`   varchar(5),

`Qty`    int(3),

`Disct`    int(10),

`Remarks`  varchar(50))

TYPE = InnoDB;

CREATE TABLE `tbl_order_hd`

( `Id_Order`     varchar(5) NOT NULL DEFAULT ‘IO’,

`Date`         date NOT NULL DEFAULT ‘0000-00-00′,

`Id_Cust`  varchar(20) NOT NULL,

`Jml`       int(10),

`Disct`     decimal(10,2),

`PPn`          int(3) DEFAULT ’10’,

`Total`        decimal(10,2),

`Remarks`   varchar(50),

PRIMARY KEY (`Id_Order`))

TYPE = InnoDB;

/* Indexes */

CREATE INDEX `Alamat` ON `tbl_cust` (`Alamat`);

CREATE INDEX `Id_City` ON `tbl_cust` (`Id_City`);

CREATE UNIQUE INDEX `Id_Cust` ON `tbl_cust` (`Id_Cust`);

CREATE INDEX `Id_Brng` ON `tbl_order_dtl` (`Id_Brng`);

CREATE INDEX `Id_Order` ON `tbl_order_dtl` (`Id_Order`);

 

/* Foreign Keys */

ALTER TABLE `tbl_City`

ADD CONSTRAINT `0_54`

FOREIGN KEY (`Id_City`)

REFERENCES `tbl_cust`(`Id_City`)

ON DELETE CASCADE

ON UPDATE CASCADE;

ALTER TABLE `tbl_order_dtl`

ADD CONSTRAINT `0_102`

FOREIGN KEY (`Id_Order`)

REFERENCES `tbl_order_hd`(`Id_Order`)

ON DELETE CASCADE

ON UPDATE CASCADE;

ALTER TABLE `tbl_order_dtl`

ADD CONSTRAINT `0_104`

FOREIGN KEY (`Id_Brng`)

REFERENCES `tbl_brg`(`Id_Brng`)

ON DELETE CASCADE

ON UPDATE CASCADE;

/* Data for table “tbl_brg” */

INSERT INTO `tbl_brg` (`Id_Brng`, `Nm_Brng`, `PC`, `Qty`, `Price`, `Remarks`) VALUES (‘00001’, ‘gel ballpoint’, ‘pcs’, 400, 3000, NULL);

INSERT INTO `tbl_brg` (`Id_Brng`, `Nm_Brng`, `PC`, `Qty`, `Price`, `Remarks`) VALUES (‘00002’, ‘ink ballpoint’, ‘pcs’, 450, 1500, NULL);

INSERT INTO `tbl_brg` (`Id_Brng`, `Nm_Brng`, `PC`, `Qty`, `Price`, `Remarks`) VALUES (‘00003’, ‘ruler plastik’, ‘pcs’, 350, 5000, ‘NULL’);

INSERT INTO `tbl_brg` (`Id_Brng`, `Nm_Brng`, `PC`, `Qty`, `Price`, `Remarks`) VALUES (‘00004’, ‘ruler besi’, ‘pcs’, 325, 10000, NULL);

INSERT INTO `tbl_brg` (`Id_Brng`, `Nm_Brng`, `PC`, `Qty`, `Price`, `Remarks`) VALUES (‘00005’, ‘eraser hitam’, ‘pcs’, 375, 6000, NULL);

INSERT INTO `tbl_brg` (`Id_Brng`, `Nm_Brng`, `PC`, `Qty`, `Price`, `Remarks`) VALUES (‘00006’, ‘eraser putih’, ‘pcs’, 450, 2000, NULL);

INSERT INTO `tbl_brg` (`Id_Brng`, `Nm_Brng`, `PC`, `Qty`, `Price`, `Remarks`) VALUES (‘00007’, ‘pensil HB’, ‘pcs’, 400, 1000, NULL);

INSERT INTO `tbl_brg` (`Id_Brng`, `Nm_Brng`, `PC`, `Qty`, `Price`, `Remarks`) VALUES (‘00008’, ‘pensil 2B’, ‘pcs’, 400, 2500, NULL);

INSERT INTO `tbl_brg` (`Id_Brng`, `Nm_Brng`, `PC`, `Qty`, `Price`, `Remarks`) VALUES (‘00009’, ‘drawing spidol’, ‘set’, 325, 8000, NULL);

INSERT INTO `tbl_brg` (`Id_Brng`, `Nm_Brng`, `PC`, `Qty`, `Price`, `Remarks`) VALUES (‘00010’, ‘crayon’, ‘set’, 330, 7500, NULL);

INSERT INTO `tbl_brg` (`Id_Brng`, `Nm_Brng`, `PC`, `Qty`, `Price`, `Remarks`) VALUES (‘00011’, ‘color pencil’, ‘set’, 340, 12000, NULL);

INSERT INTO `tbl_brg` (`Id_Brng`, `Nm_Brng`, `PC`, `Qty`, `Price`, `Remarks`) VALUES (‘00012’, ‘whitebrd spidol blk’, ‘pcs’, 350, 6500, NULL);

INSERT INTO `tbl_brg` (`Id_Brng`, `Nm_Brng`, `PC`, `Qty`, `Price`, `Remarks`) VALUES (‘00013’, ‘whitebrd spidol red’, ‘pcs’, 335, 6500, NULL);

INSERT INTO `tbl_brg` (`Id_Brng`, `Nm_Brng`, `PC`, `Qty`, `Price`, `Remarks`) VALUES (‘00014’, ‘whitebrd spidol grn’, ‘pcs’, 335, 6500, NULL);

INSERT INTO `tbl_brg` (`Id_Brng`, `Nm_Brng`, `PC`, `Qty`, `Price`, `Remarks`) VALUES (‘00015’, ‘whitebrd spidol blu’, ‘pcs’, 335, 6500, NULL);

INSERT INTO `tbl_brg` (`Id_Brng`, `Nm_Brng`, `PC`, `Qty`, `Price`, `Remarks`) VALUES (‘00016’, ‘permanent spidol blk’, ‘pcs’, 325, 8000, NULL);

INSERT INTO `tbl_brg` (`Id_Brng`, `Nm_Brng`, `PC`, `Qty`, `Price`, `Remarks`) VALUES (‘00017’, ‘permanent spidol red’, ‘pcs’, 325, 8000, NULL);

INSERT INTO `tbl_brg` (`Id_Brng`, `Nm_Brng`, `PC`, `Qty`, `Price`, `Remarks`) VALUES (‘00018’, ‘permanent spidol grn’, ‘pcs’, 325, 8000, NULL);

INSERT INTO `tbl_brg` (`Id_Brng`, `Nm_Brng`, `PC`, `Qty`, `Price`, `Remarks`) VALUES (‘00019’, ‘permanent spidol blu’, ‘pcs’, 325, 8000, NULL);

INSERT INTO `tbl_brg` (`Id_Brng`, `Nm_Brng`, `PC`, `Qty`, `Price`, `Remarks`) VALUES (‘00020’, ‘mechanical pencil’, ‘pcs’, 350, 8500, NULL);

COMMIT;

/* Data for table “tbl_City” */

INSERT INTO `tbl_City` (`Id_City`, `Nm_City`, `Remarks`) VALUES (‘BG1’, ‘Bogor’, NULL);

INSERT INTO `tbl_City` (`Id_City`, `Nm_City`, `Remarks`) VALUES (‘JP1’, ‘Jakarta Pusat’, NULL);

INSERT INTO `tbl_City` (`Id_City`, `Nm_City`, `Remarks`) VALUES (‘JS1’, ‘Jakarta Selatan’, NULL);

INSERT INTO `tbl_City` (`Id_City`, `Nm_City`, `Remarks`) VALUES (‘JT1’, ‘Jakarta Timur’, NULL);

INSERT INTO `tbl_City` (`Id_City`, `Nm_City`, `Remarks`) VALUES (‘JU1’, ‘Jakarta Utara’, NULL);

COMMIT;

/* Data for table “tbl_cust” */

INSERT INTO `tbl_cust` (`Id_Cust`, `Nm_Cust`, `Alamat`, `No_Telp`, `Id_City`, `No_Fax`, `Email`, `Limit`, `Remarks`) VALUES (‘AY001’, ‘Agus Yadi’, ‘Jl. Nidji Blok 14/19’, 9876789, ‘JP1’, 9876789, ‘Agus_yadi@gmail.com’, 5000000, ‘Membercard belum ada’);

INSERT INTO `tbl_cust` (`Id_Cust`, `Nm_Cust`, `Alamat`, `No_Telp`, `Id_City`, `No_Fax`, `Email`, `Limit`, `Remarks`) VALUES (‘AY002’, ‘Aminuddin Yahya’, ‘Wisma Podomoro Lt. 4’, 4432322, ‘JU1’, 4432322, ‘udin_gaoel@indosat-m3.net’, 15000000, NULL);

INSERT INTO `tbl_cust` (`Id_Cust`, `Nm_Cust`, `Alamat`, `No_Telp`, `Id_City`, `No_Fax`, `Email`, `Limit`, `Remarks`) VALUES (‘BM001’, ‘Budiman’, ‘Jl. Senopati 3 No. 3’, 4443331, ‘JP1’, 4443331, ‘budiman@yahoo.co.id’, 5000000, NULL);

INSERT INTO `tbl_cust` (`Id_Cust`, `Nm_Cust`, `Alamat`, `No_Telp`, `Id_City`, `No_Fax`, `Email`, `Limit`, `Remarks`) VALUES (‘BM002’, ‘Brambang Mellow’, ‘Jl. Pasar Jatinegara No. 89’, 9783343, ‘JT1’, 9783343, ‘bawang_goreng@yahoo.com’, 15000000, NULL);

INSERT INTO `tbl_cust` (`Id_Cust`, `Nm_Cust`, `Alamat`, `No_Telp`, `Id_City`, `No_Fax`, `Email`, `Limit`, `Remarks`) VALUES (‘MO001’, ‘Michael Otong’, ‘Jl. MT Haryono kav. 13’, 5324221, ‘JT1’, 5324221, ‘otong-man@yahoo.co.id’, 10000000, NULL);

INSERT INTO `tbl_cust` (`Id_Cust`, `Nm_Cust`, `Alamat`, `No_Telp`, `Id_City`, `No_Fax`, `Email`, `Limit`, `Remarks`) VALUES (‘SB001’, ‘Susilo Bambang’, ‘Taman Pelangi blok RR No. 150’, 5643433, ‘BG1’, 5643433, ‘sby_keren@jakarta.go.id’, 15000000, NULL);

INSERT INTO `tbl_cust` (`Id_Cust`, `Nm_Cust`, `Alamat`, `No_Telp`, `Id_City`, `No_Fax`, `Email`, `Limit`, `Remarks`) VALUES (‘SB002’, ‘Si Boy’, ‘Jl. Menteng No. 45’, 5456654, ‘JS1’, 5456654, ‘catatan-si-boy@gmail.com’, 15000000, ‘Membercard belum ada’);

INSERT INTO `tbl_cust` (`Id_Cust`, `Nm_Cust`, `Alamat`, `No_Telp`, `Id_City`, `No_Fax`, `Email`, `Limit`, `Remarks`) VALUES (‘SP001’, ‘Si Pitung’, ‘Jl. Bandar Kemayoran gang 12 No. 15’, 2223442, ‘JP1’, 2223442, ‘pitung_jagoan@hotmail.com’, 5000000, ‘Membercard belum ada’);

INSERT INTO `tbl_cust` (`Id_Cust`, `Nm_Cust`, `Alamat`, `No_Telp`, `Id_City`, `No_Fax`, `Email`, `Limit`, `Remarks`) VALUES (‘TR001’, ‘The Rock’, ‘Kompleks Angkatan Laut No. 12a’, 4332323, ‘JU1’, 4332323, ‘de-rok@wwe.com’, 10000000, NULL);

INSERT INTO `tbl_cust` (`Id_Cust`, `Nm_Cust`, `Alamat`, `No_Telp`, `Id_City`, `No_Fax`, `Email`, `Limit`, `Remarks`) VALUES (‘TR002’, ‘Tina Rina’, ‘Apartemen Sudirman Lt. 99 No. 999’, 1333131, ‘JP1’, 1333131, ‘tina_cute_gals@telkom.co.id’, 10000000, ‘Membercard belum ada’);

COMMIT;

/* Data for table “tbl_order_dtl” */

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO001’, ‘00001’, 25, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO003’, ‘00012’, 45, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO004’, ‘00015’, 25, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO005’, ‘00018’, 20, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO006’, ‘00019’, 19, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO007’, ‘00004’, 19, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO008’, ‘00010’, 30, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO009’, ‘00014’, 10, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO010’, ‘00017’, 15, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO011’, ‘00002’, 5, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO012’, ‘00006’, 10, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO013’, ‘00009’, 15, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO014’, ‘00001’, 20, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO015’, ‘00003’, 25, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO017’, ‘00008’, 35, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO018’, ‘00007’, 40, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO019’, ‘00011’, 45, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO020’, ‘00010’, 50, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO021’, ‘00015’, 45, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO022’, ‘00018’, 40, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO023’, ‘00014’, 35, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO024’, ‘00016’, 30, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO025’, ‘00013’, 25, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO026’, ‘00019’, 20, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO027’, ‘00020’, 15, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO028’, ‘00001’, 10, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO029’, ‘00019’, 5, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO030’, ‘00003’, 5, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO031’, ‘00018’, 10, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO032’, ‘00005’, 15, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO033’, ‘00017’, 20, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO034’, ‘00009’, 25, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO035’, ‘00015’, 30, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO036’, ‘00016’, 35, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO037’, ‘00004’, 40, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO038’, ‘00014’, 45, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO039’, ‘00013’, 50, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO040’, ‘00007’, 45, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO041’, ‘00006’, 40, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO042’, ‘00012’, 35, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO043’, ‘00013’, 30, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO044’, ‘00004’, 25, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO045’, ‘00015’, 20, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO046’, ‘00011’, 15, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO047’, ‘00010’, 10, 5, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO048’, ‘00001’, 5, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO049’, ‘00020’, 10, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO002’, ‘00007’, 80, 10, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO016’, ‘00005’, 30, 6, NULL);

INSERT INTO `tbl_order_dtl` (`Id_Order`, `Id_Brng`, `Qty`, `Disct`, `Remarks`) VALUES (‘IO050’, ‘00017’, 15, 5, NULL);

COMMIT;

/* Data for table “tbl_order_hd” */

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO001’, ‘2007-01-15’, ‘BM001’, 75000, 3750, 10, 78375, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO002’, ‘2007-01-17’, ‘AY001’, 80000, 8000, 10, 79200, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO003’, ‘2007-01-22’, ‘AY002’, 292500, 29250, 10, 289575, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO004’, ‘2007-01-22’, ‘TR001’, 162500, 8125, 10, 169812.5, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO005’, ‘2007-02-01’, ‘SB001’, 160000, 16000, 10, 158400, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO006’, ‘2007-02-10’, ‘SP001’, 152000, 7600, 10, 158840, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO007’, ‘2007-02-18’, ‘BM002’, 190000, 9500, 10, 198550, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO008’, ‘2007-02-28’, ‘TR002’, 225000, 22500, 10, 222750, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO009’, ‘2007-03-04’, ‘SB002’, 65000, 6500, 10, 64350, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO010’, ‘2007-03-08’, ‘MO001’, 120000, 6000, 10, 125400, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO011’, ‘2007-03-14’, ‘AY001’, 7500, 375, 10, 7837.5, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO012’, ‘2007-03-20’, ‘SB002’, 65000, 6500, 10, 64350, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO013’, ‘2007-03-21’, ‘SB001’, 120000, 12000, 10, 118800, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO014’, ‘2007-04-02’, ‘BM001’, 60000, 3000, 10, 62700, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO015’, ‘2007-04-11’, ‘AY002’, 120000, 12000, 10, 118800, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO016’, ‘2007-04-14’, ‘SP001’, 180000, 9000, 10, 188100, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO017’, ‘2007-04-29’, ‘BM002’, 297500, 14875, 10, 310887.5, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO018’, ‘2007-05-03’, ‘AY001’, 40000, 4000, 10, 39600, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO019’, ‘2007-05-07’, ‘TR002’, 540000, 54000, 10, 534600, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO020’, ‘2007-05-12’, ‘SP001’, 375000, 18750, 10, 391875, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO021’, ‘2007-05-15’, ‘MO001’, 292500, 14625, 10, 305662.5, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO022’, ‘2007-05-23’, ‘BM001’, 320000, 32000, 10, 316800, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO023’, ‘2007-05-26’, ‘SB001’, 227500, 22750, 10, 225225, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO024’, ‘2007-05-28’, ‘SB001’, 240000, 12000, 10, 250800, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO025’, ‘2007-06-04’, ‘AY001’, 162500, 16250, 10, 160875, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO026’, ‘2007-06-07’, ‘SB002’, 160000, 8000, 10, 167875, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO027’, ‘2007-06-11’, ‘TR001’, 127500, 6375, 10, 133237.5, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO028’, ‘2007-06-18’, ‘MO001’, 30000, 3000, 10, 29700, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO029’, ‘2007-06-22’, ‘AY001’, 40000, 4000, 10, 39600, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO030’, ‘2007-06-26’, ‘BM002’, 25000, 1250, 10, 26125, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO031’, ‘2007-07-05’, ‘TR001’, 80000, 4000, 10, 83600, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO032’, ‘2007-07-13’, ‘MO001’, 75000, 7500, 10, 74250, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO033’, ‘2007-07-17’, ‘BM001’, 160000, 16000, 10, 158400, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO034’, ‘2007-07-27’, ‘AY002’, 200000, 10000, 10, 209000, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO035’, ‘2007-08-04’, ‘MO001’, 195000, 19500, 10, 193050, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO036’, ‘2007-08-05’, ‘SP001’, 280000, 14000, 10, 292600, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO037’, ‘2007-08-08’, ‘TR001’, 400000, 20000, 10, 418000, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO038’, ‘2007-08-13’, ‘SB002’, 292500, 29250, 10, 289575, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO039’, ‘2007-08-02’, ‘TR002’, 325000, 32500, 10, 321750, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO040’, ‘2007-08-14’, ‘AY001’, 45000, 2250, 10, 47025, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO041’, ‘2007-08-20’, ‘BM002’, 240000, 12000, 10, 250800, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO042’, ‘2007-09-05’, ‘BM002’, 240000, 12000, 10, 250800, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO043’, ‘2007-09-21’, ‘AY002’, 195000, 19500, 10, 193050, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO044’, ‘2007-09-30’, ‘SB002’, 250000, 12500, 10, 261250, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO045’, ‘2007-10-13’, ‘TR001’, 130000, 13000, 10, 128700, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO046’, ‘2007-10-21’, ‘TR002’, 180000, 9000, 10, 188100, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO047’, ‘2007-10-29’, ‘SP001’, 75000, 3750, 10, 78375, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO048’, ‘2007-11-23’, ‘BM001’, 15000, 1500, 10, 14850, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO049’, ‘2007-12-11’, ‘SB001’, 85000, 8500, 10, 84150, NULL);

INSERT INTO `tbl_order_hd` (`Id_Order`, `Date`, `Id_Cust`, `Jml`, `Disct`, `PPn`, `Total`, `Remarks`) VALUES (‘IO050’, ‘2007-12-24’, ‘AY002’, 120000, 6000, 10, 125400, NULL);

COMMIT;

Tugas ke 2

1.Tunjukkan nilai Qty dari masing-masing barang yang sudah terjual

mysql> select Id_Brng, sum(Qty) from tbl_order_dtl

-> GROUP BY Id_Brng

-> ORDER BY Id_Brng;

+———–+—————+

| Id_Brng | sum(Qty) |

+———–+—————+

| 00001     |            60 |

| 00002     |             5 |

| 00003     |            30 |

| 00004     |            84 |

| 00005     |            45 |

| 00006     |            50 |

| 00007     |           165 |

| 00008     |            35 |

| 00009     |            40 |

| 00010     |            90 |

| 00011     |            60 |

| 00012     |            80 |

| 00013     |           105 |

| 00014     |            90 |

| 00015     |           120 |

| 00016     |            65 |

| 00017     |            50 |

| 00018     |            70 |

| 00019     |            44 |

| 00020     |            25 |

+———–+—————+

20 rows in set (0.33 sec)

2. Tunjukkan barang apa saja yang paling banyak terjual!

3. Tunjukkan barang apa saja yang terjual dengan Qty paling sedikit 25 pieces!

mysql> select Id_Brng, sum(Qty) from tbl_order_dtl

-> GROUP BY Id_Brng

-> HAVING SUM(Qty)>=25

-> ORDER BY Id_Brng;

+———–+—————+

| Id_Brng | sum(Qty) |

+———–+—————+

| 00001     |            60 |

| 00003     |            30 |

| 00004     |            84 |

| 00005     |            45 |

| 00006     |            50 |

| 00007     |           165 |

| 00008     |            35 |

| 00009     |            40 |

| 00010     |            90 |

| 00011     |            60 |

| 00012     |            80 |

| 00013     |           105 |

| 00014     |            90 |

| 00015     |           120 |

| 00016     |            65 |

| 00017     |            50 |

| 00018     |            70 |

| 00019     |            44 |

| 00020     |            25 |

+———–+—————+

19 rows in set (0.03 sec)

mysql>

Kategori:system database
  1. Belum ada komentar.
  1. No trackbacks yet.

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: