Panduan oracle lengkap

Menggunakan Subquery untuk Memanipulasi Data
Subquery adalah Query di dalam query.
Kegunaan-kegunaan Subquery dalam memanipulasi data:
Meng-copy data dari satu tabel ke tabel lain
Menerima data dari inline view
Mengambil data dari tabel lain untuk kemudian di update ke tabel yang dituju
Menghapus baris dari satu tabel berdasarkan baris dari tabel lain
meng-copy data dari satu tabel ke tabel lain
menggunakan Insert Statement
INSERT INTO penjualan(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE first_name LIKE '%an%'
nb: untuk membuat table dengan menyalin seluruh bentuk dan isi data pada table lain gunakan fungsi create seperti artikel DDL ini
Penjelasan :
Statement 1 : Masukkan data ke kolom id, name, salary, commission_pct pada tabel penjualan
Statement 2-4(subquery) : Data yang dimasukkan ke tabel penjualan yaitu data employee_id, last_name, salary, commission_pct dari tabel employees dimana first_name nya mengandung kata an
nb: asumsikan Tabel Penjualan telah dibuat sebelumnya dan schema yang digunakan yaitu schema HR
Hal yang perlu diperhatikan dalam menggunakan insert statement dalam mengcopy data yaitu :
Tidak menggunakan kata VALUES
Sesuaikan urutan kolom pada Insert Statement dan Subquery statement
Memasukkan data dengan menggunakan subquery sebagai tujuannya
INSERT INTO (SELECT employee_id, last_name, email, job_id, salary, department_id
FROM emp WHERE department_id = 50) VALUES (12345, 'Taylor', 'DTAYLOR', 'ST_CLERK', 5000, 50);
Penjelasan : Isikan data 12345 ke kolom employee_id, Taylor ke kolom last_name, DTAYLOR ke kolom email, ST_CLERK ke kolom job_id, 5000 ke kolom salary, 50 ke kolom department_id pada TAbel Emp
Menerima data dengan menggunakan SubQuery sebagai Source nya
SELECT last_name, salary, a.department_id, rataSalary
FROM employees a, (SELECT department_id, AVG(salary) rataSalary
FROM employees GROUP BY department_id) b
WHERE a.department_id = b.department_id AND salary > rataSalary;

Penjelasan :
kita ingin menampilkan last_name, salary, department_id dan rata-rata Salary per department pada tabel employees dimana salary yang diperoleh lebih besar dari salary rata-rata per department. hasilnya didapatkan dengan cara menggabungkan dua tabel yaitu tabel employees a dan tabel B. B itu sendiri merupakan nama alias dari tabel subquery tersebut.
Pada tabel employees A kita menghasilkan last_name, salary, department_id dan pada Tabel B kita menghasilkan department_id dan rataSalary. Lalu setelah kita mendapatkan hasil dari kedua tabel tersebut, lalu kita gabungkan kedua tabel tersebut dengan menggunakan department_id pada tabel employees dan department id yang ada pada tabel B sebagai penghubungnya.
Mungkin cara diatas memang terkesan ribet dan timbul pertanyaan dibenak kalian, mengapa kalau hanya ingin menampilkan last_name, salary, department_id, rataSalary tidak sesimpel dengan hanya menggunakan Group Function seperti yang telah dibahas sebelumnya
kira-kira query-nya seperti ini :
SELECT last_name, salary, department_id, AVG(Salary)
FROM employees GROUP BY last_name, salary, department_id havingsalary>AVG(Salary)

Bandingkan hasil query diatas, hasilnya jelas berbeda. Kenapa? karena pada query kedua menampilkan last_name, salary, department_id dan rata-rata salarynya. dimana rata-rata salary tersebut didapatkan dengan mengelompokkan Last_name dengan nama yang sama dan mempunyai salary yang sama lalu mempunyai department id yang sama. Sedangkan kita ingin menampilkan rata-rata salary per department dimana yang kita butuhkan hanya department idnya saja yang sama. Oleh karena query pertamalah yang benar.
Update 2 kolom dengan menggunakan subquery
UPDATE emp
SET job_id = (SELECT job_id FROM employees WHERE employee_id = 205),
salary = (SELECT salary FROM employees WHERE employee_id = 168 )
WHERE employee_id = 114
Penjelasan : memasukkan data-data baru ke kolom job_id dan salary yang mempunyi employee_id = 114
Menghapus baris berdasarkan nilai yang diperoleh dari baris pada tabel lain
DELETE FROM empl3
WHERE department_id = (SELECT department_id FROMdepartments WHERE department_name LIKE '%Public%')

Subqueries yang digunakan untuk menangani Query
Bagi yang belum tau apa itu subquery, silakan baca artike subquery 1 terlebih dahulu. Karena pada article ini kita akan membahasa Subqueries yang digunakan untuk menangani Query.
Sedikit review, pada saat kita mengexecutesuatu query dan di query tersebut terdapat subquery, maka subquery tersebutlah yang akan diexecute terlebih dahulu baru kemudian hasilnya digunakan untuk query utama.
contoh subquery :
SELECT last_name FROM employees
WHERE salary > ( SELECT salary FROM employees WHERE last_name = 'Abel' )
Penjelasan :
Statement ( SELECT salary FROM employees WHERE last_name = 'Abel' ) akan mengembalikan nilai 11000. Sehingga sama seperti ini
SELECT last_name FROM employees
WHERE salary >11000
Petunjuk dalam Menggunakan Subquery
Tulis subquery diantara ( ) –> (subquery)
Tempatkan subquery di sebelah kanan pembanding. contohnya seperti query diatas
Gunakan subquery yang menghasilkan satu nilai jika pembandingnya hanya membutuhkan satu nilai sebagai pembanding dan gunakan subquery yang menghasilkan banyak nilai jika pembandingnya membutuhkan banyak nilai sebagai pembanding.
contoh subquery returns single row : ( SELECT salary FROM employees WHERElast_name = 'Abel' )
contoh returns multirow : (SELECT salary FROM employees WHERE Department_id = 20 )// 13000,6000
penjelasan : subquery ke dua mengembalikan banyak nilai karena Salary di tiap Department berbeda-beda yaitu 13000 dan 6000 sedangkan subquery pertama hanya menunjuk salary yang dipunyai oleh Abel yaitu 11000.
Comparison yang digunakan oleh Single row subquery :

Contoh-contoh single row subquery :
SELECT last_name, job_id, salary FROM employees
WHERE job_id = (SELECT job_id FROM employees WHEREemployee_id = 141)
AND salary > (SELECT salary FROM employees WHERE employee_id = 143);
penjelasan :
Subquery 1 : akan mengembalikan ST_CLERK
Subquery 2 : akan mengembalikan 2600
Contoh subquery dengan group function
SELECT last_name, job_id, salary FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees)
penjelasan : Subquerynya akan mengembalikan nilai 2500
Contoh lain :
SELECT department_id, MIN(salary) FROM employees
GROUP BY department_id
HAVING MIN(salary) >(SELECT MIN(salary) FROM employees WHEREdepartment_id = 50)
Penjelasan :
Subquery nya akan mengembalikan nilai 2500. dan Secara keseluruhan querynya akan dibaca seperti ini: tampilkan department_id dan salary terkecil dari tiap department dimana salary terkecil dari tiap department tersebut lebih besar dari salary terkecil yang dimiliki department_id = 50
MULTIPLE ROW SUBQUERY
Mengembalikan Banyak nilai
Operator Pembandingnya :

Fungsi Opertor tersebut :
salary IN (1000,2000,4000), akan menampilkan salary yang nilainya sama dengan salah satu kondisi tersebut
salary > ANY (1000,2000,4000) ,akan menampilkan salary > 1000 dan menampilkan salary > 2000 serta akan menampilkan salary >4000 (bila salah satu nilai terpenuhi maka akan ditampilkan)
salary > ALL (1000,2000,4000), hanya akan menampilkan salary yang lebih besar dari 4000 saja
Menggunakan ANY operator
SELECT employee_id, last_name, job_id, salary FROM employees
WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG'
Penjelasan :
Subquery nya akan menghasilkan nilai 9000, 6000, dan 4200. Dan akan sama seperti berikut:
WHERE salary < ANY (9000, 6000,4200)
Menggunakan ALL operator
WHERE salary < ALL(9000, 6000,4200)
Menggunakan IN Operator
SELECT Employee_id, department_id from employees wheredepartment_id in (20,40)

Operasi DDL(Data Definition Language) lesson 1

