MICROSOFT SQL SERVER

MATERI MATA KULIAH

MICROSOFT SQL SERVER OLEH : AHMAD PUJIANTO, S.KOM

AKADEMI MANAJEMEN INFORMATIKA DAN KOMPUTER

AMIK TARUNA LECES - PROBOLINGGO *************************************************************

REFERENSI 1. Microsoft SQL Server 2000 Programming by Example By : Fernando G. Guerrero, Carlos Eduardo Rojas Publisher : Que Pub Date : April 16, 2001 ISBN : 0-7897-2449-9 2. Inside Microsoft SQL By Publisher Pub Date ISBN

Server 2000 : Kalen Delaney : Microsoft Press : 2001 :-

3. Transact-SQL Desk Refence By : Deac Lancaster Publisher : Prentice Hall PTR Pub Date : November 10, 2003 ISBN : 0-13-029339-3 4. Microsoft SQL Server 2000 Weekend Crash Course By : Alex Kriegel Publisher : Hungry Minds, Inc. Pub Date : 2001 ISBN : 0-7645-4840-9 5. Instant SQL Server 2000 Applications By : Greg Buczek Publisher : McGraw-Hill Pub date : 2001 ISBN : 0-07-213320-1 6. SQL Server 2000 Books Online

1

PERTEMUAN 1 INTRODUCTION TO MICROSOFT SQL SERVER

RDBMS (RELATİONAL DATABASE MANAGEMENT SYSTEMS) DAN SQL SERVER Dalam sejarah kehidupan manusia, ilmu pengetahuan (informasi) merupakan kekuatan utama yang mendorong perubahan peradaban. Keberhasilan atau kegagalan suatu negara, perusahaan atau bahkan individu sangat tergantung dari seberapa banyak dia menguasai informasi dan memanfaatkannya. Informasi berdasarkan atas satu atau beberapa kenyataan. Dalam beberapa kasus, kenyataan ini dapat berasal dari sesuatu yang abstrak (kualitatif), sehingga sulit direpresantasikan dalam angka atau simbol tertentu. Padahal kelangsungan hidup suatu badan usaha atau organisasi tergantung dari data yang spesifik (kuantitatif). Sistem Informasi Manajemen didasarkan pada kemampuan untuk menggunakan data (kualitatif maupun kuantitatif) untuk memberikan solusi tentang bagaimana menyikapi kondisi-kondisi tertentu berkenaan dengan operasional suatu badan usaha atau organisasi. Data menjadi bernilai jika akurat dan bersifat menyeluruh untuk menjawab kebutuhan informasi suatu badan usaha atau organisasi. Sehingga aspek penting yang harus diperhatikan adalah bagaimana data tersebut disimpan serta bagaimana mekanisme menggunakannya pada saat diperlukan. Sistem Manajemen Basisdata (DBMS-Database Management Systems) menyediakan sistem penyimpanan data yang reliable dan kemudahan menggunakan data (flexible) apabila diperlukan. MODEL BASISDATA Agar tidak mudah rusak, data disimpan dalam media simpan fisik (physical storage devices). Data tersebut disimpan dengan format tertentu sesuai dengan Sistem Manajemen Basisdata yang dipakai. Ada beberapa model basisdata, antara lain: • • • • • • •

Flat files Hierarchical Networked Relational Object Object-relational Document

Flat files, Hierarchical dan Networked biasanya digunakan pada komputer mainframe, sedangkan model basisdata lainnya digunakan pada personal computer (PC). SQL Server termasuk kategori model basisdata Relational. (Pembahasan model basisdata tidak diperdalam karena berada diluar materi SQL Server). MODEL RELASIONAL Pada model relasional, data disusun dalam satu atau beberapa tabel. Setiap tabel merupakan gabungan beberapa kolom yang memetakan atribut suatu entitas. Data dari berbagai tabel tersebut direlasikan dengan dengan suatu hubungan logis yang dinamis. Contoh (perhatikan gambar berikut)

2

Model relasional ini didasarkan atas teori relasional yang ada di Aljabar maupun Kalkulus. Teori ini pertama kali diperkenalkan oleh Dr. E. F. Codd, seorang matematikawan dari perusahaan IBM. Dia menulis buku “A Relational Model of Data for Large Shared Data Banks” (Communications of the ACM, vol. 3, No. 6, June 1970). Buku ini menetapkan aturan-aturan tentang basisdata relasional. Semua RDBMS menggunakan bahasa tertentu yang disebut SQL (Structured Query Language) untuk mendapatkan informasi dari suatu basisdata. SQL merupakan bagian dari standard ANSI sejak versi ANSI SQL 86. SQL yang digunakan untuk SQL Server dinamakan Transact SQL (T-SQL) yang mengacu pada standar ANSI SQL 99. Catatan : ANSI (American National Standards Institute), merupakan sebuah organisasi yang mengeluarkan standar perdagangan dan komunikasi Amerika Serikat. Setelah menjadi anggota ISO (International Standardization Organization) dan IEC (International Electrotechnical Commission), stantard ANSI dipakai sebagai standard internasional. SEJARAH SINGKAT SQL SERVER (Perhatikan gambar berikut)

3

ARSITEKTUR SQL SERVER Komponen Utama Arsitektur SQL Server (Perhatikan gambar berikut)

SQL Server Engine

Komponen utama dari arsitektur SQL Server disebut Net Library, merupakan suatu layer yang memungkinkan SQL Server dapat membaca dari dan menulis ke beberapa protokol jaringan (misalnya : soket TCP/IP). Net Library pada dasarnya merupakan suatu driver yang digunakan khusus untuk berkomunikasi melalui jaringan dengan menggunakan mekanisme interprocess communication (IPC). Semua kode SQL Server (termasuk Net Library) berkomunikasi ke Microsoft Win32 subsystem (Win32 API) melalui Microsoft Open Data Services (ODS). ODS berfungsi mengatur lalu lintas jaringan yang digunakan oleh Net Library. Net Library

4

DESAIN APLIKASI CLIENT/SERVER Dalam mengembangkan aplikasi Client/Server, ada beberapa pilihan akses data menggunakan Database Library yang digunakan untuk dapat terhubung dengan SQL Server.

5

3

4

8

1

7 2 6

1. Akses langsung melalui HTTP dengan menggunakan halaman web (HTML maupun ASP). Pada kasus ini, gunakan SQL ISAPI extension melalui virtual directory yang ada pada Internet Information Server (IIS). SQL server mendukung penggunaan Xpath sebagai suatu quey melalui HTP dan XML input/Output. 2. Akses menggunakan DB-Library (Native SQL Server). Ini tidak direkomendasikan, karena beberapa fungsi SQL Server tidak kompatibel dengan DB-Library. Untuk akses langsung ke ODBC API – menggunakan berbagai bahasa pemrograman yang dikehendaki. Direkomendasikan menggunakan bahasa C atau C++. 3. Akses tidak langsung ke ODBC Library melalui Remote Data Objects (RDO). RDO hanya kompatibel untuk aplikasi sebelum ditemukannya ActveX Data Objects (ADO). Untuk aplikasi saat ini gunakan ADO daripada RDO. 4. Akses Langsung ke OLE DB Provider. Akses ini sangat direkomendasikan karena segala fungsionalitas SQL Server dapat digunakan dengan baik. 5. Akses tidak langsung ke OLE DB Provider menggunakan ActiveX Data Objects (ADO) – atau ADO.Net – pengembangan terbaru dari ADO. Akses ini lebih mudah dilakukan daripada akses langsung ke OLE DB Provider serta sesuai dengan berbagai bahasa pemrograman termasuk Visual Basic dan ASP.Net. 6. Akses tidak langsung ke ODBC driver melalui OLE DB Provider dengan atau tanpa ADO Library tidak direkomendasikan karena membutuhkan langkah-langkah ekstra untuk itu. Meskipun demikian jika telah terhubung, maka segala fungsionalitas ODBC dapat dijalankan. Sebagai jalur alternatif adalah akses ke metadata basisdata melalui ADOX dan OLE DB menggunakan perintah-perintah Data Definition Language (DDL). Namun ini tidak direkomendasikan karena membutuhkan keahlian khusus. 7. Akses sebagai administrator melalui SQL-DMO (Distributed Management Objects). DMO adalah suatu Model Object Library yang digunakan oleh SQL Server Enterprise Manager untuk terhubung ke SQL Server. Para pengembang dapat menggunakan fungsi-fungsi yang ada pada SQL-DMO untuk membuat aplikasi kecil untuk administrator, yang berfungsi selayaknya SQL Server Enterprise Manager. 8. Akses sebagai administrator melalui Windows Management Instrumentation (WMI). WMI merupakan tool yang ada pada windows 2000, digunakan secara khusus untuk mengontrol fungsi-fungsi yang ada pada SQL Server Tip : Untuk aplikasi baru sebaiknya gunakan ADO untuk akses ke SQL Server, karena mendukung teknologi ADO.Net 5

PERTEMUAN 2 ELEMEN-ELEMEN TRANSACT SQL (T-SQL) Transact SQL (T-SQL) merupakan implementasi SQL Server terhadap standard ANSI SQL 92, yang merupakan pengembangan dari struktur SQL secara umum. Misalnya; T-SQL menambahkan penulisan control flow seperti if ... else, while, break dan continue. T-SQL merupakan bahasa basisdata yang bersifat set-operations, artinya proses grouping data dilakukan serentak sehingga lebih efisien dibandingkan dengan operasi baris per baris (row by row operations). KAIDAH PROGRAMMING T-SQL • • • • •

Gunakan huruf besar untuk semua kata kunci yang digunakan sebagai perintahperintah T-SQL. Gunakan huruf besar sebagai karakter pertama untuk nama table, termasuk semua obyek basisdata. Gunakan huruf kecil untuk nama scalar, misalnya nama kolom dan variabel, dsb. Buatlah nama yang unik, jangan gunakan nama yang sama untuk lebih dari satu obyek basisdata. Berkenaan dengan kepemilikan obyek basisdata (object ownership), database owner (dbo) haruslah merupakan pemilik semua obyek basisdata, untuk memudahkan pengelolaannya. Jika karena satu atau lain hal, kepemilikan obyek basisdata ini harus dirubah, gunakan sp_changeobjectowner yang ada di system stored procedure. Namun jika ingin merubah kepemilikan suatu basisdata, gunakan sp_changedbowner.

ATURAN PENULISAN IDENTIFIER Pada saat membuat basisdata maupun obyek basisdata, penulisan nama identitas (identifier) maksimal sebanyak 128 karakter dan maksimal sebanyak 116 karakter untuk obyek temporer (temporary object). Aturan penulisan identifier secara umum sbb: • Karakter pertama dapat berupa huruf, tanda ‘at’ (@), tanda ‘no’ (#), atau underscore (_). • Tidak boleh ada spasi. • Tidak termasuk kata kunci yang dipakai sebagai perintah-perintah T-SQL (T-SQL reserved keyword). Berikut ini daftar kata kunci yang dipakai sebagai perintah-perintah T-SQL (T-SQL reserved keyword). ADD ALL ALTER AND ANY AS ASC AUTHORIZATION BACKUP BEGIN BETWEEN BREAK BROWSE BULK BY CASCADE CASE CHECK CHECKPOINT CLOSE CLUSTERED COALESCE

DELETE DENY DESC DISK DISTINCT DISTRIBUTED DOUBLE DROP DUMMY DUMP ELSE END ERRLVL ESCAPE EXCEPT EXEC EXECUTE EXISTS EXIT FETCH FILE FILLFACTOR

IS JOIN KEY KILL LEFT LIKE LINENO LOAD NATIONAL NOCHECK NONCLUSTERED NOT NULL NULLIF OF OFF OFFSETS ON OPEN OPENDATASOURCE OPENQUERY OPENROWSET

RETURN REVOKE RIGHT ROLLBACK ROWCOUNT ROWGUIDCOL RULE SAVE SCHEMA SELECT SESSION_USER SET SETUSER SHUTDOWN SOME STATISTICS SYSTEM_USER TABLE TEXTSIZE THEN TO TOP 6

COLLATE COLUMN COMMIT COMPUTE CONSTRAINT CONTAINS CONTAINSTABLE CONTINUE CONVERT CREATE CROSS CURRENT CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR DATABASE DBCC DEALLOCATE DECLARE DEFAULT

FOR FOREIGN FREETEXT FREETEXTTABLE FROM FULL FUNCTION GOTO GRANT GROUP HAVING HOLDLOCK IDENTITY IDENTITY_INSERT IDENTITYCOL IF IN INDEX INNER INSERT INTERSECT INTO

OPENXML OPTION OR ORDER OUTER OVER PERCENT PLAN PRECISION PRIMARY PRINT PROC PROCEDURE PUBLIC RAISERROR READ READTEXT RECONFIGURE REFERENCES REPLICATION RESTORE RESTRICT

TRAN TRANSACTION TRIGGER TRUNCATE TSEQUAL UNION UNIQUE UPDATE UPDATETEXT USE USER VALUES VARYING VIEW WAITFOR WHEN WHERE WHILE WITH WRITETEXT

Semua identifier yang tidak termasuk dalam ketentuan diatas, harus diapit dengan kurung siku [ ]. Ada beberapa hal khusus berkenaan identifier al: •

•

•

Jika karakter pertama #, menunjukkan obyek lokal temporer basisdata (local temporary object – misalnya: local temporary table atau local temporary storedprocedure -). Jika karakter pertama ##, menunjukkan obyek global temporer basisdata (global temporary object – misalnya: global temporary table atau global temporary stored procedure -). Jika karakter pertama @, menunjukkan variable lokal. Sehingga tanda @ tidak dapat digunakan sebagai karakter pertama untuk nama obyek basisdata. Untuk mendefinisikan variabel lokal (menggunakan @), perintah yang digunakan adalah DECLARE.

DATA DEFINITION LANGUAGE (DDL) DDL digunakan untuk membuat dan mengelola basisdata, termasuk obyek basisdata (database object) seperti table, stored procedure, user defined function, trigger, view, default, index, rule dan statistic. Perintah yang digunakan adalah : CREATE

: Perintah untuk membuat basisdata, termasuk obyek basisdata.

Perintah-perintah T-SQL yang termasuk didalamnya adalah: 1. CREATE DATABASE (untuk membuat basisdata baru). Catatan : Setiap basisdata yang dibuat, akan menghasilkan 2 file yaitu: • *.mdf, merupakan file untuk menyimpan data sebenarnya yang mencakup semua obyek basisdata (rule, stored procedure, dll). • _log.ldf, berisi catatan transaksi tentang basisdata ybs. File tersebut secara default tersimpan di … \Program Files\Microsoft SQL Server\MSSQL\Data Contoh 1 : membuat basisdata Products secara sederhana (default) USE Master GO CREATE DATABASE Products GO ===========================================

hasilnya :

The CREATE DATABASE process is allocating 4.00 MB on disk 'products'. The CREATE DATABASE process is allocating 1.00 MB on disk 'Products_log'.

7

Contoh 2 : membuat basisdata Sales dengan pengaturan tertentu. USE Master GO CREATE DATABASE Sales ON ( NAME = Sales_dat, FILENAME = 'c:\program files\microsoft sql server\mssql\data\saledat.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = 'Sales_log', FILENAME = 'e:\program files\microsoft sql server\mssql\data\salelog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) GO =========================================== hasilnya : The CREATE DATABASE process is allocating 10.00 MB on disk 'Sales_dat'. The CREATE DATABASE process is allocating 5.00 MB on disk 'Sales_log'.

2. CREATE DEFAULT (untuk membuat obyek basisdata yang disebut default). Default : nilai yang dimasukkan dalam suatu kolom dari suatu tabel jika kolom tsb. tidak diisi oleh nilai tertentu pada saat perintah INSERT dijalankan. Default melekat pada suatu tabel, jika tabel dihapus maka default juga akan terhapus. Informasi default dapat dilihat pada obyek basisdata – defaults -. Contoh : membuat default dengan nama phonedflt dengan nilai ‘unknown’ pada basisdata Pubs. USE Pubs GO CREATE DEFAULT phonedflt AS 'unknown' ============================================ hasilnya The command(s) completed successfully.

3. CREATE FUNCTION (untuk membuat user defined function - udf - baru). Udf : set perintah T-SQL yang dibuat oleh user bersifat mengembalikan suatu nilai tertentu. * Contoh akan dibahas lebih lanjut tentang user defined function.

4. CREATE INDEX (untuk membuat index baru pada suatu table atau view). Index : merupakan kata kunci (keywords) yang digunakan untuk mempercepat proses pencarian data). Informasi index dapat dilihat pada system table – sysindexes -. Contoh : membuat index sederhana dengan nama auid_ind pada: kolom : au_id tabel : Authors basisdata : Pubs USE pubs CREATE INDEX auid_ind ON authors (au_id) GO =========================================== hasilnya The command(s) completed successfully. 8

5. CREATE PROCEDURE (untuk membuat stored procedure baru). Stored Procedure : set perintah T-SQL yang dapat meminta masukan dan mengembalikan suatu parameter yang dibuat oleh user). * Contoh akan dibahas lebih lanjut tentang stored procedure. 6. CREATE RULE (untuk membuat obyek basisdata yang disebut rule). Rule : menunjukkan daftar nilai atau batasan tertentu yang diperbolehkan dalam suatu kolom dari suatu tabel pada saat perintah INSERT dijalankan. Informasi rule dapat dilihat pada system stored procedure – sp_help -. Contoh : membuat rule dengan nama range_rule yang membatasi nilai yang dimasukkan dalam suatu kolom antara 1000 - 10000 (tergantung tabel mana yang dipakai). CREATE RULE range_rule AS @range >= 1000 AND @range < 10000 ============================================= hasilnya The command(s) completed successfully. 7. CREATE SCHEMA (untuk membuat schema baru). Schema : gambaran konseptual obyek basisdata yang memuat definisi dari table, view dan hak akses (permission). * Contoh akan dibahas lebih lanjut tentang table. 8. CREATE STATISTICS (untuk membuat statistic). Statistic : suatu histogram dari beberapa kolom dalam suatu tabel. Informasi statistic dapat dilihat pada system stored procedure – sp_autostats – diikuti nama tabel. Contoh : membuat statistic dengan nama names pada tabel Customers (database: Northwind). USE Northwind CREATE STATISTICS names ON Customers (CompanyName, ContactName) WITH SAMPLE 5 PERCENT GO ============================================ hasilnya The command(s) completed successfully. 9. CREATE TABLE (untuk membuat table baru). Table : merupakan gabungan beberapa kolom yang memetakan atribut suatu entitas. Contoh : membuat table sederhana dengan nama jobs pada database: Northwind dengan 2 kolom, Job_id : tipe data smallint, auto_increement dan primary key. Job_desc : tipe data varchar (50), nilai default ‘jalan jalan’ USE Northwind CREATE TABLE jobs ( job_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, job_desc varchar(50) NOT NULL DEFAULT 'jalan jalan' ) =========================================== hasilnya The command(s) completed successfully. 9

