STRUCTURE QUERY LANGUAGE (SQL)
þ DDL (Data Definition Language)
Yang paling sering dipakai
§ Create
§ Alter
§ Drop
þ Perintah CREATE
User
CREATE USER nama_User identified by Password;
Table
CREATE TABLE nama_tabel
(
nama_kolom tipe_data batasan,
)
Contoh penggunaan instruksi :
SQL> create table pegawai (
2 nip char(4),
3 nama varchar2(15),
4 alamat varchar2(15),
5 kota varchar2(15),
6 gaji number(7));
Index
Index digunakan untuk meningkatkan kinerja dari basis data.
CREATE INDEX nama_index ON nama_tabel (kolom ASC/DESC)
SQL> create index myindex on pegawai (nama);
Index created.
Perintah DROP
◘ DROP Table nama_table;
SQL> drop table pegawai;
◘ DROP User nama_table;
Perintah EDIT TABEL
§ menambah kolom/field
ALTER TABLE NamaTable
ADD (NamaField TipeField Batasan
.. .. .. );
§ mengubah kolom yang sudah ada
ALTER TABLE NamaTable
Modify (NamaField TipeField Batasan
.. .. .. );
þ DATA MANIPULATION LANGUAGE (DML)
Data Manipution Language (DML) digunaan untuk memanipulasi data yang tersimpan dalam database. DML yang terdiri atas :
§ INSERT
Memasukan sebuah record baru ke dalam sebuah table
Syntax :
INSERT INTO NamaTable VALUES [nilai 1, nilai2 ,…]
§ UPDATE
Untuk modifikasi record, dengan syntax sebagai berikut :
Syntax :
UPDATE NamaTable
SET NamaField = NilaiBaru
WHERE Kondisi ;
§ DELETE
Untuk menghapus satu atau beberapa record dalam suatu table.
Syntax :
DELETE FROM NamaTable
WHERE Kondisi ;
Select
Merupakan instruksi yang paling banyak digunakan dalam SQL. Digunakan untuk memilih spesifik kolom dari satu atau beberapa tabel.
Syntax :
SELECT field1, field2, …
FROM NamaTabel
WHERE kondisi;
Where
Seleksi dapat dilakukan tidak pada seluruh record, melainkan hanya pada record yang memenuhi syarat.
Bentuk klausa where adalah :
WHERE field <operator> <nilai> ;
And, Or, dan Not
Untuk menggabungkan lebih dari satu kondisi diperlukan operator AND atau OR. Sedangkan untuk negasi diperlukan operator NOT.
? Tampilkan nama, kota dan gaji bagi pegawai yang berasal dari Surabaya atau Jember dan mempunyai gaji lebih dari 600000.
SQL> select nama,kota,gaji
2 from pegawai
3 where (kota='Surabaya' or kota='Jember') and gaji>600000;
Between … And dan Not Between … And
Digunakan untuk menyederhanakan pencarian “antara” (range).
Contoh :
? Tampilkan nama dan gaji bagi pegawai yang mempunyai gaji antara 600000 sampai dengan 900000
SQL>select nama,gaji
2 from pegawai
3 where gaji between 600000 and 900000;
Like dan Not Like
Digunakan untuk permintaan yang mencari suatu teks berdasarkan kata depan (prefik), kata akhir (sufik) atau kata tengah.
Contoh :
? Tampilkan nama dan kota bagi pegawai yang berasal dari kota Jember
SQL> select nama,kota
2 from pegawai
3 where kota like 'Jember';
Catatan Tanda % mewakili ‘wild character’, yaitu karakter sembarang
? Tampilkan nama dan kota bagi pegawai yang berasal dari kota yang mengandung huruf ‘o’.
SQL> select nama,kota
2 from pegawai
3 where kota like '%o%';
In dan Not In
Digunakan untuk mencari nilai dalam satu kelompok nilai. Nilai dalam daftar terdiri atas satu atau lebih.
Contoh :
? Tampilkan nama dan kota bagi pegawai yang berasal dari kota Surabaya atau Ponorogo.
SQL> select nama,kota
2 from pegawai
3 where kota in ('Surabaya','Ponorogo');
Is Null dan Is Not Null
NULL adalah nilai yang belum diisi (tidak identik dengan spasi / blank)
? Tampilkan nama dan alamat dari pegawai yang mempunyai alamat NULL (not blank)
SQL> select nama,alamat
2 from pegawai
3 where alamat is null;
þ SUSUNAN DATA
Order By
Digunakan untuk mengurutkan (sort) data bedasarkan urutan yang dipilih, asc(naik) atau desc (turun).
Contoh :
? Tampilkan nama pegawai dengan urutan a-z (asc)
SQL> select nama from pegawai
2 order by nama asc;
Distinct
Untuk menghilangkan nilai ganda, sehingga jika ada nilai yang sama, akan ditampilkan hanya satu kali.
SQL> select distinct kota from pegawai;
þ FUNGSI-FUNGSI AGREGASI
AVG
Untuk mencari rata-rata dari seluruh record dengan kualifikasi tertentu (harus bertipe numerik).
Contoh-contoh berikut mengacu pada tabel pegawai yang telah dibuat sebelumnya.
? Tampilkan gaji rata-rata dari seluruh pegawai.
SQL> select avg(gaji) from pegawai;
MAX dan MIN
Untuk mencari nilai maksimum dan minimum dari suatu kelompok nilai (harus bertipe numerik)..
Contoh :
? Tampilkan gaji tertinggi dari seluruh pegawai.
SQL> select max(gaji) from pegawai;
Count
Untuk menghitung jumlah record dengan kualifikasi tertentu.
COUNT(*) menyatakan : Hitung jumlah seluruh record pada suatu tabel yang berlaku.
Contoh :
? Tampilkan banyaknya pegawai
SQL> select count(*) "Jumlah Pegawai"
2 from pegawai;
Jumlah Pegawai
--------------
6
SUM
Untuk menjumlah nilai dari field tertentu (harus bertipe numerik).
Contoh :
? Tampilkan jumlah seluruh gaji pegawai
SQL> select sum(gaji) "total gaji" from pegawai;
þ GROUP BY
Group by menyeleksi himpunan yang dihitung berdasarkan fungsi spesifik, misal AVG, COUNT, atau lainnya.
Untuk memberikan gambaran secara jelas, akan diberikan contoh-contoh query sebagai berikut :
Contoh-contoh :
? Ingin diketahui setiap matakuliah diikuiti oleh berapa mahasiswa (peserta)
SQL> select kode, count(nim)"PESERTA"
2 from mhskuliah
3 group by kode;
? Ingin diketahui nilai rata-rata setiap setiap mahasiswa
SQL> select nim,avg(nilai)"NILAI RATA-RATA"
2 from mhskuliah
3 group by nim;
þ GROUP BY … … HAVING
HAVING berlaku untuk kelompok query GROUP BY, berfungsi sebagaimana WHERE.
Hanya group yang mempunyai kriteria tersebut yang akan diproses, sedangkan pada WHERE yang diproses adalah setiap record dalam tabel.
Contoh-contoh :
? Ingin diketahui NIM mahasiswa yang nilai rata-ratanya diatas 75
SQL> select nim,avg(nilai)"NILAI RATA-RATA"
2 from mhskuliah
3 group by nim
4 having avg(nilai)>75;
þ JOIN
Merupakan suatu teknik untuk mengakses record dari beberapa tabel dan menggabungkan hasilnya.
Hal yang perlu diperhatikan untuk menggunakan JOIN antara lain :
§ Tampilan yang diinginkan seperti apa
§ Melibatkan tabel apa saja
§ Struktur Foreign Key dari tabel asal ke tabel yang direferensinya harus sudah didefinisikan dengan benar
§ Tes hasil join (jumlah record pada hail join harus sesuai)
Dari tiga buah tabel yang telah dibuat, perlu didefinisikan masing primary key serta foreign key nya. Primary key adalah nama field yang diberi garis bawah. Keterkaitan antar key tersebut dapat dilihat pada skema berikut :
Tabel mhs
NIM
| NAMA | ALAMAT | KOTA | TGL_LAHIR | JK |
|
|
|
|
|
|
Tabel matakuliah
KODE | NAMA_MK | SKS | SEMESTER |
|
|
|
|
Tabel mhskuliah
? Tampilkan nama mahasiswa yang nilainya diatas 75.
Analisa : query melibatkan dua tabel, karena nama mahasiswa terdapat pada tabel mhs, sedangkan nilai terdapat pada tabel mhskuliah. Dengan demikian SQL nya adalah :
SQL> select mhs.nama,mhskuliah.nilai
2 from mhs,mhskuliah,matakuliah
3 where nilai>75 and
4 mhskuliah.nim=mhs.nim and
5 mhskuliah.kode=matakuliah.kode;
Catatan
untuk menjamin hasil join selalu benar, maka semua keterkaitan antar key (foreign key) harus selalu disertakan setelah klausa where.
mhs.nama berarti field nama diambil dari tabel mhs
þ MENGGUNAKAN ALIAS
Alias digunakan untuk menyederhanakan penulisan, agar tidak terlalu panjang. Sebagai contoh query berikut :
SQL> select mhs.nama,mhskuliah.nilai
2 from mhs,mhskuliah,matakuliah
3 where nilai>75 and
4 mhskuliah.nim=mhs.nim and
5 mhskuliah.kode=matakuliah.kode;
dapat ditulis sebagai :
SQL> select m.nama, mhk.nilai
2 from mhs m, matakuliah mk, mhskuliah mhk
3 where nilai>75 and
4 mhk.nim=m.nim and
5 mhk.kode=mk.kode;
þ VIEW
View biasa dikenal sebagai virtual tabel, yaitu tabel yang dibangun dari satu atau beberapa tabel yang sudah ada. Secara fisik VIEW tidak membuat penyimpanan data seperti tabel, melainkan hanya menyimpan referensi/pointer ke record pada tabel-tabel yang berkaitan.
Syntax :
CREATE VIEW NamaView (Field1, Field2, … ) AS
SELECT … …
Jadi VIEW dibuat dengan cara menambahkan klausa CREATE VIEW … AS sebelum SELECT
Contoh :
? Buat view dengan nama MyView untuk menampilkan nim, nama, dan nilai dari mahasiwa yang nilainya dibawah rata-rata diurutkan naik berdasarkan nama
SQL> create view MyView as
2 select mhk.nim,m.nama,mhk.nilai
3 from mhs m, mhskuliah mhk, matakuliah mk
4 where mhk.nim=m.nim and
5 mhk.kode=mk.kode and
6 mhk.nilai< (select avg(nilai) from mhskuliah);
Mengubah VIEW
Untuk dapat mengubah VIEW secara langsung tanpa harus menghapus terlebih dahulu, digunakan kata kunci CREATE OR REPLACE VIEW. Dengan instruksi ini, jika terdapat view dengan nama sama, maka akan digantikan oleh view yang terbaru.
Syntax :
create or replace view MyView as
… …
Menghapus VIEW
VIEW dapat di hapus dengan instruksi DROP
SQL> drop view MyView;
Updating Melalui VIEW
Updating dapat dilakukan melalui VIEW sebagaimana updating pada tabel, tetapi perlu diperhatikan “NOT NULL” option dan batasan-batasan lainnya sebelum memasukkan nilai ke dalam VIEW.
Agar konsistensi dalam VIEW dapat dijaga, maka pada saat VIEW dibuat, pada akhir instruksi harus ditambahkan klausa “WITH CHECK OPTION”
Contoh :
SQL> Create or replace view Viewku (Nimnya,Namanya,Kotanya) as
2 Select nim,nama, kota
3 from mhs
4 where kota='Surabaya';
SQL> select * from viewku;
NIMNYA NAMANYA KOTANYA
------ -------------------- --------------------
110001 Rudi S Surabaya
110002 Joko kuncoro Surabaya
setelah VIEW ter-create, dilakukan perintah berikut :
SQL> insert into Viewku values ('112210','Ari Anggara','Bogor');
Akan di cek isi dari view setelah diinsert record tersebut :
SQL> select * from viewku;
NIMNYA NAMANYA KOTANYA
------ -------------------- --------------------
110001 Rudi S Surabaya
110002 Joko kuncoro Surabaya
Ternyata, walaupun insert berhasil dilakukan, karena tidak ada “WITH CHECK OPTION”. Tetapi view yang telah dibuat tidak mampu menghasilkan kondisi record yang paling mutahir (terupdate). Hal ini disebabkan saat pembuatan view, terdapat klausa where kota=‘Surabaya’, sehingga terjadi ketidak konsistenan saat insert where kota=’Bogor’,
Untuk menghindari permasalahan ini, maka saat pembuatan view harus disertai klausa “WITH CHECK OPTION” “with CheckOption” sebagai berkut :
SQL> Create or replace view Viewku (Nimnya,Namanya,Kotanya) as
2 Select nim,nama, kota
3 from mhs
4 where kota='Surabaya'
5 with check option;
Selanjutnya dilakukan insert record pada viewku sebagai berikut :
SQL> insert into Viewku values ('112217','Ramdani','Madiun');
insert into Viewku values ('112217','Ramdani','Madiun')
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
SQL tersebut ditolak, karena terdapat pengecekan batasan.
Melalui VIEW dapat dilakukan operasi DML, dengan beberapa batasan sebagai berikut :
q Tidak dapat memasukan nilai record baru jika mengabaikan nilai NOT NULL pada basis table dari VIEW tersebut.
q Tidak dapat melakukan INSERT atau UPDATE jika salah satu field dalam VIEW merupakan hasil kalkulasi atau fungsi
q Tidak dapat melakukan INSERT, UPDATE atau DELETE jika dalam VIEW terdapat GROUP BY atau DISTINCT.
