schema_sales.sql
CONN / AS SYSDBA
DROP USER SALES CASCADE;
SPOOL C:\SCRIPT-SQL11G.LOG
CREATE USER SALES IDENTIFIED BY ORACLE
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS;
GRANT DBA TO SALES;
CONNECT SALES/ORACLE;
alter session set nls_date_format='DD-MON-YYYY';
CREATE TABLE JABATAN (
NAMA_JBT VARCHAR2(50) NOT NULL);
CREATE TABLE WILAYAH
(KODE_WILAYAH NUMBER(3) CONSTRAINT PK_WIL PRIMARY KEY,
NAMA_WILAYAH VARCHAR2(30) NOT NULL);
CREATE TABLE GUDANG (
KODE_GUDANG NUMBER(7) CONSTRAINT PK_GUDANG PRIMARY KEY,
KODE_WILAYAH NUMBER(7)REFERENCES WILAYAH(KODE_WILAYAH),
TELEPON_GUDANG VARCHAR2(15),
ALAMAT VARCHAR2(400),
KOTA VARCHAR2(35),
KODE_POS VARCHAR2(10),
MANAGER_ID NUMBER(7)
);
CREATE TABLE GAMBAR (
IMAGE_ID NUMBER(7) CONSTRAINT PK_GAMBAR PRIMARY KEY,
FORMAT VARCHAR2(25),
USE_FILENAME VARCHAR2(1),
FILENAME VARCHAR2(255),
IMAGE LONG RAW
);
CREATE TABLE BARANG (
KODE_BRG NUMBER(7)CONSTRAINT PK_BARANG PRIMARY KEY,
NAMA_BRG VARCHAR2(50),
IMAGE_ID NUMBER(7),
HARGA_JUAL NUMBER(11,2),
SATUAN VARCHAR2(25),
KETERANGAN VARCHAR2(30)
);
CREATE TABLE BAGIAN
(KODE_BAG NUMBER(7)CONSTRAINT PK_BAGIAN PRIMARY KEY,
NAMA_BAG VARCHAR2(50),
KODE_WILAYAH NUMBER(7)REFERENCES WILAYAH(KODE_WILAYAH));
CREATE TABLE PELANGGAN (
ID_PLG NUMBER(7) CONSTRAINT PK_PELANGGAN PRIMARY KEY,
NAMA_PLG VARCHAR2(50) NOT NULL,
TELEPON_PLG VARCHAR2(15),
ALAMAT VARCHAR2(400),
KOTA VARCHAR2(35),
KODE_POS VARCHAR2(10),
CREDIT_RATING VARCHAR2(9),
SALES_REP_ID NUMBER(7),
KODE_WILAYAH NUMBER(7) REFERENCES WILAYAH(KODE_WILAYAH),
KETERANGAN VARCHAR2(255),
STATUS VARCHAR2(1) NOT NULL,
PENGIRIMAN VARCHAR2(1) NOT NULL
);
CREATE TABLE PEGAWAI (
ID_PEGAWAI NUMBER(7) CONSTRAINT PK_PEGAWAI PRIMARY KEY,
NAMA_PEGAWAI VARCHAR2(25) NOT NULL,
USERID VARCHAR2(8),
EMAIL VARCHAR2(30) UNIQUE,
TGL_MASUK DATE,
MANAGER_ID NUMBER(7),
JABATAN VARCHAR2(25),
KODE_BAG NUMBER(7),
GAJI NUMBER(11,2),
PCT_KOMISI NUMBER(4,2)
);
CREATE TABLE HD_SALES (
ORDER_ID NUMBER(7) CONSTRAINT PK_ORDER PRIMARY KEY,
ID_PLG NUMBER(7) REFERENCES PELANGGAN(ID_PLG),
TGL_ORDER DATE,
TGL_KIRIM DATE,
SALES_REP_ID NUMBER(7),
TOTAL NUMBER(12,2),
CARA_BAYAR VARCHAR2(6),
STATUS VARCHAR2(1)
);
CREATE TABLE ITEM (
ORDER_ID NUMBER(7) REFERENCES HD_SALES (ORDER_ID),
NOMOR NUMBER(7),
KODE_BRG NUMBER(7) REFERENCES BARANG(KODE_BRG),
HARGA NUMBER(11,2),
JLH NUMBER(9),
JLH_KIRIM NUMBER(9)
);
CREATE TABLE INVENTORY(
KODE_BRG NUMBER(7) REFERENCES BARANG(KODE_BRG),
KODE_GUDANG NUMBER(7) REFERENCES GUDANG(KODE_GUDANG),
JLH_STOCK NUMBER(9),
REORDER_POINT NUMBER(9),
MAX_STOCK NUMBER(9),
TGL_RESTOCK DATE
);
CREATE TABLE riwayat_jabatan
(id_peg NUMBER(5),
jabatan VARCHAR2(30),
kode_bag NUMBER(4),
tgl_ganti DATE);
-- ISI DATA TABEL JABATAN
INSERT INTO JABATAN VALUES ('PRESIDENT');
INSERT INTO JABATAN VALUES ('SALES REPRESENTATIVE');
INSERT INTO JABATAN VALUES ('STOCK CLERK');
INSERT INTO JABATAN VALUES ('VP, ADMINISTRATION');
INSERT INTO JABATAN VALUES ('VP, FINANCE');
INSERT INTO JABATAN VALUES ('VP, OPERATIONS');
INSERT INTO JABATAN VALUES ('VP, SALES');
INSERT INTO JABATAN VALUES ('WAREHOUSE MANAGER');
-- ISI DATA TABEL WILAYAH
INSERT INTO WILAYAH VALUES(1,'JAWA');
INSERT INTO WILAYAH VALUES(2,'SUMATERA');
INSERT INTO WILAYAH VALUES(3,'KALIMANTAN');
INSERT INTO WILAYAH VALUES(4,'SULAWESI');
INSERT INTO WILAYAH VALUES(5,'PAPUA');
-- ISI DATA TABEL BAGIAN
INSERT INTO BAGIAN VALUES(10,'FINANCE', 1);
INSERT INTO BAGIAN VALUES(31, 'SALES', 1);
INSERT INTO BAGIAN VALUES(32, 'ACCOUNTING', 2);
INSERT INTO BAGIAN VALUES(33, 'MARKETING', 3);
INSERT INTO BAGIAN VALUES(34, 'SECURITY', 4);
INSERT INTO BAGIAN VALUES(35, 'PAYROLL', 5);
INSERT INTO BAGIAN VALUES(41, 'OPERATIONS', 1);
INSERT INTO BAGIAN VALUES(42, 'HUMAN RESOURCES', 2);
INSERT INTO BAGIAN VALUES(43, 'STRATEGIC PLANNING', 3);
INSERT INTO BAGIAN VALUES(44, 'MAINTENANCE', 4);
INSERT INTO BAGIAN VALUES(45, 'TECHNICAL WRITING', 5);
INSERT INTO BAGIAN VALUES(50, 'ADMINISTRATION', 1);
-- ISI DATA TABEL BARANG
INSERT INTO BARANG VALUES( 10011, 'Abit AB9-Pro' ,1001, 1500000,'PCS', 'ABIT AB9-PRO IP965');
INSERT INTO BARANG VALUES( 10012, 'ASUS P5B Plus Viste Edt' ,1002, 2000000,'PCS', 'ASUS P5B Plus Viste Edt Ip965');
INSERT INTO BARANG VALUES( 10013, 'Asus Striker Extreme' ,1003, 3760000,'PCS', 'ASUS STRIKER EXTREME vVIDIA');
INSERT INTO BARANG VALUES( 10021, 'Biostar TForce %P965' ,1011, 970000,'PCS', 'BIOSTAR TFORCE P965');
INSERT INTO BARANG VALUES( 10022, 'Gigabyte GA_P31S3L' ,1012, 1000000,'PCS', 'GIGABYTE GA-P31S3L');
INSERT INTO BARANG VALUES( 10023, 'Gigabyte GA_945PL_S3' ,1013, 890000,'PCS', 'GIGABYTE GA-945PL-S3');
INSERT INTO BARANG VALUES( 20106, 'Seagate 80GB' ,NULL, 470000,'PCS', 'SEAGATE 80GB 7200');
INSERT INTO BARANG VALUES( 20108, 'Seagate 160GB' ,NULL, 540000,'PCS', 'SEAGATE 160GB SATA-II');
INSERT INTO BARANG VALUES( 20201, 'Seagate 250GB' ,NULL, 700000,'PCS', 'SEAGATE 250GB SATA-II');
INSERT INTO BARANG VALUES( 20512, 'Seagate 500gb' ,NULL, 1950000,'PCS', 'SEAGATE 500GB SATA-II');
INSERT INTO BARANG VALUES( 30321, 'Epson R-230' ,1291, 1300000,'PCS', 'EPSON PHOTO R-230');
INSERT INTO BARANG VALUES( 30326, 'Epson LQ2180' ,1296, 5500000,'PCS', 'EPSON LQ 2180');
INSERT INTO BARANG VALUES( 30421, 'HP Laserjet 1020' ,NULL, 1300000,'PCS', 'HP PRINTER LASERJET 1020');
INSERT INTO BARANG VALUES( 30426, 'HP Office Jet 4355' ,NULL, 955000,'PCS', 'HP PRINTER OFFICE JET 4355');
INSERT INTO BARANG VALUES( 30433, 'HP DJ 1360' ,NULL, 435000,'PCS', 'HP PRINTER DJ 1360');
INSERT INTO BARANG VALUES( 32779, 'Laserjet E120N' ,NULL, 1320000,'PCS', 'LEXMARK PRINTER E120N');
INSERT INTO BARANG VALUES( 32861, 'Laser E120' ,1829, 600000,'PCS', 'LEXMARK PRINTER E120');
INSERT INTO BARANG VALUES( 40421, 'EPRO INT.' ,1381, 191000,'PCS', 'EPRO INT.');
INSERT INTO BARANG VALUES( 40422, 'GADMEI EXTERNAL' ,1382, 139000,'PCS', 'GADMEI EXTERNAL');
INSERT INTO BARANG VALUES( 41010, 'GADMEI FOR LCD 5830' ,NULL, 340000,'PCS', 'GADMEI FOR LCD 5830');
INSERT INTO BARANG VALUES( 41020, 'K-WORLD CAPTURE INT+RMT' ,NULL, 280000,'PCS', 'K-WORLD CAPTURE INT+RMT');
INSERT INTO BARANG VALUES( 41050, 'PIXELVIEW TV P7000' ,NULL, 820000,'PCS', 'PIXELVIEW TV P7000');
INSERT INTO BARANG VALUES( 41080, 'PIXELVIEW PLAY TV PRO 3' ,NULL, 230000,'PCS', 'PIXELVIEW PLAY TV PRO 3');
INSERT INTO BARANG VALUES( 41100, 'CHRONOS PCMCIA TV TUNER' ,NULL, 440000,'PCS', 'CHRONOS PCMCIA TV TUNER');
INSERT INTO BARANG VALUES( 50169, 'ALTEC LEANSING BXR 1120' ,1119, 162000,'PCS', 'ALTEC LEANSING BXR 1120');
INSERT INTO BARANG VALUES( 50273, 'ALTEC LEANSING BXR 1121' ,1223, 285000,'PCS', 'ALTEC LEANSING');
INSERT INTO BARANG VALUES( 50417, 'ALTEC LEANSING 151' ,1367, 535000,'PCS', 'ALTEC LEANSING');
INSERT INTO BARANG VALUES( 50418, 'ALTEC LEANSING FX-6021' ,1368, 1690000,'PCS', 'ALTEC LEANSING');
INSERT INTO BARANG VALUES( 50419, 'ALTEC LEANSING MX-5021' ,1369, 1595000,'PCS', 'ALTEC LEANSING');
INSERT INTO BARANG VALUES( 50530, 'ALTEC LEANSING FX-5051' ,1480, 2325000,'PCS', 'ALTEC LEANSING');
INSERT INTO BARANG VALUES( 50532, 'ALTEC LEANSING VS-4021' ,1482, 1215000,'PCS', 'ALTEC LEANSING');
INSERT INTO BARANG VALUES( 50536, 'ALTEC LEANSING XT 2' ,1486, 825000,'PCS', 'ALTEC LEANSING');
-- ISI DATA TABEL GAMBAR
INSERT INTO GAMBAR VALUES( 1001, 'JTIFF', 'Y', 'bunboot.tif',NULL);
INSERT INTO GAMBAR VALUES( 1002, 'JTIFF', 'Y', 'aceboot.tif',NULL);
INSERT INTO GAMBAR VALUES( 1003, 'JTIFF', 'Y', 'proboot.tif',NULL);
INSERT INTO GAMBAR VALUES( 1011, 'JTIFF', 'Y', 'bunpole.tif',NULL);
INSERT INTO GAMBAR VALUES( 1012, 'JTIFF', 'Y', 'acepole.tif',NULL);
INSERT INTO GAMBAR VALUES( 1013, 'JTIFF', 'Y', 'propole.tif',NULL);
INSERT INTO GAMBAR VALUES( 1291, 'JTIFF', 'Y', 'gpbike.tif',NULL);
INSERT INTO GAMBAR VALUES( 1296, 'JTIFF', 'Y', 'himbike.tif',NULL);
INSERT INTO GAMBAR VALUES( 1829, 'JTIFF', 'Y', 'safthelm.tif',NULL);
INSERT INTO GAMBAR VALUES( 1381, 'JTIFF', 'Y', 'probar.tif',NULL);
INSERT INTO GAMBAR VALUES( 1382, 'JTIFF', 'Y', 'curlbar.tif',NULL);
INSERT INTO GAMBAR VALUES( 1119, 'JTIFF', 'Y', 'baseball.tif',NULL);
INSERT INTO GAMBAR VALUES( 1223, 'JTIFF', 'Y', 'chaphelm.tif',NULL);
INSERT INTO GAMBAR VALUES( 1367, 'JTIFF', 'Y', 'grglove.tif',NULL);
INSERT INTO GAMBAR VALUES( 1368, 'JTIFF', 'Y', 'alglove.tif',NULL);
INSERT INTO GAMBAR VALUES( 1369, 'JTIFF', 'Y', 'stglove.tif',NULL);
INSERT INTO GAMBAR VALUES( 1480, 'JTIFF', 'Y', 'cabbat.tif',NULL);
INSERT INTO GAMBAR VALUES( 1482, 'JTIFF', 'Y', 'pucbat.tif',NULL);
INSERT INTO GAMBAR VALUES( 1486, 'JTIFF', 'Y', 'winbat.tif',NULL);
-- ISI DATA TABEL GUDANG
INSERT INTO GUDANG VALUES( 101, 1 ,NULL ,NULL, 'SEMARANG',NULL, 6);
INSERT INTO GUDANG VALUES( 10501, 5 ,NULL ,NULL, 'SORONG',NULL, 10);
INSERT INTO GUDANG VALUES( 201, 2 ,NULL ,NULL, 'MEDAN',NULL, 7);
INSERT INTO GUDANG VALUES( 301, 3 ,NULL ,NULL, 'PONTIANAK',NULL, 8);
INSERT INTO GUDANG VALUES( 401, 4 ,NULL ,NULL, 'UJUNG PANDANG',NULL, 9);
-- ISI DATA TABEL INVENTORY
INSERT INTO INVENTORY VALUES( 10011, 101, 650, 625, 1100,NULL);
INSERT INTO INVENTORY VALUES( 10012, 101, 600, 560, 1000,NULL);
INSERT INTO INVENTORY VALUES( 10012, 10501, 300, 300, 525,NULL);
INSERT INTO INVENTORY VALUES( 10013, 101, 400, 400, 700,NULL);
INSERT INTO INVENTORY VALUES( 10013, 10501, 314, 300, 525,NULL);
INSERT INTO INVENTORY VALUES( 10021, 101, 500, 425, 740,NULL);
INSERT INTO INVENTORY VALUES( 10022, 101, 300, 200, 350,NULL);
INSERT INTO INVENTORY VALUES( 10022, 10501, 502, 300, 525,NULL);
INSERT INTO INVENTORY VALUES( 10023, 101, 400, 300, 525,NULL);
INSERT INTO INVENTORY VALUES( 10023, 10501, 500, 300, 525,NULL);
INSERT INTO INVENTORY VALUES( 20106, 101, 993, 625, 1000,NULL);
INSERT INTO INVENTORY VALUES( 20106, 201, 220, 150, 260,NULL);
INSERT INTO INVENTORY VALUES( 20106, 10501, 150, 100, 175,NULL);
INSERT INTO INVENTORY VALUES( 20108, 101, 700, 700, 1225,NULL);
INSERT INTO INVENTORY VALUES( 20108, 201, 166, 150, 260,NULL);
INSERT INTO INVENTORY VALUES( 20108, 10501, 222, 200, 350,NULL);
INSERT INTO INVENTORY VALUES( 20201, 101, 802, 800, 1400,NULL);
INSERT INTO INVENTORY VALUES( 20201, 201, 320, 200, 350,NULL);
INSERT INTO INVENTORY VALUES( 20201, 10501, 275, 200, 350,NULL);
INSERT INTO INVENTORY VALUES( 20512, 101, 850, 850, 1450,NULL);
INSERT INTO INVENTORY VALUES( 20512, 201, 162, 100, 175,NULL);
INSERT INTO INVENTORY VALUES( 20512, 301, 28, 20, 50,NULL);
INSERT INTO INVENTORY VALUES( 20512, 401, 75, 75, 140,NULL);
INSERT INTO INVENTORY VALUES( 20512, 10501, 62, 50, 87,NULL);
INSERT INTO INVENTORY VALUES( 30321, 101, 2000, 1500, 2500,NULL);
INSERT INTO INVENTORY VALUES( 30321, 201, 96, 80, 140,NULL);
INSERT INTO INVENTORY VALUES( 30321, 301, 85, 80, 140,NULL);
INSERT INTO INVENTORY VALUES( 30321, 401, 102, 80, 140,NULL);
INSERT INTO INVENTORY VALUES( 30321, 10501, 194, 150, 275,NULL);
INSERT INTO INVENTORY VALUES( 30326, 101, 2100, 2000, 3500,NULL);
INSERT INTO INVENTORY VALUES( 30326, 201, 147, 120, 210,NULL);
INSERT INTO INVENTORY VALUES( 30326, 401, 113, 80, 140,NULL);
INSERT INTO INVENTORY VALUES( 30326, 10501, 277, 250, 440,NULL);
INSERT INTO INVENTORY VALUES( 30421, 101, 1822, 1800, 3150,NULL);
INSERT INTO INVENTORY VALUES( 30421, 201, 102, 80, 140,NULL);
INSERT INTO INVENTORY VALUES( 30421, 301, 102, 80, 140,NULL);
INSERT INTO INVENTORY VALUES( 30421, 401, 85, 80, 140,NULL);
INSERT INTO INVENTORY VALUES( 30421, 10501, 190, 150, 275,NULL);
INSERT INTO INVENTORY VALUES( 30426, 101, 2250, 2000, 3500,NULL);
INSERT INTO INVENTORY VALUES( 30426, 201, 200, 120, 210,NULL);
INSERT INTO INVENTORY VALUES( 30426, 401, 135, 80, 140,NULL);
INSERT INTO INVENTORY VALUES( 30426, 10501, 423, 250, 450,NULL);
INSERT INTO INVENTORY VALUES( 30433, 101, 650, 600, 1050,NULL);
INSERT INTO INVENTORY VALUES( 30433, 201, 130, 130, 230,NULL);
INSERT INTO INVENTORY VALUES( 30433, 301, 35, 20, 35,NULL);
INSERT INTO INVENTORY VALUES( 30433, 401, 0, 100, 175,NULL);
INSERT INTO INVENTORY VALUES( 30433, 10501, 273, 200, 350,NULL);
INSERT INTO INVENTORY VALUES( 32779, 101, 2120, 1250, 2200,NULL);
INSERT INTO INVENTORY VALUES( 32779, 201, 180, 150, 260,NULL);
INSERT INTO INVENTORY VALUES( 32779, 301, 102, 95, 175,NULL);
INSERT INTO INVENTORY VALUES( 32779, 401, 135, 100, 175,NULL);
INSERT INTO INVENTORY VALUES( 32779, 10501, 280, 200, 350,NULL);
INSERT INTO INVENTORY VALUES( 32861, 101, 505, 500, 875,NULL);
INSERT INTO INVENTORY VALUES( 32861, 201, 132, 80, 140,NULL);
INSERT INTO INVENTORY VALUES( 32861, 301, 57, 50, 100,NULL);
INSERT INTO INVENTORY VALUES( 32861, 401, 250, 150, 250,NULL);
INSERT INTO INVENTORY VALUES( 32861, 10501, 288, 200, 350,NULL);
INSERT INTO INVENTORY VALUES( 40421, 101, 578, 350, 600,NULL);
INSERT INTO INVENTORY VALUES( 40421, 301, 70, 40, 70,NULL);
INSERT INTO INVENTORY VALUES( 40421, 401, 47, 40, 70,NULL);
INSERT INTO INVENTORY VALUES( 40421, 10501, 97, 80, 140,NULL);
INSERT INTO INVENTORY VALUES( 40422, 101, 0, 350, 600,NULL);
INSERT INTO INVENTORY VALUES( 40422, 301, 65, 40, 70,NULL);
INSERT INTO INVENTORY VALUES( 40422, 401, 50, 40, 70,NULL);
INSERT INTO INVENTORY VALUES( 40422, 10501, 90, 80, 140,NULL);
INSERT INTO INVENTORY VALUES( 41010, 101, 250, 250, 437,NULL);
INSERT INTO INVENTORY VALUES( 41010, 301, 59, 40, 70,NULL);
INSERT INTO INVENTORY VALUES( 41010, 401, 80, 70, 220,NULL);
INSERT INTO INVENTORY VALUES( 41010, 10501, 151, 140, 245,NULL);
INSERT INTO INVENTORY VALUES( 41020, 101, 471, 450, 750,NULL);
INSERT INTO INVENTORY VALUES( 41020, 301, 61, 40, 70,NULL);
INSERT INTO INVENTORY VALUES( 41020, 401, 91, 70, 220,NULL);
INSERT INTO INVENTORY VALUES( 41020, 10501, 224, 140, 245,NULL);
INSERT INTO INVENTORY VALUES( 41050, 101, 501, 450, 750,NULL);
INSERT INTO INVENTORY VALUES( 41050, 301, 49, 40, 70,NULL);
INSERT INTO INVENTORY VALUES( 41050, 401, 169, 70, 220,NULL);
INSERT INTO INVENTORY VALUES( 41050, 10501, 157, 140, 245,NULL);
INSERT INTO INVENTORY VALUES( 41080, 101, 400, 400, 700,NULL);
INSERT INTO INVENTORY VALUES( 41080, 301, 50, 40, 70,NULL);
INSERT INTO INVENTORY VALUES( 41080, 401, 100, 70, 220,NULL);
INSERT INTO INVENTORY VALUES( 41080, 10501, 159, 140, 245,NULL);
INSERT INTO INVENTORY VALUES( 41100, 101, 350, 350, 600,NULL);
INSERT INTO INVENTORY VALUES( 41100, 301, 42, 40, 70,NULL);
INSERT INTO INVENTORY VALUES( 41100, 401, 75, 70, 220,NULL);
INSERT INTO INVENTORY VALUES( 41100, 10501, 141, 140, 245,NULL);
INSERT INTO INVENTORY VALUES( 50169, 101, 2530, 1500, 2600,NULL);
INSERT INTO INVENTORY VALUES( 50169, 201, 225, 220, 385,NULL);
INSERT INTO INVENTORY VALUES( 50169, 401, 240, 200, 350,NULL);
INSERT INTO INVENTORY VALUES( 50273, 101, 233, 200, 350,NULL);
INSERT INTO INVENTORY VALUES( 50273, 201, 75, 60, 100,NULL);
INSERT INTO INVENTORY VALUES( 50273, 401, 224, 150, 280,NULL);
INSERT INTO INVENTORY VALUES( 50417, 101, 518, 500, 875,NULL);
INSERT INTO INVENTORY VALUES( 50417, 201, 82, 60, 100,NULL);
INSERT INTO INVENTORY VALUES( 50417, 401, 130, 120, 210,NULL);
INSERT INTO INVENTORY VALUES( 50418, 101, 244, 100, 275,NULL);
INSERT INTO INVENTORY VALUES( 50418, 201, 98, 60, 100,NULL);
INSERT INTO INVENTORY VALUES( 50418, 401, 156, 100, 175,NULL);
INSERT INTO INVENTORY VALUES( 50419, 101, 230, 120, 310,NULL);
INSERT INTO INVENTORY VALUES( 50419, 201, 77, 60, 100,NULL);
INSERT INTO INVENTORY VALUES( 50419, 401, 151, 150, 280,NULL);
INSERT INTO INVENTORY VALUES( 50530, 101, 669, 400, 700,NULL);
INSERT INTO INVENTORY VALUES( 50530, 201, 62, 60, 100,NULL);
INSERT INTO INVENTORY VALUES( 50530, 401, 119, 100, 175,NULL);
INSERT INTO INVENTORY VALUES( 50532, 101, 0, 100, 175,NULL);
INSERT INTO INVENTORY VALUES( 50532, 201, 67, 60, 100,NULL);
INSERT INTO INVENTORY VALUES( 50532, 401, 233, 200, 350,NULL);
INSERT INTO INVENTORY VALUES( 50536, 101, 173, 100, 175,NULL);
INSERT INTO INVENTORY VALUES( 50536, 201, 97, 60, 100,NULL);
INSERT INTO INVENTORY VALUES( 50536, 401, 138, 100, 175,NULL);
-- ISI DATA TABEL PELANGGAN
INSERT INTO PELANGGAN VALUES(201, 'MERCU BUANA KOMPUTER',NULL,NULL,'MEDAN',NULL, 'BAIK' ,12, 2,NULL ,'N' ,'M');
INSERT INTO PELANGGAN VALUES(202, 'CELEBES NET',NULL,NULL,'UJUNG PANDANG',NULL, 'BURUK' ,14, 4,NULL ,'N' ,'M');
INSERT INTO PELANGGAN VALUES(203, 'KAWANUA TECH',NULL,NULL,'MANADO',NULL, 'SEDANG' ,14, 4,NULL ,'N' ,'M');
INSERT INTO PELANGGAN VALUES(204, 'JAYAKARTA DATA',NULL,NULL,'JAKARTA',NULL, 'BAIK' ,11, 1,NULL ,'N' ,'M');
INSERT INTO PELANGGAN VALUES(205, 'PANGRANGO CYBER',NULL,NULL,'BANDUNG',NULL, 'BAIK' ,15, 1,NULL ,'N' ,'M');
INSERT INTO PELANGGAN VALUES(206, 'CENDRAWASIH KOMPUTER',NULL,NULL,'JAYAPURA',NULL, 'BAIK' ,15, 5,NULL ,'N' ,'M');
INSERT INTO PELANGGAN VALUES(207, 'PRIMA MULTIDATA',NULL,NULL,'PONTIANAK',NULL, 'SEDANG' ,13, 3,NULL ,'N' ,'M');
INSERT INTO PELANGGAN VALUES(208, 'ARAFURU MULTI SARANA',NULL,NULL,'SORONG',NULL, 'SEDANG' ,15, 5,NULL ,'N' ,'M');
INSERT INTO PELANGGAN VALUES(209, 'SIMPANG LIMA',NULL,NULL,'SEMARANG',NULL, 'BAIK' ,11, 1,NULL ,'N' ,'M');
INSERT INTO PELANGGAN VALUES(210, 'DUA SAUDARA',NULL,NULL,'PADANG',NULL, 'BAIK' ,12, 2,NULL ,'N' ,'M');
INSERT INTO PELANGGAN VALUES(211, 'LEGIAN MITRA',NULL,NULL,'BALI',NULL, 'BAIK' ,15, 1,NULL ,'N' ,'M');
INSERT INTO PELANGGAN VALUES(212, 'BARITO INDOKOM',NULL,NULL,'SAMARINDA',NULL, 'BAIK' ,13, 3,NULL ,'N' ,'M');
INSERT INTO PELANGGAN VALUES(213, 'GAJAYANA MEDIA',NULL,NULL,'SURABAYA',NULL, 'BAIK' ,11, 1,NULL ,'N' ,'M');
INSERT INTO PELANGGAN VALUES(214, 'GADJAH MADA KOMPUTER',NULL,NULL,'YOGYAKARTA',NULL,'BURUK' ,11, 1,NULL ,'N' ,'M');
INSERT INTO PELANGGAN VALUES(215, 'MUSI PRIMADATA',NULL,NULL,'PALEMBANG',NULL, 'BURUK' ,15, 2,NULL ,'N' ,'M');
-- ISI DATA TABEL HD_SALES
INSERT INTO HD_SALES VALUES( 97, 201, '28-AUG-2006', '17-SEP-2006', 12, 840000000 ,'CREDIT' ,'Y');
INSERT INTO HD_SALES VALUES( 98, 202, '31-AUG-2006', '10-SEP-2006', 14, 5950000 ,'CASH' ,'Y');
INSERT INTO HD_SALES VALUES( 99, 203, '31-AUG-2006', '18-SEP-2006', 14, 77070000 ,'CREDIT' ,'Y');
INSERT INTO HD_SALES VALUES( 100, 204, '31-AUG-2006', '10-SEP-2006', 11, 6011000000 ,'CREDIT' ,'Y');
INSERT INTO HD_SALES VALUES( 101, 205, '31-AUG-2006', '15-SEP-2006', 14, 80566000 ,'CREDIT' ,'Y');
INSERT INTO HD_SALES VALUES( 102, 206, '01-SEP-2006', '08-SEP-2006', 15, 83350000 ,'CREDIT' ,'Y');
INSERT INTO HD_SALES VALUES( 103, 208, '02-SEP-2006', '22-SEP-2006', 15, 3770000 ,'CASH' ,'Y');
INSERT INTO HD_SALES VALUES( 104, 208, '03-SEP-2006', '23-SEP-2006', 15, 324300000 ,'CREDIT' ,'Y');
INSERT INTO HD_SALES VALUES( 105, 209, '04-SEP-2006', '18-SEP-2006', 11, 27222400 ,'CREDIT' ,'Y');
INSERT INTO HD_SALES VALUES( 106, 210, '07-SEP-2006', '15-SEP-2006', 12, 156340000 ,'CREDIT' ,'Y');
INSERT INTO HD_SALES VALUES( 107, 211, '07-SEP-2006', '21-SEP-2006', 15, 1421710000 ,'CREDIT' ,'Y');
INSERT INTO HD_SALES VALUES( 108, 212, '07-SEP-2006', '10-SEP-2006', 13, 1495700000 ,'CREDIT' ,'Y');
INSERT INTO HD_SALES VALUES( 109, 213, '08-SEP-2006', '28-SEP-2006', 11, 10722000 ,'CREDIT' ,'Y');
INSERT INTO HD_SALES VALUES( 110, 214, '09-SEP-2006', '21-SEP-2006', 11, 15391300 ,'CASH' ,'Y');
INSERT INTO HD_SALES VALUES( 111, 204, '09-SEP-2006', '21-SEP-2006', 11, 27700000 ,'CASH' ,'Y');
INSERT INTO HD_SALES VALUES( 112, 210, '31-AUG-2006', '10-SEP-2006', 12, 5500000 ,'CREDIT' ,'Y');
-- ISI DATA TABEL ITEM
INSERT INTO ITEM VALUES( 97, 1, 20106, 90000, 1000, 1000);
INSERT INTO ITEM VALUES( 97, 2, 30321, 15000000, 50, 50);
INSERT INTO ITEM VALUES( 98, 1, 40421, 850000, 7, 7);
INSERT INTO ITEM VALUES( 99, 1, 20106, 90000, 18, 18);
INSERT INTO ITEM VALUES( 99, 2, 20512, 80000, 25, 25);
INSERT INTO ITEM VALUES( 99, 3, 50417, 800000, 53, 53);
INSERT INTO ITEM VALUES( 99, 4, 50530, 450000, 69, 69);
INSERT INTO ITEM VALUES( 100, 1, 10011, 1350000, 500, 500);
INSERT INTO ITEM VALUES( 100, 2, 10013, 3800000, 400, 400);
INSERT INTO ITEM VALUES( 100, 3, 10021, 140000, 500, 500);
INSERT INTO ITEM VALUES( 100, 4, 10023, 360000, 400, 400);
INSERT INTO ITEM VALUES( 100, 5, 30326, 5820000, 600, 600);
INSERT INTO ITEM VALUES( 100, 6, 30433, 200000, 450, 450);
INSERT INTO ITEM VALUES( 100, 7, 41010, 80000, 250, 250);
INSERT INTO ITEM VALUES( 101, 1, 30421, 160000, 15, 15);
INSERT INTO ITEM VALUES( 101, 2, 40422, 500000, 30, 30);
INSERT INTO ITEM VALUES( 101, 3, 41010, 80000, 20, 20);
INSERT INTO ITEM VALUES( 101, 4, 41100, 450000, 35, 35);
INSERT INTO ITEM VALUES( 101, 5, 50169, 42900, 40, 40);
INSERT INTO ITEM VALUES( 101, 6, 50417, 800000, 27, 27);
INSERT INTO ITEM VALUES( 101, 7, 50530, 450000, 50, 50);
INSERT INTO ITEM VALUES( 102, 1, 20108, 280000, 100, 100);
INSERT INTO ITEM VALUES( 102, 2, 20201, 1230000, 45, 45);
INSERT INTO ITEM VALUES( 103, 1, 30433, 200000, 15, 15);
INSERT INTO ITEM VALUES( 103, 2, 32779, 70000, 11, 11);
INSERT INTO ITEM VALUES( 104, 1, 20512, 90000, 7, 7);
INSERT INTO ITEM VALUES( 104, 2, 20512, 80000, 12, 12);
INSERT INTO ITEM VALUES( 104, 3, 30321, 16690000, 19, 19);
INSERT INTO ITEM VALUES( 104, 4, 30421, 160000, 35, 35);
INSERT INTO ITEM VALUES( 105, 1, 50273, 228900, 16, 16);
INSERT INTO ITEM VALUES( 105, 2, 50419, 800000, 13, 13);
INSERT INTO ITEM VALUES( 105, 3, 50532, 470000, 28, 28);
INSERT INTO ITEM VALUES( 106, 1, 20108, 280000, 46, 46);
INSERT INTO ITEM VALUES( 106, 2, 20201, 1230000, 21, 21);
INSERT INTO ITEM VALUES( 106, 3, 50169, 42900, 125, 125);
INSERT INTO ITEM VALUES( 106, 4, 50273, 228900, 75, 75);
INSERT INTO ITEM VALUES( 106, 5, 50418, 750000, 98, 98);
INSERT INTO ITEM VALUES( 106, 6, 50419, 800000, 27, 27);
INSERT INTO ITEM VALUES( 107, 1, 20106, 110000, 50, 50);
INSERT INTO ITEM VALUES( 107, 2, 20108, 280000, 22, 22);
INSERT INTO ITEM VALUES( 107, 3, 20201, 1150000, 130, 130);
INSERT INTO ITEM VALUES( 107, 4, 30321, 16690000, 75, 75);
INSERT INTO ITEM VALUES( 107, 5, 30421, 160000, 55, 55);
INSERT INTO ITEM VALUES( 108, 1, 20512, 90000, 9, 9);
INSERT INTO ITEM VALUES( 108, 2, 20512, 80000, 18, 18);
INSERT INTO ITEM VALUES( 108, 3, 30321, 16690000, 85, 85);
INSERT INTO ITEM VALUES( 108, 4, 32779, 70000, 60, 60);
INSERT INTO ITEM VALUES( 108, 5, 32861, 600000, 57, 57);
INSERT INTO ITEM VALUES( 108, 6, 41080, 350000, 50, 50);
INSERT INTO ITEM VALUES( 108, 7, 41100, 450000, 42, 42);
INSERT INTO ITEM VALUES( 109, 1, 10011, 1400000, 150, 150);
INSERT INTO ITEM VALUES( 109, 2, 10012, 1750000, 600, 600);
INSERT INTO ITEM VALUES( 109, 3, 10022, 219500, 300, 300);
INSERT INTO ITEM VALUES( 109, 4, 30326, 5820000, 1500, 1500);
INSERT INTO ITEM VALUES( 109, 5, 30426, 182500, 500, 500);
INSERT INTO ITEM VALUES( 109, 6, 32861, 600000, 50, 50);
INSERT INTO ITEM VALUES( 109, 7, 50418, 750000, 43, 43);
INSERT INTO ITEM VALUES( 110, 1, 50273, 228900, 17, 17);
INSERT INTO ITEM VALUES( 110, 2, 50536, 500000, 23, 23);
INSERT INTO ITEM VALUES( 111, 1, 40421, 650000, 27, 27);
INSERT INTO ITEM VALUES( 111, 2, 41080, 350000, 29, 29);
INSERT INTO ITEM VALUES( 112, 1, 20106, 110000, 50, 50);
-- ISI DATA TABEL PEGAWAI
INSERT INTO PEGAWAI VALUES(1001, 'MEUTIA JOVI MAHARANI' ,'meutijm',NULL, '03-MAR-1999' ,NULL ,'PRESIDENT' ,50, 22500000,NULL);
INSERT INTO PEGAWAI VALUES(1002, 'BUDI HARTADI' ,'budhart',NULL, '08-MAR-1999' ,1001 ,'VP, OPERATIONS' ,41, 10500000,NULL);
INSERT INTO PEGAWAI VALUES(1003, 'RULLY SIANIPAR' ,'rullysi',NULL, '17-JUN-2000' ,1001 ,'VP, SALES' ,31, 10000000,NULL);
INSERT INTO PEGAWAI VALUES(1004, 'EDWIN ASRUL' ,'edwirul',NULL, '07-APR-2000' ,1001 ,'VP, FINANCE' ,10, 10500000,NULL);
INSERT INTO PEGAWAI VALUES(1005, 'NOVI SETIAWATI' ,'novise',NULL, '04-MAR-2000' ,1001 ,'VP, ADMINISTRATION' ,50, 11500000,NULL);
INSERT INTO PEGAWAI VALUES(1006, 'ARIS MURSITO' ,'arismu',NULL, '18-JAN-2001' ,1002 ,'WAREHOUSE MANAGER' ,41, 7000000,NULL);
INSERT INTO PEGAWAI VALUES(1007, 'IRVAN SYAFE''I' ,'irvanar',NULL, '14-MAY-2000' ,1002 ,'WAREHOUSE MANAGER' ,41, 7500000,NULL);
INSERT INTO PEGAWAI VALUES(1008, 'DHEA LUSIANA' ,'dhealus',NULL, '07-APR-2000' ,1002 ,'WAREHOUSE MANAGER' ,41, NULL,NULL);
INSERT INTO PEGAWAI VALUES(1009, 'WIRA AULIA' ,'wirali',NULL, '09-FEB-2000' ,1002 ,'WAREHOUSE MANAGER' ,41, NULL,NULL);
INSERT INTO PEGAWAI VALUES(1010, 'PURNAMA RIYANTO' ,'purnarto',NULL, '27-FEB-2000' ,1002 ,'WAREHOUSE MANAGER' ,41, 8070000,NULL);
INSERT INTO PEGAWAI VALUES(1011, 'RIDWAN SANUSI' ,'ridwnus',NULL, '14-MAY-2000' ,1003 ,'SALES REPRESENTATIVE' ,31, 4000000, 10);
INSERT INTO PEGAWAI VALUES(1012, 'SUSI INDIARTI' ,'susarti',NULL, '18-JAN-2001' ,1003 ,'SALES REPRESENTATIVE' ,31, 4900000, 12.5);
INSERT INTO PEGAWAI VALUES(1013, 'POPY LUSIANA' ,'popusi',NULL, '18-FEB-2001' ,1003 ,'SALES REPRESENTATIVE' ,31, 5150000, 10);
INSERT INTO PEGAWAI VALUES(1014, 'ERTIN' ,'ertint',NULL, '22-JAN-2001' ,1003 ,'SALES REPRESENTATIVE' ,31, 5250000, 15);
INSERT INTO PEGAWAI VALUES(1015, 'ESTI ARVINA' ,'estarv',NULL, '09-OCT-2001' ,1003 ,'SALES REPRESENTATIVE' ,31, 4500000, 17.5);
INSERT INTO PEGAWAI VALUES(1016, 'LIA NURLITA' ,'linuta',NULL, '07-FEB-2001' ,1006 ,'STOCK CLERK' ,41, 4000000,NULL);
INSERT INTO PEGAWAI VALUES(1017, 'HERU KUTANTO' ,'herkut',NULL, '08-MAR-2001' ,1006 ,'STOCK CLERK' ,41, 1400000,NULL);
INSERT INTO PEGAWAI VALUES(1018, 'ADRIAN' ,'adran',NULL, '09-FEB-2001' ,1007 ,'STOCK CLERK' ,41, 4000000,NULL);
INSERT INTO PEGAWAI VALUES(1019, 'DHILA LESTARI' ,'dhilast',NULL, '06-AUG-2001' ,1007 ,'STOCK CLERK' ,41, 1950000,NULL);
INSERT INTO PEGAWAI VALUES(1020, 'ASWIR MATONDANG' ,'aswirton',NULL, '21-JUL-2001' ,1008 ,'STOCK CLERK' ,41, 1500000,NULL);
INSERT INTO PEGAWAI VALUES(1021, 'SUHARDIATNO' ,'sudiatno',NULL, '26-MAY-2001' ,1008 ,NULL ,NULL, 2500000,NULL);
INSERT INTO PEGAWAI VALUES(1022, 'AHMAD KOSASIH' ,'ahmaos',NULL, '30-NOV-2002' ,1009 ,NULL ,NULL, 2000000,NULL);
INSERT INTO PEGAWAI VALUES(1023, 'AMAURA' ,'amaura',NULL, '17-OCT-2002' ,1009 ,'STOCK CLERK' ,41, 1950000,NULL);
INSERT INTO PEGAWAI VALUES(1024, 'SUPRAYOGI' ,'supray',NULL, '17-MAR-2002' ,1010 ,'STOCK CLERK' ,41, 2600000,NULL);
INSERT INTO PEGAWAI VALUES(1025, 'ASFIANTI' ,'asfiai',NULL, '09-MAY-2002' ,1010 ,'STOCK CLERK' ,41, 5000000,NULL);
-- Isi tabel RIWAYAT_JABATAN
INSERT INTO riwayat_jabatan VALUES(1001,'VP, OPERATIONS',41,'01-JAN-2007');
INSERT INTO riwayat_jabatan VALUES(1001,'SALES REPRESENTATIVE',31,'20-FEB-2005');
INSERT INTO riwayat_jabatan VALUES(1014,'SALES REPRESENTATIVE',31,'01-MAY-2006');
INSERT INTO riwayat_jabatan VALUES(1014,'STOCK CLERK',41,'10-APR-2009');
INSERT INTO riwayat_jabatan VALUES(1005,'WAREHOUSE MANAGER',41,'10-APR-2008');
INSERT INTO riwayat_jabatan VALUES(1009,'STOCK CLERK',41,'01-JUL-2008');
-- Buat tabel GRADE
CREATE TABLE GRADE
(KODE_GRADE CHAR(10),
BTS_BAWAH NUMBER(10),
BTS_ATAS NUMBER(10));
-- Isi tabel Grade
insert into grade values('A',0,1999999);
insert into grade values('B',2000000,4999999);
insert into grade values('C',5000000,7999999);
insert into grade values('D',8000000,11999999);
insert into grade values('E',12000000,18999999);
insert into grade values('F',19000000,25000000);
-- ANALYZE TABEL2
ANALYZE TABLE JABATAN COMPUTE STATISTICS;
ANALYZE TABLE WILAYAH COMPUTE STATISTICS;
ANALYZE TABLE GUDANG COMPUTE STATISTICS;
ANALYZE TABLE GAMBAR COMPUTE STATISTICS;
ANALYZE TABLE BARANG COMPUTE STATISTICS;
ANALYZE TABLE BAGIAN COMPUTE STATISTICS;
ANALYZE TABLE PELANGGAN COMPUTE STATISTICS;
ANALYZE TABLE PEGAWAI COMPUTE STATISTICS;
ANALYZE TABLE HD_SALES COMPUTE STATISTICS;
ANALYZE TABLE ITEM COMPUTE STATISTICS;
ANALYZE TABLE INVENTORY COMPUTE STATISTICS;
ANALYZE TABLE GRADE COMPUTE STATISTICS;
UPDATE HD_SALES A SET TOTAL = (SELECT SUM(HARGA) FROM ITEM B WHERE B.ORDER_ID= A.ORDER_ID);
COMMIT;
ALTER TABLE PELANGGAN DROP COLUMN STATUS;
ALTER TABLE PELANGGAN DROP COLUMN PENGIRIMAN;
--INFO JUMLAH BARIS TABEL
SET LINESIZE 200;
SET PAGESIZE 150
SELECT TABLE_NAME, NUM_ROWS
FROM USER_TABLES;
SELECT * FROM BAGIAN;
COL SATUAN FOR A10
COL NAMA_BRG FOR A25
COL KETERANGAN FOR A30
SELECT * FROM BARANG
ORDER BY KODE_BRG;
COL FILENAME FOR A15
COL FORMAT FORMAT A10
SELECT * FROM GAMBAR;
SELECT * FROM GUDANG;
SELECT * FROM HD_SALES
ORDER BY ORDER_ID;
SELECT * FROM ITEM;
SELECT A.ORDER_ID, A.TOTAL, SUM(B.HARGA)
FROM HD_SALES A, ITEM B
WHERE A.ORDER_ID=B.ORDER_ID
GROUP BY A.ORDER_ID, A.TOTAL
ORDER BY 1;
SELECT * FROM INVENTORY
ORDER BY KODE_BRG;
SELECT * FROM ITEM
ORDER BY ORDER_ID
COL ID FOR 99999;
COL NAMA FOR A25;
COL BAG FOR 999;
SELECT ID_PEGAWAI ID,
NAMA_PEGAWAI NAMA,
USERID,
TGL_MASUK,
MANAGER_ID MGR,
JABATAN,
KODE_BAG BAG,
GAJI,
PCT_KOMISI
FROM PEGAWAI;
COL SHIP FOR A4
COL SALES FOR 99999
COL KOTA FOR A15
col alamat for a15
COL ID FOR 99999
COL NAMA FOR A25
COL WIL FOR 999
COL KETERANGAN FOR A10
COL PREF_PLG FOR A4
SELECT ID_PLG ID,
NAMA_PLG NAMA,
ALAMAT,
KOTA,KODE_POS,CREDIT_RATING,SALES_REP_ID SALES,KODE_WILAYAH WIL,KETERANGAN
FROM PELANGGAN;
SELECT * FROM WILAYAH;
SELECT * FROM JABATAN;
SELECT * FROM GRADE;
SPOOL OFF;
No comments:
Post a Comment