PostgreSQL: Pemula sampai Mahir

00-README.md

DBA PostgreSQL: Pemula sampai Mahir

Belajar PostgreSQL untuk pemula sampai mahir, materi yang diulas diantaranya

1. Konsep Relational dan Object Relational Database management system (ORDBMS)
2. SQL Fundamental
3. Data Manipulation
4. Data Definition
5. Transaction Control
6. User Management
7. System privileges
8. Pl/PgSQL
9. Server Maintanance
10. Tunning Database Server
11. Loadbalancer/Replication
12. Backup/Restore
13. etc

02a-create-user-schema.sql

-- create user schema database
CREATE USER hr WITH SUPERUSER LOGIN PASSWORD 'hr';

-- create database
CREATE DATABASE hr WITH OWNER hr;

02a-docker-compose.yaml

version: '3.6'
services:
  postgres:
    image: ${PRIVATE_REPOSITORY}postgres:${POSTGRES_VERSION}
    environment:
      - POSTGRES_PASSWORD
      - POSTGRES_USER
      - POSTGRES_DB
    volumes:
      - pg_data:/var/lib/postgresql/data
    ports:
      - 5432:5432

  flyway: 
    image: ${PRIVATE_REPOSITORY}flyway/flyway
    command: -url=jdbc:postgresql://postgres:5432/hr -user=hr -password=hr -connectRetries=60 migrate
    profiles:
      - migrate
    volumes:
      - ./db/migration:/flyway/sql
    depends_on:
      - postgres

  pgadmin4:
    image: ${PRIVATE_REPOSITORY}dpage/pgadmin4
    profiles:
      - debug
    environment:
      - PGADMIN_DEFAULT_PASSWORD=${POSTGRES_PASSWORD}
      - PGADMIN_DEFAULT_EMAIL=${POSTGRES_USER}@example.com
    ports:
      - 55432:80
    volumes:
      - pg_admin_data:/var/lib/pgadmin
    depends_on:
      - postgres
volumes:
  pg_data:
  pg_admin_data:

02a-install-linux-ubuntu.bash

# Create the file repository configuration:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update the package lists:
sudo apt-get update

# Install the latest version of PostgreSQL and pgadmin4 editor
sudo apt-get -y install postgresql

02a-install-pgadmin4-dekstop-linux-apt.bash

# Install the public key for the repository (if not done previously):
sudo curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add

# Create the repository configuration file:
sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

# Install for desktop mode only:
sudo apt install pgadmin4-desktop

02a-start-linux-postgresql-service.bash

systemctl restart postgresql;
systemctl status postgresql;

03a-select-alias-column-department.sql

select  department_id as kode_divisi,
        department_name nama_department,
        manager_id as "Kode Manager"  
from departments;

03a-select-all-columns-departments.sql

select department_id, department_name, manager_id, location_id
from departments;

03a-select-all-departments.sql

select * 
from departments;

03a-select-distinct-multiple-columns.sql

select distinct (job_id, manager_id),
       job_id,
       manager_id
from employees;

03a-select-distinct.sql

select distinct job_id from employees;

03a-select-only-selected-columns-departments.sql

select department_id, department_name
from departments;

03a-select-special-characters.sql

select '*' bintang,
        E'()' kurung,
        E'\\' slash,
        E'baris pertama \n baris kedua' newline,
        E'awal\t setelah' tabspace;

03a-select-with-comments.sql

/* 
 * this query will return 
 * all data from table department
 */
 select 
  department_id, -- return id of department
  department_name -- return name of department
  -- location_id -- this not executed
 from departments;

03b-select-comparison-operator.sql

select 3 > 4 as compare_less_than,
       'nilai tidak sama' <> 'nilai sama' as compare_string_no_equal,
       '28-FEB-2022'::DATE = '28-FEB-2021'::DATE as compare_date_equal, -- '28-FEB-2022'::DATE sama seperti CAST('28-FEB-2022' as date)
       2800000 IS NOT NULL as compare_not_null,
       'off'::boolean IS NOT TRUE as compare_not_true; -- 'off'::boolean sama seperti CAST('off' as boolean)

03b-select-concate-operator.sql

select employee_id as id, 
       first_name || ' ' || last_name as nama_lengkap
from employees;

03b-select-logical-operator.sql

