SAINSDATA.ID

SQLite: Menguasai Perintah SELECT

by

SQLite Database Query SELECT

Pada tutorial ini, kita akan membahas secara lengkap satu bagian dari Data Manipulation Language (DML) yaitu membuat query dengan perintah SELECT khususnya pada database SQLite. Meskipun demikian, secara umum cara yang sama juga berlaku untuk RDBMS yang lainnya.

Pembahasan akan dimulai dari penggunaan dasar SELECT hingga teknik-teknik lanjutan. Kita akan mempelajari cara membuat query SELECT untuk memilih kolom tertentu, menggunakan klausa WHERE untuk memfilter data, mengurutkan hasil dengan ORDER BY, serta menggabungkan data dari beberapa tabel menggunakan JOIN. Dengan memahami konsep dan penerapan perintah SELECT, diharapkan dapat melakukan pengelolaan dananalisis data dalam basis data SQLite dengan lebih efektif.

Tutorial ini akan menggunakan data transaksi pembelian dari suatu retail. Jika ingin mencoba dengan data yang sama silahkan mengunduhnya pada tautan berikut : transaksi.db. Database terdiri dari beberapa tabel, namun pada bagian ini kita fokus pada tabel sales_log saja.

Dasar-Dasar SELECT

Memilih Semua Kolom

SQL

-- memilih semua kolom dari tabel sales_log
SELECT * FROM sales_log;
# OUTPUT

nomor_urut    sales_id	 segment  payment_mode  shipment_mode  product    quantity    sales
   1    CA-2019-160304	 COR	  OL 	        STD	       FURN-01	       1    73.94
   2    CA-2019-125206	 CON	  OL 	        FRS	       OFFS-01	       2   114.46
   3    US-2019-116365	 COR	  OL 	        STD	       TECH-04	       2    30.08
   4    CA-2019-105207	 COR	  COD	        STD	       FURN-03	       7  1592.85
   5    US-2019-164630	 COR	  OL 	        STD	       TECH-01	       4  959.968
                   ...	 ...	   ..	        ...	           ...	       .      ...
3002    CA-2020-156720	 CON	  OL 	        STD	       OFFS-06	       3  388.024
3003    CA-2020-151450	 CON	  COD	        STD	       OFFS-06	       3  2975.51

Memilih Kolom Tertentu

Kita dapat menyeleksi beberapa kolom saja pada perintah SELECT dengan menuliskan nama-nama kolom yang akan diambil.

SQL

-- memilih beberapa kolom tertentu
SELECT segment, product, sales FROM sales_log
# OUTPUT

segment  product    sales
COR	  FURN-01    73.94
CON	  OFFS-01   114.46
COR	  TECH-04    30.08
COR	  FURN-03  1592.85
COR	  TECH-01  959.968
...          ...      ...
CON	  OFFS-06  388.024
CON	  OFFS-06  2975.51

Memilih nilai unik (DISTINCT)

SQL

-- mengunakan DISTINCT untuk mendapatkan nilai unik
-- jika lebih dari 1 kolom maka menghasilkan kombinasi unik
SELECT DISTINCT segment, payment_mode FROM sales_log;
# OUTPUT

segment  payment_mode
COR	  OL
CON	  OL
COR	  COD
HOM	  COD
CON	  COD
CON	  CD
COR	  CD
HOM	  OL
HOM	  CD

Membatasi jumlah hasil (LIMIT)

SQL

-- menggunakan LIMIT untuk membatasi hasil
SELECT * FROM sales_log LIMIT 5;
# OUTPUT

nomor_urut    sales_id	 segment  payment_mode  shipment_mode  product    quantity    sales
   1    CA-2019-160304	 COR	  OL 	        STD	       FURN-01	       1    73.94
   2    CA-2019-125206	 CON	  OL 	        FRS	       OFFS-01	       2   114.46
   3    US-2019-116365	 COR	  OL 	        STD	       TECH-04	       2    30.08
   4    CA-2019-105207	 COR	  COD	        STD	       FURN-03	       7  1592.85
   5    US-2019-164630	 COR	  OL 	        STD	       TECH-01	       4  959.968

