Home, Forum diskusi, Chatting, Download

Mengenal SQL 2

Sebagaimana telah diterangkan pada Modul sebelumnya bahwa perintah SQL dibagi atas dua kelompok besar yaitu DDL (Data Definition Language) dan DML (Data Manipulation Language). Pada DML, terbagi atas APPEND, UPDATE, DELETE, dan SELECT query yang dapat digunakan untuk pengolahan data.

Menambah data dengan APPEND query

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

Menambah suatu record ke table, hal ini dikenal sebagai append query.

Contoh :

INSERT INTO Anggota (KodeAnggota, Nama, Alamat, Telepon, Limit, BlackList)
VALUES ('SD001','Susan Dewichan','Jl. Thamrin No. 95','4513490',3,#23-Sept-2001#);

Menghapus data dengan DELETE query

DELETE FROM table
WHERE criteria

Menghapus satu atau lebih record dari tabel yang berada di daftar FROM clause yang memenuhi  WHERE clause.

Pada tabel yang memiliki hubungan one-to-many relationship dengan tabel lain. Operasi Cascade delete dapat menyebabkan record lain yang berada pada sisi many ikut terhapus ketika penghapusan di lakukan terhadap data pada sisi one.

Penting

· Data yang telah dihapus dengan delete query, tidak dapat dibatalkan.
· Lakukan backup terhadap data anda setiap saat. Jika anda salah menghapus, maka anda dapat mengambil kembali dari backup.

Contoh :

DELETE FROM Anggota WHERE BlackList;

Memperbaiki Data dengan UPDATE query

Mengubah nilai pada field-field yang ditentukan pada tabel berdasarkan kriteria tertentu. 

Syntax

UPDATE table
SET newvalue
WHERE criteria;

Penting :

· UPDATE tidak menghasilkan suatu himpunan hasil. Juga, setelah anda mengupdate record dengan menggunakan update query, anda tidak dapat membatalkan operasi tersebut. Jika anda ingin mengetahui record mana saja yang akan terupdate, pertama anda perlu melakukan select query dengan kriteria yang sama.
· Lakukan backup terhadap data anda setiap saat. Jika anda salah menghapus, maka anda dapat mengambil kembali dari backup.

Contoh :

UPDATE Anggota SET BlackList = True WHERE KodeAnggota = 'SD001';

Mengambil Data dengan SELECT query

Memerintahkan kepada Microsoft Jet Engine untuk mengembalikan data dari database dalam bentuk recordset.

Syntax yang paling minimum dari SELECT statement adalah

SELECT fields FROM table

dimana fields adalah daftar dari field-field yang akan diambil dari tabel, anda dapat menggunakan tanda * (Asterisk) untuk menyatakan seluruh field dari tabel.

Contoh :

SELECT KodeAnggota, Nama, Alamat, Telepon, Limit, Daftar, Blacklist FROM Anggota;

Atau

SELECT * FROM Anggota;

Menganti nama kolom recordset dengan reserved word AS

Ketika recordset terbentuk, Microsoft Jet Engine menggunakan nama field sebagai nama objek field pada recordset, anda dapat menentukan nama objek field tersebut dengan reserved word AS.

Contoh :

SELECT KodeAnggota, Nama, Daftar As TanggalDaftar FROM Anggota;

dalam hal ini nama objek field TanggalDaftar akan digunakan untuk field Daftar.

Anda dapat menggunakan AS untuk menentukan nama objek field pada recordset yang merupakan hasil dari fungsi Agregate maupun ekspresi.

Contoh :

SELECT Count(*) AS JlhAnggota FROM Anggota;

Akan mendapat suatu recordset dengan field JlhAnggota dan record tunggal yang berisi jumlah anggota yang terdaftar pada tabel Anggota.

Membatasi hasil query dengan WHERE clause

SELECT fieldlist
FROM tableexpression
WHERE criteria

Sebagaimana pemakaian WHERE clause pada UPDATE dan DELETE query, anda dapat menggunakan WHERE clause untuk membatasi data yang dikembalikan berdasarkan kriteria tertentu.

Contoh :

SELECT * FROM Anggota WHERE TanggalDaftar <= #01-Jan-2001#;

Mengurut hasil query dengan ORDER BY clause

SELECT fieldlist
FROM table
WHERE selectcriteria
[ORDER BY field1 [ASC | DESC ][, field2 [ASC | DESC ]][, ...]]]

Anda dapat menggunakan ORDER BY clause untuk menentukan urutan data hasil query berdasarkan field tertentu, pada defaultnya adalah Ascending kalau tidak disebutkan ASC ataupun DESC.

