Use & Sceme (oracle) - part 1

Materi 2 : User dan Schema

Setiap user yang akan mengakses database Oracle harus memiliki user account atau biasa juga disebut user name. Pada database Oracle user account merupakan bagian dari database. Berbeda dengan konsep user yang ada di RDBMS lain seperti My SQL atau SQL Server 2000, pada kedua RDBMS tersebut user ada diluar database. Ilustrasi perbedaan konsep user antara database Oracle dengan kedua database tersebut bisa dilihat pada gambar berikut :
Dengan konsep seperti diatas, tentunya user account pada suatu database baru bisa dibuat setelah database dibuat. Walaupun demikian ada beberapa user yang dibuat bersamaan dengan proses pembuatan database, user yang dibuat bersamaan dengan proses pembuatan databsae ini disebut pre-defined user . Contoh dari pre-defined user adalah user SYS dan SYSTEM. Jadi kedua user inilah yang akan Anda gunakan untuk melakukan manajemen database jika database baru dibuat. Sebaiknya buatlah user account baru untuk menyimpan tabel, index dan objek database lain jika Anda ingin membangun aplikasi.
Membuat User Account
Jika database masih baru maka yang bisa digunakan untuk membuat user adalah user SYS atau SYSTEM. Jadi sebelum membuat user login ke database dengan menggunakan salah satu dari kedua user account tersebut. Untuk login ke database gunakan SQL*Plus, pada form login isikan username dan password, misal Anda akan menggunakan user SYSTEM :
Setelah Anda berhasil login, buat user dengan perintah CREATE USER. Pada contoh ini buatlah user budi dengan password budipass, sekaligus beri quota pemakaian space maksimum sebesar 10 Mb. Untuk sementara ikuti perintah ini dulu. Manajemen space untuk user akan dibahas di bagian tersendiri.
SQL> create user BUDI
2 identified by BUDIPASS
3 default tablespace USERS
4 quota 10 m on USERS;

User created.
Jika perintah diatas berhasil Anda lakukan berarti didalam database Anda sudah ada user budi.
Hubungan User dan Schema
Ketika suatu user berhasil dibuat maka atomatis akan dibuat schema dengan nama yang sama dengan nama user di dalam database. Satu user hanya akan dihubungkan dengan satu schema. Jika sebelumnya Anda membuat user budi maka ketika user budi login ke database akan dihubungkan secara atomatis ke schema budi. Selanjutnya semua objek(seperti abel, index dan cluster) yang dibuat oleh user budi akan dikelompokkan didalam schema budi. Ketika user budi dihapus maka schema budi dan semua objek yang ada didalamnya juga ikut dihapus.
Dari gambaran diatas bisa kita simpulkan sebenarnya user sama dengan schema.
Memberi ijin Login ke database
Ketika user sudah dibuat, tidak atomatis user bisa login ke database. Agar bisa login ke database user harus diberi ijin/privilege CREATE SESSION. Tentang Manajemen Privilege akan dibahas pada bagian tersendiri. Berilah ijin CREATE SESSION pada user budi dengan cara berikut :
Pastikan Anda masih login sebagai user SYSTEM
SQL> show user USER is "SYSTEM"
Berikan privilege dengan perintah GRANT … TO
SQL> grant CREATE SESSION to BUDI;
Grant succeeded.
Lakukan login ke database dengan user budi
SQL> connect BUDI
Enter password: ********
Connected.
SQL> show user
USER is "BUDI"
Anda sudah berhasil membuat user dan menggunakan user account tersebut untuk masuk ke database. Tetapi ingat, user budi hanya bisa login ke database, belum bisa melakukan kegiatan apapun, karena user budi hanya diberi ijin CREATE SESSION.
Materi selanjutnya Tabel dan Constraints

___________________________________


Materi : Relasional Database dan RDBMS


