SQL
SQL Server 2014 Express Edition menawarkan kesempatan pada Anda untuk mendalami pengetahuan mengenai database maupun aplikasidatabase, sementara masih berupa sebuah aplikasi yang gratis. Da
lam bab ini, Anda akan mempelajari mengenai struktur dasar dari query maupun statement SQL. Perintah-perintah yang digunakan adalah merupakan bagian dari spesifikasi bahasa Transact-SQL (T-SQL), danmerupakan unsur utama dalam penggunaan Microsoft SQL Server.Meskipun ada banyak tool yang tersedia dalam merancang query secara visual, seperti misalnya Visual Database Tool yang tersedia dari Microsoft Visual Studio sendiri, namun adalah tetap penting untuk mengerti dan memahami bahasa SQL itu sendiri. Akan ada waktu dimana menulis sendiri statement SQL yang diperlukan adalah cara satusatunya,
ataupun yang paling cepat, untuk mendapatkan apa yang Anda butuhkan. Mempelajari SQL juga adalah suatu cara yang ideal untuk memahami penggunaan sebuah relational database seperti SQL Express.
Relational Database
Seperti yang telah diperkenalkan pada bab sebelumnya, sebuah relational
database terdari dari satu atau lebih table, di mana setiap table memiliki 0 atau lebih record, atau baris, data. Data untuk setiap barisnya diatur dalam unit tertentu, yang disebut sebagai field atau kolom (column). Ketika kita hendak menunjukkan field dalam sebuah table,misalnya table Customer, biasanya akan ditunjukkan seperti gambar berikut.
Table Customer
Beberapa dari table-table yang ada dalam sebuah database akan memiliki
relationship, atau hubungan, di antaranya. Hubungan itu bisa berupa hubungan one-to-one atau one-to-many. Elemen yang menghubungkan
antar-table dimungkinkan oleh sebuah pasangan Primary
Key dan Foreign Key, di mana sebuah field Foreign Key dari sebuah table adalah merupakan Primary Key dari table yang lain.
Hubungan antar-table
Suatu contoh dari hubungan one-to-many adalah antara table Customer dan table Jual. Kedua buah table tersebut memiliki field Cust_id. Field Cust_id tersebut merupakan Primary Key dari table Customer, dansekaligus merupakan Foreign Key dari table Jual. Kedua field yangberhubungan tersebut tidak perlu memiliki nama yang sama antarkeduanya,
namun hal tersebut merupakan praktek yang dianjurkan.Mengambil Data: Query SQL SELECT
Adalah sangat jarang apabila sebuah aplikasi database tidak menggunakanproses mengambil maupun menampilkan data. Sekali Anda sudah memiliki data dalam database, Anda akan menggunakannya dalam cara apa pun ke dalam aplikasi Anda. Anda akan perlu untuk melihatdata tersebut dan menganalisanya dalam cara-cara yang berbeda-beda,dengan cara mengubah-ubah variable filternya, pengurutannya, maupun
adanya perhitungan-perhitungan yang diaplikasikan pada data mentahnya.
Statement SQL SELECT merupakan perintah yang akan Anda gunakan untuk memilih, atau select, data yang Anda butuhkan daridalam database ke aplikasi Anda.
Sebuah statement SQL SELECT dapat dipecah dalam beberapa elemen,di mana setiap elemen diawali dengan sebuah keyword. Meskipun bukanmerupakan suatu keharusan, namun konvensi yang biasa diterapkan dalam penulisan keyword ini adalah dengan menggunakan huruf kapital.
Dalam awal bab ini, Anda akan mempelajari pada elemen yang palingbanyak digunakan dari sebuah statement SELECT, yaitu:
SELECT
FROM
WHERE
ORDER BY
Clause SELECT ... FROM
Sebuah statement SELECT yang paling sederhana hanya memiliki dua bagian: (1) field apa yang akan Anda tampilkan dan (2) dari table apa field tersebut berasal.
Jika Anda ingin mendapatkan semua informasi dari semua customer daridalam table Customer, maka Anda bisa menggunakan tanda asterisk (*)sebagai sebuah shortcut untuk semua field, dan query Anda akan terlihatseperti
SELECT * FROM Customer
Jika Anda hanya ingin mendapatkan beberapa field saja, Anda bisa secara eksplisit menuliskannya sebagai sebuah daftar yang dipisahkan dengan tanda koma, seperti
SELECT Cust_nama, Cust_email, Cust_reg_on, Cust_kota
FROM Customer
yang akan memberikan data berisi field-field yang Anda tuliskan dalam perintah query:
Hasil dari statement query
Menuliskan field yang dibututhkan secara eksplisit juga akan mengizinkan
Anda untuk mengatur pengurutan dari field yang akan diambil, sehingga jika misalnya Anda menginginkan data Cust_reg_on ditampilkan
terlebih dulu dibanding semua field lainnya, Anda bisa menuliskanSELECT Cust_reg_on, Cust_nama, Cust_email,Cust_kota
FROM Customer
Clause WHERE
Hal lain yang perlu dipelajari selanjutnya adalah membatasi, atau menggunakanfilter, pada data yang Anda dapatkan dari database. Dengan menambahkan sebuah clause WHERE pada statement SELECT, berartiAnda menambahkan sebuah kondisi yang harus dipenuhi oleh data-data yang terpilih. Hal ini akan membatasi jumlah baris data yang merupakan jawaban dari query yang ditanyakan.
Anda bisa melanjutkan dari query sebelumnya, dan membatasi hasilnyauntuk hanya menunjukkan customer yang tinggal di Batam saja
SELECT Cust_reg_on, Cust_nama, Cust_email,Cust_kota
FROM Customer
WHERE Kota = ‘Batam’
yang akan memberikan hasil:
Hasil setelah diberikan filter
Jika yang Anda inginkan adalah kebalikannya, customer yang tidaktinggal di Batam, Anda akan menuliskan
SELECTCust_reg_on, Cust_nama, Cust_email,Cust_kota
FROM Customer
WHERE Kota <> ‘Batam’
Tidaklah diperlukan untuk memastikan kesamaan tipe data; Anda bisajuga menggunakan operator sama dengan (atau tidak sama dengan) seperti
yang Anda inginkan secara langsung. Misalnya, Anda hendakmenampilkan semua customer yang terdaftar pada atau setelah tanggal tertentu, di mana Anda akan menuliskan
SELECT Cust_reg_on, Cust_nama, Cust_email,Cust_kota
FROM Customer
WHERE Cust_reg_on >= ‘2-May-2013’
dan mendapatkan hasil sebagai berikut:
Penggunaan filter
Tentu saja Anda bisa menuliskan kondisi yang lebih rumit. Cara yang paling jelas untuk melakukan itu adalah dengan menggunakan kondisiyang lebih dari satu pada clause WHERE. Jika Anda ingin mengetahuicustomer mana yang diregistrasi setelah tanggal tertentu, dan hanyayang tinggal di Batam saja, maka Anda bisa menuliskan
SELECT Cust_reg_on, Cust_nama, Cust_email,Cust_kota
FROM Customer
WHERE Cust_reg_on >= ('2-May-2013')AND (cust_kota = 'Batam')
yang akan menghasilkan:
\
Kondisi yang lebih dari satu
Perhatikan juga bahwa SQL memiliki operator spesial BETWEEN yang akan menguji apakah nilai berada di antara dua buah nilai lain. Hal inimemperbolehkan Anda untuk menulis query lain sebagai
SELECT Cust_reg_on, Cust_nama, Cust_email, Cust_kota
FROM Customer
WHERE Cust_reg_on BETWEEN '01-May-2013' AND'31-August-2013'
dengan hasil:
Operator BETWEEN
Anda juga bisa menambahkan operator NOT, untuk mendapatkan baris data yang tidak berada di antara dua tanggal:
SELECT Cust_reg_on, Cust_nama, Cust_email, Cust_kota
FROM Customer
WHERE Cust_reg_on NOT BETWEEN '01-May-2013' AND '31-August2013'
dengan hasil:
Operator NOT BETWEEN
Selanjutnya, bagaimana jika Anda hendak menguji apakah nilai dari sebuah field sama untuk beberapa buah nilai lain? Anda bisa menggunakan
operator OR untuk menggabungkan kedua kondisi, sepertiSELECT Cust_reg_on, Cust_nama, Cust_email, Cust_kota
FROM Customer
WHERE (Cust_kota = 'Bintan') OR (Cust_kota = 'Dumai')
Namun, jika pada data terdapat banyak kondisi nilai yang ingin Andabandingkan, Anda bisa menggunakan operator IN untuk menguji terhadap
suatu kumpulan nilai. Misalnya, query di atas dapat ditulis ulang sebagai berikut:
SELECT Cust_reg_on, Cust_nama, Cust_email, Cust_kota
FROM Customer
WHERE Cust_kota IN ('Bintan', 'Dumai')
yang akan menghasilkan baris data sebagai berikut:
Operator IN
Dan seperti pada operator BETWEEN, di sini Anda juga akan mendapatkan
lawan dari hasilnya, dengan melakukan query untuk kota yang tidak terdapat pada kumpulan kota:
SELECT Cust_reg_on, Cust_nama, Cust_email, Cust_kota
FROM Customer
WHERE Cust_kota NOT IN ('Bintan', 'Dumai')
Akhirnya, operator LIKE mengizinkan Anda untuk melakukan suatu pengenalan pola dasar dengan menggunakan karakter wildcard. Untuk Microsoft SQL Server, karakter wildcard yang bisa digunakan adalahsebagai berikut:
Beberapa contoh akan dapat menjelaskan aturan-aturan tersebut.
WHERE Cust_nama LIKE ‘A_i’akan mendapatkan semua nama yang hanya terdiri dari tiga karakter, yang diawali dengan huruf‘A’ dan diakhir dengan huruf ‘i’ (misal Ani, Aji).
WHERE Cust_kota LIKE ‘B%’akan mendapatkan semua customer yang tinggal di kota yang diawali dengan huruf ‘B’.
WHERE Cust_nama LIKE ‘A[jn]i’akan mendapatkan semua customer yang namanya diawali dengan huruf ‘A’ dan diakhiri dengan huruf ‘i’, namun huruf ditengahnya hanya yang berupa huruf ‘j’ dan ‘n’ saja.
WHERE (Cust_nama NOT LIKE 'S%') AND (Cust_nama NOT LIKE 'D%')yang akan menghasilkan baris data:
WHERE Cust_nama LIKE ‘A[^j]%’akan mendapatkan semua nama yang diawali dengan huruf ‘A’ di mana huruf keduanya bukan ‘j’.
Di sini Anda juga masih bisa untuk menempatkan operator NOT;misalnya Anda hendak mendapatkan data customer yang namanya tidakdiawali dengan huruf ‘S’ atau ‘D’, maka Anda bisa menuliskan
SELECT Cust_reg_on, Cust_nama, Cust_email, Cust_kota
FROM Customer
WHERE (Cust_nama NOT LIKE 'S%') AND (Cust_nama NOT LIKE 'D%')
yang akan menghasilkan baris data:
Penggunaan operator NOT pada operator LIKE
Clause ORDER BY
Sampai saat ini, Anda mempelajari cara untuk melakukan seleksi pada data; yaitu menentukan kondisi-kondisi untuk memilih baris data yang mana yang akan dimasukkan dalam baris data akhir untuk diambil daridatabase. Sekali Anda sudah menentukan field dan baris yang akan dimasukkan sebagai hasil dari query SELECT Anda, Anda mungkin mau mengatur urutan baris data yang akan ditampilkan.Untuk mengurutkan baris data, Anda menggunakan clause ORDER BY.Clause ORDER BY ini memerlukan sebuah nama field yang akan digunakan
untuk pengurutannya. Jika sekarang Anda kembali pada statement SELECT Anda yang pertama, Anda bisa mengurutkan hasilnya dengan kunci pada nama Kota dengan statement berikut:
SELECT Cust_nama, Cust_email, Cust_reg_on, Cust_kota
FROM Customer
ORDERBY Cust_kota
Secara default, pengurutan terhadap sebuah field adalah bersifat ascending (dari nilai terendah sampai nilai tertinggi), seperti ditunjukkan
dalam hasil berikut:
Hasil pengurutan terhadap Kota
Namun jika Anda menghen aki pengurutan yang descending, Anda bisajuga menambahkan keyword
dd DESC setelah nama field tersebut.
Clause ORDER BY tidak dibatasi hanya pada sebuah field tunggal. Andabisa menggunakan sebuah kumpulan field yang dibatasi dengan tandakoma, di mana baris data akan diurutkan berdasar field pertama yangdiberikan pada statement, dan kemudian pada field berikut dalamstatement. Jadi jika misalnya Anda hendak menambahkan field Nama customer pada statement SELECT tersebut, di mana Anda hendak mengurutkan
berdasar pada Kota dan Nama, maka Anda bisa menuliskannya:
SELECT Cust_nama, Cust_email, Cust_reg_on, Cust_kota
FROM Customer
ORDERBY Cust_kota, Cust_namaDESC
Jika Anda ingin membedakan pengurutan pada kata kuncinya, makaAnda bisa juga menuliskannya sebagai berikut:
SELECT Cust_nama, Cust_email, Cust_reg_on, Cust_kota
FROM Customer
ORDERBY Cust_nama ASC, Cust_kota DESC
Hasil dari query tersebut adalah sebagai berikut:
Kunci pengurutan yang berbeda
Perhatikan bahwa sebuah field tidak perlu dimasukkan dalam hasil akhirbaris data apabila Anda hendak menggunakannya dalam clause ORDER
BY. Jadi jika Anda tidak perlu untuk melihat data Kota, tapi perlu untukmengurutkan semua data berdasar field tersebut, maka Anda bisa menuliskan
query Anda sebagai
SELECT Cust_nama, Cust_email, Cust_reg_on
FROM Customer
ORDERBY Cust_nama ASC, Cust_kota DESC
Di mana urutan hasil yang ditunjukkan adalah sama seperti pada query sebelumnya:
Pengurutan tanpa menampilkan field kunci
Penggunaan SQL pada Aplikasi
Dalam aplikasi yang akan Anda coba buat nantinya, penggunaan SQLtidak akan hanya terbatas pada statement SELECT saja. Ada saatnyaAnda memerlukan SQL pada saat inisialisasi awal, seperti pembuatan table. Anda juga pasti membutuhkan suatu cara untuk menyimpan informasi yang Anda masukkan dalam aplikasi ke dalam database Anda.
Dalam aplikasi, nantinya akan ada form-form yang menampilkan data apa-adanya. Di sini, dimaksudkan adalah semua data pada table yang dimaksud ditampilkan semua. Untuk itu, statement yang dipakai masihtetap menggunakan statement SELECT.
Seperti pada form Customer, maka statement yang dibutuhkan adalah
SELECT * FROM Customer
Sedangkan untuk form Login, dibutuhkan suatu statement SELECT dengan dua buah kondisi, yaitu bahwa Nama user ada di dalam table,dan pasangan Password-nya adalah benar. Untuk itu, digunakan sebuah statement SELECT dengan format:
SELECT*FROM Users
WHERE User_nama ='<nama_user>'AND User_pwd ='<pwd_user>'
Statement ini akan mencari dari seluruh record yang ada dalam table Users, untuk sebuah record yang memenuhi dua kondisi, yaitu field User_nama dan field User_pwd sama dengan nilai yang diinputkandalam form.
Mengambil Data dari Dua buah Table
Statement SELECT bisa digunakan untuk mengambil data dari lebih dari satu buah table. Seperti misalnya data penjualan, di mana dalam sebuah tableJual, ada hubungan ke table Customer, seperti ditunjukkan dalam gambar berikut.
Hubungan antara dua buah table
Jadi, dari dalam table Jual, selain ada field Jual_id sebagai kunci (Primary Key), juga ada sebuah field lain, yaitu field Cust_id. Field ini disebut sebagai Foreign Key pada table Jual, sedangkan pada table Customer, field Cust_id tersebut adalah Primary Key.
Jadi, jika Anda tuliskan statement SELECT berikut:
SELECT Jual_id, Jual_tgl, Cust_id
FROM Jual
Maka, hasil yang akan ditunjukkan adalah:
Hasil dari query table Jual
Sedangkan apabila Anda menampilkan data dari table Customer, makahasil yang didapatkan adalah:
Hasil dari query table Customer
Jadi, bagaimana caranya untuk menampilkan nama customer pada querytable Jual? Di sini Anda akan belajar cara untuk melakukan JOIN padakedua field pada masing-masing table.
Yang pertama harus Anda ketahui adalah bahwa Anda bisa meletakkanlebih dari satu buah table setelah clause FROM, dengan membatasisetiap nama table dengan tanda koma.
Dan karena jumlah table adalah lebih dari satu, maka penulisan namafield setelah clause SELECT haruslah menggunakan penunjuk dari tablemana field tersebut dipilih. Format penulisannya adalah dengan menggunakan
nama table, diikuti dengan tanda titik, dan kemudian nama ielddari table tersebut. Hal ini sebenarnya tidak diperlukan, selama f
ffieldtersebut hanya ada pada salah satu table saja. Namun sebaliknya, haltersebut wajib dituliskan jika field tersebut ada pada kedua table.
Yang terakhir adalah penggunaan clause WHERE, di mana di sini ditentukan
kondisi bahwa nilai dari field untuk kedua table tersebut adalahsama.
Jadi, query di atas bisa Anda tuliskan kembali sebagai berikut:
SELECT Jual_id, Jual_tgl, Cust_nama
FROM Jual, Customer
WHERE Jual.Cust_id = Customer.Cust_id
Dengan hasil query tersebut:
Hasil dari dua buah table yang digabungkan
Mengambil Data dari Tiga buah Table
Bagaimana dengan penulisan SQL untuk data yang diambil dari tiga buahtable? Seperti misalnya untuk daftar barang. Selain diambil dari table Barang, daftar ini juga perlu untuk mengambil data nama kategori daritable Kategori, dan data stock barang dari table Barang_details.
Perhatikan struktur table, dan bisa Anda lihat bahwa antara table Barang dengan table Kategori terdapat sebuah relationship, dengan pasangan Primary Key-Foreign Key pada field Kat_id. Sedangkan untuk table Barang dengan Barang_details, field kunci yang digunakan adalah field Brg_id.
Penulisannya sama saja dengan ketiga aturan di atas, sehingga SQLSELECT yang Anda tuliskan akan berformat:
SELECT Barang.Brg_id, Brg_nama, Brg_unit, Brg_barcode,
Brg_hpp, Kat_nama, Brg_det_stock
FROM Barang, Kategori, Barang_details
WHERE Barang.Kat_id = Kategori.Kat_id AND Barang.Brg_id =
Barang_details.Brg_idPerhatikan bahwa Anda menggunakan operator AND untuk memastikan bahwa kedua kondisi tersebut harus dipenuhi, untuk kemudian mem-berikan hasil sebagai berikut:
Hasil penggabungan tiga buah table
Dalam aplikasi Anda nantinya ada beberapa form yang akan digunakan untuk menerima masukan dari user, dan kemudian akan menyimpan data yang didapat ke dalam database.
Menyimpan Data
Untuk menyimpan data baru pada sebuah table di dalam database, Andatidak lagi menggunakan clause SELECT ... FROM, namun menggunakan clause INSERT INTO ...
Pada pengisian data Barang, misalnya, user akan diminta untuk memasukkan
data-data barang baru yang akan dimasukkan. Selain itu, status stock barang tersebut juga dicatat, dalam hal ini, pada table Barang_details.
Table Barang_details ini hanya memiliki dua buah field, dan untukmemasukkan data, dibutuhkan format SQL INSERT sebagai berikut:
INSERT INTO Barang_details (Brg_id, Brg_det_stock)
VALUES (‘<data_brg_id>’, <data_brg_det_stock>)
Daftar field yang akan di isi setelah nama table tidak harus dituliskan, namun akan sangat membantu pembacaan bila dituliskan, jadi bisa menghindarkan kesalahan peletakan isi data yang akan disimpan.
Dengan tipe field data string, nilai yang akan disimpan agar diapitdengan tanda petik tunggal (misal, ‘Jl. Bunga Raya’). Untuk tipe numeric, seperti Integer, tuliskan apa adanya.
Jadi, jika dicontohkan dengan statement di atas:
INSERT INTO Barang_details (Brg_id, Brg_det_stock)
VALUES (‘PNC-UG’, 6)
akan membuat sebuah record baru pada table Barang_details, dengan isi field Brg_id = ‘PNC-UG’ dan field Brg_det_stock = 6.
Mengubah Data
Pengubahan data diperlukan dalam sebuah aplikasi yang berbasis pada data. Bagaimana jika seandainya sebuah produk naik harga modal-nya, disebabkan karena kenaikan ongkos kirim, atau penambahan pajak, dan sebagainya? Aplikasi Anda harus memiliki fitur untuk menemukan data yang mau di cari, dan kemudian mengubah salah satu, atau semua field datanya.
Format penulisannya cukup jelas. Sebagai contoh adalah Anda hendakmengubah stock barang, untuk barang dengan kode Brg_id = ‘PNC-UG’.Untuk itu, Anda perlu melakukan UPDATE terhadap table Barang_details.
UPDATE Barang_details
SET Brg_det_stock = 8
WHERE Brg_id ='PNC-UG'
Tanpa menggunakan kondisi WHERE, maka statement UPDATE ini akan mengubah data untuk semua record, tidak terkecuali. Misalkan, karenakenaikan harga BBM, maka perhitungan harga pokok penjualan untuk
semua barang dinaikkan sebesar 5%, maka perintah untuk itu adalahsebagai berikut:
UPDATE Barang
SET Brg_hpp=Brg_hpp * 1.05
Menghapus Data
Penghapusan data, bagaimanapun juga, tetap diperlukan dalam suatu aplikasi. Misalnya, sebuah produk tidak lagi bisa didatangkan, karena sudah tidak diproduksi lagi oleh supplier. Atau seorang user tidak lagimenjadi karyawan toko Anda.
Untuk menghapus, digunakan statement DELETE.
DELETEFROM Barang
WHERE Brg_id ='PNC-UG'
Harap diperhatikan di sini, bahwa sebelumnya Anda sudah menetapkan relationship untuk table Barang ini terhadap table Barang_details.Record pada table Barang_details akan mengacu pada table Barang.Karena itu, apabila Anda menghapus sebuah record pada table Barang,maka SQL Server akan memberikan pesan kesalahan, dan proses penghapusan
akan gagal. Kondisi ini disebut dengan Referential Integrity,yaitu konsep yang memastikan bahwa relationship antar-table tetapkonsisten.
Dalam hal penghapus data Barang ini, yang harus Anda lakukan supaya tetap memenuhi aturan referential integrity tersebut adalah melakukan penghapusan untuk record tersebut dari table Barang_details terlebihdahulu. Setelah record pada table itu terhapus, maka proses penghapusan
pada table Barang akan bisa dilakukan.
DELETEFROM Barang_details
WHERE Brg_id ='PNC-UG'
DELETEFROM Barang
WHERE Brg_id ='PNC-UG'
Dan seperti pada statement UPDATE di atas, statement DELETE tanpa menggunakan kondisi WHERE akan menghapus seluruh record dalam table tersebut.
Misalkan toko Anda melakukan stock-opname, dan Anda akan menghapus
semua data stock, untuk kemudian memasukkan data baru. Maka,perintah penghapusan Anda bisa ditulis sebagai berikut:
DELETEFROM Barang_details
Jika kemudian Anda akan membuang seluruh table maupun database,Anda bisa menggunakan perintah DROP.
DROP TABLE Barang_details
Semoga Bermanfaat bagi anda : Salam Sukses :D
Jangan Lupa Tinggalkan Komentar di bawah ini :D