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
.. .. .. );
-1-
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
-2-
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)
-3-
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;
-4-
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 :
-5-
Tabel mhs
NIM NAMA ALAMAT KOTA TGL_LAHIR JK
Tabel matakuliah
KODE NAMA_MK SKS SEMESTER
Tabel mhskuliah
NIM KODE NILAI TAHUN
? 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 [Link],[Link]
2 from mhs,mhskuliah,matakuliah
3 where nilai>75 and
4 [Link]=[Link] and
5 [Link]=[Link];
Catatan
untuk menjamin hasil join selalu benar, maka semua keterkaitan antar key
(foreign key) harus selalu disertakan setelah klausa where.
[Link] berarti field nama diambil dari tabel mhs
MENGGUNAKAN ALIAS
Alias digunakan untuk menyederhanakan penulisan, agar tidak terlalu panjang.
Sebagai contoh query berikut :
SQL> select [Link],[Link]
2 from mhs,mhskuliah,matakuliah
3 where nilai>75 and
4 [Link]=[Link] and
5 [Link]=[Link];
dapat ditulis sebagai :
SQL> select [Link], [Link]
2 from mhs m, matakuliah mk, mhskuliah mhk
3 where nilai>75 and
4 [Link]=[Link] and
5 [Link]=[Link];
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
-6-
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 [Link],[Link],[Link]
3 from mhs m, mhskuliah mhk, matakuliah mk
4 where [Link]=[Link] and
5 [Link]=[Link] and
6 [Link]< (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;
-7-
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 :
Tidak dapat memasukan nilai record baru jika mengabaikan nilai NOT
NULL pada basis table dari VIEW tersebut.
Tidak dapat melakukan INSERT atau UPDATE jika salah satu field dalam
VIEW merupakan hasil kalkulasi atau fungsi
Tidak dapat melakukan INSERT, UPDATE atau DELETE jika dalam VIEW
terdapat GROUP BY atau DISTINCT.
Contoh-Contoh :
1. Hitung banyaknya matakuliah yang sedang diambil oleh mahasiswa
2. Tampilkan nilai rata-rata dari seluruh mahasiswa
3. Tampilkan nim dan nilai dari mahasiswa yang nilainya dibawah rat-rata
4. Hitung banyaknya mahasiswa yang mengikuti matakuliah tertentu
5. Tampilkan dalam semester 3 dan 4 terdapat berapa mata kuliah
6. Tampilkan nim, nama, dan nama mata kuliah yang sedang diambil oleh
mahasiswa
7. Tampilkan nim, nama, nama matakuliah dan nilai dari mahasiswa yang
nilainya kurang 80
8. Tampilkan nim dan nilai rata-ratanya untuk tiap mahasiswa
-8-
9. Tampilkan nim, nama, dan nilai dari mahasiwa yang nilainya dibawah rata-
rata diurutkan naik berdasarkan nama
Tabel–tabel yang digunakan untuk contoh :
Tabel Pegawai
NIP NAMA ALAMAT KOTA GAJI
------ --------------- --------------- --------------- ---------
112230 Burhanudin [Link] 34 Surabaya 750000
112235 Kristina [Link] 22 Surabaya 600000
112237 Ninis [Link] 76 Ponorogo 800000
112220 Anton Subagyo [Link] 100 Jakarta 1000000
112215 Rina P [Link] 12 Jember 500000
112240 Ali Mustofa Nganjuk
Tabel Mhs
NIM NAMA ALAMAT KOTA TGL_LAHIR JK
------ ---------------- -------------------- ---------- ---------- ----
110001 Rudi S [Link] 34 Surabaya 10-05-1986 L
110002 Joko kuncoro [Link] 20 Surabaya 03-02-1989 L
110004 Titin W [Link] 34 Ponorogo 01-11-1987 P
110009 Ali Kusuma [Link] 134 Bandung 10-05-1983 L
110007 Anita Sari [Link] 22 Bogor 30-12-1987 P
110003 Bela Saphira [Link] 22 Bandung 17-08-1983 P
Tabel Matakuliah
KODE NAMA_MK SKS SEMESTER
------ -------------------- --------- ----------
IF1211 Pemrograman 3 4
IF1213 Basis Data 3 4
MA1201 Kalkulus 2 2
MA1204 Aljabar 2 3
IF1221 Oracle 3 4
MU1233 Bahasa Inggris 2 1
Tabel MhsKuliah
NIM KODE NILAI TAHUN
------ ------ --------- -----
110002 IF1211 73 2001
110002 MA1201 56 2001
110002 IF1221 82 2002
110009 IF1213 75 1999
110003 MU1233 80 2000
110007 MA1204 73 2001
110007 IF1221 81 2000
110004 MA1201 91 2002
-9-