moudl2sbd

(c)2007, Budi Susanto

Modul #2Penggunaan DDL dan DML pada OracleXEPada modul pertama, Anda telah mengenal sedikit tentang OracleXE serta bagaimana manajemen user yang ada pada OracleXE, sekaligus Anda telah mencoba membuat tabel dan relasinya, baik menggunakan font end OracleXE maupun dari SQL*Plus. Pada modul ke dua ini, kita akan fokus pada pemakaian perintah DDL (Data Definition Language) dan DML (Data Manipulation Language) merupakan bahasa standard dari ANSI SQL. Diharapkan setelah menyelesaikan modul ini, Anda dapat mencapai beberapa tujuan pembelajaraan berikut: mampu menggunakan perintah CREATE, dan ALTER untuk pemeliharaan struktur fisik tabel data mampu menggunakan perintah INSERT, UPDATE, dan DELETE untuk pemeliharaan data mampu memahami perintah COMMIT, ROLLBACK dan SAVEPOINT untuk penentuan transaksi data mampu menggunakan perintah SELECT ... FROM ... WHERE dan aggregation function.

PersiapanUntuk dapat mengerjakan modul ini, ada satu hal yang perlu dilakukan terlebih dahulu, yaitu :Anda harus memiliki user account sendiri untuk melakukan percobaan ini. Jika belum memiliki, silahkan jalankan command prompt DOS, kemudian ketikkan perintah berikut :

prompt> set ORACLE_SID=XEprompt> sqlplus /nologSQL> conn / as sysdbaSQL> create user namauseranda identified by passwordanda quota 10M on users;SQL> alter user namauseranda quota 10M on system SQL> grant connect, create any table, create sequence to namauseranda;SQL> quit

Perintah DDLPerintah DDL adalah perintah yang dipergunakan untuk pembuatan dan pemeliharaan tabel fisik serta definisi relasi dan constraint lainnya. Oleh karena kita menggunakan OracleXE sebagai sistem basis data relational, maka perintah DDL yang akan diujicoba mengikuti karakteristik dari DDL yang disediakan oleh Oracle. Berikut adalah bentuk umum dari perintah CREATE TABLE :

CREATE TABLE namatable ( nama_field tipedata [constraint], ...);

Saat ini kita akan membuat dua tabel yang saling berelasi seperti yang ditunjukkan dengan diagram relasi pada gambar 2.1.

Gambar 2.1 Diagram Relasi Member-Visit

For Database System Practice at DWCU Only 1

Anggota

email (PK)passwordnamatelepon

Kunjungan

ID (PK)Anggota$email (FK)waktu_masukwaktu_keluar


Pertama-tama kita akan membuat tabel Anggota terlebih dahulu, karena tabel ini merupakan tabel yang memiliki kunci primer yang akan digunakan oleh tabel Kunjungan untuk relasinya. Untuk pembuatan tabel Anggota dapat dilakukan dari prompt SQL*Plus berikut :

prompt> sqlplus useranda/passwordSQL> create table Anggota (email char(50) constraint anggota_pk primary key,password char(50) not null,nama varchar2(50) not null,telepon char(20));Untuk melihat hasil dari pekerjaan Anda, silahkan ketikkan perintah berikut dari prompt SQL:

SQL> select table_name from user_tables;SQL> desc anggota;Berikutnya kita akan membuat tabel Kunjungan yang berhubungan dengan tabel Anggota :

SQL> create table Kunjungan (id number(10) constraint kunjungan_pk primary key,anggota$email char(50),wkt_masuk timestamp default to_date(sysdate, 'DD-MON-YY HH24:MI:SS'),wkt_keluar timestamp);SQL> alter table Kunjungan add constraint kunjungan_fk FOREIGN KEY (anggota$email) REFERENCES Anggota(email);

Untuk mengisi kolom ID dari tabel Kunjungan, kita akan menyiapkan sebuah objek sequence yang nantinya dapat kita panggil setiap saat melakukan penambahan data. Dengan memanggil nilai dari objek sequence, maka nilai yang akan tersimpan pada kolom ID akan selalu unique karena nilai yang dihasilkan dari objek sequence akan selalu bertambah untuk setiap kali pemanggilan. Untuk membuat objek sequence, kita dapat memberikan perintah berikut :

SQL> create sequence kunjungan_id_seq;anda dapat memeriksa objek sequence Anda dengan memberikan perintah berikut :

SQL> select * from user_objects where object_type='SEQUENCE';atau

SQL> select * from user_sequences;


Cobalah Anda periksa pekerjaan Anda seperti yang sudah dilakukan terhadap tabel Anggota!

