.::: Sample Data Mart in SQL Server :::.

 

correlation https://teguhth.blogspot.com/2019/04/study-kasus-praktis-belajar-query.html

1. Create table for datamart base on

create table dim_barang(
KODE_BARANG char(6) not null ,
NAMA_BARANG varchar(25),
SATUAN_BARANG varchar(20),
STOK_BARANG decimal(4),
primary key (KODE_BARANG));

create table dim_suplier(
KODE_SUPLIER char(5) not null,
NAMA_SUPLIER varchar(30),
ALAMAT_SUPLIER varchar(30),
KOTA_SUPLIER varchar(15),
TELEPON_SUPLIER varchar(15),
primary key(KODE_SUPLIER));

create table dim_customer(
KODE_CUSTOMER char(6),
NAMA_CUSTOMER varchar(30),
ALAMAT_CUSTOMER varchar(30),
KOTA_CUSTOMER varchar(15),
TELEPON_CUSTOMER varchar(15),
primary key(KODE_CUSTOMER));

create table fact_pasok(
KODE_PASOK char(10),
KODE_BARANG char(6),
KODE_SUPLIER char(5),
TANGGAL_PASOK date,
JUMLAH_PASOK decimal(4),
primary key(KODE_PASOK,KODE_BARANG,KODE_SUPLIER),
foreign key(KODE_BARANG)references dim_barang(KODE_BARANG),
foreign key(KODE_SUPLIER)references dim_suplier(KODE_SUPLIER));

create table fact_pembelian(
KODE_PEMBELIAN char(10),
KODE_BARANG char(6),KODE_CUSTOMER char(6),
TANGGAL_PEMBELIAN date,
JUMLAH_PEMBELIAN decimal(4),
primary key(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER),
foreign key(KODE_BARANG)references dim_barang(KODE_BARANG),
foreign key(KODE_CUSTOMER)references dim_customer(KODE_CUSTOMER));

2. insert data

select * from dim_barang;
insert into dim_barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-01','RICE COOKER','BUAH',20);
insert into dim_barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-02','LEMARI ES','UNIT',8);
insert into dim_barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-03','TELEVISI','UNIT',30);
insert into dim_barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-04','RADIO/TAPE','BUAH',35);
insert into dim_barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-05','KOMPUTER','UNIT',28);
insert into dim_barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-06','KIPAS ANGIN','BUAH',38);
select * from dim_barang;

select * from dim_suplier;
insert into dim_suplier(KODE_SUPLIER,NAMA_SUPLIER,ALAMAT_SUPLIER,KOTA_SUPLIER,TELEPON_SUPLIER) values ('EJ-01','PT ACTRON','JL THAMRIN 12','JAKARTA','(021) 850-2301');
insert into dim_suplier(KODE_SUPLIER,NAMA_SUPLIER,ALAMAT_SUPLIER,KOTA_SUPLIER,TELEPON_SUPLIER) values ('EJ-02','PT MULYA ELEKTRONIK','JL SUDIRMAN 45','JAKARTA','(021) 855-4262');
insert into dim_suplier(KODE_SUPLIER,NAMA_SUPLIER,ALAMAT_SUPLIER,KOTA_SUPLIER,TELEPON_SUPLIER) values ('EB-01','PT ULTRASOUND','JL SUKARNO HATTA 103','BANDUNG','(021) 522-3305');
insert into dim_suplier(KODE_SUPLIER,NAMA_SUPLIER,ALAMAT_SUPLIER,KOTA_SUPLIER,TELEPON_SUPLIER) values ('EB-02','PT SUPERTRON','JL INDUSTRI 37','BANDUNG','(021) 660-4091');
insert into dim_suplier(KODE_SUPLIER,NAMA_SUPLIER,ALAMAT_SUPLIER,KOTA_SUPLIER,TELEPON_SUPLIER) values ('EB-03','PT OCHANG','JL KOSAMBI 44','BANDUNG','(022) 850-4444');  
insert into dim_suplier(KODE_SUPLIER,NAMA_SUPLIER,ALAMAT_SUPLIER,KOTA_SUPLIER,TELEPON_SUPLIER) values ('EB-04','PT TUNGGAL JAYA','JL KIARA CONDONG 77','BANDUNG','(022)740-7777');  
select * from dim_suplier;

