ST-RK-1.

16-082-007/R-

Modul
Praktikum

Mahasiswa dapat membuat dan memanipulasi database object Oracle Server

(table, view, index, sequence) dengan
menggunakan perintah-perintah SQL
(Structured Query Language)
 Structure Query Language

DAFTAR ISI
Daftar Isi .................................................................................................. 1
PETUNJUK UMUM ................................................................................ 3
MODUL 1 ................................................................................................ 5
1.1 Definisi Tabel ........................................................................... 6
1.2 Aturan-aturan Penamaan ....................................................... 6
1.3 Petunjuk Pemberian Nama .................................................... 6
1.4 CREATE TABLE Statement .................................................... 6
1.5 Constraint ................................................................................ 7
1.6 SEQUENCE ........................................................................... 10
Latihan Modul 1 ................................................................................. 11
MODUL 2 .............................................................................................. 15
2.1 Insert....................................................................................... 16
2.2 Update .................................................................................... 17
2.3 Delete...................................................................................... 18
2.4 Controlling Transaction ........................................................ 19
Latihan Modul 2 ................................................................................. 20
MODUL 3 .............................................................................................. 23
3.1 SQL (Structure Query Language) ........................................ 24
3.2 Perintah Select ....................................................................... 24
3.3 Ekspresi Aritmatik ................................................................ 25
3.4 Kolom Alias ........................................................................... 26
3.5 Operator Concate .................................................................. 27
3.6 Membatasi Data .................................................................... 27
3.7 Operator pembanding .......................................................... 28
3.8 Operator Logika .................................................................... 29
3.9 Mengurutkan Data ................................................................ 30
Latihan Modul 3 ................................................................................. 31
MODUL 4 .............................................................................................. 34
4.1 Fungsi pada SQL ................................................................... 36
4.2 Fungsi Character ................................................................... 37
4.3 Fungsi Number ..................................................................... 39
4.4 Fungsi Date ............................................................................ 40
4.5 Fungsi Konversi .................................................................... 42
4.6 Fungsi General ...................................................................... 44
Latihan Modul 4 ................................................................................. 47
MODUL 5 .............................................................................................. 49
5.1 Group Functions ................................................................... 50
5.2 Penulisan Group Function ................................................... 51
Laboratorium Komputer – STIKOM 1
Structure Query Language

Latihan Modul 5 ................................................................................. 53

MODUL 6 ............................................................................................... 55
6.1 Definisi Join ........................................................................... 56
6.2 Syntax Dasar Join .................................................................. 56
Latihan Modul 6 ................................................................................. 59
MODUL 7 ............................................................................................... 63
7.1 Subquery ................................................................................ 64
7.2 UNION................................................................................... 65
7.3 UNION ALL .......................................................................... 66
7.4 INTERSECT ........................................................................... 66
7.5 MINUS ................................................................................... 67
Latihan Modul 7 ................................................................................. 68
MODUL 8 ............................................................................................... 71
8.1 Definisi View ......................................................................... 72
8.2 Syntax dasar View ................................................................ 72
8.3 Ketentuan penggunaan view ............................................... 74
8.4 Keuntungan dari View ......................................................... 74
8.5 Index....................................................................................... 75
8.6 Syntax dasar Index ................................................................ 75
Latihan Modul 8 ................................................................................. 76

2 Laboratorium Komputer – STIKOM

 Structure Query Language

PETUNJUK UMUM
1. Relasi dibawah ini digunakan pada saat mengerjakan latihan
praktikum.
Mahasiswa
Nim
Nm_Mhs
Alm_Mhs
Tgl_Lahir Nilai Grade
Jns_Kelamin Kode_Kuliah Level_Grade
Status Nim Batas_awal
Tugas Batas_akhir
UTS
UAS
MataKuliah
Kd_Kuliah
Nm_Kuliah
SKS
Semester

2. Relasi dibawah ini digunakan pada saat mengerjakan tugas

praktikum.

Laboratorium Komputer – STIKOM 3

Structure Query Language

4 Laboratorium Komputer – STIKOM

 Structure Query Language

MODUL 1
USING DDL STATEMENTS TO CREATE
AND MANAGE TABLES AND SEQUENCES

Janganlah berputus asa, tetapi kalau anda

sampai berada dalam keadaan putus asa
berjuanglah terus meskipun dalam keadaan
putus asa.
- Hotmian Haro -

Tujuan :
Praktikan dapat membuat tabel, constraint dan relasinya

Materi :
Table, Constraint dan Relation
Sequences

Referensi :
Oracle Database 10g : SQL Fundamentals I, Volume I, Student ■
Guide

Laboratorium Komputer – STIKOM 5

Structure Query Language

1.1. Definisi Tabel

Tabel adalah tempat penyimpanan data.
1.2. Aturan-aturan Penamaan
Anda memberi nama tabel dan kolom database menurut aturan-
aturan standart untuk pemberian nama pada obyek database Oracle:
1. Nama tabel dan nama kolom harus berawal dengan huruf dan
panjangnya 1-30 karakter.
2. Nama harus berisikan karakter A-Z, a-z, 0-9, _ (underscore), $,
dan #(karakter legal tetapi penggunaan ini tidak disarankan).
3. Nama tidak boleh sama dengan nama obyek lain yang dimiliki
oleh user Oracle server yang sama.
4. Nama tidak boleh berupa kata yang direserve oleh Oracle server.

1.3. Petunjuk Pemberian Nama

Gunakan nama yang deskriptif untuk tabel dan obyek database
lainnya.
Catatan : Nama menggunakan case-insensitive. Untuk contohnya,
EMPLOYEES dianggap sama dengan nama eMPloyees atau
eMpLOYEES.

1.4. CREATE TABLE Statement

Anda membuat tabel untuk menyimpan data dengan mengeksekusi
statement SQL CREATE TABLE. Ini adalah salah satu statement
pada DDL, yang merupakan subset dari statement-statement SQL
yang digunakan untuk membuat, memodifikasi, atau menghapus
struktur database Oracle. Statement-statement ini memiliki efek
langsung pada database, dan mencatat informasi dalam kamus data.
Untuk membuat tabel, seorang user harus mempunyai akses untuk
CREATE TABLE dan area penyimpanan dimana user akan membuat
obyek. Administrator database menggunakan statement bahasa
kontrol data untuk dapat melakukan akses kepada user (Statement
DCL akan dibahas pada pelajaran berikutnya).

6 Laboratorium Komputer – STIKOM

 Structure Query Language

CREATE TABLE.
Syntax dasar Create Table pada SQL :
Single-field constraint atau column constraint :
Create table namatabel
( NamaField tipe (Size) Constraint,
……………
);

Multi-field constraint atau table constraint:

Create table namatabel
( NamaField1 tipe (Size),
NamaField3 tipe (Size),
Constraint ……………
);

1.5. Constraint
Constraint Adalah suatu aturan – aturan (batasan) yang berlaku
terhadap suatu atribut atau entity. Dibawah ini adalah salah satu
contoh constraint.
1. Primary key Adalah suatu atribut atau satu set minimal atribut
yang mengidentifikasikan secara unique antara record satu
dengan record yang lainnya dan juga merupakan suatu atribut
yang mewakili setiap kejadian dari entity.
2. Foreign key Adalah suatu atribut atau satu set atribut yang
melengkapi suatu relationship dimana atribut tersebut mengacu
ke sebuah primary key pada base table ( Induk ).
Contoh membuat tabel dan Constraint, Primary Key, Not Null dan
Check.

Laboratorium Komputer – STIKOM 7

Structure Query Language

1. Single-field constraint atau column constraint Primary Key:

Create table NewMahasiswa
( Nim char(6) Constraint PK_NewMhs_Nim
Primary Key,
Nama varchar2(30) constraint
NN_NewMhs_Nama Not Null
);

2. Multi-field constraint atau table constraint Primary Key:

Create table NewNilai
( Nim char(6),
KodeMK Char(6),
UTS Number(3) constraint CK_Nilai_UTS
Check (UTS between 0 and 100),
UAS Number(3) constraint CK_Nilai_UAS
Check (UAS between 0 and 100),
constraint PK_NewNilai_Nim_Kode Primary
Key (Nim, KodeMK)
);

Contoh membuat tabel dan Constraint Foreign Key.

3. Single-field constraint atau column constraint :
Create table NewMahasiswa
( Nim char(6) Constraint PK_NewMhs_Nim
Primary Key,
Nama varchar2(30) constraint
NN_NewMhs_Nama Not Null
);
Create table NewNilai
( Nim char(6) constraint FK_NewNL_Nim
references NewMahasiswa(Nim),
KodeMK Char(6),
UTS Number(3) constraint CK_NewNL_UTS
Check (UTS between 0 and 100),
UAS Number(3) constraint CK_NewNL_UAS
Check (UAS between 0 and 100),
constraint PK_NewNilai_Nim_Kode Primary
Key (Nim, KodeMK)
);

8 Laboratorium Komputer – STIKOM

 Structure Query Language

4. Multi-field constraint atau table constraint:

Create table NewNilai
( Nim char(6),
KodeMK Char(6),
UTS Number(3) constraint CK_NewNL_UTS
Check (UTS between 0 and 100),
UAS Number(3) constraint CK_NewNL_UAS
Check (UAS between 0 and 100),
constraint PK_NewNilai_Nim_Kode Primary
Key (Nim, KodeMK),
constraint FK_NewNL_Nim Foreign Key (Nim)
references Mahasiswa(Nim)
);

Membuat sebuah tabel yang merupakan hasil subquery dari tabel

lain, yang dimaksud subquery disini adalah meng-copy struktur
tabel , constraint dan datanya dengan perintah :
Create table NewMatakuliah
As
Select * from NewMatakuliah;

Untuk constraintnya yang diturunkan dari tabel asal ke tabel tujuan

adalah NOT NULL, sedangkan constraint-constraint yang lain
(PRIMARY KEY, UNIQUE, FOREIGN KEY, dan CHECK) hilang.
1. Menampilkan tabel-tabel yang dibuat oleh user dengan perintah:
SELECT table_name FROM user_tables;

2. Menampilkan struktur tabel dengan perintah :

DESCRIBE NewMahasiswa;

3. Menampilkan semua constraint dari tabel-tabel yang telah Anda

buat dengan perintah:
SELECT table_name, constraint_name,
constraint_type, r_constraint_name
FROM user_constraints
WHERE table_name = 'NEWMAHASISWA';

Laboratorium Komputer – STIKOM 9

Structure Query Language

4. Menampilkan nama constraint beserta nama column-nya

dengan perintah:
SELECT table_name, column_name,
constraint_name
FROM user_cons_columns
WHERE table_name = 'NEWMAHASISWA';

5. Menampilkan keterangan tabel-tabel dan kolom-kolomnya.

SELECT table_name, column_name,
data_default
FROM user_tab_columns
WHERE table_name = 'NEWMAHASISWA'
and column_name = 'NIM';

1.6. SEQUENCE
Sequences adalah nomor urut yang unik yang diciptakan oleh sistem
secara otomatis.
Contoh cara pembuatan Sequences :
CREATE SEQUENCE NamaSequences
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]

Menghapus Sequences :
Drop Sequence Nama_Sequences

