9 Dasar SQL Query Paling Umum dan Wajib Diketahui Programmer

Tutorial SQL Query ini akan membahas 9 Dasar SQL Query paling sering digunakan untuk mengingkatkan produktivitas kerja. 9 Dasar SQL Query ini wajib diketahui oleh semua programmer agar bisa menampilkan data dengan mudah di aplikasi yang sedang dikembangkan. Setiap aplikasi termasuk aplikasi website pasti akan menampilkan data. Dengan demikian pasti akan menggunkan SQL query untuk mengambil datanya dari database. Tutorial ini akan menjelaskan SQL query untuk mengambil data dari table tunggal, table berelasi satu ke banyak, table berelasi banyak ke banyak. Berikut adalah stuktur table dan relasi yang akan dijadikan contoh:

contoh struktur table dan relasi tutorial SQL query

Pada tutorial ini diasumsikan anda telah memiliki dasar SQL query select. Tutorial SQL query ini akan menggunakan database MySQL dan juga dilengkapi dengan video tutorial yang bisa diakses di channel Youtube MyPHPtutorials.

Video Tutorial 9 Dasar SQL Query Paling Umum

Seperti gambar di atas kita akan mengunakan contoh table seperti berikut:

Siswa memiliki banyak absensi
Guru wali memilliki banyak siswa
Mata pelajaran memiliki banyak siswa dan siswa memiliki banyak mata pelajaran
Setiap mata pelajaran siswa punya nilai yang dicatat dalam table nilai_siswa yang merupakan tabel penghubung relasi banyak ke banyak untuk siswa dan mata pelajaran.

1. Menampilkan Data dari Tabel tunggal

Menampilkan atau membaca data dari tabel tunggal merupakan SQL query paling dasar yang perlu diketahui. Kita akan menggunakan SQL select dengan sintaks dasar

SELECT kolom1, kolom2, kolom3, ....
FROM nama_table

Misalnya untuk membaca semua kolom tabel siswa bisa menggunakan query seperti:

SELECT * FROM siswa

Untuk menampilkan kolom tertentu bisa gunakan query:

SELECT id, nis, nama FROM siswa;

2. SQL Where - Menyaring Data Berdasakan Kriteria Tertentu

Query di atas digunakan untuk menampilkan semua data, untuk menyaring dengan kriteria tertentu gunakan WHERE dengan sintaks dasar:

SELECT kolom1, kolom2, ...
FROM nama_tabel
WHERE kondisi;

Contoh untuk menyaring nama siswa kelas 10 saja

select * from siswa where kelas = 10;

Berikut adalah operator dalam where

Operator	Deskripsi	Contoh
=	sama dengan	`where kelas = 11`
>	lebih besar dari	`where kelas > 10`
<	kurang dari	`where kelas < 11`
>=	lebih besar sama dengan	`where kelas >= 11`
<=	kurang dari atau sama dengan	`where kelas <= 11`
<>	tidak sama dengan	`where kelas <> 11`
BETWEEN	diantara	`where kelas between 10 and 12`
LIKE	seperti	`where nama like '%joni%`
IN	di dalam nilai yang diberikan	`where kelas in (10, 11, 12)`

3. SQL Limit dan Order By - Membatasi Jumlah Baris dan Mengurut Hasil

Dengan where kita bisa membatasi berdasarkan kriteria tertentuk, sedangkan jika ada ribuan baris kita bisa membatasi untuk menampilkan 10 baris pertama dengan limit dan juga bisa digunakan untuk paginasi. Sedangkan order by digunakan untuk mengurutkan hasil. Sintaks dasarnya adalah

SELECT kolom1, kolom2, ...
FROM nama_tabel
ORDER BY kolom1,kolom2, ... ASC|DESC;
LIMIT banyak OFFSET offset;

OFFSET opsional, digunakan untuk menentukan awal baris keberapa yang mau ditampilkan. ASC untuk mengurut dari paling kecil ke besar, dan DESC sebaliknya. Untuk menampilkan 10 siswa pertama dan diurut berdasarkan nama gunakan SQL query di bawah

SELECT * FROM siswa
ORDER BY nama ASC
LIMIT 10

Untuk menampilkan 20 data siswa mulai dari ke-5 gunakan SQL:

SELECT * FROM siswa
ORDER BY nama ASC
LIMIT 20 OFFSET 4

offset dimulai dari nol, maka untuk baris ke-5 nilai offset adalah 4.

4. Agregasi dan Group By - Menggunakan Sum, Avg, Count, Max dan Min

Sering kali kita melakukan agregasi dari sebuah tabel seperti menghitung jumlah, banyak, rata, nilai tertinggi dan terendah. Berikut adalah contoh masih masing dari itu.

Menghitung jumlah atau total menggunakan sum misalnya jumlah/total nilai siswa