DDL adalah bahasa yang digunakan untuk memanage atau mendefinisikan suatu tabel .
Statement-statement nya adalah:
CREATE
ALTER
RENAME
DROP
TRUNCATE
Pada artikel kali ini, kita akan membahas tentang cara pembuatan Tabel dengan menggunakan CREATE
Dalam pembuatan nama dan kolom tabel terdapat persyaratan yang harus dipenuhi yaitu:
Harus diawali dengan abjad
panjang namanya harus 1-30 karakter
Hanya bisa memakai A-Z, a-z, 0-9, _ , $, dan #
Nama Tabel dan Nama Kolomnya tidak boleh sama dengan nama dan kolom pada tabel lain, dalam user yang sama
Tidak boleh menggunakan nama yang telah di reserved oleh Oracle Server
seperti : FROM, INSERT, WHERE dan lain-lain
Syntax-nya adalah sbb :
CREATE TABLE [nama_tabel](
[nama_kolom] [type_kolom][ukuran kolom]
)
–Membuat tabel MsEmployee
CREATE TABLE MsEmployee
(
EmployeeID CHAR(5) ,
EmployeeName VARCHAR(20),
Email VARCHAR2(20),
Address VARCHAR2(30),
Salary NUMBER(10,2)
)
–Membuat tabel TrHeaderTransaction
CREATE TABLE TrHeaderTransaction
(
TransactionID CHAR(5),
CustomerID CHAR(5),
TransactionDate DATE
)
Perlu diketahui, tabel diatas belom saling berhubungan. Kita dapat menghubungkan kedua tabel tersebut dengan menggunakan Constraint.
Kegunaan CONSTRAINT :
Membuat rules pada tabel
Menghindari proses pen-delete-an pada tabel jika tabel tersebut memiliki ketergantungan
Type Constraint yang valid adalah sbb:
PRIMARY KEY
FOREIGN KEY
NOT NULL
UNIQUE
CHECKED
Pedoman dalam menggunakan Constraint :
Kita bisa memberikan nama constraint tersebut secara manual atau dapat di-generates oleh Oracle Server dengan menggunakan perintah SYS_cn
Pembuatan Constraint dapat dilakukan bersamaan pada saat kita membuat tabel atau setelah tabel selesai dibuat dengan menggunakan perintah Alter Tabel
Pendefinisian constraint dapat dilakukan pada level kolom atau pada level tabel
Syntax Constraint adalah sbb:
CONSTRAINT [nama_constraint] [TYPE_CONSTRAINT]
–Pembuatan tabel MsEmployee dengan menggunakan constraint pada level kolom
CREATE TABLE MsEmployee
(
EmployeeID CHAR(5) PRIMARY KEY NOT NULL,
CONSTRAINT cs002 CHECK(LENGTH(EmployeeID) = 5) ,
EmployeeName VARCHAR2(20) NOT NULL,
Email VARCHAR2(20) UNIQUE,
Address VARCHAR2(30) NOT NULL,
Salary NUMBER(10,2) NOT NULL
)
–Pembuatan tabel TrHeaderTransactiondengan menggunakan constraint pada level tabel
CREATE TABLE TrHeaderTransaction
(
TransactionID CHAR(5) NOT NULL,
EmployeeID CHAR(5) NOT NULL,
TransactionDate DATE NOT NULL,
CONSTRAINT aab PRIMARY KEY (TransactionID),
CONSTRAINT cs005 CHECK(LENGTH(TransactionID) = 5),
CONSTRAINT ngehubungin FOREIGN KEY (EmployeeID) REFERENCESMsEmployee(EmployeeID) ON DELETE CASCADE
)
Penjelasan :
CONSTRAINT NOT NULL digunakan untuk memastikan kolom tersebut diisi sehingga tidak bernilai kosong
CONSTRAINT CHECKED digunakan untuk mengecek apakah data tersebut telah sesuai dengan aturan yang telah ditetapkan. pada contoh diatas panjang TransactionID haruslah 5 karakter tidak boleh kurang atau lebih
CONSTRAINT UNIQUE digunakan untuk memastikan tidak ada pengisian nilai atau nama yang sama pada data yang lain sehingga mencegah duplikasi data
" CONSTRAINT ngehubungin FOREIGN KEY (EmployeeID) REFERENCESMsEmployee(EmployeeID)" digunakan untuk menghubungkan tabel MsEmployee dengan tabel TrHeaderTransaction dengan menggunakan EmployeeID sebagai penghubungnya dimana EmployeeID tersebut merupakan PRYMARY KEY dari tabel MsEmployee. Dimana Tabel MsEmployee sebagai parent dan TrHeaderTransaction sebagai child
CONSTRAINT ON DELETE CASCADE digunakan untuk memastikan jika terjadi pen-delete-an pada baris tabel parent, baris child yang mereferensikan nilai dari data tersebut juga ikut terhapus tetapi tidak berlaku sebaliknya.

Pada Oracle kita juga dapat menggunakan Syntax DEFAULT sebagai nilai awal data
contoh :
CREATE TABEL EMPLOYEES2 (
Hire_Date DATE DEFAULT SYSDATE /* kolom hire_date diisi dengan tanggal Sekarang tanpa melakukan proses INSERT */
)
Mengcopy atau menyalin tabel
Selain dengan cara membuat tabel diatas kita juga dapat membuat tabel yang yang isi dan tipe datanya sama dengan tabel lain
contoh :
CREATE MsEmp2
AS
SELECT * FROM MsEmployees
kita juga dapat menentukan kolom mana yang mau disalin
CREATE MsEmp2
AS
SELECT EmloyeeId, EmployeeName FROM MsEmployees
query diatas digunakan untuk menyalin tabel beserta dengan isi-isinya, jika kita ingin menyalin tabel hanya kolomnya saja tanpa isi datanya yaitu dengan menambah WHERE1=2
contoh :
CREATE MsEmp2
AS
SELECT * FROM MsEmployees WHERE 1=2
Operasi DDL(Data Definition Language) lesson 2

Pada lesson 1 kita membahas tentang bagaimana caranya membuat tabel, nah pada artikel kali ini kita akan ngebahas tentang bagaimana caranya mengotak-atik kolom pada tabel yang kita buat. Nah, untuk mengutak-atik kolom tersebut kita menggunakan statementALTER TABLE.
Berikut ini adalah kegunaan dari ALTER TABLE:
Membuat sebuah kolom baru pada tabel
Memodifikasi kolom yang telah dibuat
Menghapus kolom
Memberi nilai DEFAULT pada kolom baru
Membuat Kolom baru pada tabel
Syntax-nya :
ALTER TABLE
add
contoh : membuat kolom baru dengan nama 'MulaiKerja'
ALTER TABLE MsEmployee
add MulaiKerja date
Memodifikasi Kolom yang telah dibuat sebelumnya
Syntax-nya :
ALTER TABLE
modify
contoh : memodifikasi size kolom EmployeeName menjadi 150
modify EmployeeName varchar(150)
Perlu diingat : Memodifikasi kolom hanya sebatas dapat merubah type, size dan menentukan nilai DEFAULT
Menghapus Kolom
Syntax-nya :
ALTER TABLE
DROP
contoh : menghapus kolom EmployeeName
DROP EmployeeName
Selain itu, ALTER TABLE juga dapat digunakan untuk:
Membuat atau Menghapus CONSTRAINT, tetapi tidak dapat memodifikasi strukturnya
ENABLE atau DISABLE CONSTRAINT
Membuat CONSTRAINT NOT NULL dengan menggunakan statement MODIFY
Membuat CONSTRAINT
Syntax-nya :
ALTER TABLE
ADD CONSTRAINT
contoh : membuat EmployeeName menjadi PRIMARY KEY
MODIFY Employee_Name PRYMARY KEY
Menghapus CONSTRAINT
ALTER TABLE
DROP
DROP TABLE
Drop Table, merupakan suatu syntax dalam sql yang digunakan untuk menghapus tabel beserta dengan isinya.
Statement-nya sebagai berikut :
DROP TABLE nama_table
TRUNCATE
TRUNCATE merupaka fungsi yang digunakan untuk menghapus data dari tabel tanpa menghapus tabel itu sendiri, jadi yang dihapus adalah data dari si tabel tersebut
TRUNCATE TABLE nama_table
RENAME
Rename dapat digunakan untuk merubah nama tabel pada sebuah database.
Pada oracle kita menggunakan syntax:
RENAME old_table TO new_table
contoh :
RENAME employees TO employee
sedangkan pada sql server, syntaxnya :
SP_RENAME old_table, new_table
contoh :
SP_RENAME MsEmployee TO Employee
Aggregated Data dengan Menggunakan Group Function

Apa itu Aggregated Data dan apa hubungannya dengan Group Function??
Aggregated Data adalah suatu fungsi yang digunakan untuk menghasilkan satu nilai dari beberapa atau sejumlah baris dan dipengaruhi atau ditentukan oleh Group Function(fungsi yang digunakan untuk mengelompokkan data). Jadi intinya, beberapa data dari beberapa baris kita ambil, lalu kita kelompokkan dan akan menghasilkan satu nilai.
Beberapa Tipe-tipe dari Group Function:
MAX (untuk mencari nilai terbesar dalam Group Function)
MIN (untuk mencari nilai terkecil dalam Group Function)
AVG (untuk mencari nilai rata-rata dalam Group Function)
COUNT (untuk menjumlahkan baris dalam Group Function)
SUM (untuk mencari nilai total dalam Group Function)
Contoh :
struktur syntax :
select column, group_function
from table
where condition]
Group By group_by_expression]
Having group_condition]
Order By column];
nb: Schema yang digunakan yaitu Schema HR yang terdapat pada oracle
select avg(salary), max(salary), min(salary), sum(salary)
from employees
where job_id like '%REP%'

select count(*)
from employees
where department_id = 40

select count(employee_id)
from employees
where department_id = 40

Penjelasan :
Menampilkan rata-rata salary, nilai terbesari salary, nilai terkecil salary, total salary dari tabel employees dimana job id nya mengandung kata REP
Menampilkan berapa banyak jumlah baris dari tabel employees yang mempunyai department_id = 40
Menampilkan berapa banyak jumlah employee id (dihitung berdasarkan baris)dari tabel employees dimana department id nya = 40
Menggunakan Group By Clause
Perhatikan contoh berikut :
select department_id, avg(salary)
from employees
group by department_id ;

Pada contoh diatas kita menambahkan suatu fungsi yaitu fungsi Group By yang digunakan untuk mengelompokkan rata-rata salary berdasarkan department_id. jadi begini kira-kira terjemahan contoh diatas :
Tampilkan department id beserta dengan rata-rata salary dari tabel employees dimana rata-rata salarynya dikelompokkan berdasar department_id. jadi intinya , kita mencari nilai rata-rata salary dari tiap department
Menggunakan Group By Clause dengan multiple columns(banyak kolom/lebih dari satu kolom)
select department_id as dept_id, job_id, AVG(salary)
from employees
group by department_id, job_id

