SERVER

MANAJEMEN DATABASE

2.1. ARSITEKTUR CLIENT/SERVER MS SQL Server adalah salah satu jenis database relasional yang mendukung aplikasi dengan arsitektur client/server (two tier) yang mana proses sebagian proses dilakukan oleh server dan sebagian lagi dilakukan oleh aplikasi sehingga dapat mengurangi lalulintas jaringan karena MS SQL Server hanya memberikan data yang diperlukan oleh client saja. 2.2. DATABASE 2.2.1. Membuat Database Database yang akan dibuat adalah aplikasi inventory, karena pada database ini sudah mencakup pekerjaan-pekerjaan dalam system database yang diaplikasikan sehari-hari. Untuk membuat database inventory ikuti langkah-langkah di bawah ini: 1. Jalankan SQL Analyzer terlebih dahulu. 2. Ketikkan baris perintah di bawah ini: CREATE DATABASE Inventory ON PRIMARY ( NAME = Inv1, FILENAME = 'c:\LatSqlServer7\Inventdat1.mdf', SIZE = 10MB, MAXSIZE = 20, FILEGROWTH = 2), ( NAME = Inv2, FILENAME = 'c:\LatSqlServer7\Inventdat2.ndf', SIZE = 10MB, MAXSIZE = 20, FILEGROWTH = 2) LOG ON ( NAME = InvLog, FILENAME = 'c:\LatSqlServer7\Inventlog.ldf', SIZE = 5MB, MAXSIZE = 10, FILEGROWTH = 1) 3. Blok semua baris perintah di atas lalu tekan tombol F5 (atau klik icon Execute Query) untuk menjalankan baris perintah di atas. 4. Klik icon Save untuk menyimpan SQL. Simpanlah SQL setiap kali anda melakukan perubahan atau penambahan SQL! 5. Tunggu sampai selesai sehingga pada panel Resuts ditampilkan: The CREATE DATABASE process is allocating 10.00 MB on disk 'Inv1'. The CREATE DATABASE process is allocating 10.00 MB on disk 'Inv2'. The CREATE DATABASE process is allocating 10.00 MB on disk 'InvLog'.

1

| Basisdata Tingkat Lanjut/[email protected]

Setelah langkah ke 5 di atas selesai maka sekarang anda telah berhasil membuat database inventory. Untuk mengaktifkan database inventory (perhatikan gambar-1.5) adalah dengan mengikuti langkahlangkah di bawah ini: 1. Klik drop down database dan pilih untuk menampilkan nama database yang baru dibuat. 2. Klik drop down database dan pilih Iventory untuk mengaktifkan database Inventory. Selain dengan pada Query Analyzer, anda juga dapat membuat database pada Enterprise Manager dengan mengikuti langkah-langkah di bawah ini: 1. Jalankan Enterprise Manager sehingga tampil lingkungan Enterprise Manager seperti gambar-1.6. Pada tampilan ini anda dapat melihat database inventory yang telah anda buat dengan Query Analyzer: a. Klik kanan mouse pada icon Inventory. b. Pilih Properties 2. Klik kanan mouse pada icon Database dan pilih New Database. 3. Ketikkan nama database, sebagai latihan ketikkan: Coba Enterprise Manager secara otomatis membuat file database Coba_data (Coba_data.MDF) dan file log Coba_log (Coba_log.LDF). Kedua file ini ditempatkan pada lokasi (lokasi ini dapat anda ganti sesuai dengan lokasi yang diinginkan) C:\MSSQL7\data. Gantilah nama file Coba_data dengan nama Coba_dat1 sebagai file data utama (Coba_Data1_Data.MDF). Selanjutnya tentukan spesifikasi file dengan mengikuti langkah-langkah di bawah ini: a. Klik kolom Initial Size (MB) dan ketikkan 10 b. Klik radiobutton In Megabytes dalam kotak File Growth dan ketikan 2. c. Klik radiobutton Restrict filegrowth dalam kotak Maximum file size & ketikan 20. 4. Membuat file secondary: a. Klik baris kedua dan ketikkan Coba_Data2. b. Klik kolom Initial Size (MB) dan ketikkan 10. c. Klik kolom File Group dan ketikkan SECONDARY. d. Klik radiobutton In Megabytes dalam kotak File Growth dan ketikkan 2. e. Klik radiobutton Restrict filegrowth dalam kotak Maximum file size & ketikan 20. 5. File Log: a. Klik tab Transaction Log. b. Klik kolom Initial Size (MB) dan ketikkan 5. Kapasitas dari file log ini 25% dari total filesize keseluruhan database. c. Klik radiobutton In Megabytes dalam kotak File Growth dan ketikan 1. d. Klik radiobutton Restrict filegrowth dalam kotak Maximum file size & ketikan 20.

2


Gambar-1.7. Properties Database

Keterangan: Database Inventory yang dibuat di atas terdiri dari 2 buah file data dan 1 file log; 2 buah file data yaitu 1 file utama yaitu Inventdat1.mdf dan 1 file secondary (opsional) yaitu Inventdat2.ndf serta 1 file log (menampung aktivitas MS SQL) yaitu Inventlog.ldf CREATE DATABASE Inventory adalah untuk membuat nama logika database yaitu Inventory. ON untuk mengarahkan bahwa database yang ke disk NAME nama logika file PRIMARY untuk menentukan nama file utama dengan perluasan .mdf FILENAME nama fisik file dan lokasi fisik file SIZE kapasitas file (nilai awal) dalam ukuran MB MAXSIZE maksimum kapasitas file dalam ukuran MB FILEGROWTH petambahan file dalam ukuran MB atau persentase dari total keseluruhan kapasitas file data. LOG ON untuk membentuk file log. 2.2.2. Menghapus Database Sebelum anda menghapus database, database yang akan dihapus tidak boleh dalam keadaan aktif. Untuk menghapus database coba pada Query Analyzer dengan mengikuti langkah-langkah di bawah

ini: 1. Ketikkan perintah: drop database coba. 2. Blok baris perintah drop database coba. 3. Tekan tombol F5.

Untuk menghapus database coba pada Enterprise Manager dengan mengikuti langkah-langkah di bawah ini: 3


