KUMPULAN PERINTAH QUERY SQL

KUMPULAN QUERY DALAM SQL

Kumpulan Perintah SQL 

Perjanjian

•ORACLE menganggap semua perintah adalahHURUF 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 ObjectDatabase

•SELECT

– DDL (Data Definition Language).
– Digunakan untuk menyatakansuatu 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 mengendalikanObject 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] ,…} FROMtabel 

•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 memberikanalias 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] ,…} FROMtable

[WHEREkondisi] [ORDER BYkolom[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 padadepartemen 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 1000sampai 2000 dollar 

 – SELECT * FROM emp WHERE sal BETWEEN 1000 AND 2000 ANDdeptno=40;

•Membaca data pegawai yang memiliki huruf kedua dari nama adalah ‘D’ dan bekerja pada departemen tertentu, dan urutkan berdasarkan nama denganurutan menurun

 – SELECT * FROM emp WHERE ename like ‘_D%’ AND deptno IS NOT NULLORDER 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 datauntuk 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 (melakukanrelasi antara satu tabel dengan tabel lainnya)

•Jenis Join

 – Equijoin –  Nonequijoin – outer join – self join

•Mulai 9i, memiliki jenis join yang kompatibeldengan 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 e.deptno=d.deptno ANDename=‘ADAMS’;

• Nonequijoin, untuk mencari/membandingkan nilai antara

 – SELECT * FROM emp e,salgrade s WHERE e.sal BETWEEN s.losalAND s.hisal AND ename=‘ADAMS’;

•outer join, relasi dengan tabel yang kemungkinan nilai datanya tidak ada

 – SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno(+);

•self join, relasi dengan diri sendiri

 – SELECT * FROM emp e,emp m WHERE e.mgr=m.empno ANDe.ename=‘ADAMS’;

Menampilkan Data MenggunakanFungsi

Group

•Mengoperasikan sekelompok baris data menjadi bentuk group data•Fungsi Group : AVG, COUNT, MAX, MIN,SUM, …

•Format :

 – SELECT [kolom,] fungsi_group(kolom),… FROMtabel [WHERE kondisi] [GROUP BY kolom][HAVING kondisi_group] [ORDER BY kolom];

Contoh Group

•Menghitung jumlah pegawai setiap departemen danmengurutkan hasilnya berdasar nomor departemen

 – SELECT deptno,count(empno) jumlah FROM emp GROUP BYdeptno 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 deptnoIN(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 kurungtutup 
– Subquery diletakkan pada sisi kanan dari suatu perbandingan
– Penggunaan order by dalam subquery tidak diperlukan, kecualiuntuk 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 WHEREename=‘ADAMS’);

•Hasil subquery harus satu baris, menggunakan perbandingan

 – SELECT * FROM emp WHERE sal=(SELECT sal FROM emp WHEREename=‘ADAMS’);

•Hasil subquery boleh banyak baris, menggunakan range

 – SELECT * FROM emp WHERE sal in (SELECT sal FROM empWHERE deptno=20)

•Salah, karena operator perbandingan digunakan pada subquery yangmenghasilkan banyak baris

 – SELECT * FROM emp WHERE sal=(SELECT sal FROM emp WHEREdeptno=20);

Inline View

•Digunakan untuk mengantikan list Tabel menjadi perintahQUERY

•Untuk melakukan operasi yang tidak berasal dari tabelaslinya

•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) WHEREdepno>20

•Melakukan operasi join hasi dari suatu inline view

 – SELECT e.*,d.dname FROM (SELECT ename,depno FROM empWHERE sal>1000) e,(SELECT deptno,dname FROM deptWHERE deptno>20) d WHERE e.deptno=d.deptno

•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 memasukkannilai

•SELECT * FROM emp WHERE sal>&gaji;

 –&&variable, untuk hanya sekali saja memasukkannilai

•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 perintahDML yang membentuk suatu suatu kerjatertentu

Contoh Operasi DML

•Operasi DML

– INSERT into dept (deptno,dname,loc) values(1,’IT’,’SURABAYA’); 
– UPDATE dept set loc=‘JAKARTA’ WHEREdeptno=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, ...

Obyek Database Lainnya

•Table
•View
•Sequence

 – CREATE sequence nomor;
 – SELECT nomor.nextval 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 SETUNION

•Digunakan untuk menggabungkan hasil darilebih dari satu query
•Hasil dari setiap query harus memilikikolom 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 * fromemp where deptno in(20,30) order by deptno

 – SELECT * FROM emp WHERE depno=20 union allSELECT * FROM emp WHERE deptno=30;