pada contoh diatas, kita akan menampilkan dept_id , job_id beserta dengan rata-rata salary dimana rata-rata salarynya didapatkan dengan mengelompokkan department_id terlebih dahulu lalu setelah itu dikelompokkan berdasarkan job_id. Intinya, kita ingin mencari rata-rata salary dari tiap job_id dimana job_id tersebut terdapat dalam department_id yang sama .
Menggunakan Having Clause
select department_id, max(salary)
from employees
group by department_id
having max(salary)>10000

Contoh diatas akan menampilan salary terbesar pada tiap department_id dimana rata-rata salary nya lebih besar dari 110000.
Perlu diperhatikan bahwa MAX(salary)>10000 harus diletakkan pada fungsi HAVING dantidak bisa diletakkan pada fungsi WHERE karena MAX(salary) merupakan suatu Group Function

Restricting Data

Sebelumnya telah kita bahas tentang apa ituOperasi DDL, pada Pembahasan Kali ini kita akan mempelajari bagaimana caranya menggunakan Operasi DML dalam oracle yaitu Restricting Data atau membatasi data. Tetapi sebelum itu, mari kita lihat Relational Diagram dari Schema HR yang terdapat pada Oracleyang akan kita gunakan nanti.

Menggunakan klausa WHERE
Klausa WHERE digunakan untuk memberikan syarat atau kondisi data yang bagaimana yang akan diperoleh
SELECT * FROM Employees WHERE last_name = 'Kochhar'

nb: Jika nilai nilai kondisinya berupa String atau DATE maka gunakan single quote( ' ' )
Menggunakan BETWEEN Condition
Gunakan BETWEEN Condition untuk menampilkan baris data dengan rentang nilai tertentu
SELECT Employee_id, first_name FROM Employees WHERE salaryBETWEEN 2000 AND 2300

Menggunakan LIKE Condition
Biasa digunakan untuk mencari nilai berdasarkan kesamaan pola dari tiap karakter dan biasanya berupa STRING.
Terdapat 2 simbol yang digunakan untuk merepresentasikan pencarian data berupa string, yaitu:
% –> Menampilkan 0 (nol )atau lebih karakter
_ –> Menampilkan single karakter
SELECT first_name FROM Employees WHERE last_name like 'Kochhar'

Menggunakan AND, OR Condition
SELECT Employee_id, first_name FROM Employees WHERE last_name = 'Kochhar' AND salary <2100

SELECT Employee_id, first_name FROM Employees WHERE last_name = 'Kochhar' OR salary <2100

Menggunakan IN Condition
SELECT employee_id, first_name FROM Employees WHEREmanager_id IN (100,101,201)

Menggunakan NULL Condition
Digunakan untuk menampilkan data yang bernilai kosong
SELECT employee_id, manager_id FROM Employees WHEREmanager_id is NULL

nb : Pada Oracle menggunakan 'is' sedangkan sql server menggunakan '='
Untuk lebih memahami Segala kondisi-kondisi di atas, mari kita lihat contoh query-query berikut :
SELECT employee_id, first_name,last_name FROM EmployeesWHERElast_name = 'Kochhar'

Penjelasan : Tampilkan *(semua data) dari tabel Employees dimana last_namenya ADALAH Kochhar
SELECT last_name FROM Employees WHERE last_name like '_a%' andfirst_name NOT LIKE '%ar%'

Penjelasan : Tampilkan last_name DARI tabel Employees DIMANA last_namenya diawali oleh satu karakter, sebelum karakter 'a' dan first_name nya tidak mengandung kata 'ar'
SELECT Employee_id, First_name, last_name FROM Employees
WHERE Department_id = 60 and first_name like '%r' and last_namelike '%ol%' and salary > 5000

Penjelasan : Tampilkan Employee_id, First_name, last_name dari tabel Employees dimana department_id nya ADALAH 60 DAN first_name nya diakhiri dengan huruf ' r' DAN last_name nya mengandung kata 'ol' DAN salary nya LEBIH BESAR dari 5000
SELECT * FROM Employees WHERE salary BETWEEN 2000 AND 2300

Penjelasan : Tampilkan semua data DARI tabel Employees DIMANA salary nya ANTARA 2000 DAN 2300
SELECT employee_id, first_name FROM Employees WHEREmanager_id IN (100,101,201)

Penjelasan : Tampilkan Employee_id, First_name DARI tabel Employees DIMANA manager_id nya adalah 100 ATAU Manager_id nya adalah 101 ATAU Manager_id nya adalah 201
SELECT employee_id, manager_id FROM Employees WHEREmanager_id is NULL

Penjelasan : Tampilkan employee_id, manager_id DARI tabel Employees Dimana manager_id nya itu Kosong

Membuat View pada Database Oracle

VIEW adalah sebuah logical tabel yang mencerminkan tabel lain
Keuntungan VIEW
Membatasi akses data
Menyediakan data yang independen
Menampilkan view yang berbeda-beda dengan data yang sama
Memudahkan query yang kompleks
Membuat VIEW
Syntax :
CREATE [OR REPLACE] [FORCE"NOFORCE] VIEW view
[(alias[, alias]…)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint_name]]
[WITH READ ONLY [CONSTRAINT constraint]_name];
contoh query :
CREATE VIEW view_emp
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;
untuk menampilkan view yang kita buat:
SELECT * FROM nama_view
Query diatas akan membuat sebuah view yang bernama view_emp yang menampilkan data dari tabel employees yang bekerja pada department_id = 80.
View dengan alias :
CREATE VIEW view_emp2
AS SELECT employee_id ID_NUMBER, last_name NAME,
salary*12 ANN_SALARY
FROM employees

Query diatas juga dapat ditulis seperti ini:
CREATE VIEW view_emp2(ID_NUMBER, NAME, ANN_SALARY)
FROM employees

Memodifikasi VIEW dengan CREATE OR REPLACE :
CREATE OR REPLACE VIEW view_emp2(ID_NUMBER, NAME, ANN_SALARY)
FROM employees
Dengan menggunakan CREATE OR REPLACE, bila sudah ada view dengan nama yang sama dengan view yang akan kita buat, maka view terdahulu tersebut akan ditimpa dengan view yang baru.
nb : Dengan query-query diatas, jika kita melakukan perubahan pada view_emp atau view_emp2, maka tabel employees juga akan ikut berubah sesuai dengan perubahan yang terjadi pada view_emp. begitu juga sebaliknya jika kita melakukan perbuahan pada tabel employees. Hal ini dapat dicegah dengan menggunakan WITH CHECK OPTION dan READ ONLY
Membuat VIEW yang Kompleks
maksud dari view yang kompleks adalah kita menambahkan fungsi-fungsi lain dalam subquery seperti JOIN, GROUP FUNCTION, SET OPERATOR dll.
contoh :
CREATE OR REPLACE VIEW dept_Sal(name, MinSal, MaxSal, avgSal)
AS
SELECT d.department_name, MIN(e.salary),
MAX(e.salary),AVG(e.salary)
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
GROUP BY d.department_name
Aturan dalam VIEW :
Kita tidak bisa menggunakan operasi DML melalui VIEW jika didalam VIEW tersebut terdapat :
GROUP FUNCTION
GROUP BY
DISTINCT
ROWNUM
Kolomnya didefinisikan dengan ekspresi(salary + 2)
NOT NULL kolom pada base table(tabel yang disebutkan setelah kata AS ) yang tidak dipilih oleh view
WITH CHECK OPTION
dengan klausa WITH CHECK OPTION, kita dapat membatasi perubahan yang diakibatkan oleh penggunaan operasi DML. sehinga jika adanya perubahan pada view, tidak mempengaruhi pada base tabelnya
contoh :
CREATE OR REPLACE VIEW view_emp2
AS
SELECT *FROM employees
WHERE department_id = 20
WITH CHECK OPTION CONSTRAINT viewEmp2_ck ;
Dengan query diatas kita tidak dapat merubah department_id yang sudah ditentukan pada VIEW view_emp2 tetapi masih dapat melakukan perubahan terhadap data lain dan juga jika kita melakukan operasi DML pada VIEW view_emp2, data pada tabel EMPLOYEES tidak akan berubah
READ ONLY
Dengan READ ONLY kita tidak dapat melakukan operasi DML terhadap VIEW yang kita buat, sehingga view tersebut hanya dapat dilihat atau dibaca
CREATE OR REPLACE VIEW view_emp2
AS
SELECT *FROM employees
WHERE department_id = 20
READ ONLY;
Menghapus VIEW
DROP VIEW nama_view
Mengatur Hak Akses