select * from dim_customer;
insert into dim_customer(KODE_CUSTOMER,NAMA_CUSTOMER,ALAMAT_CUSTOMER,KOTA_CUSTOMER,TELEPON_CUSTOMER) values('J-0001','TOKO KARISMA','JL CIMANGGIS 34','JAKARTA','(021) 856-4209');
insert into dim_customer(KODE_CUSTOMER,NAMA_CUSTOMER,ALAMAT_CUSTOMER,KOTA_CUSTOMER,TELEPON_CUSTOMER) values('J-0002','TOKO AYU','JL CIMANGGIS 12','JAKARTA','(021) 856-1321');  
insert into dim_customer(KODE_CUSTOMER,NAMA_CUSTOMER,ALAMAT_CUSTOMER,KOTA_CUSTOMER,TELEPON_CUSTOMER) values('B-0001','TOKO WARNA','JL ABC 234','BANDUNG','(022) 432-6635');  
insert into dim_customer(KODE_CUSTOMER,NAMA_CUSTOMER,ALAMAT_CUSTOMER,KOTA_CUSTOMER,TELEPON_CUSTOMER) values('B-0002','TOKO SURYA','JL ABC 309','BANDUNG','(022) 432-6024');  
insert into dim_customer(KODE_CUSTOMER,NAMA_CUSTOMER,ALAMAT_CUSTOMER,KOTA_CUSTOMER,TELEPON_CUSTOMER) values('B-0003','TOKO MAHARANI','JL KOPO 333','BANDUNG','(022) 856-3333');  
insert into dim_customer(KODE_CUSTOMER,NAMA_CUSTOMER,ALAMAT_CUSTOMER,KOTA_CUSTOMER,TELEPON_CUSTOMER) values('B-0004','TOKO MULYA','JL OTISTA 555','BANDUNG','(022) 422-5555');  
select * from dim_customer;

select * from fact_pasok;
insert into fact_pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E001','ELK-01','EJ-01','2002-01-01',8);
insert into fact_pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E002','ELK-01','EJ-02','2002-01-01',5);
insert into fact_pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E003','ELK-02','EJ-01','2002-02-01',2);
insert into fact_pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E004','ELK-02','EJ-02','2002-02-02',3);
insert into fact_pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E005','ELK-02','EB-02','2002-01-01',2);
insert into fact_pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E006','ELK-03','EJ-01','2002-03-03',5);
insert into fact_pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E007','ELK-03','EJ-01','2002-03-04',2);
insert into fact_pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E008','ELK-03','EJ-01','2002-03-03',3);
insert into fact_pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E009','ELK-03','EB-01','2002-03-13',4);
insert into fact_pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E010','ELK-03','EB-01','2002-03-13',3);
insert into fact_pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E011','ELK-04','EB-01','2002-04-22',12);
insert into fact_pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E012','ELK-04','EB-02','2002-04-30',9);
select * from fact_pasok;

select * from fact_pembelian;
insert into fact_pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E001','ELK-01','J-0001','2002-05-20',3);  
insert into fact_pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E002','ELK-01','J-0001','2002-05-21',4);  
insert into fact_pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E003','ELK-01','J-0002','2002-05-20',2);  
insert into fact_pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E004','ELK-01','B-0001','2002-05-20',2);  
insert into fact_pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E005','ELK-01','B-0002','2002-05-22',3);  
insert into fact_pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E006','ELK-02','J-0001','2002-06-24',1);  
insert into fact_pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E007','ELK-02','J-0002','2002-06-24',1);  
insert into fact_pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E008','ELK-02','B-0001','2002-06-25',2);
insert into fact_pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E009','ELK-02','B-0002','2002-06-25',2);
insert into fact_pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E010','ELK-03','J-0001','2002-06-20',5);
insert into fact_pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E011','ELK-03','J-0002','2002-07-02',4);
insert into fact_pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E012','ELK-03','B-0001','2002-07-02',6);
insert into fact_pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E013','ELK-03','J-0001','2002-07-10',5);
insert into fact_pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E014','ELK-04','J-0002','2002-07-15',12);
insert into fact_pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E015','ELK-04','B-0002','2002-07-17',15);
select * from fact_pembelian;