select  (true and true) "AND -> true x true",
        (true and false) "AND -> true x false",
        (false and false) "AND -> false x false",
        (null and false) "AND -> null x false",
        (null and true) "AND -> null x true",
        (true or true) "OR -> true x true",
        (true or false) "OR -> true x false",
        (false or false) "OR -> false x false",
        (null or true) "OR -> null x true",
        (null or false) "OR -> null x false",
        NOT(false) "NOT -> false",
        NOT(null) "NOT -> null",
        NOT(true) "NOT -> true";

03b-select-math-column-operation.sql

select 
    first_name, 
    salary + 1000 as gaji_plus 
from employees;

03b-select-math-date-operator.sql

select 
    current_date - 2 as "lusa",
    current_date + 1 as "besok",
    date '2017-03-28' - 2 as "kurang 2 hari", 
    date '2017-03-28' - interval '2 hour' as "kurang 2jam", 
    date '2017-03-28' + 1 as "tambah 1 hari",
    -- date '2017-03-28' + date '2017-05-28' as "gak boleh",
    date '2017-03-30' - date '2017-03-15' as "durasi dalam hari",
    timestamp '2017-03-28 18:20:00' - interval '15 hour' as "kurang 15jam", 
    date '2017-03-28' + interval '26 day' as "target harus selesai", 
    timestamp '2017-03-02 12:04:30' + interval '15 minutes' as "jam makan siang";

03b-select-math-operator.sql

select 
    2 + 2 as tambah,
    2 * 2 as kali,
    2 / 2 as bagi,
    2 ^ 3 as pangkat,
    @ -5.3 as abs,
    10 % 2 as mod;

03b-select-type-cast-string.sql

select cast ('100' as int) as string_to_int,
       cast ('10.3' as double precision) as string_to_double,
       cast ('28-FEB-2022' as date) as string_to_date,
       'dimasm' || cast (93 as varchar) as int_to_string,
       cast(0 as boolean) as int_to_boolean;

03c-select-datetime-function.sql

select  current_date tgl_sekarang,
        now() datetime_sekarang_func,
        current_timestamp as datetime_tz,
        age(timestamp '1991-03-01') as years_old,
        extract(year from current_timestamp) get_current_year,
        extract(month from current_date) get_current_month;
        

03c-select-formating-function.sql

select  to_char(current_date, 'DD/MON/YYYY') date_indonesia,
        to_char(current_timestamp, 'DD/MM/YYYY HH24:MM') datetime_indonesia,
        to_char(1000000, 'RpL999,999,999.00-') sejuta_rupiah,
        to_date('02/03/22', 'DD/MM/YY') format_ke_date,
        to_number('10,132,456.53', '999,999,999') format_ke_number;

03c-select-math-function.sql

select  abs(-10) "absolut",
        div(10, 3) "division",
        mod(5, 2) "mod",
        power(2, 3) "power",
        round(5.451234, 2) "round scale2",
        round(5.43) "round",
        round(5.6) "roundup",
        floor(5.45234) "floor",
        floor(5.6) "floor2";

03c-select-nuless-function.sql

select  COALESCE(null, 'data1', 'data2') return_data1,
        COALESCE(null, null, 'data2') return_data2,
        COALESCE(null, null, null) return_null,
        NULLIF(null, 'data1') return_null1,
        NULLIF('data1', 'data1') return_null2,
        NULLIF('data1', 'data2') return_data1;
 

03c-select-string-function.sql

select  substring('Dimas Maryanto' from 1 for 3) as "substring from",
        substr('Dimas Maryanto', 1, 5) as "substr",
        lower('Ini Adalah Text BESAR dan Kecil') as "lower",
        upper('Ini Adalah Text BESAR dan Kecil') as "upper",
        initcap('Ini Adalah Text BESAR dan Kecil') as "initcamp",
        trim(both ' ' from '  ini text ada spacenya  ') as "trim both",
        trim(trailing ' ' from '  ini text ada spacenya  ') as "trim trailing",
        reverse('dimas') as "reverse",
        length('dimas maryanto') as "length",
        concat('dimasm93', ' : ', 'Dimas Maryanto') as "concat",
        ascii('D') as "ascii";

03d-select-where-between-char.sql

select employee_id, first_name, last_name
from employees 
where substring(first_name from 2 for 1) between 'h' and 'j';

03d-select-where-between-number.sql

select employee_id, first_name, last_name, email, phone_number, job_id, salary
from employees 
where salary between 17000 and 20000;

03d-select-where-eq-any-some.sql

select employee_id, first_name, phone_number, job_id
from employees
where job_id = any (ARRAY['IT_PROG', 'SA_MAN', 'MK_MAN']);

03d-select-where-in.sql

select employee_id, first_name, phone_number, job_id
from employees
where job_id in ('IT_PROG', 'SA_MAN', 'MK_MAN');