Membuat sequence dari tabel NewMhs.

create sequence seqMahasiswa
INCREMENT BY 1
START WITH 1
MAXVALUE 10 ;

10 Laboratorium Komputer – STIKOM

 Structure Query Language

Latihan
1. Buatlah tabel NewMhs sesuai dengan perintah dibawah ini.
Create Table NewMhs
(
Nim Char (11),
Nm_mhs Varchar2 (35),
Alm_mhs Varchar2 (30),
Tgl_Lahir Date,
Jns_Kelamin Char (1),
Status char (1)
);

2. Isikan data sesuai dengan perintah dibawah ini.

Insert into newmhs(nim, jns_kelamin)
values('96410100100','B');

3. Tambahkan constraint primary key untuk field nim pada tabel

NewMhs.
Alter table newmhs
Add constraint nMhs_Nim_PK
primary key (Nim);

4. Tambahkan constraint seperti dibawah ini pada tabel

mahasiswa.
Alter table newmhs
Add constraint Jns_Kelamin_nMhs_Ck Check
(Jns_Kelamin in ('P', 'W'))
Add constraint Status_nMhs_Ck Check
(Status in ('B', 'M'));

error : ada 1 data yang terisi tidak sama dengan constraint check
hapus dulu data pada tabel lalu kerjakan lagi alter-nya
5. Isikan data sesuai dengan perintah dibawah ini. Kemudian
amati dan diskusikan dengan Asisten/Coass.
Insert into newmhs(nim, jns_kelamin)
values('96410100100','B');
error : constraint check Jns_Kelamin_nMhs_Ck salah.

Laboratorium Komputer – STIKOM 11

Structure Query Language

Insert into newmhs(nim, jns_kelamin)

values('96410100100','P');

error : constraint primary key nMhs_Nim_PK salah

Alter table newmhs
Modify nm_mhs constraint nn_mhs_nama
not null;

error : ada data yang berupa null

Update newmhs
Set nm_mhs = 'Amir'
Where nim = '96410100100';

Alter table newmhs

Modify nm_mhs constraint nn_mhs_nama
not null;

Insert into newmhs(nim, jns_kelamin)

values('96410100101','P');

error : constraint not null nn_mhs_nama salah

6. copylah beberapa tabel yaitu mahasiswa, matakuliah, nilai dan
grade dari user Trainer01, dengan langkah sebagai berikut :
create table mahasiswa
as select * from [Link];

create table matakuliah

as select * from [Link];

create table nilai

as select * from [Link];

create table grade

as select * from [Link];

7. Tambahkan constraint primary key untuk field Kode_Kuliah

pada tabel NewMK.
Alter table newmk
Add constraint kode_kuliah_PK
primary key (Kd_Kuliah);

12 Laboratorium Komputer – STIKOM

 Structure Query Language

8. Buatlah script create table dengan text editor apapun dengan

nama [Link]. Adapun isi scriptnya sebagai berikut :
Create Table NewNilai
(
Kode_Kuliah Char(6),
Nim Char(11),
Tugas Number(3),
Quiz1 Number(3),
Quiz2 Number(3),
UTS Number(3),
UAS Number(3),
Constraint FK_nNilai_Kd_Kuliah
Foreign Key (Kode_Kuliah)
references NewMK(Kd_Kuliah),
Constraint FK_nNilai_Nim
Foreign Key (Nim)
references NewMhs(Nim),
Constraint PK_nNilai_Nim_Kd_Kuliah
Primary Key (Kode_Kuliah, Nim)
);

9. Buatlah sequence dari tabel NewMhs.

create sequence seqMahasiswa
INCREMENT BY 1
START WITH 1
MAXVALUE 5;

10. Tambahkan data berikut ini dengan menggunakan perintah Sql.

Amir,Jl. tenggilis 20,25-DEC-1982,W,B
Budi,Jl. Semampir 12,25-MAR-1983,P,B
Insert into NewMhs
values([Link],
'&Nama', '&Alamat',' &Tgl_Lahir',
'&Jns_Kelamin', '&Status');

11. Menampilkan Sequence yang telah dibuat.

SELECT sequence_name FROM user_sequences;

12. Memperbaiki Sequence.

ALTER SEQUENCE seqMahasiswa MAXVALUE 10;
Laboratorium Komputer – STIKOM 13
Structure Query Language

14 Laboratorium Komputer – STIKOM

 Structure Query Language

MODUL 2
MANIPULATING DATA AND
CONTROLLING TRANSACTIONS

Ciri orang yang beradap adalah dia sangat rajin

dan suka belajar, dan dia tidak malu belajar
dari orang yang berkedudukan lebih rendah
darinya.
- Hotmian Haro -

Tujuan :
Praktikan dapat memanipulasi data pada database Oracle Server
dan dapat mengontrol transaksi

Materi :
Manipulasi Data (Insert, Update, Delete)
Mengontrol transaksi (Rollback, Savepoint, Commit)

Referensi :
Oracle Database 10g : SQL Fundamentals I, Volume I, Student ■
Guide

Laboratorium Komputer – STIKOM 15

Structure Query Language

2.1. Insert
Statement Insert digunakan untuk menambah satu baris (Record) ke
dalam tabel.
Syntax dasar Insert
Insert Into namatabel Values
values1,values2,…);

Insert Into namatabel

(namafield1,namafield2,…)
Values(values1,values2,…);

Insert Into namatabel_1 Select * from

namatabel_2 Where Predikat

Insert Into namatabel_1

namafield1,namafield2,……)
Select namafield1,namafield2,……
from namatabel_2
Where Predikat;

Contoh :
1. Menambah data mahasiswa seluruh kolom.
insert into mahasiswa values
('01410100080','Cecilia',
'Jl. Tenggilis 20',
'25-DEC-1982','W','B');

2. Menambah data mahasiswa tidak seluruh kolom.

insert into mahasiswa
(Nim, Nm_Mhs, Alm_Mhs)
values ('01410100080','Cecilia',
'Jl. tenggilis 20');

16 Laboratorium Komputer – STIKOM

 Structure Query Language

3. Menambah data mahasiswabaru dari tabel mahasiswa.

insert into mahasiswabaru
select * from mahasiswa;

4. Menambah data mahasiswabaru dari tabel mahasiswa, hanya

kolom nim, nama dan alamat, dengan ketentuan hanya data
mahasiswa D3.
insert into mahasiswabaru
(nim,nm_mhs,alm_mhs)
select nim,nm_mhs,alm_mhs
from mahasiswa
where substr(nim,3,5) = „39010‟;

2.2. Update
Statement Update digunakan untuk mengubah nilai dari suatu field
pada suatu record dari sebuah tabel.
Syntax dasar Update
Update namatabel
Set NamaField1 = nilai1,
NamaField2 = nilai2,
NamaField3 = nilai3,……
Where Predikat;

Update namatabel_1
Set NamaField1 = nilai1,
NamaField2 = nilai2,……
Where Namatabel_1.Kunci =
(Select namatabel_2.Kunci
from namatabel2 Where Predikat);

Contoh :
1. Mengubah status seluruh mahasiswa menjadi B (Belum
Menikah).
Update Mahasiswa Set Status = „B‟;

Laboratorium Komputer – STIKOM 17

Structure Query Language

2. Mengubah nama dan alamat untuk mahasiswa yang

mempunyai nim 01410100080.
Update Mahasiswa
Set Nm_mhs = „Cecilia Ceria‟,
Alm_Mhs = „Jl. Tenggilis 120‟
Where nim = '01410100080';

3. Mengubah nilai tugas dari mahasiswa yang bernama Dahlia

menjadi 80.
Update Nilai
Set tugas = 80
Where nim = (select nim from mahasiswa
where nm_mhs = „Dahlia‟);

2.3. Delete
Statement Delete digunakan untuk Menghapus satu atau beberapa
record.
Syntax dasar Delete
Delete From namatabel where Predikat;

Delete namatabel_1
Where Namatabel_1.Kunci =
(Select namatabel_2.Kunci from namatabel2
Where Predikat);

Contoh :
1. Mengahapus seluruh data pada tabel mahasiswa.
Delete from mahasiswa;

2. Menghapus data mahasiswa untuk mahasiswa yang bernama

Cecilia.
Delete from mahasiswa
where nama = „Cecilia‟;

18 Laboratorium Komputer – STIKOM

 Structure Query Language

3. Menghapus data nilai untuk mahasiswa yang bernama „Dahlia‟

Delete from nilai
Where nim = select nim from mahasiswa
Where nm_mhs = „Dahlia‟)

2.4. Controlling Transaction

Commit :
Dalam melakukan perubahan data yang terjadi akibat dari operasi
DML sebenarnya tidak langsung disimpan ke dalam media
penyimpanan, tetapi ditampung di dalam buffer, untuk dapat
tersimpan kedalam media penyimpanan dibutuhkan perintah
commit.
Khusus untuk perintah – perintah DDL seperti CREATE TABLE,
DROP TABLE, ALTER TABLE dimana hasil dari operasi tersebut
akan langsung disimpan kedalam media penyimpanan (otomatis
commit dijalankan).
SavePoint :
Digunakan untuk memberikan suatu batasan (tanda) terhadap query
– query yang kita lakukan.

Rollback dan Rollback to :

Rollback untuk defaultnya digunakan untuk kembali ke awal (pada

saat login) atau pada COMMIT terakhir yang pernah dilakukan.
Rollback to digunakan untuk kembali ke area yang kita tandai.

Laboratorium Komputer – STIKOM 19

Structure Query Language

Latihan
1. Tampilkan struktur tabel mahasiswa dan tampilkan pula
datanya.
desc mahasiswa;
select * from mahasiswa;

2. Isikan data dari tabel mahasiswa milik user trainer01 untuk data
mahasiswa dengan nim 03410100265 dan 02410100000
insert into mahasiswa
select * from [Link] where
nim in('03410100265','02410100000');

3. Isikan data mahasiswa dengan data:

insert into mahasiswa
values ('01410104001','Yuswanto');

(error karena jumlah kolom yang diinputkan tidak sesuai

dengan struktur tabel)
insert into mahasiswa(nim, nm_mhs)
values ('01410104001','Yuswanto');

(error karena constraint not null, jumlah kolom)

insert into mahasiswa
values ('01410100080','Cecilia',
'jl. tenggilis 20',
'25-DEC-1982','W','B');

4. Isikan data dibawah ini dengan menggunakan tanda ampersand

(&)
02410100007, Rima, jl. banyumas 103, 4-SEP-1984, W, B
insert into mahasiswa
values('&vnim','&vnama','&valm',
'&vtgllahir','&vjns_kelamin','&vstatus');

5. Buatlah script insert data dengan text-editor apapun, kemudian

simpanlan dengan [Link]. Adapun scriptnya dibawah
ini:

20 Laboratorium Komputer – STIKOM

 Structure Query Language

insert into mahasiswa

values('&vnim','&vnama','&valm',
'&vtgllahir','&vjns_kelamin','&vstatus');

6. Kemudian isikan data dibawah ini dengan menjalankan script

