Modul Database SQL PDF

Preview

Summary

DAFTAR ISI DAFTAR ISI ..................................................................................................... i BAB I PENGENALAN BASIS DATA ................................................................ 1 1 Arsitektur Client/Server ......................................................

Description

DAFTAR ISI DAFTAR ISI ..................................................................................................... i BAB I PENGENALAN BASIS DATA ................................................................ 1 1 Arsitektur Client/Server ................................................................................. 1 2 Fitur Ms.SQL Server ..................................................................................... 1 3 Structured Query Language .......................................................................... 2 4 Tipe Data SQL Server................................................................................... 4 5 Fungsi-fungsi Dalam SQL Server ................................................................. 5 6 Operator dalam SQL Server ......................................................................... 7 7 Ekspresi CASE ............................................................................................. 10 BAB II QUERY DDL DAN DML ........................................................................ 11 1 DDL (Data Definition Language) ................................................................... 11 2 DML (Data Manipulation Language) ............................................................. 18 BAB III INDEX & VIEW .................................................................................... 26 1 Index............................................................................................................. 20 2 View.............................................................................................................. 22 BAB IV TRANSACT SQL ................................................................................. 30 1 Transact SQL ............................................................................................... 30 BAB V STORED PROCEDURE....................................................................... 33 1 Create Stored Procedure .............................................................................. 33 2 Alter Stored Procedure ................................................................................. 34 3 Execute Stored Procedure ............................................................................ 34 4 Stored Procedure dengan Parameter Output ................................................ 35 BAB VI TRIGGER ............................................................................................ 36 1 Create Trigger............................................................................................... 36 2 Alter Trigger .................................................................................................. 36 3 Drop Trigger ................................................................................................. 36 4 Triger Event .................................................................................................. 36 5 Alias in Trigger .............................................................................................. 37 BAB VII USER DEFINED FUNCTION (UDF) ................................................... 43 1 Function Return Scalar ................................................................................. 43 2 Function Return Inline Table Value ............................................................... 45 DAFTAR PUSTAKA ......................................................................................... 46 i

BAB I PENGENALAN BAHASA SQL 1. Arsitektur Client/Server Microsoft SQL server merupakan database relational yang dirancang untuk mendukung aplikasi dengan arsitektur client/server, dimana database terdapat pada computer pusat uang disebut server, dan informasi digunakan bersamasama oleh beberapa user yang menjalankan aplikasi didalam computer localnya yang disebut client. Arsitektur client/server sangat mengurangi lalu lintas network karena ia hanya memberikan data sesuai dengan permintaan user. Hal ini membantu tugastugas pemeliharaan, misalnya membackup dan merestorasi data dapat dilakukan lebih mudah karena data diletakan pada satu tempat. 2. Fitur MS SQL Server 



Relational Database Engine : komponen utama atau jantung SQL Server 2005. Analysis Services : Basis dari solusi intelijen bisnis yang ampuh (powerful), dan mendukung aplikasi-aplikasi OLAP (online analytical processing),



serta data minning. Data Transformation Service (DTS): sebuah mesin untuk membuat solusi ekspor dan impor data, serta untuk mentransformasi data ketika data

   

tersebut ditransfer. Notification Services: sebuah framework untuk solusi dimana pelanggan akan dikirimi notifikasi ketika sebuah event muncul. Reporting Services: service yang akan mengambil data dari SQL Server, dan menghasilkan laporan-laporan. Service broker: sebuah mekanisme antrian yang akan menangani komunikasi berbasis pesan diantara service. Native HTTP Support: dukungan yang memungkinkan SQL server 2005 yang (jika diinstall pada Windows Server 2003) akan merespon request terhadap HTTP endpoint, sehingga memungkinkan pembangunan sebuah web service untuk SQL Server tanpa menggunakan IIS.

Database Client Server (SQL) Lanjut

1

 

SQL server Agent : akan mengotomatiskan perawatan database dan mengatur task, event dan alert. .NET

CLR

(Common

Language

Runtime):

akan

memungkinkan

pembuatan solusi menggunakan managed code yang ditulis dalam salah 

satu bahasa .NET. Replication: serangkaian teknologi untuk menjalin dan mendistribusikan data dan obyek database dari sebuah database ke database lain, dan



melakukan sinkronisasi untuk menjaga konsistensinya. Full-Text Search: memungkinkan pengindeksan yang cepat dan flexibel untuk query berbasis kata kunci (terhadap data teks yang disimpan dalam database).

