SQL Basic

00. Database

  • Database adalah kumpulan data yang tersusun secara terstruktur agar mudah diakses, dikelola, diperbaharui dan dianalisis. Database terbagi menjadi dua jenis, yaitu database relasional dan database non-relational.
  • Database relasional adalah jenis database yang menyimpan data dalam tabel (table) yang saling berhubungan melalui relasi (relationship). Setiap tabel terdiri dari baris (row/record) dan kolom (column/field). Satu baris sama dengan satu data atau satu entitas, sedangkan satu kolom berisi informasi tentang data tersebut.
  • Database non-relasional (atau NoSQL - Not Only SQL) adalah jenis database yang tidak menyimpan data dalam tabel yang saling berelasi seperti database relasional. Sebaliknya, data dapat disimpan dalam berbagai format, seperti dokumen, key-value, kolom, atau graf.
01. RDBMS
  • RDBMS (Relational Database Management System) adalah software yang digunakan untuk mengelola database relasional tersebut.
  • RDBMS memiliki berbagai fungsi, di antaranya:
    • Membuat database dan tabel.
    • Menambah, mengubah, dan menghapus data.
    • Menjalankan query menggunakan SQL.
    • Mengatur hubungan (relasi) antar tabel.
    • Menjaga keamanan data melalui hak akses pengguna.
    • Menjaga konsistensi data dengan transaksi (ACID).
    • Membuat backup dan melakukan recovery data.
  • Contoh RDMBS adalah MySQL, PostgreSQL, Microsoft SQL Server, Oracle Database, SQLite, dan MariaDB.
  • Conton NoSQL DBMS (Database Management System) atau NoSQL Database Management System adalah MongoDB (document database), Redis (key-value database), Apache Cassandra (column database), Neo4j (graph database).
02. Pengenalan SQL
  • SQL (Structured Query Language) adalah bahasa yang digunakan untuk berkomunikasi dengan database relasional. SQL memungkinkan kita membuat database, menyimpan data, mengambil data, mengubah data, dan menghapus data. 
  • SQL juga memungkinkan pengguna untuk membuat dan mengelola struktur database, seperti tabel, indeks, dan skema. 
03. Database PostgreSQL
  • PostgreSQL adalah RDBMS (Relational Database Management System) open source yang digunakan untuk mengelola database relasional menggunakan SQL. 
  • PostgreSQL terkenal karena stabil, kaya fitur, dan mampu menangani data dalam skala kecil maupun besar, sehingga menjadi salah satu pilihan utama di industri.
04. Perintah SQL PostgreSQL
  • Tanda ; dalam query SQL akan mengeksekusi query. Jadi query SQL wajib ditutup dengan ; (titik koma).
  • Dalam PostgreSQL, ada beberapa perintah SQL yang digunakan untuk mengelola database (bukan tabel), diantaranya:
    • Membuat database baru: CREATE DATABASE nama_database;
    • Menghapus database (⚠️ Peringatan: Perintah ini akan menghapus database beserta seluruh isinya secara permanen): DROP DATABASE nama_database;
    • Melihat database yang sedang digunakan: SELECT current_database();
    • Mengubah nama database: ALTER DATABASE nama_lama RENAME TO nama_baru;
05. Perintah help di prompt psql
  • psql (command-line PostgreSQL) adalah alat untuk berkomunikasi/berinteraksi dengan server PostgreSQL, menjalankan perintah SQL, dan melakukan administrasi database melalui terminal.
  • Perintah help di Command Prompt (CMD) digunakan untuk menampilkan daftar perintah yang tersedia beserta penjelasan singkatnya. Hasil outputnya antara lain:
    • \copyright untuk melihat informasi lisensi dan hak cipta PostgreSQL.
    • \h untuk melihat semua perintah SQL.
    • \? untuk melihat semua meta-command psql, misalnya: \l (melihat database), \dt (melihat daftar tabel), \d (deskripsi tabel), \c (pindah database), \i (menjalankan file SQL), \q (keluar).
  • Perintah psql lainnya sebagai berikut:
    • \list atau \l melihat semua database.
    • \h SELECT untuk melihat bantuan khusus perintah SELECT.
    • \g akan mengeksekusi query yang sudah diketik selain titik koma.
    • \! memberi tahu psql untuk menjalankan perintah di shell (terminal). Contoh penggunaan dengan perintah shell, yaitu \! clear; akan membersihkan layar terminal (di Linux/macOS).
    • connect nama_database; atau \c nama_database; menggunakan/terhubung ke database tertentu.
06. Perintah shell
  • Shell adalah program yang menjadi perantara antara pengguna dan sistem operasi.
  • Perintah shell adalah perintah yang dijalankan langsung oleh sistem operasi melalui terminal atau command line, bukan perintah SQL. Contoh perintah shell antara lain:
    • dir menampilkan daftar file dan folder.
    • cd berpindah folder.
    • cls atau clear membersihkan layar.
    • mkdir membuat folder.
    • del menghapus file.
    • copy menyalin file.
    • move memindahkan file.
07. Tipe Data PostgreSQL
  •  Tipe Data Numerik


    Keterangan:
    • Tipe data SMALLINT, INTEGER (INT), dan BIGINT digunakan untuk menyimpan angka bulat (tanpa angka di belakang koma). 
    • SMALLINT digunakan jika angka yang disimpan relatif kecil (misalnya umur, nilai ujian, jumlah anak). 
    • INTEGER paling sering digunakan untuk angka bulat umum (misalnya ID, stok barang, jumlah pelanggan). 
    • BIGINT digunakan jika angka bisa sangat besar (misalnya jumlah view YouTube, jumlah transaksi e-commerce, data sensor).
    • Tipe data REAL, DOUBLE PRECISION, NUMERIC (p, s), DECIMAL (p, s) digunakan untuk angka yang memiliki koma/desimal.
    • REAL digunakan jika ukuran angka kecil tapi presisi terbatas (misalnya tinggi). Sebagai contoh, tingginya 170.123456 yang disimpan mungkin 170.12346 karena hanya sekitar 6 digit signifikan.
    • DOUBLE PRECISION digunakan jika presisinya tinggi (misalnya nilai pi). Sebagai contoh nilai pi 3.141592653589793 masih dapat disimpan dengan cukup akurat. DOUBLE menggunakan memori lebih besar tetapi hasilnya lebih akurat.
    • NUMERIC (p, s) menyimpan angka desimal secara tepat (misalnya harga, saldo, pajak, diskon). p adalah jumlah maksimal digit dan s adalah jumlah digit dibelakang koma. Contoh: NUMERIC (10, 2) artinya maksimal 10 digit dan 2 digit di belakang koma, misalnya 12345678.99.
    • DECIMAL (p, s) sama seperti NUMERIC. 
    • Tipe data SERIAL dan BIGSERIAL digunakan untuk membuat angka bertambah otomatis (auto increment).
    • SERIAL dipakai jika pimary key pada tabel berukuran normal. Sedangkan BIGSERIAL jika primary key pada tabel berukuran sangat besar.

  • Tipe Data Karakter


    Keterangan:
    • CHAR (Character) digunakan untuk data yang selalu memiliki panjang yang sama, misalnya: kode negara, jenis kelamin, kode mata uang.
    • VARCHAR (Variable Character) digunakan untuk data dengan panjang yang bervariasi, tetapi memiliki batas maksimum, misalnya: nama, email, nama produk, kota, alamat singkat.
    • TEXT digunakan untuk menyimpan teks tanpa menentukan batas panjang, misalnya: deskripsi produk, artikel, komentar pengguna, catatan, log.

  • Tipe Data Waktu


    Keterangan:
    • Tipe data DATE cocok digunakan untuk tanggal lahir, tanggal masuk kerja, tanggal transaksi, dan tanggal pemesanan.
    • Tipe data TIME cocok digunakan untuk jam buka toko, jam masuk kantor, jadwal pelajaran, dan waktu alarm.
    • Tipe data TIMESTAMP cocok digunakan untuk waktu transaksi, waktu login, waktu checkout, dan waktu upload file.
    • Tipe data INTERVAL dapat digunakan untuk perhitungan, misalnya durasi pengiriman, lama proses, dan selisih waktu.
  • Tipe Data BOOLEAN
    Tipe data BOOLEAN digunakan untuk menyimpan nilai kebenaran dengan tiga kemungkinan:
    • TRUE (benar)
    • FALSE (salah)
    • NULL (belum diketahui atau belum diisi)
  • Di dalam postgreSQL terdapat berbagai macam tipe data, lengkapnya dapat dilihat disini: https://www.postgresql.org/docs/current/datatype.html 