Melangkahi beberapa baris (OFFSET)

SQL

-- menggunakan OFFSET untuk melewati 3 baris pertama
-- pengambilan akan dimulai pada baris ke-4
-- dan membatasi hasil sebanyak 5 baris
SELECT nomor_urut, segment, sales FROM sales_log LIMIT 5 OFFSET 3;
# OUTPUT

nomor_urut    segment     sales
         4	COR	   1592.85 
         5	COR	   959.968
         6	COR	     4.672
         7	COR	   191.472
         8	HOM	     34.58

Nama Alias (AS)

Jika diperlukan kita dapat memberikan nama alias untuk suatu kolom. Nama alias diberikan menggunakan klausul AS diikuti nama alias. Namun dalam praktiknya, kata AS dapat dihapus dan memberikan nama alias langsung setelah nama kolom.

SQL

--  memberikan nama alias untuk beberapa kolom
SELECT nomor_urut, 
       payment_mode AS pembayaran, 
       shipment_mode AS pengiriman,
       sales nilai
FROM sales_log
LIMIT 5
# OUTPUT

nomor_urut    pembayaran  pengiriman     nilai
         1	OL 	     STD	        73.94
         2	OL 	     FRS	       114.46
         3	OL 	     STD	        30.08
         4	COD	     STD	      1592.85
         5	OL 	     STD	      959.968

Pengurutan Data (ORDER BY)

Data yang diambil dapat diurutkan menggunakan klausul ORDER BY. Pengurutan dapat dilakukan secara menaik ASC (default) maupun menurun (DESC). Pengurutan juga dapat dilakukan secara hirarki untuk lebih dari 1 kolom.

Mengurutkan secara menaik (ASC)

SQL

-- mengurutkan berdasarkan nilai `sales` ascending
SELECT sales_id, product, quantity, sales FROM sales_log
ORDER BY sales
# OUTPUT

      sales_id    product    quantity  	 sales
CA-2019-168361	OFFS-02	       1	   0.836
CA-2019-169922	OFFS-02	       4	   1.344
CA-2019-114748	OFFS-03	       1	   1.408
CA-2019-132409	OFFS-03	       1	   1.504
CA-2019-108364	OFFS-02	       5	   1.8
           ...        ...           .         ...			
CA-2020-166709	TECH-01	       3	 5517.97
US-2019-107440	TECH-02	       7	 9099.93

Mengurutkan secara menurun (DESC)

SQL

-- Mengambil 5 baris dengan sales terbesar
SELECT sales_id, sales FROM sales_log 
ORDER BY sales DESC 
LIMIT 5
# OUTPUT

      sales_id       sales
US-2019-107440	 9099.93
CA-2020-166709	 5517.97
CA-2019-158841	 4749.95
CA-2019-100300	 4476.8
CA-2020-118892	 4439.174

Mengurutkan berdasarkan 2 kolom

Data diurutkan secara menurun (dari terbesar ke terkecil) berdasarkan kolom quantity, kemudian untuk setiap nilai quantity yang sama data diurutkan lagi berdasarkan kolom sales secara menaik (dari terkecil ke terbesar).

SQL

-- mengurutkan data berdasarkan 2 kolom
SELECT sales_id, quantity, sales 
FROM sales_log 
ORDER BY quantity DESC, sales ASC
LIMIT 10
# OUTPUT

      sales_id   quantity       sales
CA-2019-141887	     14	  54.992
CA-2020-164028	     14	 249.58
CA-2020-152702	     14	 271.604
CA-2019-140571	     14	 319.76
CA-2020-169859	     14	 391.96
CA-2019-116596	     14	 427.644
US-2019-103674	     14	 437.472
CA-2020-130036	     14	1131.888
CA-2020-114524	     13	  95.468
CA-2020-147942	     13	 166.68

Filter Data (WHERE)