select sum(nilai) from nilai_siswa;
-- bisa juga menggunakan kriteria
select sum(nilai) from nilai_siswa where id_pelajaran = 1;

Menghitung banyak data menggunakan count, misalnya banyak siswa

select count(*) from siswa;
-- bisa juga menggunakan kriteria
select count(*) from siswa where kelas = 10;

Mencari nilai rata rata dengan avg, misalnya nilai rata rata siswa

SELECT avg(nilai) FROM nilai_siswa; 
-- menggunakan kriteria
select avg(nilai) from nilai_siswa where id_pelajaran = 10;

Mencari nilai tertinggi dengan max, misal nilai siswa tertinggi

SELECT max(nilai) FROM nilai_siswa;
-- dengan kriteria
SELECT max(nilai) FROM nilai_siswa where id_pelajaran=1;

Mencari nilai terendah dengan min, misal nilai siswa terendah

SELECT min(nilai) FROM nilai_siswa;
-- dengan kriteria
SELECT min(nilai) FROM nilai_siswa where id_pelajaran=1;

Agregasi bisa juga digabungkan dengan GROUP BY sehingga menghasilkan data yang lebih terperinci, misal mencari nilai tertinggi untuk setiap siswa atau mata pelajaran. Sintaksnya adalah

SELECT fungsi_agregasi(kolom) FROM nama_tabel GROUP BY kolom1, kolom2,...

Berikut contoh untuk masing masing fungsi agregasi di atas.

Total nilai masing pelajaran atau siswa

SELECT sum(nilai),id_pelajaran FROM nilai_siswa GROUP BY id_pelajaran;
SELECT sum(nilai),id_siswa FROM nilai_siswa GROUP BY id_siswa;

Banyak siswa masing masing kelas

SELECT count(*), kelas FROM siswa GROUP BY kelas;

Nilai rata rata siswa tiap mata pelajaran

SELECT avg(nilai), id_pelajaran FROM nilai_siswa GROUP BY id_pelajaran;

Nilai tertinggi dan terendah setiap pelajaran

SELECT max(nilai), id_pelajaran FROM nilai_siswa GROUP BY id_pelajaran;
SELECT min(nilai), id_pelajaran FROM nilai_siswa GROUP BY id_pelajaran;

5. Having - Menyaring Hasil dari Agregasi

Jika ingin menyaring hasil menggunakan agregasi anda tidak akan bisa. Misalnya jika Anda mencoba menjalankan SELECT avg(nilai), id_pelajaran FROM nilai_siswa WHERE avg(nilai) > 6 GROUP BY id_pelajaran; maka akan terjadi error. Untuk itu kita perlu mengunakan Having dengan sintaks seperti di bawah

SELECT fungsi_agregasi(kolom), kolom2, ...
FROM nama_tabel
WHERE kondisi
GROUP BY kolom1, kolom2, ...
HAVING kondisi
ORDER BY kolom1, kolom2, ...

Berikut beberapa contoh menggunakan having

Total nilai masing siswa yang lebih besar dari 30

SELECT sum(nilai),id_siswa FROM nilai_siswa GROUP BY id_siswa HAVING sum(nilai) > 30;

Kelas dengan jumlah siswa lebih dari 40

SELECT count(*), kelas FROM siswa GROUP BY kelas HAVING count(*) > 40;

Nilai rata rata siswa tiap mata pelajaran yang lebih dari 7

SELECT avg(nilai), id_pelajaran FROM nilai_siswa GROUP BY id_pelajaran HAVING avg(nilai) > 7

6. Subquery

Subquery adalah query yang berada dalam query lain atau sering disebut dengan sub-Select. Subquery dapat digunakan dalam SELECT (kolom) dan juga WHERE seperti contoh di bawah.

Membandingkan Nilai Siswa dengan Nilai Rata Rata

SELECT id_siswa, id_pelajaran, nilai, (SELECT avg(nilai) FROM nilai_siswa) FROM nilai_siswa;

Mencari Siswa Dengan Nilai Dibawah Nilai Rata Rata

SELECT id_siswa, id_pelajaran, nilai FROM nilai_siswa where nilai < (SELECT avg(nilai) FROM nilai_siswa);

Menampilkan Siswa Yang tidak Absen (atau alpa) Subquery yang memberikan hasil lebih dari satu baris hanya bisa digunakan dengan operator IN seperti

SELECT * FROM siswa WHERE id NOT IN (SELECT id_siswa FROM absensi WHERE tanggal_absen >= '2022-05-16 00:00:00' AND tanggal_absen < '2022-05-17 00:00:00')

SQL JOIN - Menampilkan Data Lebih Dari Satu Tabel

SQL Join digunakan untuk menampilkan data lebih dari satu tabel. Bisa digunakan untuk menampilkan data untuk table dengan relasi 1 ke banyak dan juga dari banyak ke banyak. Ada tiga join yaitu INNER, LEFT dan RIGHT yang akan dijelaskan masing masing di bawah.