08. Contraints
  • Dalam PostgreSQL (psql), constraint adalah aturan yang diterapkan pada kolom tabel untuk berbagai macam kebutuhan. Jenis-jenis constraint sebagai berikut:
  • PRIMARY KEY : Menjamin bahwa setiap baris memiliki identitas yang unik dan tidak boleh NULL. Di dalam sebuah tabel, hanya terdapat satu kolom PRIMARY KEY, yaitu biasanya pada kolom id atau kode unik.

    Contoh penggunaan PRIMARY KEY pada kolom id:
    CREATE TABLE customers(
		id SERIAL PRIMARY KEY,
		name VARCHAR(100),
		age SMALLINT,
		email text,
		address text
);
    Keterangan:
    • Tidak boleh ada id yang sama.
    • id tidak boleh NULL.

  • UNIQUE: Memastikan bahwa semua nilai dalam suatu kolom harus unik (tidak boleh ada duplikasi). Perbedaan dengan PRIMARY KEY, kita bisa membuat beberapa kolom UNIQUE dalam satu tabel, sedangkan PRIMARY KEY hanya boleh satu.

    Contoh penggunaan UNIQUE pada kolom email:
    CREATE TABLE customers(
		id SERIAL PRIMARY KEY,
		name VARCHAR(100),
		age SMALLINT,
		email text UNIQUE,
		address text
);
    Keterangan:
    • kolom email datanya harus unik (tidak boleh ada email yang sama di kolom email).

  • NOT NULL: Memastikan bahwa kolom tidak boleh berisi nilai NULL.
    Contoh penggunaan NOT NULL pada kolom name dan email:
    CREATE TABLE customers(
		id SERIAL PRIMARY KEY,
		name VARCHAR(100) NOT NULL,
		age SMALLINT,
		email text UNIQUE NOT NULL,
		address text
);
    Keterangan:
    •  Kolom name dan email tidak boleh kosong datanya (wajib diisi).  

  • CHECK: Memungkinkan kita untuk menentukan kondisi tertentu yang harus dipenuhi setiap baris.
    Contoh penggunaan CHECK pada kolom age:
    CREATE TABLE customers(
		id SERIAL PRIMARY KEY,
		name VARCHAR(100) NOT NULL,
		age SMALLINT CHECK (age>17),
		email text UNIQUE NOT NULL,
		address text
);
    Keterangan:
    • Ketentuan di kolom age harus lebih dari 17 tahun. 

  • DEFAULT:  Memberikan nilai default/bawaan pada suatu kolom jika pengguna tidak memasukkan nilai secara eksplisit.
    Contoh penggunaan DEFAULT pada kolom address:
    CREATE TABLE customers(
		id SERIAL PRIMARY KEY,
		name VARCHAR(100) NOT NULL,
		age SMALLINT CHECK (age>17),
		email text UNIQUE NOT NULL,
		address text DEFAULT 'no address'
);
    Keterangan:
    •  Kolom address jika tidak diisi maka akan otomatis diisi dengan no address.
09. Persiapan Belajar: Menjalankan sebuah container PostgreSQL menggunakan Docker
  • Download tools yang akan digunakan di sini: Docker Desktop
  • Jika sudah, buka docker dekstop (pastikan engine running)
  • Buka CLI (contoh: command prompt)
  • Ketikan perintah berikut untuk mendownload/pull image postgres di docker:
    docker pull postgres:15
  • Kemudian jalankan container postgres: 
    docker run --name belajar-sql-postgresql -e POSTGRES_PASSWORD=secret -d postgres:15
    Penjelasan: 
    • docker run : membuat dan menjalankan container baru dari sebuah image.
    • --name belajar-sql-postgresql : memberikan nama container, yaitu belajar-sql-postgresql.
    • -e POSTGRES_PASSWORD=secret : menetapkan environment variable untuk password user postgres.
    • -d : menjalankan container di background (detached mode).
    • postgres:15 : menggunakan image PostgreSQL versi 15 dari Docker Hub.

  • Masuk ke dalam container postgres:
    docker exec -it belajar-sql-postgresql psql -U postgres
    Penjelasan:
    • docker exec : menjalankan perintah di dalam container yang sedang berjalan.
    • -it : membuka terminal interaktif agar bisa mengetik perintah.
    • belajar-sql-postgresql : nama container tujuan.
    • psql : menjalankan program psql (PostgreSQL interactive terminal).
    • -U postgres : login menggunakan user postgres.

  • Setelah masuk ke dalam postgreSQL command line, sebagai permulaan ketikan perintah help; untuk mengetahui beberapa perintah yang dapat digunakan.
10. Cara Menjalankan Container yang sudah ada
  • Melihat daftar container (termasuk yang mati): 
    docker ps -a
  • Menghidupkan kembali container: 
    docker start [nama_atau_id_container]
  • Masuk ke dalam terminal interaktif container:
    docker exec -it [nama_container] psql -U [nama_pengguna]
11. Menjalankan Perintah SQL Melalui File

Untuk menyalin file SQL dari lokasi direktori yang ada di komputer ke dalam container, caranya adalah dengan seperti berikut ini.
  • Masuk ke dalam jendela CLI yang baru, dan arahkan direktori kerja CLI ke direktori yang sama dengan tempat menyimpan data, misal datanya customers.sql.
  • Kemudian jalankan perintah berikut, untuk menyalin data customers.sql ke dalam container:
    docker cp customers.sql belajar-sql-postgresql:/tmp/customers.sql
    Penjelasan:
    • docker : menjalankan Docker CLI.
    • cp : menyalin (copy) file atau folder.
    • customer.sql : file yang akan disalin dari komputer.
    • belajar-sql-postgresql : nama container tujuan.
    • /tmp/customers.sql : lokasi penyimpanan file di dalam container.

  • Kemudian kembali ke dalam container postgres, dan eksekusi perintah berikut ini:
    \i /tmp/customers.sql
    Penjelasan:
    • Perintah \i adalah perintah psql yang digunakan untuk mengeksekusi perintah SQL yang ada di dalam file SQL.

  • Maka data akan ditambahkan, silahkan periksa dengan menggunakan perintah SELECT.
12. Perintah SQL Membuat Tabel

Sintaks Dasar:
CREATE TABLE nama_tabel (
    nama_kolom1 TIPE_DATA,
    nama_kolom2 TIPE_DATA,
    nama_kolom3 TIPE_DATA
);
Keterangan:
  • CREATE TABLE : perintah SQL untuk membuat tabel baru.
  • nama_tabel : nama tabel yang ingin dibuat.
  • nama_kolom : nama setiap kolom.
  • TIPE_DATA : jenis data yang akan disimpan pada kolom tersebut.
13. SQL: Menambahkan Data ke Tabel