Bagaimana caranya Anda dapat mengetahui semua object constraint Anda dari dynamic view USER_CONSTRAINTS? Constraint apa saja yang dibuatkan untuk Anda terkait dengan tabel yang sudah dibuat?


Dari tabel Anggota yang sudah kita definisikan, kita akan menambahkan dua field baru yang akan menyimpan jenis kelamin dan tanggal daftar. Untuk melakukan hal ini, Anda dapat memberikan perintah berikut dari prompt SQL :

SQL> alter table anggota add seks char(1) check (seks in ('L','P')) not null;SQL> alter table anggota add tgl_daftar date default sysdate not null;

Perintah DMLSetelah struktur tabel telah terbentuk, selanjutnya Anda dapat menggunakan perintah DML (Data Manipulation Language) untuk memanipulasi data yang terdapat pada sebuah tabel. Untuk menambahkan data baru ke sebuah tabel, Anda dapat memberikan perintah INSERT dengan bentuk umum sebagai berikut :

INSERT INTO Anggota VALUES (nilaiemail, nilaipassword, nilainama, nilaitelp, nilaiseks, nilaitgldaftar);

atau

INSERT INTO Anggota [(field_yang_akan_diisi)] VALUES (nilai_nilainya);

contoh:

INSERT INTO Anggota VALUES ('[email protected]', 'rocket', 'Joni Hari', '592-0646', 'L', DEFAULT);INSERT INTO Anggota (email, password, nama, seks) VALUES ('[email protected]', 'bullet', 'Tomy Lusiana', 'P');

Selanjutnya, silahkan Anda coba menambahkan 2 data berikut dengan perintah INSERT :

Email Password Nama Telepon Seks [email protected] arrow Sony Galang NULL P 10-MAR-07

email_anda passw_anda nama_anda telp_anda seks_anda DEFAULT

Untuk menambahkan data baru ke tabel Kunjungan, kita dapat melakukan dengan memberikan perintah berikut :

INSERT INTO Kunjungan VALUES (kunjungan_id_seq.nextval, '[email protected]', to_date('25-JUN-06 14:00:00', 'DD-MON-YY HH24:MI:SS'), to_date('25-JUN-06 17:30:00', 'DD-MON-YY HH24:MI:SS'), NULL, 'R');

Lakukan hal yang sama terhadap tabel Kunjungan dengan data-data sebagai berikut :

Anggota$email wkt_masuk wkt_keluar [email protected] 25/6/2006 06:00:00 PM 25/6/2006 08:00:00 PM [email protected] 26/6/2006 10:00:00 AM 26/6/2006 11:30:00 AM R


Lakukan penambahan kolom pada tabel Kunjungan untuk field berikut : * Keterangan varchar2(100)* Jns_kunjungan char(1) dengan nilai valid adalah 'R', 'I', 'K'


[email protected] 27/6/2006 09:00:00 AM 27/6/2006 10:00:00 AM [email protected] 27/6/2006 04:00:00 PM 27/6/2006 06:00:00 PM K

Perintah TransaksionalPada database Oracle, Anda dapat melakukan kontrol terhadap transaksi yang Anda lakukan. Yang dimaksud transaksi di sini adalah semua perintah DML yang Anda berikan ke database Oracle. Perlu diketahui bahwa perintah transaksional tidak berlaku untuk semua perintah DDL, artinya setelah Anda melakukan operasi CREATE TABLE misalnya, Anda tidak dapat melakukan pembatalan transaksi. Untuk membatalkan CREATE TABLE anda harus menggunakan perintah DDL lainnya, yaitu DROP TABLE.Ada dua perintah pokok pada transaksional, yaitu COMMIT dan ROLLBACK. Secara sederhana, perintah COMMIT digunakan untuk memberitahu kepada Oracle, bahwa semua transaksi yang telah dilakukan akan disimpan pada media database file. Sedangkan untuk ROLLBACK memberitahukan Oracle untuk membatalkan semua transaksi yang sudah dilakukan sampai waktu commit terakhir. Untuk membantu Anda memahami hal ini, silahkan lakukan percobaan berikut ini :

SQL> insert into Anggota values ('[email protected]', 'owdp', 'OWDP UKDW', NULL, 'L', DEFAULT);SQL> commit;SQL> select * from anggota;SQL> delete from anggota;SQL> select * from anggota;SQL> rollback;SQL> select * from anggota;

Pada lingkungan SQL*Plus, Anda dapat juga menggunakan perintah SAVEPOINT yang bermanfaat untuk meberi tanda pada beberapa titik transaksi yang Anda anggap perlu untuk diberi catatan. Dengan titik-titik catatan transaksi yang sudah dibuat, Anda dapat melakukan ROLLBACK pada satu titik transaksi tertentu. Silahkan lakukan percobaan berikut :

