Pelatihan dan Sertifikasi Kompetensi Teknis Profesi Basis Data Programmer

Hari 3

Galeri

Praktik Nilai Otomatis [Inventory]

– Nur Hidayat

set search_path=public;

-- Generated Value/Kolom dengan Nilai Otomatis
ALTER TABLE public.transaction_detail
ADD COLUMN amount numeric generated always as (quantity*unit_price) stored;

-- sample data for item
INSERT INTO item (code, name, spesification) VALUES ('I/0001', 'Orange', 'Orange Color') returning id;

-- sample data for master table
insert into transaction(id, code, date) values (1, 'PO-01/2020', extract(epoch from timestamp '2020-05-01'));
insert into transaction(id, code, date) values (2, 'PO-02/2020', extract(epoch from timestamp '2020-05-05'));
insert into transaction(id, code, date) values (3, 'PO-03/2020', extract(epoch from timestamp '2020-05-08'));

-- sample data for detail table
insert into transaction_detail(transaction_id, item_id, quantity, unit_price) values (1, 1, 15, 10000);
insert into transaction_detail(transaction_id, item_id, quantity, unit_price) values (1, 1, 25, 10000);
insert into transaction_detail(transaction_id, item_id, quantity, unit_price) values (2, 1, 35, 10000);
insert into transaction_detail(transaction_id, item_id, quantity, unit_price) values (3, 1, 45, 10000);
insert into transaction_detail(transaction_id, item_id, quantity, unit_price) values (3, 1, 55, 10000);

Join

– Nur Hidayat

• Intersection (irisan)
  • Irisan A dan B [tengah]
    -- alternatif 1
select * from A, B where A.ID=B.A_ID
-- alternatif 2: lebih mudah dibaca
select * from A join B on A.ID=B.A_ID
• Difference (selisih)
  • A Selisih B [kiri tengah]
    select * from A left join B on A.ID=B.A_ID where B.A_ID is null
  • B Selisih A [kanan tengah]
    select * from A right join B on A.ID=B.A_ID where A.ID is null
• Complement (komplemen)
  • A Komplemen B [kiri atas]
    select * from A left join B on A.ID=B.A_ID
  • B Komplemen A [kiri atas]
    select * from A right join B on A.ID=B.A_ID
• Union (Gabungan)
  • A Gabungan B [kiri bawah]
    select * from A full outer join B on A.ID=B.A_ID
• Inverse Intersection (kebalikan dari irisan)
  • Kebalikan dari A Irisan B
    select * from A full outer join B on A.ID=B.A_ID where A.ID is null or B.A_ID is null
• Hindari menggunakan in dan not in. Coba ubah ke dalam bentuk join, karena join menggunakan index, sedangkan in tidak menggunakan index.

Praktik Join [Education]

– Nur Hidayat

CREATE SCHEMA education
    AUTHORIZATION postgres;

set search_path = 'education';

create table students (
	id serial primary key,
	code varchar(20) not null,
	name varchar(200) not null
);

create table courses (
	id serial primary key,
	code varchar(20) not null,
	name varchar(200) not null
);

create table student_courses (
	id serial primary key,
	semester integer not null,
	student_id integer not null,
	course_id integer not null,
	constraint fk_student_courses_students
		foreign key (student_id) 
		references students (id),
	constraint fk_student_courses_courses
		foreign key (course_id) 
		references courses (id)
);

insert into students (code, name) values ('2016001','David Beckam');
insert into students (code, name) values ('2016002','Alexis Sanchez');
insert into students (code, name) values ('2016003','Mesut Oezil');
insert into students (code, name) values ('2016004','Lionel Messi');
insert into students (code, name) values ('2016005','Andres Iniesta');
insert into students (code, name) values ('2016006','Hector Bellerin');
insert into students (code, name) values ('2016007','Sergio Aguero');
insert into students (code, name) values ('2016008','David Silva');
insert into students (code, name) values ('2016009','Andik Firmansyah');
insert into students (code, name) values ('2016010','Boaz Sallosa');