10. CREATE TRIGGER (untuk membuat trigger baru). Trigger: merupakan suatu bentuk stored procedure khusus yang dijalankan otomatis ketika user malakukan perubahan data pada suatu tabel. * Contoh akan dibahas lebih lanjut tentang stored procedure. 11. CREATE VIEW (untuk membuat view baru). View : merupakan suatu bentuk tabel semu yang berisi data sebenarnya dari satu atau beberapa table. * Contoh : akan dibahas lebih lanjut tentang stored procedure.

DROP

: Perintah untuk menghapus basisdata.

Perintah-perintah T-SQL yang termasuk didalamnya adalah: DROP DATABASE (untuk menghapus satu atau beberapa basisdata dari SQL Server). 1. Contoh : menghapus beberapa basisdata sekaligus (Pubs dan Newpubs). DROP DATABASE Pubs, Newpubs ================================================= hasilnya The command(s) completed successfully. Catatan : sekali terhapus, maka basisdata tsb. akan dihapus dari Enterprise Manager dan media simpan fisik (file *.mdf dan _log.ldf akan terhapus). Oleh karena itu berhati hatilah menggunakan perintah ini. Sebaiknya basisdata di back up secara rutin untuk menjaga kemungkinan terburuk akibat rusak atau terhapusnya suatu basisdata. 2.

DROP DEFAULT (untuk menghapus satu atau beberapa default dari basisdata yang aktif saat itu, baik yang terhubung pada suatu tabel atau tidak). Contoh 1 : menghapus default, jika tidak tergantung pada suatu table atau userdefined data type. Perintah berikut ini akan menghapus default dengan nama datedflt (database: Pubs). USE Pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'datedflt' AND type = 'D') DROP DEFAULT datedflt GO ================================================= hasilnya The command(s) completed successfully. Contoh 2 : menghapus default yang tergantung pada suatu table atau user-defined data type. Perintah berikut ini akan menghapus default dengan nama phonedflt (database: Pubs) yang bergantung pada kolom phone, tabel author. USE Pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'phonedflt' AND type = 'D') BEGIN EXEC sp_unbindefault 'authors.phone' DROP DEFAULT phonedflt END GO ================================================= hasilnya The command(s) completed successfully.

10

3.

DROP FUNCTION (untuk menghapus satu atau beberapa user defined function dari basisdata yang aktif saat itu). Contoh : menghapus beberapa user defined function sekaligus (misalnya : bulat_ganjil dan prima). DROP FUNCTION bulat_ganjil, prima ================================================ hasilnya The command(s) completed successfully.

4.

DROP INDEX (untuk menghapus satu atau beberapa index dari basisdata yang aktif saat itu. Perintah ini tidak dapat menghapus index yang terbentuk pada kolom yang bersifat primary key). Contoh : menghapus index dengan nama auid_ind pada tabel author (databaase: Pubs) USE Pubs IF EXISTS (SELECT name FROM sysindexes WHERE name = 'auid_ind') DROP INDEX authors.auid_ind GO ================================================ hasilnya The command(s) completed successfully.

5.

DROP PROCEDURE (untuk menghapus satu atau beberapa stored procedure dari basisdata yang aktif saat itu). Contoh : menghapus stored procedure dengan nama byroyalty yang ada di database: Pubs. USE Pubs DROP PROCEDURE byroyaly GO ================================================ hasilnya The command(s) completed successfully.

6.

DROP RULE (untuk menghapus satu atau beberapa rule dari basisdata yang aktif saat itu). Contoh : menghapus rule yang tergantung pada suatu table atau user-defined data type. Perintah berikut ini akan menghapus rule dengan nama pubid_rule (database: Pubs) yang bergantung pada kolom pub_id, tabel publisher. USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'pubid_rule' AND type = 'R') BEGIN EXEC sp_unbindrule 'publishers.pub_id' DROP RULE pubid_rule END GO ================================================ hasilnya The command(s) completed successfully.

7.

DROP STATISTICS (untuk menghapus statistic dalam satu group –collection - atau beberapa collection pada tabel tertentu dari basisdata yang aktif). Contoh : menghapus beberapa statistic sekaligus (statistic group – collection – dengan nama anames dari tabel authors dan collection dengan nama tnames dari tabel titles)

11

USE Pubs DROP STATISTICS authors.anames, titles.tnames GO ================================================ hasilnya The command(s) completed successfully.

8.

DROP TABLE (untuk menghapus semua data dari suatu table beserta definisinya – termasuk: index, trigger, constraint dan hak akses (permission) - dari basisdata yang aktif atau dari basisdata yang dipilih. View dan stored procedure yang mengacu pada table tersebut harus dihapus juga dengan perintah DROP VIEW atau DROP PROCEDURE). Contoh : menghapus tabel titles1 dari database : Pubs USE Pubs DROP TABLE titles1 GO ------------------------------------------------ atau USE Nortwind DROP TABLE Pubs.dbo.titles1 GO ================================================ hasilnya The command(s) completed successfully.

9.

DROP TRIGGER (untuk menghapus satu atau beberapa trigger dari basisdata yang aktif). Contoh : menghapus trigger dengan nama employee_insupd dari database : Pubs. USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'employee_insupd' AND type = 'TR') DROP TRIGGER employee_insupd GO ================================================ hasilnya The command(s) completed successfully.

10. DROP VIEW (untuk menghapus satu atau beberapa view dari basisdata yang aktif) Contoh : menghapus view dengan nama titles_view pada database : Pubs. USE Pubs IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'titles_view') DROP VIEW titles_view GO ================================================ hasilnya The command(s) completed successfully.

ALTER

: Untuk memodifikasi basisdata.

Perintah-perintah T-SQL yang termasuk didalamnya adalah: 1. ALTER DATABASE (untuk menambah atau menghapus file dan filegroup dari suatu basisdata. Perintah ini juga dapat digunakan untuk memodifikasi atribut suatu file atau filegroup – misalnya: merubah nama, ukuran dari suatu file – serta merubah nama basisdata, nama filegroup dan merubah nama file data *.mdf – maupun file catatan - _log.ldf -) Contoh : membuat basisdata baru dengan nama Test1, kemudian dimodifikasi dengan menambahkan file data baru dengan nama Test1dat2, ukuran 5 Mb.

12

USE master GO CREATE DATABASE Test1 ON ( NAME = Test1dat1, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat1.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) GO ALTER DATABASE Test1 ADD FILE ( NAME = Test1dat2, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat2.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) GO ============================================= hasilnya The CREATE DATABASE process is allocating 5.00 MB on disk 'Test1dat1'. The CREATE DATABASE process is allocating 1.25 MB on disk 'Test1_log'. Extending database by 5.00 MB on disk 'Test1dat2'.

2.

ALTER FUNCTION (untuk memodifikasi user defined funcdion yang telah dibuat dengan perintah CREATE FUNCTION, tanpa merubah hak akses (permission) serta tidak berpengaruh pada dependent function, stored procedure dan trigger). * Contoh akan dibahas lebih lanjut tentang flow kontrol.

3.

ALTER PROCEDURE (untuk memodifikasi procedure yang telah dibuat dengan perintah CREATE PROCEDURE, tanpa merubah hak akses (permission) serta tidak berpengaruh pada dependent stored procedure atau trigger). * Contoh akan dibahas lebih lanjut tentang stored procedure.

4.

ALTER TABLE (untuk memodifikasi definisi suatu table, menambah atau menghapus kolom dan constraint atau mengaktifkan dan menonaktifkan constraint dan trigger). Contoh : membuat tabel dengan nama test_table dengan 1 kolom (kolom_a, tipe data INT), kemudian ditambahkan 1 kolom (kolom_b, tipe data VARCHAR (20), setiap menambah data baru diperbolehkan NULL). CREATE TABLE test_table ( kolom_a INT) GO ALTER TABLE test_table ADD kolom_b VARCHAR(20) NULL GO ================================================ hasilnya The command(s) completed successfully.

5.

ALTER TRIGGER (untuk memodifikasi definisi suatu trigger yang telah diBuat dengan perintah CREATE TRIGGER). * Contoh akan dibahas lebih lanjut tentang stored procedure.

6.

ALTER

VIEW (untuk memodifikasi view, termasuk indexed view tanpa mempengaruhi hak akses (permission), dependent stored procedure atau trigger).

* Contoh akan dibahas lebih lanjut tentang stored procedure. 13

DATA MANIPULATION LANGUAGE (DML) DML merupakan perintah-perintah T-SQL yang paling sering digunakan, antara lain untuk menampilkan, menambah merubah dan menghapus informasi dari suatu basisdata. Perintah-perintah tsb. adalah:

SELECT : untuk menampilkan data dari basisdata berupa pemilihan terhadap baris, kolom dari satu atau beberapa table.

Contoh : berikut ini perintah untuk menampilkan data customerid dan companyname dari tabel Customers (database: Northwind) dengan kriteria customerid = ‘ANTON’ USE Northwind SELECT customerid,companyname FROM Customers WHERE customerid = 'ANTON' GO =================================================== hasilnya : customerid companyname ---------- ---------------------------------------ANTON Antonio Moreno Taquería (1 row(s) affected)

INSERT

: untuk menambah data baru pada suatu table atau view.

Contoh : berikut ini perintah untuk menambahkan data pada tabel Customers (database: Northwind), customerid = ACME1 companyname = ACME Publishing contactname = Fernando conntact title = DBA USE Northwind INSERT INTO Customers (customerid, companyname, contactname, contacttitle) VALUES ('ACME1','ACME Publishing','Fernando','DBA') GO =================================================== hasilnya : (1 row(s) affected)

UPDATE

: untuk merubah data pada suatu table.

Contoh : berikut ini perintah untuk merubah contactname = Fernando menjadi Fernando Guerrero pada tabel Customers (database: Northwind). USE Northwind UPDATE Customers SET contactname = 'Fernando Guerrero' WHERE customerid = 'ACME1' GO =================================================== hasilnya : (1 row(s) affected)

14

DELETE

: untuk menghapus data dari suatu table.

Contoh : berikut ini perintah untuk menghapus data pada tabel Customers (database: Northwind), dengan kriteria customerid = ‘ACME1’ USE Northwind DELETE Customers WHERE customerid = 'ACME1' GO =================================================== hasilnya : (1 row(s) affected)

DATA CONTROL LANGUAGE (DCL). DCL merupakan bagian dari perintah-perintah T-SQL yang digunakan untuk mengatur keamanan basisdata (database security), dengan mengatur hak akses permission dari masing-masing obyek basisdata. Perintah-perintah tsb. antara lain:

: untuk memperbolehkan user mengakses suatu basisdata maupun obyek-obyek basisdata. DENY : untuk membatalkan hak akses user terhadap suatu basisdata maupun obyekobyek basisdata. REVOKE : untuk menghapus informasi hak akses user dari permissions table (syspermissions) baik berupa GRANT maupun DENY. GRANT

Syntax

: GRANT permission ON object TO user DENY permission ON object TO user REVOKE permission ON object TO user

Contoh : berikut ini perintah memperbolehkan user1 untuk menampilkan data pada tabel Catetgories (database: Northwind). USE Northwind GRANT SELECT ON Categories TO user1 GO =================================================== hasilnya : The command(s) completed successfully Sedangkan untuk mengatur hak akses (permission) kepada user tententu: Syntax

: GRANT statement TO user DENY statement TO user REVOKE statement TO user

Contoh : berikut ini perintah memperbolehkan user1 untuk membuat table pada database Northwind. USE Northwind GRANT CREATE TABLE TO user1 GO =================================================== hasilnya : The command(s) completed successfully

15

Untuk setiap obyek basisdata dapat diatur jenis-jenis hak aksesnya, sehingga setiap obyek basisdata dapat memiliki hak akses yang berbeda. Hak akses tersebut antara lain : SELECT, INSERT, UPDATE, DELETE dan EXECUTE. Sedangkan hak akses REFERENCES, hanya berlaku jika kita ingin membuat kunci tamu (foreign key) pada suatu table. Jenis hak akses yang lain adalah berupa pernyataan (statement), yaitu user dapat membuat obyek-obyek basisdata serta mem-back up file basisdata (*.mdf) maupun file catatannya (_log.ldf). Pernyataan (statement) yang dipakai untuk hak akses antara lain : BACKUP DATABASE, BACKUP LOG, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, dan CREATE VIEW. Diantara beberapa pernyataan (statement) tsb, ada satu pernyataan yang hanya boleh dibuat pada basisdata MASTER, yaitu : CREATE DATABASE. Contoh : berikut ini dibuat sebuah login baru (login3), kemudian menambahkan user (user3) untuk login yang baru dibuat tsb, serta memperbolehkannya untuk membuat basisdata baru (dengan pernyataan CREATE DATABASE). USE Master EXEC sp_addlogin 'login3' EXEC sp_adduser 'login3','user3' GRANT CREATE DATABASE TO user3 GO ============================================= hasilnya : New login created. Granted database access to 'login3'.

TIP Untuk membuat basisdata maupun obyek-obyek basisdata dapat diakses oleh seluruh user, gunakan klausa public. Contoh : dengan menggunakan klausa public untuk membuat tabel (CREATE TABLE) pada database: Northwind, menyebabkan semua user dapat membuat table pada database: Nothwind. USE Northwind GRANT CREATE TABLE TO public GO =========================================== hasilnya : The command(s) completed succesfully.

16

PERTEMUAN 3 TIPE DATA, VARIABEL, OPERATOR DAN FLOW KONTROL TIPE DATA Ada 27 macam tipe data yang digunakan dalam Transact SQL (T-SQL) yang dikelompokkan sbb: 1.

Limited Character • CHAR Karakter dengan panjang tetap, menyimpan data s/d 8.000 karakter. • VARCHAR Karakter dengan panjang dapat berubah, menyimpan data s/d 8.000 karakter. • NCHAR Karakter Unicode dengan panjang tetap, menyimpan data s/d 4.000 karakter. • NVARCHAR Karakter Unicode dengan panjang dapat berubah, menyimpan data s/d 4.000 karakter.

2.

Unlimited Character • TEXT Karakter dengan panjang dapat berubah, menyimpan data s/d 2.147.483.647 karakter. • NTEXT Karakter Unicode dengan panjang dapat berubah, menyimpan data s/d 1.073.741.823 karakter.

3.

Binary • BINARY Data biner dengan panjang tetap, menyimpan data s/d 8.000 bytes. • VARBINARY Data biner dengan panjang dapat berubah, menyimpan data s/d 8.000 bytes.

4.

Binary Large Objects • IMAGE Data biner dengan panjang dapat berubah, menyimpan data s/d 2.147.483.647 bytes.

5.

Integer • BIGINT Nilai integer dari -2^63 s/d 2^63 – 1, menyimpan 8 bytes untuk tiap nilai. • INT Nilai Integer dari -2.147.483.648 s/d 2.147.483.647, menyimpan 4 bytes untuk tiap nilai. • SMALLINT Nilai integer dari -32.768 s/d 32.767 • TINYINT Nilai integer dari 0 s/d 255 • BIT Nilai integer dengan hanya 2 kemungkinan nilai, yaitu 0 atau 1.

6.

Aproximate Numeric • REAL Angka presisi 1 s/d 7 digit, menyimpan 4 bytes setiap nilai. • FLOAT Angka presisi 8 s/d 15 digit, menyimpan 8 bytes setiap nilai.

7.

Exac Numeric • DECIMAL atau NUMERIC Angka dengan presisi tetap, menyimpan data s/d 17 bytes

17

8.

Date and Time • DATETIME Tanggal dan Waktu dari 1 Januari 1753 s/d 31 Desember 9999. Akurasi waktu sampai dengan 1/300 detik. • SMALLDATETIME Tanggal dan Waktu dari 1 Januari 1900 s/d 6 Juni 2079. Akurasi waktu sampai dengan menit.

9.

Currency • MONEY Nilai mata uang dari -922.337.203.685.477,5808 s/d 922.337.203.685.477,5807 • SMALLMONEY Nilai mata uang dari -214.748,3648 s/d 214.748,3647

10. Other • UNIQUEIDENTIFIER Berkaitan dengan tipe data sistem (NEWID()). Jika menggunakan tipe data ini, anda dapat menggunakan NEWID() sebagai nilai default, menyimpan 16 byte GUID. USE Northwind Contoh : CREATE TABLE Bigidentifiers ( kolom1 UNIQUEIDENTIFIER DEFAULT NEWID() ) GO ========================================= hasilnya The command(s) completed successfully. •

TABLE Hampir sama seperti table, menyimpan data dalam bentuk baris (row) bersifat sementara (temporary storage).

•

SQL_VARIANT Dapat menyimpan hampir semua nilai (integer, character, numeric, dll) kecuali TEXT, NTEXT, IMAGE, TIMESTAMP dan SQL_VARIANT , meyimpan data s/d 8.016 bytes. Tipe data ini hampir sama dengan tipe data Variant pada Visual Basic.

•

TIMESTAMP atau ROWVERSION Tipe data khusus yang selalu berubah setiap ada penambahan maupun perubahan data, menyimpan 8 byte data biner.

•

CURSOR Hanya digunakan untuk nilai variabel dan nilai output parameter stored procedure.

TIPE DATA BUATAN SENDIRI (User Defined Data Type – UDDT -) Kita dapat membuat tipe data sendiri dengan menggunakan system stored procedure yang disebut sp_addtype. Syntax : sp_addtype uddt_name, uddt_base_type, nullability Contoh : membuat tipe data untuk menyimpan data no telpon (boleh NULL) USE Northwind EXEC sp_addtype phone_number,'CHAR(10)',NULL GO ====================================================== hasilnya (1 row(s) affected) Type added.

18

Informasi tentang UDDT tersimpan pada system table systypes, yang ada di semua basisdata. Sedangkan untuk menampilkan properties UDDT yang telah dibuat, gunakan system stored procedure sp_help seperti contoh berikut ini. Untuk menghapusnya gunakan sp_droptype. USE Northwind EXEC sp_help phone_number GO ======================================= hasilnya Type_name Storage_type Length Prec Scale Nullable Default_name Rule_name ------------ ------------ ------ ---- ----- -------- ------------ ------phone_number char 10 10 NULL yes none none TIP UDDT tersimpan di database tertentu pada saat dibuat. Jika kita menginginkan semua user juga dapat menggunakan UDDT yang kita buat, maka UDDT harus dibuat di database Model. Karena semua database yang dibuat, merupakan turunan dari database Model.

MEMILIH TIPE DATA YANG SESUAI Memilih tipe data yang sesuai dengan jenis data yang akan diolah merupakan sesuatu yang penting. Oleh karena itu perhatikan hal-hal sbb : 1.

Untuk data yang berbentuk teks, gukanan tipe data yang sesuai (Limited Character atau Unlimited Character) dan pastikan bahwa panjangnya juga sesuai. Misalnya : untuk menyimpan informasi kode pos, kita memilih tipe data VARCHAR(100). Meskipun tipe data ini dapat menampung informasi kode pos, namun terjadi pemborosan media simpan. Karena kode pos maksimal hanya berisi 5 karakter. Untuk data yang jumlahnya sedikit hal ini tidak begitu terasa, namun bagaimana untuk data yang jumlahnya besar? Tentu akan menurunkan kinerja database yang kita buat.

