Diktat Basic Database

Diktat Basic Database
September 2011
For the latest information, please see bluejack.binus.ac.id
i|Page
Information in this document, including URL and other Internet Web site references, is
subject to change without notice. This document supports a preliminary release of software
that may be changed substantially prior to final commercial release, and is the proprietary
information of Binus University.
This document is for informational purposes only.
BINUS UNIVERSITY MAKES NO
WARRANTIES, EITHER EXPRESS OR IMPLIED, AS TO THE INFORMATION IN THIS
DOCUMENT.
The entire risk of the use or the results from the use of this document remains with the
user. Complying with all applicable copyright laws is the responsibility of the user. Without
limiting the rights under copyright, no part of this document may be reproduced, stored in
or introduced into a retrieval system, or transmitted in any form or by any means
(electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without
the express written permission of Binus University.
Binus Universitymayhave patents, patent applications, trademarks, copyrights, or other
intellectual property rights coveringsubject matter in this document. Except as expressly
provided in any written license agreement from Binus University, the furnishing of this
document does not give you any license to these patents, trademarks, copyrights, or other
intellectual property.
Unless otherwise noted, the example companies, organizations, products, domain names, email addresses, logos, people, places and events depicted herein are fictitious, and no
association with any real company, organization, product, domain name, email address,
logo, person, place or event is intended or should be inferred.
© 2011 Binus University. All rights reserved.
The names of actual companies and products mentioned herein may be the trademarks of
their respective owners.
ii | P a g e
Table of Contents
OVERVIEW ..................................................................................................... iii
Chapter 01 Introduction to SQL.......................................................................... 1
Chapter 02 Data Definition Language ................................................................ 21
Chapter 03 Data Manipulation Language Basic ................................................... 40
Chapter 04 Data Manipulation Language Intermediate ........................................ 49
Chapter 05 Data Manipulation Language Advanced ............................................. 60
Chapter 06 Data Control Language ................................................................... 74
Chapter 07 Advanced Query ............................................................................ 97
Chapter 08 Database Design ......................................................................... 123
iii | P a g e
OVERVIEW
Chapter 01
•
Introduction to SQL
Chapter 02
•
Data Definition Language
Chapter 03
•
Data Manipulation Languange Basic
Chapter 04
•
Data Manipulation Languange Intermediate
Chapter 05
•
Data Manipulation Languange Advance
Chapter 06

Data Control Language
Chapter 07
•
Advanced Query
Chapter 08
•
Database Design
1|Page
Chapter 01
Introduction to SQL
Objectives
1. Introduction To Database
2. Introduction To Sql Server
3. Database (Diagram, Table, View, Stored Procedure, Users, Roles)
4. Attach & Detach Database
5. Import & Export Data
6. Backup & Restore Database
7. Query Execution
Learning Outcomes
1. Describe database systems, terminology, environment, and new concept of database
2.
Apply database language and SQL Programming language
2|Page
1.1.
Introduction To Database
Database atau basis data merupakan bagian sangat penting dalam sebuah proses
pengolahan data. Basis data adalah koleksi data yang saling berhubungan dan memiliki arti dan
terorganisasi secara rapi. Data tersebut harus dapat diakses dengan urutan yang berbeda-beda
secara logis dengan cara yang relatif mudah.
Suatu sistem basis data terdiri dari empat komponen yaitu data, yang secara fisik
menyimpan informasi-informasi, Database Management System (DBMS) yaitu software yang
mengelola basis data, Data Definition language (DDL) dan Data Manipulation Language
(DML) yaitu basis data yang berfungsi untuk mendeskripsikan data ke database management
system (DBMS) dan juga member fasilitas untuk melakukan perubahan, pemeliharaan, dan
pengelolaan basis data, dan program aplikasi yang memudahkan pengguna akhir untuk
menggunakan data dan mendapatkannya sebagai informasi yang sesuai.
1.2.
Introduction To Sql Server
Microsoft SQL Server adalah Relational Database Management System (RDBMS)
yang dirancang untuk dapat berjalan pada platform mulai dari laptop hingga server multiprosesor
besar. SQL Server biasanya digunakan sebagai sistem backend untuk website dan CRMS
perusahaan dan dapat membantu ribuan pengguna secara bersamaan.
SQL Server hadir dengan sejumlah alat untuk membantu administrasi database dan
tugas-tugas pemrograman. SQL Server jauh lebih kuat dan terukur daripada sistem manajemen
database desktop seperti Microsoft Access. Penggunaan Access sebagai database pada website
umumnya menghasilkan error yang lebih banyak dibanding SQL Server.
Meskipun SQL Server juga dapat dijalankan sebagai sebuah sistem database desktop,
SQL Server paling sering digunakan sebagai sistem database server.
Server Database System (Server berbasis sistem database)
Server berbasis sistem database merupakan database yang dirancang untuk berjalan pada
server pusat, sehingga beberapa pengguna dapat mengakses data yang sama secara bersamaan.
Para pengguna biasanya mengakses database melalui aplikasi.
Sebagai contoh, sebuah website dapat menyimpan semua konten di database. Setiap kali
sebuah artikel dibaca pengunjung, mereka mengambil data dari database. Seperti yang Anda
ketahui, situs web biasanya tidak terbatas hanya pada satu pengguna. Jadi, pada suatu saat
3|Page
tertentu, sebuah website bisa melayani sampai ratusan, atau bahkan ribuan artikel untuk
pengunjung website. Pada saat yang sama, pengguna lain dapat memperbarui profil pribadi
mereka pada menu anggota, atau berlangganan newsletter, atau apapun yang pengguna situs web
lakukan.
1.3.
Database (Diagram, Table, View, Stored Procedure, Users, Roles)
Diagram
Database Diagram adalah alat sebuah visual yang memungkinkan untuk merancang dan
memvisualisasikan database-database yang berhubungan. Ketika merancang database, Anda
dapat menggunakan Database Diagram untuk membuat, mengedit, atau menghapus tables,
columns, keys, indexes, relationships, and constraints. Untuk memvisualisasikan database,
Anda dapat membuat satu atau lebih diagram yang menggambarkan sebagian atau semua tables,
columns, keys, dan hubungan di dalamnya.
Untuk database apapun, Anda bisa membuat diagram database yang diinginkan. Setiap
tabel database dapat muncul pada sejumlah diagram. Dengan demikian, Anda dapat membuat
diagram yang berbeda untuk memvisualisasikan bagian-bagian yang berbeda dari database, atau
untuk menekankan aspek yang berbeda dari desain. Sebagai contoh, Anda dapat membuat
diagram besar yang menampilkan semua tabel dan kolom. Anda dapat membuat diagram yang
lebih kecil yang menunjukkan semua tabel tanpa menunjukkan kolom. Setiap diagram database
yang Anda buat disimpan dalam database terkait.
Table
Fondasi dari setiap Relational Database Management System adalah objek database yang
disebut table. Setiap database terdiri dari satu atau lebih tabel, yang menyimpan data / informasi
database. Setiap tabel memiliki nama yang unik dan terdiri dari kolom dan baris. Setiap tabel
terdiri dari serangkaian kolom yang berisi informasi tentang jenis data yang tersimpan dalam
tabel. Setiap kolom harus diberi nama, tipe data, dan panjang. Tabel A juga dapat memiliki satu
set foreign key, key constraint, rules dan index. Kolom, index, constraint, dan trigger pada
suatu tabel akan dianggap properti dari tabel dalam database diagram.
Kolom-kolom pada tabel database (disebut juga field tabel) memiliki nama-nama mereka
sendiri yang unik dan memiliki tipe data yang telah didefinisikan sebelumnya. Kolom-kolom
pada tabel juga dapat memiliki atribut yang mendefinisikan fungsi kolom tersebut (kolom
4|Page
sebagai primary key, kolom tidak memiliki indeks yang didefinisikan pada kolom tersebut,
kolom memiliki nilai default tertentu, dan sebagainya).
Jika kolom pada tabel menggambarkan tipe data, baris pada tabel berisi data aktual untuk
kolom tersebut.
View
Data pada SQL Server disimpan dalam bentuk tabel dan Anda dapat melakukan
pengolahan data secara fisik pada tabel tersebut. Begitu juga dengan View, View dapat juga
dipakai untuk pemanggilan data. Namun view tidak menyimpan data secara fisik pada disk.View
adalah salah satu objek database yang merupakan turunan dari satu atau lebih tabel.
Dalam SQL Server,View merupakan tabel virtual. Sama seperti tabel sebenarnya, View
terdiri dari baris dan kolom, dan Anda dapat mengambil data dari View (kadang-kadang bahkan
update data dalam sebuah View). Field dalam tabel virtual View adalah bidang dari satu atau
lebih tabel nyata dalam database.
Anda dapat menggunakan View untuk menyajikan data yang diambil dari dua tabel yang
digabungkan, namun seolah-olah berasal dari satu tabel sehingga menyederhanakan skema dari
database Anda bagi pengguna melakukan laporan ad-hoc.
Anda juga dapat menggunakan View sebagai mekanisme keamanan untuk membatasi
data yang tersedia bagi pengguna akhir. View juga dapat berupa data agregat (sangat berguna jika
Anda dapat mengambil keuntungan dari indexed views), dan membantu data partisi.
Kegunaan View:
-
Untuk menyembunyikan kompleksitas dalam skema database, atau menyesuaikan data dan
skema untuk satu set pengguna.
-
Untuk mengontrol akses ke baris dan kolom data.
-
Untuk menampilkan hasil perhitungan data.
-
Untuk membuat laporan.
Stored Procedure
Stored Procedure adalah sekelompok pernyataan Transact-SQL yang dikompilasi
menjadi rencana eksekusi tunggal. Prosedur yang disimpan sangat membantu dalam mencapai
implementasi yang konsisten dari logika di seluruh aplikasi. Statement dalam SQL dan logika
5|Page
yang diperlukan untuk melakukan tugas dapat dirancang, dikode, dan diuji sekali dalam prosedur
yang tersimpan. Koding logika bisnis menjadi prosedur yang tersimpan tunggal juga
menawarkan satu titik kontrol untuk memastikan bahwa aturan-aturan bisnis dengan benar
ditegakkan.
Manfaat menggunakan Stored Procedure dalam SQL Server adalah:
-
memungkinkan pemrograman modular
-
eksekusi lebih cepat
-
mengurangi network traffic
-
dapat digunakan sebagai mekanisme keamanan
Users
Untuk dapat terhubung ke sebuah instance SQL Server, pertama anda harus login terlebih
dahulu sehingga instance dapat mengotentikasi Anda (tahu siapa Anda). Tetapi untuk melakukan
operasi pada database, Anda memerlukan User account dalam database tersebut. User account
digunakan untuk mengakses database. Jadi anda bisa login, tapi jika Anda tidak memiliki akun
user yang berhubungan untuk login itu, Anda tidak akan dapat mengakses database tersebut.
SQL Server memungkinkan untuk membuat login bagi pengguna. Setiap individu yang
membutuhkan akses ke SQL Server dapat diberikan user account mereka sendiri.
Ketika administrator mengkonfigurasi user login ini, ia dapat menetapkan mereka ke
sejumlah peran dan skema, tergantung pada akses individu yang berhak.
Roles
Ketika membuat login user baru di SQL Server, Anda dapat memilih untuk menempatkan
user baru ini ke satu atau lebih Server Roles(peran) pada server.
Server Roles (jangan dikelirukan dengan peran database) tersedia untuk melakukan
berbagai tugas administrasi dalam database. Tidak semua orang bisa mendapatkan server role.
Pada kenyataannya, hanya pengguna seperti database administrator yang diberikan server role.
Beberapa peran dalam SQL Server sys admin, server admin, setup admin, security
admin, process admin, db creator, disk admin, bulk admin, public.
6|Page
1.4.
Attach & Detach Database
File-file log data dan transaksi database dapat dilepaskan(Detach) dan kemudian
disambungkan(Attach) ke instance yang sama atau beda dalam SQL Server. Attach dan Detach
database berguna jika Anda ingin mengubah database pada instance yang berbeda dari SQL
Server pada komputer yang sama atau untuk memindahkan database.
Cara Attach Database
Klik kanan pada folder database klik Attach.
Setelah kotak dialog Attach Database tampil, pilih Add.
7|Page
Tentukan file database yang akan di Attach, setelah itu klik OK.
8|Page
Cara Detach Database
Klik kanan di database yang akan di detach, setelah itu klik Task, lalu klik Detach.
9|Page
Lalu klik OK.
1.5.
Import & Export Data
Import dan Export data digunakan agar dapat menggunakan data yang dihasilkan oleh
aplikasi lain. Kemampuan untuk mengimpor data sangat penting dalam aplikasi perangkat lunak
karena itu berarti bahwa satu aplikasi dapat melengkapi yang lain. Banyak program, misalnya,
dirancang untuk dapat mengimpor grafis dalam berbagai format.
Kebalikan dari mengimpor adalah ekspor, yang mengacu pada kemampuan satu aplikasi
agar dapat dipakai sesuai format data untuk aplikasi lain.
10 | P a g e
Cara Import Data
Klik kanan di database klik Tasks, lalu klik Import Data.
11 | P a g e
Cara Export Data
Klik kanan di database klik Tasks, lalu klik Export Data.
1.6.
Backup & Restore Database
Salinan dari sebuah file atau direktori pada perangkat penyimpanan terpisah dapat kita
buatkanbackup jadi jika sewaktu-waktu data yang original rusak atau terhapus, masih bisa
digunakan salinannya. Proses backup mengacu pada pembuatan salinan data sehingga salinan ini
dapat digunakan untuk mengembalikan data semula setelah peristiwa kehilangan data.
12 | P a g e
Terdapat dua tujuan utama dalam backup. Yang pertama adalah untuk memulihkan
keadaan setelah terjadi bencana (disaster recover). Yang kedua adalah untuk mengembalikan
sejumlah file kecil setelah secera sengajaterhapus atau rusak. Kehilangan data merupakan hal
sangat umum, 66% dari pengguna internet telah mengalami kehilangan data yang serius.
Restore berguna untuk mengembalikan ke dalam keadaan semula dan membuat restitusi.
Backup dan Restore adalah adalah proses menyalin data terlebih dahulu dengan tujuan spesifik
untuk mengembalikan data yang sama setelah suatu peristiwa yang dapat mengakibatkan
hilangnya data.
Cara Backup Database
Klik kanan di database klik Tasks, lalu klik Back Up.
13 | P a g e
Lalu setelah kotak dialog Back Up Database muncul, klik Add untuk menambahkan alamat
kemana hasil backup akan disimpan.
Kemudian akan muncul kotak dialog Select Backup Destination, lalu tentukan kemana hasil back
up akan disimpan, lalu klik OK.
14 | P a g e
15 | P a g e
Cara Restore Database
Klik kanan di database klik Tasks, lalu klik Restore Database.
16 | P a g e
Lalu setelah kotak dialog Restore Database muncul, tentukan tujuan database yang akan di
restore lalu pilih From device untuk menentukan lokasi file yang mau di restore.
Lalu akan muncul kotak dialog Specify Backup.
17 | P a g e
Setelah itu pilihlah file yang akan di restore, kemudian klik OK.
18 | P a g e
1.7.
Query Execution
Apa yang sebenarnya membuat SQL Server berbeda dari bahasa pemrograman lain
adalah cara SQL Server memproses kodenya. Umumnya, kebanyakan bahasa pemrograman
memroses statement dari atas ke bawah. Sebaliknya, SQL Server memroses dalam urutan yang
unik yang dikenal sebagai Logical Tahap Pengolahan Query. Fase ini menghasilkan serangkaian
tabel-tabel virtual dengan masing-masing tabel virtual ke tahap berikutnya (tabel virtual tidak
dapat dilihat). Fase dan perintah yang diberikan adalah sebagai berikut:
1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. ORDER BY
6. HAVING
7. CUBE | ROLLUP
8. ON
19 | P a g e
9. DISTINCT
10. OUTER
11. TOP
SQL Query Analyzer adalah antarmuka utama untuk menjalankan query SQL terhadap
database Anda. Anda dapat menggunakan SQL Query Analyzer untuk membuat dan
menjalankan script adhoc, atau anda dapat membuat skrip SQL dan menyimpannya untuk
digunakan.
Pada dasarnya kalau kita bekerja dengan Microsoft SQL Server kita harus masuk atau
joint ke Server, baik kalau kita bekerja dengan SQL Enterprise Manager maupun dengan SQL
Query Analyzer. Hanya kalau kita masuk melalui SQL Enterprise Manager kita bisa langsung
menggunakan fasilitas yang ada seperti membuka database, membuat database baru, membuat
dan membuka tabel, dan lain-lain. Tetapi apabila Anda bekerja di lingkungan Query Analyzer
Anda harus menggunakan Transact-SQL baik untuk membuat database, tabel, maupun yang
lainnya.
Langkah :
1. KlikNew Query
2. Kemudian masukan query yang ingin di jalankan  blog query klik Execute
20 | P a g e
21 | P a g e
Chapter 02
Data Definition Language
Objectives
1. Create & Drop Database
2. Create & Drop Table
3. Alter Statement
4. Create & Drop Index
5. Create & Drop View
Learning Outcomes
1. Apply database language and SQL Programming language
22 | P a g e
2.1.
Create & Drop Database

Create Database
Untuk membuat database baru.
Langkah :
1.
2.
Pada bagian Object Explorer, pilih folder Database
Klik kanan pada folder database dan pilih New Database
23 | P a g e
3.
Isi nama database dan klik OK
24 | P a g e

Drop Database
Untuk menghapus database.
Langkah :
1. Pada bagian Object Explorer, klik kanan pada folder Database yang ingin dihapus dan
pilih Delete
25 | P a g e
2. Klik OK pada window Delete Object
2.2.
Create & Drop Table

Create Table
Untuk membuat tabel dalam database.
Langkah :
1. Pada bagian Object Explorer, expand Database kemudian pada bagian Tables klik kanan
pilih new table
26 | P a g e
2.
Isi nama kolom dan tipe datanya Kemudian tekan ctrl+ s dan isi nama tabelnya
27 | P a g e
Sintaks :
CREATE TABLE [ table_name ]
(
[ column 1 ] [ data_type_for_column_1 ] ,
[ column 2 ] [ data_type_for_column_2 ] ,
...
)
Contoh :
Nama Tabel : MsCustomer
Nama Field
Tipe Data
Length
CustomerId
char
5
Name
varchar
30
Address
varchar
50
Phone
varchar
15
Constraint Statement
Constraint merupakan sebuah batasan yang digunakan untuk memberikan
properti khusus yang memberikan integritas data dan membuat indeks-indeks pada tabel dan
kolom yang di tunjuk. Constraint berguna untuk penerapan sebuah ketentuan pada table dan
untuk mencegah penghapusan table jika terdapat ketentuan-ketentuan yang telah dibuat.
28 | P a g e
Macam-macam Constraint
 NOT NULL
Constraint ini berguna untuk memberlakukan sebuah column untuk tidak menerima nilai
NULL.
 UNIQUE
Constraint ini berguna untuk mengidentifikasikan keunikan setiap record sebuah table
dalam database.
 DEFAULT
Digunakan untuk memasukkan nilai default kedalam sebuah kolom yang ditunjuk pada
table. Nilai default akan ditambahkan ke dalam semua record baru, jika tidak ada nilai lain
yang ditentukan.
 CHECK
Digunakan untukmembatasirentangnilaiyangdapatditempatkandalam sebuah kolom pada
table.
29 | P a g e
 PRIMARY KEY
Constraint ini berguna untuk mengidentifikasikan keunikan setiap record sebuah table
dalam database. Primary Key harus mengandung nilai keunikan dan tidak dapat menerima
nilai NULL.
 FOREIGN KEY
Foreign Key merupakan sebuah kumpulan field dalam satu relasi yang digunakan
untuk menunjuk ke suatu baris (tuple) pada relasi yang lain (harus berkorespondensi
dengan primary key pada relasi yang kedua).
MsCustomer
PK
TrHeaderOrder
CustomerId
CustomerName
CustomerAddress
CustomerPhone
PK
OrderId
FK1
CustomerId
OrderDate
Nama Tabel : TrHeaderOrder
PrimaryKey : OrderId
ForeignKey : CustomerId
Nama
Tipe
Field
Data
OrderId
char
Length
5
Keterangan
Harus diisi dan panjang = 5, harus
diawali dengan „OD‟ dan 3 digit
terakhirnya berupa angka
Contoh Format : OD[0-9][0-9][0-9]
CustomerId
char
5
Apabila nilainya diubah atau dihapus di
MsCustomer, data di TrHeaderOrder
juga ikut berubah
OrderDate
datetime
Tidak boleh NULL
30 | P a g e
Referential Integrity
Referential Integrity digunakan untuk menjaga konsistensi baris-baris data antara dua
buah tabel. Aturan ini mengharuskan sebuah baris pada sebuah tabel yang terelasikan pada
tabel lain harus mengacu pada sebuah baris di dalam tabel tersebut. Aturan untuk referential
integrity biasanya digunakan jika terjadi proses modifikasi data.Hubungan referential
integriry didefinisikandenganForeign KeydanklausaPrimary Keydalam perintah Create
Table.
Saat kita melakukan on update atau on delete kita bisa menambahkan pilihan sebagai berikut
:

NO ACTION
Jika kita melakukan update pada primary key yang ditunjuk atau delete pada record
yang ditunjuk maka tidak melakukan reaksi apa pun.

CASCADE
Jika kita melakukan update pada primary key yang ditunjuk, maka foreign key akan
ikut ter-update. Jika kita melakukan delete pada record yang ditunjuk maka recordrecord dari foreign key akan ikut ter-delete.

SET NULL
Jika kita melakukan update pada primary key yang ditunjuk atau delete pada record
yang ditunjuk maka foreign key akan berisi nilai null.
31 | P a g e

SET DEFAULT
Jika kita melakukan update pada primary key yang ditunjuk atau delete pada record
yang ditunjuk maka foreign key akan berisi nilai default
MsStudent
PK
StudentId
StudentName
StudentAddress
PhoneNumber
Major
TrHeaderBorrowing
PK
BorrowId
FK1
StudentId
BorrowDate
TrDetailBorrowing
PK,FK1
PK,FK2
BorrowId
BookId
Qty
Nama Table
: TrDetailBorrowing
PrimaryKey,ForeignKey
: BorrowId, BookId
Catatan
%
mewakili banyak character atau tidak ada karakter
_
mewakili satu character
[]
range
MsBook
PK
BookId
BookName
Author
Publisher
32 | P a g e

Drop Table
Untuk menghapus table dalam database
Langkah :
1. Pada bagian Object Explorer, expand Database kemudian expand Tables lalu klik kanan
pada table yang mau dihapus lalu pilih Delete
33 | P a g e
2. Klik OK pada window Delete Object
Sintaks :
DROP TABLE [ table_name ]
Contoh :
Catatan
Jika ada tabel yang ber relasi, drop tabel yang mengandung fereignkey dulu baru
yang mengandung primary key
34 | P a g e
2.3.
Alter Statement
Alter Statement merupakan sebuah syntax sqlyang dapat membuat perubahan pada
definisi tabel dalam sql.
Alter Statement memungkinkan kita untuk :

Menambahkan column baru pada table

Menghapus sebuah column yang ada pada table

Mengubah tipe data dan meningkatkan size dari tipe data pada column dalam table

Menambahkan sebuah constraint pada table

Menghapus sebuah constraint yang ada pada table

Memberikan atau menghapus nilai default pada sebuah column dalam table
Sintaks
1.
Add new column
ALTER TABLE [table_name]
ADD [column_name] [datatype]
Contoh :
2.
Drop column from table
ALTER TABLE [table_name]
DROP COLUMN [colun_name]
Contoh :
3.
Change the data type of a column in a table
ALTER TABLE [table_name]
ALTER COLUMN [column_name][datatype]
35 | P a g e
Contoh :
4.
Add Constraint from table
ALTER TABLE [table_name]
ADD CONSTRAINT [constraint_name] CHECK
[colum check]
Contoh :
5.
Drop Constraint from table
ALTER TABLE [table_name]
DROP CONSTRAINT [constraint_name]
Contoh :
6.
Change default value for a column
Set default
ALTER TABLE [table_name]
ADD CONSTRAINT [constraint_name] DEFAULT
[default_value] FOR [column_name]
Contoh :
36 | P a g e
2.4.
Create & Drop Index

Create Index
Indeks dalam database yang sangat mirip dengan indeks di perpustakaan. Indeks
memungkinkan menemukan informasi dalam database yang cepat, sama seperti yang mereka
lakukan di perpustakaan. Jika semua buku di perpustakaan diindeks alfabet maka Anda tidak
perlu menelusuri seluruh perpustakaan untuk menemukan buku tertentu. Sebaliknya Anda
hanya akan mendapatkan huruf pertama dari judul buku dan Anda akan menemukan bagian
ini surat dalam perpustakaan memulai pencarian anda dari sana, yang akan mempersempit
pencarian Anda secara signifikan.
Sebuah Index dapat dibuat pada satu kolom atau kombinasi kolom dalam tabel
database. Sebuah indeks tabel adalah struktur database yang mengatur nilai-nilai dari satu
atau lebih kolom dalam tabel database dalam urutan tertentu. Indeks Tabel memiliki pointer
ke nilai yang disimpan dalam kolom tertentu atau kombinasi kolom dari tabel. Pointer ini
dipesan tergantung pada urutan yang ditentukan dalam indeks.
Sintaks :
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX [ index_name ]
ON [table_name] ( [column 1] [ ASC | DESC ] [ ,...n ] )
[ INCLUDE ( column_name [ ,...n ] ) ]
[ WITH ( <relational_index_option> [ ,...n ] ) ]
[ ON { partition_scheme_name ( column_name ) | filegroup_name | default } ]
Contoh :

Drop Index
Untuk menghapus index yang ada
Sintaks :
DROP INDEX[ index_name ] ON [table_name]
37 | P a g e
Contoh :
2.5.
Create & Drop View

Create View
Sebuah view adalah tabel yang dibangun dari satu atau beberapa tabel yang sudah ada. Secara
fisik, VIEW tidak membuat penyimpanan data seperti tabel biasa, melainkan hanya
menyimpan referensi/pointer ke record pada tabel-tabel yang berkaitan. VIEW biasa disebut
juga “virtual tabel”. View dapat juga diciptakan dari beberapa tabel.
Sintaks :
CREATE VIEW [view_name] (column 1, column 2)
AS
Select column 1, column 2…
FROM [table name]
WHERE [condition]
Contoh :

Drop View
Sintaks :
DROP VIEW [view_name]
Contoh :
2.6.
Exercise
1. Buatlah sebuah database baru dengan nama Android_Database
2. Buatlah sebuah tabel dengan data sebagai berikut :
38 | P a g e
MsCustomer
PK
CustomerId
Name
Address
Phone
Nama tabel
TrHeaderSales
PK
SalesId
FK1
SalesDate
Note
CustomerId
: TrHeaderSales
Primarykey : SalesId
Foreign key : CustomerId
Nama Field
Tipe Data
Length
Keterangan
SalesId
char
5
Format nya SL[0-9][0-9][0-9]
CustomerId
char
5
Apabila nilainya diubah atau dihapus di
MsCustomer, data di TrHeaderSales ikut
berubah
SalesDate
datetime
Note
varchar
Tidak boleh NULL
100
3. Buatlah sebuah index dengan nama CustIndex pada CustomerId didalam MsCustomer
(create index)
4. Hapuslah Index CustIndex pada soal 3
(drop index)
5. Hapuslah table TrHeaderSales pada soal 2
(drop table)
6. Buatlah sebuah view dengan nama ViewInfoCustomer untuk menampilkan Name, Address
dan phone yang ada pada tabel MsCustomerdimana CustomerId adalah „CU017‟.
(create view, select)
7. Hapus view bernama ViewInfoCustomer yang telah dibuat pada soal nomor 6
(drop view)
8. Ubahlah table MsCustomer dengan menambahkan column baru yaitu column gender dengan
tipe data varchar size 20
39 | P a g e
Answer
1.
2.
3.
4.
5.
6.
7.
8.
40 | P a g e
Chapter 03
Data Manipulation Language Basic
Objectives
1. Insert
2. Update
3. Delete
4. Commit
5. Rollback
Learning Outcomes
1. Describe database systems, terminology, environment, and new concept of database
2. Apply database language and SQL Programming language
41 | P a g e
3.1.
Insert
Digunakan untuk memasukkan data-data ke dalam tabel yang sudah dibuat sebelumnya
Sintaks :
INSERT INTO [table_name]([column_1],[column_2],[column_3],…) VALUES
([value_1], [value_2], [value_3]..)
Contoh :
Masukkan data karyawan baru yang bernama Ucok dengan kode karyawan KK006, alamat di Jl.
Pisang No.33 dan TelpKaryawan 3216548.
Jawab :
3.2.
Update
Digunakan untuk melakukan perubahan data yang ada di dalam table.
Sintaks:
UPDATE [table_name] SET [column_1] = [new value], [column_2] = [new value], [column_3]
= [new value],… WHERE {condition}
Contoh :
Ubalah NamaKaryawan dengan kodeKaryawan KK002 menjadi “Indrawan S.Kom”
Jawab :
3.3.
Delete
Digunakan untuk melakukan penghapusan data yang ada di dalam table.
Sintaks:
DELETE FROM [table_name] WHERE {condition}
42 | P a g e
Contoh :
Hapuslah data pelanggan dimana NamaPelanggan mengandung kata “Lestari”.
Sebelum proses delete:
Setelah proses Delete :
Jawab :
3.4.
Commit
Digunakan untuk membuat semua perubahan data yang dilakukan sejak transaksi dimulai
menjadi permanen di database kita.
Contoh :
Hapuslah data-data pada MsGame dimana NamaGame mengandung kata “Dead”.
43 | P a g e
Jawab :
3.5.
Rollback
Digunakan untuk mengembalikan transaksi yang telah dilakukan ke awal transaksi, atau pada
suatu titik aman tertentu dalam transaksi.
Contoh:
Hapuslah data-data pada MsGame dimana NamaGame mengandung kata “moon”.
Data sebelum proses delete:
Data setelah proses delete :
44 | P a g e
Data setelah proses rollback :
Jawab :
3.6.
Exercise
1. Masukkan data karyawan baru yang bernama Wandi dengan kode karyawan KK007,
alamatKaryawan di Jl. Rambutan No.44 dan TelpKaryawan 111222.
(insert)
2. Masukkan data karyawan baru yang bernama Pandu dengan kode karyawan KK008 dan
alamatKaryawan di Jl. Cherry No.66.
(insert)
3. Ubahlah semua TelpPelanggan yang masih kosong (NULL) dengan kalimat „N/A‟.
(update, is null)
45 | P a g e
Sebelum proses update:
Setelah proses update:
4. Ubahlah Qty pada TrDetailPenjualan dengan menambah 1 pada masing-masing Qty yang
TglTransaksinya terjadi pada bulan Mei dan tahun 2009.
(update, month, year)
46 | P a g e
Sebelum proses update
Setelah proses update
47 | P a g e
5. Hapuslah data pelanggan yang tidak memiliki nilai TelpPelanggan (null).
(delete, is null)
Sebelum proses delete:
Setelah proses delete:
6. Hapuslah semua data pada MsGame dimana JenisGamenya adalah PC.
(delete)
Sebelum proses delete:
48 | P a g e
Setelah proses delete:
49 | P a g e
Chapter 04
Data Manipulation Language Intermediate
Objectives
1. Select Statement
2. SQL Syntax
3. SQL Operator
-
Operator Aritmatik
-
Operator Logika
Learning Outcomes
1. Describe database systems, terminology, environment, and new concept of database
2. Apply database language and SQL Programming language
50 | P a g e
4.1.
Select Statement
Digunakan untuk menampilkan data – data dalam table kita.
Sintaks:
SELECT [column_1],[column_2],[column_3],… FROM [table_name]
Contoh :
Tampilkan semua data pada tabel MsKaryawan.
Jawab :
4.2.
SQL Syntax
Berikut ini adalah sintak-sintak SQL yang biasa digunakan:
Sintak
Keterangan
datediff
Fungsi
untuk mendapatkan selisih tanggal.
Bentuk umum
datediff(SatuanTanggal,Tanggal1,Tanggal2)
Contoh
datediff(day,'9/10/10','9/20/10')
akan mengembalikan 10.
datename
Fungsi
untuk mendapatkan nama dari bagian tanggal tertentu.
51 | P a g e
Bentuk umum
datename(SatuanTanggal,Tanggal)
Contoh
datename(month, '10/5/10')
akan mengembalilkan October
datepart
Fungsi
untuk mengambil data tertentu dari tanggal.
Bentuk umum
datepart(SatuanTanggal,Tanggal)
Contoh
datepart(month, '10/5/10')
akan mengembalikan 10.
dateadd
Fungsi
untuk menambahkan satuan waktu tertentu ke data yang
berupa datetime.
Bentuk umum
dateadd(SatuanTanggal, BesarSatuanTanggal, Tanggal)
Contoh
dateadd(day,5, '10/9/10')
akan mengembalikan 14 Oktober 2010
month
Fungsi
untuk mengambil data bulan.
Bentuk umum
month(Tanggal)
52 | P a g e
Contoh
Month(Tanggal) dan tanggal hari ini adalah 4 January
2011, maka akan mengembalikan nilai 1.
day
Fungsi
untuk mengambil data tanggal.
Bentuk umum
day(Tanggal)
Contoh
day (Tanggal) dan tanggal hari ini adalah 4 January
2011, maka akan mengembalikan nilai 4.
year
Fungsi
untuk mengambil data tahun.
Bentuk umum
year(Tanggal)
Contoh
year (Tanggal) dan tanggal hari ini adalah 4 January
2011, maka akan mengembalikan nilai 2011.
charindex
Fungsi
untuk mengambil urutan dari kata/string tertentu.
Bentuk umum
charindex(String yang dicari, namaField, IndexMulai)
Contoh
ada 1 record, namanya = „Ricky‟
charindex(„ic‟,nama,1) -> mengembalikan nilai 2
left
Fungsi
digunakan untuk mengambil beberapa karakter dari
53 | P a g e
sebelah kiri (awal).
Bentuk umumnya :
Left(namaField, jumlahHuruf)
Contoh :
ada 1 record, namanya = „Ricky‟
left(nama, 1) -> ambil 1 huruf terdepan dari nama, ini
akan mengembalikan „R‟
right
Fungsi
digunakan untuk mengambil beberapa karakter dari
sebelah kanan(dari belakang).
Bentuk umumnya :
right(namaField, jumlahHuruf)
Contoh
ada 1 record, namanya = „Ricky‟
right(nama,2) -> ambil 2 huruf terbelakang dari nama, ini
akan mengembalikan „ky‟.
substring
Fungsi
digunakan untuk mengambil beberapa karakter mulai
dari
index
tertentu.
bentuk
umumnya
:
substring(namaField, indexMulai, banyak).
Bentuk umumnya:
Substring(namaField, IndexMulai, JumlahHuruf)
Contoh
substring(nama, 3 2) -> mengambil 2 huruf dari nama,
dimulai dari index ke-5, ini akan mengembalikan „ck
54 | P a g e
rtrim
Fungsi
digunakan untuk menghilangkan spasi di sebelah kanan.
Jika kita mempunyai suatu field, dan typenya adalah
char(20), ternyata tempat yg kita gunakan hanya 7 dari
20 tempat yg disediakan. Maka, 13 tempat tersisa, akan
diisi dengan spasi.
Rtrim ini berguna untuk menghilangkan spasi tersebut.
Tetapi jika type data kita varchar(20), rtrim ini sudah
tidak diperlukan lagi, karena walaupun kita memesan
tempat sebanyak 20, namun yg kita gunakan hanya 7
saja, maka sisa 13 tempat tersebut akan dimampatkan,
sehingga memorinya bisa dipakai oleh yg lain (tidak diisi
dengan spasi). Biasanya, rtrim ini digunakan bersama sama dengan right.
Bentuk umumnya
rtrim(namaField)
Contohnya
saya mempunyai satu field nama bertype char(20), dan
kemudian saya ingin menampilkan karakter terakhir dari
nama tersebut, padahal nama-nama tersebut tidak pasti
berjumlah 20 karakter.
right(rtrim(nama), 1)
reverse
Fungsi
Digunakan untuk membalikkan kata atau kalimat.
Bentuk umum
reverse(namaField)
55 | P a g e
Contoh
reverse(nama) dan jika nama adalah “Nathan”, maka
kata yang dihasilkan “nahtaN” .
len
Fungsi
Digunakan untuk mencari panjang kata atau kalimat.
Bentuk umum
len(namaField)
Contoh
Len(nama) dan jika nama adalah “Edbertius”, maka akan
menghasilkan nilai 9.
stuff
Fungsi
Digunakan untuk memasukkan suatu kata atau kalimat
(String2) ke dalam kata atau kalimat lainnya (String1).
Fungsi
ini
akan
menghapus
sejumlah
karakter
(JumlahKarakter) mulai dari IndexMulai dan kemudian
menggantikan memasukkan String2 ke dalam String1
mulai dari posisi IndexMulai
Bentuk umum
Stuff(String1, IndexMulai, JumlahKarakter, String2)
Contoh
Stuff(nama,2,3,‟erl‟) dan jika nama adalah “Sxxxy”,
maka kata yang dihasilkan adalah “Serly”
convert
Fungsi
Digunakan untuk mengubah tipe data suatu nilai ke tipe
data lainnya dan melakukan formatting untuk data
berupa tanggal.
56 | P a g e
Bentuk umum
Conver(tipeDataTujuan, namaField [, jenisStyle])
Contoh
Convert(char, tgl, 106) dan jika tgl adalah “4 January
2011”, maka tgl yang dihasilkan adalah dengan format
“dd MMM YYYY”, 4 Jan 2011
cast
Fungsi
Digunakan untuk mengubah tipe data suatu nilai ke tipe
data lainnya.
Bentuk umum
Cast(namaField AS tipeDataTujuan)
Contoh
„Rp. ‟+Cast(harga AS char)+‟,00‟ dan jika harga adalah
5000, maka hasilnya adalah “Rp. 5000,00”
4.3.
SQL Operator
4.3.1. Operator Aritmatik
Operator aritmatik digunakan
untuk melakukan operasi matematika terhadap dua
ekspresi dari satu atau lebih tipe data numerik. Operator-operator aritmatik dalam SQL
Server dapat dilihat pada table di bawah ini :
Operator
Arti
+ (Add)
Melakukan operasi penambahan.
- (Subtract)
Melakukan operasi pengurangan.
* (Multiply)
Melakukan operasi perkalian.
/ (Divide)
Melakukan operasi pembagian.
% (Modulo)
Mengembalikan sisa hasil pembagian.
Contoh :
7%3=1, karena sisa pembagian 7 dari 3 adalah 1.
57 | P a g e
Contoh :
Tampilkan NamaGame dan Harga+PPN 10%, dimana Harga+PPN 10% merupakan
kelipatan dari 10.000. Harga + PPN10% didapapt dari harga game setelah ditambah
pajak sebesar 10% dari harga awal.
Jawab :
4.3.2. Operator Logika
Operator logika digunakan untuk melakukan pengujian kebenaran suatu kondisi.
Operator-operator logika sama dengan operator-operator perbandingan, dimana operator
logika akan mengembalikan data Boolean, berupa nilai TRUE, FALSE, atau
UNKNOWN. Beberapa operator logika dapat dilihat pada table di bawah :
Operator
Arti
AND
Mengembalikan nilai True, jika
kedua
ekspresi
booleannya
bernilai TRUE.
OR
Mengembalikan
nilai
TRUE
TRUE, jika salah satu ekspresi
booleannya bernilai TRUE.
Between
Mengembalikan
nilai
TRUE,
jika operand yang diuji berada
dalam jangkauan.
Like
Mengembalikan
nilai
TRUE,
jika operand yang diuji sesuai
dengan pattern yang ditentukan.
Not
Membalikkan nilai Boolean dari
operator-operator logika.
58 | P a g e
Exists
Mengembalikan
nilai
TRUE,
jika subquery mengembalikan
setidaknya satu baris.
IN
Mengembalikan
nilai
TRUE,
jika operand yang diuji sesuai
dengan salah satu daftar ekspresi
yang disediakan.
Contoh :
Tampilkan data
Tampilkan Kodegame, NamaGame, dan Harga dimana JenisGame nya adalah “PC” atau
“Wii” atau JenisGame tidak mengadung kata “PS”. Tampilkan data yang harganya antara
50000 dan 80000.
Jawab :
59 | P a g e
4.4.
Exercise
1. Tampilkan semua NamaPelanggan dan AlamatPelanggan yang ada.
(select)
2. Tampilkan KodePenjualan, Tanggal, dan Jumlah Terjual dimana Jumlah Terjual lebih dari 1
buah. Tanggal didapat dari TglTransaksi dengan format “dd MMM YYYY”, sedangkan
Jumlah Terjual didapat dari Qty yang ditambah dengan kata “ buah” di bagian belakang.
(convert, cast)
3. Tampilkan Sandi dan Harga dimana 3 digit terakhir merupakan kelipatan 2 dan harga diantara
30000 dan 50000. Sandi merupakan gabungan dari “Kata terakhir dari NamaGame”,”Kata
kedua dari NamaGame”-“Kata pertama dari NamaGame”.
(reverse, substring, charindex, cast, between)
60 | P a g e
Chapter 05
Data Manipulation Language Advanced
Objectives
1. Sub Query
2. Alias Sub Query
3. Multi Table Query
4. Combine Table Result (IN, EXISTS)
5. Join and Union
6. Aggregate Function
7. Grouping and Sorting
Learning Outcomes
1. Describe database systems, terminology, environment, and new concept of database
2. Apply database language and SQL Programming language
61 | P a g e
5.1.
Sub Query
Subquery adalah adalah query select yang mengembalikan nilai tunggal dan terletak dalam
sebuah perintah SELECT, INSERT, UDPATE, DELETE atau di dalam subquery lainnya. Sebuah
subquery biasa disebut juga dengan inner query atau inner select, sedangkan query yang
mengandung subquery biasa disebut dengan outer query atau outer select.
Contoh:
Hapus data dari pelanggan yang TelpPelanggannya kosong.
Jawab:
5.2.
Alias Sub Query
Alias subquery adalah subquery yang diberi nama, alias subquery dianggap sebagai tabel virtual
sehingga dapat digunakan untuk kerperluan-keperluan yang lain.
Contoh :
Tampilkan semua NamaKaryawan dan NamaPelanggan dengan cara cross product.
Jawab :
5.3.
Multi Table Query
Multi Table Query adalah query yang melibatkan lebih kolom-kolom tidak hanya dalam satu
tabel, tapi melibatkan 2 atau lebih tabel. Multi Table Query ini dapat digunakan dalam perintah
SELECT (untuk menampilkan kolom-kolom yang tersebar di dua tabel atau lebih), UPDATE
atau DELETE (untuk meng-udpate atau menghapus data disuatu tabel, namun syarat atau kondisi
tersebar di tabel lain).
Contoh:
Tampilkan NamaGame, Harga, dan JenisGame dimana Harganya merupakan kelipatan dari
15000.
62 | P a g e
Jawab :
5.4.
Combine Table Result (IN, EXISTS)
IN EXISTS ini digunakan, jika kita ingin menampilkan data - data dari suatu tabel, namun syarat
nya ada di tabel yang lain(lebih dari satu tabel).
5.4.1. IN
Sintaks in digunakan jika kita sudah mengetahui nilai yang mau dikembalikan dari satu
kolom dari tabel yang berbeda. IN akan mengembalikan 1 kolom, jadi pertama - tama kita
harus sebutkan dulu foreignKey yang menghubungkan tabel1 dan tabel2, dan didalam
subquery-nya, yang kita select HANYA 1 kolom saja, sehingga foreignKey dari table1
akan disamakan dengan foreignKey dari table2.
penggunaan in tidak hanya untuk
menghubungkan tabel - tabel saja, namun juga bisa digunakan untuk mengambil nilai
tertentu.
Sintaks:
SELECT column_name FROM table_name
WHERE column_name <primary key> in ( SELECT column_name FROM table_name)
Contoh:
Tampilkan KodeKaryawan dan NamaKaryawan dimana KodeKaryawan berakhiran 2, 3
atau 5 (Gunakan sintak in).
Jawab :
5.4.2. EXISTS
Sintak Exists mirip dengan in bedanya pada pengembalian nilai, sintak exists tidak
mengembalikan data-data yang di SELECT dalam subquery, namun mengembalikan nilai
boolean, yaitu true atau false. Nilai true dikembalikan jika subquery dalam exists
63 | P a g e
mengandung minimal satu baris data, sebaliknya nilai false dikembalikan jika subquery
dalam exists tidak menghasilkan baris data sama sekali.
Sintaks:
SELECT column_name FROM table_name
WHERE exists (SELECT * FROM table_name WHERE column_name = column_name)
Contoh:
Tampilkan NamaGame dah Harga dimana panjang kata dari JenisGame lebih dari 3
karakter (gunakan sintak exists).
Jawab :
5.5.
Join and Union
5.5.1. Join
Keyword JOIN digunakan di SQL statement untuk men-query data dari dua atau lebih
tabel, berdasarkan relationship antar kolom di tabel-tabel ini. Tabel di database sering
dihubungkan dengan “keys”.
Primary key adalah kolom (atau kombinasi dari kolom) dengan nilai yang unik untuk
setiap barisnya. Setiap nilai dari primary key harus unik di tabel tersebut, tujuannya
adalah untuk mengikat data dari tabel secara bersamaan, di beberapa tabel tanpa
mengulang semua data di setiap tabel.
Untuk mencoba contoh-contoh di bawah, silahkan eksekusi query create+insert dibawah
ini :
(*query di bawah ini digunakan untuk contoh-contoh Join dan untuk soal nomor 9)
64 | P a g e
Ada beberapa tipe JOIN di antara lain adalah sebagai berikut.

JOIN (INNER JOIN)
Mengembalikan baris jika setidak-tidaknya terdapat satu kesamaan foreign key dan
primary key yang dihubungkan dengan sintak JOIN.
Contoh :

LEFT JOIN (LEFT OUTER JOIN)
Mengembalikan semua baris dari tabel yang ada di sebelah kiri perintah JOIN, bahkan
jika tidak terdapat kesamaan antara foreign key dan primary di kedua tabel.
Contoh :
65 | P a g e

RIGHT JOIN (RIGHT OUTER JOIN)
Mengembalikan semua baris dari tabel yang ada di sebelah kanan perintah JOIN,
bahkan jika tidak terdapat kesamaan antara foreign key dan primary di kedua tabel.
Contoh :

FULL JOIN (FULL OUTER JOIN)
Mengembalikan semua baris dari tabel yang ada disebelah kiri maupun kanan perintah
JOIN walaupun tidak terdapat tidak terdapat kesamaan antara foreign key dan primary
di kedua tabel.
Contoh :
66 | P a g e
5.5.2. Union
Mengembalikan dan menggabungkan semua nilai hasil SELECT dari masing-masing
tabel. Jika kita mau men-copy atau dengan kata lain baris yang sama tidak dihiraukan
(ditampilkan berulang kali), kita bisa menggunakan perintah UNION ALL, jika kita ingin
mengurangi redudansi data/baris yang berulang dihapus dari TABLE hasil UNION, kita
bisa menggunakan perintah UNION atau UNION DISTINCT.
(*query di bawah ini digunakan untuk contoh-contoh Union, Union All dan untuk soal
nomor 10)
Contoh Union :
Tampilkan secara unik (tanpa redudansi) semua Nama dan Nilai dari KelasA dan KelasB.
Jawab:
67 | P a g e
Contoh Union All :
Tampilkan semua Nama dan Nilai dari KelasA dan KelasB tanpa memperhatikan
redudansi data yang ada.
Jawab:
5.6.
Aggregate Function
Aggregate adalah fungsi-fungsi yang disediakan oleh SQL untuk melakukan perhitungan
(kalkulasi) terhadap data.
Fungsi-fungsi agregasi antara lain :
1. sum(nama_field)
Sintak sum digunakan untuk menjumlahkan isi baris-baris pada field tersebut.
2. count(nama_field)
Sintak count digunakan untuk menghitung banyaknya baris (pencacahan data).
3. avg(nama_field)
Sintak avg digunakan untuk menghitung rata-rata dari isi baris-baris yang di SELECT.
4. max(nama_field)
Sintak max digunakan untuk mencari nilai max dari baris-baris.
5. min(nama_field)
Sintak min digunakan untuk mencari nilai min dari baris-baris.
Untuk sum dan avg, field yang mau dihitung harus tipe datanya numeric. Sedangkan untuk count,
baris yang mempunyai nilai NULL, tidak ikut dihitung.
68 | P a g e
Contoh:
Tampilkan Harga game termurah, Harga game termahal, Rata-rata harga game, Jumlah game,
dan Total harga jual. Total harga jual didapat dari jumlah semua harga game yang ada.
Jawab:
5.7.
Grouping and Sorting
5.7.1. Grouping
GROUP BY digunakan untuk pengelompokan data, biasa digunakan bersama dengan
fungsi SQL agregat seperti SUM untuk menyediakan sarana pengelompokan dataset
hasilnya dengan tabel database kolom tertentu. Jika suatu query mengandung proses
perhitungan/kalkulasi dengan menggunakan aggregate, maka field-field lain yang tidak
termasuk dalam aggregate harus dimasukkan dalam group by untuk mengelompokkan
hasil kalkulasi.
Sintaks:
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
Contoh :
Tampilkan KodeJenisGame dan total harga perjenis game yang dikelompokkan
berdasarkan KodeJenisGame.
69 | P a g e
Jawab:
5.7.2. Sorting
Pengurutan/sorting dilakukan dengan sintak order by. Order by digunakan untuk
mengurutkan hasil-set dengan sebuah kolom yang ditetapkan. ORDER BY mengurutkan
hasil dalam urutan menaik (ascending) secara default. Jika Anda ingin mengurutkan hasil
yang menurun (besar ke kecil) dalam urutan, Anda dapat menggunakan kata kunci DESC.
Sintaks:
SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC
Contoh :
Tampilkan NamaGame dan Harganya yang di urutakan berdasarkan harga dari kecil ke
besar.
Jawab:
70 | P a g e
5.8.
Exercise
1. Tampilkan NamaGame dan Harga dimana Namagamenya adalah “Left for Dead”.
(sub query)
2. Tampilkan NamaGame, Harga, dan Selisih antara harga dengan rata-rata harga untuk masingmasing game.
(abs, avg )
3. Tampilkan
KodeGame,
Qty,
KodePenjualan,
TglTransaksi,
KodePelanggan,
dan
KodeKaryawan dimana Qty merupakan bilangan genap.
(alias sub query)
4. Tampilkan JenisGame, NamaGame, Harga (tambahkan “RP. ” di depan harga), dan Serial
Number dari masing-masing game. Serial Number merupakan NamaGame yang dibalik,
spasi yang ada diganti dengan kata “xz” dan huruf “o” diganti dengan angka 47. Tampilkan
Serial Number dalam huruf kecil.
(convert, replace, lower, reverse, len)
71 | P a g e
5. Tampilkan Tanggal ( dengan format “dd MMM YYYY”), KodePelanggan, KodeKaryawan,
KodeGame, dan Qty dimana Qty merupakan kelipatan 2.
(convert)
6. Tampilkan NamaKaryawan, NamaPelanggan, NamaGame, JenisGame, dan Qty game yang
dibeli dimana NamaGame tepat mengandung 3 kata, panjang kata dari gabungan NamaGame
dan JenisGame merupakan kelipatan 2.
(len, like, not like)
7. Tampilkan KodePenjualan, TglTransaksi, dan KodePelanggan dimana Pelanggan pernah
melakukan pembelian dengan Qty lebih besar dari rata-rata Qty yang ada. (Buatlah 2 jawaban
untuk mengerjakan soal ini, 1 jawaban menggunakan IN dan yang lainnya menggunakan
exists).
(in/exists, avg)
8. Tampilkan Nama, Alamat, dan TelpPelanggan dimana pelanggan tersebut pernah membeli
barang dengan Qty lebih besar dari pada jumlah rata-rata dan jumlah minimal pembelian dari
semua transaksi yang ada. Tampilkan pelanggan yang TelpPelanggannya tidak kosong dan
nama pelanggan terdiri dari 2 kata atau lebih.
72 | P a g e
Nama didapat dari kata pertama NamaPelanggan. Alamat didapat dari AlamatPelanggan yang
kata “Jln.” diganti dengan kata “Jalan ” dan “No.” diganti dengan kata “Nomor ”. (Buatlah 2
jawaban untuk mengerjakan soal ini, 1 jawaban menggunakan IN dan yang lainnya
menggunakan exists).
(in/exists, substring, charindex, replace, in, avg, min,not null, like)
9. Tampilkan semua nama dari karyawan, email, Jabatan(revisi), dan Password tanpa
mempedulikan apakah karyawan tersebut sudah atau belum memiliki jabatan. Jabatan(revisi)
di dapat dari nama jabatan yang diganti kata “Tukang” menjadi “Distributor”. Sedangkan
Password didapat dari email sebelum tanda “@” dan ditambah dengan “!@#”.
(replace, upper, substring, charindex, left join)
10. Tampilkan semua Nama dan Nilai siswa dari KelasA dan KelasB, dimana Nilainya di bawah
nilai rata-rata kelas siswa bersangkutan tanpa mempedulikan redudansi data.
(avg, union all)
11. Tampilkan NamaGame, JenisGame dan Harga satuan dari game yang memiliki harga di atas
harga rata-rata game lainnya. Harga Satuan didapat dari harga yang ditambah dengan awalan
“RP. ” dan akhiran “,00”.
(cast, avg)
73 | P a g e
12. Tampilkan TglTransaksi, namaKaryawan, namaPelanggan, kodePenjualan, dan jumlah item
terjual dimana namaKaryawan hanya mengandung satu kata, Jumlah item terjual yang dijual
lebih dari 1, tglTransaksi tidak dilakukan pada tanggal terawal dan terakhir dari tanggaltanggal transaksi yang ada. Jumlah item terjual merupakan jumlah Qty dari transaksi
bersangkutan.
(convert,sum, not like, group by,sum, max)
13. Tampilkan
Hari dan JumlahTransaksi yang diurutkan berdasarkan JumlahTransaksi dari
besar ke kecil. Hari merupakan nama hari dari TglTransaksi.
(datename, count, datename, count)
14. Tampilkan Tanggal Transaksi (didapat dari TglTransaksse dan ubah format tanggal sesuai
yang terlihat pada gambar dibawah), Jumlah transaksi (didapat dari jumlah traksaksi pada
tanggal bersangkutan), dan Total pendapatan kotor ( didapat dari penjumlahan dari
Qty*Harga) dimana Total pendapatan kotor lebih besar dari pada rata-rata Total pendapatan
kotor yang ada. Urutkan hasil query secara ascending.
(convert, count, cast, sum, distinct, order by )
74 | P a g e
Chapter 06
Data Control Language
Objectives
1. Registrasi Server
-
Create Login
-
New Database
-
Database Roles
-
Delete Database
-
User Roles
2. Grant & Revoke
Learning Outcomes
1. Apply database language and SQL Programming language
75 | P a g e
6.1.
Registrasi Server
Jika anda telah memiliki SQL Server 2008 di dalam komputer anda, maka langkah selanjutnya
adalah menggunakannya. Berikut ini tampilan awalnya:
Pada tampilan awal, anda akan di hadapkan dialogboxconnecttoserver dimana pada box ini
terdapat beberapa pilihan:
Servertype:

Database Engine

Analysis Service

Reporting Service

SQL Server mobile

Integration Service
Servername:
Berisi nama server yang ada, untuk default-nya anda bisa memilih „localhost‟ atau karakter „.‟.
76 | P a g e
Authentification:
ada 2 yaitu Windows Authentification (untuk masuk sebagai admin, tidak perlu username dan
password) dan SQL Server Authentification (dimana anda harus memasukan username dan
password).
Lalu terdapat 4 tombol yaitu:
1. Tombol Connect , langsung konek ke database berdasarkan servertype dan servername yang
dipilih dengan authentifikasi yang dipilih.
2. Tombol Cancel, untuk membatalkan koneksi ke database.
3. Tombol Help, untuk memanggil help SQL (sudah otomatis ter-install sewaktu meng-install
microsoft SQL server 2005).
4. Tombol Option, untuk memunculkan pilihan koneksi yang lebih banyak lagi.
Jika anda sudah terhubung, maka tampilannya akan seperti ini:
Pertama, anda diminta untuk mendaftarkan / registering new server terlebih dahulu. Ini bertujuan
sebagai awal dari sekian tahap untuk mengelola database.
Server yang telah diregistrasi dapat ditampilkan di windowRegisteredservers. Server dapat dibuat
dan diregistrasi, di-rename atau dihapus dalam SQL Management Studio. Informasi server yang
sering diakses akan disimpan di ManagementStudioonRegistration.
77 | P a g e
Untuk me-registrasi sebuah server, lakukan klik kanan node di RegisteredServer’sTree dan klik
UpdateLocalServerRegistration. Aksi ini akan meng-updatelocalserverregistration.
78 | P a g e
Untuk membuat New Server, lakukan langkah-langkah berikut:
1) Klik DatabaseEngine di tool bar Registered Servers.
2) Klik kanan Microsoft SQL Servers dan arahkan mouse ke New, lalu klik Server Registration.
3) Di dialog boxRegistration, klik tab General dan masukkan nama server baru yang anda
inginkan di textbox yang tersedia. Gambar dibawah mengilustrasikan jika kita ingin membuat
sebuah server bernama Tutorial Server
79 | P a g e
4) Pada Authentication pilih Windows authentication atau select SQL Server Authentification
dan isi username serta passwordnya.
5) Ingat bahwa Registeredservername terisi secara otomatis. Anda dapat memasukkan deskripsi
singkat tentang server ke dalam textbox yang tersedia.
6) Informasi tentang properties dari connection merupakan opsional, untuk menerima default
connectionproperties klik tombol Save.
7) Ingat bahwa server yang telah diregistrasi akan muncul di list registeredservers.
Sebuah server yang telah diregistrasi dapat dihapus dengan cara melakukan klik kanan pada
server yang dimaksud dan meng-klik delete. Sebuah dialogbox konfirmasi proses pen-delete-an
akan muncul. Klik Yes untuk menghapus server.
Harus dicatat bahwa nama dari server dapat diganti kapan saja dan perubahan nama tidak
memiliki pengaruh pada connection properties. Nama dapat diganti dengan melakukan klik
kanan pada server yang dimaksud dan arahkan cursor ke Edit, kemudian klik Server Registration
Properties.
80 | P a g e
a. Create Login
Untuk membuat Login baru dapat dilakukan dengan cara klik kanan pada security, pilih new,
lalu klik login.
Lalu akan muncul windows seperti:
81 | P a g e
Beri nama login name-nya, anda dapat mengubah server roles dengan mengklik server roles
lalu akan muncul windows seperti:
82 | P a g e
Ada beberapa role yang telah disediakan oleh SQL server, antara lain:
Fixed Server Role
Penjelasan
dapat melakukan segala aktivitas pada SQL Server Installation.
sysadmin
Hak aksesnya:
Create Database, Alter Database, Drop Database, Revoke,
Grant.
dapat mengkonfigurasi server-wide settings.
serveradmin
Hak aksesnya:
Reconfigure, shutdown, sp_configure, sp_fulltext_service,
sp_tableoption.
dapat mengatur extendedstoredprocedure.
setupadmin
Hak aksesnya:
Menambah member pada setupadmin, add/drop/configure
83 | P a g e
Fixed Server Role
Penjelasan
linked servers.
dapat mengatur login untuk server.
securityadmin
Hak aksesnya:
sp_addlogin, sp_password, sp_droplogin, sp_denylogin,
sp_defaultdb.
dapat mengatur proses yang berjalan pada SQL Server.
processadmin
Hak aksesnya:
Menambah member pada processadmin, KILL.
dapat membuat dan alter database.
dbcreator
Hak aksesnya:
Create database, alter database, drop database, sp_renamedb,
restore database.
dapat mengatur diskfiles.
diskadmin
Hak aksesnya:
Menambah member pada diskadmin, DISK INIT,
sp_addumpdevice, sp_diskdefault, sp_dropdevice.
dapat melakukan bulkinsertoperation.
bulkadmin
Hak aksesnya:
Menambah member pada bulkadmin, BULKINSERT.
84 | P a g e
Pilih server rolenya, lalu ubah user mapping dengan klik user mapping untuk
membuat mapping ke database.
85 | P a g e
Pilih security -> SQL Server and Windows Authentication mode
Pilih databasenya, lalu klik OK untuk mengakhiri. Setelah itu maka login baru sudah
terbentuk dan siap untuk digunakan.
86 | P a g e
Lalu akan muncul Window seperti ini, klik Yes
87 | P a g e
88 | P a g e
b. New Database
Untuk membuat database yang baru dapat dilakukan dengan cara klik kanan pada
database, klik new database.
89 | P a g e
Lalu akan muncul windows seperti yang dibawah:
Beri nama databasenya, anda juga dapat mengatur autogrowth nya untuk mengatur
pertumbuhan database. Jika sudah selesai maka click OK.
c. Database Roles
Langkah – langkah untuk membuat Database Role:
1. Expand database yang ingin dibuat rolenya.
2. Expand Security
3. Klik kanan pada Roles, Pilih new, klik New Database Roles.
90 | P a g e
4. Isi Role Name dengan nama yang diinginkan. Isi Ownernya. Dan yang terakhir Buat
Role Membernya menjadi Public dengan cara klik tombol add.
91 | P a g e
5. Klik tombol Browse
6. Pilih yang public, lalu klik OK.
92 | P a g e
d. Delete Database
Untuk menghapus database yang telah dibuat:
1. Klik kanan pada database yang ingin dihapus, klik delete.
93 | P a g e
2. Centang delete backup and restore history information for database untuk menghapus
log milik database.
e. User Roles
Berikut adalah query untuk membuat sebuah role pada user.
sp_addlogin login_id [, password [, database]]
Membuat login account baru untuk SQL Server Login.
Contoh:
EXEC sp_addlogin Margaret, Rose
sp_droplogin login_id
Menghapus nama login dari SQL Server Login.
Contoh:
EXEC sp_droplogin „Victoria‟
sp_password passwd_lama passwd_baru login_id
Mengganti password lama dengan password baru.
Contoh:
94 | P a g e
EXEC sp_password „ok‟, „coffee‟
sp_defaultdb login_id nama_database
Mengubah default database untuk login-id tersebut.
Contoh:
EXEC sp_defaultdb „Victoria‟, „pubs‟
sp_addrole [ @nama_role = ] „role‟ [ , [ @ownername = ] „owner‟ ]
Membuat role yang baru
Contoh:
EXEC sp_addrole „Managers‟
sp_droprole [ @rolename = ] „role‟
Menghapus role
Contoh:
EXEC sp_droprole „Sales‟
sp_addrolemember [ @rolename = ] „role‟, [ @membername = ]
„security_account‟
Menambahkan user ke role
Contoh:
EXEC sp_addrolemember „Engineering‟, „Michael‟
sp_droprolemember [ @rolename = ] „role‟ , [ @membername = ]
„security_account‟
Mengeluarkan member dari role
Contoh:
EXEC sp_droprolemember „Sales‟, „Jonb‟
95 | P a g e
6.2.
Grant & Revoke