Sintaks Dasar:
INSERT INTO nama_tabel (kolom1, kolom2, kolom3)
VALUES (nilai1, nilai2, nilai3);
Keterangan:
  • INSERT INTO : perintah SQL untuk menambahkan data ke tabel.
  • nama_tabel : nama tabel tujuan.
  • (kolom1, kolom2, ...) : kolom yang akan diisi.
  • VALUES ( ... ) : nilai yang dimasukkan ke setiap kolom.
14. SQL: Perintah ORDER BY
  • Perintah ORDER BY digunakan untuk mengurutkan baris di dalam tabel berdasarkan kolom tertentu, terdapat dua pilihan dalam order by yaitu:
    • DESC : Mengurutkan dari yang besar ke kecil
    • ASC : Mengurutkan dari yang kecil ke besar (default)

  • Contohnya jika kita ingin menampilkan tabel customers berurutan berdasarkan kolom city dari abjad tertinggi (Z) ke rendah (A), gunakan perintah berikut:
    SELECT * FROM customers ORDER BY city DESC;
    Keterangan:
    • SELECT : memilih data yang ingin ditampilkan.
    • * : menampilkan semua kolom.
    • FROM customers : mengambil data dari tabel customers.
    • ORDER BY city : mengurutkan data berdasarkan kolom city.
    • DESC : mengurutkan dari terbesar ke terkecil (Z → A untuk teks).
15. SQL: Perintah DISTINCT
  • Perintah DISTINCT digunakan untuk menampilkan hanya nilai unik dari sebuah kolom, menghilangkan duplikasi data. 
  • Contoh jika kita ingin mengetahui nilai unik dari city, maka dapat menggunakan perintah berikut:
    SELECT DISTINCT city FROM customers ORDER BY city;
    Hasilnya akan menampilkan data di city tanpa data duplikat.
16. SQL: Perintah WHERE
  • WHERE clause dalam PostgreSQL digunakan untuk memfilter data dalam pernyataan SQL seperti SELECT, UPDATE, dan DELETE. 
  • Dengan kata lain, WHERE menentukan kondisi yang harus dipenuhi oleh baris yang akan diambil, diperbarui, atau dihapus.
WHERE + Operator Perbandingan

WHERE dapat digunakan dengan operator perbandingan seperti:

Misalnya pada tabel products kita ingin mengetahui product yang memiliki harga dibawah 100 dollar, maka dapat menggunakan perintah berikut:
SELECT * FROM products WHERE price<100;
WHERE + Operator Logika

Dapat juga dikombinasikan antara operator perbandingan dan operator logika, seperti berikut:

Contohnya kita ingin mencari product yang memiliki harga antara 100 dan 500 dollar, maka:
SELECT * FROM products WHERE price>100 AND price<500;
atau kita ingin mencari product yang memiliki harga dibawah 50 atau diatas 500, maka:
SELECT * FROM products WHERE price<50 OR price>500;
Tabel Operator Logika
WHERE + BETWEEN

Dapat juga dikombinasikan dengan BETWEEN untuk mencari data dalam rentang tertentu.

Contohnya:
SELECT * FROM products WHERE price BETWEEN 100 AND 500;
WHERE + LIKE atau ILIKE

Dapat juga dikombinasikan dengan LIKE atau ILIKE untuk mencari pola tertentu, seperti berikut.


Misalnya kita ingin mencari customer yang memiliki email dengan akhiran ‘@ibm.com’, maka:
SELECT * FROM customers WHERE email LIKE '%@bing.com';
Atau kita ingin mencari customer yang berasar dari Jakarta dengan menggunakan ILIKE:
SELECT * FROM customers WHERE city ILIKE 'jakarta';

WHERE + IN atau NOT IN

Dapat juga dikombinasikan dengan IN untuk mendapatkan data yang termasuk dalam daftar tertentu.

Contoh misalnya kita ingin mencari data customer yang beralamat di kota Jakarta, Bandung dan Surabaya, maka kita dapat menggunakan cara berikut:
SELECT * FROM customers WHERE city IN ('Jakarta', 'Bandung', 'Surabaya');
17. SQL: Perintah GROUP BY

GROUP BY adalah klausa yang digunakan untuk mengelompokkan baris berdasarkan nilai dalam satu atau lebih kolom, lalu menerapkan fungsi agregat seperti COUNT(), SUM(), AVG(), MAX(), atau MIN() pada setiap kelompok. 

Sintaks Dasar:
SELECT kolom1, kolom2, AGGREGATE_FUNCTION(kolom3)
FROM nama_tabel
GROUP BY kolom1, kolom2;
Contoh dari tabel yang kita miliki, misalkan kita ingin menghitung jumlah customer berdasarkan kotanya, kita dapat menggunakan GROUP BY seperti berikut:
SELECT city, COUNT(city) FROM customers GROUP BY city;
Penjelasan:
  • SELECT city : menampilkan nama kota.
  • COUNT(city) : menghitung jumlah data pada setiap kota.
  • FROM customers : mengambil data dari tabel customers.
  • GROUP BY city : mengelompokkan data berdasarkan kota.
Kita dapat kombinasikan dengan 
ORDER BY
 tetapi yang wajib diperhatikan adalah, ORDER BY diletakkan setelah 
GROUP BY
  .
SELECT city, COUNT(city) FROM customers GROUP BY city ORDER BY COUNT(city);
Keterangan:
  • ORDER BY diletakkan setelah GROUP BY.
Perhatikan fungsi
COUNT
diatas merupakan salah satu dari yang namanya Aggregate Function. Beberapa aggregate function yang sering digunakan:


Terdapat aggregate function lainnya yang dapat kita gunakan, lengkapnya dapat dilihat disini.

18. SQL: Perintah 
GROUP BY
+
HAVING

Jika ingin memfilter hasil setelah proses pengelompokan (hasil GROUP BY), gunakan
HAVING
(mirip dengan
WHERE
, tetapi untuk hasil agregasi).

Misalkan kita ingin mengetahui kota yang memiliki jumlah customer lebih dari 5 orang, kita bisa gunakan
HAVING
seperti ini:
SELECT city, COUNT(city) FROM customers GROUP BY city HAVING COUNT(city)>5;
Keterangan:
  • SELECT city : menampilkan nama kota.
  • COUNT(city) : menghitung jumlah data pada setiap kota.
  • FROM customers : mengambil data dari tabel customers.
  • GROUP BY city : mengelompokkan data berdasarkan kota.
  • HAVING COUNT(city) > 5 : menampilkan hanya kelompok yang jumlahnya lebih dari 5.
Jika menggunakan
WHERE
:
SELECT city, COUNT(city) FROM customers WHERE city LIKE 'J%' GROUP BY city HAVING COUNT(city)>2;
Atau kita ingin mengetahui  rata-rata harga product dari brand yang memiliki huruf depan A:
SELECT brand, ROUND(AVG(price)) FROM products GROUP BY brand HAVING brand LIKE 'A%';
Note: Fungsi
ROUND
diatas berfungsi untuk membulatkan, bukan aggregate function.

19. SQL: Perintah LIMIT