3. Structured Query Language Dalam basis data salah satu bahasa yang harus dikuasai adalah SQL (Structured Query Language). SQL merupakan bahasa computer standar yang ditetapkan oleh ANSI (American National Standard Institute) yang digunakan untuk berkomunikasi dengan system manajemen basis data relational (RDBMS). SQL pertama kali dikembangkan oleh IBM dalam SEQUEL-XRM dan system-R project (1974-1977) dimana SEQUEL merupakan kepanjangan dari Structured English Query Language yang pada akhirnya diganti menjadi hanya Structured Query Language. Publikasi standar SQL pertama pada tahun 1989 yang dikenal dengan SQL-89 atau SQL-1 kemudian pada tahun 1992 dirilis SQL-92 atau SQL-2 dan Database Client Server (SQL) Lanjut

2

pada tahun 1999 dirilis SQL-99 atau SQL-3. Standar terbaru dari SQL adalah SQL 2003. Setiap DBMS memiliki perbedaan dalam menerapkan standar SQL sehingga setiap DBMS memiliki ciri tersendiri yang berbeda dengan yang lain. 4. Tipe Data SQL Server Secara garis besar ada beberapa jenis tipe data pada SQL Server, yaitu :   

Tipe data numeric Tipe data karakter Tipe data temporal (date, time dan datetime).

a. Tipe Data Numerik Tipe Data

Keterangan Tipe data INT ini mungkin sudah banyak dikenal oleh Anda. Tipe data ini

Int

dapat menerima nilai mulai dari -231 hingga 231-1. Tipe data ini menghabiskan 4 bytes untuk menyimpan data pada harddisk. Tipe data BIT hanya bisa menerima input angka 1 dan 0 sebagai nilai (atau

Bit

bisa juga null, yang berarti tidak ada nilai). Tipe data ini sangat membantu jika Anda ingin menghasilkan output yes/no, true/false, dsb. Tipe data SMALLINT ini juga mirip dengan tipe data INT, hanya saja nilai yang diterima lebih kecil dari tipe data INT. Tipe data ini dapat menerima nilai mulai dari -215 hingga 215-1. SMALLINT Sama seperti INT ,

Smallint

membutuhkan 50% memory yang digunakan INT. INT berfungsi untuk mendefinisikan integer, bilangan bulat yang dapat menampung angka hingga 4byte. Sedangkan SMALLINT hanya mampu mendefinisikan angka sebanyak 2byte. Tipe data BIGINT ini mirip dengan tipe data INT, hanya saja nilai yang

Bigint

diterima lebih besar dari pada tipe data INT. Tipe data ini dapat menerima nilai mulai dari -263 hingga 263-1. Tipe data ini menghabiskan 8 bytes untuk menyimpan data pada harddisk.

Database Client Server (SQL) Lanjut

3

Tipe data DECIMAL ini menerima nilai yang lebih presisi dibanding tipe data integer yang telah dibahas sebelumnya. Tipe data ini menggunakan 2 parameter untuk menentukan tingkat presisi nilai yang diterima; precision dan scale. Precision adalah jumlah digit yang bisa diterima oleh Decimal

field, sedangkan scale adalah jumlah angka di belakang koma yang bisa

(p[,[s]])

diterima oleh field. Jadi, jika kita membuat parameter precision sebanyak 5 dan scale sebanyak 2 maka field kita bisa menerima nilai seperti ini : 123,45. Tipe data ini bisa menerima nilai mulai dari -1038hingga 1038-1. Tipe data ini menghabiskan 5-17 bytes untuk menyimpan data pada harddisk, tergantung pada tingkat kepresisian nilai yang dimasukkan. Tipe data NUMERIC ini pada dasarnya sama dengan tipe data DECIMAL.

Numeric (p[,[s]])

Jadi tipe data ini bisa disebut sinonim dari decimal. Fungsinya adalah untuk mendefinisikan angka pecahan baik fixed desimal ataupun floating point. Nilai n adalah jumlah bytes total dan p adalah presisi angka dibelakang koma. Tipe data DATETIME ini dapat menerima nilai tanggal dan waktu. Berfungsi untuk mendefinisikan tanggal, menyimpan tahun, bulan, hari,

Datetime

jam, menit, detik dan seperseribu detik (milliseconds). Tipe data DATETIME ini menghabiskan 8 bytes untuk menyimpan data pada harddisk. Tipe data MONEY merupakan Bilangan pecahan dengan 4 angka

Money

dibelakang koma. Digunakan untuk perhitungan moneter. Tipe data ini menghabiskan 8 bytes untuk menyimpan data pada harddisk. Tipe data FLOAT ini mirip dengan tipe data DECIMAL, hanya saja

Float

paramater scale pada tipe data ini bisa menerima nilai yang tak terhingga, seperti pada nilai pi. Berfungsi untuk mendefinisikan angka pecahan (floating point). Nilai n adalah jumlah angka yang dapat ditampung. Tipe data REAL ini mirip dengan tipe data FLOAT, hanya saja lebih kecil dibandingkan menerima nilai yang dengan FLOAT. sama dengan float

Real

namun menempati memory 50% dari float. Dan penyimpanan tipe data REAL pun hanya menggunakan 4 bytes untuk menyimpan data pada harddisk. Tipe data BINARY ini dapat menerima data BINARY dengan maksimum

Binary

8000 bytes data. Tipe data ini diinterpretasikan sebagai string dari bit. dan berfungsi untuk menyimpan bit pattern seperti heksadecimal.

Database Client Server (SQL) Lanjut

4

b. Tipe Data Karakter Tipe Data

Keterangan Tipe data CHAR berfungsi untuk Mendefinisikan string sepanjang dan mempunyai karakter. Tipe data CHAR ini dapat digunakan untuk memasukkan data karakter non-Unicode dengan jumlah karakter yang

Char

fix. Tipe data CHAR ini bisa menerima hingga 8000 karakter, dan jumlah

[(n)]

bytes yang dibutuhkan tergantung jumlah karakter yang dimasukkan. Apabila jumlah karakter yang dimasukan adalah 1 karakter maka membutuhkan 1 byte. Tipe data VARCHAR ini dapat Mendefinisikan string sepanjang variabel n. Tipe data ini mirip dengan tipe data char, namun tipe data ini berguna bagi Anda yang tidak mengetahui secara pasti jumlah karakter yang akan dimasukkan oleh user. Tipe data ini juga bisa menerima nilai hingga

Varchar

8000 karakter. Jadi, jika pada tipe data char, Anda mendefinisikan

[(n)]

char(5), maka Anda akan selalu membutuhkan 5 bytes untuk menyimpan data pada harddisk, walaupun jumlah karakter yang dimasukkan hanya 1 hingga 4 karakter; maka pada tipe data ini, jumlah bytes yang dibutuhkan akan lebih fleksibel. Tipe data FLOAT menghabiskan 4-8 bytes untuk menyimpan data pada harddisk. Tipe data TEXT dapat Menyimpan teks sampai dengan 2 GB. Text

Text

disebut juga dengan binary large objects (BLOBs).

Image

Tipe data IMAGE berfungsi untuk Mendefinisikan binary data untuk menyimpan image seperti GIF, JPG, TIFF,dll.

Tipe Data Temporal Tipe Data

Keterangan

Date

Define tanggal, default literal format YYYY-MM-DD

DateTime

Define kombinasi tanggal dan waktu dengan base 24 jam

5. Fungsi-Fungsi Dalam SQL Server Fungsi –fungsi dala SQL server dapat deklompokkan dalam dua kelompok besar yaitu fungsi aggregate dan fungsi scalar. Bagian setelah ini akan mendefinisikan fungsi-fungsi tersebut.

Database Client Server (SQL) Lanjut

5

a. Fungsi Aggregate Fungsi

Keterangan

AVG()

Returns the average value

COUNT()

Returns the number of rows

FIRST()

Returns the first value

LAST()

Returns the last value

MAX()

Returns the largest value

MIN()

Returns the smallest value

SUM()

Returns the sum

b. Fungsi Skalar SQL Server menyediakan beberapa fungsi scalar yang digunakan dalam pembuatan ekspresi scalar. Fungsi scalar bekerja pada satu nilai saja. Berikut ini adalah kelompok fungsi-fungsi scalar : 1) Fungsi String SQL Server menyediakan fungsi-fungsi String yang dapat digunakan sebagai bagian dari ekspresi karakter manapun. Fungsi ini digunakan untuk bermacam macam operasi string. Antara lain : Fungsi

Keterangan

Sintaks CHAR (expresi

CHAR

Mengembalikan karakter dari nilai kode ascii

Integer)

Fungsi ini digunakan untuk menentukan CHARINDEX

posisi awal dari suatu karakter string (String1)

CHARINDEX

di dalam string lain (String 2).

(String1,string2)

Fungsi ini digunakan menampilkan sejumlah tertentu karakter dihitung dari sebelah kiri

LEFT(String,Jumlah

LEFT

suatu karakter string.

Karakter)

LEN

Menghitung panjang karakter string

LEN ( String )

Fungsi ini digunakan untuk mengubah huruf LOWER

besar menjadi kecil.

LOWER (String)

Fungsi ini digunakan untuk menghilangkan LTRIM RIGHT

karakter blank disebelah kiri string.

LTRIM (String)

Fungsi ini digunakan untuk

RIGHT(String,Jumlah

mengambil

sejumlah karakter mulai dari sebelah kanan.

Karakter)

Database Client Server (SQL) Lanjut

6

