Rangkuman SQL

RANGKUMAN

                             SQL (STRUCTURED QUERY LANGUAGE)

            SQL merupakan singkatan dari Structured Query Language. SQL atau juga sering disebut sebagai query merupakan suatu bahasa (language) yang digunakan untuk mengakses database. SQL dikenalkan pertama kali dalam IBM pada tahun 1970 dan sebuah standar ISO dan ANSII ditetapkan untuk SQL. Hampir semua software database mengenal atau mengerti SQL. Jadi, perintah SQL pada semua software database hampir sama.
Terdapat 3 (tiga) jenis perintah SQL, yaitu DDL, DML dan DCL.

D D L (Data Definition Language)

            DDL digunakan untuk mendefinisikan, mengubah, serta menghapus basis data dan objek-objek yang diperlukan dalam basis data, misalnya tabel, view, user, dan sebagainya. Secara umum, DDL yang digunakan adalah create untuk membuat objek baru, use untuk menggunakan objek, alter untuk mengubah objek yang sudah ada, dan drop untuk menghapus objek.

            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.

D M L (Data Manipulation Language)

            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 untuk menampilkan data
• INSERT untuk menambahkan data baru
• UPDATE untuk mengubah data yang sudah ada
• DELETE untuk menghapus data

            Select  adalah perintah yang paling sering digunakan pada SQL, sehingga kadang-kadang istilah query dirujukkan pada perintah select. Select digunakan untuk menampilkan data dari satu atau lebih tabel, biasanya dalam sebuah basis data yang sama. Secara umum, perintah SELECT memiliki bentuk lengkap.

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

D C L (Data Control Language)

            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.

QUERY

Pengertian Query

Query adalah semacam kemampuan untuk menampilkan suatu data dari database dimana mengambil dari table-tabel yang ada di database, namun tabel tersebut tidak semua ditampilkan sesuai dengan yang kita inginkan. data apa yang ingin kita tampilkan.
misal : data peminjam dengan buku yang dipinjam, maka nanti akan mengambil data dari table peminjam dan tabel buku.

1.      Query adalah suatu extracting data dari suatu database dan menampilkannya untuk “pengolahan” lebih lanjut.

2.      Query adalah pertanyaan atau permintaan informasi tertentu dari sebuah basisdata yang ditulis dalam format tertentu.

3.      Query adalah perintah-perintah untuk mengakses data pada sistem basis data

4.      Query adalah merupakan bahasa untuk melakukan manipulasi terhadap database, yang telah distandarkan dan lebih dikenal dengan nama Structured Query Language (SQL)

            Bahasa query (query language) adalah bahasa khusus yang digunakan untuk melakukan query pada basis data. Contoh penggunaan bahasa query adalah: SELECT ALL WHERE kota=”Yogyakarta” AND umur<40. Query tersebut meminta semua record dari basis data yang sedang digunakan (misalkan basisdata konsumen) yang bertempat tinggal di Yogyakarta dan berumur lebih dari 40 tahun (kota dan umur adalah nama field yang telah didefinisikan). Standar bahasa query yang banyak digunakan adalah SQL (structured query language). Metode ini paling rumit tetapi paling fleksibel dibandingkan metode query yang lain, query dengan parameter yang telah tersedia dan query by example.

Query dibedakan menjadi 2, yaitu

1.      Untuk membuat/mendefinisikan obyek-obyek database seperti membuat tabel, relasi dan sebagainya. Biasanya disebut dengan Data Definition Language (DDL)

2.      Untuk memanipulasi data, yang biasanya dikenal dengan Data Manipulation Language (DML). Manipulasi data bisa berupa:

            a. Menambah, mengubah atau menghapus data.

            b. Pengambilan informasi yang diperlukan dari database, yang mana datanya diambil dari tabel                     maupun dari query sebelumnya

SPESIAL OPERATOR

 BETWEEN : digunakan untuk menentukan range nilai