Sebelum membahas berbagai jenis perintah SQL akan lebih baik kalao kita bicara tentang Relational Database dan Relational Database Management System atau biasa di kenal dengan RDBMS.
Relational Database sebenarnya adalah salah satu konsep penyimpanan data, sebelum konsep database relasional muncul sebenarnya sudah ada dua model database yaitu Network Database dan Hierarchie Database. Dalam database relasional, data disimpan dalam bentuk relasi atau tabel dua dimensi, dan antar tabel satu dengan tabel lainnya terdapat hubungan atau relationship, sehingga sering kita baca diberbagai literatur, database didefinisikan sebagai “kumpulan dari sejumlah tabel yang saling hubungan atau keterkaitan”. Nah, kumpulan dari data yang diorganisasikan sebagai tabel tadi disimpan dalam bentuk data elektronik di dalam hardisk komputer. Untuk membuat struktur tabel, mengisi data ke tabel, mengubah data jika diperlukan dan menghapus data dari tabel diperlukan software. Software yang digunakan membuat tabel, isi data, ubah data dan hapus data disebut Relational Database Management System atau dikenal dengan singkatan RDBMS sedangkan perintah yang digunakan untuk membuat tabel, isi, ubah dan hapus data disebut perintah SQL yang merupakan singkatan dari Structure Query Language. Jadi, setiap software RDBMS pasti bisa digunakan untuk menjalankan perintah SQL.
Sebenarnya fungsi RDBMS bukan cuma buat tabel, isi data, ubah dan hapus data, untuk manajemen data dalam skala besar dan agar bisa mendukung proses bisnis yang kontinyu dan real time suatu RDBMS dituntut untuk mempunyai kemampuan manajemen user dan keamanan data, backup dan recovery data serta kemampuan lainnya yang berkaitan dengan kecepatan pemrosesan data (performance).
Salah satu software RDBMS yang ada dipasaran saat ini dan cukup banyak digunakan adalah Oracle Database.
Berinteraksi dengan Database Oracle
Untuk mengakses data yang ada di database digunakan perintah SQL, perintah-perintah SQL ini ditulis atau diinput dengan tools yang sudah disediakan oleh Oracle yaitu SQL*PLus, iSQL*Plus dan SQL Developer.Perintah SQL dikelompokkan berdasarkan fungsinya sebagai berikut:
Perintah untuk pendefinisian/pembuatan objek (Data Definition Language / DDL)
• CREATE
• ALTER
• RENAME
• DROP
• TRUNCATE
Perintah untuk menampilkan data (Data Retrieval)
• SELECT
Perintah untuk memanipulasi data (Data Manipulation Language /DML)
• INSERT
• UPDATE
• DELETE
• MERGE
Perintah untuk mengontrol transaksi (Transaction Control Language /TCL)
• COMMIT
• ROLLBACK
• SAVEPOINT
Perintah untuk mengatur wewenang atau privilege ( Data Control Language /DCL)
• GRANT
• REVOKE
Sekian bahasan tentang Relational Database dan RDBMS



______________________________________
Aggregasi Data dengan GROUP BY, ROLLUP dan CUBE

Tulisan ini untuk Ugi yang meminta bahasan tentang ROLLUP dan CUBE. Yang disampaikan disini hanya dasarnya saja, silahkan dikembangkan lagi.
Jenis-jenis Group Function
• AVG : Mencari rata-rata
• COUNT : Menghitung jumlah baris
• MAX : Mencari nilai maksimum
• MIN : Mencari nilai minimum
• STDDEV : Mencari standard deviasi
• SUM : Mencari nilai total
• VARIANCE : Mencari variance
Group function akan memproses banyak data dan memberikan hasil satu atau beberapa hasil sesuai dengan pengelompokan yang dilakukan. Untuk pengelompokan digunakan klausa GROUP BY.

Dari tabel EMP yang dimiliki SCOTT terlihat daftar seperti berikut :
SQL> select deptno,empno,ename,sal
2 from emp
3 order by deptno;
DEPTNO EMPNO ENAME SAL
———- ———- ———- ———-
10 7782 CLARK 2460
10 7839 KING 5010
10 7934 MILLER 1310
20 7566 JONES 2985
20 7902 FORD 3010
20 7876 ADAMS 1110
20 7369 SMITH 810
20 7788 SCOTT 6010
30 7521 WARD 1260
30 7844 TURNER 1510
30 7499 ALLEN 1610
30 7900 JAMES 960
30 7698 BLAKE 2860
30 7654 MARTIN 1260
14 rows selected.
Misalkan Anda ingin mencari salary tertinggi dari daftar diatas, gunakan function MAX.
SQL> select max(sal)
2 from emp;
MAX(SAL)
———-
6010
Function MAX akan memperoses semua baris data yang ada (14 row) untuk menghasilkan satu nilai maksimum (6010). Nilai 6010 berarti salary tertinggi dalam perusahaan tersebut. Jika Anda menginginkan salary tertinggi di setiap department (DEPTNO) tambahkan klausa GROUP BY deptno.
SQL> select DEPTNO, MAX(SAL)
2 from EMP
3 GROUP BY DEPTNO;
DEPTNO MAX(SAL)
———- ———-
30 2860
20 6010
10 5010
Dalam pemakaian GROUP BY, kolom yang ada di klausa GROUP BY tidak harus dimunculkan di klausa SELECT. Sebagai contoh, kolom DEPTNO tidak harus ada di klausa SELECT seperti berikut.
SQL> select MAX(SAL)
2 from EMP
3 GROUP BY DEPTNO;
MAX(SAL)
———-
2860
6010
5010
Tetapi jika kolom DEPTNO ada di klausa SELECT tetapi tidak digunakan sebagai pengelompok atau disebutkan di klausa GROUP BY akan error.
SQL> select DEPTNO, MAX(SAL)
2 from EMP;
select DEPTNO, MAX(SAL)
*
ERROR at line 1:
ORA-00937: not a single-group group function
Silahkan coba dengan group function yang lain.
HAVING
Jika dalam single function, untuk menyeleksi data Anda menggunakan klausa WHERE. Sedangkan untuk menyeleksi data dimana pembandingnya merupakan group function digunakan HAVING. Sebagai contoh, tampilkan Department yang rata-rata salarynya diatas 4000.
Kita lihat rata-rata gaji di setiap Department
SQL> SELECT DEPTNO, ROUND(AVG(SAL),0) AS RATA2
2 FROM EMP
3 GROUP BY DEPTNO;
DEPTNO RATA2
———- ———-
30 1577
20 2785
10 2927
Jika yang ingin ditampilkan adalah department yang rata-rata salarynya diatas 2500 berarti yang dijadikan pembanding adalah AVG(SAL) karena ini merupakan group function maka harus digunakan HAVING untuk menyeleksinya. HAVING boleh diletakkan sebelum atau sesudah GROUP BY
Perintahnya adalah sebagai berikut:
SQL> SELECT DEPTNO, ROUND(AVG(SAL),0) AS RATA2
2 FROM EMP
3 GROUP BY DEPTNO
4 HAVING AVG(SAL) > 2500;
DEPTNO RATA2
———- ———-
20 2785
10 2927
Atau
SQL> SELECT DEPTNO, ROUND(AVG(SAL),0) AS RATA2
2 FROM EMP
3 HAVING AVG(SAL) > 2500
4 GROUP BY DEPTNO;
DEPTNO RATA2
———- ———-
20 2785
10 2927
• GROUP BY dengan ROLLUP dan CUBE
ROLLUP dan CUBE adalah group function yang biasa digunakan untuk keperluan Data Warehousing.
Untuk memahami operasi ROLLUP dan CUBE kita buat tabel PENDUDUK sbb:
create table penduduk
(id number(3) primary key,
nama varchar2(15),
jk char(1) check (JK in (’L',’P')),
KAB VARCHAR2(15),
KEC VARCHAR2(15));
Dan isi datanya sehingga mempunyai data sbb:

Anda diminta untuk menampilkan data jumlah penduduk per kecamatan dan perkabupaten.
Untuk keperluan ini Anda bisa menggunakan GROUP BY KAB, KEC
SQL> SELECT KAB, KEC, COUNT(ID)
2 FROM PENDUDUK
3 GROUP BY KAB, KEC;
KAB KEC COUNT(ID)
————— ————— ———-
KAB 1 KEC 1 8
KAB 1 KEC 2 6
KAB 2 KEC 2 10
KAB 2 KEC 1 6
Disini terlihat, jika menggunakan GROUP BY maka tidak ditampilkan sub total jumlah penduduk di KAB 1 dan KAB 2 dan Total jumlah penduduk. Agar bisa menampilkan sub total per kabupaten dan total jumlah penduduk, Anda harus menggunakan ROLLUP seperti dbawah ini :
SQL> SELECT KAB, KEC, COUNT(ID)
2 FROM PENDUDUK
3 GROUP BY ROLLUP(KAB, KEC)
4 ORDER BY KAB, KEC;
KAB KEC COUNT(ID)
————— ————— ———-
KAB 1 KEC 1 8
KAB 1 KEC 2 6
KAB 1 14
KAB 2 KEC 1 6
KAB 2 KEC 2 10
KAB 2 16
30
Anda bisa permanis tampilan dengan cara sbb:

Dengan ROLLUP bisa ditampilkan nilai total dari setiap group.
Bagaimana dengan CUBE ?. CUBE biasa digunakan untuk membuat cross-tabulation (sub total lebih dari satu dimensi).
Misalkan Anda diminta untuk membuat tabulasi data penduduk berdasarkan jenis kelamin (JK) di setiap Kabupaten dan setelah itu tampilkan jumlah total penduduk laki-laki (L) dan perempuan (P). Anda bisa menyelesaikan hal ini dengan menggunakan CUBE sepeti berikut.

Anda tidak usah pusing membaca hasil query diatas, kalau disederhanakan sebenarnya hasil query diatas adalah tabulasi sepeti berikut:

Demikian penjelasan dasar tentang aggregasi data dengan GROUP BY, ROLLUP dan CUBE
______________________________________________________


Lebih Lanjut dengan JOIN

Saran: Sebaiknya baca dulu materi yang membahas tentang dasar JOIN.
Pada materi 9 ini membahas tentang berbagai jenis JOIN baik dengan syntax Oracle maupun JOIN dari standard SQL ANSI, seperti :
• Equi Join
• Non Equi Join
• Outer Join
• Self Join
• Natural Join
• Menggunakan clausa USING dalam Natural Join
• Menggunakan clausa ON dalam Natural Join
• Left Outer Join
• Right Outer Join
• Full Outer Join
Tabel‐tabel yang digunakan :
Tabel PEGAWAI, BAGIAN ditambah juga dengan tabel lain yang ada di schema SCOTT dan HR
DROP TABLE PEGAWAI;
DROP TABLE BAGIAN;
CREATE TABLE PEGAWAI
(ID_PEG NUMBER(2) PRIMARY KEY,
NAMA CHAR(10),
KODE_BAG NUMBER(2)
);
INSERT INTO PEGAWAI VALUES(1,’AMRA’,20);
INSERT INTO PEGAWAI VALUES(2,’BUDI’,10);
INSERT INTO PEGAWAI VALUES(3,’CICI’,20);
INSERT INTO PEGAWAI VALUES(4,’DEWI’,20);
INSERT INTO PEGAWAI VALUES(5,’EDWIN’,30);
INSERT INTO PEGAWAI VALUES(6,’FIAN’,20);
INSERT INTO PEGAWAI VALUES(7,’GALI’,40);
INSERT INTO PEGAWAI VALUES(8,’HARI’,10);
INSERT INTO PEGAWAI VALUES(9,’INDRA’,30);
INSERT INTO PEGAWAI VALUES(10,’JOHAN’,40);
_______________________________

