Laporan Praktikum Desain Basis Data "Sub Query dan Indeks"

BAB I

DASAR TEORI

Subquery adalah Query di dalam query.

Kegunaan-kegunaan  Subquery dalam memanipulasi data:

• Meng-copy data dari satu tabel ke tabel lain
• Menerima data dari inline view
• Mengambil data dari tabel lain untuk kemudian di update ke tabel yang dituju
• Menghapus baris dari satu tabel berdasarkan baris dari tabel lain

meng-copy data dari satu tabel ke tabel lain

•  menggunakan Insert Statement

INSERT INTO penjualan(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE first_name LIKE ‘%an%’

nb: untuk membuat table dengan menyalin seluruh bentuk dan isi data pada table lain gunakan fungsi create seperti artikel DDL ini

Penjelasan :

Statement  1 : Masukkan data ke kolom id, name, salary, commission_pct pada tabel penjualan

Statement 2-4(subquery) : Data yang dimasukkan ke tabel penjualan yaitu data employee_id, last_name, salary, commission_pct dari tabel employees dimana first_name nya mengandung kata an

nb: asumsikan Tabel Penjualan telah dibuat sebelumnya dan schema yang digunakan yaitu schema HR

Hal yang perlu diperhatikan dalam menggunakan insert statement dalam mengcopy data yaitu :

1. Tidak menggunakan kata VALUES
2. Sesuaikan urutan kolom pada Insert Statement dan Subquery statement

Memasukkan data dengan menggunakan subquery sebagai tujuannya

INSERT INTO (SELECT employee_id, last_name, email, job_id, salary, department_id
FROM emp WHERE department_id = 50) VALUES (12345, ‘Taylor’, ‘DTAYLOR’,  ‘ST_CLERK’, 5000, 50);

Penjelasan : Isikan data 12345 ke kolom employee_id, Taylor ke kolom last_name, DTAYLOR ke kolom email, ST_CLERK ke kolom job_id,  5000 ke kolom salary, 50 ke kolom department_id pada TAbel Emp

 Menerima data dengan menggunakan SubQuery sebagai Source nya

SELECT last_name, salary, a.department_id, rataSalary
FROM employees a, (SELECT department_id, AVG(salary) rataSalary
FROM employees GROUP BY department_id) b
WHERE a.department_id = b.department_id AND salary > rataSalary;

Penjelasan :

kita ingin menampilkan last_name,  salary,  department_id dan  rata-rata Salary per department pada tabel employees dimana salary yang diperoleh lebih besar dari salary rata-rata per department. hasilnya didapatkan dengan cara menggabungkan dua tabel yaitu tabel employees a dan tabel B. B itu sendiri merupakan nama alias dari tabel subquery tersebut.

Pada tabel employees A kita menghasilkan last_name, salary, department_id dan pada Tabel B kita menghasilkan department_id dan rataSalary. Lalu setelah kita mendapatkan hasil dari kedua tabel tersebut, lalu kita gabungkan kedua tabel tersebut dengan menggunakan department_id pada tabel employees dan department id yang ada pada tabel B sebagai penghubungnya.

Mungkin cara diatas memang terkesan ribet dan timbul pertanyaan dibenak kalian, mengapa kalau  hanya ingin  menampilkan last_name,  salary,  department_id,  rataSalary tidak sesimpel dengan hanya menggunakan Group Function seperti yang telah dibahas sebelumnya

kira-kira query-nya seperti ini :

SELECT last_name, salary, department_id, AVG(Salary)
FROM employees GROUP BY last_name, salary, department_id having salary>AVG(Salary)

Bandingkan hasil query diatas, hasilnya jelas berbeda. Kenapa? karena pada query kedua  menampilkan  last_name, salary, department_id dan rata-rata salarynya. dimana rata-rata salary tersebut didapatkan dengan mengelompokkan Last_name dengan nama yang sama dan mempunyai salary yang sama lalu mempunyai department id yang sama. Sedangkan kita ingin menampilkan rata-rata salary per department dimana yang kita butuhkan hanya department idnya saja yang sama. Oleh karena query pertamalah yang benar.

Update 2 kolom dengan menggunakan subquery

UPDATE emp
SET job_id = (SELECT job_id FROM employees WHERE employee_id = 205),
salary = (SELECT salary FROM employees WHERE employee_id = 168 )
WHERE employee_id = 114

Penjelasan : memasukkan data-data baru ke kolom job_id dan salary yang mempunyi employee_id = 114

Menghapus baris berdasarkan nilai yang diperoleh dari baris pada tabel lain

DELETE FROM empl3
WHERE department_id = (SELECT department_id FROMdepartments WHERE department_name LIKE ‘%Public%’)

INDEX :

• adalah sebuah schema object
• digunakan oleh oracle server untuk meningkatkan kecepatan penerimaan data dengan menggunakan pointer
• Dapat mengurangi kebutuhan disk input/output dengan menggunakan sebuah metode rapid path access untuk mengalokasikan data dengan cepat
• Tabel yang independen
• Digunakan dan dipelihara secara otomatis oleh ORACLE SERVER

INDEX dapat dibuat dengan 2 cara yaitu :

1. Secara Otomatis
Index secara otomatis dibuat ketika  kita mendefinisikan sebuah primary key atau UNIQUE Constraint dalam mendefinisikan sebuah tabel

2. Secara Manual
user dapat membuat index yang sama pada kolom untuk meningkatkan kecepatan akses data

Tipe – tipe INDEX :

• Unique
  Memastikan kalau value dari kolom harus bersifat unique dan dibuat secara otomatis
• Non-Unique
  Memastikan cara tercepat untuk mencari data dan dibuat secara manual oleh user
• Single Column
  Hanya terdapat satu kolom dalam satu index
• Concatenated atau Composite
  Dapat terdiri hingga 16 kolom dalam satu index

Membuat Sebuah INDEX

syntax :

CREATE INDEX index
ON table (column[, column]…);

contoh Meningkatkan kecepatan query access pada kolom FIRST_NAME di tabel EMPLOYEES

CREATE INDEX emp_first_name_idx
ON employees(first_name);

Banyak INDEX Belum Tentu lebih baik

maksud dari statement diatas adalah jika mempunyai banyak index di dalam tabel, bukan berarti kita akan menghasilkan query yang lebih cepat, kenapa? Karena Setiap operasi DML yang terjadi dan di COMMIT pada tabel yang menggunakan INDEX, berarti setiap INDEX yang terdapat pada tabel tersebut juga harus disesuaikan dengan perubahan yang terjadi, yang menyebabkan kinerja  ORACLE SERVER menjadi lebih berat.

berikut merupakan petunjuk kapan sebaiknya index dibuat :

Menghapus Sebuah Index

DROP INDEX nama_index

nb: Index dapat dihapus oleh pemilik index tersebut saja dan hanya oleh orang yang mempunyai DROP ANY INDEX privileges. Dan index tidak dapat dirubah atau dimodifikasi

KOLOM UNIK

Unique berfungsi untuk menjaga agar tidak terjadinya duplikasi nilai (kesamaan data) dalam sebuah kolom, hal ini dapat ditangani dengan membuat sebuah indeks unik atau fungsi unik sendiri pada kolom yang dimaksud. Unique ini sering digunakan dalam pembuatan bukan primary key namun membutuhkan cek dupikasi agar tidak ada yang sama, karena dalam primary key sudah otomatis mempunyai sifat unik. Berikut StrukturSQL saat pembuatan tabel baru: CREATE TABLE [nama tabel] ([nama kolom] [tipe data] [unique]);

Ketika tabel sudah ada kita bisa menggunakan:

ALTER TABLE [nama tabel] ADD UNIQUE ([nama kolom]);

CHECK

Check berfungsi untuk melakukan pembatasan nilai masukan dalam sebuah kolom, sebagai contoh misalkan kita ingin agar kolom gender yang terdiri dari satu karakter hanya memiliki dua pilihan karakter yaitu M (male) atau F (Fimale) ini dapat kita seting dengan menggunakan CHECK. Dengan menggunakan CHECK maka sebuah kolom hanya bisa diisi dengan data yang memenuhi kriteria dalam CHECK. Berikut query contoh pengunaan check :

db_contoh=> CREATE TABLE pelanggan (db_contoh(> nama varchar(35),db_contoh(> kode_area CHAR(10) CHECK(length(trim(kode_area)) = 2), db_contoh(> umur INTEGER CHECK (umur >= 0), db_contoh(> gender CHAR(1) CHECK (gender IN (‘L’, ‘P’)), db_contoh(> ttl DATE CHECK (ttl BETWEEN ’1998-01-01′ AND CURRENT_DATE), db_contoh(> CHECK (upper(trim(nama)) != ‘nita’ OR db_contoh(> upper(trim(nama)) != ‘jeki’) db_contoh(> ); CREATE

TRIM

Suatu ketika pasti akan memiliki data yang di dalamnya terdapat spasi kosong yang tidak diperlukan, misalnya spasi ganda. Jika ada masalah seperti ini, kita dapat membersihkan spasi-spasi kosong yang tidak diperlukan menggunakan fungsi TRIM, RTRIM, dan LTRIM. Ketiga fungsi ini memiliki bentuk penggunaan sebagai berikut :

-  RTRIM : digunakan untuk membersihkan spasi kosong yang ada di bagian kanan (Right) String.

-LTRIM : digunakan untuk membersihkan spasi kosong yang ada di bagian kiri (Left) String.

-TRIM : digunakan untuk membersihkan spasi kosong yang ada di bagian kiri, kanan, maupun tengah String Berikut Struktur SQL nya :

Select trim([nama kolom]) from [nama tabel];

Dalam penggunaannya, fungsi TRIM memiliki tiga opsi. Ketiga opsi ini dapat digunakan untuk menentukan karakter apa yang akan dihapus dari suatu String. Jadi, fungsi TRIM juga dapat menghilangkan karakter tertentu (bukan spasi kosong saja) dari suatu string. Opsinya sebagai berikut:

-LEADING : merupakan opsi untuk menghilangkan karakter terpilih yang ada di sebelah kiri. Parameter Leading diartikan sebagai sufik dari karakter yang ada.

-TRAILING : merupakan opsi untuk menghilangkan karakter terpilih yang ada di sebelah kanan String. Parameter Trailing diartikan sebagai sufik dari karakter yang ada.

-BOTH : merupakan opsi yang dapat menangani parameter Leading maupun Trailing. Berikut Struktur SQL nya :

Select trim(LEADING ‘[karakter, misal : -]’ from [nama kolom]) from [nama tabel]; 

BAB II

HASIL PRAKTIKUM

A.    Hasil Praktikum PostgreSQL

1.      Menampilkan nama fakultas dan jumlah mahasiswa yang mempunyai ketentuan nama fakultas yang dimunculkan dengan jumlah mahasiswanya terkecil. Querynya sebagai berikut:

“SELECT nama_fak, count(*) as jumlah_mahasiswa from mahasiswa m, fakultas f where m.id_fak=f.id_fak GROUP BY f.nama_fak ORDER BY 2 ASC LIMIT 1;”.

2.      Selanjutnya menampilkan nama mahasiswa, nama fakultas, alamat dengan ketentuan nama fakultas sama dengan edi dan alamatnya tidak sama dengan luki.

Querynya sebagai berikut:

“SELECT nama_mah, nama_fak, alamat_mah from mahasiswa m, fakultas f where m.id_fak=f.id_fak and nama_fak in (SELECT nama_fak from fakultas f, mahasiswa m where m.id_fak=f.id_fak and nama_mah=’edi’) and alamat_mah <> SELECT alamat_mah from mahasiswa where nama_mah=’luki’);”

3.      Membuat index di tabel mahasiswa dalam kolom alamat, kemudian buat index lagi yang bersifat unik pada tabel fakultas dalam kolom fak_nama. Kemudian coba masukkan data yang sama.

Querynya seperti di bawah ini:

“CREATE INDEX ALAMAT_INDEX ON MAHASISWA (alamat_mah);

CREATE UNIQUE INDEX NAMA_FAK_INDEX ON FAKULTAS (nama_fak);”

4.      Buat kolom nama pada tabel mahasiswa menjadi unik, dan coba inputkan 2 data yang sama.

Querynya sebagai berikut:

“ALTER TABLE MAHASISWA ADD UNIQUE (nama_mah);”

Coba inputkan 2 nama yang sama.

5.      Pindah data dari tabel mahasiswa, fakultas ambil kolom nim, nama mahasiswa, alamat, dan nama fakultas ke tabel baru yang dinamai ‘identitas’.

Querynya sebagai berikut:

“SELECT nim_mah, nama_mah, alamat_mah, nama_fak into identitas from mahasiswa m, fakultas f where m.id_fak=f.id_fak;”.

6.      Coba buat contoh pembuatan check. Kita akan coba langsung menambahkan pada tabel yang ada.

Querynya sebagai berikut:

“ALTER TABLE mahasiswa ADD CHECK (gender in (‘L’,’P’));”

Coba inputkan data mahasiswa yang gendernya sesuai dan tidak sesuai syarat.

7.      Inputkan data di tabel mahasiswa dimana sebelum karakter dahulukan spasi dan diakhiri dengan tanda ‘+’. Misalnya ‘    linta++++’. Lalu munculkan seluruh data dan hilangkan spasi di depan.

Querynya sebagai berikut:

“SELECT TRIM (LEADING ‘ ‘ from nama_mah) FROM mahasiswa;”.

8.      Lalu coba munculkan seluruh data mahasiswa dengan menghilangkan karakter di akhir data dan karakter ‘a’ di awal pada kolom nama.

Querynya sebagai berikut:

“SELECT TRIM (both ‘a,+’ from nama_mah) FROM mahasiswa;”.

B.     Hasil Praktikum MySql

1.      Menampilkan nama fakultas dan jumlah mahasiswa yang mempunyai ketentuan nama fakultas yang dimunculkan dengan jumlah mahasiswanya terkecil. Querynya sebagai berikut:

“SELECT nama_fak, count(*) as jumlah_mahasiswa from mahasiswa m, fakultas f where m.id_fak=f.id_fak GROUP BY f.nama_fak ORDER BY 2 ASC LIMIT 1;”.

2.      Selanjutnya menampilkan nama mahasiswa, nama fakultas, alamat dengan ketentuan nama fakultas sama dengan edi dan alamatnya tidak sama dengan luki.

Querynya sebagai berikut:

“SELECT nama_mah, nama_fak, alamat_mah from mahasiswa m, fakultas f where m.id_fak=f.id_fak and nama_fak in (SELECT nama_fak from fakultas f, mahasiswa m where m.id_fak=f.id_fak and nama_mah=’edi’) and alamat_mah <> SELECT alamat_mah from mahasiswa where nama_mah=’luki’);” atau tanda ‘<>’ bisa diganti ‘not in’.

3.      Membuat index di tabel mahasiswa dalam kolom alamat, kemudian buat index lagi yang bersifat unik pada tabel fakultas dalam kolom fak_nama. Kemudian coba masukkan data yang sama.

Querynya seperti di bawah ini:

“CREATE INDEX alamat_index2 ON MAHASISWA (alamat_mah);

CREATE UNIQUE INDEX nama_fak_index2 ON FAKULTAS (nama_fak);”

4.      Buat kolom nama pada tabel mahasiswa menjadi unik, dan coba inputkan 2 data yang sama.

Querynya sebagai berikut:

“ALTER TABLE MAHASISWA ADD UNIQUE (nama_mah);”

Coba inputkan 2 nama yang sama.

5.      Pindah data dari tabel mahasiswa, fakultas ambil kolom nim, nama mahasiswa, alamat, dan nama fakultas ke tabel baru yang dinamai ‘identitas’.

Querynya sebagai berikut:

Buat tabelnya dulu

“CREATE TABLE identitas (nim_mah integer not null, nama_mah varchar(20), alamat_mah varchar(15), nama_fak varchar(15));”

Pindah data

“INSERT INTO identitas SELECT nim_mah, nama_mah, alamat_mah, nama_fak from mahasiswa m, fakultas f where m.id_fak=f.id_fak;”.

6.      Coba buat contoh pembuatan check. Kita akan coba langsung menambahkan pada tabel yang ada.

Querynya sebagai berikut:

“ALTER TABLE mahasiswa ADD CHECK (gender in (‘L’,’P’));”

Coba inputkan data mahasiswa yang gendernya sesuai dan tidak sesuai syarat.

7.      Inputkan data di tabel mahasiswa dimana sebelum karakter dahulukan spasi dan diakhiri dengan tanda ‘+’. Misalnya ‘    linta++++’. Lalu munculkan seluruh data dan hilangkan spasi di depan.

Querynya sebagai berikut:

“SELECT TRIM (LEADING ‘ ‘ from nama_mah) FROM mahasiswa;”.

8.      Lalu coba munculkan seluruh data mahasiswa dengan menghilangkan karakter di akhir data dan karakter ‘a’ di awal pada kolom nama.

Querynya sebagai berikut:

“SELECT TRIM(trailing ‘+’ from nama_mah) FROM mahasiswa;”

“SELECT TRIM(leading ‘a’ from nama_mah) FROM mahasiswa;”.

 Evaluasi PostgreSQL dan MySQL

- Ketika drop index menggunakan postgreSQL, perintah yang  digunakan adalah Drop index nama_index; Sedangkan pada MySQL menggunakan perintah : Alter table nama_table_dari_Index drop index nama_index;

- Ketika memindahkan data dari tabel mahasiswa di PostgreSQL cukup memberikan perintah kolom mana yang ingin ditampilkan kemudian langsung memasukkan kedalam tabel identitas seperti berikut : Select nim_mah, nama_mah, alamat_mah, nama_fak into identitas from mahasiswa,fakultas; sedangkan di MySQL harus membuat tabel setelah itu baru memasukkan kolom ke dalam tabel identitas,perintahnya sebagai berikut : CREATE TABLE identitas SELECT nim_mah, nama_mah, alamat_mah, nama_fak FROM mahasiswa,fakultas WHERE mahasiswa.id_fak=fakultas.id_fak;

- Pada bagian TRIM, di PostgreSQL boleh ada spasi setelah query TRIM, namun pada MySQL tidak boleh ada spasi. Kemudian pada check juga ada perbedaan, dalam MySQL untuk menggunakan fungsi CHECK harus menggunakan lain cara. Lalu pada TRIM di bagian BOTH juga terjadi perbedaan, kalau POstgreSQL boleh depan belakang, tapi kalai Mysql harus depannya atau belakangnya.

BAB III

KESIMPULAN

1. Query select into statement merupakan query  SQL yang digunakan untuk mengopi informasi dari tabel ke tabel yang lain tanpa membuat tabel sebelumnya.
2. Subquery  atau query Nested merupakan bentuk query yang terdapat dalam query yang lain.
3. Indeks disini berguna dalam suatu pencarian nilai atau data dalam database.
4. Unique berfungsi untuk menjaga agar tidak terjadinya duplikasi nilai (kesamaan data) dalam sebuah kolom, hal  ini dapat ditangani dengan membuat sebuah indeks  unik atau fungsi unik sendiri pada kolom yang dimaksud.
5. Check berfungsi untuk melakukan pembatasan nilai masukan dalam sebuah kolom, sebagai  contoh misalkan kita ingin agar kolom  gender  yang terdiri dari satu karakter hanya memiliki dua pilihan karakter yaitu M  (male) atau F  (Fimale) ini dapat kita seting dengan menggunakan CHECK. 
6. Suatu ketika pasti akan memiliki data yang di dalamnya terdapat spasi kosong yang tidak diperlukan, misalnya spasi ganda. Jika ada masalah seperti ini, kita dapat membersihkan  spasi-spasi kosong yang tidak diperlukan menggunakan fungsi TRIM, RTRIM, dan LTRIM.
7. RTRIM : digunakan untuk membersihkan spasi kosong yang ada di bagian kanan (Right) String.
8. LTRIM : digunakan untuk membersihkan spasi kosong yang ada di bagian kiri (Left) String.
9. TRIM : digunakan untuk membersihkan spasi kosong yang ada di bagian kiri, kanan, maupun tengah String.

DAFTAR PUSTAKA

1. http://risnotes.com/2011/12/subqueries/
2. http://risnotes.com/2012/01/index/
3. Modul Praktikum Desain Basis Data Bab VII
4. http://catatankuliahum.blogspot.com/2013/02/sub-query-basis-data.html

• Tweet
• Share
• Share
• Share
• Share

About Admin MC3

This is dummy text. It is not meant to be read. Accordingly, it is difficult to figure out when to end it. But then, this is dummy text. It is not meant to be read. Period.

Related Post