2.

Untuk data yang berbentuk angka, carilah nilai minimum dan maksimumnya. Hal ini untuk menghindari penggunaan tipe data yang terlalu besar sehingga menimbulkan pemborosan media simpan. Misalnya : untuk menyimpan informasi IP Address dari suatu komputer dalam jaringan (LAN), gunakan gunakan tipe data TINYINT. Karena tipe data ini dapat menyimpan nilai dari 0 s/d 255. Catatan: Jika panjang data untuk tipe data karakter (CHAR, NCHAR, VARCHAR atau NVARCHAR) atau tipe data biner (BINARY atau VARBINARY) tidak ditentukan, maka panjangnya dianggap 1 oleh SQL Server. Meskipun pada saat menyimpan data tidak terjadi error, namum data yang tersimpan hanya karakter pertama saja. Contoh : USE Northwind DECLARE @teks VARCHAR SET @teks = 'Probolinggo' SELECT @teks GO ========================================== hasilnya P (1 row(s) affected)

19

3.

Berhati-hatilah jika mendefinisikan panjang tipe data, apakah menggunakan panjang yang tetap (fixed length) atau dapat berubah (variable length). Sebagai ilustrasi perhatikan contoh berikut ini: USE Northwind CREATE TABLE Mahasiswa ( nama VARCHAR(50), alamat CHAR(50) ) GO ============================================ hasilnya The command(s) completed successfully. Jika kolom nama diisi “Paimo” dan alamat diisi “Leces”, maka: • SQL Server hanya menggunakan 5 byte untuk menyimpan “Paimo”, karena tipe datanya adalah VARCHAR (variable length). • SQL Server menggunakan 50 byte untuk menyimpan “Leces”, karena tipe datanya adalah CHAR (fixed length).

4.

Jika ingin menyimpan data yang lebih besar dari 8.000 byte, gunakan tipe data TEXT, NTEXT atau IMAGE yang dapat menyimpan data s/d 2 GB. Namun untuk mengelola data tsb. harus menggunakan perintah-perintah tertentu (WRITETEXT, READTEXT dan UPDATETEXT). TIP : Perintah-perintah DML dapat juga digunakan untuk mengelola data tsb. namun hanya sebagian data saja yang dapat diakses (dengan menggunakan fungsi SUBSTRING).

5.

Berhati-hatilah jika menggunakan kelompok data Aproximate Numeric (FLOAT atau REAL), karena menyimpan angka dengan presisi. Sehingga tidak dapat digunakan sebagai perbandingan angka pasti (exact comparison) pada saat pemilihan data dengan menggunakan klausa WHERE.

6.

Tipe data TABLE tidak dapat digunakan sebagai tipe data suatu kolom dalam tabel, karena tidak mungkin ada tabel di dalam tabel. Tipe data ini digunakan sebagi tabel sementara (temporary table) karena hanya tersimpan di memory (lebih cepat diakses daripada tabel sebenarnya yang tersimpan di media simpan – hard disk -). Sebagfai ilustrasi perhatikan contoh berikut yang membuat sebuah variabel dengan tipe data TABLE, kemudian ditambahkan 2 data baru sbb: Contoh : USE Northwind DECLARE @Dosen TABLE(nama VARCHAR(20), alamat VARCHAR(20)) INSERT @Dosen VALUES ('Andi','Probolinggo') INSERT @Dosen VALUES ('Indra','Leces') SELECT * FROM @Dosen GO ============================================= hasilnya (1 row(s) affected) (1 row(s) affected) nama -------------------Andi Indra (2 row(s) affected)

7.

alamat -------------------Probolinggo Leces

Meskipun tipe data TIMESTAMP dan ROWVERSION identik, gunakan tipe data ROWVERSION untuk keperluan yang tepat daripada menggunakan TIMESTAMP. Karena Microsoft akan merubah tipe data TIMESTAMP berkenaan dengan standar ANSI SQL 92, dimana tipe data TIMESTAMP digunakan untuk menyimpan data tanggal dan waktu. 20

VARIABEL Variabel lokal dalam T-SQL digunakan di stored procedure, user defined function, trigger dan script. Untuk definisi variabel gunakan perintah DECLARE. Syntax : DECLARE @variable_name datatype Pada saat didefinisikan, variabel bernilai secara default bernilai NULL. Gunakan perintah SET atau SELECT. Perhatikan contoh berikut: DECLARE @nama VARCHAR(20) SET @nama = 'Rendy' SELECT @nama GO ===================================== hasilnya Rendy (1 row(s) affected)

Nilai variabel dapat juga diambilkan dari perintah query. Perhatikan contoh berikut: USE Northwind DECLARE @ln VARCHAR(20), @fn VARCHAR(20) SELECT @ln = lastname, @fn = firstname FROM Employees WHERE employeeid = 1 SELECT @fn, @ln GO ============================================= hasilnya -------------------- -------------------Nancy Davolio (1 row(s) affected) Variabel yang diawali dengan tanda @@ menunjukkan variabel global system function. Variabel seperti ini tidak dapat kita ubah nilainya, karena digunakan oleh SQL Server untuk tujuan tertentu. Berikut ini daftar system funtion yang diawali dengan @@. System Function @@CONNECTIONS @@ERROR @@IDENTITY @@MAX_CONNECTIONS @@OPTIONS @@ROWCOUNT @@SERVERNAME @@SPID @@VERSION

Keterangan Jumlah koneksi ke SQL Server sejak pertama kali start. Kode error yang muncul dari pernyataan terakhir kali dijalankan. Jika tidak ada error, nilainya 0. Identitas (id) terakhir yang dibuat pada session yang aktif. Jumlah maksimal koneksi ke SQL Server yang dibolehkan. Informasi tentang set option pada session yang aktif. Jumlah baris dari pernyataan terakhir yang dieksekusi. Nama SQL Server yang aktif. Id dari proses yang aktif. Versi SQL Server yang aktif.

Contoh : menampilkan versi SQL Server yang aktif. SELECT @@VERSION GO ============================================= hasilnya Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) (1 row(s) affected) 21

OPERATOR Ada beberapa tipe operator yang digunakan di SQL Server, yaitu: 1.

Operator Penugasan (Assignment Operator) • Simbol : = Digunakan untuk memasukkan nilai ke dalam variabel (lihat contoh di atas)

2.

Operator Aritmatika (Arithmetic Operator) • Simbol : + (penambahan – addition-) • Simbol : - (pengurangan – subtraction -) • Simbol : * (perkalian – multiplication -) • Simbol : / (pembagian – division -) • Simbol : % (sisa pembagian – modulo atau remainder of division -) Contoh : SELECT 8/4 SELECT 9%4 SELECT -7 GO ================================================== hasilnya ----------2 (1 row(s) affected) ----------1 (1 row(s) affected) -----------7 (1 row(s) affected)

3.

Operator Perbandingan (Comparison Operator) • Simbol : = (sama dengan – equal to -) • Simbol : <> (tidak sama dengan – not equal to -) • Simbol : < (kurang dari – less than -) • Simbol : > (lebih dari – greater than -) • Simbol : <= (kurang dari sama dengan – less than or equal to -) • Simbol : >= (lebih dari sama dengan – greater than or equal to -) Operator perbandingan dapat digunakan untuk semua tipe data kecuali TEXT, NTEXT dan IMAGE. Contoh : USE Northwind SELECT employeeid, lastname, firstname FROM Employees WHERE employeeid <= 3 GO ================================================== hasilnya employeeid lastname firstname ----------- -------------------- ---------1 Davolio Nancy 2 Fuller Andrew 3 Leverling Janet (3 row(s) affected)

4.

Operator Logika (Logical Operator) • Simbol : AND (bernilai true, jika semua pernyataan bernilai true) • Simbol : OR (bernilai true, jika ada salah satu pernyataan bernilai true) • Simbol : NOT (bernilai true, jika pernyataannya bernilai false) 22

Contoh : USE Northwind SELECT employeeid, lastname, firstname, city FROM Employees WHERE firstname='anne'AND city='london' GO ================================================ hasilnya employeeid lastname firstname city ----------- -------------------- ---------- --------------9 Dodsworth Anne London (1 row(s) affected) •

Simbol : BETWEEN (memeriksa nilai diantara dua ekspresi) Syntax : ekspresi1 BETWEEN ekspresi2 AND ekspresi3 Simbol BETWEEN dapat juga ditulis dengan >= dan <= Syntax : (ekspresi1 >= ekspresi2) AND (ekspresi1 <= ekspresi3)

Contoh : USE Northwind SELECT employeeid, firstname, lastname FROM Employees WHERE employeeid BETWEEN 2 AND 5 GO ================================================= hasilnya employeeid firstname lastname ----------- ---------- -------------------2 Andrew Fuller 3 Janet Leverling 4 Margaret Peacock (3 row(s) affected) •

Simbol : IN (memeriksa nilai yang terdapat dalam beberapa ekspresi) Syntax : ekspresi IN (ekspresi_1, ekspresi_2, …, ekspresi_n)

Contoh : USE Northwind SELECT employeeid, firstname, lastname FROM Employees WHERE employeeid IN (2,6,9) GO ================================================ hasilnya employeeid firstname lastname ----------- ---------- -------------------2 Andrew Fuller 6 Michael Suyama 9 Anne Dodsworth (3 row(s) affected) •

Simbol : LIKE (mendapatkan nilai tertentu yang menggunakan karakter khusus yang disebut wildcard.)

sesuai

kriteria

dengan

Syntax : ekspresi LIKE kriteria Ada 4 macam wildcard yang digunakan, yaitu : »

Tanda persen (%) : untuk menentukan semua kriteria 23

Contoh : USE Northwind SELECT firstname, lastname FROM Employees WHERE firstname LIKE 'a%' SELECT firstname, lastname FROM Employees WHERE firstname LIKE '%e' SELECT firstname, lastname FROM Employees WHERE firstname LIKE '%ae%' GO =========================================== hasilnya firstname lastname ---------- -------------------Andrew Fuller Anne Dodsworth (2 row(s) affected) firstname lastname ---------- -------------------Anne Dodsworth (1 row(s) affected) firstname lastname ---------- -------------------Michael Suyama (1 row(s) affected) » » »

Tanda underscore (_): untuk menentukan satu karakter kriteria Di dalam tanda kurung siku [ ]: untuk menentukan kriteria yang ada ada di dalamnya Kombinasi kriteria yang ada di dalam kurung siku [ ]

Contoh : USE Northwind SELECT firstname, lastname FROM Employees WHERE firstname LIKE '_anet' SELECT firstname, lastname FROM Employees WHERE firstname LIKE '[js]%' SELECT firstname, lastname FROM Employees WHERE firstname LIKE '[^amjslr]%' GO ============================================= firstname lastname ---------- -------------------Janet Leverling (1 row(s) affected)

hasilnya

firstname lastname ---------- -------------------Janet Leverling Steven Buchanan (2 row(s) affected) firstname lastname ---------- -------------------Nancy Davolio (1 row(s) affected)

24

•

Simbol : + (menggabungkan string) Contoh : DECLARE @kata1 VARCHAR(10), @kata2 VARCHAR(10) SET @kata1 = 'AMIK ' SET @kata2 = 'TARUNA' SELECT @kata1 + @kata2 GO ================================================= hasilnya -------------------AMIK TARUNA (1 row(s) affected) Biasanya simbol + digunakan untuk menggabungkan dua kolom. Contoh : SELECT firstname + ' ' + lastname FROM Employees WHERE employeeId = 1 GO ======================================== hasilnya ------------------------------Nancy Davolio (1 row(s) affected)

FLOW KONTROL Flow kontrol digunakan untuk mengontrol aliran eksekusi kode program dalam T-SQL. Ada beberapa macam flow kontrol, yaitu: 1.

IF … ELSE Contoh : USE Northwind IF EXISTS (SELECT * FROM Shippers) BEGIN DECLARE @jum_brs INT SELECT @jum_brs = count(*) FROM Shippers PRINT 'Ada ' + CAST(@jum_brs AS VARCHAR(10)) + ' data pada tabel Shippers' END ELSE PRINT 'Tidak ada data' GO ================================================== hasilnya Ada 3 data pada tabel Shippers

2.

RETURN Perintah ini digunakan untuk keluar dari script atau stored procedure. Jika digunakan pada stored procedure, RETURN menunjukkan nilai parameter. Contoh : PRINT 'Kalimat Pertama' RETURN PRINT 'Kalimat Kedua' GO ================================================= hasilnya Kalimat Pertama

25

3.

WAITFOR Digunakan untuk : • Memerintahkan SQL Server menunggu sampai jam yang ditentukan. Syntax : WAITFOR TIME ‘jam’ • Memerintahkan SQL Server menunggu sampai waktu yang ditentukan. Syntax : WAITFOR DELAY ‘waktu’ Contoh : WAITFOR TIME '10:25:00' SELECT getdate() WAITFOR DELAY '00:00:10' SELECT getdate() GO ========================================== hasilnya 28/09/2007 10:25:00 (1 row(s) affected) -----------------------------------------------------28/09/2007 10:25:10 (1 row(s) affected)

4.

WHILE Perulangan pernyataan sampai kondisi tertentu benar. Contoh : DECLARE @a INT, @b INT, @hasil INT SET @a = 3 SET @b = 4 SET @hasil = 0 WHILE @b > 0 BEGIN SET @hasil = @hasil + @a SET @b = @b - 1 END SELECT @hasil GO ================================================== hasilnya 12 (1 row(s) affected)

5.

BREAK Perintah ini digunakan di dalam pernyataan WHILE, untuk keluar dari perulangan.

6.

CONTINUE Sama seperti BREAK, digunakan untuk melanjutkan perulangan WHILE dari awal. Contoh : DECLARE @hitung INT SET @hitung = 0 WHILE @hitung < 10 BEGIN IF @hitung = 3 BREAK SET @hitung = @hitung + 1 PRINT 'Looping ke '+ CAST(@hitung AS VARCHAR(10)) CONTINUE PRINT 'Tidak dijalankan' END PRINT 'Selesai looping' GO ================================================ hasilnya Looping ke 1 Looping ke 2 Looping ke 3 Selesai looping 26

7.

GOTO Perintah ini mengarahkan SQL Server untuk menjalankan perintah tertentu. Perintah ini sangat berguna untuk menangani kesalahan program (error handling). Contoh : USE Northwind IF NOT EXISTS (SELECT * FROM Suppliers) GOTO error1 GOTO selesai error1: PRINT 'Tabel Supplier tidak ada data' selesai: PRINT 'Program selesai dijalankan' ================================================ hasilnya Program selesai dijalankan

BATCH DAN SCRIPT Batch Merupakan satu atau beberapa perintah SQL Server yang diakhiri dengan pernyataan GO. Batch dianggap sebagai satu unit program kecil (hampir seperti stored procedure). Namun ada beberapa perintah yang tidak dapat ditulis dua kali dalam satu batch, yaitu CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, dan CREATE VIEW. Script Merupakan perintah-perintah SQL Server yang tersusun dari satu atau beberapa batch. Srcipt dapat digunakan untuk menyimpan schema dari basisdata dalam satu file.

27

PERTEMUAN 4 TABLE MEMBUAT TABLE SQL Server dapat menampung s/d 2 milyar table dalam satu database, yang dalam setiap table-nya terdapat s/d 1024 kolom/field. Ukuran table dan banyaknya baris/record yang dapat disimpan tergantung dari kapasitas media simpannya (hard disk). Kapasitas penyimpanan data per baris/record maksimal sebanyak 8060 byte. Di dalam SQL Server ada 2 jenis tabel yang dapat dibuat, yaitu tabel permanen dan tabel sementara. Tabel permanen adalah tabel yang tersimpan dalam media simpan (hard disk), sedangkan tabel sementara adalah tabel yang dibuat di dalam memory.

Berikut format dasar perintah untuk membuat tabel: CREATE TABLE [ database_name.[ owner ] . | owner. ] table_name ( { < column_definition > | column_name AS computed_column_expression | < table_constraint > ::= [ CONSTRAINT constraint_name ] } | [ { PRIMARY KEY | UNIQUE } [ ,...n ] ) [ ON { filegroup | DEFAULT } ] [ TEXTIMAGE_ON { filegroup | DEFAULT } ] ---------------------------------------------------------------------< column_definition > ::= { column_name data_type } [ COLLATE < collation_name > ] [ [ DEFAULT constant_expression ] | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ] ] [ ROWGUIDCOL] [ < column_constraint > ] [ ...n ] < column_constraint > ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor ] [ON {filegroup | DEFAULT} ] ] ] | [ [ FOREIGN KEY ] REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] [ NOT FOR REPLICATION ] ] | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) }

28

