modul 2 sql server 2005

L S I K T I I T B 2 0 0 9

MODUL PRAKTIKUM

TI-4006 ANALISIS

PERANCANGAN SISTEM

INFORMASI

Pengenalan

Microsoft

SQL Server

2005

M O D U L P R A K T I K U M T I - 4 0 0 6 A N A L I S I S P E R A N C A N G A N S I S T E M I N F O R M A S I

Pengenalan Microsoft SQL Server 2005

Laboratorium Sistem Informasi dan Keputusan

Program Studi Teknik Industri

Gedung Laboratorium Teknologi III

Institut Teknologi Bandung

2008

Data Retrieval (Basic) Tujuan:

• Mengenal aturan dasar penulisan SQL dalam Relational Database Management

System (RDBMS) Microsoft SQL Server 2005.

• Mampu menggunakan perintah-perintah dasar pengambilan data tabel (data

retrieval) dan operasi-operasi aritmetik, logika, dan pembanding.

• Mampu membuat dan mendefinisikan data dalam penyusunan tabel.

• Mampu memanipulasi berbagai atribut dalam tabel.

Pendahuluan Dalam Relational Database Management System (RDBMS), data direpresentasikan dalam bentuk

tabel dan relasi. Query yang akan ditampilkan juga akan berbentuk tabel. Sebuah tabel dapat

berisikan satu atau lebih kolom. Sebuah kolom disusun dengan nama kolom dan tipe data yang

menjelaskan atribut setiap record. Struktur dari tabel disebut juga relation schema, selanjutnya

akan didefinisikan melalui atribut-atributnya. Jadi setiap informasi yang akan disimpan harus

sesuai dengan tipe data atribut pada waktu tabel pertama kali dibuat.

SQL menggunakan aturan tabel, baris, dan kolom untuk relasi, record dan atribut. Dalam modul

ini kita akan otomatis menggunakan aturan tersebut.

Sebuah tabel dapat terdiri dari berbagai tipe data dan domain. Microsoft SQL Server mengenal

tipe data berikut ini.

Data Types

NCHAR Unicode Fixed-length character data

NVARCHAR Unicode Variable-length character data

REAL Decimal numeric data

INT Integer numeric data

DATETIME Date and Time data

UNIQUEIDENTIFIER Globally Unique Identifier data

Modul

2

3

SQL Statement Rules SQL Statements

SELECT Data Retrieval

INSERT

Data Manipulation Language UPDATE

DELETE

CREATE

Data Definition Language

ALTER

DROP

RENAME

TRUNCATE

� SQL statement tidak bergantung pada huruf besar atau kecil, tetapi lebih baik jika

konsisten dalam penulisannya. Konvensi yang disepakati adalah untuk keyword

menggunakan UPPER CASE dan nama-nama tabel/kolom menggunakan Pascal Case.

� SQL statement dapat lebih dari satu baris, dan diakhiri dengan tanda ; (titik koma).

� Kata-kata kunci (keyword) tidak dapat disingkat atau dipisah berbeda baris.

� Tabulasi (tabs) dan indentasi biasanya digunakan untuk memudahkan pembacaan.

Selecting Columns Dalam SQL, sebuah query memiliki formula yang tetap (komponen dalam tanda kurung [] adalah

opsional).

SELECT [DISTINCT] <column(s)>

FROM <table>

[WHERE <condition>]

[ORDER BY <column(s) [ASC|DESC]>];

4

Kolom yang akan dipilih dari sebuah tabel ditulis setelah keyword SELECT. Operasi ini disebut juga

projection. Contoh query-nya:

SELECT DepartmentID, [Name]

FROM HumanResources.Department;

Hint !

• Ditampilkan seluruh baris pada kolom DepartementID dan Name dari tabel

HumanResources.Department

• […] wajib digunakan saat nama kolom mengandung spasi

Misal “Departement ID” akan dibaca sebagai 2 hal berbeda, yaitu “Departement” dan

“ID” maka harus dituliskan dengan [Departement ID]

DepartmentID Name

12 Document Control

1 Engineering

16 Executive

... ...

Jika hendak menampilkan semua kolom maka dapat digunakan simbol “ * ”.

SELECT *

FROM HumanResources.Department

Hint !

• Ditampilkan seluruh baris dan kolom dari tabel HumanResources.Department

DepartmentID Name GroupName ModifiedDate

1 Engineering Research and Development 1998-06-01 00:00:00.000

2 Tool Design Research and Development 1998-06-01 00:00:00.000

3 Sales Sales and Marketing 1998-06-01 00:00:00.000

... ... ... ...

Query tersebut akan menampilkan semua record dengan seluruh kolomnya dari tabel

HumanResources.Department.

5

Klausa SELECT juga dapat berisi ekspresi aritmetik yang melibatkan operator aritmetik.

SELECT EmployeeID, VacationHours, VacationHours + 24

FROM HumanResources.Employee;

Hint !

• Ditampilkan seluruh baris pada kolom EmployeeID, Vacation Hours, dan 1 kolom baru dari

tabel HumanResources.Department

• Setiap baris i pada kolom baru tersebut berisi data xi

dimana xi = (data pada baris yang sama di VacationHours) + 24

EmployeeID VacationHours (No column name)

1 21 45

2 42 66

3 2 26

... ... ...