insert into courses (code, name) values ('MKU001','Kewarganegaraan');
insert into courses (code, name) values ('MKU002','Bahasa Inggris');
insert into courses (code, name) values ('MKU003','Bahasa Indonesia');
insert into courses (code, name) values ('IKI001','Konsep Pemrograman');
insert into courses (code, name) values ('IKI002','Sistem Basis Data');
insert into courses (code, name) values ('IKI003','Sistem Operasi');
insert into courses (code, name) values ('IKI004','Grafika Komputer');
insert into courses (code, name) values ('IKI005','Matematika Diskrit');
insert into courses (code, name) values ('AST006','Astronomi Dasar');
insert into courses (code, name) values ('AST007','Astronomi Komputasi');

insert into student_courses (semester, student_id, course_id) values (1,1,1);
insert into student_courses (semester, student_id, course_id) values (1,1,2);
insert into student_courses (semester, student_id, course_id) values (1,1,5);
insert into student_courses (semester, student_id, course_id) values (1,1,9);
insert into student_courses (semester, student_id, course_id) values (1,1,7);
insert into student_courses (semester, student_id, course_id) values (1,2,5);
insert into student_courses (semester, student_id, course_id) values (1,2,7);
insert into student_courses (semester, student_id, course_id) values (1,2,9);
insert into student_courses (semester, student_id, course_id) values (1,3,1);
insert into student_courses (semester, student_id, course_id) values (1,3,2);
insert into student_courses (semester, student_id, course_id) values (1,4,5);
insert into student_courses (semester, student_id, course_id) values (1,4,9);
insert into student_courses (semester, student_id, course_id) values (1,5,7);
insert into student_courses (semester, student_id, course_id) values (1,5,5);
insert into student_courses (semester, student_id, course_id) values (1,5,1);
insert into student_courses (semester, student_id, course_id) values (1,5,9);
insert into student_courses (semester, student_id, course_id) values (1,8,7);
insert into student_courses (semester, student_id, course_id) values (1,8,5);
insert into student_courses (semester, student_id, course_id) values (1,8,1);
insert into student_courses (semester, student_id, course_id) values (1,8,9);
insert into student_courses (semester, student_id, course_id) values (1,8,6);
insert into student_courses (semester, student_id, course_id) values (1,8,2);
insert into student_courses (semester, student_id, course_id) values (1,9,1);
insert into student_courses (semester, student_id, course_id) values (1,9,9);

insert into student_courses (semester, student_id, course_id) values (2,2,1);
insert into student_courses (semester, student_id, course_id) values (2,2,2);
insert into student_courses (semester, student_id, course_id) values (2,2,5);
insert into student_courses (semester, student_id, course_id) values (2,2,9);
insert into student_courses (semester, student_id, course_id) values (2,2,7);
insert into student_courses (semester, student_id, course_id) values (2,3,5);
insert into student_courses (semester, student_id, course_id) values (2,3,7);
insert into student_courses (semester, student_id, course_id) values (2,3,9);
insert into student_courses (semester, student_id, course_id) values (2,4,1);
insert into student_courses (semester, student_id, course_id) values (2,4,2);
insert into student_courses (semester, student_id, course_id) values (2,5,5);
insert into student_courses (semester, student_id, course_id) values (2,5,9);
insert into student_courses (semester, student_id, course_id) values (2,6,7);
insert into student_courses (semester, student_id, course_id) values (2,6,5);
insert into student_courses (semester, student_id, course_id) values (2,6,1);
insert into student_courses (semester, student_id, course_id) values (2,6,9);
insert into student_courses (semester, student_id, course_id) values (2,9,7);
insert into student_courses (semester, student_id, course_id) values (2,9,5);
insert into student_courses (semester, student_id, course_id) values (2,9,1);
insert into student_courses (semester, student_id, course_id) values (2,9,9);
insert into student_courses (semester, student_id, course_id) values (2,9,6);
insert into student_courses (semester, student_id, course_id) values (2,9,2);
insert into student_courses (semester, student_id, course_id) values (2,10,1);
insert into student_courses (semester, student_id, course_id) values (2,10,9);