< table_constraint > ::= [ CONSTRAINT constraint_name ] { [ { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] { ( column [ ASC | DESC ] [ ,...n ] ) } [ WITH FILLFACTOR = fillfactor ] [ ON { filegroup | DEFAULT } ] ] | FOREIGN KEY [ ( column [ ,...n ] ) ] REFERENCES ref_table [ ( ref_column [ ,...n ] ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] [ NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] ( search_conditions ) }

Perintah diatas dapat disederhanakan menjadi : CREATE TABLE table_name ( Fieldname1_name data_type([NULL|NOT Fieldname2_name data_type([NULL|NOT Fieldname2_name data_type([NULL|NOT FieldnameN_name data_type([NULL|NOT PRIMARY KEY ([fieldname]) )

NULL]), NULL]), NULL]), NULL]),

Sebelum membuat tabel, sebaiknya dibuat rancangannya dahulu.

LATIHAN : buatlah tabel dengan struktur sbb: 1.

Tabel nasabah.

No 1 2 3

Field id_nasabah nama_nasabah alamat_nasabah

2.

Tabel cabang_bank

No 1 2 3

Field kode_cabang nama_cabang alamat_cabang

3.

Tabel bagian

No 1 2

Field kode_bagian nama_bagian

4.

Tabel kota

No 1 2

Field kode_kota nama_kota

Tipe Data INT VARCHAR VARCHAR

NULL Tidak Tidak Tidak

Ket PK Panjang=30 Panjang=50, DEFAULT NULL

Tipe Data CHAR VARCHAR VARCHAR

NULL Tidak Tidak Tidak

Ket Panjang=5,PK Panjang=50 Panjang=50, DEFAULT NULL

Tipe Data INT VARCHAR

NULL Tidak Tidak

Ket PK Panjang=20, UNIQUE

Tipe Data INT VARCHAR

NULL Tidak Tidak

Ket PK Panjang=20

29

MEMBUAT TABEL DENGAN CONSTRAINT Constraint merupakan aturan-aturan tentang bagaimana sebuah nilai dapat dimasukkan ke dalam kolom/field. Constraint merupakan mekanisme standar dalam penerapan integritas basisdata (database integrity). Ada 3 jenis constraint, yaitu : •

Domain Constraint Data yang dimasukkan dalam kolom/field harus sesuai dengan kriteria yang kita inginkan (validasi data). Misalnya : jika kita menghendaki kolom total_harga pada tabel penjualan_barang hanya dapat diisi dengan nilai >= 0, maka jika kita memasukkan data dengan nilai minus (<= 0), maka data tersebut tidak dapat diterima. Yang termasuk constraint ini adalah FOREIGN KEY, CHECK dan DEFAULT.

•

Entity Constraint Data yang ada pada satu baris/record tidak boleh ada pada baris/record yang lainnya. Yang termasuk constraint ini adalah PRIMARY KEY dan UNIQUE.

•

Referential Integrity Constraint Constraint ini mempertahankan konsistensi data dari beberapa tabel yang terhubung melalui suatu relationship. Jika kita mendefinisikan suatu Referential Integrity, SQL Server akan mencegah user untuk :
Menambah baris/record pada satu tabel yang terhubung dengan relationship, jika data tsb. tidak ada pada tabel master.
Merubah nilai pada tabel master, sehingga baris/record pada tabel yang lain manjadi “yatim piatu”.
Menghapus baris/record pada tabel master, jika pada tabel lainnya terdapat baris/record yang berhubungan.

Contoh : Berikut ini adalah Referential Integrity yang terbentuk karena adanya relationship antara tabel sales dan tabel titles. Relaionship yang dimaksud adalah antara title_id (Foreign Key) pada tabel sales dan title_id (Primary Key) pada tabel titles. Perhatikan gambar berikut :

30

CONSTRAINT CLASS SQL Server mendukung 5 macam Constraint, yaitu : 1.

PRIMARY KEY Constraint Merupakan batasan nilai yang unik (tidak boleh ada data kembar) pada suatu kolom/field. Satu tabel hanya boleh mengandung 1 PRIMARY KEY. Contoh : USE Northwind CREATE TABLE tes ( id_tes INT PRIMARY KEY ) GO ============================================== hasilnya The command(s) completed successfully.

2.

FOREIGN KEY Constraint FOREIGN KEY pada satu tabel terhubung dengan CANDIDATE KEY pada tabel lainnya, yang berfungsi untuk mencegah perubahan nilai oleh user sehingga menyebabkan data menjadi tidak konsisten. Contoh : CREATE TABLE customer ( INT cust_id cust_name VARCHAR(50) )

NOT NULL NOT NULL

IDENTITY

PRIMARY KEY,

CREATE TABLE orders ( INT NOT NULL IDENTITY PRIMARY KEY, order_id cust_id INT NOT NULL REFERENCES customer(cust_id) ) --------------------------------------------------------------------------------Perintah REFERENCES pada tabel Orders diatas merupakan setting referential default (NO ACTION), sehingga perintah tsb. dapat juga ditulis sbb : CREATE TABLE orders ( INT NOT NULL IDENTITY PRIMARY KEY, order_id INT NOT NULL REFERENCES customer(cust_id) cust_id ON UPDATE NO ACTION ON DELETE NO ACTION ) --------------------------------------------------------------------------------Perintah ON UPDATE atau ON DELETE memiliki 2 pilihan, yaitu : NO ACTION dan CASCADE. Misalnya perintah diatas diubah sbb : CREATE TABLE orders ( INT NOT NULL IDENTITY PRIMARY KEY, order_id cust_id INT NOT NULL REFERENCES customer(cust_id) ON UPDATE NO ACTION ON DELETE CASCADE ) --------------------------------------------------------------------------------Maka :





Kita tidak dapat menambah cust_id baru pada tabel Orders yang tidak ada pada tabel Customer, karena dibatasi oleh klausa ON UPDATE NO ACTION. Sehingga akan menimbulkan error. Klausa ON DELETE akan mengontrol data yang akan dihapus. • NO ACTION Jika kita menghapus data langsung pada tabel Orders akan menimbulkan error. • CASCADE Data (cust_id) yang dihapus melalui tabel Customer, maka semua cust_id pada tabel Orders yang bersesuaian akan ikut terhapus.

31

3.

UNIQUE Constraint (ALTERNATE KEY) Pada saat desain tabel, dimungkinkan ada beberapa kolom/field yang memiliki nilai unik (tidak ada duplikasi data – disebut Candidate Key) yang salah satunya bisa dijadikan sebagai Primary Key. Kolom/field yang tidak dijadikan sebagai Primary Key (disebut Alternate Key) dapat dijadikan index dengan memberikan batasan UNIQUE. Dalam satu tabel hanya boleh ada 1 Primary Key, namun bisa terdiri dari > 1 Alternate Key yang dijadikan index dengan memberikan batasn UNIQUE. Contoh : CREATE TABLE customer_location INT NOT NULL, cust_id cust_location_number INT NOT NULL, CONSTARINT cust_loc_unique UNIQUE (cust_id, cust_location_number) ) GO

4.

CHECK Constraint CHECK Constraint memungkinkan kita untuk memberikan batasan tertentu pada suatu kolom/field, sehingga data yang diterima hanyalah data yang benar-benar sesuai dengan kriteria yang kita tentukan. Contoh : CREATE TABLE employee ( INT emp_id

NOT NULL PRIMARY KEY CHECK (emp_id BETWEEN 0 AND 1000), VARCHAR(30) NOT NULL CONSTRAINT no_nums emp_name CHECK (emp_name NOT LIKE '%[0-9]%'), mgr_id INT NOT NULL REFERENCES employee(emp_id), CONSTRAINT valid_mgr CHECK (mgr_id <> emp_id OR emp_id=1) ) GO 5.

DEFAULT Constraint DEFAULT akan menambahkan nilai tertentu ke dalam kolom/field yang tidak didefinisikan melalui perintah INSERT. Nilai tersebut dapat kita tentukan dengan menggunakan DEFAULT Constraint pada saat pembuatan (atau modifikasi) tabel. Contoh : CREATE TABLE ShipperID ShipperName DateInSystem ) GO

Shippers ( INT VARCHAR(30) SMALLDATETIME

IDENTITY NOT NULL PRIMARY KEY, NOT NULL, NOT NULL DEFAULT GETDATE()

LATIHAN : buatlah tabel dengan struktur sbb: 1. Tabel rekening. No 1 2

Field no_rekening kode_cabang

Tipe Data INT CHAR

NULL Tidak Ya

3 4

pin saldo

CHAR INT

Tidak Ya

Ket PK Panjang=5, FK dari tabel cabang_bank (kode_cabang), Referential Integrity Constraint  gunakan pilihan CASCADE untuk UPDATE dan DELETE panjang=6 -

32

2. Tabel rekening_nasabah No 1

Field id_nasabah

Tipe Data INT

2

no_rekening

INT

NULL Ya

Ya

Ket FK dari tabel nasabah (id_nasabah), Referential Integrity Constraint  gunakan pilihan CASCADE untuk UPDATE dan DELETE FK dari tabel rekening (no_rekening), Referential Integrity Constraint  gunakan pilihan CASCADE untuk UPDATE dan DELETE

3. tabel transaksi No 1 2

Field no_transaksi no_rekening

Tipe Data INT INT

NULL Tidak Ya

3

id_nasabah

INT

Tidak

4 5 6

jenis_transaksi tanggal jumlah

CHAR DATETIME INT

Tidak Tidak Tidak

Ket PK FK dari tabel rekening (no_rekening), Referential Integrity Constraint  gunakan pilihan CASCADE untuk UPDATE dan DELETE FK dari tabel nasabah (id_sasabah), Referential Integrity Constraint  gunakan pilihan CASCADE untuk UPDATE dan DELETE Panjang=10 DEFAULT tgl sekarang -

MODIFIKASI STRUKTUR TABEL Kita dapat merubah struktur tabel yang telah dibuat dengan menggunakan perintah ALTER TABEL. Perintah ini dapat digunakan untuk menambah kolom/field baru, menghapus kolom/field, merubah struktur kolom/field (misalnya: merubah tipe data, ukuran, dsb), menambahkan Constaint baru, menghapus Constraint, Disable/Enable Constraint dan Trigger. Syntax : ALTER TABLE table { [ ALTER COLUMN column_name { new_data_type [ ( precision [ , scale ] ) ] [ COLLATE < collation_name > ] [ NULL | NOT NULL ] | {ADD | DROP } ROWGUIDCOL } ] | ADD { [ < column_definition > ] | column_name AS computed_column_expression } [ ,...n ] | [ WITH CHECK | WITH NOCHECK ] ADD { < table_constraint > } [ ,...n ] | DROP { [ CONSTRAINT ] constraint_name | COLUMN column } [ ,...n ] | { CHECK | NOCHECK } CONSTRAINT { ALL | constraint_name [ ,...n ] } | { ENABLE | DISABLE } TRIGGER { ALL | trigger_name [ ,...n ] } } ---------------------------------------------------------------------

33

< column_definition > ::= { column_name data_type } [ [ DEFAULT constant_expression ] [ WITH VALUES ] | [ IDENTITY [ (seed , increment ) [ NOT FOR REPLICATION ] ] ] ] [ ROWGUIDCOL ] [ COLLATE < collation_name > ] [ < column_constraint > ] [ ...n ] < column_constraint > ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor ] [ ON { filegroup | DEFAULT } ] ] | [ [ FOREIGN KEY ] REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] [ NOT FOR REPLICATION ] ] | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) } < table_constraint > ::= [ CONSTRAINT constraint_name ] { [ { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] { ( column [ ,...n ] ) } [ WITH FILLFACTOR = fillfactor ] [ ON {filegroup | DEFAULT } ] ] | FOREIGN KEY [ ( column [ ,...n ] ) ] REFERENCES ref_table [ ( ref_column [ ,...n ] ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] [ NOT FOR REPLICATION ] | DEFAULT constant_expression [ FOR column ] [ WITH VALUES ] | CHECK [ NOT FOR REPLICATION ] ( search_conditions ) }

Menambah Kolom/field Syntax : ALTER TABLE nama_tabel ADD nama_kolom tipe_data [NULL|NOT NULL] Contoh : menambah kolom/field hobby, VARCHAR(20) pada tabel mhs. USE Amik ALTER TABLE mhs ADD hobby VARCHAR(20) NULL GO Menghapus kolom/field Syntax : ALTER TABLE nama_tabel DROP COLUMN nama_kolom Contoh : menghapus kolom/field alamat dan hobby pada tabel mhs. USE Amik ALTER TABLE mhs DROP COLUMN alamat,hobby GO

34

Merubah struktur kolom/field Syntax : ALTER TABLE nama_tabel ALTER COLUMN nama_kolom tipe_data_baru [NULL|NOT NULL] Contoh : merubah panjang kolom/field hobby menjadi 30 USE Amik ALTER TABLE mhs ALTER COLUMN hobby VARCHAR(30) NULL GO Menambah Constraint baru Contoh : menambah kolom/field alamat tidak boleh NULL dengan Constraint DEFAULT=’Probolinggo’ USE Amik ALTER TABLE mhs ADD alamat VARCHAR(20) NOT NULL CONSTRAINT cons_alamat DEFAULT ‘Probolinggo’ GO Menghapus Constraint Syntax : ALTER TABLE nama_tabel DROP nama_constraint Contoh : menghapus Constraint dflt_alamat (lihat contoh di atas) USE Amik ALTER TABLE mhs DROP Adflt_alamat GO Disable Constarint Syntax : ALTER TABLE nama_tabel NOCHECK CONSTRAINT nama_constraint Enable Constraint Syntax : ALTER TABLE nama_tabel CHECK CONSTRAINT nama_constraint Contoh: ALTER TABLE bagian ADD no_telp VARCHAR(15) NULL CONSTRAINT cek_telp CHECK (no_telp LIKE '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9]') INSERT INTO bagian (kode_bagian,nama_bagian,no_telp) (100,'penjualan','(335) 681-680') GO ----------------------------------------------------------------(1 row(s) affected) -----------------------------------------------------------------

VALUES

INSERT INTO bagian (kode_bagian,nama_bagian,no_telp) VALUES (200,'pembelian','335 681681') GO ----------------------------------------------------------------Msg 547, Level 16, State 1, Line 1 INSERT statement conflicted with COLUMN CHECK constraint 'cek_telp'. The conflict occurred in database 'Amik', table 'mhs', column 'no_telp'. The statement has been terminated. ----------------------------------------------------------------ALTER TABLE bagian NOCHECK CONSTRAINT cek_telp INSERT INTO bagian (kode_bagian,nama_bagian,no_telp) VALUES (200,'pembelian','335 681681') GO -----------------------------------------------------------------(1 row(s) affected) 35

MENGHAPUS TABLE Untuk menghapus satu atau beberapa view gunakan perintah DROP TABLE Syntax : DROP TABLE { table } [ ,...n ] Contoh : USE Pubs DROP TABLE stores_CA, stores_WA GO ------------------------------------------------------------ hasilnya The command(s) completed successfully.

36

PERTEMUAN 5 VIEW View pada hakekatnya merupakan suatu query untuk menampilkan data (dengan perintah SELECT) yang tersimpan dengan nama tertentu pada database. Sehingga jika kita ingin menampilkan data yang sama, cukup memanggil nama view ybs. View sering disebut dengan virtual table (tabel semu), karena view tidak menyimpan data. Data yang ditampilkan oleh view berasal dari satu atau beberapa tabel yang disebut base tables. View hanya dapat dibuat pada database tertentu, artinya base tables yang digunakan harus berasal dari database yang sama. Sebuah view dapat menampilkan s/d 1.024 kolom/field. FUNGSI VIEW • •

•

•

• •

Menyembunyikan kerumitan struktur database bagi end user. Dengan menggunakan view, berarti kita membuat satu lapis keamanan (security layer) antara tabel dan user. Sehingga kita dapat mengatur data apa yang boleh atau tidak boleh dilihat oleh user. Jika karena suatu sebab tertentu mengharuskan perubahan struktur database pada aplikasi yang kita buat, kita tidak perlu membongkar ulang aplikasi tsb. melainkan cukup dengan definisi ulang view. View dapat digunakan untuk membagi data yang ada pada suatu tabel. Misalnya kita mempunyai tabel dengan 3 kolom/field, namun hanya 2 kolom/field saja yang boleh diakses oleh user. Kita dapat membuat view untuk keperluan tsb. Kita dapat membuat index pada view (SQL Server 2000), sehingga data dapat ditampilkan lebih cepat. Kita dapat menambah (INSERT), merubah (UPDATE) dan menghapus (DELETE) data melalui view (SQL Server 2000).

MEMBUAT VIEW Berikut format dasar perintah untuk membuat view : CREATE VIEW [ < database_name > . ] [ < owner > . ] view_name [ ( column [ ,...n ] ) ] [ WITH < view_attribute > [ ,...n ] ] AS select_statement [ WITH CHECK OPTION ] -----------------------------------------------------------------< view_attribute > ::= { ENCRYPTION | SCHEMABINDING | VIEW_METADATA } Contoh : buat view untuk menampilkan data pada tabel stores (database : Pubs) dengan kriteria state=CA USE Pubs GO CREATE VIEW stores_CA AS SELECT * FROM stores WHERE state='CA' GO SELECT * FROM stores_CA GO

37

---------------------------------------------------- hasilnya stor_id ------7066 7067 7896

stor_name -----------------Barnum's News & Brews Fricative Bookshop

stor_address ------------------567 Pasadena Ave. 577 First St. 89 Madison St.

city ----------Tustin Los Gatos Fremont

state ----CA CA CA

zip ----92789 96745 90019

(3 row(s) affected) Suatu view dapat digunakan untuk membuat view lagi (nesting) dan view yang baru terbentuk dapat juga digunakan untuk membuat view lagi (nesting), dst s/d 32 level (maks).

VIEW PROPERTIES Properties view, dapat dilihat dengan menggunakan system stored procedures : •

sp_help Untuk menampilkan metadata suatu view. Contoh : menampilkan metadata dari view stores_CA USE Pubs EXEC sp_help stores_CA GO ----------------------------------------------------- hasilnya Name Owner Type Created_datetime ------------ ----------- ----------- ----------------------stores_CA dbo view 19/10/2007 15:24:52 Column_name Type TrimTrailingBlanks ------------------------------stor_id char no stor_name no

ComputedLength Prec Scale Nullable FixedLenNullInSource Collation ------------------ ---------------------------------- ---------no 4,00 no no SQL_Latin1_General_CP1_CI_AS

varchar no no

40,00 yes SQL_Latin1_General_CP1_CI_AS

stor_address varchar no no no

40,00 yes SQL_Latin1_General_CP1_CI_AS

city no

varchar no no

20,00 yes SQL_Latin1_General_CP1_CI_AS

state no

char

no yes

2,00 yes SQL_Latin1_General_CP1_CI_AS

zip no

char

no yes

5,00 yes SQL_Latin1_General_CP1_CI_AS

Identity Seed Increment Not For Replication -------------------------- ----- --------- ------------------No identity column defined. NULL NULL NULL RowGuidCol ------------------------------------------------------------No rowguidcol column defined. No constraints have been defined for this object. No foreign keys reference this table. The object does not have any indexes.

38

•

sp_helptext Menampilkan definisi (source) pada suatu view. Contoh : USE Pubs EXEC sp_helptext stores_CA GO --------------------------------------------------- hasilnya Text -----------------------------------------------------CREATE VIEW stores_CA AS SELECT * FROM stores WHERE state='CA'

•

sp_depends Menampilkan base table yang digunakan pada suatu view. Contoh : USE Pubs EXEC sp_depends stores_CA GO -------------------------------------------------- hasilnya In the current database, the specified object references the following: Name -----------dbo.stores dbo.stores dbo.stores dbo.stores dbo.stores dbo.stores

•

type ----user user user user user user

table table table table table table

updated selected column ---------- ----------- ----------------no yes city no yes state no yes zip no yes stor_id no yes stor_name no yes stor_address

information_schema.views Menampilkan metadata dari suatu view. Contoh : USE Pubs SELECT * FROM INFORMATION_SCHEME.VIEWS WHERE table_name='stores_CA' GO ---------------------------------------------------- hasilnya TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE ------------ ----------------- ----------- ----------------------- ----------- -----------Pubs dbo stores_CA CREATE VIEW stores_CA AS SELECT * FROM stores WHERE state='CA' NONE NO (1 row(s) affected)

•

sp_refreshview Jika kita membuat view dengan perintah SELECT *, kemudian menambahkan kolom/field baru pada base table nya, maka definisi view tsb. tidak akurat lagi. Untuk itu gunakan sp_refreshview untuk mengupdate definisi view tsb. Contoh : USE Pubs ALTER TABLE stores ADD status CHAR(2) NULL GO EXEC sp_depends stores_CA GO EXEC sp_refreshview stores_CA GO EXEC sp_depends stores_CA GO 39

--------------------------------------------------- hasilnya In the current database, the specified object references the following: column Name type updated selected ------------ -------------- ----------- ----------------dbo.stores user table no yes city dbo.stores user table no yes state dbo.stores user table no yes zip dbo.stores user table no yes stor_id dbo.stores user table no yes stor_name dbo.stores user table no yes stor_address In the current database, Name type ------------ ----dbo.stores user table dbo.stores user table dbo.stores user table dbo.stores user table dbo.stores user table dbo.stores user table dbo.stores user table

the specified object references the following: updated selected column ---------- ----------- ----------------no yes status no yes city no yes state no yes zip no yes stor_id no yes stor_name no yes stor_address

VIEW OPTION •

WITH ENCRYPTION Definisi (source) suatu view tidak dapat dilihat oleh user. Contoh : USE Pubs GO CREATE VIEW stores_WA WITH ENCRYPTION AS SELECT stor_id, stor_name, city FROM stores WHERE state='WA' GO EXEC sp_helptext stores_WA GO --------------------------------------------- hasilnya The object comments have been encrypted.

•

WITH SCHEMABINDING Mengikat view dengan base table, sehingga base table tidak dapat dihapus begitu saja (karena masih terkait dengan view ybs). Penulisan SCHEMABINDING harus menyertakan pemilik (owner) base table (misalnya : ‘dbo.table_name’) dan daftar kolom/field yang akan ditampilkan harus ditentukan, tidak boleh menggunakan perintah ‘SELECT *’ Contoh : USE tes GO CREATE VIEW jobs_min WITH SCHEMABINDING AS SELECT job_id, job_desc, min_lvl FROM dbo.jobs WHERE min_lvl < 100 GO DROP TABLE jobs GO ------------------------------------------------ hasilnya Msg 3729, Level 16, State 1, Line 2 Cannot DROP TABLE 'jobs' because 'jobs_min'.

it

is

being

referenced

by

object

40

•

WITH CHECK OPTION Kita juga dapat memodifikasi data melalui view, namun hanya satu base table yang dapat diupdate (kita tidak dapat mengupdate data sekaligus pada view dengan banyak base table) Contoh 1 : modifikasi data melalui view. USE tes GO SELECT * FROM jobs_min GO UPDATE jobs_min SET min_lvl=30 WHERE job_id=14 GO SELECT * FROM jobs_min GO ---------------------------------------------------- hasilnya job_id job_desc min_lvl ------ -------------------------------------------------- ------1,00 New Hire - Job not specified 10,00 12,00 Editor 25,00 13,00 Sales Representative 25,00 14,00 Designer 25,00 (4 row(s) affected) (1 row(s) affected) job_id -----1,00 12,00 13,00 14,00

job_desc -------------------------------------------------New Hire - Job not specified Editor Sales Representative Designer

min_lvl ------10,00 25,00 25,00 30,00

(4 row(s) affected) Contoh 2 : Modifikasi data melalui view dengan CHECK OPTION USE tes GO CREATE VIEW jobs_min AS SELECT job_id, job_desc, min_lvl FROM dbo.jobs WHERE min_lvl<75 WITH CHECK OPTION GO SELECT * FROM jobs_min GO UPDATE jobs_min SET min_lvl=100 WHERE job_id=14 GO --------------------------------------------------- hasilnya job_id job_desc min_lvl ------ -------------------------------------------------- ------1,00 New Hire - Job not specified 10,00 12,00 Editor 25,00 13,00 Sales Representative 25,00 14,00 Designer 30,00 (4 row(s) affected) Server: Msg 550, Level 16, State 1, Line 1 The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint. The statement has been terminated. 41

MODIFIKASI VIEW Berikut ini format dasar perintah untuk memodifikasi view : ALTER VIEW [ < database_name > . ] [ < owner > . ] view_name [ ( column [ ,...n ] ) ] [ WITH < view_attribute > [ ,...n ] ] AS select_statement [ WITH CHECK OPTION ] < view_attribute > ::= { ENCRYPTION | SCHEMABINDING | VIEW_METADATA } Pada saat memodifikasi view, definisi (source) harus ditulis ulang dan beberapa OPTION yang sudah didefinisikan juga ditulis ulang. Karena SQL Server akan menimpa (overwrite) definisi (source) view yang lama dengan definisi (source) view yang baru. Contoh : USE tes GO CREATE VIEW jobs_min WITH ENCRYPTION, SCHEMABINDING AS SELECT job_id, job_desc, min_lvl FROM dbo.jobs WHERE min_lvl<75 WITH CHECK OPTION GO ALTER VIEW jobs_min WITH ENCRYPTION, SCHEMABINDING AS SELECT job_id, job_desc, min_lvl FROM dbo.jobs WHERE min_lvl>75 WITH CHECK OPTION GO ---------------------------------------------------------- hasilnya The command(s) completed successfully.

MENGHAPUS VIEW Untuk menghapus satu atau beberapa view gunakan perintah DROP VIEW Syntax : DROP VIEW { view } [ ,...n ] Contoh : USE Pubs DROP VIEW stores_CA, stores_WA GO ------------------------------------------------------------ hasilnya The command(s) completed successfully.

42

PERTEMUAN 6 QUERY I SELECT Statement Pernyataan SELECT digunakan untuk menampilkan informasi dari database. SELECT select_list [ INTO new_table ] FROM table_source [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] The UNION operator can be used between queries Pernyataan SELECT diatas mengandung beberapa klausa antara lain :  Klausa SELECT  Klausa INTO  Klausa FROM  Klausa WHERE  Klausa GROUP BY  Klausa HAVING  Klausa ORDER BY

Klausa SELECT Syntax : SELECT [ ALL | DISTINCT ] [ TOP n [ PERCENT ] [ WITH TIES ] ] < select_list > Contoh 1 : Menampilkan semua data pada tabel shippers. USE northwind SELECT * FROM shippers GO ------------------------------------------------------------ hasilnya ShipperID CompanyName Phone ----------- ---------------------------------------- -----------------1,00 Speedy Express (503) 555-9831 2,00 United Package (503) 555-3199 3,00 Federal Shipping (503) 555-9931 (3 row(s) affected) Contoh 2 : Menampilkan beberapa kolom/field pada tabel shippers. USE northwind SELECT ShipperID, CompanyName FROM shippers GO ----------------------------------------------------------- hasilnya ShipperID CompanyName ----------- ---------------------------------------1,00 Speedy Express 2,00 United Package 3,00 Federal Shipping (3 row(s) affected) 43

Contoh 3 : Menampilkan data dengan konstanta tertentu pada tabel shippers. USE northwind SELECT 'Nama Perusahaan : ' + CompanyName + ' -- ' + Phone FROM shippers GO -------------------------------------------------- hasilnya Nama Perusahaan : Speedy Express -- (503) 555-9831 Nama Perusahaan : United Package -- (503) 555-3199 Nama Perusahaan : Federal Shipping -- (503) 555-9931 (3 row(s) affected) Contoh 4 : Menampilkan data dengan konstanta tertentu pada tabel products. USE northwind SELECT 'Harga per unit ' + productname + ' : ' + CAST(unitprice AS VARCHAR(10)) FROM Products GO ------------------------------------------------- hasilnya Harga per unit Chai : 18.00 Harga per unit Chang : 19.00 Harga per unit Aniseed Syrup : 10.00 Harga per unit Chef Anton's Cajun Seasoning : 22.00 ... dst Penjelasan masing-masing sub klausa pada klausa SELECT sbb : •

[ ALL | DISTINCT ] Klausa DISTINCT akan menampilkan data tunggal (data kembar akan ditampilkan satu kali). Klausa ALL merupakan klausa default. Contoh : USE northwind SELECT ALL title FROM employees GO SELECT DISTINCT title FROM employees GO -------------------------------------------- hasilnya title -----------------------------Sales Representative Vice President, Sales Sales Representative Sales Representative Sales Manager Sales Representative Sales Representative Inside Sales Coordinator Sales Representative (9 row(s) affected) title -----------------------------Inside Sales Coordinator Sales Manager Sales Representative Vice President, Sales (4 row(s) affected)

44

•

[ TOP n [ PERCENT ] [ WITH TIES ] ] TOP n Hanya n data pertama yang ditampilkan. n merupakan nilai integer 0 s/d 4294967295. TOP n PERCENT Hanya n persen data pertama yang ditampilkan. n merupakan nilai integer 0 s/d 100. WITH TIES Hanya dapat digunakan jika berpasangan dengan TOP n atau TOP n PERCENT dan ORDER BY. Jika baris tarakhir terdapat data yang sama dalam suatu kolom, maka data tersebut akan ditampilan semua. Contoh : USE Northwind SELECT EmployeeID, LastName, FirstName, Title FROM employees GO SELECT TOP 3 EmployeeID, LastName, FirstName, Title FROM employees GO SELECT TOP 3 WITH TIES EmployeeID, LastName, FirstName, Title FROM employees ORDER BY Title GO --------------------------------------------------- hasilnya EmployeeID ----------1,00 2,00 3,00 4,00 5,00 6,00 7,00 8,00 9,00

LastName -----------Davolio Fuller Leverling Peacock Buchanan Suyama King Callahan Dodsworth

FirstName ---------Nancy Andrew Janet Margaret Steven Michael Robert Laura Anne

Title -----------------------------Sales Representative Vice President, Sales Sales Representative Sales Representative Sales Manager Sales Representative Sales Representative Inside Sales Coordinator Sales Representative

(9 row(s) affected) EmployeeID ----------1,00 2,00 3,00

LastName FirstName Title ------------- ---------- -----------------------------Davolio Nancy Sales Representative Fuller Andrew Vice President, Sales Leverling Janet Sales Representative

(3 row(s) affected) EmployeeID ----------8,00 5,00 6,00 7,00 1,00 3,00 4,00 9,00

LastName FirstName Title ------------- ---------- -----------------------------Callahan Laura Inside Sales Coordinator Buchanan Steven Sales Manager Suyama Michael Sales Representative King Robert Sales Representative Davolio Nancy Sales Representative Leverling Janet Sales Representative Peacock Margaret Sales Representative Dodsworth Anne Sales Representative

(8 row(s) affected)

45

KOLOM ALIAS Alias berfungsi untuk merubah tampilan nama kolom/field. Alias diperlukan karena : • •

Dimungkinkan ada nama kolom/field yang sama pada saat kita menampilkan data dari banyak tabel. Sehingga kita dapat membedakan kolom/field tsb. Kadangkala kita melakukan perhitungan terhadap suatu kolom/field. SQL Server tidak akan memproses perhitungan tsb. jika kolom/field yang dihitung tidak menggunakan alias.

Syntax : nama_kolom AS nama_alias Klausa AS bersifat opsional (bisa ditulis bisa tidak). Panjang alias yang diperbolehkan s/d 128 karakter. Jika alias terdapat spasi, gunakan tanda petik.

Contoh : USE Northwind SELECT ProductName, (UnitPrice * UnitsInStock) "Total Harga" FROM Products GO -------------------------------------------- hasilnya ProductName Total Harga ---------------------------------------- --------------------Chai Rp702 Chang Rp323 Aniseed Syrup Rp130 Chef Anton's Cajun Seasoning Rp1.166 Chef Anton's Gumbo Mix Rp0 Grandma's Boysenberry Spread Rp3.000 ... dst

Klausa INTO Digunakan untuk membuat tabel baru beserta datanya. Syntax : [ INTO new_table ] Contoh : membuat tabel baru (authors_CA) + datanya, state=’CA’ USE pubs SELECT * INTO authors_CA FROM authors WHERE state='CA' GO ----------------------------------------------------- hasilnya (15 row(s) affected)

46

Klausa FROM Mendefinisikan tabel-tabel yang dipakai sebagai acuan untuk menampilkan data. Syntax : [ FROM { < table_source > } [ ,...n ] ] < table_source > ::= table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ] | view_name [ [ AS ] table_alias ] | rowset_function [ [ AS ] table_alias ] | OPENXML | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] | < joined_table > < joined_table > ::= < table_source > < join_type > < table_source > ON < search_condition > | < table_source > CROSS JOIN < table_source > | < joined_table > < join_type > ::= [ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } ] [ < join_hint > ] JOIN Contoh 1 : menampilkan data dari 2 tabel (sales dan stores). USE Pubs SELECT sales.ord_num, sales.ord_date, sales.qty, stores.stor_name FROM sales, stores WHERE sales.stor_id = stores.stor_id GO ------------------------------------------------------ hasilnya ord_num ord_date qty stor_name -------- ------------------- ------- --------------------------6871 14/09/1994 0:00:00 5,00 Eric the Read Books 722a 13/09/1994 0:00:00 3,00 Eric the Read Books A2976 24/05/1993 0:00:00 50,00 Barnum's QA7442.3 13/09/1994 0:00:00 75,00 Barnum's ... dst (21 row(s) affected) Contoh 2 : menampilkan data dari database lain. USE Northwind SELECT au_fname + ' '+ au_lname AS name FROM Pubs..Authors GO -------------------------------------------------------- hasilnya name ---------------------------Abraham Bennet Reginald Blotchet-Halls Cheryl Carson ... dst (23 row(s) affected)

