MODUL 4 Query SELECT dan DML (INSERT, UPDATE, DELETE)
PRAKTIKUM BASIS DATA LANJUT TEKNIK PERANGKAT LUNAK UNIVERSITAS PGRI RONGGOLAWE
BAGIAN 1 QUERY SELECT Tujuan Pembelajaran : • • •
Mengetahui kemampuan dari SQL SELECT Statement Dapat membuat perintah SQL dengan SELECT Statement Dapat menggunakan iSQL*PLUS sebagai antarmuka pengolahan query
4.1 Pengenalan iSQL*PLUS iSQL*PLUS adalah SQL*PLUS berbasis web yang disediakan oleh Oracle, didalamnya kita bisa menampilkan struktur tabel, mengedit SQL Statement, menjalankan perintah SQL, menyimpan SQL Statement ke dalam file script, dan memanggil file script yang sudah disimpan.
4.2 Logging in iSQL*PLUS Untuk memanggil iSQL*PLUS pada ORACLE 10g, ketahui alamat pemanggilan ISQL*PLUS dengan cara membuka file : “ C:\oracle\product\10.2.0\db_1\install\readme.txt “
Kemudian perhatikan alamat yang berada di bawah : “ The iSQL*Plus URL is : “. Ketik ulang alamat tersebut pada WEB BROWSER sehingga muncul halama awal iSQL*Plus.
Apabila ORACLE 10g diinstall lengkap dengan SAMPLE SCHEMA, isikan dengan Username : scott, Password : password_yang_ditentukan_oleh_praktikum, Connect Identifier : orcl
2
1
Percobaan 1 : Lakukan kembali langkah-langkah pemanggilan iSQL*Plus di atas :
4.3 Menampilkan Struktur Tabel Untuk menampilkan struktur table digunakan perintah DESCRIBE. Formatnya : DESC namatabel atau DESCRIBE namatabel Percobaan 2 : Tampilkan struktur tabel “emp” dengan menggunakan iQSL*Plus :
Name EMPNO
Null? NOT NULL
Type NUMBER(4)
ENAME
VARCHAR2(10)
JOB
VARCHAR2(9)
MGR
NUMBER(4)
HIREDATE
DATE
SAL
NUMBER(7,2)
COMM
NUMBER(7,2)
DEPTNO
NUMBER(2)
4.4 Berinteraksi dengan File Script Apa yang ditulis dalam editing window pada iSQL*PLUS dapat disimpan sebagai file script. Tombol untuk melakukan penyimpanan suatu query kedalam file .sql pada iSQL*PLUS adalah Tombol “Save Script” – Ditunjukkan dengan angka 1 pada gambar halaman Workspace iSQL*PLUS. Sedangkan tombol untuk melakukan pemanggilan isi file .sql adalah tombol “Load Script” – Ditunjukkan dengan angka 2 pada gambar halaman workspace iSQL*PLUS. Percobaan 3 : Simpan perintah untuk menampilkan struktur tabel pada percobaan 2 dengan filename NPM anda dan letakkan file tersebut pada DESKTOP . Bersihkan Workspace iSQL*PLUS. Kemudian lakukan pemanggilan file tersebut sehingga perintah untuk menampilkan struktur tabel muncul dan cobalah untuk mengeksekusi perintah tersebut:
4.5 Dasar Statement SELECT Sintak (cara penulisan) dari statement SELECT : SELECT [DISTINCT] {*, column [alias], … } FROM table; SELECT digunakan untuk memilih kolom yang ingin ditampilkan. FROM digunakan untuk memilih table asal. Aturan Penulisan Statement SQL
• • • • •
SQL Statement tidak case sensitive artinya tidak dibedakan antara penulisan huruf kecil dan huruf besar. SQL Statement dapat terdiri dari lebih dari satu baris. Keyword tidak bisa disingkat atau dipisah di baris yang berbeda. Klausa biasanya ditempatkan pada baris yang berbeda. Inden digunakan untuk memudahkan pembacaan
4.6 Memilih semua Kolom Percobaan 4 : Lakukan query untuk menampilkan seluruh data pada tabel “emp”;
EMPNO ENAME
JOB
7369 SMITH CLERK
MGR HIREDATE SAL COMM DEPTNO 7902 17-DEC-80
800
7499 ALLEN SALESMAN 7698 20-FEB-81
1600
300
30
7521 WARD
SALESMAN 7698 22-FEB-81
1250
500
30
7566 JONES
MANAGER
2975
7839 02-APR-81
20
20
Akan terdapat beberapa baris data yang kolom “COMM” tidak berisi data apapun. Hal tersebut dikatakan NULL. Nilai NULL (kosong) pada suatu kolom bisa berarti ada data yang tidak diisi, atau tidak diketahui nilainya. Nilai NULL tidak sama dengan NOL (zero). Nilai NULL juga tidak sama dengan spasi kosong.
4.7 Memilih Kolom yang Spesifik Percobaan 5 : Lakukan query untuk menampilkan data kolom “ENAME” dan “HIREDATE” pada tabel “emp”;
ENAME
HIREDATE
SMITH
17-DEC-80
ALLEN
20-FEB-81
WARD
22-FEB-81
JONES
02-APR-81
4.8 Ekspresi Aritmatik Suatu ekspresi yang melibatkan tipe data bilangan (NUMBER) dan tanggal (DATE) menggunakan ekspresi artimatika. Contoh : SELECT last_name, salary, salary+300 FROM employee Percobaan 6 : Lakukan query untuk menampilkan data kolom “ENAME”, “HIREDATE” dan “SAL” yang ditambahkan dengan 250 pada tabel “emp”;
ENAME
HIREDATE
SAL+250
SMITH
17-DEC-80
1050
ALLEN
20-FEB-81
1850
WARD
22-FEB-81
1500
JONES
02-APR-81
3225
4.9 Operator Presedence Perkalian dan pembagian memiliki prioritas (precedence) lebih tinggi daripada penambahan dan pengurangan. Contoh : SELECT last_name, salary, 12*salary+300 FROM employee Percobaan 7 : Lakukan query untuk menampilkan data kolom “ENAME”, “HIREDATE” dan “SAL” yang dikalikan 50 dan ditambahkan dengan 250 pada tabel “emp”;
ENAME
HIREDATE
50*SAL+250
SMITH
17-DEC-80
40250
ALLEN
20-FEB-81
80250
WARD
22-FEB-81
62750
JONES
02-APR-81
149000
4.10
Penggunaan Tanda Kurung
Penggunaan tanda kurung memiliki prioritas paling tinggi dibanding presedensi operator yang lain. Contoh : SELECT last_name, salary, 12*(salary+300) FROM employee Percobaan 8 : Lakukan query untuk menampilkan data kolom “ENAME”, “HIREDATE” dan “SAL” yang isinya ditambahkan dengan 250 kemudian baru dikalikan 50 pada tabel “emp”;
ENAME
HIREDATE
50*(SAL+250)
SMITH
17-DEC-80
52500
ALLEN
20-FEB-81
92500
WARD
22-FEB-81
75000
JONES
02-APR-81
161250
4.11
Menggunakan Kolom Alias
Judul (secara default) pada tiap kolom yang ditampilkan (heading) selalu sama dengan nama kolomnya dan ditulis dengan hutuf besar. Penggantian judul kolom, sehingga tidak sama dengan judul default disebut kolom alias. Contoh :
SELECT last_name AS “Name” FROM employee; Atau SELECT last name “Name” FROM employee;
Percobaan 9 : Lakukan query untuk menampilkan data kolom “ENAME” dengan nama kolom aliasnya “Nama Pegawai” dan “HIREDATE” dengan nama kolom aliasnya “Tanggal Diterima” pada tabel “emp”;
Nama Pegawai
Tanggal Diterima
SMITH
17-DEC-80
ALLEN
20-FEB-81
WARD
22-FEB-81
JONES
02-APR-81
4.12
Operator Penyambungan
Nilai dari dua kolom atau lebih dapat digabungkan dengan menggunakan operator penyambungan ( || ). Contoh : SELECT last_name || job_id AS “Employees” FROM employees;
Percobaan 10 : Lakukan query untuk menampilkan gabungan data kolom “ENAME” dan “JOB” dengan nama kolom aliasnya “Nama Samaran” pada tabel “emp”;
Nama Samaran SMITHCLERK ALLENSALESMAN WARDSALESMAN JONESMANAGER 4.13
Literal Character Strings
Literal dapat berupa karakter, ekspresi atau bilangan yang terdapat pada klausa SELECT. Literal berupa tanggal (DATE) dan karakter harus diapit dengan tanda petik tunggal. Contoh : SELECT last_name || ‘working as’ || job_id AS “Employee Details” FROM employee; Percobaan 11 : Lakukan query untuk menampilkan gabungan data kolom “ENAME” dan “JOB” dengan kalimat penyambung “Bekerja Sebagai” nama kolom aliasnya “Nama dan Posisi” pada tabel “emp”;
Nama dan Posisi SMITH Bekerja Sebagai CLERK ALLEN Bekerja Sebagai SALESMAN WARD Bekerja Sebagai SALESMAN JONES Bekerja Sebagai MANAGER
4.14
Baris yang Duplikat dan cara menghindari munculnya baris duplikat
Perintah query akan memberikan hasil termasuk suatu nilai yang bisa muncul lebih dari satu kali. Untuk menghindari hal tersebut maka gunakan DISTINC setelah kata SELECT. Contoh : SELECT DISTINCT department_id FROM employee; Percobaan 12 : Lakukan query untuk menampilkan data kolom “JOB” tanpa ada yang boleh berulang pada tabel “emp”;
JOB CLERK SALESMAN PRESIDENT MANAGER
BAGIAN 2 DML (INSERT, UPDATE, DELETE) Tujuan Pembelajaran : • • • •
Memahami Statement DML (Data Manipulation Language) Menyisipkan baris ke dalam table Merubah baris dalam table Menghapus baris dari table
4.15
Data Manipulation Language
Data Manipulation Language (DML) adalah suatu statement yang dijalankan pada saat kita memerlukan :
• • •
penambahan baris baru pada table memodifikasi baris yang ada pada table menghapus baris yang ada pada table
DML Statement identik dengan operasi INSERT, MODIFY dan DELETE. Istilah Transaksi mengandung pengertian kumpulan Statement DML yang membentuk suatu fungsi tertentu.
4.16
Menambahkan Baris Baru ke dalam Tabel (INSERT)
Menambahkan baris baru ke dalam table menggunakan perintah INSERT. INSERT INTO table [(column [, column …] ) ] VALUES
(value [, value…] );
Percobaan 13 : Menyisipkan baris ke dalam table DEPT30
4.17
INSERT dengan nilai NULL
Kolom yang tidak disebutkan dalam perintah INSERT INTO secara otomatis akan diisi dengan nilai NULL. Percobaan 14 : Lakukan proses INSERT seperti pada contoh berikut, sehingga kolom loc akan berisi nilai NULL
Percobaan 15: Lakukan proses INSERT, bila tidak disebutkan kolom apa saja yang harus diisi, maka nilai pada VALUES harus mencantumkan semua kolom yang ada pada table sesuai dengan urutannya.
4.18
INSERT menggunakan Fungsi dan Nilai terformat
Suatu fungsi bisa digunakan sebagai suatu nilai dalam perintah INSERT. Percobaan 16: Lakukan proses INSERT yang menyertakan fungsi sysdate ke dalam table EMP
Nilai yang dimasukkan ke dalam perintah INSERT bisa menggunakan nilai terformat. Percobaan 17 :Lakukan proses INSERT dengan menggunakan nilai data tanggal yang diformat.
4.19
INSERT dengan variable substitusi
Pada perintah INSERT bisa dicantumkan variable substitusi. Percobaan 18 : Lakukan proses INSERT yang mencantumkan variable substitusi dari suatu nilai kolom pada baris baru yang akan dimasukkan ke dalam table.
4.20
Pembuatan Script dengan Kustomisasi Prompt
Untuk melakukan kustomisasi, dapat digunakan perintah ACCEPT dan PROMPT. Percobaan 19 : Dengan penambahan kustomisasi prompt. Buat file .sql dengan nama file NPM anda dan kemudian save di drive C. Isi dari file tersebut adalah sebagai berikut ACCEPT department_id PROMPT 'Silahkan masukkan nomer department : ' ACCEPT department_name PROMPT 'Silahkan masukkan nama department : ' ACCEPT location PROMPT 'Silahkan masukkan lokasi department : ' INSERT INTO dept(deptno,dname,loc) VALUES (&department_id,'&department_name','&location');
Kemudian jalankan dengan mengetikkan sebgai berikut : SQL> @c:\nomor_npm.sql
4.21
Mengkopi Baris dari Tabel lain
Perintah INSERT juga bisa digunakan untuk mengkopi baris data yang berasal dari table yang lain. Percobaan 20 : Berikut ini akan ditambahkan baris baru ke dalam table manager yang berasal dari table pegawai yang pekerjaannya = ‘MANAGER’
4.22
Perubahan Data dalam Tabel (UPDATE)
Untuk memodifikasi baris data yang ada pada table digunakan perintah UPDATE. Sintak dari perintah UPDATE : UPDATE table SET column = value [, column = value, …] [WHERE condition]; Percobaan 21 : Ubah nomer department menjadi = 20, untuk pegawai yang memiliki nomer department = 7782;
Semua baris pada table akan dimodifikasi jika klausa WHERE tidak disertakan.
Hasilnya, jika diperiksa :
4.23
UPDATE dengan multiple column subquery
Perintah UPDATE bisa menggunakan multiple column subquery. Percobaan 22 : Ubah nilai dari kolom pekerjaan dan nomer department dari pegawai dengan nomer pegawai ‘7698’ sehingga nilainya sama dengan jenis pekerjaan dan nomer department yang dimiliki oleh pegawai bernomer ‘7499’
4.24
UPDATE berdasarkan table yang lain
UPDATE berdasarkan table yang lain artinya perubahan pada sebuah table dimana kondisi perubahannya ditentukan berdasarkan nilai yang terdapat pada table yang lain. Percobaan 23 : Ubah data nomer department yang dimiliki oleh pegawai dengan pekerjaan yang sama dengan pegawai bernomor ‘7788’ , data nomer department tersebut harus diubah menjadi data yang sama dengan nomer department yang dimiliki oleh pegawai bernomor ‘7788’;
4.25
Kesalahan pada UPDATE
Salah satu kesalahan pada perintah UPDATE, misal jika kita berusaha untuk merubah data sedangkan data tersebut terikat pada integrity constraint (merupakan suatu key). Percobaan 24 : Ubah data pegawai dengan nomer department 10 menjadi data dengan nomer department 55.
Terjadi kesalahan dikarenakan nomer department ‘55’ tidak terdapat dalam table pegawai. Query berikut ini baru bisa dijalankan tanpa ada kesalahan :
4.26
Menghapus baris dari table (DELETE)
Baris data yang ada pada table dapat dihapus dengan menggunakan perintah DELETE. Percobaan 25 : ‘DEVELOPMENT’
Percobaan 26 : Januari 1997
Menghapus data pegawai yang mempunyai nama department =
Hapus data semua pegawai yang tanggal mulai bekerjanya setelah tanggal 1
Jika klausa WHERE ditiadakan, maka semua baris dalam table akan dihapus. Percobaan 27 : Menghapus semua baris yang ada pada table pegawai.
4.27
DELETE berdasarkan table yang lain
Subquery dapat digunakan dalam statement DELETE untuk menghapus baris pada suatu table berdasarkan data yang ada di table yang lain. Percobaan 28 : Menghapus data pada table pegawai, untuk pegawai yang bekerja pada department ‘SALES’
4.28
Kesalahan pada DELETE
Jika baris data yang dihapus pada table berkaitan dengan integrity constraint, maka akan terjadi kesalahan. Percobaan 29 : Misal akan dihapus data pada table department yang memiliki nomer department ‘10’. Jika nomer department 10 ini mempunyai data yang berkaitan dengan data yang ada di table pegawai (ada pegawai yang bekerja di department 10), maka akan muncul pesan kesalahan, seperti pada query berikut :