Operator dan fungsi lain yang di-support oleh Microsoft SQL Server antara lain:

� Untuk numeric data : ABS, COS, SIN, EXP, LOG, POWER, MOD, SQRT, +, -, *, /

� Untuk character data : LOWER, UPPER, LENGTH

Dalam SQL ini, dikenal juga operator precedence, yaitu jika terdapat lebih dari satu operator

aritmetik maka perkalian dan pembagian mendapat prioritas pertama, lalu tambah dan kurang

mendapat prioritas berikutnya. Jika operator aritmetik memiliki prioritas yang sama, maka urutan

pengerjaan dimulai dari kiri ke kanan. Apabila dikehendaki suatu operasi dikerjakan terlebih

dahulu, maka operasi dapat diapit tanda kurung ().

6

Perhatikan penulisan SQL berikut ini:

SELECT Title

FROM HumanResources.Employee

Hint !

• Ditampilkan seluruh baris pada kolom Title dari tabel HumanResources.Employee

Title

Accountant

Accountant

Accounts Manager

...

Hasil dari query ini adalah list Title untuk setiap Employee. Terdapat Title yang berulang, hal ini

disebabkan setiap record yang ditemukan akan langsung ditampilkan (tidak ada pengecekan

duplikasi). Untuk mengatasinya dapat digunakan klausa DISTINCT setelah keyword SELECT

(pemaksaan sepihak penghilangan duplikasi pada hasil query).

SELECT DISTINCT Title


Hint !

• Ditampilkan hanya baris dengan data yang unik pada kolom Title dari tabel

HumanResources.Employee

Title

Accountant

Accounts Manager

Accounts Payable Specialist

...

7

Sebuah kolom bisa diberi nama lain (alias) dalam hasil query. Namun perlu diperhatikan bahwa

nama yang diberikan hanya pada penamaan kolom hasil query BUKAN mengubah nama field

tabel. Pemberian nama ini dengan menggunakan keyword AS.

SELECT EmployeeID AS ID, NationalIDNumber AS ID2


Hint !

• Ditampilkan semua baris pada kolom EmployeeID sebagai kolom ID dan kolom

NationalIDNumber sebagai kolom ID2 dari tabel HumanResources.Employee

ID1 ID2

187 10708100

230 109272464

273 112432117

... ...

SQL juga mendukung perintah untuk melakukan sorting hasil query yang akan ditampilkan. Hal ini

dapat dilakukan dengan menambah klausa ORDER BY yang diikuti satu atribut atau lebih sesuai

dengan yang ditampilkan setelah klausa SELECT. DESC menunjukkan descending order, sedangkan

ASC menunjukkan ascending order. Secara default, bila tidak dituliskan ASC atau DESC maka SQL

akan menganggap kolom yang bersangkutan adalah ASC.

SELECT EmployeeID, ManagerID, Gender


ORDER BY ManagerID, Gender DESC;

Hint !

• Ditampilkan seluruh baris pada kolom EmployeeID, ManagerID, dan Gender dari tabel

HumanResources.Employee dengan diurutkan berdasarkan data pada kolom ManagerID

kemudian kolom Gender secara descending

EmployeeID ManagerID Gender

109 NULL M

4 3 M

11 3 M

... ... ...

8

Selecting Records Pembahasan akan difokuskan pada pemilihan atribut dari setiap record dari tabel. Jika record

yang diinginkan memiliki kondisi tertentu, maka klausa WHERE dapat digunakan. Dalam klausa

WHERE, kondisi sederhana berdasarkan operator pembanding hingga dikombinasikan dengan

operator logika AND, OR, dan NOT untuk mendapatkan kondisi yang lebih kompleks.

SELECT EmployeeID, Title


WHERE Title = 'Design Engineer';

Hint !

• Ditampilkan seluruh baris pada kolom EmployeeID dan Title dari tabel

HumanResources.Employee, namun hanya yang memiliki title : Design Engineer

EmployeeID Title

9 Design Engineer

11 Design Engineer

270 Design Engineer

Contoh:

List the Job Title of Employees whose ManagerID is smaller than 10 :

SELECT Title


WHERE ManagerID < 10;

Hint !

Ditampilkan seluruh baris dari kolom Title dari tabel HumanResources.Employee, namun hanya

yang memiliki managerID kurang dari 10

9

Untuk setiap tipe data, operator pembanding seperti =, != atau <>, <, >, <=, => diperbolehkan

untuk pengkondisian klausa WHERE.

Operator pembanding lain:

� Set Conditions : <column> [NOT] IN (<list of values>)

Contoh:

SELECT *


WHERE ManagerID IN (20,30);

Hint !

Ditampilkan seluruh baris dari tabel HumanResources.Employee, namun hanya yang memiliki

managerID 20 dan 30. Untuk record yang memiliki managerID selain 20 atau 30 tidak ditampilkan

� Null value: <column> IS [NOT] NULL

Contoh:

SELECT *


WHERE ManagerID IS NULL;

Hint !

Ditampilkan seluruh baris dari tabel HumanResources.Employee, namun hanya yang Tidak (is

null) memiliki manager. Jadi untuk Employee yang memiliki manager (managerID) tidak

ditampilkan

� Domain conditions: <column> [NOT] BETWEEN <lower bound> AND <upper bound>

Contoh:

SELECT *


WHERE ManagerID BETWEEN 1 AND 10;

Hint !

Ditampilkan seluruh baris dari tabel HumanResources.Employee, namun hanya yang memiliki

managerID antara 1 s.d. 10. Jadi untuk Employee yang memiliki manager dengan managerID

diluar range tersebut, tidak ditampilkan.

String Operations Contoh operasi string:

� UPPER (<string>): mengambil string dan mengubah setiap huruf menjadi huruf besar.

� LOWER (<string>): mengubah setiap huruf menjadi huruf kecil.

� LENGTH (<string>): menghitung jumlah karakter dalam kata <string>.

10

Aggregate Functions Fungsi agregasi di sini adalah fungsi statistik seperti COUNT, MIN, MAX dan lain-lain.

� COUNT : menghitung jumlah baris dari perintah SELECT

Berapa banyak record yang ada di dalam tabel Employee?

SELECT COUNT(*)


Hint !

Ditampilkan jumlah seluruh baris dari tabel HumanResources.Employee

Berapa banyak job title yang berbeda yang ada di dalam tabel Employee?

SELECT COUNT (DISTINCT Title)


Hint !

Ditampilkan jumlah baris dengan data yang unik pada kolom Title dari tabel

HumanResources.Employee. Jadi yang memiliki nilai Title yang sama hanya

dihitung sekali.

� MAX : menghitung nilai maksimum dari sebuah kolom

� MIN : menghitung nilai minimum dari sebuah kolom

Tampilkan jam sakit minimum dan maksimum dari tabel Employee!

SELECT MIN(SickLeaveHours), MAX(SickLeaveHours)


Hint !

Ditampilkan nilai minimum dari nilai Sickleavehours dan nilai maksimum dari nilai

SickLeaveHours dari tabel HumanResources.Employee.

Hitung selisih antara jam sakit maksimum dan minimum dari tabel Employee!

SELECT MAX(SickLeaveHours) - MIN(SickLeaveHours)


Hint !

Ditampilkan selisih dari nilai minimum Sickleavehours dan nilai maksimum dari

nilai SickLeaveHours dari tabel HumanResources.Employee.

11

� SUM : menghitung jumlah dari sekumpulan nilai (hanya berlaku untuk tipe data

number)

Hitung jumlah jam sakit dari pekerja yang bekerja di bawah ID manager 3!

SELECT SUM(SickLeaveHours)


WHERE ManagerID = 3;

Hint !

Ditampilkan nilai total data pada kolom Sickleavehours dari tabel

HumanResources.Employee, namun hanya milik pegawai yang bekerja dibawah

manager dengan managerID =3.

� AVG : menghitung nilai rata-rata dari sebuah kolom (hanya berlaku untuk tipe data

number).

Perhatikan bahwa perintah AVG, MIN, dan MAX mengabaikan record dengan nilai null (null value)

untuk atribut terkait, tetapi COUNT mengikutkan nilai null.

Selain klausa ORDER BY, terdapat klausa GROUP BY sebagai penambahan fungsi agregrat untuk

menampilkan data sesuai kategori pengelompokan yang diinginkan. Klausa ini biasanya diikuti

dengan klausa HAVING untuk menunjukkan persyaratan kriteria dari kategori yang telah ditunjuk

pada klausa GROUP BY. Di mana satu group akan terdiri atas banyak field lain yang akan

ditampilkan.

SELECT ManagerID, MIN (EmployeeID), MIN (Gender)


GROUP BY ManagerID

HAVING MIN (EmployeeID) > 3;

Hint !

Ditampilkan nilai managerID, nilai minimum EmployeeID dan nilai minimum Gender dari

tabel HumanResources.Employee dengan mengelompokkan berdasarkan managerID

yang nilai minimum employeeID nya lebih dari 3

ManagerID EmployeeID Gender

3 4 F

4 6 M

5 8 M

... ... ...

12

Creating, Modifying, and Dropping Tables

Creating Tables

Perintah SQL untuk membuat tabel yang kosong adalah:

CREATE TABLE <table name> ( <column 1> <data type> [ NOT NULL] [ UNIQUE] [<column constraint>], ... <column n> <data type> [ NOT NULL] [ UNIQUE] [<column constraint>], [<table constraint(s)>] );

Untuk setiap kolom, nama dan tipe data harus ditentukan dan setiap nama kolom harus unik.

Definisi kolom dipisahkan dengan tanda koma.

Tidak ada perbedaan huruf besar maupun huruf kecil dalam pemberian nama. Penggunaan perbedaan huruf besar kecil hanya pada operasi pembanding string.

Konstrain NOT NULL ditentukan setelah penentuan tipe data kolom, konstrain membutuhkan nilai atribut yang telah didefinisikan untuk kolom yang bersangkutan, yang bukan null.

Keyword UNIQUE menunjukkan bahwa tidak ada dua buah record yang dapat memiliki nilai atribut yang sama untuk kolom tersebut.

Jika kondisi NOT NULL tidak ditentukan untuk sebuah kolom, maka nilai atribut null diperbolehkan dan dua buah record yang memiliki nilai atribut null untuk kolom tersebut tidak melanggar konstrain.

Contoh: Pernyataan Create Table untuk tabel HumanResources.EmployeeAdress mempunyai bentuk:

CREATE TABLE [HumanResources].[EmployeeAddress]( [EmployeeID] [int] NOT NULL, [AddressID] [int] NOT NULL, [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_EmployeeAddress_rowguid] DEFAULT (newid()), [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_EmployeeAddress_ModifiedDate] DEFAULT (getdate()), CONSTRAINT [PK_EmployeeAddress_EmployeeID_AddressID] PRIMARY KEY CLUSTERED ( [EmployeeID] ASC, [AddressID] ASC ) )

Catatan : kecuali untuk kolom EmployeeID dan AddressID, nilai Null (kosong) diperbolehkan.

13

Tipe-tipe data sudah dijelaskan pada modul I terdahulu, namun karena pemakaian penting baru di modul ini maka penjelasan tipe data diulang.

� Exact Numerics

1. BIGINT, angka dengan jangkauan nilai dari -263 (-9,223,372,036,854,775,808) sampai 263 - 1 (9,223,372,036,854,775,807) dan membutuhkan tempat penyimpanan 8 byte.

2. INT, angka dengan jangkauan nilai dari -231 (-2,147,483,648) sampai 231 - 1 (2,147,483,647) dan membutuhkan tempat penyimpanan 4 byte.

3. SMALLINT, angka dengan jangkauan nilai dari -215 (-32,768) sampai 215 - 1 (32,767) dan membutuhkan tempat penyimpanan 2 byte.

4. TINYINT, angka dengan jangkauan nilai dari 0 sampai 255 dan membutuhkan tempat penyimpanan 1 byte.

5. BIT, angka dengan jangkauan nilai dari 0 sampai 1 dan membutuhkan tempat penyimpanan 1 byte untuk tabel yang berisi 8 baris dan seterusnya.

6. DECIMAL[(p[, s])] dan NUMERIC[(p[, s])], presisi dan angka skala yang tetap dengan jangkauan nilai dari -1038 + 1 sampai 1038 – 1 dan membutuhkan tempat penyimpanan dari 5 sampai 17 byte.

p (presisi), total angka maksimum dari digit desimal yang dapat disimpan di sisi kiri dan kanan tanda desimal. Jangkauan nilai presisi adalah mulai dari 1 sampai 38, dengan nilai default 18.

s (skala), angka maksimum dari digit desimal yang dapat disimpan di sebelah kanan tanda desimal dengan jangkauan nilai dari 0 sampai p, dengan nilai default 0 sehingga 0 <= s <= p. Skala hanya dapat didefinisikan jika p sudah didefinisikan.

7. MONEY, angka mata uang dengan jangkauan nilai dari -922,337,203,685,477.5808 sampai 922,337,203,685,477.5807 dan membutuhkan tempat penyimpanan 8 byte.

8. SMALLMONEY, angka mata uang dengan jangkauan nilai dari -214,748.3648 to 214,748.3647 dan membutuhkan tempat penyimpanan 4 byte.

� Approximate Numerics

1. FLOAT(n), angka aproksimasi dengan jangkauan nilai dari -1.79E+38 sampai -2.23E-38, 0 dan 2.23E-38 sampai 1.79E+38 dan membutuhkan tempat penyimpanan dari 4 dan 8 byte. Nilai n memiliki jangkauan dari 1 sampai 53.

2. REAL, angka aproksimasi dengan jangkauan nilai dari -1.18E-38, 0, dan 1.18E-38 sampai 3.40E+38 dan membutuhkan tempat penyimpanan 4 byte.

14

� Date and Time

1. DATETIME, tanggal dan waktu dengan jangkauan nilai dari 1 Januari 1753 sampai 31 Desember 9999 dan memiliki presisi 3.33 milidetik.

2. SMALLDATETIME, tanggal dan waktu dengan jangkauan nilai dari 1 Januari 1900 sampai 6 Juni 2079 dan memiliki presisi 1 menit.

� Character Strings

1. CHAR(n), karakter non-Unicode yang panjangnya bersifat tetap terhadap kapasitas penyimpanan yang didefinisikan oleh n dan membutuhkan tempat penyimpanan sebesar n byte. Nilai n memiliki jangkauan dari 1 sampai 8,000.

2. VARCHAR(n atau max), karakter non-Unicode yang panjangnya bersifat variabel terhadap kapasitas penyimpanan yang didefinisikan oleh n dan membutuhkan tempat penyimpanan sebesar [jumlah karakter yang disimpan + 2] byte. Nilai n memiliki jangkauan dari 1 sampai 8,000 dan max bernilai 231 – 1 byte.

3. TEXT, karakter non-Unicode yang panjangnya bersifat variabel dengan jumlah karakter maksimum sebesar 231 - 1 (2,147,483,647) dan membutuhkan tempat penyimpanan sebesar [jumlah karakter yang disimpan] byte.

� Unicode Character Strings

1. NCHAR, karakter Unicode yang panjangnya bersifat tetap terhadap kapasitas penyimpanan yang didefinisikan oleh n dan membutuhkan tempat penyimpanan sebesar 2 kali n byte. Nilai n memiliki jangkauan dari 1 sampai 4,000.

2. NVARCHAR(n atau max), karakter Unicode yang panjangnya bersifat variabel terhadap kapasitas penyimpanan yang didefinisikan oleh n dan membutuhkan tempat penyimpanan sebesar 2 kali jumlah karakter yang disimpan + 2 byte. Nilai n memiliki jangkauan dari 1 sampai 4,000 dan max bernilai 230 – 1 byte.

