SQLite Kueri: Pilih, Dimana, LIMIT, OFFSET, Hitung, Kelompokkan Berdasarkan

Bagian-bagian di bawah ini menjelaskan cara membaca data dengan SELECT dan FROM, memfilter baris dengan WHERE dan operatornya, mengurutkan dan membatasi hasil, fungsi agregasi, GROUP BY dan HAVING, subkueri, operasi himpunan, penanganan NULL, ekspresi CASE, dan ekspresi tabel umum.

Untuk menulis query SQL di SQLite database, Anda harus mengetahui cara kerja klausa SELECT, FROM, WHERE, GROUP BY, ORDER BY, dan LIMIT serta cara menggunakannya.

Selama tutorial ini, Anda akan mempelajari cara menggunakan klausa ini dan cara menulisnya SQLite klausa.

Membaca Data dengan Pilih

Klausa SELECT adalah pernyataan utama yang Anda gunakan untuk menanyakan sebuah SQLite basis data. Dalam klausa SELECT, Anda menyatakan apa yang harus dipilih. Namun sebelum klausa pilih, mari kita lihat dari mana kita dapat memilih data menggunakan klausa FROM.

Klausa FROM digunakan untuk menentukan di mana Anda ingin memilih data. Dalam klausa from, Anda dapat menentukan satu atau beberapa tabel atau subquery untuk memilih data, seperti yang akan kita lihat nanti di tutorial.

Perhatikan bahwa, untuk semua contoh berikut, Anda harus menjalankan sqlite3.exe dan membuka koneksi ke database contoh seperti yang mengalir:

Langkah 1) Pada langkah ini,

Buka Komputer Saya dan navigasikan ke direktori berikut “C:\sqlite” lalu jalankan “sqlite3.exe“:

Langkah 2) Buka basis data “TutorialsSampleDB.db” dengan perintah berikut:

Sekarang Anda siap menjalankan semua jenis kueri pada database.

Dalam klausa SELECT, Anda tidak hanya dapat memilih nama kolom, tetapi Anda memiliki banyak opsi lain untuk menentukan apa yang akan dipilih. Sebagai berikut:

SELECT *

Perintah ini akan memilih semua kolom dari semua tabel yang direferensikan (atau subkueri) dalam klausa FROM. Misalnya:

SELECT *
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Ini akan memilih semua kolom dari tabel siswa dan tabel departemen:

PILIH nama tabel.*

Ini akan memilih semua kolom hanya dari tabel “nama tabel”. Misalnya:

SELECT Students.*
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Ini akan memilih semua kolom dari tabel siswa saja:

Nilai literal

Nilai literal adalah nilai konstan yang dapat ditentukan dalam pernyataan pilih. Anda dapat menggunakan nilai literal secara normal dengan cara yang sama seperti Anda menggunakan nama kolom dalam klausa SELECT. Nilai literal ini akan ditampilkan untuk setiap baris dari baris yang dikembalikan oleh kueri SQL.

Berikut beberapa contoh nilai literal berbeda yang dapat Anda pilih:

• Literal Numerik – angka dalam format apa pun seperti 1, 2.55,… dll.
• Literal string – String apa pun 'AS', 'ini adalah contoh teks', … dll.
• BATAL – nilai BATAL.
• Current_TIME – Ini akan memberi Anda waktu saat ini.
• CURRENT_DATE – ini akan memberi Anda tanggal saat ini.

Ini bisa berguna dalam beberapa situasi di mana Anda harus memilih nilai konstan untuk semua baris yang dikembalikan. Misalnya, jika Anda ingin memilih semua siswa dari tabel Siswa, dengan kolom baru bernama negara yang berisi nilai “USA”, Anda dapat melakukan ini:

SELECT *, 'USA' AS Country FROM Students;

Ini akan memberi Anda semua kolom siswa, ditambah kolom baru “Negara” seperti ini:

Perhatikan bahwa kolom baru Negara ini sebenarnya bukan kolom baru yang ditambahkan ke tabel. Ini adalah kolom virtual, dibuat dalam kueri untuk menampilkan hasil dan tidak akan dibuat di tabel.

Nama dan Alias

Alias ​​adalah nama baru untuk kolom yang memungkinkan Anda memilih kolom dengan nama baru. Alias ​​kolom ditentukan menggunakan kata kunci “AS”.

Misalnya, jika Anda ingin memilih kolom Nama Siswa untuk dikembalikan dengan “Nama Siswa” dan bukan “Nama Siswa”, Anda dapat memberinya alias seperti ini:

SELECT StudentName AS 'Student Name' FROM Students;

Ini akan memberi Anda nama siswa dengan nama “Nama Siswa” dan bukan “Nama Siswa” seperti ini:

Perhatikan bahwa nama kolom tetap "StudentName"; kolom StudentName tetap sama, tidak berubah karena alias.

Alias ​​​​tidak akan mengubah nama kolom; itu hanya akan mengubah nama tampilan di klausa SELECT.

Perhatikan juga bahwa, kata kunci “AS” adalah opsional, Anda dapat memasukkan nama alias tanpa itu, kira-kira seperti ini:

SELECT StudentName 'Student Name' FROM Students;

Dan itu akan memberi Anda keluaran yang sama persis dengan kueri sebelumnya:

Anda juga bisa memberikan alias tabel, bukan hanya kolom. Dengan kata kunci yang sama “AS”. Misalnya, Anda dapat melakukan ini:

SELECT s.* FROM Students AS s;

Ini akan memberi Anda semua kolom di tabel Siswa:

Ini bisa sangat berguna jika Anda menggabungkan lebih dari satu tabel; daripada mengulang nama tabel lengkap dalam kueri, Anda dapat memberi setiap tabel nama alias singkat. Misalnya, dalam kueri berikut:

SELECT Students.StudentName, Departments.DepartmentName
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Query ini akan memilih setiap nama mahasiswa dari tabel “Mahasiswa” beserta nama jurusannya dari tabel “Departemen”:

Namun, kueri yang sama dapat ditulis seperti ini:

