sql

halaman 1 dari 12 Sistem Basis Data-Bag.9 oleh: Badrudin Hadibrata

Bagian 9

PENGANTAR SQL & QBE

Berdasarkan acuan model relasional, ada 2 bahasa query komersial yang tersedia : yaitu

SQL (Structured Query Language) dan QBE (Query By Example). SQL mewakili bahasa

query Prosedural sedangkan QBE mewakili yang Non Prosedural.

Memahami Aljabar dan Kalkulus Relasional adalah kunci dalam memahami SQL (Structure

Query Language).

1. SQL DBMS umumnya menyediakan program khusus (utilitas/utility) yang dapat digunakan

secara interaktif untuk melakukan berbagai operasi terhadap basis data, seperti pembuatan

tabel, penghapusan tabel, penambahan data, pengubahan data, dll. Namun disamping adanya

program khusus itu, DBMS juga menyediakan sekumpulan perintah (dalam bentuk command

line, yakni perintah yang langsung dituliskan oleh pemakai) untuk maksud yang sama.

Kumpulan perintah ini dapat disebut sebagai Bahasa Basis Data (Database Language).

Karena DBMS yang digunakan untuk mengelola basis data (back end) dan development

tools yang digunakan untuk menulis aplikasi (front end) basis data belum tentu dibuat (berasal

dari) perusahaan pembuat perangkat lunak yang sama, maka diperlukan sebuah bahasa basis

data yang sifatnya standar. Dengan bahasa yang standar, kita dapat menggunakan bahasa

tersebut untuk mengakses data dimanapun, tanpa peduli DBMS yang digunakan, tidak peduli

development tools yang digunakan. Dengan begitu terdapat independensi antara DBMS dan

development tools. Ada banyak sekali bahasa basis data yang pernah dibuat untuk masing-

masing DBMS. Namun akhirnya yang menjadi standar adalah SQL.

SQL singkatan dari Structured Query Language (sering dibaca SEQUEL), SQL

merupakan bahasa query standar yang digunaka untuk mengakses basis data relasional.

Standarisasi internasional terhadap SQL pertama kali dilakukan oleh ANSI (American

National Standards Institution).

SQL pertama kali diterapkan pada system R (proyek riset IBM). Namun kini SQL juga

dijumpai pada berbagai platform, dari mikrokomputer hingga mainframe. SQL dapat

digunakan baik secara berdiri sendiri maupun dilekatkan pada bahasa-bahasa lain seperti

COBOL dan C. SQL juga sudah menjadi bagian dari sejumlah DBMS, seperti Oracle, Sybase,

dan Informix.

1.1. SQL Sebagai Subbahasa

Penyebutan SQL sebagai bahasa query sebenarnya tidak tepat sebab kemampuan SQL

tidak terbatas hanya untu query (memperoleh data), tetapi juga mencakup kemampuan lain

seperti :

Pendefinisian struktur data

Pengubahan data

Pengaturan sekuritas, dll.

Terkadang SQL dikatakan sebagai subbahasa data. Bukan sebagai bahasa, alasannya

karena SQL tidak mendukung persyaratan bahasa yang lengkap, sekalipun SQL dapat dipakai

untuk mengakses basis data. SQl tidak menyediakan hal-hal berikut :

Pernyataan pengujian kondisi (semabam IF)

Pernyataan pengulangan (semacam REPEAT).

1.2. Antar Muka SQL Terhadap DBMS Penggunaan SQL pada DBMS cukup luas. SQL dapat dipakai oleh berbagai kalangan

(DBA, Pemrogram, ataupun Pengguna).

SQL sebagai bahasa administrasi basis data

Dalam hal ini SQL dipakai oleh DBA untuk menciptakan serta mengendalikan

pengaksesan basis data.

SQL sebagai bahasa query interaktif

Pengguna dapat memberikan perintah-perintah untuk mengakses basis data yang sesuai

dengan kebutuhannya. Hal ini sangat berguna untuk memenuhi kebutuhan-kebutuhan yang

bersifat seketika.

SQL sebagai bahasa pemrograman basis data

Pemrogram dapat menggunakan perintah-perintah SQL dalam program aplikasi yang

dibuat, guna mengakses basis data.

SQL sebagai bahasa client/server

Sebuah client dapat menjalankan suatu aplikasi yang mengakses basis data yang ada pada

