RESUME SQL 1 PERT.3

Menggunakan Single Row Function untuk meng-customize Output

Fungsi fungsi SQL

Fungsi fungsi sangat mendukung fitur pada SQL

 Melakukan perhitungan perhitungan pada data

 Memodifikasi item item data individual

 Memanipulasi keluaran dari kelompok baris baris

 Format untuk tampilan tanggal dan angka angka

 Konversi tipe data tipe data kolom

Ada dua tipe dari fungsi fungsi

 Single rows functions (fungsi fungsi baris tunggal)

 Multiple row functions (fungsi fungsi banyak baris)

Single Row Functions

Fungsi fungsi ini hanya digunakan pada baris baris tunggal dan mengembalikan satu hasil per baris.

Ada tipe tipe berbeda dari singlerow functions. Single row function mencakup hal hal berikut :

 Character (karakter)

 Number (angka)

 Date (tanggal)

 Conversion (konversi)

 General (umum)

Multiple row Functions

Fungsi fungsi dapat memanipulasi kelompok dari baris baris untuk memberi suatu hasil baris baris per kelompok. Fungsi fungsi ini dikenal juga sebagai group functions

Single Rows Functions

Singlerow function digunakan untuk memanipulasi item item data. Singlerow functions menerima satu atau lebih argument argument dan mengembalikan satu nilai untuk setiap baris yang dihasilkan oleh suatu query. Suatu argumen dapat berupa berikut ini :

 Usersupplied constant (Konstanta yang disediakan oleh user)

 Nilai variable

 Nama Kolom

 Ekspresi

Fitur fitur dari singlerow functions mencakup :

 Aksi pada setiap baris yang dikembalikan di dalam query

 Mengembalikan satu hasil per baris

 Memungkinkan pengembalian suatu nilai data dari suatu tipe berbeda daripada satu yang direferensikan.

 Mungkin menerima satu atau lebih argument argumen

 Dapat digunakan didalam klausa klausa SELECT, WHERE, dan ORDER BY;dapat disarangkan(nested)

Dalam sintak :

function_name adalah nama dari fungsi arg1, arg2 adalah setiap argumen yang digunakan oleh fungsi. Hal ini bisa jadi diwakili oleh suatu kolom atau ekspresi.

Cakupan single row function:

 Character Functions : Menerima input karakter dan dapat mengembalikan baik nilai nilai karakter ataupun angka.

 Number Functions : Menerima masukkan angka dan mengembalikan nilai nilai angka

 Date Functions : Bekerja pada nilai nilai dari tipe data DATE (semua date functions

mengembalikan suatu nilai dari tipe data DATE kecuali fungsi MONTHS_BETWEEN, yang mengembalikan suatu angka.)

 Coversion Functions : Mengkonversi suatu nilai dari satu tipe data ke tipe data lainnya.

 General Functions (fungsi fungsi umum) :

NVL

NVL

2

NULLIF

COALESCE

CASE

DECODE

Character Functions

Singlerow character functions menerima data karakter sebagai masukan dan dapat mengembalikan

baik nilai nilai karakter maupun angka. Character functions dapat dibagi menjadi :

 Case manipulation functions

 Characte rmanipulation Functions

LOWER(column/expression) : Mengkonversi nilai nilai karakter huruf menjadi lowercase (huruf kecil)

UPPER(column/expression) : Mengkonversi nilai nilai karakter huruf menjadi uppercase (huruf besar)

INITCAP(column/expression) : Mengkonversi nilai nilai alpha karakter menjadi uppercase untuk huruf pertama dari tiap kata; semua huruf huruf lain lowercase

CONCAT(column1/expressio1,colum2/expression2) : Menggabungkan nilai karakter pertama ke karakter kedua : sama dengan operator penggabungan ( || )

SUBSTR (column/expression,m[,n]) : Menghasilkan karakter karakter tertentu dari nilai karakter

dimulai pada posisi karakter kem,ken panjang karakter (jika m adalah negatif, dihitung mulai dari akhir nilai karakter. Jika n dihilangkan, menghasilkan semua karakter sampai akhir dari rangkaian.)

LENGTH (column/expression) : Mengembalikan jumlah karakter dalam ekspresi

