MASTERS SINTAKS DASAR ORACLE

ERD ( Entity Relationship Diagram )
ERD merupakan suatu model untuk menjelaskan hubungan antar data dalam basis data berdasarkan objek-objek dasar data yang mempunyai hubungan antar relasi. Pada dasarnya ada tiga simbol yang digunakan, yaitu :

a.    Entiti / Entitas : Entiti merupakan objek yang mewakili sesuatu yang nyata dan dapat dibedakan dari sesuatu yang lain. Simbol dari entiti ini biasanya digambarkan dengan persegi panjang.

b.   Atribut : berfungsi untuk mendeskripsikan karakteristik dari entitas tersebut. Isi dari atribut mempunyai sesuatu yang dapat mengidentifikasikan isi elemen satu dengan yang lain. Gambar atribut diwakili oleh simbol elips.

c.    Hubungan / Relasi : Hubungan antara sejumlah entitas yang berasal dari himpunan entitas yang berbeda. Relasi dapat digambarkan dengan notasi belah ketupat. Contoh : Relasi yang terjadi diantara dua himpunan entitas (misalnya A dan B) dalam satu basis data.

d.   Kardinalitas :  menggambarkan banyaknya jumlah maksimum entitas yang dapat berelasi dengan entitas pada himpunan entitas yang lain. Jenis dari Kardinalitas, yaitu :

1. Satu ke satu (One to one) : Hubungan relasi satu ke satu yaitu setiap entitas pada himpunan entitas A berhubungan paling banyak dengan satu entitas pada himpunan entitas B.
2. Satu ke banyak (One to many) : Setiap entitas pada himpunan entitas A dapat berhubungan dengan banyak entitas pada himpunan entitas B, tetapi setiap entitas pada entitas B dapat berhubungan dengan satu entitas pada himpunan entitas A.
3. Banyak ke banyak (Many to many): Setiap entitas pada himpunan entitas A dapat berhubungan dengan banyak entitas pada himpunan entitas B.

Primary Key

Primary key adalah sebuah constraint gabungan dari Contraint Unique dan Constraint Not Null. Sehingga jika sebuah kolom diberikan constraint Primary Key, maka data yang diberikan tidak boleh kosong dan tidak boleh sama dengan baris yang lain. Misalnya pada tabel mahasiswa, kolom no_mahasiswa diberikan constraint Primary Key, karena setiap mahasiswa pasti memiliki no_mahasiswa, sehingga tidak kosong (Not Null) dan juga tidak mungkin satu orang mahasiswa mempunyai no_mahasiswa yang sama dengan mahasiswa yang lain (Unique). Hanya saja perbedaannya adalah constraint PRIMARY KEY membentuk index untuk kolom tersebut. Konvensi nama Constraint-nya adalah : PK_NamaTabel.

Deklarasi Primary Key pada satu kolom saat CREATE TABLE adalah:

CREATE TABLE nama_tabel (nama_kolom tipe_kolom PRIMARY KEY); atau

CREATE TABLE nama_tabel (nama_kolom tipe_kolom CONSTRAINT nama_constraint PRIMARY KEY);

Foreign Key

Foreign Key adalah kolom dalam sebuah tabel yang bukan primary key, tetapi kolom tersebut merupakan primary key dalam tabel yang lain. Foreign key digunakan untuk menjaga integritas data antara tabel parent dengan table child. Misalnya pada tabel Mahasiswa, yang menjadi primary key adalah kolom no_mahasiswa. Dan tabel mahasiswa ini berelasi dengan tabel nilai_mahasiswa, di dalam tabel nilai_mahasiswa terdapat kolom no_mahasiswa yang menghubungkan kedua tabel tersebut, tetapi kolom no_mahasiswa di dalam tabel nilai_mahasiswa bukan sebagai primary key, akan tetapi menjadi foreign key.

Deklarasi saat CREATE TABLE adalah :

CREATE TABLE nama_tabel (nama_kolom tipe_data,..., nama_kolom_n tipe_data, CONSTRAINT nama_constraint FOREIGN KEY (nama_kolom) REFERENCES nama_tabel_parent [ON DELETE CASCADE]);

Data pada tabel parent tidak dapat di update dan dibapus jika telah mempunyai child. Kecuali dengan pemberian pernyataan ON DELETE CASCADE saat deklarasi Constraint FOREIGN KEY. Dengan pernyataan tersebut, jika data pada tabel parent dihapus, maka semua data pada tabel child yang mengacu ke data tersebut juga akan ikut dihapus.

SQL (Structured Query Language )

SQL merupakan suatu bahasa (language) yang digunakan untuk mengakses database. Terdapat 3 jenis perintah SQL, yaitu :