1. Jalankan Enterprise Manager sehingga tampilan seperti gambar-1.6. 2. Klik kanan mouse pada icon Coba dan pilih Delete. 2.3. TABEL Tabel hampir sama dengan file database pada xBase seperti customber.dbf yang dibentuk dari beberapa field/kolom. 2.3.1. Batasan Batasan (constraint) digunakan untuk menjaga integritas dari database melalui penyaringan informasi yang dimasukkan ke dalam kolom tabel. Batasan memiliki prioritas utama di atas trigger, rule, dan default. →Sebuah kolom harus diisi data (tidak boleh null). →Untuk menguji data yang dimasukkan ke dalam suatu kolom. →Isi suatu kolom tidak boleh sama (unik). →Kunci Indeks suatu table, harus diisi data (not null) dan unique. →Kunci indeks pada tabel lain yang direlasikan ke suatu table. Suatu table yang mempunyai foreign key tidak dapat dihapus (drop) sebelum tabel lain yang direlasikan dihapus. Sebagai contoh untuk untuk menghapus tabel t_barang, anda harus menghapus tabel t_beli_detil dan t_jual_detil terlebih dahulu. 2.3.2. Membuat Tabel NOT NULL CHECK UNIQUE PIRMARY KEY FOREIGN KEY

Tabel-tabel yang diperlukan dalam aplikasi harus dibuat terlebih dahulu dan disimpan dalam satu file database. Adapun tabel-tabel yang digunakan dalam contoh aplikasi inventory adalah seperti di bawah ini: Tabel: t_customer No Nama Kolom 1 ct_kode 2 ct_nama 3 ct_alamat1 4 ct_alamat2 5 ct_alamat3 6 ct_telepon 7 ct_fax 8 ct_email

Tipe Char Char Char Char Char Char Char Char

Panjang 5 30 40 40 40 30 30 30

Keterangan Kode customer Nama customer Alamat Alamat Alamat Telepon Fax Emaial

Ketikkan perintah di bawah ini dan blok lalu tekan tombol F5 untuk menjalankannya: CREATE TABLE t_customer ( ct_kode char(5) NOT NULL, ct_nama char(30), ct_alamat1 char(40), ct_alamat2 char(40), 4


)

ct_alamat3 char(40), ct_telepon char(30), ct_fax char(30), ct_email char(30)

Tabel t_supplier No Nama Kolom 1 sp_kode 2 sp_nama 3 sp_alamat1 4 sp_alamat2 5 sp_alamat3 6 sp_telepon 7 sp_fax 8 sp_email

Tipe Char Char Char Char Char Char Char Char

Panjang 5 30 40 40 40 30 30 30

Keterangan Kode supplier Nama supplier Alamat Alamat Alamat Telepon Fax Emaial

Ketikkan perintah di bawah ini dan blok lalu tekan tombol F5 untuk menjalankannya: CREATE TABLE t_supplier ( sp_kode char(5) NOT NULL, sp_nama char(30), sp_alamat1 char(40), sp_alamat2 char(40), sp_alamat3 char(40), sp_telepon char(30), sp_fax char(30), sp_email char(30) ) Tabel t_barang No Nama Kolom Tipe 1 br_kode Char 2 br_nama Char 3 br_satuan Char 4 Br_harga Numeric 5 br_stock_lalu Numeric 6 br_stock_skrg Numeric 7 br_min_stock Numeric

Panjang 5 35 5 7 12,2 12,2 12,2

Keterangan Kode barang Nama barang Harga satuan Stock sekarang Minimum stock

Ketikkan perintah di bawah ini dan blok lalu tekan tombol F5 untuk menjalankannya: CREATE TABLE t_barang ( br_kode char(5) NOT NULL, br_nama char(35), br_satuan char(5), br_harga numeric(7), br_stock_lalu numeric(12,2), 5


)

br_stock_skrg numeric(12,2), br_min_stock numeric(4)

Tabel t_beli No Nama Kolom 1 bl_no 2 bl_no 3 sp_kode

Tipe Panjang Char 5 Datetime Char 5

Keterangan No Faktur Tanggal Kode supplier

Ketikkan perintah di bawah ini dan blok lalu tekan tombol F5 untuk menjalankannya: CREATE TABLE t_beli ( bl_no char(5) NOT NULL, bl_tgl datetime, sp_kode char(5) ) Tabel t_beli_Detil No Nama Kolom 1 bl_no 2 br_kode 3 bd_jumlah 4 bd_harga

Tipe Char Char Date Numeric

Panjang 5 5 5

Keterangan No faktur Kode barang Jumlah Harga

Ketikkan perintah di bawah ini dan blok lalu tekan tombol F5 untuk menjalankannya: CREATE TABLE t_beli_detil ( bl_no char(5) NOT NULL, br_kode char(5) NOT NULL, bd_jumlah numeric(5), bd_harga numeric(5) ) Tabel t_jual No Nama Kolom 1 jl_no 2 jl_tgl 3 ct_kode

Tipe Panjang Char 5 Datetime Char 5

Keterangan No faktur Tgl penjualan Kode customer

Ketikkan perintah di bawah ini dan blok lalu tekan tombol F5 untuk menjalankannya: CREATE TABLE t_jual ( jl_no char(5) NOT NULL, jl_tgl datetime, ct_kode char(5) )

6


Tabel t_jual_detil No Nama Kolom 1 jl_no 2 br_kode 3 jd_jumlah 4 jd_harga

Tipe Char Char Numeric Numeric

Panjang 5 5 5 5

Keterangan No faktur Kode barang Jumlah Harga

CREATE TABLE t_jual_detil ( jl_no char(5) NOT NULL, br_kode char(5) NOT NULL, jd_jumlah numeric(5), jd_harga numeric(5) ) 2.3.3. Mengubah Tabel Tabel-tabel yang telah dibuat, kemungkinan terdapat kekurangan-kekurangan atau ada yang harus diganti. Berikut ini beberapa contoh untuk mengubah sebuah table. -- Menghapus kolom ct_email pada tabel t_customer Alter table t_customer drop column ct_email -- Menambah kolom ct_email pada tabel t_customer Alter table t_customer add ct_email char(30) -- Mengubah panjang kolom ct_email pada tabel t_customer Alter table t_customer Alter column ct_email char(35) 2.3.4. Mehapus Tabel Tabel yang telah dibuat dapat anda hapus (drop). Namum sebelum table dihapus anda harus mengetahui relasi antara tabel yang akan dihapus bilamana table yang akan dihapus tersebut direferensikan (mempunyai relasi atau foreign key) ke table lain. Sebagai contoh untuk menghapus tabel t_barang, anda harus menghapus table t_beli_detil dan t_jual_detil terlebih dahulu. DROP t_beli_detil GO DROP t_jual_detil GO DROP t_barang Perintah GO pada baris perintah di atas digunakan untuk membatas baris perintah, sehingga apabila baris perintah di atas diblok lalu tekan tombol F5, pekerjaan ini sama juga dengan menjalankannya perintah baris pertama, ketiga dan ke lima. Perintah GO penting apabila dalam suatu file query (sql) dijalankan sekaligus.

