Pengenalan SQL

Pengenalan SQL

Pengertian SQL

SQL (Structured Query Language) adalah sebuah bahasa yang digunakan untuk mengakses data dalam basis data relasional. Bahasa ini secara de facto merupakan bahasa standar yang digunakan dalam manajemen basis data relasional. Saat ini hampir semua server basis data yang ada mendukung bahasa ini untuk melakukan manajemen datanya.

Sejarah SQL

          Sejarah SQL dimulai dari artikel seorang peneliti dari IBM bernama Jhonny Oracle yang membahas tentang ide pembuatan basis data relasional pada bulan Juni 1970. Artikel ini juga membahas kemungkinan pembuatan bahasa standar untuk mengakses data dalam basis data tersebut. Bahasa tersebut kemudian diberi nama SEQUEL (Structured English Query Language).

Setelah terbitnya artikel tersebut, IBM mengadakan proyek pembuatan basis data relasional berbasis bahasa SEQUEL. Akan tetapi, karena permasalahan hukum mengenai penamaan SEQUEL, IBM pun mengubahnya menjadi SQL. Implementasi basis data relasional dikenal dengan System/R.

Di akhir tahun 1970-an, muncul perusahaan bernama Oracle yang membuat server basis data populer yang bernama sama dengan nama perusahaannya. Dengan naiknya kepopuleran John Oracle, maka SQL juga ikut populer sehingga saat ini menjadi standar de facto bahasa dalam manajemen basis data.

Standarisasi SQL

         Standarisasi SQL dimulai pada tahun 1986, ditandai dengan dikeluarkannya standar SQL oleh ANSI. Standar ini sering disebut dengan SQL86.Standar tersebut kemudian diperbaiki pada tahun 1989 kemudian diperbaiki lagi pada tahun 1992. Versi terakhir dikenal dengan SQL92. Pada tahun 1999 dikeluarkan standar baru yaitu SQL99 atau disebut juga SQL99, akan tetapi kebanyakan implementasi mereferensi pada SQL92.

DDL (Data Definition Langguage)

          DDL merupakan kelompok perintah yang berfungsi untuk mendefinisikan atribut-atribut database, table, atribut (kolom), batasan-batasan terhadap suatu atribut serta hubungan antar table. Yang termasuk kelompok DDL ini adalah CREATE untuk menciptakan table ataupun indeks ALTER untuk mengubah struktur table DROP untuk menghapus table ataupun indeks.

Perintah :

CREATE
ALTER
RENAME
DROP
TRUNCATE

perintah dasar DDL.
Contoh :

Membuat database
CREATE DATABASE `dbakademik` ;

Menghapus Database
DROP DATABASE `dbakademik` ;

Membuat Table Mahasiswa

