STRUCTURE QUERY LANGUAGE (SQL) Diterapkan pada : Informix, AS-400 Dbase IV, Access, Visual Basic
Jenis SQL 1.
Interactive langsung dapat dioperasikan
2.
Embedded disisipkan ke dalam sebuah program
Pengelompokan Statement SQL 1.
Data Definition Language ( DDL ) CREATE
DATABASE TABLE INDEX VIEW
DROP
ALTER TABLE
2. Data Manipulation Language ( DML ) INSERT, SELECT, UPDATE, DELETE
3.
Data Access GRANT, REVOKE
4.
Data Integrity RECOVER TABLE
5.
Auxiliary UNLOAD, LOAD, RENAME COLUMN
SQL
Hal : 1 dari 17
Kasus Data Definition Language (DDL) Pembuatan
Sintaks :
Database
CREATE DATABASE nama_db;
Contoh : Buat database dengan nama latihan CREATE DATABASE latihan; Pembuatan
Tabel
Sintaks : CREATE TABLE nama_tabel (nama_kolom1 jenis_kolom1, nama_kolom2 jenis _kolom2, …); Contoh : Struktur database MHS (npm char(8), nama char(25), alamat char(30)) MKUL (kdmk char(5), mtkul char(25), sks smallint) NILAI (npm char(8), kdmk char(5), mid smallint, final smallint) Membuat tabel CREATE TABLE MHS (npm char(8) notnull, nama char(25) notnull, alamat char(30) notnull); CREATE TABLE MKUL (kdmk char(5) notnull, mtkuliah char(25) notnull, sks smallint notnull); CREATE TABLE NILAI (npm char(8) notnull, kdmk char(5) notnull, mid smallint, final smallint);
SQL
Hal : 2 dari 17
Pembuatan
Index
Sintaks : CREATE [UNIQUE] INDEX nama_index ON nama_tabel (nama_kolom); Contoh: 1). Buat index data mahasiswa berdasarkan npm dengan nama MHSIN dimana npm tidak boleh sama CREATE UNIQUE INDEX MHSIN ON MHS(npm); 2).
CREATE INDEX NILAIIN ON NILAI(kdmk);
Pembuatan
View
Sintaks : CREATE VIEW nama_view[(nama_kolom1, …)] AS SELECT statement [WITH CHECK OPTION]; Contoh : 1). Buat view dengan nama MHSVIEW yang berisi semua data mahasiswa CREATE VIEW MHSVIEW AS SELECT * FROM MHS; 2). CREATE VIEW NILVIEW(npm, kdmk, mid) AS SELECT npm, kdmk, mid FROM NILAI;
SQL
Hal : 3 dari 17
Menghapus Database / Tabel / Index / View Sintaks
:
DROPDATABASE nama_db; DROP TABLE nama_tabel; DROP INDEX nama_index; DROP VIEW nama_view;
Contoh : Hapus tabel MHS DROP TABLE MHS; Merubah
Struktur Tabel
Sintaks : ALTER TABLE nama_tabel ADD (nama_kolom jenis_kolom [BEFORE nama_kolom]) MODIFY (nama_kolom jenis_kolom) DROP (nama_kolom jenis_kolom);
Contoh
:
1). Tambahkan kolom jkel dengan panjang 1 char pada tabel MHS ALTER TABLE MHS ADD(jkel char(1)); 2). Ubah panjang kolom mtkuliah menjadi 30 char ALTER TABLE MKUL MODIFY(mtkuliah char(30)); 3). Hapus kolom jkel dari tabel MHS ALTER TABLE MHS DROP(jkel char(1));
SQL
Hal : 4 dari 17
Contoh Data MHS npm 10296832 10296126 31296500 41296525 50096487 21196353
nama
alamat
Nurhayati Astuti Budi Prananingrum Pipit Quraish
Jakarta Jakarta Depok Bogor Bekasi Bogor
MKUL kdmk
mtkuliah
sks
KK021 P. Basis Data KD132 SIM KU122 Pancasila
2 3 2
NILAI npm 10296832 10296126 31296500 41296525 21196353 50095487 10296832
SQL
kdmk KK021 KD132 KK021 KU122 KU122 KD132 KD132
mid 60 70 55 90 75 80 40
final 75 90 40 80 75 0 30
Hal : 5 dari 17
Data Manipulation Language (DML)
INSERT Sintaks : INSERT INTO nama_tabel[(nama_kolom1, …)] VALUES(data1, …); Contoh : 1). Masukkan data mata kuliah Berkas Akses dengan kode KK222 dan besarnya 2 INSERT INTO MKUL VALUES(“KK222”, ”Berkas Akses”, 2); 2). INSERT INTO NILAI(npm,kdmk,mid) VALUES(“32296222”,”KK222”,30);
UPDATE Sintaks : UPDATE nama_tabel SET nama_kolom = ekspresi WHERE kondisi; Contoh : 1).Ubah alamat menjadi “Depok” untuk mahasiswa yang memiliki npm “50096487” UPDATE MHS SET alamat=”Depok” WHERE npm=”50096487”; 2). UPDATE NILAI SET mid=mid+10 WHERE kdmk=”KK021”;
DELETE Sintaks : DELETE FROM nama_tabel WHERE kondisi Contoh : Hapus data nilai mata kuliah “KK021” bagi mahasiswa yang mempunyai npm ”10296832” DELETE FROM NILAI WHERE npm=”10296832” AND kdmk=”KK021”;
SELECT Sintaks
SQL
: SELECT [DISTINCT] nama_kolom FROM nama_tabel [ WHERE kondisi ] [ GROUP BY nama_kolom ] [ HAVING kondisi ] [ ORDER BY nama_kolom [ASC/DESC]] Hal : 6 dari 17
Contoh Satu tabel ( Simple Query ) 1).
Tampilkan semua data mahasiswa SELECT npm,nama,alamat FROM MHS; atau SELECT * FROM MHS; Hasil : npm 10296832 10296126 31296500 41296525 50096487 21196353
SQL
nama Nurhayati Astuti Budi Prananingrum Pipit Quraish
alamat Jakarta Jakarta Depok Bogor Bekasi Bogor
Hal : 7 dari 17
2).
Tampilkan mata kuliah yang sks-nya 2 SELECT mtkuliah FROM MKUL WHERE sks = 2; Hasil : mtkuliah P. Basis Data Pancasila
SELECT * FROM nilai WHERE mid >= 60 OR final > 75;
SELECT npm, kdmk, mid FROM nilai WHERE mid BETWEEN 70 AND 100;
3).
Tampilkan nama mahasiswa yang diawali dengan huruf “P” SELECT nama FROM MHS WHERE nama LIKE “P%”; Hasil : nama Prananingrum Pipit
SELECT nama FROM MHS WHERE nama NOT LIKE “%a%”;
SELECT nama FROM MHS WHERE nama LIKE “_u%”;
4). Tampilkan alamat mahasiswa, dimana alamat yang sama hanya ditampilkan satu kali saja SELECT DISTINCT alamat FROM MHS; Hasil : alamat
SQL
Hal : 8 dari 17
Jakarta
Depok Bogor Bekasi 5). Tampilkan semua data mahasiswa dengan nama terurut dari “Z” ke “A” SELECT * FROM MHS ORDER BY nama DESC; Hasil : npm 21196353 41296525 50096487 10296832 31296500 10296126
SQL
nama Quraish Prananingrum Pipit Nurhayati Budi Astuti
alamat Bogor Bogor Bekasi Jakarta Depok Jakarta
Hal : 9 dari 17
Tampilkan alamat mahasiswa dan jumlah masiswa yang bertempat tinggal di alamat tersebut SELECT alamat, COUNT(*) FROM MHS GROUP BY alamat; Hasil : alamat Jakarta Depok Bogor Bekasi
COUNT(*) 2 1 2 1
Tampilkan alamat dan jumlah mahasiswa yang bertempat tinggal pada alamat yang jumlahnya lebih dari satu SELECT alamat, COUNT(*) FROM MHS GROUP BY alamat HAVING COUNT(*) > 1; Hasil : alamat Jakarta Bogor
SQL
COUNT(*) 2 2
Hal : 10 dari 17
6).
Tampilkan nilai Midtest tertinggi dan terendah untuk mata kuliah “KD132” SELECT MAX(mid), MIN(mid) FROM NILAI WHERE kdmk=”KD132”; Hasil : MAX(mid) 80
MIN(mid) 40
Tampilkan rata-rata dan jumlah nilai final test mata kuliah “KD132”
SELECT AVG(final), SUM(final) FROM NILAI WHERE kdmk= ”KD132”; Hasil : AVG(final) 40
SQL
SUM(final) 120
Hal : 11 dari 17
Lebih Dari Satu Tabel 1).
Tampilkan nama mahasiswa yang mempunyai nilai midtest lebih kecil dari 60 Sub Query Langkah–langkah SELECT FROM SELECT FROM
WHERE WHERE
SELECT FROM WHERE SELECT npm FROM NILAI WHERE mid<60 SELECT nama FROM MHS WHERE SELECT npm FROM NILAI WHERE mid<60 SELECT nama FROM MHS WHERE npm IN (SELECT npm FROM NILAI WHERE mid<60); Join Langkah SELECT nama FROM MHS WHERE npm IN (SELECT npm FROM NILAI WHERE mid<60); SELECT nama FROM MHS, NILAI WHERE MHS.npm = NILAI.npm AND NILAI.mid < 60; Hasil : nama Nurhayati Budi Quraish 2).
a. Sub Query :
SQL
SELECT nama FROM MHS WHERE npm IN (SELECT npm FROM NILAI WHERE kdmk IN (SELECT kdmk FROM MKUL WHERE mtkuliah = “SIM”));
Hal : 12 dari 17
b. JOIN :
3).
SELECT nama FROM MHS, NILAI, MKUL WHERE MKUL.mtkuliah = ”SIM” AND NILAI.kdmk = MKUL.kdmk AND MHS.npm = NILAI.npm;
Tampilkan nama mahasiswa yang tidak mengambil mata kuliah “KK021” SELECT nama FROM MHS WHERE NOT EXISTS (SELECT * FROM NILAI WHERE NILAI.npm=MHS.npm AND kdmk= “KK021” ); Hasil : nama Astuti Prananingrum Pipit Quraish
4).
Tampilkan npm mahasiswa yang bernama Budi atau yang memiliki nilai final test > 75 SELECT npm FROM MHS WHERE nama=“Budi” UNION SELECT npm FROM NILAI WHERE final>75; Hasil : npm 31296500 10296126 41296525
SQL
Hal : 13 dari 17
Data Access
GRANT Sintaks : GRANT hak_akses ON nama_db [WITH GRANT OPTION] [AS GRANTOR]; GRANT hak_akses ON nama_tabel TO nama_pemakai [WITH GRANT OPTION] [AS GRANTOR] Contoh :
Berikan hak akses kepada Avi untuk menampilkan nilai final test GRANT SELECT(final) ON NILAI TO AVI;
REVOKE Sintaks : REVOKE hak_akses ON nama_db FROM nama_pemakai; REVOKE hak_akses ON nama_tabel FROM nama_pemakai; Contoh : Tarik kembali dari Avi hak akses untuk menampilkan nilai final test REVOKE SELECT(final) ON NILAI FROM AVI;
Data Integrity
RECOVER TABLE Sintaks : RECOVER TABLE nama_tabel; Contoh : Kembalikan keadaan data mahasiswa seperti pada saat sebelum terjadi kerusakan RECOVER TABLE MHS;
SQL
Hal : 14 dari 17
Auxiliary
UNLOAD Sintaks : UNLOAD TO “nama_path” [DELIMITER “char_pemisah”] SELECT statement; Contoh : Ubah semua data mahasiswa ke bentuk ASCII dan disimpan ke file teks di directory /home/avi UNLOAD TO “/home/avi/teks” DELIMITER “|” SELECT * FROM MHS;
LOAD Sintaks : LOAD FROM “nama_path” DELIMITER “char_pemisah” INSERT INTO nama_tabel[nama_kolom]; Contoh : Merubah file teks ke tabel MHS_2 di directory /home/avi : LOAD FROM “/home/avi/teks” DELIMITER “|” INSERT INTO MHS_2;
RENAME Sintaks : RENAME COLUMN nama_kolom_lama TO nama_kolom_baru; Contoh : Ganti nama kolom alamat yang ada pada tabel MHS menjadi KOTA RENAME COLUMN MHS alamat TO kota;
SQL
Hal : 15 dari 17
SQL
Hal : 16 dari 17
