SQL Server Oleh Ir.Hartono
Konsep Database Data
nilai yang memiliki suatu arti
Informasi
data yang berguna
Database
kumpulan data yang terorganisasi
Sistem informasi
sinergi unsur-unsur pengelolaan database untuk menyediakan informasi
Sistem Database
sinergi unsur-unsur penyimpanan dan pengelolaan data
Database Relasional
model database yang tabel-tabelnya memiliki hubungan dengan definisi tertentu
DBMS
DataBase Management System adalah perangkat lunak untuk mengelola sistem database
RDBMS
Relational DataBase Management System adalah DBMS untuk database realsional
2
Hirarki Data Nilai Dosen Mahasiswa
file
NIM
Nama
Tgl. Lahir
Nama field
database phrase
110011
Salma Hayek
1-Mar-1970
110012
Laetita Casta
21-Des-1974
110013
Arnold Sgarbuger
5-Jun-1960
word
field
5
karakter
Database
4
File
Table
3
Phrase (kalimat)
Record (Row)
2
Word (kata)
Item – Field (Column)
1
Character (huruf)
Character 3
Tipe--file File (1/2) Tipe • File Induk (master file) – File Induk Acuan (reference master file) • file induk yang recordnya relatif statis, jarang berubah nilainya. Misalnya file daftar gaji, file mata pelajaran.
– File Induk Dinamik (dynamic master file) • file induk yang nilai dari record-recordnya sering berubah atau sering dimutakhirkan (update) sebagai hasil dari suatu transaksi. Misalnya file induk data barang, yang setiap saat harus di up-date bila terjadi transaksi.
• File Transaksi (transaction file) – File ini bisa disebut file input; digunakan untuk merekam data hasil dari transaksi yang terjadi. Misalnya file penjualan yang berisi data hasil transaksi penjualan. 4
Tipe--file File (2/2) Tipe • File Laporan (report file) – File ini bisa disebut output file, yaitu file yang berisi informasi yang akan ditampilkan.
• File Sejarah (history file) – File ini bisa disebut file arsip (archival file), merupakan file yang berisi data masa lalu yang sudah tidak aktif lagi, tetapi masih disimpan sebagai arsip.
• File Pelindung (backup file) – File ini merupakan salinan dari file-file yang masih aktif di dalam database pada suatu saat tertentu. File ini digunakan sebagai pelindung atau cadangan bila filedatabase yang aktif mengalami kerusakan atau hilang. 5
Sistem Database Terkoneksi • Sistem database terkoneksi dibangun dengan adanya service dan arsitektur sistem yang digunakan • Jenis service – User service : presentasi, antar-muka, validasi, aturan – Business service : aturan bisnis – Data service : penyimpanan dan pengaksesan data, aturan
• Arsitektur sistem – Single tier (host) : semua service berada dalam satu komputer – 2-tier (client/server) : service dibagi pada komputer-komputer yang berbeda yang saling terhubung – 3-tier – N-tier
6
1-Tier (Client(Client-Server)
User Interface Presentation service Application service Business service Data service
7
2-Tier (Client(Client-Server)
Clients Tier I User Interface Presentation service Application service
Tier II Application service Business service Data service
Server
8
3-Tier
Tier I User Interface Presentation service
Tier II Application service Business service Data service
Tier III Data service
9
N-Tier (Multi(Multi-Tier)
Tier I User Interface Presentation service
Tier II Application service Business service
Tier IV Web service
Tier III Data service
10
Pengenalan • SQL Server adalah perangkat lunak sistem management database relasional (Relational DataBase Management System [RDBMS]) • SQL Server menggunakan perintah Transact-SQL (T-SQL) untuk melakukan proses pada database • T-SQL menggunakan ANSI SQL-92 sebagai fondasinya
11
Edisi MS SQL Server 2000 • • • • •
Desktop Personal Standard Developer Enterprise
12
Komponen Penyusun Database • Physical Database – File database secara fisik (.mdf, .ndf, .ldf)
• Logical Database – Database yang tersimpan di dalam physical database (table, view)
13
File--file Physical Database File • Primary Data File (.mdf) – Satu database hanya memiliki satu .mdf – Sebagai file utama penyimpan data user dan data sistem
• Secondary Data Files (.ndf) – Opsional : boleh ada, boleh tidak – Boleh lebih dari satu file – Sebagai file tambahan penyimpan data user
• Log Files (.ldf) – Satu database minimal memiliki satu .mdf – Sebagai penyimpan log aktivitas yang terjadi pada database
• Catatan ; – saat database logical dibuat, secara default akan terdiri dari satu file .mdf dan satu file .ldf – Ekstensi file .mdf, .ndf, .ldf dapat diganti
14
Database Default SQL Server • Master – Menyimpan data-data sistem – Tidak boleh rusak
• MSDB – Menyimpan data-data yang digunakan MS SQL Agent
• Model – Database model untuk membuat database baru
• TempDB – Menyimpan sementara hasil query yang kompleks
15
Objek--objek Database SQL Server Objek • • • • • • • •
Table Constraint Rule Default View Stored Procedure Trigger User Defined Data Types
16
Kelompok PerintahPerintah-Perintah SQL • Data Definition Language (DDL) – CREATE – ALTER – DROP
• Data Manipulation Language (DML) – – – –
SELECT INSERT UPDATE DELETE
• Data Control Language (DCL) – GRANT – DENY – REVOKE 17
Query Analyzer • Query Analyzer adalah program bantu dalam Ms SQL Server untuk menjalankan perintah-perintah SQL
18
Login Window
19
Tampilan Awal QA
20
Tampilan QA
21
Penulisan Perintah SQL pada QA • Huruf besar dan huruf kecil tidak berpengaruh • Nama objek seperti nama tabel, kolom, view dsb. disarankan jangan mengandung spasi. Gunakan garis bawah (under score) sebagai pengganti spasi • Perintah boleh ditulis satu baris. Disarankan dibagi menjadi beberapa baris untuk mempermudah pembacaan
22
Membuat Database • Perintah SQL untuk membuat database adalah CREATE • Sintaks : CREATE DATABASE nama_tabel ( Spesifikasi_kolom1, … , Spesifikasi_kolomn )
23
Contoh CREATE DATABASE binuscenter • Membuat database FTI tanpa spesifikasi, menggunakan default CREATE DATABASE FTI ON ( NAME = filedata1, FILENAME = ‘d:\filedata1.mdf’, SIZE = 10 mb, MAXSIZE = UNLIMITED, FILEGROWTH = 20% ) • Membuat database FTIr dengan satu file data berukuran 10MB dan pertumbuhan 20% tidak terbatas
24
Tentang SIZE dan FILEGROWTH • Nilai SIZE dan FILEGROWTH harus bilangan bulat • SIZE – Satuannya KB, MB (default), TB. – Nilai minimal 512 KB. – Jika tidak ditentukan, default 1 MB
• FILEGROWTH – – – –
Satuannya KB, MB (default), TB, %. Nilai minimal 64 KB. Nilai 0 digunakan untuk tidak mengadakan penambahan Jika tidak ditentukan, default 10%.
25
Informasi tentang Database • Perintah untuk mendapatkan informasi tentang database yang ada adalah : – sp_helpdb nama_database
• Informasi yang dapat diperoleh adalah : – – – – – – – – – – –
Nama database Ukuran database Pemilik Tanggal pembuatan Status File-file data logical & physical Filegroup Ukuran file Ukuran maksimum Pertumbuhan Pemakaian 26
Contoh CREATE DATABASE binuscenter ON ( NAME = filedata1, FILENAME = ‘d:\filedata1.mdf’, SIZE = 10 mb, MAXSIZE = UNLIMITED, FILEGROWTH = 20% ), ( NAME = filedata2, FILENAME = ‘d:\filedata2.ndf’, SIZE = 1, MAXSIZE = 250 mb, FILEGROWTH = 514 kb ) membuat sebuah database baru dengan nama FTI dengan dua buah file data, yaitu: FileData1 dan FileData2.
27
Contoh CREATE DATABASE FTI ON ( NAME = filedata1, FILENAME = ‘d:\filedata1.mdf’, SIZE = 2, MAXSIZE = 1 gb, FILEGROWTH = 20% ) LOG ON ( NAME = filelog1, FILENAME = ‘d:\filelog1.ldf’, SIZE = 640 kb ) membuat sebuah database baru dengan nama FTI dengan sebuah file data (FileData1) dan sebuah log file (FileLog1). NOTE: spesifikasi Size, Maxsize, dan Filegrowth tidak wajib diberikan 28
Contoh Modifikasi Spec ALTER DATABASE FTI MODIFY FILE ( NAME = filedata1, SIZE = 3, MAXSIZE = 500 mb )
mengubah spesifikasi salah satu file dari sebuah database.Yang dapat diubah hanya untuk size, maxsize dan filegrowth. NOTE: nama file fisik tidak boleh ditulis. Size dan Maxsize tidak boleh lebih kecil dari ukuran file saat perubahan
29
Contoh ALTER DATABASE FTI ADD FILE ( NAME = filedata2, FILENAME = ‘d:\filedata2.ndf’, SIZE = 1, MAXSIZE = 500 mb ) menambahkan sebuah file data ke dalam database FTI ALTER DATABASE FTI ADD LOG FILE ( NAME = filelog2, FILENAME = ‘d:\filelog2.ldf’, SIZE = 640 kb, MAXSIZE = UNLIMITED ) menambahkan sebuah file log ke dalam database FTI
30
Contoh ALTER DATABASE FTI REMOVE FILE filelog2
Perintah ini untuk menghapus file dari database FTI (berlaku untuk file data maupun log). NOTE : file yang dihapus menggunakan nama file logical
DROP DATABASE FTI
Perintah ini untuk menghapus database FTI. NOTE : database aktif harus MASTER (use master dahulu sebelum melakukan drop database)
31
Tabel
Tabel adalah objek untuk menyimpan data yang terdiri dari row (record) dan column (field) Sintaks untuk membuat tabel : CREATE TABLE nama_tabel ( nama_kolom1 tipe_data, … nama_kolomn tipe_data )
32
Tipe Data (1/2) BigInt Bilangan Bulat
Bilangan Pasti
Bilangan Biner Bilangan Desimal
Uang
Bilangan Approksimasi
Bilangan Pecahan
Int SmallInt
Bit
0 atau 1
Decimal
-10^38 +1 s/d 10^38 –1
Numeric
idem
Money
Unicode
-214,748.3648 s/d +214,748.3647
Real
-214,748.3648 s/d +214,748.3647
Float
-1.79E + 308 s/d 1.79E + 308. January 1, 1900 s/d June 6, 2079 dg akurasi 1 menit January 1, 1753 s/d December 31, 9999 dengan akurasi 3/100 detik (3.33 millidetik)
Char
maximum length of 8,000 characters
VarChar
maximum length of 8,000 characters maximum length of 2^31 - 1 (2,147,483,647) characters
NChar
maximum length of 4,000 characters
NVarChar
maximum length of 4,000 characters
NText
33
-2^63 (-922,337,203,685,477.5808) s/d 2^63 - 1 (+922,337,203,685,477.5807) dgn akurasi 1/1000
SmallMoney
Text
Karakter / String
2^15 (-32,768) s/d 2^15 - 1 (32,767). 0 s/d 255
DateTime
Non Unicode
-2^31 (-2,147,483,648) s/d 2^31 - 1 (2,147,483,647).
TinyInt
SmallDateTime
Waktu
-2^63 (-9.223.372.036.854.775.808) s/d 2^63-1 (9.223.372.036.854.775.807)
maximum length of 2^30 - 1 (1,073,741,823) characters.
Tipe Data (2/2) Biner
Binary
maximum length of 8,000 bytes
VarBinary
maximum length of 8,000 bytes
Image
2^31 - 1 (2,147,483,647) bytes
cursor sql_variant Tipe Lain
table timestamp uniqueidentifier
34
Perbedaan Char dan Varchar
Char memiliki panjang tetap Varchar memiliki panjang sesuai panjang datanya
Char(5)
b
o
b
b
o
b
b
o
b
disimpan VarChar(5)
b
o
b
Catatan : direkomendasikan menggunakan tipe data Char untuk kolom Primary Key dari pada Varchar
35
Contoh CREATE TABLE pelanggan ( kode_pelangan CHAR(5), nama_pelanggan VARCHAR(25), alamat VARCHAR(50), telepon VARCHAR(15), umur TINYINT, tgl_lahir SMALLDATETIME )
36
Informasi tentang Tabel
Perintah untuk mendapatkan informasi tentang database yang ada adalah :
sp_help nama_tabel
Informasi yang dapat diperoleh adalah : 37
pemilik tipe tabel tgl. dibuat struktur tabel kolom identity rowGUIDcol filegroup index constraint referensi FK
Modifikasi Definisi Tabel --menambah kolom ALTER TABLE pelanggan ADD hobi VARCHAR(250) --mengubah spec kolom ALTER TABLE pelanggan ALTER COLUMN hobi VARCHAR(100) --menghapus kolom ALTER TABLE pelanggan DROP COLUMN hobi --menghapus tabel DROP TABLE pelanggan --mengubah nama kolom SP_RENAME ‘pelanggan.alamat’, ‘domisili’, ‘COLUMN’ 38
Input Data
Sintaks perintah penginputan data ke tabel : INSERT [INTO] nama_tabel [( nama_kolom1[, … , nama_kolomn] )] VALUES ( nilai1[, … , nilain] )
Data bertipe karakter (char, varchar, text) dan datetime (smalldatetime, datetime) harus diapit dengan tanda petik satu. Format standard yang digunakan untuk datetime adalah ’31 May 2005’ atau ‘05/31/2005’.
39
Contoh INSERT INTO pelanggan VALUES( ‘P0001’, ‘Mitra Budiaji Chan’, ‘Kemanggisan Ilir’, ‘08128149909’, 20, ’28 Nov 2005’ ) Memasukkan sebuah record ke dalam table Pelanggan dimana semua column diisi. INSERT INTO pelanggan (kode_pelanggan, nama_pelanggan) VALUES(‘P0002’,‘Julyana Anggreini’) Memasukkan sebuah record ke dalam table Pelanggan dimana hanya column Kode_Pelanggan dan Nama_Pelanggan yang diisi. NOTE: selain column yang diisi oleh perintah INSERT, akan berisi data NULL. 40
Contoh INSERT INTO pelanggan VALUES(NULL, NULL, NULL, NULL, NULL, NULL) Memasukkan sebuah record ke dalam table Pelanggan dimana semua column diisi dengan data NULL INSERT INTO pelanggan VALUES(‘’, ‘’, ‘’, ‘’, ‘’, ‘’) Memasukkan sebuah record ke dalam table Pelanggan dimana semua column diisi dengan EMPTY STRING. Column bertipe karakter akan berisi karakter kosong. Column bertipe bilangan akan berisi nilai NOL. Column bertipe tanggal akan berisi tanggal awal, yaitu: 1 Jan 1753 (untuk datetime) dan 1 Jan 1900 (untuk smalldatetime)
41
Update Data UPDATE pelanggan SET kode_pelanggan = ‘P0003’ WHERE kode_pelanggan = ‘’ mengubah data pada column Kode_Pelanggan menjadi P0003 bila column Kode_Pelanggan berisi karakter kosong. Perintah tersebut untuk UPDATE pelanggan SET Nama_pelanggan = ‘Hengky’ WHERE kode_pelanggan IS NULL mengubah data pada column Nama_Pelanggan menjadi ‘Hengky’ bila column Kode_Pelanggan berisi data NULL. UPDATE pelanggan SET umur = 21 mengubah SEMUA data pada column Umur menjadi 21. NOTE: bila perintah UPDATE tidak memiliki WHERE clause, maka semua record akan diubah sesuai dengan nilai baru pada SET.
42
Delete Data DELETE FROM pelanggan WHERE kode_pelanggan IS NULL
menghapus record bila column Kode_Pelanggan berisi data NULL.
DELETE FROM pelanggan WHERE kode_pelanggan = ‘P0003’ AND nama_pelanggan = ‘’
menghapus record bila column Kode_Pelanggan berisi data ‘P0003’ dan column Nama_Pelanggan berisi karakter kosong.
DELETE FROM pelanggan
menghapus SEMUA record dari table Pelanggan. NOTE: bila perintah DELETE tidak memiliki WHERE clause, maka semua record akan di-HAPUS. 43
Select Data
SELECT * FROM customers Menampilkan semua data & semua column dari table Customers. SELECT contactname, contacttitle FROM customers Menampilkan column ContactName dan ContactTitle dari table Customers. Tanda koma setelah select menunjukkan pergantian column. SELECT contactname AS Name, contacttitle AS Title FROM customers Menampilkan column ContactName dengan judul “Name” dan column ContactTitle dengan judul “Title” dari table Customers. SELECT Name = contactname, Title = contacttitle FROM customers SELECT contactname AS “Contact Name”, contacttitle AS [Contact Title] FROM customers Menampilkan column ContactName dengan judul “Contact Name” dan column ContactTitle dengan judul “Contact Title” dari table Customers. Jadi, bila kita ingin menggabungkan dua kata, dapat menggunakan tanda “ “ atau [ ].
44
Filtering
SELECT TOP 10 * FROM customers Menampilkan hanya 10 record teratas dari table Customers. SELECT TOP 30 PERCENT * FROM customers Menampilkan hanya 30 percent record teratas dari table Customers. SELECT contacttitle FROM customers Menampilkan hanya column ContactTitle dari table Customers. Data yang ditampilkan akan berulang karena ada beberapa record dimana column ContactTitle tersebut berisikan data yang sama. Bila hanya ingin ditampilkan data yang berbeda saja, maka dapat digunakan keyword DISTINCT. SELECT DISTINCT contacttitle FROM customers menampilkan column ContactTitle yang berisikan data berbeda dari table Customers
45
Filtering : Where
SELECT * FROM customers WHERE region IS NOT NULL Menampilkan record dari table Customers dimana column Region tidak berisikan NULL. SELECT * FROM customers WHERE region <> ‘’ Menampilkan record dari table Customers dimana column Region tidak berisikan empty string. SELECT * FROM customers WHERE country = ‘Germany’ Menampilkan record dari table Customers yang memenuhi kondisi tersebut, yaitu: colum country berisikan ‘Germany’. SELECT * FROM products WHERE unitprice > 10 Menampilkan record dari table Products dimana column UnitPrice berisikan data lebih besar dari 10.
46
Logical Operators
SELECT * FROM products WHERE unitprice > 10 AND UNITSINSTOCK < 10 Menampilkan record dari table Products dimana column UnitPrice berisikan data lebih besar dari 10 dan column UnitsInPrice kecil dari 10. NOTE: Operator AND hanya bersifat BENAR bila kedua syarat/kondisi yang ada bernilai BENAR. SELECT * FROM products WHERE unitprice > 10 OR unitsinstock < 10 Menampilkan record dari table Products dimana column UnitPrice berisikan data lebih besar dari 10 atau column UnitsInPrice kecil dari 10. NOTE: Operator OR hanya bersifat BENAR bila salah satu atau kedua syarat/kondisi bersifat BENAR.
47
Operator BETWEEN
Operator BETWEEN dapat digunakan untuk membuat kondisi/syarat yang bersifat range atau suatu jangkauan.
SELECT * FROM products WHERE unitprice BETWEEN 10 AND 20 Menampilkan record dari table Products dimana column UnitPrice bernilai antara 10 sampai 20. Perintah sinonim : SELECT * FROM products WHERE unitprice >= 10 AND unitprice <= 20
SELECT * FROM products WHERE unitprice NOT BETWEEN 10 AND 20 Menampilkan record dari table Products dimana column UnitPrice tidak bernilai antara 10 sampai 20. Perintah sinonim : SELECT * FROM products WHERE unitprice < 10 OR unitprice > 20
48
Operator LIKE
Karakter wildcard yang dapat digunakan adalah:
% : sembarang karakter, banyaknya minimal 0 _ : sembarang karakter, banyaknya harus 1 [ ] : sembarang karakter di dalam tanda kurung tersebut [^ ] : sembarang karakter selain yang terdapat dalam tanda kurung tersebut
SELECT * FROM customers WHERE custname LIKE ‘MAR%S%’ Menampilkan record dari table Customers dimana column CustName berisi data yang sesuai dengan pola ‘Mar%s%’ SELECT * FROM customers WHERE custname LIKE ‘__A%’ Menampilkan record dari table Customers dimana column CustName berisi data yang sesuai dengan pola ‘__a%’, yaitu: karakter ketiga adalah huruf ‘a’. SELECT * FROM customers WHERE custname LIKE ‘[STUV]%’ Menampilkan record dari table Customers dimana column CustName berisi data yang sesuai dengan pola ‘[STUV]%’, yaitu: karakter pertama adalah S, T, U, atau V
49
Operator Perbandingan (1/3) Operator
Arti
Contoh
=
Sama dengan
SELECT fname, lname FROM employees WHERE lname = 'Smith'
<> atau !=
Tidak sama dengan
SELECT fname, lname FROM employees WHERE status <> 'Active'
>
Lebih dari
SELECT fname, lname FROM employees WHERE hire_date > '12/31/90'
<
kurang dari
SELECT fname, lname FROM employees WHERE job_lvl < 100
>= atau !<
Lebih atau sama dengan
SELECT au_lname FROM authors WHERE au_lname >= 'T'
<= atau !>
Kurang atau sama dengan
SELECT fname, lname FROM employees WHERE hire_date <= '01/01/95'
50
Operator Perbandingan (2/3) Operator
Arti
Contoh
BETWEEN expr1 AND expr2 jangkauan
SELECT fname, lname FROM employees WHERE hire_date BETWEEN '12/31/90' AND '12/31/91'
IS [NOT] NULL
Apakah berisi NULL
SELECT fname, lname FROM employees WHERE photo_on_file IS NULL
[NOT] LIKE
Apakah sString cocok dengan pola
SELECT fname, lname FROM employees WHERE lname LIKE ('MAC%')
51
Operator Perbandingan (3/3) Operator
Arti
Contoh
expr1 [NOT] IN (val1, val2, ...)
Apakah ada di dalam daftar
SELECT fname, lname FROM employees WHERE sales_region IN ('SW', 'SE') SELECT product_name FROM products WHERE supplier_id IN (SELECT supplier_id FROM supplier WHERE (country = 'Sweden'))
ANY (SOME)
Hasil subquery sesuai dengan kriteria
SELECT au_lname, au_fname FROM authors where city <> any (SELECT city FROM publishers)
ALL
Apakah semua hasil subquery sesuai dengan kriteria
SELECT title FROM titles where advance > all (SELECT advance FROM publishers,titles where titles.pub_id = publishers.pub_id AND pub_name = 'Alogdata Infosystems')
[NOT] EXISTS
Apakah subquery mendapatkan data
SELECT product_name FROM products WHERE EXISTS (SELECT * FROM orders, products WHERE orders.prod_id = products.prod_id)
atau expr1 [NOT] IN (subquery)
52
Fungsi String
SELECT * FROM customers WHERE LEFT(custname,2) = ‘MI’ Menampilkan record dari table Customers dimana dua karakter paling kiri dari column CustName adalah ‘Mi’ SELECT contacttitle, RIGHT(contacttitle,3) AS ‘KANAN’ FROM customers Menampilkan column ContactTitle dan tiga karakter paling kanan column ContactTtile dari table Customers.
SELECT * FROM customers WHERE SUBSTRING(custname,2,3) = ‘sam’
SELECT * FROM customers WHERE LEN(custname) = 10 Menampilkan record dari table Customers dimana column CustName berisikan 10 karakter
53
REPLICATE
ASCII
SUBSTRING
NCHAR
DIFFERENCE
SOUNDEX
LEN REVERSE UNICODE
CHAR PATINDEX SPACE CHARINDEX
LOWER REPLACE RIGHT STR UPPER QUOTENAME LTRIM STUFF 54 RTRIM
LEFT
Fungsi Waktu
SELECT GETDATE() Menampilkan tanggal yang tersimpan pada SQL Server dengan format datetime. SELECT * FROM employees WHERE DATEDIFF(YEAR, birthdate, GETDATE()) > 50) Menampilkan record dari table Employees dimana selisih column BirthDate dan sekarang lebih besar dari 50 tahun. SELECT LASTNAME, DAY(birthdate), MONTH(birthdate) FROM employees Menampilkan column LastName, tanggal dari column BirthDate dan bulan dari column BirthDate
55
Fungsi Matematika ABS
LOG10
ACOS
PI
ASIN
POWER
ATAN
RADIANS
ATN2
RAND
CEILING
ROUND
COS
SIGN
COT
SIN
DEGREES
SQUARE
EXP
SQRT
FLOOR
TAN
LOG 56
Contoh
select round(129.93547, 2) -- 129.94000 select round(129.4357, 2) -- 129.4400 select round(129.1,0) -- 129.0 select round(129.9,0) -- 130.0 select floor(129.93547) -- 129 select floor(129.4357) -- 129 select ceiling(129.93547) -- 130 select ceiling(129.4357) -- 130 57
FUngsi Konversi
CONVERT( )
Converts data from one data type into another. Useful to format data or to use the contents of a data column as an argument in a function that requires a different data type.
SELECT 'Hired: ' + CONVERT(char (11), hire_date) FROM employee Displays a date with a caption in front of it; the CONVERT( ) function creates a string out of the date so that it can be concatenated with a literal string.
SOUNDEX( )
Returns the Soundex code for the specified expression, which you can use to create "sounds like" searches.
SELECT au_lname, au_fname FROM authors WHERE SOUNDEX(au_fname) = 'M240' Searches for names that sound like "Michael".
STR( )
Converts numeric data into a character string so you can manipulate it with text operators.
SELECT str(job_id) + ' ' + str(job_lvl) FROM employee Displays the job_id and job_lvl columns (both numeric) in a single string.
58
Pengurutan
SELECT * FROM customers ORDER BY contactname
Menampilkan record table Customers diurutkan berdasarkan column ContactName
SELECT * FROM customers ORDER BY contactname DESC
Menampilkan record table Customers diurutkan berdasarkan column ContactName secara menurun
59
Fungsi Agregat
SELECT MAX(contactname), MIN(contactname) FROM customers Menampilkan nilai maksimum dan minimum column ContactName dari table Customers. NOTE: Fungsi Max() dan Min() berlaku untuk column bertipe apa saja. SELECT SUM(unitinstock), AVG(unitinstock) FROM products Menampilkan total dan rata-rata column UnitInStock dari table Products. NOTE: Fungsi Sum() dan Avg() hanya berlaku untuk column bertipe bilangan, seperti: integer, decimal, dan money. SELECT COUNT(*) FROM customers Menampilkan jumlah record dari table Customers SELECT COUNT(region) FROM customers Menampilkan jumlah data column REGION dari table Customers. NOTE: Data NULL tidak dihitung 60
GROUP BY
SELECT country FROM customers GROUP BY country Menampilkan column Country dengan pengelompokkan berdasarkan column Country SELECT country, COUNT(*) FROM customers GROUP BY country Menampilkan column Country dan jumlah record dengan pengelompokkan berdasarkan column Country SELECT country, MAX(contactname) FROM customers GROUP BY country ORDER BY MAX(contactname) Menampilkan column Country dan maksimum ContactName dengan pengelompokkan berdasarkan column Country yang diurutkan berdasarkan ContactName.
61
Fungsi IsNull()
IsNull adalah fungsi untuk mengecek apakah tidak ada data (NULL) atau ada, kemudian jika tidak ada data maka pada hasil query ditampilkan data penggantinya Contoh : SELECT title, type, price FROM titles SELECT title, type, ISNULL(price,0) FROM titles
62
Manfaat IsNull()
Soal : buatlah rata-rata harga (price) dari tabel titles pada database Pubs Jawaban : SELECT AVG(price) FROM titles Bandingkan dengan hasil query di bawah ini :
SELECT AVG(price), AVG(ISNULL(price,0)) FROM
titles
SELECT COUNT(price), COUNT(ISNULL(price,0)) FROM SELECT SUM(price), SUM(ISNULL(price,0)) FROM
63
titles
titles
Join
Join adalah operasi untuk menampilkan data dari dua atau lebih database yang memiliki relationship Jenis Join
Inner join Outer join
64
Left join Right join
Full join Cross join Self join
Join Tabel_A
Tabel_B
NIP
Nama
NIP
Gaji
001
Ali
001
1000000
002
Budi
002
2000000
003
Buce
003
3000000
004
Cici
004
4000000
005
Dedi
007
5000000
006
Fifi
008
6000000
65
Inner Join
Menampilkan data dari tabel-tabel yang data dari satu tabel dengan tabel lainnya memiliki relasi
SELECT tabel_a.nip, tabel_a.nama, tabel_b.gaji FROM tabel_a INNER JOIN tabel_b ON tabel_a.nip = tabel_b.nip
66
Left Join
Menampilkan semua data pada semua tabel sebelah kiri Data pada tabel kanan yang tidak memiliki relasi dengan data pada tabel kiri akan ditampilkan dengan NULL
SELECT tabel_a.nip, tabel_a.nama, tabel_b.gaji FROM tabel_a LEFT JOIN tabel_b ON tabel_a.nip = tabel_b.nip
67
Right Join
Menampilkan semua data pada semua tabel sebelah kanan Data pada tabel kiri yang tidak memiliki relasi dengan data pada tabel kanan akan ditampilkan dengan NULL
SELECT tabel_a.nip, tabel_a.nama, tabel_b.gaji FROM tabel_a RIGHT JOIN tabel_b ON tabel_a.nip = tabel_b.nip
68
Full Join
Menampilkan semua data pada semua tabel Data yang tidak memiliki relasi akan ditampilkan dengan NULL
SELECT tabel_a.nip, tabel_a.nama, tabel_b.gaji FROM tabel_a FULL JOIN tabel_b ON tabel_a.nip = tabel_b.nip
69
Cross Join
Menghasilkan cartesian product dan jarang dipergunakan Setiap data pada semua tabel akan dipetakan satu per satu
SELECT tabel_a.nip, tabel_a.nama, tabel_b.gaji FROM tabel_a CROSS JOIN tabel_b
70
Tabel Pegawai Self Join Bos NIP Nama 001 Joni 001 002 Budi 001 003 Tuti 001 004 Amir 002 SELECT a.nip, a.nama, b.nama AS ‘Nama Bos‘ FROM pegawai a INNER JOIN pegawai b ON a.bos = b.nip
Hasil query
71
NIP 001 002 003 004
Nama Joni Budi Tuti Amir
Nama Bos Joni Joni Joni Budi
Latihan Join #1
Gunakan database Northwind untuk menampilkan data pemesanan barang yang terdiri dari OrderID, ContactName, nama pegawai, dan OrderDate Petunjuk :
Tabel yang dipergunakan :
72
Orders Customers Employees
Latihan Join #2
Tampilkan data pemesanan yang dihasilkan pada Latihan Join #1 yang khusus memesan produk “Singaporean Hokkien Fried Mee’ Petunjuk :
Tabel yang dipergunakan :
73
Orders Customers Employees Order Details Products
Constraint
Constraint merupakan objek yang dipergunakan untuk menjaga integritas data Jenis constraint :
74
Primary key Foreign key Unique Default Check
Integritas Data
Entity integrity (table level) :
Domain integrity (column level)
Identity column Unique Default Check
Referential integrity (relational level) :
75
Primary key Foreign key
Primary Key
Primary Key adalah satu atau gabungan beberapa kolom yang datanya unik Dalam satu table maksimal ada satu primary key Kolom yang menjadi PK harus NOT NULL Contoh pembuatan tabel dengan kolom dijadikan primary key :
CREATE TABLE pelanggan( kdpelanggan CHAR(10) CONSTRAINT pk_kdpelanggan PRIMARY KEY, nmpelanggan VARCHAR(25), nomor_ktp VARCHAR(20), kota VARCHAR(15) )
76
Menambah dan Menghapus PK ALTER TABLE pelanggan ADD CONSTRAINT pk_kdpelanggan PRIMARY KEY (kdpelanggan)
Menambah constraint PK
ALTER TABLE pelanggan DROP CONSTRAINT pk_kdpelanggan
NOTE: sebuah Primary Key hanya dapat dihapus jika tidak ada Foreign Key yang mengacu pada Primary Key tersebut
sp_helpconstraint nama_tabel
Melihat informasi constraint pada suatu tabel
77
Foreign Key Foreign Key adalah satu atau gabungan beberapa kolom yang datanya ada di dalam dalam kolom primary key pada tabel lain Foreign key datanya tidak unik Foreign key menghubungkan tabel anak ke tabel induk Tipe data antara PK dan FK harus sama
CREATE TABLE transaksi( kdtransaksi CHAR(6), kdpelanggan CHAR(10) CONSTRAINT fk_trplg FOREIGN KEY REFERENCES pelanggan(kdpelanggan), tgltransaksi DATETIME )
78
Menambah dan Menghapus FK -- menambah foreign key ALTER TABLE transaksi ADD CONSTRAINT fk_trans_pelanggan FOREIGN KEY (kdpelanggan) REFERENCES pelanggan(kdpelanggan) -- menghapus foreign key ALTER TABLE transaksi DROP CONSTRAINT fk_trans_pelanggan
79
Unique
Unique digunakan agar tidak ada data yang sama (unik) pada kolom-kolom yang bukan Primary Key
CREATE TABLE pelanggan( kdpelanggan CHAR(10) CONSTRAINT pk_kdpelanggan PRIMARY KEY, nmpelanggan VARCHAR(25), nomor_ktp VARCHAR(20) UNIQUE, kota VARCHAR(15) )
80
Check
Check digunakan untuk memvalidasi data yang diinput pada tabel Satu check dapat digunakan beberapa kolom, dapat juga satu kolom memiliki lebih dari satu Check
CREATE TABLE pegawai ( kdpegawai CHAR(10), nmpegawai VARHAR(25), kota VARCHAR(15) CONSTRAINT ck_kota CHECK (LEN(kota)>= 5), tgllahir SMALLDATETIME )
81
Default
Default digunakan jika ingin memberikan suatu nilai tertentu jika pada suatu kolom datanya tidak diisi
CREATE TABLE pegawai ( kdpegawai CHAR(10), nmpegawai VARHAR(25), kota VARCHAR(15) CONSTRAINT df_kota DEFAULT ‘Jakarta’ )
82
Menambah dan Menghapus Default -- MENAMBAH CONSTRAINT ALTER TABLE pegawai ADD CONSTRAINT df_kota DEFAULT ‘JAKARTA’ FOR kota -- MENGHAPUS CONSTRAINT ALTER TABLE pegawai DROP CONSTRAINT df_kota
83
View
View adalah objek untuk menampilkan data yang dibaca dari satu atau beberapa tabel View bertindak sebagai tabel virtual read only Alasan penggunaan view :
Sintaks pembuatan view :
Kompleksitas query Masalah keamanan CREATE VIEW nama_view AS query_select
Sintaks pemakaian view :
84
SELECT * FROM nama_VIEW
Contoh View CREATE VIEW vw_customers AS SELECT customerid, companyname, contactname, address, city, country
FROM customers
membuat sebuah view dengan nama vw_Customers dimana hanya ditampikan column CustomerID, CompanyName, ContactName, Address, City dan Country dari table Customers.
CREATE VIEW vw_custorder AS SELECT * FROM customers INNER JOIN orders ON customers.customerid = orders.customerid
Membuat sebuah view bernama vw_custorder menampilkan semua kolom dari data pemesanan barang yang dilakukan oleh customer
85
Contoh CREATE VIEW VW_COMPPRODUCT AS SELECT COMPANYNAME, PRODUCTID, SUM(QUANTITY), SUM(QUANTITY*UNITPRICE) FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.CUSTOMERID = ORDERS.CUSTOMERID INNER JOIN [ORDER DETAILS] ON [ORDER DETAILS].ORDERID = ORDERS.ORDERID WHERE PRODUCTID = 1 GROUP BY COMPANYNAME, PRODUCTID
Perintah tersebut juga akan menghasilkan ERROR dimana semua column dalam view harus memiliki nama. Pada perintah SELECT tersebut, column ke-tiga dan ke-empat tidak memiliki nama column.
SELECT * FROM VW_CUSTOMERS
Perintah tersebut akan menampilkan isi dari view ‘vw_Customer’
86
Mengubah dan Menghapus View ALTER VIEW vw_customers AS SELECT companyname, contactname, city, country FROM customers
mengubah view vw_Customers dimana hanya column CompanyName, ContactName, City dan Country yang akan ditampilkan
DROP VIEW vw_customers
Perintah tersebut akan menghapus view vw_Customers
87
Batch
Sekelompok perintah T-SQL yang dikirimkan oleh suatu program aplikasi untuk dijalankan oleh SQL Server untuk melakukan tugas tertentu dalam suatu waktu tertentu Perintah-perintah dalam batch akan dikompilasi menjadi satu executable unit yang disebut execution plan Aturan pada batch :
CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, dan CREATE VIEW tidak dapat dikombinasikan dengan statement lain. Statement CREATE di awal batch dan semua statement setelahnya dianggap bagian dari definisi statement CREATE. Sebuah tabel tidak dapat diubah dan kolom-kolom baru yang direferensikan dalam satu batch. Statement EXECUTE tidak perlu ditulis jika berada pada awal batch.
Dalam suatu batch diakhiri dengan perintah GO Dalam suatu file dapat terdiri dari beberapa batch 88
T-SQL Scripts
Merupakan urutan-urutan perintah T-SQL yang disimpan dalam sebuah file Dapat terdiri dari satu batch atau lebih Perintah Go menandakan akhir suatu batch Jika perintah Go tidak ada, maka script dianggap memiliki satu batch saja
89
Contoh Batch Dalam Script USE pubs GO /* akhir batch */ CREATE VIEW auth_titles AS SELECT * FROM authors GO /* akhir batch */ SELECT * FROM auth_titles GO /* akhir batch */
90
