U9SQbEVJ

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