47

TABLE ALIAS Syntax : nama_tabel AS nama_alias Klausa AS bersifat opsional (bisa ditulis bisa tidak). Panjang alias yang diperbolehkan s/d 128 karakter. Jika alias terdapat spasi, gunakan tanda petik. Contoh : USE Pubs SELECT s.ord_num, s.ord_date, s.qty, t.stor_name FROM sales s, stores t WHERE s.stor_id = t.stor_id GO ------------------------------------------------------ hasilnya ord_num ord_date qty stor_name -------- ------------------- ------- --------------------------6871 14/09/1994 0:00:00 5,00 Eric the Read Books 722a 13/09/1994 0:00:00 3,00 Eric the Read Books A2976 24/05/1993 0:00:00 50,00 Barnum's QA7442.3 13/09/1994 0:00:00 75,00 Barnum's ... dst (21 row(s) affected)

Klausa WHERE Digunakan untuk membatasi hasil query dengan kriteria yang diinginkan. Syntax : [ WHERE < search_condition > | < old_outer_join > ] < old_outer_join > ::= column_name { * = | = * } column_name Contoh : menampilkan lastname, firstname dan hiredate dari karyawan yang tinggal di Seattle. USE Northwind SELECT lastname, firstname, hiredate FROM Employees WHERE city = 'seattle' GO ----------------------------------------------------- hasilnya lastname firstname hiredate ------------------- ---------- -------------------------------Davolio Nancy 01/05/1992 0:00:00 Callahan Laura 05/03/1994 0:00:00 (2 row(s) affected)

Operator Pada Klausa WHERE Beberapa Operator yang sering digunakan untuk menentukan kriteria pada klausa WHERE adalah : No 1 2 3 4 5 6

Operator =, >, <, <>, != >=, <=, !>, !< NOT, AND, OR BETWEEN LIKE IN ALL, ANY, SOME

7

EXISTS

Contoh Sederhana = ‘Bob’ = ‘Jakarta’ OR = ‘Manajer’ BETWEEN 1 AND 5 LIKE ‘ROM%’ IN (“A”,”b”,”123”) {=|<>|!=|>|>=|!>|<|=|!<} {SOME|ANY} (subquery) EXISTS (subquery)

48

Contoh 1 : Daftar karyawan yang tinggal di luar kota London. USE Northwind SELECT lastname, firstname, city FROM Employees WHERE city <> 'London' GO --------------------------------------------------- hasilnya lastname firstname city -------------------- ---------- --------------Davolio Nancy Seattle Fuller Andrew Tacoma Leverling Janet Kirkland Peacock Margaret Redmond Callahan Laura Seattle (5 row(s) affected)

Contoh 2 : Daftar karyawan yang nama belakangnya dimulai dengan huruf B. USE Northwind SELECT lastname, firstname FROM Employees WHERE lastname LIKE 'B%' GO -------------------------------------------------- hasilnya lastname firstname -------------------- ---------Buchanan Steven (1 row(s) affected)

Contoh 3 : Daftar karyawan yang tidak tinggal di Seattle, Redmond atau Tacoma USE Northwind SELECT lastname, firstname, city FROM Employees WHERE city NOT IN ('Seattle','Redmond','Tacoma') GO ---------------------------------------------------- hasilnya lastname firstname city -------------------- ---------- --------------Leverling Janet Kirkland Buchanan Steven London Suyama Michael London King Robert London Dodsworth Anne London (5 row(s) affected)

Contoh 4 : Daftar karyawan yang mulai bekerja antara 1/1/1993 dan 31/12/1993. USE Northwind SELECT lastname, firstname, hiredate FROM Employees WHERE hiredate BETWEEN '1993.1.1' AND '1993.12.31' GO --------------------------------------------------- hasilnya lastname firstname hiredate ------------------- ---------- ----------------------------Peacock Margaret 03/05/1993 0:00:00 Buchanan Steven 17/10/1993 0:00:00 Suyama Michael 17/10/1993 0:00:00 (3 row(s) affected)

49

Contoh 5 : Daftar order yang diterima tanggal 4/7/1996. USE Northwind SELECT orderid, customerid, employeeid, orderdate FROM Orders WHERE CONVERT(VARCHAR(20),orderdate,102) = '1996.07.04' GO -------------------------------------------------------- hasilnya orderid customerid employeeid orderdate ---------- ---------- ----------- -------------------------------10.248,00 VINET 5,00 04/07/1996 0:00:00 (1 row(s) affected) Catatan : Format DATETIME yang digunakan pada Klausa WHERE. (yy) 1 2 3 4 5 6 7 8 -

(yyyy) 0 or 100 (*) 101 102 103 104 105 106 107 108 9 or 109 (*)

10 11 12 -

110 111 112 13 or 113 (*)

14 -

114 20 or 120 (*) 21 or 121 (*)

-

126(***)

Standard Default USA ANSI British/French German Italian Default + milliseconds USA JAPAN ISO Europe default + milliseconds ODBC canonical ODBC canonical (with milliseconds) ISO8601

-

130* 131*

Kuwaiti Kuwaiti

Input/Output** mon dd yyyy hh:miAM (or PM) mm/dd/yy yy.mm.dd dd/mm/yy dd.mm.yy dd-mm-yy dd mon yy Mon dd, yy hh:mm:ss mon dd yyyy hh:mi:ss:mmmAM (or PM) mm-dd-yy yy/mm/dd yymmdd dd mon yyyy hh:mm:ss:mmm(24h) hh:mi:ss:mmm(24h) yyyy-mm-dd hh:mi:ss(24h) yyyy-mm-dd hh:mi:ss.mmm(24h)

yyyy-mm-dd Thh:mm:ss:mmm(no spaces) dd mon yyyy hh:mi:ss:mmmAM dd/mm/yy hh:mi:ss:mmmAM

Contoh 6 : Daftar suppplier yang region nya tidak NULL. USE Northwind SELECT companyname, contactname, region FROM Suppliers WHERE region IS NOT NULL GO ------------------------------------------------- hasilnya Companyname contactname region ----------------------------------- ------------------------- -------New Orleans Cajun Delights Shelley Burke LA Grandma Kelly's Homestead Regina Murphy MI Cooperativa de Quesos 'Las Cabras' Antonio del Valle Saavedra Asturias Pavlova, Ltd. Ian Devling Victoria ... dst (9 row(s) affected)

50