suatu server. Dalam hal ini system operasi antara server dan client bisa berbeda. Aplikasi

pada client bias juga dibuat dengan menggunakan perangkat lunak DBMS yang berbeda

dengan server. Sebagai contoh

Aplikasi Visual FoxPro Sebagai klien

Oracle sebagai Database Server

Aplikasi Delphi Sebagai klien


Dilingkungan PC, pengaksesan data antar DBMS yang berbeda dapat dilakukan dengan

mudah berkat adanya ODBC (Open Database Connectivity). ODBC merupakan antar

muka pemrograman standar industri yang memungkinkan aplikasi-aplikasi

mengakses berbagai system manajemen basis data yang berada dalam berbagai

platform. ODBC memungkinkan ketidaktergantungan basis data melalui kaidah SQL

yang standar. Perangkat lunak ODBC tersusun atas sejumlah komponen, yaitu lapisan

aplikasi, manajer pengendali (driver manager), pengendali yang spesifik terhadap basis

data, dan sumber data. Aplikasi

ODBC.DLL

(Manajer Pengendali)

Pengendali Spesifik Terhadap DBMS

Sumber Data

(DBMS dan Perangkat Lunak jaringan)

DBMS

Aplikasi

Berisi SQL yang dilekatkan dan logika proses. Aplikasi melakukan pemrosesan

transaksi, dan menampilkan hasil. Hal ini dilakukan dengan memanggil fungsi-fungsi

API (Aplication Program Interface) yang disediakan oleh manajer pengendali untuk

berhubungan dengan sumber data.

Manajer Pengendali

Menyediakan antar muka ODBC yang umum, memuat pengendali yang spesifik

terhadap basis data sesuai yang diminta aplikasi, dan memetakan pemanggilan-

pemanggilan ODBC terhadap fungsi-fungsi yang disediakan pengendali yang spesifik

terhadap basis data.

Pengendali Spesifik terhadap basis data

Memproses pemanggilan-pemanggilan fungsi ODBC, mengkonversi SQL dan tipe

data sesuai dengan sintaks pada DBMS, dan memformat kode kesalahan DBMS pada

bentuk yang standar. Pengendali ini juga bertanggung jawab untuk mengirimkan hasil

dank ode kesalahan kepada manajer pengendali.

Sumber Data

Terdiri atas DBMS dan perangkat lunak Sistem Operasi atau jaringan yang digunakan

untuk berhubungan dengan DBMS yang ada pada computer lain.

SQL juga diterapkan pada internet atau intranet untuk mengakses basis data melalui halaman-

halaman web, untuk mendukung konsep web dinamis.

halaman 3 dari 12 Sistem Basis Data-Bag.9

oleh: Badrudin Hadibrata


1.3. Elemen SQL Elemen dasar SQL mencakup pernyataan, nama, tipe data, konstanta, ekspresi, dan fungsi

bawaan.

a. Pernyataan

Pernyataan adalah perintah SQL yang meminta suatu tindakan kepada DBMS.

SQL memiliki kira-kira 30 pernyataan, antara lain :

Pernyataan Keterangan ALTER Mengubah struktur tabel COMMIT Mengakhiri/menyetujui sebuah eksekusi transaksi CREATE Menciptakan tabel, ideks, database DELETE Menghapus record pada tabel GRANT Memberikan hak pengelolaan basis data kepada pengguna (user) DROP Menghapus tabel, indeks, database dll ……….

b. Nama

Nama digunakan sebagai identitas bagi objek-objek pada DBMS. Contoh objek pada

DBMS adalah tabel, kolom, dan pengguna.

c. Tipe Data Setiap data memiliki tipe data. Beberapa tipe data standar dapat dilihat pada tabel di

bawah ini :

Tipe Data Keterangan CHAR Untuk menyatakan deretan karakter (string). Misalnya, untuk

menyatakan nama orang, nama jalan, dll. INTEGER Untuk menyatakan bilangan bulat NUMERIC Untuk menyatakan bilangan real dll ……….

d. Konstanta

Konstanta menyatakan nilai yang tetap, beberapa contoh konstanta misalnya:

Konstanta numeric : 123, -234, 5,50

Konstanta : ‘Jl. Sukapura No.23’