DDL ( Data Defenition Language)

DDL merupakan perintah SQL yang berhubungan dengan pendefinisian suatu struktur database, dalam hal ini database dan table. Beberapa perintah dasar yang termasuk DDL ini antara lain :

1. CREATE

CREATE TABLE mhs (

nim varchar(10) NOT NULL,

nama varchar(30) NOT NULL,

tgllahir date,

alamat text,

PRIMARY KEY(nim));

Keterangan Perintah diatas :

·   CREATE TABLE merupakan perintah dasar dari pembuatan table.

·   mhs merupakan nama tabel yang akan dibuat.

·   Nim, nama, tgllahir dan alamat merupakan nama field

·   Varchar, date dan text merupakan tipe data dari field

·   NOT NULL merupakan option untuk menyatakan bahwa suatu field tidak boleh kosong.

·   PRIMARY KEY merupakan perintah untuk menentukan field mana yang akan dijadikan primary key pada tabel.

·   10 dan 30 di belakang tipe data merupakan panjang maksimal dari suatu field

·   Untuk tipe data date dan text (dan beberapa tipe data lainnya) panjang karakter maksimalnya tidak perlu ditentukan.

·   Jangan lupa akhiri perintah dengan titik-koma (;)

2. ALTER

Perintah ini digunakan untuk mengubah struktur table. Kita dapat menambah kolom, menghapus kolom, memodifikasi panjang kolom, menambah atau menghapus constraints, dan meng-enable-kan atau men-disable-kan constraints tersebut menggunakan perintah ini.

TRUNCATE digunakan untuk menghapus data dari table.

COMMENT digunakan untuk menambahkan komentar tentang suatu objek database ke dalam data dictionary. Ketika kita mengeksekusi statement-statement ini, akan terjadi proses commit otomatis. Kita tidak dapat me-rollback perintah-perintah DDL.

Menambahkan Kolom

Kita dapat menambahkan kolom ke dalam table dengan menggunakan perintah ALTER TABLE dengan klausa ADD.

Sintaks

ALTER TABLE table_name ADD (column_name datatype (angka)  NOT NULL );

ALTER TABLE mahasiswa  ADD (telepon varchar(14) NOT NULL);

dimana:

• datatype : tipe data dan panjangnya
• NOT NULL: menambahkan contraint NOT NULL untuk kolom baru

Memodifikasi Column

Kita dapat memodifikasi kolom dengan menggunakan perintah ALTER TABLE dengan klausa MODIFY. Modifikasi kolom dapat mengandung perubahan tipe data, panjang, nilai default, dan constraint NOT NULL.

Sintaks

ALTER TABLE table_name MODIFY (column_name datatype [DEFAULT expr] NOT NULL);

Contoh :

ALTER TABLE matakuliah MODIFY ( sks numeric (1 ) NOT NULL);

dimana:

• DEFAULT expr : menentukan nilai default dari kolom

Aturan Penggunaan

·      Memperbesar lebar kolom atau presisi untuk kolom bertipe numerik

·      Memperkecil lebar kolom jika kolom hanya mengandung nilai null atau jika table tidak memiliki data

·      Mengubah tipe data jika kolom mengandung nilai null

·      Mengkonversi tipe data CHAR menjadi VARCHAR2 atau sebaliknya, jika kolom mengandung nilai null atau jika kita tidak mengubah panjang tipe datanya

·      Perubahan ke nilai default akan berefek kepada proses insert berikutnya, tidak terhadap data yang sudah ada

·      Menambahkan constraint NOT NULL hanya jika tidak terdapat nilai null pada kolom

Menambahkan dan Menghapus Contraint

Kita dapat menambah dan menghapus contraint pada table yang telah ada dengan menggunakan perintah ALTER TABLE dengan klausa ADD atau DROP.

Sintaks Menambah Constraint

ALTER TABLE table_name  ADD  [ CONSTRAINT constraint_name  ] type (column_name);

Contoh :

ALTER TABLE mahasiswa ADD CONSTRAINT PK_nomor PRIMARY KEY (nomor);

ALTER TABLE mahasiswa ADD CONSTRAINT PRIMARY KEY  PK_nomor  (nomor);

dimana:

• type: tipe constraint ( contohnya PRIMARY KEY dan FOREIGN KEY)

Aturan penggunaan

• Kita dapat menambah, menghapus, mengaktifkan, dan menonaktifkan constraint, tetapi kita tidak dapat memodifikasi strukturnya
• Kita dapat menambahkan contraint NOT NULL terhadap kolom dengan menggunakan klausa MODIFY dari perintah ALTER TABLE