PERTEMUAN 7 QUERY II SELECT Statement Syntax : SELECT statement ::= < query_expression > [ ORDER BY { order_by_expression | column_position [ ASC | DESC ] } [ ,...n ] ] [ COMPUTE { { AVG | COUNT | MAX | MIN | SUM } ( expression ) } [ ,...n ] [ BY expression [ ,...n ] ] ] [ FOR { BROWSE | XML { RAW | AUTO | EXPLICIT } [ , XMLDATA ] [ , ELEMENTS ] [ , BINARY base64 ] } ] [ OPTION ( < query_hint > [ ,...n ]) ] < query expression > ::= { < query specification > | ( < query expression > ) } [ UNION [ ALL ] < query specification | ( < query expression > ) [...n ] ] < query specification > ::= SELECT [ ALL | DISTINCT ] [ { TOP integer | TOP integer PERCENT } [ WITH TIES ] ] < select_list > [ INTO new_table ] [ FROM { < table_source > } [ ,...n ] ] [ WHERE < search_condition > ] [ GROUP BY [ ALL ] group_by_expression [ ,...n ] [ WITH { CUBE | ROLLUP } ] ] [ HAVING < search_condition > ]

FUNGSI AGREGAT Salah satu fungsi SQL adalah kemampuan untuk dapat menyajikan ringkasan data yang tersimpan di database. Dalam T-SQL fungsi tersebut dikenal dengan Fungsi Agregat (aggregate function). Berikut ini fungsi agregat yang sering dipakai: Fungsi Agregat AVG COUNT COUNT_BIG MAX MIN SUM

Keterangan Untuk mencari nilai rata-rata dalam suatu kolom/field. Untuk menghitung jumlah baris/record dalam sebuah tabel. Nilai yang dihasilkan bertipe INT. Untuk menghitung jumlah baris/record dalam sebuah tabel. Nilai yang dihasilkan bertipe BIGINT. Untuk menampilkan nilai terbesar dalam suatu kolom/field. Untuk menampilkan nilai terkecil dalam suatu kolom/field. Untuk menjumlahkan nilai pada suatu kolom/field (tipe data harus berupa integer).

51

Contoh 1 : menampilkan rata-rata stok barang, pada perusahaan Northwind USE Northwind SELECT AVG(unitsinstock) FROM Products GO ---------------------------------------------- hasilnya 40.00 (1 row(s) affected)

Contoh 2 : menampilkan jumlah karyawan USE Northwind SELECT COUNT(*) FROM Employees GO --------------------------------------------- hasilnya 9.00 (1 row(s) affected)

Contoh 3 : menampilkan barang yang termahal harganya. USE Northwind SELECT MAX(unitprice) FROM Products GO --------------------------------------------- hasilnya $263.50 (1 row(s) affected)

Contoh 4 : menampilkan karyawan yang tertua USE Northwind SELECT MIN(birthdate) FROM Employees GO ------------------------------------------- hasilnya 9/19/1937 12:00:00 AM (1 row(s) affected)

Contoh 5 : menampilkan jumlah stok barang USE Northwind SELECT SUM(unitsinstock) FROM Products GO ------------------------------------------- hasilnya 3,119.00 (1 row(s) affected)

Contoh 6 : penggunaan Klausa DISTINCT dan fungsi agregat. USE Northwind SELECT COUNT(DISTINCT title) FROM Employees GO ------------------------------------------ hasilnya 4,00 (1 row(s) affected)

52

Klausa GROUP BY Digunakan untuk mengelompokkan data kembar dalam suatu field. Jika dalam perintah SELECT menggunakan fungsi agregat, maka berfungsi untuk menghitung ringkasan masingmasing kelompok. Syntax : [ GROUP BY [ ALL ] group_by_expression [ ,...n ] [ WITH { CUBE | ROLLUP } ] ] Contoh 1 : menghitung harga rata-rata untuk masing-masing kategori produk. USE Northwind SELECT CategoryID, AVG(UnitPrice) "Harga Rata2" FROM Products GROUP BY CategoryID GO ------------------------------------------ hasilnya CategoryID Harga Rata2 ----------- --------------------1.00 $37.98 2.00 $23.06 3.00 $25.16 4.00 $28.73 5.00 $20.25 6.00 $54.01 7.00 $32.37 8.00 $20.68 (8 row(s) affected) Contoh 2 : menampilkan jumlah pelanggan yang tinggal di Spain dan Venezuela. USE Northwind SELECT country, COUNT(*) AS [number of customers] FROM Customers WHERE country IN ('Spain','Venezuela') GROUP BY country GO --------------------------------------------------- hasilnya country number of customers --------------- ------------------Spain 5.00 Venezuela 4.00 (2 row(s) affected) Penjelasan masing-masing sub pada klusa GROUP BY • ALL Klausa ALL akan menampilkan semua nilai yang dikelompokkan, meskipun tidak sesuai dengan kriteria. Contoh : menampilkan harga rata-rata untuk kategori produk 2 s/d 4. USE Northwind SELECT CategoryID, AVG(UnitPrice) Rata2 FROM Products WHERE CategoryID BETWEEN 2 AND 4 GROUP BY CategoryID GO ---------------------------------------------- hasilnya CategoryID Rata2 ----------- --------------------2.00 $23.06 3.00 $25.16 4.00 $28.73 (3 row(s) affected) --------------------------------------------------------53

USE Northwind SELECT CategoryID, AVG(UnitPrice) Rata2 FROM Products WHERE CategoryID BETWEEN 2 AND 4 GROUP BY ALL CategoryID GO ---------------------------------------------- hasilnya CategoryID Rata2 ----------- --------------------1.00 NULL 2.00 $23.06 3.00 $25.16 4.00 $28.73 5.00 NULL 6.00 NULL 7.00 NULL 8.00 NULL (8 row(s) affected) •

•

ROLLUP Klausa ROLLUP akan menampilkan ringkasan kolom/field dikelompokkan. Ringkasan ini ditandai dengan klausa NULL.

pertama

yang

CUBE Klausa CUBE akan menampilkan ringkasan semua kolom/field yang dikelompokkan. Ringkasan ini ditandai dengan klausa NULL. Contoh : USE Northwind CREATE TABLE Stok ( nama VARCHAR(10), supplier VARCHAR(10), jumlah INT ) INSERT INSERT INSERT INSERT

INTO INTO INTO INTO

Stok Stok Stok Stok

VALUES VALUES VALUES VALUES

('Item ('Item ('Item ('Item

1', 1', 2', 2',

'Sup 'Sup 'Sup 'Sup

A', B', A', B',

10) 4) 20) 8)

SELECT * FROM Stok GO SELECT nama, supplier, SUM(jumlah) Total FROM Stok GROUP BY nama, supplier WITH ROLLUP GO SELECT nama, supplier, SUM(jumlah) Total FROM Stok GROUP BY nama, supplier WITH CUBE GO -------------------------------------------------- hasilnya nama supplier jumlah ---------- ---------- ----------Item 1 Sup A 10.00 Item 1 Sup B 4.00 Item 2 Sup A 20.00 Item 2 Sup B 8.00 (4 row(s) affected) nama supplier ---------- ---------Item 1 Sup A Item 1 Sup B Item 1 NULL Item 2 Sup A Item 2 Sup B Item 2 NULL NULL NULL (7 row(s) affected)

Total ----------10.00 4.00 14.00 20.00 8.00 28.00 42.00

WITH ROLLUP

54

nama supplier ---------- ---------Item 1 Sup A Item 1 Sup B Item 1 NULL Item 2 Sup A Item 2 Sup B Item 2 NULL NULL NULL NULL Sup A NULL Sup B (9 row(s) affected)

Total ----------10.00 4.00 14.00 20.00 8.00 28.00 42.00 30.00 12.00

WITH CUBE

Klausa HAVING Klausa HAVING berguna untuk membatasai hasil query, seperti halnya klausa WHERE. Urutan evaluasinya adalah WHERE – GROUP BY – HAVING. Syntax : [ HAVING < search_condition > ] Contoh : menampilkan negara mana saja yang berlangganan > 5 kali. USE Northwind SELECT country, COUNT(*) AS [number of customers] FROM Customers GROUP BY country HAVING COUNT(*) > 5 GO ----------------------------------------------------- hasilnya country number of customers --------------- ------------------Brazil 9.00 France 11.00 Germany 11.00 UK 7.00 USA 13.00 (5 row(s) affected)

Klausa ORDER BY Klausa ORDER BY digunakan untuk mengurutkan hasil query. Pengurutan dilakukan naik dari kecil ke besar (ASC) atau turun dari besar ke kecil (DESC). Syntax : [ ORDER BY {order_by_expression [ASC | DESC]} [,...n] ] Contoh 1 : Menampilkan nama depan menaik, dan nama belakang menurun. USE Northwind SELECT lastname naik, firstname turun FROM Employees ORDER BY lastname ASC, firstname DESC GO ------------------------------------------------------- hasilnya naik turun -------------------- ---------Buchanan Steven Callahan Laura Davolio Nancy Dodsworth Anne Fuller Andrew King Robert Leverling Janet Peacock Margaret Suyama Michael (9 row(s) affected)

55

Contoh 2 : Menampilkan harga barang > 20, diurutkan menurun. USE Northwind SELECT ProductName, UnitPrice FROM Products WHERE UnitPrice > 20 ORDER BY 2 DESC GO ---------------------------------------------------- hasilnya ProductName UnitPrice ---------------------------------------- --------------------Côte de Blaye $263.50 Thüringer Rostbratwurst $123.79 Mishi Kobe Niku $97.00 ... dst (37 row(s) affected)

Klausa COMPUTE Klausa COMPUTE akan menampilkan ringkasan pada kolom/field yang dipilih. Syntax: [ COMPUTE { { AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM } ( expression ) } [ ,...n ] [ BY expression [ ,...n ] ] ] Contoh : menampilkan produk dengan CategoryID=5, SupplierID 9 dan 26 menggunakan COMPUTE SELECT SupplierID, ProductID, ProductName, UnitPrice, UnitsOnOrder FROM Northwind..Products WHERE CategoryID = 5 AND SupplierID IN ( 9 , 26 ) COMPUTE MIN(UnitPrice), AVG(UnitPrice), MAX(UnitsOnOrder) GO ------------------------------------------------------ hasilnya SupplierID ProductID ProductName UnitPrice UnitsOnOrder ----------- ---------- ---------------------- ---------- -----------9.00 22.00 Gustaf's Knäckebröd $21.00 0.00 9.00 23.00 Tunnbröd $9.00 0.00 26.00 56.00 Gnocchi di nonna Alice $38.00 10.00 26.00 57.00 Ravioli Angelo $19.50 0.00 min ========== $9.00 avg ========== $21.88 max =========== 10.00 (5 row(s) affected)

56

menggunakan COMPUTE BY SELECT SupplierID, ProductID, ProductName, UnitPrice, UnitsOnOrder FROM Northwind..Products WHERE CategoryID = 5 AND SupplierID IN ( 9 , 26 ) ORDER BY SupplierID COMPUTE MIN(UnitPrice), AVG(UnitPrice), MAX(UnitsOnOrder) BY SupplierID GO --------------------------------------------------------- hasilnya SupplierID ProductID ProductName UnitPrice UnitsOnOrder ----------- ---------- ---------------------- ---------- -----------9.00 22.00 Gustaf's Knäckebröd $21.00 0.00 9.00 23.00 Tunnbröd $9.00 0.00 min ========== $9.00 avg ========== $15.00 max =========== 0.00 SupplierID ----------26.00 26.00

ProductID ---------56.00 57.00

ProductName ---------------------Gnocchi di nonna Alice Ravioli Angelo

UnitPrice UnitsOnOrder ---------- -----------$38.00 10.00 $19.50 0.00 min ========== $19.50 avg ========== $28.75 max =========== 10.00

(6 row(s) affected)

Klausa FOR Syntax : [ FOR { BROWSE | XML { RAW | AUTO | EXPLICIT } [ , XMLDATA ] [ , ELEMENTS ] [ , BINARY BASE64 ] } ] Keterangan : • BROWSE Menampilkan data pada mode cursor (temporary). Contoh : Menampilkan nama produk dan harganya SELECT ProductName, UnitPrice FROM Northwind..Products FOR BROWSE GO 57

--------------------------------------------- hasilnya ProductName UnitPrice ---------------------------------------- --------------------Chai $18.00 Chang $19.00 Aniseed Syrup $10.00 ... dst (77 row(s) affected) •

XML Menampilkan data dengan format XML (eXtensible Markup Language). { RAW | AUTO | EXPLICIT } RAW : Menampilkan hasil query dalam format elemen XML dengan tag Contoh : SELECT TOP 1 ProductName, UnitPrice FROM Northwind..Products FOR XML RAW GO ---------------------------------------------- hasilnya XML_F52E2B61-18A1-11d1-B105-00805F49916B ----------------------------------------------------- (1 row(s) affected) AUTO

: Menampilkan hasil query dalam format nested XML. Setiap kolom dianggap sebagai suatu elemen XML.

Contoh : USE Northwind SELECT TOP 2 a.au_id, a.au_lname, t.title_id FROM authors a, titleauthor ta, titles t WHERE a.au_id = ta.au_id AND t.title_id = ta.title_id FOR XML AUTO GO ---------------------------------------------- hasilnya XML_F52E2B61-18A1-11d1-B105-00805F49916B ------------------------------------- (2 row(s) affected) EXPLICIT : Menampilkan hasil query dalam format nested XML. Setiap kolom dianggap sebagai suatu elemen XML, dengan pengaturan yang lebih fleksibel dan kompleks. User dapat menentukan kolom/field mana yang dijadikan element maupun sub elemen. Contoh : SELECT TOP 2 1 AS Tag, 0 AS Parent, a.au_id AS [authors!1!author_id], a.au_lname AS [authors!1!au_lname!element], t.title_id AS [authors!1!title_id!element] FROM authors a , titleauthor ta , titles t WHERE a.au_id = ta.au_id AND t.title_id = ta.title_id FOR XML EXPLICIT GO 58

---------------------------------------------------- hasilnya XML_F52E2B61-18A1-11d1-B105-00805F49916B ------------------------------------- Carson PC1035 MacFeather PS1372 (2 row(s) affected)

Klausa OPTION Klausa OPTION merupakan suatu tanda (hint) bagi SQL Server untuk menjalakan query untuk keseluruhan hasil query. Setiap tanda (hint) hanya dapat ditulis satu kali. Syntax : [ OPTION ( < query_hint > [ ,...n ] ) ] < query_hint > ::= { { HASH | ORDER }GROUP | { CONCAT | HASH | MERGE }UNION | { LOOP | MERGE | HASH }JOIN | FAST number_rows | FORCE ORDER | MAXDOP number | ROBUST PLAN | KEEP PLAN | KEEPFIXED PLAN | EXPAND VIEWS } Contoh : memerintahkan eksekusi 5 hasil query pertama lebih cepat. USE Pubs SELECT au_id, au_lname, au_fname FROM authors OPTION (FAST 5) GO ------------------------------------------- hasilnya au_id au_lname ----------- ---------------------------------------409-56-7008 Bennet 648-92-1872 Blotchet-Halls 238-95-7766 Carson 722-51-5454 DeFrance 712-45-1867 del Castillo ... dst

au_fname ----------------Abraham Reginald Cheryl Michel Innes

(23 row(s) affected)

59

Klausa UNION Klausa UNION menggabungkan 2 query atau lebih menjadi satu set hasil query, dengan syarat : • •

Jumlah dan urutan kolom/field harus sama untuk masing-masing query. Tipe datanya harus sesuai

Syntax : {< query specification > | ( < query expression > ) } UNION [ ALL ] < query specification | ( < query expression > ) [ UNION [ ALL ] < query specification | ( < query expression > ) [ ... n ] ] Klausa UNION akan menampilkan data tunggal (jika ada ada kembar akan ditampilkan satu kali). Sedangkan UNION ALL akan menampilkan semua data. Contoh : SELECT 'Customer' AS Type, Region FROM Northwind..Customers WHERE Region = 'OR' UNION SELECT 'Supplier', Region FROM Northwind..Suppliers WHERE Region = 'OR' GO --------------------------------------------- hasilnya Type Region -------- --------------Customer OR Supplier OR (2 row(s) affected) SELECT 'Customer' AS Type, Region FROM Northwind..Customers WHERE Region = 'OR' UNION ALL SELECT 'Supplier', Region FROM Northwind..Suppliers WHERE Region = 'OR' GO --------------------------------------------- hasilnya Type Region -------- --------------Customer OR Customer OR Customer OR Customer OR Supplier OR (5 row(s) affected)

60

PERTEMUAN 8 MODIFIKASI DATA INSERT Statement Digunakan untuk menambah data baru. Syntax : INSERT [ INTO] { table_name WITH ( < table_hint_limited > [ ...n ] ) | view_name | rowset_function_limited } {

[ ( column_list ) ] { VALUES ( { DEFAULT | NULL | expression } [ ,...n] ) | derived_table | execute_statement }

} | DEFAULT VALUES < table_hint_limited > ::= { FASTFIRSTROW | HOLDLOCK | PAGLOCK | READCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | TABLOCK | TABLOCKX | UPDLOCK } Namun secara sederhana syntax tsb. dapat ditulis sbb : INSERT INTO Table_name (column_1,column_2,..,column_n) VALUES (value_1,value_2,..,value_n) Urutan nilai yang dimasukkan harus sama dengan urutan kolom/field-nya. Klausa INTO bersifat opsional (bisa ditulis bisa tidak). Contoh 1 : menambahkan data baru pada tabel Teritories (dengan daftar kolom). USE Northwind INSERT Territories (TerritoryID,TerritoryDescription,Regionid) VALUES ('77777','Fort Lauderdale',4) GO ------------------------------------------------------ hasilnya (1 row(s) affected) Contoh 2 : menambahkan data baru pada tabel Teritories (tanpa daftar kolom). USE Northwind INSERT Territories VALUES ('88888','Miami',4) GO ------------------------------------------------------ hasilnya (1 row(s) affected) Contoh 3 : kolom yang bersifat IDENTITY, tidak perlu diisi (ShipperID = IDENTITY) SQL Server otomatis akan menambahkan nilai baru.

61

USE Northwind INSERT Shippers (companyname, phone) VALUES ('Super Fast Shipping','(503) 555-6493') GO ------------------------------------------------- hasilnya (1 row(s) affected)

Contoh 4 : Non-aktifkan otomatis menambah nilai pada kolom yang bersifat IDENTITY. USE Northwind SET IDENTITY_INSERT Shippers ON INSERT Shippers (shipperid,companyname, phone) VALUES (20,'ACME Shipping','(503) 555-8888') SET IDENTITY_INSERT Shippers OFF GO ------------------------------------------------ hasilnya (1 row(s) affected)

Contoh 5a : * Menambahkan NULL pada kolom/field yang memperbolehkan NULL. * Menambahkan DEFAULT pada kolom/field dengan nilai DEFAULT. USE Northwind INSERT Products (productname,supplierid,categoryid,quantityperunit, reorderlevel,discontinued) VALUES ('Donut',NULL,NULLx,'6 pieces',DEFAULT,DEFAULT) GO --------------------------------------------------------- hasilnya (1 row(s) affected)