03d-select-where-isnull.sql

select employee_id, first_name, last_name, job_id, manager_id
from employees 
where manager_id is null;

03d-select-where-like-percent.sql

select employee_id, first_name, last_name, email, phone_number, job_id, salary
from employees 
where last_name like 'A%';

03d-select-where-like-underscore.sql

select employee_id, first_name, last_name, email, phone_number, job_id, salary
from employees 
where last_name like '_t%';

03d-select-where-logic-and.sql

select employee_id, first_name, last_name, department_id, manager_id
from employees 
where department_id = 90 and manager_id = 100;

03d-select-where-logic-not.sql

select employee_id, first_name, last_name, job_id, manager_id, salary
from employees 
where salary not between 3000 and 20000;

03d-select-where-logic-or.sql

select employee_id, first_name, last_name, department_id, manager_id
from employees 
where department_id = 90 or manager_id = 100;

03d-select-where-posix.sql

select employee_id, first_name, phone_number, job_id
from employees
where first_name ~ '^S.*(a|v|ph)';

03d-select-where-relation-eq.sql

select employee_id, first_name, last_name, email, phone_number, job_id, salary
from employees 
where employee_id = 100;

03d-select-where-relation-greater-than.sql

select employee_id, first_name, last_name, email, phone_number, job_id, salary
from employees 
where salary >= 20000;

03d-select-where-similar-to.sql

select employee_id, first_name, phone_number, job_id
from employees
where first_name similar to 'Ste(v|ph)en';

03e-select-order-by-a-column-desc.sql

select location_id, department_id, department_name
from departments
order by location_id desc;

03e-select-order-by-a-column.sql

select location_id, department_id, department_name
from departments
order by location_id;

03e-select-order-by-column-number.sql

select location_id, department_id, department_name
from departments
order by 2;

03e-select-order-by-columns.sql

select location_id, department_id, department_name
from departments
order by location_id asc, department_id desc;

03e-select-order-by-with-null.sql

select employee_id, first_name, salary, commission_pct
from employees 
order by 
  salary desc,
  commission_pct asc nulls first;

03f-select-limit-number.sql

select employee_id, first_name, salary, commission_pct
from employees 
order by employee_id
limit 10;

03f-select-limit-offset-number.sql

select employee_id, first_name, salary, commission_pct
from employees 
order by employee_id
limit 5
offset 100;

03f-select-offset-number.sql

select employee_id, first_name, salary, commission_pct
from employees 
order by employee_id
offset 100;

04a-select-aggregate-func.sql

SELECT  max(salary) max_salary,
        min(salary) min_salary,
        avg(salary) avg_salary,
        count(*) count_employees
FROM employees;

04a-select-group-by-grouping-set.sql

SELECT manager_id, department_id, count(*), sum(salary)
FROM employees
GROUP BY GROUPING SETS ((manager_id), (department_id));

04a-select-group-by-with-aggregate.sql

SELECT  job_id,
        count(*) count_employees_by_job,
        sum(salary) salary_group_by_job
FROM employees
GROUP BY job_id;

04a-select-group-by-with-having.sql

SELECT  job_id,
        count(*) count_employees_by_job,
        sum(salary) salary_group_by_job
FROM employees
GROUP BY job_id
HAVING count(*) >= 5;

04a-select-group-by-with-where-and-having.sql

SELECT  job_id,
        count(*) count_employees_by_job,
        sum(salary) salary_group_by_job
FROM employees
WHERE job_id in ('FI_ACCOUNT', 'SA_MAN', 'IT_PROG', 'HR_REP', 'MK_MAN')
GROUP BY job_id
HAVING sum(salary) >= 20000;

04a-select-group-by.sql

SELECT  job_id
FROM employees
GROUP BY job_id;

04a-select-single-row-func.sql

SELECT  UPPER(last_name) nama,
        to_char(salary, '$L999,999.00') gaji_sebulan,
        concat(first_name, ' ', last_name) as nama_lengkap
FROM employees
LIMIT 10;

04b-basic-join-tables.sql

SELECT emp.job_id, job.job_title, emp.employee_id, emp.first_name, loc.street_address
FROM employees emp 
        natural join jobs job
        natural join locations loc
limit 10;

04b-cross-join.sql

SELECT dep.department_id, dep.department_name, job.job_id, job.job_title
FROM departments dep 
     CROSS JOIN jobs job;

04b-full-outer-join.sql

