modul praktikum basis datalab.ilkom.unila.ac.id/modul/semester genap/s1 ilmu...7 6.setelah file...
TRANSCRIPT
2
MODUL PRAKTIKUM BASIS DATA
OLEH
ASISTEN BASIS DATA
S1 ILMU KOMPUTER
JURUSAN ILMU KOMPUTER
FAKULTAS MATEMATIKA DAN ILMU PENGETAHUAN ALAM
UNIVERSITAS LAMPUNG
2017
3
DAFTAR ISI
Pertemuan 1 SQL Data Modeler ....................................................................................................... 4
Pertemuan 2 Relasi dalam SQL Data Modeler ................................................................................ 14
Pertemuan 3 Supertypes, Subtypes, dan Transferability ................................................................... 19
Pertemuan 4 Normalisasi dalam Database ....................................................................................... 28
Pertemuan 5 Arcs ............................................................................................................................ 41
Pertemuan 6 Hierarki dan Rekursif .................................................................................................. 47
Pertemuan 7 Historical Data Model ............................................................................................... 52
Pertemuan 8 Mapping .................................................................................................................... 54
Pertemuan 9 SQL SELECT * STATEMENT ........................................................................................ 65
Pertemuan 10 Syntax SQL ARITHMATIC,COLUMNS, ROWS .......................................................... 77
Pertemuan 11Logical Comparisons and Predence Rules ............................................................ 89
Pertemuan 12 Function ................................................................................................................. 102
Pertemuan 13 JOIN dan GROUP .................................................................................................... 124
Pertemuan 14 INSERT DAN CREATE TABLE ..................................................................................... 144
4
Pertemuan 1
Pengenalan
SQL Data Modeler
Tujuan Intruksional : Pokok Bahasan ini mengenalkan tentang SQL Data Modeler
Kompetensi Yang Diharapkan : Mahasiswa diharapkan memahami tentang SQL Data Modeler termasuk cara instalasi, tools - tools SQL Data Modeler, dan cara menggunakannya.
Waktu Pertemuan : 100 Menit
1.1 Dasar Teori
SQL Data Modeler adalah sebuah software yang disediakan oleh Oracle untuk mempermudah dalam merancang database.
1.2 Instalasi SQL Modeler
1.Untuk menginstal dan menggunakan SQL Developer Data Modeler, download file
datamodeler.zip pada situs oracle.com.
5
3.Pilih Developer Tools.
4.Pilih SQL Developer Data Modeler.
6
5.Klik Accept License Agreement kemudian download SQL Data Modeler sesuai dengan
sistem operasi yang digunakan.
7
6.Setelah file terdownload, unzip Data Modeler ke dalam direktori (folder) pilihan. lokasi direktori ini akan disebut sebagai <datamodeler_install>. Sebagai contoh, pada sistem Windows mungkin ingin memilih C: \ sebagai lokasi unzip.
7.Untuk menjalankan Data Modeler, klik dua kali datamodeler64.exe (Windows sistem 64-
bit) atau datamodeler.exe (sistem Windows 32-bit). Pada Linux dan Mac OS X sistem, jalankan sh datamodeler.sh.
8.Pada sistem Windows Jika Anda diminta untuk memasukkan nama path lengkap untuk
JDK, klik Browse dan temukan file JDK pada direktori Java yang telah terinstal. Sebagai contoh, pada sistem Windows mungkin file ada pada direktori: C: \ Program Files \ Java \ jdk1.7.0_51
9.SQL Data Modeler siap digunakan
8
1.3 Data Modeler User Interface
1.Untuk memulai pembuatan desain database, klik kanan pada Logical Model kemudian pilih show
9
2.Setelah muncul lembar Logical(untitled_) klik new entity untuk menambahkan entity pada lembar Logical(untitled_)
3.Isi kolom name dengan nama entity
10
4.Pilih attributes sehingga muncul dialog attribute seperti gambar, klik icon + untuk membuat atribut baru, kemudian isi kolom name dengan nama attribute, klik logical dan tentukan tipe data yang digunakan untuk attribute, ceklis dialogbox Primary UID jika attribute tersebut merupakan primary key, kemudian klik Apply
5.Klik icon + untuk menambahkan attribute lainnya, untuk tipe data seperti varchar akanmuncul
kolom size untuk menentukan ukuran data yang akan disimpan, jika sudah selesai klik OK
11
6.Buat tiga entity seperti contoh pada gambar
7.Untuk membuat relasi, klik icon relasi (contoh yang digunakan adalah relasi 1.N)
kemudian akan muncul dialog relation seperti gambar, isi kolom name untuk menentukan nama relasi, isi kolom Name on source dan Name on target, kemudian klik OK
12
9.Untuk mengubah tampilan menjadi lebih detail, klik view pada menubar, pilih Logical Diagram
Notation, kemudian pilih Bachman Notation
13
Tugas :
Buatlah Conceptual Model Database yang mengimplementasikan seluruh jenis relasi (One-to-Many, One-to-One, Many-to-Many) !
14
Pertemuan 2
Relasi dalam SQL Data Modeler
Tujuan Intruksional :
Pokok Bahasan ini mempelajari tentang penggunaan Relasi di dalam SQL Data Modeler
Kompetensi Yang Diharapkan :
Mahasiswa diharapkan memahami tentang penggunaan Relasi dalam SQL Data Modeler termasuk jenis-jenis relasi, dan pembagian relasi berdasarkan kardinalitas dan opsionalitas.
Waktu Pertemuan : 100 Menit
2.1 Pengertian Relasi
Relasi merupakan hubungan yang terjadi pada suatu tabel dengan tabel lainnya
yang mempresentasikan hubungan antar objek di dunia nyata dan berfungsi untuk
mengatur operasi suatu database.
1.2 Jenis-jenis Relasi
Dalam Oracle SQL Data Modeler, relasi antar tabel dibagi berdasarkan dua hal,
yaitu kardinalitas dan opsionalitas.
a. Kardinalitas (Cardinality)
Kardinalitas Relasi menunjukkan jumlah maksimum entitas yang dapat berelasi
dengan entitas pada himpunan entitas yang lain. Kardinalitas relasi merujuk
kepada hubungan maksimum yang terjadi dari himpunan entitas yang satu ke
himpunan entitas yang lain dan begitu juga sebaliknya.
15
a. Satu ke satu (One to One),
b. Satu ke Banyak / Banyak ke Satu (one to many / many to one),
c. Banyak ke Banyak (Many to Many).
b. Opsionalitas (Optionality)
Opsionalitas Relasi menunjukkan haruskah atau tidak harus adanya suatu relasi
antara satu entitas dengan entitas lainnya.
Contoh :
Cara Membacanya : Setiap Mahasiswa harus memilih satu jurusan, sedangkan
suatu jurusan tidak harus memiliki Mahasiswa.
16
Contoh Kasus 1:
Suatu Rumah Sakit ingin merancang database untuk menyimpan data rekam
medis pasien. Database terbesut memiliki ketentuan sebagai berikut:
1. Satu rekam medis hanya boleh dimiliki oleh satu orang pasien, sedangkan
satu orang pasien bisa memiliki lebih dari satu atau tidak memiliki rekam
medis.
2. Satu rekam medis hanya boleh memuat satu resep obat, tetapi boleh juga
tidak memuat resep obat.
3. Satu resep obat harus berisi minimal satu jenis obat.
Penyelesaian :
1. Kita perlu membuat 4 buah entity yaitu : Pasien, Rekam Medis, Resep, dan
Obat
2. Dari ketentuan 1, maka relasi yang akan terbentuk adalah :
3. Dari ketentuan 2, maka relasi yang akan terbentuk adalah :
17
4. Untuk ketentuan 3 , kita tidak bisa langsung me-relasikan tabel Resep dan
Obat, oleh karena itu kit perlu membuat tabel bantu.
18
TUGAS
Suatu fakultas merancang database untuk menyimpan data mahasiswa dan
dosen. Database terbesut memiliki ketentuan sebagai berikut:
1. Satu fakultas memiliki banyak jurusan, dan satu jurusan hanya dimiliki oleh
1 fakultas
2. Satu dosen hanya bisa mengepalai satu jurusan.
3. Satu dosen hanya bisa mengajar satu mata kuliah, sedangkan satu mata
kuliah bisa diajar oleh banyak dosen.
Buatlah Conceptual Diagram dari kasus di atas.
19
Pertemuan 3
Supertypes, Subtypes, dan Transferability
Tujuan Intruksional :
Pokok Bahasan ini mempelajari tentang Supertype, Subtype, dan Relasi
Transferability
Kompetensi Yang Diharapkan :
Mahasiswa diharapkan memahami tentang Supertype, Subtype, dan Relasi
Transferability termasuk mendefinisikan serta memberikan contoh Supertype.
Subtype dan Relasi Transferability
Waktu Pertemuan : 100 Menit
3.1 Pengertian Subtype, Supertype, dan Transferability
Subtype : Pengelompokan sebagian entitas instances dari suatu entitas
type yang memiliki attribute yang berbeda dengan kelompok
lain
Supertype : Sebuah entitas type umum yang memiliki satu atau lebih
subtypes.
Transferability : Relasi antar table yang menunjukkan bisa tidaknya suatu
instance
20
3.2 Relasi dan Subtypes
❖ Relasi pada tingkat supertype berarti semua subtypes ikut serta dalam
relasi tersebut
❖ Relasi pada tingkat subtype berarti hanya subtype tersebut yang terkait
dengan relasi yang dimaksud
Gambar 3.1 ER Diagram Relationship dengan Subtype/Supertype
21
3.3 Contoh Supertype dan Subtype
Contoh 1 :
Supertypes : Kendaraan
Subtypes :
• Motor
• ID
• Harga
• Ukuran Mesin
• Truk
• ID
• Harga
• Ukuran Mesin
• Kapasitas Bak
• Jenis Bak
• Mobil
• ID
• Harga
• Ukuran Mesin
• Jumlah Penumpang
attributs
milik semua
Kendaraan
Kendaraan_
Harga Kendaraan
Ukuran_Me
Truk Mobil
attributs
milik
Truk
Jenis_B Ukuran_B Jumlah_Penump
attributs
milik
Mobil
saja
Gambar 3.2 Contoh ER Diagram Kendaraan Subtype dan Supertype
22
Pada desain ER Diagram kendaraan diatas ada tidak terdapat entitas Motor. Entitas
Motor tidak ditulisakn karena dalam suatu supertype dapat memiliki entitas
instance yang bukan merupakan instance dari subtype yang manapun.
Gambar 3.3 Contoh ER Diagram Kendaraan Subtype dan Supertype
23
Contoh 2
Supertypes : Pasien
Subtypes :
• Rawat Inap
• ID
• Nama
• Tanggal Lahir
• Tanggal Masuk
• Tanggal Keluar
• Rawat Jalan
• ID Nama
• Tanggal Lahir
• Tanggal Kembali
Pasien_ID Nama Tanggal_Lahir
Pasien
Tangal_masuk
Pasien Rawat Inap
Pasien Rawat Jalan
Tanggal_Keluar
Tanggal_Kembali
Dari diagram yang dibuat tersebut, dapat diambil kesimpulan bahwa semua pasien
memiliki nama dan tanggal lahir, namaun hanya pasien rawat inap yang memiliki
tanggal masuk dan tanggal keluar.
24
3.4 Transferbilty dan Non-Transferbilty
3.4.1 Transferbilty
Dalam suatu desain database yang dibuat ada sebuah relasi antar entitas. Setiap
entitas yang berelasi dengan entitas lainnya saling berpengaruh. Dalam suatu
contoh nyata dalam pembuatan desain database Kepegawaian terdapat hubungan
antara EMPLOYEE dan DEPARTMENT. Setiap EMPLOYEE ditugaskan ke sebuah
DEPARTMENT. Dalam hal ini seorang EMPLOYEE bisa dipindah tugaskan untuk
kesebuah DEPARTMENT lain, hal ini kita sebut dengan relasi Transferbility.
Transferbilty adalah sebuah relasi antar entitas yang nilainya dapat berpindah
(Transferable).
Contoh : Hubungan antara STUDENT dan STUDY GROUP
Seorang Siswa diperbolehkan untuk berpindah dari satu STUDY GROUP
ke STUDY GROUP yang lain. Dalam hal ini hubungan relasi antara
STUDENT dan STUDY GROUP adalah dapat Tranferable.
25
3.4.2 Non-Transferbilty
Non-Transferbilty adalah sebuah relasi antar entitas yang nilainya tidak dapat
berpindah/dipindahkan (Transferable). Ini merupakan kebalikan dari Relasi
Transferbility.
26
Contoh : Hubungan antara POEM dan AUTHOR
Satu POEM tidak bisa untuk berpindah nilai lagi menjadi AUTHOR
yang lain, karena nilai POEM sudah ditentukan nilainya pada tepat satu
AUTHOR. Dalam hal ini hubungan relasi antara POEM dan
AUTHOR adalah dapat Non-Tranferable.
TUGAS :
27
Implementasikan dan berikan contoh data modeler yang menggunakan Subtype,
Supertype, dan Transferbility!
28
Pertemuan 4
Normalisasi dalam Database
Tujuan Intruksional :
Pokok Bahasan ini mempelajari tentang normalisasi pada model database
Kompetensi Yang Diharapkan :
Mahasiswa diharapkan memahami tentang normalisasi pada model database termasuk
aturan First Normal Form, Second Normal Form, dan Third Normal Form dan dapat
mengimplementasikan bentuk normal diatas pada permasalahan yang ada.
Waktu Pertemuan : 100 Menit
4.1. Pengertian Normalisasi
Normalisasi merupakan sebuah proses mengorganisir data dalam suatu database untuk
menghindari redundansi data, anomali pada penyisipan (insertion), anomali pada
perubahan (update), dan anomali pada penghapusan (deletion).
4.2. Anomali pada DBMS
Ada tiga jenis anomali yang terjadi ketika database tidak dinormalisasi. Anomaly yang terjadi
adalah - Insertion, update dan deletion anomaly. Mari kita ambil contoh untuk memahami
hal ini.
Misalkan sebuah perusahaan perabotan menyimpan rincian pegawai dalam tabel bernama
pegawai yang memiliki empat atribut: id_pegawai untuk menyimpan id pegawai, nama
29
untuk menyimpan nama pegawai, alamat untuk menyimpan alamat pegawai dan divisi untuk
menyimpan rincian divisi di mana pegawai bekerja. Pada suatu waktu table pegawai terlihat
seperti ini:
id_pegawai nama alamat divisi
101 Agus Bandar Lampung D001
101 Agus Bandar Lampung D003
123 Budi Pringsewu D890
166 Susi Pesawaran D900
166 Susi Pesawaran D004
Tabel diatas tidaklah normal. Kita akan lihat masalah yang didapat pada tabel yang tidak
normal.
Update Anomaly: Dalam tabel di atas kita memiliki dua baris untuk pegawai bernama Agus
karena ia bekerja pada 2 divisi. Jika kita ingin memperbaharui (update) alamat dari Agus kita
harus memperbaharui 2 baris data karena jika tidak, hal tersebut tidak konsisten. Jika entah
bagaimana, alamat Agus yang benar berada pada 1 baris namu tidak pada baris yang lain,
Agus akan mempunyai 2 alamat berbeda dan bisa menyebabkan data yang tidak konsisten.
Insert Anomaly: Jika ada pegawai baru yang bergabung, namun masih dalam masa training
dan belum terdaftar pada divisi manapun, maka data tidak akan bisa dimasukkan (insert) ke
table jika atribut divisi tidak memperbolehkan nilai null.
Delete Anomaly: Jika pada suatu waktu perusahaan akan menutup divisi D890 lalu
menghapus baris yang memiliki nilai pada atribut divisi yaitu D890 maka informasi
30
mengenai pegawai Budi akan terhapus karena hanya ia terdaftar sebagai pegawai yang
bekerja pada divisi D890.
4.3. Bentuk Normal
Berikut adalah bentuk-bentuk normal yang paling umum digunakan:
1. First Normal Form (1NF)
• Setiap data dibentuk dalam flat file, data dibentuk dalam satu record demi satu record
nilai dari field berupa “atomic value”.
• Untuk memeriksa 1NF, setiap atribut harus memiliki nilai tunggal untuk setiap
instance dari sebuah entitas. (Tidak ada set atribute yang berulang atau bernilai
ganda).
31
• Satu kode, satu nama, dan satu alamat ada untuk gedung sekolah, tetapi tidak satu
kelas. Satu sekolah akan memiliki banyak kelas, oleh karena itu banyak data yang
akan mengalami redundansi.
• Karena banyak ruang kelas yang ada di gedung sekolah, ruang kelas adalah multi-
valued dan melanggar 1NF.
• Jika ada atribut multi-valued, buatlah suatu entitas tambahan dan hubungkan dengan
entitas asli dengan jenis relasi 1: M (one to many).
Pelanggaran Bentuk Normal Pertama (First Normal Form)
32
• Coba perhatikan entitas-entitas diatas.
• Apakah ada atribut yang bersifat multi-valued?
Solusi Bentuk Normal Pertama (First Normal Form)
“Ketika semua atribut dalam suatu entitas yang bernilai tunggal, entitas yang dikatakan
dalam Bentuk Normal Pertama.”
33
2. Second Normal Form (2NF)
34
Syarat untuk menerapkan normalisasi bentuk kedua ini adalah data telah dibentuk dalam
1NF, berikut adalah beberapa fungsi normalisasi 2NF.
• Menghapus beberapa subset data yang ada pada tabel dan menempatkan mereka pada
tabel terpisah.
• Menciptakan hubungan antara tabel baru dan tabel lama dengan menciptakan foreign
key.
• Tidak ada atribut dalam tabel yang secara fungsional bergantung pada candidate key
tabel tersebut.
Dari entitas diatas
1. Apakah purchase price milik dari supplier number, product number, atau keduanya?
2. Apakah supplier name milik dari supplier number, product number, atau keduanya?
Solusi Bentuk Normal Kedua (Second Normal Form)
35
Bahwasanya entitas sebelumnya merupakan pelanggaran dalam 2NF, jika menggunakan entitas
tersebut maka terjadinya data rangkap.
maka perlu dibuat entitas baru untuk mendefinisikan Supplier dan Product Supplier.
3. Third Normal For (3NF)
Normalisasi database dalam bentuk 3NF bertujuan untuk menghilangkan seluruh atribut atau field
yang tidak berhubungan dengan primary key. Dengan demikian tidak ada ketergantungan transitif
pada setiap kandidat key. Syarat dari bentuk normal ketiga atau 3NF adalah :
1. Memenuhi semua persyaratan dari bentuk normal kedua.
2. Menghapus kolom yang tidak tergantung pada primary key.
36
Perhatikan entitas diatas.
Apakah store name berhubungan dengan UID dari CD ?
Apakah store address berhubungan dengan UID dari CD ?
Tidak, dan ini merupakan pelanggaran pada 3NF.
Model 3NF menunjukkan bahwasanya, perlunya dibuat entitas baru dengan nama STORE yang
terdiri dari number, name, dan address yang terhubung dengan entitas CD.
37
Contoh Aturan 2
Entitas diatas merupakan entitas CITY yang didalamnya, Juga terdapat atribut state.
Dalam 3NF itu merupakan pelanggaran, karena pada entitas diatas termasuk dalam
Transitive Dependency -> Ketergantungan Transitif
38
Model 3NF menunjukkan bahwasanya, perlunya dibuat entitas baru dengan nama STATE yang
terdiri dari id, name, dan flower yang terhubung dengan entitas CITY.
Contoh 3
• Pada entitas diatas, diasumsikan bahwasanya setiap employee memiliki partner.
• Mengingat bahwasanya pada 3NF tidak diperbolehkan adanya Transitive Dependency ->
Ketergantungan Transitif.
• Pada entitas diatas apakah partner name dan birth date berhubungan dengan UID dari
EMPLOYEE ?
Tentu tidak, maka diperlukan entitas baru yang mendefinisikan partner yang terhubung dengan
Employee.
39
Latihan :
40
ID
Bu
ku
Judul_
Buku
TglTerb
it
ID_Pener
bit
Nama_
Penerbit
Alama
t_Pene
rbit
IDPeminja
man
NamaPe
minjama
n
Alamat_Pem
injaman
B1
2
Sistemk
u
20-jan-
12
P102 Gramed Teluk PJ65 Ateng Jakarta
A6
7
Bukuku 12-feb-
17
P454 PencetP
in
Karan
g
PJ77 Deri Palembang
H6
3
ORAC
LE
8-des-
10
P54 ChuMar
t
Metro PJ34 Iman Medan
J53 MySQL 9-feb-
13
P87 Indomei Rajaba
sa
PJ2 Saddik Surabaya
41
Pertemuan 5
Arcs
Tujuan Intruksional : Pokok Bahasan ini mengenalkan tentang Arcs
Kompetensi Yang Diharapkan : Mahasiswa diharapkan memahami tentang konsep Arcs dan cara membuat Arcs di data modeler.
Waktu Pertemuan : 100 Menit
1. Pengertian Arcs
Arc dalam pemodelan data membantu perancang memperjelas relasi XOR (Exclusive OR)
dalam pada database. Hubungan OR Eksklusif adalah hubungan antara satu entitas dan dua
(atau lebih) entitas lain dimana hanya satu dari hubungan bisa eksis dalam satu waktu.
Contoh XOR :
Seorang pemenang undian, hanya akan menerima mobil ATAU uang tunai.
Dari kalimat diatas dapat ditarik kesimpulan bahwa pemenang undian tersebut hanya dapat
menerima mobil, atau hanya dapat menerima uang tunai, HANYA SALAH SATU TIDAK
KEDUANYA.
Arc digunakan dalam kasus pemodelan data pada relasi XOR.
2. Studi Kasus
Papan iklan adalah media yang bisa menampilkan film, iklan barang, atau pengumuman
masyarakat. Papan iklan dapat menampilkan hanya 1 dari ketiga kategori iklan diatas dalam
satu waktu atau dalam arti tidak dapat memasang 3 jenis iklan dalam 1 waktu.
Papan iklan hanya dapat memasang 1 film, 1 iklan barang, atau 1 pengumuman masyarakat
pada satu waktu.
42
a. Langkah Pertama yaitu membuat ENTITAS. Dari kasus diatas dapat dilihat bahwa
memiliki 4 entitas yaitu Papan Iklan, Film, Iklan Barang, dan Pengumuman Masyarakat.
Satu Film dapat dipasang pada banyak Papan Iklan.
Satu Iklan Barang dapat dipasang pada banyak Papan Iklan.
Satu Pengumuman Masyarakat dapat dipasang pada banyak Papan Iklan.
b. Langkah Kedua yaitu :
Karena pada satu waktu hanya dapat diisi oleh 1 film, atau 1 Iklan Barang, atau 1
Pengumuman Masyarakat dan tidak dapat ketiganya sekaligus, berarti relasi pada diagram
diatas mengimplementasikan relasi XOR, maka harus dibuatkan Arc.
Cara Membuat ARC pada datamodeler yaitu :
Klik pada Entitas yang utama atau yang dapat berisi 3 kategori tersebut. (Contoh
diatas yaitu PAPAN IKLAN).
Tekan Shift atau Ctrl.
Pilih semua relasi yang akan dibuat Arc.
43
Lalu Klik New Arc.
44
Arc akan terbentuk.
Arc
relasi XOR
3. Perbedaan Arc dan Supertype/Subtype
Pada beberapa kasus tidak semua mutual eksklusif harus dibuat Arc, namun ada beberapa
kasus yang hanya harus dibuatkan Supertype/Subtype pada pemodelannya.
Contoh :
Pada sebuah ACARA, ada dua LOKASI acara yang dapat dipilih yaitu RUMAH, atau
GEDUNG. Tentunya tidak dapat dipilih keduanya sekaligus pada satu waktu.
a. Haruskah dibuatkan Arc pada relasi diatas?
b. Apakah Rumah dan Gedung memiliki Atribut yang sama?
c. Dapatkah RUMAH dan GEDUNG dijadikan Subtype pada SuperType LOKASI ?
45
Benarkah pemodelan data diatas?
Pemodelan data diatas tentu kurang tepat. Karena Entitas GEDUNG dan Entitas RUMAH
memiliki atribut yang hampir sama. Maka, akan lebih tepat jika dijadikan Subtype dari
Supertype LOKASI dan tidak memerlukan Arc.
46
SOAL LATIHAN
Pada sebuah acara PELATIHAN, acara tersebut dapat diambil pemateri dari
PERSEORANGAN, atau PERUSAHAAN TRAINING tidak dapat diambil pemateri dari
keduanya dalam 1 waktu.
Buatkan pemodelan data di data modeler!
47
Pertemuan 6
Hierarki dan Rekursif
Tujuan Intruksional : Pokok Bahasan ini mengenalkan tentang Arcs
Kompetensi Yang Diharapkan : Mahasiswa diharapkan memahami tentang konsep Arcs dan cara membuat Arcs di data modeler.
Waktu Pertemuan : 100 Menit
1. Pengertian Hierarki
Hierarki pada pemodelan data adalah cara untuk menggambarkan tingkatan pada data.
Contoh :
Presiden Mengepalai beberapa Menteri.
Menteri Mengepalai beberapa Deputi.
Deputi Mengepalai beberapa Kepala Daerah.
PRESIDEN
MENTERI MENTERI
DEPUTI DEPUTI DEPUTI DEPUTI
KEPALA DAERAH KEPALA DAERAH
48
Gambar diatas menggambarkan salah satu contoh hierarki atau struktur.
49
2.1 Studi Kasus
Pada sebuah kantor terjadi seperti dibawah ini :
PRESDIR mengepalai beberapa DIREKTUR
DIREKTUR mengepalai beberapa MANAGER
MANAGER mengepalai beberapa KADIV.
Bagaimana memodelkan hierarki pada kasus diatas?
1. Langkah pertama membuat entitas.
50
2. Pengertian Rekursif
Rekursif adalah pemodelan data yang memiliki relasi pada entitas itu sendiri.
2.1 Studi Kasus
Pada sebuah perusahaan terjadi dimana para pegawai mengepalai pegawai
lainnya. Contoh :
MANAGER mengepalai
KADIV. KADIV mengepalai
Pegawai Divisi.
Lalu, Bagaimana menggambarkan pemodelan rekursif?
51
2. Karena beberapa pegawai akan mengepalai beberapa pegawai lainnya, maka akan
terjadi relasi rekursif.
Lalu, apa perbedaan Hierarki dan Rekursif?
Hierarki akan menjelaskan pemodelan data lebih rinci, dimana dijelaskan para pegawai
perusahaan langsung dibagi - bagi atas struktur organisasi perusahaan, namun tidak
dengan Rekursif. Pemodelan dengan rekursif lebih simple namun tidak mendetail.
52
Pertemuan 7
Historical Data Model
Tujuan Instruksional :
Pokok bahasan ini menjelaskan tentang konsep Historical Data Model
Kompetensi yang Diharapkan :
Mahasiswa diharapkan dapat memahami tentang konsep Historical Data Model serta
cara membuat Historical Data Model Pada Data Modeler.
Waktu Pertemuan : 100 menit
1. Pengertian Historical Data Model
Historical Data Model adalah pemodelan database yang dapat menyimpan sejarah
daripada suatu data (History Data) yang memungkinkan terjadinya perubahan,
penambahan, ataupun pengurangan data tersebut dari masa ke masa.
Contoh:
Pada tahun 2014 terdapat barang X dengan harga Rp. 25.000,00 . Barang X tersebut
terjual 4 barang dengan total pembayaran Rp. 100.000,00 . Namun, pada tahun 2015
terjadi kenaikan harga pada barang X menjadi Rp. 30.000,00 . Barang X tersebut juga
terjual 4 barang dengan total pembayaran Rp. 120.000,00.
Dari contoh diatas, dengan terjadinya perubahan harga pada barang X, tentunya
diperlukan suatu pemodelan database yang dapat menyimpan perubahan data tersebut,
serta tidak mengganggu data sebelumnya yang telah di proses.
53
2. Studi Kasus
Seorang ARTIS akan meminjam PERHIASAN pada suatu toko perhiasan untuk
menghadiri Gala Premiere Film.
Toko perhiasan tersebut saat ini hanya dapat melihat siapa yang sedang meminjam
perhiasan saat ini. Namun, manager ingin melihat siapa saja yang telah menyewa
perhiasan tersebut?
Bagaimana cara melihat siapa saja yang meminjam jika dibuatkan pemodelan data seperti
diatas?
Tentu kita harus membuat entitas baru yaitu RIWAYAT PEMINJAMAN
54
Pertemuan 8
Mapping
Tujuan Intruksional : Pokok Bahasan ini mempelajari tentang pemetaan dari Conseptual Model menjadi tabel.
Kompetensi Yang Diharapkan : Mahasiswa diharapkan memahami tentang pemetaan entitas-entitas ataupun relasinya kedalam sebuah table Waktu Pertemuan : 100 Menit
1. Mapping One-to-One and One-to-Many Relationship
Dalam sebuah Conseptual Model terdapat tiga entitas yang saling berhubungan yaitu
entitas Fakultas, Jurusan, dan Dosen dimana satu Fakultas memiliki banyak Jurusan dan
satu Jurusan dikepalai tepat oleh satu Dosen.
Hasil pemetaan dari Conseptual Model adalah.
55
Entitas Fakultas, Jurusan, dan Dosen dipetakan menjadi tabel Fakultas, Jurusan, dan Dosen.
Relasi One-to-Many antara entitas Fakultas dengan entitas Jurusan dipetakan dengan
adanya Primary Key (PK) dari tabel Fakultas yang menjadi Foreign Key (FK) pada tabel
Jurusan. Relasi One-to-One antara entitas Dosen dengan entitas Jurusan dipetakan dengan
adanya Primary Key (PK) dari tabel Dosen yang menjadi Foreign Key (FK) pada tabel
Jurusan.
2. Mapping Many-to-Many Relationship
Terdapat sebuah Conseptual Model dengan entitas dan relasi sebagai berikut.
56
Hasil pemetaan dari Conseptual Model adalah.
57
Entitas Film, Kritikus, dan Review dipetakan menjadi tabel Film, Kritikus, dan Review.
Entitas Review merupakan Intersection Entity yang menjadi solusi untuk relasi Many-to-
Many antara entitas Film dan Kritikus. Pada Coneptual Model terdapat simbol “bar” atau
Barred Relationship yang menjadikan Foreign Key (FK) pada tabel Review (FLM_ID,
KRT_ID) juga menjadi Primary Key (PK) dari tabel Review.
3. Mapping Heirarchy Relationship
Sebuah Heirarchy Relationship dengan Cascade Barred Relationship digambarkan melalui
contoh Conseptual Model berikut
Hasil pemetaan dari Conseptual Model adalah.
58
Entitas Gedung, Lantai, dan Ruang dipetakan menjadi tabel Gedung, Lantai, dan Ruang.
Cascade Barred Relationship pada relasi hirarki menjadikan PK dari tabel Lantai
merupakan kombinasi atribut Nomor dan FK GDG_ID sehingga saat dijadikan FK untuk
tabel Ruang maka PK dari tabel Lantai (Nomor, GDG_ID) menjadi FK pada tabel ruang
dan juga menjadikannya sebagai PK dari tabel Ruang sehingga PK dari tabel Ruang terdiri
dari kombinasi atribut Nomor, LNT_No, dan GDG_ID.
4. Mapping Recrusive Relation
Terdapat sebuah Conseptual Model yang terdiri dari dua entitas yaitu entitas Divisi dan
Karyawan dimana banyak Karyawan yang bekerja pada satu Divisi dan terdapat karyawan
yang merupakan manajer dari beberapa karyawan lain.
59
Hasil pemetaan dari Conseptual Model adalah.
60
Entitas Karyawan dan Divisi dipetakan menjadi tabel Karyawan dan Divisi.
Hasil pemetaan dari Recrusive Relation pada entitas Karyawan berupa PK dari tabel
Karyawan yang menjadi FK pada tabel Karyawan sendiri yaitu pada atribut mgr_ID.
5. Mapping Arcs
Terdapat sebuah Conseptual Model yang menggambarkan bahwa beberapa Tiket untuk
sebuah pertunjukan dapat dibeli melalui Agen, Situs_Web, atau Gerai_Penjualan.
Hasil pemetaan dari Conseptual Model adalah.
61
Entitas Tiket, Gerai_Pernjualan, Agen, dan Situs_Web dipetakan menjadi tabel Tiket,
Gerai_Pernjualan, Agen, dan Situs_Web. Pemetaan dari Arcs dapat dilakukan dengan
menambahkan kode program atau menggunakan Trigger dimana dari tiga atribut yang
menjadi FK (AGN_ID, WEB_ID, GRP_ID) hanya salah satu yang dapat terisi. Jika satu
atribut sudah terisi (Misalnya AGN_ID) maka atribut lain (WEB_ID, GRP_ID) tidak
dapat terisi.
62
6. Mapping Supertype and Subtype
Terdapat sebuah Conseptual Model dengan entitas Supertype dan Subtype sebagai berikut.
Hasil pemetaan dari Conseptual Model adalah.
63
Supertype mewarisi seluruh atribut dan relasi yang dimiliki kepada Subtypenya.
64
7. Mapping Non-Transferable Relationship
Terdapat sebuah Conseptual Model dengan entitas dan relasi sebagai berikut.
Hasil pemetaan dari Conseptual Model adalah.
Nilai yang menjadi FK pada tabel Person (GOL_Kode) tidak boleh diubah.
65
Pertemuan 9
Database Programming with SQL Syntax
SQL SELECT * STATEMENT
menggunakan APEX
Tujuan Intruksional : Pokok Bahasan ini mempelajari tentang Database Programming dengan SQL
Kompetensi Yang Diharapkan : Mahasiswa diharapkan memahami tentang Database Programming termasuk cara instalasi dan penulisan syntax SQL menggunakan aplikasi Oracle Application Express (APEX).
Waktu Pertemuan : 100 Menit
1-1. ORACLE APPLICATION EXPRESS (APEX)
Oracle Application Express (APEX) adalah sebuah software yang disediakan oleh Oracle
untuk membangun dan mengakses aplikasi seolah-olah bekerja di database terpisah, serta
mempercepat proses pengembangan aplikasi. 3 Komponen APEX : SQL Workshop,
Application Builder, Object Browser.
Log in ke Oracle Application Express (APEX)
Akses halaman login APEX : https://iacademy.oracle.com/
Workspace :
US_A001APAC6416
Username :
US_A001APAC6416_SQL01_S01
Password : 123456
66
SQL WORKSHOP → SQL Commands
67
2. Statement Window: ketik
perintah SQL di sini
1. Run SQL button : Klik tombol ini
untuk mengeksekusi pernyataan SQL. 3. Result Window : Output dari perintah
SQL (atau pesan kesalahan) akan
ditampilkan
Basic SELECT Statement
1. SELECT * : mengambil semua baris pada tabel.
Syntax dasar Contoh
SELECT *
FROM <table name>;
SELECT *
FROM employees;
2. SELECT Statement dengan sebuah Condition
Ubah pernyataan SELECT: mengembalikan subset dari data.
Syntax dasar Contoh
SELECT <column name 1, column name 2,
etc.>
FROM <table name>
WHERE <condition>;
SELECT first_name, last_name, job_id
FROM employees
WHERE job_id = 'SA_REP';
Hasil:
68
69
3. Correcting errors
Saat memasukkan perintah SQL, gunakan ejaan yang benar, jika tidak maka akan
mendapatkan pesan error.
Contoh:
1. Salah ejaan SELECT
SEECT *
FROM employees;
2. Salah memasukkan nama tabel employees.
SELECT *
FROM employee;
3. Salah memasukkan kolom first_name
SELECT name
FROM employees;
70
1-2. RELATIONAL DATABASE
Relational Database menghubungkan tabel dengan menggunakan field yang sama, dengan
minimal 2 tabel.
Contoh
Tabel "countries" yang ditampilkan adalah salah satu dari beberapa tabel di database
employee.
SELECT *
FROM countries;
Hasil:
Accessing Data in an RDBMS
Relational Database-Management System (RDBMS) mengatur data ke dalam baris dan
kolom yang terkait dengan memasukkan pernyataan SQL di APEX. Permintaan tersebut
kemudian dikirim ke Oracle Server (sebuah database yang berjalan di komputer), lalu
diproses dan data akan ditampilkan.
71
Categories of SQL Statements
1. Data Manipulation Language (DML): dimulai dengan INSERT, UPDATE, DELETE,
atau MERGE yang digunkaan untuk memodifikasi data tabel dan memasukkan baris
baru, mengubah baris yang ada, atau menghapus baris yang ada.
2. Data Definition Language (DDL) : membuat, mengubah, dan menghapus struktur data
dari database dimulai dengan CREATE, ALTER, DROP, RENAME, dan
TRUNCATE begin DDL statements.
3. Transaction Control Language (TCL) : mengelola perubahan yang dibuat oleh
pernyataan DML menggunakan COMMIT, ROLLBACK, dan SAVEPOINT, dapat
dikelompokkan menjadi logical transactions.
4. Data Control Language (DCL): memberi atau menghapus hak akses ke database dan
struktur di dalamnya menggunakan GRANT dan REVOKE.
72
SELECT last_name
FROM
employees
1-3. ANATOMY OF A SQL STATEMENT
SELECT Keyword : mencari data tertentu.
SELECT Statement : mengambil informasi dari database.
Syntax dasar:
SELECT <column_name(s)>
FROM <table_name>;
SELECT : menentukan kolom yang akan ditampilkan.
FROM : menentukan tabel yang berisi kolom yang tercantum dalam klausa SELECT.
Konvensi
keyword
sebuah klausa (clause): bagian dari pernyataan SQL.
sebuah pernyataan: kombinasi dari dua / lebih klausa.
Projection and Selection
Projection: memilih kolom dalam sebuah tabel
Selection: memilih baris dalam sebuah tabel.
A. Selecting All Colums: menampilkan semua kolom: gunakan asterisk symbol (*) dari
sebuah nama kolom dalam klausa SELECT.
Contoh:
73
1. Menampilkan tabel countries
SELECT *
FROM countries;
2. Menampilkan semua kolom dalam tabel menggunakan daftar masing-masing.
SELECT country_id, country_name, region_id
FROM countries;
Hasil:
B. Projecting Specific Columns: menampilkan PROJECT dengan kolom tertentu dari
tabel. Masukkan nama kolom dan pisahkan setiap nama dengan koma (,).
SELECT location_id, city, state_province
FROM locations;
Hasil:
Tuliskan syntax untuk menghasilkan output sebagai berikut:
Petunjuk: 1. Semua soal menggunakan tabel employees.
2 No. soal sesuai nomor materi.
1-1
74
1-3
2-1
75
2-2
2-3
3-1
3-1 SELECT * FROM employees WHERE salary <= 10000 AND job_id LIKE 'ST%' OR last_name LIKE 'a%';
Output dari syntax diatas adalah….
76
3-2
77
Pertemuan 10
Database Programming with SQL
Syntax SQL ARITHMATIC,COLUMNS, ROWS
Menggunakan APEX
Tujuan Intruksional : Pokok Bahasan ini mempelajari tentang Database Programming dengan SQL
Kompetensi Yang Diharapkan : Mahasiswa diharapkan memahami tentang Database Programming termasuk cara instalasi dan penulisan syntax SQL menggunakan aplikasi Oracle Application Express (APEX).
Waktu Pertemuan : 100 Menit
Menggunakan Operator Aritmatika
add (+), subtract (-) , multiply (*) and divide (/)
Hasil perhitungan hanya ditampilkan pada output, tidak membuat sebuah kolom baru
pada tabel/ mengganti nilai data.
Contoh: Kolom nilai awal SALARY dan output SALARY + 300 untuk semua
employees.
78
SELECT last_name, salary, salary + 300
FROM employees;
Hasil:
Precedence in Arithmetic Operators
Precedence adalah urutan di mana Oracle mengevaluasi operator yang berbeda dalam
ekspresi yang sama.
Saat mengevaluasi sebuah ekspresi yang mengandung banyak operator, Oracle
mengevaluasi operator dengan prioritas yang lebih tinggi sebelum mengevaluasi yang
lebih rendah.
Oracle mengevaluasi operator dengan prioritas yang sama dari kiri ke kanan dalam
sebuah ekspresi.
Gunakan tanda kurung ( ) untuk mendahulukan ekspresi yang dievaluasi.
Operator Precedence (Tidak menggunakan tanda kurung)
SELECT last_name, salary, 12*salary +100 FROM employees;
Hasil:
79
80
Using Parentheses (Menggunakan tanda kurung)
SELECT last_name, salary, 12*(salary +100) FROM employees;
Hasil:
NULL Values
NULL adalah nilai yang unavailable, unassigned, unknown, atau inapplicable.
NULL tidak sama dengan nol atau spasi.
Relational databases menggunakan placeholder yang disebut NULL / null untuk
mewakili nilai-nilai yang tidak diketahui.
Contoh:
SELECT last_name, job_id, salary, commission_pct, salary*commission_pct
FROM employees;
Hasil:
Salaries dan Commissions
NULL
81
Aliases (AS)
Alias adalah penamaan kembali sebuah judul kolom pada output.
Tanpa alias, ketika hasil pernyataan SQL ditampilkan, nama kolom yang ditampilkan
akan sama dengan nama kolom pada tabel atau nama yang menunjukkan operasi
aritmatika seperti 12 * (SALARY + 100).
Kolom alias: Mengganti nama judul, berguna dalam perhitungan, diikuti nama kolomnya,
bisa terdapat keyword AS opsional antara nama kolom dan alias, menggunakan tanda
kutip (“ ”) jika berisi spasi / karakter khusus / case-sensitive.
Menggunakan Kolom Aliases
Syntax dasar:
SELECT * |column|expr [ AS alias], ..... FROM table;
Contoh:
SELECT last_name AS name,
commission_pct AS comm FROM employees;
SELECT last_name "Name", salary*12 "Annual
Salary"
FROM employees;
82
2-1 COLUMNS, CHARACTERS, AND ROWS
DESCRIBE
DESCRIBE (DESC) digunakan untuk menampilkan struktur tabel (nama tabel, tipe data,
Primary Key dan Foreign Key, Nullable.
Sintaks dasar Contoh
DESC <nama_tabel>; DESC customers;
Hasil :
Operator Concatenation (Penggabungan)
Concatenation (Penggabungan) berfungsi untuk menggabungkan dua atau lebih karakter
menjadi satu kesatuan kalimat. Simbol concatenation adalah 2 garis vertikal || atau disebut
“pipes”.
Sintaks dasar :
string1 || string2 || string_n
Contoh :
Concatenation
SELECT department_id || department_name
FROM departments;
Hasil :
83
Concatenation dengan Literal Values
SELECT last_name || ' has a monthly salary of ' ||
salary || ' dollars.'AS Pay
FROM employees;
Hasil :
Concatenation dengan Alias
SELECT first_name || ' ' || last_name AS "Employee Name" FROM employees;
Hasil :
84
2-2 LIMIT ROWS SELECTED
DISTINCT
DISTINCT digunakan adalah salah satu operator di database Oracle bahkan hampir di
semua database yang digunakan untuk mencegah adanya duplikasi data atau record.
Misalkan ada 10 orang yang bernama 'AHMAD', maka dengan menggunakan operator
Distinct, bisa mengeliminasi data 'AHMAD' menjadi satu nama saja. DISTINCT
digunakan setelah klausa SELECT.
Sintaks dasar Contoh
SELECT DISTINCT <nama_kolom>
FROM <nama_tabel>;
SELECT DISTINCT department_id
FROM employees;
Hasil :
WHERE
Klausa WHERE pada perintah SELECT digunakan untuk menyeleksi data atau record sesuai
dengan kondisi yang diinginkan. Klausa WHERE bersifat opsional.
a. WHERE dengan Operator Perbandingan
Contoh :
SELECT employee_id, last_name, department_id
FROM employees
WHERE department_id = 90;
85
Hasil :
b. WHERE dengan Karakter
Contoh :
SELECT first_name, last_name
FROM employees
WHERE last_name = 'Taylor';
Hasil :
86
2-3 COMPARISON OPERATORS (OPERATOR PERBANDINGAN)
BETWEEN...AND
Operator ini digunakan untuk memilih dan menampilkan baris berdasarkan range nilai.
Range nilai yang ditampilkan termasuk nilai batas terendah dan nilai batas tertinggi.
Contoh :
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 9000 AND 11000;
Hasil :
IN
IN digunakan untuk menguji apakah sebuah nilai berada dalam seperangkat nilai tertentu.
Contoh :
SELECT city, state_province, country_id
FROM locations
WHERE country_id IN('UK', 'CA');
Hasil :
LIKE
Perintah LIKE merupakan kondisi untuk mendapatkan data dengan memilih data yang sesuai
dengan kondisi. Dalam melakukan pencarian dengan kondisi like, maka perlu menyebutkan
wildcard berupa garis bawah ( _ ) atau persen (%).
Berikut penjelasannya :
87
Tanda garis bawah ( _ ) berarti cocok dengan sebuah karakter apa saja dengan
panjang karakter harus sesuai dengan jumlah karakter garis bawah. Contohnya like
a_i berarti cocok dengan ani, adi atau ali namun tidak cocok dengan abri atau andi
karena garis bawahnya hanya satu karakter diantara a dan i.
Tanda persen (%) berarti cocok dengan karakter apa saja tanpa bergantung
panjangnya.
Contoh :
Mencari last_name pada tabel employees yang memiliki huruf kedua terakhir ‘o’
last_name.
SELECT last_name
FROM employees
WHERE last_name LIKE '_o%';
Hasil :
Opsi ESCAPE dapat digunakan untuk menunjukkan bahwa _ atau% adalah bagian
dari namanya, bukan nilai wildcard.
Contoh :
Mencari JOB_ID dari tabel employees yang berisi pola _R.
SELECT last_name, job_id
FROM EMPLOYEES
WHERE job_id LIKE '%\_R%' ESCAPE '\';
Hasil :
88
IS NULL, IS NOT NULL
Tes kondisi IS NULL digunakan untuk data yang tidak tersedia, belum ditetapkan, atau tidak diketahui.
IS NOT NULL digunakan untuk data yang tersedia di database.
Contoh :
Mencari semua last_name dari employees yang tidak memiliki manajer.
SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;
Hasil :
IS NOT NULL menampilkan baris yang memiliki nilai di kolom commission_pct.
SELECT last_name, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
Hasil :
89
Pertemuan 11
Database Programming with SQL
Logical Comparisons and Predence Rules
menggunakan APEX
Tujuan Intruksional : Pokok Bahasan ini mempelajari tentang Database Programming dengan SQL
Kompetensi Yang Diharapkan : Mahasiswa diharapkan memahami tentang Database Programming termasuk cara instalasi dan penulisan syntax SQL menggunakan aplikasi Oracle Application Express (APEX).
Waktu Pertemuan : 100 Menit
3-1 LOGICAL COMPARISONS AND PRECEDENCE RULES
Logical Operators: menggabungkan dua atau lebih kondisi untuk menghasilkan satu hasil.
Returned ONLY IF keseluruhan hasil dari kondisi ini benar.
AND : Returns TRUE jika kedua kondisi benar.
OR : Returns TRUE jika salah satu kondisi benar.
NOT : Returns TRUE jika kondisi salah.
Operator AND
Hasil yang dikembalikan akan menjadi baris yang memenuhi KEDUA kondisi yang
ditentukan dalam klausa WHERE.
SELECT last_name, department_id, salary
FROM employees
WHERE department_id > 50 AND salary > 12000;
SELECT last_name, hire_date, job_id
FROM employees
WHERE hire_date > '01/jan/1998' AND job_id
LIKE 'SA%';
90
Operator OR
Jika klausa WHERE menggunakan kondisi OR, hasil yang dikembalikan dari kueri
akan menjadi baris yang memenuhi salah satu dari kondisi OR.
Semua baris kembali memiliki location_id = 2500 OR mereka memiliki seorang
manager_id = 124.
SELECT department_name, manager_id, location_id
FROM departments
WHERE location_id = 2500 OR manager_id=124;
3-1 LOGICAL COMPARISONS AND PRECEDENCE RULES
Logical Operators: menggabungkan dua atau lebih kondisi untuk menghasilkan satu hasil.
Returned ONLY IF keseluruhan hasil dari kondisi ini benar.
AND : Returns TRUE jika kedua kondisi benar.
OR : Returns TRUE jika salah satu kondisi benar.
NOT : Returns TRUE jika kondisi salah.
Operator AND
Hasil yang dikembalikan akan menjadi baris yang memenuhi KEDUA kondisi yang
ditentukan dalam klausa WHERE.
SELECT last_name, department_id, salary
FROM employees
WHERE department_id > 50 AND salary > 12000;
SELECT last_name, hire_date, job_id
FROM employees
WHERE hire_date > '01/jan/1998' AND job_id
LIKE 'SA%';
91
Operator OR
Jika klausa WHERE menggunakan kondisi OR, hasil yang dikembalikan dari kueri
akan menjadi baris yang memenuhi salah satu dari kondisi OR.
Semua baris kembali memiliki location_id = 2500 OR mereka memiliki
seorang manager_id = 124.
SELECT department_name, manager_id, location_id
FROM departments
WHERE location_id = 2500 OR manager_id=124;
Hasil:
92
Operator NOT Mengembalikan baris yang TIDAK memenuhi syarat dalam klausa WHERE.
SELECT department_name, location_id
FROM departments
WHERE location_id NOT IN (1700,1800);
Hasil:
Aturan yang diutamakan
AND-OR
SELECT last_name||' '||salary*1.05
AS "Employee Raise"
FROM employees
WHERE department_id IN(50,80) AND
first_name LIKE 'C%'
OR last_name LIKE '%s%';
1. Operator AND dievaluasi sebelum operator OR.
2. Jika salah satu dari kondisi dalam pernyataan AND tidak terpenuhi, maka operator
OR digunakan untuk memilih baris.
Hasil:
93
Konsep penting:
ORDER OPERATORS
1 Aritmatika + - * /
2 Concatenation ||
3 Comparison <, <=, >, >=, <>
4 IS (NOT) NULL, LIKE, (NOT) IN
5 (NOT) BETWEEN
6 NOT
7 AND
8 OR
AND – OR
Contoh:
SELECT last_name||' '||salary*1.05
AS "Employee Raise",department_id, first_name
FROM employees
WHERE department_id IN(50,80)
AND first_name LIKE 'C%'
OR last_name LIKE '%s%';
1. Kondisi AND dievaluasi, sehingga semua karyawan yang bekerja di dept 80
atau 50, AND yang memiliki nama depan dimulai dengan huruf "C"
dikembalikan.
2. Klausa OR kemudian dievaluasi dan mengembalikan employees dengan
nama terakhir huruf "s".
Hasil:
OR-AND
Contoh:
94
SELECT last_name||' '||salary*1.05
AS "Employee Raise", department_id, first_name FROM employees
WHERE department_id IN(50,80) OR
first_name LIKE 'C%'
AND last_name LIKE '%s%';
1. first_name dimulai dengan "C" AND last_name berisi "s". Kedua kondisi ini harus
dipenuhi untuk dikembalikan.
2. Setiap employees di department 50 dan 80 akan dikembalikan.
Hasil:
Menambahkan tanda kurung akan mengubah cara klausa WHERE dievaluasi, dan baris
kembali.
SELECT last_name||' '||salary*1.05
AS "Employee Raise", department_id, first_name
FROM employees
WHERE (department_id IN(50,80) OR
first_name LIKE 'C%')
AND last_name LIKE '%s%';
1. Nilai dalam tanda kurung dipilih.
2. Semua nilai dalam tanda kurung yang juga mengandung huruf "s" pada last_name
akan dikembalikan.
Hasil:
95
3-2 SORTING ROWS
ORDER BY
ORDER BY digunakan untuk mengurutkan data.
Contoh :
Ascending
SELECT last_name, hire_date FROM employees ORDER BY hire_date;
Hasil :
Descending
SELECT last_name, hire_date FROM employees ORDER BY hire_date DESC;
Hasil :
Menggunakan kolom Alias
SELECT last_name, hire_date AS "Date Started" FROM
employees
ORDER BY "Date Started";
Hasil :
96
Menggunakan kolom lain
SELECT employee_id, first_name
FROM employees
WHERE employee_id < 105
ORDER BY last_name;
Hasil :
Menggunakan banyak kolom
SELECT department_id, last_name
FROM employees
WHERE department_id <= 50
ORDER BY department_id, last_name;
Hasil :
97
Operator NOT Mengembalikan baris yang TIDAK memenuhi syarat dalam klausa WHERE.
SELECT department_name, location_id
FROM departments
WHERE location_id NOT IN (1700,1800);
Hasil:
Aturan yang diutamakan
AND-OR
SELECT last_name||' '||salary*1.05
AS "Employee Raise"
FROM employees
WHERE department_id IN(50,80) AND
first_name LIKE 'C%'
OR last_name LIKE '%s%';
1. Operator AND dievaluasi sebelum operator OR.
2. Jika salah satu dari kondisi dalam pernyataan AND tidak terpenuhi, maka operator
OR digunakan untuk memilih baris.
Hasil:
98
Konsep penting:
ORDER OPERATORS
1 Aritmatika + - * /
2 Concatenation ||
3 Comparison <, <=, >, >=, <>
4 IS (NOT) NULL, LIKE, (NOT) IN
5 (NOT) BETWEEN
6 NOT
7 AND
8 OR
AND – OR
Contoh:
SELECT last_name||' '||salary*1.05
AS "Employee Raise",department_id, first_name
FROM employees
WHERE department_id IN(50,80)
AND first_name LIKE 'C%'
OR last_name LIKE '%s%';
1. Kondisi AND dievaluasi, sehingga semua karyawan yang bekerja di dept 80
atau 50, AND yang memiliki nama depan dimulai dengan huruf "C"
dikembalikan.
2. Klausa OR kemudian dievaluasi dan mengembalikan employees dengan
nama terakhir huruf "s".
Hasil:
OR-AND
Contoh:
99
SELECT last_name||' '||salary*1.05
AS "Employee Raise", department_id, first_name FROM employees
WHERE department_id IN(50,80) OR
first_name LIKE 'C%'
AND last_name LIKE '%s%';
1. first_name dimulai dengan "C" AND last_name berisi "s". Kedua kondisi ini harus
dipenuhi untuk dikembalikan.
2. Setiap employees di department 50 dan 80 akan dikembalikan.
Hasil:
Menambahkan tanda kurung akan mengubah cara klausa WHERE dievaluasi, dan baris
kembali.
SELECT last_name||' '||salary*1.05
AS "Employee Raise", department_id, first_name
FROM employees
WHERE (department_id IN(50,80) OR
first_name LIKE 'C%')
AND last_name LIKE '%s%';
1. Nilai dalam tanda kurung dipilih.
2. Semua nilai dalam tanda kurung yang juga mengandung huruf "s" pada last_name
akan dikembalikan.
Hasil:
100
3-2 SORTING ROWS
ORDER BY
ORDER BY digunakan untuk mengurutkan data.
Contoh :
Ascending
SELECT last_name, hire_date FROM employees ORDER BY hire_date;
Hasil :
Descending
SELECT last_name, hire_date FROM employees ORDER BY hire_date DESC;
Hasil :
Menggunakan kolom Alias
SELECT last_name, hire_date AS "Date Started" FROM
employees
ORDER BY "Date Started";
Hasil :
101
Menggunakan kolom lain
SELECT employee_id, first_name
FROM employees
WHERE employee_id < 105
ORDER BY last_name;
Hasil :
Menggunakan banyak kolom
SELECT department_id, last_name
FROM employees
WHERE department_id <= 50
ORDER BY department_id, last_name;
Hasil :
102
Pertemuan 12
Function
Tujuan Intruksional:
Pokok bahasan ini mempelajari tentang fungsi-fungsi dalam APEX
Kompetensi Yang Diharapkan:
Mahasiswa diharapkan memahami tentang fungsi-fungsi dalam APEX
Waktu Pertemuan : 100 Menit
4.1 Case and Character Manipulation
Dual Table
Dual Table memiliki satu baris yang disebut "X" dan satu kolom bernama "DUMMY."
Dual Table digunakan untuk membuat pernyataan SELECT dan menjalankan fungsi
yang tidak terkait langsung dengan tabel database tertentu.
Conoh :
LOWER (column | expression) mengubah karakter alfa menjadi huruf kecil.
Query :
103
Output :
UPPER (column | expression) mengubah karakter alfa menjadi huruf besar.
Query :
Output :
INITCAP(column|expression) mengubah karakter alfa menjadi huruf besar pada setiap awal kata.
Query :
Output :
Character Manipulation Functions
Character manipulation function digunakan untuk mengekstrak, mengubah, memformat, atau
mengubah beberapa string karakter.
104
CONCAT : untuk menggabungkan dua nilai.
Mengambil dua argumen karakter string, dan menggabungkan string kedua dengan yang
perta ma. Dapat juga ditulis menggunakan concatenation operator ( || ).
Query :
Output :
Output :
SUBSTR : untuk mengekstrak string dengan panjang yang ditentukan.
Argumennya adalah (character String, starting position, length). Length argument bersifat
opsional, dan jika dihilangkan, akan mengembalikan semua karakter ke akhir string.
Query :
105
Output :
Output :
Query :
Output :
106
LENGTH : untuk menampilkan panjang string dengan nilai angka.
Fungsi ini mengambil karakter string sebagai argumen dan mengembalikan angka
yang merupakan panjang dari dari karakter string tersebut.
Query :
Output :
Output :
INSTR : untuk menemukan posisi numerik dari karakter tertentu.
INSTR mencari substring pertama dalam string karakter dan mengembalikan
posisinya sebagai angka. Jika substring tidak ditemukan, angka nol akan dikembalikan.
Query :
107
Output :
Output :
LPAD : Pads pada sisi kiri karakter string, menghasilkan right-justified.
LPAD membutuhkan 3 argumen: string karakter, jumlah karakter dalam padded string, dan
karakter untuk pad.
Query :
Output :
108
Output :
RPAD : Pads pada sisi kanan karakter string, menghasilkan left-justified.
Query :
Output :
109
Output :
TRIM : untuk menghapus semua karakter spesifik dari awal, akhir, atau awal dan akhir
string.
Query :
Output :
Query :
Output :
110
Output :
REPLACE : untuk menggantikan urutan karakter dalam string dengan kumpulan karakter
lainnya.
- string1 adalah string yang akan memiliki karakter pengganti di dalamnya
- string_to_replace adalah string yang akan dicari dan diambil dari string1
- [replacement_string] adalah string baru yang akan dimasukkan ke dalam string1
Query :
Output :
Query :
Output :
111
Output :
Column Aliases
Kolom alias digunakan untuk memberi nama sebuah fungsi. Bila kolom alias digunakan,
kolom alias hanya akan muncul di output dan bukan pada sintaks fungsi sebenarnya.
Contoh :
112
Ketika dicoba untuk kedua kalinya tanpa menggunakan kolom alias, nama kolom akan
kembali seperti semula.
Subtitution Variables
Subtitution variables digunakan untuk menjalankan query yang sama dengan banyak nilai
berbeda untuk mendapatkan set hasil yang berbeda.
Oracle Application Express mendukung variabel substitusi. Untuk menggunakannya, nilai
hardcoded dalam pernyataan harus diganti dengan “: named_variable”.
Misalnya ini adalah query aslinya :
Lalu ingin menjalankan dengan nilai yang berbeda (20, 30, 40, … dsb).
Maka dapat ditulis dengan :
113
Catatan : gunakan simbol titik dua (“ : ”) di depan ‘enter_dept_id. Ini adalah simbol yang
membuat Oracle Application Express mengenali teks sebagai variabel.
4.2 Number Function
Terdapat 3 jenis fungsi angka dalam SQL query, yaitu:
1. Round
2. Trunc
3. Mod
1. ROUND
ROUND dapat digunakan untuk membulatkan angka ke jumlah tertentu dari nilai decimal.
Query:
2. TRUNC
TRUNC digunakan untuk mengakhiri kolom, ekspresi, atau nilai ke angka tertentu dari nilai
decimal.
Query:
TRUNC tidak membulatkan angka, tetapi mengakhiri angkat pada titik tertentu.
114
Query:
SELECT MIN_salary, Max_salary, MIN_salary / Max_salary AS
"Koma", ROUND(MIN_salary/Max_salary,2),
TRUNC(MIN_salary/Max_salary,2) FROM JOBS
WHERE
max_salary=8500
Output:
3. MOD
MOD digunakan untuk menemukan sisa setelah satu nilai dibagi dengan nilai lain.
Contoh: MOD dari 5 dibagi dengan 2 adalah 1.
Query:
SELECT region_id, MOD(region_id,2) FROM countries;
4.2 Date Function
Output:
Kolom ‘MOD(REGION_ID,2)’ menunjukkan
jumlah bandara pada setiap region berjumlah ganjil
(1) atau genap (0).
1. SYSDATE
SYSDATE adalah fungsi tanggal yang digunakan untuk menghasilkan tanggal dan waktu
saat ini dari database server.
115
Query:
Output:
Date Data Type
Tipe data ‘Date’ pada Oracle selalu menyimpan informasi sebagai 4 digit angka: 2 digit
pertama menunjukkan abad dan 2 digit terakhir untuk menunjukkan tahun.
Contoh:
Database Oracle menyimpan tahun sebagai 1996 atau 2004, bukan hanya 96 atau 04.
Query:
Output:
116
Query:
Output:
Query:
Output:
117
MONTHS_BETWEEN:
Mengambil 2 buah data DATE dan menghasilkan nilai berupa jumlah bulan diantara
kedua tanggal tersebut.
Query:
Output:
ADD_MONTHS:
Mengambil 2 data berupa tanggal (DATE) dan angka, kemudian menghasilkan tanggal
118
(DATE) dengan menambahkan angka tersebut kedalam komponen bulan dari data DATE.
Query:
Output:
NEXT_DAY
Mengambil 2 argumen berupa tanggal (DATE) dan hari, kemudian menghasilkan tanggal
dari hari tersebut pada minggu setelahnya.
Query:
Output:
• LAST_DAY:
Mengambil tanggal (DATE) untuk mencari hari terakhir pada bulan dari tanggal (DATE)
yang diilih.
Query:
Output:
119
ROUND
Menghasilkan nilai DATE yang telah dibulatkan kedalam unit yang telah ditentukan pada
argument kedua.
Query:
Output:
TRUNC
Menghasilkan nilai DATE yang telah dipotong kedalam unit yang telah ditentukan pada
statement kedua.
Query:
Output:
120
Berikut adalah contoh query SQL query dengan menggunakan beberapa fungsi DATE:
Output:
121
TUGAS OBSERVASI-DEMONTRASI/PRAKTIK 1. Tampilkan gabungan kata “Oracle” dan “Academy” dari tabel dual kedalam kolom “Subject” menggunakan huruf kapital, serta tampilkan pula angka yang menyatakan panjang string dari subject tersebut kedalam kolom “Length of Subject” dalam satu kali output.
2. Tampilkan semua last_name dari table employees yang memiliki karakter ‘a’ pada posisi
kedua dalam urutan karakter string last_name.
3. Tulisakan query yang dapat menghasilkan output berikut :
4. Tuliskan query untuk menampilkan karakter pertama dari first_name yang digabungkan
dengan last_name, dan salary untuk karyawan yang bekerja di departemen 20 dalam kolom
alias “Employees Data”. Gunakan tabel EMPLOYEES. Gunakan konsep Subtitution
Variable untuk menjalankan kasus yang berbeda (department_id = 30, 40, 50, dst).
122
5. Tuliskan query untuk menampilkan:
employee_id, salary, commission_pct, nilai round dari salary dibagi comission_pct
dengan nilai decimal 4) jadikan kolom ‘hasil bagi’, trunc dari salary dibagi
commission_pct dengan nilai decimal 2, dan MOD 2 dari employee_id dari tabel
employees, dimana salary lebih dari 5000.
4. Tampilkan last_name dan tanggal employee dipekerjakan (hire date) dikurangi sebulan
sebagai kolom ‘H-30’ dari tabel employees.
123
5. Tampilkan tanggal employee dipekerjakaan ditambah 24 bulan dan tanggal di hari selasa
seminggu setelah employee mulai bekerja dari tabel employees.
124
Pertemuan 13
JOIN dan GROUP
Tujuan Intruksional:
Pokok bahasan ini mempelajari tentang fungsi JOIN dan GROUP dalam APEX
Kompetensi Yang Diharapkan:
Mahasiswa diharapkan memahami tentang fungsi JOIN dan GROUP dalam APEX
Waktu Pertemuan : 100 Menit
9.1 JOIN
JOIN adalah salah satu fungsi untuk menghubungkan beberapa atribut dari tabel (Entitas) yang
berbeda yang memiliki relasi.
Contoh : Ingin menghubungkan entitas Departments dan Location.
Tabel Departments :
125
Tabel Locations :
A. NATURAL JOIN Tidak memerlukan sesuatu yang secara spesifik untuk menggabungkan 2 tabel, jika tabel itu
memiliki relasi maka akan dapat dihubungkan.
SQL :
SELECT department_name, city
FROM departments NATURAL JOIN locations ;
Hasil :
126
B. USING USING digunakan untuk menentukan atribut apa yang akan digunakan untuk menggabungkan
beberapa tabel.
Contoh : SELECT last_name, job_title
FROM employees JOIN jobs USING (job_id)
Hasil :
C. JOIN ON dan PENGGUNAAN ALIAS
Contoh PENGGUNAAN ALIAS : SELECT e.last_name, e.email FROM employees e ;
Penggunaan huruf "e" diatas adalah contoh meng-alias-kan entitas.
SQL :
SELECT e.last_name, d.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
Contoh JOIN ON : SELECT last_name, job_title
FROM employees e JOIN jobs j
ON (e.job_id = j.job_id);
Hasil :
C. LEFT OUTER JOIN bagian paling kiri dari tabel yang akan menjadi referensi.
Contoh :
Hasil :
D. RIGHT OUTER JOIN Bagian kanan tabel yang akan menjadi referensi atau acuan.
Contoh :
SQL :
SELECT e.last_name, d.department_id, d.department_name FROM employees e
RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);
SQL :
SELECT e.last_name, d.department_id, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON (e.department_id = d.department_id);
E. FULL OUTER JOIN : FULL OUTER JOIN adalah gabungan dari LEFT OUTER JOIN dan RIGHT OUTER JOIN.
Contoh :
F. INNER JOIN INNER JOIN adalah penggabungan data yang dimunculkan hanya data yang terdapat pada tabel
- tabel yang dihubungkan.
Contoh :
SQL :
SELECT e.last_name, d.department_id, d.department_name
FROM employees e
INNER JOIN departments d ON (e.department_id = d.department_id);
Hasil :
9.2 GROUP
Group By Clause
Group By merupakan kalusa yang digunakan untuk menampilkan sekumpulan data pada
tabel berdasarkan kelompok tertentu.
Query:
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY department_id;
Output:
Query pada contoh diatas menampilkan data ‘department_id’, dan rata-rata ‘salary’ dari
tabel ‘employees’, dimana baris data dikelompokkan berdasarkan ‘department_id’
kemudian dihitung rata-rata dari ‘salary’ dan diurutkan berdasarkan ‘department_id’.
Sehingga pada tiap baris misalnya baris pertama menampilkan karyawan yang bekerja
pada department 10 memiliki rata-rata gaji sebesar 4400.
Group Withn Group
Group Withn Group merupakan istilah yang digunakan untuk pengelompokan bersarang.
Query:
SELECT department_id, job_id, count(*)
FROM employees
WHERE department_id > 40
GROUP BY department_id, job_id
ORDER BY department_id;
Output:
Query pada contoh diatas menampilkan data karyawan yang dikelompokkan berdasarkan
‘department_id’, lalu di dalam masing-masing departemen, dilakukan pengelompokan
lagi berdasarkan ‘job_id’ kemudian menampilkan jumlah karyawan yang bekerja pada
setiap pekerjaan di setiap departemen.
Having Clause
Klausa Having digunakan untuk menyeleksi data berdasarkan kriteria tertentu, kriteria
tersebut menentukan kondisi bagi Group By. Kelompok data yang memenuhi klausa
Having saja yang akan ditampilkan.
Query:
SELECT department_id, AVG(salary), Count(department_id)
FROM employees
HAVING AVG(salary)>7000
GROUP BY department_id
ORDER BY department_id;
Output:
Query pada contoh diatas menampilkan hasil pengelompokan data berdasarkan
‘department_id’ kemudian menampilkan hasil pengelompokan data yang memiliki rata-
rata ‘salary’ lebih dari 7000.
ROLLUP
ROLLUP digunakan untuk menghitung subtotal dan total dari setiap kelompok data.
Syarat penggunaannya adalah
– adanya tipe data numerik yang akan dihitung.
– kumpulan data yang sejenis dalam kumpulan data.
Query:
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY ROLLUP (department_id, job_id);
Output:
CUBE
CUBE memiliki fungsi yang hampir sama dengan ROLLUP yaitu menghitung total dan
sub total, namun CUBE digunakan untuk membuat cross-tabulasi(sub-total lebih dari satu
dimensi).
Query:
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY CUBE (department_id, job_id);
Output:
Grouping Set
Grouping Sets adalah perluasan dari klausa Group By. Grouping Sets digunakan untuk
mendefinisikan pengelompokkan lebih dari satu dalam query yang sama.
Query:
SELECT department_id, job_id, manager_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY GROUPING SETS
((job_id, manager_id), (department_id, job_id), (department_id, manager_id));
Output:
GRUPING Function
Funsi GRUPING digunakan untuk mengetahui baris mana yang menampilkan data dari
hasil dari perhitungan, dan baris mana yang menampilkan data sebenarnya dari database,
penggunaan fungsi GRUPING akan mengembalikan nilai 0 atau 1, di mana:
- Nilai 0 yang dihasilkan menandakan :
Atribute telah digunakan untuk menghitung nilai agregat
Nilai NULL yang ada pada kolom adalah stored NULL.
- Nilai 1 yang dihasilkan menandakan :
Atribute tidak pernah digunakan untuk menghitung nilai agregat
Nilai NULL yang ada pada kolom adalah nilai NULL yang dihasilkan dari
penggunaan operator ROOLUP atau CUBE sebagai hasil dari pengelompokan
data.
Query:
SELECT department_id, job_id, SUM(salary),
GROUPING(department_id) AS "Dept sub total",
GROUPING(job_id) AS "Job sub total"
FROM employees
WHERE department_id < 50
GROUP BY CUBE (department_id, job_id);
Output:
1. Nilai 23400 menunjukkan total gaji dari seluruh karyawan yang memenuhi syarat
klusa WHERE. Di sini sudah tidak digunakan apa jenis pekerjaannya atau kode
departmentnya. Karena kedua kolom, yaitu ‘department_id’ dan ‘job_id’ tidak
digunakan dan nilai NULL yang ada pada kolom ‘department_id’ dan ‘job_id’
merupakan hasil dari penggunaan operator CUBE, maka nilai yang dihasilkan
dari fungsi GROUPING adalah 1 di kolom ‘Dept sub total’ dan ‘Job sub total’.
2. Nilai 6000 pada kolom ‘salary’ menunjukkan total gaji seluruh karyawan yang
bekerja sebagai ‘MK_REP’ (tidak menggunakan department_id). Kolom yang
digunakan adalah kolom ‘jobt_id’. Karena itu, nilai kembalian yang dihasilkan
pada kolom ‘Job sub total’ adalah 0, sedangkan kolom ‘Dept sub total’ berisi nilai
1, karena kolom ‘department_id’ tidak digunakan untuk menghitung total gaji ini.
3. Nilai 4400 pada kolom ‘salary’ menunjukkan total gaji seluruh karyawan yang
berada di departemen 10 (tidak menggunakan job_id). Kolom yang digunakan
adalah kolom ‘department_id’. Karena itu, nilai kembalian yang dihasilkan pada
kolom ‘Dept sub total’ adalah 0, sedangkan kolom ‘Job sub total’ berisi nilai 1,
karena kolom ‘job_id’ tidak digunakan untuk menghitung total gaji ini.
4. Nilai 6000 pada kolom ‘salary’ menunjukkan total gaji karyawan yang bekerja
sebagai ‘MK_REP’ pada department 20. Kolom yang digunakan adalah kolom
‘department_id’ dan ‘job_id’. Karena itu, nilai kembalian yang dihasilkan pada
kolom ‘Dept sub total’ dan ‘Job sub total’ adalah 0.
LATIHAN SOAL JOIN
1. Buatkan perintah SQL untuk memunculkan last_name dari tabel (entitas) employees , city
dari tabel (entitas) locations, menggunakan NATURAL JOIN !
2. Buatkan perintah SQL untuk tampilan dibawah ini menggunakan :
A. USING
B. JOIN ON
Clue :
- department_name ada di entitas DEPARTMENTS
- salary ada di entitas EMPLOYEES
3. Buatlah tampilan seperti dibawah ini dengan mengimplementasikan salah satu fungsi
INNER JOIN, LEFT OUTER JOIN, FULL OUTER JOIN, RIGHT OUTER JOIN !
A.
B.
C.
D.
4. Tulisan SQL untuk gambar dibawah ini menggunakan ON dan ALIAS
Clue :
- last_name dan salary dari entitas employees
- department_name dari entitas departments
- city dari entitas locations
SOAL MATERI GROUPING
1. Tuliskan query yang dapat menampilkan manager_id, jumlah karyawan dan rata-rata gaji
yang diterima oleh suatu kelompok karyawan yang dikelompokkan berdasarkan manager_id,
dan diurutkan berdasarkan rata rata gaji yang terbesar ke yang terkecil.
2. Tuliskan query yang dapat menampilkan data job_id,manager_id, jumlah karyawan dan rata-rata
gaji karyawan yang dikelompokan berdasarkan manager_id yang berada dalam suatu kelompok
job_id, dan diurutkan berdasarkan job_id karyawan.
3. Tuliskan query yang dapat menampilkan job_id, jumlah karyawan dan rata-rata gaji yang karyawan
yang dikelompokkan berdasarkan job_id yang memiliki rata-rata gaji kurang dari 8000.
4. Tuliskan query yang dapat menampilkan data department_id, manager_id, dan total gaji yang
dikelompokkan berdasarkan manager_id yang berada dalam suatu kelompok department_id dengan
kondisi department_id dibawah 60 dan tampilkan subtotal dan total dari gaji yang diterima pada
tiap tiap kelompok data serta keterangan apakah baris tersebut menggunakan perhitungan dari
kelompok manager_id atau department_id.
5. Tuliskan query yang dapat menampilkan data department_id, manager_id, dan total gaji yang dikelompokkan
berdasarkan manager_id yang berada dalam suatu kelompok department_id dengan kondisi department_id
dibawah 50 dan tampilkan subtotal dan total dari gaji berdasarkan dimensi kelompok department_id dan
dimensi kelompok manager_id serta keterangan apakah baris
tersebut menggunakan perhitungan dari kelompok manager_id atau department_id.
Pertemuan 14
INSERT DAN CREATE TABLE
Tujuan Intruksional : Pokok Bahasan ini mempelajari tentang INSERT dan CREATE tabel dalam APEX
Kompetensi Yang Diharapkan : Mahasiswa diharapkan memahami tentang cara INSERT dan CREATE table dalam APEX
Waktu Pertemuan : 100 Menit
10.1 INSERT
INSERT adalah sebuah fungsi yang digunakan untuk memasukkan data ke dalam suatu tabel. Dengan
fungsi ini kita akan menambahkan satu row/baris data baru pada tabel yang diinginkan.
SQL untuk melakukan INSERT yaitu :
INSERT INTO nama_tabel
(kolom1, kolom2, kolom3, kolom4)
VALUES ( isi_kolom1, isi_kolom2, isi_kolom3, isi_kolom4);
Contoh : Kita ingin menambahkan data baru pada tabel copy_employees.
Sebelum melakukan INSERT ke dalam tabel, kita harus tahu dulu kolom apa saja yang terdapat pada
tabel tesebut. Jadi kita perlu melihatnya dengan perintah SELECT * FROM.
Dari situ kita bisa melihat kolom yang terdapat pada tabel copy_employees.
Sehingga perintah SQL untuk meng-INSERT data baru yaitu :
INSERT INTO copy_employees
(employee_id, first_name, last_name, email, phone_number, hire_date, job_id,
salary, commission_pct, manager_id, department_id)
VALUES
(300,'Fadhli', 'Iman', '[email protected]', '0857123456', '06-Jun-2016', 'AD_MGR',
50000, '.3', 100, 80);
Hasil :
Selain dengan menggunakan SQL diatas, kita juga bisa melakukan INSERT pada TABEL
tanpa perlu menuliskan nama kolom pada tabel, dengan syarat kita harus mengisi semua
VALUES pada setiap kolom tabel.
SQL:
INSERT INTO copy_employees
VALUES
(300,'Fadhli', 'Iman', '[email protected]', '0857123456', '06-Jun-2016', 'AD_MGR',
50000, '.3', 100, 80);
Namun untuk menghindari kekeliruan lebih dianjurkan untuk menuliskan setiap nama kolom
pada tabel yang akan di-INSERT.
HAL-HAL yang perlu diperhatikan dalam melakukan INSERT pada TABLE.
- Untuk setiap kolom yang memiliki tipe data selain INTEGER (misal VARCHAR,
DATE, dll) harus menggunakan tanda petik ( ' ) pada VALUES yang akan di-
INSERT.
- Jika ingin mengosongkan salah satu kolom pada TABEL yang akan di-INSERT
(memasukkan nilai NULL) maka dapat dilakukan dengan cara sebagai berikut:
Contoh : kita akan menginputkan data ke tabel copy_employees, tetapi tidak mengisi
kolom manager_id dan department_id.
Maka ada 2 cara untuk SQL yang dipakai :
Cara 1 (mengosongkan nilai VALUES):
INSERT INTO copy_employees
(employee_id, first_name, last_name, email, phone_number, hire_date,
job_id, salary, commission_pct, manager_id, department_id)
VALUES
(300,'Fadhli', 'Iman', '[email protected]', '0857123456', '06-Jun-2016',
'AD_MGR', 50000, '.3', '' , '');
Cara 2 (tidak memasukkan kolom yang ingin dikosongkan):
INSERT INTO copy_employees
(employee_id, first_name, last_name, email, phone_number, hire_date,
job_id, salary, commission_pct)
VALUES
(300,'Fadhli', 'Iman', '[email protected]', '0857123456', '06-Jun-2016',
'AD_MGR', 50000, '.3');
Sebelum memasukkan nilai null kita harus mengecek terlebih dahulu apakah kolom
tersebut bisa diisi NULL (NULLABLE) atau tidak. Caranya adalah dengan menggunakan
query SQL :
DESCRIBE copy_employees
Hasil :
Dari situ kita bisa melihat kolom mana yang bisa diisi nilai NULL dan yang tidak bisa.
10.2 CREATE
Fungsi CREATE digunakan untuk membuat sebuah TABEL baru di dalam database.
Sebelum membuat TABEL baru ada beberapa hal yang perlu diperhatikan dalam
memberi nama pada TABEL dan KOLOM pada tabel tersebut :
- Harus diawali dengan huruf.
- Harus terdiri dari 1-30 karakter.
- Karakter yang diperbolehkan adalah huruf A-Z, angka 0-9, _ (underscore), $, dan #.
- Nama tabel tidak boleh sama dengan tabel yang sudah ada.
- Nama kolom harus berbeda satu sama lain dalam satu tabel.
SQL yang digunakan untuk membuat sebuah TABEL yaitu :
CREATE TABLE nama_tabel
( kolom1 TIPEDATA (panjang),
kolom2 TIPEDATA (panjang),
.....
);
Contoh: kita akan membuat sebuah tabel baru dengan nama Mahasiswa, maka SQL yang
digunakan yaitu ;
CREATE TABLE mahasiswa
(nama VARCHAR (50) ,
npm VARCHAR (15),
jurusan VARCHAR (30),
tanggal_lahir DATE);
Hasil:
SOAL LATIHAN
1. Buatlah perintah SQL untuk meng-INSERT data ke tabel copy_employees, dengan
ketentuan sebagai berikut :
- First name dan last name menggunakan nama kalian masing-masing.
- Phone number diisi NPM kalian masing-masing.
-Hire date diisi tanggal lahir.
-Job ID diisi dengan :
Untuk kelas A : CA-STU Untuk
kelas B : CB-STU Untuk kelas C :
CC-STU Untuk kelas D : CD-
STU
-Salary diisi 5 digit terakhir NPM masing-masing.
-Commission PCT dan Manager ID dikosongkan.
-Department ID diisi 2 digit terakhir NPM.
2. Buatlah perintah SQL untuk membuat tabel yang memiliki minimal 5 kolom dan memiliki minimal 4 tipe data yang berbeda