INSTR (column/expression, 'string', [,m], [n]) : Mengembalikan posisi numerik dari suatu rangkaian penamaan. Secara optional, Anda dapat menyediakan suatu posisi kem untuk memulai pencarian, dan yang terjadi din dari suatu rangkaian. m dan n defaultnya 1, artinya pencarian dimulai di awal suatu pencarian dan melaporkan kejadian yang pertama.

LPAD (column /expression, n,'string') : Mengisi nilai karakter perataan kanan (rightjustified) ke suatu lebar total n posisi karakter

RPAD (column /expression, n,'string'): Mengisi nilai karakter perataan kiri (leftjustified) ke suatu lebar total n posisi karakter

TRIM (leading/trailing/both,trim_character FROM trim_source) : Memungkinkan Anda untuk memotong karakter karakter bagian awal atau bagian akhir atau kedua duanya dari suatu rangkaian karakter. Jika trim_character atau trim_source adalah suatu karakter literal, Anda harus mengapitnya dengan tanda petik tunggal. Ini adalah fitur yang ada di Oracle8i dan versi selanjutnya.

REPLACE (text, search_string, replacement_string): Mencari suatu ekspresi teks untuk suatu rangkaian karakter dan, jika ditemukan, digantikan dengan rangkaian yang telah ditentukan.

Contoh:

• LOWER (‘SQL Course’) à sql course
• UPPER (‘SQL Course’) à SQL COURSE
• INITCAP (‘SQL Course’) à Sql Course
• CONCAT(‘Halo’, ‘Dunia’) à HaloDunia
• SUBSTR (‘HaloDunia’,1,4) à Halo
• LENGTH(‘HaloDunia’) à 9
• INSTR(‘HaloDunia’, ‘D’) à 5
• LPAD (salary,10,’*’) à *****24000
• RPAD (salary,10,’*’) à 24000*****
• REPLACE (‘JACK and JUE’, ‘J’, ‘BL’) à BLACK and BLUE
• TRIM(‘H’ FROM ‘HaloDunia’) à aloDunia

ROUND (column | expression, n) : Membulatkan kolom, ekspresi, atau nilai posisi ken desimal atau, jika n dihilangkan, tidak ada posisi desimal ( Jika n adalah negatif, angka angka di kiri dari desimal dibulatkan.)

TRUNC (column | expression, n) : Memotong kolom, ekspresi, atau nilai posisi ken desimal atau, jika n dihilangkan, n defaultnya nol

MOD (m, n) : Mengembalikan sisa dari m yang dibagi oleh n

Contoh:

ROUND (45.926, 2) à 45.93

TRUNC (45.926, 2) à 45.92

MOD (1600, 300) à 100

Format Tanggal Oracle

Database Oracle menyimpan tanggal tanggal dalam suatu format angka sendiri, yang menunjukkan abad, tahun, bulan, hari, jam, menit, dan detik.

Tampilan default dan format inputan untuk setiap tanggal adalah DD-MON-RR.

Tanggal Oracle berlaku antara January 1,4712 B.C. (1 Januari 4712 S.M.) dan December 31, 9999 A.D. (31 Desember 9999 M.).

Contoh:

SELECT last_name, hire_date

FROM employee

WHERE hire_date < ’01-FEB-88’;

Contoh di atas, suatu output kolom HIRE_DATE ditampilkan format default DDMONRR.

Meskipun begitu, tanggal tanggal tidak disimpan dalam database dengan format ini. Semua bagian bagian dari tanggal dan waktu disimpan. Maka, walaupun suatu HIRE_DATE seperti 17-JUN-87 ditampilkan sebagai hari, bulan, dan tahun, ada juga informasi waktu dan abad disatukan dengan date tersebut. Data yang lengkap bisa jadi June 17, 1987, 5:10:43 p.m. (17 Juni 1987, 5:10:43 petang).

Saat suatu record pada suatu kolom tanggal disisipkan ke dalam suatu tabel, suatu informasi abad diambil dari fungsi SYSDATE. Meskipun demikian, ketika suatu kolom tanggal ditampilkan di layar, bagian abad tidak ditampilkan (secara default).

Secara internal tipe data DATE selalu menyimpan informasi tahun sebagai empat digit angka : dua digit untuk abad dan dua digit untuk tahun. Sebagai contoh, database Oracle menyimpan tahun dengan cara 1987 atau 2004, dan tidak seperti 87 atau 04.

