SQL ( Structured Query Language )

Structured Query Language

Sejarah

• Tahun 1986, ANSI (American National

Standards Institute) dan ISO mengumumkan standard SQL, SQL-86

• IBM merilis Systems Application Architecture) SAA-SQL tahun 1987
• Berturut-turut ANSI merilis SQL-89, SQL- 92, dan SQL- 99

SQL (Structured query language)

• SQL adalah bahasa yang digunakan untuk mengelola database relasional
• SQL adalah bahasa standard untuk sistem manajemen database relasional
• Sistem database yang menggunakan SQL :
  1. Oracle
  2. DB2
  3. Sybase
  4. MS SQL
  5. MS Access
  6. My SQL

Type Data

Dibedakan menjadi :

1. Tipe data numerik

semua data bilangan yang dapat diperhitungkan bukan angka yang bersifat keterangan, mis jumlah komputer

jenis tipe data numerik : integer, float, single, double, currency.

          2. Tipe data karakter

semua data huruf, angka dan tanda baca jenis tipe data karakter : char, string, text, memo.

          3. Tipe data tanggal

mendefinisikan waktu

jenis tipe data waktu : date, datetime, time, timestamp.

          4. Tipe data boolean ; tipe data khusus untuk menyatakan status benar atau salah, ya atau tidak.

kebanyakan mereka memiliki perintah tambahan yang proprietary.

Jenis perintah SQL :

1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
3. DCL (Data Control Language)

Skema Contoh

Struktur Dasar

• Select, berkaitan dengan operasi proyeksi

pada aljabar relasional. Digunakan untuk mendaftar atribut yang ingin dikeluarkan sebagai hasil query

• From, berkaitan dengan operasi produk

kartesian (relasi mana yang akan di-scan)

• Where, berkaitan dengan predikat seleksi.

Operasi SELECT

Operasi select digunakan untuk mengambil sebagian atau seluruh isi tabel dari suatu basisdata.

Contoh : “Tentukan nama-nama dari semua cabang bank dalam relasi loan “

Query-nya :

SELECT branch-name FROM loan

 

Operasi WHERE

Klausa where menspesifikasi kondisi yang harus dipenuhi oleh hasil query

– Berkaitan dengan predikat seleksi pada aljabar relasional.

Contoh : “Temukan semua loan number untuk pinjaman-pinjaman yang dibuat pada cabang Perryridge dengan jumlah lebih besar dari $1200”.

Query-nya ditulis sebagai berikut :

SELECT loan-number FROM loan WHERE branch-name = “Perryridge” and amount >1200;

• Perbandingan dapat dikombinasikan dengan

menggunakan operasi logika and, or, dan not.

• Operand hubungan logika dapat menggunakan

operasi perbandingan <,<=,>,>=,=, dan <>

• Contoh:

SELECT loan-number FROM loan WHERE amount <=100000 and amount >=90000;

• SQL juga memasukkan perintah between
  • untuk menentukan apakah suatu nilai lebih kecil daripada atau sama dengan suatu nilai lain dan lebih besar daripada atau sama dengan suatu nilai lain.
  • Contoh : “jika diinginkan menemukan loan-number

yang jumlah pinjamannya antara $90000 dan $100000”

Query ditulis sebagai berikut :

SELECT loan-number FROM loan WHERE amount between 90000 and 100000

• Klausa from menunjukkan daftar relasi

yang dilibatkan dalam query

– Berkaitan dengan operasi produk kartesian pada aljabar relasional

• Contoh: Produk Kartesian dari borrower x loan

select * from borrower, loan;

Contoh query : “Untuk semua customer yang mempunyai sebuah pinjaman dari bank, temukan nama dan loan number mereka”.

Dalam SQL ditulis :

SELECT distinct customer-name,borrower.loan-number FROM borrower, loan WHERE borrower.loan-number = loan.loan-number

Contoh: “Tampilkan nama,loan number and loan amount dari semua customer yang memiliki pinjaman di cabang Perryridge”

select customer-name, borrower.loan-number, amount from borrower, loan where borrower.loan-number = loan.loan-number and branch-name = ‘Perryridge’

DDL (data definition language)

Merupakan kelompok perintah yang digunakan untuk melakukan pendefinisian tabel.

Kelompok perintah DDL dapat membuat :

1. Tabel
2. Mengubah struktur
3. Menghapus tabel
4. Membuat index

DML (Data manipulation Language)

