Modul 4: Manipulasi dan Retrieve Data bag. 2

Dasar Teori
  
a. Memberikan nama lain pada kolom : SELECT namakolomlama AS namakolombaru FROM namatabel;

Berikut ini perintah untuk memberikan nama lain pada kolom jenis menjadi jenis_film pada tabel jenisfilm : SELECT JENIS AS TYPE FROM JENISFILM;


b. Menggunakan alias untuk nama tabel : SELECT namalias.jenis, namalias.harga FROM namatabel namalias;

Berikut ini perintah untuk memberikan alias pada tabel jenisfilm : SELECT J.JENIS, J.HARGA FROM JENISFILM J;

c. Menampilkan data lebih dari dua tabel : SELECT * FROM namatabel1, namatabel2, namatabel-n;


Berikut ini perintah untuk memnampilkan tabel jenisfilm dan film: SELECT * FROM jenisfilm, film;
 
d. Nested Queries / Subquery (IN, NOT IN, EXISTS, NOT EXISTS)Subquery berarti query di dalam query. Dengan menggunakan subquery, hasil dari query akan menjadi bagian dari query di atasnya. Subquery terletak di dalam klausa WHERE atau HAVING. Pada klausa WHERE, subquery digunakan untuk memilih baris-baris tertentu yang kemudian digunakan oleh query. Sedangkan pada klausa HAVING, subquery digunakan untuk memilih kelompok baris yang kemudian digunakan oleh query.

Contoh 1 : perintah untuk menampilkan data pada tabel jenisfilm yang mana data pada kolom jenis-nya tercantum pada tabel film menggunakan IN : SELECT * FROM JENISFILM WHERE JENIS IN (SELECT JENIS FROM FILM);
atau menggunakan EXISTS : SELECT * FROM JENISFILM WHERE EXISTS (SELECT * FROM FILM WHERE HARGA > 2000);

Pada contoh di atas : SELECT JENIS FROM FILM disebut subquery, sedangkan : SELECT * FROM JENISFILM berkedudukan sebagai query. Perhatikan, terdapat data jenis dan harga pada tabel jenisfilm yang tidak ditampilkan. Hal ini disebabkan data pada kolom jenis tidak terdapat pada kolom jenis di tabel film.

Contoh 2 : perintah untuk menampilkan data pada tabel jenisfilm yang mana data pada kolom jenis-nya tidak tercantum pada tabel film menggunakan NOT IN : SELECT * FROM JENISFILM WHERE JENIS NOT IN (SELECT JENIS FROM FILM);

atau menggunakan NOT EXISTS : SELECT * FROM JENISFILM WHERE NOT EXISTS (SELECT * FROM FILM WHERE HARGA > 2000);

e. Operator comparison ANY dan ALLOperator ANY digunakan berkaitan dengan subquery. Operator ini menghasilkan TRUE (benar) jika paling tidak salah satu perbandingan dengan hasil subquery menghasilkan nilai TRUE. Ilustrasinya jika:

Gaji > ANY (S)

Jika subquery S menghasilkan G1, G2, ..., Gn, maka kondisi di atas identik dengan :

(gaji > G1) OR (gaji > G2) OR ... OR (gaji > Gn)
Contoh : perintah untuk menampilkan semua data jenisfilm yang harganya bukan yang terkecil : SELECT * FROM JENISFILM WHERE HARGA > ANY (SELECT HARGA FROM JENISFILM);

Operator ALL digunakan untuk melakukan perbandingan dengan subquery. Kondisi dengan ALL menghasilkan nilai TRUE (benar) jika subquery tidak menghasilkan apapun atau jika perbandingan menghasilkan TRUE untuk setiap nilai query terhadap hasil subquery.

Contoh : perintah untuk menampilkan data jenisfilm yang harganya paling tinggi : SELECT * FROM JENISFILM WHERE HARGA >= ALL (SELECT HARGA FROM JENISFILM);

f. Sintak ORDER BY
Klausa ORDER BY digunakan untuk mengurutkan data berdasarkan kolom tertentu sesuai dengan tipe data yang dimiliki. 
Contoh: 

Perintah untuk mengurutkan data film berdasarkan kolom judul: SELECT * FROM FILM ORDER BY JUDUL; 
atau tambahkan ASC untuk pengurutan secara ascending (menaik) :
SELECT * FROM FILM ORDER BY JUDUL ASC;
atau tambahkan DESC untuk pengurutan secara descending (menurun):
SELECT * FROM FILM ORDER BY JUDUL DESC;

