Resume SQL 1

Senin 21 Oktober 2013

RESUME SQL 1

Mata kuliah : Structure Query Language I

Dosen : Titik Lusiani, M.Kom, OCA

Blog : http://blog.stikom.edu/lusiani/

v  Memahami SQL dasar-dasar Database

SQL adalah Bahasa yang digunakan untuk berinteraksi dengan database.

Database adalah kumpulan data yang disimpan dengan beberapa cara pengorganisasian.

Adapun istilah database kontainer (biasanya sebuah file atau sekumpulan file) untuk menyimpan data yang telah diatur.

Penertian lain dari database adalah kontainer yang dibuat dan dimanipulasi melalui DBMS (software database/sistem manajemen database).

            Tabel adalah file terstruktur yang dapat menyimpan data dengan tipe khusus, dalam tabel juga terdapat kolom dan tipe data. Tabel tersusun dari beberapa kolom, kolom berisi bagian informasi dalam tabel setiap kolom dalam database mempunyai tipe data terkait, data dalam tabel juga berisi record/baris.

            Dalam tabel juga terdapat costraint atau aturan dalam kolom yang harus ada isinya contoh : primary key, Not null (tidak boleh kosong), check (pengecekan suatu kolom), unique, foreign key.

primary key : memiliki nilai unik dalam setiap kolom harus memiliki primary key agar proses perubahan data lebih mudah.

Berikut ketentuan dalam pembuatan primary key :

-          Dua baris tidak dapat memiliki nilai primary key yang sama.

-          Setiap baris harus memiliki nilai primary key kolom tidak boleh kosong.

-          Kolom yang berisikan nilai primary key tidak pernah dapat dimodifikasi dan diperbarui.

Foreign key : merupakan kunci penghubung dari satu kolom ke kolom lain hubungan antar tabel

Perintah SELECT digunakan untuk menampilkan atau mengambil data dari database.

Perintah SELECT memiliki 3 (tiga) macam kemampuan :

a. Projection: Perintah SELECT bisa digunakan untuk memilih kolom apa saja dari tabel yang akan ditampilkan.

 Contoh :

SELECT NIM , alamat

FROM Mhs;

b. Selection: Perintah SELECT bisa digunakan untuk memilih baris data mana saja yang akan ditampilkan.

c. Joining: Perintah SELECT bisa digunakan untuk menampilkan data atau informasi dari 2 atau lebih tabel yang terhubung

Contoh selection dan joining :

Menampilkan nama lengkap mahasiswa dan nim dari seluruh mahasiswa

SELECT MAHASISWA.NAMA_DEPAN ||' '|| MAHASISWA.NAMA_BELAKANG "NAMA LENGKAP", KRS.NIM

FROM MAHASISWA, KRS

WHERE MAHASISWA.NIM = KRS.NIM;

Ø  Perintah SELECT sederhana terdiri atas :

“ SELECT */[{DISTINCT} COLUMN/EXPRESSION {ALIAS}] FROM [TABLE]; ”

Single-Row Function digunakan untuk memanipulasi baris data. Single-row function menerima 1 (satu) atau lebih input parameter / argument dan mengembalikan 1 (satu) nilai untuk setiap baris yang dikembalikan oleh query.

Sebuah input parameter / argument dapat terdiri atas / diisi dengan:

·         Nilai konstan yang dimasukkan pengguna

·         Nilai variabel

·         Nama kolom

·         Ekspres

Ø  Character function dapat dibagi menjadi 2:

ü  Case-manipulation function

ü  Character-manipulation function

 

Ø  Case-manipulation function terdiri dari:

Ø  LOWER: mengubah karakter mixed-case atau uppercase menjadi lowercase.

Ø  UPPER: mengubah karakter mixed-case atau lowercase menjadi uppercase.

Ø  INITCAP: mengubah setiap huruf depan dari kata menjadi uppercase dan membiarkan huruf yang lain tetap  .

Ø  Character-manipulation function  terdiri dari:

o   CONCAT: menggabungkan 2(dua) nilai menjadi 1 (satu). CONCAT hanya dapat menggabungkan 2 parameter / argument.

o   SUBSTR: mengambil karakter dari posisi karakter dan panjang yang disebutkan.

o   LENGTH: mengambil panjang sebuah string karakter menjadi nilai angka.

o   INSTR: mengambil posisi (dalam bentuk angka) dari karakter yang disebutkan.

o   LPAD: menambahkan karakter di bagian kiri string karakter