SELECT dep.department_id, dep.department_name, emp.employee_id, emp.last_name
FROM departments dep
      FULL OUTER JOIN employees emp on dep.manager_id = emp.employee_id;

04b-inner-join.sql

SELECT emp.employee_id, emp.last_name, job.job_id, job.job_title
FROM employees emp
      inner join jobs job on (emp.job_id = job.job_id)
LIMIT 10;

04b-join.sql

SELECT emp.employee_id, emp.last_name, job.job_id, job.job_title
FROM employees emp
      join jobs job on (emp.job_id = job.job_id)
LIMIT 10;

04b-left-outer-join.sql

SELECT dep.department_id, dep.department_name, emp.employee_id, emp.last_name
FROM departments dep
      LEFT OUTER JOIN employees emp on dep.manager_id = emp.employee_id;

04b-on-join-conditions.sql

SELECT dep.department_id, dep.department_name, emp.employee_id, emp.last_name
FROM departments dep
      LEFT OUTER JOIN employees emp on dep.manager_id = emp.employee_id;

04b-right-outer-join.sql

SELECT dep.department_id, dep.department_name, emp.employee_id, emp.last_name
FROM departments dep
      RIGHT OUTER JOIN employees emp on dep.manager_id = emp.employee_id;

04b-self-join.sql

SELECT  emp.employee_id "employee id", 
        emp.last_name as "employee name", 
        man.employee_id "manager id", 
        man.last_name "manager name"
FROM employees emp
  LEFT OUTER JOIN employees man on emp.manager_id = man.employee_id
LIMIT 10;

04b-using-join-conditions.sql

SELECT emp.employee_id, emp.last_name, job.job_id, job.job_title
FROM employees emp
      join jobs job using (job_id)
LIMIT 10;

04b-where-join-conditions.sql

SELECT emp.employee_id, emp.last_name, job.job_id, job.job_title
FROM employees emp, jobs job
WHERE emp.job_id = job.job_id
LIMIT 10;

04c-subquery-all-lower.sql

select employee_id, first_name, salary, job_id
from employees out
where salary < ALL (
    select max(salary) max_salary
    from employees
    group by job_id
    order by max_salary
);

04c-subquery-all-not-eq.sql

select employee_id, first_name, salary, job_id
from employees out
where salary <> ALL (
    select min(salary) min_salary
    from employees
    group by job_id
    order by min_salary
);

04c-subquery-any-eq.sql

select employee_id, first_name, salary, job_id
from employees out
where salary = any (
    select max(salary) max_salary
    from employees
    group by job_id
    order by max_salary
)
limit 10;

04c-subquery-any-higher.sql

select employee_id, first_name, salary, job_id
from employees out
where salary > any (
    select max(salary) max_salary
    from employees
    group by job_id
    order by max_salary
)
limit 10;

04c-subquery-basic-inline-view.sql

select emp.employee_id, emp.first_name, emp.salary, func.rata2, func.minimun, func.maximum
from employees emp,
     (select round(avg(job.max_salary), 0) rata2,
             min(job.max_salary)           minimun,
             max(job.max_salary)           maximum
      from jobs job) as func
where emp.salary >= func.rata2;

04c-subquery-basic-single-row-comparison.sql

select employee_id, first_name, salary
from employees emp
where emp.salary >= (select avg(max_salary) from jobs)
order by salary;

04c-subquery-exists-predicate.sql

select employee_id, first_name, job_id, salary
from employees out
where exists(
        select 1
        from job_history
        where employee_id = out.employee_id);

04c-subquery-in-predicate-multi-columns.sql

select employee_id, first_name, salary, job_id
from employees out
where (job_id, salary) in (
    select distinct job_id, (select max(min_salary) from jobs where inq.job_id = job_id)
    from employees inq
)
limit 10;

04c-subquery-in-predicate.sql

select employee_id, first_name, salary, job_id
from employees out
where job_id in (
    select distinct job_id
    from employees inq
    where inq.department_id = 80)
limit 10;

04c-subquery-lateral-corellate.sql

select emp.employee_id,
       emp.first_name,
       history.job_id,
       history.start_date
from employees emp,
     lateral (select job.job_id, job.start_date::date
              from job_history job
              where emp.employee_id = job.employee_id) as history
order by employee_id, job_id

04c-subquery-more-correlate.sql

select emp.employee_id,
       emp.first_name                           employee_name,
       emp.salary                               employee_salary,
       (select man.first_name
        from employees man
        where emp.manager_id = man.employee_id) manager_name,
       (select man.salary
        from employees man
        where emp.manager_id = man.employee_id) manager_salary