g. Sintak DISTINCT
Distinct adalah kata kunci ini untuk menghilangan duplikasi. Sebagai Contoh, buat sebuah tabel pelanggan yang berisi nama dan kota asal dengan beberapa record isi dan beberapa kota asal yang sama. Kemudian ketikkan perintah berikut : 
SELECT DISTINCT KOTA FROM PELANGGAN;

Dengan perintah di atas maka nama kota yang sama hanya akan ditampilkan satu saja. 

h. UNION, INTERSECT dan EXCEPT UNION 
Merupakan operator yang digunakan untuk menggabungkan hasil query, dengan ketentuan jumlah, nama dan tipe kolom dari masing-masing tabel yang akan ditampilkan datanya harus sama. Berikut ini perintah untuk memperoleh data pada tabel film dimana jenisnya action dan horor :
SELECT JENIS, JUDUL FROM FILM WHERE JENIS = 'ACTION' UNION SELECT JENIS, JUDUL FROM FILM WHERE JENIS = 'HOROR';

Perintah di atas identik dengan :
SELECT JENIS, JUDUL FROM FILM WHERE JENIS = 'ACTION' OR JENIS ='HOROR';

Namun tidak semua penggabungan dapat dilakukan dengan OR, yaitu jika bekerja pada dua tabel atau lebih.

INTERSECT merupakan operator yang digunakan untuk memperoleh data dari dua buah query dimana data yang ditampilkan adalah yang memenuhi kedua query tersebut dengan ketentuan jumlah, nama dan tipe kolom dari masing-masing tabel yang akan ditampilkan datanya harus sama.
SELECT * FROM namatabel1 INTERSECT SELECT * FROM namatabel2 ;

Pada MySQL tidak terdapat operator INTERSECT namun sebagai gantinya dapat menggunakan operator IN seperti contoh 1 pada bagian Nested Queries.

EXCEPT / Set Difference merupakan operator yang digunakan untuk memperoleh data dari dua buah query dimana data yang ditampilkan adalah data yang ada pada hasil query 1 dan tidak terdapat pada data dari hasil query 2 dengan ketentuan jumlah, nama dan tipe kolom dari masing-masing tabel yang akan ditampilkan datanya harus sama.
SELECT * FROM namatabel1 EXCEPT SELECT * FROM namatabel2;
 
Pada MySQL tidak terdapat operator EXCEPT namun sebagai gantinya dapat menggunakan operator NOT IN seperti contoh 2 pada bagian Nested Queries.
 

PRAKTIKUM

1. Buat tabel pegawai sebagai berikut: 

 

JAWAB:

 
2. Isi data tabel (data dapat dilihat pada halaman terakhir)!
JAWAB:

3. Tampilkan semua kolom di tabel!
JAWAB:

 
4. Tampilkan kolom idpegawai, namabelakang dan gaji saja!
JAWAB:

 
5. Tampilkan kolom idpegawai, namabelakang, gaji, tunjangan dan sebuah kolom baru yaitu tunjangan+gaji yang berisi jumlah tunjangan dan gaji !
JAWAB:

 
6. Ubah tunjangan menjadi NULL untuk pegawai dengan idpegawai = E003. Kemudian lakukan kembali percobaan 5.

7. Seperti percobaan 5, tampilkan kolom idpegawai, namabelakang, gaji, tunjangan dan sebuah kolom baru (gunakan alias) yaitu total_pendapatan yang berisi jumlah tunjangan dan gaji!

8. Tambahkan record baru dengan value: E006,lincoln, burrows, linc@yahoo.com, 085275384544, 2008-09-01, L0006, 1750000, NULL, ex, coml.

9. Untuk pegawai yang ber-id E004 dan E005 ubah idmanajernya menjadi al!

10. Sekarang tampilkan kolom idmanajer saja!

 

11. Dari percobaan 10, terdapat 3 idmanajer yang sama dengan total record 6, sekarang tampilkan idmanajer tanpa duplikasi idmanajer sehingga akan tampil 4 record dengan idmanajer yang berbeda!

12. Tampilkan pegawai yang gajinya antara 1750000 - 1250000!
 
 
13. Tampilkan tabel pegawai yang terurut berdasarkan namabelakang (dari a ke z)!