Data yang diambil dapat difilter berdasarkan berbagai kondisi. Kondisi-kondisi ini dapat menggunakan operator seperti:

  • Untuk data numerik dapat menggunakan "=" (sama dengan), "<>" (tidak sama dengan), ">" (lebih besar dari), "<" (lebih kecil dari), ">=" (lebih besar sama dengan), "<=" (lebih kecil sama dengan).
  • IN/NOT IN, menyeleksi/tidak berdasarkan daftar nilai yang diberikan
  • BETWEEN/NOT BETWEEN menyeleksi/tidak berdasarkan rentang nilai yang diberikan (inklusif)
  • LIKE/NOT LIKE menyeleksi/tidak berdasarkan pattern yang diberikan
  • REGXP, menyeleksi berdasarkan regular expression yang diberikan
  • IS NULL/IS NOT NULL, menyeleksi berdasarkan nilai NULL atau tidak NULL
  • OR, menggabungkan 2 atau lebih kondisi dan bernilai benar jika setidaknya 1 kondisi saja terpenuhi
  • AND, menggabungkan 2 atau lebih kondisi dan bernilai benar jika dan hanya jika semua kondisi benar

Filter dengan 1 kondisi

Contoh, mengambil data dengan nilai sales lebih dari atau sama dengan 4000:

SQL

-- mengambil data dengan sales >= 4000
SELECT sales_id, sales FROM sales_log 
WHERE sales >=4000
# OUTPUT

      sales_id       sales
CA-2019-158841	 4749.95
US-2019-107440	 9099.93
CA-2019-100300	 4476.8
CA-2020-118892	 4439.174
US-2020-102183	 4330.552
CA-2020-166709	 5517.97

Filter dengan kondisi ‘LIKE’, ‘BETWEEN’, ‘<‘ dan ‘AND’

Contoh berikut yaitu mengambil data dengan beberapa kondisi dengan klausul LIKE, BETWEEN, AND dan >:

SQL

-- menyeleksi dengan beberapa kondisi
SELECT sales_id, product, quantity, sales  FROM sales_log 
WHERE 
   product LIKE "FURN%" AND 
   quantity BETWEEN 5 AND 10 AND
   sales > 2000
ORDER BY quantity DESC
# OUTPUT

      sales_id    product  quantity      sales
CA-2019-113831	FURN-03	    10	2275.5
CA-2020-118892	FURN-02	     9	4439.174
CA-2020-123967	FURN-03	     9	2694.62
CA-2019-107104	FURN-01         8	3406.664
CA-2020-149559	FURN-02	     8	2101.272
US-2020-162558	FURN-03	     7	2335.116
US-2019-164196	FURN-03	     6	2678.94
CA-2019-122903	FURN-02	     5	3504.9
CA-2020-150091	FURN-01	     5	2185.9

Filter dengan kondisi ‘LIKE’, ‘IN’, ‘=’ dan ‘AND’

Contoh berikut yaitu mengambil data dengan beberapa kondisi dengan klausul LIKE, IN, AND dan =:

SQL

-- menyeleksi dengan beberapa kondisi
SELECT * FROM sales_log 
WHERE 
   sales_id LIKE "%2020%" AND 
   shipment_mode IN ("FRS", "SEC") AND
   segment = "CON"
LIMIT 5
# OUTPUT

nomor_urut      sales_id    segment  payment_mode  shipment_mode  product  quantity      sales
 504	  CA-2019-120200	CON	  OL	        FRS	        OFFS-09	     2	 11.632
1320	  CA-2020-147207	CON	  OL	        SEC	        OFFS-01	     3	415.144
1326	  US-2020-123862	CON	  CD	        SEC	        OFFS-01	     3	101.02
1339	  CA-2020-138779	CON	  CD	        FRS	        TECH-03	     5	 89.92
1340	  US-2020-104661	CON	  CD	        FRS	        TECH-04	     8	102.592

Operasi dan Fungsi Kolom

Kolom baru dapat ditambahkan misalnya dengan melakukan operasi pada 1 atau beberapa kolom yang ada. Contoh operator matematika yang bisa dilakukan meliputi "+", "-", "*", "/" dan sebagainya.