IS NULL :  digunakan untuk mengecek apakah suatu atribut bernilai kosong (NULL)

LIKE : digunakan untuk mengecek apakah suatu atribut mengandung string/ karaker yang mirip

IN : digunakan untuk mengecek apakah suatu nilai suatu atribut terdapat dalam suatu daftar himpunan nilai

EXISTS : digunakan untuk mengecek apakah suatu atribut memiliki nilai.

BETWEEN digunakan untuk menentukan range nilai

Contoh:

SELECT  * FROM  MAHASISWA

    WHERE  IPK  BETWEEN  2.00  AND  3.00;

SELECT  * FROM  MAHASISWA

    WHERE  IPK>=2.00  AND  IPK<= 3.00;

IS NULL digunakan untuk mengecek apakah suatu atribut bernilai kosong (NULL)

Contoh:

SELECT  * FROM  WALI

    WHERE  JBT_FNG  IS NULL;

SELECT  * FROM  MAHASISWA

    WHERE  ALAMAT IS NULL;

LIKE digunakan untuk mengecek apakah suatu atribut mengandung string/ karaker yang mirip

Contoh:

SELECT  * FROM  WALI

    WHERE  NAMA  LIKE  ‘%SANTO%’;

SELECT  * FROM  MAHASISWA

    WHERE  ALAMAT LIKE ‘KOMPLEK%;

IN digunakan untuk mengecek apakah suatu nilai suatu atribut terdapat dalam suatu daftar himpunan nilai

Contoh:

SELECT  * FROM  WALI

    WHERE  JBT_FNG  IN (‘ASISTEN AHLI’, ‘LEKTOR’);

SELECT  * FROM  MAHASISWA

    WHERE IPK  IN (2.00, 2.50, 3.00, 3.50);

EXIST digunakan untuk mengecek apakah suatu atribut memiliki nilai.

Contoh:

SELECT  *  FROM  WALI

    WHERE  JBT_FNG  EXIST;

SELECT  * FROM  MAHASISWA

    WHERE ALAMAT EXIST;

DISTINCT digunakan untuk menghilangkan duplikasi pada saat menampilkan data.

Contoh:

SELECT  DISTINCT JBT_FNG FROM  WALI

SELECT  DISTINCT NILAI FROM  NILAIMHS

TOP n digunakan untuk menampilkan data sebanyak n baris pertama.

Contoh:

SELECT  TOP 10 NAMA, NIP FROM WALI

SELECT  TOP 10 NOMHS, NAMA, IPK FROM MAHASISWA

GROUP BY digunakan untuk mengelompokkan data berdasarkan item tertentu.

Contoh:

SELECT  KD_BARANG, SUM(STOCK)

       FROM  BARANG GROUP BY KD_BARANG

SELECT  KD_BARANG, HARGA, SUM(STOCK)

       FROM  BARANG WHERE HARGA<=5000

       GROUP BY KD_BARANG, HARGA

HAVING digunakan untuk memfilter baris-baris hasil dari pengelompokkan.

SELECT  KD_BARANG, HARGA, SUM(STOCK)

       FROM  BARANG GROUP BY KD_BARANG, HARGA

       HAVING  SUM(STOCK)<=10

Membuat dan Menggunakan Query

Terdapat tiga metode utama untuk membuat query:

1. dengan memilih parameter yang telah disediakan pada menu. Metode ini paling mudah digunakan namun paling tidak fleksibel karena pengguna hanya dapat menggunakan pilihan parameter yang terbatas.

2. query by example (QBE) adalah metode query yang disediakan sistem dalam bentuk record kosong dan pengguna dapat menentukan field dan nilai tertentu yang akan digunakan dalam query.

3. bahasa query (query language) adalah bahasa khusus yang digunakan untuk melakukan query pada sebuah basisdata. Metode ini paling rumit tetapi paling fleksibel.