14. Tampilkan tabel pegawai yang diurutkan berdasarkan nama depan dengan urutan terbalik (dari z ke a)!

Manipulasi dan Retrieve Data (Bagian 1)

Dasar Teori
DML (Data Manipulation Language)adalah kelompok perintah yang berfungsi untuk memanipulasi data dalam basis data, misalnya untuk pengambilan, penyisipan, pengubahan dan penghapusan data. Perintah yang termasuk dalah kategori DML adalah :
INSERT, DELETE, UPDATE dan SELECT.

1. INSERT
Perintah INSERT digunakan untuk menambahkan baris pada suatu tabel. Terdapat dua cara untuk menambah baris, yaitu: 

Cara 1: 
Menambah baris dengan mengisi data pada setiap kolom
INSERT INTO namatabel VALUES (nilai1,nilai2,nilai-n); 
Cara 2 : Menambah baris dengan hanya mengisi data pada kolom tertentu
INSERT INTO namatabel (kolom1,kolom2,kolom-n) VALUES (nilai1,nilai2,nilai-n);

Keterangan:
Jika data bertipe string, date atau time (contoh : action, horor, 2007-11-10) maka pemberian nilainya diapit dengan tanda petik tunggal ('horor') atau petik ganda ("horor"). Jika data bertipe numerik (2500, 400) maka pemberian nilainya tidak diapit tanda petik tunggal maupun ganda. 

2. DELETE
Perintah DELETE digunakan untuk menghapus satu baris, baris dengan kondisi tertentu atau seluruh baris. Syntax :
DELETE FROM namatabel [WHERE kondisi];
Perintah dalam tanda [] bersifat opsional untuk menghapus suatu baris dengan suatu kondisi tertentu.

3. UPDATE
Perintah UPDATE digunakan untuk mengubah isi data pada satu atau beberapa kolom pada suatu tabel. Syntax :
UPDATE namatabel SET kolom1 = nilai1, kolom2 = nilai2 [WHERE kondisi];
Perintah dalam tanda [] bersifat opsional untuk mengubah suatu baris dengan suatu kondisi tertentu.

4. SELECT
Perintah SELECT digunakan untuk menampilkan isi dari suatu tabel yang dapat dihubungkan dengan tabel yang lainnya.

a. Menampilkan data untuk semua kolom menggunakan asterisk (*):
SELECT * FROM namatabel;

b. Menampilkan data untuk kolom tertentu:
SELECT kolom1,kolom2,kolom-n FROM namatabel;

c. Menampilkan data dengan kondisi data tertentu dengan klausa WHERE:
SELECT * FROM namatabel WHERE kondisi;

Beberapa operator perbandingan yang dapat digunakan pada klausa WHERE adalah "="(sama dengan) , > (lebih dari), < (kurang dari), < > (tidak sama dengan), >= (lebih dari atau sama dengan), <= (kurang dari atau sama).


Praktik
1. Buat sebuah database dengan nama coba
Create database coba;

 

2. Buat sebuah tabel dengan nama pet di dalam database coba.
Create table pet(name varchar(20) not null, owner varchar(20), species varchar(20), sex char(1), birth date, death date);

3. Isi data ke dalam tabel pet seperti berikut:

Untuk memasukan data seperti diatas, bisa menggunakan perintah seperti berikut:

4. Tampilkan semua isi/record tabel pet!
select * from pet;

5. Ubah data tanggal lahir hewan yang bernama Bowser menjadi: 1979-08-31!
Upadate pet set birth='1979-08-31' where name='Bowser';

6. Tampilkan satu baris / record data yang telah diubah tadi yaitu record dengan nama Bowser saja!
select * from pet where name='Bowser';

 
7. Hapus hewan yang bernama Chirpy!
Delete from pet where name='Chirpy';

8. Tampilkan record atau data yang tanggal kelahirannya lebih dari atau sama dengan 1998-1-1 !
Select * from pet where birth>='1998-01-01';

9. Tampilkan semua hewan dengan spesis anjing dan anjing tersebut berjenis kelamin betina!
Select * from pet where species='dog' && sex='f';

10. Dengan satu perintah select, tampilkan semua hewan dengan spesis ular atau spesis burung(dalam satu tabel ada hewan spesies ular dan burung saja)!
Select * from pet where species='Snake' || species='Bird';