Menampilkan Data dari beberapa Tabel (JOIN)

Data-data dalam suatu database disimpan dalam bentuk tabel dan dalam satu database sudah pasti terdiri dari banyak tabel. Untuk menampilkan informasi yang sederhana kita cukup mengambil data dari satu tabel, tetapi untuk memberikan informasi yang lengkap kita harus mengambil data dari beberapa tabel. Melakukan query dengan mengambil data dari beberapa tabel ini disebut JOIN.
Syarat yang harus dipenuhi untuk melakukan join antara dua tabel.
• Pada kedua tabel harus ada kolom dengan nilai yang sesuai
• Gunakan kondisi join untuk menghubungkan kedua tabel tersebut
Untuk memperjelas tentang join, buatlah tabel PEGAWAI dan BAGIAN dan isi kedua tabel tersebut dengan data seperti dibawah ini, gunakan SQL*Plus dan connect sebagai user HR
SQL> CONNECT HR/HR
CREATE TABLE PEGAWAI
(ID_PEG NUMBER(2) PRIMARY KEY,
NAMA CHAR(10),
KODE_BAG NUMBER(2)
);
INSERT INTO PEGAWAI VALUES(1,’AMRA’,20);
INSERT INTO PEGAWAI VALUES(2,’BUDI’,10);
INSERT INTO PEGAWAI VALUES(3,’CICI’,20);
INSERT INTO PEGAWAI VALUES(4,’DEWI’,20);
INSERT INTO PEGAWAI VALUES(5,’EDWIN’,30);
INSERT INTO PEGAWAI VALUES(6,’FIAN’,20);
INSERT INTO PEGAWAI VALUES(7,’GALI’,40);
INSERT INTO PEGAWAI VALUES(8,’HARI’,10);
INSERT INTO PEGAWAI VALUES(9,’INDRA’,30);
INSERT INTO PEGAWAI VALUES(10,’JOHAN’,40);
CREATE TABLE BAGIAN
(KODE NUMBER(2) PRIMARY KEY,
NAMA_BAG CHAR(10)
);
INSERT INTO BAGIAN VALUES(10,’SYS.ADMIN’);
INSERT INTO BAGIAN VALUES(20,’PROGRAMMER’);
INSERT INTO BAGIAN VALUES(30,’ANALIS’);
INSERT INTO BAGIAN VALUES(40,’DBA’);
INSERT INTO BAGIAN VALUES(50,’NETWORKING’);
Tampilkan data dari tabel PEGAWAI
SELECT * FROM PEGAWAI;
ID_PEG NAMA KODE_BAG
———- ———- ———-
1 AMRA 20
2 BUDI 10
3 CICI 20
4 DEWI 20
5 EDWIN 30
6 FIAN 20
7 GALI 40
8 HARI 10
9 INDRA 30
10 JOHAN 40
Tampilkan data dari tabel BAGIAN
SELECT * FROM BAGIAN;
KODE NAMA_BAG
———- ———-
10 SYS.ADMIN
20 PROGRAMMER
30 ANALIS
40 DBA
50 NETWORKING
Dari kondisi diatas, ketika kita menampilkan data pegawai dari tabel PEGAWAI informasi yang ditampilkan kurang jelas, karena tabel PEGAWAI hanya menyimpan kolom KODE_BAG. Bagaimana agar ketika menampilkan data pegawai yang dimunculkan adalah nama bagian tempat pegawai ditempatkan. Untuk menampilkan informasi seperti itu tidak cukup hanya dari tabel PEGAWAI, kita harus melibatkan juga tabel BAGIAN karena nama bagian tersimpan di kolom NAMA_BAG pada tabel BAGIAN. Jadi kita harus melakukan JOIN antara tabel PEGAWAI dan BAGIAN. Bagaimana caranya ?.
Syntax JOIN:

Untuk join antara tabel PEGAWAI dan BAGIAN perintahnya seperti dibawah ini:
Sebenarnya kolom PEGAWAI.KODE_BAG dan BAGIAN.KODE_BAG tidak harus ditampilkan. Kedua kolom ini bisa dihilangkan untuk menyederhanakan tampilan. Kenapa pada perintah diatas kok tetap ditampilkan ?. Hal ini hanya untuk memperjelas bahwa penghubung kedua tabel adalah kolom KODE_BAG yang ada di tabel PEGAWAI dan BAGIAN.
Ketik ulang perintah join diatas dengan menghilangkan kolom KODE_BAG.

Demikian penjelasan dasar tentang JOIN. Lebih detail tentang join dan berbagai jenis join yang ada materinya sedang dipersiapkan.
Terimakasih
_________________________________________

Single Row Function