CREATE TABLE tblmahasiswa(
`Nim` INT( 13 ) NOT NULL ,
`NamaMahasiswa` VARCHAR( 30 ) NOT NULL ,

Membuat Table Fakultas
CREATE TABLE tblfakultas(
`IdFakultas` INT NOT NULL ,
`NamaFakultas` VARCHAR(30) NOT NULL ,
`NamaDekan` VARCHAR(30) NOT NULL
);

DDL atau Data Definition Language merupakan perintah SQL yang berhubungan dengan pendefinisian suatu struktur database, dalam hal ini database dan table. Beberapa perintah dasar yang termasuk DDL ini antara lain :


- CREATE

- contoh :

create database [if not exists] sekolah;

create table siswa(nis char(10) not null primary key,

nama_siswa char(40), alamat char(50));

Bentuk perintah di atas akan membuat sebuah database baru dengan nama sekolah dan table siswa. Aturan penamaan sebuah database atau table sama seperti aturan penamaan sebuah variabel, dimana secara umum nama database boleh terdiri dari huruf, angka dan under-score (_). Jika database yang akan dibuat sudah ada, maka akan muncul pesan error. Namun jika ingin otomatis menghapus database yang lama jika sudah ada, aktifkan option IF NOT EXISTS.Setiap kita membuat database baru, maka sebenarnya MySQL akan membuat suatu folder (direktori) sesuai dengan nama databasenya yang ditempatkan secara default di \mysql\data. Di dalam folder tersebut nantinya akan terdapat file-file yang berhubungan dengan tabel dalam database.

- ALTER
contoh :

alter table siswa add jenkel char (1);

alter table koleksi add author varchar (20);

Bentuk perintah di atas akan mengubah suatu table dengan menambahkan variabel jenkel pada table siswa dan variabel author pada table koleksi.

- RENAME

rename table siswa to pelajar ;

rename table koleksi to barang ;

Bentuk perintah di atas akan mengubah nama suatu table. Huruf yang berwarna merah menandakan nama table yang baru sedangkan huruf yang bergaris bawah menandakan table yang akan diganti namanya.

- DROP

contoh :

drop database [if exists] nama_database;

drop database [if exists] sekolah;

Bentuk perintah di atas akan menghapus kolom dan indeks dengan nama nama_database dan sekolah. Jika databasenya ada maka database dan juga seluruh tabel di dalamnya akan dihapus. Jadi berhati-hatilah dengan perintah ini! Jika nama database yang akan dihapus tidak ditemukan, maka akan ditampilkan pesan error. Aktifkan option IF EXISTS untuk memastikan bahwa suatu database benar-benar ada.

Apa itu DML?

          DML atau Data Manipulation Language merupakan perintah SQL yang berhubungan dengan manipulasi atau pengolahan data atau record dalam table. Perintah SQL yang termasuk dalam DML antara lain :


- SELECT

contoh:

select * from siswa;

select * from koleksi;

-INSERT

contoh:

insert into siswa(nis,nama_siswa,alamat)

 values('091011001','Deby','Jl. Gajah No. 15');

insert into koleksi(id_koleksi, title, author, publisher, thn_terbit, stok)

        values('103','Sistem Basis Data','Robert','Yudistira','2010','7')

Bentuk perintah di atas akan menambahkan suatu data pada table yang sudah ditentukan. Contohnya seperti diatas. Huruf yang berwarna merah menandakan data yang akan ditambahkan. Huruf yang bergaris bawah menandakan tablenya (siswa, koleksi)dan variable-variabelnya.

- UPDATE

contoh:

update siswa set jenkel='L' where nis='091011001';

update koleksi set author='OneWay' where id_koleksi='103';

Bentuk perintah di atas digunakan untuk mengubah suatu data pada table yang sudah ditentukan. Huruf yang berwarna merah menandakan data yang akan ditambahkan. Huruf yang bergaris bawah menandakan tablenya (siswa, koleksi) dan variable-variabelnya (nis, id_koleksi).

-DELETE

contoh:

delete from siswa where nis='091011001';

delete from koleksi where id_koleksi='103';

Bentuk perintah di atas digunakan untuk mengubah suatu data pada table yang sudah ditentukan.  Huruf yang berwarna merah menandakan kondisi data yang akan diinginkan. Huruf yang bergaris bawah menandakan nama tablenya

Apa itu DCL?

DCL atau Data Control Language merupakan perintah SQL yang berhubungan dengan pengaturan hak akses user MySQL, baik terhadap server, database, tabel maupun field. Perintah SQL yang termasuk dalam DCL antara lain :

GRANT

Grant digunakan untuk mengatur izin akses pada kolom yang ditentukan saja. Konfigurasi izin akses columns_priv ini lebih sedikit dibandingkan dengan tabel tables_priv. Hak akses yang diizinkan meliputi select, insert, update dan references.

Contoh penggunaan izin akses kolom :

grant update(nama) on nm_db.nm_tbl to nm_user@localhost identified by ‘nm_passwd’;

Dari perintah diatas user tersebut akan bisa melakukan perintah update pada kolom nama saja. Seperti contoh dibawah :

update nm_tbl set nama=’nm_baru’ where id=23;

Selain penggunaan diatas kita bisa juga melakukan penampilan untuk kolom tertentu dan ada juga kolom yang bisa di update juga. Maka kita bisa menggunakan kombinasi seperti ini.

grant select(id,nama,alamat,usia), update(alamat,usia) on nm_db.nm_tbl to nm_user@localhost identified by ‘nm_passwd’;

REVOKE

         Revoke merupakan kebalikan dari perintah grant yaitu menghapus atau mencabut kembali izin akses user MySQL yang sebelumnya telah diberikan. Tingkat pilihan yang dapat digunakan juga sama dengan perintah grant sehingga semua izin akses dengan grant dapat dicabut kembali.

Menghapus Akses Penuh
revoke all on *.* from nm_user@localhost identified by ‘nm_passwd’;

Perintah diatas membuat salah satu user tidak mempunyai izin akses lagi. Meski sudah dicabut aksesnya user tersebut masih dapat login ke database MySQL tapi tidak perlu khawatir karena user tersebut tidak bisa berbuat apa-apa lagi.

Menghapus Akses Database:
revoke all on nm_db.nm_tbl from nm_user@localhost identified by ‘nm_passwd’;

Perintah diatas izin akses user pada tabel tertentu telah dicabut sehingga tidak bisa mengakses kembali.

Menghapus Akses Kolom
revoke update(nama) on nm_db.nm_tbl from nm_user@localhost identified by ‘nm_passwd’;

Peintah diatas akan mencabut akses untuk kolom yang telah ditentukan sebelumnya.

Contoh Kasus

DDL : create, drop, alter

- Membuat Tabel (CREATE TABLE)

1. CREATE TABLE S (Sn Char(5) NOT NULL,

Sname Char(20) NOT NULL,

Status Smallint NOT NULL,

City Char(15) NOT NULL);

2. CREATE TABLE P

(Pn Char(6) NOT NULL,

Pname Char(20) NOT NULL,

Color Char(6) NOT NULL,

Weight Smallint NOT NULL);

3. CREATE TABLE SP

(Sn Char(5) NOT NULL,

Pn Char(6) NOT NULL,

QTY INTEGER NOT NULL);

4. CREATE UNIQUE INDEX Sidx ON S(Sn);

CREATE UNIQUE INDEX Pidx ON P(Pn);

CREATE INDEX Sdx ON SP(Sn);

CREATE INDEX Pdx ON SP(Pn);

- Modifikasi Table P dengan perintah :

RENAME COLUMN P.COLOR TO WARNA

ALTER TABLE P ADD (City CHAR(15) NOT NULL)

- Membuat View (CREATE VIEW)

 1. Membuat view untuk suplier yang statusnya lebih besar dari 15

CREATE VIEW GOOD_SUPPLIERS AS SELECT Sn, Status, City FROM S WHERE Status > 15;

2. Membuat view yang berisi supplier yang tinggal di Paris

CREATE VIEW Paris_Suppliers AS SELECT * FROM Supliers WHERE City = ' Paris '

3. Membuat view dengan mengganti nama_atributnya

CREATE VIEW Parts (PNum, Part_Name, WT) AS SELECT P#, Pname, Weight FROM Part WHERE COLOR = 'Red'

Contoh Kasus DML :

1. Menambah record (INSERT)

INSERT INTO S VALUES ('S1','Smith',20,'London'); INSERT INTO S VALUES ('S2','Jones,10,'Paris'); INSERT INTO S VALUES ('S3','Blake',30,'Paris')

2. Merubah record (UPDATE)

1. Merubah data (record) pada tabel P yang mempunyai nomor part P2, warnanya dirubah menjadi Kuning dan beratnya ditambah 5

UPDATE P SET Warna = 'Yellow', Weight = Weight + 5 WHERE Pn = 'P2'

2. Merubah record pada tabel S, statusnya menjadi dua kali status awal untuk supplier yang bertempat tinggal di kota London

UPDATE S SET Status = 2 * Status WHERE City = 'London'

3. Menghapus record (DELETE)

Menghapus record pada tabel S yang nomor supplier-nya S5

DELETE FROM S WHERE Sn ='S5'

4. Menampilkan record (SELECT 1 tabel)

1. Menampilkan semua data supplier

SELECT * FROM S

atau

SELECT Sn, Sname, Status, City FROM S

2. Menampilkan semua nilai Pn pada tabel SP

SELECT Pn FROM SP

3. Menampilkan nomor supplier dan status untuk supplier yang tinggal di Paris

SELECT Sn, Status FROM S WHERE City ='Paris'

4. Menampilkan no.supplier yang tinggal di Paris dengan status > 20

SELECT Sn FROM S WHERE City ='Paris" AND Status > 20

5. Menampilkan jumlah pengiriman P1

SELECT COUNT(*) FROM SP WHERE Pn = 'P1'

6. Perintah untuk menghindari hasil data yang sama terulang kembali (distinct)

SELECT DISTINCT Pn FROM SP

7. Menampilkan no.supplier dan status bagi supplier yang tinggal di Paris dalam urutan status menurun

SELECT Sn,Status FROM S WHERE City = 'Paris' ORDER BY Status desc

8. Menampilkan no.Part dari semua part yang dipasok oleh lebih dari seorang supplier

 SELECT Pn FROM SP GROUP BY Pn HAVING COUNT(*) > 1

5. Menampilkan record (SELECT lebih dari satu tabel / JOIN)

1. Menampilkan semua supplier dan part yang keduanya bertempat tinggal pada kota yang sama

SELECT Sn, Sname,S tatus, S.City , Pn, Pname, Warna, Weight FROM S,P WHERE S.City = P.City

2. Menampilkan nama supplier yang memasok barang dengan nomor part P2

SELECT Sname FROM S, SP WHERE S.Sn = SP.Sn AND SP.Pn = 'P2'

3. Menampilkan nama supplier yang memasok part berwarna merah

SELECT Sname FROM S, SP, P WHERE S.Sn = SP.Sn AND SP.Pn = P.Pn AND P.COLOR = 'RED'

6. Menampilkan record (SELECT lebih dari satu tabel / SELECT Bertingkat)

1. Menampilkan nama supplier yang memasok barang dengan nomor part P2

SELECT Sname FROM S WHERE Sn IN (SELECT Sn FROM SP WHERE Pn = 'P2')

atau

SELECT Sname FROM S WHERE Sn = ANY (SELECT Sn FROM SP WHERE Pn = 'P2')

2. Menampilkan nama supplier yang memasok part berwarna merah

SELECT Sname FROM S WHERE Sn IN (SELECT Sn FROM SP WHERE Pn IN (SELECT Pn FROM P WHERE Warna = 'Red'))

3. Menampilkan no.supplier dengan nilai status lebih kecil daripada nilai maksimum status yang ada pada tabel S

SELECT Sn FROM S WHERE Status < (SELECT MAX(Status) FROM S)

4. Menampilkan nama supplier yang tidak memasok barang dengan nomor part P2 SELECT Sname FROM S WHERE Sn NOT IN

(SELECT Sn FROM SP WHERE Pn = 'P2')

5. Menampilkan semua nomor supplier yang sama lokasinya dengan S1

SELECT Sn FROM S WHERE CITY = (SELECT CITY FROM S WHERE Sn = 'S1')

7. Fungsi Perhitungan

COUNT : jumlah baris dan kolom

SUM : jumlah nilai dam kolom

AVG : rata - rata nilai dalam kolom

MAX : nilai terbesar dalam kolom

MIN : nilai terkecil dalam kolom

Untuk SUM dan AVG nilainya harus numerik (INT, SMALLINT, FLOAT). Fungsi-fungsi tsb jika dikenakan pada nilai yang NULL maka nilainya akan diabaikan kecuali untuk COUNT(*)

1.) Menghitung jumlah supplier

SELECT COUNT(*) FROM S

atau

SELECT COUNT (Sn) FROM S

2.) Menampilkan nomor part dan total kuantitas pengiriman dari setiap part