Fungsi SYSDATE

SYSDATE adalah fungsi tanggal yang mengembalikan tanggal dan waktu server database saat ini.

Anda dapat menggunakan SYSDATE seperti Anda ingin menggunakan setiap nama kolom lain.

Sebagai contoh, Anda dapat menampilkan tanggal saat ini dengan memilih SYSDATE dari suatu tabel. Biasanya tabel untuk memilih SYSDATE dari suatu tabel contoh disebut tabel DUAL.

Contoh

Menampilkan tanggal saat ini menggunakan tabel DUAL.

SELECT SYSDATE

FROM DUAL;

Aritmatika pada Dates

 Menambah atau mengurangkan suatu angka ke atau dari suatu tanggal yang menghasilkan nilai date.

 Mengurangkan dua tanggal untuk mencari angka dari hari hari antara tanggal tanggal tersebut.

 Menambah jam ke suatu tanggal dengan membagi suatu angka dari jam dengan 24.

Dates Functions

Date function bekerja pada tanggal –tanggal Oracle. Semua date function mengembalikan suatu nilai dengan tipe data DATE kecuali MONTHS_BETWEEN,yang mengembalikan nilai numerik.

 MONTHS_BETWEEN (date1, date2) : Mencari jumlah bulan diantara date1 dan

date2. Hasilnya bisa jadi positif atau negatif. Jika date1 lebih awal daripada date2,

hasilnya adalah positif ; jika date1 lebih awal daripada date2, hasilnya adalah negatif .

Sebagian dari hasil bukan bilangan bulat (noninteger) menunjukkan suatu bagian dari bulan.

 ADD_MONTHS (date, n) : Menambahkan n jumlah suatu bulan kalender ke date. Nilai dari n harus bilangan bulat (integer) dan bisa negatif.

 NEXT_DATE (date, ’char’) : (‘char’) setelah date menemukan suatu tanggal

dari suatu hari tertentu pada suatu minggu. Nilai dari char bisa angka yang mewakili suatu hari atau suatu karakter string.

 LAST_DAY (date) : Mencari hari terakhir dari suatu tanggal dalam suatu bulan yang

berisi date.

 ROUND (date[,’fmt’] ) : Mengembalikan pembulatan date ke suatu unit yang

ditentukan oleh model format fmt. Jika model format fmt dihilangkan, date dibulatkan

ke hari terdekat.

 TRUNC (date[,’fmt’] ) : Mengembalikan date dengan bagian suatu waktu dari suatu

hari yang dipotong ke unit yang ditentukan oleh model format fmt. Jika model format fmt

dihilangkan , date dipotong ke hari terdekat.

Conversion Functions

Disamping tipe data tipe data Oracle, kolom kolom dari table table di dalam Oracle dapat

didefinisikan menggunakan tipe data tipe data ANSI, DB2, dan SQL/DS. Meskipun demikian, server Oracle secara internal merubah tipe data tipe data yang sama dengan tipe data tipe data Oracle.

Dalam beberapa kasus, server Oracle menggunakan data dari suatu tipe data dimana server Oracle memperkirakan data dari suatu tipe data yang lain. Ketika ini terjadi, server Oracle dapat secara otomatis mengubah suatu data ke tipe data yang diperkirakan. Perubahan tipe data ini bisa jadi dilakukan secara implisit (implicitly) oleh server Oracle atau secara eksplisit (explicitly) oleh user.

Konversi konversi tipe data implisit bekerja menurut aturan aturan yang dijelaskan dalam slide berikutnya.

Konversi konversi tipe data eksplisit dilakukan dengan menggunakan conversion functions.

Conversion functions merubah suatu nilai dari suatu tipe data ke tipe data lain. Umumnya, bentuk dari nama nama fungsi mengikuti konvensi (kesepakatan) data type TO data type. Tipe data yang pertama adalah tipe data input;tipe data yang kedua adalah output.

Catatan : Meskipun disediakan konversi tipe data implisit, Anda diijinkan melakukan konversi tipe data eksplisit untuk memastikan kebenaran pernyataan SQL Anda.

Konversi Tipe Data Implisit

Untuk penugasan penugasan, server Oracle dapat secara otomatis mengkonversi berikut ini :