Oracle menyediakan banyak function untuk melakukan manipulasi data. Function yang ada di Oracle dibagi dalam dua kategori yaitu Single-row function dan Multi-row function atau Group Function. Perbedaan kedua kelompok function itu adalah pada jumlah data masuk dan jumlah data hasil function. Single row function memproses satu row data dan hasilnya juga satu row sedangkan Multi row atau Group function data yang diproses banyak sedangkan row yang dihasilkan satu atau lebih sedikit jumlah row-nya dari row data yang diproses. Ilustrasi perbedaan mendasar dari kedua kelompok function disajikan dalam gambar berikut :
Penjelasan:
Single-row function : setiap satu row sumber LAST_NAME, misalnya Raphaely diproses dengan function UPPER menjadi RAPHAELY
Multi-row function :Semua data 22000, 6200, 5800, 5600, 5200, 5000 ada 5 data diproses dengan SUM menjadi satu baris data 49800
Kedua kelompok function ini tidak bisa saling menggantikan, artinya ada situasi yang harus kita selesaikan dengan single-row dan ada situasi yang memang harus diselesaikan dengan group function.
Untuk lebih jelasnya tentang Single-row function download materi di bagian Download Materi. Untuk Multi-row function akan dibahas pada materi lain.
Terimakasih

________________________________________________________
Menyeleksi dan Mengurutkan Data

Prasyarat :
- Anda sudah membaca dan memahami materi 5 yaitu tentang cara menampilkan data
Menyeleksi Data
Dengan perintah SELECT yang sederhana seperti sebelumnya, maka semua baris data akan ditampilkan. Terkadang kita perlu hanya menampilkan data dengan kriteria tertentu saja. Misalkan menampilkan data pegawai yang ada di kode bagian 12 atau menampilkan pegawai dengan gaji dibawah Rp. 3.000.000,-. Untuk memenuhi kebutuhan tersebut pada perintah SELECT perlu ditambahkan klausa WHERE yang bisa kita gunakan untuk menambahkan kriteria atau kondisi data yang akan ditampilkan. Syntax perintah SELECT menjadi sebagai berikut:

Jalankan perintah dua berikut ini dan perhatikan hasil outputnya.
• Perintah ini hanya menampilkan data pegawai dengan KODE_BAG=12
SQL> SELECT *
2 FROM pegawai
3 WHERE kode_bag=12;
• Perintah ini hanya menampilkan data pegawai dengan GAJI dibawah 3000000.
SQL> SELECT *
2 FROM pegawai
3 WHERE gaji < 3000000;
Semoga anda sudah semakin memahami cara menyeleksi data dengan perintah SQL.
Selanjutnya, jika kita perhatikan pada klausa WHERE, kriteria yang kita berikan berupa ekspresi yang terdiri dari nama_kolom, operator pembanding dan nilai_kriteria. Untuk komponen kriteria tersebut dijelaskan sebagai berikut:
• nama_kolom
adalah nama kolom yang terdapat pada tabel yang akan ditampilkan datanya. Seperti kolom NIP, NAMA, JK, EMAIL, GAJI dan KODE_BAG pada tabel PEGAWAI.
• operator_pembanding
adalah operator pembanding tunggal seperti = (sama dengan), > (lebih besar), < (lebih kecil), >= (lebih besar sama dengan), <= (lebih kecil sama dengan) dan <> (tidak sama dengan) atau operator pembanding jamak seperti IN, LIKE dan BETWEEN…AND…Operator pembanding juga bisa dikombinasikan dengan beberapa fungsi yang akan dijelaskan pada bagian lain.
• nilai_pembanding
adalah nilai yang kita tentukan. Nilai yang kita isi ini harus sesuai tipe datanya dengan kolom yang kita bandingkan. Jika kolom bertype data karakter atau date(tanggal) maka nilai harus kita apit dengan tanda kutip tunggal, contohnya adalah WHERE nama=’YULI’
Sebagai latihan :
• tampilkan pegawai dengan nama TONO
• tampilkan pegawai dengan NIP 1302
Operator BETWEEN…AND…
Operator BETWEEN..AND.. digunakan untuk menampilkan data yang berada dalam interval nilai tertentu. Untuk menggunakan operator ini harus ditentukan nilai batas bawah dan batas atas. Sebagai contoh, tampilkan pegawai yang gajinya berada dalam interval 5000000 sampai 7000000, maka perintahnya ditulis seperti berikut:
SELECT NIP,NAMA,GAJI
FROM PEGAWAI
WHERE GAJI BETWEEN 5000000 AND 7000000;
Operator IN
Jika dengan operator = (sama dengan) nilai pembanding yang bisa diberikan cuma satu nilai, seperti pegawai yang gajinya 3000000 maka ditulis (WHERE GAJI = 3000000) sehingga yang ditampilkan adalah hanya pegawai yang gajinya 3000000. Bagaimana jika ingin menampilkan pegawai yang gajinya 2000000, 3000000 dan 5000000 dengan satu perintah SELECT. Untuk kasus seperti ini gunakanlah operator IN. Perintahnya ditulis seperti berikut:
SELECT NIP,NAMA,GAJI
FROM PEGAWAI
WHERE GAJI IN (2000000,3000000,5000000);
Operator LIKE
Operator LIKE digunakan untuk menampilkan data yang sesuai dengan format tertentu. Untuk menentukan format baik untuk data karakter maupun numerik digunakan wildcard _ (underscore) dan % (persen)
• _ (underscore) format untuk mewakili satu karakter
• % (persen) format untuk mewakili beberapa karakter
Contoh:
Tampilkan pegawai yang Namanya diawali dengan hurus E.
perintahnya :
SELECT NIP,NAMA
FROM PEGAWAI
WHERE NAMA LIKE ‘E%’;
Tampilkan pegawai yang karakter kedua dari namanya adalah huruf I.
perintahnya:
SELECT NIP,NAMA
FROM PEGAWAI
WHERE NAMA LIKE ‘_I%’;
Cobalah dengan kombinasi format lainnya.
Operator Logika
Kita memerlukan logika operator dalam menyeleksi data jika kondisi atau kriteria yang kita inginkan lebih dari satu. Operator logika berfungsi untuk menghubungkan kondisi-kondisi tersebut. Operator logika yang bisa digunakan seperti tertera di bawah ini.

