1. FUNGSI SELECT (MENAMPILKAN DATA) PADA SEBUAH TABEL
Fungsi select dalam bentuk sederhana hanya mempunyai pasangan kata from, yang berarti memilih tabel yang akan di tampilkan. Bentuk umumnya sbb :
Select
From
Where
Kita bisa melihat data yang ada di dalam tabel dengan menggunakan perintah "SELECT". Perintah SELECT adalah perintah yang akan sering kita gunakan nantinya. Kita mulai dengan cara yang paling sederhana.
mysql> select * from t_mhs ;
Perintah di atas menampilkan seluruh data yang ada di dalam tabel karyawan, karena menggunakan tanda asterik "*" di dalam perintah SELECT. Bagaimana kalau kita hanya mau menampilkan kolom nama dan gender saja?
mysql> select nama, gender from t_mhs
-> ;
Kalau kita hanya mau menampilkan data-data t_mhs yang berjenis kelamin perempuan saja, Cukup dengan menambahkan perintah "WHERE"
mysql> select nama, gender from t_mhs
-> where gender="Perempuan"
-> ;
Kita tampilkan data berdasarkan urutan nama mahasiswa dengan menambahkan perintah "ORDER BY" pada "SELECT":
mysql> select * from t_mhs
-> order by nama ;
Nah kalo yang sekarang diurut berdasarkan nama, tetapi dengan urutan terbalik (descending). Cukup dengan menambahkan perintah "DESC" pada SELECT:
mysql> select * from t_mhs
-> order by nama DESC ;
Operator Pembanding dan Operator Logika
Sudah saatnya kita melangkah ke permainan data yang lebih mengasyikan lagi dengan menggunakan dua operator, yaitu Operator Pembanding dan Operator Logika. Kedua jenis operator ini akan sering digunakan dalam proses "query" data.
Operator Pembanding
Operator Pembanding Keterangan
Lebih besar >
Lebih kecil <= Lebih besar atau sama dengan >=
Lebih kecil atau sama dengan <= Sama dengan = Tidak sama dengan <>
Operator Logika
Operator Logika Keterangan
Dan AND atau &&
Atau OR atau ||
Lebih besar atau sama dengan NOT atau !
Lebih kecil atau sama dengan <= Tidak sama dengan <>
Berikut ini adalah penerapan dari operator di atas:
Kita tampilkan data tanggal pinjam yang tanggal pinjamnya setelah tanggal 2009-05-21.
mysql> select *
-> from t_pinjam
-> where tgl_pinjam > "2009-05-20";
Sekarang kita tampilkan data peminjam yang tanggal pinjamnya antara tanggal 22 Mei 2009 dan 24 Mei 2009 :
mysql> select *
-> from t_pinjam
-> where tgl_pinjam >= "2009-05-22"
-> and tgl_pinjam <= "2009-05-24"; Baik kita lanjutkan tutorial ini dengan perintah-perintah lainnya. Kita akan menampilkan data buku yang id golongannya ”130”: mysql> select * from t_buku
-> where id_gol="130" ;
Kita tampilkan data buku yang id_golongannya bukan ”130”:
mysql> select * from t_buku
-> where id_gol !="130" ;
Perintah di atas dapat juga menggunakan tanda "<>', dan hasilnya tetap sama dengan di atas:
mysql> select * from t_buku
-> where id_gol <>"130" ;
Fungsi Agregasi
Beberapa fungsi untuk statistik dasar adalah:
Sekarang berapa mahasiswa yang meminjam buku, dengan kode buku ”12” ?
mysql> select count(*) from t_pinjam
-> where id_buku =”12”;
Berapakah rata-rata denda dari tabel denda?
mysql> select avg(denda) from t_denda;
Berapakah denda yang terbesar?
mysql> select max(denda) from t_denda;
Berapakah denda yang terkecil?
mysql> select min(denda) from t_denda;
Dan berapakah jumlah denda keseluruhan pada table denda?
mysql> select sum(denda) from t_denda;
Operator Precedence
Operator precedence adalah tingkatan hirarki dalam memproses serangkaian operator.
Tingkatan hirarki Jenis Operator
Paling Tinggi BINARY
NOT !
- (unary minus)
* / %
+ -
<< >>
&
|
< <= = <=> != <> >= > IN IS LIKE REGEXP RLIKE
BETWEEN
AND &&
Paling Rendah OR ||
Semakin keatas posisi operator, maka semakin tinggi tingkat hirarki operator tersebut. Begitu pula sebaliknya, semakin rendah posisinya maka akan semakin lemah hirarkinya.
Untuk operator yang sama kuat, misal + dan - digabung dengan operator * / %, maka akan ditentukan hirarkinya tergantung dari posisi mana yang paling kiri/paling awal ditemukan. Dan untungnya posisi hirarki ini dapat diubah dengan bantuan tanda kurung "(" dan ")". Sekarang kita lihat penerapannya.
mysql> select 10+15-11*2, (10+15-11)*2,
-> 2*6-5, 2*(6-5) ;
+------------+--------------+-------+---------+
| 10+15-11*2 | (10+15-11)*2 | 2*6-5 | 2*(6-5) |
+------------+--------------+-------+---------+
| 3 | 28 | 7 | 2 |
+------------+--------------+-------+---------+
1 row in set (0.00 sec)
Anda bisa perhatikan bahwa walaupun angka dan operatornya sama, tapi hasilnya bisa berbeda. Dan itu karena adanya peranan dari tanda kurung "(" dan ")" yang akan mengubah peta posisi hirarki operator....
Operator LIKE, NOT LIKE, REGEXP
Operator LIKE, NOT LIKE, REGEXP akan banyak kita gunakan terutama dalam operasi karakter.
Sekarang kita kan coba menggunakan operator LIKE. Operator LIKE ini digunakan untuk mencari data yang "menyerupai" atau "hampir sama" dengan kriteria tertentu. Biasanya untuk mencari data string/teks. Simbol "%" digunakan untuk membantu pelaksanaan operator LIKE. Posisi "%" sangat berpengaruh dalam menentukan kriteria. Kita langsung dengan contoh-contohnya saja ya biar lebih jelas penggunaannya...
Tampilkan data mahasiswa yang namanya berawalan huruf "a": (perhatikan posisi simbol persennya "%")
mysql> select *
-> from t_mhs
-> where nama LIKE "a%" ;
Tampilkan data mahasiswa yang namanya berakhiran huruf "i". Perhatikan posisi penulisan tanda "%".:
mysql> select *
-> from t_mhs
-> where nama LIKE "%a" ;
Contoh-contoh berikut akan lebih memudahkan pemahaman tentang pemakaian fungsi like.
• “a%” kata yang awalnya dimulai dengan a.
• ”%a%” kata yang di tengah-tengahnya mengandung karakter a.
• " _ _ _ “ kata yang tepat tiga karakter.
• ” _ _ _ %” kata yang minimum terdiri dari 3 karakter.
Bagaimana caranya agar operator LIKE dapat membedakan huruf besar dan kecil... Sederhana saja, cukup dengan menambahkan kata BINARY saja setelah perintah LIKE (sehingga perintahnya menjadi LIKE BINARY). Kita lihat contohnya...
mysql> select *
-> from t_mhs
-> where nama LIKE BINARY "A%" ;
Ya dengan menggunakan LIKE BINARY, penulisan huruf "a" akan dibedakan artinya dengan "A".
Operator REGEXP (singkatan dari REGular EXPressions) merupakan bentuk lain dari operator LIKE, dengan fungsi yang lebih disempurnakan. Operator REGEXP biasanya ditemani juga dengan simbol-simbol tertentu dalam melaksanakan tugasnya, seperti:
Simbol Keterangan
. Satu tanda titik (.) untuk mewakili satu karakter
[?] Untuk mewakili beberapa karakter atau range yang ditentukan.
^ Untuk posisi awal dari sebuah kriteria yang ditentukan
$ Untuk posisi akhir dari sebuah kriteria yang ditentukan
Kita langsung saja pada contohnya. Tampilkan data mahasiswa yang namanya berawalan huruf 'a':
mysql> select *
-> from t_mhs
-> where nama REGEXP "^a" ;
Tampilkan data mahasiswa yang namanya berawalan huruf 'a' sampai dengan huruf 'd':
mysql> select *
-> from t_mhs
-> where nama REGEXP "^[a-g]"
-> order by nama ;
Tampilkan data mahasiswa yang namanya berakhiran huruf "i":
mysql> select *
-> from t_mhs
-> where nama REGEXP "i$"
-> order by nama ;
Tampilkan data mahasiswa yang panjang namanya 10 karakter:
mysql> select *
-> from t_mhs
-> where nama REGEXP "^..........$" ;
Atau perintah diatas bisa juga ditulis dengan:
mysql> select *
-> from t_mhs
-> where nama REGEXP "^.{10}$" ;
Nah, kurang lebih itulah dasar-dasar MySQL. Ini baru tutorial perkenalan. Kita akan bertemu lagi dengan tutorial berikutnya, dasar-dasar database relasi. Kemudian dilanjutkan dengan tutorial penerapan sederhana "database relasi dengan menggunakan 2 buah tabel".
2. FUNGSI SELECT (MENAMPILKAN DATA) PADA BEBERAPA TABEL
Misalnya untuk menampilkan nama dari t_mhs dan no_telp dari t_telp:
mysql> select nama,telp from tmhs
-> inner join t_telp
-> on t_mhs.nim=t_telp.nim;
NIM yang ada di t_mhs dan NIM yang ada di t_telp dijadikan sebagai relasi / penghubung antara t_mhs dan t_telp
Atau bisa ditulis sebagai berikut
mysql> select nama,telp from t_mhs,t_telp
-> where t_mhs.nim=t_telp.nim;
Tampilkan nama mahasiswa yang meminjam buku dengan judul ”Pemrograman Web” :
mysql> select nama from t_mhs,t_pinjam,t_buku
-> where t_mhs.nim=t_pinjam.nim and
-> t_pinjam.id_buku=t_buku.id_buku and judul=’Pemrograman Web’;
NIM yang ada di t_mhs dan NIM yang ada di t_pinjam digunakan untuk menghubungkan t_mhs dengan t_pinjam. Id_buku yang ada di t_pinjam dan id_buku yang ada di t_buku digunakan untuk menghubungkan t_pinjam dengan t_buku
3. Fungsi Renaming (Penggantian Nama)
SQL memberikan fasilitas untuk melakukan renaming (penggantian nama) suatu kolom ataupun tabel. Sebagai contoh :
mysql> select nama as nama_mahasiswa from t_mhs a,t_pinjam b,t_buku c
-> where a.nim=b.nim and
-> b.id_buku=c.id_buku and judul=’Pemrograman Web’;
4. Fungsi Delete (Hapus Data)
Delete yang dilakukan hanya bisa diberikan pada record, bukan pada data kolom tertentu. Untuk menghapus data pada kolom tertentu menggunakan perintah update. Sintaks delete secara umum berbentuk :
Delete from
Where
Contoh sintaks untuk menghapus data telp pada t_telp dengan nim “117”:
mysql> delete from t_telp
-> where nim=’117’;
5. Fungsi Update (Meng-edit Data)
Update merupakan perintah untuk mengubah data yang sudah ada di dalam tabel. Perintah update memiliki sintaks secara umum sebagai berikut
Update
Set =
Where
Contoh penggunaan sintaks update untuk mengedit nama mahasiswa dari ”Agus Saputra” menjadi “Kadek Saputra” dengan nim “112” pada t_mhs
mysql> update t_mhs set nama=”Kadek Saputra”
-> where nim=’117’;
sumber (http://idupagayatri.blogspot.com/2010/01/basis-data-sintak.html)
Fungsi select dalam bentuk sederhana hanya mempunyai pasangan kata from, yang berarti memilih tabel yang akan di tampilkan. Bentuk umumnya sbb :
Select
From
Where
Kita bisa melihat data yang ada di dalam tabel dengan menggunakan perintah "SELECT". Perintah SELECT adalah perintah yang akan sering kita gunakan nantinya. Kita mulai dengan cara yang paling sederhana.
mysql> select * from t_mhs ;
Perintah di atas menampilkan seluruh data yang ada di dalam tabel karyawan, karena menggunakan tanda asterik "*" di dalam perintah SELECT. Bagaimana kalau kita hanya mau menampilkan kolom nama dan gender saja?
mysql> select nama, gender from t_mhs
-> ;
Kalau kita hanya mau menampilkan data-data t_mhs yang berjenis kelamin perempuan saja, Cukup dengan menambahkan perintah "WHERE"
mysql> select nama, gender from t_mhs
-> where gender="Perempuan"
-> ;
Kita tampilkan data berdasarkan urutan nama mahasiswa dengan menambahkan perintah "ORDER BY" pada "SELECT":
mysql> select * from t_mhs
-> order by nama ;
Nah kalo yang sekarang diurut berdasarkan nama, tetapi dengan urutan terbalik (descending). Cukup dengan menambahkan perintah "DESC" pada SELECT:
mysql> select * from t_mhs
-> order by nama DESC ;
Operator Pembanding dan Operator Logika
Sudah saatnya kita melangkah ke permainan data yang lebih mengasyikan lagi dengan menggunakan dua operator, yaitu Operator Pembanding dan Operator Logika. Kedua jenis operator ini akan sering digunakan dalam proses "query" data.
Operator Pembanding
Operator Pembanding Keterangan
Lebih besar >
Lebih kecil <= Lebih besar atau sama dengan >=
Lebih kecil atau sama dengan <= Sama dengan = Tidak sama dengan <>
Operator Logika
Operator Logika Keterangan
Dan AND atau &&
Atau OR atau ||
Lebih besar atau sama dengan NOT atau !
Lebih kecil atau sama dengan <= Tidak sama dengan <>
Berikut ini adalah penerapan dari operator di atas:
Kita tampilkan data tanggal pinjam yang tanggal pinjamnya setelah tanggal 2009-05-21.
mysql> select *
-> from t_pinjam
-> where tgl_pinjam > "2009-05-20";
Sekarang kita tampilkan data peminjam yang tanggal pinjamnya antara tanggal 22 Mei 2009 dan 24 Mei 2009 :
mysql> select *
-> from t_pinjam
-> where tgl_pinjam >= "2009-05-22"
-> and tgl_pinjam <= "2009-05-24"; Baik kita lanjutkan tutorial ini dengan perintah-perintah lainnya. Kita akan menampilkan data buku yang id golongannya ”130”: mysql> select * from t_buku
-> where id_gol="130" ;
Kita tampilkan data buku yang id_golongannya bukan ”130”:
mysql> select * from t_buku
-> where id_gol !="130" ;
Perintah di atas dapat juga menggunakan tanda "<>', dan hasilnya tetap sama dengan di atas:
mysql> select * from t_buku
-> where id_gol <>"130" ;
Fungsi Agregasi
Beberapa fungsi untuk statistik dasar adalah:
- MAX : mencari data terbesar dari sekelompok data
- MIN : mencari data terkecil dari sekelompok data
- COUNT : mencari cacah data (data NULL tidak akan dimasukkan dalam perhitungan, kecuali disebutkan secara khusus)
- SUM : mencari jumlah dari sekumpulan data numeris
- AVG : mencari rerata dari sekumpulan data numeris
Sekarang berapa mahasiswa yang meminjam buku, dengan kode buku ”12” ?
mysql> select count(*) from t_pinjam
-> where id_buku =”12”;
Berapakah rata-rata denda dari tabel denda?
mysql> select avg(denda) from t_denda;
Berapakah denda yang terbesar?
mysql> select max(denda) from t_denda;
Berapakah denda yang terkecil?
mysql> select min(denda) from t_denda;
Dan berapakah jumlah denda keseluruhan pada table denda?
mysql> select sum(denda) from t_denda;
Operator Precedence
Operator precedence adalah tingkatan hirarki dalam memproses serangkaian operator.
Tingkatan hirarki Jenis Operator
Paling Tinggi BINARY
NOT !
- (unary minus)
* / %
+ -
<< >>
&
|
< <= = <=> != <> >= > IN IS LIKE REGEXP RLIKE
BETWEEN
AND &&
Paling Rendah OR ||
Semakin keatas posisi operator, maka semakin tinggi tingkat hirarki operator tersebut. Begitu pula sebaliknya, semakin rendah posisinya maka akan semakin lemah hirarkinya.
Untuk operator yang sama kuat, misal + dan - digabung dengan operator * / %, maka akan ditentukan hirarkinya tergantung dari posisi mana yang paling kiri/paling awal ditemukan. Dan untungnya posisi hirarki ini dapat diubah dengan bantuan tanda kurung "(" dan ")". Sekarang kita lihat penerapannya.
mysql> select 10+15-11*2, (10+15-11)*2,
-> 2*6-5, 2*(6-5) ;
+------------+--------------+-------+---------+
| 10+15-11*2 | (10+15-11)*2 | 2*6-5 | 2*(6-5) |
+------------+--------------+-------+---------+
| 3 | 28 | 7 | 2 |
+------------+--------------+-------+---------+
1 row in set (0.00 sec)
Anda bisa perhatikan bahwa walaupun angka dan operatornya sama, tapi hasilnya bisa berbeda. Dan itu karena adanya peranan dari tanda kurung "(" dan ")" yang akan mengubah peta posisi hirarki operator....
Operator LIKE, NOT LIKE, REGEXP
Operator LIKE, NOT LIKE, REGEXP akan banyak kita gunakan terutama dalam operasi karakter.
Sekarang kita kan coba menggunakan operator LIKE. Operator LIKE ini digunakan untuk mencari data yang "menyerupai" atau "hampir sama" dengan kriteria tertentu. Biasanya untuk mencari data string/teks. Simbol "%" digunakan untuk membantu pelaksanaan operator LIKE. Posisi "%" sangat berpengaruh dalam menentukan kriteria. Kita langsung dengan contoh-contohnya saja ya biar lebih jelas penggunaannya...
Tampilkan data mahasiswa yang namanya berawalan huruf "a": (perhatikan posisi simbol persennya "%")
mysql> select *
-> from t_mhs
-> where nama LIKE "a%" ;
Tampilkan data mahasiswa yang namanya berakhiran huruf "i". Perhatikan posisi penulisan tanda "%".:
mysql> select *
-> from t_mhs
-> where nama LIKE "%a" ;
Contoh-contoh berikut akan lebih memudahkan pemahaman tentang pemakaian fungsi like.
• “a%” kata yang awalnya dimulai dengan a.
• ”%a%” kata yang di tengah-tengahnya mengandung karakter a.
• " _ _ _ “ kata yang tepat tiga karakter.
• ” _ _ _ %” kata yang minimum terdiri dari 3 karakter.
Bagaimana caranya agar operator LIKE dapat membedakan huruf besar dan kecil... Sederhana saja, cukup dengan menambahkan kata BINARY saja setelah perintah LIKE (sehingga perintahnya menjadi LIKE BINARY). Kita lihat contohnya...
mysql> select *
-> from t_mhs
-> where nama LIKE BINARY "A%" ;
Ya dengan menggunakan LIKE BINARY, penulisan huruf "a" akan dibedakan artinya dengan "A".
Operator REGEXP (singkatan dari REGular EXPressions) merupakan bentuk lain dari operator LIKE, dengan fungsi yang lebih disempurnakan. Operator REGEXP biasanya ditemani juga dengan simbol-simbol tertentu dalam melaksanakan tugasnya, seperti:
Simbol Keterangan
. Satu tanda titik (.) untuk mewakili satu karakter
[?] Untuk mewakili beberapa karakter atau range yang ditentukan.
^ Untuk posisi awal dari sebuah kriteria yang ditentukan
$ Untuk posisi akhir dari sebuah kriteria yang ditentukan
Kita langsung saja pada contohnya. Tampilkan data mahasiswa yang namanya berawalan huruf 'a':
mysql> select *
-> from t_mhs
-> where nama REGEXP "^a" ;
Tampilkan data mahasiswa yang namanya berawalan huruf 'a' sampai dengan huruf 'd':
mysql> select *
-> from t_mhs
-> where nama REGEXP "^[a-g]"
-> order by nama ;
Tampilkan data mahasiswa yang namanya berakhiran huruf "i":
mysql> select *
-> from t_mhs
-> where nama REGEXP "i$"
-> order by nama ;
Tampilkan data mahasiswa yang panjang namanya 10 karakter:
mysql> select *
-> from t_mhs
-> where nama REGEXP "^..........$" ;
Atau perintah diatas bisa juga ditulis dengan:
mysql> select *
-> from t_mhs
-> where nama REGEXP "^.{10}$" ;
Nah, kurang lebih itulah dasar-dasar MySQL. Ini baru tutorial perkenalan. Kita akan bertemu lagi dengan tutorial berikutnya, dasar-dasar database relasi. Kemudian dilanjutkan dengan tutorial penerapan sederhana "database relasi dengan menggunakan 2 buah tabel".
2. FUNGSI SELECT (MENAMPILKAN DATA) PADA BEBERAPA TABEL
Misalnya untuk menampilkan nama dari t_mhs dan no_telp dari t_telp:
mysql> select nama,telp from tmhs
-> inner join t_telp
-> on t_mhs.nim=t_telp.nim;
NIM yang ada di t_mhs dan NIM yang ada di t_telp dijadikan sebagai relasi / penghubung antara t_mhs dan t_telp
Atau bisa ditulis sebagai berikut
mysql> select nama,telp from t_mhs,t_telp
-> where t_mhs.nim=t_telp.nim;
Tampilkan nama mahasiswa yang meminjam buku dengan judul ”Pemrograman Web” :
mysql> select nama from t_mhs,t_pinjam,t_buku
-> where t_mhs.nim=t_pinjam.nim and
-> t_pinjam.id_buku=t_buku.id_buku and judul=’Pemrograman Web’;
NIM yang ada di t_mhs dan NIM yang ada di t_pinjam digunakan untuk menghubungkan t_mhs dengan t_pinjam. Id_buku yang ada di t_pinjam dan id_buku yang ada di t_buku digunakan untuk menghubungkan t_pinjam dengan t_buku
3. Fungsi Renaming (Penggantian Nama)
SQL memberikan fasilitas untuk melakukan renaming (penggantian nama) suatu kolom ataupun tabel. Sebagai contoh :
mysql> select nama as nama_mahasiswa from t_mhs a,t_pinjam b,t_buku c
-> where a.nim=b.nim and
-> b.id_buku=c.id_buku and judul=’Pemrograman Web’;
4. Fungsi Delete (Hapus Data)
Delete yang dilakukan hanya bisa diberikan pada record, bukan pada data kolom tertentu. Untuk menghapus data pada kolom tertentu menggunakan perintah update. Sintaks delete secara umum berbentuk :
Delete from
Where
Contoh sintaks untuk menghapus data telp pada t_telp dengan nim “117”:
mysql> delete from t_telp
-> where nim=’117’;
5. Fungsi Update (Meng-edit Data)
Update merupakan perintah untuk mengubah data yang sudah ada di dalam tabel. Perintah update memiliki sintaks secara umum sebagai berikut
Update
Set =
Where
Contoh penggunaan sintaks update untuk mengedit nama mahasiswa dari ”Agus Saputra” menjadi “Kadek Saputra” dengan nim “112” pada t_mhs
mysql> update t_mhs set nama=”Kadek Saputra”
-> where nim=’117’;
sumber (http://idupagayatri.blogspot.com/2010/01/basis-data-sintak.html)
Structured Query Language (SQL) merupakan bahasa yang banyak digunakan dalam berbagai produk database. SQL dibangun di laboratorium IBM-San Jose California sekitar akhir tahun 70-an. Pertama kali dikembangkan sebagai bahasa di produk database DB2 yang sampai saat ini merupakan produk database andalan IBM. SQL sering di lafalkan dengan “sequel”. Saat ini organisasi standar America (ANSI) menetapkan standar bahasa SQL yaitu ANSI-92 standard.
Masing-masing vendor database memiliki dialeknya sendiri sebagaian besar spesifikasinya mengacu pada standar ANSI tersebut dengan berbagai ekstensi tambahan. SQL Server menggunakan bahasa Transact-SQL dalam produknya, sedangkan Oracle menggunakan PL/SQL. Dalam tutorial ini penulis menggunakan database NorthWind yang merupakan database sampel di SQL Server sebagai sarana latihan. Tools yang digunakan adalah Query Analyzer, yang dapat diakses dari menu Start > Program > Microsoft SQL Server > Query Analyzer. Anda juga dapat menggunakan produk database lain seperti MySQL atau Oracle dengan konsep yang sama.
Dasar SQL
Fungsi paling dasar dari SQL adalah untuk menampilkan data dari database. Data tersebut selanjutnya dapat difilter dan dimanipulasi sesuai kebutuhan aplikasi. Perintah perintah dalam SQL terbagi dalam 2 kelompok besar :
• Data Manipulation Language
• Data Definition Language
Menampilkan Data dengan Statement SELECT
Syntax paling dasar untuk mengambil data dari database adalah sebagai berikut :
SELECT column
FROM table
Buka query Analyser dan pastikan anda telah terkoneksi dengan database Northwind. Tuliskan kode berikut untuk menampilkan data customer yang terdapat di tabel Customers.
SELECT CustomerID, CompanyName, ContactName
FROM Customers
Untuk menampilkan semua kolom dari suatu table, digunakan tanda asterik (*), dari pada menyebutkan nama kolomnya satu per satu. Tiluskan statement berikut :
SELECT * FROM Customers
Maka akan ditampilkan seluruh kolom di table Customers yang berjumlah 11 kolom.
Meskipun cara ini sangat sederhana untuk menampilkan isi seluruh kolom dari suatu table, anda tetap dianjurkan untuk hanya mengambil data dari kolom yang anda perlukan saja. Pengambilan data yang tidak perlu mengakibatkan penurunan performa aplikasi.
Filter Data dengan WHERE
Perintah SELECT dan FROM diatas hanya membatasi jumlah kolom yang ditampilkan saja, sedangkan jumlah baris yang dihasilkan tidak dibatasi. Anda sering memerlukan hanya baris atau data yang memenuhi kriteria tertentu saja yang ditampilkan.
Klausa WHERE digunakan untuk menentukan kriteria RECORD yang ditampilkan. Syntax umumnya adalah sebagai berikut :
SELECT columns
FROM tables
WHERE Conditions
Perintah SQL diatas dapat dimodifikasi agar menampilkan data untuk customer dengan kode ALFKI saja.,Kode SQL nya menjadi sebagai berikut :
SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE CustomerID = 'ALFKI'
Anda juga dapat membuat beberapa kriteria sekaligus dengan klausa WHERE. Logika yang digunakan bias berupa OR (atau) serta AND (dan)
Misalkan perintah berikut :
SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE City = 'London' AND
ContactName = 'Thomas Hardy'
Dasar SQL
Fungsi paling dasar dari SQL adalah untuk menampilkan data dari database. Data tersebut selanjutnya dapat difilter dan dimanipulasi sesuai kebutuhan aplikasi. Perintah perintah dalam SQL terbagi dalam 2 kelompok besar :
• Data Manipulation Language
• Data Definition Language
Menampilkan Data dengan Statement SELECT
Syntax paling dasar untuk mengambil data dari database adalah sebagai berikut :
SELECT column
FROM table
Buka query Analyser dan pastikan anda telah terkoneksi dengan database Northwind. Tuliskan kode berikut untuk menampilkan data customer yang terdapat di tabel Customers.
SELECT CustomerID, CompanyName, ContactName
FROM Customers
Untuk menampilkan semua kolom dari suatu table, digunakan tanda asterik (*), dari pada menyebutkan nama kolomnya satu per satu. Tiluskan statement berikut :
SELECT * FROM Customers
Maka akan ditampilkan seluruh kolom di table Customers yang berjumlah 11 kolom.
Meskipun cara ini sangat sederhana untuk menampilkan isi seluruh kolom dari suatu table, anda tetap dianjurkan untuk hanya mengambil data dari kolom yang anda perlukan saja. Pengambilan data yang tidak perlu mengakibatkan penurunan performa aplikasi.
Filter Data dengan WHERE
Perintah SELECT dan FROM diatas hanya membatasi jumlah kolom yang ditampilkan saja, sedangkan jumlah baris yang dihasilkan tidak dibatasi. Anda sering memerlukan hanya baris atau data yang memenuhi kriteria tertentu saja yang ditampilkan.
Klausa WHERE digunakan untuk menentukan kriteria RECORD yang ditampilkan. Syntax umumnya adalah sebagai berikut :
SELECT columns
FROM tables
WHERE Conditions
Perintah SQL diatas dapat dimodifikasi agar menampilkan data untuk customer dengan kode ALFKI saja.,Kode SQL nya menjadi sebagai berikut :
SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE CustomerID = 'ALFKI'
Anda juga dapat membuat beberapa kriteria sekaligus dengan klausa WHERE. Logika yang digunakan bias berupa OR (atau) serta AND (dan)
Misalkan perintah berikut :
SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE City = 'London' AND
ContactName = 'Thomas Hardy'
Sortir Data dengan ORDER BY
ORDER BY digunakan untuk mengurutkan hasil pencarian data. Secara default data yang ditampilkan disortir berdasarkan urutan masuknya data ke dalam tabel. Dengan menggunakan ORDER BY anda dapat mengurutkan berdasarkan kolom tertentu yang anda kehendaki.
Bila anda perhatikan perintah SQL diatas maka data yang dihasilkan telah diurutkan berdasarkan kolom CustomerID. Anda dapat merubahnya dengan mengurutkan berdasarkan kolom ContactName dengan perintah berikut :
SELECT CustomerID, CompanyName, ContactName
FROM Customers
ORDER BY ContactName
Sehingga hasilnya adalah :
CustomerID CompanyName ContactName
Terlihat bahwa data telah diurutkan berdasarkan ContactName secara ascending (dari a ke z). Anda dapat membalik urutan menjadi dari z ke a dengan merubah klausa ORDER BY menjadi seperti berikut :
ORDER BY ContactName desc
Secara default urutan yang digunakan adalah ascending.
Selain itu dapat pula digunakan beberapa kriteria pengurutan. Artinya pengurutan dilakukan berdasarkan kolom yang disebut pertama, setelah itu kolom kedua, dan selanjutnya. Perintah diatas dapat ditambahkan sehingga menjadi sebagai berikut :
SELECT CustomerID, CompanyName, ContactName
FROM Customers
ORDER BY ContactName, CompanyName
Maka data akan diurutkan mengikuti ContactName, dan selanjutnya berdasarkan CompanyName.
Perintah ORDER BY juga dapat digabungkan dengan WHERE misalnya sebagai berikut :
SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE City = 'London' OR
ContactName = 'Thomas Hardy'
ORDER BY ContactName
Perhatikan baik-baik bahwa ORDER BY harus diletakkan setelah WHERE. Apabila urutan tersebut terbalik
maka statement SQL tidak dapat dijalankan dan menghasilkan pesan error berikut :
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'WHERE'.
Cari yang Mirip dengan LIKE
Apabila WHERE memfilter data berdasarkan kriteria tertentu yang sudah pasti, maka LIKE digunakan untuk memberikan kriteria yang tidak memiliki kepastian.
Misalkan anda ingin mencari nama produk yang dimulai dengan huruf c maka digunakan perintah berikut :
SELECT ProductID, ProductName
from Products
WHERE ProductName LIKE 'c%'
Perhatikan tanda % setelah huruf c tersebut, yang dapat diartikan sebagai : semua yang dimulai dengan c.
Keluaran perintah tersebut adalah :
ProductID ProductName
Contoh lain adalah bila diinginkan mencari nama produk yang mengandung huruf v maka digunakan perintah
berikut :
select ProductID, ProductName
from dbo.Products
WHERE ProductName LIKE '%v%'
Perhatikan bahwa tanda % diletakkan sebelum dan sesudah huruf v, yang berarti dicari segala sesuatu yang mengandung huruf v tersebut.
Tanda % tersebut biasa dikenal sebagai wildcard, yang berfungsi menentukan berbagai kriteria dalam operator LIKE. Daftar selengkapnya mengenai wildcard yang dapat digunakan di SQL Server dapat Anda baca di Books Online SQL Server.
Bila anda perhatikan perintah SQL diatas maka data yang dihasilkan telah diurutkan berdasarkan kolom CustomerID. Anda dapat merubahnya dengan mengurutkan berdasarkan kolom ContactName dengan perintah berikut :
SELECT CustomerID, CompanyName, ContactName
FROM Customers
ORDER BY ContactName
Sehingga hasilnya adalah :
CustomerID CompanyName ContactName
Terlihat bahwa data telah diurutkan berdasarkan ContactName secara ascending (dari a ke z). Anda dapat membalik urutan menjadi dari z ke a dengan merubah klausa ORDER BY menjadi seperti berikut :
ORDER BY ContactName desc
Secara default urutan yang digunakan adalah ascending.
Selain itu dapat pula digunakan beberapa kriteria pengurutan. Artinya pengurutan dilakukan berdasarkan kolom yang disebut pertama, setelah itu kolom kedua, dan selanjutnya. Perintah diatas dapat ditambahkan sehingga menjadi sebagai berikut :
SELECT CustomerID, CompanyName, ContactName
FROM Customers
ORDER BY ContactName, CompanyName
Maka data akan diurutkan mengikuti ContactName, dan selanjutnya berdasarkan CompanyName.
Perintah ORDER BY juga dapat digabungkan dengan WHERE misalnya sebagai berikut :
SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE City = 'London' OR
ContactName = 'Thomas Hardy'
ORDER BY ContactName
Perhatikan baik-baik bahwa ORDER BY harus diletakkan setelah WHERE. Apabila urutan tersebut terbalik
maka statement SQL tidak dapat dijalankan dan menghasilkan pesan error berikut :
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'WHERE'.
Cari yang Mirip dengan LIKE
Apabila WHERE memfilter data berdasarkan kriteria tertentu yang sudah pasti, maka LIKE digunakan untuk memberikan kriteria yang tidak memiliki kepastian.
Misalkan anda ingin mencari nama produk yang dimulai dengan huruf c maka digunakan perintah berikut :
SELECT ProductID, ProductName
from Products
WHERE ProductName LIKE 'c%'
Perhatikan tanda % setelah huruf c tersebut, yang dapat diartikan sebagai : semua yang dimulai dengan c.
Keluaran perintah tersebut adalah :
ProductID ProductName
Contoh lain adalah bila diinginkan mencari nama produk yang mengandung huruf v maka digunakan perintah
berikut :
select ProductID, ProductName
from dbo.Products
WHERE ProductName LIKE '%v%'
Perhatikan bahwa tanda % diletakkan sebelum dan sesudah huruf v, yang berarti dicari segala sesuatu yang mengandung huruf v tersebut.
Tanda % tersebut biasa dikenal sebagai wildcard, yang berfungsi menentukan berbagai kriteria dalam operator LIKE. Daftar selengkapnya mengenai wildcard yang dapat digunakan di SQL Server dapat Anda baca di Books Online SQL Server.
Melakukan Perhitungan
Selain mengambil data dari database anda dapat juga melakukan berbagai perhitungan terhadap data tersebut.
Berbagai fungsi yang dapat dilakukan adalah penjumlahan, perkalian, pembagian dan pengurangan. Simbolsimbol
yang digunakan adalah sebagai berikut :
• * Perkalian
• / Pembagian
• + Penjumlahan
• - Pengurangan
Contoh berikut menghitung harga setiap produk yang terjual dengan mengalikan Quantity dan UnitPrice.
SELECT ProductID, (UnitPrice * Quantity) as TotalHarga
FROM [Order Details]
Misalkan tiap produk dikenakan diskon sebesar 10% dan ingin menampilkan harga setelah diskon, maka perintahnya adalah sebagai berikut :
SELECT ProductID, (UnitPrice * Quantity) as TotalHarga,
(UnitPrice * Quantity) * 0.1 as Diskon,
(UnitPrice * Quantity) * (1 - 0.1) as HargaDiskon
FROM [Order Details]
Diskon 10% adalah sama dengan 0.1, sehingga angka tersebut digunakan dalam kode program.
Hasil perhitungan selengkapnya adalah :
ProductID TotalHarga Diskon HargaDiskon
Membuat Alias dengan AS
Dalam contoh perhitungan dengan SQL diatas banyak digunakan keyword AS untuk memberikan nama kolom. Fungsi AS tersebut adalah memberikan alias terhadap hasil perhitungan sehingga lebih mudah dibaca.
Apabila suatu perhitungan tidak disertakan alias menggunakan AS maka kolom hasil perhitungan tersebut menjadi tidak dikenal. Perhatikan contoh berikut :
SELECT ProductID, (UnitPrice * Quantity)
FROM [Order Details]
Maka kolom hasil perkalian tidak memiliki nama sebagaimana hasil berikut :
ProductID
Menghitung Group Data dengan Fungsi Agregat
Selain mengambil data dengan kriteria tertentu, sering juga diperlukan berbagai perhitungan yang bersifat ringkasan. Fungsi agregat merupakan sekumpulan fungsi yang siap dipakai untuk mendapatkan hasil penjumlahan, penghitungan frekuansi, rata-rata, dan lain-lain. Penggunaan fungsi ini sering digabungkan dengan klausa GROUP BY yang akan diterangkan kemudian.
Fungsi SUM
Apabila anda ingin mendapatkan jumlah dari sekelompok data yang memiliki kriteria tertentu maka SUM adalah pilihan yang tepat. Fungsi ini menjumlahkan nilai kolom tertentu yang telah dikelompokkan menurut kriteria tertentu.
Misalnya anda ingin menghitung jumlah jumlah barang yang terjual untuk kategori produk tertentu yang terdapat di tabel Order Details pada database NorthWind.
USE NorthWind
SELECT SUM(Quantity) as QTY from [Order Details]
WHERE ProductID = 11
Maka dihasilkan output sebagai berikut :
QTY
Perintah tersebut diawali dengan USE Northwind yang artinya anda menggunakan database NorthWind.Misalnya anda ingin menggunakan database lain bernama Pubs maka diketikkan perintah USE Pubs. Perintah ini merupakan alternatif pemilihan database dengan cara manual dari menu combo di Query Analyser. Selanjutnya diambil data kolom Quantity yang telah dimasukkan sebagai argumen fungsi SUM, sehingga output yang diharapkan adalah total Quantity produk yang memiliki ProductID = 11. Pengunaan criteria ProductID merupakan suatu keharusan karena fungsi SUM akan menjumlahkan data yang memiliki criteria tertentu. Kriteria tersebut dalam contoh diatas adalah ProductID = 11.
Fungsi SUM hanya dapat digunakan untuk menjumlahkan kolom dengan type data Numeric.
Berbagai fungsi yang dapat dilakukan adalah penjumlahan, perkalian, pembagian dan pengurangan. Simbolsimbol
yang digunakan adalah sebagai berikut :
• * Perkalian
• / Pembagian
• + Penjumlahan
• - Pengurangan
Contoh berikut menghitung harga setiap produk yang terjual dengan mengalikan Quantity dan UnitPrice.
SELECT ProductID, (UnitPrice * Quantity) as TotalHarga
FROM [Order Details]
Misalkan tiap produk dikenakan diskon sebesar 10% dan ingin menampilkan harga setelah diskon, maka perintahnya adalah sebagai berikut :
SELECT ProductID, (UnitPrice * Quantity) as TotalHarga,
(UnitPrice * Quantity) * 0.1 as Diskon,
(UnitPrice * Quantity) * (1 - 0.1) as HargaDiskon
FROM [Order Details]
Diskon 10% adalah sama dengan 0.1, sehingga angka tersebut digunakan dalam kode program.
Hasil perhitungan selengkapnya adalah :
ProductID TotalHarga Diskon HargaDiskon
Membuat Alias dengan AS
Dalam contoh perhitungan dengan SQL diatas banyak digunakan keyword AS untuk memberikan nama kolom. Fungsi AS tersebut adalah memberikan alias terhadap hasil perhitungan sehingga lebih mudah dibaca.
Apabila suatu perhitungan tidak disertakan alias menggunakan AS maka kolom hasil perhitungan tersebut menjadi tidak dikenal. Perhatikan contoh berikut :
SELECT ProductID, (UnitPrice * Quantity)
FROM [Order Details]
Maka kolom hasil perkalian tidak memiliki nama sebagaimana hasil berikut :
ProductID
Menghitung Group Data dengan Fungsi Agregat
Selain mengambil data dengan kriteria tertentu, sering juga diperlukan berbagai perhitungan yang bersifat ringkasan. Fungsi agregat merupakan sekumpulan fungsi yang siap dipakai untuk mendapatkan hasil penjumlahan, penghitungan frekuansi, rata-rata, dan lain-lain. Penggunaan fungsi ini sering digabungkan dengan klausa GROUP BY yang akan diterangkan kemudian.
Fungsi SUM
Apabila anda ingin mendapatkan jumlah dari sekelompok data yang memiliki kriteria tertentu maka SUM adalah pilihan yang tepat. Fungsi ini menjumlahkan nilai kolom tertentu yang telah dikelompokkan menurut kriteria tertentu.
Misalnya anda ingin menghitung jumlah jumlah barang yang terjual untuk kategori produk tertentu yang terdapat di tabel Order Details pada database NorthWind.
USE NorthWind
SELECT SUM(Quantity) as QTY from [Order Details]
WHERE ProductID = 11
Maka dihasilkan output sebagai berikut :
QTY
Perintah tersebut diawali dengan USE Northwind yang artinya anda menggunakan database NorthWind.Misalnya anda ingin menggunakan database lain bernama Pubs maka diketikkan perintah USE Pubs. Perintah ini merupakan alternatif pemilihan database dengan cara manual dari menu combo di Query Analyser. Selanjutnya diambil data kolom Quantity yang telah dimasukkan sebagai argumen fungsi SUM, sehingga output yang diharapkan adalah total Quantity produk yang memiliki ProductID = 11. Pengunaan criteria ProductID merupakan suatu keharusan karena fungsi SUM akan menjumlahkan data yang memiliki criteria tertentu. Kriteria tersebut dalam contoh diatas adalah ProductID = 11.
Fungsi SUM hanya dapat digunakan untuk menjumlahkan kolom dengan type data Numeric.
Fungsi COUNT
Untuk menghitung frekuensi pemunculan suatu data digunakan fungsi COUNT. Sebagaimana fungsi SUM, maka COUNT hanya dapat digunakan apabila data tersebut telah ditentukan kriterianya dengan klausa
WHERE.
Misalkan anda ingin menghitung jumlah Customer yang terdapat di negara France. Tabel yang digunakan adalah tabel Customers.
SELECT COUNT (CustomerID) as Jumlah from Customers
WHERE Country = 'France'
Fungsi AVG
Fungsi ini hanya dapat digunakan untuk tipe data numeric, sebagaimana fungsi SUM. AVG menghitung rata-rata sekumpulan data yang telah ditentukan kriterianya menggunakan WHERE.
USE NorthWind
SELECT AVG(Quantity) as Rataan from [Order Details]
WHERE ProductID = 11
Contoh diatas menghitung rata-rata jumlah produk yang terjual untuk barang dengan ProductID = 11. Apabila diinginkan menghitung rata-rata seluruh jumlah peroduk terjual maka perintahnya menjadi :
SELECT AVG(Quantity) as Rataan from [Order Details]
Fungsi MIN dan MAX
Untuk mencari nilai maksimum dan minimum dari sekumpulan data anda dapat menggunakan fungsi MIN dan MAX. Misalkan anda ingin mencari jumlah barang yang paling banyak terjual maka dijalankan perintah berikut :
SELECT MAX(Quantity) as Maksimum from [Order Details]
Menggunakan Beberapa Fungsi Sekaligus
Fungsi-fungsi yang telah dijelaskan diatas juga dapat digunakan bersama-sama dalam satu perintah. Perhatikan baris kode berikut :
USE NorthWind
SELECT MAX(Quantity) as Maksimum,
MIN(Quantity) as Minimum,
AVG(Quantity) as Rataan
from [Order Details]
Jalankan perintah tersebut maka didapat hasil sebagai berikut :
Maksimum Minimum Rataan
-------- ------- -----------
130 1 23
(1 row(s) affected)
Klausa GROUP BY
Fungsi agregat yang telah dijelaskan sebelumnya hanya menampilkan satu baris hasil. Sering dibutuhkan untuk menampilkan rangkuman hasil perhitungan beberapa kelompok data dalam satu kali tampilan. Misalnya anda ingin menghitung rata-rata dan jumlah produk yang terjual untuk setiap jenis produk. Untuk melakukan ini digunakan klausa GROUP BY yang berfungsi mengelompokkan data yang memiliki criteria sama. Dengan demikian dapat dihasilkan suatu rangkuman hasil perhitungan untuk tiap kategori data.
Tuliskan kode program berikut :
SELECT ProductID, SUM(Quantity)as Jumlah
from [Order Details]
GROUP BY ProductID
Perintah tersebut akan menghitung jumlah produk yang terjual untuk setiap ProductID dan mengelompokkan
hasilnya berdasarkan ProductID tersebut. Hasil yang didapat sebagai berikut :
ProductID Jumlah
Terlihat jelas bahwa SQL Server telah mengelompokkan barang berdasarkan ProductID dan menghitung jumlah di kolom (QTY) untuk tiap ProductID tersebut. Hal yang perlu mendapatkan perhatian adalah pada baris terakhir kode program tersebut yaitu pada bagian :
GROUP BY ProductID
Bagian ini berfungsi mengelompokkan barang berdasarkan ProductID nya. Sedangkan kolom ProductID sendiri telah disebutkan di dalam daftar SELECT. Ini merupakan aturan dasar apabila anda menggunakan GROUP BY dalam perhitungan dengan fungsi agregat. Kolom dalam daftar SELECT yang tidak dihitung dengan fungsi agregat harus dimasukkan dalam daftar GROUP BY. Pada contoh diatas kolom ProductID masuk dalam daftar SELCT tetapi tidak dihitung menggunakan fungsi SUM, sehingga harus dimasukkan dalam daftar GROUP BY.
Salah satu contoh pengembangan yang lebih kompleks dari perintah penggunaan GROUP BY ini adalah sebagai berikut :
SELECT ProductID, SUM(Quantity)as Jumlah,
AVG(Quantity) as Rataan,
SUM(Quantity*UnitPrice)as Nilai,
AVG(Quantity*UnitPrice)as AVGNilai
from [Order Details]
GROUP BY ProductID
ORDER BY Nilai DESC
Kode tersebut menghitung jumlah, rata-rata, serta nilai uang tiap kategori produk. Nilai uang dari tiap produk didapat dengan mengalikan Quantity dengan UnitPrice. Selanjutnya data diurutkan berdasarkan nilai uangnya dari yang terbesar hingga terkecil.
WHERE.
Misalkan anda ingin menghitung jumlah Customer yang terdapat di negara France. Tabel yang digunakan adalah tabel Customers.
SELECT COUNT (CustomerID) as Jumlah from Customers
WHERE Country = 'France'
Fungsi AVG
Fungsi ini hanya dapat digunakan untuk tipe data numeric, sebagaimana fungsi SUM. AVG menghitung rata-rata sekumpulan data yang telah ditentukan kriterianya menggunakan WHERE.
USE NorthWind
SELECT AVG(Quantity) as Rataan from [Order Details]
WHERE ProductID = 11
Contoh diatas menghitung rata-rata jumlah produk yang terjual untuk barang dengan ProductID = 11. Apabila diinginkan menghitung rata-rata seluruh jumlah peroduk terjual maka perintahnya menjadi :
SELECT AVG(Quantity) as Rataan from [Order Details]
Fungsi MIN dan MAX
Untuk mencari nilai maksimum dan minimum dari sekumpulan data anda dapat menggunakan fungsi MIN dan MAX. Misalkan anda ingin mencari jumlah barang yang paling banyak terjual maka dijalankan perintah berikut :
SELECT MAX(Quantity) as Maksimum from [Order Details]
Menggunakan Beberapa Fungsi Sekaligus
Fungsi-fungsi yang telah dijelaskan diatas juga dapat digunakan bersama-sama dalam satu perintah. Perhatikan baris kode berikut :
USE NorthWind
SELECT MAX(Quantity) as Maksimum,
MIN(Quantity) as Minimum,
AVG(Quantity) as Rataan
from [Order Details]
Jalankan perintah tersebut maka didapat hasil sebagai berikut :
Maksimum Minimum Rataan
-------- ------- -----------
130 1 23
(1 row(s) affected)
Klausa GROUP BY
Fungsi agregat yang telah dijelaskan sebelumnya hanya menampilkan satu baris hasil. Sering dibutuhkan untuk menampilkan rangkuman hasil perhitungan beberapa kelompok data dalam satu kali tampilan. Misalnya anda ingin menghitung rata-rata dan jumlah produk yang terjual untuk setiap jenis produk. Untuk melakukan ini digunakan klausa GROUP BY yang berfungsi mengelompokkan data yang memiliki criteria sama. Dengan demikian dapat dihasilkan suatu rangkuman hasil perhitungan untuk tiap kategori data.
Tuliskan kode program berikut :
SELECT ProductID, SUM(Quantity)as Jumlah
from [Order Details]
GROUP BY ProductID
Perintah tersebut akan menghitung jumlah produk yang terjual untuk setiap ProductID dan mengelompokkan
hasilnya berdasarkan ProductID tersebut. Hasil yang didapat sebagai berikut :
ProductID Jumlah
Terlihat jelas bahwa SQL Server telah mengelompokkan barang berdasarkan ProductID dan menghitung jumlah di kolom (QTY) untuk tiap ProductID tersebut. Hal yang perlu mendapatkan perhatian adalah pada baris terakhir kode program tersebut yaitu pada bagian :
GROUP BY ProductID
Bagian ini berfungsi mengelompokkan barang berdasarkan ProductID nya. Sedangkan kolom ProductID sendiri telah disebutkan di dalam daftar SELECT. Ini merupakan aturan dasar apabila anda menggunakan GROUP BY dalam perhitungan dengan fungsi agregat. Kolom dalam daftar SELECT yang tidak dihitung dengan fungsi agregat harus dimasukkan dalam daftar GROUP BY. Pada contoh diatas kolom ProductID masuk dalam daftar SELCT tetapi tidak dihitung menggunakan fungsi SUM, sehingga harus dimasukkan dalam daftar GROUP BY.
Salah satu contoh pengembangan yang lebih kompleks dari perintah penggunaan GROUP BY ini adalah sebagai berikut :
SELECT ProductID, SUM(Quantity)as Jumlah,
AVG(Quantity) as Rataan,
SUM(Quantity*UnitPrice)as Nilai,
AVG(Quantity*UnitPrice)as AVGNilai
from [Order Details]
GROUP BY ProductID
ORDER BY Nilai DESC
Kode tersebut menghitung jumlah, rata-rata, serta nilai uang tiap kategori produk. Nilai uang dari tiap produk didapat dengan mengalikan Quantity dengan UnitPrice. Selanjutnya data diurutkan berdasarkan nilai uangnya dari yang terbesar hingga terkecil.
Menghilangkan Penggandaan dengan DISTINCT
Penggunaan keyword DISTINCT hampir mirip dengan GROUP BY yaitu untuk melakukan grouping hasil pencarian. Tetapi DISTINCT hanya berfungsi menghilangkan duplikasi hasil dan tidak dapat digunakan untuk membantu melakukan berbagai perhitungan fungsi agregat.
Penerapan praktisnya misalnya anda ingin mencari negara mana saja yang yang terdapat di tabel customer. Apabila anda menggunakan perintah SELECT saja maka akan muncul banyak duplikasi karena banyak terdapat customer yang berasal dari negara yang sama. Dengan menggunakan DISTINCT maka duplikasi tersebut dapat dihilangkan.
SELECT DISTINCT Country
FROM Customers
Cobalah bandingkan hasilnya apabila anda menggunakan perintah berikut :
SELECT Country
FROM Customers
Membatasi GROUP BY dengan HAVING
Untuk membatasi hasil yang didapat dari suatu perintah yang menggunakan GROUP BY digunakan klausa HAVING. Fungsi klausa ini mirip dengan WHERE tetapi HAVING hanya dapat digunakan bersamaan dengan klausa GROUP BY dan diletakkan sesudah GROUP BY.
Perintah berikut digunakan untuk mencari produk yang jumlah penjualannya melebihi 1000 buah di table Order Details.
SELECT ProductID, SUM(Quantity)as Jumlah,
FROM [Order Details]
GROUP BY ProductID
HAVING SUM(Quantity) > 1000
Mencari Data dengan BETWEEN
Keyword BETWEEN digunakan untuk mencari data yang memenuhi interval kriteria tertentu dalam suatu kolom. Biasanya digunakan untuk mencari data berdasarkan interval tanggal tertentu. Penggunaan praktisnya misalnya anda ingin mencari penjualan yang terjadi antara tanggal 1 Januari 2003 sampai dengan 31 Januari 2003. Keyword BETWEEN digunakan bersamaan dengan interval tanggal tersebut.
Tuliskan baris kode berikut :
USE NorthWind
SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderDate BETWEEN '07/01/1996' AND '07/31/1996'
Kode tersebut mencari data di tabel Orders yang memiliki OrderDate antara tanggal 1 Juli 1996 sampai
dengan 31 Juli 1996.
Selain menggunakan kriteria tanggal anda juga dapat menggunakan keyword BETWEEN tersebut untuk tipe data lain misalnya string dan numeric. Misalnya anda ingin menampilkan data customer yang diawali huruf A sampai dengan D, maka digunakan kode program berikut :
SELECT * FROM dbo.Customers
WHERE CompanyName BETWEEN 'A' AND 'D'
Menggabungkan Tabel
Anda telah mempelajari bagaimana menampilkan data dari database Northwind serta memfilternya dengan berbagai kriteria. Pengambilan data tersebut hanya dari satu tabel saja sehingga belum dapat menyajikan informasi lebih detil.
Perintah SQL dapat digunakan untuk menampilkan data dari 2 atau lebih tabel. Antara tabel tersebut harus memiliki penghubung yaitu Primary Key dan Foreign Key sebagaimana telah dijelaskan pada Bab 2 mengenai desain database. Dengan menggunakan kriteria penghubung tersebut anda dapat menampilkan data dari beberapa tabel secara konsisten.
Inner Join
Penggabungan tabel dalam perintah SQL menggunakan keyword JOIN. Jenis penggabungan tabel yang pertama adalah INNER JOIN. INNER JOIN hanya menampilkan data yang benar-benar terdapat di dalam tabel yang saling dihubungkan.
Misalnya anda menggabungkan tabel Customers dengan Orders, maka field penghubung yang digunakan adalah CustomerID.
Dalam teknik INNER JOIN maka hasil yang ditampilkan hanya record yang memiliki CustomerID sama di kedua tabel tersebut. Apabila terdapat customr yang CustomerID nya tidak ditemukan di tabel Orders maka data tersebut tidak ditampilkan.
Coba ketikkan perintah berikut di Query Analyser :
SELECT CompanyName, OrderID, OrderDate
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
Perintah SQL tersebut mengambil field CompanyName dari tabel Customers, sedangkan field OrderID dan OrderDate diambil dari tabel Orders. Kunci utama penggabungan dua tabel tersebut adalah keyword INNER JOIN dengan kriteria CustomerID.
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
Antara nama tabel dengan nama field dibatasi dengan tanda titik (.) misalnya Orders.CustomerID. Tujuan pencantuman nama tabel tersebut adalah menghindari ambiguitas yang mengakibatkan error apabila perintah tersebut dieksekusi. Karena CustomerID tersebut terdapat di kedua tabel maka nama tabel harus dicantumkan agar dapat diidentifikasi secara unik field mana yang dimaksud. Untuk memberikan gambaran lebih kompleks mengenai penerapan penggabungan tabel ini anda dapat memodifikasi contoh perintah yang terdapat penggunaan GROUP BY. Pada perintah tersebut anda menghitung Jumlah dan rataan produk yang terjual, tetapi hanya ditampilkan ProductID saja sedangkan nama produknya tidak terlihat karena terdapat di tabel lain. Anda dapat menggabungan tabel Order Details tersebut dengan tabel Products yang menyimpan nama produk. Dengan demikian dapat ditampilkan baik ProductID maupun nama produknya. Tuliskan baris perintah berikut :
SELECT Products.ProductID, Products.ProductName,
SUM([Order Details].Quantity) AS Jumlah,
AVG([Order Details].Quantity) AS Rataan,
SUM(Products.UnitPrice * [Order Details].Quantity)
AS Nilai,
AVG(Products.UnitPrice * [Order Details].Quantity)
AS AVGNilai
FROM Products
INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID
GROUP BY Products.ProductID, Products.ProductName
Pada perintah diatas anda dapat melihat bahwa semua nama field diawali dengan nama tabelnya. Hal ini menyebabkan penulisan kode program menjadi panjang, tetapi sisi positifnya adalah kita dapat langsung mengetahui posisi tabel dari tiap-tiap field yang ditampilkan.
Selain itu antara tabel Customers terdapat beberapa field yang namanya sama sehingga apabila tidak dituliskan nama tabelnya berakibat error pada perintah tersebut.
Penerapan praktisnya misalnya anda ingin mencari negara mana saja yang yang terdapat di tabel customer. Apabila anda menggunakan perintah SELECT saja maka akan muncul banyak duplikasi karena banyak terdapat customer yang berasal dari negara yang sama. Dengan menggunakan DISTINCT maka duplikasi tersebut dapat dihilangkan.
SELECT DISTINCT Country
FROM Customers
Cobalah bandingkan hasilnya apabila anda menggunakan perintah berikut :
SELECT Country
FROM Customers
Membatasi GROUP BY dengan HAVING
Untuk membatasi hasil yang didapat dari suatu perintah yang menggunakan GROUP BY digunakan klausa HAVING. Fungsi klausa ini mirip dengan WHERE tetapi HAVING hanya dapat digunakan bersamaan dengan klausa GROUP BY dan diletakkan sesudah GROUP BY.
Perintah berikut digunakan untuk mencari produk yang jumlah penjualannya melebihi 1000 buah di table Order Details.
SELECT ProductID, SUM(Quantity)as Jumlah,
FROM [Order Details]
GROUP BY ProductID
HAVING SUM(Quantity) > 1000
Mencari Data dengan BETWEEN
Keyword BETWEEN digunakan untuk mencari data yang memenuhi interval kriteria tertentu dalam suatu kolom. Biasanya digunakan untuk mencari data berdasarkan interval tanggal tertentu. Penggunaan praktisnya misalnya anda ingin mencari penjualan yang terjadi antara tanggal 1 Januari 2003 sampai dengan 31 Januari 2003. Keyword BETWEEN digunakan bersamaan dengan interval tanggal tersebut.
Tuliskan baris kode berikut :
USE NorthWind
SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderDate BETWEEN '07/01/1996' AND '07/31/1996'
Kode tersebut mencari data di tabel Orders yang memiliki OrderDate antara tanggal 1 Juli 1996 sampai
dengan 31 Juli 1996.
Selain menggunakan kriteria tanggal anda juga dapat menggunakan keyword BETWEEN tersebut untuk tipe data lain misalnya string dan numeric. Misalnya anda ingin menampilkan data customer yang diawali huruf A sampai dengan D, maka digunakan kode program berikut :
SELECT * FROM dbo.Customers
WHERE CompanyName BETWEEN 'A' AND 'D'
Menggabungkan Tabel
Anda telah mempelajari bagaimana menampilkan data dari database Northwind serta memfilternya dengan berbagai kriteria. Pengambilan data tersebut hanya dari satu tabel saja sehingga belum dapat menyajikan informasi lebih detil.
Perintah SQL dapat digunakan untuk menampilkan data dari 2 atau lebih tabel. Antara tabel tersebut harus memiliki penghubung yaitu Primary Key dan Foreign Key sebagaimana telah dijelaskan pada Bab 2 mengenai desain database. Dengan menggunakan kriteria penghubung tersebut anda dapat menampilkan data dari beberapa tabel secara konsisten.
Inner Join
Penggabungan tabel dalam perintah SQL menggunakan keyword JOIN. Jenis penggabungan tabel yang pertama adalah INNER JOIN. INNER JOIN hanya menampilkan data yang benar-benar terdapat di dalam tabel yang saling dihubungkan.
Misalnya anda menggabungkan tabel Customers dengan Orders, maka field penghubung yang digunakan adalah CustomerID.
Dalam teknik INNER JOIN maka hasil yang ditampilkan hanya record yang memiliki CustomerID sama di kedua tabel tersebut. Apabila terdapat customr yang CustomerID nya tidak ditemukan di tabel Orders maka data tersebut tidak ditampilkan.
Coba ketikkan perintah berikut di Query Analyser :
SELECT CompanyName, OrderID, OrderDate
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
Perintah SQL tersebut mengambil field CompanyName dari tabel Customers, sedangkan field OrderID dan OrderDate diambil dari tabel Orders. Kunci utama penggabungan dua tabel tersebut adalah keyword INNER JOIN dengan kriteria CustomerID.
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
Antara nama tabel dengan nama field dibatasi dengan tanda titik (.) misalnya Orders.CustomerID. Tujuan pencantuman nama tabel tersebut adalah menghindari ambiguitas yang mengakibatkan error apabila perintah tersebut dieksekusi. Karena CustomerID tersebut terdapat di kedua tabel maka nama tabel harus dicantumkan agar dapat diidentifikasi secara unik field mana yang dimaksud. Untuk memberikan gambaran lebih kompleks mengenai penerapan penggabungan tabel ini anda dapat memodifikasi contoh perintah yang terdapat penggunaan GROUP BY. Pada perintah tersebut anda menghitung Jumlah dan rataan produk yang terjual, tetapi hanya ditampilkan ProductID saja sedangkan nama produknya tidak terlihat karena terdapat di tabel lain. Anda dapat menggabungan tabel Order Details tersebut dengan tabel Products yang menyimpan nama produk. Dengan demikian dapat ditampilkan baik ProductID maupun nama produknya. Tuliskan baris perintah berikut :
SELECT Products.ProductID, Products.ProductName,
SUM([Order Details].Quantity) AS Jumlah,
AVG([Order Details].Quantity) AS Rataan,
SUM(Products.UnitPrice * [Order Details].Quantity)
AS Nilai,
AVG(Products.UnitPrice * [Order Details].Quantity)
AS AVGNilai
FROM Products
INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID
GROUP BY Products.ProductID, Products.ProductName
Pada perintah diatas anda dapat melihat bahwa semua nama field diawali dengan nama tabelnya. Hal ini menyebabkan penulisan kode program menjadi panjang, tetapi sisi positifnya adalah kita dapat langsung mengetahui posisi tabel dari tiap-tiap field yang ditampilkan.
Selain itu antara tabel Customers terdapat beberapa field yang namanya sama sehingga apabila tidak dituliskan nama tabelnya berakibat error pada perintah tersebut.
LEFT JOIN dan RIGHT JOIN
Berbeda dengan INNER JOIN yang hanya menampilkan irisan data dari tabel yang digabungkan maka LEFT JOIN akan menampilkan data dari tabel yang disebutkan terlebih dahulu.
LEFT JOIN
Misalkan pada diagram gambar 3.2 diatas digunakan perintah LEFT JOIN maka apabila tabel Customers disebutkan terlebih dahulu SQL Server akan menampilkan semua nama customer yang terdapat di table tersebut. Semua data customer ditampilkan walaupun CustomerID nya tidak terdapat di tabel Orders. Penggunaan praktis perintah ini misalnya anda ingin mengetahui frekuensi order semua customer baik yang telah memiliki order maupun yang belum. Untuk kebutuhan tersebut maka ditampilkan semua customer berikut data frekuensi ordernya. Perhatikan contoh perintah berikut :
SELECT Customers.CustomerID, Customers.CompanyName,
COUNT(Orders.OrderID) AS Frekuensi
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, Customers.CompanyName
ORDER BY COUNT(Orders.OrderID)
Karena menggunakan perintah LEFT JOIN maka semua data di tabel Customers ditampilkan seluruhnya
walaupun frekuensi ordernya 0
Anda dapat melihat terdapat 2 customer yang frekuensi 0 tetapi tetap ditampilkan. Di akhir tampilan hasil
tersebut terlihat ada 91 baris yang ditampikan. Untuk melakukan pengecekan jalankan perintah berikut :
SELECT COUNT(*) from dbo.Customers
Artinya terdapat 91customer di tabel Customers, yaitu sama dengan jumlah baris di perintah sebelumnya.
Dengan demikian memang benar bahwa LEFT JOIN telah menampilkan seluruh Customer yang berjumlah 91.
RIGHT JOIN
Perintah RIGHT JOIN merupakan kebalikan dari LEFT JOIN, yaitu menampilkan semua isi tabel yang disebutkan kedua dalam perintah join. Dalam contoh di atas apabila LEFT JOIN diganti RIGHT JOIN maka semua isi tabel Orders akan ditampilkan semuanya.
Cobalah memodifikasi perintah diatas menjadi sebagai berikut :
SELECT Customers.CustomerID, Customers.CompanyName,
COUNT(Orders.OrderID) AS Frekuensi
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, Customers.CompanyName
ORDER BY COUNT(Orders.OrderID)
Setelah anda jalankan perintah tersebut lihatlah perbedaaanya dengan perintah LEFT JOIN sebelumnya. Maka terlihat hanya dihasilkan 89 baris, berarti terdapat 2 customer yang CustomerID nya tidak terdapat di table Orders atau frekuensi ordernya = 0.
FULL JOIN
Jenis join terakhir adalah FULL JOIN yang menampilkan semua data dari dua tabel yang dihubungkan meskipun terdapat data yang tidak memiliki pasangan di tabel lainnya.
Misalnya kita mengambil data dari tabel Country dengan tabel City menggunakan FULL JOIN. Data dari kedua tabel akan ditampilkan semuanya baik untuk nama kota yang tidak memiliki data negara maupun sebaliknya. Perhatikan perintah berikut :
Manipulasi Data dengan SQL
Selain untuk mengambil informasi dari database anda juga dapat menggunakan perintah SQL untuk memanipulasi data. Proses tersebut meliputi menambah, menghapus, dan mengedit data.
Perintah manipulasi data sangat sering digunakan dalam aplikasi database dan bahakan dapat dikatakan menjadi inti sebuah aplikasi. Sebuah tabel dapat diisi dengan data, dihapus, maupun diedit datanya. Perintahperintah tersebut dilaksanakan berdasarkan kriteria tertentu menggunakan keyword WHERE, BETWEEN maupun LIKE.
Statement INSERT
Untuk mengisikan data ke dalam suatu tabel digunakan perntah INSERT yang memiliki syntax umum sebagai berikut :
INSERT table (column list)
VALUES (value list)
Misalnya untuk mengisikan data customer baru dituliskan perintah berikut :
INSERT Customers (CustomerID, CompanyName, ContactName)
VALUES ('MJTR', 'Majuterus', 'Lisha')
Perintah tersebut mengisikan data di tabel Customers untuk tiga kolom yaitu CustomerID, CompanyName
dan ContactName. Sedangkan kolom lain yang tidak diisi maka terisi dengan nilai default sesuai dengan desain tabelnya. Apabila desain tabelnya tidak mengijinkan nilai NULL maka anda harus mengisikan nilainya dalam perintah INSERT tersebut.
Apabila anda menampilkan data tabel Customers maka tampak data yang telah diisikan tersebut sebagai berikut :
CustomerID CompanyName ContactName
Apabila perintah INSERT digunakan untuk mengisikan seluruh kolom yang terdapat di suatu tabel maka nama kolom tidak perlu disebutkan secara eksplisit. Cukup disebutkan nilai data yang akan dimasukkan saja. Misalnya untuk mengisikan data ke tabel Shippers yang hanya terdiri dari tiga kolom dilancarkan perintah berikut :
INSERT Shippers
VALUES ('Megah Shipping', '021-55568953')
Anda mungkin bertanya mengapa VALUES yang diisikan hanya dua kolom, sedangan tabel Shippers terdiri dari 3 kolom. Jawabannya adalah karena kolom pertama yang bernama ShippersID telah disetting desainnya sebagai autonumber. Dengan demikian kolom tersebut akan terisi secara otomatis dengan angka berurut setiap terdapat data baru yang dimasukkan sehingga tidak perlu lagi diinsert secara eksplisit.
INSERT dari Tabel Lain
Pengisian tabel juga dapat menggunakan data yang diperoleh dari tabel lain. Caranya adalah dengan menggunakan perintah SELECT v berisi daftar data yang akan dimasukkan setelah perintah INSERT. Misalnya anda ingin memasukkan data di tabel Suppliers ke dalam tabel Customers maka digunakan perintah berikut :
INSERT Customers (CustomerID, CompanyName, ContactName)
SELECT SupplierID, CompanyName, ContactName
FROM Suppliers
WHERE Country = 'USA'
Apabila anda belum melakukan perubahan apapun di tabel Suppliers maka akan terdapat 4 supplier yang diisikan ke tabel Customers.
Perintah tersebut menggunakan kriteria WHERE Country = 'USA', sehingga hanya supplier yang berada di negara USA saja yang dimasukkan ke tabel Customers.
Prinsip utama dalam penggunaan perintah tersebut adalah jumlah kolom yang akan diisi harus sama dengan kolom yang diambil dalam daftar SELECT. Dengan demikian apabila dua buah tabel memiliki jumlah kolom yang sama maka anda dapat menggunakan perintah tersebut untuk mengisikan seluruh kolom dengan perintah sederhana tanpa menyebutkan kolmnya satu per satu.
Misalkan tabel Customers dan Suppliers memiliki jumlah kolom sama dan anda ingin memasukkan semua supplier ke tabel Customers maka dapat digunakan perintah sederhana sebagai berikut :
INSERT Customers
SELECT * FROM Suppliers
Statement DELETE
Statement DELETE merupakan kebalikan perintah INSERT. Perintah ini menghapus data yang terdapat di suatu tabel. Data dihapus per record atau per baris berdasarkan kriteria tertentu.
Penentuan kriteria record mana yang akan dihapus bisa dilakukan dengan menggunakan klausa WHERE.
Misalkan anda ingin menghapus data semua customer yang berada di negara France.
Syntax umum statement ini adalah sebagai berikut :
DELETE FROM table_name
WHERE Condition
Untuk menghapus data customer yang berasal dari Mexico di tabel Customers maka perintahnya adalah :
DELETE FROM Customers
where Country= 'Mexico'
Atau anda bisa juga menghapus data berdasarkan CustomerID yang merupakan primary key di tabel tersebut.
DELETE FROM Customers
where CustomerID= ‘ALFKI’
Selain menggunakan WHERE, dapat juga digabungkan dengan operator LIKE dan BETWEEN untuk membuat kriteria yang lebih fleksible. Misalnya sebagai berikut :
DELETE FROM Orders
WHERE OrderDate BETWEEN '07/01/1996' AND '07/31/1996'
Perintah tersebut akan menghapus data di tabel order yang memiliki OrderDate antara 1 Juli 1996 sampai dengan 31 Juli 1996.
LEFT JOIN
Misalkan pada diagram gambar 3.2 diatas digunakan perintah LEFT JOIN maka apabila tabel Customers disebutkan terlebih dahulu SQL Server akan menampilkan semua nama customer yang terdapat di table tersebut. Semua data customer ditampilkan walaupun CustomerID nya tidak terdapat di tabel Orders. Penggunaan praktis perintah ini misalnya anda ingin mengetahui frekuensi order semua customer baik yang telah memiliki order maupun yang belum. Untuk kebutuhan tersebut maka ditampilkan semua customer berikut data frekuensi ordernya. Perhatikan contoh perintah berikut :
SELECT Customers.CustomerID, Customers.CompanyName,
COUNT(Orders.OrderID) AS Frekuensi
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, Customers.CompanyName
ORDER BY COUNT(Orders.OrderID)
Karena menggunakan perintah LEFT JOIN maka semua data di tabel Customers ditampilkan seluruhnya
walaupun frekuensi ordernya 0
Anda dapat melihat terdapat 2 customer yang frekuensi 0 tetapi tetap ditampilkan. Di akhir tampilan hasil
tersebut terlihat ada 91 baris yang ditampikan. Untuk melakukan pengecekan jalankan perintah berikut :
SELECT COUNT(*) from dbo.Customers
Artinya terdapat 91customer di tabel Customers, yaitu sama dengan jumlah baris di perintah sebelumnya.
Dengan demikian memang benar bahwa LEFT JOIN telah menampilkan seluruh Customer yang berjumlah 91.
RIGHT JOIN
Perintah RIGHT JOIN merupakan kebalikan dari LEFT JOIN, yaitu menampilkan semua isi tabel yang disebutkan kedua dalam perintah join. Dalam contoh di atas apabila LEFT JOIN diganti RIGHT JOIN maka semua isi tabel Orders akan ditampilkan semuanya.
Cobalah memodifikasi perintah diatas menjadi sebagai berikut :
SELECT Customers.CustomerID, Customers.CompanyName,
COUNT(Orders.OrderID) AS Frekuensi
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, Customers.CompanyName
ORDER BY COUNT(Orders.OrderID)
Setelah anda jalankan perintah tersebut lihatlah perbedaaanya dengan perintah LEFT JOIN sebelumnya. Maka terlihat hanya dihasilkan 89 baris, berarti terdapat 2 customer yang CustomerID nya tidak terdapat di table Orders atau frekuensi ordernya = 0.
FULL JOIN
Jenis join terakhir adalah FULL JOIN yang menampilkan semua data dari dua tabel yang dihubungkan meskipun terdapat data yang tidak memiliki pasangan di tabel lainnya.
Misalnya kita mengambil data dari tabel Country dengan tabel City menggunakan FULL JOIN. Data dari kedua tabel akan ditampilkan semuanya baik untuk nama kota yang tidak memiliki data negara maupun sebaliknya. Perhatikan perintah berikut :
Manipulasi Data dengan SQL
Selain untuk mengambil informasi dari database anda juga dapat menggunakan perintah SQL untuk memanipulasi data. Proses tersebut meliputi menambah, menghapus, dan mengedit data.
Perintah manipulasi data sangat sering digunakan dalam aplikasi database dan bahakan dapat dikatakan menjadi inti sebuah aplikasi. Sebuah tabel dapat diisi dengan data, dihapus, maupun diedit datanya. Perintahperintah tersebut dilaksanakan berdasarkan kriteria tertentu menggunakan keyword WHERE, BETWEEN maupun LIKE.
Statement INSERT
Untuk mengisikan data ke dalam suatu tabel digunakan perntah INSERT yang memiliki syntax umum sebagai berikut :
INSERT table (column list)
VALUES (value list)
Misalnya untuk mengisikan data customer baru dituliskan perintah berikut :
INSERT Customers (CustomerID, CompanyName, ContactName)
VALUES ('MJTR', 'Majuterus', 'Lisha')
Perintah tersebut mengisikan data di tabel Customers untuk tiga kolom yaitu CustomerID, CompanyName
dan ContactName. Sedangkan kolom lain yang tidak diisi maka terisi dengan nilai default sesuai dengan desain tabelnya. Apabila desain tabelnya tidak mengijinkan nilai NULL maka anda harus mengisikan nilainya dalam perintah INSERT tersebut.
Apabila anda menampilkan data tabel Customers maka tampak data yang telah diisikan tersebut sebagai berikut :
CustomerID CompanyName ContactName
Apabila perintah INSERT digunakan untuk mengisikan seluruh kolom yang terdapat di suatu tabel maka nama kolom tidak perlu disebutkan secara eksplisit. Cukup disebutkan nilai data yang akan dimasukkan saja. Misalnya untuk mengisikan data ke tabel Shippers yang hanya terdiri dari tiga kolom dilancarkan perintah berikut :
INSERT Shippers
VALUES ('Megah Shipping', '021-55568953')
Anda mungkin bertanya mengapa VALUES yang diisikan hanya dua kolom, sedangan tabel Shippers terdiri dari 3 kolom. Jawabannya adalah karena kolom pertama yang bernama ShippersID telah disetting desainnya sebagai autonumber. Dengan demikian kolom tersebut akan terisi secara otomatis dengan angka berurut setiap terdapat data baru yang dimasukkan sehingga tidak perlu lagi diinsert secara eksplisit.
INSERT dari Tabel Lain
Pengisian tabel juga dapat menggunakan data yang diperoleh dari tabel lain. Caranya adalah dengan menggunakan perintah SELECT v berisi daftar data yang akan dimasukkan setelah perintah INSERT. Misalnya anda ingin memasukkan data di tabel Suppliers ke dalam tabel Customers maka digunakan perintah berikut :
INSERT Customers (CustomerID, CompanyName, ContactName)
SELECT SupplierID, CompanyName, ContactName
FROM Suppliers
WHERE Country = 'USA'
Apabila anda belum melakukan perubahan apapun di tabel Suppliers maka akan terdapat 4 supplier yang diisikan ke tabel Customers.
Perintah tersebut menggunakan kriteria WHERE Country = 'USA', sehingga hanya supplier yang berada di negara USA saja yang dimasukkan ke tabel Customers.
Prinsip utama dalam penggunaan perintah tersebut adalah jumlah kolom yang akan diisi harus sama dengan kolom yang diambil dalam daftar SELECT. Dengan demikian apabila dua buah tabel memiliki jumlah kolom yang sama maka anda dapat menggunakan perintah tersebut untuk mengisikan seluruh kolom dengan perintah sederhana tanpa menyebutkan kolmnya satu per satu.
Misalkan tabel Customers dan Suppliers memiliki jumlah kolom sama dan anda ingin memasukkan semua supplier ke tabel Customers maka dapat digunakan perintah sederhana sebagai berikut :
INSERT Customers
SELECT * FROM Suppliers
Statement DELETE
Statement DELETE merupakan kebalikan perintah INSERT. Perintah ini menghapus data yang terdapat di suatu tabel. Data dihapus per record atau per baris berdasarkan kriteria tertentu.
Penentuan kriteria record mana yang akan dihapus bisa dilakukan dengan menggunakan klausa WHERE.
Misalkan anda ingin menghapus data semua customer yang berada di negara France.
Syntax umum statement ini adalah sebagai berikut :
DELETE FROM table_name
WHERE Condition
Untuk menghapus data customer yang berasal dari Mexico di tabel Customers maka perintahnya adalah :
DELETE FROM Customers
where Country= 'Mexico'
Atau anda bisa juga menghapus data berdasarkan CustomerID yang merupakan primary key di tabel tersebut.
DELETE FROM Customers
where CustomerID= ‘ALFKI’
Selain menggunakan WHERE, dapat juga digabungkan dengan operator LIKE dan BETWEEN untuk membuat kriteria yang lebih fleksible. Misalnya sebagai berikut :
DELETE FROM Orders
WHERE OrderDate BETWEEN '07/01/1996' AND '07/31/1996'
Perintah tersebut akan menghapus data di tabel order yang memiliki OrderDate antara 1 Juli 1996 sampai dengan 31 Juli 1996.
Menghapus Seluruh Tabel
Apabila anda ingin mengosongkan tabel dan menghapus semua data yang ada di dalamnya maka digunakan perintah DELETE tanpa menggunakan kondisi WHERE.
Contoh berikut adalah perintah untuk mengosongkan isi tabel Products :
DELETE Products
Perintah tersebut hanya mengosongkan isi tabel saja tetapi tidak menghapus tabelnya.
Perlu diperhatikan apabila data dalam suatu tabel ternyata memiliki hubungan referential integrity dengan tabel lain maka penghapusan tersebut tidak dapat dilakukan. Mislanya apabila anda ingin menghapus data customer yang telah memiliki data order di tabel Orders, sedangakan antara kedua tabel tersebut memiliki hubungan referential integrity.
Untuk menghapus semua data di suatu tabel yang tidak memiliki hubungan referential integrity dengan table lain dapat digunakan perintah TRINCATE.
TRUNCATE TABLE Customers Maka perintah tersebut akan menghapus semua data customer yang tidak memiliki hubungan dengan table lain.
Statement UPDATE
Apabila anda ingin mengedit atau merubah suatu data tanpa menghapusnya maka digunakan perintah UPDATE . Perintah ini juga menggunakan kondisi tertentu dengan klausa WHERE sebagaimana perintah DELETE.
Syntax umum statement UPDATE adalah sebagai berikut :
UPDATE table_name
SET Column1 = Value1, Column2 = Value2, …..
WHERE condition
Perintah ini melakukan perubahan pada kolom tertentu sebagaimana yang disebutkan dalam perintah SET. Perubahan dilakukan terhadap record yang memenuhi kriteria di klausa WHERE.
Misalkan anda ingin menaikkan harga produk sebesar 10% untuk semua barang yang memiliki CategoryID = 2, maka digunakan perintah berikut :
UPDATE Products
SET UnitPrice = UnitPrice * 1.1
WHERE CategoryID = 2
Harga barang terdapat di kolom UnitPrice sehingga kolom tersebut dikalikan dengan 1.1. Perintah tersebut akan menaikkan harga semua barang yang terdapat di tabel Products sesuai dengan kriteria CategoryID. Selain menggunakan kriteria WHERE perintah ini juga dapat digabungkan dengan operator LIKE dan BETWEEN sebagaiman statement DELETE diatas.
Contoh berikut menggambarkan penggunaan beberapa kriteria untuk mengupdate suatu data :
UPDATE Customers
SET Country = 'Indonesia',
ContactName = 'Lisha'
WHERE City = 'Jakarta'
OR City = 'Surabaya'
Perintah diatas berguna untuk merubah nama negara menjadi Indonesia dan nama ContactName menjadi Lisha apabila kolom City berisi Jakarta atau Surabaya.
sumber : http://kolu.web.id/forum/archive/index.php/t-174.html
Contoh berikut adalah perintah untuk mengosongkan isi tabel Products :
DELETE Products
Perintah tersebut hanya mengosongkan isi tabel saja tetapi tidak menghapus tabelnya.
Perlu diperhatikan apabila data dalam suatu tabel ternyata memiliki hubungan referential integrity dengan tabel lain maka penghapusan tersebut tidak dapat dilakukan. Mislanya apabila anda ingin menghapus data customer yang telah memiliki data order di tabel Orders, sedangakan antara kedua tabel tersebut memiliki hubungan referential integrity.
Untuk menghapus semua data di suatu tabel yang tidak memiliki hubungan referential integrity dengan table lain dapat digunakan perintah TRINCATE.
TRUNCATE TABLE Customers Maka perintah tersebut akan menghapus semua data customer yang tidak memiliki hubungan dengan table lain.
Statement UPDATE
Apabila anda ingin mengedit atau merubah suatu data tanpa menghapusnya maka digunakan perintah UPDATE . Perintah ini juga menggunakan kondisi tertentu dengan klausa WHERE sebagaimana perintah DELETE.
Syntax umum statement UPDATE adalah sebagai berikut :
UPDATE table_name
SET Column1 = Value1, Column2 = Value2, …..
WHERE condition
Perintah ini melakukan perubahan pada kolom tertentu sebagaimana yang disebutkan dalam perintah SET. Perubahan dilakukan terhadap record yang memenuhi kriteria di klausa WHERE.
Misalkan anda ingin menaikkan harga produk sebesar 10% untuk semua barang yang memiliki CategoryID = 2, maka digunakan perintah berikut :
UPDATE Products
SET UnitPrice = UnitPrice * 1.1
WHERE CategoryID = 2
Harga barang terdapat di kolom UnitPrice sehingga kolom tersebut dikalikan dengan 1.1. Perintah tersebut akan menaikkan harga semua barang yang terdapat di tabel Products sesuai dengan kriteria CategoryID. Selain menggunakan kriteria WHERE perintah ini juga dapat digabungkan dengan operator LIKE dan BETWEEN sebagaiman statement DELETE diatas.
Contoh berikut menggambarkan penggunaan beberapa kriteria untuk mengupdate suatu data :
UPDATE Customers
SET Country = 'Indonesia',
ContactName = 'Lisha'
WHERE City = 'Jakarta'
OR City = 'Surabaya'
Perintah diatas berguna untuk merubah nama negara menjadi Indonesia dan nama ContactName menjadi Lisha apabila kolom City berisi Jakarta atau Surabaya.
sumber : http://kolu.web.id/forum/archive/index.php/t-174.html
Tidak ada komentar:
Posting Komentar