-------------------
-- Menampilkan seluruh mahasiswa dan mata  
-- kuliah yang dia ambilnya di semester 1.
-------------------
select s.id, s.code, s.name, c.id, c.code, c.name
from students s
join student_courses sc on s.id = sc.student_id
join courses c on sc.course_id = c.id
where sc.semester = 1
order by s.code, s.name, c.code, c.name

-------------------
-- Menampilkan seluruh mahasiswa dan mata  
-- kuliah yang dia ambilnya di semester 1.
-- -> dengan menggunakan alias di nama kolom/field
-------------------
select s.code as student_code, s.name as student_name
, c.code as course_code, c.name as course_name
from students as s
join student_courses as sc on s.id = sc.student_id
join courses as c on sc.course_id = c.id
where sc.semester = 1
order by s.code, s.name, c.code, c.name;

-------------------
-- Menampilkan data mahasiswa yang sama sekali 
-- tidak mengambil mata kuliah di semester 1
-- NOTE: Ini query yang salah
-------------------
select s.id, s.code, s.name, c.id, c.code, c.name
from students s
left join student_courses sc on s.id = sc.student_id 
left join courses c on sc.course_id = c.id
where sc.semester = 1
and c.code is null
order by s.code, s.name, c.code, c.name

-------------------
-- Menampilkan data mahasiswa yang sama sekali 
-- tidak mengambil mata kuliah di semester 1
-- NOTE: Ini query yang benar, filter semester harus di JOIN condition
-------------------
select s.id student_id, s.code student_code, s.name student_name
, c.id course_id, c.code course_code, c.name course_name, sc.semester
from students s
left join student_courses sc on s.id = sc.student_id and sc.semester = 1
left join courses c on sc.course_id = c.id
where c.code is null
order by s.code, s.name, c.code, c.name;

-------------------
-- Menampilkan data mata kuliah yang 
-- tidak ada yang mengambil di semester 1.
-- NOTE: Ini query yang salah
-------------------
select s.id, s.code, s.name, c.id, c.code, c.name
from students s
join student_courses sc on s.id = sc.student_id 
right join courses c on sc.course_id = c.id
where sc.semester = 1
and s.code is null
order by s.code, s.name, c.code, c.name

-------------------
-- Menampilkan data mata kuliah yang 
-- tidak ada yang mengambil di semester 1.
-- NOTE: Ini query yang benar, filter semester harus di JOIN condition
-------------------
select s.id, s.code, s.name, c.id, c.code, c.name
from students s
join student_courses sc on s.id = sc.student_id and sc.semester = 1
right join courses c on sc.course_id = c.id
where s.code is null
order by s.code, s.name, c.code, c.name

-------------------
-- Menampilkan daftar semua mahasiswa berikut dengan 
-- jumlah mata kuliah yang diambilnya di semester ke-1, 
-- serta menampilkan angka 0 (nol) untuk mahasiswa 
-- yang tidak mengambil mata kuliah apapun.
-- NOTE: Ini query yang salah
-------------------
select s.id, s.code, s.name, count(sc.id) jumlah
from students s
left join student_courses sc 
    on s.id=sc.student_id
where sc.semester = 2
group by s.id
order by s.code, s.name

-------------------
-- Menampilkan daftar semua mahasiswa berikut dengan 
-- jumlah mata kuliah yang diambilnya di semester ke-1, 
-- serta menampilkan angka 0 (nol) untuk mahasiswa 
-- yang tidak mengambil mata kuliah apapun.
-- NOTE: Ini query yang benar, filter semester harus di JOIN condition
-------------------
select s.id, s.code, s.name, count(sc.id) jumlah
from students s
left join student_courses sc 
    on s.id=sc.student_id and sc.semester = 1 
group by s.id
order by s.code, s.name