Pemberian Hak akses merupakan salah satu cara dalam mengatur tingkat keamanan dalam database. Database Security sendiri dikategorikan berdasarkan 2 jenis yaitu : System Security dan Data Security
System Security melindungi dan mengatur hak akses dan penggunaan database pada level sistem, seperti username, password, alokasidisc space yang diberikan pada user dan pengoperasian sistem yang dapat dioperasikan oleh user.
Data Security yaitu memastikan tidak adanya data yang corrupt dan terkontrolnya pengaksesan terhadap data tersebut.
System Privilege
System Privilege ini digunakan oleh database administrator untuk memberikan hak istimewa kepada user dalam menjalankan sistem.
Tipe-tipe hak istimewa DBA atau DBA Privileges :
CREATE USER : penerima privilege dapat membuat user database baru
DROP USER : penerima privilege dapat menghapus user
DROP ANY TABLE : penerima privilege dapat menghapus tabel apa saja dalam suatu schema
BACK UP ANY TABLE : penerima privilege dapat mem-back up tabel apa saja
SELECT ANY TABLE : penerima privilege dapat membuat query, tabel dan view dalam suatu schema
CREATE ANY TABLE : penerima privilege dapat membuat tabel pada schema apapun
MEMBUAT USER
Sebelum memberikan privilege, hal pertama yang harus kita lakukan yaitu membuat user baru terlebih dahulu yang diberi nama 'manager'.
Syntax :
CREATE USER user
IDENTIFIED BY password
contoh :
CREATE USER manager
IDENTIFIED BY 123
Pada tahap ini manager belum dapat melakukan apapun, karena manager hanya dibuat tanpa diberikan suatu privilege oleh database administrator.
HAK ISTIMEWA USER
Setelah user dibuat, maka database administrator dapat memberikanya system privilege
contoh System privilege yang dapat diberikan :
CREATE SESSION : untuk connect ke database
CREATE TABLE : untuk membuat tabel
CREATE SEQUENCE : membuat urutan dalam database schema
CREATE VIEW : membuat sebuah VIEW dalam suatu schema
CREATE PROCEDURE : membuat STORE PROCEDURE, FUNCTION atauPACKAGE dalam user's schema
Memberikan System Privilege Kepada User
Contoh :
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE PROCEDURE
TO manager
Pada query diatas DBA memberikan 5 hak akses sekaligus yaitu create session, create table, create sequence, create view, dan create procedure
Memberikan Tablespace kepada USER
setelah kita memberikan sebuah privileges kepada user, kita harus memberikan sebuah quota agar user tersebut dapat melakukan aktifitasnya seperti membuat schema dll. Jika kita tidak memberikan quota, maka ketika user tersebut melakukan suatu pekerjaan membuat table contohnya maka akan terjadi pesan error sbb:
create table m (ad number) *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
Syntax memberikan quota yaitu :
GRANT size TABLESPACE TO user_name;
contoh :
GRANT UNLIMITED TABLESPACE TO Fern;
Membuat ROLE
Apa itu role ?? Role merupakan nama group dari beberapa privilege yang saling berhubungan yang dapat diberikan kepada user. Metode ini digunakan untuk memudahkan dalam mencabut dan mengontrol privilege yang diberikan.
User dapat mempunyai beberap role dan user yang berbeda dapat mempunyai role yang sama.
Syntax :
CREATE ROLE nama_roll
Membuat role :
CREATE ROLE manager
Memberikan Privilege kepada ROLE
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE PROCEDURE
TO manager
Memberika Sebuah ROLE kepada USER
GRANT manager TO Fern, Beifern
Pada query diatas, Fern dan Beifern telah mendapat suatu roll manager yang berisikan hak akses berupa create session, create table, create sequence, create view, dan create procedure. dengan catatan Fern dan Beifern telah dibuat sebelumnya.
Selaint ROLE diatas, sudah ada role yang telah didefinisikan terlebih dahulu oleh Oracle yaitu:
CONNECT
Mencangkup System Privileges : ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM,CREATE TABLE, CREATE VIEW
RESOURCES
Mencangkup System privileges : CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE
DBA
Mencangkup semua System Privileges
Contoh memberikan privileges DBA :
GRANT DBA TO Fern
Merubah PASSWORD
Selain membuat account user, DBA juga dapat merubah password dari user tersebut
contoh :
ALTER USER nama_user
IDENTIFIED BY password_baru
OBJECT PRIVILEGES
Apa itu Object Privilege? Object Privilege adalah sebuah privilege yang diberikan kepada user agar user tersebut dapat melakukan aksi tertentu seperti SELECT, UPDATE, dll kepada tabel, view, sequence, atau store procedure.
Perhatikan tabel dibawah ini :

Berdasarkan table diatas, ALTER hanya dapat memanipulasi TABLE dan SEQUENCE. SELECT hanya dapat memanipulasi TABLE, VIEW , dan SEQUENCE.
Syntax :
GRANT tipe_object_privilege(s)
ON OBJECT(nama_tabel/nama_view/nama_sequence/nama_procedure)
TO nama_user
Contoh Grant SELECT :
GRANT SELECT
ON employees
TO Fern
Contoh Grant UPDATE :
GRANT UPDATE(employee_id, job_id)
ON employees
TO Fern, Beifern
Contoh Grant SELECT yang diberikan kepada semua user :
GRANT SELECT
ON employees
TO PUBLIC
Karena tabel employees terdapat pada SCHEMA HR, maka Fern harus menggunakan SELECT statement dengan cara :
SELECT * FROM HR.employees
atau dapat juga menggunakan SYNONIM
contoh :
CREATE SYNONIM emp FOR HR.employees;
SELECT * FROM emp;
Guideline:
untuk memberikan sebuah object privilegE, object tersebut harus terdapat pada schema yang kita miliki.
Sang pemilik object dapat memberikan object privilege yang ia miliki kepada user lain
Sang pemilik object otomatis memiliki semua object privileges dari object tersebut
WITH GRANT OPTION
Mungkin timbul pertanyaan dibenak kalian, bisa ga sih user yang kita buat tadi seperti Fern dan Beifern dapat meneruskan hak aksesnya kepada user lain?? jawabanya BISA. yaitu dengan menggunakan statement WITH GRANT OPTION
contoh :
GRANT SELECT, INSERT
ON employees
TO Fern
WITH GRANT OPTION
Dengan query diatas, Fern dapat meneruskan hak akses yang dia miliki kepada user lain
MENCABUT HAK AKSES YANG DIBERIKAN
Setelah kita memberikan hak akses kepada user lain, kita juga dapat mencabut privilege yang kita berikan yaitu dengan menggunakan REVOKE statement
Syntax :
REVOKE jenis_privilege " all
ON object
FROM user
[CASCADE CONSTRAINT]
contoh :
REVOKE SELECT
ON EMPLOYEES
FROM Fern
Conditional Expressions
Pada oracle database, disediakan sebuah fungsi yang menyerupai dengan logika IF-THEN-ELSEyang dapat digunakan pada SQL statement yang diberi nama dengan Conditional Expression
Conditional Expressions terbagi dua:
CASE expressions
DECODE expression

1. CASE Expressions
syntax :
CASE expr1 WHEN expr_pembanding 1 THEN RETURN_expr1
WHEN expr_pembanding 2 THEN RETURN_expr2
WHEN expr_pembanding N THEN RETURN_exprN
ELSE else_expr
END
contoh :
Ubah salary karyawan yang bekerja pada department id =20 menjadi 2 kali gaji, yang bekerja pada department id=30 menjadi 3 kali gaji dan karyawan yang bekerja pada department sisanya tidak mendapat kenaikan gaji. serta tampilkan first_name, beserta salary terbarunya
SELECT first_name, salary,
CASE department_id WHEN 20 THEN salary*2
WHEN 30 THEN salary*3
ELSE salary END NewSalary
FROM employees
2. DECODE Expression
Ekspresi DECODE sama fungsinya dengan Ekspresi Case yang membedakan hanya cara penulisannya saja
syntax :

contoh :
SELECT first_name, salary,
DECODE ( department_id, 20, salary*2,
30, salary*3,
salary
) NewSalary
FROM employees