yang telah dibuat.
02410100045, Roy, jl. kopi tubruk 55, 16-MAY-1985, P, B
7. Ubalah status seluruh mahasiswa menjadi sudah menikah (M).
update mahasiswa set status = 'M';

8. Ubalah status mahasiswa yang bernama Firmansyah dan Rabeca

menjadi belum menikah.
update mahasiswa set status = 'B'
where nm_mhs in ('Firmansyah','Rabeca');

9. Hapuslah data mahasiswa yang bernama Firmansyah dan

Rabeca.
delete mahasiswa where nm_mhs in
('Firmansyah','Rabeca');

(error karena relasi PK dan FK)

Controlling Transaction
10. Membatalkan seluruh kegiatan Insert, Update dan Delete
(DML) diatas.
Rollback;

11. Tampilkan data mahasiswa

select * from mahasiswa;

12. Lakukan perintah SQL dibawah ini dengan urutan yang telah
ditentukan, kemudian amati dan diskusikan dengan
asisten/coass.

Laboratorium Komputer – STIKOM 21

Structure Query Language

insert into mahasiswa values

('01410100080','Cecilia','jl. tenggilis
20','25-DEC-1982','W','B');

select * from mahasiswa;

commit;
insert into mahasiswa values
('02410100045','Roy',
'jl. kopi tubruk 55',
'16-MAY-1985','P','B');

select * from mahasiswa;

savepoint satu;

update mahasiswa
set nm_mhs = 'Boy'
where nm_mhs = 'Roy';

select * from mahasiswa;

savepoint dua;

delete mahasiswa
where nm_mhs = 'Boy'

select * from mahasiswa;

rollback to dua;

select * from mahasiswa;

rollback to satu;

select * from mahasiswa;

rollback;
select * from mahasiswa;

22 Laboratorium Komputer – STIKOM

 Structure Query Language

MODUL 3
RETRIEVING DATA
USING THE SQL SELECT STATEMENT AND
RESTRICTING AND SORTING DATA

Kita seharusnya diajar untuk tidak menunggu

inspirasi untuk memulai sesuatu. Tindakan
selalu melahirkan inspirasi. Sedangkan
inspirasi jarang diikuti dengan tindakan.
- Frank Tibolt -

Tujuan :
Praktikan dapat melakukan query terhadap suatu tabel dengan
menggunakan beberapa operator.

Materi :
Perintah Select, Ekspresi Aritmatika, Kolom Alias, Membatasi Data,
Operator Concat, Operator Pembanding dan Operator Logika,
Mengurutkan Data

Referensi :
Oracle Database 10g : SQL Fundamentals I, Volume I, Student ■
Guide

Laboratorium Komputer – STIKOM 23

Structure Query Language

3.1. SQL (Structure Query Language)

Sebuah relasional database bisa berisi satu tabel atau lebih. Tabel
merupakan struktur dasar penyimpanan data di RDBMS yang
terdiri dari baris dan kolom. Sebuah tabel dapat mempunyai kolom
yang bernilai unik, dimana nilai kolom tersebut berbeda untuk
setiap barisnya, kolom ini disebut dengan Primary Key (PK).
Bagaimana dan dimana sebuah relasional database disimpan secara
fisik pada media penyimpanan tidak dapat diketahui dengan pasti,
meskipun demikian data-data tersebut tetap dapat diakses dan
dimodifikasi dengan menggunakan Structured Query Language
(SQL) statement, yang merupakan bahasa standard American
National Standard Institute (ANSI) untuk operasi relasional
database.
Secara umum perintah SQL dikelompokkan menjadi 4 yaitu Data
Manipulation Language (DML), Data Definition Language (DDL),
Data Control Language (DCL) dan Transaction Control.
Data Manipulation Language (DML) adalah perintah yang
digunakan untuk membaca dan memodifikasi data pada suatu tabel,
terdiri dari perintah SELECT, INSERT, UPDATE, DELETE, MERGE.
Data Definition Language (DDL) merupakan perintah yang
digunakan untuk mendefinisikan pembuatan ataupun modifikasi
dari obyek database. DDL terdiri dari perintah CREATE, ALTER,
DROP, RENAME, TRUNCATE, COMMENT.
Data Control Language (DCL) digunakan untuk menentukan hak
akses apa saja yang diberikan kepada seorang user. Perintah yang
termasuk dalam DCL adalah GRANT dan REVOKE
Transaction Control digunakan untuk mengatur perubahan yang
dilakukan oleh DML. Perintah yang termasuk dalam Transaction
Control adalah COMMIT, ROLLBACK dan SAVEPOINT

3.2. Perintah Select

Perintah SQL SELECT diperlukan untuk membaca data dari
database, terkadang tidak semua informasi ingin diambil dari
sebuah database. Oleh sebab itu perintah SQL SELECT mempunyai
kemampuan Projection, Selection, dan Joining.

24 Laboratorium Komputer – STIKOM

 Structure Query Language

1. Projection adalah kemampuan perintah SQL SELECT untuk

memilih kolom mana yang akan ditampilkan.
2. Selection adalah kemampuan perintah SQL SELECT untuk
memilih baris yang akan ditampilkan.
3. Joining merupakan kemampuan perintah SQL SELECT untuk
menampilkan informasi yang berasal dari 2 tabel atau lebih,
dimana ada relasi tertentu yang menghubungkan antar tabel-
tabel tersebut.
Perintah dasar Select :
SELECT *|{[DISTINCT] column|expression
[alias], ....}
FROM table;

Contoh :
1. Menampilkan semua kolom yang ada pada tabel mahasiswa.
Select * From Mahasiswa;

2. Menampilkan kolom tertentu saja dari tabel mahasiswa.

Select nim, nm_mhs, alm_mhs
From mahasiswa;

3.3. Ekspresi Aritmatik

Untuk membuat sebuah pelaporan terkadang diperlukan suatu
perhitungan-perhitungan tertentu terhadap sebuah data, dalam SQL
hal ini sangat dimungkinkan dengan memanfaatkan ekspresi
aritmatik. Sebuah ekspresi aritmatik bisa terdiri dari nama kolom,
nilai konstan, dan operator aritmatik.
Operator aritmatik untuk tipe data angka dan tanggal adalah :

Operator Keterangan

+ Penjumlahan tipe data angka atau tanggal

- Pengurangan tipe data angka atau tanggal

Laboratorium Komputer – STIKOM 25

Structure Query Language

* Perkalian untuk tipe data angka

/ Pembagian untuk tipe data angka

Operator aritmatik dapat digunakan pada setiap bagian perintah

SELECT, kecuali dibagian FROM.
Contoh :
select Nim, (tugas*0.2) + (uts*0.3) +
(uas*0.5) From Nilai;

Apabila pada suatu ekpresi aritmatik terdapat lebih dari satu

operator, aturan prioritasnya adalah sebagai berikut:
 Perkalian (*) dan pembagian (/) dikerjakan lebih dulu daripada
penjumlahan (+) dan pengurangan (-)
 Operator yang memiliki level prioritas yang sama akan
dikerjakan mulai dari kiri ke kanan
 Tanda kurung ( ) digunakan untuk mengubah standar urutan
prioritas, atau untuk memperjelas penulisan ekspresi aritmatik.

3.4. Kolom Alias

Saat menampilkan hasil suatu query, SQL secara default
menggunakan nama kolom yang ada pada perintah SELECT sebagai
headernya. Terkadang nama kolom ini kurang bisa menjelaskan isi
atau sulit dimengerti oleh user. Untuk memudahkan user dalam
membaca hasil query, maka nama kolom header bisa diganti dengan
menggunakan kolom alias.
Kolom alias digunakan untuk mengubah kolom header, sangat
berguna pada operasi aritmatik, nama alias dituliskan sesudah nama
kolom dengan menggunakan spasi sebagai pemisah atau
menggunakan keyword AS antara nama kolom dan nama alias.
Nama alias memerlukan tanda petik ganda (“ “) apabila kolom alias
mengandung spasi atau karakter spesial (seperti # atau $), atau case-
sensitive, jika tidak maka tidak perlu menggunakan tanda petik
ganda (” ”) yaitu dengan langsung menyebutkan nama aliasnya.

26 Laboratorium Komputer – STIKOM

 Structure Query Language

Contoh :
select Nim, (tugas*0.2) + (uts*0.3) +
(uas*0.5) "Nilai Akhir"
From Nilai;
select Nim, (tugas*0.2) + (uts*0.3) +
(uas*0.5) Nilai
From Nilai;

3.5. Operator Concate

Digunakan untuk menggabungkan nilai kolom atau karakter dengan
kolom yang lain, disimbolkan dengan tanda ||. Apabila operasi ini
mengandung nilai NULL, maka hasilnya tetap karakter.
Contoh :
Menampilkan gabungan antara nama mahasiswa dengan alamatnya
dimana digabungkan dengan menggunakan tanda koma (”,”).
select nm_mhs||','||alm_mhs
From mahasiswa;

3.6. Membatasi Data

Dalam kehidupan sehari-hari, terkadang dibutuhkan suatu laporan
berdasarkan kriteria tertentu. Untuk memenuhi kebutuhan tersebut
digunakan perintah SQL sebagai berikut :
SELECT *|{ [DISTINCT] column | expression
[alias], ...}
FROM table
WHERE condition(s);

WHERE membatasi query hanya pada baris-baris yang memenuhi

sebuah kondisi tertentu yang disebutkan pada klausa where.
Condition terdiri dari nama kolom, ekspresi, konstanta dan operator
pembanding.

Laboratorium Komputer – STIKOM 27

Structure Query Language

3.7. Operator pembanding

Operator pembanding digunakan dalam sebuah kondisi untuk
membandingkan suatu ekspresi dengan suatu nilai atau ekspresi
yang lain.
Operator Arti
= Sama dengan
> Lebih besar
>= Lebih besar sama dengan
< Lebih kecil
<= Lebih kecil sama dengan
<>, !=, ^= Tidak sama dengan
BETWEEN ... AND ... Antara 2 nilai
IN (set) Membandingkan dengan setiap nilai
pada sekumpulan data (set)
LIKE Membandingkan dengan suatu pola
karakter
IS NULL Sama dengan NULL

Contoh :
Menampilkan nim, uts dan uas untuk nilai uts lebih besar dari 70.
Select nim, uts, uas
From nilai
Where uts > 70;

Operator In
Digunakan untuk mencari nilai dalam satu kelompok nilai (daftar /
list). Nilai dalam daftar terdiri atas satu atau lebih.
Menampilkan nim dan alamat untuk mahasiswa yang bernama
Bagaskara dan Firmansyah :
Select nim, alm_mhs
From mahasiswa
Where nm_mhs in
('Bagaskara','Firmansyah');

28 Laboratorium Komputer – STIKOM

 Structure Query Language

Operator Like
Digunakan untuk permintaan yang mencari suatu Nilai , dalam
pencarian juga dapat digunakan karakter - karakter khusus seperti
%, Contoh :
Menampilkan nama mahasiswa dan alamat dari tabel Mahasiswa
untuk nama mahasiswa yang berawalan huruf B :
Select nm_mhs
From mahasiswa
Where nm_mhs like 'B%';

Operator Between
Digunakan untuk melakukan pencarian berbentuk antara (range).
Menampilkan nilai akhir pada tabel nilai dengan ketentuan untuk
nilai akhir diantara 70 dan 90.
Contoh :
Select Nim, (tugas*0.2) + (uts*0.3) +
(uas*0.5) "Nilai Akhir"
From nilai
Where (tugas*0.2) + (uts*0.3) + (uas*0.5)
between 70 and 90;

3.8. Operator Logika

Sebuah kondisi logika mengkombinasikan hasil dua komponen
kondisi menjadi satu nilai. Pada SQL terdapat 3 operator logic yaitu
AND, OR, dan NOT. Berikut adalah tabel kebenaran dari operator
logika.
Operator logika AND
AND TRUE FALSE NULL
TRUE TRUE FALSE NULL
FALSE FALSE FALSE FALSE
NULL NULL FALSE NULL

Laboratorium Komputer – STIKOM 29

Structure Query Language

Operator logika OR
AND TRUE FALSE NULL
TRUE TRUE TRUE TRUE
FALSE TRUE FALSE NULL
NULL TRUE NULL NULL
Operator logika NOT
NOT TRUE FALSE NULL
FALSE TRUE NULL
Contoh :
Menampilkan nim dan alamat untuk mahasiswa yang bernama
Bagaskara dan Firmansyah :
Select nim, alm_mhs
From mahasiswa
Where nm_mhs = 'Bagaskara' or
nm_mhs = 'Firmansyah';

3.9. Mengurutkan Data

Untuk menampilkan data secara urut tambahkan ORDER BY pada
perintah SELECT. ORDER BY mempunyai 2 opsi yaitu ASC yang
merupakan nilai default, opsi ini akan mengurutkan data dari nilai
terkecil ke nilai terbesar. Opsi kedua adalah DESC yaitu
mengurutkan data dari nilai terbesar ke nilai terkecil. ORDER BY
harus dituliskan pada bagian terakhir perintah SELECT.
SELECT * { [DISTINCT] column | expression
[alias], ...}
FROM table
WHERE condition(s)
[ORDER BY {column, expr,
numeric_position}[ASC|DESC] ];

Default pengurutan data adalah ascending:

 Numerik diurutkan dari nilai terkecil ke nilai terbesar, misal 1
sampai 999.
 Tanggal ditampilkan mulai dari tanggal terawal, misal 01-JAN-
92, 01-JAN-95.

30 Laboratorium Komputer – STIKOM

 Structure Query Language

 Karakter ditampilkan sesuai dengan urutan abjad, yaitu dari A

sampai Z
 NULL ditampilkan pada urutan terakhir apabila menggunakan
ascending order, dan ditampilkan diurutan pertama apabila
menggunakan descending order.
 Pada ORDER BY bisa menggunakan kolom yang tidak
disebutkan pada perintah SELECT.
Contoh :
Select nim, nm_mhs, alm_mhs
From mahasiswa Order By Nim Desc;

Laboratorium Komputer – STIKOM 31

Structure Query Language

Latihan
1 Buatlah sebuah query untuk menampilkan nim mahasiswa dan
nama mahasiswa S1 dengan ketentuan tampilkan nimnya
dengan menggabungkan “SI-“, Contoh Nim: 99410104001
menjadi SI-99410104001.
NIM NAMA MAHASISWA
SI-97410104001 Ananta Jodi
SI-97410104002 Bagaskara
SI-97410104003 Karmila
…… ……
SI-00410104003 Karmila
2 Buatlah sebuah query untuk menampilkan nama mahasiswa,
tanggal lahir dan alamatnya untuk mahasiswa D3 angkatan 98.

NAMA MAHASISWA TGL LAHIR ALAMAT

Firmansyah 15-MAR-79 Jl. Bugenvil 10

3 Buatlah sebuah query untuk menampilkan nim, kode kuliah,

UTS dan UASnya untuk mahasiswa yang mempunyai nim
99410104001 dan 99410104002 dan urutkan berdasarkan nim
secara ascending.

NIM KODE UTS UAS

99410104001 BD-002 60 70
99410104001 KD-003 70 60
99410104002 BD-002 70 90
4 Buatlah sebuah query untuk menampilkan seluruh data mata
kuliah yang diselenggarakan pada Semester 3 dengan jumlah
SKS diatas 2 SKS.

KODE MATA KULIAH SKS SEMESTER

BD-002 Structure Query Language 4 3

5 Buatlah sebuah query untuk menampilkan seluruh data nilai

yang mempunyai nilai UTS atau UASnya tidak diantara 50 dan
80 dan untuk mahasiswa D3 angkatan 98.

KODE NIM TUGAS UTS UAS

32 Laboratorium Komputer – STIKOM

 Structure Query Language

KD-003 98390104001 70 85 75

6 Buatlah sebuah query untuk menampilkan nim, kode_kuliah

dan nilai akhir untuk mahasiswa S1 yang menempuh mata
kuliah dengan kode kuliah AI-001 dan BD-002 dan nilai
akhirnya sama dengan 69.

NIM KODE NILAI AKHIR

97410104001 AI-001 69
97410104001 BD-002 69

7 Buatlah sebuah query untuk menampilkan nim, kode_kuliah

dan nilai akhir mahasiswa berdasarkan range yang ditentukan.
Antara r1 dan r2. Keterangan : r1 = range awal, r2 = range
akhir.

NIM KODE NILAI AKHIR

99410104002 BD-002 80
00410104001 BD-002 80

8 Buatlah sebuah query untuk menampilkan nim, nama

mahasiswa, dan nilai akhir mahasiswa yang mempunyai nilai
akhir diatas 60 dan urutkan mulai dari yang nilainya terbesar.

NIM KODE KULIAH NILAI AKHIR

99410104002 BD-002 80
00410104001 BD-002 80
98390104001 KD-003 77
………… ……. ……
………… ……. ……
98390104001 BD-002 64.5
98390104001 AI-001 62

Catatan
S1 = 41010 (Nim) dan D3 = 39010 (Nim)
Nilai Akhir = (Tugas * 0.2) + (Uts * 0.3) + (Uas * 0.5)

Laboratorium Komputer – STIKOM 33

Structure Query Language

34 Laboratorium Komputer – STIKOM

 Structure Query Language

MODUL 4
USING SINGLE-ROW FUNCTIONS
TO CUSTOMIZE OUTPUT

Lumbung emas dalam diri kamu adalah fikiran

kamu. Kamu dapat
menggali sedalam-dalamnya dan sepuas-puas
yang kamu inginkan.
- Abdullah Mansur M.H -

Tujuan :
Praktikan dapat memahami Single Row Functions dalam query
suatu tabel.

Materi :
Fungsi Character, Number, Arithmatic, Conversion, Date And Time
Conditional Expressions (Case dan Decode)

Referensi :
Oracle Database 10g : SQL Fundamentals I, Volume I, Student ■
Guide

Laboratorium Komputer – STIKOM 35

Structure Query Language

4.1. Fungsi pada SQL

Fungsi adalah sebuah feature dari SQL, yang digunakan untuk :
 Melakukan perhitungan
 Mengubah data item
 Memanipulasi hasil untuk sekelompok baris
 Memformat tampilan tanggal dan angka
 Mengubah tipe data dari suatu kolom.
SQL functions kadang memerlukan beberapa argument atau input
dan selalu menghasilkan satu nilai.

Fungsi SQL terdiri dari 2 macam yaitu Single-row functions dan

multiple-row functions. Single-row functions beroperasi hanya pada
satu baris dan menghasilkan satu nilai untuk setiap baris. Sedangkan
multiple-row functions digunakan untuk memanipulasi sekumpulan
baris and menghasilkan satu nilai, functions ini juga dikenal dengan
Group Functions.
Single-row Functions
Digunakan untuk manipulasi data item. Menerima satu argument
atau lebih dan menghasilkan satu nilai untuk setiap baris yang
ditampilkan. Argumen untuk single-row functions bisa berupa :
 Konstanta
 Variable
 Nama kolom
 Ekspresi

36 Laboratorium Komputer – STIKOM

 Structure Query Language

Features dari single-row functions adalah :

 Bekerja pada setiap baris yang dihasilkan oleh query
 Menghasilkan satu nilai untuk setiap baris
 Dapat mengembalikan suatu nilai dengan tipe data yang
berbeda
 Dapat menerima satu argument atau lebih
 Dapat digunakan pada SELECT, WHERE, dan ORDER BY, serta
dapat dinested.
Single-row functions terdiri dari 5 jenis, yaitu Character functions,
Number functions, Date functions, Conversion functions dan
General functions.

4.2. Fungsi Character

Fungsi karakter menerima input berupa karakter dan
mengembalikan nilai yang bisa berupa karakter maupun angka.
Fungsi karakter dibagi menjadi 2 kelompok yaitu Case-manipulation
functions dan Character-manipulation functions.
Case-manipulation Functions berguna untuk mengubah karakter
dari huruf kecil ke huruf besar atau sebaliknya.
Character-manipulation Functions digunakan untuk memanipulasi
karakter, misal menggabungkan karakter, mengambil sebagian
karakter dari sebuah kalimat, serta mengetahui panjang dari sebuah
kalimat.

Laboratorium Komputer – STIKOM 37

Structure Query Language

Berikut adalah daftar dari fungsi karakter yang digunakan pada

pembahasan modul 2 adapun yang lainnya dapat dilihat pada
referensi modul 2.
Kelompok Fungsi Kegunaan
Case- LOWER (column|expressions) Mengubah ke huruf
manipulation kecil
UPPER (column|expressions) Mengubah ke huruf
besar
Character- CONCAT (column1|expressions1, Menggabungkan
manipulation column2|expressions2) kalimat atau karakter
pertama dengan kali-
mat atau karakter
kedua
SUBSTR (column|expressions, m[,n]) Mengambil karakter
mulai dari posisi m
sebanyak n. Jika n
tidak dituliskan,
maka semua karakter
mulai posisi ke m
sampai terakhir akan
diambil
LPAD(column|expressions, n, ‘string’) Menambahkan
karakter tertentu
disebelah kiri suatu
kalimat, sehingga
panjang kalimat
menjadi n.

RPAD(column|expressions, n, ‘string’) Menambahkan

karakter tertentu
disebelah kanan
suatu kali-mat,
sehingga panjang
kalimat menjadi n.

Contoh :
Menampilkan nama dan alamat mahasiswa yang dimulai 3 huruf
pertama dari nim dan diambil sebanyak 5 karakter.
Select Nim,Nm_Mhs
From Mahasiswa
Where substr(nim,3,5) = „41010‟;
38 Laboratorium Komputer – STIKOM
 Structure Query Language

Kelompok Fungsi Kegunaan

TRIM(leading|trailing|both, Menghilangkan spasi
trim_character FROM trim_source)) atau karakter tertentu
dari sebuah kalimat.
Jika trim_character
atau trim_source
adalah sebuah literal,
maka harus
menggunakan tanda
petik tunggal.

4.3. Fungsi Number

Fungsi angka digunakan untuk memanipulasi suatu angka, berikut
daftar dari fungsi angka :

Fungsi Kegunaan

ROUND(column|expression, n) Membulatkan suatu nilai sampai

n desimal. Jika n tidak
disebutkan, berarti tidak ada
nilai desimal, jika n bernilai
negatif berarti dibulatkan kekiri
sesuai dengan nilai n

TRUNC(column|expression, n) Memotong suatu nilai sampai n

desimal. Jika n tidak disebutkan,
maka n bernilai 0. Jika n bernilai
negatif berarti dibulatkan kekiri
sesuai dengan nilai n

MOD(m, n) Mencari nilai sisa dari m dibagi n

Contoh :
1. Menampilkan format nilai menggunakan round.
select round(45.923,2), round(45.923),
round(45.923, -1)
From Dual;

Laboratorium Komputer – STIKOM 39

Structure Query Language

Hasilnya :

ROUND(45.923,2) ROUND(45.923) ROUND(45.923,-1)

45.92 46 50

2. Menampilkan format nilai menggunakan trunc.

select trunc(45.923,2), trunc(45.923),
trunc(45.923, -1)
From Dual;

Hasilnya :

TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1)