7. Inner Join

INNER join digunakan untuk menampilkan data yang memiliki kesamaan nilai di kedua tabel atau lebih.

contoh struktur table dan relasi tutorial SQL query

Misalnya untuk menampilkan data siswa dan nama wali siswa. Jika ada data siswa seperti:

nis	nama	id_wali
0001	Joni	1
0002	Ayu	1
0003	Dian	1
0004	Budi	2
0004	Andi	null

dan data guru seperti

id	nip	nama
1	1001	Lestari
2	1002	Bagus
3	1003	Dimas

SELECT nis, siswa.nama, guru.nama as wali FROM `siswa` INNER JOIN guru ON guru.id = siswa.id_wali;

Maka contoh hasil dari query di atas adalah

nis	nama	wali
0001	Joni	Lestari
0002	Ayu	Lestari
0003	Dian	Lestari
0005	Budi	Bagus

Sedangkan guru yang tidak menjadi wali siswa seperti Dimas tidak akan ditampilkan, begitu juga dengan Andi yang belum punya wali.

8. Left Join

LEFT join digunakan untuk menampilkan semua data dari tabel sebelah kiri dan data yang sesuai di tabel sebelah kanan.

contoh struktur table dan relasi tutorial SQL query

Jika SQL inner join di atas diganti dengan left join

SELECT nis, siswa.nama, guru.nama as wali FROM `siswa` LEFT JOIN guru ON guru.id = siswa.id_wali;

Maka hasilnya akan menjadi:

nis	nama	wali
0001	Joni	Lestari
0002	Ayu	Lestari
0003	Dian	Lestari
0004	Budi	Bagus
0005	Andi	null

Andi akan ditampilkan tapi tidak ada data walinya.

Kalau menggunakan Subquery seperti di atas kita bisa mencari siswa yang tidak masuk, dengan left join kita bisa membuat laporan untuk menampilkan absensi siswa baik yang masuk sekolah dan tidak. Seperti query di bawah:

SELECT siswa.nis, siswa.nama, absensi.tanggal_absen FROM siswa LEFT JOIN absensi ON siswa.id = absensi.id_siswa AND absensi.tanggal_absen >= '2022-05-16 00:00:00' AND absensi.tanggal_absen < '2022-05-17 00:00:00' WHERE siswa.kelas = 10;

9. Right Join

RIGH join kebalikan dari left join yang akan menampilkan semua data di tabel kanan dan data yang disesuai di table kiri.

contoh struktur table dan relasi tutorial SQL query

jika left join di atas diganti dengan right join seperti di bawah

SELECT nis, siswa.nama, guru.nama as wali FROM `siswa` RIGHT JOIN guru ON guru.id = siswa.id_wali;

Maka hasilnya akan seperti:	nis	nama
0001	Joni	Lestari
0002	Ayu	Lestari
0003	Dian	Lestari
null	null	Dimas

guru Dimas akan ditampilkan tapi tidak ada data nis dan nama siswanya.

10. Bonus - Menampilkan Data Dengan Relasi Banyak ke Banyak

SQL join tentunya dapat digunakan untuk menampilkan tabel dengan relasi banyak ke banyak. Yang mana terdapat satu tabel penghubung sehingga perlu membaca tiga tabel. Seperti tabel siswa yang memiliki relasi banyak ke banyak dengan mata pelajaran. Satu siswa bisa belajar banyak mata pelajaran dan satu mata pelajaran bisa dipelajari banyak siswa. Relasi ini dihubungan dengan tabel nilai_siswa (yang mana dalam tutorial ini juga ditambahkan kolom nilai untuk mencatat nilai siswa). Di bawah adalah contoh SQL JOIN tabel dengan relasi banyak ke banyak untuk menampilkan semua mata pelajaran siswa.

SELECT siswa.nis, siswa.nama, siswa.kelas, mata_pelajaran.nama as mataPelajaran, nilai_siswa.nilai FROM siswa INNER JOIN nilai_siswa on siswa.id = nilai_siswa.id_siswa INNER JOIN mata_pelajaran ON nilai_siswa.id_pelajaran = mata_pelajaran.id;

SQL dimulai dari tabel siswa kemudian di-join dengan tabel nilai_siswa lalu di-join lagi dengan tabel mata_pelajaran.

Kesimpulan

Dengan memiliki 9 dasar SQL query wajib ini maka kalian akan bisa dengan mudah menampilkan data untuk tabel dengan relasi satu ke satu, menampilkan data dari tabel dengan releasi satu ke banyak dan juga tabel dengan releasi banyak ke banyak. Jika kalian memiliki contoh atau dasar lain yang penting untuk dipelajari silahkan cantumkan dikolom komentar di bawah. Demikian pula bagi kalian yang ingin bertanya. Selamat Mencoba.