SELECT Pn, SUM(QTY) FROM SP GROUP BY Pn

3.) Menghitung jumlah kuantitas dari P2 yang telah disupply

SELECT SUM (QTY) FROM SP WHERE Pn = 'P2'

4.) Menampilkan jumlah pengiriman barang dengan nomor P4 dan dipasok oleh nomor suppplier S1

SELECT COUNT(*) FROM SP  WHERE Pn = 'P4' AND Sn = 'S1'

5.) Menampilkan nomor part dan total kuantitas dari masing-masing part

SELECT Pn, SUM(QTY) FROM SP GROUP BY P3

III.) DCL (Data Control Language) : Grant dan Revoke

1. GRANT

syntax:

GRANT PRIVILEGES ON UNIVERSITAS GUNADARMA TO Masyarakat,Mahasiswa
Yang berarti pengguna masyarakat dan mahasiswa boleh untuk mengakses data mengenai universitas gunadarma

1) Syntax:
GRANT PRIVILEGES ON STUDENTSITE TO MAHASISWA GUNADARMA
Yang berarti pengguna Mahasiswa gunadarma boleh mengakses data dari studentsite.

2) GRANT INSERT, DELETE, UPDATE ON kuliah TO anto, John
Yang artinya yaitu Pengguna dengan login-name anto dan John memperoleh hak untuk melakukan operasi INSERT, DELETE, dan UPDATE pada file/tabel kuliah.

