memahami sql statement

Memahami SQL Statement Hendra, S.T.

Indoprog 1

Mengenal SQL 1

Structured Query Language (SQL) adalah suatu bahasa database standar industri. SQL

menyediakan Data Definiton Language (DDL) dan Data Manipulation Language (DML).

Walaupun dalam beberapa bagian saling bersangkutan, perintah DDL memungkinkan

kita untuk membuat dan mendefinisikan tabel (CREATE TABLE), dan index (CREATE

INDEX), sedangkan perintah DML memungkinkan anda untuk membangun query untuk

mengambil data (SELECT) dari beberapa tabel, menyisip (INSERT) data baru,

memperbaiki (UPDATE) data, dan menghapus (DELETE).

Penguasaan SQL Statement merupakan syarat mutlak bagi Database Programmer, karena

dengan pemanfaatan SQL Statement akan menghasilkan efisiensi dan efektifitas yang

tinggi dalam manipulasi data.

Pada trend pemrograman Client Server, Client melakukan request ke server dengan suatu

query yang ditulis dalam bentuk SQL Statement, dan kemudian query tersebut akan

diproses oleh Database Server, serta mengembalikan data ataupun hasil untuk query

tersebut kembali ke komputer Client.

Data Definition Language (DDL)

Membuat Tabel Baru