Grant
Anda dapat menambahkan hak akses ke user, hak akses dapat berupa select, insert,
update, delete, references, alter, and index. Berikut penjelasan mengenai hak akses:
Hak Akses
Description
Select
Kemampuan untuk query tabel dengan sintaks select.
Insert
Update
Delete
References
Alter
Index
Kemampuan untuk menambakan baris baru ke tabel
dengan sintaks insert.
Kemampuan untuk merubah data pada tabel dengan
menggunakan sintaks update.
Kemampuan untuk menghapus baris dari tabel dengan
menggunakan sintaks delete.
Ability to create a constraint that refers to the tabel.
Ability to change the tabel definition with the alter tabel
statement.
Ability to create an index on the tabel with the create index
statement.
Contoh:

Revoke
Revoke untuk membuang hak yang telah dilarang dengan perintah Deny atau hak
yang telah diberikan oleh perintah GRANT
Contoh:
96 | P a g e
6.3.
Exercise
1. Berilah hak akses kepada publik (select,insert,update,delete, dll) terhadap tabel
MsCustomer
(grant all)
2. Berilah hak akses kepada salah satu teman anda untuk melakukan update terhadap
tabel MsStaff dan teman anda dapat memberikan hak akses pada tabel MsStaff juga
kepada user lainnya.
(grant)
3. Berilah hak akses kepada salah satu teman anda untuk melakukan insert dan update
terhadap tabel MsMenu.
(grant)
4. Cabutlah hak akses untuk insert dan updatepada tabel MsMenu kepunyaan teman
Anda yang telah Anda berikan pada soal nomor 3.
(revoke)
5. Cabutlah semua hak akses tabel MsCustomer kepada publik yang telah anda berikan
pada soal nomor 1.
(revoke)
97 | P a g e
Chapter 07
Advanced Query
Objectives
1. Trigger
2. Cursor
3. Store Procedure
4. Indexing
Learning Outcomes
1. Apply database language and SQL Programming language
98 | P a g e
TRIGGER
a. Pengenalan Trigger
Trigger adalah blok program Transact-SQL yang diasosiasikan dengan Tabel dan
disimpan dalam Database. Seperti dalam bahasa, Trigger berarti pemicu. Pemicu yang
dimaksudkan disini adalah pada saat kita mengeksekusi SQL statement, maka akan
muncul event. Database statement yaitu Insert, Update dan Delete. Jadi Trigger
merupakan suatu aksi (Transact-SQL) yang dilakukan terhadap statement yang berlaku
pada sebuah tabel dimana trigger berada. Beberapa manfaat Trigger antara lain:
1. Meluruskan aturan-aturan yang harus berlaku.
2. Menjaga nilai sebelum dimasukkan ke dalam database.
3. Keamanan system.
4. Membuat historical record(riwayat perubahan).
5. Dan lain-lain
b. Create Trigger
Sintaks pembuatan Trigger:
99 | P a g e
Contoh Sederhana:
sebuah Trigger diberikan pada Table Pegawai
Jika anda memasukkan data:
Maka hasil dari query tersebut setelah insert diberikan trigger adalah:
Trigger yang bernama triggerPegawai dibuat dan setiap kali sesudah record baru diisi ke
tabel, maka program trigger akan aktif.
Di dalam Trigger secara implisit dapat diakses "koleksi" record pada kasus "insert"
dimasukkan ke dalam tabel virtual yang bernama "inserted" dan untuk record yang
dihapus dimasukkan ke dalam tabel virtual "deleted".
Untuk "Update" terdapat 2 buah tabel virtual yaitu record yang lama masuk ke dalam
tabel "deleted" sedangkan record yang baru masuk ke tabel virtual "inserted".
Pada saat kita melakukan insert, maka record akan masuk kedalam tabel “inserted”, pada
saat melakukan delete, maka record akan masuk kedalam tabel “deleted”. Lalu apakah
yang akan terjadi pada saat kita melakukan update? Apabila kita melakukan update, kita
100 | P a g e
menghapus data lama dan menggantinya dengan data yang baru, maka tabel virtual yang
digunakan yaitu tabel “inserted” dan “deleted”.
c. Drop Trigger
Sintaks ini digunakan untuk menghapus Trigger yang sudah dibuat.
Sintaks penghapusan Trigger:
Contoh Sederhana:
d. Exception Handling
Exception Handling adalah suatu mekanisme penanganan error yang mungkin terjadi
dalam suatu query. Sintaks TRY akan dikerjakan terlebih dahulu, jika tidak ditemukan
error maka sintaks CATCH tidak akan dijalankan, dan sebaliknya jika error ditemukan
saat menjalankan query, maka sintaks CATCH akan dikerjakan untuk menanggulangi
error tersebut.
Sintaks Penggunaan Exception Handling:
101 | P a g e
Contoh Sederhana:
Jika dijalankan hasil query diatas adalah:“Query Wrong” karena tipe data yang diterima
seharusnya int bukan char ataupun varchar pada nilai „angka‟.
CURSOR
a. Pengenalan Cursor
Cursor adalah object database yang di gunakan oleh aplikasi untuk memanipulasi data
dalam set per baris, berbeda dengan sql commands biasa yang mengoperasikan semua ke
dalam semua baris dalam suatu kurun waktu. Berikut format Cursor yang umum:
102 | P a g e
Contoh:
 Membuat tabel MsKaryawan terlebih dahulu
 Insert data ke tabel MsKaryawan
 Anda diminta untuk membuat sebuah cursor yang dapat menampilkan seluruh data