Perintah diatas akan menghasilkan hasil query yang sama dengan perintah pada contoh 5b di bawah ini :

Contoh 5b : * Menambahkan NULL pada kolom/field yang memperbolehkan NULL. * Menambahkan DEFAULT pada kolom/field dengan nilai DEFAULT. USE Northwind INSERT Products (productname,quantityperunit) VALUES ('Donut','6 pieces') GO --------------------------------------------------------- hasilnya (1 row(s) affected)

Contoh 6 : menambahkan nilai DEFAULT dan NULL pada suatu tabel. USE Northwind INSERT Orders DEFAULT VALUES GO ------------------------------------------- hasilnya (1 row(s) affected)

Agar nilai DEFAULT dan NULL dapat ditambahkan, maka tabel tsb. harus : • • •

Terdapat satu kolom/field yang bersifat IDENTITY, atau Kolom/field tsb. harus memiliki nilai DEFAULT, atau Kolom/field tsb. harus memperbolehkan nilai NULL.

62

Contoh 7 : Menambahkan beberapa data dari suatu tabel dengan kriteria tertentu. USE Northwind CREATE TABLE #employees_in_wa ( lastname NVARCHAR(40), firstname NVARCHAR(20) ) INSERT #employees_in_wa SELECT lastname,firstname FROM Employees WHERE region = 'WA' SELECT * FROM #employees_in_wa GO ------------------------------------------- hasilnya lastname firstname ---------------------------------------- -------------------Davolio Nancy Fuller Andrew Leverling Janet Peacock Margaret Callahan Laura (5 row(s) affected)

DELETE Statement Digunakan untuk menghapus data Syntax : DELETE [ FROM ] { table_name WITH ( < table_hint_limited > [ ...n ] ) | view_name | rowset_function_limited } [ FROM { < table_source > } [ ,...n ] ] [ WHERE { < search_condition > | { [ CURRENT OF { { [ GLOBAL ] cursor_name } cursor_variable_name } ] } } ] [ OPTION ( < query_hint > [ ,...n ] ) ]

|

< table_source > ::= table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ] | view_name [ [ AS ] table_alias ] | rowset_function [ [ AS ] table_alias ] | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] | < joined_table > < joined_table > ::= < table_source > < join_type > < table_source > ON < search_condition > | < table_source > CROSS JOIN < table_source > | < joined_table > < join_type > ::= [ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ] [ < join_hint > ] JOIN < table_hint_limited > ::= { FASTFIRSTROW | HOLDLOCK | PAGLOCK | READCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | TABLOCK | TABLOCKX | UPDLOCK }

63

< table_hint > ::= { INDEX ( index_val [ ,...n ] ) | FASTFIRSTROW | HOLDLOCK | NOLOCK | PAGLOCK | READCOMMITTED | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | TABLOCK | TABLOCKX | UPDLOCK } < query_hint > ::= { { HASH | ORDER } GROUP | { CONCAT | HASH | MERGE } UNION | FAST number_rows | FORCE ORDER | MAXDOP | ROBUST PLAN | KEEP PLAN } Namun secara sederhana syntax tsb. dapat ditulis sbb : DELETE table_name WHERE condition Contoh : USE MASTER CREATE DATABASE mydb GO USE mydb SELECT TOP 8 * INTO suppliers FROM Northwind..Suppliers SELECT SupplierID Kode, CompanyName Nama, ContactName Kontak FROM Suppliers GO DELETE FROM Suppliers WHERE SupplierID = 2 SELECT SupplierID Kode, CompanyName Nama, ContactName Kontak FROM Suppliers GO DELETE FROM suppliers WHERE SupplierID BETWEEN 4 AND 7 SELECT SupplierID Kode, CompanyName Nama, ContactName Kontak FROM Suppliers GO -------------------------------------------- hasilnya The CREATE DATABASE process is allocating 0.63 MB on disk 'mydb'. The CREATE DATABASE process is allocating 0.49 MB on disk 'mydb_log'. (8 row(s) affected) ID ------1,00 2,00 3,00 4,00 5,00 6,00 7,00 8,00

Nama -----------------------------------Exotic Liquids New Orleans Cajun Delights Grandma Kelly's Homestead Tokyo Traders Cooperativa de Quesos 'Las Cabras' Mayumi's Pavlova, Ltd. Specialty Biscuits, Ltd.

Kontak -----------------Charlotte Cooper Shelley Burke Regina Murphy Yoshi Nagase Antonio del Valle Saavedra Mayumi Ohno Ian Devling Peter Wilson

(8 row(s) affected)

64

Kode -----1,00 3,00 4,00 5,00 6,00 7,00 8,00

Nama -------------------------------------Exotic Liquids Grandma Kelly's Homestead Tokyo Traders Cooperativa de Quesos 'Las Cabras' Mayumi's Pavlova, Ltd. Specialty Biscuits, Ltd.

Kontak ----------------Charlotte Cooper Regina Murphy Yoshi Nagase Antonio del Valle Saavedra Mayumi Ohno Ian Devling Peter Wilson

(7 row(s) affected) Kode -----1,00 3,00 8,00

Nama -------------------------------------Exotic Liquids Grandma Kelly's Homestead Specialty Biscuits, Ltd.

Kontak -------------------Charlotte Cooper Regina Murphy Peter Wilson

(3 row(s) affected)

Contoh : menghapus data dengan perintah TRUNCATE USE Northwind CREATE TABLE #shippers ( companyname NVARCHAR(20), phone NVARCHAR(20) ) INSERT #shippers SELECT companyname,phone FROM Shippers SELECT * FROM #shippers GO TRUNCATE TABLE #shippers SELECT * FROM #shippers GO ------------------------------------------------ hasilnya (4 row(s) affected) companyname -------------------Speedy Express United Package Federal Shipping ACME Shipping

phone -------------------(503) 555-9831 (503) 555-3199 (503) 555-9931 (503) 555-8888

(4 row(s) affected) companyname phone -------------------- -------------------(0 row(s) affected) Perintah TRUNCATE akan dieksekusi lebih cepat daripada DELETE, karena SQl Server langsung menghapus dari physical storage. Sedangkan DELETE akan mencatat setiap baris/row yang dihapus pada file .log (sehingga lebih lambat daripada TRUNCATE).

65

UPDATE Statement Digunakan untuk memperbarui data pada baris/row suatu tabel. Perintah ini hanya dapat memperbarui data untuk satu tabel saja. Jika diinginkan perubahan data pada banyak tabel, maka perintah ini harus ditulis lagi. Syntax : UPDATE { table_name WITH ( < table_hint_limited > [ ...n ] ) | view_name | rowset_function_limited } SET { column_name = { expression | DEFAULT | NULL } | @variable = expression | @variable = column = expression } [ ,...n ] { { [ FROM { < table_source > } [ ,...n ] ] [ WHERE < search_condition > ] } | [ WHERE CURRENT OF { { [ GLOBAL ] cursor_name } | cursor_variable_name } ] } [ OPTION ( < query_hint > [ ,...n ] ) ] < table_source > ::= table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ] | view_name [ [ AS ] table_alias ] | rowset_function [ [ AS ] table_alias ] | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] | < joined_table > < joined_table > ::= < table_source > < join_type > < table_source > ON < search_condition > | < table_source > CROSS JOIN < table_source > | < joined_table > < join_type > ::= [ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } ] [ < join_hint > ] JOIN

< table_hint_limited > ::= { FASTFIRSTROW | HOLDLOCK | PAGLOCK | READCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | TABLOCK | TABLOCKX | UPDLOCK } < table_hint > ::= { INDEX ( index_val [ ,...n ] ) | FASTFIRSTROW | HOLDLOCK | NOLOCK | PAGLOCK | READCOMMITTED | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | TABLOCK | TABLOCKX | UPDLOCK } < query_hint > ::= { { HASH | ORDER } GROUP | { CONCAT | HASH | MERGE } UNION | {LOOP | MERGE | HASH } JOIN | FAST number_rows | FORCE ORDER | MAXDOP | ROBUST PLAN | KEEP PLAN }

66

Namun secara sederhana syntax tsb. dapat ditulis : UPDATE Table_name SET column_1 = new_value, .. column_n = new_value WHERE condition Contoh 1 : Update tabel Shippers_copy, ShipperID = 2 USE mydb SELECT * INTO Shippers_copy FROM Northwind..Shippers SELECT * FROM Shippers_copy GO UPDATE shippers_copy SET CompanyName = 'AAA', Phone = '(111) 111-1111' WHERE ShipperID = '2' SELECT * FROM Shippers_copy GO ------------------------------------------------------ hasilnya ShipperID CompanyName Phone ----------- --------------------------------------- -----------------1,00 Speedy Express (503) 555-9831 2,00 United Package (503) 555-3159 3,00 Federal Shipping (503) 555-9931 (3 row(s) affected) ShipperID ----------1,00 2,00 3,00

CompanyName Phone --------------------------------------- ------------------Speedy Express (503) 555-9831 AAA (111) 111-1111 Federal Shipping (503) 555-9931

(3 row(s) affected) Contoh 2 : USE mydb SELECT * FROM Shippers_copy GO UPDATE Shippers_copy SET CompanyName = CompanyName + ' Express', Phone = '(305) 555 8888' WHERE ShipperID = 3 SELECT * FROM Shippers_copy GO ----------------------------------------------------- hasilnya ShipperID CompanyName Phone ----------- --------------------------------------- -----------------1,00 Speedy Express (503) 555-9831 2,00 AAA (111) 111-1111 3,00 Federal Shipping (503) 555-9931 (3 row(s) affected) ShipperID ----------1,00 2,00 3,00

CompanyName Phone --------------------------------------- -----------------Speedy Express (503) 555-9831 AAA (111) 111-1111 Federal Shipping Express (305) 555 8888

(3 row(s) affected)

67

Contoh 3 : SELECT title_id, qty, ord_num FROM sales WHERE title_id = 'BU1032' GO SELECT title_id , ytd_sales WHERE title_id = 'BU1032' GO

FROM titles

UPDATE titles SET ytd_sales = titles.ytd_sales + (SELECT SUM(s.qty) FROM sales s WHERE s.title_id = titles.title_id ) SELECT title_id , ytd_sales FROM titles WHERE title_id = 'BU1032' GO ----------------------------------------------------- hasilnya title_id qty ord_num -------- ------ -------------------BU1032 5,00 6871 BU1032 10,00 423LL930 (2 row(s) affected) title_id ytd_sales -------- ----------BU1032 4.095,00 1 row(s) affected) title_id ytd_sales -------- ----------BU1032 4.110,00 (1 row(s) affected)

68

PERTEMUAN 9 UJIAN TENGAH SEMESTER (UTS)

69

PERTEMUAN 10 JOIN ANTAR TABEL Operasi JOIN digunakan untuk menampilkan data yang berasal lebih dari 2 tabel. Untuk dapat menggunakan operasi JOIN ini, tabel tersebut harus terhubung dengan suatu relasi (relationship). Masing – masing tabel yang terhubung dengan suatu relasi, menggunakan kunci tamu (foreign key) dan atau kunci utama (primary key) sebagai penghubungnya Ada 3 jenis operasi JOIN, yaitu : CROSS JOIN (CARTESIAN PRODUCT), INNER JOIN, OUTER JOIN.

CROSS JOIN Menghasilkan semua kombinasi baris terdapat pada tabel - tabel yang digabungkan. Pada kenyataanya join jenis ini tidak akan pernah dipakai. Meskipun demikian, join jenis inilah yang merupakan dasar dari join antar tabel. Contoh : USE mydb CREATE TABLE A ( id_a INT NOT NULL IDENTITY PRIMARY KEY, nama VARCHAR(10)) CREATE TABLE B ( id_b INT NOT NULL IDENTITY PRIMARY KEY, hoby VARCHAR(10)) INSERT A VALUES ('andi') INSERT A VALUES ('anto') INSERT A VALUES ('affan') INSERT B VALUES ('membaca') INSERT B VALUES ('makan') GO SELECT * FROM A, B -– dapat ditulis SELECT * FROM A CROSS JOIN B GO SELECT * FROM B, A -– dapat ditulis SELECT * FROM B CROSS JOIN A ----------------------------------------------------------------------------- hasilnya id_a nama id_b hoby ----------- ---------- ----------- ---------1,00 andi 1,00 membaca 1,00 andi 2,00 makan 2,00 anto 1,00 membaca 2,00 anto 2,00 makan 3,00 affan 1,00 membaca 3,00 affan 2,00 makan (6 row(s) affected) id_b hoby ----------- ---------1,00 membaca 2,00 makan 1,00 membaca 2,00 makan 1,00 membaca 2,00 makan (6 row(s) affected)

id_a ----------1,00 1,00 2,00 2,00 3,00 3,00

nama ---------andi andi anto anto affan affan

INNER JOIN Join jenis ini hanya akan menghasilkan output berupa kombinasi baris-baris yang berpasangan saja. Kombinasi baris yang bukan pasangannya akan dieliminasi. Baris-baris yang tidak memiliki pasangan pada tabel lainnya juga tidak akan dimunculkan. Contoh 1 : menampilkan informasi produk dan nama suplier untuk tiap produk. USE Northwind SELECT ProductID, ProductName, CompanyName FROM Products INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID GO ------------------------------------------------------------------------------ hasilnya ProductID ProductName CompanyName ----------- ---------------------------------------- ----------------------------------1,00 Chai Exotic Liquids 2,00 Chang Exotic Liquids 3,00 Aniseed Syrup Exotic Liquids 4,00 Chef Anton's Cajun Seasoning New Orleans Cajun Delights 5,00 Chef Anton's Gumbo Mix New Orleans Cajun Delights ... dst (77 row(s) affected)

70

Catatan : Syntax pada contoh diatas dapat ditulis sbb : (hasilnya sama saja) USE Northwind SELECT ProductID, ProductName, CompanyName FROM Products JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID GO --------------------------------------------------------------- atau USE Northwind SELECT ProductID, ProductName, CompanyName FROM Products, Suppliers WHERE Products.SupplierID = Suppliers.SupplierID GO

Prinsip kerja INNER JOIN dapat dilihat pada gambar berikut ini.

Contoh 2 : menggunakan nama alias untuk menampilkan produk dan kategorinya. USE Northwind SELECT P.Productname, C.Categoryname FROM Products P JOIN Categories C ON P.CategoryID = C.CategoryID GO ----------------------------------------------------------------------------- hasilnya ProductName CategoryName ---------------------------------------- --------------Chai Beverages Chang Beverages Aniseed Syrup Condiments Chef Anton's Cajun Seasoning Condiments Chef Anton's Gumbo Mix Condiments ... dst (77 row(s) affected)

Contoh 3 : JOIN lebih dari 2 tabel, menampilkan informasi semua Region yang berkaitan dengan masing-masing Employee. USE Northwind SELECT E.FirstName + ' ' + E.LastName FullName, T.TerritoryDescription, R.RegionDescription FROM Employees E JOIN EmployeeTerritories ET ON E.EmployeeID = ET.EmployeeID JOIN Territories T ON ET.TerritoryID = T.TerritoryID JOIN Region R ON T.RegionID = R.RegionID GO ----------------------------------------------------------------------------- hasilnya FullName TerritoryDescription RegionDescription ---------------------- --------------------- -----------------------------------Nancy Davolio Wilton Eastern Nancy Davolio Neward Eastern Andrew Fuller Westboro Eastern Andrew Fuller Bedford Eastern ... dst (49 row(s) affected)

71

Contoh 4 : UPDATE dengan menggunakan operasi JOIN, menaikkan harga produk sebesar $5 yang disuplai oleh “Exotic Liquids” USE Northwind SELECT P.ProductID, P.UnitPrice, S.CompanyName FROM Products P JOIN Suppliers S ON P.SupplierID = S.SupplierID WHERE S.CompanyName = 'Exotic Liquids' UPDATE Products SET UnitPrice = UnitPrice + 5 FROM Products P JOIN Suppliers S ON P.SupplierID = S.SupplierID WHERE CompanyName = 'Exotic Liquids' SELECT P.ProductID, P.UnitPrice, S.CompanyName FROM Products P JOIN Suppliers S ON P.SupplierID = S.SupplierID WHERE S.CompanyName = 'Exotic Liquids' GO ----------------------------------------------------------------------------- hasilnya ProductID UnitPrice CompanyName ----------- --------------------- ---------------------------------------1.00 $18.00 Exotic Liquids 2.00 $19.00 Exotic Liquids 3.00 $10.00 Exotic Liquids (3 row(s) affected) (3 row(s) affected) ProductID UnitPrice ----------- --------------------1.00 $23.00 2.00 $24.00 3.00 $15.00 (3 row(s) affected)

CompanyName ---------------------------------------Exotic Liquids Exotic Liquids Exotic Liquids

Dengan cara yang sama, perintah DELETE juga dapat digunakan bersama dengan operasi JOIN untuk menghapus data tertentu.

SELF JOIN Operasi SELF JOIN merupakan operasi JOIN khusus yang sebenarnya sama dengan operasi INNER JOIN. Operasi ini menghubungkan satu tabel dengan dirinya sendiri. Contoh : Pada tabel Employees, terdapat kolom/field ReportsTo yang merupakan kunci tamu (foreign key) yang terhubung dengan suatu relasi ke kolom / field EmployeeID sebagai kunci utama (primary key). Sehingga untuk menampilkan informasi karyawan beserta atasannya digunakan operasi SELF JOIN. USE Northwind SELECT E1.EmployeeID NIK, E1.Firstname + ' ' + E1.LastName Karyawan, E2.FirstName + ' ' + E2.LastName Atasan FROM Employees E1 JOIN Employees E2 ON E1.ReportsTo = E2.EmployeeID GO --------------------------------------------------------------------------- hasilnya NIK Karyawan Atasan ----------- ------------------------------- ------------------------------1.00 Nancy Davolio Andrew Fuller 3.00 Janet Leverling Andrew Fuller 4.00 Margaret Peacock Andrew Fuller 5.00 Steven Buchanan Andrew Fuller 6.00 Michael Suyama Steven Buchanan 7.00 Robert King Steven Buchanan 8.00 Laura Callahan Andrew Fuller 9.00 Anne Dodsworth Steven Buchanan (8 row(s) affected)

OUTER JOIN Join jenis ini hampir sama dengan INNER JOIN, hanya saja baris yang tidak memiliki pasangan tetap akan dimunculkan. OUTER JOIN dibagi lagi menjadi : • • •

RIGHT OUTER JOIN. LEFT OUTER JOIN. FULL OUTER JOIN. 72