Contoh :

SELECT * FROM Anggota ORDER BY Nama;

SELECT * FROM Anggota WHERE TanggalDaftar <= #01-Jan-2001# ORDER BY Nama;

Mengetahui statistik data dengan Fungsi Agregate

Anda dapat menggunakan fungsi-fungsi Agregate seperti AVG (rata-rata), COUNT (jumlah data), MIN atau MAX, SUM (hasil jumlah), StDEV atau StDEVP (standard deviasi sample/populasi), VAR atau VARP (variasi sample / populasi) untuk mendapatkan nilai statistifk field terentu.

Contoh :

SELECT Sum(Denda) AS JlhDenda FROM Transaksi Where Kembali = #20-Sep=2001#;

Akan mendapat suatu recordset dengan field JlhDenda dan record tunggal yang berisi jumlah perhitungan field denda pada tanggal 20 September 2001 dari tabel Transaksi.

Meringkas data dengan GROUP BY clause

SELECT fieldlist
FROM table
WHERE criteria
[GROUP BY groupfieldlist]

Anda dapat menggunakan ORDER BY clause untuk meringkas record yang nilai fieldnya sama menjadi satu record dikombinasikan dengan fungsi Agregate untuk mendapatkan statistik dari masing-masing ringkasan.

Misalnya kita ingin mendapatkan suatu daftar recordset yang berisi jumlah denda harian berdasarkan tanggal pengembalian (Kembali), maka perintah SQL adalah :

SELECT Kembali, Sum(Denda) AS JlhDenda FROM Transaksi ORDER BY kembali;

Membatasi data yang telah diringkas dengan HAVING clause

SELECT fieldlist
FROM table
WHERE selectcriteria
GROUP BY groupfieldlist
[HAVING groupcriteria]

Jika anda menggunakan WHERE clause untuk membatasi data hasil query berdasarkan kriteria tertentu, maka HAVING clause digunakan untuk membatasi data setelah GROUPING dilakukan.

Contoh :

SELECT Kembali, Sum(Denda) AS JlhDenda FROM Transaksi
   ORDER BY kembali 
   HAVING Sum(Denda) > 10000;

Mendapatkan daftar hasil jumlah denda harian, dimana yang dikembalikan hanya jumlah denda yang diatas 10000.

Menggunakan predikat ALL, DISTINCT, DISTINCT ROW dan TOP

SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]]
FROM table

Secara default kalau tidak dituliskan, maka predikat ALL akan digunakan. Anda dapat menggunakan predikat DISTINCT untuk mengambil satu record dari beberapa record yang memiliki nilai yang sama.

Contoh :

SELECT DISTINCT Aktor FROM CD;

Akan menghasilkan daftar nama Aktor dari tabel CD yang kita miliki. pemakaian predikat DISTINCT dalam hal ini untuk memastikan hanya 1 aktor terambil dari beberapa CD yang mungkin memiliki aktor utama yang sama.

Pemakaian predikat TOP untuk mendapatkan n record atau n persen record dari daftar teratas.

Misalnya kita ingin mendapatkan 10 kode CD yang paling sering diTransaksi oleh Anggota.

SELECT TOP 10 KodeCD, Count(*) FROM Transaksi 
   GROUP BY KodeCD 
   ORDER BY Count(*) DESC;

Syntax penulisan kongkrit dari SELECT statement

SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, ...]]}
FROM tableexpression [, ...] 
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]

Mengambil kolom dari dua atau lebih tabel dengan Operasi INNER JOIN

Kadang-kadang kita perlu melakukan kombinasi kolom-kolom dari beberapa tabel menjadi suatu recordset tunggal dimana yang memiliki nilai yang sama pada kolom tertentu.

Pada operasi INNER JOIN akan mengabungkan record-record yang memiliki nilai sama pada kolom tertentu pada kedua tabel.

FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field2

Perhatikan kembali tabel-tabel pada Rental.Mdb modul sebelumnya :

Tabel Transaksi

Tabel CD

Tabel Anggota

Misalnya kita ingin mengambil suatu recordset yang berisi kolom-kolom berikut :

Dalam hal ini kita akan menggambil data dari dua tabel, yaitu tabel Transaksi, dan tabel Anggota, dimana dalam pengambilan tersebut KodeAnggota pada tabel Transaksi harus bersesuaian dengan  KodeAnggota pada tabel Anggota, sehingga FROM clause-nya dapat ditulis menjadi :

FROM Transaksi INNER JOIN
    Anggota ON Transaksi.KodeAnggota = Anggota.KodeAnggota