7


2.4. Constraint 2.4.1. Membuat Indeks Indeks (primary key) adalah salah batasan (constraint) digunakan untuk menghindari kerangkapan data yang dimasukkan ke dalam suatu table. Setiap table harus mempunyai indeks. Kolom yang akan dijadikan sebagai indeks jangan bertipe datetime karena data datetime selalu berbeda (tidak unik). Pembuatan indeks berarti anda melakukan perubahan table dan membuat suatu constraint. Sebagai contoh buatlah indeks table t_customer berdasarkan ct_kode dengan nama indeks xPKt_customer ALTER TABLE t_customer ADD CONSTRAINT XPKt_customer PRIMARY KEY (ct_kode) Blok baris perintah di atas lalu tekan tombol F5. Selengkapnya perintah untuk membuat indeks table-table yang lain adalah sebagai berikut: ALTER TABLE t_supplier ADD CONSTRAINT XPKt_supplier

PRIMARY KEY (sp_kode)

ALTER TABLE t_barang ADD CONSTRAINT XPKt_barang

PRIMARY KEY (br_kode)

ALTER TABLE t_beli ADD CONSTRAINT XPKt_beli ALTER TABLE t_beli_detil ADD CONSTRAINT XPKt_beli_detil ALTER TABLE t_jual ADD CONSTRAINT XPKt_jual ALTER TABLE t_jual_detil ADD CONSTRAINT XPKt_jual_detil

PRIMARY KEY (bl_no) PRIMARY KEY (bl_no, br_kode) PRIMARY KEY (jl_no) PRIMARY KEY (jl_no, br_kode)

2.4.2. Membuat Relasi Relasi (foreign key) adalah menghubungkan suatu key pada suatu table ke table aktif untuk mencegah penghapusan record pada table referensi yang telah digunakan pada table lain. Misalkan kode supplier 00001 pada table t_supplier telah digunakan pada table t_beli. Apabila kode supplier 00001 dihapus pada table t_ supplier, maka akan tampil pesan error bahwa data ini tidak boleh dihapus. Sebagai contoh buatlah relasi table antara table t_ supplier dengan table t_beli berdasarkan sp_kode dengan nama indeks xFKt_supplier.

8


ALTER TABLE t_beli ADD CONSTRAINT xFKt_supplier FOREIGN KEY (sp_kode) REFERENCES t_supplier Blok baris perintah di atas lalu tekan tombol F5. Selengkapnya perintah untuk membuat foreign key table-table yang lain adalah sebagai berikut: ALTER TABLE t_beli_detil ADD CONSTRAINT xFKt_beli FOREIGN KEY (bl_no) REFERENCES t_beli ALTER TABLE t_beli_detil ADD CONSTRAINT xFKt_beli_barang FOREIGN KEY (br_kode) REFERENCES t_barang ALTER TABLE t_jual ADD CONSTRAINT xFKt_customer FOREIGN KEY (ct_kode) REFERENCES t_customer ALTER TABLE t_jual_detil ADD CONSTRAINT xFKt_jual FOREIGN KEY (jl_no) REFERENCES t_jual ALTER TABLE t_jual_detil ADD CONSTRAINT xFKt_jual_barang FOREIGN KEY (br_kode) REFERENCES t_barang 2.4.3. Check Check adalah batasan (constraint) untuk memeriksa data yang dimasukkan ke dalam suatu kolom dengan menguji suatu kondisi. Misalkan sebelum detil pembelian disimpan, periksa terlebih jumlah barang, jika jumlah barang kecil atau sama dengan nol, maka batalkan penyimpanan data. Chek sebaiknya ditempatkan pada aplikasi karena jika usernya banyak maka akan membebankan kepada server. Untuk membuat constraint check (pemberian nama check harus unik dalam suatu database.) pada table t_barang adalah dengan perintah di bawah ini: Alter table t_beli_detil add constraint Ck_JumBeli check ( bd_jumlah > 0) Alter table t_jual_detil add constraint Ck_JumJual check ( jd_jumlah > 0) 2.4.4. Default Default adalah batasan (constraint) untuk memberikan nilai default sebuah kolom apabila kolom tersebut tidak diisi. Nilai default 0 (nol) diberikan untuk kolom br_stock_lalu dan br_stock_skrg pada table t_barang. Apabila anda tidak memberikan nilai default nol pada 2 buah kolom ini, maka kolom ini akan selalu berisi null karena null + angka tidak tidak terdefinisi. 9


Selain nilai default pada table t_barang, anda juga dapat memberikan nilai default untuk kolom tanggal pada table t_beli dan t_jual. Untuk membuat constraint default (pemberian nama default harus unik dalam suatu database) pada pada ketiga table di atas adalah dengan perintah di bawah ini: Alter table t_barang add constraint Df_stock_lalu default 0 for br_stock_lalu Alter table t_barang add constraint Df_stock_skrg default 0 for br_stock_skrg Alter table t_beli add constraint Df_beli_tgl default getdate() for bl_tgl Alter table t_jual add constraint Df_jual_tgl default getdate() for jl_tgl 2.5. Rule Rule adalah batasan (constraint) sama dengan chek. Perbedaannya adalah check hanya berlaku untuk sebuah table sendangkan rule dapat digunakan untuk table-table yang memerlukanya. Sebagai contoh kita akan membuat rule untuk memeriksa jumlah barang pada pembelian dan penjualan harus > 0. Apabila anda menggunakan rule, maka hapuslah check yang sudah ada terlebih dahulu dengan perintah di bawah ini: Alter table t_beli_detil drop constraint Ck_JumBeli Alter table t_jual_detil drop constraint Ck_JumJual Untuk membuat constraint rule (pemberian nama rule harus unik dalam suatu database.) adalah dengan mengetikkan baris perintah di bawah ini: create rule rl_jumlah as @jumlah > 0 Setelah rule dibuat, maka pekerjaan selanjutnya adalah menghubungkan rule tersebut ke kolom yang menggunakannya yaitu ke kolom bd_jumlah dan jd_jumlah seperti di bawah ini: --bind rule rl_jumlah ke kolom bd_jumlah pada tabel t_beli_detil sp_bindrule 'rl_jumlah','t_beli_detil.bd_jumlah' --bind rule rl_jumlah ke kolom jd_jumlah pada tabel t_jual_detil sp_bindrule 'rl_jumlah','t_jual_detil.jd_jumlah' 2.6. Melihat Informasi Tabel Setelah anda membuat sebuah table, maka anda dapat melihat informasi yang berhungan dengan table tersebut seperti nama table, nama kolom, tipe kolom, indeks, constraint dan lain sebagainya. Untuk melihat informasi suatu table adalah dengan menjalankan sp_help yang diikuti dengan nama table yang diinginkan. Sebagai contoh untuk melihat informasi table t_customer ketikkan perintah: sp_help t_customer blok baris perintah ini lalu tekan tombol F5.

