BELAJAR SQL MENGGUNAKAN POSTGRESQL

Pernahkah kamu mendengar tentang SQL? Nah, SQL adalah singkatan dari Structured Query Language, sebuah bahasa pemrograman yang dirancang khusus untuk mengelola data dalam database. Bayangkan database sebagai gudang besar berisi informasi, SQL adalah perantara untuk bisa mengakses informasi yang ada di dalam gudang tersebut.

Dengan SQL, kamu bisa melakukan berbagai hal, seperti:

• Menyimpan data: Menyimpan data baru ke dalam database, seperti data pelanggan, produk, atau transaksi.
• Mengelola data: Mengubah, menghapus, dan memperbarui data yang sudah ada di database.
• Mencari data: Menemukan informasi yang kamu butuhkan dengan cepat dan mudah.
• Menganalisis data: Mengolah data untuk menghasilkan laporan dan visualisasi yang menarik.

SQL adalah bahasa standar yang digunakan dalam berbagai jenis database, seperti Oracle, PostgreSQL, MySQL, dan SQL Server. Jadi, dengan mempelajari SQL, kamu bisa mengoperasikan berbagai jenis database, tanpa perlu mempelajari bahasa baru.

💡 Disclaimer: Pada artikel ini, saya menggunakan database tools dbeaver versi community dan postgreSQL server.

Basic SQL Command

Sekarang kita akan belajar tentang perintah-perintah dasar SQL yang sering digunakan.

Apa itu SQL Command?

SQL Command adalah perintah-perintah yang digunakan dalam bahasa SQL untuk berinteraksi dengan database. Berikut adalah perintah-perintah dasar SQL yang wajib kamu ketahui.

1. Membuat table (CREATE)

CREATE digunakan tidak hanya untuk membuat table, tapi juga bisa untuk membuat database, view, dan object lainnya.

Contoh penggunaan:

-- Membuat tabel
CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE
);

SQL Syntax :

CREATE [OBJECT TYPE] [OBJECT NAME] (
  [COLUMN NAME] [DATA TYPE] [CONSTRAINTS],
  ...
);

• [OBJECT TYPE]: Berupa TABLE, VIEW, FUNCTION, PROCEDURE, dll.
• [OBJECT NAME]: Nama dari object tersebut.
• [COLUMN NAME]: Nama column dari object.
• [DATA TYPE]: Tipe data dari kolom tersebut (Contoh : INT, VARCHAR(255), DATE).
• [CONSTRAINTS]: Aturan opsional (Contoh: NOT NULL, PRIMARY KEY, UNIQUE).

2. Mengubah table (ALTER)

ALTER digunakan untuk mengubah struktur tabel, database, dan object lainnya. Pada ALTER, kamu bisa menambah column dengan perintah ADD dan menghapus column dengan perintah DROP.

Contoh penggunaan :

-- Mengubah struktur tabel dengan menambahkan column phone_number
ALTER TABLE customers ADD COLUMN phone_number VARCHAR(255);
-- Mengubah struktur tabel dengan menghapus column phone_number
ALTER TABLE customers DROP COLUMN phone_number

SQL Syntax :

ALTER [OBJECT TYPE] [OBJECT NAME] [ACTION] [OPTIONS];

• [OBJECT TYPE]: Berupa TABLE, VIEW, FUNCTION, PROCEDURE, dll.
• [OBJECT NAME]: Nama dari object tersebut.
• [ACTION]: Modifikasi yang ingin dilakukan (Contoh: ADD COLUMN, MODIFY COLUMN, DROP COLUMN).
• [OPTIONS]: Parameter opsional

3. Menghapus table (DROP)

DROP digunakan untuk menghapus database, tabel, view, dan object lainnya.

Contoh penggunaan :

-- Menghapus table customers
DROP TABLE customers;

Syntax :

DROP [OBJECT TYPE] [OBJECT NAME];

• [OBJECT TYPE]: Berupa TABLE, VIEW, FUNCTION, PROCEDURE, dll
• [OBJECT NAME]: Nama dari object tersebut.

4. Menghapus semua data di Table (TRUNCATE)

TRUNCATE digunakan untuk menghapus semua data yang ada di table tetapi tidak menghapus table.

Contoh penggunaan :

-- Menghapus data di table customers
TRUNCATE TABLE customers;

Syntax :

TRUNCATE TABLE [TABLE NAME];

• [TABLE NAME]: Nama table yang akan dihapus datanya.

5. Menambahkan data (INSERT)

INSERT digunakan untuk menambahkan data baru ke dalam table.

Contoh penggunaan :

-- Menambahkan data baru
INSERT INTO customers (name, email) VALUES ('John Doe', 'johndoe@example.com');

Syntax :

INSERT INTO [TABLE NAME] (COLUMN_NAME1, COLUMN_NAME2, ...) VALUES (VALUE_1, VALUE_2, ...);

