Mengenal SQL 1. Data Definition Language (DDL) Membuat Tabel Baru

Memahami SQL Statement

Hendra, S.T.

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

1


Hendra, S.T.

type size

jenis data pada masing-masing field 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.94065645841247E324 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. Menyimpan suatu nilai long integer antara – 2,147,483,648 dan 2,147,483,647.

LONG 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

ForumID

Text

25

Keterangan

Text

50

Alamat

Text

50

AutoIncrField

AllowNulls

Require

Tidak

Ya

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

2


Hendra, S.T.

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 Nama Field

Type

Size

Status

Byte

1

Keterangan

Text

50

AutoIncrField

AllowNulls

Require

Tidak

Ya

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 Nama Field

Type

Size

Email

Text

25

Nama

Text

50

Alamat

Text

50

Kota

Text

50

Telepon

Text

25

Homepage

Text

50

Perusahaan

Text

50

TanggalGabung

Date

8

AutoIncrField

AllowNulls

Require

Tidak

Ya

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

3


Hendra, S.T.

Telepon TEXT(50), Homepage TEXT(50), Perusahaan TEXT(50), TanggalGabung Date);

Tabel Aktifitas Nama Field

Type

Size

AutoIncrField

AllowNulls

Require

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

4


Hendra, S.T.

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

5


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

Hendra, S.T.

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

6


Hendra, S.T.

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

7


Hendra, S.T.

Ketik Rental

Dan Pilih Save. 2. Dengan menggunakan DDL SQL buatlah tabel-tabel berikut ini Tabel Anggota Nama Field

Type

Size

KodeAnggota

Text

Nama Alamat

Indoprog

AutoIncrField

AllowNulls

Require

5

Tidak

Ya

Text

50

Tidak

Ya

Text

50

Tidak

Ya

8


Hendra, S.T.

Telepon

Text

15

Ya

Limit

Byte

Tidak

Daftar

DateTime

Ya

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

9


Hendra, S.T.

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 Nama Field

Type

Size

KodeCD

Text

Judul

AllowNulls

Require

7

Tidak

Ya

Text

50

Tidak

Ya

Kategori

Text

20

Tidak

Ya

Aktor

Text

30

Ya

Harga

Currency

Tidak

Daftar

DateTime

Ya

Indoprog

AutoIncrField

Ya

10


Hendra, S.T.

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 Nama Field

Type

Size

AutoIncrField

AllowNulls

Require

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

11


Hendra, S.T.

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 Nama Field

Type

BlackList

Bit

Size

AutoIncrField

AllowNulls

Require

Ya

Perintah SQL DDL : ALTER TABLE Anggota ADD COLUMN BlackList Bit; Nama Field

Type

Size

Status

Text

1

AutoIncrField

AllowNulls

Require

Ya

Perintah SQL DDL : ALTER TABLE CD ADD COLUMN Status Text(1);

Indoprog

12


Hendra, S.T.

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 Nama Field

Type

Size

Fakultas

Text

Keterangan

Text

AutoIncrField

AllowNulls

Require

2

Tidak

Ya

50

Tidak

Ya

AllowNulls

Require

Tabel Jurusan Nama Field

Type

Size

AutoIncrField

Jurusan

Text

2

Tidak

Ya

Keterangan

Text

50

Tidak

Ya

AllowNulls

Require

Tabel Mahasiswa Nama Field

Type

Size

AutoIncrField

NIM

Text

10

Tidak

Ya

Nama

Text

50

Tidak

Ya

Fakultas

Text

2

Tidak

Ya

Jurusan

Text

2

Tidak

Ya

Nama Field

Type

Size

AllowNulls

Require

MataKuliah

Text

6

Tidak

Ya

Keterangan

Text

50

Tidak

Ya

SKS

Byte

Tidak

Ya

Semester

Byte

Tidak

Ya

AllowNulls

Require

Tidak

Ya

Tabel Kuliah AutoIncrField

Tabel Dosen Nama Field

Type

Size

NIP

Text

10

Indoprog

AutoIncrField

13

Memahami SQL Statement Nama

Hendra, S.T.

Text

50

Tidak

Ya

Nama Field

Type

Size

AllowNulls

Require

Id

Long

NIM

Text

10

Tidak

Ya

MataKuliah

Text

6

Tidak

Ya

NIP

Text

10

Tidak

Ya

Tanggal

DateTime

Tidak

Ya

NilaiHuruf

Text

Tidak

Ya

Tabel KHS

Indoprog

AutoIncrField Ya

1

Ya

14


Hendra, S.T.

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 Susan SD001 Dewichan

Alamat

Telepon Limit Daftar

Jl. Thamrin No. 95 Medan

4513490 3

23-Sept2001

Blacklist False

INSERT INTO Anggota (KodeAnggota, Nama, Alamat, Telepon, Limit, BlackList) VALUES ('SD001','Susan Dewichan','Jl. Thamrin No. 95','4513490',3,#23Sept-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

15


Hendra, S.T.

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

16


Hendra, S.T.

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

17


Hendra, S.T.

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 = #20Sep=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

18


Hendra, S.T.

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

19


Hendra, S.T.

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

20

Memahami SQL Statement Limit

Byte

Daftar

DateTime

Hendra, S.T.

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 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 : Kolom

Tabel sumber

Tanggal

Transaksi

Judul

CD

Nama

Anggota

Harga

CD

Kriteria

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

21


Hendra, S.T.

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

Indoprog

22


Hendra, S.T.

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

23


Hendra, S.T.

PIVOT Format([Tanggal],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec ");

Indoprog

24


Hendra, S.T.

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-Sept2001

False

SD001

Susan Dewichan

Jl. Thamrin No. 95 Medan

4513490 3

23-Sept2001

False

Tabel CD KodeCD

Judul

Kategori Aktor

Harga Daftar

Status

MI01.01

Mission Impossible

Action

Tom Cruise

3500

10-Mar-2000

B

Drama

Julia Robert

3500

22-Apr-2000

B

RA01.01 Run Away Bride Tabel Transaksi Tanggal 23-Sept-2001

KodeAnggota HW001

KodeCD 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

25


Hendra, S.T.

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

26

Memahami SQL Statement • • • • • • •

Hendra, S.T.

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

27


Hendra, S.T.

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

28


Hendra, S.T.

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

29


Hendra, S.T.

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

30

Memahami SQL Statement • • •

Hendra, S.T.

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

31


Hendra, S.T.

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;

Indoprog

32

Mengenal SQL 1. Data Definition Language (DDL) Membuat Tabel Baru

Recommend Documents