STMIK JAKARTA STI&K – 2011
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 ) DATABASE TABLE INDEX VIEW
CREATE
2. 3. 4. 5.
DROP
ALTER TABLE Data Manipulation Language ( DML ) INSERT, SELECT, UPDATE, DELETE Data Access GRANT, REVOKE Data Integrity RECOVER TABLE Auxiliary UNLOAD, LOAD, RENAME COLUMN
Kasus Data Definition Language (DDL) Pembuatan Database Sintaks : CREATE DATABASE nama_db; Contoh
:
Buat database dengan nama latihan
CREATE DATABASE latihan; STMIK JAKARTA STI&K
Page 1
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); 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;
STMIK JAKARTA STI&K
Page 2
Menghapus Database / Tabel / Index / View Sintaks
:
DROPDATABASE DROP TABLE DROP INDEX DROP VIEW
nama_db; nama_tabel; nama_index; 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));
Contoh Data
MHS
npm
10296832 10296126 31296500 41296525 50096487 21196353
STMIK JAKARTA STI&K
nama
Nurhayati Astuti Budi Prananingrum Pipit Quraish
alamat
Jakarta Jakarta Depok Bogor Bekasi Bogor
Page 3
MKUL
kdmk
KK021 KD132 KU122
mtkuliah
sks
P. Basis Data SIM Pancasila
2 3 2
NILAI
npm
10296832 10296126 31296500 41296525 21196353 50095487 10296832
kdmk
mid
KK021 KD132 KK021 KU122 KU122 KD132 KD132
final
60 70 55 90 75 80 40
75 90 40 80 75 0 30
Data Manipulation Language (DML) INSERT Sintaks
:
INSERT INTO nama_tabel[(nama_kolom1, …)] VALUES(data1, …);
Contoh : Masukkan data mata kuliah Berkas Akses dengan kode KK222 dan besarnya 2 INSERT INTO MKUL VALUES(“KK222”, ”Berkas Akses”, 2); INSERT INTO NILAI(npm,kdmk,mid) VALUES(“32296222”,”KK222”,30); UPDATE Sintaks
:
UPDATE nama_tabel SET nama_kolom = ekspresi WHERE kondisi;
Contoh : Ubah alamat menjadi “Depok” untuk mahasiswa yang memiliki npm “50096487” UPDATE MHS SET alamat=”Depok” WHERE npm=”50096487”; STMIK JAKARTA STI&K
Page 4
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 : SELECT [DISTINCT] nama_kolom FROM nama_tabel [ WHERE kondisi ] [ GROUP BY nama_kolom ] [ HAVING kondisi ] [ ORDER BY nama_kolom [ASC/DESC]]
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 2).
nama Nurhayati Astuti Budi Prananingrum Pipit Quraish
alamat Jakarta Jakarta Depok Bogor Bekasi Bogor
Tampilkan mata kuliah yang sks-nya 2 SELECT mtkuliah FROM MKUL WHERE sks = 2; Hasil : mtkuliah P. Basis Data Pancasila
STMIK JAKARTA STI&K
Page 5
SELECT * FROM nilai WHERE mid >= 60 OR final > 75; SELECT npm, kdmk, mid FROM nilai WHERE mid BETWEEN 70 AND 100; Tampilkan nama mahasiswa yang diawali dengan huruf “P” SELECT nama FROM MHS WHERE nama LIKE “P%”;
3).
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 Jakarta Depok Bogor Bekasi
5).
Tampilkan semua data mahasiswa dengan nama terurut dari “Z” ke “A” SELECT * FROM MHS ORDER BY nama DESC;
STMIK JAKARTA STI&K
Page 6
Hasil : npm 21196353 41296525 50096487 10296832 31296500 10296126
nama
alamat
Quraish Prananingrum Pipit Nurhayati Budi Astuti
Bogor Bogor Bekasi Jakarta Depok Jakarta
6. Tampilkan alamat mahasiswa dan jumlah mahasiswa 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
7. 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
COUNT(*) 2 2
8. Tampilkan nilai Midtest tertinggi dan terendah untuk mata kuliah “KD132” SELECT MAX(mid), MIN(mid) FROM NILAI WHERE kdmk=”KD132”; Hasil : MAX(mid) 80
STMIK JAKARTA STI&K
MIN(mid) 40
Page 7
9. Tampilkan rata-rata dan jumlah nilai final test mata kuliah “KD132” SELECT AVG(final), SUM(final) FROM NILAI WHERE kdmk= ”KD132”; Hasil : AVG(final)
SUM(final) 40
120
Lebih Dari Satu Tabel 1).
Tampilkan nama mahasiswa yang mempunyai nilai midtest lebih kecil dari 60 Sub Query Langkah–langkah SELECT SELECT
FROM FROM
WHERE WHERE
SELECT SELECT npm
FROM FROM NILAI
WHERE WHERE mid<60
SELECT nama SELECT npm
FROM MHS FROM NILAI
WHERE WHERE mid<60
SELECT nama (SELECT npm
FROM MHS FROM NILAI
WHERE npm IN WHERE mid<60);
Join Langkah SELECT nama FROM MHS (SELECT npm FROM NILAI
WHERE npm IN WHERE mid<60);
SELECT nama FROM MHS, NILAI WHERE MHS.npm = NILAI.npm AND NILAI.mid < 60;
Hasil : nama Nurhayati Budi Quraish
STMIK JAKARTA STI&K
Page 8
2).
a. Sub Query : SELECT nama FROM MHS WHERE npm IN (SELECT npm FROM NILAI WHERE kdmk IN (SELECT kdmk FROM MKUL WHERE mtkuliah = “SIM”)); b. Join : SELECT nama FROM MHS, NILAI, MKUL WHERE MKUL.mtkuliah = ”SIM” AND NILAI.kdmk = MKUL.kdmk AND MHS.npm = NILAI.npm;
3). 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
STMIK JAKARTA STI&K
Page 9
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; 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;
STMIK JAKARTA STI&K
Page 10
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 :
Contoh
:
RENAME COLUMN nama_kolom_lama TO nama_kolom_baru;
Ganti nama kolom alamat yang ada pada tabel MHS menjadi KOTA RENAME COLUMN MHS alamat TO kota;
STMIK JAKARTA STI&K
Page 11