CREATE TABLE table (field1 type [(size)] [NOT NULL] [index1] [, field2 type [(size)]

[NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])

Perintah CREATE TABLE digunakan untuk mendefinisikan suatu tabel baru beserta

fieldnya dan konstraint, jika NOT NULL disebutkan, maka record baru membutuhkan

data yang sah untuk field tersebut.

CONSTRAINT clause dapat digunakan untuk membuat berbagai batasan pada field

termasuk PRIMARY KEY, anda dapat juga menggunakan perintah CREATE INDEX

untuk membuat index dan PRIMARY KEY pada tabel yang ada. CONSTRAINT clause

menyerupai CREATE INDEX, tetapi dapat digunakan untuk membuat Relation antar

tabel.

CONSTRAINT name {PRIMARY KEY | UNIQUE | NOT NULL |

REFERENCES foreigntable [(foreignfield1, foreignfield2)]}

dimana :

table nama tabel yang akan dibuat

field1, field2 nama field


Indoprog 2

type jenis data pada masing-masing field

size ukuran field untuk jenis data TEXT dan Binary

index untuk membuat CONSTRAINT

jenis data pada database JET

Jenis Data Ukuran Penyimpanan Keterangan BINARY 1 byte per karakter Menyimpan segala jenis data sebagaimana adanya tanpa translasi.

BIT 1 byte Menyimpan nilai Yes atau No.

BYTE 1 byte Menyimpan nilai integer 0 s/d 255

COUNTER 4 byte Menyimpan nilai numerik yang otomatis bertambah setiap record baru ditambahkan.

CURRENCY 8 byte Menyimpan suatu nilai skalar antara – 922,337,203,685,477.5808

dan 922,337,203,685,477.5807

DATETIME 8 byte Menyimpan suatu nilai tanggal dan waktu antara tahun 100 s/d

9999

GUID 128 byte Menyimpan suatu nomor identifikasi unik yang digunakan untuk

RPC

SINGLE 4 byte Menyimpan suatu nilai floating point single-precision dengan

jangkauan – 3.402823E38 s/d – 1.401298E-45 untuk nilai negatif

, 1.401298E-45 s/d 3.402823E38 untuk nilai positif dan 0.

DOUBLE 8 byte

Menyimpan suatu nilai floating point double-precision dengan

jangkauan – 1.79769313486232E308 s/d – 4.94065645841247E-

324 untuk nilai negatif, 4.94065645841247E-324 s/d

1.79769313486232E308 untuk nilai positif, dan 0

SHORT 2 byte Menyimpan suatu nilai short integer antara – 32,768 dan 32,767.

LONG Menyimpan suatu nilai long integer antara – 2,147,483,648 dan

2,147,483,647.

LONGTEXT 1 byte per karakter Kosong s/d maksimal 1.2 gigabytes.

LONGBINARY Sesuai dengan kebutuhan Kosong s/d maksimal 1.2 gigabytes. Digunakan untuk objek OLE.

TEXT 1 byte per karakter 0 s/d 255 karakter

Contoh :

Tabel Forum

Nama Field Type Size AutoIncrField AllowNulls Require

ForumID Text 25 Tidak Ya

Keterangan Text 50

Alamat Text 50 Tidak Ya

Primary Key (P_Key) dengan field ForumID

Perintah SQL :

CREATE TABLE FORUM (

ForumID TEXT(25) NOT NULL CONSTRAINT P_KEY PRIMARY KEY,

Keterangan TEXT(50),


Indoprog 3

Alamat TEXT(50) NOT NULL);

atau

CREATE TABLE FORUM (

ForumID TEXT(25) NOT NULL ,

Keterangan TEXT(50),

Alamat TEXT(50) NOT NULL,

CONSTRAINT P_KEY PRIMARY KEY (ForumID));

Tabel Status


Status Byte 1 Tidak Ya

Keterangan Text 50

Primary Key (P_Key) dengan field Status

Perintah SQL :

CREATE TABLE STATUS (

Status BYTE NOT NULL CONSTRAINT P_KEY PRIMARY KEY,

Keterangan TEXT(50));

Tabel Peserta


Email Text 25 Tidak Ya

Nama Text 50

Alamat Text 50

Kota Text 50

Telepon Text 25

Homepage Text 50

Perusahaan Text 50

TanggalGabung Date 8

Primary Key (P_Key) dengan field Email

Perintah SQL :

CREATE TABLE PESERTA (Email TEXT(25) NOT NULL CONSTRAINT P_KEY PRIMARY

KEY,

Nama TEXT(50),

Alamat TEXT(50),

Kota TEXT(50),


Indoprog 4

Telepon TEXT(50),

Homepage TEXT(50),

Perusahaan TEXT(50),

TanggalGabung Date);

Tabel Aktifitas


ID Long 4 Ya

Email Text 25 Tidak Ya

ForumID Text 25 Tidak Ya

Status Byte Tidak Ya

Primary Key (P_Key) dengan field ID

Berserta Relation terhadap tabel PESERTA, FORUM dan STATUS

Nama Field (Foreign key) Foreign Tabel Foreign Field Email Peserta Email

ForumID Forum ForumID

Status Status Status

Perintah SQL :


Indoprog 5

CREATE TABLE AKTIFITAS (

ID COUNTER CONSTRAINT P_KEY PRIMARY KEY,

Email TEXT(25) NOT NULL CONSTRAINT AktifitasPeserta REFERENCES

PESERTA(Email),

ForumID TEXT(25) NOT NULL CONSTRAINT AktifitasForum REFERENCES

FORUM (ForumID),

Status BYTE CONSTRAINT AktifitasStatus REFERENCES

STATUS(Status));

Membuat Index

CREATE [ UNIQUE ] INDEX indexON table (field [ASC|DESC][, field [ASC|DESC],

...])[WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]

Membuat suatu index pada tabel yang telah ada.

dimana :

index nama dari index yang akan dibuat

table nama dari tabel yang akan mengandung index tersebut

field nama dari field yang akan di index

Gunakan UNIQUE untuk membuat nilai unik terhadap field tertentu sehingga tidak

memungkinkan duplikasi data. Gunakan WITH clause untuk menentukan apakah :

• Tidak memperbolehkan pemasukan nilai Null pada field yang diindex dengan

menggunakan option DISALLOW NULL.

• Menghindarkan record dengan nilai Null pada field yang diindex untuk

diikutsertakan dalam index.

• Membuat index sebagai PRIMARY, setiap tabel hanya dapat memiliki satu index

PRIMARY.

Contoh membuat Index S_KEY berdasarkan Nama pada tabel PESERTA, perintah SQL

DDL-nya adalah sebagai berikut :

CREATE INDEX S_KEY ON PESERTA(NAMA);

Memodifikasi Tabel

ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL]