Fungsi ini digunakan untuk menghilangkan setiap karakter kosong dari ujung kanan RTRIM

suatu string.

2) Fungsi Tanggal Fungsi-fungsi tanggal berikut ini memiliki kegunaan untuk mendapatkan nilai-nilai seputar data tanggal misalnya nama hari, tanggal saat ini, nama bulan dan sebagainya. Berikut ini adala fungsi-fungsi tanggal yang ada di SQL Server. Fungsi

Keterangan

Sintaks

Fungsi untuk mendapatkan data tanggal saat ini beserta waktunya hingga kecepatan GETDATE()

DATEPART()

100 milidetik

SELECT GETDATE

FUNgsi ini digunakan untuk mendapatkan

SELECT

nilai tertentu (item) dari data tanggal yang

DATEPART(item,tan

disertakan.

ggal) SELECT

DATENAME()

DATEDIFF()

Fungsi ini digunakan untuk mendapatkan

DATENAME(item,tan

nama hari dari tanggal tertentu

ggal)

Fungsi ini digunakan untuk mendapatkan

SELECT

beberapa

DATEDIFF(item,tang

"jarak"

antara

dua

tanggal,

hasilnya berupa integer.

gal1,tanggal2)

Fungsi ini menambahkan tanggal tertentu

DATEADD()

dengan item tertentu (misalkan ditambah

SELECT

bulan/month

DATEADD(item,num

atau

hari/day),

sejumlah bilangan tertentu.

dengan

ber,tanggal)

6. Operator dalam SQL Server SQL server mendukung hampir semua operator yang digunakan oleh sebagian besar bahasa pemrograman. Tipe-tipe operator yang didukung oleh SQL server adalah sebagai berikut: a. Operator Aritmatika Operator

Keterangan

*

Perkalian

/

Pembagian

Database Client Server (SQL) Lanjut

7

%

Modulus

-

Pengurangan

+

Penjumlahan



Beberapa aturan mengenai penggunaan operator aritmatika adalah: Operasi aritmatika dapat dilakukan pada kolom numerik atau konstanta numerik



Modulus (%) operator tidak dapat digunakan pada kolom money, smallmoney, float, atau tipe data real Prioritas urutan dari operator aritmatika dalam sebuah ekspresi adalah

perkalian (*), pembagian (/), modulo (%) diikuti oleh pengurangan (-) dan penambahan (+). Prioritas

urutan dari operator dapat

diubah dengan

menggunakan obyek pengelompokan utama disebut kurung (()) b. Operator Perbandingan Operator perbandingan memungkinkan pengambilan baris dari tabel berdasarkan kondisi yang ditentukan dalam klausa WHERE Syntax: SELECT column_list FROM table_name WHERE expression1 comparison_operator expression2 Operator

Keterangan

||

Penggabungan

=

Sama dengan

Tidak sama dengan

>

Lebih besar dari

>=

Lebih besar atau sama dengan

Tidak lebih dari

!=

Tidak sama dengan

c. Operator Logika Operator logika digunakan untuk mengambil data yang dapat diekstraksi dalam kisaran. Syntax:

Database Client Server (SQL) Lanjut

8

SELECT column_list FROM table_name WHERE expression1 range_operator expression2 AND expression3 Operator

Keterangan

NOT

Negasi dari suatu nilai

AND

Akan menghasilkan nilai true apabila dua nilai yang dibandingkan bernilai true

OR

Akan menghasilkan nilai salah apabila dua nilai yang dibandingkan bernilai salah

BETWEEN

Akan bernilai true apabila nilai yang dianalisa masih berada dalam jangkauan

LIKE

Akan bernilai true jika nilai yang dianalisa menemukan nilai standar

IN

Akan berniai true apabila beberapa item yang ditemukan bernilai true

SOME

Akan menghasilkan nilai true apabila item apapun bernilai true

ANY

Akan menghasilkan nilai true apabila beberapa item bernilai true

ALL

Akan menghasilkan nilai true apabila semua nilai bernilai benar

d. Operator List Operator IN memungkinkan pemilihan nilai-nilai yang cocok dengan salah satu dari nilai-nilai dalam daftar Operator NOT IN membatasi pemilihan nilai-nilai yang cocok dengan salah satu nilai dalam daftar Syntax: SELECT column_list FROM table_name WHERE expression list_operator (‘value_list‘)

Database Client Server (SQL) Lanjut

9

7. Ekspresi CASE Perintah CASE pada SQL Server adala perintah yang fleksibel dan sangat bagus dalam membangun ekspresi yang dinamis. Cara kerjanya mirip dengan perintah if() pada bahasa pemograman lain. Ekpresi CASE mengembalikan ekspresi juga, karena itu dapat dipergun...