Dimana operator logika diletakkan ?. Tetap di klausa WHERE juga tetapi diletakkan antara dua kondisi. Lebih jelasnya lihat sysntax dibawah ini.

• Operator AND
Dengan operator AND, data yang akan dimunculkan adalah data-data yang memenuhi kedua kondisi. Sebagai contoh akan ditampilkan data pegawai yang kode_bagian=11 dan gajinya diatas 3000000, perintahnya adalah sebagai berikut:
SELECT * FROM PEGAWAI
WHERE KODE_BAG=11 AND GAJI > 3000000;
• Operator OR
Data akan ditampilkan jika minimal memenuhi salah satu kondisi. Sebagai contoh akan ditampilkan data pegawai yang kode_bagian=11 atau pegawai gajinya diatas 3000000, perintahnya adalah sebagai berikut:
SELECT * FROM PEGAWAI
WHERE KODE_BAG=11 OR GAJI > 3000000;
• Operator NOT
Data akan ditampilkan jika tidak memenuhi kondisi. Operator NOT berfungsi untuk menegasikan kondisi. Sebagai contoh, tampilkan pegawai yang bukan KODE_BAG 11, perintahnya sebagai berikut:
SELECT * FROM PEGAWAI
WHERE NOT (KODE_BAG=11);
ORDER BY
Klausa ORDER BY digunakan untuk melakukan pengurutan data berdasarkan kolom tertentu. ORDER BY diletakkan di akhir dari perintah SELECT. Syntax perintah SELECT dengan ORDER BY menjadi seperti berikut:

Keterangan :
• ASC : mengurutkan dari kecil ke besar
• DESC : mengurutkan dari besar ke kecil
• Defaultnya adalah ASC
Tulis perintah-perintah berikut untuk lebih memahami pengurutan data
SQL> SELECT * FROM PEGAWAI
2 ORDER BY NIP;
SQL> SELECT * FROM PEGAWAI
2 ORDER BY NAMA;
SQL> SELECT * FROM PEGAWAI
2 ORDER BY GAJI DESC;
• Sorting berdasarkan kolom ALIAS
SQL> SELECT NIP, NAMA, (GAJI*0.5) AS “BONUS”
2 FROM PEGAWAI
3 ORDER BY BONUS DESC;
• Sorting lebih dari satu kolom
SQL> SELECT KODE_BAG, NIP, NAMA, GAJI
2 FROM PEGAWAI
3 ORDER BY KODE_BAG, GAJI;
SQL> SELECT KODE_BAG, NIP, NAMA, GAJI
2 FROM PEGAWAI
3 ORDER BY KODE_BAG, GAJI DESC;
Sekian dan Terimakasih


No Comments Enjoyed this post? Subscribe via RSS. Thanks for visiting!
Menampilkan Data dengan SELECT
Written by Bambang Sutejo on Sep 11th, 2007 | Filed under: Fundamental SQL
Prasyarat :
- Anda sudah selesai mempelajari dan menjalankan script yang ada di Materi 4
Setelah data kita input ke tabel, sekarang saatnya menampilkan data. Untuk menampilkan data yang pernah kita input digunakan perintah SELECT. Syntax Perintah SELECT yang sederhana adalah sebagai berikut :

Terdiri dari dari dua klausa, yaitu klausa SELECT yang menentukan kolom apa yang akan ditampilkan dan klausa FROM yang menentukan dari tabel data akan diambil.
Cara menulis perintah SQL
• Perintah SQL not case sensitif, artinya perintah SQL bisa ditulis dengan huruf kapital atau huruf kecil
• Perintah SQL bisa ditulis dalam satu baris atau dalam beberapa baris
• Setiap perintah SQL diakhiri dengan tanda titik koma atau semicolon “;”
• Keyword tidak boleh disingkat atau ditulis pada baris yang berbeda
• Untuk memudahkan pembacaan sebaiknya setiap klausa ditulis pada baris yang berbeda
Sebagai contoh, tampilkan data pegawai NIP, NAMA, GAJI dan KODE_BAG, maka perintahnya adalah sebagai berikut :