LIMIT digunakan dalam perintah SQL untuk membatasi jumlah baris yang dikembalikan oleh sebuah query. Ini berguna saat Anda hanya ingin melihat sejumlah data tertentu dari hasil query, misalnya menampilkan 10 baris pertama dari sebuah tabel:
SELECT * FROM customers LIMIT 10;
Jika dikombinasikan dengan clausal lainnya maka
LIMIT
akan berada di akhir perintah, contoh seperti ini:
SELECT city, COUNT(city) FROM customers WHERE city IN ('Jakarta', 'Cimahi', 'Bandung') GROUP BY city HAVING COUNT(city)>4 ORDER BY COUNT(city) DESC LIMIT 3;
Secara umum untuk urutan perintah, contohnya adalah yang diawali dengan
SELECT
biasanya seperti berikut ini:
  1. SELECT → Memilih kolom mana yang akan ditampilkan dalam hasil akhir.
  2. FROM → Menentukan tabel sumber data.
  3. JOIN → Jika ada penggabungan tabel, dilakukan di tahap ini.
  4. WHERE → Memfilter data sebelum dilakukan pengelompokan  (
    GROUP BY
    ).
  5. GROUP BY → Mengelompokkan data berdasarkan satu atau lebih kolom.
  6. HAVING → Memfilter hasil setelah
    GROUP BY
    , biasanya berdasarkan fungsi agregat.
  7. ORDER BY → Mengurutkan hasil query berdasarkan kolom tertentu.
  8. LIMIT → Membatasi jumlah baris yang ditampilkan.
Contohnya:
SELECT brand, ROUND(AVG(price)) FROM products WHERE brand LIKE 'A%' GROUP BY brand HAVING AVG(price)>10 ORDER BY brand LIMIT 3;
Tidak hanya
SELECT
, kedepannya kita juga akan belajar mengenai
UPDATE
dan
DELETE
, keduanya memiliki hirarki yang sama.

20. Operator dan Fungsi Matematika
 
PostgreSQL memiliki banyak operator dan fungsi yang bisa digunakan dalam berbagai skenario, seperti manipulasi data, agregasi, administrasi database dan lain sebagainya. Untuk mengetahui berbagai macam fungsi pada postgreSQL dapat dilihat pada dokumentasi berikut ini.

Fungsi dan Operator Matematika

Operator matematika umum yang kita ketahui seperti penjumlahan, pengurangan, pembagian, perkalian, dan sebagainya dapat kita gunakan di dalam perintah SQL.


Contoh: misalkan kita ingin membuat kolom baru yang berisi, nilai diskon 10% dari harga product, maka dapat menggunakan perintah seperti berikut:
SELECT price*0.1 FROM products;
Fungsi matematika dibawah ini digunakan untuk operasi numerik:
  • ABS(value)
    → Mengambil nilai absolut
    SELECT ABS(-10); -- Output: 1
  • ROUND(value, decimal_places)
    → Membulatkan angka
    SELECT ROUND(3.14159, 2); -- Output: 3.14
  • CEIL(value)
    → Membulatkan ke atas
    SELECT CEIL(3.1); -- Output: 4
  • FLOOR(value)
    → Membulatkan ke bawah
    SELECT FLOOR(3.9); -- Output: 3
  • POWER(base, exponent)
    → Pangkat
    SELECT POWER(2, 3); -- Output: 8
  • SQRT(value)
    → Akar kuadrat
    SELECT SQRT(16); -- Output: 4
Contoh penggunaan fungsi ini pada tabel yang kita miliki, misalnya ingin membuatlatkan ke atas setiap harga product yang ada di tabel
products
:
SELECT *,CEIL(price) FROM products;

Fungsi String

Fungsi ini digunakan untuk manipulasi string.
  • LENGTH(string)
    → Menghitung panjang string
    SELECT LENGTH('PostgreSQL');
  • UPPER(string)
    → Mengubah string menjadi huruf besar
    SELECT UPPER('postgresql');
  • LOWER(string)
    → Mengubah string menjadi huruf kecil
    SELECT LOWER('POSTGRESQL');
  • CONCAT(string1, string2)
    → Menggabungkan string
    SELECT CONCAT('Hello', ' ', 'World');
  • SUBSTRING(string FROM start FOR length)
    → Mengambil bagian dari string
    SELECT SUBSTRING('PostgreSQL' FROM 1 FOR 4); -- Output: 'Post'
  • TRIM(string)
    → Menghapus spasi di awal dan akhir string
    SELECT TRIM('  PostgreSQL  ');
Contoh penggunaan pada tabel
customers
kita ingin menghitung panjang nama dari setiap customer, maka:
SELECT name, LENGTH(name) FROM customers;

Fungsi Date & Time

Fungsi ini digunakan untuk menangani data waktu.
  • CURRENT_DATE
    → Mengambil tanggal saat ini
    SELECT CURRENT_DATE;
  • CURRENT_TIME
    → Mengambil waktu saat ini
    SELECT CURRENT_TIME;
  • CURRENT_TIMESTAMP
    → Mengambil tanggal dan waktu saat ini
    SELECT CURRENT_TIMESTAMP;
  • AGE(timestamp)
    → Menghitung selisih tanggal
    SELECT AGE('2025-01-01', '2000-01-01');
  • DATE_PART(field, timestamp)
    → Mengambil bagian dari tanggal
    SELECT DATE_PART('year', CURRENT_DATE); -- Output: 2025
  • NOW()
    → Mengambil waktu saat ini dengan timezone
    SELECT NOW();
Contoh penggunaan pada data
customers
kita ingin mengetahui umur dari setiap customer, kita bisa gunakan data berikut.
SELECT name, AGE(NOW(), date_of_birth) FROM customers;

Fungsi Manipulasi Data
  • COALESCE(value1, value2, ...)
    → Mengembalikan nilai pertama yang tidak NULL
    SELECT COALESCE(NULL, 'Default Value', 'Another Value'); -- Output: 'Default Value'
    Contoh pengguaan pada tabel customers:
    SELECT email, COALESCE(email, 'NO EMAIL') FROM customers;
  • NULLIF(value1, value2)
    → Mengembalikan NULL jika kedua nilai sama
    SELECT NULLIF(10, 10); -- Output: NULL
SELECT NULLIF(10, 5);  -- Output: 10
Fungsi Administratif

Digunakan untuk pengelolaan database.
  • VERSION()
    → Melihat versi PostgreSQL
    SELECT VERSION();
  • CURRENT_USER
    → Melihat user yang sedang login
    SELECT CURRENT_USER;
  • pg_size_pretty(pg_database_size('database_name'))
    → Melihat ukuran database
    SELECT pg_size_pretty(pg_database_size('electronic_store'));
  • Dan masih banyak lagi fungsi lainnya.
21. SQL: Perintah ALIAS

Alias adalah nama sementara yang diberikan pada kolom atau tabel untuk mempermudah pembacaan dan penulisan query. Alias digunakan dalam perintah SQL untuk memberikan nama lain yang lebih singkat atau lebih deskriptif.

Sintak penggunaan alias adalah sebagai berikut.
SELECT kolom AS alias_kolom FROM tabel;
Contohnya kita akan membuat kolom baru dengan nama discount dimana di dalamnya berisi nilai diskon 10% dari harga product di dalam tabel
products
, maka:
SELECT *,CEIL(price) AS discount FROM products;
Penulisan
AS
bersifat opsional, kita bisa saja menuliskan tanpa
AS
seperti berikut.
SELECT *,CEIL(price) discount FROM products;
Dan hasilnya tetaplah sama.

22. Memasukkan Data Dummy

Silahkan jalankan perintah berikut, digunakan untuk pembelajaran mengenai
DELETE
dan
UPDATE
.
INSERT INTO customers (name, gender, date_of_birth, email, phone, city) VALUES
('Budi Santoso', 'Male', '1990-05-12', 'budi@example.com', '081234567890', 'Jakarta'),
('Angel', 'Female', '1995-02-11', 'angel@example.com', '081234567760', 'London'),
('Dirgam', 'Male', '2000-05-09', 'dirgam@example.com', '081234542760', 'London');
23. SQL: Perintah DELETE (⚠️)

Perintah
DELETE
digunakan untuk menghapus data dari tabel. Perintah ini mirip penggunaanya seperti
SELECT
, oleh karena itu disarankan untuk menjalankan perintah
SELECT
terlebih dahulu sebelum menjalankan
DELETE
untuk memastikan kita menghapus data yang tepat.