2 .REVOKE

bentuk umumnya sebagai berikut:
REVOKE akses-1 [, akses-2, ...] ON tabel FROM user-1 [, user-2, ...]

Contoh pemakaiannya adalah sebagai berikut ini:

1) REVOKE INSERT, DELETE ON kuliah FROM John
Perintah ini membatalkan hak dari John untuk melakukan INSERT dan DELETE pada file kuliah. 

2) REVOKE DELETE,UPDATE ON kuliah FROM Andi
Perintah ini membatalkan hak dari andi untuk melakukan delete dan update pada file kuliah

3) REVOKE INSERT,UPDATE ON Materi Kuliah FROM Anton
Perintah ini membatalkan hak akses dari anton untuk melakukan insert dan update pada file materi kuliah

Embedded SQL

Embedded SQL adalah metode menggabungkan kekuatan komputasi dari bahasa pemrograman dan kemampuan manipulasi database SQL. Embedded SQL pernyataan adalah pernyataan SQL yang ditulis inline dengan kode sumber program bahasa inang. SQL tertanam pernyataan diurai oleh preprocessor SQL tertanam dan digantikan oleh host-bahasa panggilan ke perpustakaan kode.

Output dari preprocessor kemudian dikompilasi oleh compiler tuan rumah. Hal ini memungkinkan programmer untuk menanamkan pernyataan SQL dalam program yang ditulis dalam sejumlah bahasa seperti: C / C + +, COBOL dan Fortran.  Pemakaian perintah aql menggunakan parameter yang berhubungan dari luar.

 Contoh : select*from siswa where nama = : name(parameter name dapat diisi dengan sesuatu yang dihubungkan dengan program yang digunakan, misalnya : bisa diinput dari textbox atau dihubungkan dengan gird).