Selain operator tersebut, terdapat beberapa fungsi yang juga dapat digunakan yaitu:

  • ABS: Mengembalikan nilai absolut dari suatu angka, ABS(column_name)
  • ROUND: Membulatkan angka ke bilangan desimal terdekat, ROUND(column_name, decimal)
  • CEIL: Mengembalikan nilai integer yang lebih besar dari atau sama dengan argumen, CEIL(column_name)
  • FLOOR: Mengembalikan nilai integer yang lebih kecil dari atau sama dengan argumen, FLOOR(column_name)
  • EXP: Mengembalikan nilai eksponensial dari argumen, EXP(column_name)
  • LOG: Mengembalikan logaritma natural dari argumen, LOG(column_name)
  • POWER: Mengembalikan nilai pangkat dari argumen POWER(column_name, exponent)
  • SQRT: Mengembalikan akar kuadrat dari argumen, SQRT(column_name)
  • SIGN: Mengembalikan tanda (1 untuk positif, 0 untuk nol, -1 untuk negatif) dari argumenSIGN(column_name)
  • RAND: Mengembalikan angka acak antara 0 dan 1, RAND()

Adapun untuk data teks terdapat beberapa operasi sebagai berikut:

  • LENGTH: Mengembalikan panjang teks : LENGTH(column_name)
  • UPPER: Mengonversi teks menjadi huruf kapital: UPPER(column_name)
  • LOWER: Mengonversi teks menjadi huruf kecil: LOWER(column_name)
  • TRIM: Menghapus spasi di awal dan akhir teks: TRIM(column_name)
  • LTRIM: Menghapus spasi di awal teks: LTRIM(column_name)
  • RTRIM: Menghapus spasi di akhir teks: RTRIM(column_name)
  • CONCAT: Menggabungkan dua atau lebih string: CONCAT(column1, column2)
  • REPLACE: Mengganti substring dalam teks dengan teks lain: REPLACE(column_name, 'old_string', 'new_string')
  • INSTR: Mengembalikan posisi dari substring dalam teks: INSTR(column_name, 'substring')
  • SUBSTR: Mengambil sub-bagian teks dari teks: SUBSTR(column_name, start_index, length)
  • LEFT: Mengambil sejumlah karakter dari awal teks: LEFT(column_name, length)
  • RIGHT: Mengambil sejumlah karakter dari akhir teks: RIGHT(column_name, length)

SQL

--  membuat kolom baru hasil operasi kolom lain
SELECT 
   SUBSTR(sales_id, 4, 4) AS Tahun,
   sales/quantity AS Harga,
   sales AS Nilai,
   sales * 0.1 AS PPN
FROM sales_log
LIMIT 5
# OUTPUT

Tahun     Harga       Nilai          PPN
2019	  73.94	   73.94	    7.394
2019	  57.23	  114.46	   11.446
2019	  15.04	   30.08	    3.008
2019	 227.55	 1592.85	  159.285
2019	239.992	 959.968	  95.9968

Fungsi Agregasi

Fungsi agregasi digunakan untuk menghitung nilai agregat dari satu atau lebih baris dalam hasil kueri. Berikut adalah beberapa fungsi agregasi yang umum digunakan dalam SQLite:

  • COUNT: Menghitung jumlah baris dalam kolom, COUNT(column_name)
  • SUM: Menghitung total jumlah nilai dalam kolom, SUM(column_name)
  • AVG: Menghitung rata-rata dari nilai dalam kolom, AVG(column_name)
  • MIN: Mengembalikan nilai minimum dalam kolom, MIN(column_name)
  • MAX: Mengembalikan nilai maksimum dalam kolom, MAX(column_name)
  • GROUP_CONCAT: Menggabungkan nilai-nilai dalam grup menjadi satu string GROUP_CONCAT(column_name)

SQL

-- Menghitung jumlah baris
SELECT COUNT(*) FROM sales_log;     -- COUNT(*): 3003

-- menghitung total sales
SELECT SUM(sales) FROM sales_log;   -- SUM(sales): 815770.8943

-- menghitung rata-rata sales
SELECT AVG(sales) FROM sales_log;   -- AVG(sales): 271.651979453879

-- mencari nilai minimum sales
SELECT MIN(sales) FROM sales_log;   -- MIN(sales): 0.836