Untuk menghapus constraint, kita dapat mengidentifikasi nama constraint dari data dictionary views USER_CONSTRAINTS dan USER_CONS_COLUMNS. Kemudian, kita dapat menggunakan perintah ALTER TABLE dengan klausa DROP. Opsi CASCADE pada klausa DROP menyebabkan seluruh constraint yang bergantung terhadap constraint yang kita hapus akan ikut terhapus.

Sintaks

ALTER TABLE table_name DROP PRIMARY KEY | UNIQUE (column_name) | CONSTRAINT constraint_name [CASCADE];

Contoh :

 Menghapus Constraint -----> ALTER TABLE employees DROP CONSTRAINT pk_idbrgdis;

Catatan:

Ketika kita menghapus integrity constraint, constraint tersebut tidak lagi dijalankan oleh Oracle dan tidak lagi tersedia di dalam data dictionary.

Menghidupkan dan Mematikan Constraint

Kita dapat menghidupkan dan mematikan constraint tanpa menghapus atau menciptakannya ulang dengan menggunakan perintah ALTER TABLE dengan klausa ENABLE atau DISABLE.

Sintaks
ALTER TABLE table_name DISABLE | ENABLE CONSTRAINT constraint_name  [CASCADE];

Aturan penggunaan

• Jika kita menghidupkan constraint, constraint tersebut terpakai pada seluruh data dalam table. Seluruh data dalam table harus memenuhi ketetapan dari contraint tersebut.
• Jika kita menghidupkan contraint UNIQUE atau PRIMARY KEY, maka UNIQUE dan PRIMARY KEY index otomatis diciptakan.
• Kita dapat menggunakan klausa ENABLE dan DISABLE baik pada perintah CREATE TABLE maupun perintah ALTER TABLE.
• Klausa CASCADE mematikan constraint yang bergantung dibawahnya

RENAME

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

Mengubah Nama dan Menghapus Isi Table

Perintah-perintah DDL tambahan termasuk perintah RENAME, yang mana digunakan untuk mengubah nama table, view, sequence, atau synonym, dan perintah TRUNCATE TABLE, yang mana digunakan untuk menghapus seluruh data dari table dan untuk membebaskan tempat penyimpanan yang digunakan oleh table tersebut.

Sintaks – Perintah RENAME
RENAME old_name TO new_name;

Sintaks – Perintah TRUNCATE (Menghapus Data pada tabel)

TRUNCATE TABLE table_name; Contohnya : truncate table customer;

Catatan:

Perintah DELETE dapat juga menghapus seluruh data dari table, tetapi ia tidak membebaskan tempat penyimpanan yang digunakan oleh table tersebut.

Menambahkan Komentar Terhadap Table

Kita dapat menambahkan komentar hingga 2000 bytes, terhadap column, table, view, atau snapshot dengan menggunakan perintah COMMENT. Komentar tersimpan didalam data dictionary dan dapat dilihat didalam salah satu data dictionary views berikut didalam kolom COMMENTS:

• ALL_COL_COMMENTS
• USER_COL_COMMENTS
• ALL_TAB_COMMENTS
• USER_TAB_COMMENTS

Sintaks

COMMENT ON TABLE table_name | COLUMN table.column_name IS ‘isi_komentar’;

Contoh:

COMMENT ON TABLE employee IS ‘Employee Information’;

Menghapus komentar pada column.

COMMENT ON COLUMN employee.last_name IS ”;

DROP

Menghapus Table

Perintah DROP TABLE menghapus definisi table. Ketika kita menghapus table, database akan kehilangan seluruh data dalam table dan seluruh index yang terasosiasi dengannya. Opsi CASCADE CONTRAINTS jika menghapus referential integrity constraint yang bergantung kepada table tersebut.

Sintaks

DROP TABLE table_name  [CASCADE CONSTRAINTS];

Aturan penggunaan

• Seluruh data dihapus dari table
• Seluruh view, synonym, stored procedure, function, atau package tetap ada, tetapi statusnya menjadi invalid
• Seluruh transaksi akan di-commit
• Hanya pencipta table tersebut sajalah atau user dengan privilege DROP ANY TABLE yang dapat menghapus table tersebut

Catatan:

Perintah DROP TABLE, sekali dieksekusi, tidak dapat diputar balik. Oracle tidak memberikan pertanyaan ketika kita menjalankan perintah DROP TABLE. Jika kita memiliki table tersebut atau memiliki privilege dengan level tinggi, maka table segera terhapus. Seluruh perintah-perintah DDL mengeksekusi commit, sehingga menyebabkan transaksi menjadi permanen.