pada decode, ekspresi pembandingnya hanya cukup ditulis satu kali
Insert
Insert digunakan untuk memasukkan data ke dalam tabel. Penggunaan insert pun bermacam-macam. Untuk lebih jelasnya langsung saja kita mulai.
contoh 1 :
nb: jika valuenya bukan bertipe date atau char/string maka jangan gunakan single quote
INSERT INTO employeesVALUES ('value1 ,value2,'value3 , 'value4 )
Penting : pada query diatas karena field employeesnya tidak di sebutkan, maka pada pengisian values harus mencakup seluruh field yang ada pada tabel employees serta urutan value nya juga harus sesuai dengan urutan yang ada pada tabel employees. Selain itu jumlah Values yang diberikan tidak boleh lebih atau kurang dari kolom yang ada pada tabel employees!!
contoh 2 :
INSERT INTO nama_tabel (kolom1, kolom3, kolom2, kolom4) VALUES('value1 ,value3, 'value2 ,' value4 )
Pada contoh selanjutnya kita akan menggunakan fungsi subquery
contoh 3 :
Syntax nya : INSERT INTO nama_tabel(kolom1. kolom2, kolom3) [Subquery]
INSERT INTO penjualan(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE first_name LIKE '%an%'
Penjelasan untuk Query diatas
contoh 4 :
Syntax nya : INSERT INTO Subquery VALUES (value1, 'value2 , 'value3 …)
INSERT INTO (SELECT employee_id, last_name, email, job_id, salary, department_id
FROM emp WHERE department_id = 50) VALUES (12345, 'Taylor', 'DTAYLOR', 'ST_CLERK', 5000, 50);
Penjelasan untuk query diatas>
contoh 5 :
Syntax : INSERT INTO nama_tabel VALUES (&VARIABEL)
Pada Oracle disediakan suatu fungsi yang dapat digunakan untuk memberikan hak kepada user untuk menentukan sendiri nilai yang ingin diperoleh yang disimbolkan dengan ' & '
INSERT INTO employees VALUES (&var1, '&var2 , '&var3 , …)
Query diatas hampir sama dengan contoh 1 tetapi bedanya ketika query diatas dijalankan, maka system akan meminta user untuk memasukkan nilai yang dibutuhkan.
Selain Itu kita juga dapat memasukkan data ke tabel-tabel yang berbeda atau disebutMultitable Insert Statement
Tipe-tipe Multitable Insert Statement :
Unconditional INSERT
Conditional ALL INSERT
Conditional FIRST INSERT
Pivoting INSERT
Namun disini kita akan membahas Unconditional dan Conditional INSERT saja.
Contoh 6 Unconditional INSERT :
INSERT ALL
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR FROM employees
WHERE employee_id > 200;
Penjelasan :
Tampilkan value dari empid, hiredate, sal dan mgr dari tabel employees dimana employee_id nya lebih dari 200. Kemudian masukkan nilai empid, hiredate, sal tersebut ke dalam tabel sal_history setelah itu masukkan juga nilai empid, mgr, sal ke dalam tabelmgr_history. clue : bacanya dari statement select
Contoh 7 Conditional ALL INSERT:
INSERT ALL
WHEN SAL > 10000 THEN
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
WHEN MGR > 200 THEN
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id as EMPID,hire_date as HIREDATE, salary as SAL, manager_id MGR FROM employees
WHERE employee_id > 200;
Penjelasan :
Tampilkan nilai dari EMPID, HIREDATE, SAL, MGR dari tabel employees dimanaemployee_idnya lebih besar dari 200. Jika nilai sal > 10000 maka masukkan nilai EMPID, HIREDATE, SAL ke dalam tabel sal_history. jika mgr > 200 maka masukkan nilai EMPID,MGR,SAL ke dalam tabel mgr_history.
Contoh 8 Conditional FIRST INSERT:
INSERT FIRST
WHEN SAL > 25000 THEN
INTO special_sal VALUES(DEPTID, SAL)
WHEN HIREDATE like ('%00%') THEN
INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
WHEN HIREDATE like ('%99%') THEN
INTO hiredate_history_99 VALUES(DEPTID, HIREDATE)
ELSE
INTO hiredate_history VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM(salary) SAL, MAX(hire_date) HIREDATE FROM employees
GROUP BY department_id;
Penjelasan :
Tampilkan nilai dari deptid, sal, hiredate dari tebel employees. Jika sal>25000 maka masukkan nilai DEPTID, SAL ke dalam tabel special_sal. Jika hiredate mengandung kata oo maka masukkan nilai depid, hiredate ke dalam tabel hiredate_history_00. Jikahiredate mengandung 99 maka masukkan nilai DEPTID, HIREDATE ke dalam tabel hiredate_history_99. Selain kondisi diatas yang telah disebutkan sebelumnya maka masukkan nilai DEPTID, HIREDATE ke dalam tabel hiredate_history.
Selain Query diatas insert all dan insert first juga bisa tidak ditentukan VALUES nya pada saat pengkondisian
contoh :
INSERT FIRST
WHEN department_id = 10 THEN INTO emp01
WHEN department_id = 20 THEN INTO emp02
ELSE INTO EMP03
SELECT * FROM EMPLOYEES ;
pada query diatas, jika kondisinya terpenuhi maka nilai yang ada pada semua kolom di tabelemployees akan dimasukkan ke dalam kolom tabel yang kondisinya terpenuhi tersebut.
Pada dasarnya Insert First dan Insert All memiliki fungsi yang sama
JOIN
JOIN merupakan salah satu cara dalam SQL untuk menggabungkan beberapa tabel. Sebelum memulai perhatikan schema HR terlebih dahulu
Perbedaan INNER JOIN dengan OUTER JOIN
INNER JOIN digunakan untuk mencari data yang sama antara dua table, sedangkan OUTER JOIN digunakan untuk mencari data yang sama sekaligus data yang tidak sama
Tipe – tipe JOIN :
Natural Joins
Equijoin
non-equijoin
Cross Join
outer join
Full outter Join
Self Join
Cross Join
Ketentuan Natural JOIN
2 Tabel yang ingin di NATURAL JOIN harus mempunyai minimal satu nama kolom yang sama
contoh : tabel A mempunyai kolom employee_id dan tabel B juga mempunyai kolom employee_id. kolom employee_id inilah yang akan digunakan untuk menggabungkan ke dua tabel tersebut
Jika Kolom yang mempunyai nama sama tersebut memiliki type kolom yang berbeda, maka akan terjadi error
contoh : Tabel A tipe kolom employee_idnya char sedangkan employee_id pada tabel B bertipe integer
Natural Join akan menampilkan data dimana data tersebut memiliki nilai yang sama antara nama kolom yang sama pada tabel A dan Tabel B
contoh query :
SELECT department_id, department_name, location_id, city
FROM departments NATURAL JOIN locations
Penjelasan :
Pada query diatas field department_id, department_name, location_id terdapat pada tabel Departments dan field location_id, city terdapat pada tabel Locations
JOIN dengan USING clause
Ketentuan dan Kegunaan USING Clause
USING clause berguna untuk menutupi salah satu kekurangan dari NATURAL JOIN yaitu menspesifikkan kolom mana yang akan digunakan sebagai penggabung 2 tabel. Hal ini bertujuan untuk menghindari terjadinya error ketika diantara tabel A dan Tabel B mempunyai beberapa nama kolom yang sama tetapi salah satu dari kolom yang sama tersebut memiliki tipe data yang berbeda
Gunakan hanya satu kolom yang akan dijadikan sebagai penggabung tabel
Jangan gunakan tabel alias atau nama tabel kepada kolom yang akan menggunakan USING clause
contoh query :
SELECT employees.employee_id, employees.last_name, departments.location_id, department_id
FROM employees JOIN departments
USING (department_id) ; // perhatihan kolom department_id. kolom tersebut sama sekali tidak memakai alias
Penjelasan : employee_id, last_name, department_id terdapat pada tabel employees, sedangkan department_id, location_id terdapat pada departments. Karena department_id terdapat pada tabel employees dan departments, maka kolom tersebutlah yang akan digunakan sebagai penggabung.
Ambiguous Column Names
Ambiguouos Column Names atau nama kolom yang ambigu merupakan error yang terjadi pada saat execute, si system yang meng execute tersebut menjadi 'bingung' kolom mana yang mau dia ambil. untuk lebih jelasnya lihat contoh dibawah..
SELECT employee_id, last_name, department_id, department_name
FROM employees, departments
WHERE department_id = department_id
Jika query diatas dijalankan, pasti akan terjadi error, kenapa? karena pada tabel employee dan tabel departments sama-sama terdapat nama kolom yang sama, yaitu kolom department_id, sehingga si system menjadi pusing, kolom mana yang mau dia ambil, kolom di employees kah? atau kolom di departments kah?.
Untuk menghindari kerancuan tersebut maka digunakanlah tabel alias seperti query di bawah.
SELECT employee_id, last_name, a.department_id, department_name // bisa juga b.department_id
FROM employees a, departments b
WHERE a.department_id = b.department_id
JOIN dengan ON Clause
Intinya penggunaan ON JOIN sama fungsinya dengan menggunakan query dibawah ini:
SELECT employee_id, last_name, a.department_id, department_name
FROM employees a, departments b
WHERE a.department_id = b.department_id
Kalau menggunakan Join dengan ON clause menjadi :
SELECT employee_id, last_name, a.department_id, department_name
FROM employees a JOIN departments b ON a.department_id = b.department_id
Menggabungkan 3 tabel dengan ON clause
SELECT employee_id, last_name, a.department_id, department_name, city
FROM employees a JOIN departments b ON a.department_id = b.department_id JOIN Locations c
ON b.Location_id = b.location_id
SELF JOIN dengan menggunakan ON clause
Perhatikan tabel employees dibawah :

Pada gambar diatas kita dapat melihat donald dan douglas mempunyai seorang manager yang memiliki ID = 124. bagaimana jika kasusnya kita ingin melihat siapa saja orang yang dikepalai oleh seorang manager dengan id = 124?? dengan kasus seperti inilah kita dapat menggunakan SELF JOIN
Query :
SELECT e.employee_id mgr, m.first_name nama_bawahan, m.employee_id bawahan_ID
FROM employees e join employees m ON e.employee_id=m.manager_id
WHERE e.employee_id=124;

OUTER JOIN
Outer Join merupakan sebuah fungsi dimana fungsi tersebut dapat dipakai untuk menampilkan data yang memiliki kesamaan seperti join yang telah kita bahas sebelumnya, JUGA menampilkan data yang tidak memiliki kesamaan
Tipe-tipe OUTER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
LEFT OUTER JOIN
Sebelum memulai kita asumsikan Tabel Employees berada Disebelah kiri Dan tabel Departments berada disebelah kanan. Jika kita melakukan LEFT OUTER JOIN terhadap kedua tabel tersebut, maka kita akan mendapatkan semua data di tabel employees yang sama dan tidak sama dengan di tabel departments. jadi ibaratnya kita menampilkan data Employees yang tidak dipakai di Departments
contoh :
SELECT last_name, e.department_id, department_name
FROM employees e LEFT OUTER JOIN departments d

RIGHT OUTER JOIN
Right Outer Join kebalikan dari LEFT OUTER JOIN
contoh :
FROM employees e RIGHT OUTER JOIN departments d

FULL OUTER JOIN
Full Outer Join merupakan gabungan dari LEFT OUTER JOIN dan RIGHT OUTER JOIN
contoh :
FROM employees e FULL OUTER JOIN departments d

CROSS JOIN
Cross Join disebut juga dengan CARTESIAN PRODUCT yaitu perkalian dari 2 buah tabel
contoh :
data tabel A : {a,b,c,d } , data Tabel B {F,G,H,I}
Jika tabel A CROSS JOIN tabel B menjadi : {aF, aG, aH, aI, bF, bG, bH, bI, cF, cG, cH, cI, dF, dG, dH, dI}
query nya :
SELECT last_name, department_name FROM employees
CROSS JOIN departments

SQL FUNCTION

SQL Function atau Fungsi yang ada di SQL merupakan sebuah fungsi yang ada pada SQL. Seperti character function, number function, dan date function. Dengan Function-function tersebut, kita dapat menggunakannya untuk mengkonversikan suatu tipe data ke bentuk tipe data yang lain. Contohnya tipe data yang tadinya bertipe karakter bisa menjadi data yang bertipe date.
Kegunaan-kegunaan dari SQL FUNCTION:
Merubah tipe data
Menampilkan format tipe data date dan number
Mengubah tipe data kolom
Memungkinkan terjadinya proses kalkulasi dalam data
Mengubah atau memodifikasi output
Nb: Kita menggunakan SQL FUNCTION yang terdapat pada ORACLE
Tipe –Tipe SQL FUNCTION :
Single Row Function
contohnya :
Character Function
Number Function
Date Function
Conversion Function
General Function
Multiple Row Function
Contohnya : Group Function, dengan group function, kita dapat memanipulasi suatu group yang terdiri dari beberapa baris, menghasilkan hanya satu nilai
Fungsi Date

Fungsi tanggal atau Date Functionmerupakan fungsi yang digunakan untuk mengoperasikan nilai yang bertipe DATE. DiOracle secara default format DATE yang ditampilkan adalah 19-JUN-11 (DD-MM-RR). Tetapi ada hal yang perlu diketauhui bahwa di dalam database, dates tidak hanya disimpan dalam bentuk Day, Month dan Year saja, tetapi juga menyertakan Time dan Century. Century sendiri secara otomatis disimpan ketika user meng- insert data ke dalam tabel dengan menggunakan fungsi SYSDATE . Jadi data lengkapnya seperti ini June 19, 2011, 4:15:20 p.m.
Fungsi SYSDATE
Fungsi SYSDATE atau SYSDATE Function mengembalikan nilai berupa tanggal sekarang
contoh :
SELECT SYSDATE FROM dual
hasilnya adalah tanggal pada saat saya membuat article ini :

DATES dengan Aritmatika
Kegunaannya :
Menambah atau mengurangi bilangan dari atau ke bentuk format dates
Menentukan berapa jumlah hari dari selisih 2 tanggal
Menambah hours ke dalam format dates

Hari ini tanggal 13-01-2012
contoh date+number :
SELECT SYSDATE + 2 FROM DUAL

contoh date-date/number :
SELECT last_name, (SYSDATE-hire_date)/7 as weeks
FROM Employees

Query diatas menampilkan berapa lama karyawan tersebut telah bekerja dalam satuan minggu.
DATE FUNCTION

Contoh penggunaan Date Function

Contoh soal :
Tampilkan employee_id, hire_date, lama bekerja(dalam bulan), hari sabtu pertama dimulai dari dia bekerja, hari terakhir awal bulan dia bekerja dimana employeenya telah bekerja selama 70 bulan
SELECT employee_id,hire_date, MONTHS_BETWEEN(SYSDATE, hire_Date) "" ' bulan' as "lama kerja",NEXT_DAY(hire_date,'sabtu') "sabtu awal",LAST_DAY(hire_Date) "hari terakhir di awal bulan"
From employees WHERE MONTHS_BETWEEN(SYSDATE, hire_Date) >= 70

TRUNC and ROUND Function
Pada gambar diatas untuk lebih menspesifikkan nilai dibelakan koma, kita dapat menggunakan fungsi ROUND dan fungsi TRUNCT
Contoh fungsi Round dengan date:
SELECT employee_id,hire_date,ROUND(MONTHS_BETWEEN(SYSDATE, hire_Date)) "" ' bulan' as "lama kerja",NEXT_DAY(hire_date,'sabtu') "sabtu awal",LAST_DAY(hire_Date) "hari terakhir di awal bulan"

Contoh fungsi Trunc dengan date :
SELECT employee_id,hire_date,TRUNC(MONTHS_BETWEEN(SYSDATE, hire_Date),2) "" ' bulan' as "lama kerja",NEXT_DAY(hire_date,'sabtu') "sabtu awal",LAST_DAY(hire_Date) "hari terakhir di awal bulan"

Fungsi General
Berikut ini merupakan beberapa fungsi general :
NVL
NVL2
NULLIF
COALESCE
perbedaan NVL dan NVL2 :
NVL hanya mempunyai 2 ekspresi sedangkan NVL2 mempunyai 3 ekspresi
Ekspresi 1 dan ekspresi 2 yang ada pada NVL harus mempunyai tipe data yang sama satu sama lain sedangkan pada NVL2 yang harus sama tipe datanya adalah ekspresi 2 dan ekspresi 3
1. NVL
Fungsi NVL merupakan fungsi yang digunakan untuk merubah nilai null menjadi nilai yang lain tetapi tipe datanya masih sama seperti tipe data pada ekspresi1
Tipe data yang dapat menggunakan fungsi NVL adalah DATE, CHARACTERS dan NUMBER.
syntax :
NVL(expresi1, expresi2)
expresi1 : kolom yang ingin dirubah nilai nullnya
expresi2 : nilai yang diinginkan

contoh :

contoh query :
SELECT first_name, salary, commission_pct, NVL(commission_pct, 2) as "new commission_pct", salary*2*NVL(commission_pct, 2) as "New Salary"
FROM Employees

Pada query diatas kita merubah nilai null yang ada pada kolom commission pct agar nilai null tersebut dapat melakukan operasi aritmatika, karena nilai null sendiri merupakan nilai yang tidak dapat melakukan operasi aritmatika
2. NVL2
Perbedaan yang paling mendasar antara NVL dan NVL2 yaitu NVL hanya menggunakan 2 ekspresi sendangkan NVL2 menggunakan 3 ekspresi
syntax :
NVL2(expresi1, expresi2,ekspresi4)
Ekspresi1 : kolom yang ingin dirubah nilai nullnya
Ekspresi2 : apabila terdapat data yang tidak bernilai null pada ekspresi1 maka akan dirubah menjadi ekspresi 2
Ekspresi3 : apabila terdapat daya yang bernilai null pada ekspresi1, maka akan dirubah menjadi ekspresi 3
contoh query :
SELECT first_name, commission_pct, NVL2(commission_pct, 'sal+income', 'income' )
FROM employees
ORDER BY commission_pct

contoh lain :
SELECT first_name, commission_pct, NVL2(commission_pct, 'sal+income', 8 )
FROM employees
ORDERBY commission_pct

nb: pada contoh diatas jika ekspresi2 dan ekspresi3 memiliki tipe data yang berbeda, makaoracle server akan mengkonversikan tipe data ekspresi3 menjadi bertipe data ekspresi 2. Jika dilakukan proses aritmatika pada contoh diatas, maka error result akan terjadi.
3. NULLIF
Fungsi NULLIF merupakan fungsi yang digunakan untuk membandingkan 2 ekpresi. Dimana jika kedua ekspresi tersebut bernilai sama maka akan menghasilkan nilai NULL sedangkan bila kedua ekspresi tersebut memiliki nilai yang berbeda, maka akan menghasilkan nilai ekspresi 1
syntax :
NULLIF(ekspresi1, ekspresi2)
contoh :

nb: secara logika fungsi NULLIF sama dengan CASE expression
4. COALESCE
Pada fungsi COALESCE kita dapat menampung banyak ekspresi. dan setiap ekspresi harus mempunyai tipe data yang sama
syntax :
COALESCE(ekpr1, eksp2, … , ekspN)
eksp1 : bila ekspresi1 nilainya tidak null, maka akan menghasilkan nilai ekspresi 1
eksp2 : bila ekspresi 1 bernilai null, maka akan mennghasilkan nilai ekspresi 2
ekspN : akan menghasilkan nilai ekspN jika ekspresi pendahulunya bernilai null
contoh query :
SELECT first_name, COALESCE(salary,commission_pct, -1)
FROM employees

Fungsi Karakter
Fungsi Karakter atau Character Functionadalah sebuah fungsi yang digunakan untuk merubah tipe inputan yang tadinya bertipe karakter menjadi bertipe number maupun bertipe karakter.
Ada 2 jenis Character Function :
Case Manipulation Function
Character Manipulation Function
Case Manipulation Function
Case Manipulation Function yaitu fungsi yang digunakan untuk memanipulasi kata.
Case Manipulation Function terbagi menjadi 3 jenis yaitu :

1. LOWER
LOWER merupakan fungsi yang digunakan untuk merubah kata menjadi huruf kecil
2. UPPER
Kebalikan dari LOWER, UPPER merupakan fungsi yang digunakan untuk merubah kata menjadi huruf kapital
3. INITCAP
INITCAP merupakan fungsi yang digunakan untuk mengubah karakter awal pada kata menjadi huruf kapital
Character Manipulation Function
Character Manipulation Function merupakan fungsi yang digunakan untuk memanipulasi karakter
Berikut merupakan fungsi-fungsi dari karakter :

1. CONCAT
CONCAT(kolom/'ekspresi', kolom/'expresi') atau Concatenation merupakan fungsi yang digunakan untuk menggabungkan 2 kata menjadi satu
2. SUBSTR
SUBSTR(kolom/'expresi', posisi_awal, posisi_akhir) merupakan fungsi yang digunakan untuk mengambil sejumlah karakter didalam satu kata atau kalimat
3. LENGTH
LENGTH(kolom/'expresi') merupakan fungsi yang digunakan untuk menghitung panjang karakter dalam satu kata atau kalimat
4. INSTR
INSTR(kolom/'expresi', karakternya) digunakan untuk menentukan pada posisi ke berapa karakter yang ingin kita ketahui
5. LPAD
LPAD(kolom/'expresi', n , karakter_yg_ditambahkan) merupakan fungsi yang digunakan untuk menambahkan karakter sebanyak n, yang diletakkan di sebelah kiri suatu kata. Pada gambar diatas kita menggunakan LPAD agar baris didalam kolom salary diisi dengan 10 karakter, dan apabila baris tersebut kurang dari 10 karakter, maka akan ditambahkan karakter ' * ' sebanyak yang dibutuhkan agar mencapai 10 karakter
6. RPAD
RPAD merupakan kebalikan dari LPAD
7. TRIM
TRIM( karakterYgIngindipotong FROM kolom/'expresi') digunakan untuk menghapus atau memotong karakter yang kita inginkan
8. REPLACE
REPLACE(kolom/'expresi', kataYgDiganti, KataPengganti) merupakan fungsi yang digunakan untuk mengganti kata dengan kata yang kita inginkan
Fungsi Konversi
Fungsi Konversi atau Conversion Functionadalah fungsi yang digunakan untuk merubah tipe data ke bentuk lain. Pada Oracle, dapat dilakukan dengan menggunakan 2 cara, yaitu secara eksplisit dan secara implisit. Konversi secara implisit adalah konversi yang dilakukan secara otomatis oleh Oracle Server. Sedangkankonversi secara eksplisit adalah konversi yang dilakukan oleh user.
KONVERSI IMPLISIT
Contoh Konversi Implisit :
SELECT * FROM Employees
WHERE hire_date > '19-jun-2000
Perhatikan ekpsresi hire_date > '19-jun-2000 , hire-date merupakan kolom yang bertipe DATE , sedangkan '19-jun-2000 bertipe STRING dan seharusnya kedua statement tersebut tidak dapat dibandingkan karena berlainan tipe data, tetapi oleh oracle server ekspresi '19-jun-2000 dirubah menjadi bertipe DATE sehingga tidak terjadi error pada saat di execute
Tipe data yang dapat dikonversikan secara otomatis oleh oracle server adalah sbb:

Selain contoh diatas, ekspresi salary > '2000 juga dapat dirubah oleh oracle server yang tadinya '2000 bertipe string karena terdapat tanda petik, dirubah menjadi bertipe number
KONVERSI EKSPLISIT

Gambar diatas menjelaskan jika ingin merubah tipe data dari CHARACTER ke NUMBER gunakan fungsi TO_NUMBER, jika ingin merubah tipe data dari DATE ke CHARACTER gunakan fungsi TO_CHAR dst..
1. Fungsi TO_CHAR
a. Fungsi TO_CHAR untuk merubah tipe DATE

Format Modelnya :
Harus ditutup dengan tanda petik satu
Case Sensitive
Dapat memuat semua format date yang valid
Dipisahkan dengan koma
Berikut adalah contoh format date yang valid :

Selain itu di oracle juga terdapat time format

Contoh Query :
SELECT last_name, TO_CHAR (hire_date,'fmDD MONTH YYYY') AShire_date
FROM employees

nb : query diatas berguna untuk merubah tipe hire date menjadi char dan syntax fm pada 'fmDD' berfungsi untuk menghilangkan angka 0(nol). tanpa fm -> 07 januari 2000, menggunakan fm -> 7 januari 2000
Bentuk format yang lain

contoh :
SELECT last_name, TO_CHAR (hire_date,'DDspth MONTH YYYY') AShire_date
FROM employees

b.Fungsi TO_CHAR untuk merubah bilangan
Syntax :
TO_CHAR(NUMBER, 'FORMAT_MODEL')
Berikut merupakan elemen-elemen yang digunakan untuk menampilkan nilai bilangan sebagai karater

contoh :
SELECT TO_CHAR(salary, $99,999.00) salary FROM employees

2. FUNGSI TO_DATE
Fungsi TO_DATE atau TO_DATE function digunakan untuk mengkonversi tipe string ke tipeDATE
contoh :
SELECT last_name, hire_date FROM employees
WHERE hire_date = TO_DATE('Mei 24, 1999 , 'MONTH DD, YYYY')
pada Fungsi To_Date terdapat sintax fx yang digunaka untuk memastikan posisi string dengan format modelnya harus sama persis tidak boleh berbeda walau hanya satu spasi
contoh :
SELECT last_name, hire_date FROM employees
WHERE hire_date = TO_DATE('Mei 24, 1999 , 'fxMONTH DD, YYYY')

Format Tanggal RR

format YY dilihat pada 2 digit awal tahun pada saat ini yang apabila saat ini tahun 1995 dan kita menuliskan 27-10-17 maka akan diartikan sebagai tahun 1917. sedangkan apabila tahun sekarang adalah tahun 2001 dan kita menuliskan 27-10-95, maka akan diartikan sebagai tahun 2095
Kalau Format RR, kita harus perhatikan 2 digit tahun saat ini dan 2 digit tahun yang ingin kita tulis. dan untuk lebih jelasnya silakan lihat gambar diatas
Number Function
Number Function atau fungsi angka merupakan sebuah fungsi yang menerima inputan bertipe numeric dan mengembalikan output bertipe numeric.
perhatikan gambar dibawah ini :

ROUND berguna untuk membulatkan angka desimal, pembulatanyapun sama dengan pembulatan matematika
TRUNC berguna untuk mengambil nilai sebanyak yang dibutuhkan. pada gambar diatas kita mengambil 2 angka dibelakang koma
MOD atau Modulus digunakan untuk menentukan sisa hasil pembagian.
Contoh ROUND function :

Contoh TRUNC function :

Contoh MOD function :

Database Transaction

Bisa dibilang Data Transaction adalah segala macam proses DML, DDL, maupun DCL(Data Control Language) yang terjadi dalam database. Control Transaction memberikan kita fleksibilitas dan control dalam merubah data dan juga memastikan terjadinya kekonsistenan data.
Tipe Transaction
Tipe
Deskripsi
DDL
Terdiri dari satu statement DDL
DML
Terdiri dari beberapa statement DML
DCL
Terdiri dari satu statement DCL
Database Transaction dimulai dengan DML statement yang kemudian diakhiri dengan salah satu event berikut :
Sebuah COMMIT atau ROLLBACK
Di Executenya statement DDL atau DCL
USER Keluar dari iSQL Plus
System Crash
Eksplisit Transaction Contorl Statemnt
Tipe
Deskripsi
COMMIT
mengakhiri transaksi yang sedang terjadi dengan merubah semua pending data menjadi permanen
ROLLBACK
Mengembalikan transaksi kebentuk semula sampai bertemu COMMIT terakhir kali
SAVEPOINT name
Sebagai penanda di dalam transaksi yang sedang terjadi
ROLLBACK TO SAVEPOINT
Mengembalikan transaksi sampai bertemunya SAVEPOINT
Proses Implicit Transaction
Berikut adalah statement-statemnet yang mengakibatkan COMMIT secara otomatis :
Terjadinya DDL statement
Terjadinya DCL statement
Normal Exit dari iSQL Plus tanpa menggunakan COMMIT atau ROLLBACK sebelumnya.
Dan ROLLBACK secara otomatis terjadi ketika terjadinya termination pada iSQL PLUS secara abnormal atau terjadinya system failure.
Berikut ini hal yang dapat dilakukan Sebelum data di COMMIT atau di ROLLBACK :
Current user dapat melihat hasil dari DML statement yang dilakukannya dengan menggunakan SELECT sedangkan user yang lain tidak dapat melihat hasil tersebut sampai dikeluarkanya statement commit oleh Current USER
Semua Data yang sedang digunakan oleh Current User menjadi terkunci, sehingga tidak dapat diakses oleh user lain dan hanya bisa diakses oleh si CURRENT User
Current User dapat mengembalikan data ke bentuk sebelumnya
Berikut ini hal yang dapat dilakukan setelah data di COMMIT atau di ROLLBACK :
Perubahan data menjadi permanent
Data sebelum di commit otomatis langsung hilang
Semua user dapat melihat datanya
Data yang tadinya terkunci menjadi terbuka, sehingga dapat diakses oleh seluruh user
Seluruh Savepoint hilang
Contoh :
Sebelum kita melakukanya kita harus menggunakan keyword SET AUTOCOMMIT OFF terlebih dahulu, yang berguna untuk memberi tahu sistem untuk mematikan autocommit.
SET AUTOCOMMIT OFF
DELETE FROM Employees WHERE employee_id = 120;
setelah itu kita dapat mengembalikanya lagi dengan
ROLLBACK
atau membuatnya permanen dengan menuliskan
COMMIT
Nb: Walaupun kita telah menuliskan SET AUTOCOMMIT OFF, tetapi autocommit tetap terjadi jika adanya proses ddl, dcl dan sistem failure
SAVEPOINT
Savepoint digunakan untuk menandakan sampai dimana rollbacknya berhenti
Contoh :
Update employees
SET Salary = 2000
WHERE employee_id = 130;
SAVEPOINT A;
Update Employees
SET Salary = 4000
SAVEPOINT B;
Update Employees
SET Salary = 5000
Ketika kita menjalankan
ROLLBACK TO SAVEPOINT B;
Maka SALARY employee_id = 130 adalah 4000 dan Jika kita hanya menuliskan ROLLBACK saja, Maka hasilnya adalah salary sebelum Diupdate atau masih salary semula yaitu 2800
INDEX
INDEX :
adalah sebuah schema object
digunakan oleh oracle server untuk meningkatkan kecepatan penerimaan data dengan menggunakan pointer
Dapat mengurangi kebutuhan disk input/output dengan menggunakan sebuah metode rapid path access untuk mengalokasikan data dengan cepat
Tabel yang independen
Digunakan dan dipelihara secara otomatis olehORACLE SERVER
INDEX dapat dibuat dengan 2 cara yaitu :
1. Secara Otomatis
Index secara otomatis dibuat ketika kita mendefinisikan sebuah primary key atau UNIQUE Constraint dalam mendefinisikan sebuah tabel
2. Secara Manual
user dapat membuat index yang sama pada kolom untuk meningkatkan kecepatan akses data
Tipe – tipe INDEX :
Unique
Memastikan kalau value dari kolom harus bersifat unique dan dibuat secara otomatis
Non-Unique
Memastikan cara tercepat untuk mencari data dan dibuat secara manual oleh user
Single Column
Hanya terdapat satu kolom dalam satu index
Concatenated atau Composite
Dapat terdiri hingga 16 kolom dalam satu index
Membuat Sebuah INDEX
syntax :
CREATE INDEX index
ON table (column[, column]…);
contoh Meningkatkan kecepatan query access pada kolom FIRST_NAME di tabel EMPLOYEES
CREATE INDEX emp_first_name_idx
ON employees(first_name);
Banyak INDEX Belum Tentu lebih baik
maksud dari statement diatas adalah jika mempunyai banyak index di dalam tabel, bukan berarti kita akan menghasilkan query yang lebih cepat, kenapa? Karena Setiap operasi DML yang terjadi dan di COMMIT pada tabel yang menggunakan INDEX, berarti setiap INDEX yang terdapat pada tabel tersebut juga harus disesuaikan dengan perubahan yang terjadi, yang menyebabkan kinerja ORACLE SERVER menjadi lebih berat.
berikut merupakan petunjuk kapan sebaiknya index dibuat :

Menghapus Sebuah Index
DROP INDEX nama_index
nb: Index dapat dihapus oleh pemilik index tersebut saja dan hanya oleh orang yang mempunyai DROP ANY INDEX privileges. Dan index tidak dapat dirubah atau dimodifikasi
Rollup dan Cube di Oracle

Rollup merupakan salah satu ekstensi dari Group by clause. Dengan Rollup, kita dapat menggunakanya untuk mencari subtotal.
Syntax :
SELECT [column,] group_function(column). . .
FROM table
[WHERE condition]
[GROUP BY [ROLLUP] group_by_expression]
[HAVING having_expression];
[ORDER BY column];

Contoh :
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY ROLLUP(department_id, job_id);

Pada nomor 1 kita mendapatkan subtotal salary berdasarkan department_id dan Job_id yang sama.
Pada nomor 2, kita mendapatkan subtotal salary hanya berdasarkan Department_id
Pada Nomor 3, kita mendapatkan Grand Total Salary dimana department_id < 60
Cube Operator
Sama dengan Rollup, Cube juga merupakan salah satu ekstensi dari Group by Clause. Kita dapat menggunakan Cube untuk mendapatkan hasil berupa Cross Tabulation Values dengan hanya menggunakan single Select statement.
Syntax :

SELECT [column,] group_function(column)...
FROM table
[WHERE condition]
[GROUP BY [CUBE] group_by_expression]
[HAVING having_expression]
[ORDER BY column];
Contoh :
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY CUBE (department_id, job_id) ;

Pada Hasil query diatas,
Indikator merah Merupakan Grand Total salary
Indikator biru merupakan subtotal salary hanya dari job_id
Indikator Pink merupakan subtotal salary yang dihitung dengan mengelompokkan job_id dan department_id
Indikator ungu merupakan subtotal salary berdasarkan department_id.
Operator Cube dapat digunakan pada semua aggregate function termasuk MAX, MIN, COUNT, AVG, and SUM. Dan dengan Cube Operator, kita dapat mendapatkan hasil dari semua kombinasi subbtotal yang didefinisikan pada Group By clause.
SEQUENCE

SEQUENCE adalah sebuah object dalam database yang digunakan untuk mengenerate bilangan
Sequence :
Dapat secara otomatis mengenerate bilangan yang unik
Sharable object
Dapat digunakan untuk menentukan value atau nilai dari primary key
Replace application code
Dapat meningkatkan kecepatan dalam mengkakses nilai sequence secara signifikan ketika sequence tersebut telah ada di memory (cached in memory)
Syntax :
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n " NOMAXVALUE}]
[{MINVALUE n " NOMINVALUE}]
[{CYCLE " NOCYCLE}]
[{CACHE n " NOCACHE}];
CYCLE atau NOCYCLE : digunakan untuk menentukan apakah SEQUENCE akan tetap dilanjutkan ketika sequence tersebut telah mencapai nilai maksimum atau minimum
CACHE atau NOCACHE : digunakan untuk menentukan seberapa banyak nilai yang akan dletakkan dan disimpan oleh ORACLE SERVER di memory
Membuat Sequence
Kita akan membuat sequence dengan nama emp_id_seq yang akan digunakan sebagai nilai dari kolom primary key pada tabel DEPARTMENTS.
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 240
MAXVALUE 9999
NOCACHE
NOCYCLE;
NEXTVAL and CURRVAL Pseudocolumns
NEXTVAL menghasilkan next value dari nilai sequence yang telah tersedia. Nextval selalu menghasilkan nilai yang berbeda setiap kali dijalankan walaupun dijalankan oleh user yang berbeda
CURRVAL menghasilkan nilai sekarang atau current value.
Sebelum CURRVAL memuat sebuah nilai, NEXTVAL harus dibuat terlebih dahulu
Aturan dalam menggunakan NEXTVAL dan CURRVAL :
Gunakan pada :
SELECT list dari select statement yang bukan bagian dari subquery
SELECT list dari subquery dalam INSERT statement
Klausa VALUES pada INSERT statement
Klausa SET pada UPDATE statement
Jangan gunakan pada :
SELECT list pada VIEW
SELECT statement dengan DISTINCT keyword
SELECT statement dengan klausa GROUP BY, HAVING, ORDER BY
Subquery di dalam SELECT, DELETE, atau UPDATE statement
DEFAULT expressions didalam CREATE TABLE atau ALTER TABLE statement
Cara Menggunakan SEQUENCE
contoh :
INSERTINTO departments(department_id, department_name, location_id)
VALUES (dept_deptid_seq.NEXTVAL,'Support', 2500);

Untuk melihat current value dari SEQUENCE dept_deptid_seq :
SELECT dept_deptid_seq.CURRVAL
FROM dual;

Caching Sequence Values
Dengan Caching Sequence Values di dalam memory, maka hal itu akan berdampak pada kecepatan dalam mengakses nilai dari sequence tersebut
Jarak antara sequence dapat terjadi jika :
Terjadinya ROLLBACK
Sistem crashes
Sequence nya di pakai pada tabel lain
Memodifikasi Sebuah SEQUENCE
Kita juga dapat merubah pertambahan nilai, nilai maksimum, nilai minimum, cycle option, or cache option dengan menggunakan statement ALTER. Hal ini dapat dilakukan ketika sebuah SEQUENCE telah mencapai batasnya
contoh :
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 25
MAXVALUE 999999
NOCACHE
NOCYCLE;
Petunjuk dalam Merubah Sebuah SEQUENCE
Kita harus sebagai pemilik atau memiliki ALTER PRIVILEGE terhadap SEQUENCE tersebut
Hanya nilai yang akan dibuat yang dapat dirubah bukan nilai yang sudah ada
SEQUENCE harus dihapus atau dibuat ulang untuk mengulang nilai sequence pada bilangan yang berbeda
START WITH option tidak dapat kita rubah dengan menggunakan ALTER SEQUENCE. START WITH hanya dapat kita rubah dengan menghapus dan membuat ulang SEQUENCE
Untuk menghapus Sebuah SEQUENCE, gunakan DROP statement
DROP SEQUENCE nama_sequence
Set Operators
Tipe – Tipe SET OPERTORS :
UNION
UNION ALL
INTERSECT
MINUS
Syarat dan Ketentuan penggunaan SET OPERATOR :
Jumlah dan tipe kolom yang ingin ditampilkan harus sama antara query yang pertama dan query kedua
Order By clause dapat dipakai pada query terakhir dan jika tidak ditentukan data akan diurutkan secara ascending
Perbedaan denga Join yaitu Kalau dengan JOIN tabel akan bertambah ke samping sedangkan dengan SET OPERATOR data akan bertambah kebawah
Sebelum memulai perhatikan tabel yang akan kita gunakan :

UNION OPERATOR

Union Operator akan menggabungkan hasil dari 2 query, dimana hasil dari penggabungan 2 query tersebut telah dieleminasi dari duplikat data
contoh : tabel C {a,b,c,d} union tabel D {c,d,e} hasilnya {a,b,c,d,e}
contoh query :
SELECT nama, salary from A
UNION
SELECT nama, salary from B

UNION ALL OPERATOR

Union All Operator akan menggabungkan hasil dari 2 query, dimana hasil dari penggabungan 2 query tersebut tidak dieleminasi dari duplikat data
contoh : tabel C {a,b,c,d} union tabel D {c,d,e} hasilnya {a,b,c,d,c,d,e}
contoh query :
UNION ALL

INTERSECT OPERATOR
Intersect Operator akan menggabungkan 2 query dan menghasilkan data yang sama antara hasil dari query pertama dan hasil dari query kedua
contoh : tabel C {a,b,c,d} INTERSECT tabel D {c,d,e} hasilnya {c,d}
contoh query :
INTERSECT

MINUS OPERATOR

Minus Operator akan menggabungkan 2 query dan menghasilkan data yang ada pada TABEL A tetapi tidak ada pada tabel B
MINUS

SINONIM
SYNONYM digunakan untuk mempermudah kita dalam mengakses sebuah object dengan cara memanggil sebuah tabel dengan nama yang berbeda. dan dengan Synonim kita dapat :
- Membuat referensi tabel dengan mudah yang dimana referensi tersebut dimiliki oleh user lain
- Memperpendek nama dari sebuah Object
Membuat SYNONYM
syntax :
CREATE [PUBLIC] SYNONYMsynonym
FOR object;
contoh :
CREATE SYNONYM F_dept
FOR FERN.DEPARTMENTS;
query diatas digunakan untuk membuat sebuah synonym dengan nama F_Dept yang merupakan alias atau kependekan nama dari tabel DEPARTMENTS yang dimiliki oleh FERN(user). Sehingga jika kita ingin mengakses tabel departments kepemilikan FERN, kita hanya tinggal memanggil sinonimnya saja
contoh :
SELECT * FROM F_dept
selain itu kita juga dapat membuat sinonim yang dapat diakses oleh semua user dengan menambahkan PUBLIC
contoh:
CREATE PUBLIC SYNONYM F_dept
FOR FERN.DEPARTMENTS;
Menghapus SYNONYM
DROP SYNONYM nama_synonym

Panduan oracle lengkap

Recommend Documents