SELECT s.StudentName, d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Kami memberi tabel Mahasiswa alias “s” dan tabel Departemen alias “d”. Kemudian, alih-alih menggunakan nama tabel lengkap, kami menggunakan aliasnya untuk merujuk ke tabel tersebut. INNER JOIN menggabungkan dua atau lebih tabel menggunakan suatu kondisi. Dalam contoh kami, kami menggabungkan tabel Mahasiswa dengan tabel Departemen menggunakan kolom DepartmentId. Ada juga penjelasan mendalam tentang INNER JOIN di bagian “SQLite Bergabung” tutorial.

Ini akan memberi Anda keluaran persis seperti kueri sebelumnya:

MANA

Menulis query SQL menggunakan klausa SELECT saja dengan klausa FROM seperti yang kita lihat di bagian sebelumnya, akan memberi Anda semua baris dari tabel. Namun, jika Anda ingin memfilter data yang dikembalikan, Anda harus menambahkan klausa “WHERE”.

Klausul WHERE digunakan untuk memfilter hasil yang dikembalikan oleh kueri SQL. Begini cara kerja klausa WHERE:

• Dalam klausa WHERE, Anda dapat menentukan “ekspresi”.
• Ekspresi tersebut akan dievaluasi untuk setiap baris yang dikembalikan dari tabel yang ditentukan dalam klausa FROM.
• Ekspresi tersebut akan dievaluasi sebagai ekspresi Boolean, dengan hasil benar, salah, atau nol.
• Kemudian hanya baris yang ekspresinya dievaluasi dengan nilai sebenarnya yang akan dikembalikan, dan baris dengan hasil salah atau nol akan diabaikan dan tidak disertakan dalam kumpulan hasil.

Untuk memfilter kumpulan hasil menggunakan klausa WHERE, Anda harus menggunakan ekspresi dan operator.

Daftar operator di SQLite dan cara menggunakannya

Pada bagian berikut, kami akan menjelaskan cara memfilter menggunakan ekspresi dan operator.

Ekspresi adalah satu atau lebih nilai literal atau kolom yang digabungkan satu sama lain dengan operator.

Perhatikan bahwa, Anda dapat menggunakan ekspresi dalam klausa SELECT dan klausa WHERE.

Dalam contoh berikut, kita akan mencoba ekspresi dan operator dalam klausa select dan klausa WHERE. Untuk menunjukkan cara kerjanya.

Ada berbagai jenis ekspresi dan operator yang dapat Anda tentukan sebagai berikut:

SQLite operator penggabungan “||”

Operator ini digunakan untuk menggabungkan satu atau lebih nilai atau kolom literal satu sama lain. Ini akan menghasilkan satu string hasil dari semua nilai atau kolom literal yang digabungkan. Misalnya:

SELECT 'Id with Name: '|| StudentId || StudentName AS StudentIdWithName
FROM Students;

Ini akan digabungkan menjadi alias baru “StudentIdWithName“:

• Nilai string literal “ID dengan Nama: “
• dengan nilai kolom “StudentId” dan
• dengan nilai dari kolom “StudentName”

SQLite Operator CAST:

Operator CAST digunakan untuk mengkonversi nilai dari tipe data ke tipe data lain.

Misalnya, jika Anda memiliki nilai numerik yang disimpan sebagai nilai string seperti ini ” '12.5' ​​” dan Anda ingin mengubahnya menjadi nilai numerik, Anda dapat menggunakan operator CAST untuk melakukannya seperti ini “CAST( '12.5' ​​AS REAL)“. Atau jika Anda memiliki nilai desimal seperti 12.5, dan Anda hanya perlu mendapatkan bagian bilangan bulatnya saja, Anda dapat mengubahnya menjadi bilangan bulat seperti ini “CAST(12.5 AS INTEGER)”.

Example

Dalam perintah berikut kita akan mencoba mengonversi nilai yang berbeda ke tipe data lain:

SELECT CAST('12.5' AS REAL) ToReal, CAST(12.5 AS INTEGER) AS ToInteger;

Ini akan memberi Anda:

Hasilnya adalah sebagai berikut:

• CAST('12.5' ​​AS REAL) – nilai '12.5' ​​adalah nilai string, akan dikonversi ke nilai REAL.
• CAST(12.5 AS INTEGER) – nilai 12.5 merupakan nilai desimal, akan diubah menjadi nilai integer. Bagian desimalnya akan terpotong dan menjadi 12.

SQLite Hitung Operatorsi:

Ambil dua atau lebih nilai literal numerik atau kolom numerik dan kembalikan satu nilai numerik. Operator aritmatika yang didukung dalam SQLite adalah:

• Penambahan “+” – memberikan jumlah dari kedua operan.
• Subtraction “–” – subtrackedua operand dan menghasilkan selisihnya.
• Perkalian “*” – hasil perkalian dari kedua operan.
• Pengingat (modulo) “%” – menunjukkan sisa yang dihasilkan dari pembagian satu operan dengan operan kedua.
• Pembagian “/” – mengembalikan hasil bagi dari pembagian operan kiri dengan operan kanan.

Contoh:

Pada contoh berikut, kita akan mencoba lima operator aritmatika dengan nilai numerik literal dalam klausa select yang sama:

SELECT 25+6, 25-6, 25*6, 25%6, 25/6;

Ini akan memberi Anda:

Perhatikan bagaimana kita menggunakan pernyataan SELECT tanpa klausa FROM di sini. Dan ini diperbolehkan masuk SQLite selama kita memilih nilai literal.

SQLite Operator perbandingan

Bandingkan dua operan satu sama lain dan kembalikan nilai benar atau salah sebagai berikut:

• “<” – mengembalikan nilai true jika operand kiri lebih kecil dari operand kanan.
• “<=” – mengembalikan nilai true jika operand kiri kurang dari atau sama dengan operand kanan.
• ">" – mengembalikan nilai true jika operand kiri lebih besar dari operand kanan.
• ">=" – mengembalikan nilai true jika operand kiri lebih besar dari atau sama dengan operand kanan.
• “=” dan “==” – mengembalikan nilai true jika kedua operand sama. Perhatikan bahwa kedua operator tersebut sama, dan tidak ada perbedaan di antara keduanya.
• “!=” dan “<>” – mengembalikan nilai true jika kedua operand tidak sama. Perhatikan bahwa kedua operator tersebut sama, dan tidak ada perbedaan di antara keduanya.