Konstanta simbolik : yaitu konstanta yang tersedia pada SQL dan memiliki makna

tersendiri, contoh : SYSDATE (tanggal system), USER (nama pengguna yang

menjalankan SQL). Konstanta ini sering disebut Variabel System. e. Ekspresi

Ekspresi adalah segala sesuatu yang menghasilkan nilai. Ekspresi dapat digunakan untuk

menghitung nilai. Contoh : (laba/modal) * 100 f. Predikat (syarat)

Predicate selalu menghasilkan value bertipe Boolean yang isinya hanya True atau False

(benar/salah). Digunakan untuk syarat bagi data yang akan ditampilkan (diambil). Macam-

macam predicate di dalam sintaks SQL antara lain :


Predicate Fungsi Comparison ( <, >, <>, = , >=, <= )

Membandingkan data secara range. Misalnya : < lebih kecil daripada > lebih besar daripada < > tidak sama dengan

BETWEEN Membatasi data secara range. Misalnya : 5 <= x <= 10 , cara penulisannya : x BETWEEN 5 AND 10

EXISTS Memeriksa apakah data pada tabel lain ada atau tidak IN Memberikan beberapa batasan data.

Misalnya : x=’P’ atau x=’A’ atau x=’H’ , cara penulisannya : x IN (‘P’,’A’,’H’)

LIKE Membandingkan bagian isi data dari suatu field Misalnya : Nama_mhs yang dimulai dengan huruf ‘a’ Nama_mhs Like ‘a%’

IS NULL Memeriksa apakah field tersebut mempunyai isi / value atau tidak.

SOME / ANY / ALL Membandingkan isi field dengan isi field tabel lain sebagai tabel lookup. ANY salah satu record pada tabel lookup SOME sama dengan ANY ALL semua record pada tabel lookup

g. Operator Logika

Macam-macam operator logika yang digunakan di dalam sintaks SQL, anatara lain :

Operator Logika Fungsi AND Membandingkan dua predicate;

Jika salah satu predicate nilainya (isi) false, maka menghasilkan false. Jika kedua predicate bernilai true (benar) maka hasilnya true.

OR Membandingkan dua predicate; Jika salah satu predicate nilainya true, maka menghasilkan true. Jika kedua predicate nilainya false, maka hasilnya false.

NOT Membalikan nilai predicate; Jika predicate bernilai false maka hasilnya true, begitu sebaliknya apabila predicate bernilai true maka hasilnya false.

h. Fungsi String

String function (function yang mengolah kumpulan karakter/string) adalah sebagai

berikut :

Function Gunanya Concatenation Menggabungkan dua nilai yang bertipe string.

Contoh : ‘ADI’ || ‘PERGI’ menjadi ‘ADIPERGI’

LOWER Mengubah nilai string menjadi huruf kecil semua. Contoh : LOWER(‘ADI w 123’) menjadi ‘adi w 123’

UPPER Mengubah nilai string menjadi huruf besar semua Contoh : UPPER(‘saya 123’) menjadi “SAYA 123’

SUBSTRING Mengambil bagian dari sebuah nilai string. Contoh : SUBSTRING(‘surabaya’ from 3)


Hasilnya ‘rabaya’ SUBSTRING(‘surabaya’ from 3 for 4) Hasilnya ‘raba’

TRIM Menghapus karakter pada nilai string yang berada di tepi. Contoh : TRIM(LEADING’_’FROM’_ABC_’) Hasilnya ‘ABC_’ TRIM(TRAILING’_’FROM’_ABC_’) Hasilnya ‘_ABC’ TRIM(BOTH’_’FROM’_ABC_’) Hasilnya ‘ABC’ TRIM (BOTH’A’FROM’ABC’) Hasilnya ‘BC’ TRIM(‘ ABC ‘) Hasilnya ‘ABC’

i. Fungsi Bawaan (Fungsi Agregasi/fungsi yang mengolah nilai)

Adalah sebuah subprogram yang menghasilkan suatu nilai jika dipanggil. SQL memiliki

sejumlah fungsi bawaan/fungsi agregasi :

Function Kegunaan MIN Mengambil nilai yang terkecil pada sebuah field (atribut) MAX Mengambil nilai yang terbesar pada sebuah field (atribut) AVG Mengambil nilai rata-rata pada sebuah field (atribut) yang