Digunakan untuk melakukan manipulasi data dalam database, menambahkan (insert), mengubah (update), menghapus (delete), mengambil dan mencari data (query).

Perintah SQL standar tsb dapat digunakan untuk menyelesaikan tugas yang diberikan berhubungan dengan data suatu database.

Dcl (data control language)

Perintah untuk melakukan pendefinisian pemakai yang boleh mengakses database dan apa saja privilegenya.

Fasilitas ini tersedia pada sistem manajemen database yang memiliki fasilitas keamanan dengan membatasi pemakai dan kewenangannya.

Data definision language (DDL)

digunakan untuk melakukan pembuatan struktur database, mulai dari mendefinisikan database, tabel-tabel dan indexnya, view, dan perintah-perintah berkenaan dengan maintenance dari strukture database itu sendiri.

Membuat database

Perintah CREATE DATABASE namadatabase

Perintah ini digunakan pertama kali sebelum membuat tabel, view, fungsi, prosedur atau pun komponen lain suatu sistem database.

Contoh :

create database datamahasiswa;

Membuat tabel

Perintah CREATE TABLE namatabel(Field1 TipeData1 [, field2 tipedata2[, ...] ]

);

perintah ini diberikan untuk membuat tabel dalam suatu database

contoh :

create table kota(kodekota char(3) not null,namakota varchar(35) null,primary key (kodekota));

Menambah Field baru tabel

Perintah ALTER TABLE namatable

ADD fieldbaru tipenya;

namatabel adalah nama dari tabel yang akan ditambah fieldnya

fieldbaru adalah nama field yang akan ditambahkan .

Contoh :

alter table bukualamat add foreign key (kodekota) reference kota (kodekota);

Mengubah lebar field tabel Perintah ALTER TABLE namatabel

MODIFY fieldnya tipenya panjangbaru namatabel adalah nama dari table yang akan diubah salah satu fieldnya.

fieldnya adalah nama field yang akan diubah lebar fieldnya.

tipenya dan panjangbaru merupakan berubahan yang akan diterapkan kepada tabel tsb.

contoh :

alter table dbmahasiswa modify (nama_mahasiswa char(45));

Menghapus tabel

Perintah DROP TABLE namatabel

namatabel adalah nama dari tabel yang akan dihapus secara fisik.

penghapusan menyebabkan struktur dan data yang dibuat akan hilang

Menghapus database

Perintah DROP DATABASE namadatabase;

namadatabase adalah nama dari database yang akan dihapus.

penghapusan database akan menyebabakan seluruh struktur dan data yang ada didalamnya menjadi hilang

Membuat index

Perintah

CREATE INDEX namaindeks ON namatabel (namakolom1[,namakolom2, ...])

namaindeks adalah nama yang diacu untuk mendapatkan data index dari suatu kolom dalam tabel.

namatabel adalah nama dari tabel yang kolom-kolomnya akan dibuatkan indexnya.

contoh :

create index kotaonbukualamat on bukualamat (kodekota);

Menghapus index

Perintah DROP INDEX namaindex ON namatabel

penghapusan index tidak menyebabkan terhapusnya tabel.

penghapusan index tabel suatu kolom hanya menyebabkan prosees pencarian data pada kolom tersebut bisa lebih lambat.

Data manipulation language (DML)

Merupakan bagian dari SQL yang digunakan untuk melakukan manipulasi dalam database (tambah, ubah, hapus, cari)

Contoh Dataset Loan

loan-number	branch-name	amount
L-170	Downtown	3000
L-230	Redwood	4000
L-260	Perryridge	1700

Borrower

customer-name	loan-number
Jones	L-170
Smith	L-230
Hayes	L-155

Insert

