postgresql tutorial

Cara Install

Bila belum memiliki program postgresql anda dapat melakukan langkah berikut
- Untuk OS Mac
- buka website postgresapp.com
- kemudian klik pada download
- install program ke dalam application
- jalankan dari application
Download data dari website kelasdigital.com
- klik pada tombol download pada Data DVDrental yang akan digunakan
- pindahkan file hasil download(postgresql.zip) ke dalam My Document
- tekan mouse kanan pada file zip dan lakukan extract.
- kalau pada mac anda dapat langsung menekan open
- hasilnya di Folder My document akan ada folder postgresql
  - dalam folder ini ada 3 file 2 file gambar dan 1 file dvdrental.tar
install program microsoft visual studio code (vscode)
- download dari https://code.visualstudio.com/download
- lakukan install seperti biasa sesuai OS anda
membuka folder kerja
- jalankan program vscode
- pilih menu File, Open folder (ingat Open folder)
- pilih folder my document/postgresql
membuka terminal
- pilih menu View, Terminal
- atau tekan tombol ctrl + tilde (`)
pada layar terminal mulai praktek perintah2 berikut:
- psql -U postgres
  - masukkan password standard les
- Catatan! anda dapat mengganti tulisan yudy sesuai nama anda sendiri
- create user user_yudy with login password 'kid';
  - membuat user baru dengan nama user_yudy dengan password awal kid
- create database user_yudy;
  - setiap user ada database default, maka buat database user_yudy
- alter role user_yudy superuser;
  - memberikan user_yudy akses sebagai superuser
lakukan load database
- jalankan command prompt
- psql -U user_yudy
- masuk ke dalam system psql dengan user anda
- create database dvdrental_yudy;
  - membuat database baru dengan nama dvdrental_yudy
- \q
  - keluar
- pg_restore -U user_yudy -d dvdrental_yudy C:\temp\dvdrental.tar
  - melakukan restore data dari file tar yang ada di c:\temp\dvdrental.tar
  - user yang digunakan user_yudy
  - database yang digunakan adalah milik user_yudy dengan nama dvdrental_yudy
- psql -U user_yudy -d dvdrental_yudy
  - login dengan nama anda dan dengan database dvdrental milik anda
- \l
  - list database yang ada, pastikan dvdrental_namaanda ada di situ
- \d
  - tampilkan semua objek di database yang saat ini sedang aktif (table, view)
- \d customer
  - tampilkan semua kolom field customer

Perintah select, from, order, limit, offset

SELECT * FROM customer;
- tampilkan semua data customer
- tekan tombol j untuk kebawah
- tekan tombol k untuk keatas
- tekan tombol q untuk kembali tanpa melanjutkan

SELECT first_name, email FROM customer;

tampilkan hanya kolom tertentu

  SELECT film_id, title, release_year
  FROM film
  ORDER BY film_id
  LIMIT 4 OFFSET 3;

penjelasan
- pilih 3 kolom dari database film
- urutkan berdasarkan film_id
- limit 4 artinya tampilkan hanya 4 data
- offset 3 artinya lompati dulu 3 baris dulu baru tampilkan datanya

perintah where, like , in

operator yang bisa digunakan, =, >, <, >=, <=
- <> atau != yang artinya tidak sama (saran gunakan !=)
- AND , OR bila ada 2 syarat atau lebih.

ketik perintah berikut:

  SELECT last_name, first_name
  FROM customer
  WHERE first_name = 'Jamie' AND last_name = 'Rice';

artinya tampilkan nama dari table customer dengan syarat first_name nya Jamie dan last_name = Rice
penggunakan where dan like
- WHERE first_name LIKE 'Y_dy%'
  - _ artinya satu huruf sembarang
  - % artinya lebih dari satu huruf sembarang
- WHERE first_name ILIKE 'BAR%';
  - artinya first_name mengandung tulisan BAR atau bar,
  - ILIKE artinya tidak case sensitif
- WHERE first_name NOT LIKE '%kevin%'
  - nama yang mengandung kevin tidak ditampilkan

contoh penggunakan IN

  SELECT customer_id, rental_id, return_date
  FROM rental
  WHERE customer_id IN (1, 2)
  ORDER BY return_date DESC;

penjelasan
- tampilkan data dari tabel rental dimana customer_id harus bernilai 1 atau 2

contoh penggunaan IN dengan subquery

  SELECT first_name, last_name
  FROM customer
  WHERE customer_id IN (
   SELECT customer_id 
   FROM rental
   WHERE CAST (return_date AS DATE) = '2005-05-27');

subquery adalah query di dalam query
cast berfungsi convert tipe data dimana return_date menjadi tipe date
cara compare data mengetik tanggal adalah tahun-bulan-tanggal
contoh subquery sederhana

    SELECT film_id, title, rental_rate
    FROM film
    WHERE rental_rate > (
     SELECT AVG (rental_rate)
     FROM film
     );

contoh soal diatas adadalah menghitung rata-rata rental_rate terlebih dahulu
selanjutnya akan ditampilkan film yang ratenya diatas rata-rata

alias

alias adalah hasil proses dari satu / beberapa field menjadi satu field dengan nama baru

    SELECT first_name || ' ' || last_name AS full_name
    FROM customer
    ORDER BY full_name;

Inner join

perhatikan perintah berikut:

    SELECT c1.customer_id, first_name, last_name, amount, payment_date
    FROM customer c1
    INNER JOIN payment ON payment.customer_id = c1.customer_id;

penjelasan
- from customer c1 , dimana c1 adalah nama alias dari customer
- ada 2 tabel yang digunakan yaitu payment dan customer
- bila ada 2 nama customer_id harus disertakan dari tabel mana di bagian select

soal

tampilkan kolom first_name, last_name dari table customer, urutkan berdasarkan first_name ASC dan bila ada yang sma urutkan berdasarkan last_name DESC
tampilkan kolom film_id, title dan rental_rate (harga rentalnya), dari table film, diurutkan berdasarkan rental_rate secara DESC (besar ke kecil) batasi data yang ditampilkan hanya 10 saja
tampilkan customer_id, amount dan payment_date dari tabel payment dengan syarat jumlah pembayarannya (amount) adalah lebih kecil dari 1 dolar atau lebih besar dari 8 dolar
tampilkan customer_id, rental_id, return_date dari tabel rental dimana customer_id tidak boleh sama dengan 3,4 dan 6 urutkan dari besar ke kecil berdasarkan customer_id dan saat menampilkan batasi datanya hanya 3 0
tampilkan dari tabel customer kolom first_name dan last_name, dimana syaratnya first_namenya memiliki 1 huruf depan sembarang, huruf kedua - keempat adalah "her" dan sisanya sembarang
tampilkan customer_id, first_name, last_name dan amout serta payment_date dari tabel customer, yang dilakukan inner join dengan payment dimana customer_id harus sama dengan 2

jawab

soal 1

    SELECT first_name, last_name
    FROM customer
    ORDER BY first_name ASC, last_name DESC;

soal 2

    SELECT film_id, title, rental_rate
    FROM film
    ORDER BY rental_rate DESC
    LIMIT 10;

soal 3

    SELECT customer_id, amount, payment_date
    FROM payment
    WHERE amount <= 1 OR amount >= 8;

soal 4

    SELECT customer_id, rental_id, return_date
    FROM rental
    WHERE customer_id NOT IN (3, 4, 6)
    ORDER BY customer_id
    LIMIT 30;

soal 5

    SELECT first_name, last_name
    FROM customer
    WHERE first_name LIKE '_her%';

soal 6

    SELECT customer.customer_id, first_name, last_name,  payment.amount, payment_date
    FROM  customer
    INNER JOIN payment ON payment.customer_id = customer.customer_id
    WHERE customer.customer_id = 2;