6. Mengubah data (UPDATE)

UPDATE digunakan untuk mengubah data yang sudah ada di dalam table.

Contoh penggunaan :

-- Mengubah data
UPDATE customers SET name = 'Jane Doe' WHERE id = 1;

Syntax :

UPDATE [TABLE NAME] SET COLUMN_NAME1 = VALUE_1, COLUMN_NAME2 = VALUE_2, ... WHERE [CONDITION];

7. Menghapus data (DELETE)

DELETE digunakan untuk menghapus data dari table.

Contoh penggunaan :

-- Menghapus data
DELETE FROM customers WHERE email = 'johndoe@example.com';

Syntax :

DELETE FROM [TABLE NAME] WHERE [CONDITION];

8. Menampilkan data (SELECT)

SELECT digunakan untuk menampilkan data dari table, bisa menampilkan semua column atau beberapa column tertentu.

Contoh penggunaan :

-- Mengambil semua data dari tabel
SELECT * FROM customers;

-- Mengambil data tertentu dari tabel
SELECT name, email FROM customers WHERE id = 1;

Syntax :

// Menampilkan semua column
SELECT * FROM [TABLE NAME];

// Menampilkan spesifik column
SELECT [COLUMN_NAME1], [COLUMN_NAME2], ... FROM [TABLE NAME];

9. Memulai transaksi baru (BEGIN)

BEGIN digunakan untuk memulai transaksi baru. Untuk contoh penggunaan, bisa lihat pada contoh COMMIT dan ROLLBACK Syntax :

BEGIN TRANSACTION [TRANSACTION_NAME];

10. Menyelesaikan transaksi (COMMIT)

COMMIT digunakan untuk menyelesaikan transaksi dan menyimpan perubahan.

Contoh penggunaan :

-- Memulai transaksi baru
BEGIN;

-- Menambahkan data baru
INSERT INTO customers (name, email) VALUES ('John Roe', 'richardroe@example.com');

-- Menyelesaikan transaksi
COMMIT;

Syntax :

COMMIT TRANSACTION;

11. Membatalkan transaksi (ROLLBACK)

ROLLBACK digunakan untuk membatalkan transaksi dan mengembalikan database ke keadaan semula. ROLLBACK biasanya digunakan untuk meng-handle apabila terjadi error dalam transaction, sehingga database bisa kembali dalam keadaan bersih.

Contoh pengunaan :

-- Memulai transaksi baru
BEGIN;

-- Mengubah data
UPDATE customers SET name = 'Richard Roe' WHERE id = 2;
-- Contohnya, saat transaksi ini berlangsung, tiba-tiba terjadi kesalahan jaringan. Oleh karena itu, diperlukan rollback untuk mencegah database menyimpan data yang tidak valid / junk data.

-- Membatalkan transaksi
ROLLBACK;

Syntax :

ROLLBACK TRANSACTION;

12. Memberi akses database (GRANT)

GRANT digunakan untuk memberikan akses kepada pengguna untuk melakukan operasi tertentu pada database.

Contoh penggunaan :

-- Memberikan izin kepada user1 untuk membaca tabel  
GRANT SELECT ON customers TO 'user1';  

Syntax :

GRANT [PRIVILEGE] ON [OBJECT] TO [USER|ROLE];

• [PRIVILEGE]: Privilege yang ingin diberikan aksesnya (Contoh: SELECT, INSERT, UPDATE, DELETE).
• [OBJECT]: Nama object yang ingin diberikan aksesnya (Contoh: table, view, procedure).
• [USER|ROLE]: Username atau role name yang ingin diubah aksesnya.

13. Menghapus akses database (REVOKE)

REVOKE digunakan untuk menghapus akses yang telah diberikan kepada pengguna.

Contoh penggunaan :

-- Menghapus izin user1 untuk bisa melihat data customers
REVOKE SELECT ON customers FROM 'user1';

Syntax :

REVOKE [PRIVILEGE] ON [OBJECT] FROM [USER|ROLE];

• [PRIVILEGE]: Privilege yang ingin dihapus aksesnya (Contoh: SELECT, INSERT, UPDATE, DELETE).
• [OBJECT]: Nama object yang ingin dihapus aksesnya (Contoh: table, view, procedure).
• [USER|ROLE]: Username atau role name yang ingin diubah aksesnya.

Dari ke-13 perintah dasar ini, sebetulnya kamu bisa kelompokan menjadi 5 kategori utama yaitu :

1. DDL - Data Definition Language DDL digunakan untuk mendefinisikan struktur database, termasuk membuat (CREATE), mengubah (ALTER), dan menghapus tabel (DROP / TRUNCATE), database, dan objek lainnya.

2. DML - Data Manipulation Language DML digunakan untuk memanipulasi data dalam database, seperti menambahkan (INSERT), menghapus (DELETE), dan mengubah data (UPDATE).