from employees emp
where emp.manager_id is not null
limit 10;

04c-subquery-multi-columns-single-row-comparison.sql

select employee_id, first_name, salary, coalesce(commission_pct, 0), job_id
from employees emp
where (emp.salary, emp.salary, emp.job_id) >= (
    select round(stddev(max_salary), 0), round(max(min_salary)), 'IT_PROG'
    from jobs
)
order by salary
limit 10;

04c-subquery-pairwaise-basic.sql

select employee_id, first_name, salary, commission_pct
from employees
where salary >= (
    select min(max_salary)
    from jobs
    where job_id = 'IT_PROG'
)
limit 10;

04c-subquery-pairwise-correlate.sql

select employee_id, first_name, salary, commission_pct, job_id
from employees emp
where emp.salary = (
    select avg(min_salary)
    from jobs job
    where emp.job_id = job.job_id
);

04c-subquery-select-column-list.sql

SELECT j.job_title,
       (SELECT min(h.start_date)::date FROM job_history h) as start_join
FROM jobs j;

04c-subquery-simple-correlate.sql

select emp.employee_id,
       emp.first_name                           employee_name,
       (select man.first_name
        from employees man
        where emp.manager_id = man.employee_id) manager_name
from employees emp
where emp.manager_id is not null
limit 10;

04d-select-case-when-else.sql

select employee_id    as kode_karyawan,
       commission_pct as besar_komisi,
       case
           when commission_pct is null
               then 'Tidak memiliki komisi'
           when commission_pct >= 0.2
               then 'Memiki komisi lebih besar dari 20%'
           else 'Memiliki komisi lebih kecil dari 10%'
           end
from employees
limit 50;

04d-select-case-when.sql

select employee_id    as kode_karyawan,
       commission_pct as besar_komisi,
       case
           when commission_pct is null
               then 'Tidak memiliki komisi'
           end
from employees;

04d-select-nested-case-when-else.sql

select employee_id    as kode_karyawan,
       commission_pct as besar_komisi,
       case
           when commission_pct is not null
               then
               case
                   when commission_pct <= 0.1
                       then 'Komisi sebesar 10%'
                   when commission_pct <= 0.2
                       then 'Komisi sebesar 20%'
                   when commission_pct <= 0.3
                       then 'Komisi sebesar 30%'
                   else 'Komisi lebih besar dari 30%'
                   end
           else 'Tidak memiliki komisi'
           end
from employees
limit 60;

04d-where-case-when-else.sql

select employee_id    as kode_karyawan,
       commission_pct as besar_komisi,
       salary         as gaji_sebulan
from employees
where case
          when commission_pct is null and salary <= 2200
              then true
          when commission_pct is null
              then false
          when commission_pct is not null and salary < 8000
              then salary in (7500, 7000, 7200)
          when commission_pct is not null and salary < 12000
              then salary = 11000
          end;

04e-select-except-all.sql

select *
from (values (1, 'dimasm93', 'Dimas Maryanto', true),
             (2, 'myusuf', 'Muhamad Yusuf', true),
             (3, 'mpurwadi', 'Muhamad Purwadi', false),
             (3, 'mpurwadi', 'Muhamad Purwadi', false)
     ) as data1
EXCEPT ALL
select *
from (values (1, 'dimasm93', 'Dimas Maryanto', true),
             (4, 'abdul', 'Abdul Rahman', false)
     ) as data2;

04e-select-except.sql

select *
from (values (1, 'dimasm93', 'Dimas Maryanto', true),
             (2, 'myusuf', 'Muhamad Yusuf', true),
             (3, 'mpurwadi', 'Muhamad Purwadi', false)
     ) as data1
EXCEPT
select *
from (values (1, 'dimasm93', 'Dimas Maryanto', true),
             (4, 'abdul', 'Abdul Rahman', false)
     ) as data2;

04e-select-intersect-all.sql

select *
from (values (1, 'dimasm93', 'Dimas Maryanto', true),
             (2, 'myusuf', 'Muhamad Yusuf', true),
             (2, 'myusuf', 'Muhamad Yusuf', true),
             (3, 'mpurwadi', 'Muhamad Purwadi', false)
     ) as data1
INTERSECT ALL
select *
from (values (1, 'dimasm93', 'Dimas Maryanto', true),
             (4, 'abdul', 'Abdul Rahman', false),
             (2, 'myusuf', 'Muhamad Yusuf', true),
             (2, 'myusuf', 'Muhamad Yusuf', true),
             (3, 'mpurwadi', 'Muhamad Purwadi', false)
     ) as data2;