-------------------
-- Menampilkan daftar semua matakuliah berikut dengan 
-- jumlah mata kuliah yang diambilnya di semester ke-1, 
-- serta menampilkan angka 0 (nol) untuk mahasiswa 
-- yang tidak mengambil mata kuliah apapun.
-- NOTE: Ini query yang benar, filter semester harus di JOIN condition
-------------------
select s.id, s.code, s.name, count(sc.id) jumlah
from courses s
left join student_courses sc 
    on s.id=sc.student_id and sc.semester = 1 
group by s.id
order by s.code, s.name

Stored Procedure [Education]

– Nur Hidayat

Rule of Thumb penggunaan basis data

1. Gunakan satu perintah SQL
2. Gunakan stored procedure
3. Gunakan aplikasi
4. Fikirkan kembali apa yang diinginkan

<?php
//perspektif programmer sistem
for ($i = 1; $i <= 100; $i++)
{
  if ($counter % 15 == 0)
    echo "Database Programmer\n";
  else if ($counter % 3 == 0)
    echo "Database\n";
  else if ($counter % 5 == 0)
    echo "Programmer\n";
  else
    echo "$i\n";
}

function sp_loop($start_, $end)
{
  for ($i = 1; $i <= 100; $i++)
  {
    if ($counter % 15 == 0)
      echo "Database Programmer\n";
    else if ($counter % 3 == 0)
      echo "Database\n";
    else if ($counter % 5 == 0)
      echo "Programmer\n";
    else
      echo "$i\n";
  }
}

-- programmer database
select case
  when generate_series % 15 = 0 then 'Database Programmer' 
  when generate_series % 3 = 0 then 'Database'
  when generate_series % 5 = 0 then 'Programmer'
  else generate_series::text end hasil
from generate_series(1, 100);

create or replace procedure sp_loop (start_ int, end_ int) language plpgsql    
as $$
begin
  select case
  when generate_series % 15 = 0 then 'Database Programmer' 
  when generate_series % 3 = 0 then 'Database'
  when generate_series % 5 = 0 then 'Programmer'
  else generate_series::text end hasil
from generate_series(start_, end_);
end $$

set search_path=education;

-- contoh user-defined function di postgresql
CREATE OR REPLACE FUNCTION sp_matakuliahdiambil (semester_ int) RETURNS refcursor AS $$
DECLARE
ref refcursor;
BEGIN
  OPEN ref FOR select s.id, s.code, s.name, count(sc.id) jumlah
    from courses s
    left join student_courses sc 
    on s.id=sc.student_id and sc.semester = semester_
    group by s.id
    order by s.code, s.name;
  RETURN ref;
END;
$$ LANGUAGE plpgsql;

Trigger [Inventory]

– Nur Hidayat

set search_path=public;

-- skenario: jika menambahkan/memperbarui/menghapus detail transaksi, maka akan menjumlahkan total amount ke tabel transaksi
ALTER TABLE public.transaction
ADD COLUMN amount numeric;

-- buat dulu stored procedure
CREATE OR REPLACE FUNCTION public.sp_transaction_updateamount() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  update public.transaction set amount=(select sum(amount) from public.transaction_detail where transaction_id=NEW.transaction_id) where id=NEW.transaction_id;
  RETURN NEW;
END;
$$

-- buat trigger
CREATE TRIGGER trg_ai_transaction_detail
    AFTER INSERT ON public.transaction_detail
    FOR EACH ROW
    EXECUTE PROCEDURE public.sp_transaction_updateamount();

CREATE TRIGGER trg_au_transaction_detail
    AFTER UPDATE ON transaction_detail
    FOR EACH ROW
    EXECUTE PROCEDURE sp_transaction_updateamount();

CREATE TRIGGER trg_ad_transaction_detail
    AFTER DELETE ON transaction_detail
    FOR EACH ROW
    EXECUTE PROCEDURE sp_transaction_updateamount();

-- cek amount (masih null semua)
select * from transaction;

-- update semua data (idnya saja) agar trigger dijalankan
update transaction_detail set id=id;


-- cek amount (sudah terisi dari trigger)
select * from transaction;