o   RPAD: menambahkan karakter di bagian kanan string karakter.

o   REPLACE: menggantikan karakter yang disebut pada string karakter dengan karakter / string karakter lain.

o   TRIM: menghapus karakter yang disebutkan dari huruf paling awal atau paling akhir (atau keduanya) dari string karakter. Dapat juga digunakan untuk menghapus spasi.

Ø  General Functions Fungsi-fungsi berikut ini dapat digunakan dengan tipe data apapun dan berhubungan dengan penggunaan nilai NULL:

Þ    NVL (expr1, expr2) Mengubah sebuah nilai NULL menjadi nilai yang tertentu.

Þ    NVL2 (expr1, expr2, expr3) Jika expr1 bernilai NULL, maka fungsi akan                                mengembalikan nilai pada expr3; sedangkan jika expr1 tidak bernilai null, maka                                              fungsi akan mengembalikan nilai pada expr2.

Þ    NULLIF (expr1, expr2)

Membandingkan 2 (dua) ekspresi dan mengembalikan NULL jika ekspresi tersebut sama. Jika kedua ekspresi tidak sama, maka fungsi akan mengembalikan expr1.

Ø  COALESCE (expr1, expr2, . . ., exprN)Mengembalikan ekspresi non-NULL pertama dari daftar ekspresi.

Ø  Subquery adalah sebuah query SELECT yang ditancapkan pada sebuah klausa di query SELECT yang lain. anda dapat meletakkan subquery pada beberapa klausa pada SQL query, meliputi:

§  Klausa WHERE

§  Klausa HAVING

§  Klausa FROM

Ø  Syntax sederhana untuk menuliskan subquery adalah sebagai berikut:

SELECT SELECT_LIST

FROM TABLE

WHERE EXPR OPERATOR

(SELECT SELECT_LIST FROM TABLE);

1.      Contoh  single-row subquery

Menampilkan nim, nama belakang, dan kota_tinggal dari mahasiswa yang tinggal di kota yang sama dengan mahasiswa bernama depan ‘HANDIGA’.

SELECT NIM, NAMA_BELAKANG, KOTA_TINGGAL FROM MAHASISWA WHERE KOTA_TINGGAL =

(SELECT KOTA_TINGGAL FROM MAHASISWA WHERE NAMA_DEPAN = ‘HANDIGA’);

2.      multiple-row subquery

Menampilkan nim, nama lengkap, kode mk, nama mk, dan nilai uas yang dari mahasiswa yang mengambil mata kuliah yang diadakan pada semester 5.

SELECT MHS.NIM, MHS.NAMA_DEPAN ||' '|| MHS.NAMA_BELAKANG "NAMA LENGKAP", N.KODE_MK, MK.NAMA_MK, N.UAS

FROM MAHASISWA MHS JOIN NILAI N

ON MHS.NIM = N.NIM

JOIN MATAKULIAH MK

ON MK.KODE_MK = N.KODE_MK

WHERE N.KODE_MK IN (SELECT KODE_MK FROM MATAKULIAH WHERE SEMESTER = 5);

Ø  Menggabungkan tabel (join)

Join digunakan untuk menghubungkan antar tabel.

Contoh :

SELECT vend_name, prod_name, prod_price

FROM  vendors, products

WHERE vendors.vend_id = products.vend_id;

Ø  Tipe-tipe join :

·         Inner join : join berdasarkan pengujian persamaan antara dua tabel

Contoh :

SELECT vend_name, prod_name, prod_price

FROM vendors inner join products

             on vendors.vend_id = products.vend_id;

·         Self join : join pada dirinya sendiri/join pada tabel yang sama

Contoh :

SELECT c1.cust_id, c1.cust_name, c1.cust_contact

FROM customers c1, customers c2

WHERE c1.cust_name= c2.cust_name

               And c2.cust_contact = ‘jim’;

·         Natural join : join untuk memilih hanya satu kolom yang unik

Contoh :

SELECT c.*, o.order_num, o.order_date, ol.prod_id, ol.quantity, ol.item_price

FROM customers c, orders o, orderitems ol

WHERE c.cust_id= o.cust_id

               And ol.order_num = o.order_num

     And prod_id = ‘RGAN01’;

·         Outer join : join memasukkan baris-baris tabel yang tidak mempunyai baris berkaitan dalam tabel terkait

Contoh :

SELECT c.cust_id, o.order_num

FROM customers, orders

WHERE c.cust_id = o.cust_id;