Untuk menampilkan data dengan banyak kondisi dapat menggunakan operator logika yaitu: NOT, AND, OR, BETWEEN, LIKE, IN, SOME, ANY dan ALL. Untuk operator NOT, AND dan OR mempunyai kondisi logika dengan aturan baku. Pelajari selengkapnya bahasa SQL sebagai DML pada postingan ini.
Kumpulan Manipulasi Data SQL
Untuk memudahkan Anda dalam belajar dan praktek (Learn by Doing), maka saya asumsikan Anda telah mengimport database northwind. Tapi jika belum, silakan download di SINI terlebih dahulu.
1. AND
Berikut adalah perintah SQL untuk menampilkan record pada tabel products yang memiliki CategoryID=2 dan SupplierID=2.
SELECT * FROM Products WHERE CategoryID=2 AND SupplierID=2;
Hasil:
2. OR
Berikut adalah perintah SQL untuk menampilkan record pada tabel products yang memiliki CategoryID=2 atau SupplierID=2.
SELECT * FROM Products WHERE CategoryID=2 OR SupplierID=2;
Hasil:
3. BETWEEN
Berikut adalah perintah SQL untuk menampilkan record pada tabel products yang memiliki UnitsInStock antara 20 sampai 50. Diluar batasan itu tidak ditampilkan.
SELECT * FROM Products WHERE UnitsInStock BETWEEN 20 AND 50;
Hasil:
Mengetahui Jumlah Omzet
SELECT
p.ProductName, SUM(od.Quantity) Terjual
FROM
products AS p JOIN order_details AS od
ON p.productID=od.productID
JOIN orders AS o
ON od.orderID=o.orderID
WHERE YEAR(o.OrderDate) BETWEEN 1996 AND 1997
GROUP BY 1
Hasil:
4. IN
Berikut adalah perintah SQL untuk menampilkan record pada suatu tabel, dengan cara menggunakan salah satu record sebagai referensi.
Contoh, saya pilih ProductID sebagai referensi SupplierID dan CategoryID untuk menampilkan data terkait. Sebenarnya contoh ini kurang pas, namun masih bisa untuk menjelaskan cara kerja perintah IN.
SELECT * FROM products WHERE ProductID IN (SupplierID,CategoryID);
Hasil:
5. ORDER BY (Mengurutkan Data)
Mengurutkan record menggunakan perintah ORDER BY [ASC | DESC]. Pengurutan berlaku untuk tipe data string dan integer/numeric. Jika dalam pengurutan tidak disebutkan jenis pengurutannya maka decara default diset urut naik. Jika ada klausa WHERE maka order by diletakkan setelah WHERE.
1. Mengurutkan Secara Ascending (Naik)
SELECT * FROM products ORDER BY ProductName;
Secara default pengurutan (ORDER) SQL adalah ASCENDING. Meskipun sebenarnya dengan penulisan seperti ini juga bisa:
SELECT * FROM products ORDER BY ProductName ASC;
Hasil:
2. Mengurutkan Secara Descending (Turun)
SELECT * FROM products ORDER BY ProductName DESC;
Hasil:
3. Mengurutkan Dengan Prioritas Acuan
Contoh ini menggunakan SupplierID sebagai prioritas acuan, sehingga penempatannya harus paling kiri diantara acuan yang lain. Sedangkan CategoryID akan kami gunakan sebagai Acuan minoritas.
SELECT * FROM products ORDER BY SupplierID, CategoryID;
Hasil:
4. Mengurutkan Dengan Descending dan Ascending
SELECT * FROM products ORDER BY SupplierID DESC, CategoryID;
Hasil:
5. Mengurutkan Dengan Ascending dan Descending
SELECT * FROM products ORDER BY SupplierID, CategoryID DESC;
Hasil:
6. Menampilkan nama depan dan belakang
SELECT TitleOfCourtesy,FirstName,LastName
FROM employees ORDER BY FirstName ASC;
Hasil:
6. Pengelompokan RECORD
Untuk menampilkan pengelompokan record gunakan GROUP BY. Kemampuan GROUP BY akan terlihat berbeda dengan ORDER BY manakala kita memanfaatkannya untuk keperluan Agregasi secara khusus pada kolom yang belum dikelompokkan.
Contoh:
Berikut akan kami tunjukan bagaimana cara mengetahui jumlah orderan yang perlu dikirim ke setiap negara.
SELECT COUNT(OrderID), ShipCountry FROM orders GROUP BY (ShipCountry);
Hasil:
Mengetahui Nama Pembeli
SELECT
c.ContactName,
s.CompanyName,
COUNT(O.ShipVia)Frequensi
FROM
orders AS O JOIN shippers AS s
ON s.ShipperID=O.ShipVia
JOIN customers AS c
ON c.CustomerID=O.CustomerID
group by 1,2
Hasil:
7. LIKE
Pembahasan lebih mendetail tentang Query LIKE bisa Anda simak di SQL Pencarian String. Sepintas akan kami berikan contoh bagaimana menampilkan employees dengan nama depan diawali dengan huruf A:
SELECT TitleOfCourtesy,FirstName,LastName
FROM employees WHERE FirstName LIKE 'A%';
Hasil:
8. UPDATE
Tengok terlebih dahulu data yang ingin di update (Sebelum UPDATE).
SELECT ProductName,SupplierID,UnitsInStock
FROM products WHERE
SupplierID=1;
Hasil:
Proses Update
UPDATE products SET UnitsInStock=UnitsInStock+5
WHERE SupplierID=1;
Tengok kembali datanya,
Perintah update digunakan untuk mengubah data/record dari tabel.
Berikut formatnya:
Update namatabel set namakolom = "databaru" where [kondisi]
Contoh berikut menjelaskan akan mengupdate data dari tabel cabang pada kolom alamat, dimana semua alamat lama akan diganti dengan 'Jl. Gejayan'.
Upadate cabang set alamat = 'Jl. Gejayan'
Jika record yang diubah lebih dari satu gunakan tanda koma ( , ) berikut formatnya:
Update namatabel set namakolom1="nilai1", namakolom2="nilai2", ......Where [kondisi]
Contoh:
Update biodata set jur="TI", nilai="B", angkatan="2007" where jk="L"
Perintah diatas artinya mengubah semua record mahasiswa yang berjenis kelamin laki-laki dimana jurusan=TI, nilai=B dan angkatan=2007.
9. COUNT
Menghitung jumlah data yang tersedia dengan kondisi SupplierID=1;
SELECT count(*) FROM products WHERE SupplierID=1;
Hasil:
Memberikan judul laporan sebagai "Jumlah"
SELECT count(*) AS Jumlah
FROM products WHERE
SupplierID=1;
Hasil:
10. LIMIT
Memberikan batasan dengan hanya menampilkan max 10 record secara random
SELECT TitleOfCourtesy,FirstName,LastName
FROM employees ORDER BY RAND() LIMIT 10;
Hasil:
Memberikan batasan dengan hanya menampilkan max 5 record
SELECT TitleOfCourtesy,FirstName,LastName
FROM employees ORDER BY FirstName ASC LIMIT 5;
Hasil:
Memberikan batasan dengan parameter;
LIMIT mulai_dari, jumlah_record
Di bawah ini akan saya berikan contoh menampilkan record employees yang dimulai dengan interval 3 dari urutan ke-1, hal itu sama saja dengan menampilkan record mulai dari urutan ke-4. Kemudian maksimal record yang ingin saya tampilkan adalah 3.
Perhatikan laporan employees di atas, interval 0 (urutan ke-1) ditempati oleh Andrew, berarti interval 3 (urutan ke-4) ditempati oleh Laura.
SELECT TitleOfCourtesy,FirstName,LastName
FROM employees ORDER BY FirstName ASC LIMIT 3,4;
Hasil:
11. AS (ALIAS)
Klausa alias digunakan untuk mengganti nama kolom tabel yang dihasilkan dari perintah select.
Format dasarnya:
Select field1 as 'nama1', field2 as 'nama2', ... from tabelasal
Contoh:
Select id_nasabah as 'ID', nama_nasabah as 'Nama Lengkap', jk as 'Kelamin' from nasabah
12. SELECT
Digunakan untuk menampilkan record yang berada dalam tabel sesuai dengan kriteria tertentu.
Format perintah select sebagai berikut:
Select (field1,field2, ....) from tabelasal where [kondisi]
group by [ekspresi] Having [kondisi]
order by [ekspresi] [asc | desc]
Untuk menampilkan semua record dalam satu tabel perintahnya:
Select * from cabang
Untuk menampilkan beberapa field dalam satu tabel perintahnya:
Select nama, jk, alamat from biodata
Menampilkan record dalam satu tabel dengan syarat kondisi tertentu:
Select nama, alamat, tgl_lahir from biodata where jk='L' and alamat='Jl. Gejayan'
Untuk menampilkan semua record yang ada dalam tabel rekening yang saldonya lebih dari 350000, perintahnya:
Select * from rekening where saldo > 350000
13. DELETE
Perintah delete digunakan untuk menghapus sebuah/beberapa record sesuai dengan kondisi.
Berikut formatnya:
Delete from namatabel where [kondisi]
Anda perlu berhati-hati menggunakan perintah delete, karena tidak akan mengkonfirmasi dalam proses penghapusan.
Berikut ini contohnya:
Delete from cabang where namacabang='Jl. Gejayan'
Bila ingin menghapus semua record dalam tabel, anda tidak perlu menyertakan kondisi pada klausa where.
14. INSERT
Perintah INSERT digunakan untuk menyisipkan record ke dalam tabel / view. Penyisipan record tergantung pada saat menciptakan tabel beserta constraintnya. Jika field dideklarasikan NOT NULL maka field tersebut harus diisi.
Contoh:
Insert into barang values ('KT01','Kartu Mainan','Buah',15000)
Atau jika semua record tidak diisi, maka fieldnya harus disebutkan.
Contoh:
Insert into barang (kdbrg,nmbrg,hrgsat) values ('KT01','Kartu Mainan',15000)
15. UNIQUE
Unique, fungsi sama dengan Primary Key. Perbedaannya terletak pada Primary Key yang hanya dapat dibuat satu kali pada sebuah tabel dan Unique dapat dibuat beberapa kali pada sebuah tabel.
Jika Anda menginginkan beberapa field bersifat unik maka deklarasikan dengan Unique yang tidak termasuk dalam field Primary Key.
16. FOREIGN KEY ... REFERENCES
Foreign Key ... References, merupakan kunci tamu atau relasi.
Berasal dari kunci primer pada tabel master yang diletakkan pada tabel transaksi. Batasan Foreign Key hanya bisa merujuk field yang memiliki batasan Primary Key atau Unique pada tabel yang dirujuk.
17. PRIMARY KEY
Primary Key, merupakan kunci primer yang bersifat unik dan hanya dapat dibuat per tabel.
18. CONSTRAINT
Constraint adalah fitur yang memberlakukan integritas data, menjaga kualitas data dan membuat index khusus untuk tabel dan field-fieldnya.
Constraint juga merupakan kata kunci opsional yang menandakan awal dari definisi batasan Primary Key, Foreign Key, Unique, Check dan Default.
Contoh pada field ag hanya nilai ('I','K','P','B','H') yang boleh diinputkan. Contraint dilakukan pada saat menciptakan tabel.
Create table biodata (
Nim int identity (1,1),
Nama varchar(20) not null,
Jk char(1),
Tgllahir datetime,
Status bit default 1,
Ag varchar(1) constraint agama check (ag in('I','K','P','B','H')),
Primary key (nim))
19. DEFAULT VALUE
Default value, artinya nilai default atribut adalah 1.
Default, adalah nilai default yang dapat diterapkan pada semua field kecuali field bertipe TimeStamp atau Identity.
Contoh di bawah ini menunjukkan bahwa status mahasiswa 1 = belum menikah, dan nilai 0 = menikah.
Create table biodata (
Nim int identity (1,1),
Nama varchar(20) not null,
Jk char(1),
Tgllahir datetime,
Status bit default 1,
Ag varchar(1) constraint agama check (ag in('I','K','P','B','H')),
Primary key (nim))
20. IDENTITY
Identity, menyatakan field yang secara otomatis akan diisi nilai dengan kenaikan 1 dimulai dari angka 1. Identity setara auto_increment pada MySQL.
Identity, digunakan untuk memberikan nilai unik dengan kenaikan nilai tertentu. Digunakan bersamaan dengan Primary Key dan bertipe numeric.
Hanya satu field yang bersifat identity dalam sebuah tabel. Anda harus menentukan nilai awal dan nilai akhir atau tidak keduanya. Jika tidak ditentukan maka defaultnya bernilai (1,1).
Contoh:
Create table biodata (
Nim int identity (1,1),
Nama varchar(20) not null,
Jk char(1),
Tgllahir datetime,
Status bit default 1,
Ag varchar(1) constraint agama check (ag in('I','K','P','B','H')),
Primary key (nim))
21. NOT NULL
Not Null, menyatakan sebuah field nilainya tidak diketahui. Not Null tidak boleh dipakai pada field yang memiliki sifat identity atau primary key.
Contoh:
Create table biodata (
Nim int identity (1,1),
Nama varchar(20) not null,
Jk char(1),
Tgllahir datetime,
Status bit default 1,
Ag varchar(1) constraint agama check (ag in('I','K','P','B','H')),
Primary key (nim))
