KUMPULAN PERINTAH
SQL
Odi Nurdiawan, [Link]
Perjanjian
• ORACLE menganggap semua perintah adalah
HURUF BESAR
– Semua sama : SELECT, select, Select
– Termasuk Nama Object : SISWA, siswa, Siswa
dianggap sama
– kecuali
• LITERAL menggunakan tanda ‘…’, tergantung penulisan:
‘Ini’, ‘INI’,’ini’ dianggap berbeda
• NAMA suatu pengenal menggunakan tanda “…” : “SISWA”,
“Siswa”, “siswa” dianggap berbeda
Pendahuluan
• Perintah SQL:
– Data Retrieval. Digunakan untuk mengambil/membaca Object
Database
• SELECT
– DDL (Data Definition Language). Digunakan untuk menyatakan
suatu Object Database
• CREATE, ALTER, DROP, RENAME, TRUNCATE
– DML (Data Manipulation Language). Digunakan untuk
pengubahan Object Database
• INSERT, UPDATE, DELETE, MERGE
– Transaction Control. Digunakan untuk mengendalikan Transaksi
• COMMIT, ROLLBACK, SAVEPOINT
– DCL (Data Control Language). Digunakan untuk mengendalikan
Object Database
• GRANT, REVOKE
Penulisan SQL SELECT Dasar
• Pembagian/Kemampuan perintah SELECT
– Proyeksi : Memilih Kolom
– Seleksi : Memilih Baris
– Join : Penggabungan tabel
• Format :
– SELECT *|{[DISTINCT] kolom|ekspresi [alias],…} FROM tabel
• SELECT artinya memilih kolom mana yang akan
ditampilkan (proyeksi)
– Dapat berupa list kolom, suatu persamaan (operasi aritmatika),
fungsi, obyek tertentu
– Dapat diberikan alias (nama lain) pada masing-masing list
• FROM menunjukkan nama Tabel yang akan ditampilkan
– Dapat berupa tabel tunggal, join, view, inline view
Contoh Perintah SQL
• Melihat seluruh tabel yang dimiliki oleh USER
– SELECT * FROM tab;
• Melihat struktur suatu tabel
– DESC emp;
• Melihat seluruh isi suatu tabel
– SELECT * FROM emp;
• Melihat hanya sebagian kolom dari suatu tabel
– SELECT ename,deptno FROM emp;
• Melihat sekaligus melakukan operasi pada suatu kolom dan
memberikan alias pada masing-masing kolom
– SELECT ename nama,sal gaji,sal*(0.10) bonus FROM emp;
• Melakukan penggabungan dengan suatu literal dan kolom
– SELECT ‘nama : ’||ename FROM emp;
• Mengambil hanya nilai-nilai yang berbeda/membatasi hasil yang
muncul (semua pegawai bekerja pada departemen apa saja)
– SELECT DISTINCT deptno FROM emp;
Memilih/Membatasi dan Mengurutkan Data
• Memilih baris data mana saja yang akan ditampilkan (seleksi)
• Format :
– SELECT *|{[DISTINCT] kolom|ekspresi [alias],…} FROM table
[WHERE kondisi] [ORDER BY kolom [ASC|DESC]];
• WHERE menyatakan batasan apa saja yang diberikan
• ORDER BY menyatakan hasil akan diurutkan menurut kolom mana
(bisa lebih dari satu kolom)
– ASC (atau tidak disebutkan/default) menyatakan urutan naik
– DESC menyatakan urutan turun
• Kondisi :
– Perbandingan - =, <>, >, <. <=, >=
– Range - IN(…), BETWEEN … AND …, LIKE
– Boolean - AND, OR, NOT
– NULL - IS NULL, IS NOT NULL
Contoh Perintah SQL
• Membaca data semua pegawai yang bekerja pada departemen dengan
nomor departemen 40
– SELECT * FROM emp WHERE deptno=40;
• Membaca data lengkap pegawai dengan nama ADAMS
– SELECT * FROM emp WHERE ename=‘ADAMS’;
• Membaca pegawai yang memiliki gaji >= 2000 dollar dan bekerja pada
departemen 20, 30 atau 40
– SELECT * FROM emp WHERE sal>=2000 AND deptno IN(20,30,40);
• Membaca data pegawai dari departemen 40 yang memiliki gaji dari
1000 sampai 2000 dollar
– SELECT * FROM emp WHERE sal BETWEEN 1000 AND 2000 AND
deptno=40;
• Membaca data pegawai yang memiliki huruf kedua dari nama adalah
‘D’ dan bekerja pada departemen tertentu, dan urutkan berdasarkan
nama dengan urutan menurun
– SELECT * FROM emp WHERE ename like ‘_D%’ AND deptno IS NOT
NULL ORDER BY ename DESC;
Fungsi Single-Row
• Input fungsi dari satu baris/record, dan menghasilkan satu per
baris. Input banyak baris, menghasilkan banyak baris yang sama
– Mengolah data items
– Dapat menerima argumen dan menghasilkan satu nilai
– Mengeluarkan nilai untuk setiap baris data
– Dapat mengubah tipe data
– Dapat dalam bentuk bersarang (nested)
– Dapat menerima argumen berupa data kolom atau suatu ekspresi
• Fungsi Multi-Row : Melakukan operasi pada sekelompok baris
data untuk menghasilkan sebuah data. Input fungsi dari banyak
baris, menghasilkan satu atau banyak baris (Gouping, Fungsi
Agregat)
Contoh Fungsi
• Umum (nvl, nvl2, decode)
– SELECT nvl(deptno,0) FROM emp;
• Karakter (lower, upper, initcap, concat, substr)
– SELECT lower(ename) FROM emp;
• Bilangan (round, trunc, mod)
– SELECT sal,round(sal/100) FROM emp;
• Tanggal (sysdate, add_months, round, trunc)
– SELECT sysdate,sysdate+1 besok FROM dual;
• Konversi implisit, atau eksplisit (to_number, to_date, to_char)
– SELECT to_char(sysdate,’dd-mm-yyyy’) FROM dual;
Menggabungkan Tabel (Join)
• Kegunaan :
– Digunakan untuk menampilkan dari banyak tabel
– Mengkombinasikan seluruh kemungkinan data antar banyak tabel
– Mendapatkan informasi yang tersimpan pada tabel lain (melakukan
relasi antara satu tabel dengan tabel lainnya)
• Jenis Join
– Equijoin
– Nonequijoin
– outer join
– self join
• Mulai 9i, memiliki jenis join yang kompatibel
dengan SQL’99
Contoh Join
• Kartesian, untuk mendapatkan seluruh kombinasi data
– SELECT * FROM emp,dept;
• Equijoin, relasi antar tabel dengan nilai data yang sama tepat
– SELECT * FROM emp e,dept d WHERE [Link]=[Link] AND
ename=‘ADAMS’;
• Nonequijoin, untuk mencari/membandingkan nilai antara
– SELECT * FROM emp e,salgrade s WHERE [Link] BETWEEN [Link]
AND [Link] AND ename=‘ADAMS’;
• outer join, relasi dengan tabel yang kemungkinan nilai datanya tidak
ada
– SELECT * FROM emp e,dept d WHERE [Link]=[Link](+);
• self join, relasi dengan diri sendiri
– SELECT * FROM emp e,emp m WHERE [Link]=[Link] AND
[Link]=‘ADAMS’;
Menampilkan Data Menggunakan
Fungsi Group
• Mengoperasikan sekelompok baris data menjadi
bentuk group data
• Fungsi Group : AVG, COUNT, MAX, MIN, SUM,
…
• Format :
– SELECT [kolom,] fungsi_group(kolom),… FROM
tabel [WHERE kondisi] [GROUP BY kolom]
[HAVING kondisi_group] [ORDER BY kolom];
Contoh Group
• Menghitung jumlah pegawai setiap departemen dan
mengurutkan hasilnya berdasar nomor departemen
– SELECT deptno,count(empno) jumlah FROM emp GROUP BY
deptno ORDER BY deptno;
• Menghitung total gaji pegawai dari departemen nomor 20,
30, atau 40, tetapi hanya menampilkan yang total setiap
jurusannya lebih besar dari 5000 dollar
– SELECT deptno,sum(sal) FROM emp WHERE deptno
IN(20,30,40) GROUP BY deptno having sum(sal)>5000 ORDER
BY deptno;
Subquery
• Mencari sesuatu yang berasal dari hasil query lainnya
• Petunjuk
– Subquery diawali dengan kurung buka dan diakhiri dengan kurung
tutup
– Subquery diletakkan pada sisi kanan dari suatu perbandingan
– Penggunaan order by dalam subquery tidak diperlukan, kecuali
untuk melakukan analisa top-n
– Gunakan operator yang sesuai dengan hasil dari subquery. Single-
row operator dengan single-row subquery, multiple-row operator
dengan multiple-row subquery
Contoh Subquery
• Siapa saja pegawai yang gajinya lebih besar dari gaji Adam ? Untuk
itu, harus dicari dulu, berapa gaji Adam ?
– SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE
ename=‘ADAMS’);
• Hasil subquery harus satu baris, menggunakan perbandingan
– SELECT * FROM emp WHERE sal=(SELECT sal FROM emp WHERE
ename=‘ADAMS’);
• Hasil subquery boleh banyak baris, menggunakan range
– SELECT * FROM emp WHERE sal in (SELECT sal FROM emp
WHERE deptno=20)
• Salah, karena operator perbandingan digunakan pada subquery yang
menghasilkan banyak baris
– SELECT * FROM emp WHERE sal=(SELECT sal FROM emp WHERE
deptno=20);
Inline View
• Digunakan untuk mengantikan list Tabel menjadi perintah
QUERY
• Untuk melakukan operasi yang tidak berasal dari tabel
aslinya
• Menyederhanakan perintah yang rumit
Contoh Inline View
• Membaca data pegawai dengan nomor departemen>20,
yang berasal dari suatu inline view
– SELECT * FROM (SELECT ename,deptno FROM emp) WHERE
depno>20
• Melakukan operasi join hasi dari suatu inline view
– SELECT e.*,[Link] FROM (SELECT ename,depno FROM emp
WHERE sal>1000) e,(SELECT deptno,dname FROM dept
WHERE deptno>20) d WHERE [Link]=[Link]
• Melakukan manipulasi data pada suatu inline view
– UPDATE (SELECT ename,sal FROM emp WHERE delpno=20)
SET sal=1000 WHERE sal=1100
Sedikit tentang SQL*Plus
• Subsitusi Variabel
– &variable, untuk tiap kali run selalu
memasukkan nilai
• SELECT * FROM emp WHERE sal>&gaji;
– &&variable, untuk hanya sekali saja
memasukkan nilai
• SELECT * FROM emp WHERE sal>&&gajih;
– Mendefinisikan nilai secara langsung
• define gajih=1500;
Memanipulasi Data
• DML - Data Manipulation Language
– Menambah baris data baru
– Mengubah isi suatu baris data
– Menghapus suatu baris data
• Transaksi adalah kumpulan dari perintah
DML yang membentuk suatu suatu kerja
tertentu
Contoh Operasi DML
• Operasi DML
– INSERT into dept (deptno,dname,loc) values
(1,’IT’,’SURABAYA’);
– UPDATE dept set loc=‘JAKARTA’ WHERE deptno=1;
– DELETE dept WHERE deptno=1;
Kontrol Transaksi
• Operasi Kontrol Transaksi
– COMMIT;
– ROLLBACK;
– SAVE POINT
• Kontrol Transaksi Implisit
– Auto COMMIT
• Melakukan operasi DDL dan DCL
• Keluar dari aplikasi SQL*PLUS secara normal
– Auto ROLLBACK
• Keluar dari SQL*PLUS secara tidak normal
• Kegagalan System
Membuat dan Mengatur Tabel
• CREATE
CREATE TABLE nama (
kolom tipe konstrain,
kolom tipe konstrain,
…,
CONSTRAINT nama_konstrain konstrain);
• tipe : NUMBER, CHAR, VARCHAR, DATE, …
• konstrain : NOT NULL, PRIMARY KEY,
UNIQUE, DEFAULT, …
• Perintah Lainnya :
– ALTER TABLE, DROP TABLE, TRUNCATE TABLE
Menambahkan Konstrain
• Kegunaan :
– Memberikan aturan pada tingkat table
– Membatasi Manipulasi tertentu pada Tabel
– Menjaga Integritas Data
– Menjaga Validitas Data
• Jenis
– NOT NULL
– UNIQUE
– PRIMARY KEY
– FOREIGN KEY
– CHEK
Konstrain
• NOT NULL
– Suatu kolom harus memiliki nilai tertentu (tidak boleh kosong)
• UNIQUE
– Suatu kolom harus tidak boleh memiliki nilai yang sama (tetapi boleh null)
– …, kolom tipe NOT NULL UNIQUE, …
– …, CONSTRAINT nama_konstrain UNIQUE (kolom), ...
• PRIMARY KEY
– Suatu kolom dinyatakan sebagai kunci utama dari suatu tabel
– Otomatis dianggap UNIQUE dan NOT NULL
– …, kolom tipe NOT NULL PRIMARY KEY, ...
– ..., CONSTRAINT nama_konstrain PRYMARY KEY(kolom), ...
• FOREIGN KEY
– Menyatakan suatu kolom harus sesuai dengan kolom lain dari suatu tabel
– …, kolom tipe REFERENCES tabel(kolom), …
– …, CONSTRAINT nama_konstrain FOREIGN KEY(kolom) REFERENCES
tabel(kolom), ...
• CHECK
– Melakukan pengujian pada suatu kolom
– ..., CONSTRAINT nama_konstrain CHECK kondisi, ...
Contoh Membuat Tabel
CREATE TABLE dosen(
nip varchar(15) not null primary key,
nama varchar(50) not null
);
CREATE TABLE siswa(
nrp varchar(15) not null primary key,
nama varchar(50) not null,
dosen_wali varchar(15) references dosen(nip)
);
CREATE INDEX nama_siswa ON siswa(nama);
Membuat VIEW
• Kegunaan:
– Membatasi Akses Data
– Menyederhanakan query
– Menghasilkan bentuk lain dari data yang sama
• Format :
– CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW nama
[(kolom_alias,…)] AS subquery [WITH CHECK OPTION
[CONSTRAINT konstrain]] [WITH READ ONLY
[CONSTRAINT konstrain]];
• Contoh :
– CREATE OR replace view pegawai as SELECT * FROM emp
e,dept d WHERE [Link]=[Link];
• Inline VIEW, view yang langsung dituliskan pada baris
perintah SQL tanpa perlu membuat object view
– SELECT * FROM (SELECT empno,ename,depno,sal FROM
emp) e, dept d WHERE [Link]=[Link];
Obyek Database Lainnya
• Table
• View
• Sequence
– CREATE sequence nomor;
– SELECT [Link] FROM dual;
• Index
– CREATE index nama on tabel(kolom);
• Synonym
– CREATE synonym nama for nama_object
Mengatur Akses User
• Membuat User
– CREATE USER nama IDENTIFIED BY password;
• Hak/Wewenang disebut Privileges
– System Privileges. Mengatur Hak dari USER
• CREATE, DROP, SELECT, …
• Contoh : GRANT CREATE table to dono;
– Object Privileges. Mengatur Hak USER pada Object
dari USER Lain
• alter, delete, execute, …
• Contoh : GRANT SELECT on emp to dono;
• Kumpulan Privileges dan Role disebut Role
– Contoh,
• GRANT connect TO dono;
• GRANT resource TO dono;
Menggunakan Operator SET
UNION
• Digunakan untuk menggabungkan hasil dari
lebih dari satu query
• Hasil dari setiap query harus memiliki
kolom yang sama
• UNION - tidak semua (yang kembar
dibuang)
• UNION ALL - semua
Contoh
• Menggabungkan semua hasil dari pegawai
yang bekerja pada departemen 20 dan
departemen 30. Hasilnya sama dengan
perintah : select * from emp where deptno
in(20,30) order by deptno
– SELECT * FROM emp WHERE depno=20
union all SELECT * FROM emp WHERE
deptno=30;