Dari varchar2 ke number

Dari varchar2 ke date

Dari number ke varchar2

Dari date ke varchar2

RESUME SQL 1 PERT. 4

Menampilkan Data Dari Beberapa Tabel

Tipe Tipe pada Join Join yang mengacu pada standar SQL:1999 termasuk sebagai berikut :

 Cross joins

 Natural joins

 Klausa USING

 Full (atau twosided) outer joins

 Kondisi kondisi penggabungan tidak tetap untuk outer join

Menggabungkan Tabel tabel

Menggunakan Sintak SQL:1999 Gunakan suatu join untuk mengquery data dari beberapa tabel :

SELECT table1.column, table2.column

FROM table1

[NATURAL JOIN table2] |

[JOIN table2 USING (column_name)] |

[JOIN table2

            ON {table1.column_name = table2.column_name}]|

[LEFT |RIGHT | FULL OUTER JOIN table2

            ON {table1.column_name = table2.column_name}]|

[CROSS JOIN table2];

Mendefinisikan Join

Dalam sintak:

table1.column menunjukkan tabel dan kolom dari mana data diperoleh NATURAL JOIN menggabungkan dua tabel berdasarkan nama kolom yang sama JOIN table USING column_name melakukan suatu equijoin berdasarkan nama kolom. JOIN table ON table1.column_name melakukan suatu equijoin berdasarkan suatu kondisi pada klausa ON, = table2.column_name LEFT/RIGHT/FULL OUTER digunakan untuk melakukan outer joins.

CROSS JOIN mengembalikan suatu Cartesian product dari dua tabel.

Membuat Natural Joins

 Klausa NATURAL JOIN adalah didasarkan pada semua kolom pada dua tabel yang memiliki nama yang sama.

 NATURAL JOIN memilih baris baris dari dua tabel yang memiliki nilai nilai yang sama dalam semua kolom yang sesuai.

 Jika kolom kolom memiliki nama yang sama memiliki tipe data berbeda, menghasilkan suatu error.

Natural Join dengan suatu klausa WHERE

Pembatasan tambahan pada suatu natural join diterapkan dengan menggunakan klausa WHERE.

Contoh berikut membatasi baris baris sebagai outputnya pada suatu department ID sama dengan 20 atau 50 :

SELECT department_id, department_name,

location_id, city

FROM departments

NATURAL JOIN locations

WHERE department_id IN(20,50);

Klausa USING

Natural joins menggunakan semua kolom dengan menyesuaikan nama nama dan tipe data tipe

Data untuk menggabungkan table tabel.

Klausa USING dapat digunakan untuk menentukan hanya kolom kolom tertentu yang akan digunakan untuk suatu equijoin. Kolom kolom yang direferensikan pada klausa USING tidak akan memiliki suatu perubah/qualifier (nama tabel atau alias) di manapun pada pernyataan SQL.

Sebagai contoh, pernyataan berikut adalah tepat :

SELECT l.city, d.department_name

FROM locations l JOIN departments d USING (location_id)

WHERE location_id = 1400;

Pernyataan berikut tidak tepat karena LOCATION_ID dirubah (qualified) pada klausa WHERE :

SELECT l.city, d.department_name

FROM locations l JOIN department d USING (location_id)

WHERE d.location_id = 1400;

ORA25154:

column part of USING clause cannot have qualifier

Pembatasan yang sama juga berlaku untuk NATURAL join. Oleh karena itu, kolom kolom yang memiliki nama yang sama pada kedua tabel harus digunakan tanpa ada qualifier/perubah.

Klausa USING untuk Equijoins

Untuk menentukan nama departement dari para pekerja, Anda membandingkan nilai pada kolom

DEPARTMENT_ID di dalam tabel EMPLOYEES dengan nilai nilai DEPARTMENT_ID di dalam tabel DEPARTMENTS. Hubungan antara table table EMPLOYEES dan DEPARTMENTS adalah suatu equijoin; karena itu, nilai nilai pada kolom DEPARTMENT_ID di kedua tabel harus sama.

Seringkali, tipe dari join ini melibatkan kelengkapan kelengkapan primary dan foreign key.

Catatan: Equijoins juga disebut simple joins atau inner joins.