04e-select-intersect.sql

select *
from (values (1, 'dimasm93', 'Dimas Maryanto', true),
             (2, 'myusuf', 'Muhamad Yusuf', true),
             (3, 'mpurwadi', 'Muhamad Purwadi', false)
     ) as data1
INTERSECT DISTINCT 
select *
from (values (1, 'dimasm93', 'Dimas Maryanto', true),
             (4, 'abdul', 'Abdul Rahman', false),
             (3, 'mpurwadi', 'Muhamad Purwadi', false)
     ) as data2;

04e-select-union-all.sql

select *
from (values (1, 'dimasm93', 'Dimas Maryanto', true),
             (2, 'myusuf', 'Muhamad Yusuf', true),
             (3, 'mpurwadi', 'Muhamad Purwadi', false)
     ) as data1
union all
select *
from (values (1, 'dimasm93', 'Dimas Maryanto', true),
             (4, 'abdul', 'Abdul Rahman', false)
     ) as data2;

04e-select-union-and-except.sql

select *
from (values (1, 'dimasm93', 'Dimas Maryanto', true),
             (2, 'myusuf', 'Muhamad Yusuf', true),
             (3, 'mpurwadi', 'Muhamad Purwadi', false)
     ) as data1
union
distinct
select *
from (values (1, 'dimasm93', 'Dimas Maryanto', true),
             (4, 'abdul', 'Abdul Rahman', false)
     ) as data2
except
select *
from (values (2, 'myusuf', 'Muhamad Yusuf', true)
     ) as data3;

04e-select-union-distinct.sql

select *
from (values (1, 'dimasm93', 'Dimas Maryanto', true),
             (2, 'myusuf', 'Muhamad Yusuf', true),
             (3, 'mpurwadi', 'Muhamad Purwadi', false)
     ) as data1
union distinct
select *
from (values (1, 'dimasm93', 'Dimas Maryanto', true),
             (4, 'abdul', 'Abdul Rahman', false)
     ) as data2;

04f-auxiliary-with-query.sql

with employees_in_dep_90 as
         (
             select *
             from employees
             where department_id in (90, 100, 110)
         ),
     top3_salaries as
         (
             select employee_id, first_name, salary
             from employees_in_dep_90
             order by salary desc
             limit 3
         )
select *
from top3_salaries;

04f-complex-with-query.sql

with top10_salaries as
         (
             select employee_id, first_name, salary, department_id
             from employees
             where salary >= 10000
             order by salary desc
             limit 10),
     department_uk_us as
         (
             select distinct department_id as dep_id
             from departments
                      natural join locations
             where country_id in ('UK', 'US')
         )
select *
from top10_salaries
where department_id in (select dep_id from department_uk_us);

04f-simple-with-query.sql

with get_emp_in_dep_hundred as (
    select *
    from employees
    where department_id = 100
)
select employee_id, first_name, salary, commission_pct
from get_emp_in_dep_hundred
limit 5;

04f-with-query-cycle-link-relation-path.sql

with recursive managed_by(manager_id, employee_id, first_name, depth, is_cycle, path) as
  (
      select our_manager.manager_id,
            our_manager.employee_id,
            our_manager.first_name,
            0,
            false,
            ARRAY [our_manager.employee_id]
      from employees our_manager
      UNION ALL
      select emp.manager_id,
            emp.employee_id,
            emp.first_name,
            man.depth + 1,
            emp.employee_id = ANY (path),
            path || emp.employee_id
      from employees emp
              join managed_by man on (emp.manager_id = man.employee_id)
      where NOT is_cycle
  )
select *
from managed_by;

04f-with-query-cycle-link-relation.sql

with recursive managed_by(manager_id, employee_id, first_name, depth) as
     (
         select our_manager.manager_id,
                our_manager.employee_id,
                our_manager.first_name,
                0
         from employees our_manager
         UNION ALL
         select emp.manager_id,
                emp.employee_id,
                emp.first_name,
                man.depth + 1
         from employees emp
                  join managed_by man on (emp.manager_id = man.employee_id)
     )
select *
from managed_by;

04f-with-query-recursive.sql

with recursive managed_by(manager_id, employee_id, first_name) as
     (
         select our_manager.manager_id,
                our_manager.employee_id,
                our_manager.first_name
         from employees our_manager
         where employee_id = 101
         UNION ALL
         select emp.manager_id,
                emp.employee_id,
                emp.first_name
         from employees emp
                  join managed_by man on (emp.manager_id = man.employee_id)
     )
