Contekan Query SQL dari SELECT sampai Window Function

Mengambil kolom

Contoh memakai dialek SQLite. Teks dan tanggal ditulis di antara kutip tunggal; kutip ganda menandai nama kolom.

• SELECT * FROM tabelLatih: SELECT: mengambil isi tabel

  Mengambil semua kolom dan semua baris satu tabel.

  SELECT * FROM produk;

• SELECT kolom1, kolom2Latih: Memilih kolom tertentu

  Mengambil kolom tertentu saja; urutan kolom di hasil mengikuti urutan penulisannya.

  SELECT nama, kota FROM pelanggan;

• ASLatih: Mengganti nama kolom dengan AS

  Memberi nama lain pada kolom hasil. Nama kolom di tabel asalnya tidak berubah.

  SELECT nama AS pelanggan FROM pelanggan;

• DISTINCTLatih: DISTINCT dan LIMIT

  Membuang baris hasil yang sama persis. Yang dibandingkan seluruh baris hasil, bukan satu kolom saja.

  SELECT DISTINCT status FROM pesanan;

• LIMIT nLatih: DISTINCT dan LIMIT

  Membatasi jumlah baris yang dikembalikan; ditulis paling akhir.

  SELECT * FROM produk LIMIT 3;

Menyaring baris

• WHERE = <> < > <= >=Latih: WHERE dengan perbandingan

  Menyisakan baris yang syaratnya benar. Angka ditulis tanpa pemisah ribuan.

  SELECT nama, stok FROM produk WHERE stok < 50;

• AND / ORLatih: Teks, AND, dan OR

  AND menuntut semua syarat, OR cukup salah satunya. AND diperiksa lebih dulu; tanda kurung memaksa urutan lain.

  WHERE kota = 'Jakarta' OR kota = 'Bandung'

• LIKELatih: LIKE dan IN

  Mencocokkan pola teks: % berarti nol karakter atau lebih, _ tepat satu karakter.

  WHERE nama LIKE 'Pulpen%'

• IN (…)Latih: LIKE dan IN

  Benar bila nilainya sama dengan salah satu isi daftar; pengganti beberapa = yang digabung OR.

  WHERE kota IN ('Medan', 'Makassar')

• BETWEEN a AND bLatih: BETWEEN dan IS NULL

  Rentang nilai dengan kedua batas ikut terhitung. Batas kecil ditulis lebih dulu, atau hasilnya kosong.

  WHERE stok BETWEEN 20 AND 60

• IS NULL / IS NOT NULLLatih: NULL: nilai yang belum ada

  Memeriksa sel yang belum terisi. Syarat = NULL tidak pernah benar, sehingga hasilnya selalu kosong.

  WHERE kota IS NULL

Mengurutkan dan meringkas

Urutan kerjanya: FROM, WHERE, GROUP BY, HAVING, ORDER BY, lalu LIMIT.

• ORDER BY … DESCLatih: Mengurutkan dengan ORDER BY

  Mengurutkan hasil. ASC (kecil ke besar) berlaku bila arahnya tidak ditulis; DESC dari besar ke kecil.

  ORDER BY stok DESC

• ORDER BY a ASC, b DESCLatih: ORDER BY dengan dua kolom

  Kolom kedua hanya mengurutkan baris yang nilai kolom pertamanya sama. Tiap kolom punya arahnya sendiri.

  ORDER BY kota ASC, nama DESC

• COUNT / SUM / AVG / MIN / MAXLatih: COUNT, SUM, AVG, MIN, MAX

  Meringkas banyak baris menjadi satu nilai. Tanpa GROUP BY, hasilnya selalu satu baris.

  SELECT MIN(harga), MAX(harga), AVG(harga) FROM produk;

• COUNT(*) vs COUNT(kolom)Latih: Agregat pada kolom yang ada kosongnya

  COUNT(*) menghitung semua baris; COUNT(kolom) hanya yang kolomnya terisi. Selisihnya adalah jumlah sel kosong.

  SELECT COUNT(*) AS baris, COUNT(kota) AS kota_terisi FROM pelanggan;

• GROUP BYLatih: Mengelompokkan dengan GROUP BY

  Memecah baris menjadi kelompok dan menghitung agregat per kelompok: satu baris hasil per kelompok.

  SELECT kota, COUNT(*) FROM pelanggan GROUP BY kota;

• HAVINGLatih: Menyaring kelompok dengan HAVING

  Menyaring kelompok sesudah agregatnya dihitung; WHERE menyaring baris sebelum dikelompokkan.

  GROUP BY kota HAVING COUNT(*) >= 2

Menggabungkan tabel

• JOIN … ONLatih: INNER JOIN

  Memasangkan baris dua tabel menurut syarat ON. JOIN tanpa keterangan berarti INNER JOIN: baris tanpa pasangan tidak ikut.

  FROM pesanan JOIN pelanggan ON pelanggan.id = pesanan.pelanggan_id

• FROM tabel AS tLatih: Alias tabel dan self-join

  Alias tabel: nama pendek yang berlaku selama kueri. Dua alias untuk satu tabel menghasilkan self-join.

  FROM produk AS a JOIN produk AS b ON b.kategori = a.kategori AND b.harga > a.harga

• LEFT JOINLatih: LEFT JOIN

  Menahan semua baris tabel kiri dan mengisi kolom tabel kanan dengan NULL bila pasangannya tidak ada — jawaban untuk pertanyaan "mana yang belum pernah".

  FROM produk LEFT JOIN detail_pesanan ON detail_pesanan.produk_id = produk.id