karyawan:
Nb:
103 | P a g e
Hasil:
b. Type of Cursors
Ada 4 Jenis Kursor:
1. Forward Only:Kursor hanya dapat bergerak maju sepanjang recordset
2. Static:memberikan copy recordset yang disimpan dalam tempdb yang untuk mencari
data atau membuat report
3. Dinamic:memberikan akses untuk bisa melihat penambahan, perubahan dan
penghapusan data oleh user yang lain.
4. Keyset:seperti dinamic tetapi tidak dapat melihat penambahan oleh user lain. Dan
juga akan menolak akses terhadap record yang user lain hapus. Perubahan data masih
dapat dilihat.
Beberapa Istilah pada Cursor
a. Next - Mengembalikan baris setelah baris terakhir diambil.
b. Prior - Mengembalikan baris sebelum baris terakhir diambil.
c. First - Mengembalikan baris yang paling pertama.
d. Last - Mengembalikan baris yang paling terakhir.
e. Absolute
Mengembalikan baris ke-n dari baris pertama jika n adalah bilangan bulat positif. Jika
n adalah bilangan bulat negatif, maka akan mengembalikan baris ke-n dari baris
terakhir. Jika n adalah 0, maka tidak ada baris yang diambil.
104 | P a g e
f. Relative
Mengembalikan baris ke-n dari baris terakhir diambil. Jika n adalah positif, maka
baris ke-n setelah baris terakhir diambil akan diambil. Jika n adalah negatif, maka
baris ke-n sebelum baris terkahir diambil akan diambil.Jika n adalah 0, maka baris
yang sama yang akan diambil.
STORE PROCEDURE
a. Pengenalan transact SQL Code (Pengenalan Transact SQL)
Transact-SQL merupakan bahasa pemograman yang dikembangkan dari SQL.Seperti
diketahui bahwa SQL adalah bahasa non procedural yang artinya alur program tidak
seperti bahasa pemograman biasa, melainkan melalui “request” dan “response” yaitu
query dan jawabannya berupa hasil atau resultsed.
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
pemograman procedural seperti Fungsi, Prosedur, Loop, Case, If Then Else dan lainnya.
Kerangka Transact SQL
Transact-SQL dimulai dengan deklarasi variable dan disusul dengan blok program.
Variable harus dideklarasikan sebelum digunakan. Nama variable selalu dimulai dengan
karakter @. Variable dapat diberikan nilai melalui instruksi SELECT.