Perhatikan bahwa, SQLite menyatakan nilai sebenarnya dengan 1 dan nilai salah dengan 0.

Contoh:

SELECT
  10<6 AS '<', 10<=6 AS '<=',
  10>6 AS '>', 10>=6 AS '>=',
  10=6 AS '=', 10==6 AS '==',
  10!=6 AS '!=', 10<>6 AS '<>';

Ini akan memberikan sesuatu seperti ini:

SQLite Operator Pencocokan Pola

“LIKE” – digunakan untuk pencocokan pola. Dengan menggunakan “Like”, Anda dapat mencari nilai yang cocok dengan pola yang ditentukan menggunakan karakter pengganti (wildcard).

Operan di sebelah kiri dapat berupa nilai literal string atau kolom string. Polanya dapat ditentukan sebagai berikut:

• Berisi pola. Misalnya, StudentName LIKE '%a%' – ini akan mencari nama siswa yang mengandung huruf “a” di posisi mana pun pada kolom StudentName.
• Dimulai dengan pola. Misalnya, “StudentName LIKE 'a%'” – cari nama siswa yang diawali dengan huruf “a”.
• Berakhir dengan pola tersebut. Misalnya, “StudentName LIKE '%a'” – Mencari nama siswa yang berakhiran huruf “a”.
• Mencocokkan karakter tunggal apa pun dalam sebuah string menggunakan huruf garis bawah “_”. Misalnya, “StudentName LIKE 'J___'” – Mencari nama siswa yang panjangnya 4 karakter. Nama tersebut harus diawali dengan huruf “J” dan dapat memiliki tiga karakter lain setelah huruf “J”.

Contoh pencocokan pola:

Dapatkan nama Siswa yang dimulai dengan huruf 'j':

SELECT StudentName FROM Students WHERE StudentName LIKE 'j%';

Hasil:

Dapatkan nama Siswa diakhiri dengan huruf 'y':

SELECT StudentName FROM Students WHERE StudentName LIKE '%y';

Hasil:

Dapatkan nama Siswa yang mengandung huruf 'n':

SELECT StudentName FROM Students WHERE StudentName LIKE '%n%';

Hasil:

“GLOB” – setara dengan operator LIKE, tetapi GLOB peka terhadap huruf besar dan kecil, tidak seperti operator LIKE. Misalnya, dua perintah berikut akan menghasilkan hasil yang berbeda:

SELECT 'Jack' GLOB 'j%';
SELECT 'Jack' LIKE 'j%';

Ini akan memberi Anda:

Pernyataan pertama mengembalikan 0(false) karena operator GLOB peka terhadap huruf besar-kecil, jadi 'j' tidak sama dengan 'J'. Namun, pernyataan kedua akan menghasilkan 1 (benar) karena operator LIKE tidak peka huruf besar-kecil, jadi 'j' sama dengan 'J'.

Operator lain:

SQLite DAN

Operator logika yang menggabungkan satu atau lebih ekspresi. Ini akan mengembalikan nilai benar, hanya jika semua ekspresi menghasilkan nilai “benar”. Namun, ini akan mengembalikan false hanya jika semua ekspresi menghasilkan nilai “false”.

Contoh:

Kueri berikut akan mencari siswa yang memiliki StudentId > 5 dan StudentName dimulai dengan huruf N, siswa yang dikembalikan harus memenuhi dua kondisi:

SELECT *
FROM Students
WHERE (StudentId > 5) AND (StudentName LIKE 'N%');

Sebagai hasilnya, pada gambar di atas, ini hanya akan memberi Anda "Nancy". Nancy adalah satu-satunya siswa yang memenuhi kedua syarat tersebut.

SQLite OR

Operator logika yang menggabungkan satu atau lebih ekspresi, sehingga jika salah satu operator gabungan menghasilkan nilai benar, maka operator tersebut akan mengembalikan nilai benar. Namun, jika semua ekspresi menghasilkan nilai salah, maka hasilnya akan salah.

Contoh:

Kueri berikut akan mencari siswa yang memiliki StudentId > 5 atau StudentName dimulai dengan huruf N, siswa yang dikembalikan harus memenuhi setidaknya satu kondisi berikut:

SELECT *
FROM Students
WHERE (StudentId > 5) OR (StudentName LIKE 'N%');

Ini akan memberi Anda:

Sebagai output, pada gambar di atas, ini akan memberi Anda nama siswa yang memiliki huruf “n” di namanya ditambah id siswa yang bernilai>5.

Seperti yang Anda lihat, hasilnya berbeda dengan kueri dengan operator AND.

SQLite ANTARA

BETWEEN digunakan untuk memilih nilai-nilai yang berada dalam rentang dua nilai. Misalnya, “X BETWEEN Y AND Z” akan mengembalikan nilai true (1) jika nilai X berada di antara dua nilai Y dan Z. Jika tidak, akan mengembalikan nilai false (0). “X BETWEEN Y AND Z” setara dengan “X >= Y AND X <= Z”, di mana X harus lebih besar dari atau sama dengan Y dan X kurang dari atau sama dengan Z.

Contoh:

Pada contoh query berikut, kita akan menulis query untuk mendapatkan siswa dengan nilai Id antara 5 dan 8:

SELECT *
FROM Students
WHERE StudentId BETWEEN 5 AND 8;

Ini hanya akan memberikan siswa dengan id 5, 6, 7, dan 8:

SQLite IN

Mengambil satu operan dan daftar operan. Operator ini akan mengembalikan true jika nilai operan pertama sama dengan salah satu nilai operan dari daftar. Operator IN mengembalikan true (1) jika daftar operan berisi nilai operan pertama di dalam nilainya. Jika tidak, operator ini akan mengembalikan false (0).

Seperti ini: “col IN(x, y, z)“. Ini setara dengan ” (col=x) atau (col=y) atau (col=z) ”.

Contoh:

Kueri berikut akan memilih siswa dengan id 2, 4, 6, 8 saja:

SELECT *
FROM Students
WHERE StudentId IN(2, 4, 6, 8);

Seperti ini:

Kueri sebelumnya akan memberikan hasil yang sama persis dengan kueri berikut karena keduanya ekuivalen:

SELECT *
FROM Students
WHERE (StudentId = 2) OR (StudentId =  4) OR (StudentId =  6) OR (StudentId = 8);

Kedua kueri memberikan keluaran yang tepat. Namun yang membedakan kedua query tersebut adalah, query pertama kita menggunakan operator “IN”. Pada kueri kedua, kami menggunakan beberapa operator “ATAU”.

Operator IN setara dengan menggunakan beberapa operator OR. “WHERE StudentId IN(2, 4, 6, 8)” setara dengan ” WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8);“

Seperti ini:

SQLite TIDAK MASUK

Operator “NOT IN” adalah kebalikan dari operator IN. Namun dengan sintaks yang sama; ia menerima satu operand dan daftar operand. Ia akan mengembalikan nilai true jika nilai operand pertama tidak sama dengan nilai salah satu operand dalam daftar. Artinya, ia akan mengembalikan nilai true (0) jika daftar operand tidak mengandung operand pertama. Seperti ini: “col NOT IN(x, y, z)“. Ini setara dengan “(col<>x) AND (col<>y) AND (col<>z)“.

Contoh:

Kueri berikut akan memilih siswa dengan id yang tidak sama dengan salah satu dari Id ini 2, 4, 6, 8:

SELECT *
FROM Students
WHERE StudentId NOT IN(2, 4, 6, 8);

Begini

Pada query sebelumnya kita memberikan hasil yang sama persis dengan query berikut karena keduanya ekuivalen:

SELECT *
FROM Students
WHERE (StudentId <> 2) AND (StudentId <> 4) AND (StudentId <> 6) AND (StudentId <> 8);

Seperti ini:

Pada tangkapan layar di atas,

Kami menggunakan beberapa operator tidak sama dengan “<>” untuk mendapatkan daftar siswa yang tidak sama dengan salah satu Id berikut, yaitu 2, 4, 6, dan 8. Kueri ini akan mengembalikan semua siswa selain dari daftar Id ini.

SQLite ADA

Operator EXISTS tidak mengambil operan apa pun; hanya dibutuhkan klausa SELECT setelahnya. Operator EXISTS akan mengembalikan nilai true (1) jika ada baris yang dikembalikan dari klausa SELECT, dan akan mengembalikan false (0) jika tidak ada baris sama sekali yang dikembalikan dari klausa SELECT.

Contoh:

Dalam contoh berikut, kita akan memilih nama departemen, jika id departemen ada di tabel mahasiswa:

SELECT DepartmentName
FROM Departments AS d
WHERE EXISTS (SELECT DepartmentId FROM Students AS s WHERE d.DepartmentId = s.DepartmentId);

Ini akan memberi Anda:

Hanya tiga departemen “TI, Fisika, dan Seni” yang akan dikembalikan. Dan nama departemen “Matematika” tidak akan dikembalikan karena tidak ada mahasiswa di departemen tersebut, sehingga ID departemen tidak ada dalam tabel mahasiswa. Itulah mengapa operator EXISTS mengabaikan departemen “Matematika”.

SQLite JANGAN

Reverses adalah hasil dari operator sebelumnya yang datang setelahnya. Misalnya:

• BUKAN ANTARA – Ini akan mengembalikan nilai benar jika ANTARA mengembalikan salah dan sebaliknya.
• NOT LIKE – Ini akan mengembalikan nilai true jika LIKE mengembalikan false dan sebaliknya.
• BUKAN GLOB – Ini akan mengembalikan nilai true jika GLOB mengembalikan false dan sebaliknya.
• NOT EXISTS – Ini akan mengembalikan nilai true jika EXISTS mengembalikan false dan sebaliknya.

Contoh:

Dalam contoh berikut, kita akan menggunakan operator NOT dengan operator EXISTS untuk mendapatkan nama departemen yang tidak ada di tabel Students, yang merupakan hasil kebalikan dari operator EXISTS. Jadi, pencarian akan dilakukan melalui DepartmentId yang tidak ada di tabel department.

SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId
                  FROM Students AS s
                  WHERE d.DepartmentId = s.DepartmentId);

Keluaran:

Hanya departemen “Matematika” yang akan dikembalikan. Karena departemen “Matematika” adalah satu-satunya departemen yang tidak ada dalam tabel mahasiswa.

Membatasi dan Memesan

SQLite Memesan

SQLite Urutannya adalah mengurutkan hasil Anda berdasarkan satu atau lebih ekspresi. Untuk mengurutkan hasil yang ditetapkan, Anda harus menggunakan klausa ORDER BY sebagai berikut:

• Pertama, Anda harus menentukan klausa ORDER BY.
• Klausa ORDER BY harus ditentukan di akhir kueri; hanya klausa LIMIT yang dapat ditentukan setelahnya.
• Tentukan ekspresi untuk mengurutkan data, ekspresi ini bisa berupa nama kolom atau ekspresi.
• Setelah ekspresi, Anda dapat menentukan arah pengurutan opsional. Entah DESC untuk mengurutkan data secara descending, atau ASC untuk mengurutkan data secara menaik. Jika Anda tidak menentukannya, data akan diurutkan dalam urutan menaik.
• Anda dapat menentukan lebih banyak ekspresi menggunakan tanda “,” di antara satu sama lain.

Example

Dalam contoh berikut, kita akan memilih semua mahasiswa yang diurutkan berdasarkan nama mereka namun dalam urutan menurun, kemudian berdasarkan nama departemen dalam urutan menaik:

SELECT s.StudentName, d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
ORDER BY d.DepartmentName ASC , s.StudentName DESC;

Ini akan memberi Anda:

• SQLite pertama-tama akan mengurutkan semua siswa berdasarkan nama departemen mereka dalam urutan menaik
• Kemudian untuk setiap nama jurusan, semua mahasiswa di bawah nama jurusan tersebut akan ditampilkan dalam urutan menurun berdasarkan namanya

SQLite Membatasi:

Anda dapat membatasi jumlah baris yang dikembalikan oleh kueri SQL Anda, dengan menggunakan klausa LIMIT. Misalnya, LIMIT 10 hanya akan memberi Anda 10 baris dan mengabaikan semua baris lainnya.