Bagaimana jika akan menampilkan data dari semua kolom yang ada ?. Ada dua cara yang bisa anda lakukan yaitu dengan menuliskan semua kolom pada perintah SELECT seperti ini :
SQL> SELECT NIP, NAMA, JK, EMAIL, GAJI, KODE_BAG
2 FROM PEGAWAI;
atau ganti semua kolom dengan tanda asterik “*” setelah SELECT seperti ini :
SQL> SELECT *
2 FROM PEGAWAI;
Hasil kedua perintah diatas adalah sama.
Ekspresi Aritmatika
Pada perintah SQL kita bisa menggunakan ekspresi aritmatik dengan menggunakan Operator Aritmatika seperti di bawah ini.

Jalankan perintah berikut ini dan perhatikan pada kolom GAJI+500000:
SELECT NIP,NAMA,GAJI,GAJI+500000
FROM PEGAWAI;
Cobalah dengan operator yang lainnya.
Operator precedence
Jika dalam satu ekspresi terdapat banyak operator, maka urutan pengerjaannya adalah *, /, +, - (kali, bagi, tambah, kurang) dengan aturan sebagai berikut:
• Perkalian dan pembagian akan dikerjakan lebih dulu dari penjumlahan dan pengurungan
• Operator dengan prioritas yang sama akan dikerjakan dari sisi kiri ke kanan
• Untuk menentukan urutan pengerjaan sebaiknya gunakan tanda kurung
Jalankan dua perintah berikut:
SELECT NIP,NAMA,GAJI,12*GAJI+500000
FROM PEGAWAI;
SELECT NIP,NAMA,GAJI,12*(GAJI+500000)
FROM PEGAWAI;
Kolom Alias
• Kolom alias digunakan untuk menggantikan judul kolom
• Biasa digunakan untuk kolom-kolom yang ada ekspresinya
• Ditulis sesudah kolom yang akan diganti judul kolomnya, bisa juga digunakan keyword AS antara nama kolom dengan kolom alias
• Jika kolom alias terdapat karakter space atau karakter khusus lainnya maka apit kolom alias dengan tanda kutip ganda
Contoh penulisan kolom alias
SELECT NIP,NAMA,GAJI,12*GAJI+500000 AS Total
FROM PEGAWAI;
SELECT NIP,NAMA,GAJI,12*GAJI+500000 AS “Total Gaji”
FROM PEGAWAI;
Sampai disini penjelasan tentang cara menampilkan data dari tabel. Materi selanjutnya adalah bagaimana menyeleksi data yang akan ditampilkan dan mengurutkan data.
Terima kasih

__________________________________________________________
Input Data ke dalam tabel

Prasyarat :
• Anda sudah membaca dan menjalankan perintah-perintah yang ada di Materi 3 tentang Membuat Tabel dan Mendefinisikan Constraint
Perintah INSERT
Untuk mengisi data ke tabel digunakan perintah INSERT. Syntax perintah INSERT adalah sebagai berikut :
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);
Sebelum melakukan pengisian data dengan perintah INSERT, kita harus lihat dulu strukutr tabel yang akan kita isi, karena urutan kolom dalam perintah INSERT sangat penting.
Lakukan koneksi ke database dengan user BUDI
SQL> connect budi
Enter password: ******
Connected.
SQL>
Lihat, tabel apa saja yang dimiliki oleh user BUDI
SQL> SELECT table_name
2 FROM user_tables;
TABLE_NAME
————————-
PEGAWAI
BAGIAN
Lihat struktur tabel BAGIAN dan PEGAWAI