3. NTEXT, karakter Unicode yang panjangnya bersifat variabel dengan jumlah karakter maksimum sebesar 230 - 1 (1,073,741,823) dan membutuhkan tempat penyimpanan sebesar [2 kali jumlah karakter yang disimpan] byte.

� Binary Strings

1. BINARY (n), data biner yang panjangnya bersifat tetap terhadap kapasitas penyimpanan yang didefinisikan oleh n dan membutuhkan tempat penyimpanan sebesar n byte. Nilai n memiliki jangkauan dari 1 sampai 8,000.

2. VARBINARY (n atau max), data biner yang panjangnya bersifat variabel terhadap kapasitas penyimpanan yang didefinisikan oleh n dan membutuhkan tempat penyimpanan sebesar [jumlah data yang disimpan + 2] byte. Nilai n memiliki jangkauan dari 1 sampai 8,000 dan max bernilai 231 – 1 byte.

15

3. IMAGE, data biner yang panjangnya bersifat variabel dengan jumlah data maksimum sebesar 231 - 1 (2,147,483,647) dan membutuhkan tempat penyimpanan sebesar [jumlah karakter yang disimpan] byte.

� Tipe Data yang Lain

1. TIMESTAMP, angka biner yang unik, yang secara otomatis dihasilkan ketika terjadi perubahaan dalam suatu row pada suatu tabel dan membutuhkan tempat penyimpanam sebesar 8 byte.

2. UNIQUEIDENTIFIER, data globally unique indentifier (GUID) yang membutuhkan tempat penyimpanan sebesar 16 byte. Contoh: 6F9619FF-8B86-D011-B42D-00C04FC964FF.

� Note :

� Dalam penggunaan tipe data, direkomendasikan untuk menggunakan unicode character string dibanding menggunakan character string. Ini dikarenakan Unicode bisa menampung lebih banyak jenis character.

16

Modifying Tables

Modifikasi struktur tabel seperti mengubah tipe data kolom, menambah kolom, dll. dimungkinkan dalam SQL. Perintah yang digunakan adalah ALTER TABLE.

ALTER TABLE [table_name] ADD ([column_name][ DEFAULT expr] [column datatype]…); ALTER TABLE [table_name] MODIFY ([column_name][ DEFAULT expr] [column datatype]…);

Contoh :

� Tambahkan kolom Phone ke tabel HumanResources.EmployeeAddress!

ALTER TABLE HumanResources.EmployeeAddress ADD Phone VARCHAR(10);

Hint !

Merubah Table employee dengan menambah kolom baru yakni kolom phone dengan

tipe Char 10

Catatan :

� Kolom yang ditambahkan tidak dapat ditentukan letaknya dalam tabel, kolom tersebut akan muncul sebagai kolom terakhir.

� Jika tabel yang ditambahkan kolom telah berisi data sebelumnya, maka kolom yang baru tersebut akan bernilai NULL setiap barisnya.

17

Dropping Tables

Contoh:

� Hapus kolom Phone dari tabel HumanResources.EmployeeAddress!

ALTER TABLE HumanResources.EmployeeAddress DROP COLUMN Phone;

Hint !

Merubah Table EmployeeAddress dengan menghapus kolom yakni kolom phone

Penghapusan tabel digunakan perintah DROP TABLE dan untuk menghapus seluruh baris atau isi tabel dalam suatu tabel TRUNCATE TABLE.

Contoh:

� Hapus tabel HumanResources.EmployeeAddress!

DROP TABLE HumanResources.EmployeeAddress;

� Hapus semua record dalam tabel HumanResources.EmployeeAddress!

TRUNCATE TABLE HumanResources.EmployeeAddress;

Pada tahap mendefinisikan tabel perlu ada faktor yang perlu diperhatikan yaitu masalah

konstrain (constraints). Konstrain di sini merupakan batasan aturan dalam tabel, misalnya konstrain akan menghalangi penghapusan sebuah tabel bila tabel tersebut telah memiliki relasi dengan tabel lain. Macam-macam konstrain yang diperkenalkan oleh Microsoft SQL Server antara lain: � NOT NULL (Kolom yang bersangkutan tidak dapat bernilai NULL)

� UNIQUE (Nilai dalam kolom tersebut harus unik untuk seluruh baris yang ada)

� PRIMARY KEY (Identitas unik dalam setiap baris dalam tabel)

� FOREIGN KEY (Identitas untuk membuat relasi antar tabel)

Aturan penulisan konstrain,

CREATE TABLE [schema.]table (column datatype [ DEFAULT expr] [column_constraint], ... [table_constraint][, ...]);

18

Contoh :