DML ( Data Manipulation Language )

DML merupakan perintah SQL yang berhubungan dengan manipulasi atau pengolahan data atau record dalam table. Perintah SQL yang termasuk dalam DML antara lain :

SELECT

INSERT

Insert digunakan untuk memasukkan data ke sebuah tabel dalam user (schema) tertentu. Syntax Insert dapat dituliskan sebagai berikut : INSERT INTO nama_table ( nama_kolom1, nama_kolom2, . . .) VALUES ( nilai1,  nilai2, . . .);

Contoh :

INSERT INTO MURID (NIS, NAMA, TGL_LAHIR, JENIS_KELAMIN, ALAMAT, ORTU) VALUES ( ’000001′,  ‘Tumbur Josua’, TO_DATE(’23-04-1993′,‘DD-MM-YYYY’), ‘L’, ‘JL. P. DIPENOGORO, TEGAL’, ‘UCOK’);

UPDATE

Update digunakan untuk mengubah data pada sebuah tabel dalam user (schema) tertentu berdasarkan kondisi tertentu. Syntax Update dapat dituliskan sebagai berikut : UPDATE nama_table  SET nama_kolom1  =  nilai1, nama_kolom2 = nilai2, nama_kolom3 = nilai3 WHERE  kondisi_update ;

Contoh :

UPDATE  MURID  SET NAMA = ‘MUKHTARUL UMAM SHOLEH’,  TGL_LAHIR = TO_DATE(’25-04-1993′,‘DD-MM-YYYY’) WHERE  NIS = ’000001′;

DELETE

Delete digunakan untuk menghapus data pada sebuah tabel dalam user (schema) tertentu berdasarkan kondisi tertentu. Syntax Delete dapat dituliskan sebagai berikut : DELETE nama_table WHERE  kondisi_delete;

Contoh : DELETE MURID WHERE  NIS = ’000001′;

DCL (Data Control Language)

DCL merupakan perintah SQL yang berhubungan dengan pengaturan hak akses user MySQL, baik terhadap server, database, tabel maupun field. Perintah SQL yang termasuk dalam DCL antara lain :

GRANT
Perintah ini digunakan untuk memberikan hak / izin akses oleh administrator (pemilik utama) server kepada user (pengguna biasa). Hak akses tersebut berupa hak membuat (CREATE), mengambil (SELECT), menghapsu (DELETE), mengubah (UPDATE) dan hak khusus berkenaan dengan sistem databasenya.

SINTAKS :  GRANT privileges ON tbname TO user

GRANT CONNECT, RESOURCE TO tumbur;
CONTOH : GRANT select, update, insert, delete ON perpustakaan.buku TO 'ali'@'localhost';

Untuk membuat role gunakan perintah CREATE ROLE seperti berikut ini:

Sintaks : CREATE ROLE nama_role IDENTIFIED BY password_role;

Contoh : CREATE ROLE role_tumbur  IDENTIFIED BY tumbur_juga;

GRANT privilege TO nama_role;

GRANT CREATE TABLE TO role_tumbur;

GRANT SELECT , UPDATE TABLE ON barang TO role_tumbur;

GRANT UPDATE TABLE ON barang TO role_tumbur;

GRANT nama_role TO nama_user;

GRANT role_tumbur TO tumbur;

REVOKE

Perintah ini memiliki kegunaan terbalik dengan GRAND, yaitu untuk menghilangkan atau mencabut hak akses yang telah diberikan kepada user oleh administrator.
SINTAKS : REVOKE privileges ON tbname FROM user
CONTOH : REVOKE select, update, insert, delete ON perpustakaan.buku FROM 'ali'@'localhost';

REVOKE nama_role FROM nama_user;

REVOKE role_tumbur FROM tumbur;

CONSTRAINT PADA TABEL

a. Not Null

Deklarasi saat CREATE TABLE adalah:

CREATE TABLE nama_tabel (nama_kolom tipe_kolom NOT NULL); atau

CREATE TABLE nama_tabel (nama_kolom tipe_kolom CONSTRAINT nama_costraint NOT NULL);

Deklarasi saat ALTER TABLE adalah:

ALTER TABLE nama_tabel MODIFY (nama_kolom tipe_kolom NOT NULL); atau

ALTER TABLE nama_tabel MODIFY (nama_kolom tipe_kolom CONSTRAINT nama_constraint NOT NULL);

b. Unique

Deklarasi saat CREATE TABLE adalah:

CREATE TABLE nama_tabel (nama_kolom tipe_kolom UNIQUE); atau