3. DQL - Data Query Language DQL digunakan untuk mengambil data dari database. Perintah DQL yang paling umum adalah SELECT.

4. TCL - Transaction Control Language TCL digunakan untuk mengontrol transaksi dalam database, seperti memulai (BEGIN), menyelesaikan (COMMIT), dan membatalkan transaksi (ROLLBACK),

5. DCL - Data Control Language DCL digunakan untuk mengatur hak akses dan kontrol (GRANT/REVOKE) terhadap database, seperti memberikan izin kepada pengguna untuk mengakses tabel atau database.

Urutan menjalankan SQL Query

Selain basic SQL Command, untuk menjalankan SQL query terdapat urutan penulisan query yang harus diperhatikan :

1. FROM/JOIN

• Digunakan untuk menentukan tabel mana yang akan diambil datanya.
• JOIN digunakan untuk menggabungkan data dari beberapa tabel.

-- Mengambil semua data dari tabel customers
SELECT * FROM customers; 
 -- Menggabungkan data dari tabel orders dan customers berdasarkan kolom customer_id
SELECT orders.id, customers.name FROM orders JOIN customers ON orders.customer_id = customers.id;

2. WHERE

• Digunakan untuk memfilter data berdasarkan kondisi tertentu.

-- Mengambil semua data dari tabel customers dengan negara Indonesia
SELECT * FROM customers WHERE country = 'Indonesia';
-- Mengambil data dari tabel orders dan customers yang status ordernya "shipped"
SELECT orders.id, customers.name FROM orders JOIN customers ON orders.customer_id = customers.id WHERE orders.status = 'shipped';

3. HAVING

• Digunakan untuk memfilter data berdasarkan agregasi data.

-- Menghitung total order untuk setiap customer dan hanya menampilkan customer yang memiliki total order lebih dari 5
SELECT COUNT(*) AS total_orders FROM orders GROUP BY customer_id HAVING total_orders > 5; 

4. GROUP BY

• Digunakan untuk mengelompokkan data berdasarkan kolom tertentu.

-- Menghitung total customer untuk setiap negara
SELECT country, COUNT(*) AS total_customers FROM customers GROUP BY country; 

5. ORDER BY

• Digunakan untuk mengurutkan data berdasarkan kolom tertentu.

-- Mengurutkan data dari tabel customers berdasarkan nama secara ascending (dari A ke Z)
SELECT * FROM customers ORDER BY name ASC; 
-- Mengurutkan data dari tabel orders dan customers berdasarkan tanggal pembuatan order secara descending (dari terbaru ke terlama)
SELECT orders.id, customers.name FROM orders JOIN customers ON orders.customer_id = customers.id ORDER BY orders.created_at DESC; 

6. LIMIT

• Digunakan untuk membatasi jumlah data yang ditampilkan.

-- Menampilkan 10 data pertama dari tabel customers
SELECT * FROM customers LIMIT 10; 
 -- Menampilkan 5 data terakhir dari tabel orders dan customers yang diurutkan berdasarkan tanggal pembuatan order secara descending
SELECT orders.id, customers.name FROM orders JOIN customers ON orders.customer_id = customers.id ORDER BY orders.created_at DESC LIMIT 5;

Studi Kasus

Pre-step

1. Untuk membuat database baru di dbeaver pilih Database → New Database Connection → PostgreSQL → klik finish.
2. Pada sidebar pilih database yang sudah kita buat dan klik connect.
3. Kemudian buka SQL editor untuk connect database yang telah kita buat.
4. Untuk mengetest apakah SQL Editor sudah terkoneksi dengan baik, kita dapat mengetik select 1 dan meng-execute query.
5. Apabila berhasil, data 1 akan terdisplay dan kita sudah bisa menggunakan SQL Editor untuk menulis SQL Command.

Contoh kasus

Perusahaan XYZ memiliki data produk dan penjualan dengan format seperti berikut:

1. Table produk

Dengan data set untuk table produk seperti di bawah ini

2. Table penjualan

Dengan data set untuk table penjualan seperti di bawah ini

SQL Command untuk populate semua sample data di atas adalah :

-- Create table produk
CREATE TABLE produk (
  id_produk SERIAL PRIMARY KEY,
  nama_produk VARCHAR(255) NOT NULL,
  harga INT NOT NULL,
  stok INT NOT NULL
);

-- Populate data produk
INSERT INTO produk (nama_produk, harga, stok)
VALUES
  ('Sabun mandi', 10000, 100),
  ('Shampo', 15000, 50),
  ('Pasta gigi', 12000, 75),
  ('Detergen', 20000, 125),
  ('Sabun cuci piring', 25000, 200);

