SQL Queries — Part II

Ulasan sebelumnya pada SQL Queries — Part I membahas tentang Filtering, yang merupakan mengembangan perintah WHERE dengan operasi matematika, operasi logikal, BETWEEN, IN, dan LIKE. Anggegation yang memanfaatkan perintah COUNT, MIN, MAX, AVG, serta SUM. Grouping dengan memanfaatkan perintah GROUP BY. Serta, Conditional Statement yang merupakan pengembangan perintah SELECT menggunakan syntax CASE.

Kali ini saya akan melanjutkan pembahasan tentang Query pada SQL. Ulasan kali ini akan membahas tentang (1) JOIN, berfungsi untuk menggabungkan data beserta jenis-jenisnya, seperti: inner join, left join dan right join. (2) SUBQUERY, merupakan query dalam query, biasanya digunakan untuk mengambil data dari tabel lainnya. (3) VIRTUAL TABLE, merupakan cara untuk menyimpan atau menampilkan data dalam tabel vitual. (4) STRING FUNCTIONS yang bisa dimanfaatkan untuk memanipulasi data teks, seperti: merapikan dan mengganti teks. (5) MATHEMATICAL FUNCTIONS yang berfungsi untuk memanipulasi data numerik, misalnya pembulatan dan pemangkatan. Serta (6) DATE-TIME FUNCTIONS yang berfungsi untuk memanipulasi data date-time, seperti mengekstrak data date-time.

1. JOIN

Perintah JOIN berfungsi untuk menggabungkan kolom berdasarkan nilai baris yang sama antara dua tabel. JOIN digunakan untuk manarik informasi dari satu tabel dan menambahkan data dari tabel lain untuk memperoleh informasi yang lebih komprehensif untuk dianalisis.

Misalnya ketika ingin melihat produk yang memiliki nilai penjualan paling besar disetiap region. Agar lebih terorganisir, biasanya data disimpan pada tabel yang spesifik. Data tentang nama, usia, alamat, serta data tentang customer disimpan dalam tabel customer. Data tentang nama, jenis, dan kategori produk disimpan dalam tabel product. Sedangkan data tanggal penjualan disimpan pada tabel sales. Meski berbeda tabel, kita tetap bisa mengambil informasi dari semua tabel dengan memanfaatkan relasi antar tabel serta fungsi JOIN pada SQL. Gambar dibawah menunjukkan relasi antar ketiga tabel.

1.1 ERD dari tabel customer, sales, dan product.

Diperlukan kolom yang sama disetiap tabel untuk melakukan JOIN. Kolom-kolom ini biasa disebut primary key (nilai unik yang digunakan untuk mengidentifikasi data) dan foreign key (primary key pada tabel lainnya). Pada contoh diatas customer_id merupakan primary key pada tabel customer dan foreign key pada tabel sales. Kolom yang sama disetiap tabel ini yang dijadikan rujukan untuk melakukan JOIN, dituliskan dengan syntax ON saat melakukan JOIN. Perhatikan gambar berikut.

1.2 Syntax untuk melakukan JOIN

Note: Pada contoh JOIN kali ini akan digunakan sampel data yang lebih kecil agar mudah dipahami. Perhatikan jumlah data dan kolom customer_id pada kedua tabel berikut:

1.3 Tabel sample sales

1.4 Tabel sample customer

Ada beberapa macam JOIN yang bisa dilakukan untuk menggabungkan tabel. Pada ulasan kali ini akan membahas empat tipe JOIN, perhatikan gambar berikut:

1.5 Ilustrasi JOIN

a. INNER JOIN, hanya mengambil data yang sama diantara dua tabel.

1.6 Contoh penggunaan inner join

Tabel sample_sales memiliki 155 baris, sedangkan tabel sample_customer memiliki 150 baris. Setelah dilakukan INNER JOIN hanya diperoleh 35 baris yang beririsan antara kedua tabel. Perhatikan customer_id pada INNER JOIN (BH-11710, BP-11290, dll).