11. Dengan satu perintah select, tampilkan semua hewan yang berspesis kucing dengan kelamin lakilaki atau hewan yang berspesis anjing dengan kelamin betina (tampilkan dalam satu tabel)!
Select * from pet where species='cat' && sex='M' || species='Dog' && sex='F';

Tugas Praktikum 2

1. Tulis semua perintah-perintah SQL percobaan diatas beserta outputnya?
2. Apa Kegunaan Index di Tabel?
3. Apa maksud dari int(11)?
4. Ketika kita melihat struktur tabel dengan perintah desc, ada kolom Null yang berisi Yes dan No. Apa maksudnya?


JAWAB

1). a. Syntax Membuat Database : CREATE DATABASE namadatabase;

Namadatabase tidak boleh mengandung spasi dan tidak boleh memiliki nama yang sama antar database. Berikut ini perintah untuk membuat database dengan nama testing:

CREATE DATABASE testing;

b. Memilih Database : USE namadatabase;

Sebelum membuat suatu tabel, terlebih dahulu harus memilih salah satu database sebagai database aktif yang akan digunakan untuk menyimpan tabel-tabel, Berikut ini perintah untuk menggunakan database dengan nama testing:

USE testing;

c. Syntax Menghapus Database : DROP DATABASE namadatabase;

Database yang akan dihapus sesuai dengan namadatabase. Berikut ini perintah untuk menghapus database dengan nama testing :

DROP DATABASE testing;

 

d. Membuat Tabel : CREATE TABLE namatabel2 ( Field1 TipeData1,Field2 TipeData2);

Namatabel tidak boleh mengandung spasi (space). Field1 dan TipeData1 merupakan ama kolom pertama dan tipe data untuk kolom pertama. Jika ingin membuat tabel dengan kolom lebih dari satu, maka setelah pendefinisian tipe data sebelumnya diberikan tanda koma (,). Berikut ini perintah untuk membuat tabel dengan nama mahasiswa:

CREATE TABLE mahasiswa (NIM CHAR(12), nama VARCHAR(50));

e. Menampilkan Tabel

Untuk menampilkan daftar nama tabel yang ada pada database yang sedang aktif/digunakan (dalam hal ini database testing) :

SHOW TABLES;

f. Menampilkan Atribut Tabel : DESC namatabel;

Untuk menampilkan deskripsi tabel (dalam hal ini mahasiswa) syntaxnya adalah :

DESC mahasiswa;

 

g. Syntax Menghapus Tabel : DROP TABLE namatabel;

Tabel yang akan dihapus sesuai dengan namatabel, berikut ini perintah untuk menghapus tabel dengan nama mahasiswa :

DROP TABLE mahasiswa;

h. Mendefinisikan Null/Not Null : CREATE TABLE namatabel ( Field1 TipeData1 NOT NULL, Field2 TipeData2);

contoh:

CREATE TABLE mahasiswa (NIM CHAR(12) NOT NULL, nama VARCHAR(50));

i. Mendefinisikan Nilai Default : CREATE TABLE namatabel (Field1 TipeData1 DEFAULT nilaidefault,Field2 TipeData2 DEFAULT nilaidefault);

Nilai default adalah nilai yang otomatis diberikan oleh sistem untuk suatu kolom ketika ada penambahan baris baru, sementara nilai pada kolom tersebut tidak diisi oleh pengguna. Pada kasus ini kelas :

CREATE TABLE kelas( A12 char(3) DEFAULT ‘IPA’);

j. Mendefinisikan Primary Key Pada Tabel

Terdapat tiga cara untuk mendefinisikan primary key. Berikut ini adalah Syntax mendefinisikan primary key untuk Field1

CREATE TABLE namatabel(Field1 TipeData1 NOT NULL PRIMARY KEY, Field2 TipeData2);

CREATE TABLE mahasiswa (NIM CHAR(12) NOT NULL PRIMARY KEY, nama VARCHAR(50));

Atau

CREATE TABLE namatabel ( Field1 TipeData1, Field2 TipeData2, PRIMARY KEY(Field1));

CREATE TABLE mahasiswa (NIM CHAR(12), nama VARCHAR(50), PRIMARY KEY(NIM));

Atau

ALTER TABLE namatabel ADD CONSTRAINT namaconstraint (namakolom);

ALTER TABLE mahasiswa ADD CONSTRAINT PRIMARY KEY (NIM);

  

k. Menghapus Primary Key Pada Tabel