10


2.7. Memasukkan Data Memasukkan data ke dalam table sebenarnya adalah pekerjaan aplikasi. Meskipun demikian pemasukan data melalui Query Analyzer adalah untuk menguji suatu table apakah sudah berfungsi seperti apa yang diharapkan. Perintah untuk memasukkan data ke dalam table adalah dengan perintah insert. Sebagai contoh untuk memasukkan data barang ke dalam table t_barang adalah sebagai berikut: INSERT t_barang VALUES ('00001','Disket Verbatin','Box',0,0,0) Perintah di atas untuk memasukkan data ke table t_barang yang mana data yang dimasukkan sesuai dengan urutan kolom pada table. Apabila urutan data yang dimasukkan tidak sesuai dengan urutan kolom atau tidak sama dengan jumlah kolom, maka anda harus menyebutkan nama kolom seperti contoh di bawah ini: INSERT t_barang (br_nama,br_satuan,br_kode,) VALUES ('Disket Verbatin','Box', '00001') Untuk melihat hasil dari perintah di atas ketikkan perintah di bawah ini: SELECT * FROM t_barang Maka hasil dari perintah di atas adalah sebagai berikut: br_kode br_nama br_satuan br_stock_lalu br_stock_skrg br_min_stock ------- ----------------------------------- --------- -------------------------------------00001 Disket Verbatin Box .00 .00 NULL Hasil dari perintah INSERT pertama berbeda dengan hasil INSERT kedua. Pada INSERT pertama semua kolom bertipe numeric (br_stock_lalu, br_stock_skrg dan br_min_stock) bernilai 0 (nol) sedangkan pada INSERT kedua hanya kolom br_stock_lalu dan br_stock_skrg yang bernilai 0 (nol) hal ini diisi dengan default df_stock_lalu, dan df_stock_skrg. Kolom br_min_stock bernilai NULL karena tidak diisi data. 2.8. Mengubah Data Anda dapat mengubah isi dari suatu kolom baik secara keseluruhan maupun baris tertentu saja dengan perintah update. Sebagai contoh anda dapat mengubah semua baris tabel t_barang pada kolom br_stock dengan nol dengan perintah update di bawah ini: update t_barang set br_stock_lalu = 0 Contoh lain untuk mengubah kode barang 00002 pada table t_beli_detil dengan kode barang 00003: update t_beli_detil set br_kode = '00003' where br_kode = '00002' 2.9. Menghapus Data Cara menghapus data sama halnya dengan mengubah data dengan perintah delete. Perlu diperhatikan menghapus isi suatu table yang direferensikan ke table lain, anda harus menghapus baris table yang berisi kolom referensi (foreign key atau table anak) terlebih dahulu, kemudian menghapus 11


baris table yang mereferensikan (primary key atau table induk) seperti untuk menghapus kode barang 00001 pada table t_barang anda harus menghapus semua baris pada table t_beli_detil dan t_jual_detil yang berisi kode barang 00001 terlebih dahulu. Contoh lain adalah untuk menghapus satu transaksi (satu faktur) pembelian, anda harus menghapus detil penjual terlebih dahulu kemudian dilanjutkan menghapus master detil penjualan seperti di bawah ini: --menghapus detil penjualan dengan no faktur Fak01 delete t_jual_detil where jl_no = 'Fak01' --menghapus semua isi tabel penjualan delete t_jual_detil Contoh lain untuk menghapus suatu kode barang pada detil penjualan dengan kode barang 00001 dan no faktur Fak01: delete t_jual_detil where jl_no = 'Fak01' and br_kode = ‘00001’ 2.10.

Menampilkan Data

Pekerjaan yang banyak dilakukan adalah menampilkan data untuk mendapatkan sutau informasi dari tabel-tabel yang ada. 2.10.1. Select Pada beberapa contoh yang telah dibahas sebelum telah diperkenalkan perintah select untuk menampilkan data (isi tabel). Data yang akan ditampilkan tergantung dari kebutuhan dari user. Menampilkan Seluruh Data --Tampilkan semua isi tabel customer select * from t_customer --Tampilkan semua isi tabel pembelian dan supplier select * from t_beli,t_supplier Menampilkan Data Tertentu --Tampilkan nama customer yang di awali dengan A select * from t_customer where ct_nama like 'A%' --Tampilkan nama customer yang di awali dengan B select * from t_customer where ct_nama like 'B%' --Tampilkan nama customer yang huruf awalnya 'E' atau 'F' select * from t_customer where left(ct_nama,1) in ('E','F') --Tampilkan nama customer yang di awali dengan A,B atau C select * from t_customer where ct_nama like '[A-C]%' --Tampilkan nama customer yang tidak di awali dengan A,B atau select * from t_customer where ct_nama not like '[A-C]%' --atau 12