Dalam klausa LIMIT, Anda dapat memilih sejumlah baris tertentu mulai dari posisi tertentu menggunakan klausa OFFSET. Misalnya, “LIMIT 4 OFFSET 4” akan mengabaikan 4 baris pertama, dan mengembalikan 4 baris mulai dari baris kelima, sehingga Anda akan mendapatkan baris 5, 6, 7, dan 8.

Perhatikan bahwa klausa OFFSET bersifat opsional, Anda dapat menuliskannya seperti “LIMIT 4, 4” dan itu akan memberikan hasil yang tepat.

Contoh:

Pada contoh berikut, kita akan mengembalikan hanya 3 siswa dimulai dari id siswa 5 dengan menggunakan query:

SELECT * FROM Students LIMIT 4,3;

Ini akan memberi Anda hanya tiga siswa mulai dari baris 5. Jadi ini akan memberi Anda baris dengan StudentId 5, 6, dan 7:

Menghapus duplikat

Jika kueri SQL Anda mengembalikan nilai duplikat, Anda dapat menggunakan kata kunci "DISTINCT" untuk menghapus duplikat tersebut dan hanya mengembalikan nilai yang unik. Anda dapat menentukan lebih dari satu kolom setelah kata kunci DISTINCT.

Contoh:

Kueri berikut akan mengembalikan nilai “nama departemen” duplikat: Di sini kita memiliki nilai duplikat dengan nama TI, Fisika, dan Seni.

SELECT d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Ini akan memberi Anda nilai duplikat untuk nama departemen:

Perhatikan bagaimana ada nilai duplikat untuk nama departemen. Sekarang, kita akan menggunakan kata kunci DISTINCT dengan kueri yang sama untuk menghapus duplikat tersebut dan hanya mendapatkan nilai unik. Seperti ini:

SELECT DISTINCT d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Ini hanya akan memberi Anda tiga nilai unik untuk kolom nama departemen:

Agregat

SQLite Agregat adalah fungsi bawaan yang didefinisikan di SQLite yang akan mengelompokkan beberapa nilai dari beberapa baris menjadi satu nilai.

Berikut adalah agregat yang didukung oleh SQLite:

SQLite AVG()

Mengembalikan rata-rata untuk semua nilai x.

Contoh:

Dalam contoh berikut ini kita akan mendapatkan nilai rata-rata yang diperoleh siswa dari semua ujian:

SELECT AVG(Mark) FROM Marks;

Ini akan memberi Anda nilai “18.375”:

Hasil ini diperoleh dari penjumlahan seluruh nilai nilai dibagi dengan hitungannya.

JUMLAH() – JUMLAH(X) atau JUMLAH(*)

Mengembalikan jumlah total berapa kali nilai x muncul. Dan berikut beberapa opsi yang dapat Anda gunakan dengan COUNT:

• COUNT(x): Hanya menghitung nilai x, dengan x adalah nama kolom. Ini akan mengabaikan nilai NULL.
• COUNT(*): Menghitung semua baris dari semua kolom.
• COUNT (DISTINCT x): Anda dapat menentukan kata kunci DISTINCT sebelum x yang akan menghitung nilai x yang berbeda.

Example

Dalam contoh berikut, kita akan mendapatkan jumlah total Departemen dengan COUNT(DepartmentId), COUNT(*), dan COUNT(DISTINCT DepartmentId) dan perbedaannya:

SELECT COUNT(DepartmentId), COUNT(DISTINCT DepartmentId), COUNT(*) FROM Students;

Ini akan memberi Anda:

Sebagai berikut:

• COUNT(DepartmentId) akan memberi Anda hitungan semua id departemen, dan akan mengabaikan nilai null.
• COUNT(DISTINCT DepartmentId) memberi Anda nilai berbeda dari DepartmentId, yang hanya berjumlah 3. Yang merupakan tiga nilai berbeda dari nama departemen. Perhatikan ada 8 nilai nama jurusan pada nama mahasiswa. Namun hanya tiga nilai yang berbeda yaitu Matematika, IT, dan Fisika.
• COUNT(*) menghitung banyaknya baris pada tabel siswa yang berjumlah 10 baris untuk 10 siswa.

GROUP_CONCAT() – GROUP_CONCAT(X) atau GROUP_CONCAT(X,Y)

Fungsi agregat GROUP_CONCAT menggabungkan beberapa nilai menjadi satu nilai dengan koma untuk memisahkannya. Fungsi ini memiliki opsi berikut:

• GROUP_CONCAT(X): Ini akan menggabungkan semua nilai x menjadi satu string, dengan koma “,” digunakan sebagai pemisah antar nilai. Nilai NULL akan diabaikan.
• GROUP_CONCAT(X, Y): Ini akan menggabungkan nilai x menjadi satu string, dengan nilai y digunakan sebagai pemisah antara setiap nilai, bukan pemisah default ','. Nilai NULL juga akan diabaikan.
• GROUP_CONCAT(DISTINCT X): Ini akan menggabungkan semua nilai x yang berbeda menjadi satu string, dengan koma “,” digunakan sebagai pemisah di antara nilai-nilai tersebut. Nilai NULL akan diabaikan.

GROUP_CONCAT(Nama Departemen) Contoh

Kueri berikut akan menggabungkan semua nilai nama departemen dari tabel mahasiswa dan departemen menjadi satu string yang dipisahkan koma. Jadi, alih-alih mengembalikan daftar nilai, satu nilai pada setiap baris. Kueri ini hanya akan mengembalikan satu nilai pada satu baris, dengan semua nilai dipisahkan koma:

SELECT GROUP_CONCAT(d.DepartmentName)
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Ini akan memberi Anda:

Ini akan memberi Anda daftar nilai nama 8 departemen yang digabungkan menjadi satu string yang dipisahkan koma.

GROUP_CONCAT(Nama Departemen BERBEDA) Contoh

Kueri berikut akan menggabungkan nilai-nilai berbeda dari nama departemen dari tabel mahasiswa dan departemen menjadi satu string yang dipisahkan dengan koma:

SELECT GROUP_CONCAT(DISTINCT d.DepartmentName)
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Ini akan memberi Anda:

Perhatikan bagaimana hasilnya berbeda dari hasil sebelumnya; hanya tiga nilai yang dikembalikan yang merupakan nama departemen berbeda, dan nilai duplikat telah dihapus.

GROUP_CONCAT(Nama Departemen,'&') Contoh

Kueri berikut akan menggabungkan semua nilai kolom nama departemen dari tabel mahasiswa dan departemen menjadi satu string, tetapi dengan karakter '&' sebagai pemisah, bukan koma:

SELECT GROUP_CONCAT(d.DepartmentName, '&')
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Ini akan memberi Anda:

Perhatikan bagaimana karakter “&” digunakan sebagai pengganti karakter default “,” untuk memisahkan nilai-nilai.

SQLite MAKS() & MENIT()

MAX(X) mengembalikan Anda nilai tertinggi dari nilai X. MAX akan mengembalikan nilai NULL jika semua nilai x adalah nol. Sedangkan MIN(X) mengembalikan Anda nilai terkecil dari nilai X. MIN akan mengembalikan nilai NULL jika semua nilai X adalah null.

Example

Pada kueri berikut, kita akan menggunakan fungsi MIN dan MAX untuk mendapatkan nilai tertinggi dan nilai terendah dari tabel “Marks”:

SELECT MAX(Mark), MIN(Mark) FROM Marks;

Ini akan memberi Anda:

SQLite JUMLAH(x), Jumlah(x)

Keduanya akan mengembalikan jumlah semua nilai x. Namun, keduanya berbeda dalam hal berikut:

• SUM akan mengembalikan null jika semua nilainya nol, tetapi Total akan mengembalikan 0.
• TOTAL selalu mengembalikan nilai floating point. SUM mengembalikan nilai integer jika semua nilai x adalah integer. Namun, jika nilainya bukan bilangan bulat, maka nilai floating point akan dikembalikan.

Example

Pada kueri berikut, kita akan menggunakan SUM dan total untuk mendapatkan jumlah total semua nilai dalam tabel “Marks”:

SELECT SUM(Mark), TOTAL(Mark) FROM Marks;

Ini akan memberi Anda:

Seperti yang Anda lihat, TOTAL selalu mengembalikan floating point. Namun SUM mengembalikan nilai bilangan bulat karena nilai di kolom “Tandai” mungkin berupa bilangan bulat.

Contoh selisih SUM dan TOTAL:

Pada query berikut ini kita akan menunjukkan perbedaan antara SUM dan TOTAL ketika kita mendapatkan JUMLAH nilai NULL:

SELECT SUM(Mark), TOTAL(Mark) FROM Marks WHERE TestId = 4;

Ini akan memberi Anda:

Perhatikan bahwa tidak ada nilai untuk TestId = 4, jadi ada nilai null untuk pengujian tersebut. SUM mengembalikan nilai null sebagai kosong, sedangkan TOTAL mengembalikan 0.

Kelompokkan OLEH

Klausa GROUP BY digunakan untuk menentukan satu atau lebih kolom yang akan digunakan untuk mengelompokkan baris ke dalam grup. Baris-baris yang nilainya sama akan dikumpulkan (disusun) menjadi beberapa kelompok.

Untuk kolom lain yang tidak disertakan dalam kelompokkan berdasarkan kolom, Anda dapat menggunakan fungsi agregat untuk kolom tersebut.

Contoh:

Kueri berikut akan memberi Anda jumlah total mahasiswa yang hadir di setiap departemen.

SELECT d.DepartmentName, COUNT(s.StudentId) AS StudentsCount
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
GROUP BY d. DepartmentName;

Ini akan memberi Anda:

Klausa GROUPBY DepartmentName akan mengelompokkan semua siswa ke dalam kelompok satu untuk setiap nama departemen. Untuk setiap kelompok “jurusan”, ia akan menghitung siswa di dalamnya.

klausa MEMILIKI

Jika Anda ingin memfilter grup yang dikembalikan oleh klausa GROUP BY, Anda dapat menentukan klausa “HAVING” dengan ekspresi setelah GROUP BY. Ekspresi tersebut akan digunakan untuk memfilter grup ini.

Example

Pada query berikut, kita akan memilih departemen yang hanya memiliki dua mahasiswa:

SELECT d.DepartmentName, COUNT(s.StudentId) AS StudentsCount
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
GROUP BY d. DepartmentName
HAVING COUNT(s.StudentId) = 2;

Ini akan memberi Anda:

Klausa HAVING COUNT(S.StudentId) = 2 akan memfilter grup yang dikembalikan dan hanya mengembalikan grup yang berisi tepat dua siswa di dalamnya. Dalam kasus kita, jurusan Seni memiliki 2 mahasiswa, sehingga ditampilkan di output.

SQLite Kueri & Subkueri

Di dalam kueri apa pun, Anda bisa menggunakan kueri lain baik di SELECT, INSERT, DELETE, UPDATE, atau di dalam subkueri lain.

Kueri bertingkat ini disebut subkueri. Sekarang kita akan melihat beberapa contoh penggunaan subkueri dalam klausa SELECT. Namun, dalam tutorial Memodifikasi Data, kita akan melihat bagaimana kita dapat menggunakan subkueri dengan pernyataan INSERT, DELETE, dan UPDATE.

Menggunakan subquery dalam contoh klausa FROM

Pada query berikut ini kita akan menyertakan subquery di dalam klausa FROM:

SELECT
  s.StudentName, t.Mark
FROM Students AS s
INNER JOIN
(
   SELECT StudentId, Mark
   FROM Tests AS t
   INNER JOIN Marks AS m ON t.TestId = m.TestId
)  ON s.StudentId = t.StudentId;

Pertanyaannya:

   SELECT StudentId, Mark
   FROM Tests AS t
   INNER JOIN Marks AS m ON t.TestId = m.TestId

Kueri di atas disebut subkueri di sini karena bersarang di dalam klausa FROM. Perhatikan bahwa kita memberinya nama alias “t” sehingga kita bisa merujuk ke kolom yang dikembalikan darinya dalam kueri.

Kueri ini akan memberi Anda:

Jadi dalam kasus kami,