Input data dengan urutan kolom sesuai dengan struktur tabel
Isi tabel BAGIAN dengan data kode : 10, nama_bagian : ADMINISTRATION
SQL> INSERT INTO bagian(kode,nama_bagian) VALUES(10,’ADMINISTRASI’);
1 row created.
Sebenarnya jika kita akan memasukkan data dan kita sudah mengetahui struktur tabelnya dan data tersebut akan diinput urut sesuai nama kolom, maka nama kolom tidak perlu disebutkan. Dengan demikian maka penulisan perintah INSERT menjadi lebih sederhana, seperti dibawah ini :
SQL> INSERT INTO bagian VALUES(20,’MARKETING’);
1 row created.
Input data untuk kolom tertentu saja
Nama kolom perlu disebutkan jika kita akan mengisi nilai hanya ke beberapa kolom saja. Misalkan isi data PEGAWAI untuk kolom NIP: 100 NAMA: ‘EDWIN’, maka perintah INSERT ditulis sebagai berikut:
SQL> INSERT INTO pegawai (nip,nama) VALUES(100,’ANTON’);
1 row created.
Untuk kolom lain pada baris tersebut akan diisi dengan NULL(kosong).
Jika kita lihat, dengan menggunakan perintah INSERT untuk menginput satu baris data diperlukan satu perintah INSERT. Sebenarnya masih ada beberapa utility lain di Oracle yang bisa digunakan untuk memasukkan data ke tabel seperti External Table, SQL*Loader dan Data Pump. Utility itu nanti akan dibahas pada bagian tersendiri.
Untuk latihan dan nanti akan digunakan untuk materi selanjutnya, isi tabel BAGIAN dan PEGAWAI dengan data berikut :
– isi tabel BAGIAN
insert into bagian values(10,’Administrasi’);
insert into bagian values(11,’Penjualan’);
insert into bagian values(12,’Gudang’);
– isi data pegawai
insert into pegawai values(1000,’WIRA’, ‘L’,null,7000000,10);
insert into pegawai values(1100,’BUDI’, ‘L’,null,5000000,12);
insert into pegawai values(1200,’ERTIN’, ‘P’,null,4250000,10);
insert into pegawai values(1300,’NOVI’, ‘P’,null,5500000,11);
insert into pegawai values(1201,’AHMAD’, ‘L’,null,2575000,10);
insert into pegawai values(1202,’ESTI’, ‘P’,null,3000000,10);
insert into pegawai values(1305,’EDWIN’, ‘L’,null,3250000,11);
insert into pegawai values(1306,’AMRA’, ‘L’,null,3100000,11);
insert into pegawai values(1101,’TONO’, ‘L’,null,2250000,12);
insert into pegawai values(1102,’SUTEJO’,'L’,null,2750000,12);
insert into pegawai values(1301,’DIANA’, ‘P’,null,3000000,11);
insert into pegawai values(1302,’YULI’, ‘P’,null,2750000,11);
insert into pegawai values(1303,’RINA’, ‘P’,null,3100000,11);
insert into pegawai values(1304,’DHILA’, ‘P’,null,2500000,11);
insert into pegawai values(1103,’ZAENAL’,'L’,null,1800000,12);
commit;
/
Download script untuk isi data ke tabel isi_tabel.sql
Materi tentang input data selesai. Selanjutnya adalah Materi 5 tentang Menampilkan data dari tabel
Terima kasih

____________________________________________
Membuat Tabel dan Mendefinisikan Constraint

Prasyarat :
• Instalasi Software Oracle database sudah berhasil
• Anda sudah membuat database Oracle
• Sebaiknya anda baca Materi 2 tentang User dan Schema
Membuat Tabel
Untuk belajar membuat tabel perhatikan struktur tabel di bawah ini :

Login sebagai user BUDI. (Catatatan: user BUDI sudah dibuat pada bahasan Materi 1 tentang User dan Schema).
SQL> CONNECT budi
Enter password: ******
Connected.
Buat tabel PEGAWAI dan BAGIAN
SQL> CREATE TABLE PEGAWAI
2 (NIP NUMBER(4),
3 NAMA VARCHAR2(15),
4 JK CHAR(1),
5 EMAIL VARCHAR2(20),
6 GAJI NUMBER(10),
7 KODE_BAG NUMBER(2));
SQL> CREATE TABLE BAGIAN
2 (KODE NUMBER(2),
3 NAMA_BAGIAN VARCHAR2(20));
Mendefinisikan Constraint
Constraint adalah batasan atau ketentuan yang diterapkan di tabel untuk menjaga konsistensi dan integritas data. Ada 5 jenis constraint di Oracle, yaitu :
• Primary Key
• Unique
• Not Null
• Check
• Foreign Key
Penggunaan dan cara mendefinisikan constraint adalah sebagai berikut:
Primary key
Defenisikan kolom NIP pada tabel PEGAWAI sebagai primary key.
SQL> ALTER TABLE PEGAWAI
2 ADD CONSTRAINT PK_PEGAWAI PRIMARY KEY (nip);
Defenisikan kolom KODE pada tabel BAGIAN sebagai primary key.
SQL> ALTER TABLE BAGIAN
2 ADD CONSTRAINT PK_BAGIAN PRIMARY KEY (kode);
Not Null
Definisikan kolom NAMA pada tabel PEGAWAI harus selalu diisi (Not Null)
SQL> ALTER TABLE PEGAWAI
2 MODIFY nama NOT NULL;
Check
Definisikan kolom JK (jenis kelamin) pada tabel PEGAWAI hanya boleh diisi oleh ‘L’ dan ‘P’
SQL> ALTER TABLE PEGAWAI
2 ADD CONSTRAINT ck_jk CHECK (JK IN (’L’,’P’));
Pastikan kolom GAJI pada tabel PEGAWAI minimal 1000000
SQL> ALTER TABLE PEGAWAI
2 ADD CONSTRAINT ck_gaji_1jt CHECK (GAJI >= 1000000);
Unique
Pastikan data untuk kolom EMAIL pada tabel PEGAWAI tidak boleh ada yang sama (Unique)
SQL> ALTER TABLE PEGAWAI
2 ADD CONSTRAINT UQ_EMAIL UNIQUE(email);
Foreign Key
Definisikan agar kolom KODE_BAG pada tabel PEGAWAI selalu merujuk ke kolom KODE pada tabel BAGIAN. (pendefinisian Foreign Key)
SQL> ALTER TABLE PEGAWAI
2 ADD CONSTRAINT fk_kode_bag FOREIGN KEY (kode_bag)
3* REFERENCES bagian(kode);
Bahasan Create table dan Constraint selesai…….
Download script materi3.sql
Materi selanjutnya adalah Materi 4 : Input data ke tabel
Terimakasih