select * from t_customer where ct_nama like '[^A-C]%' --Tampilkan nama customer yang di awali dengan AB tepat 4 karakter --where line 'AB dan underscore 2 kali maka akan tampil ABCD tetapi apabila --ditambah uderscore 3 kali maka tidak akan tampil select * from t_customer where ct_nama like 'AB__' --Tampil fax customer sama degan null select * from t_customer where ct_fax is null --Tampil fax customer yang tidak null select * from t_customer where ct_fax is not null --Tampilkan barang dan urutkan (oder) berdasarkan nama dari A ke Z select * from t_barang order by br_nama --Tampilkan barang dan urutkan (oder) berdasarkan nama dari Z ke A select * from t_barang order by br_nama DESC --Tampilkan 3 record pertama dari table barang Select top 3 * from t_barang --Tampilkan stock barang terbanyak Select top 1 * from t_barang order by br_stock_skrg DESC --atau Select max( br_stock_skrg ) from t_barang --atau dengan label JUMLAH STOCK Select max( br_stock_skrg ) as 'JUMLAH STOCK' from t_barang --Tampilkan Kode Barang, Nama Barang dan Jumlah Stock select br_kode,br_nama,br_stock_skrg from t_barang --Tampilkan semua barang yang jumlah stocknya lebih besar dari 10 select * from t_barang where br_stock_skrg > 10 --Tampilkan kode dan nama barang yang huruf awal nama lebih besar atau sama dengan N select br_kode,br_nama from t_barang where left(br_nama,1) >= 'N' --tampilkan satu kali (distinct) kode supplier dalam tabel t_beli select distinct(sp_kode)from t_beli --tampilkan dalam periode tertentu select distinct(sp_kode)from t_beli where bl_tgl >= '10/01/2001' and bl_tgl <= '10/31/2001' --atau dengan between select distinct(sp_kode)from t_beli where bl_tgl between '10/01/2001' and '10/31/2001' 2.10.2. Pengelompokan

13


Seringkali informasi yang diinginkan dikelompok berdasarkan kolom-kolom terntu seperti untuk mengetahui frekuensi transaksi supplier dan customer per bulan. Untuk pengelompokan data dengan parameter GROUP BY dan HAVING sebagai parameter kondisi pengelompokan data. --Tampilkan Supplier dari Pembelian, -->error karena pada parameter group by tidak --diikutsertakan semua kolom yang ditampilkan select sp_kode,bl_no from t_beli group by sp_kode --penulisan yang benar adalah select sp_kode,bl_no from t_beli group by sp_kode,bl_no --Tampilkan berapa kali suatu Supplier malakukan transaksi select sp_kode,count(sp_kode)as frekuensi from t_beli group by sp_kode --Group by dan having (having = where) --akan error karena group by berlawanan degnan having select sp_kode,count(sp_kode) as frekuensi from t_beli group by sp_kode where sp_kode = '00001' --seharusnya select sp_kode,count(sp_kode) as frekuensi from t_beli group by sp_kode having sp_kode = '00001' 2.10.3. Penggabungan Tabel Inner Joint Adalah operator untuk menggabungkan dua buah table yang mempunyai nilai yang sama melihat dari 2 sisi, sisi kiri sama dengan sisi kanan. select t_barang.br_kode,br_nama,bd_jumlah,bd_harga,( bd_jumlah * bd_harga ) as 'JML HARGA' from t_barang inner join t_beli_detil on t_barang.br_kode = t_beli_detil.br_kode

Penulisan t_barang.br_kode dimaksudkan untuk menentukan kode barang pada table mana yang akan ditampilkan karena terdapat dua buah kode barang yaitu pada table t_barang dan t_beli_detil. Anda dapat menampilkan salah satu dari kode barang ini. Anda dapat menggunakan alias untuk memperpendek penulisan nama table sehingga baris perintah di atas dapat ditulis seperti di bawah ini: select a.br_kode,br_nama,bd_jumlah,bd_harga, ( bd_jumlah * bd_harga ) as 'JML HARGA' from t_barang a inner join t_beli_detil b on a.br_kode = b.br_kode

Penggunaan kata inner joint dapat anda ganti dengan operator sama dengan (=) sehingga baris perintah di atas dapat anda tulis seperti di bawah ini: select a.br_kode,br_nama,bd_jumlah,bd_harga,( bd_jumlah * bd_harga ) as 'JML HARGA' from t_barang a, t_beli_detil b where a.br_kode = b.br_kode 14


--Tampilkan data pembelian dengan kode supplier 00002 (joint 3 tabel)

select a.sp_kode,b.br_kode,br_nama,bd_jumlah,bd_harga,( bd_jumlah *bd_harga) as 'JML HARGA' from t_beli a,t_beli_detil b,t_barang c where a.bl_no = b.bl_no and b.br_kode = c.br_kode and a.sp_kode = '00002'

--Menampilkan jumlah transaksi yang dilakukan oleh supplier select a.sp_kode,sp_nama,sp_alamat1,count(a.sp_kode) as Frekunsi from t_beli a inner join t_supplier b on a.sp_kode = b.sp_kode group by a.sp_kode,sp_nama,sp_alamat1 Outer Joint Adalah operator untuk menggabungkan data dengan melihat satu sisi saja, kiri (left joint) atau kanan (right joint) saja. Left joint adalah operator untuk menampilkan semua data pada table pertama (left) meskipun pada table kedua (right) tidak dipenuhi kondisinya. Sebagai contoh ada tiga supplier dalam tabel t_supplier, pada transaksi pembeli hanya dua supplier melakukan transaksi. Apabila anda melakukan left joint t_supplier dengan table t_beli maka akan ditampilkan data semua supplier hanya saja satu supplier tidak ada transaksi. select a.sp_kode,bl_no from t_supplier a left outer join t_beli b on a.sp_kode = b.sp_kode --atau select a.sp_kode,bl_no from t_supplier a,t_beli b where a.sp_kode *= b.sp_kode Misalkan hasil dari perintah di atas adalah sebagai berikut: sp_kode bl_no ------- ----00001 Fak01 00001 Fak05 00002 Fak02 00002 Fak03 00002 Fak04 00003 NULL (6 row(s) affected) Apabila anda tidak melakukan tidak melakukan left joint seperti di bawah ini, maka hasilnya adalah 5 row(s) affected, karena kode supplier 00003 tidak ada pada table t_beli. select a.sp_kode,bl_no from t_supplier a,t_beli b where a.sp_kode = b.sp_kode sp_kode bl_no ------- ----15


00001 00001 00002 00002 00002

Fak01 Fak05 Fak02 Fak03 Fak04