Sintak umum untuk perintah
DELETE
adalah sebagai berikut.
DELETE FROM nama_tabel WHERE kondisi;
Perintah
DELETE
biasa menggunakan kondisi PRIMARY KEY, agar data yang terhapus adalah data spesifik, contoh misalnya kita akan menghapus data dengan
customer_id=251
, maka:
DELETE FROM customers WHERE customer_id=251;
Atau misalnya kita ingin menghapus data customer dari kota tertentu:
DELETE FROM customers WHERE city= 'London';
Perlu diperhatikan hati-hati dalam menjalankan perintah
DELETE
karena data yang terhapus ada kemungkinan tidak dapat kembalikan.

24. SQL: Perintah UPDATE (⚠️)

Perintah
UPDATE
digunakan untuk memperbarui satu atau lebih baris dalam tabel. Berikut adalah sintaks dasar perintah
UPDATE
:
UPDATE nama_tabel
SET kolom1 = nilai1,
    kolom2 = nilai2,
    ...
WHERE kondisi;
Contoh misalnya kita akan mengupdate data products pada
product_id=10
SELECT * FROM products WHERE product_id = 10;
Dimana jika diperhatikan terdapat kesalahan brand dan harga, kita akan mengubahnya menjadi data yang benar:
UPDATE products
SET brand = 'Lenovo',
    price = 49.99
WHERE product_id = 10;
Jika dicek kembali, maka data pada
product_id = 10
harusnya sudah berubah.

25. SQL: Perintah ALTER

Perintah
ALTER
dalam PostgreSQL digunakan untuk mengubah struktur dari objek yang ada dalam database, seperti database, tabel, constraint dan lain-lain. Dengan
ALTER
, kita bisa menambah, mengubah, atau menghapus atribut dari suatu objek tanpa harus menghapusnya terlebih dahulu.

ALTER DATABASE

Digunakan untuk mengubah struktur database:
  • Mengubah nama database
    ALTER DATABASE nama_database RENAME TO nama_baru;
    Contoh kita akan mengubah nama database yang kita miliki dari electronic_store menjadi electronic_store_db. Untuk dapat mengubah sebuah database, kita harus terlebih dahulu berpindah database lainnya (bukan yang akan diubah):
    \c postgres
    Barulah kemudian kita bisa mengubah nama database target:
    ALTER DATABASE electronic_store RENAME TO electronic_store_db;
ALTER TABLE

Digunakan untuk mengubah struktur dari struktur tabel:
  • Menambahkan kolom
    ALTER TABLE nama_tabel ADD COLUMN nama_kolom tipe_data;
  • Mengubah tipe data kolom
    ALTER TABLE nama_tabel ALTER COLUMN nama_kolom TYPE tipe_data;
  • Mengubah nama kolom
    ALTER TABLE nama_tabel RENAME COLUMN nama_lama TO nama_baru;
  • Menghapus kolom
    ALTER TABLE nama_tabel DROP COLUMN nama_kolom;
  • Mengubah nama tabel
    ALTER TABLE nama_tabel RENAME TO nama_baru;
Contoh pada tabel
products
kita akan menambahkan kolom
others
, maka dapat menggunakan perintah berikut.
ALTER TABLE customers
ADD COLUMN others TEXT;
Jika diperhatikan dengan perintah
SELECT
, maka akan terlihat kolom baru yaitu
others
. Kita bisa mengubah tipe data, mengganti nama kolom dan menghapus kolom tersebut.
ALTER TABLE customers
ALTER COLUMN others
TYPE VARCHAR(100);
ALTER TABLE customers
RENAME COLUMN others TO keterangan;
ALTER TABLE customers
DROP COLUMN keterangan;

ALTER CONSTRAINT

Dapat digunakan untuk mengubah contraint yang ada di dalam tabel.
  • Menambahkan constraint
    ALTER TABLE nama_tabel ADD CONSTRAINT nama_konstraint TIPE_KONSTRAINT (kolom);
  • Menghapus constraint
    ALTER TABLE nama_tabel DROP CONSTRAINT nama_konstraint;
Contoh kita akan memasukkan constraint untuk harga product di tabel
product
, dimana constraint ini memastikan harga setiap product lebih dari nol (>0):
ALTER TABLE products ADD CONSTRAINT price_check CHECK(price>0);

Jika kita periksa dengan perintah \d products maka akan terdapat constraint baru.

26. Constraint FOREIGN KEY

Foreign Key (FK) adalah sebuah constraint dalam PostgreSQL yang digunakan untuk membentuk hubungan antara dua tabel. FK memastikan bahwa nilai dalam satu kolom (atau beberapa kolom) di tabel anak (child table) memiliki referensi yang valid ke nilai di kolom tabel induk (parent table).

Konsep dasar FK:
  • FK digunakan untuk menjaga integritas referensial data.
  • Nilai FK di tabel anak harus ada di tabel induk atau harus bernilai NULL .
  • PostgreSQL akan mencegah penghapusan atau pembaruan data di tabel induk jika masih ada referensi di tabel anak.
Contoh sebelumnya kita membuat tabel
customers
dan
products
dengan perintah berikut.
  • Tabel 
    customers
     
    create table customers (
	customer_id SERIAL PRIMARY KEY,
	name VARCHAR(50) NOT NULL,
	date_of_birth DATE NOT NULL,
	gender VARCHAR(50) NOT NULL,
	city VARCHAR(15) NOT NULL,
	email VARCHAR(50) UNIQUE,
	phone VARCHAR(50)
);
  • Tabel 
    products
     
    CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    brand VARCHAR(50),
    price DECIMAL(10,2) NOT NULL,
    in_stock BOOLEAN NOT NULL DEFAULT TRUE
);
Selanjutnya kita akan membuat tabel baru yaitu
transactions
yang berisi informasi transaksi yang dilakukan customers dalam membeli product. Disini kita akan menggunakan FK:
CREATE TABLE transactions (
    transaction_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    product_id INT REFERENCES products(product_id),
    quantity INT NOT NULL CHECK (quantity > 0),
    date_transaction DATE NOT NULL DEFAULT CURRENT_DATE
);

Jika kita periksa deksripsi tabel transaction (\d transactions) maka akan muncul tampilan berikut ini:


Perhatikan terdapat 2 foreign key constraints sesuai dengan saat kita membuat tabelnya.

27. SQL: Perintah Join

JOIN digunakan untuk menggabungkan data dari dua atau lebih tabel berdasarkan kondisi tertentu. Ini memungkinkan kita untuk mengambil data yang berkaitan dari berbagai tabel dalam satu query.
SELECT tabel1.kolom, tabel2.kolom, ...
FROM tabel1
JOIN tabel2 ON tabel1.kolom = tabel2.kolom;

Atau jika menggunakan alias:

SELECT t1.kolom, t2.kolom, ...
FROM tabel1 t1
JOIN tabel2 t2 ON t1.kolom = t2.kolom;

Keterangan:

  • t1.kolom = t2.kolom : biasanya misalkan t1.kolom adalah kolom PRIMARY KEY di tabel t1 dan t2.kolom adalah kolom FOREIGN KEY dari t1.kolom di tabel t2.

Contoh, perhatikan gambar berikut:

Kolom supervisor_id pada tabel emp_dets dan kolom supervisor_id pada tabel supervisor_dets bisa dijadikan dasar join:

  1. Membuat tabel
    CREATE TABLE supervisor_dets (
    supervisor_id INT PRIMARY KEY,
    supervisor_name VARCHAR(100)
);

CREATE TABLE emp_dets (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    supervisor_id INT
);
  2. Mengisi data
    INSERT INTO supervisor_dets (supervisor_id, supervisor_name) VALUES