CREATE TABLE [HumanResources].[Employee]( [EmployeeID] [int] IDENTITY (1,1) NOT NULL, [NationalIDNumber] [nvarchar](15) NOT NULL, [ContactID] [int] NOT NULL, [LoginID] [nvarchar](256) NOT NULL, [ManagerID] [int] NULL, [Title] [nvarchar](50) NOT NULL, [BirthDate] [datetime] NOT NULL, [MaritalStatus] [nchar](1) NOT NULL, [Gender] [nchar](1) NOT NULL, [HireDate] [datetime] NOT NULL, [SalariedFlag] [dbo].[Flag] NOT NULL CONSTRAINT [ DF_Employee_SalariedFlag] DEFAULT(1), [VacationHours] [smallint] NOT NULL CONSTRAINT[DF_Employee_VacationHours] DEFAULT(0), [SickLeaveHours] [smallint] NOT NULL CONSTRAINT DF_Employee_SickLeaveHours] DEFAULT (0), [CurrentFlag] [dbo].[Flag] NOT NULL CONSTRAINT [DF_Employee_CurrentFlag] DEFAULT(1), [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_Employee_rowguid] DEFAULT(newid()), [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Employee_ModifiedDate] DEFAULT(getdate()), CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED ( [EmployeeID] ASC ) ON [ PRIMARY] ) ON [ PRIMARY]; ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Employee_ManagerID] FOREIGN KEY ([ManagerID]) REFERENCES [HumanResources].[Employee] ([EmployeeID]) ; ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [FK_Employee_Employee_ManagerID]

19

Data Manipulation in SQL

Setelah sebuah tabel dibuat menggunakan perintah CREATE TABLE, record-record dapat

disisipkan ke dalam tabel maupun dihapus atau dimodifikasi.

Insertions

Cara termudah untuk menyisipkan sebuah record ke dalam tabel adalah dengan menggunakan perintah INSERT.

INSERT INTO <table> [(column i, ..., column j>)] VALUES (<value i, ..., value j>);

Perhatikan informasi tentang pengisian statement INSERT ini: � Statement ini hanya berfungsi untuk pengisian satu baris pada sebuah tabel. � Urutan nilai (value) harus mengikuti default dari urutan kolom yang dituliskan. � Nilai karakter dan tanggal, harus diapit oleh tanda kutip dua (‘’).

Contoh:

INSERT INTO HumanResources.EmployeeAddress (EmployeeID, AddressID, ModifiedDate) VALUES (50, 100, NOW())

Hint !

Memasukan data kedalam Table EmployeeAddress dengan menambah baris baru

untuk kolom employeeID = 50, AddressID=100, dan ModifiedDate=fungsi now() .

Untuk pengisian record dengan nilai NULL, maka terdapat dua buah cara, yaitu:

Metode implisit : Nama kolom tidak dituliskan dalam statement INSERT.

Contoh:

INSERT INTO HumanResources.EmployeeAddress (EmployeeID, AddressID) VALUES (50, 100);

Hint !

Memasukan data kedalam Table employeeAddress dengan menambah baris baru

untuk kolom employeeID = 50, AddressID=100, namun modified date tidak diisikan

20

Metode eksplisit: Nilai kolom yang bersangkutan dituliskan NULL.

Contoh:

INSERT INTO HumanResources.EmployeeAddress (EmployeeID, AddressID, ModifiedDate) VALUES (70, 100, NULL);

Hint !

Memasukan data kedalam Table employee dengan menambah baris baru untuk kolom

employeeID = 50, AddressID=100, dan modified date dinyatakan tidak bernilai Updates

Untuk mengubah nilai atribut dari suatu record dari suatu tabel, kita menggunakan perintah UPDATE :

UPDATE <table> SET <column i> = <expression i>, ..., <column j> = <expression j> [ WHERE <condition>];

“expression” dapat berisi konstan (sebuah nilai baru), operasi string atau aritmetik atau

sebuah SQL query. Perhatikan bahwa nilai baru yang di-assign pada setiap kolom harus mengikuti tipe data kolom tersebut.

Contoh:

� The Employee 100 is reassigned to the Manager 20 because he/she is married :

UPDATE HumanResources.Employee SET ManagerID = 20, MaritalStatus = ‘M’ WHERE EmployeeID = 100;

Hint !

Merubah data baris (record) yang sudah berisi pada table employee dengan mengubah

nilai managerID menjadi 20 dan MaritalStatus menjadi ”M”, untuk pegawai dengan

EmployeeID=100

Analog dengan statement INSERT, data tabel lain dapat diambil untuk pengisian nilai tabel

baru yang kita diinginkan. Pada kasus ini kita dapat menggunakan sebuah query pada ekspresinya.

21

Deletions

Seluruh atau record tertentu dapat dihapus dari sebuah tabel dengan menggunakan perintah DELETE.

DELETE FROM <table> [ WHERE <condition>];

Jika keyword WHERE dihilangkan maka seluruh record yang ada dalam tabel tersebut

akan dihapus. Perintah lain untuk menghapus seluruh record adalah TRUNCATE TABLE <table>. Perhatikan pada kasus ini, perintah penghapusan tidak dapat dibatalkan.

Contoh :

Hapus semua Employee dengan Title Accountant!

DELETE FROM HumanResources.Employee WHERE Title = ‘Accountant’;

Hint !

Menghapus seluruh baris(record) pada table employee yang memiliki title Accountant.

Atau menghapus seluruh record Accountant.

22

Joining Tables, Create View, Sub

Queries

Tujuan:

• Mampu menggunakan perintah-perintah pengambilan data dari tabel-tabel yang

berbeda dengan menggunakan fungsi Join Condition.

• Mampu memahami perbedaan Self Join, Inner, Join, dan OuterJoin

• Mampu memahami penggunaan View dan Sub Query