Sehingga Perintah SQL adalah sebagai berikut :

SELECT Transaksi.Tanggal, Transaksi.KodeAnggota, Anggota.Nama, Transaksi.Denda
   FROM Transaksi INNER JOIN 
   Anggota ON Transaksi.KodeAnggota = Anggota.KodeAnggota
   WHERE NOT Denda Is NULL;

Pada perintah SELECT yang menggambil kolom lebih dari satu tabel, kita perlu menuliskan nama Tabel dan Field yang dipisahkan dengan dot (.).

Anda dapat melakukan nested untuk mengambil data lebih dari dua tabel

Misalnya kita ingin mengambil suatu recordset yang berisi kolom-kolom berikut :

Dalam hal ini kita akan menggambil data dari tiga tabel, yaitu tabel Transaksi, tabel CD dan tabel Anggota, dimana dalam pengambilan tersebut KodeCD pada tabel Transaksi harus bersesuaian dengan  KodeCD pada tabel CD, dan KodeAnggota pada tabel Transaksi harus bersesuaian dengan KodeAnggota pada tabel Anggota, sehingga FROM clause-nya dapat ditulis menjadi :

FROM (Transaksi INNER JOIN Anggota ON Transaksi.KodeAnggota = Anggota.KodeAnggota) INNER JOIN CD ON Transaksi.KodeCD =CD.KodeCD;

atau

FROM (Transaksi INNER JOIN CD ON Transaksi.KodeCD =CD.KodeCD) INNER JOIN Anggota ON Transaksi.KodeAnggota = Anggota.KodeAnggota ;

dan perintah SQLnya adalah sebagai berikut :

SELECT Transaksi.NoTransaksi, CD.Judul, CD.Harga, Anggota.Nama
FROM (Transaksi INNER JOIN Anggota ON Transaksi.KodeAnggota = Anggota.KodeAnggota)
INNER JOIN CD ON Transaksi.KodeCD =CD.KodeCD;

atau

SELECT Transaksi.NoTransaksi, CD.Judul, CD.Harga, Anggota.Nama
FROM (Transaksi INNER JOIN CD ON Transaksi.KodeCD =CD.KodeCD)
INNER JOIN Anggota ON Transaksi.KodeAnggota = Anggota.KodeAnggota ;

Operasi LEFT JOIN, RIGHT JOIN 

Jika pada operasi INNER JOIN, recordset yang dihasilkan adalah data-data yang memiliki nilai yang bersesuaian pada kolom tertentu, sedangkan operasi OUTER JOIN seperti LEFT JOIN dan RIGHT JOIN dapat digunakan untuk mengambil seluruh data dari suatu tabel walaupun pada tabel lainnya tidak ada data yang bersesuaian.

Contoh :

SELECT Transaksi.Tanggal, Transaksi.KodeAnggota, Anggota.Nama, Transaksi.Denda
   FROM Transaksi LEFT JOIN
   Anggota ON Transaksi.KodeAnggota = Anggota.KodeAnggota
   WHERE NOT Denda Is NULL;

Membuat union query dengan Operasi UNION

Membuat suatu query union, yang mana mengabung hasil dari dua query atau tabel dengan menghilangkan record duplikat, untuk mengikutkan record yang duplikat, anda dapat mencamtumkan ALL.

[TABLE] query1 UNION [ALL] [TABLE] query2 [UNION [ALL] [TABLE] queryn [ ... ]]

Misalnya kita memiliki dua tabel History (data transaksi bulan lalu), Transaksi (data transaksi sekarang), kita ingin mengambil data dari dua tabel tersebut menjadi satu recordset.

SELECT * FROM History UNION ALL SELECT * FROM Transaksi;

Membuat CROSSTAB query dengan TRANSFORM statement

TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]

Anda dapat menggunakan perintah TRANSFORM untuk membuat suatu Crosstab query yang merupakan ringkasan data yang kolomnya berasal dari field atau ekspresi.

Misalnya kita akan membuat ringkasan data jumlah transaksi peminjaman CD oleh masing-masing Anggota (baris) pada masing-masing bulan JAN, FEB, ... (kolom)

Maka perintahnya adalah :

TRANSFORM Count(NoTransaksi)
SELECT Transaksi.KodeAnggota
FROM Transaksi
GROUP BY Transaksi.KodeAnggota
PIVOT Format([Tanggal],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Latihan 1, Mencoba Append, Update, dan Delete query

Latihan 2, Mencoba SELECT query

Latihan 3, Latihan