• UNION ALLLatih: Subtotal dan total ala ROLLUP

  Menumpuk hasil beberapa SELECT berkolom sama menjadi satu; di SQLite dipakai sebagai padanan ROLLUP untuk subtotal dan total.

• INSERT INTO … SELECTLatih: ETL: staging, transformasi, dan INSERT…SELECT

  Menyisipkan hasil sebuah SELECT ke tabel lain, misalnya baris staging yang belum ada di tabel dimensi.

Logika, subkueri, dan CTE

• COALESCE(a, b, …)Latih: Mengganti NULL dengan COALESCE

  Nilai pertama yang tidak NULL dari kiri; dipakai menulis keterangan pengganti untuk sel kosong.

  COALESCE(kota, 'Tidak diisi') AS kota

• CASE WHEN … THEN … ELSE … ENDLatih: Membuat kategori dengan CASE

  Mengubah nilai menjadi kategori. Syarat diperiksa dari atas dan berhenti di yang pertama benar; ELSE untuk sisanya.

  CASE WHEN jumlah >= 20 THEN 'grosir' WHEN jumlah >= 5 THEN 'partai' ELSE 'eceran' END

• SUM(CASE WHEN … THEN 1 ELSE 0 END)Latih: Menghitung bersyarat dengan CASE

  Hitungan bersyarat sebagai kolom: beberapa kategori muncul berdampingan dalam satu baris hasil.

  SUM(CASE WHEN status = 'batal' THEN 1 ELSE 0 END) AS batal

• WHERE x IN (SELECT …)Latih: Subkueri dengan IN

  Subkueri yang menghasilkan daftar nilai sebagai syarat. NOT IN membalik pertanyaannya menjadi "yang belum".

  WHERE id IN (SELECT produk_id FROM detail_pesanan WHERE pesanan_id = 3)

• WHERE x < (SELECT AVG(x) …)Latih: Subkueri yang menghasilkan satu nilai

  Subkueri skalar: satu nilai ringkasan yang dibandingkan dengan setiap baris dan dihitung ulang tiap kueri dijalankan.

  WHERE stok < (SELECT AVG(stok) FROM produk)

• WITH nama AS (…)Latih: Menamai hasil antara dengan WITH

  CTE: hasil antara bernama yang dipakai seperti tabel di kueri utamanya. Beberapa CTE dipisah koma.

  WITH elektronik AS (SELECT * FROM produk WHERE kategori = 'Elektronik') SELECT nama FROM elektronik;

Fungsi SQLite

• date(tanggal, '+7 day')Latih: Fungsi tanggal SQLite

  Tanggal baru dari tanggal lama: '+1 month' menambah sebulan, 'start of month' mundur ke tanggal satu.

  SELECT date(tanggal, '+7 day') AS jatuh_tempo FROM pesanan;

• strftime('%Y-%m', tanggal)Latih: Fungsi tanggal SQLite

  Bagian tanggal sebagai teks: %Y tahun, %m bulan dua digit, %d tanggal. '%Y-%m' menjadi kolom periode untuk rekap bulanan.

• upper / lower / substr / lengthLatih: Fungsi teks dan angka SQLite

  Menyeragamkan huruf, mengambil potongan teks (posisi pertama bernomor 1), dan menghitung panjangnya.

  SELECT substr(nama, 1, 1) AS inisial FROM pelanggan;

• round(x, n) / printf()Latih: Fungsi teks dan angka SQLite

  Membulatkan ke n angka di belakang koma, dan menyusun teks berformat seperti label Rupiah.

  SELECT printf('Rp%d', harga) AS label FROM produk;

Window function

Window function menghitung nilai untuk setiap baris tanpa meringkasnya; penandanya OVER.

• ROW_NUMBER() OVER (PARTITION BY … ORDER BY …)Latih: Peringkat dengan ROW_NUMBER, RANK, dan NTILE

  Nomor urut 1, 2, 3 di dalam tiap kelompok PARTITION BY, menurut ORDER BY di dalam kurungnya.

  ROW_NUMBER() OVER (PARTITION BY kelas ORDER BY skor DESC)

• RANK() / DENSE_RANK()Latih: Peringkat dengan ROW_NUMBER, RANK, dan NTILE

  Peringkat yang memberi nilai kembar nomor sama. RANK melompati nomor sesudah nilai kembar; DENSE_RANK tidak.

• NTILE(n)Latih: Peringkat dengan ROW_NUMBER, RANK, dan NTILE

  Membagi baris yang sudah diurutkan menjadi n kelompok berukuran hampir sama, misalnya segmen pelanggan.

• LAG() / LEAD()Latih: Membandingkan dengan baris sebelumnya: LAG dan LEAD

  Nilai dari baris sebelum atau sesudahnya menurut ORDER BY. Baris pertama tidak punya pendahulu, jadi LAG di sana NULL.

  derajat - LAG(derajat) OVER (PARTITION BY kota ORDER BY tanggal) AS perubahan

• SUM() OVER (ORDER BY …)Latih: Total berjalan dan rata-rata bergerak

  Total berjalan: jumlah dari baris pertama sampai baris yang sedang dihitung. OVER () kosong memberi total keseluruhan di setiap baris.

  SUM(masuk) OVER (ORDER BY tanggal) AS saldo

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROWLatih: Total berjalan dan rata-rata bergerak

  Frame: dua baris sebelumnya ditambah baris ini — dasar rata-rata bergerak tiga baris.

  AVG(masuk) OVER (ORDER BY tanggal ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)