Cara 1 : Jika primary key dibuat dengan menggunakan alter table :

ALTER TABLE namatabel DROP CONSTRAINT namaconstraint;

Cara 2 : Jika primary key dibuat melalui create table :

ALTER TABLE namatabel DROP PRIMARY KEY;

ALTER TABLE mahasiswa DROP PRIMARY KEY;

 

l. Menambah Kolom Baru Pada Tabel : ALTER TABLE namatabel ADD fieldbaru tipe;

Namatabel adalah nama tabel yang akan ditambah fieldnya. Fieldbaru adalah nama kolom yang akan ditambahkan, tipe adalah tipe data dari kolom yang akan ditambahkan. Berikut ini perintah untuk menambah kolom kelas dengan tipe data char(3) :

ALTER TABLE mahasiswa ADD kelas CHAR(3);

Untuk meletakkan field diawal, tambahkan sintaks first :

ALTER TABLE mahasiswa ADD COLUMN umur CHAR(3) FIRST;

Untuk menyisipkan field setelah field tertentu, tambahkan sintaks after :

ALTER TABLE mahasiswa ADD COLUMN phone VARCHAR(12) AFTER nama;

m. Mengubah Tipe Data atau Lebar Kolom Pada Tabel : ALTER TABLE NAMATABEL MODIFY COLUMN FIELD TIPE

Namatabel adalah nama tabel yang akan diubah tipe data atau lebar kolomnya. Field adalah kolom yang akan diubah tipe data atau lebarnya. Tipe adalah tipe data baru atau tipe data

lama dengan lebar kolom yang berbeda. Berikut ini perintah untuk mengubah tipe data untuk kolom phone dengan char(13) :

ALTER TABLE mahasiswa MODIFY COLUMN phone CHAR(13);

n. Mengubah Nama Kolom : ALTER TABLE namatabel CHANGE COLUMN namalamakolom namabarukolom tipedatabaru;

Namatabel adalah nama tabel yang akan diubah nama kolomnya, namalamakolom adalah kolom yang akan diganti namanya, namabarukolom adalah nama baru kolom, tipedatanya adalah tipe data dari kolom tersebut. Berikut ini perintah untuk mengubah nama kolom phone menjadi telpon :

ALTER TABLE mahasiswa CHANGE COLUMN phone telpon CHAR(13);

o. Menghapus Kolom Pada Tabel : ALTER TABLE namatabel DROP COLUMN namakolom;
ALTER TABLE mahasiswa DROP COLUMN umur; 

p. Membuat dan Menghapus Index

Index berfungsi mempercepat proses pencarian data dalam suatu tabel. Adanya index pada suatu field tabel menyebabkan proses pencarian otomatis akan dilakukan terlebih dahulu ke dalam index, apabila ditemukan baru akan diambilkan data sesungguhnya dari tabel, apabila tidak ditemukan dalam index, sudah dipastikan bahwa data tesebut memang tidak ada dalam tabel. Index juga dapat dibuat untuk setiap kolom yang akan dijadikan kriteria tertentu untuk pencarian data sehingga proses pencariannya akan lebih cepat. Ada perintah untuk membuat dan menghapus index, tapi tidak ada perintah untuk merubah index.

Perhatikan contoh berikut :

CREATE INDEX IDXNOMOR ON mahasiswa(NIM);

Atau

ALTER TABLE MAHASISWA ADD INDEX IDXnama(nama);

Untuk menghapus index :

DROP INDEX IDXNOMOR ON mahasiswa;

Atau

ALTER TABLE mahasiswa DROP INDEX IDXnama;

2. Index berfungsi mempercepat proses pencarian data dalam suatu tabel. Adanya index pada suatu field tabel menyebabkan proses pencarian otomatis akan dilakukan terlebih dahulu ke dalam index, apabila ditemukan baru akan diambilkan data sesungguhnya dari tabel, apabila tidak ditemukan dalam index, sudah dipastikan bahwa data tesebut memang tidak ada dalam tabel. 

3. Yang dimaksud int(11) artinya suatu data yang dipakai atau digunakan menggunakan tipe data int atau integer dengan length atau panjang karakter 11.

4. Apabila Null berisi no, maka data tersebut nantinya akan dilakukan pengisian atau penginputan. Sedangkan apabila Null berisi yes, maka artinya data tersebut akan dikosongkan atau tidak dilakukan penginputan.