45.92 45 40

3. Menampilkan seluruh data mahasiswa yang mempunyai Nim

Ganjil.
select * from mahasiswa
where mod(nim,2) = 1;

4.4. Fungsi Date

Oracle database menyimpan tanggal dalam format angka yang
terdiri dari: abad, tahun, bulan, tgl, jam, menit dan detik, nilai
tanggal yang dapat disimpan oleh Oracle adalah antara 1 Januari
4712 SM sampai 31 Desember 9999.
Sedangkan untuk tampilan defaultnya menggunakan format DD-
MON-RR, yang berarti menampilkan 2 digit angka yang
menunjukkan tanggal, kemudian 3 digit karakter terdepan dari
nama bulan, dan 2 digit terakhir dari tahun. Meskipun saat
ditampilkan tahunnya hanya terdiri dari dua digit, tetapi pada
penyimpanannya tetap 4 digit, dimana 2 digit terdepan
menunjukkan abad dan 2 digit berikutnya menunjukkan tahun.
Untuk menampilkan tanggal dan waktu dari database server
digunakan fungsi SYSDATE. Fungsi SYSDATE digunakan
sebagaimana nama kolom pada perintah SELECT.

40 Laboratorium Komputer – STIKOM

 Structure Query Language

Untuk memanipulasi data bertipe tanggal, selain menggunakan