RIGHT OUTER JOIN Operasi Right Outer Join akan menghasilkan output berupa kombinasi baris-baris yang berpasangan DAN baris-baris pada tabel sebelah kanan yang tidak berpasangan dengan baris-baris pada tabel sebelah kiri. Output baris yang tidak perpasangan pada tabel sebelah kiri akan diberi nilai NULL. Contoh : menampilkan informasi Region, baik yang memiliki Territory ataupun tidak. USE Northwind INSERT Region VALUES (5,'Europe') INSERT Region VALUES (6,'Latin America') INSERT Region VALUES (7,'Asia') SELECT T.TerritoryID, T.TerritoryDescription, R.RegionID, R.RegionDescription FROM Territories T RIGHT OUTER JOIN Region R ON T.RegionID = R.RegionID GO ----------------------------------------------------------------------------- hasilnya (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) TerritoryID -------------01581 01730 01833 ... dst NULL NULL NULL

TerritoryDescription --------------------Westboro Bedford Georgetow NULL NULL NULL

RegionID -------------1.00 1.00 1.00 5.00 6.00 7.00

RegionDescription -----------------------------------Eastern Eastern Eastern Europe Latin America Asia

(56 row(s) affected)

Catatan : RIGHT OUTER JOIN dapat juga ditulis dengan RIGHT JOIN saja, sehingga syntax pada contoh diatas dapat juga ditulis sbb: SELECT T.TerritoryID, T.TerritoryDescription, R.RegionID, R.RegionDescription FROM Territories T RIGHT JOIN Region R ON T.RegionID = R.RegionID

Prinsip kerja RIGHT OUTER JOIN dapat dilihat sbb :

LEFT OUTER JOIN Merupakan kebalikan dari konsep RIGHT OUTER JOIN. Pada dasarnya LEFT OUTER JOIN akan menghasilkan output yang sama dengan RIGHT OUTER JOIN, jika posisi tabelnya dirubah (tabel yang semula berada disebelah kiri, diletakkan di kanan dan sebaliknya). Sehingga contoh kasus pada operasi RIGHT OUTER JOIN diatas dapat ditulis dengan operasi LEFT OUTER JOIN (dengan menukar posisi tabelnya), sbb: USE Northwind SELECT T.TerritoryID, T.TerritoryDescription, R.RegionID, R.RegionDescription FROM Territories T RIGHT OUTER JOIN Region R ON T.RegionID = R.RegionID SELECT T.TerritoryID, T.TerritoryDescription, R.RegionID, R.RegionDescription FROM Region R LEFT OUTER JOIN Territories T ON T.RegionID = R.RegionID GO

73

----------------------------------------------------------------------------- hasilnya TerritoryID TerritoryDescription RegionID RegionDescription -------------- --------------------- -------------- -----------------------------------01581 Westboro 1.00 Eastern 01730 Bedford 1.00 Eastern 01833 Georgetow 1.00 Eastern ... dst NULL NULL 5.00 Europe NULL NULL 6.00 Latin America NULL NULL 7.00 Asia (56 row(s) affected) TerritoryID TerritoryDescription -------------- --------------------01581 Westboro 01730 Bedford 01833 Georgetow ... dst NULL NULL NULL NULL NULL NULL (56 row(s) affected)

RegionID -------------1.00 1.00 1.00 5.00 6.00 7.00

RegionDescription -----------------------------------Eastern Eastern Eastern Europe Latin America Asia

Catatan : seperti halnya pada operasi RIGHT OUTER JOIN, syntax LEFT OUTER JOIN dapat juga ditulis dengan LEFT JOIN saja, sehingga syntax pada contoh diatas dapat juga ditulis sbb: SELECT T.TerritoryID, T.TerritoryDescription, R.RegionID, R.RegionDescription FROM Region R LEFT JOIN Territories T ON T.RegionID = R.RegionID

FULL OUTER JOIN Operasi FULL OUTER JOIN merupakan gabungan dari operasi LEFT OUTER JOIN dan RIGHT OUTER JOIN. Sehingga outputnya akan menghasilkan : • • •

Semua kombinasi baris-baris yang berpasangan. Baris pada tabel sebelah kiri yang tidak berpasangan dengan baris pada tabel sebelah kanan, maka pada tabel sebelah kanan akan diberi nilai NULL. Baris pada tabel sebelah kanan yang tidak berpasangan dengan baris pada tabel sebelah kiri, maka pada tabel sebelah kiri akan diberi nilai NULL

Contoh : menampilkan informasi apakah di suatu kota terdapat Supplier dan Customer, Supllier saja, Customer saja atau tidak ada Supllier maupun Customer USE Northwind SELECT S.CompanyName Nama_Supp, S.Country Kota_Supp, C.CompanyName Nama_Pelg, C.Country Kota_Pelg FROM Suppliers S FULL OUTER JOIN Customers C ON S.Country = C.Country GO ----------------------------------------------------------------------------- hasilnya Nama_Supp Kota_Supp Nama_Pelg Kota_Pelg ---------------------------- -------------- ---------------------- ------------... dst Exotic Liquids Specialty Biscuits, Ltd. ... dst

UK UK

Around the Horn Around the Horn

UK UK

NULL NULL ... dst

NULL NULL

Chop-suey Chinese Ernst Handel

Switzerland Austria

Zaanse Snoepfabriek Leka Trading ... dst

Netherlands Singapore

NULL NULL

NULL NULL

(195 row(s) affected)

Catatan : seperti halnya pada operasi RIGHT OUTER JOIN dan LEFT OUTER JOIN, Syntax FULL OUTER JOIN dapat juga ditulis dengan FULL JOIN saja, sehingga syntax pada contoh diatas dapat juga ditulis sbb: SELECT S.CompanyName Nama_Supp, S.Country Kota_Supp, C.CompanyName Nama_Pelg, C.Country Kota_Pelg FROM Suppliers S FULL JOIN Customers C ON S.Country = C.Country

74

PERTEMUAN 11 STORED PROCEDURE Stored Procedure merupakan obyek basisdata yang tersusun dari satu atau beberapa pernyataan T-SQL. Sehingga jika ingin menghasilkan output yang sama dari suatu pernyataan T-SQL, cukup memanggil nama stored procedurenya. Beberapa keuntungan penggunaan stored procedure : • Bersifat precompiled statement. Pada saat dijalankan pertama kali, akan membuat execution plan di memory dan akan digunakan lagi setiap kali menjalankan stored procedure. Sehingga mempercepat eksekusinya. Bandingkan dengan menjalankan pernyataan T-SQL secara terpisah satu per satu. Karena setiap menjalankan pernyataan T-SQL, SQL Server akan membuat satu execution plan baru. • Optimasi lalu lintas jaringan. Saat menjalankan stored procedure yang terdiri dari beberapa pernyataan T-SQL dalam suatu jaringan, maka yang dikirimkan ke server hanya satu pernyataan TSQL saja. Sehingga mengurangi beban lalu lintas jaringan. • Alasan keamanan. Pada aplikasi yang digunakan oleh banyak user (multiuser), masing-masing user memiliki hak akses yang berbeda, sehingga diperlukan pengaturan hak akses. Stored procedure dapat digunakan untuk memberikan / membatasi hak akses masing-masing user. • Modular Programming. Salah satu kriteria program yang baik adalah kemampuan maintenance-able, (dapat di perbaiki dengan mudah) yaitu dengan memecahnya menjadi beberapa modul. Modul-modul program tersebut dapat didefinisikan lewat stored procedure. • Dapat menerima parameter. Parameter merupakan nilai yang diinputkan oleh user sehingga menghasilkan output tertentu sesuai yang diinginkan. Dengan paramete,r user dapat berinteraksi dengan aplikasi yang kita buat.

JENIS STORED PROCEDURE Ada 4 • • • •

jenis stored procedure : System Stored Procedure User-Defined Stored Procedure Temporary Stored Procedure Extended Stored Procedure

SYSTEM STORED PROCEDURE Fungsi utama system stored procedure adalah sebagai sarana berinteraksi dengan system table SQL Server untuk keperluan administratif. System stored procedure secara default ikut terinstall pada saat intsalasi SQL Server. Nama system stored procedure selalu dimulai dengan awalan sp_, sehingga dapat dieksekusi dara basisdata manapun. Contoh : menampilkan informasi database yang ada di SQL Server. EXEC sp_helpdb ------------------------------------------------------------------------------ hasilnya Name db_size owner dbid created -------------- -------------- -------------- -------------- ----------master 12.19 MB sa 1,00 Aug 6 2000 model 1.13 MB sa 3,00 Aug 6 2000 msdb 13.25 MB sa 4,00 Aug 6 2000 Northwind 3.94 MB sa 6,00 Aug 6 2000 Pubs 2.13 MB sa 5,00 Aug 6 2000 Tempdb 8.50 MB sa 2,00 Dec 7 2007

Catatan : Keterangan lebih lengkap lihat SQL Server Books Online 75

USER-DEFINED STORED PROCEDURE Stored procedure jenis ini, digunakan untuk menyelesaikan permasalahan dalam aplikasi bisnis. Masing-masing permasalahan dapat didefinisikan melalui stored procedure ini, mulai dari yang sederhana sampai dengan permasalahan yang kompleks. Untuk membuat user-defined stored procedure ini, digunakan perintah CREATE PROCEDURE.

TEMPORARY STORED PROCEDURE Stored procedure ini hampir sama dengan user-defined stored procedure dalam arti bahwa stored procedure ini digunakan juga untuk menyelesaikan permasalahan dalam aplikasi bisnis. Namun tempat penyimpanannya berbeda. User-defined stored procedure akan tersimpan di media simpan (device storage / hard disk) yaitu di database dimana userdefined stored procedure tsb. dibuat. Sedangkan temporary stored procedure akan tersimpan di memory yaitu di database tempdb. Karena tersimpan di memory, sehingga ketika aplikasi kita tutup maka temporary stored procedure ini akan otomatis akan terhapus. Untuk membuat temporary stored procedure ini, digunakan perintah CREATE PROCEDURE dan namanya diawali dengan karakter #.

EXTENDED STORED PROCEDURE Extended stored procedure merupakan program DLL (Dynamic Link Library) ditulis dengan bahasa C/C++ yang digunakan untuk memperluas kemampuan SQL Server. SQL Server sendiri memiliki beberapa extended stored procedure yang tersimpan di master database dengan nama berawalan xp_. Fungsi utama extended stored procedure bawaan SQL Server adalah untuk tujuan administratif. Extended stored procedure digunakan untuk mengakses Microsoft Win32 subsystem (Win32 API) melalui Microsoft Open Data Services (ODS). (Lihat kembali pertemuan 1 tentang Arsitektur SQL Server). PERINGATAN : Meskipun kita dapat membuat sendiri extended stored procedure, namun jika kita belum menguasai tentang struktur SQL Server dengan baik, sebaiknya tidak dilakukan karena extended stored procedure yang kita buat nantinya akan dijalankan di alamat memory yang sama dengan SQL Server. Sehingga jika extended stored procedure yang kita buat terdapat error akan mengakibatkan SQL Server juga akan error.

MEMBUAT STORED PROCEDURE Syntax : CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]

Contoh 1 : menampilkan string ‘AMIK TARUNA’ USE mydb CREATE PROC proc1 AS BEGIN -- BEGIN dan END bersifat opsional PRINT 'AMIK TARUNA' END GO ----------------------------------------------------------------------------- hasilnya The command(s) completed successfully.

76

Untuk menjalankannya ada beberapa cara : • Menggunakan reserved word EXECUTE atau EXEC + nama stored procedure Contoh : EXEC proc1 • Langsung menyebutkan nama store procedure nya jika merupakan perintah awal suatu batch. Contoh 2 : menampilkan nama karyawan dan alamatnya CREATE PROC proc2 AS BEGIN SELECT FirstName + ' ' + LastName Nama, address + ', '+ country Alamat FROM Northwind..Employees END GO Proc2 GO ---------------------------------------------------------------------------- hasilnya Nama Alamat ------------------------------ --------------------------------------------Nancy Davolio 507 - 20th Ave. E. Apt. 2A, USA Andrew Fuller 908 W. Capital Way, USA ... dst (9 row(s) affected)

STORED PROCEDURE DENGAN PARAMETER Parameter digunakan untuk ‘berkomunikasi’ dengan user . Untuk Mendefinisikan parameter, diperlukan 2 s/d 4 informasi yaitu : • Nama • Tipe data Bersifat opsional, • Nilai default Digunakan sesuai tujuan stored procedure yang dibuat • Jenis Syntax : @parameter_name [AS] datatype [= default|NULL] [VARYING] [OUTPUT|OUT] Nama parameter harus diawali dengan karakter ‘@’. Tipe data sesuai dengan tipe data SQL Server. Nilai default merupakan nilai yang otomatis diproses oleh stored procedure. Ada 2 jenis parameter : • Parameter INPUT Merupakan nilai yang di-passing ke dalam stored procedure untuk diproses lebih lanjut (passed by value). Nilai tsb. tetap tidak berubah sebelum ataupun setelah diproses di dalam stored procedure. • Parameter OUTPUT Merupakan variabel yang di-passing sebagai referensi (passed by reference). Setiap perubahan nilainya menyebabkan perubahan pada stored procedure. Untuk membedakan dengan INPUT parameter, ditambahkan klausa OUTPUT pada saat definisinya.

PARAMETER INPUT Contoh 1 : menampilkan data Employees sesuai EmployeeID yang dipilih user. CREATE PROC emp_id @employeeid INT AS BEGIN SELECT EmployeeID NIK, FirstName + ' ' + LastName Nama, Country Asal FROM Northwind..Employees WHERE employeeid = @employeeid END GO emp_id 3 GO ----------------------------------------------------------------------------- hasilnya NIK Nama Asal ----------- ------------------------------- --------------3,00 Janet Leverling USA (1 row(s) affected)

77

Contoh 2 : Menampilkan Lastname yang huruf pertamanya diinputkan oleh user. USE mydb GO CREATE PROC emp_LN @xemp_LN CHAR(1) AS BEGIN SELECT EmployeeID, LastName FROM Northwind..Employees WHERE LastName LIKE '%' + @xemp_LN + '%' END GO EXEC emp_LN 'D' GO ----------------------------------------------------------------------------- hasilnya EmployeeID LastName ----------- -------------------1,00 Davolio 9,00 Dodsworth (2 row(s) affected)

Contoh 3 : Menampilkan Lastname yang huruf pertamanya diinputkan oleh user. dengan nilai default. USE mydb GO CREATE PROC emp_LN_default @xemp_LN CHAR(1) = 'B' AS BEGIN SELECT EmployeeID, LastName FROM Northwind..Employees WHERE LastName LIKE '%' + @xemp_LN + '%' END GO EXEC emp_LN_default EXEC emp_LN_default 'D' GO ----------------------------------------------------------------------------- hasilnya EmployeeID LastName ----------- -------------------5,00 Buchanan (1 row(s) affected) EmployeeID LastName ----------- -------------------1,00 Davolio 9,00 Dodsworth (2 row(s) affected)

Contoh 4 : Membuat Database Object sebagai parameter serta membuat query pada saat runtime USE mydb GO CREATE PROC db_obj @tablename NVARCHAR(256) AS BEGIN DECLARE @query NVARCHAR(1000) SET @query = 'SELECT * FROM ' + @tablename EXEC (@query) END GO EXEC db_obj 'Northwind..Region' GO ----------------------------------------------------------------------------- hasilnya RegionID RegionDescription ----------- -------------------------------------------------1,00 Eastern 2,00 Western 3,00 Northern 4,00 Southern (4 row(s) affected)

78

Contoh 5 : menambahkan data baru ke tabel Categories USE mydb GO CREATE PROC Add_Ctg @nama NVARCHAR(15), @ket NTEXT AS BEGIN INSERT INTO Northwind..Categories (CategoryName,Description) VALUES (@nama, @ket) END GO EXEC Add_Ctg 'Makanan Kering', 'Makanan yang tahan lama' GO SELECT CategoryID, CategoryName, Description FROM Northwind..Categories GO ------------------------------------------------------------------------------ hasilnya CategoryID CategoryName Description ----------- --------------- -------------------------------------------------------1,00 Beverages Soft drinks, coffees, teas, beers, and ales 2,00 Condiments Sweet and savory sauces, relishes, spreads, and seasonings 3,00 Confections Desserts, candies, and sweet breads 4,00 Dairy Products Cheeses 5,00 Grains/Cereals Breads, crackers, pasta, and cereal 6,00 Meat/Poultry Prepared meats 7,00 Produce Dried fruit and bean curd 8,00 Seafood Seaweed and fish 9,00 Makanan Kering Makanan yang tahan lama (9 row(s) affected)

PARAMETER OUTPUT Contoh : Menampilkan pelanggan dg kode = ‘WELLI’ dan nama perusahaannya. USE mydb GO CREATE PROC CustInfo @custid NCHAR(10), @contact NVARCHAR(60) OUTPUT, @company NVARCHAR(80) OUTPUT AS BEGIN SELECT @contact = contactname, @company = companyname FROM Northwind..Customers WHERE customerid = @custid END GO DECLARE @cust_id NCHAR(10), @customer_name NVARCHAR(60), @customer_company NVARCHAR(80) SET @cust_id = 'WELLI' EXEC CustInfo @cust_id, @customer_name OUTPUT, @customer_company OUTPUT SELECT @customer_name + ' - ' + @customer_company GO ------------------------------------------------------------------------------ hasilnya Paula Parente - Wellington Importadora (1 row(s) affected)

Catatan : Jika kata OUTPUT tidak dicantumkan pada saat definis stored procedure, maka parameter yang didefinisikan dianggap sebagai parameter INPUT.

79

MODIFIKASI STORED PROCEDURE Syntax : ALTER PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION }][ FOR REPLICATION ] AS sql_statement [ ...n ]

Contoh 1 : Modifikasi stored procedure pada contoh 4 (db_obj). USE mydb GO ALTER PROC db_obj @tablename NVARCHAR(256), @columname NVARCHAR(256) AS BEGIN DECLARE @query NVARCHAR(1000) SET @query = 'SELECT ' + @columname + ' FROM '+ @tablename EXEC (@query) END GO EXEC db_obj 'Northwind..Employees','EmployeeId, FirstName, LastName' GO ----------------------------------------------------------------------------- hasilnya EmployeeId FirstName LastName ----------- ---------- -------------------1,00 Nancy Davolio 2,00 Andrew Fuller 3,00 Janet Leverling ... dst (9 row(s) affected)

Contoh 2 : Modifikasi stored procedure sehingga source code –nya tidak dpt dilihat USE mydb GO ALTER PROC db_obj @tablename NVARCHAR(256), @columname NVARCHAR(256) WITH ENCRYPTION AS BEGIN DECLARE @query NVARCHAR(1000) SET @query = 'SELECT ' + @columname + ' FROM '+ @tablename EXEC (@query) END GO EXEC sp_helptext db_obj GO ------------------------------------------------------------------------------ hasilnya The object comments have been encrypted.

MENGHAPUS STORED PROCEDURE Syntax : DROP PROC|PROCEDURE <sproc name> Contoh : menghapus stored procedure proc1, proc2, proc3 USE mydb GO DROP PROC proc1, proc2, proc3 GO ----------------------------------------------------------------------------- hasilnya The command(s) completed successfully.

80

81