• s.StudentName dipilih dari query utama yang memberikan nama siswa dan
• t.Mark dipilih dari subkueri; yang memberi nilai yang diperoleh masing-masing siswa tersebut

Menggunakan subquery dalam contoh klausa WHERE

Dalam query berikut ini kita akan menyertakan subquery dalam klausa WHERE:

SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId
                  FROM Students AS s
                  WHERE d.DepartmentId = s.DepartmentId);

Pertanyaannya:

SELECT DepartmentId
FROM Students AS s
WHERE d.DepartmentId = s.DepartmentId

Kueri di atas disebut subkueri di sini karena bersarang di klausa WHERE. Subquery akan mengembalikan nilai DepartmentId yang akan digunakan oleh operator NOT EXISTS.

Kueri ini akan memberi Anda:

Dalam kueri di atas, kami telah memilih departemen yang tidak memiliki siswa yang terdaftar di dalamnya. Yang merupakan departemen “Matematika” di sini.

set Operations – UNION, Intersect

SQLite mendukung operasi SET berikut:

PERSATUAN & PERSATUAN SEMUA

Ini menggabungkan satu atau lebih kumpulan hasil (sekelompok baris) yang dikembalikan dari beberapa pernyataan SELECT menjadi satu kumpulan hasil.

UNION akan mengembalikan nilai yang berbeda. Namun, UNION ALL tidak akan dan akan menyertakan duplikat.

Perhatikan bahwa nama kolom akan menjadi nama kolom yang ditentukan dalam pernyataan SELECT pertama.

Contoh UNI

Dalam contoh berikut, kita akan mendapatkan daftar DepartmentId dari tabel students dan daftar DepartmentId dari tabel departments di kolom yang sama:

SELECT DepartmentId AS DepartmentIdUnioned FROM Students
UNION
SELECT DepartmentId FROM Departments;

Ini akan memberi Anda:

Kueri hanya mengembalikan 5 baris yang merupakan nilai id departemen yang berbeda. Perhatikan nilai pertama yang merupakan nilai null.

SQLite UNION SEMUA Contoh

Dalam contoh berikut, kita akan mendapatkan daftar DepartmentId dari tabel students dan daftar DepartmentId dari tabel departments di kolom yang sama:

SELECT DepartmentId AS DepartmentIdUnioned FROM Students
UNION ALL
SELECT DepartmentId FROM Departments;

Ini akan memberi Anda:

Kueri akan menghasilkan 14 baris, 10 baris dari tabel siswa, dan 4 baris dari tabel departemen. Perhatikan bahwa, ada duplikat dalam nilai yang dikembalikan. Perhatikan juga bahwa nama kolom adalah yang ditentukan dalam pernyataan SELECT pertama.

Sekarang, mari kita lihat bagaimana UNION all akan memberikan hasil yang berbeda jika kita mengganti UNION ALL dengan UNION:

SQLite MEMOTONG

Mengembalikan nilai yang ada di kedua kumpulan hasil gabungan. Nilai yang ada di salah satu kumpulan hasil gabungan akan diabaikan.

Example

Pada query berikut, kita akan memilih nilai DepartmentId yang ada di kedua tabel Students dan Departments pada kolom DepartmentId:

SELECT DepartmentId FROM Students
Intersect
SELECT DepartmentId FROM Departments;

Ini akan memberi Anda:

Kueri hanya mengembalikan tiga nilai 1, 2, dan 3. Yang merupakan nilai yang ada di kedua tabel.

Namun nilai null dan 4 tidak dimasukkan karena nilai null hanya terdapat pada tabel mahasiswa dan tidak terdapat pada tabel jurusan. Dan nilai 4 ada di tabel jurusan dan bukan di tabel siswa.

Itu sebabnya nilai NULL dan 4 diabaikan dan tidak disertakan dalam nilai yang dikembalikan.

KECUALI

Misalkan jika Anda memiliki dua daftar baris, list1 dan list2, dan Anda hanya menginginkan baris dari list1 yang tidak ada di list2, Anda dapat menggunakan klausa “EXCEPT”. Klausa EXCEPT membandingkan dua daftar dan mengembalikan baris yang ada di list1 dan tidak ada di list2.

Example

Pada query berikut, kita akan memilih nilai DepartmentId yang ada pada tabel departments dan tidak ada pada tabel students:

SELECT DepartmentId FROM Departments
EXCEPT
SELECT DepartmentId FROM Students;

Ini akan memberi Anda:

Kueri hanya mengembalikan nilai 4. Yang merupakan satu-satunya nilai yang ada di tabel departemen, dan tidak ada di tabel siswa.

penanganan NULL

Nilai “NULL” adalah nilai khusus dalam SQLiteNilai null digunakan untuk mewakili nilai yang tidak diketahui atau nilai yang hilang. Perlu dicatat bahwa nilai null sangat berbeda dengan nilai “0” atau nilai kosong. Karena 0 dan nilai kosong adalah nilai yang diketahui, sedangkan nilai null adalah nilai yang tidak diketahui.

Nilai NULL memerlukan penanganan khusus SQLite, sekarang kita akan melihat cara menangani nilai NULL.

Cari nilai NULL

Anda tidak dapat menggunakan operator persamaan normal (=) untuk mencari nilai null. Misalnya, kueri berikut mencari siswa yang memiliki nilai DepartmentId null:

SELECT * FROM Students WHERE DepartmentId = NULL;

Kueri ini tidak akan memberikan hasil apa pun:

Karena nilai NULL tidak sama dengan nilai lainnya termasuk nilai null itu sendiri, itulah mengapa nilai tersebut tidak memberikan hasil apa pun.

Namun, agar kueri berfungsi, Anda harus menggunakan operator “IS NULL” untuk mencari nilai null seperti berikut:

SELECT * FROM Students WHERE DepartmentId IS NULL;

Ini akan memberi Anda:

Kueri akan mengembalikan siswa yang memiliki nilai DepartmentId nol.

Jika Anda ingin mendapatkan nilai-nilai yang tidak null, maka Anda harus menggunakan operator “IS NOT NULL” seperti ini:

SELECT * FROM Students WHERE DepartmentId IS NOT NULL;