Merubah Nama Nama Kolom Ambigu

 Gunakan awalan awalan tabel untuk merubah nama nama kolom yang ada pada beberapa tabel.

 Gunakan awalan awalan tabel untuk meningkatkan performa.

 Gunakan kolom kolom alias untuk membedakan kolom kolom yang memiliki nama nama

sama tapi berada dalam table table berbeda.

 Jangan gunakan alias alias pada kolom kolom yang disebutkan pada klausa USING dan dimanapun terdaftar pada pernyataan SQL.

Menggunakan Tabel Tabel Alias

Merubah nama nama kolom dengan nama nama tabel bisa sangat menghabiskan banyak waktu,

khususnya jika nama nama tabel yang digunakan panjang. Anda dapat menggunakan table tabel

alias daripada nama nama tabel. Seperti suatu kolom alias memberikan nama lain suatu kolom, suatu tabel alias memberikan nama lain suatu tabel. Tabel table alias membantu menjaga kode SQL menjadi lebih sederhana, sehingga menghemat penggunaan memory.

Perhatikan bagaimana table table alias diidentifikasi pada klausa FROM dalam contoh. Suatu nama tabel disebutkan secara utuh, diikuti oleh spasi dan kemudian suatu tabel alias. Tabel EMPLOYEES diberikan suatu alias e, dan tabel DEPARTMENTS memiliki suatu alias d.

Pedoman pedoman

 Tabel table alias dapat mencapai 30 karakter panjangnya, tapi alias alias pendek lebih baik

daripada alias alias panjang.

 Jika suatu tabel alias digunakan untuk nama tabel khusus pada suatu klausa FROM, maka table alias itu harus diganti dengan nama tabel sepanjang pernyataan SELECT.

 Tabel table alias sebaiknya mempunyai arti.

 Tabel alias berlaku hanya pada pernyataan SELECT saat ini.

Membuat Join Join dengan Klausa ON

 Kondisi join untuk natural join pada dasarnya adalah suatu equijoin pada seluruh kolom kolom dengan nama yang sama.

 Gunakan klausa ON untuk menentukkan kondisi kondisi perubahan atau menentukan kolom kolom untuk digabungkan.

 Kondisi penggabungan adalah dipisahkan dari kondisi kondisi pencarian lain.

 Klausa ON membuat kode mudah untuk dipahami.

Menggabungkan Suatu Tabel dengan Tabel itu Sendiri

Kadangkala Anda perlu untuk menggabungkan suatu tabel dengan tabel itu sendiri. Untuk mencari nama manager dari masing masing pegawai, Anda perlu untuk menggabungkan tabel EMPLOYEES dengan tabel EMPLOYEES sendiri, atau melakukan suatu selfjoin.

Sebagai contoh, untuk mencari nama manager dari Lorentz, Anda perlu untuk :

 Mencari nama Lorentz dalam tabel EMPLOYEES dengan mencarinya di kolom LAST_NAME.

 Mencari nomor manager dari Lorentz dengan mencarinya di kolom MANAGER_ID. Nomor

manager Lorentz adalah 103.

 Mencari nama manager dengan EMPLOYEE_ID 103 dengan mencarinya di kolom

LAST_NAME. Hunold adalah pegawai dengan nomor pegawai 103, jadi Hunold adalah

manager dari Lorentz.

Pada proses ini, Anda mencarinya dalam tabel dua kali. Yang pertama mencari nama Lorentz di

kolom LAST_NAME dan nilai dari 103 di MANAGER _ID. Kedua saat Anda mencari nomor 103 di kolom EMPLOYEE_ID dan mencari nama Hunold di kolom LAST_NAME.

Menerapkan Kondisi Kondisi Tambahan pada Suatu Join

Anda dapat menerapkan kondisi kondisi tambahan untuk join.

Pada contoh ditunjukkan suatu join pada table table EMPLOYEES dan DEPARTMENTS dan, sebagai tambahan, ditampilkan hanya pegawai pegawai yang memiliki nomor manager 149. Untuk menambah penambahan kondisi kondisi pada klausa ON, Anda dapat menambahkan klausa klausa AND. Sebagai alternatif, Anda dapat menggunakan klausa WHERE untuk menerapkan tambahan kondisi kondisi :

SELECT e.employee_id, e.last_name, e.department_id,

d.department_id, d.location_id