telah dikelompokan. COUNT Menghitung jumlah record (digunakan untuk mencacah data). SUM Mengakumulasi (menjumlah) nilai pada sebuah field.

1.4. Kelompok Pernyatan SQL Pernyataan SQL dapat dikelompokan menjadi 4 kelompok, yaitu : DDL, DML, DCL,

Pengendali Transaksi.

a. DDL (Data Definition Language)

DDL merupakan kelompok perintah SQL yang berfungsi untuk mendefinisikan objek-

objek basis data (tabel, kolom/field, database), batasan-batasan terhadap suatu atribut, serta

hubungan antar tabel. Yang termasuk kedalam kelompok ini adalah :

CREATE membuat tabel/database

ALTER merubah struktur tabel (menambah/mengurangi/merubah atribut tabel)

DROP menghapus tabel/database

b. DML (Data Manipulation Language)

DML adalah kelompok perintah yang berfungsi untuk memanipulasi data dalam basis

data, misalnya untuk pengambilan, penyisipan, pengubahan, dan penghapusan data. Perintah

yang termasuk kedalam kategori DML adalah :

SELECT memilih data

INSERT menambah data


DELETE menghapus data

UPDATE mengubah data

c. DCL (Data Control Language)

DCL berisi perintah-perintah untuk mengendalikan pengaksesan data. Pengendalian

dapat dilakukan berdasarkan per pengguna, per tabel, per kolom maupun per operasi yang

boleh dilakukan. Perintah-perintah yang termasuk kedalam kelompok DCL adalah :

GRANT memberikan hak kendali pengaksesan data

REVOKE mencabut hak kendali pengaksesan data

LOCK TABLE mengunci tabel

d. Pengendali Transaksi

Pengendali transaksi adalah perintah-perintah yang berfungsi untuk mengendalikan

pengeksekusian transaksi. Perintah yang termasuk kedalam kategori ini :

COMMIT Menyetujui rangkaian perintah yang berhubungan erat (disebut transaksi) yang telah berhasil dilakukan.

ROLLBACK

Membatalkan transaksi yang dilakukan karena adanya kesalahan atau kegagalan pada salah satu rangkaian perintah.

1.5. Bekerja Dengan SQL Penulisan SQL pada berbagai DBMS sangat bervariasi. Contoh bentuk umum yang

sering dipakai adalah:

Select * From tblpengarang

atau

Select * From tblpengarang; (menggunakan tanda titik koma diakhir)

atau

Select * ; From tblpengarang (tanda titik komanya diletakan diakhir baris pertama, untuk

memberitahukan bahwa baris tersebut belum berakhir).

a. Menciptakan Tabel (DDL)

Tabel diciptakan melalui pernyataan CREATE TABLE, sebagai contoh :

create table mahasiswa(NIM varchar(10) primary key not null, Nama_mhs

varchar(20) not null, Alamat varchar(20), Jenis_Kelamin varchar(3));

Pada contoh di atas tabel yang diciptakan adalah tabel mahasiswa, sebagai berikut:

Atribut Tipe Data

Length Kunci Primer

Not Null (tidak boleh kosong)

Allow Null (boleh kosong)

NIM varchar 10 Primary key - Nama_mhs varchar 20 - - Alamat varchar 20 - - Jenis_Kelamin varchar 3 - -


Contoh lain : membuat tabel Nilai,

Create table Nilai(NIM char(6) not null, kd_kul char(6) not null, indeks_nilai char(1), primary key (nim, kd_kul));

b. Menciptakan Indeks (DDL)

Indeks biasa diciptakan dengan tujuan :

Indeks dapat meningkatkan kinerja

Indeks menjamin bahwa suatu kolom bersifat unik.

Sebagai contoh, jika seringkali terdapat perintah untuk mengurutkan data mahasiswa menurut

nama mahasiswa, maka nama mahasiswa akan lebih baik kalau diindeks.

Dengan adanya indeks, pencarian suatu data yang berdasarkan pada kolom yang

diindeks akan dapat dilakukan dengan cepat. Kelemahannya, pengindeksan memperlambat

proses penambahan dan penghapusan baris pada tabel, karena saat terjadi penambahan atau

penghapusan baris, indeks perlu diperbaharui.

Contoh membuat indeks dari tabel mahasiswa:

Create index idx_nama