Joining Tables

Untuk menampilkan data yang diperoleh dari hubungan antara dua tabel atau lebih maka dapat

digunakan fungsi join condition. Dengan fungsi join condition, record pada suatu tabel dapat

dihubungkan dengan baris pada tabel lainnya dengan menggunakan adanya hubungan nilai pada

kolom-kolom yang berasal dari tabel-tabel tersebut.

Ada 3 tipe join yang dikenal yakni:

� Equijoins, dikenal juga sebagai Inner Join atau Simple Join. Tipe join ini digunakan bila

ingin menampilkan isi dari kolom tertentu pada suatu tabel dan menggabungkanya

dengan kolom tertentu pada tabel lainnya yang ditampilkan menurut kesamaan isi dari

kolom yang sama-sama dan dijadikan acuan sebagai penghubung antara kedua table

tersebut. Operator yang digunakan adalah INNER JOIN.

23

Contoh skenario:

Terdapat dua tabel, yaitu :

1. Tabel Mahasiswa

Atribut : Nim (PK), Nama, IDLab

2. Tabel Lab

Atribut : IDLab (PK), Nama

Kita ingin menampilkan hanya data Mahasiswa yang terdaftar dalam Lab beserta nama Lab

tersebut.

Diketahui bahwa nama mahasiswa berasal dari tabel Mahasiswa, sedangkan nama Lab

berasal dari tabel Lab.

Dengan adanya Foreign Key pada tabel Mahasiswa yaitu IDLab yang dapat dihubungkan

dengan Primary Key pada tabel Lab yaitu IDLab (nama primary key dan foreign key dapat

berbeda), maka SQL statement yang bisa kita pakai adalah sebagai berikut:

FROM : memilih dari table mana saja data akan dibaca atau diambil.

24

ON : penghubung antara kedua tabel. Data dalam kedua tabel akan ditampilkan dalam satu

baris jika memiliki penghubung yang benilai sama

Dari statement diatas terlihat bahwa kita ingin menampilkan nama mahasiswa dan

nama lab (SELECT Mahasiswa.Nama) dari table Mahasiswa. Sementara itu nama Lab

didapatkan dari tabel Lab sehingga untuk menampilkan nama Lab, ditambahkan sintaks

SELECT Lab.Nama.

Hal yang menyatukan keduanya adalah IDLab yang dimiliki tabel Mahasiswa maupun

table Lab. Karena itu untuk menampilkan Nama yang sesuai maka nilai IDLab pada table

Mahasiswa harus sama dengan nilai pada table Lab. Sehingga ditambahkan syarat ON

Lab.IDLab=Mahasiswa.IDLab

Tampilan:

Output yang dihasilkan hanya menampilkan data yang memiliki IDLab dengan nilai yang

sama. Sehingga data yang tidak memiliki IDLab atau tidak terdapat pasangannya pada

tabel Mahasiswa dan Lab, maka data tersebut tidak akan ditampilkan.

25

� Outer joins, join ini digunakan untuk melihat juga baris yang tidak memenuhi syarat join

yang berikan. Outer join dinyatakan dengan operator OUTER JOIN.

Terdapat dua jenis statement Outer join, yaitu Right Outer Join dan Left Outer Join.

Perbedaannya adalah kolom yang menjadi acuan untuk tampilan eksekusi. Jika

menggunakan Right Outer Join, maka yang dijadikan acuan adalah tabel yang

statemennya berada di sebelah kanan statement Right Outer Join tersebut. Sebaliknya,

jika menggunakan Left Outer Join makan kolom yang dijadikan acuan adalah tabel yang

statemennya berada di sebelah kiri statemen Left Outer Join.

Contoh:

Kita ingin mengetahui apakah seorang mahasiswa terdaftar pada suatu lab atau tidak, dan

jika ia maka di lab apakah ia terdaftar. Tampilkan hanya kolom Nama pada tabel Mahasiswa

dan Nama pada tabel Lab yang bersangkutan (kosong jika mahasiswa tersebut tidak

terdaftar di Lab manapun).

Dengan demikian maka SQL statement yang dapat digunakan adalah sebagai berikut :

FROM : memilih dari table mana saja data akan dibaca atau diambil.

Mahasiswa AS M : memberi alias tabel dengan nama “Mahasiswa” dengan nama “M”

sehingga mempersingkat penulisan statement yang digunakan.

SELECT : memilih Tabel.Kolom yang akan ditampilkan sebagai hasil dari suatu query.

M.Nama : Kolom “Nama” dalam tabel “M” (Mahasiswa).

ON : penghubung antara kedua tabel. Data dalam kedua tabel akan ditampilkan dalam satu

baris jika memiliki penghubung yang benilai sama.

LEFT OUTER JOIN : menampilkan data dengan mengacu pada tabel yang ditulis di sebelah

kiri statement JOIN.

Mahasiswa LEFT OUTER JOIN Lab : seluruh data mahasiswa akan ditampilkan, kemudian

data Lab akan menyesuaikan sesuai dengan ada atau tidaknya penghubung antara kedua

table tersebut.

RIGHT OUTER JOIN : menampilkan data dengan mengacu pada tabel yang ditulis di sebelah

kanan statement JOIN.

Mahasiswa RIGHT OUTER JOIN Lab : seluruh data Lab akan ditampilkan, kemudian data

