BELAJAR POSTGRESQL DENGAN BANTUAN VIRTUALBOX

selamat berjumpa teman blogger,..
pada kali ini saya ingin mendokumentasikan tahapan belajar saya mengenai database relational dengan task yang digunakan adalah postgresql, dbeaver, virtualbox dan centos7.

oke, langsung saja pada implementasinya ya....

Tahapan pertama kita akan melakukan instalasi virtualbox pada ubuntu 18.04. hal ini saya lakukan agar belajar mengenal linux CentOS.

Download virtualbox di https://www.virtualbox.org/wiki/Linux_Downloads

Setelah selesai download arahkan ke path direktori tempat downloadnya, ektrak dan install

cd Download/ 
dpkg -i VirtualBox-5.2_5.2.8-121009~Ubuntu~bionic_amd64.deb.
apt-get -f install

kemudian buka virtualbox yang sudah terinstal

Tahapan kedua kita akan menginstall CentOS7 pada Virtualbox:
-  Download ISO CentOS7 (http://isoredirect.centos.org/centos/7/isos/x86_64/)
-  klik new pada virtualbox

karena kita mau install centos, maka kasih nama nya "CentOS" dan type "linux", version "Redhat" akan secara default terisi.

berikut tahapan pilihan yang di setting:

kemudian "OK" maka setelah itu mesin virtual centos7 kita siap untuk di instalasi.
setelah itu tekan start.

pilih install CentOs 7 dan berikut ini screen capture pilihan instalasi yang saya gunakan:

kemudian pilih "Begin Installations" dan setelah itu maka akan muncul permintaan untuk setting password root dan setting user akses. Berikut ini adalah settingan yang saya lakukan:

tunggu sampai proses selesai. Setelah selesai maka secara otomatis akan diminta untuk "Reboot" kemudian mesin virtual centos kita siap untuk di operasikan.

 setelah itu kita masuk bisa menggunakan root atau user yang tadi kita buat.

Oke, kawan blogger semuanya sampai sini instalasi minimal requiment centos7 kita sudah siap kita gunakan.

Tahapan Ketiga adalah melakukan setting SSH agar kita bisa mengakses virtual mesin kita di tempat lain. Berikut adalah cara yang kita lakukan:

arahkan directory ke :

cd /etc/sysconf ig/network-script
sudo vi ifcfg-enp0s3

ganti ONBOOT=no jadi ONBOOT=yes
caranya:
tekan i untuk insert kemudian tuliskan yes setelah selesai tekan esc, setelah itu tekan shiff+: kemudian tulisan wq untuk save & quit.

setelah itu lakukan reboot dengan mengetik reboot. kemudian, lakukan login kembali dan pengecekan dengan mengetik ip addr

perhatikan pada 2: enp0s3
jika ada link 10.0.2.15/24 maka sudah berhasil. Sekarang kita akan lakukan konfigurasi pada network nya. Dengan cara klik kanan pada bawah gambar mirip monitor 2 bagian itu pilih network setting.

Ok, sampai sini sudah selesai untuk settingan network onboot nya. Kemudian kita test apakah sudah bisa kita gunakan.

Tahapan berikut nya adalah melakukan remote virtual mesin centos7 nya melalui ubuntu 18.04 komputer saya. Caranya adalah melalui SSH : ssh -p 2222 rudy@127.0.0.1 

tekan enter dan masukan password user kita pada virtual mesin yang sudah dibuat.

ok, sampai sini kita sudah berhasil melakukan remote virtual mesin kita. Kemudian kita akan mempersiapkan komponen untuk belajar relational database.

Tahapan Ke empat adalah melakukan instalasi postgresql pada virtual mesin centos7 yang kita miliki.

sudo yum -y update
sudo yum install postgresql-server postgresql-contrib 
sudo postgresql-setup initdb

lakukan konfigurasi berikut:

sudo vi /var/lib/pgsql/data/pg_hba.conf

ganti

dengan komponen berikut ini:

Setelah selesai keluar dan simpan.
Kemudian, lakukan restart dan enable postgresql.

sudo systemctl start postgresql
sudo systemctl enable postgresql

mari kita buat postgresql role dan database:

sudo -i -u postgres

kemudian kita atur ulang terlebih dahulu password untuk postgres nya.

sudo passwd postgres

sudo vi /var/lib/pgsql/data/pg_hba.conf

tambahkan di IPV4

host      all        all       192.168.1.0/24         md5

sudo vi /var/lib/pgsql/data/postgresql.conf

aktifkan listen

listen_addresses='*'

Kemudian, lakukan lagi restart dan enable postgresql.

sudo systemctl start postgresql
sudo systemctl enable postgresql

setelah itu masuk ke root postgres:

sudo -u postgres psql

buat lah password verifikasi role untuk DB postgres:

ALTER USER postgres PASSWORD 'rty123';

kemudian buka dbeaver dan buatlah koneksi ke DB postgress yang tadi sudah kita buat, berikut adalah tahapannya:

sampai pada tahapan ini kita sudah bisa melakukan koneksi remote virtual mesin dan connector database postgresql ke tools database administrator dbeaver.

Pada umumnya orang ketika menggunakan postgresql maka dia akan menggunakan Pgadmin untuk tools administrator dan develoment nya. Kenapa saya menggunakan dbeaver pada tutorial ini? saya menggunakan dbeaver, alasannya adalah karena:

1. saya lagi malas install banyak tools databases administrator

2. saya senang dengan dbeaver karena dia memiliki service untuk multiplatform administrator
3. saya memiliki kebutuhan yang tidak begitu urgent untuk menggunakan pgadmin

jika teman-teman ingin mencoba pgadmin silahkan, nanti bisa sharing-sharing ya ilmunya.
oke teman-teman blogger, pagi ini kita akan masuk ke belajar query-query basic di postgresql.

Tahapan berikut adalah belajar mengenai query postgresql:

sebelumnya kita bikin satu konsep analisis sederhana sehingga kita bisa membuat data yang kita butuhkan.

Pertama, kita buat table dengan nama test_da1 dengan nama variabel : users_id, order_id, creat_time, payment, product

query untuk membuat tabel baru:

create table test_da1(
user_id varchar (8),
order_id varchar (6),
creat_time timestamp,
payment float8,
product varchar (100)
);

silahkan cek tabel data yang sudah dibuat dengan cara:

select *
from test_da1

Setelah itu kita akan memasukan nilai untuk setiap variabel yang sudah kita buat pada tabel test_da1.

insert into test_da1 (user_id, order_id, creat_time, payment, product)
values
('101', '10001', '2018-04-02 5:26:21', '48000', 'pulsa'),
('102', '10002', '2018-04-02 7:44:13', '25000', 'paket data'),
('101', '10003', '2018-04-02 8:26:21', '320000', 'listrik pln'),
('101', '10004', '2018-04-02 8:31:18', '180000', 'air pdam'),
('103', '10005', '2018-04-02 9:32:02', '21000', 'pulsa'),
('102', '10006', '2018-04-02 9:34:11', '200000', 'voucher game'),
('103', '10007', '2018-04-02 9:36:51', '11000', 'pulsa'),
('107', '10008', '2018-04-02 11:06:13', '28000', 'paket data'),
('105', '10009', '2018-04-02 11:16:06', '49000', 'listrik pln'),
('101', '10010', '2018-04-02 12:06:41', '1200000', 'kartu kredit'),
('105', '10011', '2018-04-02 12:12:58', '98000', 'listrik pln'),
('103', '10012', '2018-04-02 13:11:11', '85000', 'bpjs'),
('106', '10013', '2018-04-02 13:12:32', '240000', 'tv kabel'),
('108', '10014', '2018-04-02 13:26:45', '800000', 'multifinance'),
('106', '10015', '2018-04-02 13:46:09', '210000', 'telcom');

Setelah melakukan input data mari kita mencoba beberapa case sederhana seperti berikut ini:

temukan siapa saja users dengan beberapa kali transaksi dalam hari yang sama, apa product yang mereka cari dan berapa banyak uang yang harus mereka keluarkan pada transaksi kedua?
query nya seperti berikut ini:

select T.user_id, T.payment, T.product
from test_da1 as T
where creat_time = (select min(creat_time) from test_da1 where user_id= T.user_id and creat_time > (select min(creat_time) from test_da1 where user_id=T.user_id))
order by creat_time

menemukan nilai pembelian lebih besar dari nilai rata-rata keseluruhan payment?

select *
from test_da1
where payment > (select AVG (payment) as avg_payment from test_da1)

menemukan customer yang melakukan transaksi berurutan kurang dari 10 menit ?

select c.user_id , extract(minute from c.selang_waktu)
from(
select b.user_id , b.creat_time - b.lag_creat_time as selang_waktu
from (
    select 
user_id , 
creat_time, 
lag(creat_time, 1) over (partition by user_id order by creat_time) as lag_creat_time
    from  test_da1
    )b
    )c
    where selang_waktu < '10 mins'

menemukan total payment per users dan tampilkan dalam urutan besar ke kecil?

select user_id, sum(payment)total
from test_da1
group by 1
order by 2 desc

menemukan 10 kolom pertama dengan pengurutan dari besar ke kecil bersadarkan user_id?

select * 
from test_da1
order by 1 desc
limit 10

oke, kawan kita coba lanjut ke data soal no 2
berikut data yang dibuat:

create table test_da2(
customer_id varchar (8),
shop_id varchar (6),
item_price float8,
status_order varchar (45),
finish_time timestamp
)

insert into test_da2 (customer_id, shop_id, item_price, status_order, finish_time)
values 
('561061', '111','230000', 'finished', '2018-05-02 05:30:00'),
('645217', '111','60650', 'finished', '2018-02-13 12:15:00'),
('507023', '111','466500', 'finished', '2018-04-17 10:00:00'),
('983190', '111','871250', 'cancelled', '2018-03-13 15:10:40'),
('678876', '112','746500', 'finished', '2018-01-09 07:10:00'),
('880741', '111','779000', 'pending', '2018-01-09 13:40:10'),
('14936', '112','499000', 'finished', '2018-05-23 09:15:00'),
('804734', '112','751000', 'finished', '2018-03-14 17:50:20'),
('599983', '112','948000', 'finished', '2018-04-18 15:20:10'),
('233141', '112','612900', 'finished', '2018-02-13 10:00:00'),
('725865', '112','840500', 'finished', '2017-12-12 19:20:10'),
('215085', '113','999000', 'finished', '2018-04-17 20:40:50'),
('61165', '123','4250000', 'finished', '2018-01-08 03:50:20'),
('332028', '123','6450000', 'finished', '2017-12-13 06:40:12'),
('472965', '123','2615000', 'finished', '2018-02-06 19:24:12'),
('265245', '123','9815500', 'finished', '2018-03-21 00:50:00'),
('837958', '123','350000', 'finished', '2018-04-18 23:30:00'),
('352073', '123','5550000', 'finished', '2018-05-16 02:30:40'),
('242847', '123','2310500', 'finished', '2018-04-11 11:30:00'),
('746083', '123','7447500', 'pending', '2018-05-16 07:04:10'),
('139441', '124','1250000', 'cancelled', '2018-11-21 22:07:00'),
('954810', '13578','45000', 'cancelled', '2018-05-14 22:05:00'),
('112788', '45098','1350250', 'pending', '2018-02-20 19:30:00');

menemukan shoping price diatas 500k finished dengan transaksi 6 bulan terakhir asumsikan bahwa hari ini adalah 1 may?

select *
from test_da2
where finish_time between '2017-10-01 23:59:59' and '2018-04-01 23:59:59'
and status_order = 'finished'
and item_price > 500000

menemukan total price dengan asumsi sama seperti soal sebelumnya dan minimal 1 kali pembelanjaan finished?

select sum(item_price) as total_price
from test_da2
where finish_time between '2017-10-01 23:59:59' and '2018-04-01 23:59:59'
and status_order = 'finished'
and item_price > 500000

menemukan harga tertinggi berdasarkan status order finished dan diurutkan dari tinggi ke rendah?

select *
from test_da2
where status_order ='finished'
order by 3 desc

menemukan 3 top income berdasarkan finished order?

select shop_id, sum(item_price)
from test_da2
where status_order='finished'
group by 1
order by 2 desc
limit 3

pilihlah transaksi berdasarkan finish_time pada 10 transaksi pertama?

select *
from test_da2
where status_order= 'finished'
order by 5 asc
limit 10

Tags: #SQL