select employee_id,
       first_name,
       manager_id
from managed_by;

04f-with-query-sortable-multi-columns.sql

with recursive managed_by(manager_id, employee_id, first_name, salary, department_id, path) as
     (
         select our_manager.manager_id,
                our_manager.employee_id,
                our_manager.first_name,
                our_manager.salary,
                our_manager.department_id,
                array [ROW (our_manager.department_id, our_manager.salary)]
         from employees our_manager
         where employee_id = 101
         UNION ALL
         select emp.manager_id,
                emp.employee_id,
                emp.first_name,
                emp.salary,
                emp.department_id,
                array [ROW (emp.department_id, emp.salary)]
         from employees emp
                  join managed_by man on (emp.manager_id = man.employee_id)
     )
select employee_id, first_name, manager_id, department_id, salary
from managed_by
order by path desc;

04f-with-query-sortable.sql

with recursive managed_by(manager_id, employee_id, first_name, path) as
     (
         select our_manager.manager_id,
                our_manager.employee_id,
                our_manager.first_name,
                array [our_manager.first_name]
         from employees our_manager
         where employee_id = 101
         UNION ALL
         select emp.manager_id,
                emp.employee_id,
                emp.first_name,
                array [emp.first_name]
         from employees emp
                  join managed_by man on (emp.manager_id = man.employee_id)
     )
select employee_id, first_name, manager_id
from managed_by
order by path desc;

05a-simple-delete.sql

DELETE FROM regions
WHERE region_id = 5;

05a-simple-insert-returning.sql

INSERT INTO regions (region_name)
VALUES ('Asia Tenggara')
RETURNING region_id;

05a-simple-insert.sql

INSERT INTO regions (region_id, region_name)
VALUES (5, 'Asia Tenggara');

05a-simple-update.sql

UPDATE regions
SET region_name = 'Oceania'
WHERE region_id = 5;

05b-dml-insert-multiple-rows.sql

INSERT INTO countries (country_id, country_name, region_id)
values ('ID', 'Indonesia', 3),
       ('SI', 'Singapore', 3),
       ('TH', 'Thailand', 3);

05b-dml-insert-no-specify-salary-column.sql

INSERT INTO employees (email, first_name, last_name, job_id)
values ('YUSUF', initcap('Muhamad'), initcap('yusuf'), upper('it_prog'));

05b-dml-insert-null-value-salary.sql

INSERT INTO employees (email, first_name, last_name, job_id, salary)
values ('PURWADI', initcap('muhamad'), initcap('purwadi'), upper('it_prog'), null);

05b-dml-insert-on-conflict-nothing.sql

INSERT INTO countries as newValue (country_id, country_name, region_id)
values ('ID', 'Indonesia', 3)
on conflict (country_id) do nothing;

05b-dml-insert-on-conflict-update.sql

INSERT INTO countries (country_id, country_name, region_id)
values ('ID', 'Republic Indonesia', 3)
on conflict (country_id) do 
  update SET country_name = excluded.country_name,
             region_id    = excluded.region_id;

05b-dml-insert-specific-columns.sql

INSERT INTO employees (email, first_name, last_name, job_id, salary)
values ('DIMAS', initcap('dimas'), initcap('maryanto'), upper('it_prog'), 15000);

05b-dml-insert-specific-datatype.sql

INSERT INTO job_history (employee_id, start_date, job_id)
VALUES (2, '2016-07-15', 'IT_PROG');

05b-dml-insert-using-default-value.sql

INSERT INTO employees (email, first_name, last_name, job_id, salary)
VALUES ('JUNAEDI', null, initcap('junaedi'), upper('it_prog'), DEFAULT);

05b-nested-insert-with-query.sql

WITH insert_emp as (
    INSERT INTO employees (first_name, last_name, email, job_id, salary, manager_id, department_id)
        VALUES (initcap('Dimas'), initcap('Maryanto'), upper('dimas'), 'IT_PROG', 5000, 102, 90)
        RETURNING employee_id, job_id, department_id
)
INSERT INTO job_history (employee_id, start_date, job_id, department_id)
SELECT employee_id, now(), job_id, department_id
FROM insert_emp;

05c-dml-update-default-expression.sql

UPDATE employees 
SET salary = DEFAULT
WHERE department_id = 10;

05c-dml-update-from-clause.sql

UPDATE employees emp
SET salary         = min_salary,
    commission_pct = 0.1
FROM jobs job
WHERE (job.job_id = emp.job_id)
  and department_id = 10;