operasi aritmatik, juga bisa memanfaatkan fungsi tanggal. Semua
fungsi tanggal akan menghasilkan data bertipe tanggal, kecuali
fungsi MONTHS_BETWEEN yang menghasilkan angka.
Berikut adalah daftar dari fungsi tanggal, adapun yang lainnya
dapat dilihat pada referensi modul 2.
Fungsi Keterangan
MONTHS_BETWEEN(date1, date2) Selisih bulan antara dua tanggal
ADD_MONTHS(date, n) Menambahkan tanggal dengan n
bulan
NEXT_DAY(date, „char‟) Mencari tanggal yang akan
datang sesudah hari yang
disebutkan pada char. Char dapat
berupa angka yang mewakili
urutam hari dalam minggu atau
bisa juga berupa nama hari
LAST_DAY(date) Mencari tanggal terakhir pada
bulan yang bersangkutan

Contoh :
1. Menampilkan nama dan umur mahasiswa.
Select nm_mhs,
round(months_between(sysdate,tgl_lahir)/1
2) "Umur MHS"
From mahasiswa;

2. Menampilkan nama dan tanggal lahir mhs dgn ketentuan yang

bln lahirnya 5 bulan setelah bulan maret (add_months).
Select nm_mhs, tgl_lahir from mahasiswa
Where to_char(tgl_lahir,'mm')=
to_char(add_months(to_date(3,'mm'),5),
'mm');

3. Menentukan tanggal berikutnya dari minggu berikutnya.

Select nm_mhs,
lpad(to_char(tgl_lahir,'dd'),10)
"tgl lahir",
to_char(next_day(tgl_lahir,'MONDAY'),
'dd') - to_char(tgl_lahir,'dd')
"selisih hari"
Laboratorium Komputer – STIKOM 41
Structure Query Language

From mahasiswa;

4. Menampilkan tanggal akhir pada bulan sekarang.

Select last_day(sysdate)From dual;

4.5. Fungsi Konversi

Fungsi konversi digunakan untuk mengubah tipe data menjadi tipe
data yang lain, sebetulnya tanpa menggunakan fungsi konversi
Oracle secara otomatis akan mengubah suatu tipe data menjadi tipe
data yang sesuai, fasilitas ini disebut dengan Implicit Data Type
Conversion. Tipe data yang secara otomatis diubah oleh Oracle
adalah dari VARCHAR2 atau CHAR mejadi NUMBER, VARCHAR2
atau CHAR menjadi DATE, dan NUMBER menjadi VARCHAR2,
serta DATE menjadi VARCHAR2.
Meskipun secara otomatis Oracle mengubah data secara implisit,
tetap dianjurkan untuk mengubah tipe data secara eksplisit, agar
kinerja Oracle tetap baik. Explisit Data Type Conversion akan

mengubah NUMBER menjadi CHARACTER, dan CHARACTER

menjadi DATE serta sebaliknya. Explicit Data Type Conversion bisa
digambarkan sebagai berikut:
Data Type Conversion bisa digambarkan sebagai berikut:
SQL menyediakan tiga fungsi untuk mengkonversikan sebuah nilai
dari satu tipe data menjadi tipe data yang lain, yaitu:

42 Laboratorium Komputer – STIKOM

 Structure Query Language

Fungsi Kegunaan
TO_CHAR(number|date, [ fmt], Mengubah sebuah angka atau
[nlsparams] ) tanggal menjadi VARCHAR2
dengan format fmt.

Number conversion: parameter

nlsparams menunjukkan karakter
yang dikembalikan berdasarkan
format angka.

Date conversion: parameter

nlsparams menunjukkan bahasa
yang digunakan untuk penamaan
bulan dan hari serta singkatan
yang digunakan.
TO_NUMBER(char, [ fmt], [nlsparams] Mengubah karakter menjadi
) angka sesuai dengan format fmt.
Parameter nlsparams
kegunaannya sama seperti pada
fungsi TO_CHAR
TO_DATE(char, [ fmt], [nlsparams] ) Mengubah karakter yang
mewakili sebuah tanggal menjadi
format tanggal sesuai fmt. Jika fmt
tidak dituliskan, maka formatnya
adalah DD-MON-YY.
Parameter nlsparams
kegunaannya sama seperti pada
fungsi TO_CHAR

Format model harus dituliskan dengan menggunakan tanda petik

tunggal, case-sensitive, dapat diisi dengan format tanggal yang
valid. Antara tanggal dan format tanggal harus dipisahkan dengan
tanda koma. Secara otomatis nama hari dan nama bulan
ditambahkan dengan spasi, untuk menghilangkan spasi dapat
digunakan mode fm.

Laboratorium Komputer – STIKOM 43

Structure Query Language

Berikut adalah elemen dari format tanggal:

Elemen Hasil
YYYY 4 digit tahun dalam angka
YEAR Ejaan tahun
MM 2 digit angka untuk bulan
MONTH Nama bulan
MON 3 digit terdepan dari nama bulan
DY 3 digit terdepan dari nama hari
DAY Nama hari
DD 2 digit urutan hari dalam satu bulan

Contoh :

1. Menampilkan tanggal hari ini dengan format dd-mm-yyyy misal

14-07-2002.
Select to_char(sysdate,‟dd-mm-yyyy‟)
From [Link];

2. Menampilkan data 0011001 yang semula bertipe string menjadi

bertipe number(numeric).
Select to_number(„0011001‟)
From [Link];

3. Menampilkan nama dan alamat mahasiswa yang berulang tahun

pada tanggal 7 Juli 1975.
Select Nm_Mhs, Alm_Mhs
From Mahasiswa
Where tgl_lahir = to_date („Jul 7,1975‟,
‟Month dd, yyyy‟);

4.6. Fungsi General

Fungsi ini terdiri dari 2 macam, yaitu fungsi yang digunakan untuk
menangani nilai NULL dan fungsi yang merupakan ekspresi
kondisi.
Berikut dalah daftar dari General Functions yang digunakan untuk
menangani nilai NULL:
Fungsi Keterangan
NVL(expr1, expr2) Mengubah nilai NULL menjadi
44 Laboratorium Komputer – STIKOM
 Structure Query Language

suatu nilai tertentu

NVL2(expr1, expr2, expr3) Jika expr1 tidak bernilai NULL,
maka expr2 akan ditampilkan, jika
expr1 bernilai NULL, maka expr3
yang ditampilkan.
NULLIF(expr1, expr2) Membandingkan nilai dari expr1
dan expr2, jika nilai expr1 = expr 2,
maka hasilnya adalah NULL. Jika
expr1 <> expr2, maka expr1 yang
ditampilkan.
COALESCE(expr1, expr2, ..., exprn) Menampilkan ekspresi pertama
yang tidak bernilai NULL

Selain menangani nilai NULL, General Functions juga dapat

mewakili suatu kondisi (IF-THEN-ELSE). Perintah yang digunakan
dalam ekspresi kondisi ini adalah: CASE dan DECODE.
Penulisan dari ekspresi CASE adalah sebagai berikut:
CASE expr WHEN comparison_expr1
THEN return_expr1
[WHEN comparison_expr2
THEN return_expr2
WHEN comparison_exprn
THEN return_exprn
ELSE else_expr]
END
Contoh :
Menampilkan nim mhs dan nilai huruf berdasarkan
nilai akhirnya.
select nim,
(case
when ((tugas*0.2) + (uts*0.3) +
(uas*0.5)) >= 80
then 'A'
when ((tugas*0.2) + (uts*0.3) +
(uas*0.5)) >= 65
then 'B'
when ((tugas*0.2) + (uts*0.3) +
(uas*0.5)) >= 55
then 'C'
when ((tugas*0.2) + (uts*0.3) +
(uas*0.5)) >= 45
Laboratorium Komputer – STIKOM 45
Structure Query Language

then 'D'
else 'E'
end) "Keterangan"
from nilai;

Pada ekspresi CASE, Oracle akan membandingkan apakah ada nilai

yang sesuai antara expr dan comparison_expr. Apabila ada nilai
yang sama maka hasilnya adalah return_expr dari pasangan WHEN
... THEN yang sesuai. Apabila tidak ada nilai yang sama, maka
hasilnya adalah else_expr jika ada kalimat ELSE, jika kalimat ELSE
tidak ada maka hasilnya adalah NULL.
Nilai dari semua return_expr dan else_expr tidak boleh NULL,
selain itu semua ekspresi pada CASE harus sama tipe datanya.
Fasilitas yang hampir sama fungsinya dengan CASE adalah
DECODE. Fungsi DECODE dituliskan dengan format sebagai
berikut:
DECODE(col|expression,
search1, result1[,search2, result2, ...]
[, default] )

Sama dengan fungsi IF-THEN-ELSE pada umumnya, fungsi