-- Create table penjualan
CREATE TABLE penjualan (
  id_penjualan SERIAL PRIMARY KEY,
  tanggal_penjualan DATE NOT NULL,
  id_produk INT NOT NULL,
  jumlah INT NOT NULL,
  total_harga INT NOT NULL,
  FOREIGN KEY (id_produk) REFERENCES produk (id_produk)
);

-- Populate data penjualan
INSERT INTO penjualan (tanggal_penjualan, id_produk, jumlah, total_harga)
VALUES
  ('2024-02-14', 1, 2, 20000),
  ('2024-02-15', 2, 1, 15000),
  ('2024-02-01', 3, 3, 36000),
  ('2024-01-23', 4, 1, 20000),
  ('2024-02-15', 5, 2, 50000),
  ('2024-02-10', 4, 1, 20000),
  ('2024-01-19', 3, 2, 24000),
  ('2024-01-20', 2, 2, 30000),
  ('2024-02-15', 4, 3, 60000),
  ('2024-02-12', 5, 1, 25000);

Soal 1 : Menampilkan nama produk dengan awalan “Sabun”

Hasil yang diharapkan :

Solusi :

select nama_produk from produk where nama_produk like 'Sabun%'

WHERE untuk menerapkan kondisi pada row yang akan diambil dari table. LIKE digunakan untuk mencocokkan pola teks dan % adalah wildcard digunakan mencocokkan bagian dari teks yang tidak pasti. Sehingga Sabun% digunakan untuk mendapatkan semua nama produk yang dimulai dengan “Sabun” dan diikuti oleh apa pun karakter lainnya.

Soal 2 : Menampilkan tanggal penjualan yang unik.

Karena penjualan di tanggal 2024-02-15 sebanyak 3x, maka hasil yang diharapkan hanya ada 8 baris seperti di bawah ini :

Solusi :

SELECT DISTINCT tanggal_penjualan
FROM penjualan;

DISTINCT digunakan dalam query SELECT untuk mengembalikan hanya nilai-nilai unik dari column yang dipilih sehingga otomatis menghilangkan data yang duplicate.

Soal 3 : Menampilkan total penjualan perhari.

Hasil yang diharapkan :

Solusi :

SELECT tanggal_penjualan, SUM(total_harga) AS total_penjualan
FROM penjualan
GROUP BY tanggal_penjualan;

SUM untuk menjumlahkan nilai-nilai dalam sebuah column numeric. Apabila kita menggunakan SUM maka harus menambahkan GROUP BY untuk mengelompokkan data penjualan berdasarkan tanggal_penjualan

Soal 4 : Menampilkan trend penjualan per bulan

Hasil yang diharapkan :

Solusi :

SELECT EXTRACT(MONTH FROM tanggal_penjualan) AS bulan, SUM(total_harga) AS total_penjualan
FROM penjualan
GROUP BY bulan
ORDER BY bulan;

EXTRACT untuk mengambil bulan dari tanggal_penjualan. ORDER BY digunakan untuk mengurutkan hasil berdasarkan bulan, sehingga total penjualan akan ditampilkan secara urut berdasarkan bulan.

Solusi 5 : Menemukan produk dengan total penjualan kurang dari 30.000

Hasil yang diharapkan :

Solusi :

SELECT
  produk.nama_produk,
  SUM(total_harga) AS total_penjualan
FROM penjualan
INNER JOIN produk ON penjualan.id_produk = produk.id_produk
GROUP BY produk.nama_produk, penjualan.total_harga
HAVING SUM(total_harga) < 30000
ORDER BY SUM(total_harga) ASC;

JOIN untuk menggabungkan data penjualan dan produk dengan perantara id_produk (disesuaikan dengan Foreign Key yang telah di set di table penjualan

HAVING untuk menerapkan kondisi pada hasil agregat. Sehingga hanya data dengan jumlah total harga kurang dari 30000 yang akan disertakan dalam hasil query.

Solusi 6 : Menemukan produk terlaris

Karena detergen terjual sebanyak 1x di tanggal 2024-01-23, 1x di tanggal 2024-02-10, dan 3x di tanggal 2024-02-15, maka hasil yang diharapkan :

Solusi :

SELECT
  nama_produk,
  SUM(jumlah) AS total_penjualan 
FROM penjualan
INNER JOIN produk ON penjualan.id_produk = produk.id_produk
GROUP BY nama_produk
ORDER BY total_penjualan DESC
LIMIT 1;

LIMIT untuk membatasi jumlah row yang akan ditampilkan hasil query. Karena kita telah ORDER BY terlebih dahulu secara DESC total penjualan, maka kita hanya perlu mengampil data teratas sebagai produk yang paling laris.

Demikian pembelajaran kita mengenai SQL. Jika kamu merasa masih belum terlalu paham mengenai SQL, kamu juga bisa mencoba berlatih secara gratis di website ini:

• W3Schools
• Leetcode