Ini akan memberi Anda:

Kueri akan mengembalikan siswa yang tidak memiliki nilai NULL DepartmentId.

Hasil bersyarat

Jika Anda memiliki daftar nilai dan ingin memilih salah satunya berdasarkan kondisi tertentu. Untuk itu, syarat nilai tertentu tersebut harus benar agar dapat dipilih.

Ekspresi CASE akan mengevaluasi daftar kondisi ini untuk semua nilai. Jika kondisinya benar, maka nilai tersebut akan dikembalikan.

Misalnya, jika Anda memiliki kolom “Nilai” dan Anda ingin memilih nilai teks berdasarkan nilai nilai seperti berikut:

– “Sangat Baik” jika nilainya lebih tinggi dari 85.

– “Sangat Baik” jika nilainya antara 70 dan 85.

– “Baik” jika nilainya antara 60 dan 70.

Kemudian Anda dapat menggunakan ekspresi CASE untuk melakukannya.

Ini dapat digunakan untuk mendefinisikan beberapa logika dalam klausa SELECT sehingga Anda dapat memilih hasil tertentu tergantung pada kondisi tertentu seperti pernyataan if misalnya.

Operator CASE dapat didefinisikan dengan sintaksis berbeda sebagai berikut:

Anda dapat menggunakan kondisi yang berbeda:

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  WHEN condition3 THEN result3
  …
  ELSE resultn
END

Atau, Anda hanya dapat menggunakan satu ekspresi dan memasukkan kemungkinan nilai berbeda untuk dipilih:

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  WHEN value3 THEN result3
  …
  ELSE restuln
END

Perhatikan bahwa klausa ELSE bersifat opsional.

Example

Pada contoh berikut, kita akan menggunakan ekspresi CASE dengan nilai NULL pada kolom ID departemen di tabel Mahasiswa untuk menampilkan teks 'Tidak Ada Departemen' seperti berikut:

SELECT
  StudentName,
  CASE
    WHEN DepartmentId IS NULL THEN 'No Department'
    ELSE DepartmentId
  END AS DepartmentId
FROM Students;

• Operator CASE akan memeriksa nilai DepartmentId apakah null atau tidak.
• Jika nilainya NULL, maka ia akan memilih nilai literal 'Tanpa Departemen' dan bukan nilai DepartmentId.
• Jika bukan nilai null, maka nilai kolom DepartmentId akan dipilih.

Ini akan memberi Anda output seperti yang ditunjukkan di bawah ini:

Ekspresi tabel umum

Ekspresi tabel umum (CTE) adalah subkueri yang didefinisikan di dalam pernyataan SQL dengan nama tertentu.

Ini memiliki keunggulan dibandingkan subkueri karena didefinisikan dari pernyataan SQL dan akan membuat kueri lebih mudah dibaca, dipelihara, dan dipahami.

Ekspresi tabel umum dapat didefinisikan dengan meletakkan klausa WITH di depan pernyataan SELECT seperti berikut:

WITH CTEname
AS
(
   SELECT statement
)
SELECT, UPDATE, INSERT, or update statement here FROM CTE

“CTEname” adalah nama apa pun yang dapat Anda berikan untuk CTE, Anda dapat menggunakannya untuk merujuknya nanti. Perhatikan bahwa Anda dapat mendefinisikan pernyataan SELECT, UPDATE, INSERT, atau DELETE pada CTE.

Sekarang, mari kita lihat contoh cara menggunakan CTE pada klausa SELECT.

Example

Dalam contoh berikut, kita akan mendefinisikan CTE dari pernyataan SELECT, lalu kita akan menggunakannya nanti pada kueri lain:

WITH AllDepartments
AS
(
  SELECT DepartmentId, DepartmentName
  FROM Departments
)
SELECT
  s.StudentId,
  s.StudentName,
  a.DepartmentName
FROM Students AS s
INNER JOIN AllDepartments AS a ON s.DepartmentId = a.DepartmentId;

Dalam kueri ini, kita mendefinisikan CTE dan memberinya nama “AllDepartments“. CTE ini didefinisikan dari kueri SELECT:

SELECT DepartmentId, DepartmentName
  FROM Departments

Kemudian setelah kami mendefinisikan CTE, kami menggunakannya dalam kueri SELECT yang muncul setelahnya.

Perhatikan bahwa, Ekspresi tabel umum tidak memengaruhi output kueri. Ini adalah cara untuk menentukan tampilan logis atau subkueri untuk menggunakannya kembali dalam kueri yang sama. Ekspresi tabel umum seperti variabel yang Anda deklarasikan, dan menggunakannya kembali sebagai subkueri. Hanya pernyataan SELECT yang mempengaruhi keluaran kueri.

Kueri ini akan memberi Anda:

Kueri lanjutan

Kueri tingkat lanjut adalah kueri yang berisi gabungan kompleks, subkueri, dan beberapa agregat. Di bagian berikut, kita akan melihat contoh kueri tingkat lanjut:

Di mana kita mendapatkan,

• Nama departemen dengan semua mahasiswa untuk setiap departemen
• Nama siswa dipisahkan dengan koma dan
• Menunjukkan departemen yang memiliki setidaknya tiga mahasiswa di dalamnya

SELECT
  d.DepartmentName,
  COUNT(s.StudentId) StudentsCount,
  GROUP_CONCAT(StudentName) AS Students
FROM Departments AS d
INNER JOIN Students AS s ON s.DepartmentId = d.DepartmentId
GROUP BY d.DepartmentName
HAVING COUNT(s.StudentId) >= 3;

Kami menambahkan klausa JOIN untuk mendapatkan DepartmentName dari tabel Departments. Setelah itu, kami menambahkan klausa GROUP BY dengan dua fungsi agregasi:

• “COUNT” untuk menghitung siswa tiap kelompok jurusan.
• GROUP_CONCAT untuk menggabungkan siswa untuk setiap grup dengan dipisahkan koma dalam satu string.

Setelah GROUP BY, kami menggunakan klausa HAVING untuk memfilter departemen dan memilih hanya departemen yang memiliki minimal 3 mahasiswa.

Hasilnya akan seperti berikut:

Pertanyaan Umum Demo Slot