Bd tsql 2013
-
Upload
devandy-enda -
Category
Documents
-
view
326 -
download
2
description
Transcript of Bd tsql 2013
MAKALAH
BASIS DATA“TRANSACT SQL PADA SQL SERVER 2000 / 2005”
OLEH :DEPANDI ENDA (1106315)
PROGRAM STUDI D3 TEKNIK INFORMATIKAPOLITEKNIK NEGERI BENGKALIS
2012
KATA PENGANTAR
Kiranya tidak ada kata yang pantas terucap dari penulis, selain rasa syukur
kepada Allah SWT, atas segala petunjuk, kekuatan, dan kejernihan pikiran dalam
menyusun makalah ini hingga bisa terselesaikan dan tersaji kepada para pembaca
yang budiman.
Makalah ini merupakan sebagian materi yang di ajarkan pada matakuliah
Basis Data. Makalah ini juga diselesaikan untuk menyelesaikan tugas yang
diberikan dosen pengampu mata kuliah. Dalam penyajian makalah ini penulis juga
berupaya untuk membuat ringkasan materi yang sangat sederhana dan mudah
dipahami oleh pembaca.
Walaupun penulis sudah berupaya semaksimal mungkin untuk
mempersembahkan yang terbaik, namun penulis menyadari bahwa makalah ini
masih jauh dari kata sempurna. Oleh karena itu, segala saran dan kritik yang
membangun sangatlah penulis harapkan dari para pembaca untuk pengembangan
penulisan maupun materi yang dipaparkan pada makalah ini.
Akhir kata, terima kasih kepada pembaca yang budiman, dan semoga hari
ini jauh lebih baik dari hari-hari sebelumnya.
Bengkalis, Januari 2013
Penulis
| BASIS DATA - TEKNIK INFORMATIKA 2012 2
DAFTAR ISI
Kata Pengantar.................................................................................................. 2
Daftar Isi........................................................................................................... 3
BAB I : PENDAHULUAN
A. Latar Belakang............................................................................. 6
B. Tujuan.......................................................................................... 6
C.Ruang Lingkup............................................................................. 6
BAB II : LANDASAN TEORI
A.Pengertian Basis Data................................................................... 7
B.Mengenal SQL Server 2000......................................................... 7
C.Menjalankan Service Pada SQL Server 2000 / 2005................... 8
D.Membuka Query Anlyzer Pada SQL Server 2000 / 2005............ 8
BAB III : PEMBAHASAN
A.Perintah Dasar T-SQL
DML (Data Definition Language)
CREATE.................................................................... 11
ALTER...................................................................... 11
DROP........................................................................ 11
DCL (Data Control Langguage)
GRANT..................................................................... 12
REVOKE................................................................... 16
DDL (Data Manipulation Language)
SELECT.................................................................... 18
INSERT..................................................................... 18
UPDATE................................................................... 18
DELETE.................................................................... 18
COMMIT................................................................... 18
ROLLBACK.............................................................. 18
| BASIS DATA - TEKNIK INFORMATIKA 2012 3
B. Fungsi Aggregate Pada T-SQL
SUM ( ).................................................................................. 19
AVG ( ).................................................................................. 19
MAX ( )................................................................................. 19
MIN ( )................................................................................... 19
COUNT ( )............................................................................. 19
C. Fungsi Built-in Pada T-SQL
DATEPART.......................................................................... 20
STRING................................................................................. 20
(‘+”).......................................................................... 20
UPPER....................................................................... 20
LOWER..................................................................... 20
LEFT.......................................................................... 20
RIGHT....................................................................... 20
SUBSTRING............................................................. 20
Penggabungan ‘+’ , UPPER dan SUBSTRING........ 20
LEN....................................................................................... 21
CONVERT............................................................................ 21
D. CONSTRAINT
Primary Key................................................................... 21
Foreign Key.................................................................... 22
Not Null.......................................................................... 22
Unique............................................................................ 22
Check.............................................................................. 22
Default............................................................................ 23
Identity........................................................................... 23
Drop Constraint.............................................................. 23
E. INDEX
Clustered Index.............................................................. 24
Non Clustered Index....................................................... 25
Unique Index.................................................................. 25
| BASIS DATA - TEKNIK INFORMATIKA 2012 4
F. VIEW
Membuat View................................................................ 25
Mengubah View.............................................................. 25
Menghapus View............................................................. 25
G. STORED PROCEDURE
Stored Procedure tanpa Parameter................................. 26
Stored Procedure dengan Parameter.............................. 26
Mengubah Stored Procedure.......................................... 26
Menghapus Stored Procedure......................................... 26
H. TRIGGER
Membuat Trigger............................................................ 26
Mengubah Trigger.......................................................... 26
Menghapus Trigger........................................................ 26
I.Transact SQL (Bagian II)
Pengenalan T-SQL......................................................... 27
Bentuk Umum T-SQL.................................................... 27
Menjalankan Program T-SQL........................................ 28
Variabel Global.............................................................. 29
Perulangan di T-SQL..................................................... 29
Continue dan Break di T-SQL....................................... 30
Return di T-SQL............................................................. 31
If – Else dan Case........................................................... 31
KESIMPULAN................................................................................................. 32
DAFTAR PUSTAKA....................................................................................... 33
| BASIS DATA - TEKNIK INFORMATIKA 2012 5
BAB I
PENDAHULUAN
A. Latar Belakang
Ketika system computer belum ditemukan, pengorganisasian data dikerjakan secara manual. Hal ini masihlah masuk akal kalau pengorganisasian datanya masih dalam tingkat skala kecil. Namun bagaimana kalau pengorganisasian datanya sudah berada dalam tingkatan besar ? Komputerlah solusinya. Dengan komputer sangat memungkinkan untuk mengorganisasi data yang besar. Hal ini disebabkan kerja komputer yang memiliki kekonstanan yang tetap, lain halnya dengan manusia, yang daya kerjanya dapat menurun dan sangat mudah membuat kesalahan.
Masalah diatas sudah dipecahkan dengan komputer. Tapi masalah database dengan komputer adalah tak semua orang bisa mengoperasikannya. Bagi anda yang sudah mengenal bahasa pemrograman pascal, basic, c/c++, visual basic, java, php, dll tentunya sudah merasakan betapa rumitnya membuat, mengolah(mengedit,menghapus,mencari,menginput data) sebuah file table saja. Apalagi namanya database sudah pasti akan lebih dari satu table, maka dapat dibayangkan kerumitannya. Oleh sebab itu maka bermunculan database engine yang sudah banyak menyediakan fitur – fitur lengkap untuk mempermudah dalam mengakses dan mengolah database. Salah satunya adalah SQL Server 2005.
B. Tujuan
Makalah ini disusun untuk para pembaca khusus nya mahasiswa yang sedang mempelajari tentang Basis Data yang mana pada pokok pembahasan makalah ini hanya membahas tentang Transact SQL pada SQL Server 2005. Setelah mempelajari makalah ini diharapkan para mahasiswa lebih memahami secara teori maupun pengembangannya dalam pemecahan soal tentang Basis Data, serta memberikan tambahan wawasan pengetahuan bagi pembaca untuk memecahkan soal pembahasan tentang Transact SQL.
C. Ruang Lingkup
Dalam modul ini, dibicarakan mengenai Basis Data sebagai bahan pengetahuan dasar bagi mahasiswa materi yang dibahas meliputi :
1. Transact SQL pada SQL Server 2005
| BASIS DATA - TEKNIK INFORMATIKA 2012 6
BAB IILANDASAN TEORI
A. Pengertian Basis Data
Database atau basisdata di dalam buku Simarmata & Paryudi (2006:1), sebagai
berikut:
1. Menurut Stephens dan Plew (2000), adalah mekanisme yang digunakan untuk
menyimpan informasi atau data.
2. Menurut silberschatz, dkk (2002) mendefenisikan basisdata sebagai kumpulan
data berisi informasi yang sesuai untuk sebuah perusahaan.
3. Menurut Ramakrishnan dan Gehrke (2003) menyatakan basisdata sebagai
kumpulan data, umumnya mendeskripsikan aktivitas satu organisasi atau lebih
yang berhubungan.
4. Menurut McLeod, dkk (2001), adalah kumpulan seluruh sumber daya berbasis
komputer milik organisasi.
Dapat diambil kesimpulan bahwa database merupakan sekumpulan data
yang saling terintegrasi satu sama lain dan terorganisasi berdasarkan sebuah
skema atau struktur tertentu dan tersimpan pada sebuah hardware komputer.
B. Mengenal SQL Server 2000
SQL Server 2000 merupakan salah satu produk DBMS yang dibuat oleh
Microsoft. Selain Microsoft SQL Server 2000, produk DBMS Microsoft yang lain
adalah Microsoft Acces yang di includekan dalam paket Microsoft Office
sehingga versi DBMS Microsoft Access menyesuaikan versi MS.Office yang ada.
Saat ini versi MS. Access yang terbau adalah versi MS. Acces 2010 sama halnya
produk SQL Server yang terbaru adalah SQL Server 2010 yang baru saja dirilis.
SQL Server 2000 menawarkan beberapa fitur didalam mengelola database
yang terdapat didalam SQL Server 2000. Ada 2 Fitur yang biasa digunakan untuk
mengelola database didalam SQL Server 2000, yaitu :
1. Menggunakan Enterprise Manager (Berbasis GUI)
2. Menggunakan SQL Query Anlyzer (Berbasis Sintaks SQL)
| BASIS DATA - TEKNIK INFORMATIKA 2012 7
C. Menjalankan Service Pada SQL Server 2005
1. Klik Start Menu – Run – Services.msc – Klik Kanan Pada SQL Server
(SQLEXPRESS) services -Start
D. Membuka Query Anlyzer Pada SQL Server 2000 / 2005
1. Start Menu – All Programs – Microsoft SQL Server 2005 –SQL Server
Mangement Studio Express
| BASIS DATA - TEKNIK INFORMATIKA 2012 8
2. Pilih Windows Authentification - Connect
3. Pilih New Query
| BASIS DATA - TEKNIK INFORMATIKA 2012 9
4. Tampilan SQL Query telah berhasil dibuka.
| BASIS DATA - TEKNIK INFORMATIKA 2012 10
BAB IIIPEMBAHASAN
A. Perintah Dasar T-SQL
1. DML (Data Manipulation Langguage)
CREATE
Sintaks:
CREATE DATABASE NAMA_DATABASE
–-Membuat Database dengan nama DBS_BUKU
USE NAMA_DATABASE
-–Menggunakan DBS_BUKU
CREATE TABLE NAMA_TABEL (NAMA_KOLOM_1 TIPE_DATA(NILAI) ([NULL|NOT NULL),NAMA_KOLOM_2 TIPE_DATA(NILAI) ([NULL|NOT NULL),..............................................NAMA_KOLOM_n TIPE_DATA(NILAI) ([NULL|NOT NULL)PRIMARY KEY (NAMA_KOLOM))
ALTER
Sintaks:
ALTER TABLE NAMA_TABEL{(ALTER COLUMN NAMA_KOLOM TYPE_DATA(NILAI))}
ALTER TABLE NAMA_TABEL{(ADD NAMA_KOLOM TYPE_DATA(NILAI))}
ALTER TABLE NAMA_TABEL{(ADD UNIQUE NAMA_KOLOM)}
DROP
Sintaks:
ALTER TABLE NAMA_TABEL DROP CONSTRAINT NAMA_CONSTRAINT
--Merubah struktur TBL_BUKU sekaligus menghapus Constraint dengan -----nama CK_TOTAL
ALTER TABLE NAMA_TABEL DROP COLUMN NAMA_KOLOM
--Merubah struktur TBL_BUKU sekaligus menghapus kolom TOTAL
DROP TABLE NAMA_TABEL
---Menghapus TBL_BUKU
| BASIS DATA - TEKNIK INFORMATIKA 2012 11
DROP DATABASE NAMA_DATABASE
---Menghapus DBS_BUKU
GRANT
Seorang user yang telah membuat suatu objek database dapat memberikan hak akses kepada user yang lain. Statement dalam T-SQL yang dapat digunakan untuk memberikan hak akses adalah dengan Statement GRANT.
Syntax dari Statement GRANT secara lengkap adalah sebagai berikut:
1
2
3
GRANT { ALL | Statement [,…n] }
To user_account {,…n}
Object permissions :
Keterangan : GRANT adalah statamen untuk menspesifikasikan pemberian hak akses. ALL adalah menspesifikasikan bahwa semua hak akses diberikan. Statement adalah berisi Statement hak akses apa saja yang diberikan.
Statement ini merupakan Statement hak akses yang telah disebutkan pada pembahasan sebelumnya. Statement yang lebih dari satu dipisahkan dengan tanda koma.
TO adalah keyword untuk menspesifikasikan kepada siapa hak aksestersebut diberikan.
User_Account adalah nama user yang diberi hak akses.
Hak akses dapat diberikan menggunakan keyword ALL, jika semua hak akses
akan diberikan. Tetapi jika hanya beberapa Statement hak akses saja, Anda dapat
menyebutkan langsung Statement hak akses tersebut.
01
02
03
04
05
GRANT
{ ALL [ Privileges] | Permission [ ,…n]
}
{
[ ( Column [ ,…n] ) ] ON { Table |
| BASIS DATA - TEKNIK INFORMATIKA 2012 12
06
07
08
09
10
11
View}
| ON { Table | View } [ ( Column [ ,…n
] ) ]
| ON {Stored_Procedure | Extended_Procedure
}
| ON { User_Defined_Function}
Keterangan : Privileges adalah keyword opsional yang dapat Anda sertakan pada SQL-92 Permission adalah hak akses yang diberikan. Daftar hak akses telah
dijelaskan pada bagian sebelumnya. Jika hak akses yang diberikan lebihdari satu maka dipisahkan dengan tanda koma. Hak akses ini disertai dengan nama-nama objek database yang diberikan. Objek database tersebut dapat berupa tabel, view, stored procedure, extended procedure, dan lain-lain.
Column adalah nama-nama kolom yang diijinkan sesuai dengan hak akses yang diberikan pada daftar hak akses tersebut.
Table adalah nama-nama tabel yang diijinkan untuk diakses. View adalah nama-nama view yang diijinkan untuk diakses. Stored procedure adalah nama-nama stored procedure yang diijinkan untuk
dieksekusi (dijalankan). Extended procedure adalah nama-nama extended prosedur yang
diijinkan untuk dijalankan. User Defined Function adalah nama-nama user defined function (fungsi) yang
diijinkan untuk dieksekusi. WITH GRANT OPTION adalah untuk menspesifikasikan bahwa user yang
diberi hak akses tersebut dapat memberikan hak aksesnya kepada user yang lain. AS{Group | Role } menspesifikasikan optional nama dari user account dalam
database pada group atau role untuk mengeksekusi Statement GRANT.
Untuk memahami bagaimana penggunaan syntax di atas, anda akan mencoba
beberapa contoh penggunaan dari Statement GRANT. Langkah pertama
menjalankan SQL Query Analyzer dan melakukan koneksi dengan user sa.
Setelah berhasil melakukan koneksi dan menjalankan SQL Query Analyser,
selanjutnya anda lakukan koneksi dengan user tamu melalui menu File kemudian
pilih Connect. Setelah anda melakukan koneksi dengan dua user tersebut, pada
window SQL Query Analyser akan terdapat dua buah window SQL Editor. Satu
buah window untuk user sa dan satu buah lainnya untuk user tamu.
| BASIS DATA - TEKNIK INFORMATIKA 2012 13
Setelah kedua window SQL Editor tersebut muncul, Anda dapat melakukan
eksekusi pernyataan T-SQL untuk masing-masing user. Anda dapat mengecek
apakah user tamu yang baru saja anda gunakan untuk berhubungan dengan
database sudah dapat mengakses data pada database CobaDB atau belum dengan
mengeksekusi Statement T-SQL berikut :
1
2
3
USE NAMA_DATABASE
GO
SELECT * FROM NAMA.TABEL
Ketika anda mengeksekusi pernyataan di atas, SQL Server akan menampilkan
pesan error. Hal ini terjadi karena user tamu tidak mempunyai hak akses terhadap
tabel contoh. Masukkan Statement berikut pada window SQL Editor user sa untuk
memberikan hak akses pada user tamu.
USE NAMA_DATABASE
GO
GRANT SELECT ON NAMA_TABEL TO CLIENT-PC
Setelah Anda mengeksekusi pernyataan diatas, kemudian anda mencoba
mengeksekusi Statement SELECT untuk user tamu seperti Statement
sebelumnya, maka SQL server akan mengumpulkan hasilnya.
Contoh diatas adalah contoh pemberian hak akses dengan satu hak akses. Anda
dapat memberikan beberapa hak akses sekaligus seperti yang telah disampaikan
sebelumnya dengan menuliskannya dengan dipisahkan tanda koma. Statement
berikut adalah untuk memberikan hak akses SELECT dan UPDATE untuk tabel
contoh.
1 USE NAMA_DATABASE
| BASIS DATA - TEKNIK INFORMATIKA 2012 14
2
3
GO
GRANT SELECT,UPDATE ON NAMA_TABEL TO CLIENT-PC
Setelah mengeksekusi Statement dicatat, user tamu akan mempunyai hak akses
untuk melihat dan memodifikasi data pada tabel contoh. Untuk mencoba hasil
eksekusi dari Statement di atas, Anda dapat melakukan retrieving data dengan
Statement SELECT berikut:
1
2
3
USE NAMA_DATABASE
GO
SELECT * FROM NAMA_TABEL
Selanjutnya Anda juga dapat melakukan modifikasi data pada tabel tersebut
dengan menggunakan user tamu.
Contoh-contoh diatas adalah contoh penggunaan statetemen GRANT untuk
memberikan hak akses. Selain itu, anda juga dapat memberikan hak akses
Statement, Contoh pemberian hak akses Statement adalah pada pernyataan T-SQL
berikut :
1
2
3
Use NAMA_DATABASE
GO
GRANT CREATE TABLE TO CLIENT-PC
Dengan mengeksekusi pernyataan T-SQL di atas maka user tamu akan
mempunyai hak untuk membuat tabel pada database CobaDB. Melalui window
| BASIS DATA - TEKNIK INFORMATIKA 2012 15
SQL editor user tamu Anda dapat menjalankan Statement berikut untuk membuat
tabel contoh2 kemudian mengisi tabel tersebut dengan beberapa kata.
1
2
3
USE NAMA_DATABASE
GO
CREATE TABLE TABEL_BARU ( no int, nama varchar(10))
Setelah menjalankan Statement diatas, Anda dapat memasukkan data dengan
menggunakan Statement insert. Eksekusi Statement berikut untuk memasukkan
data.
1
2
3
4
USE NAMA_DATABASE
GO
INSERT INTO TABEL_YANG_ADA VALUES (1, ‘Contoh 1’)
INSERT INTO TABEL_BARU VALUES (2, ‘Contoh 2’)
Setelah anda selesai mengeksekusi pernyataaan-pernyataan di atas, Anda dapat
mengecek hasilnya dengan menjalankan Statement SELECT.
REVOKE
Hak akses yang telah diberikan oleh seseorang uses kepada user yang lain dapat
dicabut kembali oleh user yang memberikan hak akses. Kapan saja user tersebut
ingin mencabutnya maka dengan Statement REVOKE hak akses
tersebut dapat dicabut tanpa harus konfirmasi dengan user yang bersangkutan.
Statement REVOKE menggunakan syntax yang sama dengan Statement GRANT.
Perbedaannya adalah kalau pada Statement GRANT menggunakan keyword TO
| BASIS DATA - TEKNIK INFORMATIKA 2012 16
sedangkan pada Statement REVOKE menggunakan keyword FROM. Pada bagian
sebelumnya anda telah memberikan hak akses SELECT pada tabel contoh untuk
user tamu. Statement berikut adalah Statement untuk mencabut hak akses tersebut.
1
2
3
USE NAMA_DATABASE
GO
REVOKE SELECT ON NAMA_TABEL FROM CLIENT-PC
Setelah ini anda mengeksekusi Statement di atas, maka hak akses SELECT pada
tabel mata kuliah untuk user tamu telah dicabut. Untuk membuktikannya anda
dapat menjalankan Statement SELECT pada table mata kuliah menggunakan user
tamu. SQL server akan menampilkan pesan error.
Dengan cara yang sama anda dapat menggunakan Statement REVOKE untuk
mencabut hak akses Statement. Contoh berikut adalah contoh bagaimana
mencabut hak akses Statement CREATE TABLE dari user tamu.
1
2
3
USE NAMA_DATABASE
GO
REVOKE CREATE TABLE FROM CLIENT-PC
Setelah Statement diatas dieksekusi, user tamu tidak bisa lagi membuat tabel pada
database CobaDB. Jika ini dilakukan maka SQL Server akan menampilkan pesan
error yang sama.
2. DDL (Data Manipulation Langguage)
SELECT
| BASIS DATA - TEKNIK INFORMATIKA 2012 17
Sintaks:
SELECT * FROM NAMA_TABELWHERE (KONDISI)GROUP/HAVING BY (NAMA_KOLOM)ORDER BY NAMA_KOLOM (ASC/DESC)
INSERT
Sintaks:
INSERT INTO NAMA_TABEL(KOLOM_1,KOLOM_2,...KOLOM_N)VALUES (NILAI_1,NILAI2,.....NILAI_N)
ATAU
INSERT INTO NAMA_TABELVALUES (NILAI_1,NILAI2,.....NILAI_N)
ATAU
INSERT INTO NAMA_TABEL_TUJUAN (KOLOM_1,KOLOM_2,KOLOM_3) SELECT KOLOM_SUMBER1, KOLOM_SUMBER2, KOLOM_SUMBER3) FROM NAMA_TABEL_SUMBER
UPDATE
Sintaks:
UPDATE NAMA_TABELSET NAMA_KOLOM = (NILAI_BARU)WHERE NAMA_KOLOM = (KOLOM_YANG_DIUPDATE)
DELETE
Sintaks:
DELETE FROM NAMA_TABELWHERE (KONDISI)
COMMIT / ROLLBACKDigunakan untuk mengeksekusi/membatalkan perintah yang baru saja dilakukan apabila kondisi telah terpenuhi, contohnya didalam procedure berikut ini:
Sintaks:
CREATE PROCEDURE NAMA_PROCEDURE@ PARAMETER_PROCEDURE@ PARAMETER_PROCEDURE
AS
| BASIS DATA - TEKNIK INFORMATIKA 2012 18
BEGIN TRANSACTIONUPDATE NAMA_TABEL SET NAMA_KOLOM = (NILAI_BARU)WHERE (KONDISI)
IF @@ERROR <> 0BEGIN
ROLLBACK TRANSACTIONEND
ELSEBEGIN
COMMIT TRANSACTIONEND
GO
B. Fungsi Aggregate Pada T-SQL
1. SUM
Sintaks:
SELECT SUM(NAMA_KOLOM) AS ‘NAMA_KOLOM_BARU’ FROM NAMA_TABEL
2. AVG
Sintaks:
SELECT AVG(NAMA_KOLOM) AS ‘NAMA_KOLOM_BARU’ FROM NAMA_TABEL
3. MAX
Sintaks:
SELECT MAX(NAMA_KOLOM) AS ‘NAMA_KOLOM_BARU’ FROM NAMA_TABEL
4. MIN
Sintaks:
SELECT MIN(NAMA_KOLOM) AS ‘NAMA_KOLOM_BARU’ FROM NAMA_TABEL
5. COUNT
Sintaks:
SELECT COUNT(NAMA_KOLOM) AS ‘NAMA_KOLOM_BARU’ FROM NAMA_TABEL
C. Fungsi Built-in Pada T-SQL
1. DATEPART
| BASIS DATA - TEKNIK INFORMATIKA 2012 19
2. STRING
| BASIS DATA - TEKNIK INFORMATIKA 2012 20
D. Constraint di Microsoft SQL Server
Constraint atau batasan adalah suatu deklarasi atau perintah yang menyatakan
nilai – nilai yang diperbolehkan dan hubungannya dengan nilai – nilai yang lain
dalam suatu database. Penggunaan constraint yang tepat akan membuat suatu
database menjadi terjamin integritas datanya. Maka dari itu akan dibahas
constraint yang ada dalam Microsoft SQL Server 2000 beserta contoh
penggunaannya.
1. PRIMARY KEY
Primary Key disebut sebagai constraint untuk menjaga integritas data,
yaitu sebuah primary key tidak boleh mempunyai duplikat dan otomatis tidak
boleh kosong. Secara umum syntax untuk membuat sebuah primary key adalah:
1
2
CREATE TABLE Nama_Tabel (Nama_Field Tipedata CONSTRAINT
pk_Namatabel PRIMARY KEY, … )
Atau jika primary key terdiri atas banyak kolom (multiple column), maka
constraint dapat dibuat sebagai berikut:
1 CREATE TABLE NamaTabel(NamaField1 TipeData1, NamaField2
| BASIS DATA - TEKNIK INFORMATIKA 2012 21
2
3
4
TipeData2, NamaField3 TipeData3, NamaField4 TipeData4, NamaField5
TipeData5, NamaField6 TipeData6, NamaField7 TipeData7, CONSTRAINT
pk_NamaTabel PRIMARY KEY (NamaField3, NamaField2, NamaField7) )
2. NOT NULL
Jika sebuat kolom pada tabel tidak boleh kosong maka constraint NOT NULL
harus diberikan.
3. UNIQUE
UNIQUE digunakan untuk menjamin bahwa nilai kolom adalah tunggal tidak
mempunyai duplikat. Primary key dan unique keduanya adalah kunci kata yang
mempunyai arti sama. Microsoft SQL Server mengijinkan field dengan constraint
unique untuk mempunyai nilai NULL. Bila satu kolom mempunyai nilai NULL,
maka nilai NULL tidak boleh muncul lagi. Primary key sebaliknya tidak boleh
kosong karena primary key secara implisit adalah NOT NULL.
4. CHECK
CHECK digunakan untuk menjamin bahwa nilai kolom dalah dalam ruang nilai
tertentu. Misalnya nilai mata kuliah dibatasi tidak boleh melebihi nilai 100.
Operator logika seperti AND, OR, dan NOT dapat disertakan dalam pembuatan
batasan ini.
5. FOREIGN KEY
FOREIGN KEY adalah kolom pada sebuah tabel yang menunjukkan bahwa
kolom tersebut adalah primary key pada tabel lain. Penulisan constraint
foreignkey adalah :
1
2
CONSTRAINT fk_nama_constraint FOREIGN KEY field REFERENCES
nama_tabel(field)
Foreign key harus menunjuk ke primary key atau unique pada tabel lain.
| BASIS DATA - TEKNIK INFORMATIKA 2012 22
6. DEFAULT
Nilai default sebuah kolom dapat disuplai oleh system, jika pemakai tidak
memberikan nilai pada field tersebut. DEFAULT tidak berlaku untuk kolom
dengan tipe data timestamp atau field yang mempunyai property IDENTITY.
7. IDENTITY
IDENTITY merupakan property sebuah field, yaitu system menciptakan nomor
urut secara otomatis pada setiap memasukkan recordbaru.
Penggunaan:
NamaKolom Tipe IDENTITY(No_awal, nilai_increment)
Setiap terjadi INSERT, maka kolom akan mendapatkan nomor urut, yang
dimulai dari No_awal, dan setiap record berikutnya ditambahkan nilai increment
(default adalah 1).
CREATE TABLE pendaftar(no_daftar int IDENTITY(100,2), nama char(20)
Pada awalnya no_daftar adalah 100. Untuk setiap record baru
berikutnya, no_daftar akan bernilai 102, 104, 106, dan seterusnya.
8. DROP CONSTRAINT
DROP digunakan untuk menghapus constraint yang ada.
ALTER TABLE nama_tabel DROP fk_nama_constraint
E. Index di Microsoft SQL Server
Index mempercepat pencarian data. Dengan menggunakan index ,data tidak perlu dicari dari awal hingga akhir, melainkan menggunakan algoritma pencarian yang efisien, sehingga waktu proses menjadi semakin cepat. Tanpa index, proses pemanggilan table scan terjadi, yang berarti query processor harus melalui masing-masing record dalam tabel secara indvidual untuk menemukan record yang sesuai dengan kriteria yang dipilih.
| BASIS DATA - TEKNIK INFORMATIKA 2012 23
Proses ini sama dengan mengalokasikan informasi spesifik di dalam buku. Anda dapat menggunakan daftar isi atau index untuk menemukan topik tertentu secara mudah. Tanpa itu semua, Anda harus membuka dan mencari melalui semua halaman sampai Anda menemukan yang Anda cari.Index mempunyai Overhead selain tempat memory yang lebih besar, juga reorganisasi data karena operasi INSERT dan DELETE menjadi lebih kompleks. Dengan demikian index tidak boleh digunakan secara sembarang, melainkan dengan perencanaan dan performance tuning yang baik.
Memilih kolom untuk Index
Index dipilih berdasarkan selektifitas dari query yaitu seberapa sering record diakses melalui SELECT, UPDATE atau DELETE. Selektifitas dipengaruhi oleh kriteria pencarian, yaitu bagaimana memenuhi criteria tersebut dengan mengakses atribut apa saja.
Hal-hal yang dapat dijadikan landasan untuk membuat index adalah :
Kolom yang sangat sering dicari Primary Key dan Foreign key Kolom yang diakses secara abjad Kolom yang diakses dan sering digunakan dengan JOIN. Kolom yang sering dicari berdasarkan jarak (range) atau urutan. Kolom yang sering menjadi predikat pada kunci kata “WHERE”
Hal-hal yang menyarankan untuk tidak menggunakan Index adalah : Hasil query melebihi 20% jumlah record Kolom yang mempunyai nilai valid sedikit (misalnya jenis kelamin) Panjang kolom yang besar.Untuk melihat index yang sudah ada gunakan stored procedure
sp_helpindex namatabel
dan
sp_spaceused
untuk mengetahui pemakaian disk.
Clustered Index
Dengan Clustered index secara fisik record disusun sesuai dengan susunan index. Dengan demikian hanya ada satu clustered index yang boleh didefinisikan dalam satu tabel. Primary key adalah kandidat baik untuk menjadi clustered index (index secara implicit diciptakan melalui constraint primary key). Dalam menciptakan index, maka secara default otomatis yang dipilih adalah non clustered index.Cara membuat index adalah:
| BASIS DATA - TEKNIK INFORMATIKA 2012 24
DROP INDEX CI_NIM
CREATE CLUSTERED INDEX CI_NIM ON Mahasiswa (NIM).
DROP dijalankan untuk memastikan bahwa index tersebut belum ada.
Non Clustered Index
Dalam non clustered index secara fisik susunan record tidak berpengaruh. Non clustered index menyimpan pointer yang menunjuk ke baris atau record dari tabel. Dalam sebuah tabel dapat dibuat banyak non clustered index, sejauh index tersebut dibutuhkan. Dalam syntax create index pilih NONCLUSTERED adalah default.Cara membuat non clustered index adalah:
DROP INDEX NI_Nama
CREATE NONCLUSTERED INDEX NI_Nama ON Mahasiswa (Nama)
Atau:
CREATE INDEX NI_Nama ON Mahasiswa (Nama)
Unique Index
Secara otomatis unique index diciptakan melalui constraint primary key dan unique. Sistem akan memeriksa setiap saat record baru diciptakan atau di-update.Membuat unique index secara manual adalah:
CREATE UNIQUE INDEX UI_NIM ON Mahasiswa (NIM)
F. VIEW
1. Membuat View (CREATE VIEW)Sintaks:
CREATE VIEW NAMA_VIEW (NAMA_KOLOM)AS EKSPRESI_SELECT
2. Mengubah View (ALTER VIEW)Sintaks:ALTER VIEW NAMA_VIEW EKSPRESI SELECT
3. Menghapus View (DROP VIEW)
| BASIS DATA - TEKNIK INFORMATIKA 2012 25
DROP VIEW NAMA_VIEW
G. STORED PROCEDURE
1. Procedure Tanpa Parameter
CREATE PROCEDURE NAMA_PROCEDURE AS
EKSPRESI_SELECT
2. Procedure Dengan Parameter
CREATE PROCEDURE NAMA_PROCEDURE@NAMA_PARAMETER_1 TIPE_DATA(NILAI)
ASEKSPRESI_SELECT
EXEC NAMA_PROCEDURE ‘@NAMA_PARAMETER’
3. Mengubah Stored Procedure
ALTER PROCEDURE NAMA_PROCEDURE EKSPRESI SELECT
4. Menghapus Stored Procedure
DROP PROCEDURE NAMA_PROCEDURE
H. TRIGGER
1. Membuat Trigger
CREATE TRIGGER NAMA_TRIGGER ON NAMA_TABEL | NAMA_VIEWFOR INSERT | UPDATE | DELETEAS
EKSPRESI_SELECT
2. Mengubah Stored Procedure
ALTER TRIGGER NAMA_TRIGGER ON NAMA_TABEL | NAMA_VIEWINSERT | UPDATE | DELETEAS
EKSPRESI_SELECT
3. Menghapus Stored Procedure
DROP TRIGGER TRIGGER_NAME
| BASIS DATA - TEKNIK INFORMATIKA 2012 26
I. TRANSACT SQL (BAG 2)
1. Pengenalan T-SQL
TRANSACT-SQL atau T-SQL adalah bahasa pemrograman yang dikembangkan dari SQL. Seperti diketahui SQL adalah bahasa non procedural, artinya alur program tidak seperti bahasa pemrograman biasa, melainkan melalui “request” dan “response”.
Melalui perintah SQL seseorang melakukan query atau transaksi, yang kemudian akan menerima jawaban dari Database Server berupa hasil atau ResultSet. Query dan transaksi dilakukan di bagian Client,sedangkan pemrosesan dilakukan pada Server.
Transact-SQL mengembangkan kemampuan SQL, sehingga Transact-SQL dapat melengkapi SQL dengan instruksi logic (procedural logic), yaitu program aplikasi. Hasil proses SQL-Server (ResultSet) dapat diolah lebih lanjut dengan menggunakan logic pemrograman procedural seperti Fungsi, Procedure, Loop, Case, If Then Else, dan lainnya.
2. Bentuk umum T-SQL
TRANSACT-SQL dimulai dengan deklarasi variable dan disusul dengan blok
program. Tipe dari variabel yang dapat dideklarasikan adalah bagian dari
Microsoft SQL Server seperti char, varchar, datetime, int, money, dan lainnya.
Variabel harus dideklarasikan sebelum digunakan. Nama variabel selalu dimulai
dengan karakter @. Variabel dapat diberikan nilai melalui instruksi SELECT.
DECLARE
/* deklarasi variabel */
@v1 int,
@nama varchar(30)
Keterangan:
SELECT digunakan juga untuk eksekusi aritmatika atau lainnya. Misalnya :
SELECT @v1 = @v1 – 100
| BASIS DATA - TEKNIK INFORMATIKA 2012 27
VARIABEL dapat digunakan untuk mengambil nilai dari sebuah query:
Contoh :
DECLARE @nama char(30)
SELECT @nama = nama FROM Mahasiswa
WHERE nim=’0123456’
Hasil :Depandi Enda
PRINT adalah fungsi yang menampilkan teks dan variable pada console (layar).Contoh :
PRINT @nama
PRINT “Nama yang ditemukan adalah :”
PRINT @nama
PRINT “Nama yang ditemukan adalah :”+@nama
3. Menjalankan T-SQL
DECLARE
@honor int,
@total int,
@nama varchar(30),
@instansi char(10),
DECLARE @nama char(30)
SELECT @nama = nama FROM Mahasiswa
WHERE nim=’0123456’
PRINT @nama
@tgl_hari_ini datetime,
@tgl_nanti datetime
SELECT @Berat=65
SELECT @nama=’Ricky Septiandi’
SELECT @tgl_hari_ini=getdate()
SELECT @posisi=’Asisten Dosen’
| BASIS DATA - TEKNIK INFORMATIKA 2012 28
SELECT @tgl_nanti=@tgl_hari_ini
PRINT ( ‘Berat anda adalah’)
PRINT <@Berat>
PRINT <@nama>
PRINT ( @posisi )
PRINT ( @tgl_hari_ini )PRINT ( @tgl_nanti )
Hasil :
Berat anda adalah
65
Ricky Septiandi
Asisten Dosen
MAY 7 2012 10:18AMMAY 7 2012 10:18AM
4. Variabel Global
Variabel global adalah variabel yang disiapkan oleh Microsoft SQL Server untuk memberikan informasi kepada Client. Variable global bersifat read-only. Nama variabel global diawali dengan @@.
@@error adalah bilangan bulat yang menyatakan nomor error. Jika variable tersebut tidak sama dengan 0, maka system memberikan indikasi bahwa terjadi error dan identitas error dinyatakan dalam angka.
@@cursor_rows adalah nilai balik dari cursor yang terakhir kali diolah.
@@fetch_status adalah nilai balik dari “fetch” pada cursor. Nilai 0 adalah OK.@@identity memberikan nilai otomatis setiap kali INSERT dilakukan. Nilai ini adalah identitas dari record tersebut.
@@nestlevel adalah nesting level dari stored procedures atau triggers
@@rowcount memberikan nilai balik berupa jumlah baris (rows) yang terpengaruh oleh instruksi terakhir. Setelah instruksi IF atau WHILE, maka
@@rowcount dihapus menjadi 0.@@trancount menghasilkan banyaknya transaksi aktif pada setiap koneksi@@spid adalah Server Process Id
5. Perulangan di T-SQL
| BASIS DATA - TEKNIK INFORMATIKA 2012 29
WHILE meupakan klausa perulangan atau looping yang terdapat di Transact-SQL, biasa digunakan dalam mengeksekusi satu blok program berulang-ulang sampai kondisi pada WHILE menjadi false.
WHILE kondisi
BEGIN
…
Contoh penggunaan While untuk menampilkan angka hitung mundur:
DECLARE @I int
SELECT @i=5
WHILE @i > 0
BEGIN
PRINT “ i = “+str( @i)
SELECT @i = @i - 1
END
Hasil :
i = 5
i = 4
i = 3
i = 2
i = 1
Perhatikan bahwa fungsi str() diperlukan untuk mengkonversi bilangan integer
menjadi string (teks).
6. Continue dan Break di T-SQL
| BASIS DATA - TEKNIK INFORMATIKA 2012 30
CONTINUE dan BREAK berkaitan dengan WHILE. CONTINUE melanjutkan
alur program pada pemeriksaan kondisi WHILE, sedangkan BREAK
mengakibatkan alur program keluar dari WHILE.
WHILE kondisi1
BEGIN
CONTINUE
…
IF kondisi2
BREAK
…
END
7. Return di T-SQL
RETURN akan menghentikan program dari eksekusi. RETURN dapat digunakan
dalam memproses error.
IF @@error <> 0
BEGIN
PRINT “Error…”
RETURN
END
8. If – Else dan Case
If Else pada Transact-SQL digunakan untuk eksekusi kondisi dari statement.
Statement Transact-SQL mengikuti If dan kondisinya dieksekusi bila kondisi
terpenuhi : evaluasi kondisi mengembalikan nilai True. Else dijalankan bila
kondisi If tidak dipenuhi : evaluasi kondisi mengembalikan nilai False.
IF kondisi
| BASIS DATA - TEKNIK INFORMATIKA 2012 31
Instruksi
IF kondisi
Instruksi1
ELSE
Instruksi2
IF kondisi
BEGIN
Instruksi
Case pada Transact-SQL melakukan evaluasi terhadap beberapa kondisi dan
mengembalikan satu hasil dari beberapa kemungkinan hasil yang ada. Case dapat
digunakan dalam pernyataan atau klausa apapun yang memperbolehkan ekspresi
yang valid. Contoh, Case dapat digunakan dalam pernyataan SELECT, UPDATE,
DELETE dan SET, dan di klausa seperti select_list, IN, WHERE, ORDER BY,
dan HAVING.
IF kondisi1
ELSE IF kondisi2
ELSE IF kondisi3
ELSE
Dapat diganti:
CASE WHEN kondisi1 THEN
WHEN kondisi1 THEN
WHEN kondisi1 THEN
ELSE
| BASIS DATA - TEKNIK INFORMATIKA 2012 32
KESIMPULAN
Database merupakan sekumpulan data yang saling terintegrasi satu sama
lain dan terorganisasi berdasarkan sebuah skema atau struktur tertentu dan
tersimpan pada sebuah hardware komputer.
SQL Server 2000 merupakan salah satu produk DBMS yang dibuat oleh
Microsoft.
SQL Server 2000 menawarkan beberapa fitur didalam mengelola database
yang terdapat didalam SQL Server 2000. Ada 2 Fitur yang biasa digunakan untuk
mengelola database didalam SQL Server 2000, yaitu :
1. Menggunakan Enterprise Manager (Berbasis GUI)
2. Menggunakan SQL Query Anlyzer (Berbasis Sintaks SQL Menggunakan
Transact SQL)
TRANSACT-SQL atau T-SQL adalah bahasa pemrograman yang dikembangkan dari SQL. Seperti diketahui SQL adalah bahasa non procedural, artinya alur program tidak seperti bahasa pemrograman biasa, melainkan melalui “request” dan “response”.
Melalui perintah SQL seseorang melakukan query atau transaksi, yang kemudian akan menerima jawaban dari Database Server berupa hasil atau ResultSet. Query dan transaksi dilakukan di bagian Client,sedangkan pemrosesan dilakukan pada Server.
| BASIS DATA - TEKNIK INFORMATIKA 2012 33
DAFTAR PUSTAKA
Baskoro, Fajar & Arunanto FX, 2000. Desaign dan Analisa Sistem Informasi,
PIKTI ITS-Surabaya. 2000
Microsoft Team.BOL Ms.SQL Server. 2000
C.J Date,1995. An Introduction To Database Systems : Addison Wesley
Publishing Co., Inc,
Korth and Silberschatz, 1986. Database Systems Concepts : Mc. Graw – Hill
International Co.,
Edhy Sutanta, 1996. Sistem Basis Data. Yogyakarta : Penerbit Andy
Harianto Kristanto, 1993. Konsep dan Perancangan Database. Yogyakarta :
Penerbit Andy
Fathansyah, 1999. Basis Data. Bandung : Penerbit Informatika
Halaman Web :
http://www.IlmuKomputer.com
| BASIS DATA - TEKNIK INFORMATIKA 2012 34