(5 row(s) affected) Right Joint Right joint adalah operator untuk menampilkan semua data pada table kedua (right) meskipun pada table pertama (left) tidak dipenuhi kondisinya. Right joint adalah kebalikan dari Left Joint. Full Joint Adalah operator untuk menggabungkan dua buah table dan menampilkan semua baris pada table pertama dan table kedua. select a.sp_kode,bl_no from t_supplier a full join t_beli b on a.sp_kode = b.sp_kode Cross Joint Adalah operator untuk mengalikan setiap baris table pertama dengan baris table kedua. Sebagai contoh table t_supplier berisi 3 baris dan table t_beli berisi 5 baris maka hasil dari cross joint akan menghasilkan 15 ( 3 x 5 ) baris. select a.sp_kode,bl_no from t_supplier a cross join t_beli b Union Adalah operator untuk untuk menggabungkan hasil select dari dua buah table atau lebih yang mempunyai isi kolom yang sama meskipun nama kolom berbeda ke dalam satu hasil. select * from t_supplier union select * from t_customer

Nama-nama kolom dari hasil union di atas adalah sesuai dengan nama kolom pada table t_supplier. Tentunya akan memberikan informasi yang salah. Oleh karena itu anda dapat menyamakan antara kolom supplier dengan kolom customer; kolom sp_kode dan ct_kode dinamakan kolom KODE , kolom sp_nama dan ct_nama dinamakan kolom NAMA dan seterusnya seperti di bawah ini: select sp_kode as KODE, sp_nama as NAMA, sp_alamat1 as ALAMAT, sp_alamat2 as JALAN, sp_alamat3 as KOTA, sp_fax as FAX, sp_email as EMAIL from t_supplier union select ct_kode,ct_nama,ct_alamat1,ct_alamat2,ct_alamat3,ct_fax,ct_email from t_customer 2.10.4. Sub Query 16


Sub Query adalah sebuah query terdapat dalam suatu query (nested query). Suatu subquery harus di di awali dengan kurung buka dan di akhiri dengan kurung tutup yang menandakan bahwa subquery adalah sama dengan satu kolom. Dalam satu subquery hanya boleh mengembalikan 1 (satu) baris data, jika lebih dari 1 baris (row) maka akan terjadi error. --Tampilkan supplier yang telah telah melakukan transaksi dengan operator in select sp_kode,sp_nama from t_supplier where sp_kode in ( select sp_kode from t_beli ) --Tampilkan supplier yang telah telah melakukan transaksi dengan operator exists select sp_kode,sp_nama from t_supplier where exists ( select sp_kode from t_beli where t_supplier.sp_kode = t_beli.sp_kode ) --Untuk satu supplier select sp_kode,sp_nama from t_supplier where sp_kode in ( select sp_kode from t_beli where t_beli.sp_kode = '00001' ) --Tampilkan no faktur, kode barang, jumlah, harga dan nama barang dan jumlah harga --select a.bl_no,a.br_kode, ( select t_barang.br_nama from t_barang where t_barang.br_kode = a.br_kode ),a.bd_jumlah,a.bd_harga, (a.bd_jumlah*a.bd_harga) as 'JML HARGA' from t_beli_detil a group by a.bl_no,a.br_kode,a.bd_jumlah,a.bd_harga --Tampilkan no faktur, kode barang, nama supplier 15 karakter pertama, --jumlah, harga dan nama barang dan jumlah harga

select a.bl_no,a.br_kode, ( select left(t_supplier.sp_nama,15) from t_supplier where t_supplier.sp_kode in (select t_beli.sp_kode from t_beli where t_beli.bl_no = a.bl_no ) ), ( select t_barang.br_nama from t_barang where t_barang.br_kode = a.br_kode ), a.bd_jumlah,a.bd_harga,(a.bd_jumlah * a.bd_harga) as 'JML HARGA' from t_beli_detil a group by a.bl_no,a.br_kode,a.bd_jumlah,a.bd_harga

17


2.10.5. Iktisar Iktisar adalah laporan hasil ringkasan dari suatu transaksi data yang dapat menunjang dalam pengambilan suatu keputusan. Sebagai contoh anda dapat menjumlahkan harga satuan barang dan mencari harga ratanya dengan perintah select dan klausa compute sum untuk jumlah dan klausa compute avg untuk rata-rata. Untuk membuat iktisar harus diikutsertakan klausa order by (data diurutkan berdasarakan apa?) agar hasil ringkasan dikelompokkan. select br_kode,br_nama,br_harga from t_barang order by br_kode compute sum( br_harga ) compute avg( br_harga ) Contoh lain adalah untuk mencari berapa jumlah barang pada pembelian dan terdapat pada faktur mana saja barang tersebut? Iktisar ini dapat dicari dengan mengetikkan baris perintah di bawah ini. select bl_no,br_kode,bd_jumlah from t_beli_detil order by br_kode compute sum( bd_jumlah ) by br_kode compute sum( bd_jumlah ) 2.10.6. View View adalah sebuah table semu yang berisi query sehingga table view tidak dapat digunakan untuk menyimpan data. Dengan adanya table view maka anda dapat membatasi akses user terhadap suatu table. Sebagai contoh seorang user dengan level operator tidak boleh melihat harga satuan (pokok) suatu barang, maka anda dapat membuat table untuk menampilkan kolom-kolom tertentu saja. select br_kode,br_nama,br_stock_skrg from t_barang Perintah di atas akan menghasilkan kode, nama, dan stock barang. Agar anda tidak mengetikkan perintah tersebut setiap kali ingin menampilkan data tersebut di atas, anda dapat membuat view katakana nama view-nya dalah tv_barang1. create view tv_barang1 as select br_kode,br_nama,br_stock_skrg from t_barang Untuk menampilkan isi table view sama halnya dengan menampilkan isi table. Untuk menampilkan isi table view tv_barang1 adalah sebagai berikut: select * from tv_barang1 Apabila dalam sebuah table view terdapat rumus (calculation field) mis: (bd_jumlah * bd_harga ) maka rumus ini harus diberi label dengan as misalnya as nilai. Contoh lain adalah seperti melihat frekuensi transaksi suatu supplier seperti di bawah ini. 18


create view tv_supp_frek as select sp_kode,count(sp_kode)as frekuensi from t_beli group by sp_kode Anda dapat melihat informasi table untuk melihat apakah suatu table adalah table atau bukan dengan menjalankan store prosedure sp_tables. Apabila sutau table view tidak digunakan lagi, anda dapat menghapusnya seperti di bawah ini. drop view tv_supp_frek 2.11.

Skrip

