· 10 years ago · Nov 26, 2015, 10:24 AM
1
2 DROP DATABASE IF EXISTS penjualan1;
3 CREATE DATABASE IF NOT EXISTS penjualan1;
4 USE penjualan1;
5
6 DROP TABLE IF EXISTS detil_pesan;
7 CREATE TABLE IF NOT exists detil_pesan (
8 id_pesan int(5) NOT NULL,
9 id_produk varchar(5) NOT NULL,
10 jumlah int(5) NOT NULL default '0',
11 harga decimal(10,0) NOT NULL default '0',
12 PRIMARY KEY (id_pesan,id_produk)
13 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
14
15 DROP TABLE IF EXISTS faktur;
16 CREATE TABLE if not exists faktur (
17 id_faktur int(5) NOT NULL auto_increment,
18 id_pesan int(5) NOT NULL,
19 tgl_faktur date NOT NULL,
20 PRIMARY KEY (id_faktur)
21 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
22
23 DROP TABLE IF EXISTS kuliah;
24 CREATE TABLE IF NOT EXISTS kuitansi (
25 id_kuitansi int(5) NOT NULL auto_increment,
26 id_faktur int(5) NOT NULL,
27 tgl_kuitansi date NOT NULL,
28 PRIMARY KEY (id_kuitansi)
29 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
30
31 DROP TABLE IF EXISTS pelanggan;
32 CREATE TABLE IF NOT EXISTS pelanggan (
33 id_pelanggan varchar(5) NOT NULL,
34 nm_pelanggan varchar(40) NOT NULL,
35 alamat text NOT NULL,
36 telepon varchar(20) NOT NULL,
37 email varchar(50) NOT NULL,
38 PRIMARY KEY (id_pelanggan)
39 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1;
40
41 DROP TABLE IF EXISTS pesan;
42 CREATE TABLE IF NOT EXISTS pesan (
43 id_pesan int(5) NOT NULL auto_increment,
44 id_pelanggan varchar(5) NOT NULL,
45 tgl_pesan date NOT NULL,
46 PRIMARY KEY (id_pesan)
47 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
48
49 DROP TABLE IF EXISTS produk;
50 CREATE TABLE IF NOT EXISTS produk (
51 id_produk varchar(5) NOT NULL,
52 nm_produk varchar(30) NOT NULL,
53 satuan varchar(10) NOT NULL,
54 harga decimal(10,0) NOT NULL default '0',
55 stock int(3) NOT NULL default '0',
56 PRIMARY KEY (id_produk)
57 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
58
59 -- menginput data
60
61 -- tabel pelanggan
62 INSERT INTO pelanggan VALUES ('P0001', 'Budi Santoso','Jakarta Selatan', '0217327762', 'budi_s@gmail.com');
63 INSERT INTO pelanggan VALUES ('P0002', 'Zaskia Putri','Jakarta Selatan', '0217450231', 'z_putri@gmail.com');
64 INSERT INTO pelanggan VALUES ('P0003', 'Muhammad Syahranu','Surabaya', '031720000', 'ranu@yahoo.com');
65 INSERT INTO pelanggan VALUES ('P0004', 'Putri Cantika Sari','Malang', '0341212700', 'c_putri@gmail.com');
66 INSERT INTO pelanggan VALUES ('P0005', 'Siska Shanti','Jakarta Timur', '0217327762', 'siska_sh@yahoo.com');
67 INSERT INTO pelanggan VALUES ('P0006', 'Johan Pahing','Surabaya', '03180000', 'jh_top@yahoo.com');
68
69 -- tabel produk
70 INSERT into produk VALUES('B0001','Buku Tulis','Biji',4500,100);
71 INSERT into produk VALUES('B0002','Penghapus','Biji',1500,200);
72 INSERT into produk VALUES('B0003','Penggaris','Biji',2500,50);
73 INSERT into produk VALUES('B0004','Pensil','Biji',3000,200);
74 INSERT into produk VALUES('B0005','Bolpen','Biji',2500,300);
75
76 -- tabel pesan
77 INSERT into pesan (ID_PELANGGAN,TGL_PESAN) VALUES ('P0001','2015-11-19');
78 INSERT into pesan (ID_PELANGGAN,TGL_PESAN) VALUES ('P0002','2015-1-19');
79 INSERT into pesan (ID_PELANGGAN,TGL_PESAN) VALUES ('P0003','2015-2-19');
80 INSERT into pesan (ID_PELANGGAN,TGL_PESAN) VALUES ('P0001','2015-3-19');
81 INSERT into pesan (ID_PELANGGAN,TGL_PESAN) VALUES ('P0002','2015-11-10');
82 INSERT into pesan (ID_PELANGGAN,TGL_PESAN) VALUES ('P0005','2015-11-5');
83
84 -- tabel detil_pesan
85 INSERT into detil_pesan VALUES ('1','B0001',5,4500);
86 INSERT into detil_pesan VALUES ('1','B0002',5,1500);
87 INSERT into detil_pesan VALUES ('1','B0003',5,2500);
88 INSERT into detil_pesan VALUES ('2','B0002',1,1500);
89 INSERT into detil_pesan VALUES ('2','B0005',2,2500);
90 INSERT into detil_pesan VALUES ('3','B0002',3,1500);
91 INSERT into detil_pesan VALUES ('4','B0004',7,3000);
92 INSERT into detil_pesan VALUES ('5','B0005',2,2500);
93 INSERT into detil_pesan VALUES ('6','B0001',10,4500);