CREATE TABLE nama_tabel (nama_kolom tipe_kolom CONSTRAINT nama_constraint UNIQUE);

Deklarasi saat ALTER TABLE adalah:

CREATE TABLE nama_tabel ADD CONSTRAINT nama_constraint UNIQUE (nama_kolom);

c. Primary Key

Deklarasi Primary Key pada satu kolom saat CREATE TABLE adalah:

CREATE TABLE nama_tabel (nama_kolom tipe_kolom PRIMARY KEY); atau

CREATE TABLE nama_tabel (nama_kolom tipe_kolom CONSTRAINT nama_constraint PRIMARY KEY);

Deklarasi PRIMARY KEY pada beberapa kolom saat ALTER TABLE adalah:

ALTER TABLE nama_tabel ADD CONSTRAINT nama_constraint PRIMARY KEY (nama_kolom);

Deklarasi PRIMARY KEY pada beberapa kolom saat ALTER TABLE adalah:

ALTER TABLE nama_tabel ADD CONSTRAINT nama_constraint PRIMARY KEY (nama_kolom,...,nama_kolom_n);

d. Foreign Key

Deklarasi saat CREATE TABLE adalah :

CREATE TABLE nama_tabel (nama_kolom tipe_data,..., nama_kolom_n tipe_data, CONSTRAINT nama_constraint FOREIGN KEY (nama_kolom) REFERENCES nama_tabel_parent);

Deklarasi saat ALTER TABLE adalah :

ALTER TABLE nama_tabel ADD CONSTRAINT nama_constraint FOREIGN KEY (nama_kolom_n) REFERENCES nama_tabel_parent);

e. Check

Deklarasi saat CREATE TABLE adalah :

CREATE TABLE nama_tabel (nama_kolom tipe_kolom CHECK (syarat)); Atau

CREATE TABLE nama_tabel (nama_kolom tipe_kolom CONSTRAINT nama_constraint CHECK (syarat));

Deklarasi saat ALTER TABLE adalah :

ALTER TABLE nama_tabel ADD CONSTRAINT nama_constraint CHECK (syarat);

f. Default

Deklarasi saat CREATE TABLE adalah :

CREATE TABLE nama_tabel (nama_kolom tipe_data DEFAULT nilai_default);

Deklarasi saat ALTER TABLE adalah :

ALTER TABLE nama_tabel MODIFY nama_kolom tipe_data DEFAULT nilai _default;

Membuat Tabel Baru dan penerapan Constraint Primary Key, Not Null, Unique

create table mahasiswa (

no_mahasiswa char(8) CONSTRAINT PK_mhs PRIMARY KEY,

nama varchar2(50) CONSTRAINT nnull_mhs NOT NULL,

no_KTP varchar2(20) CONSTRAINT unq_mhs UNIQUE, jenis_kelamin CHAR(1),

usia number(3));

Contoh Penerapan Constraint Foreign Key dan Check :

create table nilai_mahasiswa (

no_mahasiswa char(8),

id_matakuliah varchar2(5),

nilai number(3) CONSTRAINT ck_nilai (nilai>0),

CONSTRAINT fk_nilai FOREIGN KEY (no_mahasiswa) REFERENCES mahasiswa(no_mahasiswa)) ;

SINTAKS JOIN

Two Table Inner Join

Traditional Joins
Nama Join	Sintaks	Contoh
Two Table Inner Join	SELECT , FROM , WHERE =	SELECT nama, nama_barang, b.id_barang FROM karyawan k, barang b WHERE k.id_barang = b.id_barang;
Three Table Inner Join	SELECT , FROM , WHERE = AND = ;	SELECT nama,nama_barang, b.id_barang, k.no_konsumen, jumlah FROM barang b, penjualan p, konsumen k WHERE b.id_barang = p.id_barang AND p.no_konsumen= k.no_konsumen ORDER BY b. id_barang;
Left Outer Join	SELECT , FROM , WHERE AND = (+);	SELECT nama,nama_barang, b.id_barang FROM karyawan k, barang b WHERE k.id_barang = b.id_barang(+);
Right Outer Join	SELECT , FROM , WHERE (+) = ;	SELECT nama,nama_barang, b.id_barang FROM karyawan k, barang b WHERE k.id_barang(+) = b.id_barang;
Self Join	SELECT , FROM , , WHERE = < alias.column_name> AND = ;	UPDATE barang SET nama_barang = 'UPDATE2' WHERE id_barang = ‘0061’; SELECT nama,nama_barang, b.id_barang, k.no_konsumen, jumlah FROM barang b, penjualan p, konsumen k WHERE b.id_barang = p.id_barang AND p.no_konsumen= k.no_konsumen