[CONSTRAINT index] |CONSTRAINT multifieldindex} |DROP {COLUMN field I

CONSTRAINT indexname} }

Melakukan modifikasi terhadap rancangan tabel yang telah ada.

dimana :


Indoprog 6

table Nama dari tabel

field Nama field yang akan ditambah (ADD), dihapus (DROP)

type Type field yang akan ditambahkan

size Ukuran field yang akan ditambahkan

index Perintah CONSTRAINT clause

indexname Nama index yang akan dihapus

Contoh menambahkan field Keterangan Text(50) pada tabel aktifitas :

ALTER TABLE Aktifitas ADD COLUMN Keterangan Text(50);

Menghapus Table atau Index

DROP {TABLE table | INDEX index ON table}

Menghapus tabel yang ada dalam database, atau index dari suatu tabel

table nama tabel yang akan dihapus atau tabel dimana index berada

index nama index yang akan dihapus

Contoh menghapus tabel TEMP

DROP TABLE Temp;


Indoprog 7

Latihan 1

Judul : Pembuatan Tabel, Index dan Konstraint dengan

DML

1. Aktifkan Visual Data Manager pada menu Add-Ins, dan buatlah suatu Database file

baru Rental.Mdb

Kemudian akan muncul jendela VisData

Pada Menu File, Pilih New, Microsoft Access, Version 7.0 MDB …


Indoprog 8

Ketik Rental

Dan Pilih Save.

2. Dengan menggunakan DDL SQL buatlah tabel-tabel berikut ini

Tabel Anggota


KodeAnggota Text 5 Tidak Ya

Nama Text 50 Tidak Ya

Alamat Text 50 Tidak Ya


Indoprog 9

Telepon Text 15 Ya

Limit Byte Tidak Ya

Daftar DateTime Ya

PrimaryKey P_KEY KodeAnggota

Caranya :

Pada SQL Statement Window, ketikkan perintah SQL DDL berikut :

CREATE TABLE Anggota (

KodeAnggota Text(5) NOT NULL CONSTRAINT P_KEY PRIMARY KEY,

Nama Text(50) NOT NULL,

Alamat Text(50) NOT NULL,

Telepon Text(15),

Limit Byte NOT NULL,

Daftar DateTime);

Lakukan klik pada tombol Execute, dan akan muncul dialog box :

Lakukan klik pada No


Indoprog 10

Kalau muncul dialog box berikut, abaikan saja, karena DDL SQL tidak menghasilkan

Recordset

Klik kanan pada Database Window, dan pilih Refresh List

Selanjutnya buatlah tabel-tabel berikut ini :

Tabel CD


KodeCD Text 7 Tidak Ya

Judul Text 50 Tidak Ya

Kategori Text 20 Tidak Ya

Aktor Text 30 Ya

Harga Currency Tidak Ya

Daftar DateTime Ya


Indoprog 11

PrimaryKey P_KEY KodeCD

Perintah SQL DDL :

CREATE TABLE CD (

KodeCD Text(7) NOT NULL CONSTRAINT P_KEY PRIMARY KEY,

Judul Text(50) NOT NULL, Kategori Text(20) NOT NULL,

Aktor Text(30),

Harga Currency NOT NULL,

Daftar DateTime);

Tabel Transaksi


NoTransaksi Long Ya Tidak Ya

Tanggal DateTime Tidak Ya

KodeAnggota Text 5 Tidak Ya

KodeCD Text 7 Tidak Ya

Kembali DateTime Ya

Denda Currency Ya

PrimaryKey P_KEY NoTransaksi

dan Relation sebagai berikut :

Perintah SQL DDL :