SQL> insert into Anggota values ('[email protected]', 'info', 'Bagian Informasi UKDW', '0274-563929', 'L', DEFAULT);SQL> savepoint tambah_info;SQL> insert into Anggota values ('[email protected]', 'tv', 'TV Kampus UKDW', '0274-563929', 'P', DEFAULT);SQL> savepoint tambah_tv;SQL> select * from anggota;SQL> delete from anggota;SQL> savepoint hapus_semua;SQL> update anggota set seks = 'L';SQL> select * from anggota;

cobalah Anda berikan perintah berikut :


Untuk Latihan:- ubahlah data telepon untuk anggota yang memiliki email [email protected] dengan '0274-512322'- ubahlah data tanggal daftar untuk anggota bernama Anda sendiri menjadi tanggal 1 MARET 2006- tambahkan data kunjungan untuk anggota bernama Anda sendiri dimana untuk data wkt_masuk menggunakan nilai default, wkt_keluar kosong dan jenis kunjungan adalah K.


SQL> ROLLBACK TO hapus_semua;(apa yang Anda dapatkan? Mengapa?)SQL> ROLLBACK TO tambah_info;(apa yang Anda dapatkan? Mengapa?)SQL> commit;SQL> select * from anggota;SQL> rollback;SQL> select * from anggota;(mengapa data Anda tidak dibatalkan?)

Query DataPada bagian ini kita akan lebih mendalami tentang perintah untuk pengambilan data dari tabel-tabel yang sudah kita definisikan. Pertama dan yang termudah adalah untuk mengambil semua data :

SQL> select * from anggota;SQL> select * from Kunjungan;

untuk melakukan ini, Anda dapat menggunakan SQL Command yang tersedia dari lingkungan Front End Oracle XE. Jika dari perintah dasar SELECT tersebut, Anda ingin melakukan penyaringan data berdasarkan suatu field dan data tertentu, Anda dapat meletakkan definisi kondisi penyaringan tersebut pada klausa WHERE setelah klausa FROM. Sebagai contoh :

tampilkan semua anggota yang berinisial JONI

SQL> select * from anggota where nama like 'JONI%';

tampilkan semua anggota yang memiliki email dari domain yahoo.com dan berjenis kelamin P.

SQL> select * from anggota where email like '%yahoo.com%' AND seks = 'P';

atau

SQL> select * from anggota where trim(email) like '%yahoo.com' AND seks = 'P';

Catatan: fungsi trim() di atas akan membuang semua spasi di awal dan di akhir dari email. Hal ini tidak perlu dilakukan untuk tipe data varchar.

Tampilkan lamanya waktu kunjungan yang ada.

SQL> SELECT TO_CHAR(wkt_masuk,'MM-DD-YYYY HH24:MI:SS') Masuk,TO_CHAR(wkt_keluar,'MM-DD-YYYY HH24:MI:SS') Keluar, wkt_keluar wkt_masuk selisihFROM Kunjungan;

Tampilkan semua anggota yang datang pada tanggal 26 Juni 2006.


Cobalah untuk melakukan query dengan nilai 'joni%'. Bagaimana hasilnya? Silahkan diskusikan dengan asisten dan teman Anda!


SQL> SELECT * from Kunjungan WHERE wkt_masuk like '26-JUN-06';

Dalam sebuah SQL, Anda juga dapat melakukan pemanggilan aggregation function seperti MAX, MIN, SUM, AVG, COUNT, VARIANCE, STDDEV. Semua anggregation function tersebut harus dapat diletakkan pada klausa SELECT dan HAVING. Berikut adalah struktur perintah dasar SELECT untuk aggregation :

SELECT field, field, field, ...FROM namatabel[WHERE kondisi ][HAVING kondisi ][GROUP BY daftar_field_non_aggregate]Sebagai contoh dapat diberikan perintah query berikut :

tampilkan jumlah anggota yang memiliki domain email yahoo.com!

SQL> select count(email) from anggota where trim(email) like '%yahoo.com';

tampilkan tanggal terakhir yang mendaftar !

SQL> select max(tgl_daftar) from anggota;

Lakukan percobaan untuk permintaan berikut : tampilkan kunjungan anggota yang lama kunjungannya tercepat dan terlama! Jika masih ada waktu, silahkan mencoba latihan yang diberikan oleh asisten Anda!


Lakukan query untuk beberapa pertanyaan berikut :* tampilkan semua Insidental (I) kunjungan beserta tanggal kunjungan dan lama kunjungannya.* tampilkan semua anggota yang tidak memiliki data telepon. (gunakan IS NOT NULL)* tampilkan semua kunjungan yang selesai setelah pukul 14:00!

moudl2sbd

Documents