-- Membuat database jika belum ada
CREATE DATABASE IF NOT EXISTS `uts_mandat` DEFAULT CHARACTER SET utf8mb4 COLLATE
utf8mb4_general_ci;
USE `uts_mandat`;
-- 1. Membuat tabel sales
CREATE TABLE `sales` (
`SPL` varchar(20) NOT NULL,
`Salesman` varchar(15) NOT NULL,
PRIMARY KEY (`SPL`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 2. Membuat tabel pelanggan
CREATE TABLE `pelanggan` (
`kd_pelanggan` varchar(20) NOT NULL,
`Nama` varchar(20) NOT NULL,
`Alamat` varchar(30) NOT NULL,
`Kota` varchar(15) NOT NULL,
`Telepon` varchar(15) NOT NULL,
`Kategori` varchar(20) NOT NULL,
PRIMARY KEY (`kd_pelanggan`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 3. Membuat tabel barang
CREATE TABLE `barang` (
`kd_barang` varchar(15) NOT NULL,
`Nama_Barang` varchar(20) NOT NULL,
`Satuan` varchar(20) NOT NULL,
`Harga_Satuan` int(10) NOT NULL,
PRIMARY KEY (`kd_barang`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 4. Membuat tabel faktur
CREATE TABLE `faktur` (
`Faktur` varchar(15) NOT NULL,
`SPL` varchar(15) NOT NULL,
`Tanggal` date NOT NULL,
`Langganan` varchar(20) NOT NULL,
`Jatuh_Tempo` date NOT NULL,
`Ket` varchar(20) NOT NULL,
`DPP` float NOT NULL,
`PPN` float NOT NULL,
`Total_Bayar` float NOT NULL,
PRIMARY KEY (`Faktur`),
KEY `fk_faktur_sales` (`SPL`),
CONSTRAINT `fk_faktur_sales` FOREIGN KEY (`SPL`) REFERENCES `sales` (`SPL`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 5. Membuat tabel transaksi
CREATE TABLE `transaksi` (
`Faktur` varchar(20) NOT NULL,
`kd_barang` varchar(15) NOT NULL,
`kd_pelanggan` varchar(15) NOT NULL,
`Kuantitas` int(5) NOT NULL,
`Jumlah_Kotor` float NOT NULL,
`Potongan_Persen` float NOT NULL,
`Potongan_Rp` float NOT NULL,
`Jumlah_Bersih` float NOT NULL,
KEY `fk_transaksi_faktur` (`Faktur`),
KEY `fk_transaksi_barang` (`kd_barang`),
KEY `fk_transaksi_pelanggan` (`kd_pelanggan`),
CONSTRAINT `fk_transaksi_barang` FOREIGN KEY (`kd_barang`) REFERENCES `barang`
(`kd_barang`),
CONSTRAINT `fk_transaksi_faktur` FOREIGN KEY (`Faktur`) REFERENCES `faktur`
(`Faktur`),
CONSTRAINT `fk_transaksi_pelanggan` FOREIGN KEY (`kd_pelanggan`) REFERENCES
`pelanggan` (`kd_pelanggan`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Menambahkan data ke tabel sales
INSERT INTO sales (SPL, Salesman) VALUES
('S220.55139', 'Ivan Kusuma'),
('S220.55140', 'Budi Santoso'),
('S220.55141', 'Ani Wijaya'),
('S220.55142', 'Dewi Lestari'),
('S220.55143', 'Eko Prasetyo'),
('S220.55144', 'Fajar Nugroho'),
('S220.55145', 'Gita Permata'),
('S220.55146', 'Hadi Prabowo'),
('S220.55147', 'Indah Sari'),
('S220.55148', 'Joko Susilo');
-- Menambahkan data ke tabel pelanggan
INSERT INTO pelanggan (kd_pelanggan, Nama, Alamat, Kota, Telepon, Kategori) VALUES
('54020017460679', 'Toko Sumber Rejeki', 'Jl. Merdeka No. 12', 'Bandung',
'0221234567', 'Retail'),
('54020017460680', 'Toko Makmur Jaya', 'Jl. Pahlawan No. 45', 'Jakarta',
'0212345678', 'Grosir'),
('54020017460681', 'Toko Abadi Sentosa', 'Jl. Sudirman No. 78', 'Surabaya',
'0313456789', 'Retail'),
('54020017460682', 'Toko Sejahtera', 'Jl. Gatot Subroto No. 34', 'Medan',
'0614567890', 'Grosir'),
('54020017460683', 'Toko Lancar Jaya', 'Jl. Diponegoro No. 56', 'Semarang',
'0245678901', 'Retail'),
('54020017460684', 'Toko Barokah', 'Jl. Ahmad Yani No. 89', 'Yogyakarta',
'0276789012', 'Grosir'),
('54020017460685', 'Toko Rahmat', 'Jl. Sisingamangaraja No. 23', 'Bali',
'0367890123', 'Retail'),
('54020017460686', 'Toko Anugerah', 'Jl. Hayam Wuruk No. 67', 'Makassar',
'0418901234', 'Grosir'),
('54020017460687', 'Toko Mulia', 'Jl. Thamrin No. 90', 'Palembang', '0719012345',
'Retail'),
('54020017460688', 'Toko Sentosa', 'Jl. Asia Afrika No. 11', 'Bandung',
'0220123456', 'Grosir');
-- Menambahkan data ke tabel barang (produk Indofood)a
INSERT INTO barang (kd_barang, Nama_Barang, Satuan, Harga_Satuan) VALUES
('100148', 'AB - Indomie Ayam Bawang', '40/Car', 106600),
('136916', 'PMBJ - Pop Mie Rasa Baso Jumbo', '24/Car', 99600),
('400582', 'CTBEB - Snack Chitato BEB 15g Renceng', '60/Car', 99900),
('424928', 'RSRD - Bumbu Racik Special Rendang', '24/Box', 120000),
('431530', 'CHTROC - Chiki Twist ROC 22.5g', '60/Car', 90850),
('500123', 'IMS - Indomie Rasa Soto Special', '40/Car', 110000),
('600456', 'SRK - Sarimi Rasa Kari Ayam', '24/Car', 95000),
('700789', 'PRB - Promina Biscuit Rasa Keju', '48/Box', 85000),
('800012', 'TSK - Teh Sosro Kotak 250ml', '24/Pack', 75000),
('900345', 'IMLK - Indomilk Susu Kental Manis', '48/Kaleng', 125000);
-- Menambahkan data ke tabel faktur
INSERT INTO `faktur` (`Faktur`, `SPL`, `Kd_Pelanggan`, `Tanggal`, `Jatuh_Tempo`,
`Ket`, `DPP`, `PPN`, `Total_Bayar`) VALUES
('17353679', 'S220.55139', '54020017460679', '2024-10-04', '2024-10-18', 'Lunas',
406600, 42693, 449293),
('17353680', 'S220.55140', '54020017460680', '2024-10-05', '2024-10-19', 'Lunas',
299200, 31416, 330616),
('17353681', 'S220.55141', '54020017460681', '2024-10-06', '2024-10-20', 'Belum
Lunas', 315000, 33075, 348075),
('17353682', 'S220.55142', '54020017460682', '2024-10-07', '2024-10-21', 'Lunas',
318600, 33453, 352053),
('17353683', 'S220.55143', '54020017460683', '2024-10-08', '2024-10-22', 'Lunas',
399400, 41937, 441337),
('17353684', 'S220.55144', '54020017460684', '2024-10-09', '2024-10-23', 'Belum
Lunas', 180000, 18900, 198900),
('17353685', 'S220.55145', '54020017460685', '2024-10-10', '2024-10-24', 'Lunas',
425000, 44625, 469625),
('17353686', 'S220.55146', '54020017460686', '2024-10-11', '2024-10-25', 'Lunas',
310000, 32550, 342550),
('17353687', 'S220.55147', '54020017460687', '2024-10-12', '2024-10-26', 'Belum
Lunas', 341600, 35868, 377468),
('17353688', 'S220.55148', '54020017460688', '2024-10-13', '2024-10-27', 'Lunas',
290850, 30539, 321389 );
('17353689', 'S220.55144', '54020017460688', '2024-10-14', '2024-10-28', 'Belum
Lunas', 319500, 31950, 351450);
-- Menambahkan data ke tabel transaksi
INSERT INTO `transaksi` (`Faktur`, `Kd_Barang`, `Kuantitas`, `Jumlah_Kotor`,
`Potongan_Persen`, `Potongan_Rp`, `Jumlah_Bersih`) VALUES
-- Faktur 17353679 (3 barang dengan kuantitas berbeda)
('17353679', '100148', 3, 319800, 0.00, 0, 319800),
('17353679', '500123', 2, 220000, 0.00, 0, 220000),
('17353679', '900345', 1, 125000, 0.00, 0, 125000),
-- Faktur 17353680
('17353680', '136916', 2, 199200, 0.00, 0, 199200),
('17353680', '400582', 1, 99900, 0.00, 0, 99900),
('17353680', '431530', 1, 90850, 0.00, 0, 90850),
('17353680', '700789', 1, 85000, 0.00, 0, 85000),
-- Faktur 17353681
('17353681', '424928', 2, 240000, 0.00, 0, 240000),
('17353681', '600456', 1, 95000, 0.00, 0, 95000),
('17353681', '800012', 1, 75000, 0.00, 0, 75000),
-- Faktur 17353682
('17353682', '100148', 1, 106600, 0.00, 0, 106600),
('17353682', '500123', 3, 330000, 0.00, 0, 330000),
('17353682', '900345', 2, 250000, 0.00, 0, 250000),
-- Faktur 17353683
('17353683', '136916', 1, 99600, 0.00, 0, 99600),
('17353683', '400582', 3, 299700, 0.00, 0, 299700),
('17353683', '431530', 1, 90850, 0.00, 0, 90850),
('17353683', '700789', 1, 85000, 0.00, 0, 85000),
-- Faktur 17353684
('17353684', '424928', 1, 120000, 0.00, 0, 120000),
('17353684', '600456', 1, 95000, 0.00, 0, 95000),
('17353684', '800012', 1, 75000, 0.00, 0, 75000),
-- Faktur 17353685
('17353685', '100148', 2, 213200, 0.00, 0, 213200),
('17353685', '136916', 1, 99600, 0.00, 0, 99600),
('17353685', '400582', 1, 99900, 0.00, 0, 99900),
('17353685', '424928', 1, 60000, 0.00, 0, 60000),
('17353685', '500123', 1, 110000, 0.00, 0, 110000),
-- Faktur 17353686
('17353686', '431530', 2, 181700, 0.00, 0, 181700),
('17353686', '600456', 1, 95000, 0.00, 0, 95000),
('17353686', '700789', 1, 85000, 0.00, 0, 85000),
('17353686', '800012', 1, 75000, 0.00, 0, 75000),
-- Faktur 17353687
('17353687', '900345', 2, 250000, 0.00, 0, 250000),
('17353687', '100148', 1, 106600, 0.00, 0, 106600),
('17353687', '500123', 1, 110000, 0.00, 0, 110000),
-- Faktur 17353688
('17353688', '136916', 1, 99600, 0.00, 0, 99600),
('17353688', '400582', 2, 199800, 0.00, 0, 199800),
('17353688', '424928', 1, 60000, 0.00, 0, 60000),
('17353688', '431530', 1, 90850, 0.00, 0, 90850),
('17353688', '700789', 1, 85000, 0.00, 0, 85000);
-- Faktur 17353689
('17353689', '136916', 1, 99600, 0.00, 0, 99600),
('17353689', '400582', 1, 99900, 0.00, 0, 99900),
('17353689', '424928', 2, 120000, 0.00, 0, 120000),
('17353679', '600456', 1, 95000, 0.00, 0, 95000),
('17353680', '800012', 1, 75000, 0.00, 0, 75000),
('17353681', '900345', 1, 125000, 0.00, 0, 125000);