(302, 'Harvey Specter'),
(315, 'Louis Litt'),
(333, 'Jessica Pearson');

INSERT INTO emp_dets (emp_id, emp_name, supervisor_id) VALUES
(101, 'Milk Ross', 302),
(102, 'Katrina Bennet', 333),
(103, 'Rachel Zane', 302);
  • Join: 
    SELECT *
FROM emp_dets 
JOIN supervisor_dets
ON emp_dets.supervisor_id = supervisor_dets.supervisor_id;
28. Macam-macam JOIN SQL

Didalam SQL terdapat beberapa jenis join yang bisa kita gunakan:

Inner Join

Mengembalikan hanya baris yang memiliki kecocokan di kedua tabel.

Contoh kita akan menggabungkan tabel transaction dan customers dengan menggunakan inner join:

SELECT
	transactions.transaction_id,
	transactions.customer_id,
	customers.name,
	transactions.quantity,
	transactions.date_transaction
FROM transactions
JOIN customers
ON transactions.customer_id = customers.customer_id;

Kita juga bisa menggunakan alias seperti berikut ini.

SELECT
	t.transaction_id,
	t.customer_id,
	c.name,
	t.quantity,
	t.date_transaction
FROM transactions t
JOIN customers c
ON t.customer_id = c.customer_id;

Left Join / Right Join 

Left join mengembalikan semua baris dari tabel kiri dan hanya yang cocok dari tabel kanan, sedangkan untuk right join adalah sebaliknya.

Contohnya adalah seperti berikut:

  • Left join
    SELECT
	t.transaction_id,
	t.customer_id,
	c.name,
	t.quantity,
	t.date_transaction
FROM transactions t
LEFT JOIN customers c
ON t.customer_id = c.customer_id;
            Keterangan: 
    • Kolom left itu kolom di tabel setelah FROM, yaitu kolom custimer_id di tabel transactions

  • Right join
    SELECT
	t.transaction_id,
	t.customer_id,
	c.name,
	t.quantity,
	t.date_transaction
FROM transactions t
RIGHT JOIN customers c
ON t.customer_id = c.customer_id;

Full Join

Mengembalikan semua baris dari kedua tabel, baik yang cocok maupun tidak.

Contohnya adalah sebagai berikut:

SELECT
	t.transaction_id,
	t.customer_id,
	c.name,
	t.quantity,
	t.date_transaction
FROM transactions t
FULL JOIN customers c
ON t.customer_id = c.customer_id;

Dapat kita simpulkan mengenai join adalah:

  • Gunakan INNER JOIN untuk mendapatkan data yang cocok di kedua tabel.
  • Gunakan LEFT JOIN jika ingin semua data dari tabel kiri meskipun tidak ada kecocokan di tabel kanan.
  • Gunakan RIGHT JOIN jika ingin semua data dari tabel kanan meskipun tidak ada kecocokan di tabel kiri.
  • Gunakan FULL JOIN untuk mendapatkan semua data dari kedua tabel.
29. SQL: Perintah WITH

Perintah WITH dalam SQL digunakan untuk membuat Common Table Expression (CTE), yaitu tabel sementara yang hanya berlaku selama eksekusi satu query. 

WITH tidak membuat tabel permanen di database. Ia hanya membuat tabel sementara (CTE) yang berlaku selama satu eksekusi query, sehingga query menjadi lebih terstruktur, mudah dibaca, dan lebih mudah dikelola.

Sintaks Dasar:

WITH nama_cte AS (
    SELECT ...
)
SELECT *
FROM nama_cte;

Keterangan:

  • nama_cte adalah nama tabel sementara.
  • Isi di dalam AS (...) adalah query yang menghasilkan data.
  • Setelah itu, CTE dapat digunakan seperti tabel biasa.
Contoh: Misalnya kita memiliki tabel employee.

Tanpa WITH:

SELECT *
FROM (
    SELECT *
    FROM employee
    WHERE department = 'IT'
) AS it_employee
WHERE salary > 8500;

Dengan WITH:

WITH it_employee AS (
    SELECT *
    FROM employee
    WHERE department = 'IT'
)

SELECT *
FROM it_employee
WHERE salary > 8500;

Hasilnya:


Versi kedua lebih mudah dipahami karena prosesnya dipisahkan menjadi beberapa langkah.

30. SQL: Perintah CASE

CASE adalah perintah SQL yang digunakan untuk membuat logika percabangan (conditional logic).

Sintaks
Ada 2 bentuk utama CASE.

1) Simple CASE
Digunakan ketika membandingkan satu kolom dengan beberapa nilai.

CASE kolom
    WHEN nilai1 THEN hasil1
    WHEN nilai2 THEN hasil2
    ELSE hasil_lain
END

Contoh: CASE yang membandingkan satu kolom (country) dengan beberapa nilai yang sudah diketahui.

SELECT
    customer_id,
    country,
    CASE country
        WHEN 'USA' THEN 'Amerika'
        WHEN 'Canada' THEN 'Kanada'
        ELSE 'Negara Lain'
    END AS negara
FROM customers;

Penjelasan:

  • SELECT customer_id, country : menampilkan kolom customer_id dan kolom country dari tabel customers.
  • CASE country : gunakan nilai pada kolom country sebagai dasar perbandingan. Di sini SQL akan membaca nilai country pada setiap baris, kemudian membandingkannya dengan setiap WHEN. SQL akan mulai mengecek kondisi.
  • WHEN 'USA' THEN 'Amerika' : jika isi kolom country adalah USA maka hasil CASE adalah Amerika.
  • WHEN 'Canada' THEN 'Kanada' : jika nilai country adalah Canada maka hasilnya Kanada.
  • ELSE 'Negara Lain' : jika nilai country bukan USA atau Canada maka hasilnya Negara Lain.
  • END AS negara : mengakhiri blok CASE dan memberi nama kolom hasil sebagai negara. Kolom negara tidak disimpan di database. Kolom ini hanya muncul pada hasil query.

    • Misalkan datanya:

    Hasilnya:


    2) Searched CASE (Paling Sering Digunakan)
    Digunakan jika kondisinya berupa perbandingan seperti >, <, BETWEEN, atau menggabungkan beberapa kolom.
    CASE
    WHEN kondisi1 THEN hasil1
    WHEN kondisi2 THEN hasil2
    ELSE hasil_lain
END

    Contoh 1: Kategori Harga Produk. 

    SELECT
    product_name,
    price,
    CASE
        WHEN price >= 1000000 THEN 'Mahal'
        WHEN price >= 500000 THEN 'Sedang'
        ELSE 'Murah'
    END AS kategori_harga
FROM products;

    Penjelasan:

    • CASE : memulai logika percabangan. Berbeda dengan Simple CASE, pada contoh ini tidak ada nama kolom setelah CASE, karena setiap WHEN berisi kondisi.
    • WHEN price >= 1000000 THEN 'Mahal' : jika harga produk lebih besar atau sama dengan Rp1.000.000, maka hasilnya Mahal.
    • WHEN price >= 500000 THEN 'Sedang' : jika harga produk lebih besar atau sama dengan Rp500.000, maka hasilnya Sedang. SQL hanya memeriksa kondisi ini jika kondisi pertama tidak terpenuhi.
    • ELSE 'Murah' : jika semua kondisi sebelumnya tidak terpenuhi, maka hasilnya Murah.
    • END AS kategori_harga : mengakhiri blok CASE dan memberi nama kolom hasil kategori_harga. Kolom ini hanya ada pada hasil query, bukan ditambahkan ke tabel.

    Jika membandingkan Dua Kolom. 

    SELECT
    product_name,
    price,
    stock,
    CASE
        WHEN price >= 1000000 AND stock > 0 THEN 'Premium Tersedia'
        WHEN price >= 1000000 AND stock = 0 THEN 'Premium Habis'
        ELSE 'Produk Biasa'
    END AS status_produk