Indoprog 12

CREATE TABLE Transaksi (NoTransaksi Counter CONSTRAINT P_KEY PRIMARY

KEY,

Tanggal DateTime NOT NULL,

KodeAnggota Text(5) NOT NULL CONSTRAINT TransaksiAnggota

REFERENCES Anggota(KodeAnggota),

KodeCD Text(7) NOT NULL CONSTRAINT TransaksiCD REFERENCES

CD(KodeCD),

Kembali DateTime,

Denda Currency);

3. Tambahkan Index untuk tabel Anggota berdasarkan nama, tabel CD berdasarkan Judul

Perintah SQL DDL :

CREATE INDEX S_KEY ON Anggota (Nama);

CREATE INDEX S_KEY ON CD (Judul);

4. Tambahkan field BlackList pada Tabel Anggota, dan field Status pada CD


BlackList Bit Ya

Perintah SQL DDL :

ALTER TABLE Anggota ADD COLUMN BlackList Bit;


Status Text 1 Ya

Perintah SQL DDL :

ALTER TABLE CD ADD COLUMN Status Text(1);


Indoprog 13

Judul : Tantangan pemakaian DDL SQL

1. Aktifkan Visual Data Manager, dan buatlah suatu Database file baru Akademik.Mdb

2. Dengan menggunakan DDL SQL buatlah tabel-tabel berikut ini, definisikan Primary

Key masing-masing tabel dan Relation antar tabel.

Tabel Fakultas


Fakultas Text 2 Tidak Ya

Keterangan Text 50 Tidak Ya

Tabel Jurusan


Jurusan Text 2 Tidak Ya


Tabel Mahasiswa


NIM Text 10 Tidak Ya


Fakultas Text 2 Tidak Ya

Jurusan Text 2 Tidak Ya

Tabel Kuliah


MataKuliah Text 6 Tidak Ya


SKS Byte Tidak Ya

Semester Byte Tidak Ya

Tabel Dosen


NIP Text 10 Tidak Ya


Indoprog 14


Tabel KHS


Id Long Ya Ya

NIM Text 10 Tidak Ya

MataKuliah Text 6 Tidak Ya

NIP Text 10 Tidak Ya

Tanggal DateTime Tidak Ya

NilaiHuruf Text 1 Tidak Ya


Indoprog 15

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 :

KodeAnggota Nama Alamat Telepon Limit Daftar Blacklist

SD001 Susan

Dewichan

Jl. Thamrin No. 95

Medan 4513490 3

23-Sept-

2001 False

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.


Indoprog 16

Contoh :

DELETE FROM Anggota WHERE BlackList;

Catatan :

Kita tidak perlu menulis BlackList=True, karena BlackList sendirinya

bertipe Logical

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;


Indoprog 17

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 ]][, ...]]]


Indoprog 18

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


Indoprog 19

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] [,

...]]}


Indoprog 20

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

Nama Field Type NoTransaksi Long Tanggal DateTime

KodeAnggota Text

KodeCD Text Kembali DateTime

Denda Currency

Tabel CD

Nama Field Type KodeCD Text

Judul Text

Kategori Text Aktor Text

Harga Currency

Daftar DateTime

Tabel Anggota

Nama Field Type KodeAnggota Text

Nama Text

Alamat Text Telepon Text


Indoprog 21

Limit Byte Daftar DateTime

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

Kolom Tabel sumber Kriteria

Tanggal Transaksi

Judul Transaksi

Nama Anggota

Denda Transaksi Not Null

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


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 :

Kolom Tabel sumber Kriteria

Tanggal Transaksi

Judul CD

Nama Anggota

Harga CD

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


Indoprog 22

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


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 [ ...

]]


Indoprog 23

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


Indoprog 24

PIVOT Format([Tanggal],"mmm") In