Deklarasi Variable
Tipe dari variable yang dapat dideklarasikan adalah tipe data dari Ms-SQL yaitu char,
varchar, datetime, int, money dan lainnya.
Contoh:
105 | P a g e
Variable dapat digunakan untuk mengambil nilai dari sebuah query:
Hasil:
Judul dvd yaitu Spiderman2 (dari kolom)
PRINT adalah fungsi yang menampilkan teks dan variable pada console (layar).

IF
If digunakan dalam mengendalikan alur program berdasarkan kondisi.
Sintaks:
106 | P a g e
Contoh:

WHILE
While digunakan untuk mengeksekusi satu blok program berulang – ulang sampai
kondisi pada WHILE menjadi false.
Sintaks:
Contoh:
Fungsi str() di atas diperlukan untuk mengkonversi bilangan integer menjadi string
(teks).
107 | P a g e

CASE
CASE menyederhanakan IF yang berlapis dengan tujuan agar program dapat
dimengerti/dibaca lebih mudah.
Sintaks:
Contoh:
Hasil:
108 | P a g e

CONTINUE DAN BREAK
Continue dan break berkaitan dengan WHILE. Continue melanjutkan alur program
pada pemeriksaan kondisi WHILE, sedangkan Break mengakibatkan alur program
untuk keluar dari WHILE.