Anda dapat menggunakan query untuk melihat, mengubah, dan menganalisa data dalam berbagai cara. Anda juga dapat menggunakan query sebagai sumber untuk record-record yang digunakan pada form dan report.

Ada beberapa macam query yang terdapat dalam Access 2003, diantaranya adalah:

1)      Select Query, merupakan jenis query yang paling umum. Select query mengambil data dari satu table atau lebih menggunakan suatu kriteria tertentu, kemudian manyampaikannya. Anda juga dapat menggunakan select query untuk mengelompokkan sejumlah record dan menghitung total, banyak data, serta rata-ratanya.

2)      Parameter Query, adalah query yang jika dijalankan menampilkan kotak dialog yang menanyakan informasi yang akan digunakan sebagai kriteria untuk mengambil data atau suatu nilai yang Anda ingin sisipkan ke dalam suatu field. Parameter Query juga berguna sebagai basis bagi form dan report.

3)      Crosstab Query, menampilkan nilai-nilai yang telah diolah (total, banyak data, rata-rata dan lain-lain) dari suatu field dalam tabel dan mengelompokkannya ke dalam suatu kelompok fakta yang didaftarkan pada bagian kiri dari datasheet dan kelompok fakta yang lain pada bagian atas dari datasheet.

4)      Action Query, adalah query yang membuat perubahan terhadap satu atau beberapa record sekaligus. Ada 4 macam action query, yaitu delete query, update query, append query dan make-table query.
Delete Query, digunakan untuk menghapus sekumpulan record dalam satu tabel atau lebih berdasarkan kriteria tertentu.

5)      Update Query, digunakan untuk membuat perubahan umum/global terhadap sekumpulan record dalam satu tabel atau lebih berdasarkan criteria tertentu.

6)      Append Query, digunakan untuk menambahkan sekelompok record dari satu atau lebih tabel ke dalam satu atau lebih tabel berdasarkan kriteria tertentu.

7)      Table Query, digunakan untuk membuat tabel baru dari seluruh atau sebagian data dalam satu atau lebih tabel. Make tabel query juga berguna untuk membuat tabel untuk ditransfer pada database Access 2003 yang lain, membuat report yang menampilkan data mulai posisi tertentu, membuat backup atau salinan dari suatu tabel, membuat tabel history yang berisi record-record yang sudah lama.

SQL Query,

            adalah query yang dibuat menggunakan permyataan SQL. SQL query terdiri dari union query, pass-through query, data definition query.

1)      Union, jenis query ini menggabungkan field (kolom) dari satu atau lebih tabel atau query ke dalam satu fiel atau kolom dalam query gasilnya

2)      Pass-Through, jenis query ini mengirimkan perintah secara langsung ke database ODBC, seperti Microsoft SQL Server, menggunakan perintah yang dapat diterima oleh server.

3)      Data Definition, jenis query ini membuat obyek database, seperti tabel Microsoft Access atau tabel Microsoft SQL Serve

FUNGSI AGREGAT

Beberapa SMBD memiliki fungsi aggregat, yaitu fungsi-fungsi khusus yang melibatkan sekelompok data (aggregat). Secara umum fungsi aggregat adalah:

• SUM untuk menghitung total nominal data
• COUNT untuk menghitung jumlah kemunculan data
• AVG untuk menghitung rata-rata sekelompok data
• MAX dan MIN untuk mendapatkan nilai maksimum/minimum dari sekelompok data.

Fungsi aggregat digunakan pada bagian SELECT. Syarat untuk fungsi aggregat diletakkan pada bagian HAVING, bukan  WHERE.

Query Antar Tabel

Jenis-jenis tabel berdasarkan fungsinya

Tabel Data
            Adalah sebuah tabel yang menampung data-data yang digunakan sebagai informasi. Table data merupakan table yang sangat umum digunakan dalam database relasi. Biasanya sifat data pada table ini adalah dinamis karena sering diubah, dihapus dan diperbaharui.