Perintah INSERT INTO namatabel (field1 [,field2 [,...]]) VALUE (nilai1 [,nilai2 [,...]]; atau INSERT INTO namatabel VALUES (nilai1 [,nilai2[,...]]); 

namatabel adalah tabel yang akan diisi data.

field1,field2 ... Adalah field-field (kolom) dari tabel yang akan diisi

nilai1,nilai2 ... Adalah data yang akan dimasukkan dalam tiap kolom yang disebutkan pada bagian field.

Untuk menambahkan satu tuple dalam relasi digunakan statement

insert.

Contoh :

INSERT INTO account values (“Perryridge”,”A-9732”,1200)

Query ini identik dengan

INSERT INTO account (branch-name, account-number,balance) values (“Perryridge”,”A-9732”,1200)

Insert juga dapat dilakukan untuk suatu hasil dari query yang lain.

Contoh :

INSERT INTO account SELECT branch-name, loan-number, 200

FROM loan WHERE branch-name = “Perryridge”

Update

Perintah UPDATE namatabel

SET field1 = nilai1 [, field2= nilai2 [,...]] [WHERE kondisi];

namatabel adalah nama dari tabel yang akan diperbaiki datanya

field1 adalah nama field dalam tabel yang akan diubah.

nilai1 adalah data yang akan dimasukan ke dalam field1

field2 dan nilai 2 adalah nama field dan datanya dst.

kondisi adalah kriteria data dalam tabel yang akan diperbaiki

perintah update digunakan untuk memperbaiki data dalam suatu record (baris) dalam suatu tabel, perbaikan dapat dilakukan untuk satu record, beberapa atau seluruh record.

Contoh :

untuk menaikkan saldo para nasabah sebesar 5% ditulis

query sebagai berikut :

UPDATE account SET balance = balance * 1.05

Untuk menaikkan saldo nasabah sebesar 6% bagi nasabah yang saldonya lebih dari $10000

UPDATE account SET balance = balance *1.06 WHERE balance >10000

question :

Apa yang akan terjadi jika dalam pengupdate-an suatu record apabila lupa menulis kondisinya ?

Query yang sama dengan sebelumnya: Naikkan semua account dengan saldo di atas $10,000 sebesar 6%, account yang lain sebesar 5%.

update account set balance = case when balance <= 10000 then balance *1.05 else balance * 1.06 end

delete

Perintah DELETE FROM namatabel [WHERE kondisi];

namatabel adalah nama dari tabel yang akan dihapus datanya.

kondisi adalah kriteria data dalam tabel yang akan dihapus.

perintah DELETE digunakan untuk melakukan penghapusan record dari suatu tabel yang memiliki kondisi yang dinyatakan dalam pernyataan kondisi.

“Hapus semua account pada cabang Perryridge”

delete from account where branch-name = ‘Perryridge’

“Hapus semua account di setiap cabang yang berlokasi di Needham city”

delete from account where branch-name in (select branch-name

from branch where branch-city = ‘Needham’) delete from depositor

where account-number in (select account-number from branch, account

where branch-city = ‘Needham’ and branch.branch-name = account.branch-name)

Apa yang akan terjadi jika dalam DELETE suatu record apabila lupa menulis kondisinya ?

Data mahasiswa

NIM	Nama	Jurusan
2901	Anjar	Sipil
2902	Jasmin	Hukum
2903	Bayu	Hukum

Ingin menghapus data Mahasiswa dengan NIM = 2902 :

DELETE from Mahasiswa Where NIM = 2902;

Hasil

NIM	Nama	Jurusan
2901	Anjar	Sipil
2903	Bayu	Hukum

Jika ingin menghapus record mahasiswa bernama Anjar dan Bayu bersamaan, dapat digunakan perintah :

DELETE from Mahasiswa

where Nama =‘Anjar’ or Nama=‘Bayu’;

Beberapa hal yang patut diperhatikan dalam penulisan perintah SQL adalah:

1. Perhatikan huruf besar - huruf kecil. Agus tidak sama dengan agus. Gaji_Pegawai tidak sama dengan GajiPegawai.
2. Jangan lupa untuk membubuhi tanda titik koma ( ; ) di setiap akhir penulisan perintah.

Select

Perintah

SELECT (* | field1 [,field2 [,...]]) FROM namatabel [WHERE kondisi]

namatabel adalah nama dari tabel yang akan ditampilkan datanya

field1,field2 ... Adalah nama field yang akan ditampilkan datanya.

* digunakan untuk menampilkan seluruh field dari tabel

kondisi adalah kriteria data dalam table yang akan ditampilkan

Perintah SELECT digunakan untuk menampilkan isi dari suatu tabel

Kondisi

LIKE

merupakan kata kunci dalam SQL yang digunakan untuk mendefinisikan suatu kriterisa yang lebih fleksibel.

Kondisi yang dinyatakan dengan menggunakan LIKE dapat memfilter data sehingga dapat menampilkan suatu kriteria seolah dengan menggunakan bahasa inggris.

Perintah

SELECT * FROM namatabel WHERE namafield LIKE ‘datadicari’;

perintah ini akan menampilkan seluruh record dalam tabel yang memiliki data dalam nama field yang disebutkan dengan “datadicari”

Perintah

SELECT * FROM namatabel WHERE namafield LIKE ‘datadicari%’;

perintah ini akan menampilkan seluruh record dalam tabel yang memiliki data dalam nama field yang disebutkan diawali dengan “datadicari”

View

Perintah

CREATE VIEW namaview

AS ekspresiQuery

namaview adalah nama dari view yang akan dibuat

ekspresiQuery adalah perintah select dan kondisi query yang ditentukan sama seperti halnya pada saat melakukan perintah select dengan menggunakan kondisi.

Data control language (DCL)

Terdiri atas sekelompok perintah SQL untuk memberikan hak otorisasi mengakses database, mengalokasikan space, pendefinisian space, pengauditan penggunaan database.

Secara umum DCL merupakan bahasa yang digunakan untuk melakukan pengelolahan pemakai yang dapat melakukan akses dan manipulasi database terutama perintah GRANT dan REVOKE

Perintah COMMIT dan ROLLBACK merupakan kelengkapan fasilitas dalam pembuatan aplikasi yang memungkinkan suatu transaksi yang terjadi untuk dapat segera disimpan atau dibatalkan transaksinya.

Fungsi Agregat

Fungsi yang disediakan oleh SQL untuk melakukan ringkasan (summary) data, bukan menampilkan data baris per baris.

Fungsi Agregat di SQL :

1. Sum()
2. Avg()
3. Max()
4. Min()
5. Count()

Fungsi agregat dapat disisipkan pada perintah SELECT, yang digunakan untuk melakukan manipulasi sederhana ataupun untuk mendapatkan informasi dari suatu tabel.

SUM

sum(namafield)

merupakan fungsi agregat yang digunakan untuk melakukan penjumlahan isi field yang bertipe numerik yang namanya disebutkan padan namafield yang dijadikan parameter pada fungsi sum()

Tabel Karyawan

Kode	Nama	Gaji
KP01	Amrin	200000
KP02	Camelia	300000
KP03	Bembi	100000

SELECT SUM(gaji) From Karyawan

Hasil

Gaji

600000

AVG

avg(namafield)

fungsi ini digunakan untuk mendapatkan nilai rata-rata suatu field yang bertipe numerik yang namanya disebutkan sebagai parameter pada fungsi avg().

SELECT AVG(gaji) From Karyawan

Hasil

Gaji

200000

Max

max(namafield)

fungsi ini digunakan untuk mendapatkan nilai terbesar(maximum) dari field bertipe numerik yang nama fieldnya dijadikan parameter pada fungsi min

Tabel Karyawan

Kode	Nama	Gaji
KP01	Amrin	200000
KP02	Camelia	300000
KP03	Bembi	100000

select MAX(gaji) from Karyawan;

Hasil :

Gaji

300000

Min

min(namafield)

fungsi ini digunakan untuk mendapatkan nilai terkecil (minimum) dari field bertipe numerik yang nama fieldnya dijadikan parameter pada fungsi min

select MIN(gaji) from Karyawan;

Gaji

100000

count(namafield)

digunakan untuk mengetahui jumlah record dari suatu tabel.

Jumlah record yang ditampilkan adalah jumlah record berdasarkan perintah SELECT

sql> use dbmahasiswa;

database changed

sql>select count(*) from bukumahasiswa

+----------------+

| count(*) |

|-----------------|

| 2 |

+-----------------+

Group by

Perintah Group By memiliki kegunaan untuk melakukan perhitungan berdasarkan kriteria tertentu.

Pegawai_baru

Kode	Nama	Asal	Pendidikan	Gaji
PB01	Ronald	Jakarta	S1	400000
PB02	Made	Bali	S1	300000
PB03	Aziz	Semarang	S1	300000
PB04	Mustofa	Semarang	D3	250000
PB05	Eka	Jakarta	S1	275000
PB06	Gozali	Yogya	D3	200000
PB07	Dani	Jakarta	S1	350000

Dari tabel Pegawai_baru, kita ingin menampilkan gaji tertinggi / maksimum yang diperoleh pegawai berdasarkan pendidikannya.

select Pendidikan,max(Gaji)
from Pegawai_baru
GROUP BY Pendidikan;

Hasil

D3	250000
S1	400000

select Asal,count(Asal) from Pegawai_baru GROUP BY Asal;

Bali	1
Jakarta	3
Semarang	2
Yogya	1

select Pendidikan,count(Pendidikan),sum(Gaji) from Pegawai_baru GROUP BY Pendidikan;

D3	2	162500
S1	5	450000