DECODE akan membandingkan nilai col atau expression dengan
nilai dari setiap search, apabila nilainya sama maka hasilnya adalah
result, tetapi jika tidak ada nilai yang sama, maka yang
dikembalikan adalah nilai default, dan apabila nilai default tidak
didefinisikan maka hasilnya adalah NULL.
Contoh :
Menampilkan nama,alamat, jenis kelamin dari mahasiswa yang ada
dimana untuk column Jenis Kelamin ditampilkan dengan format
tampilan Pria dan Wanita.
Select Nm_Mhs,Alm_Mhs,
decode(Jns_Kelamin,‟P‟,‟Pria‟,
‟W‟,‟Wanita‟)
From Mahasiswa;

46 Laboratorium Komputer – STIKOM

 Structure Query Language

Latihan
1 Buatlah sebuah query untuk menampilkan nim, kode kuliah
dan nilai akhir yang telah dibulatkan dengan fungsi round
untuk mahasiswa S1 angkatan 2000.

NIM KODE NILAI AKHIR

00410104001 BD-002 80
00410104002 BD-002 68
00410104003 BD-002 77

2 Buatlah sebuah query untuk menampilkan nama mahasiswa

dan statusnya dengan mengubah tampilan jika M menjadi
Menikah dan jika BM menjadi Belum Menikah, dengan
ketentuan untuk mahasiswa S1 angkatan 97.

NAMA MAHASISWA STATUS

Ananta Jodi Menikah
Bagaskara Belum Menikah
Dewi Yanti Belum Menikah

3 Buatlah sebuah query untuk menampilkan nama mahasiswa

dan tanggal lahirnya untuk mahasiswa yang lahir 1 bulan
setelah bulan september.

NAMA MAHASISWA TANGGAL LAHIR

Dewi Yanti 15 October 1976

4 Buatlah sebuah query untuk menampilkan nama dan tanggal

lahirnya dalam format seperti contoh dibawah ini.
Misalkan 05-JAN-79 menjadi Friday, the Fifth of January, 1979.
Yang ditampilkan hanya mahasiswa S1 angkatan 99.

NAMA
TANGGAL LAHIR
MAHASISWA
Gamalia Friday, the Fifth of January, 1979
Herlambang Saturday, the Tenth of February, 1979

Laboratorium Komputer – STIKOM 47

Structure Query Language

5 Buatlah sebuah query untuk menampilkan nama dan tanggal

lahirnya, dengan ketentuan untuk mahasiswa yang lahir
minggu pertama, quartal ke tiga dan pada bulan genap.

NAMA MAHASISWA TANGGAL LAHIR

Agung Laksono 07 August 1983

6 Buatlah sebuah query untuk menampilkan nama mahasiswa

yang umurnya diantara 30 sampai 35 tahun.

NAMA MAHASISWA UMUR

Ananta Jodi 31
Bagaskara 30
Dewi Yanti 30

7 Buatlah sebuah query untuk menampilkan kode kuliah, nim

dan nilai akhir dan keterangan, dengan ketentuan hanya
mahasiswa S1 angkatan 99.

KODE NIM NILAI AKHIR KETERANGAN

BD-002 99410104001 66 Sedang
BD-002 99410104002 80 Bagus
KD-003 99410104001 69 Sedang

Keterangan :

80 – 100 Bagus
50 – 79 Sedang
0 – 49 Kurang

Catatan
S1 = 41010 (Nim) dan D3 = 39010 (Nim)
Nilai Akhir = (Tugas * 0.2) + (Uts * 0.3) + (Uas * 0.5)

48 Laboratorium Komputer – STIKOM

 Structure Query Language

MODUL 5
REPORTING AGGREGATED DATA
USING THE GROUP FUNCTIONS

Kreatif tanpa keberanian untuk memulai tidak

lebih dari berangan-angan, sebaliknya penuh
inisiatif tanpa kreatifitas hanya mengikuti apa
yang dilakukan orang lain. Berani memulai
disertai dengan kreatifitas menjadikan diri anda
proaktif.
- Hotmian Haro -

Tujuan :
Praktikan memahami penggunaan Group Functions. suatu tabel

Materi :
avg, count, max, min, stddev, sum
Nesting Group Function

Referensi :
Oracle Database 10g : SQL Fundamentals I, Volume I, Student ■
Guide

Laboratorium Komputer – STIKOM 49

Structure Query Language

5.1. Group Functions

Group functions bekerja pada sekelompok baris dan menghasilkan
satu nilai untuk setiap kelompok. Yang termasuk dalam group
functions adalah:

Fungsi Keterangan
AVG([DISTINCT|ALL] n) Rata-rata nilai, nilai NULL tidak
diperhitungkan
COUNT({*|[DISTINCT|ALL]expr}) COUNT (*) menghasilkan
jumlah baris pada sebuah tabel,
termasuk data yang bernilai
NULL dan data yang nilainya
duplikat.

COUNT(expr) menghasilkan
jumlah baris dengan
mengabaikan nilai NULL pada
kolom yang diidentifikasikan
pada expr.

COUNT(DISTINCT expr)
menghasilkan jumlah baris
dengan mengabaikan nilai
NULL dan nilai yang sama pada
kolom yang diidentifikasikan
pada expr

MAX([DISTINCT|ALL]expr) Nilai maksimum dari expr,

dengan mengabaikan nilai
NULL
MIN([DISTINCT|ALL]expr) Nilai minimum dari expr,
dengan mengabaikan nilai
NULL
STDDEV([DISTINCT|ALL] n) Standard deviasi dari n dengan
mengabaikan nilai NULL
SUM([DISTINCT|ALL] n) Jumlah dari n dengan
mengabaikan nilai NULL
VARIANCE([DISTINCT|ALL] n) Variance dari n dengan
mengabaikan nilai NULL

50 Laboratorium Komputer – STIKOM

 Structure Query Language

5.2. Penulisan Group Function

Penulisan dari perintah group functions adalah sebagai berikut:
SELECT [column,] group_function(column),
...
FROM table
[WHERE condition]
[GROUP BY column]
[HAVING group_condition]
[ORDER BY column]

Perintah DISTINCT memberikan pengaruh pada hasil perhitungan

group functions, karena data yang bernilai sama akan hanya
dihitung sekali. Apabila semua data (tanpa menghiraukan yang
duplikat) akan dihitung, maka digunakan ALL, ALL tidak perlu
dituliskan karena merupakan nilai default.
Semua group functions mengabaikan nilai NULL pada
perhitungannya, untuk menyertakan nilai NULL pada suatu group
functions gunakan fungsi NVL, NVL2 atau COALESCE.
HAVING dan WHERE fungsinya hampir sama yaitu untuk
membatasi row yang akan ditampilkan. Perbedaannya terletak pada
kondisi yang mengikutinya, apabila kondisi yang digunakan
mengandung group functions maka kondisi tersebut harus
diletakkan dibelakang HAVING. WHERE tidak memiliki
kemampuan untuk kondisi yang mengandung group functions.
Contoh :
1. Tampilkan Jumlah Matakuliah yang diselenggarakan.
Select count(*) “Jumlah Mata Kuliah” from
MataKuliah;

2. Tampilkan Jumlah Matakuliah yang di selenggarakan pada

semester 3.
Select count(*) “Jumlah Mata Kuliah”
from MataKuliah where Semester = 3;

3. Tampilkan Semester dan Jumlah Matakuliah yang di

selenggarakan pada tiap-tiap semester.

Laboratorium Komputer – STIKOM 51

Structure Query Language

Select Semester, count(*) “Jumlah Mata

Kuliah” from MataKuliah
Group By Semester;

4. Tampilkan Semester dan Total SKS yang di selenggarakan pada

tiap-tiap semester.
Select Semester, Sum(SKS) “Jumlah SKS”
from MataKuliah
Group By Semester;

5. Tampilkan Semester dan Total SKS yang di selenggarakan pada

tiap-tiap semester dan tiap semesternya mempunyai total SKS
lebih dari 6 SKS.
Select Semester, Sum(SKS) “Jumlah SKS”
from MataKuliah
Group By Semester
Having Sum(SKS) > 5;

6. Tampilkan nama mahasiswa dengan urutan abjad paling awal

dan paling akhir.
Select min(nm_mhs) AWAL, max(nm_mhs)
AKHIR
From mahasiswa;

7. Tampilkan kode mata kuliah dan rata-rata nilai akhirnya.

Select kode_kuliah,
avg((tugas*0.2) + (uts*0.3) + (uas*0.5))
"nilai akhir"
From nilai
Group by kode_kuliah;

8. Tampilkan total mahasiswa tiap angkatan mulai dari 1998

sampai dengan 2000.
Select
sum(decode(substr(nim,1,2),'98',1,0))
"Angkatan 1998",
sum(decode(substr(nim,1,2),'99',1,0))
"Angkatan 1999",
sum(decode(substr(nim,1,2),'00',1,0))
"Angkatan 2000"
From mahasiswa;
52 Laboratorium Komputer – STIKOM
 Structure Query Language

Latihan
1. Buatlah sebuah query untuk menampilkan total mahasiswa S1
angkatan 99 yang menempuh mata kuliah dengan kode kuliah
BD-002.
JUMLAH MAHASISWA
2
2. Buatlah sebuah query untuk menampilkan nim, kode kuliah dan
rata-rata nilai akhir untuk mahasiswa S1 angkatan 98 diurutkan
berdasarkan Kode Kuliah.
NIM KODE RATA2 NILAI AKHIR
98410104001 AI-001 68.5
98410104002 AI-001 76
98410104001 KD-003 73.5
98410104002 KD-003 75.5
3. Buatlah sebuah query untuk menampilkan kode kuliah dan rata-
rata nilai berdasarkan kode kuliah dan tampilkan rata-rata
nilainya diantara 70 sampai dengan 80.
KODE RATA-RATA NILAI
BD-002 72.22
KD-003 72.42
4. Buatlah sebuah query untuk menampilkan nilai tertinggi dari
rata-rata nilai akhir per matakuliah.
NILAI RATA2 TERTINGGI MK
72.42
5. Buatlah sebuah query untuk menampilkan total mahasiswa
berdasarkan kode matakuliah dan range nilai tugas (50-60, 61-70,
71-80, 81-100).

KODE 50-60 61-70 71-80 81-100

AI-001 2 3 2 1
BD-002 0 5 4 0
KD-003 1 2 1 2

Laboratorium Komputer – STIKOM 53

Structure Query Language

6. Buatlah sebuah query untuk menampilkan jumlah mahasiswa

berdasarkan angkatan dan kode matakuliah yang ditempuh,
dengan ketentuan untuk mahasiswa angkatan 98 dan 99.

KODE 1998 1999 TOTAL MAHASISWA

AI001 3 1 4
BD002 1 3 4
KD003 3 1 4

Catatan
S1 = 41010 (Nim) dan D3 = 39010 (Nim)
Nilai Akhir = (Tugas * 0.2) + (Uts * 0.3) + (Uas * 0.5)

54 Laboratorium Komputer – STIKOM

 Structure Query Language

MODUL 6
DISPLAYING DATA FROM MULTIPLE
TABLES

Kecemerlangan adalah hasil daripada sikap

yang ingin senantiasa melakukan yang terbaik.
- Hotmian Haro -

Tujuan :
Praktikan dapat menampilkan data dari beberapa table yang terikat
oleh suatu relasi.