Skrip (script) adalah bahasa pemrograman SQL berupa perintah-perintah yang digunakan untuk melakukan aktivitas dalam database. Perintah-perintah yang telah dibahas pada bagian sebelumnya adalah contoh dari skrip. Baris perintah yang dijalankan disebut dengan batch dan bila batch ini disimpan dengan suatu nama disebut stored procedure (sp). Skrip mempunyai tipe data yang bervariasi. Anda dapat mendefinisikan suatu variable dalam skrip yang dipakai pada skrip yang bersangkutan. Skrip berisi alur kontrol yang dibangun untuk pelaksanaan kondisi dari kode dan mengimplementasikan pengulangan. Bahasa pemrograman skrip ini mempunyai beberapa statemen yang sama dengan bahasa pemrograman tingkat tinggi lainnya. Dasar bahasa pemrograman ada 3 (tiga) yaitu: 1. Percabangan (if) 2. Pengulangan (while) 3. Variabel Ada bebera skrip dasar yang sering digunakan yang harus anda ketahui yaitu Use, Declare, Set, Select dan variable global yang diawali dengan tanda @@. Use Skrip use digunakan untuk menset database aktif. Pada bagian sebelumnya kita mengaktifkan database dengan mengklik drop down database. Contoh: use Inventory Declare Skrip declare digunakan mendeklarasikan variable yang mana nama variable diawali dengan tanda @ dan serta tentukan tipe datanya. Contoh: declare @kode_barang char(5), @Jumlah_transaksi numeric(12,2) Set Skrip set digunakan untuk memberikan nilai ke suatu variable. Contoh: 19


declare @kode_barang char(5), @nama_barang char(35), @Jumlah_transaksi numeric(12,2) set @kode_barang = '00001' set @nama_barang = ( select br_nama from t_barang where br_kode = @kode_barang ) set @Jumlah_transaksi = 25 --Trampilkan isi variable print @kode_barang print @nama_barang print @Jumlah_transaksi Select Skrip select fungsinya sama dengan set digunakan untuk memberikan nilai ke suatu variable. Perbedaanya adalah pada set pemberian nilai dari dalam tabel ke suatu variable menggunakan select, sedangkan pada select, select ditempatkan diawal atau pengganti set. Contoh: declare @kode_barang char(5), @nama_barang char(35), @Jumlah_transaksi numeric(12,2) select @kode_barang = '00001' select @nama_barang = br_nama from t_barang where br_kode = @kode_barang select @Jumlah_transaksi = 25 Variabel Global Variabel global disebut juga dengan system function adalah variable yang telah disediankan oleh MS SQL Server. Variabel global diawali dengan tanda @@. Isi dari variable global adalah berhubungan dengan system seperti koneksi aktif (@@connections), nama server (@@servername), versi MS SQL Server (@@version) dan lain sebagainya. Anda dapat melihat daftar variable global pada SQL Server Books Online dengan mengklik tab Index dan ketikkan @@. Contoh: print 'Koneksi aktif :'+ cast( @@connections as varchar(10) ) print 'Bahasa :'+ @@language print 'Maksimum Koneksi :'+ cast( @@max_connections as varchar(5) ) print 'Nama Server :'+ @@servername print 'Versi SQL Srver :'+ @@version Percabagan (If..Else…) If … Else adalah sebuah kontrol struktur pengambilan keputusan untuk melaksanakan satu atau lebih statemen pada suatu kondisi. Sintaks: IF <statemen> {ELSE kondisi2 <statemen>} 20


Contoh: declare @Jumlah_transaksi numeric(12,2) select @Jumlah_transaksi = bd_jumlah from t_beli_detil where br_kode = '00001' and bl_no = 'Fak01' if @Jumlah_transaksi > 0 print @Jumlah_transaksi else print 'Jumlah barang harus lebih besar dari nol!' Pengulangan (While) Pengulangan adalah sebuah struktur kontrol yang berfungsi untuk melaksanakan perintah-perintah suatu blok statemen secara berulang-ulang selama kondisi berlogika benar. Contoh: declare @nomor int set @nomor = 0 while ( @nomor < 5 ) begin set @nomor = @nomor + 1 print @nomor end 2.11.1. Stored Procedure Kumpulan (batch) perintah yang disimpan ke dalam database dengan suatu nama disebut stored procedure (sp). Hampir semua kode Transact-SQL yang ditulis sebagai batch untuk membuat stored procedure. Contoh: create proc sp_pengulangan as declare @nomor int set @nomor = 0 while ( @nomor < 5 ) begin set @nomor = @nomor + 1 print @nomor end --menjalankan stored procedure exec sp_pengulangan --atau sp_pengulangan --stored procedure untuk menampilkan barang create proc sp_barang as 21


select * from t_barang --jalankan stored procedure sp_barang sp_barang --stored procedure untuk carid kode barang create proc sp_cariBarang @kode varchar(5) as select * from t_barang where br_kode = @kode --jalankan sp_cariBarang sp_cariBarang '00001' --Mengubah store procedure dengan alter lalu lakukan perubahan misalnya kesalahan --mengetikkan nama field alter proc sp_cariBarang @kode varchar(5) as select * from t_barang where br_kode = @kode --Memeriksa ada atau tidaknya data yang dicari dengan exist alter proc sp_cariBarang @kode varchar(5) as if exists (select br_kode from t_barang where br_kode = @kode ) --menghasilkan nilai true ( >= 1 ) select * from t_barang where br_kode = @kode else print 'Barang yang anda cari tidak ada...!' --Cari yang tidak ada, maka akan ditampilkan pesan sp_cariBarang '00000' --Store Procedure dengan lebih dari 1 parameter untuk input barangparameter --antara parameter dipisahkan dengan koma create proc sp_inputBarang @kode char(5),@Nama Varchar(30) ,@satuan char(15), @harga numeric,@minstock int as insert into t_barang (br_kode,br_nama,br_satuan,br_harga,br_min_stock) values (@kode,@Nama,@satuan,@harga,@minstock) --Jalan sp_inputBarang sp_inputBarang '00004','Keyboard','Buah',50000,10 22