On mahasiswa (nama_mhs);

Bila yang akan diindeks adalah kolom yang nilainya bersifat unik, maka kata Unique perlu

ditambahkan. Contoh :

Create unique index idx_nim

On mahasiswa (NIM);

Jika indeks-nya tersusun atas lebih dari satu kolom, maka bentuknya adalah :

Create Unique Index nama_indeks

On nama_tabel (kolom_x, kolom_y);

c. Menghapus Indeks (DDL)

Indeks yang tidak dipergunakan lagi bias dihapus dengan mempergunakan pernyataan

DROP INDEX. Sintaksnya :

Drop index nama_indeks

On nama_tabel

Contoh :

Drop index idx_nama

On mahasiswa;

Atau ada juga bentuk lain penulisannya :

Drop table nama_tabel.nama_indeks

Contoh : Drop table mahasiswa.idx_nama


d. Menghapus Tabel (DDL)

Tabel yang sudah tidak terpakai lagi dapat dihapus dengan menggunakan pernyataan

DROP TABLE. Contoh : menghapus tabel mahasiswa,

Drop table mahasiswa;

Catatan : saat tabel dihapus, indeks yang berkaitan dengan tabel tersebut akan ikut dihapus.

e. Mengubah Struktur Tabel (DDL)

Setelah suatu tabel tercipta, kadangkala kita perlu mengubah struktur tabel. Perubahan

struktur ini dapat berupa penambahan atribut atupun pengurangan/penghapusan atribut

tertentu. Hal ini dapat dikerjakan dengan menggunakan pernyataan ALTER TABLE. Sintaks

SQL-nya sebagai berikut :

Alter table t add A D ;

Dimana t mewakili nama tabel, A mewakili nama atribut dan D mewakili tipe data untuk

atribut A tersebut.

Contoh : menambah atribut hobi , tipe datanya character pada tabel mahasiswa.

Alter table mahasiswa add hobi char(10);

Sedangkan untuk penghapusan atribut A dari tabel t, sintaksnya adalah :

Alter table t drop A;

Contoh : menghapus atribut alamat dari tabel mahasiswa,

Alter table mahasiswa drop alamat;

f. Menambah Data/Record (Manipulasi Data/DML)

Sintaks SQL untuk penambahan record baru kesebuah tabel adalah :

Insert into t (A1, A2, … , An)

Values (v1, v2, … , vn)

Dimana :

t adalah nama tabel yang akan mengalami penambahan record

A1, A2, … , An adalah nama-nama atribut yang akan diisi nilai

v1, v2, … , vn adalah nilai-nilai yang akan mengisi atribut-atribut tersebut.

Contoh : menambah record baru ke tabel mahasiswa,

insert into mahasiswa(nim, nama_mhs, alamat, jenis_kelamin)

values('001','Brata Dewa','Cirebon','L');

Atau bisa juga tanpa menyebutkan nama-nama atributnya, asalkan urutan isinya sesuai dengan

urutan atribut pada tabel yang dimaksud.

Contoh : menambahkan record baru ke tabel mahasiswa tanpa menyebutkan nama-nama

atributnya,

insert into mahasiswa values('002','Tiara Putri','Jakarta','P');

bentuk yang lainnya :


insert into mahasiswa

set nim='003', nama_mhs='Fadlin Alwan Hanafi', alamat='null', jk='P';

g. Mengubah Data (Record) (DML)

Sintaks SQL untuk pengubahan nilai atribut pada suatu record dari sebuah tabel adalah :

Update t

Set assignment

[where P]

Dimana :

t adalah nama tabel yang akan mengalami perubahan data

assignment adalah ekspresi pemberian nilai baru untuk suatu atribut yang akan kita ubah

P merupakan predikat atau criteria untuk pemilihan record yang akan dikenai perubahan,

jika klausa where ini tidak digunakan, maka perubahan akan dilakukan pada semua record

di dalam tabel t.

Contoh : mengubah nilai atribut sks untuk mata kuliah tertentu pada tabel kuliah:

Update kuliah

Set sks=4

Where kode_kul=’IF-310’;

Contoh lain : marubah alamat seluruh record mahasiswa menjadi ‘Cirebon’ pada tabel

mahasiswa,

Update mahasiswa

Set alamat=’Cirebon’;