Tabel Validasi
            Adalah sebuah table yang menampung data-data yang memiliki criteria tertentu dan biasanya digunakan oleh data pada table lainnya sebagai validasi. Table validasi tersebut juga table referensi (reference table) dan table pencarian (look-up table). Oleh karena itu biasanya table validasi harus lebih dulu ada struktur data dan isinya, sebelum table-table lainnya.

Tabel Penghubung
            Adalah sebuah table yang berfungsi sebagai ‘jembatan’ antara dua table yang mengalami relasi many-to-many. Dengan adanya table penghubung, maka hubungan relasi yang terjadi akan berubah dari many-to -many. Menjadi one-to-many.

Menggabungkan Tabel

Contoh :
Akan menampilkan nama pegawai yang lokasi departemennya di KENDAL

1. Select nama from pegawai,departemen where pegawai.id_departemen=departemen.id_departemen and departemen.lokasi=’KENDAL’;  

Menggunakan Alias
            Nama tabel yang digunakan dalam query antartabel dapat diganti dengan manyebutkan nama aliasnya

1. Select nama from pegawai x,departemen y where x.id_departemen=y.id_departemen and y.lokasi=’KENDAL’;  

Operator Cross Join / Perkalian Kartesian
            Operator Cross Join digunakan untuk melakukan operasi perkalian kartesian.
Contoh :
Akan dilakukan operasi cross join untuk dua buah table :
Dengan Perintah :

1. select*from sales, pelanggan;  
2. atau dapat ditulis  
3. select*from sales cross join pelanggan;  

Operasi cross join di atas diperbaiki menjadi :

1. select*from sales, pelanggan where sales.id_sales = pelanggan.id_sales;  

Equajoin
            Adalah penggabungan antar table yang menggunakan operator = pada kondisi where. (sebagai contoh perhatian penggabungan antar tabel seperti diatas)

Selfjoin
            Adalah penggabungan antar tabel yang sama :
Contoh :
Akan ditampilkan nama pegawai yang pekerjaannya 'salesman' dan pegawai yang pekerjaannya 'manages':

1. select x.nama, y.nama from pegawai x, pegawai y where x.pekerjaan='salesman' and y.pekerjaan='manager';  

Operator Union
            Digunakan untuk menggabungkan hasil dari dua buah query.
Contoh :

1. (select nama from pinjam) union (select nama from tabungan);  

Operator Intersect
            Digunakan untuk memperoleh baris-baris yang terdapat pada kedua tabel.
Contoh :

1.  (select nama from pinjam) intersect (select nama from tabungan);  

Operator Except / Minus
            Jika terdapat tabel A dan B, operasi except akan menghasilkan semua yang ada pada tabel A tetapi tidak terdapat pada tabel B :
Contoh :
Akan menampilkan semua nama nasabah yang mempunyai simpanan tetapi tidak mempunyai pinjaman.

1.  (select nama from pinjam) except (select nama from tabungan);  

Keterangan :

Perintah-perintah seperti UNIO, INTERSECT dan EXCEPT / MINUS hanya dapat dioperasikan pada MySQL mulai versi 3.24. sehingga apabila MySQL yang kita gunakan dibawah versi tersebut, maka perintah-perintah tersebut tidak dapat berfungsi.

Operator Natural Join
            Operator natural join akan melakukan operasi equijoin dengan memperlakukan nama-nama field yang sama sebagai field penghubung antar tabel.
Contoh :
Akan direlasikan dua buah table yaitu tabel pegawai dengan tabel departemen dengan kata kunci relasinya adalah id_departemen.

1. select nama,departemen, lokasi from pegawai, departemen where pegawai.id_departemen = departemen.id_departemen order by nama;  
2. atau dapat ditulis :  
3. select nama,departemen, lokasi from pegawai natural join departemen order by nama;