05c-dml-update-returning-clause.sql

UPDATE employees emp
SET salary         = min_salary,
    commission_pct = 0.3
FROM jobs job
WHERE (job.job_id = emp.job_id)
  and department_id = 10
returning *;

05c-dml-update-set-column-list.sql

UPDATE employees emp
SET (salary, commission_pct) = (
    select min_salary,
           0.1 as commission_pct
    from jobs job
    where emp.job_id = job.job_id)
WHERE department_id = 10;

05c-dml-update-set-sub-select.sql

UPDATE employees emp
SET salary = (select min_salary from jobs job where emp.job_id = job.job_id)
WHERE department_id = 10;

05c-dml-update-with-clause.sql

with default_salary as (
    select job_id, min_salary, 0.2 as commission_pct
    from jobs
)
UPDATE employees emp
SET (salary, commission_pct) = (
    select ds.min_salary, ds.commission_pct
    from default_salary ds
    where ds.job_id = emp.job_id)
where department_id = 10;

05d-dml-delete-using-from_item.sql

delete from employees emp
  using job_history old
where old.job_id = 'ST_CLERK'
  and emp.employee_id = old.employee_id;

05d-dml-delete-where-sub-query.sql

delete from employees
where employee_id in (
    select employee_id
    from job_history
    where job_id = 'ST_CLERK'
);

05d-dml-delete-with-query.sql

WITH history_emp_from_dep as (
    select distinct employee_id
    from job_history
    where start_date > '1995-01-01'
)
DELETE
FROM employees emp
    USING history_emp_from_dep history
where emp.employee_id = history.employee_id
RETURNING *;

05d-dml-dml-returning.sql

DELETE FROM countries
WHERE country_id in ('ZM', 'ZW')
RETURNING *;

05e-dml-deallocate-prepared-statement.sql

PREPARE get_employee_by_dep(integer) as
    select emp.employee_id, emp.first_name, emp.job_id, job.job_title, dep.department_id, dep.department_name
    from employees emp
             join departments dep on emp.department_id = dep.department_id
             join jobs job on emp.job_id = job.job_id
    where dep.department_id = $1;

execute get_employee_by_dep(90);

DEALLOCATE PREPARE get_employee_by_dep;

05e-dml-insert-prepared-statement.sql

PREPARE create_a_employee(varchar(20), varchar(25), varchar(25), varchar(20), varchar(10), numeric(2, 2), int) as
    insert into employees(first_name, last_name, email, phone_number, job_id, commission_pct, department_id)
    VALUES ($1, $2, $3, $4, $5, $6, $7)
    RETURNING employee_id, concat(first_name, ' ', last_name);

--- you can execute multiple times in a session
execute create_a_employee('Muhamad', 'Purwadi', 'purwadi', '08211777', 'AD_VP', 0.1, 90);
execute create_a_employee('Deni', 'Sutisna', 'deni.sutisna', '08211666', 'AD_PRES', 0.2, 90);

05e-view-pg-prepared-statements.sql

select * from pg_prepared_statements;

05f-dml-cleanup-truncate.sql

truncate job_history RESTART IDENTITY CASCADE;

05f-dml-cleanup-vacuum.sql

VACUUM (VERBOSE, ANALYZE) employees;

06a-begin-commit-transaction.sql

BEGIN;

INSERT INTO regions(region_id, region_name)
VALUES (7, 'Other 2');

SELECT * FROM regions WHERE region_id = 7;

COMMIT;

06a-begin-rollback-transaction.sql

BEGIN;

UPDATE regions set region_name = 'Other 3'
WHERE region_id = 7;

SELECT * FROM regions WHERE region_id = 7;

ROLLBACK;

06a-begin-transaction.sql

BEGIN;

INSERT INTO regions(region_id, region_name)
VALUES (7, 'Other 2');

SELECT * FROM regions WHERE region_id = 7;

06a-savepoint-transaction.sql

BEGIN;

INSERT INTO regions(region_id, region_name)
VALUES (8, 'Other 3');

UPDATE regions
set region_name = 'Other 3'
WHERE region_id = 7;

SELECT * FROM regions WHERE region_id in (7, 8);

SAVEPOINT trx_1;

UPDATE regions
set region_name = 'Other 4'
WHERE region_id = 7;

INSERT INTO regions(region_id, region_name)
VALUES (8, 'Other 3');

SELECT * FROM regions WHERE region_id in (7, 8);

ROLLBACK TO SAVEPOINT trx_1;

SELECT * FROM regions WHERE region_id in (7, 8);

COMMIT;