Perintah update juga dapat digunakan untuk melakukan perubahan dengan mengacu

pada nilai atribut yang lama (sebelum perubahan), misalnya untuk penambahan gelar di depan

nama dosen tertentu, sintaknya sebagai berikut :

Update dosen

Set nama_dosen = ’Prof. ‘ + nama_dosen

Where kode_dos = 2;

h. Menampilkan Data (Select) (DML)

Perintah untuk menampilkan data akan banyak kita gunakan untuk menampilkan informasi

berdasarkan kriteria dan urutan tertentu. Berikut ini sintaks untuk menampilkan data:

Select [field1, field2,... ] from [nama_tabel]

Where [kondisi]

Order by [nama_field]

Group by [nama_field] asc/desc;

Contoh :

• Untuk menampilkan data pegawai yang berjenis kelamin perempuan

• Untuk menampilkan nama pegawai yang diawali huruf C.

Awal sebuah teks berada pada satu huruf disebelah kiri (left), maka dapat ditulis

dengan :

Select * from pegawai where left(nama,1)=’c’;

Atau sama artinya dengan penulisan sembarang huruf depannya adalah huruf c.

Penulisan sembarang huruf dapat diwakili dengan tanda persen (%).

Select * from pegawai where nama like ’c%’;

• Untuk menampilkan nama pegawai yang huruf keduanya adalah N.

Untuk menentukan huruf yang berada ditengah-tengah teks, dapat menggunakan

substring :

Select * from pegawai where substring(nama,2,1)=’n’;

Atau sama artinya dengan penggunaan fungsi mid di bawah ini :

Select * from pegawai where mid(nama,2,1)=’n’;

Atau sama artinya dengan penulisan sembarang huruf dibagian akhir tetapi dengan dua

huruf di depannya adalah satu karakter sembarang dan huruf n. Penulisan garis bawah (

_ ) untuk menggantikan setiap karakter sembarang. Sedangkan tanda % untuk

menggantikan semua karakter sembarang yang tersisa.

Select * from pegawai where nama like ’_n%’;

halaman 11 dari 12 Sistem Basis Data-Bag.9

oleh: Badrudin Hadibrata


Latihan :

1. Menampilkan seluruh data siswa dan diurutkan berdasarkan nama_mhs secara descending (menurun).

select * from siswa order by nama_mhs desc;

2. Menampilkan seluruh data siswa yang beralamat di "majalengka" dan diurutkan

berdasarkan nama_mhs secara ascending(menaik).

select * from siswa where alamat='majalengka' order by nama_mhs asc;

3. Untuk menampilkan nama siswa yang huruf keduanya adalah "N" Untuk menentukan

huruf yang berada ditengah-tengah teks, dapat menggunakan substring , perintahnya:

Select * from siswa where substring(nama_mhs,2,1)=’n’; Atau sama artinya dengan penggunaan fungsi mid di bawah ini: Select * from siswa where mid(nama_mhs,2,1)=’n’;

Atau sama artinya dengan penulisan sembarang huruf dibagian akhir tetapi dengan dua huruf di depannya adalah satu karakter sembarang dan huruf n. Penulisan garis bawah ( _ ) untuk menggantikan setiap karakter sembarang. Sedangkan tanda % untuk menggantikan semua karakter sembarang yang tersisa. perintahnya:

Select * from siswa where nama_mhs like ’_n%’;

4. Untuk menampilkan nama pegawai yang diawali huruf C. Awal sebuah teks berada pada satu huruf disebelah kiri (left), maka dapat ditulis dengan : perintahnya:

Select * from siswa where left(nama_mhs,1)=’c’;

Atau sama artinya dengan penulisan sembarang huruf depannya adalah huruf c. Penulisan sembarang huruf dapat diwakili dengan tanda persen (%). perintahnya :

Select * from siswa where nama_mhs like ’c%’;

5. Menampilkan siswa yang beralamat di cirebon atau kuningan. Perintahnya : select * from siswa where alamat='cirebon' or alamat='kuningan';

6. coba anda ketikan 2 buah perintah berikut:

• select * from siswa where alamat='cirebon' or alamat='kuningan' and jk='p';

• select * from siswa where (alamat='cirebon' or alamat='kuningan') and

jk='p'; Apa bedanya hasil dari kedua perintah tersebut ?

sql

Documents