.::: Implementation Store Procedure on Datawarehouse SQL Server ( Sample table barang, customer, suplier, pasok & pembelian) :::.

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

.::: Create table Inventory Datawarehouse SQL Server Original (Converting script from SQL Studio :::.

A. Create Database & Table 
create database teguhth
go

1. Table barang

-- table barang with index
CREATE TABLE [dbo].[barang](
[KODE_BARANG] [char](6) NOT NULL,
[NAMA_BARANG] [varchar](25) NULL,
[SATUAN_BARANG] [varchar](20) NULL,
[STOK_BARANG] [decimal](4, 0) NULL,
PRIMARY KEY CLUSTERED ([KODE_BARANG] ASC))

.::: Create Stored Procedure & Function Using SQL Server (Sample database moonton Mobile Legend) :::.

Stored Procedure

Stored Procedure merupakan sekumpulan perintah-perintah SQL yang tersimpan dengan nama tertentu dan diproses sebagai sebuah kesatuan. Bisa diakatan sebuah sub program yang tersimpan di database.

Membuat Function

Function adalah suatu blok SQL yang memiliki konsep sama dengan procedure, hanya saja pada function terdapat pengembalian nilai (return value)

A. Create database
create database mobile_legend
go

.::: Basic Store Procedure SQL Server :::.

1. Sample 1
-- SQL Query 1
select kode_barang, stok_barang from barang;

-- Store Procedure 1
create procedure kodestokbarang 
as 
select kode_barang, stok_barang from barang;

.::: Create Linked Server MSSQL to use Open Query (to another MSSQL) insert, update, delete :::.

A. Preconfigurasi
1. Konfiguration ODBC Data Source (64)
No Need Configuration ODBC

2. create Linked Server
Server Objects -> Linked Servers -> New Linked Server
General
Linked Server: MSSQL
Server Type: SQL Server
or 
Linked Server: MSSQL\instance
Linked Server: IP_SQLServer\instance

.::: Create Linked Server MSSQL to use Open Query (Oracle) insert, update, delete :::.

A. Preconfigurasi
1. Konfiguration ODBC Data Source (64)
No Need Configuration ODBC

2. create Linked Server
Server Objects -> Linked Servers -> New Linked Server
General
Linked Server: Oracle
Provider: Oracle Provider for OLE DB
Product name: Oracle
Data Source: localhost:1521/tka

.::: Create Linked Server MSSQL to use Open Query (MariaDB/MySQL) insert, update, delete :::.

A. Preconfigurasi
1. Konfiguration ODBC Data Source (64)
system DSN > Add >> MySQL ODBC 5.3 ANSI Driver

Connection Parameters

Data Source Name: dbatka

TCP/IP Server: teguhth-otherserver port 3306

user: root

password: []

Database: teguhth

.::: How to backup and restore table & database Oracle :::.

A. Backup & Restore table Oracle
1. Backup Table Database oracle 
exp aisyah/hanin@tka tables=pasok file="D:\labolatorium\oracle\backup\pasok.dmp"

2. Restore Table Oracle 
imp teguh/triharto@tka
imp teguh/triharto@tka tables=pasok file="D:\labolatorium\oracle\backup\pasok.dmp"

.::: Sample Aljabar(Algebra) Iner join, normal join, equal join(=),left outer join and right outer join In SQL :::.:::.

1. join/normal join/equal join(=)

bertujuan untuk mencari kesamaan data antara table yang satu dengan table yang lain
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SELECT     table-1.field-1,...,table-1.field-n,
        table-2.field-1,...,table-2.field-n,
        table-n.field-1,...,table-n.field-n,
FROM     table-1,...,table-n
WHERE     table-1.field_PK = table-2.field_PK AND
        table-2.field_PK = table-n.field_PK
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

.::: Sample Aljabar(Algebra): union(gabungan), intersection(irisan), MINUS (difference), distinct (proyeksi) In SQL :::.

1. UNION (Combine/gabungan)
union bertujuan menggabungkan dua query atau lebih menjadi satu kesatuan query dengan syarat query/subquery yang akan digabung harus memiliki domain kolom yang sama
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SELECT         field 1,...,field n,[agregate function]
FROM         table
 [WHERE]     [criteria]
 [GROUP BY] [field 1,...,field n]
UNION
SELECT         field 1,...,field n,[agregate function]
FROM         table
 [WHERE]     [kriteria]
 [GROUP BY] [field 1,...,field n]
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

.::: Sample Query, Sub Query from one table or More in SQL :::.

1. select one table

select * from BARANG;

select * from SUPLIER;

select * from PASOK;

2. select more table

select BARANG.NAMA_BARANG, PASOK.TANGGAL_PASOK as TGL_PASOK,PASOK.JUMLAH_PASOK from BARANG, PASOK where BARANG.KODE_BARANG=PASOK.KODE_BARANG;

select BARANG.kode_BARANG,PASOK.kode_PASOK, BARANG.nama_BARANG,BARANG.satuan_BARANG, PASOK.tanggal_PASOK as tglPASOK, jumlah_PASOK from BARANG, PASOK where BARANG.kode_BARANG=PASOK.kode_BARANG;

.::: Sample min, max, avg, sum, count, Group by, order by, Having, where, comparison (=,<>,>,<,>= atau <=), Between, In, like / not like on Grouping, Sorting & Criteria Data SQL :::.

1. pengelompokan data (group by) dan aggregation function
select * from PASOK;
select KODE_BARANG as KD_BRG, min(JUMLAH_PASOK) as MINIMUM_PASOK from PASOK group by KODE_BARANG;
select KODE_BARANG as KD_BRG, max(JUMLAH_PASOK) as MAXIMUM_PASOK from PASOK group by KODE_BARANG;
select KODE_BARANG as KD_BRG, avg(JUMLAH_PASOK) as AVERAGE_PASOK from PASOK group by KODE_BARANG;
select KODE_BARANG as KD_BRG, sum(JUMLAH_PASOK) as JUMLAH_PASOK from PASOK  group by KODE_BARANG;
select KODE_BARANG as KD_BRG, count(JUMLAH_PASOK) as COUNT_PASOK from PASOK group by KODE_BARANG;

.::: How To Add Column, Update, Insert & Delete row table on SQL example MariaDB :::.

1. Check original table Barang

2. Add Column table Barang (add KETERANGAN)

3. update row table ( update/fill column keterangan)

4. delete row table (example delete KODE_BARANG='ELK-99')

5. Modify Column table ( example change ELK02-ket to ELK02-ketvv2)

6. drop column on table (example delete/drop column KETERANGAN)

1. Check original table Barang

syntax

mysql -u root -p

show tables;

desc barang;

select * from barang;