Mahasiswa akan menyesuaikan sesuai dengan ada atau tidaknya penghubung antara

kedua table tersebut.

26

Untuk lebih jelasnya perhatikan hasil dari statement diatas :

Bandingkan hasilnya dengan equijoins (tipe pertama).

Pada Left Join, seluruh data dari tabel Mahasiswa ditampilkan baik yang memiliki Lab

ataupun tidak. Sedangkan pada Right Join berlaku sebaliknya, data seluruh Lab ditampilkan

baik yang memiliki anggota ataupun tidak. Jika ada dua data atau lebih, maka seluruh data

akan ditampilkan seperti pada contoh Right Join diatas, Lab LSIK dan LPSKE menampilkan

kedua anggotanya walaupun pada tabel Lab hanya terdapat satu data Lab tersebut (LSIK

maupun PSKE).

27

� Self Joins, terkadang kita ingin menghubungkan tabel dengan tabel itu sendiri. Untuk

memahami makna join tipe ini silakan perhatikan data pada tabel Pegawai. Di sana

terdapat kolom Supervisor_ID yang berisikan kode Pegawai yang menjadi atasan

masing-masing pegawai. Untuk lebih jelasnya lakukan query berikut.

Tabel Pegawai

Atribut : ID (PK), Nama, Supervisor_ID

Tampilan :

Output yang dihasilkan berupa nama pegawai dan nama supervisor, hasil tersebut berdasarkan kesamaan antara Supervisor_ID dengan ID pada tabel yang sama. Sehingga pegawai yang tidak memiliki data Supervisor_ID tidak akan ditampilkan.

28

Creating, Modifying, and Dropping View

View merupakan tabel virtual yang merepresentasikan data dari satu tabel dasar atau lebih dalam

berbagai alternatif cara.

Statement View mirip dengan statement awal pada pembuatan tabel yang diikuti dengan

statement Select :

CREATE VIEW [Name_View]

AS [Select_Statement];

Contoh :

Kita ingin menampilkan kolom seluruh pekerja beserta nama managernya. Baik yang

memiliki ManagerID ataupun tidak.

Maka statement Join yang digunakan adalah Outer Join.

CREATE VIEW vEmployeeManager

AS SELECT E.EmployeeID, E.Title , E.ManagerID

FROM HumanResources.Employee AS E

LEFT OUTER JOIN HumanResources.Employee AS M

ON E.ManagerID = M.EmployeeID;

Setelah itu kita dapat melakukan query dari tabel view yang telah dibuat.

Contoh :

Dari tabel view vEmployeeManager diatas aka dicari EmployeeId mana yang tidak

mempunyai ManagerID.

Statement query untuk contoh kasus diatas adalah :

SELECT EmployeeID, Title

FROM vEmployeeManager

WHERE ManagerID IS NULL

29

Tampilan:

Setelah view dibuat, view dapat dimodifikasi dengan menggunakan pernyataan berikut.

ALTER VIEW [Name_View]

AS [Select_Statement] ;

View juga dapat dihapus dengan menggunakan perintah berikut.

DROP VIEW [Name_View];

30

Updatable View Data dari tabel yang mendasari suatu view dapat dimodifikasi, sepanjang kondisi-kondisi berikut

ini benar.

� Segala bentuk modifikasi, termasuk pernyataan UPDATE, INSERT, dan DELETE, harus

me-refer kepada kolom-kolom dari satu tabel dasar.

� Kolom-kolom yang dimodifikasi di dalam view harus secara langsung me-refer ke data

yang terdapat di kolom tabel. Kolom tidak dapat diturunkan di dalam bentuk lain,

seperti melalui cara berikut.

� Fungsi agregat : AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR,

dan VARP.

� Suatu perhitungan. Kolom tidak dapat dihitung dari sebuah ekspresi yang

menggunakan kolom lain.

� Kolom-kolom yang dimodifikasi tidak dipengaruhi oleh klausa GROUP BY, HAVING, atau

DISTINCT.

31

Subqueries

Subquery merupakan query yang yang bersarang di dalam suatu pernyataan SELECT, INSERT,

UPDATE, atau DELETE, atau di dalam subquery lainnya. Subquery dapat digunakan di manapun

suatu ekspresi diperbolehkan.

Contoh skenario :

Pernyataan berikut akan menampilkan nama-nama produk yang harganya sama dengan produk

yang bernama ‘Chainring Bolts’. Penyataan selanjutnya menunjukkan pernyataan join yang setara

dengan pernyataan subquery sebelumnya.

/* Pernyataan SELECT yang dibangun dengan menggunakan

subquery. */

SELECT Name

FROM AdventureWorks.Production.Product

WHERE ListPrice =

(

SELECT ListPrice

FROM AdventureWorks.Production.Product

WHERE Name = 'Chainring Bolts'

)

/* Pernyaatan SELECT, yang dibangun dengan menggunakan join,

yang menghasilkan keluaran yang sama. */

SELECT Prd1.Name

FROM AdventureWorks.Production.Product AS Prd1

JOIN AdventureWorks.Production.Product AS Prd2

ON (Prd1.ListPrice = Prd2.ListPrice)

WHERE Prd2. Name = 'Chainring Bolts'

32

Tampilan:

modul 2 sql server 2005

Documents