-- mencari nilai maksimum sales
SELECT MAX(sales) FROM sales_log;   -- MAX(sales): 9099.93

Agregasi Data (GROUP BY)

Perintah GROUP BY berguna untuk mengelompokkan data berdasarkan fungsi agregasi tertentu. Misal untuk memperoleh nilai rata-rata sales menurut product. mencari nilai tertinggi sales menurut shipment_mode dan sebaginya. GROUP BY juga dapat dilakukan secara bertingkat untuk bebeberapa kolom sekaligus.

Agregasi data menurut 1 kolom

Misal untuk memperoleh banyaknya penjualan untuk masing-masing shipment_mode:

SQL

-- Grouping data menurut shipment_mode
SELECT 
   shipment_mode AS Mode,
   COUNT(sales_id) AS Jumlah
FROM sales_log
GROUP BY shipment_mode
# OUTPUT

Mode    Jumlah
FRS	    499
SAM	    163
SEC	    568
STD	   1773

Contoh lainnya, untuk memperoleh total quantity dan sales menurut segment:

SQL

-- Grouping data menurut segment
SELECT
   segment,
   SUM(quantity) AS tot_qty,
   ROUND(SUM(sales), 2) AS tot_sales
FROM sales_log
GROUP BY segment
ORDER BY segment DESC
# OUTPUT

segment  tot_qty   tot_sales
HOM	    2147	145511.87
COR	    3579	269036.12
CON	    5853	401222.91

Agregasi data menurut 2 kolom

SQL

-- Grouping data menurut shipment_mode dan payment_mode
SELECT
   shipment_mode,
   payment_mode,
   ROUND(AVG(sales), 2) AS avg_sales
FROM sales_log
GROUP BY shipment_mode, payment_mode
# OUTPUT

shipment_mode  payment_mode avg_sales
FRS	        CD 	         239.72
FRS	        COD	         220.04
FRS	        OL 	         314.03
SAM	        CD 	         208.26
SAM	        COD	         334.36
SAM	        OL 	         378.92
SEC	        CD 	         268.25
SEC	        COD	         276.24
SEC	        OL 	         258.65
STD	        CD 	         264.27
STD	        COD	         287.76
STD	        OL 	         258.06

Filter Hasil Grup dengan HAVING

Hasil pengelompokan menggunakan GROUP BY dapat di filter dengan menambahkan klausul HAVING.

SQL

-- Grouping data menurut shipment_mode dan payment_mode
SELECT
   shipment_mode,
   payment_mode,
   ROUND(AVG(sales), 2) AS avg_sales
FROM sales_log
GROUP BY shipment_mode, payment_mode
HAVING avg_sales > 300
# OUTPUT

shipment_mode  payment_mode avg_sales
FRS	        OL 	         314.03
SAM	        COD	         334.36
SAM	        OL 	         378.92

Kombinasi Tabel (JOIN)

Klausul JOIN dapat digunakan untuk mengombinasikan data dari dua tabel atau lebih. Terdapat beberapa mode penggunaan klausul JOIN seperti yang diilustrasikan pada gambar di bawah ini.

SQL Query SELECT JOIN
Sumber: ginacostag

Contoh INNER JOIN (1)

Pada database ini, terdapat tabel shipment yang terdiri dari dua kolom yaitu shipment_id dan description. Pada bagian sebelumnya, kita hanya menampilkan data menggunakan shipment_id saja ( pada tabel sales_log yaitu kolom shipment_mode). Dengan mengambil informasi dari tabel shipment kita dapat menampilkan hasil yang lebih informatif.

SQL

-- Inner Join
SELECT 
    A.sales_id, 
    A.shipment_mode, 
    B.descriptions, 
    ROUND(A.sales) as sales
FROM sales_log A
INNER JOIN shipment B 
ON A.shipment_mode = B.shipment_id
LIMIT 10
# OUTPUT

sales_id          shipment_mode   Descriptions         sales       
CA-2019-160304	STD	         Standard Class	   73.94
CA-2019-125206	FRS	         First Class	     114.46
US-2019-116365	STD	         Standard Class	   30.08
CA-2019-105207	STD	         Standard Class	 1592.85
US-2019-164630	STD	         Standard Class	  959.97
CA-2019-158211	STD	         Standard Class	    4.67 
CA-2019-134474	SEC	         Second Class	    191.47
CA-2019-101938	STD	         Standard Class	   34.58
CA-2019-158806	STD	         Standard Class	   23.08
US-2019-100461	STD	         Standard Class	 1565.88

Contoh INNER JOIN (2)

Pada data base juga terdapat tabel category yang terdiri dari dua kolom yaitu category_id dan descriptions. Selanjutnya terdapat tabel product yang terdiri dari 3 kolom yaitu cat_id (foreign key dari category_id), product_id dan descriptions. Kita dapat menampilkan deskripsi kategori dan produk secara lengkap dengan menggabungkan kedua tabel. (Karena category.category_id sama dengan product.cat_id maka pada contoh di bawah ini hanya kita tampilkan saja dari tabel category)

SQL

-- Inner Join
SELECT A.*, B.product_id, B.descriptions 
FROM category A
INNER JOIN product B
ON A.category_id=B.cat_id
# OUTPUT

category_id descriptions       product_id   Descriptions      
FURN	    Furniture	      FURN-01	  Bookcases
FURN	    Furniture	      FURN-02	  Chairs
FURN	    Furniture	      FURN-03	  Tables
FURN	    Furniture	      FURN-04	  Furnishings
OFFS	    Office Supplies	OFFS-01	  Storage
OFFS	    Office Supplies	OFFS-02	  Binders
OFFS	    Office Supplies	OFFS-03	  Art
OFFS	    Office Supplies	OFFS-04	  Paper
OFFS	    Office Supplies	OFFS-05	  Envelopes
OFFS	    Office Supplies	OFFS-06	  Fasteners
OFFS	    Office Supplies	OFFS-07	  Appliances
OFFS	    Office Supplies	OFFS-08	  Labels
OFFS	    Office Supplies	OFFS-09	  Supplies
TECH	    Technology	     TECH-01	  Copiers
TECH	    Technology	     TECH-02	  Machines
TECH	    Technology	     TECH-03	  Phones
TECH	    Technology	     TECH-04	  Accessories

Contoh INNER JOIN (3)

Pada query di bawah ini, kita menampilkan agregasi total quantity dan total sales berdasarkan payment. Karena pada tabel sales hanya terdapat kolom payment_mode (berisi kode payment_id), sehingga kita perlu mendapatkan deskripsinya dengan melakukan JOIN tabel payment. Meskipun secara khusus pada contoh di bawah ini kita menggunakan LEFT JOIN, namun penggunaan INNER JOIN juga akan memberikan hasil yang sama.

SQL

-- Left Join
SELECT 
   A.payment_id, A.descriptions, 
   SUM(B.quantity) AS Tot_Qty,
   ROUND(SUM(B.sales), 2) AS Tot_Sales
FROM payment A
LEFT JOIN sales_log B
ON A.payment_id = B.payment_mode
GROUP BY A.payment_id, A.descriptions
ORDER BY Tot_Sales
# OUTPUT

payment_id  descriptions             Tot_qty    Tot_sales
CD	      Card (Credit or Debit)	  2460	165643.94
OL	      Online	                  4192	298523.88
COD	     Cash on Delevery	        4927	351603.07

Ringkasan

Dalam tutorial ini, kita telah membahas berbagai aspek SQLite, khususnya untuk mengambil data menggunakan query SELECT. Kita juga mengeksplorasi berbagai klasul seperti pengurutan dengan ORDER BY, filter data dengan WHERE hingga menggabungkan beberapa tabel menggunakan JOIN. Dengan pemahaman ini, semoga menjadi dasar yang kuat untuk melakukan berbagai operasi dan manipulasi data dengan query SELECT pada SQLite secara efektif.

Tulisan Lainnya

Tags:

Cahya Alkahfi

Data Engineer di BPS-Statistics Indonesia dan Konsultan Senior di Educativa.id. Aktif membagikan tulisan seputar sains data dan statistika di sainsdata.id

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

→ Daftar Isi