FROM products;

    Pada contoh ini SQL mengecek dua kolom sekaligus. Keduanya harus bernilai TRUE karena menggunakan AND.

    Contoh 2: CASE di ORDER BY

    Misalnya ingin produk yang stok habis muncul paling atas.

    SELECT *
FROM products
ORDER BY
CASE
    WHEN stock = 0 THEN 1
    ELSE 2
END;

    Produk dengan stock = 0 akan diurutkan lebih dahulu.

    Contoh 3: CASE + COUNT()

    Misalnya ingin menghitung jumlah pelanggan pria dan wanita.

    SELECT
    COUNT(
        CASE
            WHEN gender = 'M' THEN 1
        END
    ) AS pria,
    COUNT(
        CASE
            WHEN gender = 'F' THEN 1
        END
    ) AS wanita
FROM customers;

    Contoh 4: CASE + SUM().

    Misalnya menghitung total penjualan berdasarkan kategori. 

    SELECT
    SUM(
        CASE
            WHEN price >= 500 THEN price
            ELSE 0
        END
    ) AS total_produk_mahal
FROM products;

    Contoh 5: Menggunakan Fungsi.

    Query ini bertujuan untuk mengelompokkan nama pelanggan berdasarkan jumlah karakter pada nama. Di sini yang diperiksa bukan kolom secara langsung, tetapi hasil fungsi LENGTH().

    SELECT
    first_name,
    CASE
        WHEN LENGTH(first_name) > 5
        THEN 'Nama Panjang'
        ELSE 'Nama Pendek'
    END
FROM customers;

    Contoh Menggunakan Tiga Fungsi:

    SELECT
    first_name,
    CASE
        WHEN LENGTH(TRIM(UPPER(first_name))) > 5
        THEN 'Nama Panjang'
        ELSE 'Nama Pendek'
    END
FROM customers;

    Pada kondisi tersebut terdapat tiga fungsi yang saling bertingkat:

    UPPER(first_name)
        ↓
TRIM(...)
        ↓
LENGTH(...)

    Contoh Menggunakan Banyak Fungsi Sekaligus:

    SELECT
    customer_name,
    CASE
        WHEN LENGTH(TRIM(customer_name)) > 5
             AND UPPER(country) = 'INDONESIA'
             AND AGE(birth_date) >= INTERVAL '18 years'
        THEN 'Dewasa Indonesia'
        ELSE 'Lainnya'
    END
FROM customers;

    Contoh 6: Menggunakan Tanggal.

    Di sini CASE membandingkan kolom order_date dengan tanggal saat ini. 

    SELECT
    order_id,
    order_date,
    CASE
        WHEN order_date >= CURRENT_DATE - INTERVAL '30 days'
        THEN 'Order Baru'
        ELSE 'Order Lama'
    END
FROM orders;

    Contoh 4: CASE + SUM().

    Misalnya menghitung total penjualan berdasarkan kategori. 

    SELECT
    SUM(
        CASE
            WHEN price >= 500 THEN price
            ELSE 0
        END
    ) AS total_produk_mahal
FROM products;
    30. SQL: Set Operation

    Set Operation adalah sekumpulan perintah SQL yang digunakan untuk menggabungkan atau membandingkan hasil dari dua atau lebih query. Konsepnya berasal dari teori himpunan (Set Theory) dalam matematika. 

    Syarat Menggunakan Set Operation:
    Agar UNION, INTERSECT, dan EXCEPT dapat digunakan, kedua query harus memenuhi syarat berikut:
    • Jumlah kolom yang di select harus sama
    • Urutan kolom yang di select harus sama
    • Tipe data kolom yang di select harus kompatibel. Misalnya: INTEGER dengan INTEGER atau VARCHAR dengan TEXT
    Set Operation bekerja pada hasil query, bukan langsung pada tabel.

    1) UNION (Menggabungkan)

    Menggabungkan hasil dua query menjadi satu dan menghapus data yang duplikat.

    Contoh:  

    SELECT customer_id
FROM customers

EXCEPT

SELECT customer_id
FROM transactions;

    Penjelasan:

    • Misalkan hasil query pertama: Jakarta, Bandung, Surabaya
    • Misalkan hasil query kedua: Bandung, Medan, Surabaya
    • Maka hasil unionnya: Jakarta, Bandung, Surabaya, Medan
    2) UNION ALL

    Kalau ingin semua data ditampilkan, termasuk yang sama (duplikat), gunakan:

    SELECT city
FROM customers

UNION ALL

SELECT city
FROM suppliers;

    Penjelasan:

    • Misalkan hasil query pertama: Jakarta, Bandung, Surabaya
    • Misalkan hasil query kedua: Bandung, Medan, Surabaya
    • Maka hasil union all nya: Jakarta, Bandung, Surabaya, Bandung, Medan, Surabaya

    3) INTERSECT (Irisan)

    Mengambil data yang muncul di kedua hasil query.

    Contoh:
    SELECT city
FROM customers

INTERSECT

SELECT city
FROM suppliers;

    Penjelasan:

    • Misalkan hasil query pertama: Jakarta, Bandung, Surabaya
    • Misalkan hasil query kedua: Bandung, Medan, Surabaya
    • Maka hasil intersect nya: Bandung, Surabaya
    4) EXCEPT (Selisih)

    Mengambil data yang ada pada query pertama tetapi tidak ada pada query kedua.

    Contoh:
    SELECT city
FROM customers

EXCEPT

SELECT city
FROM suppliers;

    Penjelasan:

    • Misalkan hasil query pertama: Jakarta, Bandung, Surabaya
    • Misalkan hasil query kedua: Bandung, Medan, Surabaya
    • Maka hasil except nya: Jakarta

    Perbedaan EXCEPT dengan NOT IN

    Misalnya: 

    SELECT customer_id
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM transactions
);

    Hasilnya bisa sama dengan: 

    SELECT customer_id
FROM customers

EXCEPT

SELECT customer_id
FROM transactions;

    Bedanya:

    • NOT IN digunakan untuk memfilter berdasarkan satu kolom.
    • EXCEPT membandingkan hasil dua query secara keseluruhan, termasuk jika terdiri dari beberapa kolom.
    31. SQL: Window Function

    Window Function adalah fungsi SQL yang melakukan perhitungan pada sekumpulan baris (window) yang berhubungan dengan baris saat ini, tanpa menggabungkan (collapse) baris seperti GROUP BY.

    Inti Window Function: berbeda dengan GROUP BY, Window Function tidak mengurangi jumlah baris. Ia menambahkan hasil perhitungan ke setiap baris sehingga kita tetap memiliki data detail sekaligus informasi analitis tambahan. Ini adalah salah satu fitur SQL yang paling sering digunakan dalam dashboard, laporan bisnis, dan analisis data tingkat lanjut.

    Sintaks: 

    fungsi() OVER (
    PARTITION BY ...
    ORDER BY ...
)

    Keterangan:

    • OVER() : menandakan bahwa fungsi tersebut adalah Window Function.
    • PARTITION BY : membagi data menjadi beberapa kelompok (mirip GROUP BY, tetapi tidak mengurangi jumlah baris).
    • ORDER BY : menentukan urutan perhitungan di dalam setiap kelompok.
    Contoh Perbedaan GROUP BY dengan Window function

    Misalkan: Tabel Penjualan

    Contoh: Menggunakan GROUP BY 

    SELECT city,
       SUM(total)