Materi :
Join tabel (Equijoins, Non-equijoins, Outer joins, Self-joins, Cross
joins, Natural joins, Full (or two-sided) outer joins

Referensi :
Oracle Database 10g : SQL Fundamentals I, Volume I, Student ■
Guide

Laboratorium Komputer – STIKOM 55

Structure Query Language

6.1. Definisi Join

Join tabel digunakan untuk menampilkan data yang berasal dari dua
tabel atau lebih.

6.2. Syntax Dasar Join

Select [Link],
[Link], … … …
From table1, table2
Where table1.column1 = table2.column2;

Pada Oracle di kenal 5 tipe join yaitu Cartesian Product, Equijoin,

Non-equijoin, Outer join dan Self join.
1. Cartesian Product
Pada Cartesian Product / cross join hasil query berupa semua record
dari tabel disebelah kiri dimana masing - masing record dari tabel di
sebelah kiri akan dikombinasikan dengan semua record dari tabel di
sebelah kanan.
Menampilkan nim, kode kuliah, uts dan uas dari tabel mahasiswa
dan tabel nilai.
select [Link], kode_kuliah, uts, uas from
mahasiswa m, nilai n

hasil : 299 rows. (13 data mhs x 23 data nilai)

2. EquiJoin
Digunakan untuk menggabungkan dua buah tabel atau lebih
dengan memenuhi suatu syarat referensial tertentu.

1. Menampilkan nim, kode kuliah, uts dan uas dengan

menggunakan where.
select [Link], kode_kuliah, uts, uas from
mahasiswa m, nilai n
where [Link] = [Link];

2. Menampilkan nim, kode kuliah, uts dan uas dengan

menggunakan join on.

56 Laboratorium Komputer – STIKOM

 Structure Query Language

select [Link], kode_kuliah, uts, uas

from mahasiswa m join nilai n
on [Link] = [Link];

3. Menampilkan nim, kode kuliah, uts dan uas dengan

menggunakan using.
select nim, kode_kuliah, uts, uas
from mahasiswa join nilai
using (nim);

4. Menampilkan nim, kode kuliah, uts dan uas dengan ketentuan

kode kuliah AI-001 dan nilai akhir lebih besar sama dengan 70.
select [Link], kode_kuliah, uts, uas
from mahasiswa m, nilai n
where [Link] = [Link] and
kode_kuliah = 'AI-001'
and (tugas*0.2) + (uts*0.3) + (uas*0.5)
>= 70;

5. Menampilkan nama matakuliah dan rata-rata nilai akhir untuk

nilai akhir dibawah 70.
select nm_kuliah,
avg((tugas*0.2) + (uts*0.3) + (uas*0.5))
"Rt2 NA"
from matakuliah mk, nilai n
where mk.kd_kuliah = n.kode_kuliah
group by nm_kuliah
having avg((tugas*0.2) + (uts*0.3) +
(uas*0.5)) < 70;

3. Non-equijoin
Digunakan untuk menggabungkan dua buah tabel atau lebih
dimana sebenarnya tidak terdapat hubungan referensial antara
tabel-tabel tersebut
4. Outer Join
Left outer join
Hasil dari query Left outer join berupa semua record dari tabel
sebelah kiri yang telah di spesifikasikan di statement LEFT OUTER
jadi tidak hanya record yang sesuai dengan hasil joinnya saja.

Laboratorium Komputer – STIKOM 57

Structure Query Language

Apabila record pada tabel sebelah kiri tidak ada yang sama dengan
record pada tabel sebelah kanan maka hasil query pada semua field
yang berasal dari tabel sebelah kanan akan berisi null value.
Contoh :
select Nm_Mhs, kode_kuliah, uts, uas
from mahasiswa m left outer join nilai n
on [Link] = [Link];

Right outer join

Sebuah Right outer join adalah kebalikan dari Left outer join. Semua
record dari tabel disebelah kanan akan ditampilkan. Nilai Null akan
ditampilkan untuk semua field dari tabel sebelah kiri apabila record
dari tabel sebelah kanan tidak ada yang cocok dengan record tabel
sebelah kiri.
Contoh :
select Nm_Mhs, kode_kuliah, uts, uas
from mahasiswa m right outer join nilai n
on [Link] = [Link];

Full outer join

Pada Full join hasil query akan menghasilkan semua record dari
kedua tabel baik sebelah kiri maupun kanan.
Apabila record di salah satu tabel tidak ada yang cocok dengan
record di tabel yang lain, maka semua list dari tabel yang lain itu
akan di isi dengan null [Link] apabila ada yang cocok maka
semua column dari kedua tabel akan ditampilkan secara
keseluruhan.
Contoh :
select Nm_Mhs, kode_kuliah, uts, uas
from mahasiswa m full outer join nilai n
on [Link] = [Link];

5. Self Join
Join yang melibatkan tabel yang sama. Untuk ini alias harus dipakai.
58 Laboratorium Komputer – STIKOM
 Structure Query Language

Contoh :
Menampilkan nama mahasiswa dan tanggal lahirnya setelah
Gamalia.
select mhs1.nm_mhs, mhs1.tgl_lahir
from mahasiswa mhs1 join mahasiswa mhs2
on (mhs2.nm_mhs = 'Gamalia')
Where mhs2.tgl_lahir < mhs1.tgl_lahir;

Laboratorium Komputer – STIKOM 59

Structure Query Language

Latihan
1. Buatlah sebuah query untuk menampilkan nama mahasiswa,
nama mata kuliah, nilai UTS dan UASnya untuk mahasiswa
yang mengambil mata kuliah pada semester 3.

NAMA MATA KULIAH UTS UAS

Dahlia Structure Query Language 65 80
Indra Askara Structure Query Language 75 85
Juliet Structure Query Language 70 65
….. ….. ….. …..
Herlambang Structure Query Language 70 90

2. Buatlah sebuah query untuk menampilkan nama mahasiswa,

nama mata kuliah, UTS dan UASnya untuk mahasiswa S1
angkatan 97 yang mengambil mata kuliah Aplikasi Internet dan
Structure Query Language.

NAMA MATA KULIAH UTS UAS

Ananta Jodi Aplikasi Internet 50 80
Ananta Jodi Structure Query Language 50 80
Bagaskara Aplikasi Internet 60 85
Dewi Yanti Aplikasi Internet 70 50

3. Buatlah sebuah query untuk menampilkan nama mahasiswa,

nama mata kuliah dan nilai akhir untuk mahasiswa S1 angkatan
98 yang nilai akhirnya diantara 65 dan 75.

NAMA MATA KULIAH NILAI AKHIR

Erlangga Robi Aplikasi Internet 68.5
Erlangga Robi Komunikasi Data 73.5
4. Buatlah sebuah query untuk menampilkan nama mahasiswa dan
bulan dengan ketentuan untuk mahasiswa yang lahir pada
bulan yang sama dengan Firmansyah.

NAMA TGL LAHIR

Erlangga Robi 25-MAR-78
Indra Askara 14-MAR-80
Firmansyah 15-MAR-79

60 Laboratorium Komputer – STIKOM

 Structure Query Language

5. Buatlah sebuah query untuk menampilkan nama mahasiswa,

nama mata kuliah, nilai akhir dan grade, grade didapat dari
tabel grade, field level_grade. Query tersebut urutkan
berdasarkan nama mata kuliah.

NILAI
MATA KULIAH NAMA GR
AKHIR
Aplikasi Internet Dewi Yanti 58 C-
Aplikasi Internet Firmansyah 62 C
..... ..... ..... .....
Komunikasi Data Bagaskara 67 C+
Komunikasi Data Florenzia 76 B
..... ..... ..... .....
Structure Query Language Juliet 68 C+

6. Buatlah sebuah query untuk menampilkan jumlah mahasiswa

berdasarkan nilai gradenya dan mata kuliah yang ditempuh.

MATA
A+ A A- B+ B B- C+ C C- D
KULIAH
Komunikasi
0 0 0 0 2 2 2 0 0 0
Data
Aplikasi
0 0 0 0 2 2 2 1 1 0
Internet
Structure
Query 0 0 0 2 2 1 4 0 0 0
Language

7. Buatlah sebuah query untuk menampilkan nim mahasiswa,

nama mahasiswa dan jumlah sks yang ditempuh untuk
mahasiswa S1 urutkan mulai dari yang jumlah sksnya tertinggi.

NIM NAMA SKS

9741010400 Ananta Jodi 10
99410104001 Gamalia 7
..... ..... .....
..... ..... .....

Laboratorium Komputer – STIKOM 61

Structure Query Language

99410104002 Herlambang 4
97410104003 Dewi Yanti 3

Catatan
S1 = 41010 (Nim) dan D3 = 39010 (Nim)
Nilai Akhir = (Tugas * 0.2) + (Uts * 0.3) + (Uas * 0.5)

62 Laboratorium Komputer – STIKOM

 Structure Query Language

MODUL 7
USING SUBQUERIES TO SOLVE QUERIES
AND USING THE SET OPERATORS

Nilai hidup harus diukur dengan garis yang

lebih mulia yaitu kerja
dan bukannya usia.
- Hotmian Haro -

Tujuan :
Praktikan memahami penggunaan Subquery dan memahami
penggunaan Union, Union All, Intersect, Minus

Materi :
Subquery (single-row subquery dan multiple-row subquery)
Union, Union All, Intersect, Minus

Referensi :
Oracle Database 10g : SQL Fundamentals I, Volume I, Student ■
Guide

Laboratorium Komputer – STIKOM 63

Structure Query Language

7.1. Subquery
Subquery adalah sebuah query (operasi SELECT) yang mengandung
query lain di dalamnya. Subquery sangat berguna untuk
menghasilkan query yang nilai dari kondisinya merupakan nilai
yang belum diketahui.
Menggunakan subquery :

Main Query
“Tampilkan nama dan alamat Mahasiswa yang satu angkatan dengan
mahasiswa yang bernama Bagaskara ?”

SubQuery
“Angkatan Berapa mahasiswa yang
bernama Bagaskara ?”

Contoh
1. Menampilkan nim, nama dan alamat Mahasiswa yang satu
angkatan dengan mahasiswa yang bernama Bagaskara.
select nim, nm_mhs, alm_mhs
from mahasiswa
where substr(nim,1,2) =
(select substr(nim,1,2) from mahasiswa
where nm_mhs = 'Bagaskara');

2. Menampilkan nim, nama dan alamat Mahasiswa yang satu

angkatan dengan mahasiswa yang bernama Bagaskara dan
Gamalia.
select nim, nm_mhs, alm_mhs
from mahasiswa
where substr(nim,1,2) in (select
substr(nim,1,2) from mahasiswa
where nm_mhs in ('Bagaskara','Gamalia'));

64 Laboratorium Komputer – STIKOM

 Structure Query Language

3. Menampilkan nim, nama dan alamat Mahasiswa yang satu

angkatan dengan mahasiswa yang bernama Bagaskara dan
Rabeca dan ditampilkan selain Bagaskara dan Rabeca.
select nim, nm_mhs, alm_mhs
from mahasiswa
where substr(nim,1,2) in
(select substr(nim,1,2) from mahasiswa
where nm_mhs in
('Bagaskara','Gamalia')) And
nm_mhs not in ('Bagaskara','Rabeca');

Untuk mencoba contoh union, union all, intersect dan minus copy
terlebih dahulu tabel music dan sport dari user trainer01. Adapun
dibawah ini adalah data dari tabel music dan sport.
select * from music; select * from Sport;
Hasilnya : Hasilnya :

NAMA NAMA
Amir Budi
Budi Dodi
Cica Heru
Dodi Sisca
Elang Karin

7.2. UNION
Menggabungkan isi dua tabel, data yang sama ditampilkan satu kali.

select * from music NAMA

union Amir
select * from sport; Budi
Cica
Hasilnya :
Dodi
Elang
Heru
Karin
Sisca

Laboratorium Komputer – STIKOM 65

Structure Query Language

7.3. UNION ALL

Menggabungakan isi dua tabel, semua data ditampilkan.

select * from music

union All
select * from sport;

Hasilnya :

NAMA
Amir
Budi
Cica
Dodi
Elang
Budi
Dodi
Heru
Sisca
Karin

7.4. INTERSECT
Menampilkan data yang terdapat pada tabel pertama, juga terdapat
pada tabel kedua.

select * from music

intersect
select * from sport;

Hasilnya :

NAMA
Budi
Dodi

66 Laboratorium Komputer – STIKOM

 Structure Query Language

7.5. MINUS
Menampilkan data yang terdapat pada tabel pertama tetapi tidak
terdapat pada tabel kedua.

select * from music

minus
select * from sport;

Hasilnya :

NAMA
Amir
Cica
Elang

select * from sport

minus
select * from music;

Hasilnya :

NAMA
Heru
Karin
Sisca

Laboratorium Komputer – STIKOM 67

Structure Query Language

Latihan
1. Buatlah sebuah query untuk menampilkan nim, nama dan
alamat mahasiswa, dengan ketentuan untuk mahasiswa yang
lahir pada bulan yang sama dengan Erlangga Robi, dan
tampilkan selain Erlangga Robi.
NIM NAMA MAHASISWA ALAMAT
00410104001 Indra Askara Jl. Bugenvil 17
98390104001 Firmansyah Jl. Bugenvil 10
2. Buatlah sebuah query untuk menampilkan nama mata kuliah
dan jumlah mahasiswanya, dengan ketentuan untuk mata kuliah
yang jumlah mahasiswanya paling besar diantara mata kuliah
lainnya.
NAMA MATA KULIAH JUMLAH MAHASISWA
Structure Query Language 9
3. Buatlah sebuah query untuk menampilkan nama mahasiswa,
kode kuliah dan uts dengan ketentuan untuk nilai uts tertinggi
pada matakuliah Structure Query Language.
NAMA MAHASISWA KODE UTS
Karmila BD-002 80
4. Buatlah sebuah query untuk menampilkan kode_kuliah dan
rata2 Nilai Akhir dengan ketentuan yang rata2 Nilai Akhirnya
paling tinggi diantara semua rata2 Nilai Akhir tiap Kode Kuliah.
KODE RATA2 NILAI AKHIR
KD-003 72.42
5. Buatlah sebuah query untuk menampilkan nama mata kuliah
yang tidak di tempuh oleh mahasiswa yang bernama Bagaskara.
NAMA MATA KULIAH
Structure Query Language

68 Laboratorium Komputer – STIKOM

 Structure Query Language

6. Buatlah sebuah query untuk menampilkan nama mahasiswa dan

nilai akhir tertinggi berdasarkan mata kuliah Aplikasi Internet
dan Komunikasi Data dan tampilkan sesuai urutan nilai
tertinggi dari nilai akhir mata kuliah.
NAMA NAMA MATA KULIAH NILAI AKHIR
Florenzia Aplikasi Internet 76
Firmansyah Komunikasi Data 77

Catatan
S1 = 41010 (Nim) dan D3 = 39010 (Nim)
Nilai Akhir = (Tugas * 0.2) + (Uts * 0.3) + (Uas * 0.5)

Laboratorium Komputer – STIKOM 69

Structure Query Language

70 Laboratorium Komputer – STIKOM

 Structure Query Language

MODUL 8
CREATE VIEWS AND INDEXS

Selagi kita mencoba dan berusaha, selagi itulah

kita akan
peroleh apa yang kita inginkan.
- Hotmian Haro -

Tujuan :
Praktikan memahami bagaimana membuat View dan Index

Materi :
View dan Index

Referensi :
Oracle Database 10g : SQL Fundamentals I, Volume I, Student ■
Guide

Laboratorium Komputer – STIKOM 71

Structure Query Language

8.1. Definisi View

adalah Tabel yang dibangun dari satu atau beberapa tabel yang
sudah ada. Secara fisik, View tidak membuat penyimpanan data
seperti tabel, melainkan hanya menyimpan referensi / pointer ke
record pada tabel-tabel yang berkaitan.
View disebut juga sebagai "Virtual table" .
8.2. Syntax dasar View
Create [or Replace] View NamaView
Kolom1, Kolom2) As
Select Namafield1, NamaField2,…..
From NamaTabel
Where Predikat
[with Check Option]