select * from dim_barang;
select * from dim_customer;
select * from dim_suplier;
select * from fact_pembelian;
select * from fact_pasok;
 

3. Query Datamart

3.1 Total Jumlah Barang yang Dipasok dan Dibeli

SELECT
    b.nama_barang AS NamaBarang,
    COALESCE(SUM(pasok.jumlah_pasok), 0) AS TotalPasok,
    COALESCE(SUM(beli.jumlah_pembelian), 0) AS TotalPembelian
FROM dim_barang b
LEFT JOIN fact_pasok pasok ON b.kode_barang = pasok.kode_barang
LEFT JOIN fact_pembelian beli ON b.kode_barang = beli.kode_barang
GROUP BY b.nama_barang;
 

3.2 Analisis Penjualan per Pelanggan

SELECT
    c.nama_customer AS NamaCustomer,
    b.nama_barang AS NamaBarang,
    SUM(beli.jumlah_pembelian) AS TotalPembelian
FROM fact_pembelian beli
JOIN dim_barang b ON beli.kode_barang = b.kode_barang
JOIN dim_customer c ON beli.kode_customer = c.kode_customer
GROUP BY c.nama_customer, b.nama_barang;
 

3.3 Total Pasokan dan Penjualan untuk Setiap Barang

SELECT
    b.nama_barang AS NamaBarang,
    COALESCE(SUM(pasok.jumlah_pasok), 0) AS TotalPasok,
    COALESCE(SUM(beli.jumlah_pembelian), 0) AS TotalPenjualan
FROM dim_barang b
LEFT JOIN fact_pasok pasok ON b.kode_barang = pasok.kode_barang
LEFT JOIN fact_pembelian beli ON b.kode_barang = beli.kode_barang
GROUP BY b.nama_barang;
 

3.4 Total Barang yang Dipasok oleh Setiap Suplier

SELECT
    s.nama_suplier AS NamaSuplier,
    SUM(pasok.jumlah_pasok) AS TotalPasok
FROM dim_suplier s
JOIN fact_pasok pasok ON s.kode_suplier = pasok.kode_suplier
GROUP BY s.nama_suplier;
 

3.5 Total Barang yang Dibeli oleh Setiap Pelanggan

SELECT
    c.nama_customer AS NamaCustomer,
    SUM(beli.jumlah_pembelian) AS TotalPembelian
FROM dim_customer c
JOIN fact_pembelian beli ON c.kode_customer = beli.kode_customer
GROUP BY c.nama_customer;
 

3.6 Penjualan per Barang dan Pelanggan

SELECT
    b.nama_barang AS NamaBarang,
    c.nama_customer AS NamaCustomer,
    SUM(beli.jumlah_pembelian) AS TotalPembelian
FROM fact_pembelian beli
JOIN dim_barang b ON beli.kode_barang = b.kode_barang
JOIN dim_customer c ON beli.kode_customer = c.kode_customer
GROUP BY b.nama_barang, c.nama_customer;
 

3.7 Barang Paling Banyak Dibeli

SELECT
    b.nama_barang AS NamaBarang,
    SUM(beli.jumlah_pembelian) AS TotalPembelian
FROM dim_barang b
JOIN fact_pembelian beli ON b.kode_barang = beli.kode_barang
GROUP BY b.nama_barang
ORDER BY TotalPembelian DESC;