FROM employees e JOIN departments d

ON (e.department_id = d.department_id)

WHERE e.manager_id = 149;

ThreeWay Joins

Suatu ThreeWay join adalah suatu join pada tiga buah tabel. Dalam acuan sintak SQL:1999, join join dilakukan dari kiri ke kanan. Jadi join pertama dilakukan EMPLOYEES JOIN DEPARTMENTS. Kondisi join pertama dapat merujuk kolom kolom pada tabel EMPLOYEES dan DEPARTMENTS tapi tidak bisa merujuk ke kolom kolom pada LOCATIONS. Kondisi join kedua dapat merujuk kolom kolom pada ketiga tabel.

Non Equijoins

Suatu nonequijoins adalah suatu kondisi join yang berisi suatu operator kesamaan (equality).

Hubungan antara tabel EMPLOYEES dengan tabel JOB_GRADES adalah suatu contoh dari nonequijoins.

Hubungan antara kedua kolom adalah bahwa kolom SALARY dalam tabel EMPLOYEES

harus berada diantara nilai nilai di kolom kolom LOWEST_SALARY dan HIGEST_SALARY dalam tabel JOB_GRADES. Hubungan diperoleh menggunakan suatu operator lain daripada kesamaan (=).

Penting untuk diperhatikan bahwa semua pegawai hanya muncul sekali saat query ini dijalankan.

Tidak ada nama pegawai yang diulang dalam daftar. Ada dua alasan untuk itu :

 Tidak satupun baris dalam tabel job grade yang berisi tingkatan tingkatan yang overlap. Maka,

nilai penghasilan untuk seorang pegawai bisa berada hanya di antara salah satu barisbaris

penghasilan terendah dan penghasilan tertinggi dalam tabel tingkat gaji.

 Semua penghasilan para pegawai berada dalam batas batas yang disediakan oleh tabel job

grade. Maka, tidak akan ada pegawai mendapat penghasilan kurang dari nilai terendah yang

termasuk pada kolom LOWEST_SAL atau lebih dari nilai tertinggi yang termasuk pada kolom

HIGHEST_SAL.

Catatan: Kondisi kondisi lain (seperti <= dan >=) dapat digunakan, tapi lebih sederhana

menggunakan BETWEEN. Ingat untuk menentukan lebih dulu nilai terendah dan kemudian nilai

tertinggi saat menggunakan BETWEEN.

Mengembalikan Record Record yang Tidak Secara Langsung Sesuai dengan Outer Joins

Jika suatu baris tidak memenuhi kondisi join, baris tidak muncul pada hasil query. Sebagai contoh, dalam kondisi equijoin pada table table EMPLOYEES dan DEPARTMENTS, nomor department 190 tidak muncul karena tidak ada pegawai dengan nomor department tersebut dicatat dalam table EMPLOYEES. Malahan tampak 20 orang pegawai pada sekelompok hasil, Anda lihat 19 record. Untuk mengembalikan catatan departemen yang tidak mempunyai beberapa pegawai, Anda bisa gunakan outer join

INNER Join Versus OUTER Join

Menggabungkan table table dengan klausa klausa NATURAL JOIN, USING atau ON menghasilkan suatu inner join. Beberapa baris yang tidak sesuai tidak ditampilkan pada output. Untuk mengembalikan baris baris yang tidak sesuai, Anda bisa menggunakan suatu outer join. Suatu outer join mengembalikan semua baris baris yang memenuhi kondisi join dan juga mengembalikan beberapa atau semua baris baris tersebut dari satu tabel yang mana tidak ada baris baris dari table lain yang memenuhi kondisi join.

Ada tiga tipe dari outer join:

 LEFT OUTER

 RIGHT OUTER

 FULL OUTER

Cartesian Products

 Suatu Cartesian product terbentuk ketika :

Suatu kondisi join dihilangkan

Suatu kondisi join tidak tepat

Seluruh baris dalam tabel pertama digabungkan ke seluruh baris dalam tabel kedua

 Untuk menghindari suatu Cartesian product, selalu sertakan sebuah kondisi join yang tepat.

Membuat Cross Join

 Klausa CROSS JOIN menghasilkan crossproduct dari dua tabel.

 CROSS JOIN disebut juga Cartesian product antara dua tabel.