FROM sales
GROUP BY city;

    Hasil:


    Jumlah baris berkurang menjadi satu baris per kota.

    Contoh: Menggunakan Window Function
    SELECT
    customer,
    city,
    total,
    SUM(total) OVER(PARTITION BY city) AS total_kota
FROM sales;

    Hasil:

    Jumlah baris tetap sama, tetapi setiap baris mendapatkan informasi tambahan.

    Window Function yang sering digunakan

    1) Fungsi ROW_NUMBER()

    Memberikan nomor urut unik pada setiap baris.

    Misalkan data:


    Query:
    SELECT
    customer,
    total,
    ROW_NUMBER() OVER(ORDER BY total DESC) AS nomor
FROM sales;
    Penjelasan:
    • ROW_NUMBER() : memberikan nomor urut.
    • OVER(ORDER BY total DESC) : urutkan berdasarkan total dari terbesar ke terkecil.
    Hasil:

    Walaupun Budi dan Citra memiliki nilai yang sama, nomor urutnya tetap berbeda.

    2) Fungsi RANK()

    Memberikan peringkat, tetapi jika ada nilai yang sama maka peringkatnya sama.

    Query:
    SELECT
    customer,
    total,
    RANK() OVER(ORDER BY total DESC) AS ranking
FROM sales;
    Hasil:

    Perhatikan: Nilai yang sama mendapatkan rank yang sama.

    • Budi dan Citra sama-sama peringkat 2.
    • Peringkat 3 dilewati.
    3) Fungsi LAG()

    Mengambil nilai dari baris sebelumnya.

    Misalkan penjualan bulanan:


    Query:
    SELECT
    bulan,
    sales,
    LAG(sales) OVER(ORDER BY bulan) AS bulan_lalu
FROM penjualan;
    Penjelasan:
    • LAG(sales) : ambil nilai sales dari baris sebelumnya.
    Hasil:


    Baris pertama menghasilkan NULL karena tidak ada baris sebelumnya.

    4) Fungsi LEAD()

    Mengambil nilai dari baris berikutnya.

    Query:
    SELECT
    bulan,
    sales,
    LEAD(sales) OVER(ORDER BY bulan) AS bulan_berikutnya
FROM penjualan;
    Hasil:


    Baris terakhir menjadi NULL karena tidak ada data setelahnya.

    5) PARTITION BY

    Misalkan ingin memberi ranking di dalam setiap kota, bukan seluruh data.

    Query:
    SELECT
    customer,
    city,
    total,
    RANK() OVER(
        PARTITION BY city
        ORDER BY total DESC
    ) AS ranking
FROM sales;
    Hasil:

    Karena PARTITION BY city, setiap kota memiliki rankingnya masing-masing.

    32. SQL: Subquery

    Subquery adalah salah satu konsep SQL yang sangat penting, terutama ketika ingin menggunakan hasil dari sebuah query sebagai bagian dari query lain. Sederhananya: Subquery adalah query di dalam query.

    Cara Kerja Subquery

    Misalkan ada dua tabel.

    Tabel customers:
    Tabel transactions:


    Query:
    SELECT *
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM transactions
);
    Langkah 1: SQL menjalankan Subquery terlebih dahulu
    SELECT customer_id
FROM transactions;
    Hasilnya:
    Langkah 2: SQL menjalankan Main Query
    SELECT *
FROM customers
WHERE customer_id IN (2,4);
    Hasilnya:


    Jadi Subquery dijalankan terlebih dahulu, kemudian hasilnya digunakan oleh query utama.

    Jenis-Jenis Subquery

    1) Scalar Subquery
    Menghasilkan satu nilai.
    SELECT MAX(price)
FROM products;
    Contoh: 
    SELECT *
FROM products
WHERE price =
(
    SELECT MAX(price)
    FROM products
);
    Penjelasan Subquery:
    • SQL melihat semua nilai pada kolom price
    • Kemudian mencari nilai terbesar.
    • Misal nilai terbesar di kolom price = 1200 maka hasil subquerynya adalah 1200 (hanya satu nilai/baris)
    2) Multiple Row Subquery
    Menghasilkan banyak baris.
    SELECT customer_id
FROM transactions;
    Contoh: 
    SELECT *
FROM customers
WHERE customer_id IN
(
    SELECT customer_id
    FROM transactions
);
    3) Correlated Subquery
    Subquery yang menggunakan data dari query utama.

    Misalnya:
    SELECT
    c.customer_id,
    c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM transactions t
    WHERE t.customer_id = c.customer_id
);
    Penjelasan:
    • EXISTS berarti: apakah ada minimal satu baris yang memenuhi kondisi? Hasil dari EXISTS hanya ada dua kemungkinan: TRUE (ada minimal satu baris) atau FALSE (tidak ada satupun baris).
    Penjelasan subquery:
    SELECT 1
FROM transactions t
WHERE t.customer_id = c.customer_id;
    Inilah bagian yang disebut Correlated Subquery. Mengapa? Karena subquery menggunakan: c.customer_id. Padahal kolom tersebut berasal dari query utama, bukan dari tabel transactions. Artinya, subquery bergantung pada nilai dari query utama.

    Mengapa SELECT 1? 
    Kenapa bukan: 
    SELECT *
    Atau:
    SELECT customer_id
    Jawabannya:
    Karena EXISTS tidak peduli isi data yang dipilih. Yang dicek hanya:
    Apakah query ini menghasilkan minimal satu baris?

    Jadi:
    SELECT 1
    Atau:
    SELECT *
    Atau:
    SELECT customer_id
    Semuanya memberikan hasil yang sama ketika digunakan bersama EXIST karena EXIST tidak membutuhkan isi datanya, hanya membutuhkan jawaban "ada" atau "tidak ada". Oleh karena itu, SELECT 1 digunakan sebagai tanda bahwa nilai yang dipilih tidak penting—yang penting adalah keberadaan barisnya. Jadi kalau jawabannya "ada" maka akan muncul angka 1 sebagai tanda.

    Kalau SELECT 1 diganti SELECT 'halo' maka akan muncul halo sebagai tanda kalo jawabannya "ada".

    Bagaimana SQL Memproses Query?

    Langkah 1: SQL membaca pelanggan pertama.


    Sekarang SQL menjalankan subquery.

    Nilai:
    c.customer_id = 1;
    Subquery menjadi:
    SELECT 1
FROM transactions
WHERE customer_id = 1;
    Isi tabel transactions:

    Apakah ada customer_id = 1? 
    ❌ Tidak ada.

    Hasil subquery: 0 baris
    Karena tidak ada baris: EXISTS = FALSE
    Maka ❌ Andi tidak ditampilkan.

    Langkah 2: SQL membaca pelanggan berikutnya.



    Subquery menjadi:
    SELECT 1
FROM transactions
WHERE customer_id = 2;
    Hasil:


    Mengapa ada dua baris? Karena Budi memiliki dua transaksi. Tetapi EXISTS tidak menghitung jumlah baris. Begitu menemukan baris pertama, hasilnya langsung:
    TRUE
    Maka: Budi ditampilkan.

    Langkah 3: SQL membaca Citra
    customer_id = 3
    Subquery:
    SELECT 1
FROM transactions
WHERE customer_id = 3;
    Subquery:
    SELECT 1
FROM transactions
WHERE customer_id = 3;
    Tidak ada hasil:
    FALSE
    Citra tidak ditampilkan.

    Langkah 4: SQL membaca Deny
    customer_id = 4
    Subquery:
    SELECT 1
FROM transactions
WHERE customer_id = 4;
    Ada satu baris:
    TRUE
    Deni ditampilkan.

    Hasil akhir:


    END.

    Sumber: 
    • Udemy: Kelas Data Analyst Python 2026 & Project (New Update: + SQL) by Risdan
    • ChatGPT