--case cara 2 (search case) untuk lebih dari 1 nilai create proc sp_infoBarang as select br_kode,br_nama, case br_stock_skrg when > 10 then 'Masih ada' else 'Hampir habis' end from t_barang --sp di atas error seharusnya seeprti di bawah ini create proc sp_infoBarang as select br_kode,br_nama, case when br_stock_skrg > 10.00 then 'Masih ada' when br_stock_skrg is null then 'NULL ' else 'Hampir habis' end from t_barang --Jalankan sp_infoBarang sp_infoBarang 2.11.2. Pesan Error Setelah sebuah database selesai dibuat, maka untuk melakukan aktivitas pada database terebut bukan lagi pada lingkungan Query Analyzer akan tetapi pada aplikasi yang dibuat dengan bahasa pemrogran seperti PowerBuilder, Delphi, Visual Basic dan lain sebagainya. Pada contoh-contoh yang telah dibahas, anda telah menampilkan pesan error dengan perintah print. Perintah ini hanya akan menampilkan pesan pada lingkungan Query Analyzer dan tidak akan sampai ke aplikasi yang menggunakan database tersebut. MS SQL Server menyediakan function RAISERROR() untuk mengirimkan pesan ke aplikasi. MS SQL Server mengelompokkan pesan error menajdi dua level, yaitu level pertama adalah pesan error yang digunakan oleh user bernilai 1 s/d 18 (yang umum digunakan adalah 16) sedangkan level kedua adalah pesan error yang digunakan oleh system bernilai 19 s/d 25. Apabila level kedua bernilai antara 20 s/d 25, maka error yang terjadi adalah error fatal dan koneksi database harus diputus. Pada saat menyampaikan pesan error ke aplikasi, juga diikuti status dan biasanya bernilai 1. Contoh: alter proc sp_cariBarang @kode varchar(5) as if exists (select br_kode from t_barang where br_kode = @kode ) select * from t_barang where br_kode = @kode else raiserror( 'Barang yang anda cari tidak ada...!',16,1) 23


2.11.3. Trigger Pada saat anda melakukan aktivitas seperti insert, update atau delete data pada suatu tabel, data tersebut tidak langsung disimpan kedalam database melainkan ditempatkan sementara dalam log. Perkerjaan ini dinamakan transaction (disingkat dengan tran). Apabila terjadi error pada saat aktivitas dilakukan maka aktivitas akan dibatalkan (rollback) sebaliknya data disimpan secara permanen (commit). Trigger adalah sejenis stored procedure yang dijalankan pada saat aktivitas insert, update dan delete dilakukan. Pada trigger anda dapat mencegah aktivitas insert, update atau delete yang dilakukan user. Pada bagian 2.4.2. (Membuat Relasi) anda dapat mencegah user untuk memasukkan kode barang ke tabel t_beli_detil, karena kode barang tersebut belum ada pada tabel t_barang atau sebaliknya user tidak boleh menghapus suatu kode barang dalam tabel t_barang karena telah digunakan dalam tabel lain. Perkerjaan ini juga dapat dilakukan dengan trigger, tetapi untuk relasi antar tabel lebih baik dan mudah dilakukan dengan relasi (foreign key). Trigger yang akan dibahas disini adalah trigger untuk tabel t_beli_detil dan t_jual_detil. Pada saat aktivitas dilakukan pada tabel t_beli_detil akan mengupdate stock barang (br_stock_skrg). Pada saat insert akan menambah stock barang, update akan mengurangi dan menambah stock barang dan pada saat delete akan mengurangi stock barang. Sendangkan pada aktivitas pada tabel t_jual_detil adalah kebalikan dari aktivitas tabel t_beli_detil. Dengan adanya trigger, maka aplikasi database yang dibuat dengan suatu bahasa pemrogram tidak perlu memikirkan proses seperti update stock barang tetapi cukup melakukan insert, update dan delete. Selain itu, jika proses update stock barang dilakukan pada aplikasi, besar kemungkinan akan terjadi kesalahan proses dan informasi. Sebagi contoh pada penjualan barang pada saat aplikasi mencek jumlah stok untuk insert data, stock cukup tetapi data belum sempat disimpan stock sudah berkurang karena ada user lain yang melakukan transaksi penjualan. Untuk membuat trigger dengan perintah create trigger dan untuk mengubah trigger dengan perintah alter tringger. Anda juga dapat membuat/mengubah trigger pada tabel yang bersangkutan di lingkungan Enterprise Manager. Sebelum data disimpan secara permanent (commint), pada aktivitas insert data akan disimpan sementara dalam tabel sementara yang dinamakan inserted; pada aktivitas update dalam tabel sementara inserted dan deleted; sedangkan pada aktivitas deleted akan disimpan dalam tabel sementara deleted. Dengan demikian anda dapat mengambil isi tabel ini seperti select * from inserted dan select * from deleted. Trigger insert pada detil pembelian CREATE TRIGGER tI_beli_detil ON t_beli_detil FOR INSERT AS declare @kdTrn char(5) declare @kdBrg char(5) declare @JmlJual Int declare @JmlStock numeric(12,2) select @kdTrn = bl_no from inserted select @kdBrg = br_kode from inserted 24


select @JmlJual = bd_jumlah from inserted begin update t_barang set br_stock_skrg = br_stock_skrg+@JmlJual where br_kode = @kdBrg commit tran end Trigger update pada detil pembelian CREATE TRIGGER tU_beli_detil ON t_beli_detil FOR UPDATE AS declare @kdTrn char(5) declare @kdBrg char(5) declare @Oldkd char(5) declare @JmlJual Int declare @OldJml Int declare @JmlStock numeric(12,2) select @kdTrn = bl_no from inserted select @kdBrg = br_kode from inserted select @OldKd = br_kode from deleted select @JmlJual = bd_jumlah from inserted select @OldJml = bd_jumlah from deleted select @JmlStock= br_stock_skrg from t_barang where br_kode = @kdBrg begin --Batalkan transaksi lama update t_barang set br_stock_skrg = br_stock_skrg - @OldJml where br_kode = @Oldkd --Simpan transaksi baru update t_barang set br_stock_skrg =br_stock_skrg + @JmlJual where br_kode = @kdBrg commit tran end Trigger delete pada detil pembelian CREATE TRIGGER tD_beli_detil ON t_beli_detil FOR DELETE AS declare @kdTrn char(5) declare @kdBrg char(5) declare @JmlJual Int declare @JmlStock numeric(12,2) select @kdTrn = bl_no from deleted select @kdBrg = br_kode from deleted select @JmlJual = bd_jumlah from deleted select @JmlStock= br_stock_skrg from t_barang where br_kode = @kdBrg 25


begin update t_barang set br_stock_skrg =br_stock_skrg - @JmlJual where br_kode = @kdBrg commit tran end

26


SERVER

Recommend Documents