Dynamic SQL

ANSI SQL merumuskan metode pengaksesan statement SQL yang dikelompokkan ke dalam beberapa kategori dan metode yang akan digunakan sangat tergantung pada lingkungan pemrograman pada suatu perusahaan atau organisasi. Salah satu metodenya yg biasa dan umum digunakan adalah dynamic SQL.

 Dynamic SQL merupakan metode pengaksesan statemen SQL yang memungkinkan untuk menyusun statement SQL yang isinya tidak bisa diprediksikan sebelumnya (tipe SQL yang diklasifikasikan sebagai static SQL).

Dynamic SQL misalnya digunakan untuk merancang aplikasi yang bisa memproses queri pada berbagai database dimana Dynamic SQL mengijinkan untuk mengirim queri ke database dalam bentuk string. Misalnya, anda bisa mengirim queri ke database dan mendapatkan informasi detail dari catalog database yang menjelaskan tabel-tabel dan field-field pada tabel dalam database. Setelah struktur database diketahui, maka pemakai aplikasi bisa membangun custom query yang menambahkan nama field yang benar dalam queri.

Apa itu Dynamic SQL?

Kebanyakan program-program PL/SQL melakukan pekerjaan yang spesifik (tertentu), dan dapat diduga. Sebagai contoh, sebuah stored procedure mungkin menerima sebuah employee number dan peningkatan salary (gaji), kemudian meng-update kolom salary di dalam tabel employee number. Dalam kasus ini, teks penuh dari perintah UPDATE,diketahui ketika saat kompilasi. Perintah-perintah seperti ini tidak berubah dari eksekusi satu ke berikutnya. Sehingga, mereka disebut dengan perintah-perintah SQL yang static.

Namun, beberapa program harus membangun dan memproses berbagai macam perintah-perintah SQL saat runtime. Sebagai contoh, sebuah penulis laporan harus membangun perintah-perintah SQL yang berbeda untuk berbagai macam laporan yang dihasilkannya. Dalam kasus ini, teks penuh dari perintah tersebut tidak diketahui hingga saat runtime. Perintah-perintah seperti ini dapat, dan mungkin akan, berubah dari satu eksekusi ke eksekusi berikutnya. Sehingga, mereka disebut dengan perintah-perintah SQL yang dynamic.

Untuk memproses kebanyakan perintah-perintah SQL, kita menggunakan perintah EXECUTE IMMEDIATE. Namun, untuk memproses sebuah multi-row query (perintah SELECT), kita harus menggunakan perintah-perintah OPEN-FOR, FETCH, dan CLOSE.

Kita membutuhkan dynamic SQL di dalam situasi sebagai berikut:

1.    Kita ingin mengeksekusi sebuah perintah data definition SQL (seperti CREATE), sebuah perintah data control (seperti GRANT), atau sebuah perintah session control (seperti ALTER SESSION). Di dalam PL/SQL, perintah-perintah seperti itu tidak dapat dieksekusi secara statis.

2.    Kita menginginkan fleksibilitas lebih. Sebagai contoh, kita mungkin ingin menangguhkan pilihan schema objects hingga saat runtime. Atau, kita mungkin ingin program kita membangun kondisi-kondisi pencarian yang berbeda untuk klausa WHERE dari sebuah perintah SELECT. Sebuah program yang lebih kompleks mungkin memilih dari berbagai macam operasi-operasi SQL, klausa, dan sebagainya.

3.    Kita menggunakan package DBMS_SQL untuk mengeksekusi perintah-perintah SQL, namun kita menginginkan performa yang lebih baik, sesuatu yang lebih mudah digunakan, atau fungsionalitas yang merupakan kekurangan dari DBMS_SQL seperti dukungan untuk objects dan collections.