Contoh :
1. Membuat View dengan nama View1 untuk menampilkan Nim,
Nama dan Alamat mahasiswa.
Create View View1(No_Induk_Mahasiswa,
Nama_Mahasiswa,
Alamat_Mahasiswa) As
Select Nim, Nm_Mhs, Alm_Mhs from
Mahasiswa;

Atau
Create or Replace View View1 As
Select Nim as No_Induk_Mahasiswa,
Nm_Mhs as Nama_Mahasiswa,
Alm_Mhs as Alamat_Mahasiswa from
Mahasiswa;

72 Laboratorium Komputer – STIKOM

 Structure Query Language

2. Memperbaiki View1 untuk menampilkan Nim, Nama dan

Alamat mahasiswa, dengan ketentuan untuk mahasiswa
angkatan 98.
Alter View View1
No_Induk_Mahasiswa, Nama_Mahasiswa,
Alamat_Mahasiswa) As
Select Nim, Nm_Mhs, Alm_Mhs
from Mahasiswa
Where Substr(Nim,1,2) = „98‟;

3. Menampilkan View :
Select * from View1;

4. Menghapus View :
Drop View Nama_View;

5. Membuat View dengan nama View1 untuk menampilkan

seluruh data mahasiswa dengan ketentuan untuk mahasiswa
yang namanya mengandung huruf A
Create or Replace View View2 As
select * from mahasiswa
where nm_mhs like '%a%' or
nm_mhs like '%A%' ;

6. View2 dapat diisi dengan data sebagai berikut :

insert into view2
values ('01410100081','Dono',
'jl. tenggilis 20',
'25-DEC-1982','W','B');

Alasannya dapat diisi, dikarenakan kondisi (where) pada view

tidak diperhatikan pada saat ada perintah insert. Walupun pada
view tersebut ada kondisi nama harus mengandung huruf a atau
A.
Kondisi (where) pada view2 hanya diperhatikan pada saat ada
perintah select, sehingga jika ditampilkan maka data dono tidak
tampil.

Laboratorium Komputer – STIKOM 73

Structure Query Language

7. Agar kondisi where pada view diperhatikan pada saat ada

perintah insert, update, delete dan select, maka ditambahkan
pada akhir statement where ditambahkan syntax with check
option. Adapun contohnya sebagai berikut :
Create or Replace View1 As
select * from mahasiswa
where nm_mhs like '%a%' or
nm_mhs like '%A%' with check option

8.3. Ketentuan penggunaan view

Melalui View dapat dilakukan Insert, Update, dan Delete kecuali
contoh dibawah ini :
Tidak dapat Melakukan Insert jika mengabaikan nilai Not Null pada
tabel dari View tersebut.
Contoh :
Create or Replace View View1 as
select Nim, Nm_Mhs from Mahasiswa;

Tidak dapat melakukan Insert atau Update jika salah satu kolom
dalam View merupakan hasil kalkulasi atau fungsi.
Contoh :
Create or Replace View View2 as
select Sum(SKS) “Jumlah SKS”
from Matakuliah;

Tidak dapat melakukan Insert atau Update atau Delete jika dalam
View terdapat Group By atau Distinct.
Contoh :
Create or Replace View View3 as
Select Semester, Sum(SKS) “Jumlah SKS”
from MataKuliah Group By Semester;

8.4. Keuntungan dari View

1. View membatasi untuk mengakses data karena View bisa
menampilkan kolom yang dipilih dari tabel.
74 Laboratorium Komputer – STIKOM
 Structure Query Language

2. View bisa digunakan untuk membuat query yang sederhana

untuk membatasi hasil dari query rumit. Sebagai contohnya,
view bisa digunakan untuk informasi query dari berbagai tabel
tanpa pemakai mengetahui bagaimana menulis pernyataan join.
3. View menyediakan kelompok akses pemakai ke data.

8.5. Index
Digunakan untuk mengurutkan data secara logis, meningkatkan
kecepatan pencarian dan operasi-operasi pengurutan. Index dalam
tabel biasanya disediakan pada kunci primer (Primary Key) dan
dapat juga digunakan pada kolom lain (selain primary key), apabila
mencari nilai-nilai dalam kolom lain biasanya tidak efisien.

8.6. Syntax dasar Index

CREATE INDEX nama_index
ON Tabel(kolom);

Menghapus Index :
Drop Index Nama_Index

Contoh :
Create index idx_mhs_nama
on mahasiswa(nm_mhs);

Ada 2 macam Index yaitu : Index non-unique dan Index unique.

1. Index non-unique dibuat berdasarkan field yang tidak unik
(bukan Primary Key) seperti contoh diatas bahwa nama adalah
Index non-unique.
2. Index unique dibuat berdasarkan constraint primary key.
Misalkan Nim.
untuk melihat semua daftar index pada data dictionary, ketik
perintah dibawah ini:
Select * from user_indexes;

Laboratorium Komputer – STIKOM 75

Structure Query Language

Untuk menampilkan struktur tabel index, ketik perintah dibawah

ini:
Desc user_indexes;

76 Laboratorium Komputer – STIKOM

 Structure Query Language

Latihan
1. Buatlah View1 untuk seluruh data mahasiswa dengan ketentuan
untuk mahasiswa yang lahir pada quartal ke dua.
2. Buatlah View2 untuk menampilkan nama mahasiswa dan nilai
akhir dengan ketentuan mahasiswa S1 yang mengambil xsmata
kuliah Aplikasi Internet dan nilai akhirnya diantara 60 dan 80.
3. Buatlah View3 untuk menampilkan Nama Mahasiswa dan Nilai
akhirnya, untuk mahasiswa D3 yang mempunyai Nilai Akhir
terendah pada mata kuliah Structure Query Language.
4. Buatlah Index nonunique untuk kolom nama matakuliah pada
tabel matakuliah.
5. Tampilkan Indexes dan uniquiness yang ada pada data
dictionary untuk tabel matakuliah.

Catatan
S1 = 41010 (Nim) dan D3 = 39010 (Nim)
Nilai Akhir = (Tugas * 0.2) + (Uts * 0.3) + (Uas * 0.5)

Laboratorium Komputer – STIKOM 77