Semua kolom yang diambil akan terisi data dari kedua tabel (order_line, product_id, customer_id, sales dari tabel sample_sales, customer_name dan age dari sample_customer). Serta tidak ada kolom yang kosong.

b. LEFT JOIN, menampilkan semua data di tabel sebelah kiri (tabel pertama), dan data yang beririsan di tabel sebelah kanan (tabel kedua).

1.7 Contoh penggunaan left join

Total baris yang diperoleh setelah dilakukan LEFT JOIN adalah 155 baris, sesuai dengan tabel pertama (sample_sales). Namun juga mengambil data dari tabel kedua pada kolom customer_name dan age. (perhatikan baris 15–17)

c. RIGHT JOIN, menampilkan semua data di tabel sebelah kanan (tabel kedua), dan data yang beririsan di tabel sebelah kiri (tabel pertama).

1.8 Contoh penggunaan right join

RIGHT JOIN mengambil semua data dari tabel kedua, tetapi juga mengambil data dari tabel pertama yang beririsan dengan tabel kedua. Perhatikan baris 33–35.

d. FULL OUTER JOIN, menampilkan semua data baik ketika ada yang cocok antara kedua tabel ataupun tidak.

1.9 Contoh penggunaan full outer join

Cara lain menggabungkan data selain JOIN adalah UNION. Bedanya UNION berfungsi untuk menggabungkan baris berdasarkan kolom yang sama. Syaratnya jumlah kolom dan tipe data yang diambil dari kedua tabel harus sama. Berikut contoh penggunaan perintah UNION.

2. SUBQUERY

Subquery adalah query didalam query. Biasanya digunakan untuk mendapatkan nilai dari suatu tabel dengan suatu kondisi yang dipengaruhi oleh tabel lain. Subquery bisa diletakkan pada perintah SELECT, FROM, serta WHERE. Berikut contoh penggunaannya:

2.1 Contoh penggunaan subquery pada perintah select

2.2 Contoh penggunaan subquery pada perintah from

2.3 Contoh penggunaan subquery pada perintah where

3. VIRTUAL TABLE

Tabel virtual pada SQL memiliki banyak jenis dan manfaat. Tabel virtual digunakan untuk menyimpan atau menampilkan data. Ada jenis tabel virtual yang hanya berfungsi sebagai alat bantu query, ada juga tabel virtual yang disimpan dalam memori. Pada ulasan kali ini kan membahas tentang Common Table Expression (CTE), Temporary table, serta View.

a. Common Table Expression (CTE)
CTE memiliki kegunaan yang hampir sama dengan subquery. Pada CTE, kita membuat tabel berisi data-data tertentu, kemudian kita bisa mengambil data dari tabel CTE tersebut.

Syntax untuk membuat CTE adalah : “WITH (tabel_name) AS ….”

Misalnya kita ingin membuat tabel logistic yang mengambil informasi tabel sales dan customer. Kita bisa memanfaatkan tabel logistic untuk mengambil informasi yang dibutuhkan tampa harus melakukan JOIN berkali-kali. CTE tidak disimpan dalam memory jadi perintah SELECT hanya bisa digunakan setelah (dibawah) membuat CTE. Perhatikan gambar berikut:

3.1 Contoh penggunaan CTE

b. Temporaty Table
Temporary Table atau biasa disingkat TEMP TABLE memiliki fungsi yang sama dengan CTE. Bedanya TEMP TABLE disimpan dalam memory, jadi bisa digunakan kapan saja. Karena itu juga, kita tidak bisa membuat dua TEMP TABLE dengan nama yang sama.

Pada PostgreSQL syntax untuk membuat TEMP TABLE adalah
CREATE TEMPORARY TABLE (tabel_name)
CREATE TEMPORARY TABLE (tabel_name)
Pada bahasa SQL lain pembuatan TEMP TABLE bisa memanfaatkan tanda tagar atau #. Berikut contoh penggunaannya:

3.2 Contoh penggunaan TEMP TABLE

c. VIEW
VIEW adalah tabel virtual untuk menyimpan data. View merupakan hasil query, namun memiliki artibut layaknya tabel. View memiliki beberapa manfaat: Pertama, mudah digunakan karena memuat informasi yang dibitihkan dari beberapa tabel. Kedua, menghemat memori karena tabel virtual tidak disimpan di dalam memori, beda dengan tabel. Kegita, menjamin keamanan data karena kita bisa menentukan data apa saja yang ingin dibagikan kedalam view dan siapa saja yang bisa mengaksesnya.

Berikut contoh penggunaan VIEW:

3.2 Contoh penggunaan VIEW

4. STRING FUNCTIONS

Sama halnya dengan string atau teks pada Spreadsheet, kita juga bisa memodifikasi data pada SQL. Diantaranya dengan memanfaatkan fungsi length, upper, lower, replace, concate, dan trim.

a. LENGTH
Berfungsi untuk menghitung jumlah karakter pada teks.

4.1 Contoh penggunaan fungsi length

b. UPPER
Berfungsi untuk mengubah teks menjadi huruf kapital.

c. LOWER
Berfungsi untuk mengubah teks menjadi huruf kecil.

4.2 Contoh penggunaan fungsi upper dan lower

d. REPLACE
Berfungsi untuk mengganti teks dengan teks tertentu syntaxnya:
REPLACE (kolom, ‘teks asli’, ‘teks pengganti’)

4.3 Contoh penggunaan fungsi replace

e. CONCATE
Berfungsi untuk menggabungkan teks. Menggunakan symbol “||”

4.4 Contoh penggunaan fungsi concate

f. TRIM
Berfungsi untuk membersihkan spasi, sama seperti fungsi TRIM pada Spreadsheet.

4.5 Contoh penggunaan fungsi trim

5. MATHEMATICAL FUNCTIONS

Fungsi matematika pada SQL bisa digunakan untuk melakukan pembulatan dan pemangkatan.

a. CIEL
Berfungsi untuk melakukan pembulatan ke atas.

b. FLOOR
Berfungsi untuk melakukan pembulatan ke bawah.

c. ROUND
Berfungsi untuk melakukan pembulatan sesuai kaidah matematika.

5.1 Contoh penggunaan fungsi ciel, floor, dan round

d. POWER
Berfungsi untuk melakukan pemangkatan. Misalnya 18 pangkat 2 = 324.

5.2 Contoh penggunaan fungsi power

6. DATE-TIME FUNCTIONS

Fungsi Date-Time pada SQL bisa digunakan untuk mengembalikan data tanggal dan waktu saat ini serta mengekstrak data tanggal dan waktu.

a. CURRENT
Berfungsi untuk mengembalikan data date-time saat ini (saat perintah dijalankan).
CURRENT_DATE : mengembalikan data tanggal saat ini
CURRENT_TIME : mengembalikan data waktu saat ini
CURRENT_TIMESTAMP : mengembalikan data tanggal dan waktu saat ini

6.1 Contoh penggunaan fungsi current

b. AGE
Berfungsi untuk mengembalikan selisih data antara dua tanggal.

6.2 Contoh penggunaan fungsi age

c. EXTRACT
Berfungsi untuk mengekstrak data (day, month, year) dari sebuah data.

6.3 Contoh penggunaan fungsi extract

Demikian ulasan terkait SQL Queries — Part II. Ini merupakan rangkain ulasan tentang SQL. Ulasan sebelumnya bisa dibaca pada link berikut:
1. Dasar-Dasar SQL untuk Data Analyst
2. SQL Queries — Part I

Terima kasih telah membaca.
Semoga ulasan ini bermanfaat.
Sila dibaca dan dibagikan agar lebih banyak yang membaca ulasan ini.