("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec

");


Indoprog 25

Latihan 1

Judul : Mencoba Append, Update, dan Delete query

1. Dengan menggunakan Visual Data Manager, buka database Rental.mdb.

2. Dengan menggunakan Append query, tambahkan data-data berikut ke :

Tabel Anggota

KodeAnggota Nama Alamat Telepon Limit Daftar Blacklist

HW001 Hendra Jl. Sudirman 10/22

Medan 4521056 3

22-Sept-

2001 False

SD001 Susan

Dewichan

Jl. Thamrin No. 95

Medan 4513490 3

23-Sept-

2001 False

Tabel CD

KodeCD Judul Kategori Aktor Harga Daftar Status

MI01.01 Mission Impossible Action Tom Cruise 3500 10-Mar-2000 B

RA01.01 Run Away Bride Drama Julia Robert 3500 22-Apr-2000 B

Tabel Transaksi

Tanggal KodeAnggota KodeCD

23-Sept-2001 HW001 RA01.01

3. Dengan menggunakan UPDATE query, set BlackList untuk KodeAnggota SD001

menjadi True

4. Dengan menggunakan DELETE query, hapus data Anggota yang di BlackList.


Indoprog 26

Latihan 2

Judul : Mencoba SELECT query

1. Dengan menggunakan Visual Data Manager aktifkan Nwind.Mdb yang merupakan

database sample pada Visual Basic maupun Microsoft Access.

Pada menu File, pilih Open Database…, Microsoft Access…

Perhatikan tabel-tabel yang ada.

• Categories


Indoprog 27

• Customers

• Employees

• Orders

• Orders Details

• Products

• Shippers

• Suppliers

2. Aktifkan tampilan dengan menggunakan DBGrid , yang dapat dipilih dari Toolbar.

3. Bukalah tabel Customers, dan perhatikan kolom-kolom didalamnya.

• CustomerId

• CompanyName

• ContactName

• ContactTitle

• Address

• City

• Region

• PostalCode

• Country

• Phone

• Fax

4. Pada Jendela SQL ketikkan perintah SQL berikut, dan klik pada Execute.

SELECT CustomerId, CompanyName, ContactName FROM Customers;


Indoprog 28

Ketik SQL statement tersebut, dan klik pada Execute

Lanjutkan untuk soal berikut ini :

Dengan menggunakan WHERE clause, batasi data yang ContactTitle adalah Sales

Manager

SELECT CustomerId, CompanyName, ContactName FROM Customers WHERE

ContactTitle = 'Sales Manager';

Dengan menggunakan ORDER BY clause, urutkan recordset hasil berdasarkan

CompanyName secara Descending.

SELECT CustomerId, CompanyName, ContactName FROM Customers WHERE

ContactTitle = 'Sales Manager' ORDER BY CompanyName DESC;


Indoprog 29

Hitung Jumlah pelangan yang terdapat di Country USA dengan Fungsi Agregate Count

SELECT Count(*) As JlhPelangan FROM Customers WHERE Country = 'USA';

Buatlah Statistik jumlah pelangan berdasarkan Country dengan GROUP BY clause

SELECT Country, Count(*) As JlhPelangan FROM Customers GROUP BY Country;

Buatlah Daftar Negara yang mana jumlah pelangan diatas 10 perusahaan dengan

HAVING clause

SELECT Country, Count(*) As JlhPelangan FROM Customers GROUP BY Country

HAVING Count(*) >= 10;

Buatlah Daftar Negara dimana langanan berada (tidak boleh double) dengan

menggunakan predikat DISTINCT

SELECT DISTINCT Country FROM Customers;

Buatlah Daftar 10 besar Negara berdasarkan jumlah pelanggan, dengan predikat TOP

SELECT TOP 10 Country, Count(*) As JlhPelangan FROM Customers GROUP BY

Country ORDER BY Count(*) DESC;

5. Bukalah tabel Products dan perhatikan kolom-kolom didalamnya.

• ProductID

• ProductName

• SupplierID

• CategoryID

• QuantityPerUnit

• UnitPrice

• UnitInStock

• UnitOnOrder

• ReorderLevel

• Discontinued

6. Bukalah tabel Categories dan perhatikan kolom-kolom didalamnya.

• CategoryID

• CategoryName

• Description

• Picture

7. Dengan menggunakan perintah SQL dan operasi INNER JOIN antara tabel Products

dan Categories, tampilkan kolom ProductName, CategoryName, dan Unit Price.


Indoprog 30

SELECT Products.ProductName, Categories.CategoryName, Products.UnitPrice FROM

Products INNER JOIN Categories ON Products.CategoryId = Categories.CategoryId;

8. Bukalah tabel Suppliers dan perhatikan kolom-kolom didalamnya.

• SupplierId

• CompanyName

• ContactName

• ContactTitle

• Address

• City

• Region

• PostalCode

• Country

• Phone

• Fax

• Homepage

9. Dengan menggunakan perintah SQL dan operasi INNER JOIN antara tabel Products,

Categories, Suppliers, tampilkan kolom ProductName, CategoryName, SupplierName,

dan Unit Price.

SELECT Products.ProductName, Categories.CategoryName, Suppliers.CompanyName,

Products.UnitPrice FROM ( Products INNER JOIN Categories ON Products.CategoryId

= Categories.CategoryId) INNER JOIN Suppliers ON Products.SupplierId =

Suppliers.SupplierId;

10. Buatlah suatu daftar negara dimana perusahaan memiliki hubungan, baik dari tabel

Customers maupun tabel Suppliers, dengan operasi UNION.

SELECT DISTINCT Country From Customers UNION SELECT DISTINCT Country

From Suppliers;

11. Bukalah tabel Orders dan perhatikan kolom-kolom didalamnya.

• OrderID

• CustomerID

• EmployeeID

• OrderDate

• RequiredDate

• ShippedDate

• ShipVia

• Freight

• ShipName

• ShipAddress

• ShipCity


Indoprog 31

• ShipRegion

• ShipPostalCode

• ShipCountry

12. Buatlah suatu daftar jumlah Order Per CustomerID untuk Jan, Feb, ..., Dec.

TRANSFORM Count(*)

SELECT Orders.CustomerId

FROM Orders

GROUP BY Orders.CustomerId

PIVOT Format([OrderDate],"mmm") In

("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Bagaimana kalau untuk data Order tahun 1994 saja.

TRANSFORM Count(*)

SELECT Orders.CustomerId

FROM Orders WHERE Year([OrderDate]) = 1994

GROUP BY Orders.CustomerId

PIVOT Format([OrderDate],"mmm") In

("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


Indoprog 32

Latihan 3

Judul : Latihan

1. Dengan menggunakan Visual Data Manager aktifkan Biblio.Mdb

2. Gunakan perintah SQL untuk :

• Menampilkan kolom PubId, Name, dan Company Name dari tabel Publishers

• Batasi data yang dari City New York

• Urutkan data berdasarkan Name Descending

• Hitung jumlah publishers dari City Carmel

• Hitung jumlah pelanggan dari masing-masing City

• Buatlah Daftar City yang mana jumlah publishernya diatas 3

• Buatlah Daftar City publishers (tidak boleh double)

• Buatlah Daftar 10 besar City berdasarkan jumlah publishers

3. Tampilan recordset dengan kolom ISBN, Author dari tabel TitleAuthor dan Authors.

4. Tampilan recordset dengan kolom Title, Author, Name, dari tabel Titles, Title Author,

Authors, dan Publishers (4 Table)

SELECT Titles.Title, Authors.Author, Publishers.Name

FROM (Titles INNER JOIN Publishers ON Titles.PubID = Publishers.PubID) INNER

JOIN (Authors INNER JOIN [Title Author] ON Authors.Au_ID = [Title Author].Au_ID)

ON Titles.ISBN = [Title Author].ISBN;

memahami sql statement

Documents