SQL Server R2 2008

SQL Server By Sapta MH 1. 2. 3. Installasi SQL Server 2008 R2 Double klik sql server 2008 r2.exe Kemudian akan muncul semacam bar yang menandakan bahwa sql server sedang mengekstrak beberapa file ke komputer kita Lalu akan muncul seperti gambar di bawah ini,pilih menu installation kemudian pilih new installation or add features to an existing installation 4. Setelah itu tunggu beberapa saat 5. Lalu akan muncul gambar seperti dibawah ini, pilih new installation or add shared features, kemudian klik Next> 6. Kemudian selanjutnya chek list I accept the license terms. kemudian klik Next> 7. Klik next sampai muncul gambar seperti di bawah ini, pilih defaul instance, kemudian klik Next-> 8. Kemudian trus klik Next sampai instalasinya selesai. 9. Maka Sql Server 2008 R2 siap di jalankan NB: kalo mau liat dimana programnya ada di start->all program->microsoft sql server 2008 R2->Sql Server Management Studio. di sana anda bisa mengutak ngatik query buat databasenya. Data Konsep Database 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 Database Relasional DBMS RDBMS sinergi unsur-unsur penyimpanan dan pengelolaan data model database yang tabel-tabelnya memiliki hubungan dengan definisi tertentu DataBase Management System adalah perangkat lunak untuk mengelola sistem database Relational DataBase Management System adalah DBMS untuk database realsional Hirarki Data Nilai Dosen Mahasiswa file NIM database phrase 5 4 Nama 110011 Salma Hayek 1-Mar-1970 110012 Laetita Casta 21-Des-1974 110013 Arnold Sgarbuger 5-Jun-1960 word field File Database Phrase (kalimat) 1 Character (huruf) Word (kata) Nama field karakter 3 2 Tgl. Lahir Table Record (Row) Item Field (Column) Character Tipe File-File (1/2)  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. Tipe File-File (2/2)  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. 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 11 1-Tier (Client-Server) User Interface Presentation service Application service Business service Data service 12 2-Tier (Client-Server) Clients Tier I User Interface Presentation service Application service Tier II Application service Business service Data service Server 13 3-Tier Tier I User Interface Presentation service Tier II Application service Business service Data service Tier III Data service 14 N-Tier (Multi-Tier) Tier I User Interface Presentation service Tier II Application service Business service Tier IV Web service Tier III Data service 15 Pengenalan • SQL Server adalah perangkat lunak sistem management database relasional (Relational DataBase Management System [RDBMS]) • SQL Server menggunakan perintah TransactSQL (T-SQL) untuk melakukan proses pada database • T-SQL menggunakan ANSI SQL-92 sebagai fondasinya 16 Komponen Penyusun Database • Physical Database – File database secara fisik (.mdf, .ndf, .ldf) • Logical Database – Database yang tersimpan di dalam physical database (table, view) 17 File-file Physical Database  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 .ldf  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 18 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 19 Objek-objek Database SQL Server • • • • • • • • Table Constraint Rule Default View Stored Procedure Trigger User Defined Data Types 20 Kelompok Perintah-Perintah SQL  Data Definition Language (DDL)  CREATE  ALTER  DROP  Data Manipulation Language (DML)     SELECT INSERT UPDATE DELETE  Data Control Language (DCL)  GRANT  DENY  REVOKE 21 SQL Server Management Studio Adalah program bantu dalam MS SQL Server 2008 R2 untuk menjalankan perintah-perintah SQL Login Window Tampilan Awal SQL Server Management Studio Tampilan SQL Server Management Studio Contoh CREATE DATABASE latihan • Membuat database latihan tanpa spesifikasi, menggunakan default CREATE DATABASE latihan ON ( NAME = filedata1, FILENAME = ‘ d:\filedata1.mdf’, SIZE = 10 mb, MAXSIZE = UNLIMITED, FILEGROWTH = 20% ) • Membuat database latihan dengan satu file data berukuran 10MB dan pertumbuhan 20% tidak terbatas 27 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%. 28 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 29 Contoh CREATE DATABASE latihan 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 latihan dengan dua buah file data, yaitu: FileData1 dan FileData2. 30 Contoh CREATE DATABASE latihan 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 latihan dengan sebuah file data (FileData1) dan sebuah log file (FileLog1). NOTE: spesifikasi Size, Maxsize, dan Filegrowth tidak wajib diberikan 31 Contoh Modifikasi Spec ALTER DATABASE latihan 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 32 Contoh ALTER DATABASE latihan ADD FILE ( NAME = filedata2, FILENAME = ‘d:\filedata2.ndf’, SIZE = 1, MAXSIZE = 500 mb )  menambahkan sebuah file data ke dalam database latihan ALTER DATABASE latihan ADD LOG FILE ( NAME = filelog2, FILENAME = ‘d:\filelog2.ldf’, SIZE = 640 kb, MAXSIZE = UNLIMITED )  menambahkan sebuah file log ke dalam database latihan, 33 Contoh ALTER DATABASE latihan REMOVE FILE filelog2 • Perintah ini untuk menghapus file dari database latihan (berlaku untuk file data maupun log). NOTE : file yang dihapus menggunakan nama file logical DROP DATABASE latihan • Perintah ini untuk menghapus database latihan. NOTE : database aktif harus MASTER (use master dahulu sebelum melakukan drop database) 34 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 ) 35 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 -2^63 (-922,337,203,685,477.5808) s/d 2^63 - 1 (+922,337,203,685,477.5807) dgn akurasi 1/1000 SmallMoney -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 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^31 - 1 (2,147,483,647) characters NChar maximum length of 4,000 characters NVarChar maximum length of 4,000 characters NText maximum length of 2^30 - 1 (1,073,741,823) characters. 36 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 37 Perbedaan Char dan Varchar  Char memiliki panjang tetap  Varchar memiliki panjang sesuai panjang datanya Char(5) b o b VarChar(5) b o b disimpan b o b b o b  Catatan : direkomendasikan menggunakan tipe data Char untuk kolom Primary Key dari pada Varchar 38 Contoh CREATE TABLE pelanggan ( kode_pelangan CHAR(5), nama_pelanggan VARCHAR(25), alamat VARCHAR(50), telepon VARCHAR(15), umur TINYINT, tgl_lahir SMALLDATETIME ) 39 Informasi tentang Tabel  Perintah untuk mendapatkan informasi tentang database yang ada adalah :  sp_help nama_tabel  Informasi yang dapat diperoleh adalah :           pemilik tipe tabel tgl. dibuat struktur tabel kolom identity rowGUIDcol filegroup index constraint referensi FK 40 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 --mengubah nama kolom SP_RENAME ‘ pelanggan.alamat’, ‘ domisili’,COLUMN’ ‘ --menghapus tabel DROP TABLE pelanggan 41 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 . 42 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. 43 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) 44 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. 45 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. 46 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 [ ]. 47 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 48 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. 49 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. 50 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 51 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 52 Operator Perbandingan (1/3) Operator Arti = Sama dengan <> atau != Tidak sama dengan > Lebih dari < kurang dari >= atau !< Lebih atau sama dengan <= atau !> Kurang atau sama dengan Contoh SELECT fname, lname FROM employees WHERE lname = 'Smith' SELECT fname, lname FROM employees WHERE status <> 'Active' SELECT fname, lname FROM employees WHERE hire_date > '12/31/90' SELECT fname, lname FROM employees WHERE job_lvl < 100 SELECT au_lname FROM authors WHERE au_lname >= 'T' SELECT fname, lname FROM employees WHERE hire_date <= '01/01/95' 53 Operator Perbandingan (2/3) Operator Arti BETWEEN expr1 AND expr2 jangkauan IS [NOT] NULL Apakah berisi NULL [NOT] LIKE Apakah sString cocok dengan pola Contoh SELECT fname, lname FROM employees WHERE hire_date BETWEEN '12/31/90' AND '12/31/91' SELECT fname, lname FROM employees WHERE photo_on_file IS NULL SELECT fname, lname FROM employees WHERE lname LIKE ('MAC%') 54 Operator Perbandingan (3/3) Operator Arti expr1 [NOT] IN (val1, val2, ...) Apakah ada di dalam daftar atau Contoh 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')) expr1 [NOT] IN (subquery) ANY (SOME) Hasil subquery sesuai dengan kriteria ALL Apakah semua hasil subquery sesuai dengan kriteria [NOT] EXISTS Apakah subquery mendapatkan data SELECT au_lname, au_fname FROM authors where city <> any (SELECT city FROM publishers) SELECT title FROM titles where advance > all (SELECT advance FROM publishers,titles where titles.pub_id = publishers.pub_id AND pub_name = 'Alogdata Infosystems') SELECT product_name FROM products WHERE EXISTS (SELECT * FROM orders, products WHERE orders.prod_id = products.prod_id) 55 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 56 ASCII NCHAR SOUNDEX CHAR PATINDEX SPACE CHARINDEX REPLACE STR QUOTENAME STUFF REPLICATE SUBSTRING DIFFERENCE LEN REVERSE UNICODE LOWER RIGHT UPPER LTRIM RTRIM LEFT 57 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 58 Fungsi Matematika ABS LOG10 ASIN POWER ACOS ATAN ATN2 PI RADIANS RAND CEILING ROUND COT SIN COS DEGREES EXP FLOOR LOG SIGN SQUARE SQRT TAN 59 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 60 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. SOUNDEX( ) Returns the Soundex code for the specified expression, which you can use to create "sounds like" searches. STR( ) Converts numeric data into a character string so you can manipulate it with text operators. 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. SELECT au_lname, au_fname FROM authors WHERE SOUNDEX(au_fname) = 'M240' Searches for names that sound like "Michael". SELECT str(job_id) + ' ' + str(job_lvl) FROM employee Displays the job_id and job_lvl columns (both numeric) in a single string. 61 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 62 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 63 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. 64 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 65 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 titles SELECT SUM(price), SUM(ISNULL(price,0)) FROM titles 66 Join  Join adalah operasi untuk menampilkan data dari dua atau lebih database yang memiliki relationship  Jenis Join  Inner join  Outer join  Left join  Right join  Full join  Cross join  Self join 67 Join Tabel_A NIP Nama Tabel_B 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 68 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 69 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 70 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 71 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 72 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 73 Tabel Pegawai NIP Nama Bos 001 Joni 001 002 Budi 001 003 Tuti 001 004 Amir 002 Self Join SELECT a.nip, a.nama, b.nama AS ‘ Nama Bos‘ FROM pegawai a INNER JOIN pegawai b ON a.bos = b.nip Hasil query NIP 001 002 003 004 Nama Joni Budi Tuti Amir Nama Bos Joni Joni Joni Budi 74 Latihan Join #1 • Gunakan database Northwind untuk menampilkan data pemesanan barang yang terdiri dari OrderID, ContactName, nama pegawai, dan OrderDate • Petunjuk : – Tabel yang dipergunakan : • Orders • Customers • Employees 75 Latihan Join #2 • Tampilkan data pemesanan yang dihasilkan pada Latihan Join #1 yang khusus memesan produk Singaporean Hokkien Fried Mee • Petunjuk : – Tabel yang dipergunakan : • • • • • Orders Customers Employees Order Details Products 76 Constraint • Constraint merupakan objek yang dipergunakan untuk menjaga integritas data • Jenis constraint : – Primary key – Foreign key – Unique – Default – Check 77 Integritas Data • Entity integrity (table level) : – Identity column – Unique • Domain integrity (column level) – Default – Check • Referential integrity (relational level) : – Primary key – Foreign key 78 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) ) 79 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 80 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 ) 81 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 82 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) ) 83 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 ) 84 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’ ) 85 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 86 View  View adalah objek untuk menampilkan data yang dibaca dari satu atau beberapa tabel  View bertindak sebagai tabel virtual read only  Alasan penggunaan view :  Kompleksitas query  Masalah keamanan  Sintaks pembuatan view :  CREATE VIEW nama_view AS query_select  Sintaks pemakaian view :  SELECT * FROM nama_VIEW 87 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 88 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 89 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 90 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 91 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 92 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 */ 93 Manfaat T-SQL Script • Digunakan sebagai dokumentasi permanen dari perintah-perintah untuk mengakses database • Transfer perintah-perintah dari satu komputer ke komputer lainnya bila perlu • Dapat mempercepat pengajaran untuk menemukan problem dalam kode, mengerti kode, dan mengubah kode 94 Variabel • Variabel adalah representasi tempat penyimpanan data dalam memori • Jenis Variabel : – User defined variable – Global variable • Sebelum dapat digunakan, variabel harus dideklarasikan lebih dahulu 95 Deklarasi & Pengisian Variabel • Sintaks deklarasi : DECLARE @<nama_variabel> <tipe data> • Sintaks pengisian variabel : SET @<nama_variabel> = <nilai> Atau SELECT @<nama_variabel> = <nilai> 96 Contoh -- Deklarasi variabel DECLARE @A INT DECLARE @B INT, @KATA VARCHAR(5) -- Pemberian nilai (assignment) SET @A = 10 SET @B = 5 SELECT @KATA = ‘ HELLO’ -- Pemakaian variabel PRINT @A PRINT @B PRINT @A + @B GO 97 Contoh Error declare @nama varchar(30) declare @usia int, @tinggi int set @nama = ‘ Britney’ set @usia = 20 print ‘ Nama ‘ +@nama go print @usia 98 Fungsi cast() • Fungsi cast() berfungsi untuk mengubah tipe data suatu variabel • Contoh : declare @a int, @b int, @c int set @a = 9 set @b = 4 set @c = @a + @b print a + b = + cast(@c as char) 99 If Then • Control flow  pengecekan suatu kondisi • Sintaks penulisan : if <kondisi> begin statement(s) end 100 Contoh Declare @a int, @b int Set @a = 3 Set @b = 5 If @a > @b Begin Print a > b end 101 Operator Perbandingan Operator = > < >= <= <> != !< !> Arti Sama dengan Lebih besar Lebih kecil Lebih besar atau sama dengan Lebih kecil atau sama dengan Tidak sama dengan Tidak sama dengan (not SQL-92 standard) Tidak lebih kecil dengan (not SQL-92 standard) Tidak lebih besar dengan (not SQL-92 standard) 102 If Then Else Declare @a int, @b int Set @a = 3 Set @b = 5 If @a > @b Begin Print a > b End Else Print a < b 103 Contoh Declare @a int, @b int Set @a = 3 Set @b = 5 If @a > @b Begin Print a > b End Else if @a < @b Begin Print a < b End Else Print a = b 104 Case When Then DECLARE @NAMA CHAR(10) DECLARE @KET CHAR(30) SET @NAMA = BRITNEY SET @KET = CASE @NAMA WHEN TUKUL THEN KEMBALI KE LAPTOP WHEN BRITNEY THEN OOPS I DID IT AGAIN WHEN BULLDOG THEN GUK! GUK! END PRINT @KET 105 Case When Then Else use northwind select Nama = contactname, Posisi= case when ContactTitle like '%owner%' then 'Boss besar' when ContactTitle like '%manager%' then 'Boss kecil' else 'Kroco' end, Jabatan=contacttitle from Customers 106 While Declare @count int Set @count = 0 While (@count < 10) Begin Print @count Set @count = @count + 1 End 107 Transaction (1/2) • Transaction adalah urut-urutan operasi yang dijalankan sebagai suatu unit kerja tunggal • Transaction selalu diawali dengan perintah : – BEGIN TRANSACTION – BEGIN TRANS • Dan dapat diakhiri dengan : – COMMIT TRANSACTION atau – ROLLBACK TRANSACTION 108 Transaction (2/2) • Perubahan data dengan menggunakan transaction, tidak akan disimpan secara permanen jika belum mengeksekusi perintah COMMIT TRANSACTION • Perubahan data dengan menggunakan transaction masih dapat dibatalkan dengan menggunakan ROLLBACK TRANSACTION dengan syarat; belum melakukan COMMIT TRANSACTION 109 Contoh SELECT * FROM [order details] WHERE productid=14 BEGIN TRAN DELETE FROM [order details] WHERE productid=14 SELECT * FROM [order details] WHERE productid=14 ROLLBACK SELECT * FROM [order details] WHERE productid=14 110 Cursor • Cursor adalah hasil suatu query yang : – Row-nya dapat dinavigasi – Me-retrieve data mulai dari posisi row saat ini – Datanya dapat dimodifikasi – Level visibility untuk user dapat diatur – Dikombinasikan dengan script, sp, dan trigger 111 Metode Permintaan Cursor • Melalui Transac-SQL • Melalui fungsi-fungsi DB API (Application Programming Interface) 112 Langkah Umum Proses Cursor • • • • • Deklarasikan cursor Jalankan query untuk membentuk cursor Retrieve row dari cursor Gunakan row Tutup cursor 113 Proses Cursor dengan T-SQL • Deklarasikan variabel untuk menampung cursor • Asosiasikan variabel dengan perintah SQL • Buka cursor • Gunakan cursor • Tutup cursor 114 Perintah untuk Memantau Cursor • • • • sp_cursor_list sp_describe_cursor sp_describe_cursor_columns sp_describe_cursor_tables 115 Perintah FETCH • FETCH adalah perintah untuk menggerakkan pointer pada cursor • Jenis FETCH : – FETCH NEXT – FETCH PRIOR – FETCH FIRST – FETCH LAST – FETCH ABSOLUTE – FETCH RELATIVE 116 @@fetch_status • Mengetahui status perintah fetch terakhir dijalankan • Status : – 0  fetch sukses – -1  fetch gagal – -2  record hilang (didelete user lain) 117 @@cursor_rows • Mengetahui banyaknya row dalam suatu cursor 118 SDK untuk Cursor use latihan create table msbarang ( kdbarang varchar(5), nmbarang varchar(50), satuan varchar(12), hgsatuan int, jumstock int ) insert into msbarang values ('ar002','disket fuji film','box',44000,20) insert into msbarang values ('ar001','disket evrbatim','box',40000,10) insert into msbarang values ('cr002','keyboard 104 keys','lusin',200000,20) insert into msbarang values ('br001','monitor svga samsung 14"','buah',100000,12) insert into msbarang values ('br002','scanner umax','buah',200000,4) insert into msbarang values ('cr001','disket fuji film','box',44000,20) 119 Cursor Forward Only Declare @kode varchar(5) Declare @nama varchar(50) Declare cursor_gw cursor for Select kdbarang, nmbarang from msbarang Where satuan = Box Open cursor_gw Fetch next from cursor_gw into @kode, @nama While(@@fetch_status=0) Begin print @kode + + @nama fetch next from cursor_gw into @kode, @nama End Close cursor_gw Deallocate cursor_gw 120 Cursor Scroll declare @kode varchar(5) declare @nama varchar(50) -- declare @count int declare cursor1 cursor scroll for select kdbarang, nmbarang from msbarang where satuan='box' open cursor1 fetch first from cursor1 into @kode, @nama -- set @count = 0 while (@@fetch_status=0) -- and (@count<5) begin print @kode + ' ' + @nama fetch next from cursor1 into @kode, @nama -- set @count = @count + 1 end close cursor1 deallocate cursor1 121                      USE AdventureWorks GO -- Execute the SELECT statement alone to show the -- full result set that is used by the cursor. SELECT LastName, FirstName FROM Person.Contact ORDER BY LastName, FirstName -- Declare the cursor. DECLARE contact_cursor SCROLL CURSOR FOR SELECT LastName, FirstName FROM Person.Contact ORDER BY LastName, FirstName OPEN contact_cursor -- Fetch the last row in the cursor. FETCH LAST FROM contact_cursor -- Fetch the row immediately prior to the current row in the cursor. FETCH PRIOR FROM contact_cursor -- Fetch the second row in the cursor. FETCH ABSOLUTE 2 FROM contact_cursor -- Fetch the row that is three rows after the current row. FETCH RELATIVE 3 FROM contact_cursor -- Fetch the row that is two rows prior to the current row. FETCH RELATIVE -2 FROM contact_cursor CLOSE contact_cursor DEALLOCATE contact_cursor GO 122 Stored Procedure • Procedure adalah sekumpulan perintah T-SQL (Transact-SQL) yang dapat mengambil dan mengembalikan parameter. • Sebuah procedure hanya dapat dibuat pada database yang aktif. • Sebuah procedure dapat memiliki parameter atau tidak. Bila tidak ada parameter, maka akan dianggap NULL. 123 Sintaks CREATE PROC[EDURE] <procedure_name> [ { <@parameter> <data_type> } [ = <default> ] ] [,..n] AS <sql_statement> [ ...n ] 124 Cara Mengekskusi EXEC PR_CUSTOMER ATAU PR_CUSTOMER 125 Contoh #1 -- membuat sp CREATE PROCEDURE pr_customers AS SELECT companyname, contactname, city, country FROM customers -- menjalankan sp EXEC pr_customers -- atau pr_customers 126 Contoh #2 CREATE PROC pr_inscust AS INSERT customers (customerid, companyname, contactname, contacttitle) VALUES (‘zchan’, ‘zhao chan corp.', ‘zhao chan', 'owner') 127 Melihat Kode SP • sp_helptext nama_stored_procedure 128 Procedure Berparameter CREATE PROCEDURE pr_updcust (@ID CHAR(5), @CITY VARCHAR(15), @CTRY VARCHAR(15)) AS UPDATE customers SET CITY = @CITY, COUNTRY = @CTRY WHERE customerid = @ID 129 Eksekusi Procedure Berparameter • exec pr_UpdCust • exec pr_UpdCust 'MCHAN', 'Jakarta', 'Indonesia' 130 Mengubah Procedure ALTER Procedure pr_UpdCust (@id char(5)) AS Delete Customers Where CustomerID = @id 131 Menghapus Procedure DROP PROCEDURE <procedure_name> DROP PROCEDURE pr_UpdCust 132 Function • Function adalah sekumpulan perintah T-SQL yang di-optimasi dan di-compile dan dapat dipanggil untuk bekerja sebagai unit tunggal. • Function tidak dapat digunakan untuk melakukan perubahan terhadap keadaan global database. • Function dapat digunakan di dalam suatu query. Function juga dapat dijalankan melalui perintah EXECUTE, seperti halnya stored procedure. • Ada dua tipe Function, yaitu: – Function yang mengembalikan nilai skalar dan – Function yang mengembalikan table. 133 Function Returning Scalar CREATE FUNCTION <function_name> ( [ { <@parameter_name> [AS] <data_type> [= default] } [ ,...n ] ] ) RETURNS <scalar_return_data_type> [ AS ] BEGIN <function_body> RETURN <scalar_expression> END 134 Catatan • <function_body> adalah sekumpulan perintah T-SQL yang digunakan untuk mengevaluasi nilai skalar. • <scalar_expression> adalah nilai skalar yang dikembalikan oleh function. 135 Contoh Function Returning Scalar CREATE FUNCTION Fc_Selisih (@tglawal DATETIME, @tglakhir DATETIME) RETURNS int AS BEGIN DECLARE @temp INT SET @temp = DATEDIFF(DAY, @tglawal, @tglakhir) RETURN @temp END --Penggunaan Select LastName, dbo.Fc_Selisih(BirthDate, getdate()) From Employees 136 Catatan • Untuk Function yang mengembalikan table, yang dikembalikan adalah sebuah perintah SELECT. 137 Function Returning Table CREATE FUNCTION <function_name> ( [ { @parameter_name [AS] <data_type> [ = default ] } [ ,...n ] ] ) RETURNS TABLE [ AS ] RETURN ( <select-stmt> ) 138 Contoh Function Returning Table -- Pembuatan function Create Function Fc_Customers () Returns TABLE AS Return (Select CompanyName, ContactName, ContactTitle From Customers) -- Pemakaian function Select * From Fc_Customers () 139 Mengubah Function /*Mengubah function, drop function dahulu lalu menulis ulang semua isi function*/ Create Function Fc_Selisih (@tglawal datetime, @tglakhir datetime) Returns int AS Begin Declare @temp int Set @temp = datediff(year, @tglawal, @tglakhir) Return @temp End 140 Menghapus Function • Sintaks : – DROP FUNCTION <function_name> • Contoh : – drop function fc_Selisih 141 Trigger • Trigger adalah suatu jenis stored procedure yang dijalankan secara otomatis ketika pengguna melakukan perintah untuk merubah data pada table tertentu. 142 Jenis Trigger • • • • Trigger INSERT Trigger UPDATE Trigger DELETE atau gabungan dari trigger-trigger tersebut. 143 Catatan Tentang Trigger • SQL Server mengijinkan untuk membuat beberapa trigger bagi setiap perintah INSERT, UPDATE dan DELETE. • Jika pada table yang mengandung trigger terdapat constraint, maka constraint dicek setelah INSTEAD OF trigger berjalan dan sebelum AFTER trigger berjalan. Jika constraint dilanggar, maka INSTEAD OF trigger dibatalkan dan AFTER trigger tidak dijalankan. 144 Sintaks Trigger CREATE TRIGGER <trigger_name> ON <table> { FOR | AFTER } { INSERT | UPDATE | DELETE } AS <sql_statement> [ ...n ] 145 Contoh Create Trigger tg_ins_Cust ON Customers For insert AS Select * From Customers Select * From inserted Select * From deleted Insert Customers (CustomerID, CompanyName, ContactName) values ('MCHAN', 'Mitra Chan Corp.', 'Mitra Chan') 146 • • • • • • • Perintah Tidak Boleh Untuk Trigger Create database Create procedure Create default Create index Alter table Alter view Drop table • • • • • • • Create table Create rule Create view Alter database Alter procedure Drop database Drop procedure 147 Contoh -- trigger untuk membatasi penghapusan row -- lebih dari satu Create Trigger tg_del_Cust ON Customers For delete AS IF ((Select count(*) From deleted) > 1) Begin Rollback Tran Print 'Tidak boleh menghapus lebih dari 1 record' End 148 Contoh Trigger Mengupdate Tabel Lain 149 Contoh Memodifikasi Trigger ALTER Trigger tg_ins_Cust ON Customers For insert AS Declare @name varchar(30) Set @name = (Select ContactName From inserted) IF ((@name is null) or (@name = '')) Begin Rollback Tran Print 'Column ContactName Harus Terisi' End 150 Menghapus Trigger • Sintaks : DROP TRIGGER <trigger_name> • Contoh : Drop Trigger tg_ins_Cust 151 INSTEAD OF Trigger • INSTEAD OF trigger memungkinkan untuk memperhatikan data yang akan dimasukkan ke dalam table atau view dan memutuskan apa yang akan dilakukan sebelum data dimasukkan secara fisik. • INSTEAD OF trigger lebih banyak digunakan untuk view dimana akan dilakukan manipulasi data sebelum data tersebut dimasukkan ke table. 152 Sintaks CREATE TRIGGER <trigger_name> ON { <table> | <view> } INSTEAD OF { INSERT | UPDATE | DELETE } AS <sql_statement> [ ...n ] 153 Contoh Penggunaan (1/4) /* Membuat tabel country dan city */ create table country ( countryID int identity, countryName varchar(32) ) go create table city ( cityID int identity , CityName varchar(32), countryID int ) go 154 Contoh Penggunaan (2/4) -- Membuat view create view v_geography as select countryName, cityName from country inner join city on country.countryID = city.countryID 155 Contoh Penggunaan (3/4) create trigger tr_v_geography_io on v_geography instead of insert as BEGIN insert country (countryName) select distinct inserted.countryName from inserted left join country on inserted.countryName = country.countryName where country.countryName IS NULL /*** Exclude countries already in the table ***/ insert city (cityName, countryID) select distinct inserted.cityName, country.countryID from inserted inner join country on inserted.countryName = country.countryName left join city on inserted.cityName = city.cityName where city.cityName IS NULL /*** Exclude cities already in the table ***/ END 156 Contoh Penggunaan (4/4) select * from v_geography select * from country select * from city insert v_geography select select select select select select 'England', 'London' UNION 'England', 'Manchester' UNION 'Japan', 'Tokyo' UNION 'Japan', 'Osaka' UNION 'USA', 'Washington DC' UNION 'USA', 'New York' 157 Contoh Subquery 158