RETURN
RETURN akan menghentikan program dari eksekusi. RETURN dapat digunakan
dalam memproses error. RETURN menyebabkan program yang dieksekusi kembali
ke statement pemanggilnya.
b. Creating and Executing Stored Procedures
Prosedur adalah program yang dapat dipanggil/dieksekusi oleh program lainnya, atau
dieksekusi dari Sql-Prompt seperti ISQL.
Prosedur yang paling sering digunakan adalah stored-procedure yang berawalan sp, dan
extended stored procedure (xp), program yang dikompilasi sebagai EXE atau DLL
(Dynamic Link Library).
Sintaks Membuat prosedur:
Contoh:
Menjalankan prosedur tersebut dengan EXEC atau langsung dengan nama prosedur saja.
Untuk mengkoreksi sebuah prosedur, maka kunci kata ALTER harus digunakan. Untuk
menghapus prosedur gunakan DROP.
109 | P a g e
PARAMETER
Prosedur dapat mempunyai parameter berupa variable yang disuplai oleh program yang
memanggilnya.
Sintaks Membuat prosedur dengan parameter:
Pada saat eksekusi parameter diberikan sebagai berikut:
c. Renaming and Dropping a Stored Procedures
Setelah menjalankan atau membuat (EXEC) dari stored procedure yang telah anda
definisikan, anda dapat mengubah nama dari stored procedure tersebut.
Fakta yang sangat menarik dalam stored procedure disini, bahwa ketika kita merename
dengan menggunakan SP_RENAMEcommand, Stored Procedurenya berhasil diubah
namanya (renamed). Tetapi ketika untuk menskripkannya dengan menggunakan
sp_helptext, stored procedurenya tetap menggunakan nama yang lama alias tidak ke
update
Sebagai contoh, buatlah stored procedure
dengan nama SP_Employee
dalam Database AdventureWorks
110 | P a g e
Maka hasilnya akan menghasilkan output sebagai berikut:
Sekarang anda diminta untuk merename stored procedure dari SP_Employee menjadi
SP_GetEmployee
Contoh:
sp_rename'SP_Employee','SP_GetEmployee'
Maka hasil outputnya akan menghasilkan sebagai berikut:
111 | P a g e
Gunakan sp_helptext untuk melihat isi dari stored procedure yang telah dibuat atau
diubah.
Contoh:
Maka hasil outputnya akan sebagai berikut:
Kita dapat melihat bahwa nama stored procedure yang tertera pada gambar diatas, nama
stored procedurenya tidak mengalami perubahan. Tetapi jika kita ingin mengakses stored
procedure yang ada dalam result tersebut, akan mengalami error
112 | P a g e
Kesimpulan
Ini terjadi dikarenakan ketika stored procedure tersebut diubah namanya, sys.prodecures
nya dalam system table tidak di – update. Solusinya adalah menghapus stroed procedure
yang ada dan membuat kembali yang baru dengan nama yang diinginkan.
d. Error Handling
Untuk membuat suatu error handling, diperlukan sintaks yang namanya @@ERROR.
@@ERROR ini berfungsi sebagai bentuk dari suatu implementasi error handling.
@@ERROR terdiri dari error ID yang dihasilkan dari akhir dari sebuah statement SQL.
Ketika suatu statement di execute, maka @@ERROR adalah 0. Untuk mendeteksi
apakah suatu statement error atau tidaknya berdasarkan dari statement IF yang digunakan
untuk mengecek nilai dari suatu fungsi setelah target telah dieksekusi.
Contoh:
Jika terjadi error dalam error handling tersebut, maka akan muncul tampilan sebagai
berikut:
113 | P a g e
INDEXING
a. Beberapa tipe index:

Clustered index
Clustered index merupakan sebuah indeks yang mengurutkan data pada tabel dan
dapat diumpamakan seperi indeks huruf dalam kamus. Karena data dalam tabel hanya
dapat disimpan dalam satu urutan maka satu tabel hanya dapat memiliki satu
clustered index. Clustered index diimplementasikan sebagai struktur B-tree yang
mendukung pencarian dan pengambilan data yang cepat.

Nonclustered index
Nonclustered index tidak mengurutkan data secara fisikal dan dapat diumpamakan
seperti daftar indeks pada buku. Sebuah tabel dapat memiliki banyak nonclustered
index dan nonclustered index berisi pointer-pointer yang menunjukkan lokasi
sesungguhnya dari data yang dicari saat melakukan pencarian data. Cara ini sedikit
lebih membutuhkan waktu pencarian dibanding dengan metode clustered index.

Unique index
Unique index memastikan bahwa kunci indeks tidak ada yang mengandung nilai-nilai
duplikat.
b. Membuat index
1. Pada ObjectExplorer, klik kanan pada tabel yang ingin diberikan index kemudian klik
Design.
114 | P a g e
2. Kemudian akan muncul TableDesigner.
115 | P a g e
3. Dari menu Table Designer, klik Indexes / Keys
4.
Pada dialogbox Indexes/ Keys, klik Add. List SelectedPrimary / UniqueKeyorIndex
menampilkan index baru.
116 | P a g e
5.
Pilih index baru pada list SelectedPrimary / UniqueKeyorIndex dan atur
propertiesindex pada grid disebelah kanan. Kemudian klik Close.
c. Membuat UniqueIndex
1. Pada Object Explorer, klik kanan pada tabel yang ingin diberikan index kemudian
klik Desain.
2. Kemudian akan muncul Table Designer.
3. Dari menu Table Designer, klik Indexes / Keys
4. Pada dialogbox Indexes / Keys, klik Add. List Selected Primary / Unique Key or
Index menampilkan index baru.
5. Pada grid klik Type. Pilih Index dari drop-down list disebelah kanan properti.
6. Pada ColumnName, pilih kolom yang ingin diberikan index. Index dapat dipilih
sampai 16 kolom. Untuk perfomance yang optimal, pilih hanya 1 atau 2 kolom per
index. Index dapat diatur secara ascending atau descending.
117 | P a g e
7. Kembali ke grid, pilih isUnique. Pilih Yes.
8. Pilih pilihan Ignore duplicate keysjika ingin mengabaikan data yang baru atau
diupdate yang akan membuat sebuah duplicate keys pada index (dengan statement
INSERT atau UPDATE).
118 | P a g e
9. Index baru akan disimpan didatabase ketika menyimpan tabel.
d. Membuat ClusteredIndex
1. Pada Object Explorer, klik kanan pada tabel yang ingin diberikan index kemudian
klik Desain.
2. Kemudian akan muncul Table Designer.
3. Dari menu Table Designer, klik Indexes / Keys.
4. Pada dialog box Indexes / Keys, klik Add.
5. Pilih index baru yang ada di list Selected Primary / Unique Key or Index.
6. Pada grid, pilih Create as Clustered, dan pilih Yes dari drop-down list pada
properties disebelah kanan.
119 | P a g e
7. Index baru akan disimpan didatabase ketika menyimpan tabel.
e. Mengubah propertiesindex
1. Pada Object Explorer, klik kanan pada tabel yang memiliki index yang ingin diubah
propertiesnya.
2. Dari menu Table Designer, klik Indexes / Keys.
3. Pilih index pada list Selected Primary / Unique Key or Index.
4. Ubah properties index pada grid.
5. Perubahan akan disimpan ketika tabel disimpan.
f. Menghapus index
1. Pada Object Explorer, klik kanan pada tabel yang ingin diberikan index kemudian
klik Desain.
2. Dari menu Table Designer, klik Indexes / Keys.
3. Pada list Selected Primary / Unique Key or Index, klik index yang ingin dihapus.
4. Klik Delete.
5. Index akan dihapus dari database ketika tabel disimpan.
120 | P a g e
Create new index (Query Analyzer)
Secara optional, di Index options, pilih:
o Unique values untuk membuat unique index.
o Clustered index untuk membuat cluster index, Jika sebuah clustered index telah
ada sebelumnya, option ini tidak akan muncul.
o Ignore duplicate balues untuk mengontrol apa yang terjadi ketika INSERT
statement memasukkan banyak, nonunique key values ke dalam sebuah index.
o Do not recomputed statistics (not recommended) untuk menspesifikasi statistic
dari suatu index agar tidak otomatis di hitung kembali sebagai index yang di
update.
o Pad index untuk membiarkan space open di tiap interior node pada index.
o Drop existing untuk menghapus index yang namanya sama yang ada sebelum
membuat index yang baru.
o FILL Factor untuk menspesifikasi betapa penuhnya SQL Server seharusnya
membuat leaf level dari tiap index page selama pembuatan index.
121 | P a g e
Membuat index dengan Transact-SQL
Contoh:
122 | P a g e
Menghapus index dengan Transact-SQL
Contoh:
123 | P a g e
Chapter 08
Database Design
Objectives
1. Database Concept
2. Multiplicity
3. Data Relational
4. Normalization
Learning Outcomes
1. Apply database language and SQL Programming language
2. Design database using structure data model
124 | P a g e
8.1.
Database Concept
 Conceptual model:Merupakan pengumpulan / integrasi seluruh kebutuhan atribut dari
para user / aplikasi menjadi satu pandangan organisasi
 Logical model:Dari model conceptual yang terbentuk dapat dipilih salah satu model
data dasar logikal : hirarki, network atau relational. Kemudian dibagikan kepada para
user yang berwenang
 Physical model:Bagaimana secara fisik data tersimpan pada penyimpanan sekunder,
yang perlu dipertimbangkan mengenai metode akses (menyimpan / stored dan
mengambil / retrieval) dan teknik pengindeksan untuk retrieval (pencarian lebih
cepat).
8.2.
Multiplicity
Bertujuan untuk mengidentifikasi entity dan hubungan (relationship ) pada model data
Ada 3 macam hubungan :
1. One To One : Terjadi bila tiap anggota entity A hanya boleh berpasangan dengan
satu anggota
2. One To Many : Terjadi bila tiap anggota entity A boleh berpasangan dengan lebih
dari satu anggota B tetapi tidak berlaku sebaliknya
125 | P a g e
3. Many To Many : Terjadi bila tiap anggota A boleh berpasangan dengan lebih dari
satu anggota B dan berlaku untuk sebaliknya
mhs.stiki.ac.id/boysagi/Software/bdl/bukudb11.doc
8.3.
Data Relational
Relasi adalah hubungan sebuah tabel yang terdiri dari beberapa kolom dan beberapa
baris.
A. Berikut adalah isu-isu yang terdapat dalam pembahasan Data relationship:
1. Atribut adalah Kolom pada sebuah relasi
2. Tupel adalah Baris pada sebuah relasi
3. Domain adalah Kumpulan nilai yang valid untuk satu atau lebih atribut
4. Derajat (degree) adalah Jumlah atribut dalam sebuah relasi
5. Cardinality adalah Jumlah tupel dalam sebuah relasi
126 | P a g e
B. Dalam Data Relasional terdapat pula Relational Key , yang terdiri dari :
1. Super key yakni, Satu atribut/kumpulan atribut yang secara unik mengidentifikasi
sebuah tupel di dalam relasi
2. Candidate key yakni, Atribut di dalam relasi yang biasanya mempunyai nilai unik
dan merupakan Field-field yang bisa dipilih (dipakai) menjadi primary key.
3. Primary key yakni, Candidate key yang dipilih untuk mengidentifikasikan tupel
secara unik dalam relasi dan merupakan Field yang mengidentifikasikan sebuah
record dalam file, bersifat unik.
4. Alternate key yakni, Candidate key yang tidak dipilih sebagai primary key.
127 | P a g e
5. Foreign key yakni, Atribut dengan domain yang sama yang menjadi kunci utama
pada sebuah relasi tetapi pada relasi lain atribut tersebut hanya sebagai atribut biasa.
Foreign Key ini adalah Field yang bukan key, tetapi adalah key pada file yang lain.
8.4.
Normalization
Untuk merancang database yang baik, biasa dilakukan normalisasi. Normalisasi
merupakan sebuah teknik untuk menghasilkan set relasi dengan property yang desirable
dan memberikan data sesuai dengan kebutuhan enterprise.
Tujuan normalisasi yaitu:

mengidentifikasi hubungan antar atribut

mengkombinasikan atribut untuk membentuk relasi

mengkombinasikan relasi untuk membentuk database

menghindari anomaly
Proses normalisasi:
1. UNF
Dalam proses normalisasi UNF kita menampilkan semua field atau atribut yang ada
dalam suatu form yang ingin kita normalisasi.
2. 1NF
Sebuah relasi berada dalam 1NF jika relasi tersebut tidak berisi atribut yang berulang
(repeating group), field hasil perhitungan dihilangkan dan sudah mempunyai primary
key.
128 | P a g e
3. 2NF
Sebuah relasi berada dalam 2NF jika relasi tersebut dalam 1NF dan untuk setiap
atribut non key bergantung fungsional penuh kepada primary key. Jadi pada 2NF kita
akan menghilangkan ketergantungan sebagian / partial : ketergantungan field-field
tertentu hanya kepada salah satu key yang composit.
Contoh :
Tabel Mahasiswa ( Nim, Nama, Alamat )
Nama & alamat tergantung pada Nim dalam arti dengan Nim kita dapat menentukan
nama maupun alamat sebaliknya nama / alamat tidak menentukan nim, maka
diartikan bahwa nama & alamat tergantung secara partial kepada nim.
4. 3NF
Sebuah relasi berada dalam 3NF bila relasi tersebut dalam 1NF dan 2NF dan tidak
ada atribut non key yang tergantung fungsional kepada atribut non key yang lainnya (
transitive dependency ).
Contoh :
Tabel Pegawai ( NoPegawai, honor, KdProyek, Tanggal )
KdProyek & Tanggal adalah atribut non key. Tapi tanggal bergantung pada
KdProyek. Pemecahannya dengan membagi menjadi 2 relasi :
Proyek ( KdProyek, Tanggal )
PegProyek ( Nopegawai, honor, KdProyek )
5. BCNF ( Bentuk Normal Boyce-Codd, 1974 )
Relational R dikatakan dalam BCNF jika dan hanya jika setiap determinan adalah
suatu candidate key sehingga saling overlap. Tujuan BCNF adalah memisahkan
determinan yang bukan merupakan candidate key pada satu relasi tertentu.
Kodisinya :
A, B -> C, D
A, C -> B, D
B, C -> A, D
129 | P a g e
Contoh:
Tabel interview ( NoClient, tanggalInterview, waktuInterview, NoStaff, NoRuang )
Dipisahkan menjadi :
Tabel Interview ( NoClient, tanggalInterview, waktuInterview, NoStaff )
Tabel RuangStaff ( NoStaff, tanggalInterview, NoRuang )
Berikut adalah latihan membuat Normalisasi:
Sales Order
UNF
Sales Order (No. SO, Payment Type, Customer ID, Customer Name, Customer
Contact, Customer Address, {No, Lego ID, Lego Name, Category, Sale Price,
Quantity, Sub Total}, Grand Total, SO Date, Employee Name, Position)
130 | P a g e
1NF
Sales Order (No. SO, Customer ID, Payment Type, Customer Name, Customer
Contact, Customer Address, SO Date, Employee Name, Position)
Detail SO (No. SO, Lego ID, Lego Name, Category, Sale Price, Quantity)
2NF
Sales Order (No. SO, Customer ID, Payment Type, Customer Name, Customer
Contact, Customer Address, SO Date, Employee Name, Position)
Detail SO (No. SO, Lego ID, Quantity)
Lego (Lego ID, Lego Name, Category, Sale Price)
3NF
Customer (Customer ID, Customer Name, Customer Contact, Customer Address)
Customer Category(Customer ID, Category)
Position (Position ID, Position)
Employee (Employee ID, Position ID, Employee Name, Employee Contact,
Employee Address, Employee Salary)
Payment Type (Payment Type ID, Payment Type)
Sales Order (No. SO, Payment Type ID, Customer ID, Employee ID, SO Date)
Detail SO (No. SO, Lego ID, Quantity)
Category (Category ID, Category)
Lego (Lego ID, Category ID, Lego Name, Sale Price, Stock)
131 | P a g e
Purchase Order
UNF
Purchase Order (No. PO, Supplier ID, Supplier Name, Supplier Contact, Supplier
Address, {No,Lego ID,Lego Name, Category, Purchase Price, Quantity, Sub Total},
Grand Total, PO Date, Employee Name, Position)
1NF
Purchase Order (No. PO, Supplier ID, Supplier Name, Supplier Contact, Supplier
Address, PO Date, Employee Name, Position)
Detail PO (No. PO, Lego ID, Lego Name, Category, Purchase Price, Quantity)
132 | P a g e
2NF
Purchase Order (No. PO, Supplier ID, Supplier Name, Supplier Contact, Supplier
Address, PO Date, Employee Name, Quantity)
Detail PO (No. PO, Lego ID, Quantity)
Lego (Lego ID, Lego Name, Category, Purchase Price)
3NF
Supplier (Supplier ID, Supplier Name, Supplier Contact, Supplier Address)
Position (Position ID, Position)
Employee(Employee ID, Position ID, Employee Name, Employee Contact, Employee
Address, Employee Salary)
Purchase Order (No. PO, Supplier ID, Employee ID, PO Date)
Detail PO (No. PO, Lego ID, Quantity)
Category (Category ID, Category)
Lego (Lego ID, Category, Lego Name, Purchase Price, Stock)
133 | P a g e
Registration
UNF
Registration (No.Registration, Member ID, Member Name, Member Contact,
Member Address, Member Email, Member Type, Registration Price, Registration
Date, Employee Name, Position)
1NF
Registration (No.Registration, Member ID, Member Name, Member Contact,
Member Address, Member Email, Payment Type, Registration Price, Registration
Date, Employee Name, Position)
134 | P a g e
2NF
Registration (No.Registration, Member ID, Member Name, Member Contact,
Member Address, Member Email, Payment Type, Registration Price, Registration
Date, Employee Name, Position)
3NF
Member Category(Member Type ID, Member Type, Diskon)
Member (Member ID, Member Type ID, Member Name, Member Contact, Member
Address, Member Email)
Position(Position ID, Position)
Employee(Employee ID, Position ID, Employee Name, Employee Contact, Employee
Address, Employee Salary)
Registration(No.Registration, Member ID, Employee ID, Registration Date)
135 | P a g e
Berikut adalah contoh design Form: