TRIGGER
Definisi Trigger
Trigger adalah batch/sekumpulan perintah yang secara otomatis dikerjakan ketika terjadi penyisipan (insert), pengubahan (update) atau penghapusan data (delete) pada sebuah tabel.
Sintaks penulisan :
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ delete ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}
Latihan :
1. Buat DBTrigger
2. Buat Tabel TBNilai dengan struktur berikut :
3. Buat Trigger Insert trgInsert_Nilai
Contoh Trigger Insert pada table TBNilai :
Ketika kita mengisi data baru dengan memasukkan nilai UTS atau UAS pada tabel bernama TBNilai, maka secara otomatis NAkhir dihitung ulang dengan rumus = 40% UTS + 60 % UAS
CREATE trigger [dbo].[trgINSERT_Nilai] on [dbo].[TBNILAI]
for INSERT
as
declare @v1 as char(3)
declare @v2 as char(3)
select @v1=KdMK,@v2=NIM from inserted
update tbNilai
set NAkhir=CONVERT(INT, 0.4*UTS + 0.6*UAS)
where KdMK=@v1 and NIM=@v2
return
4. Lakukan insert pada table tersebut sbb, NAkhir jangan diisi krn akan diupdate otomatis oleh Trigger.
5. Buat Trigger Update pada TBNilai dengan contoh berikut :
Ketika kolom UTS atau UAS pada tabel bernama TBNilai dilakukan perubahan, maka secara otomatis NAkhirnya dihitung ulang dengan rumus = 40% UTS + 60 % UAS
CREATE trigger trgNilai on TBNilai for update
as
declare @v1 as char(3)
declare @v2 as char(3)
select @v1=KdMK,@v2=NIM from inserted
if update(UTS) or update(UAS) begin
update TBNilai
set NAkhir=0.4*UTS + 0.6*UAS
where KdMK=@v1 and NIM=@v2
end
return
6. Untuk menguji TrgNilai tadi (Trigger untuk update pada TBNILAI), buatlah sintaks SQL berikut :
Pembahasan di atas, kita telah melakukan percobaan dengan membuat Trigger untuk Insert dan Update. Kedua trigger dibuat pada table TBNilai. Trigger tersebut akan mengupdate NAkhir pada tabel yang sama (dirinya sendiri), seperti tampak pada ilustrasi berikut :
TRIGGER UNTUK MENCATAT PERUBAHAN
LATIHAN :
1. Buat table TBPegawai sbb :
2. Buat tabel TBLog sbb :
3. Buat Trigger Insert pada TBPegawai sbb :
4. Pengujian TRInsert_Pegawai
5. Buat Trigger untuk Update pada TBPegawai sbb :
6. Buat Trigger untuk Delete pada TBPegawai :
7.
Thursday, January 20, 2011
Transaction
TRANSACTION
1. Apa itu transaction?
Sebuah transaksi adalah sekelompok perintah yang harus dijalankan, hasilnya berhasil atau gagal untuk semua perintah tersebut
2. Apa pentingnya transaction?
Lihat contoh berikut : missal diperlukan perubahan stok setiap kali ada penjualan 1 item barang
Update Inventory
Set qty = qty – 1
Where item_id=2012
Insert Sales_detail (item_id, inv_id, datesold, qty)
Values(2012, 308, current_timestamp, 1)
BEGIN TRAN
Update Inventory
Set qty = qty – 1
Where item_id=2012
Insert Sales_detail (item_id, inv_id, datesold, qty)
Values(2012, 308, current_timestamp, 1)
COMMIT TRAN
LATIHAN 1:
--LIHAT DATA SEBELUM DIUPDATE
select * from FB
BEGIN TRAN
--LAKUKAN UPDATE
update FB
set tempo=200 where KODEPELANGGAN='S-00002'
update FB
set tempo=300 where KODEPELANGGAN='S-00003'
--LIHAT DATA SESUDAH DIUPDATE
select * from FB
--LAKUKAN ROLLBACK
ROLLBACK TRAN
--LIHAT DATA SESUDAH ROLLBACK
select * from FB
HASILNYA :
LATIHAN 2 :
--LIHAT DATA SEBELUM DIUPDATE
select * from FB
BEGIN TRAN
--LAKUKAN UPDATE
update FB
set tempo=200 where KODEPELANGGAN='S-00002'
update FB
set tempo=300 where KODEPELANGGAN='S-00003'
--LIHAT DATA SESUDAH DIUPDATE
select * from FB
--LAKUKAN COMMIT
COMMIT
--LIHAT DATA SESUDAH COMMIT
select * from FB
HASILNYA :
LATIHAN 3 :
select * from fb
BEGIN TRAN
insert into fb(NoFJ,KodePelanggan,tglFJ,totalfaktur)
VALUES ('FB-3','S-00002','2007-11-06 12:12:12','10000')
update Supplier
set Piutang = Piutang+10000 where KODEPELANGGAN ='S-00002'
select * from fb
select * from supplier
COMMIT
HASILNYA
1. Apa yang dihasilkan jika perintah ini dijalankan
-- Create table to work with
CREATE TABLE MyTranTest
(
OrderID INT PRIMARY KEY IDENTITY
)
select * from mytrantest ---------------------(A)
BEGIN TRAN TranStart
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(B)
SAVE TRAN FirstPoint
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(C)
ROLLBACK TRAN FirstPoint
select * from mytrantest ---------------------(D)
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(E)
SAVE TRAN SecondPoint
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(F)
ROLLBACK TRAN SecondPoint
select * from mytrantest
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(G)
COMMIT TRAN TranStart
SELECT TOP 3 OrderID
FROM MyTranTest
ORDER BY OrderID ASC ---------------------(H)
1. Apa itu transaction?
Sebuah transaksi adalah sekelompok perintah yang harus dijalankan, hasilnya berhasil atau gagal untuk semua perintah tersebut
2. Apa pentingnya transaction?
Lihat contoh berikut : missal diperlukan perubahan stok setiap kali ada penjualan 1 item barang
Update Inventory
Set qty = qty – 1
Where item_id=2012
Insert Sales_detail (item_id, inv_id, datesold, qty)
Values(2012, 308, current_timestamp, 1)
BEGIN TRAN
Update Inventory
Set qty = qty – 1
Where item_id=2012
Insert Sales_detail (item_id, inv_id, datesold, qty)
Values(2012, 308, current_timestamp, 1)
COMMIT TRAN
LATIHAN 1:
--LIHAT DATA SEBELUM DIUPDATE
select * from FB
BEGIN TRAN
--LAKUKAN UPDATE
update FB
set tempo=200 where KODEPELANGGAN='S-00002'
update FB
set tempo=300 where KODEPELANGGAN='S-00003'
--LIHAT DATA SESUDAH DIUPDATE
select * from FB
--LAKUKAN ROLLBACK
ROLLBACK TRAN
--LIHAT DATA SESUDAH ROLLBACK
select * from FB
HASILNYA :
LATIHAN 2 :
--LIHAT DATA SEBELUM DIUPDATE
select * from FB
BEGIN TRAN
--LAKUKAN UPDATE
update FB
set tempo=200 where KODEPELANGGAN='S-00002'
update FB
set tempo=300 where KODEPELANGGAN='S-00003'
--LIHAT DATA SESUDAH DIUPDATE
select * from FB
--LAKUKAN COMMIT
COMMIT
--LIHAT DATA SESUDAH COMMIT
select * from FB
HASILNYA :
LATIHAN 3 :
select * from fb
BEGIN TRAN
insert into fb(NoFJ,KodePelanggan,tglFJ,totalfaktur)
VALUES ('FB-3','S-00002','2007-11-06 12:12:12','10000')
update Supplier
set Piutang = Piutang+10000 where KODEPELANGGAN ='S-00002'
select * from fb
select * from supplier
COMMIT
HASILNYA
1. Apa yang dihasilkan jika perintah ini dijalankan
-- Create table to work with
CREATE TABLE MyTranTest
(
OrderID INT PRIMARY KEY IDENTITY
)
select * from mytrantest ---------------------(A)
BEGIN TRAN TranStart
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(B)
SAVE TRAN FirstPoint
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(C)
ROLLBACK TRAN FirstPoint
select * from mytrantest ---------------------(D)
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(E)
SAVE TRAN SecondPoint
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(F)
ROLLBACK TRAN SecondPoint
select * from mytrantest
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(G)
COMMIT TRAN TranStart
SELECT TOP 3 OrderID
FROM MyTranTest
ORDER BY OrderID ASC ---------------------(H)
Cursor
CURSOR
Apa itu CURSOR?
Cursor adalah suatu perintah pemrograman yang mengijinkan setiap baris (record) diproses secara terpisah terhadap record lainnya. Cursor sangat bermanfaat jika ada suatu proses yang rumit yang ingin dilakukan terhadap suatu record. Proses tersebut bisa dilakukan secara satu per satu, yatiu lewat cursor
Algoritma CURSOR ?
1. Mendeklarasikan Cursor
2. Membuka Cursor
3. Memproses Cursor
4. Menutup Cursor
5. Mendealokasikan Cursor
Penggunaan CURSOR yang sederhana
--1 Mendeklarasikan Cursor
DECLARE CsrMsiswa CURSOR
FOR Select * from Msiswa Where NIK>=2 and NIK <=4
--2 Membuka Cursor
OPEN CsrMsiswa
--3 Memproses Cursor
DECLARE @NIK int
DECLARE @NAMA char(30)
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
WHILE @@FETCH_STATUS=0
BEGIN
PRINT 'NIK :'+CONVERT(CHAR,@NIK)
PRINT 'NAMA :'+@NAMA
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
END
--4 Menutup Cursor
CLOSE CsrMsiswa
--5 Mendealokasikan Cursor
DEALLOCATE CsrMsiswa
Menggunakan Cursor pada Store Procedure
Untuk membuat SP gunakan perintah Create Procedure as. Kemudian salin 5 langkah penggunaan cursor tadi.
Menjalankan SP yang didalamnya ada Cursor
Latihan Cursor : Penggunaan Cursor untuk Pencetakan Struk Gaji
Database : PTABC
Tabel : Karyawan , dengan struktur dan datanya sbb (LIHAT GAMBAR DIBAWAH INI)
PENGGUNAAN SCROLLABLE CURSOR
Ada lima tipe scrollable cursor yang data digunakan saat memproses cursor yaitu :
- Prior
- Next
- First
- Last
- Relative (+/- n)
Untuk mengaktifkan scrollable cursor tambahkan sintak SCROLL saat mendeklarasikan CURSOR, sbb :
--1.Deklarasi cursor
DECLARE CsrKaryawan SCROLL CURSOR
FOR select NIK, NAMA from Karyawan
--2.Buka cursor
Open CsrKaryawan
--3.Proses cursor
DECLARE @nik CHAR(10), @nama VARCHAR(30)
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
--4.Tutup Cursor
Close CsrKaryawan
--5.Dealokasi Cursor
Deallocate CsrKaryawan
Contoh penggunaan scrollable cursor
Latihan :
1. Coba gunakan scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
2. Coba gunakan scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH FIRST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
3. Coba gunakan scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH LAST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH RELATIVE -2 FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH FIRST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
4. Coba gunakan scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH LAST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH FIRST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH PRIOR FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
5. Coba gunakan scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH PRIOR FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH LAST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
6. Coba gunakan scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH RELATIVE 2 FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
7. Coba gunakan scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH LAST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH PRIOR FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH PRIOR FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
8. Coba gunakan TANPA scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH LAST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH FIRST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH PRIOR FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
CATATAN :
Untuk sintaks PRIOR, LAST, FIRST, RELATIVE hanya bisa digunakan pada SCROLLABLE CURSOR
LATIHAN :
1. BUAT SP_SCR_CURSOR1, HASIL SBB :
NIK NAMA
1001
1002
1003
1004
1003
1002
1001 A
B
C
D
C
B
A
2. BUAT SP_SCR_CURSOR2, HASIL SBB :
NO DEP NIK NAMA GAJI TRANSPORT TOTAL
1 HRD 1001 A 1000 500 1500
2 1002 B 1000 500 1500
-----------------SUBTOTAL 2000 1000 3000
3 IT 1003 C 2000 750 2750
4 1004 D 2000 750 2750
-----------------SUBTOTAL 4000 1500 5500
-----------------GRANDTOTAL 6000 2500 8500
3. BUAT SP_SCR_CURSOR3 untuk mengisi tabel RekapGaji, sbb
Tabel RekapGaji
DEPARTEMEN JLHKARYAWAN GAJI TRANSPORT
HRD 2 2000 1000
IT 2 4000 1500
4. Buat SP_RekapIPK
Tabel Mahasiswa
NIK NAMA IPK
01
01
03
04
05
06 A
B
C
D
E
F 3,50
2,80
2,50
2,00
1,90
3,10
RekapIPK : ini dihasilkan setelah sp dijalankan
RENTANGA RENTANGB JUMLAHMHS
0,00 2,00
2,01 3,00
3,01 4,00
Apa itu CURSOR?
Cursor adalah suatu perintah pemrograman yang mengijinkan setiap baris (record) diproses secara terpisah terhadap record lainnya. Cursor sangat bermanfaat jika ada suatu proses yang rumit yang ingin dilakukan terhadap suatu record. Proses tersebut bisa dilakukan secara satu per satu, yatiu lewat cursor
Algoritma CURSOR ?
1. Mendeklarasikan Cursor
2. Membuka Cursor
3. Memproses Cursor
4. Menutup Cursor
5. Mendealokasikan Cursor
Penggunaan CURSOR yang sederhana
--1 Mendeklarasikan Cursor
DECLARE CsrMsiswa CURSOR
FOR Select * from Msiswa Where NIK>=2 and NIK <=4
--2 Membuka Cursor
OPEN CsrMsiswa
--3 Memproses Cursor
DECLARE @NIK int
DECLARE @NAMA char(30)
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
WHILE @@FETCH_STATUS=0
BEGIN
PRINT 'NIK :'+CONVERT(CHAR,@NIK)
PRINT 'NAMA :'+@NAMA
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
END
--4 Menutup Cursor
CLOSE CsrMsiswa
--5 Mendealokasikan Cursor
DEALLOCATE CsrMsiswa
Menggunakan Cursor pada Store Procedure
Untuk membuat SP gunakan perintah Create Procedure
Menjalankan SP yang didalamnya ada Cursor
Latihan Cursor : Penggunaan Cursor untuk Pencetakan Struk Gaji
Database : PTABC
Tabel : Karyawan , dengan struktur dan datanya sbb (LIHAT GAMBAR DIBAWAH INI)
PENGGUNAAN SCROLLABLE CURSOR
Ada lima tipe scrollable cursor yang data digunakan saat memproses cursor yaitu :
- Prior
- Next
- First
- Last
- Relative (+/- n)
Untuk mengaktifkan scrollable cursor tambahkan sintak SCROLL saat mendeklarasikan CURSOR, sbb :
--1.Deklarasi cursor
DECLARE CsrKaryawan SCROLL CURSOR
FOR select NIK, NAMA from Karyawan
--2.Buka cursor
Open CsrKaryawan
--3.Proses cursor
DECLARE @nik CHAR(10), @nama VARCHAR(30)
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
--4.Tutup Cursor
Close CsrKaryawan
--5.Dealokasi Cursor
Deallocate CsrKaryawan
Contoh penggunaan scrollable cursor
Latihan :
1. Coba gunakan scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
2. Coba gunakan scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH FIRST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
3. Coba gunakan scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH LAST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH RELATIVE -2 FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH FIRST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
4. Coba gunakan scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH LAST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH FIRST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH PRIOR FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
5. Coba gunakan scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH PRIOR FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH LAST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
6. Coba gunakan scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH RELATIVE 2 FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
7. Coba gunakan scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH LAST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH PRIOR FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH PRIOR FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
8. Coba gunakan TANPA scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH LAST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH FIRST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH PRIOR FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
CATATAN :
Untuk sintaks PRIOR, LAST, FIRST, RELATIVE hanya bisa digunakan pada SCROLLABLE CURSOR
LATIHAN :
1. BUAT SP_SCR_CURSOR1, HASIL SBB :
NIK NAMA
1001
1002
1003
1004
1003
1002
1001 A
B
C
D
C
B
A
2. BUAT SP_SCR_CURSOR2, HASIL SBB :
NO DEP NIK NAMA GAJI TRANSPORT TOTAL
1 HRD 1001 A 1000 500 1500
2 1002 B 1000 500 1500
-----------------SUBTOTAL 2000 1000 3000
3 IT 1003 C 2000 750 2750
4 1004 D 2000 750 2750
-----------------SUBTOTAL 4000 1500 5500
-----------------GRANDTOTAL 6000 2500 8500
3. BUAT SP_SCR_CURSOR3 untuk mengisi tabel RekapGaji, sbb
Tabel RekapGaji
DEPARTEMEN JLHKARYAWAN GAJI TRANSPORT
HRD 2 2000 1000
IT 2 4000 1500
4. Buat SP_RekapIPK
Tabel Mahasiswa
NIK NAMA IPK
01
01
03
04
05
06 A
B
C
D
E
F 3,50
2,80
2,50
2,00
1,90
3,10
RekapIPK : ini dihasilkan setelah sp dijalankan
RENTANGA RENTANGB JUMLAHMHS
0,00 2,00
2,01 3,00
3,01 4,00
Wednesday, January 12, 2011
Contoh Soal View
1)Pelanggan yang tidak bertransaksi di bulan juni 2005
SELECT dbo.Pelanggan.KodePelanggan, dbo.Pelanggan.NamaPelanggan, dbo.FJ.TglFJ
FROM dbo.Pelanggan LEFT OUTER JOIN
dbo.FJ ON dbo.Pelanggan.KodePelanggan = dbo.FJ.KodePelanggan
WHERE (dbo.Pelanggan.KodePelanggan NOT IN
(SELECT KodePelanggan
FROM dbo.FJ AS FJ_1
WHERE (TglFJ BETWEEN '6/1/2005' AND '6/30/2005 23:59:59')))
2) Buat view barang barang yang terjual di juni dan juli 2005 khusus yang bertanda cek
SELECT DISTINCT dbo.Barang.KodeBarang, dbo.Barang.NamaBarang, dbo.FJ.TglFJ, dbo.Barang.Cek
FROM dbo.Barang INNER JOIN
dbo.FJDet ON dbo.Barang.KodeBarang = dbo.FJDet.KodeBarang INNER JOIN
dbo.FJ ON dbo.FJDet.NoFJ = dbo.FJ.NoFJ
WHERE (dbo.FJ.TglFJ BETWEEN '6/1/2005' AND '7/31/2005 23:59:59') AND (dbo.Barang.Cek = 1)
SELECT dbo.Pelanggan.KodePelanggan, dbo.Pelanggan.NamaPelanggan, dbo.FJ.TglFJ
FROM dbo.Pelanggan LEFT OUTER JOIN
dbo.FJ ON dbo.Pelanggan.KodePelanggan = dbo.FJ.KodePelanggan
WHERE (dbo.Pelanggan.KodePelanggan NOT IN
(SELECT KodePelanggan
FROM dbo.FJ AS FJ_1
WHERE (TglFJ BETWEEN '6/1/2005' AND '6/30/2005 23:59:59')))
2) Buat view barang barang yang terjual di juni dan juli 2005 khusus yang bertanda cek
SELECT DISTINCT dbo.Barang.KodeBarang, dbo.Barang.NamaBarang, dbo.FJ.TglFJ, dbo.Barang.Cek
FROM dbo.Barang INNER JOIN
dbo.FJDet ON dbo.Barang.KodeBarang = dbo.FJDet.KodeBarang INNER JOIN
dbo.FJ ON dbo.FJDet.NoFJ = dbo.FJ.NoFJ
WHERE (dbo.FJ.TglFJ BETWEEN '6/1/2005' AND '7/31/2005 23:59:59') AND (dbo.Barang.Cek = 1)
Contoh-contoh SP dan AutoNumber
ALTER PROCEDURE SP_InsertDaftar
@nama varchar(50),
@lp varchar(50),
@tgldaftar datetime,
@jurusan varchar(50)
AS
BEGIN
declare @nodaftar varchar(50)
declare @noakhir int
declare @nobaru int
select @noakhir = Max(Convert(int,substring(NoDaftar,8,4))) from TDaftar
if(@noakhir is null)
begin
set @nobaru = 1
end
else
begin
set @nobaru = @noakhir + 1
end
set @nodaftar = Convert(varchar,datepart(yyyy,@tgldaftar)) + '/' +
@lp + '-' + right('0000' + Convert(varchar,@nobaru),4) + '/' +
@jurusan
insert into TDaftar
values(@nodaftar,@nama,@lp,@tgldaftar,@jurusan)
END
GO
exec SP_InsertDaftar 'Test3','P','1/1/2011','DK'
select * from TDaftar
-------------------------------------------------------------------------------------
CREATE PROCEDURE SP_InsertPegawai
@thnmasuk datetime,
@nama varchar(50)
AS
BEGIN
declare @nopegawai varchar(50)
declare @noakhir int
declare @nobaru int
select @noakhir = Max(Convert(int,substring(NoPegawai,1,5))) from TPegawai
if(@noakhir is null)
begin
set @nobaru = 1
end
else
begin
set @nobaru = @noakhir + 1
end
set @nopegawai = right('00000' + Convert(varchar,@nobaru),5) +
'-' + Convert(varchar,datepart(yyyy,@thnmasuk)) + '-' +
substring(@nama,1,1)
insert into TPegawai
values(@nopegawai,@thnmasuk,@nama)
END
GO
exec SP_InsertPegawai '5/6/2005','Bebas'
select * from TPegawai
-------------------------------------------------------------------------------------
CREATE PROCEDURE SP_TambahTBPO
@TglPO datetime, @KodeSupplier varchar(10)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(13)
--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(right(NomorPO,5)))
from tbPO
where substring(NomorPO,4,4) = convert(char(4), Datepart(yyyy, @TglPO))
--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1
Set @NomorPO = 'PO-' + convert(char(4), Datepart(yyyy, @TglPO )) + '-' +
RIGHT('00000'+Convert(varchar, @NoBaru), 5)
--3.Insert Data baru
Insert tbPO (NomorPO, TglPO, KodeSupplier) Values (@NomorPO, @TglPO, @KodeSupplier)
END
exec SP_TambahTBPO'6/1/2005', 'S-00005'
-------------------------------------------------------------------------------------
ALTER PROCEDURE SP_TambahGudang
@TglPO datetime, @Gudang varchar(10), @KodeSupplier varchar(10)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(50)
--1.Cari Nomor Akhir
select @NoAkhir = Max(convert(int,(right(NomorPO,5))))
from TGudang
where (substring(NomorPO,4,4) = convert(varchar(4), Datepart(yyyy, @TglPO)))and (substring(NomorPO,9,2) = Convert(varchar(2),@Gudang))
--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1
Set @NomorPO = 'PO-' + convert(varchar(4), Datepart(yyyy, @TglPO )) + '-' + @Gudang + '-' +
RIGHT('00000'+Convert(varchar(5), @NoBaru), 5)
--3.Insert Data baru
Insert TGudang (NomorPO, TglPO, Gudang, KodeSupplier) Values (@NomorPO, @TglPO, @Gudang, @KodeSupplier)
END
exec SP_TambahGudang '10/16/2010','G3','SS'
-------------------------------------------------------------------------------------
ALTER PROCEDURE SP_TambahBarang
@NamaBarang varchar(50), @Stock int
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @Kode varchar(50)
--1.Cari Nomor Akhir
select @NoAkhir = Max(convert(int,(right(KodeBarang,3))))
from TBarang
where left(KodeBarang,1) = left(@NamaBarang,1)
--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1
Set @Kode = left(@NamaBarang,1) + RIGHT('000'+Convert(varchar(5), @NoBaru), 3)
--3.Insert Data baru
Insert TBarang
Values (@Kode, @NamaBarang, @Stock)
END
exec SP_TambahBarang 'Bath Tub Toto 34523',5
select * from TBarang
@nama varchar(50),
@lp varchar(50),
@tgldaftar datetime,
@jurusan varchar(50)
AS
BEGIN
declare @nodaftar varchar(50)
declare @noakhir int
declare @nobaru int
select @noakhir = Max(Convert(int,substring(NoDaftar,8,4))) from TDaftar
if(@noakhir is null)
begin
set @nobaru = 1
end
else
begin
set @nobaru = @noakhir + 1
end
set @nodaftar = Convert(varchar,datepart(yyyy,@tgldaftar)) + '/' +
@lp + '-' + right('0000' + Convert(varchar,@nobaru),4) + '/' +
@jurusan
insert into TDaftar
values(@nodaftar,@nama,@lp,@tgldaftar,@jurusan)
END
GO
exec SP_InsertDaftar 'Test3','P','1/1/2011','DK'
select * from TDaftar
-------------------------------------------------------------------------------------
CREATE PROCEDURE SP_InsertPegawai
@thnmasuk datetime,
@nama varchar(50)
AS
BEGIN
declare @nopegawai varchar(50)
declare @noakhir int
declare @nobaru int
select @noakhir = Max(Convert(int,substring(NoPegawai,1,5))) from TPegawai
if(@noakhir is null)
begin
set @nobaru = 1
end
else
begin
set @nobaru = @noakhir + 1
end
set @nopegawai = right('00000' + Convert(varchar,@nobaru),5) +
'-' + Convert(varchar,datepart(yyyy,@thnmasuk)) + '-' +
substring(@nama,1,1)
insert into TPegawai
values(@nopegawai,@thnmasuk,@nama)
END
GO
exec SP_InsertPegawai '5/6/2005','Bebas'
select * from TPegawai
-------------------------------------------------------------------------------------
CREATE PROCEDURE SP_TambahTBPO
@TglPO datetime, @KodeSupplier varchar(10)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(13)
--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(right(NomorPO,5)))
from tbPO
where substring(NomorPO,4,4) = convert(char(4), Datepart(yyyy, @TglPO))
--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1
Set @NomorPO = 'PO-' + convert(char(4), Datepart(yyyy, @TglPO )) + '-' +
RIGHT('00000'+Convert(varchar, @NoBaru), 5)
--3.Insert Data baru
Insert tbPO (NomorPO, TglPO, KodeSupplier) Values (@NomorPO, @TglPO, @KodeSupplier)
END
exec SP_TambahTBPO'6/1/2005', 'S-00005'
-------------------------------------------------------------------------------------
ALTER PROCEDURE SP_TambahGudang
@TglPO datetime, @Gudang varchar(10), @KodeSupplier varchar(10)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(50)
--1.Cari Nomor Akhir
select @NoAkhir = Max(convert(int,(right(NomorPO,5))))
from TGudang
where (substring(NomorPO,4,4) = convert(varchar(4), Datepart(yyyy, @TglPO)))and (substring(NomorPO,9,2) = Convert(varchar(2),@Gudang))
--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1
Set @NomorPO = 'PO-' + convert(varchar(4), Datepart(yyyy, @TglPO )) + '-' + @Gudang + '-' +
RIGHT('00000'+Convert(varchar(5), @NoBaru), 5)
--3.Insert Data baru
Insert TGudang (NomorPO, TglPO, Gudang, KodeSupplier) Values (@NomorPO, @TglPO, @Gudang, @KodeSupplier)
END
exec SP_TambahGudang '10/16/2010','G3','SS'
-------------------------------------------------------------------------------------
ALTER PROCEDURE SP_TambahBarang
@NamaBarang varchar(50), @Stock int
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @Kode varchar(50)
--1.Cari Nomor Akhir
select @NoAkhir = Max(convert(int,(right(KodeBarang,3))))
from TBarang
where left(KodeBarang,1) = left(@NamaBarang,1)
--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1
Set @Kode = left(@NamaBarang,1) + RIGHT('000'+Convert(varchar(5), @NoBaru), 3)
--3.Insert Data baru
Insert TBarang
Values (@Kode, @NamaBarang, @Stock)
END
exec SP_TambahBarang 'Bath Tub Toto 34523',5
select * from TBarang
Quiz Sebelum UTS
2. SP_Lihat_Pasien
CREATE PROCEDURE SP_Lihat_Pasien
@tahun char(2),
@bulan char(2)
AS
BEGIN
SELECT * from t_pasien
where substring(id_pasien,2,2) = @tahun and
substring(id_pasien,4,2) = @bulan
END
GO
3. SP_Lihat_Gologan Darah
CREATE PROCEDURE SP_Lihat_GolDarah
@gol_darah char(2)
AS
BEGIN
SELECT * from t_pasien
where gol_darah = @gol_darah
END
GO
4.SP_Tambah_Pasien
alter PROCEDURE SP_Tambah_Pasien
@nama varchar(30)
AS
BEGIN
declare @yy varchar(2)
declare @mm varchar(2)
declare @id_pasien varchar(10)
declare @no_akhir int
declare @no_baru int
set @yy = substring(Convert(varchar,(datepart(yy,getdate()))),3,2)
set @mm = right('0' + Convert(varchar,(datepart(mm,getdate()))),2)
select @no_akhir = Max(Convert(int,right(id_pasien,3))) from t_pasien
where substring(id_pasien,2,2) = @yy and
substring(id_pasien,4,2) = @mm
if(@no_akhir is null)
begin
set @no_baru = 1
end
else
begin
set @no_baru = @no_akhir + 1
end
print @no_baru
set @id_pasien = 'P'+ @yy + @mm + right('000' + Convert(varchar,@no_baru),3)
print @id_pasien
INSERT INTO [rumahsakit].[dbo].[t_pasien]
([id_pasien]
,[nama])
VALUES
(@id_pasien
,@nama)
END
GO
5. a
alter FUNCTION HitungUmur
(
@p1 datetime
)
RETURNS varchar(20)
AS
BEGIN
DECLARE @Result1 varchar(10)
DECLARE @Result2 varchar(10)
declare @result varchar(20)
set @Result1 = Convert(varchar,datediff(yy,@p1,getdate())-1)
set @Result2 = Convert(varchar,datediff(mm,@p1,getdate())-@Result1*12)
set @result = @Result1 + ' Tahun ' + @Result2 + ' Bulan'
return @result
END
GO
b.
create FUNCTION HitungDokter
(
@p1 varchar(50)
)
RETURNS int
AS
BEGIN
DECLARE @Result int
select @Result = count(id_dokter) from t_dokter where spesialisasi like '%' + @p1 + '%'
return @Result
END
GO
CREATE PROCEDURE SP_Lihat_Pasien
@tahun char(2),
@bulan char(2)
AS
BEGIN
SELECT * from t_pasien
where substring(id_pasien,2,2) = @tahun and
substring(id_pasien,4,2) = @bulan
END
GO
3. SP_Lihat_Gologan Darah
CREATE PROCEDURE SP_Lihat_GolDarah
@gol_darah char(2)
AS
BEGIN
SELECT * from t_pasien
where gol_darah = @gol_darah
END
GO
4.SP_Tambah_Pasien
alter PROCEDURE SP_Tambah_Pasien
@nama varchar(30)
AS
BEGIN
declare @yy varchar(2)
declare @mm varchar(2)
declare @id_pasien varchar(10)
declare @no_akhir int
declare @no_baru int
set @yy = substring(Convert(varchar,(datepart(yy,getdate()))),3,2)
set @mm = right('0' + Convert(varchar,(datepart(mm,getdate()))),2)
select @no_akhir = Max(Convert(int,right(id_pasien,3))) from t_pasien
where substring(id_pasien,2,2) = @yy and
substring(id_pasien,4,2) = @mm
if(@no_akhir is null)
begin
set @no_baru = 1
end
else
begin
set @no_baru = @no_akhir + 1
end
print @no_baru
set @id_pasien = 'P'+ @yy + @mm + right('000' + Convert(varchar,@no_baru),3)
print @id_pasien
INSERT INTO [rumahsakit].[dbo].[t_pasien]
([id_pasien]
,[nama])
VALUES
(@id_pasien
,@nama)
END
GO
5. a
alter FUNCTION HitungUmur
(
@p1 datetime
)
RETURNS varchar(20)
AS
BEGIN
DECLARE @Result1 varchar(10)
DECLARE @Result2 varchar(10)
declare @result varchar(20)
set @Result1 = Convert(varchar,datediff(yy,@p1,getdate())-1)
set @Result2 = Convert(varchar,datediff(mm,@p1,getdate())-@Result1*12)
set @result = @Result1 + ' Tahun ' + @Result2 + ' Bulan'
return @result
END
GO
b.
create FUNCTION HitungDokter
(
@p1 varchar(50)
)
RETURNS int
AS
BEGIN
DECLARE @Result int
select @Result = count(id_dokter) from t_dokter where spesialisasi like '%' + @p1 + '%'
return @Result
END
GO
Quiz 2
CREATE FUNCTION JTotalFaktur
(
@tglAwal datetime,
@tglAkhir datetime
)
RETURNS money
AS
BEGIN
DECLARE @Result money
select @Result = sum(totalfaktur) from FJ
where tglFJ between @tglAwal and @tglAkhir
RETURN @Result
END
GO
-------------------------------------------------------------------------------
CREATE FUNCTION JPelanggan
(
@tglAwal datetime,
@tglAkhir datetime
)
RETURNS integer
AS
BEGIN
DECLARE @Result integer
select @Result = count(distinct kodepelanggan) from FJ
where tglFJ between @tglAwal and @tglAkhir
RETURN @Result
END
GO
-------------------------------------------------------------------------------
CREATE FUNCTION JBarang
(
@tglAwal datetime,
@tglAkhir datetime
)
RETURNS integer
AS
BEGIN
DECLARE @Result integer
select @Result = sum(fd.qty)
from FJDet fd, FJ f
where (f.NoFJ = fd.NoFj) and f.tglFJ between @tglAwal and @tglAkhir
RETURN @Result
END
GO
-------------------------------------------------------------------------------
CREATE FUNCTION JNilaiBarang
(
@tglAwal datetime,
@tglAkhir datetime
)
RETURNS money
AS
BEGIN
DECLARE @Result money
select @Result = sum(fd.qty * fd.harga)
from FJDet fd, FJ f
where (f.NoFJ = fd.NoFj) and f.tglFJ between @tglAwal and @tglAkhir
RETURN @Result
END
GO
-------------------------------------------------------------------------------
select dbo.JTotalFaktur('6/1/2005','6/30/2005 23:59:59') as JumlahTotalFaktur,
dbo.JPelanggan('6/1/2005','6/30/2005 23:59:59') as JumlahPelanggan,
dbo.JBarang('6/1/2005','6/30/2005 23:59:59') as JumlahBarang,
dbo.JNilaiBarang('6/1/2005','6/30/2005 23:59:59') as JumlahNilaiBarang
(
@tglAwal datetime,
@tglAkhir datetime
)
RETURNS money
AS
BEGIN
DECLARE @Result money
select @Result = sum(totalfaktur) from FJ
where tglFJ between @tglAwal and @tglAkhir
RETURN @Result
END
GO
-------------------------------------------------------------------------------
CREATE FUNCTION JPelanggan
(
@tglAwal datetime,
@tglAkhir datetime
)
RETURNS integer
AS
BEGIN
DECLARE @Result integer
select @Result = count(distinct kodepelanggan) from FJ
where tglFJ between @tglAwal and @tglAkhir
RETURN @Result
END
GO
-------------------------------------------------------------------------------
CREATE FUNCTION JBarang
(
@tglAwal datetime,
@tglAkhir datetime
)
RETURNS integer
AS
BEGIN
DECLARE @Result integer
select @Result = sum(fd.qty)
from FJDet fd, FJ f
where (f.NoFJ = fd.NoFj) and f.tglFJ between @tglAwal and @tglAkhir
RETURN @Result
END
GO
-------------------------------------------------------------------------------
CREATE FUNCTION JNilaiBarang
(
@tglAwal datetime,
@tglAkhir datetime
)
RETURNS money
AS
BEGIN
DECLARE @Result money
select @Result = sum(fd.qty * fd.harga)
from FJDet fd, FJ f
where (f.NoFJ = fd.NoFj) and f.tglFJ between @tglAwal and @tglAkhir
RETURN @Result
END
GO
-------------------------------------------------------------------------------
select dbo.JTotalFaktur('6/1/2005','6/30/2005 23:59:59') as JumlahTotalFaktur,
dbo.JPelanggan('6/1/2005','6/30/2005 23:59:59') as JumlahPelanggan,
dbo.JBarang('6/1/2005','6/30/2005 23:59:59') as JumlahBarang,
dbo.JNilaiBarang('6/1/2005','6/30/2005 23:59:59') as JumlahNilaiBarang
Quiz 1
create databaase DBUSMSTAN;
use DBUSMSTAN;
create table TProvinsi(
KodeProvinsi varchar(10) primary key,
NamaProvinsi varchar(50)
);
create table TBank(
KodeBank varchar(10) primary key,
NamaBank varchar(50)
);
create table TPendaftar(
IDPendaftar int identity,
NamaLengkap varchar(50),
TanggalLahir datetime,
KodeProvinsi varchar(10),
JenisKelamin varchar(10),
TinggiBadan int,
TahunLulus int,
TotalNilaiUANUAS int,
JumlahMataPelajaran int,
KondisiMata varchar(20),
KodeBank varchar(10),
constraint pk_tpendaftar primary key clustered
(
IDPendaftar asc,
)
constraint fk_provinsi foreign key(KodeProvinsi) references TProvinsi(KodeProvinsi)
constraint fk_bank foreign key(KodeBank) references TBank(KodeBank)
);
insert into TProvinsi
values('J001','Jakarta');
insert into TProvinsi
values('J002','Jawa Barat');
insert into TProvinsi
values('J003','Jawa Tengah');
insert into TProvinsi
values('J004','Jawa Timur');
insert into TProvinsi
values('S001','Surabaya');
insert into TBank
values('B001','BCA');
insert into TBank
values('B002','BRI');
insert into TBank
values('B003','BNI');
insert into TBank
values('B004','BPR');
insert into TBank
values('M001','Mandiri');
insert into TPendaftar(NamaLengkap,TanggalLahir,KodeProvinsi,JenisKelamin,TinggiBadan,TahunLulus,TotalNilaiUANUAS,
JumlahMataPelajaran,KondisiMata,KodeBank)
values('A','1-1-1990','J001','Laki-laki',170,2010,1200,12,'1','B001');
insert into TPendaftar(NamaLengkap,TanggalLahir,KodeProvinsi,JenisKelamin,TinggiBadan,TahunLulus,TotalNilaiUANUAS,
JumlahMataPelajaran,KondisiMata,KodeBank)
values('B','2-2-1990','J002','Perempuan',170,2010,1000,12,'1','B002');
insert into TPendaftar(NamaLengkap,TanggalLahir,KodeProvinsi,JenisKelamin,TinggiBadan,TahunLulus,TotalNilaiUANUAS,
JumlahMataPelajaran,KondisiMata,KodeBank)
values('C','3-3-1990','J003','Laki-laki',170,2010,900,12,'2','B003');
insert into TPendaftar(NamaLengkap,TanggalLahir,KodeProvinsi,JenisKelamin,TinggiBadan,TahunLulus,TotalNilaiUANUAS,
JumlahMataPelajaran,KondisiMata,KodeBank)
values('D','4-4-1990','S001','Perempuan',170,2010,1100,12,'3','M001');
insert into TPendaftar(NamaLengkap,TanggalLahir,KodeProvinsi,JenisKelamin,TinggiBadan,TahunLulus,TotalNilaiUANUAS,
JumlahMataPelajaran,KondisiMata,KodeBank)
values('E','5-5-1990','J004','Laki-laki',170,2010,1200,12,'1','B001');
use DBUSMSTAN;
create table TProvinsi(
KodeProvinsi varchar(10) primary key,
NamaProvinsi varchar(50)
);
create table TBank(
KodeBank varchar(10) primary key,
NamaBank varchar(50)
);
create table TPendaftar(
IDPendaftar int identity,
NamaLengkap varchar(50),
TanggalLahir datetime,
KodeProvinsi varchar(10),
JenisKelamin varchar(10),
TinggiBadan int,
TahunLulus int,
TotalNilaiUANUAS int,
JumlahMataPelajaran int,
KondisiMata varchar(20),
KodeBank varchar(10),
constraint pk_tpendaftar primary key clustered
(
IDPendaftar asc,
)
constraint fk_provinsi foreign key(KodeProvinsi) references TProvinsi(KodeProvinsi)
constraint fk_bank foreign key(KodeBank) references TBank(KodeBank)
);
insert into TProvinsi
values('J001','Jakarta');
insert into TProvinsi
values('J002','Jawa Barat');
insert into TProvinsi
values('J003','Jawa Tengah');
insert into TProvinsi
values('J004','Jawa Timur');
insert into TProvinsi
values('S001','Surabaya');
insert into TBank
values('B001','BCA');
insert into TBank
values('B002','BRI');
insert into TBank
values('B003','BNI');
insert into TBank
values('B004','BPR');
insert into TBank
values('M001','Mandiri');
insert into TPendaftar(NamaLengkap,TanggalLahir,KodeProvinsi,JenisKelamin,TinggiBadan,TahunLulus,TotalNilaiUANUAS,
JumlahMataPelajaran,KondisiMata,KodeBank)
values('A','1-1-1990','J001','Laki-laki',170,2010,1200,12,'1','B001');
insert into TPendaftar(NamaLengkap,TanggalLahir,KodeProvinsi,JenisKelamin,TinggiBadan,TahunLulus,TotalNilaiUANUAS,
JumlahMataPelajaran,KondisiMata,KodeBank)
values('B','2-2-1990','J002','Perempuan',170,2010,1000,12,'1','B002');
insert into TPendaftar(NamaLengkap,TanggalLahir,KodeProvinsi,JenisKelamin,TinggiBadan,TahunLulus,TotalNilaiUANUAS,
JumlahMataPelajaran,KondisiMata,KodeBank)
values('C','3-3-1990','J003','Laki-laki',170,2010,900,12,'2','B003');
insert into TPendaftar(NamaLengkap,TanggalLahir,KodeProvinsi,JenisKelamin,TinggiBadan,TahunLulus,TotalNilaiUANUAS,
JumlahMataPelajaran,KondisiMata,KodeBank)
values('D','4-4-1990','S001','Perempuan',170,2010,1100,12,'3','M001');
insert into TPendaftar(NamaLengkap,TanggalLahir,KodeProvinsi,JenisKelamin,TinggiBadan,TahunLulus,TotalNilaiUANUAS,
JumlahMataPelajaran,KondisiMata,KodeBank)
values('E','5-5-1990','J004','Laki-laki',170,2010,1200,12,'1','B001');
Auto Number
Definisi Autonumber
Autonumber adalah field yang isinya akan secara otomatis terisi menurut urutan tertentu.
Field Autonumber dapat dibuat dengan 2 cara :
1. Menggunakan field Identity (Autonumber). Field ini harus bertipe Int atau Numeric, kemudian tentukan Seed (Nilai dimulai dari berapa), Increment (Pertambahan Nilai)
2. Menggunakan StoreProcedure / Trigger / Function. Field ini akan dibuat oleh program dengan algoritma sbb :
1. Cari Nomor Akhir (Nomor terakhir yang ada di tabel)
2. Buat Nomor Baru (Nomor Akhir ditambah 1, dengan asumsi incrementnya 1)
3. Insert data dengan pada Field Autonumber dengan Nomor Baru
-------------------------------------------------------------------------------------
. NIP : field int dengan identity seed 1000, increment 1, NAMA : varchar(50)
NIP NAMA
1000 A
1001 B
1002 C
SQL : select NIP, NAMA FROM TABEL1
2. NIP1 : field char(2), default value 'P-'; NIP2 : field int dengan identity seed 1000, increment 1; NAMA : varchar(50)
NIP NAMA
P-1000 A
P-1001 B
P-1002 C
SQL : select NIP1+CONVERT(VARCHAR,NIP2) AS NIP, NAMA FROM TABEL2
3. NIP1 : field char(2), default value 'P-'; NIP2 : field int dengan identity seed 1000, increment 1, NIP3 : field char(4), default value datepart(yyyy,getdate()); NAMA : varchar(50)
NIP NAMA
P-1000/2008 A
P-1001/2008 B
P-1002/2008 C
SQL : select NIP1+CONVERT(VARCHAR,NIP2)+'/'+convert(varchar,NIP3) AS NIP, NAMA FROM TABEL3
4. NIP1 : field char(4), default value datepart(yyyy,getdate()) ; NIP2 : field int dengan identity seed 100, increment 1; TGLLAHIR : smalldatetime NAMA : varchar(50)
NIP TGLLAHIR NAMA
2008-100-198003 18/03/1980 A
2008-101-197406 30/06/1974 B
2008-102-198104 01/04/1981 C
SQL : select rtrim(NIP1)+'-'+CONVERT(VARCHAR,NIP2)+'-'+
convert(varchar, datepart(yyyy,TGLLAHIR))+
convert(varchar, datepart(mm,TGLLAHIR)) AS NIP, NAMA,TGLLAHIR FROM TABEL4
-------------------------------------------------------------------------------------
AUTONUMBER MENGGUNAKAN STOREPROCEDURE
CREATE PROCEDURE SP_TambahPeg
@Nama varchar(50)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(NIP))
from tblPeg
--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1
--3.Insert Data baru
Insert TblPeg (NIP, Nama) Values (Convert(char(4), @NoBaru), @Nama)
END
-------------------------------------------------------------------------------------
AUTONUMBER RESET PERIODIK
Autonumber dapat pula digunakan untuk pencatatan nomor dokumen transaksi seperti Nomor Dokumen Permintaan Barang, Nomor Pesanan Barang, Nomor Penerimaan Barang, Nomor Pengeluaran Barang, Nomor Pembelian, Nomor Kontra Bon, dsb. Selalu menggunakan penomoran yang berurutan. Penomoran ini memiliki pola yang khas. Contoh :
• Penomoran Dokumen Permintaan Barang : SPP-Tahun-NomorUrut
• Penomoran Dokumen Pesanan Barang : PO-Tahun-NomorUrut
Penomoran ini tergantung dari kebijakan perusahaan setempat. Namun pada prinsipnya Nomor Urut dapat dibagi menjadi 2 kelompok, yaitu :
1) Nomor Urut berlanjut terus (tanpa reset ke semula)
2) Nomor Urut Reset Periodik, reset biasanya mengambil pola sbb :
• Nomor Urut dengan reset Harian
• Nomor Urut dengan reset Bulanan
• Nomor Urut dengan reset Tahunan
-------------------------------------------------------------------------------------
Penomoran dengan Nomor Urut Reset Tahunan
Nomor Urut ini akan direset otomatis ke nomor awal (seed) saat tanggal komputer berada di tahun yang baru. Contoh penomoran dokumen pesanan barang dengan pola: PO-Tahun-NomorUrut
Tabel : TbPO
NomorPO TGLPO KODESUPPLIER
PO-2008-00001 16/09/2008 S-00001
PO-2008-00002 16/09/2008 S-00001
PO-2008-00003 17/09/2008 S-00002
Saat tahun berada di Tahun 2009, maka NomorPO akan direset kenomor baru yaitu menjadi PO-2009-00001, PO-2009-00002, PO-2009-00003, dst. Jadi saat akan insert record baru sistem akan memeriksa nomor terakhir di tahun tersebut, kemudian tambahkan 1 (asumsi increment 1). Jika tidak ada nomor terakhir ditahun tersebut maka dapat dipastikan nomor akan reset kenomor semula.
-------------------------------------------------------------------------------------
Store Procedure Insert dengan Nomor Urut Reset Tahunan
Untuk Pembuatan Store Procedure Insert pada table TbPO sbb :
CREATE PROCEDURE SP_TambahTBPO
@TglPO datetime, @KodeSupplier varchar(10)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(13)
--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(right(NomorPO,5)))
from tbPO
where substring(NomorPO,4,4) = convert(char(4), Datepart(yyyy, @TglPO)))
--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1
Set @NomorPO = ‘PO-‘ + convert(char(4), Datepart(yyyy, @TglPO )) + ‘-‘ +
RIGHT(‘00000’+Convert(varchar, @NoBaru), 5)
--3.Insert Data baru
Insert tbPO (NomorPO, TglPO, KodeSupplier) Values (@NomorPO, @TglPO, @KodeSupplier)
END
-------------------------------------------------------------------------------------
Penomoran dengan Nomor Urut Reset Bulanan
Nomor Urut ini akan direset otomatis ke nomor awal (seed) saat tanggal komputer berada di bulan yang baru. Contoh penomoran dokumen pesanan barang dengan pola: PO-TahunBulan-NomorUrut
Tabel : TbPOBulanan
NomorPO TGLPO KODESUPPLIER
PO-200809-00001 16/09/2008 S-00001
PO-200809-00002 16/09/2008 S-00001
PO-200809-00003 17/09/2008 S-00002
PO-200810-00001 17/10/2008 S-00002
PO-200810-00002 17/10/2008 S-00002
Saat tahun berada di Bulan 10-2008, maka NomorPO akan direset kenomor baru yaitu menjadi PO-200810-00001, PO-200810-00002, dst. Jadi saat akan insert record baru sistem akan memeriksa nomor terakhir di bulan dan tahun tersebut, kemudian tambahkan 1 (asumsi increment 1). Jika tidak ada nomor terakhir di bulan dan tahun tersebut maka dapat dipastikan nomor akan reset kenomor semula.
-------------------------------------------------------------------------------------
Store Procedure Insert dengan Nomor Urut Reset Bulanan
Untuk Pembuatan Store Procedure Insert pada table TbPOBulanan sbb :
CREATE PROCEDURE SP_TambahTBPOBulanan
@TglPO datetime, @KodeSupplier varchar(10)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(15)
--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(right(NomorPO,5)))
from tbPOBulanan
where substring(NomorPO,4,4) = convert(char(4), Datepart(yyyy, @TglPO)) AND
convert(int, substring(NomorPO,8,2)) = Datepart(mm, @TglPO)
--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1
Set @NomorPO = 'PO-' + convert(char(4), Datepart(yyyy, @TglPO )) +
right('0'+convert(varchar, datepart(mm,@TglPO)),2) + '-' +
RIGHT('00000'+Convert(varchar, @NoBaru), 5)
--3.Insert Data baru
Insert tbPOBulanan (NomorPO, TglPO, KodeSupplier) Values (@NomorPO, @TglPO, @KodeSupplier)
END
-------------------------------------------------------------------------------------
Autonumber adalah field yang isinya akan secara otomatis terisi menurut urutan tertentu.
Field Autonumber dapat dibuat dengan 2 cara :
1. Menggunakan field Identity (Autonumber). Field ini harus bertipe Int atau Numeric, kemudian tentukan Seed (Nilai dimulai dari berapa), Increment (Pertambahan Nilai)
2. Menggunakan StoreProcedure / Trigger / Function. Field ini akan dibuat oleh program dengan algoritma sbb :
1. Cari Nomor Akhir (Nomor terakhir yang ada di tabel)
2. Buat Nomor Baru (Nomor Akhir ditambah 1, dengan asumsi incrementnya 1)
3. Insert data dengan pada Field Autonumber dengan Nomor Baru
-------------------------------------------------------------------------------------
. NIP : field int dengan identity seed 1000, increment 1, NAMA : varchar(50)
NIP NAMA
1000 A
1001 B
1002 C
SQL : select NIP, NAMA FROM TABEL1
2. NIP1 : field char(2), default value 'P-'; NIP2 : field int dengan identity seed 1000, increment 1; NAMA : varchar(50)
NIP NAMA
P-1000 A
P-1001 B
P-1002 C
SQL : select NIP1+CONVERT(VARCHAR,NIP2) AS NIP, NAMA FROM TABEL2
3. NIP1 : field char(2), default value 'P-'; NIP2 : field int dengan identity seed 1000, increment 1, NIP3 : field char(4), default value datepart(yyyy,getdate()); NAMA : varchar(50)
NIP NAMA
P-1000/2008 A
P-1001/2008 B
P-1002/2008 C
SQL : select NIP1+CONVERT(VARCHAR,NIP2)+'/'+convert(varchar,NIP3) AS NIP, NAMA FROM TABEL3
4. NIP1 : field char(4), default value datepart(yyyy,getdate()) ; NIP2 : field int dengan identity seed 100, increment 1; TGLLAHIR : smalldatetime NAMA : varchar(50)
NIP TGLLAHIR NAMA
2008-100-198003 18/03/1980 A
2008-101-197406 30/06/1974 B
2008-102-198104 01/04/1981 C
SQL : select rtrim(NIP1)+'-'+CONVERT(VARCHAR,NIP2)+'-'+
convert(varchar, datepart(yyyy,TGLLAHIR))+
convert(varchar, datepart(mm,TGLLAHIR)) AS NIP, NAMA,TGLLAHIR FROM TABEL4
-------------------------------------------------------------------------------------
AUTONUMBER MENGGUNAKAN STOREPROCEDURE
CREATE PROCEDURE SP_TambahPeg
@Nama varchar(50)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(NIP))
from tblPeg
--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1
--3.Insert Data baru
Insert TblPeg (NIP, Nama) Values (Convert(char(4), @NoBaru), @Nama)
END
-------------------------------------------------------------------------------------
AUTONUMBER RESET PERIODIK
Autonumber dapat pula digunakan untuk pencatatan nomor dokumen transaksi seperti Nomor Dokumen Permintaan Barang, Nomor Pesanan Barang, Nomor Penerimaan Barang, Nomor Pengeluaran Barang, Nomor Pembelian, Nomor Kontra Bon, dsb. Selalu menggunakan penomoran yang berurutan. Penomoran ini memiliki pola yang khas. Contoh :
• Penomoran Dokumen Permintaan Barang : SPP-Tahun-NomorUrut
• Penomoran Dokumen Pesanan Barang : PO-Tahun-NomorUrut
Penomoran ini tergantung dari kebijakan perusahaan setempat. Namun pada prinsipnya Nomor Urut dapat dibagi menjadi 2 kelompok, yaitu :
1) Nomor Urut berlanjut terus (tanpa reset ke semula)
2) Nomor Urut Reset Periodik, reset biasanya mengambil pola sbb :
• Nomor Urut dengan reset Harian
• Nomor Urut dengan reset Bulanan
• Nomor Urut dengan reset Tahunan
-------------------------------------------------------------------------------------
Penomoran dengan Nomor Urut Reset Tahunan
Nomor Urut ini akan direset otomatis ke nomor awal (seed) saat tanggal komputer berada di tahun yang baru. Contoh penomoran dokumen pesanan barang dengan pola: PO-Tahun-NomorUrut
Tabel : TbPO
NomorPO TGLPO KODESUPPLIER
PO-2008-00001 16/09/2008 S-00001
PO-2008-00002 16/09/2008 S-00001
PO-2008-00003 17/09/2008 S-00002
Saat tahun berada di Tahun 2009, maka NomorPO akan direset kenomor baru yaitu menjadi PO-2009-00001, PO-2009-00002, PO-2009-00003, dst. Jadi saat akan insert record baru sistem akan memeriksa nomor terakhir di tahun tersebut, kemudian tambahkan 1 (asumsi increment 1). Jika tidak ada nomor terakhir ditahun tersebut maka dapat dipastikan nomor akan reset kenomor semula.
-------------------------------------------------------------------------------------
Store Procedure Insert dengan Nomor Urut Reset Tahunan
Untuk Pembuatan Store Procedure Insert pada table TbPO sbb :
CREATE PROCEDURE SP_TambahTBPO
@TglPO datetime, @KodeSupplier varchar(10)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(13)
--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(right(NomorPO,5)))
from tbPO
where substring(NomorPO,4,4) = convert(char(4), Datepart(yyyy, @TglPO)))
--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1
Set @NomorPO = ‘PO-‘ + convert(char(4), Datepart(yyyy, @TglPO )) + ‘-‘ +
RIGHT(‘00000’+Convert(varchar, @NoBaru), 5)
--3.Insert Data baru
Insert tbPO (NomorPO, TglPO, KodeSupplier) Values (@NomorPO, @TglPO, @KodeSupplier)
END
-------------------------------------------------------------------------------------
Penomoran dengan Nomor Urut Reset Bulanan
Nomor Urut ini akan direset otomatis ke nomor awal (seed) saat tanggal komputer berada di bulan yang baru. Contoh penomoran dokumen pesanan barang dengan pola: PO-TahunBulan-NomorUrut
Tabel : TbPOBulanan
NomorPO TGLPO KODESUPPLIER
PO-200809-00001 16/09/2008 S-00001
PO-200809-00002 16/09/2008 S-00001
PO-200809-00003 17/09/2008 S-00002
PO-200810-00001 17/10/2008 S-00002
PO-200810-00002 17/10/2008 S-00002
Saat tahun berada di Bulan 10-2008, maka NomorPO akan direset kenomor baru yaitu menjadi PO-200810-00001, PO-200810-00002, dst. Jadi saat akan insert record baru sistem akan memeriksa nomor terakhir di bulan dan tahun tersebut, kemudian tambahkan 1 (asumsi increment 1). Jika tidak ada nomor terakhir di bulan dan tahun tersebut maka dapat dipastikan nomor akan reset kenomor semula.
-------------------------------------------------------------------------------------
Store Procedure Insert dengan Nomor Urut Reset Bulanan
Untuk Pembuatan Store Procedure Insert pada table TbPOBulanan sbb :
CREATE PROCEDURE SP_TambahTBPOBulanan
@TglPO datetime, @KodeSupplier varchar(10)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(15)
--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(right(NomorPO,5)))
from tbPOBulanan
where substring(NomorPO,4,4) = convert(char(4), Datepart(yyyy, @TglPO)) AND
convert(int, substring(NomorPO,8,2)) = Datepart(mm, @TglPO)
--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1
Set @NomorPO = 'PO-' + convert(char(4), Datepart(yyyy, @TglPO )) +
right('0'+convert(varchar, datepart(mm,@TglPO)),2) + '-' +
RIGHT('00000'+Convert(varchar, @NoBaru), 5)
--3.Insert Data baru
Insert tbPOBulanan (NomorPO, TglPO, KodeSupplier) Values (@NomorPO, @TglPO, @KodeSupplier)
END
-------------------------------------------------------------------------------------
Function
Fungsi Standar
Definisi Fungsi
Fungsi adalah bagian dari perintah/statement yang merubah beberapa nilai masukan
menjadi sebuah nilai baru (keluaran/hasil).
Beberapa fungsi telah tersedia dan tinggal dipakai (fungsi standar) dan dapat pula dibuat sendiri sebagai fungsi baru (user defined function).
Semua fungsi ditulis nama yang diikuti kurung ()
Contoh
Fungsi power(a,b) untuk menghitung a pangkat b
Fungsi left(st,n) untuk mengambil n huruf depan/terkiri dari st
Contoh pemanggilan :
Select power(2,4) akan menampilkan 16
2 pangkat 4, yaitu 2 x 2 x 2 x 2 = 16
Update mhs set inisial=left(namadepan,1)+left(namabelakang,1)
Mengisi kolom inisial pada tabel mhs dengan 2 huruf yang diambil
dari 1 huruf namadepan dan 1 huruf namabelakang
select nama, ‘Hari Lahir ‘=datepart(dw,tgllahir) from mhs
Menampilkan nama dan hari lahir semua data mahasiswa
-------------------------------------------------------------------------------------
Kelompok Fungsi
Fungsi standar yang tersedia, dikelompokkan sebagai berikut
• Fungsi untuk Konfigurasi
• Fungsi untuk manipulasi kursor
• Fungsi untuk tanggal & jam
• Fungsi Matematika
• Fungsi Agregat
• Fungsi Metadata
• Fungsi untuk Security
• Fungsi untuk manipulasi string
• Fungsi untuk Sistem
• Fungsi untuk statistic
• Fungsi untuk teks & gambar
-------------------------------------------------------------------------------------
Berikut ini ditabelkan kegunaan dari beberapa fungsi yang penting untuk diketahui
No Sintaks Pemanggilan Arti/hasil
FUNGSI STRING
1 ASCII('C') Nomor ASCII dari karakter ‘C’
2 Char(65) Karakter dari ASCII bernomor 65
3 charindex('E','hello') Posisi ‘E’ dalam kata ‘hello’
4 left('HELLO',3) 3 huruf terkiri dari kata ‘HELLO’
5 ltrim(' Hello') Membuang spasi di kiri
6 right('hello',3) 3 huruf terkanan dari kata ‘HELLO’
7 rtrim('Hello ') Membuang spasi di kanan
8 len('Hello') Panjang/jumlah huruf dari kata ‘HELLO’
9 lower('HELLO') Merubah ke huruf kecil
10 patindex('%BOX%','ACTION BOX') Posisi kata ‘BOX’ dalam ‘ACTION BOX’
11 reverse('HELLO') Membalik susunan huruf/depan ke belakang dst
12 space(5) Membentuk spasi sebanyak 5
13 str(123.45,6,0) Merubah ke string dengan 6 digit tanpa spasi
14 stuff('hello',2,2,'i') Mengganti huruf kedua dengan huruf ‘i’
15 substring('hello',2,2) Mengambil 2 huruf mulai huruf ke 2
16 upper('hello') Merubah ke huruf besar
FUNGSI TANGGAL
1 getdate() Mengambil tanggal lengkap hari ini
2 datepart(dd,getdate()) Mengambil bagian tanggal dari hari ini
3 datename(dw,'1980-06-11') Menghasilkan nama hari dari 11 juni 1980
4 dateadd(yy,2,getdate()) Menambah 2 tahun dari hari ini
5 datediff(dd,'1980-06-11',getdate()) Menghitung selisih hari (umur sejak 11 jun 80)
FUNGSI MATEMATIK
1 abs(-25) Mengambil nilai absolute (tanpa negatif)
2 sin(pi()/2) Menghitung sinus sudut 90 derajat (pi=180)
3 exp(1) Menghitung bilangan e pangkat 1
4 degrees(pi()/2) Mengkonversi dari pi/2 radian ke derajat
5 radians(180) Mengkonversi 180 derajat ke radian
6 power(2,4) Menghitung 2 pangkat 4
7 floor(90.7) Membulatkan ke bawah
8 sign(90) Menghasilkan tanda bilangan (pos=1,neg=-1)
9 rand(90) Menghasilkan bil acak dengan bil pembangkit 90
10 round(1234.567,2) Membulatkan ke 2 angka belakang koma
FUNGSI SYSTEM & METADATA
1 host_id() Menghasilkan ID dari host
2 host_name() Menghasilkan nama dari host
3 suser_sname() Menghasilkan system username yg sdg aktif
4 user_id() Menghasilkan user ID yang sedang aktif
5 user_name() Menghasilkan username yang sedang aktif
6 db_id() Menghasilkan database ID yang sedang aktif
7 db_name() Menghasilkan database name yang sedang aktif
8 object_id('Authors') Menghasilkan ID dari object bernama ‘Authors’
9 object_name('629577281') Menghasilkan nama object yang ber ID=…
Untuk dapat memahami penggunaan beberapa fungsi standar dapat dicoba beberapa contoh berikut :
select ' 1. ',ascii('ABC')
select ' 2. ',char(65)
select ' 3. ',charindex('E','hello')
select ' 4. ',left('HELLO',3)
select ' 5. ','A'+ltrim(' Hello')
select ' 6. ',right('hello',3)
select ' 7. ',rtrim('Hello ')+'A'
select ' 8. ',len('Hello')
select ' 9. ',lower('HELLO')
select '10. ',patindex('%BOX%','ACTION BOX')
select '11. ',reverse('HELLO')
select '12. ','A'+space(5)+'B'
select '13. ',str(123.45,6,0)
select '14. ',stuff('hello',2,2,'i')
select '15. ',substring('hello',2,2)
select '16. ',upper('hello')
select '17. ',getdate()
select '18. ',datepart(dd,getdate())
select '19. ',datepart(mm,getdate())
select '20. ',datepart(yy,getdate())
select '21. ',datepart(qq,getdate())
select '22. ',datepart(ww,getdate())
select '23. ',datepart(dy,getdate())
select '24. ',datepart(dw,getdate())
select '25. ',datename(mm,getdate())
select '26. ',datename(dw,getdate())
select '27. ',datename(dw,'1980-06-11')
select '28. ',dateadd(dd,2,getdate())
select '29. ',dateadd(mm,2,getdate())
select '30. ',dateadd(yy,2,getdate())
select '31. ',datediff(dd,'1980-06-11',getdate())
select '32. ',datediff(mm,'1980-06-11',getdate())
select '33. ',datediff(yy,'1980-06-11',getdate())
select '34. ',abs(-25)
select '35. ',sin(pi()/2)
select '36. ',exp(1)
select '37. ',degrees(pi()/2)
select '38. ',radians(180)
select '39. ',power(2,4)
select '40. ',floor(90.7)
select '41. ',sign(90)
select '42. ',rand(90)
select '43. ',round(1234.567,2)
select '44. ',host_id()
select '45. ',host_name()
select '46. ',suser_sname()
select '47. ',user_id()
select '48. ',user_name()
select '49. ',db_id()
select '50. ',db_name()
select '51. ',object_id('authors')
select '52. ',object_name('629577281')
-------------------------------------------------------------------------------------
Membuat Fungsi Sendiri
Ada beberapa jenis fungsi yang dapat kita buat sendiri (User Defined Function), yaitu :
1. Inline Table – valued Function
2. Multi-statement– valued Function
3. Scalar– valued Function
-------------------------------------------------------------------------------------
Contoh Soal dan Jawaban
Cari jumlah barang di faktur tertentu dengan parameter NoFJ(tipenya integer)
CREATE FUNCTION hitungjumlahbarang
(
@nofj varchar(12)
)
RETURNS int
AS
BEGIN
DECLARE @Result int
SELECT @Result = count(kodebarang) from FjDet
where NoFJ = @nofj
RETURN @Result
END
GO
select distinct nofj,dbo.hitungjumlahbarang(nofj) from fjdet
-------------------------------------------------------------------------------------
Cari Total Qty*Harga dari tabel FJDet, parameternya NoFJ(tipenya money)
CREATE FUNCTION total
(
@nofj varchar(12)
)
RETURNS money
AS
BEGIN
DECLARE @Result money
SELECT @Result = sum(qty*harga) from FjDet
where NoFJ = @nofj
RETURN @Result
END
GO
select distinct nofj,dbo.total(nofj) from fjdet
-------------------------------------------------------------------------------------
Jumlah Nama Barang mengandung kata tertentu pada tabel Barang, parameternya Nama Barang (tipenya integer)
CREATE FUNCTION jumlahnamabarang
(
@nama varchar(50)
)
RETURNS int
AS
BEGIN
DECLARE @Result int
SELECT @Result = count(kodebarang) from Barang
where namabarang like '%' + @nama + '%'
RETURN @Result
END
GO
select kodebarang,namabarang from barang
where namabarang like '%Roda%'
select distinct dbo.jumlahnamabarang('Roda') from Barang
-------------------------------------------------------------------------------------
Jumlah Barang yang stoknya kurang dari x, parameternya stok barang(tipenya integer).
CREATE FUNCTION hitungstok
(
@stok int
)
RETURNS int
AS
BEGIN
DECLARE @Result int
SELECT @Result = count(kodebarang) from Barang
where stok < @stok
RETURN @Result
END
GO
select kodebarang,dbo.hitungstok(0) from barang
-------------------------------------------------------------------------------------
Jumlah pelanggan yang piutangnya lebih besar dari x, parameternya Piutang( tipenya integer).
alter FUNCTION hitungpelanggan
(
@stok money
)
RETURNS int
AS
BEGIN
DECLARE @Result int
SELECT @Result = count(kodepelanggan) from Pelanggan
where piutang > @stok
RETURN @Result
END
GO
select dbo.hitungpelanggan(2000000)
Definisi Fungsi
Fungsi adalah bagian dari perintah/statement yang merubah beberapa nilai masukan
menjadi sebuah nilai baru (keluaran/hasil).
Beberapa fungsi telah tersedia dan tinggal dipakai (fungsi standar) dan dapat pula dibuat sendiri sebagai fungsi baru (user defined function).
Semua fungsi ditulis nama yang diikuti kurung ()
Contoh
Fungsi power(a,b) untuk menghitung a pangkat b
Fungsi left(st,n) untuk mengambil n huruf depan/terkiri dari st
Contoh pemanggilan :
Select power(2,4) akan menampilkan 16
2 pangkat 4, yaitu 2 x 2 x 2 x 2 = 16
Update mhs set inisial=left(namadepan,1)+left(namabelakang,1)
Mengisi kolom inisial pada tabel mhs dengan 2 huruf yang diambil
dari 1 huruf namadepan dan 1 huruf namabelakang
select nama, ‘Hari Lahir ‘=datepart(dw,tgllahir) from mhs
Menampilkan nama dan hari lahir semua data mahasiswa
-------------------------------------------------------------------------------------
Kelompok Fungsi
Fungsi standar yang tersedia, dikelompokkan sebagai berikut
• Fungsi untuk Konfigurasi
• Fungsi untuk manipulasi kursor
• Fungsi untuk tanggal & jam
• Fungsi Matematika
• Fungsi Agregat
• Fungsi Metadata
• Fungsi untuk Security
• Fungsi untuk manipulasi string
• Fungsi untuk Sistem
• Fungsi untuk statistic
• Fungsi untuk teks & gambar
-------------------------------------------------------------------------------------
Berikut ini ditabelkan kegunaan dari beberapa fungsi yang penting untuk diketahui
No Sintaks Pemanggilan Arti/hasil
FUNGSI STRING
1 ASCII('C') Nomor ASCII dari karakter ‘C’
2 Char(65) Karakter dari ASCII bernomor 65
3 charindex('E','hello') Posisi ‘E’ dalam kata ‘hello’
4 left('HELLO',3) 3 huruf terkiri dari kata ‘HELLO’
5 ltrim(' Hello') Membuang spasi di kiri
6 right('hello',3) 3 huruf terkanan dari kata ‘HELLO’
7 rtrim('Hello ') Membuang spasi di kanan
8 len('Hello') Panjang/jumlah huruf dari kata ‘HELLO’
9 lower('HELLO') Merubah ke huruf kecil
10 patindex('%BOX%','ACTION BOX') Posisi kata ‘BOX’ dalam ‘ACTION BOX’
11 reverse('HELLO') Membalik susunan huruf/depan ke belakang dst
12 space(5) Membentuk spasi sebanyak 5
13 str(123.45,6,0) Merubah ke string dengan 6 digit tanpa spasi
14 stuff('hello',2,2,'i') Mengganti huruf kedua dengan huruf ‘i’
15 substring('hello',2,2) Mengambil 2 huruf mulai huruf ke 2
16 upper('hello') Merubah ke huruf besar
FUNGSI TANGGAL
1 getdate() Mengambil tanggal lengkap hari ini
2 datepart(dd,getdate()) Mengambil bagian tanggal dari hari ini
3 datename(dw,'1980-06-11') Menghasilkan nama hari dari 11 juni 1980
4 dateadd(yy,2,getdate()) Menambah 2 tahun dari hari ini
5 datediff(dd,'1980-06-11',getdate()) Menghitung selisih hari (umur sejak 11 jun 80)
FUNGSI MATEMATIK
1 abs(-25) Mengambil nilai absolute (tanpa negatif)
2 sin(pi()/2) Menghitung sinus sudut 90 derajat (pi=180)
3 exp(1) Menghitung bilangan e pangkat 1
4 degrees(pi()/2) Mengkonversi dari pi/2 radian ke derajat
5 radians(180) Mengkonversi 180 derajat ke radian
6 power(2,4) Menghitung 2 pangkat 4
7 floor(90.7) Membulatkan ke bawah
8 sign(90) Menghasilkan tanda bilangan (pos=1,neg=-1)
9 rand(90) Menghasilkan bil acak dengan bil pembangkit 90
10 round(1234.567,2) Membulatkan ke 2 angka belakang koma
FUNGSI SYSTEM & METADATA
1 host_id() Menghasilkan ID dari host
2 host_name() Menghasilkan nama dari host
3 suser_sname() Menghasilkan system username yg sdg aktif
4 user_id() Menghasilkan user ID yang sedang aktif
5 user_name() Menghasilkan username yang sedang aktif
6 db_id() Menghasilkan database ID yang sedang aktif
7 db_name() Menghasilkan database name yang sedang aktif
8 object_id('Authors') Menghasilkan ID dari object bernama ‘Authors’
9 object_name('629577281') Menghasilkan nama object yang ber ID=…
Untuk dapat memahami penggunaan beberapa fungsi standar dapat dicoba beberapa contoh berikut :
select ' 1. ',ascii('ABC')
select ' 2. ',char(65)
select ' 3. ',charindex('E','hello')
select ' 4. ',left('HELLO',3)
select ' 5. ','A'+ltrim(' Hello')
select ' 6. ',right('hello',3)
select ' 7. ',rtrim('Hello ')+'A'
select ' 8. ',len('Hello')
select ' 9. ',lower('HELLO')
select '10. ',patindex('%BOX%','ACTION BOX')
select '11. ',reverse('HELLO')
select '12. ','A'+space(5)+'B'
select '13. ',str(123.45,6,0)
select '14. ',stuff('hello',2,2,'i')
select '15. ',substring('hello',2,2)
select '16. ',upper('hello')
select '17. ',getdate()
select '18. ',datepart(dd,getdate())
select '19. ',datepart(mm,getdate())
select '20. ',datepart(yy,getdate())
select '21. ',datepart(qq,getdate())
select '22. ',datepart(ww,getdate())
select '23. ',datepart(dy,getdate())
select '24. ',datepart(dw,getdate())
select '25. ',datename(mm,getdate())
select '26. ',datename(dw,getdate())
select '27. ',datename(dw,'1980-06-11')
select '28. ',dateadd(dd,2,getdate())
select '29. ',dateadd(mm,2,getdate())
select '30. ',dateadd(yy,2,getdate())
select '31. ',datediff(dd,'1980-06-11',getdate())
select '32. ',datediff(mm,'1980-06-11',getdate())
select '33. ',datediff(yy,'1980-06-11',getdate())
select '34. ',abs(-25)
select '35. ',sin(pi()/2)
select '36. ',exp(1)
select '37. ',degrees(pi()/2)
select '38. ',radians(180)
select '39. ',power(2,4)
select '40. ',floor(90.7)
select '41. ',sign(90)
select '42. ',rand(90)
select '43. ',round(1234.567,2)
select '44. ',host_id()
select '45. ',host_name()
select '46. ',suser_sname()
select '47. ',user_id()
select '48. ',user_name()
select '49. ',db_id()
select '50. ',db_name()
select '51. ',object_id('authors')
select '52. ',object_name('629577281')
-------------------------------------------------------------------------------------
Membuat Fungsi Sendiri
Ada beberapa jenis fungsi yang dapat kita buat sendiri (User Defined Function), yaitu :
1. Inline Table – valued Function
2. Multi-statement– valued Function
3. Scalar– valued Function
-------------------------------------------------------------------------------------
Contoh Soal dan Jawaban
Cari jumlah barang di faktur tertentu dengan parameter NoFJ(tipenya integer)
CREATE FUNCTION hitungjumlahbarang
(
@nofj varchar(12)
)
RETURNS int
AS
BEGIN
DECLARE @Result int
SELECT @Result = count(kodebarang) from FjDet
where NoFJ = @nofj
RETURN @Result
END
GO
select distinct nofj,dbo.hitungjumlahbarang(nofj) from fjdet
-------------------------------------------------------------------------------------
Cari Total Qty*Harga dari tabel FJDet, parameternya NoFJ(tipenya money)
CREATE FUNCTION total
(
@nofj varchar(12)
)
RETURNS money
AS
BEGIN
DECLARE @Result money
SELECT @Result = sum(qty*harga) from FjDet
where NoFJ = @nofj
RETURN @Result
END
GO
select distinct nofj,dbo.total(nofj) from fjdet
-------------------------------------------------------------------------------------
Jumlah Nama Barang mengandung kata tertentu pada tabel Barang, parameternya Nama Barang (tipenya integer)
CREATE FUNCTION jumlahnamabarang
(
@nama varchar(50)
)
RETURNS int
AS
BEGIN
DECLARE @Result int
SELECT @Result = count(kodebarang) from Barang
where namabarang like '%' + @nama + '%'
RETURN @Result
END
GO
select kodebarang,namabarang from barang
where namabarang like '%Roda%'
select distinct dbo.jumlahnamabarang('Roda') from Barang
-------------------------------------------------------------------------------------
Jumlah Barang yang stoknya kurang dari x, parameternya stok barang(tipenya integer).
CREATE FUNCTION hitungstok
(
@stok int
)
RETURNS int
AS
BEGIN
DECLARE @Result int
SELECT @Result = count(kodebarang) from Barang
where stok < @stok
RETURN @Result
END
GO
select kodebarang,dbo.hitungstok(0) from barang
-------------------------------------------------------------------------------------
Jumlah pelanggan yang piutangnya lebih besar dari x, parameternya Piutang( tipenya integer).
alter FUNCTION hitungpelanggan
(
@stok money
)
RETURNS int
AS
BEGIN
DECLARE @Result int
SELECT @Result = count(kodepelanggan) from Pelanggan
where piutang > @stok
RETURN @Result
END
GO
select dbo.hitungpelanggan(2000000)
Stored Procedure
Penggunaan Store Procedure dalam SQL
Dalam tulisan ini akan membahas tentang penggunaaan store procedure dalam Microsoft SQL Server. Untuk tulisan berikutnya akan di lanjutkan dengan penggunaan store procedure secara lebih mendalam.
Apakah Store Procedure itu?
Store Procedure adalah kumpulan Pre-defined Transact-SQL yang digunakan untuk melakukan tugas/task khusus. Dalam Store Procedure dapat berisi beberapa statement dan setiap statement di kelompokan untuk satu object database.
Bagaimana cara membuat store procedure?
Untuk membuat store procedure, kita hanya menjalankan perintah “create procedure” diikuti dengan SQL script. Kita bisa meembuat store procedure melalui Query Analyzer pada MS SQL Server, atau dengan menggunakan menu New Procedure pada Enterprise Manager.
Kerangka sederhana Store Procedure
CREATE PROC procedure_name
[ { @parameter data_type }
]
AS sql_statement
Store Procedure terdiri dari:
1. Statemen CREATE PROC {PROCEDURE}
2. Nama Procedure;
3. Parameter list
4. SQL statement.
Banyak option lainnya dalam mendefinisikan store procedure, dalam tulisan ini hanya beberapa saja yang disebutkan, hanya sebagai gambaran awal dalam membuat store procedure.
Keuntungan menggunakan Store Procedure!
Penggunaan store procedure dalam sebuah pemrograman database memiliki beberapa keuntungan atau kelebihan sebagai berikut:
1. Performance
Semua perintah SQL, yang kita kirimkan ke database server melalui kumpulan action yang disebut dengan execution. Hal ini menjadikan perintah SQL yang kita kirimkan melalui beberapa proses sebelum data dikembalikan ke client.
User mengirimkan request untuk mengeksekusi store procedure.SQL Server kemudian mengecek apakah ada syntax error. Mengidentifikasi dan melakukan pengecekan alias pada FROM clausa.Membuat query plan. Meng-compile dan kemudian mengeksekusi query plan untuk kemudian mengembalikan data yang diminta.
Ada banyak proses yang tidak kita ketahui yang dijalankan, kemudian apakah dengan menggunakan store procedure kita akan mem bypass hal tersebut ?
Di satu sisi, ya. Versi terdahulu pada SQL Server menyimpan execution plan yang telah dieksekusi dalam system tables, membuat sebagian pre-compiled. Hal ini meningkatkan performance, karena server tidak melakukan kompilasi store procedure setiap kali ia dipanggil.
Pada versi terbaru SQL server, ada banyak perubahan pemrosesan statement. Store Procedure disimpan dalam procedure cache ketika di panggil, membuat subsequent lebih cepat dipanggil.
2. Security
Store Procedure memberikan keuntungan yang baik dalam hal security. Dengan menggunakan store procedure, kita bisa memberikan permission untuk user yang ditunjuk untuk dapat mengakses data, menekan immense coding yang perlu kita lakukan pada Application Client. Ini adalah cara terbaik untuk mengontrol akses ke data.
3. Modifications/Maintenance
Jika kita menggunakan store procedure untuk mengakses database, setiap perubahan pada database dapat dipantau berdasarkan client application. Hal ini dimungkinkan karena kita dapat tahu persis dimana data diakses, dan kita juga tahu dimana kita harus melakukan perubahan. Hal ini berarti kita tidak perlu pusing kepala dengan ribuan baris source code pada client application untuk menemukan baris mana yang perlu dirubah.
4. Minimal processing at the client.
Ketika membuat aplikasi client/server, normalnya adalah client yang bertanggung jawab mengenai integritas data yang masuk ke dalam database. Mengelola Primary Key,Foreign Key, semuanya dilakukan oleh client, dan database server hanya bertugas menyimpan data yang di berikan client.
Dengan menggunakan store proocedure membantu kita untuk membuat batch perintah SQL, yang bisa digunakan untuk memanage transaction,constraints dll. Hanya sedikit daya yang ditulis pada client apllication, menjadikan aplikasi client menjadi lebih ringan. Aplikasi ini akan lebih terfokus pada menampilkan data untuk keperluan user dan aplikasi client tidak tau banyak mengenai database.
Sebagai contoh. Jika kita memiliki database yang berisi ribuan rows dan ratusan table. Kita memerlukan beberapa perhitungan sebelum melakukan update pada setiap record. Jika kita mengambil data secara lengkap ke client, dan meminta client computer untuk memproses data secara lengkap, bisa dibayangkan apa yang ditimbulkan. Tetapi jika client bisa mengeksekusi store procedure, ketika kita telah selesai melakukan calculasi untuk melakukan update pada record.Client tidak perlu melakukan proses calculasi. Hal ini juga menekan proses yang terjadi di client, dan server yang menjalankan kalkulasinya.
5. Network traffic
Aplikasi pada client selalu me request/ mengirimkan data ke database server. Data ini dikirimkan sebagai packet dan dikirimkan ke jaringan(Network) ke server.
penggunaan store procedure. Untuk penjelasan syntax pada penggunaan store procedure akan di bahas pada tulisan berikutnya.
-------------------------------------------------------------------------------------
Contoh Stored Procedure
Insert
CREATE PROCEDURE InsertPelanggan
@pKodePelanggan nvarchar(10),
@pNamaPelanggan nvarchar(30),
@pPiutang money
AS
BEGIN
if(exists(select kodepelanggan from Pelanggan where kodepelanggan = @pKodepelanggan))
begin
Print 'Data Sudah Ada'
end
else
begin
insert into Pelanggan(KodePelanggan,NamaPelanggan,Piutang)
values(@pKodePelanggan,@pNamaPelanggan,@pPiutang)
Print 'Insert Berhasil'
end
END
GO
-------------------------------------------------------------------------------------
Update
CREATE PROCEDURE UpdatePelanggan
@pKodePelanggan nvarchar(10),
@pNamaPelanggan nvarchar(30),
@pPiutang money
AS
BEGIN
if(exists(select kodepelanggan from Pelanggan where kodepelanggan = @pKodepelanggan))
begin
update Pelanggan set NamaPelanggan = @pNamaPelanggan,
Piutang = @pPiutang
where KodePelanggan = @pKodePelanggan
Print 'Update Berhasil'
end
else
begin
Print 'Data Tidak Ditemukan'
end
END
GO
-------------------------------------------------------------------------------------
Delete
CREATE PROCEDURE DeletePelanggan
@pKodePelanggan nvarchar(10)
AS
BEGIN
if(exists(select kodepelanggan from Pelanggan where kodepelanggan = @pKodepelanggan))
begin
Delete from Pelanggan
where KodePelanggan = @pKodePelanggan
Print 'Update Berhasil'
end
else
begin
Print 'Data Tidak Ditemukan'
end
END
GO
-------------------------------------------------------------------------------------
Contoh Penggunaan Stored Procedure
exec insertpelanggan 'asd','asd',5000
exec updatepelanggan 'asd','TEST',500000
exec deletepelanggan 'asd'
Dalam tulisan ini akan membahas tentang penggunaaan store procedure dalam Microsoft SQL Server. Untuk tulisan berikutnya akan di lanjutkan dengan penggunaan store procedure secara lebih mendalam.
Apakah Store Procedure itu?
Store Procedure adalah kumpulan Pre-defined Transact-SQL yang digunakan untuk melakukan tugas/task khusus. Dalam Store Procedure dapat berisi beberapa statement dan setiap statement di kelompokan untuk satu object database.
Bagaimana cara membuat store procedure?
Untuk membuat store procedure, kita hanya menjalankan perintah “create procedure” diikuti dengan SQL script. Kita bisa meembuat store procedure melalui Query Analyzer pada MS SQL Server, atau dengan menggunakan menu New Procedure pada Enterprise Manager.
Kerangka sederhana Store Procedure
CREATE PROC procedure_name
[ { @parameter data_type }
]
AS sql_statement
Store Procedure terdiri dari:
1. Statemen CREATE PROC {PROCEDURE}
2. Nama Procedure;
3. Parameter list
4. SQL statement.
Banyak option lainnya dalam mendefinisikan store procedure, dalam tulisan ini hanya beberapa saja yang disebutkan, hanya sebagai gambaran awal dalam membuat store procedure.
Keuntungan menggunakan Store Procedure!
Penggunaan store procedure dalam sebuah pemrograman database memiliki beberapa keuntungan atau kelebihan sebagai berikut:
1. Performance
Semua perintah SQL, yang kita kirimkan ke database server melalui kumpulan action yang disebut dengan execution. Hal ini menjadikan perintah SQL yang kita kirimkan melalui beberapa proses sebelum data dikembalikan ke client.
User mengirimkan request untuk mengeksekusi store procedure.SQL Server kemudian mengecek apakah ada syntax error. Mengidentifikasi dan melakukan pengecekan alias pada FROM clausa.Membuat query plan. Meng-compile dan kemudian mengeksekusi query plan untuk kemudian mengembalikan data yang diminta.
Ada banyak proses yang tidak kita ketahui yang dijalankan, kemudian apakah dengan menggunakan store procedure kita akan mem bypass hal tersebut ?
Di satu sisi, ya. Versi terdahulu pada SQL Server menyimpan execution plan yang telah dieksekusi dalam system tables, membuat sebagian pre-compiled. Hal ini meningkatkan performance, karena server tidak melakukan kompilasi store procedure setiap kali ia dipanggil.
Pada versi terbaru SQL server, ada banyak perubahan pemrosesan statement. Store Procedure disimpan dalam procedure cache ketika di panggil, membuat subsequent lebih cepat dipanggil.
2. Security
Store Procedure memberikan keuntungan yang baik dalam hal security. Dengan menggunakan store procedure, kita bisa memberikan permission untuk user yang ditunjuk untuk dapat mengakses data, menekan immense coding yang perlu kita lakukan pada Application Client. Ini adalah cara terbaik untuk mengontrol akses ke data.
3. Modifications/Maintenance
Jika kita menggunakan store procedure untuk mengakses database, setiap perubahan pada database dapat dipantau berdasarkan client application. Hal ini dimungkinkan karena kita dapat tahu persis dimana data diakses, dan kita juga tahu dimana kita harus melakukan perubahan. Hal ini berarti kita tidak perlu pusing kepala dengan ribuan baris source code pada client application untuk menemukan baris mana yang perlu dirubah.
4. Minimal processing at the client.
Ketika membuat aplikasi client/server, normalnya adalah client yang bertanggung jawab mengenai integritas data yang masuk ke dalam database. Mengelola Primary Key,Foreign Key, semuanya dilakukan oleh client, dan database server hanya bertugas menyimpan data yang di berikan client.
Dengan menggunakan store proocedure membantu kita untuk membuat batch perintah SQL, yang bisa digunakan untuk memanage transaction,constraints dll. Hanya sedikit daya yang ditulis pada client apllication, menjadikan aplikasi client menjadi lebih ringan. Aplikasi ini akan lebih terfokus pada menampilkan data untuk keperluan user dan aplikasi client tidak tau banyak mengenai database.
Sebagai contoh. Jika kita memiliki database yang berisi ribuan rows dan ratusan table. Kita memerlukan beberapa perhitungan sebelum melakukan update pada setiap record. Jika kita mengambil data secara lengkap ke client, dan meminta client computer untuk memproses data secara lengkap, bisa dibayangkan apa yang ditimbulkan. Tetapi jika client bisa mengeksekusi store procedure, ketika kita telah selesai melakukan calculasi untuk melakukan update pada record.Client tidak perlu melakukan proses calculasi. Hal ini juga menekan proses yang terjadi di client, dan server yang menjalankan kalkulasinya.
5. Network traffic
Aplikasi pada client selalu me request/ mengirimkan data ke database server. Data ini dikirimkan sebagai packet dan dikirimkan ke jaringan(Network) ke server.
penggunaan store procedure. Untuk penjelasan syntax pada penggunaan store procedure akan di bahas pada tulisan berikutnya.
-------------------------------------------------------------------------------------
Contoh Stored Procedure
Insert
CREATE PROCEDURE InsertPelanggan
@pKodePelanggan nvarchar(10),
@pNamaPelanggan nvarchar(30),
@pPiutang money
AS
BEGIN
if(exists(select kodepelanggan from Pelanggan where kodepelanggan = @pKodepelanggan))
begin
Print 'Data Sudah Ada'
end
else
begin
insert into Pelanggan(KodePelanggan,NamaPelanggan,Piutang)
values(@pKodePelanggan,@pNamaPelanggan,@pPiutang)
Print 'Insert Berhasil'
end
END
GO
-------------------------------------------------------------------------------------
Update
CREATE PROCEDURE UpdatePelanggan
@pKodePelanggan nvarchar(10),
@pNamaPelanggan nvarchar(30),
@pPiutang money
AS
BEGIN
if(exists(select kodepelanggan from Pelanggan where kodepelanggan = @pKodepelanggan))
begin
update Pelanggan set NamaPelanggan = @pNamaPelanggan,
Piutang = @pPiutang
where KodePelanggan = @pKodePelanggan
Print 'Update Berhasil'
end
else
begin
Print 'Data Tidak Ditemukan'
end
END
GO
-------------------------------------------------------------------------------------
Delete
CREATE PROCEDURE DeletePelanggan
@pKodePelanggan nvarchar(10)
AS
BEGIN
if(exists(select kodepelanggan from Pelanggan where kodepelanggan = @pKodepelanggan))
begin
Delete from Pelanggan
where KodePelanggan = @pKodePelanggan
Print 'Update Berhasil'
end
else
begin
Print 'Data Tidak Ditemukan'
end
END
GO
-------------------------------------------------------------------------------------
Contoh Penggunaan Stored Procedure
exec insertpelanggan 'asd','asd',5000
exec updatepelanggan 'asd','TEST',500000
exec deletepelanggan 'asd'
View & Join
VIEW
VIEW adalah query yang disimpan, bentuknya menyerupai table. View bersifat logic, artinya tidak memiliki data fisik karena data fisiknya ada di table. View adalah cara menampilkan/menyajikan data yang ada di table.
Ada beberapa bentuk view :
1. Diambil dari 1 tabel (seluruh kolom, sebagian kolom, calculated colomn, seluruh record, sebagian record)
2. Diambil dari beberapa table (syaratnya harus ada relasi antar table)
a. INNER JOIN
b. LEFT OUTER JOIN
c. RIGHT OUTER JOIN
d. FULL OUTER JOIN
e. UNION (syaratnya field yang di-UNION harus sejenis)
3. Hasil dari Pengelompokkan Data (Group By)
4. Hasil dari Pivot Table (Cross Table)
-------------------------------------------------------------------------------------
Definisi Join
Join adalah operasi untuk mengambil informasi dari 2 tabel atau lebih dalam 1 waktu. Dengan join baris data dari satu tabel dihubungkan dengan baris data pada tabel lain berdasarkan kolom tertentu.
Klasifikasi Join
• inner join
• outer join
• cross join
• equi join
• natural join
• self join
Dari beberapa macam join tersebut yang banyak digunakan adalah inner join dan outer join
Inner Join
Inner join adalah default dari join, digunakan mendapatkan data dari tabel lain berdasarkan kolom yang dihubungkan. Bila tidak ditemukan maka baris data tersebut dibatalkan.
Syntax :
Select … from tabelA [inner] join tabelB
on tabelA.namakolom operator tabelB.namakolom
Keterangan :
• Kata inner adalah opsional (boleh ditulis ataupun tidak)
• Klausa where, order by dll dapat disertakan
Contoh :
Menampilkan ID buku, Judul buku dan nama penerbitnya dari tabel Title & Publishers
Pada tabel Title tidak ada nama penerbit, tetapi ada kode penerbit/Pub_Id yang namanya dapat diperoleh dari tabel Publishers berdasarkan Pub_Id
Select t.Title_Id, t.Title, p.Pub_name from Titles t join Publishers p
on t.Pub_Id = p.Pub_Id
Outer Join
Outer join adalah join yang digunakan memasangkan data dari satu tabel dengan tabel lain berdasarkan kolom yang dihubungkan walaupun salah satu kolom penghubungnya tidak berpasangan.
Syntax :
Select … from tabelA [left|right] outer join tabelB
on tabelA.namakolom operator tabelB.namakolom
Keterangan :
• Kata outer harus ditulis dan didahului kata left atau right
• Kata left dipilih bila nama tabel disebelah kiri kata join yang menjadi acuan
• Kata right dipilih bila nama tabel disebelah kanan kata join yang menjadi acuan
• Klausa where, order by dll dapat disertakan
Contoh :
Menampilkan daftar penerbit & nama-nama pengarang yang berada di kota yang sama (semua penerbit ditampilkan walaupun tidak ada nama pengarang yang satu kota)
Select p.Pub_name, a.Au_Lname, a.Au_Fname from Publishers p
left outer join Authors a on p.City = a.City
dalam syntax lain :
Select p.Pub_name, a.Au_Lname, a.Au_Fname from Publishers p, Authors a
where p.City *= a.City
VIEW adalah query yang disimpan, bentuknya menyerupai table. View bersifat logic, artinya tidak memiliki data fisik karena data fisiknya ada di table. View adalah cara menampilkan/menyajikan data yang ada di table.
Ada beberapa bentuk view :
1. Diambil dari 1 tabel (seluruh kolom, sebagian kolom, calculated colomn, seluruh record, sebagian record)
2. Diambil dari beberapa table (syaratnya harus ada relasi antar table)
a. INNER JOIN
b. LEFT OUTER JOIN
c. RIGHT OUTER JOIN
d. FULL OUTER JOIN
e. UNION (syaratnya field yang di-UNION harus sejenis)
3. Hasil dari Pengelompokkan Data (Group By)
4. Hasil dari Pivot Table (Cross Table)
-------------------------------------------------------------------------------------
Definisi Join
Join adalah operasi untuk mengambil informasi dari 2 tabel atau lebih dalam 1 waktu. Dengan join baris data dari satu tabel dihubungkan dengan baris data pada tabel lain berdasarkan kolom tertentu.
Klasifikasi Join
• inner join
• outer join
• cross join
• equi join
• natural join
• self join
Dari beberapa macam join tersebut yang banyak digunakan adalah inner join dan outer join
Inner Join
Inner join adalah default dari join, digunakan mendapatkan data dari tabel lain berdasarkan kolom yang dihubungkan. Bila tidak ditemukan maka baris data tersebut dibatalkan.
Syntax :
Select … from tabelA [inner] join tabelB
on tabelA.namakolom operator tabelB.namakolom
Keterangan :
• Kata inner adalah opsional (boleh ditulis ataupun tidak)
• Klausa where, order by dll dapat disertakan
Contoh :
Menampilkan ID buku, Judul buku dan nama penerbitnya dari tabel Title & Publishers
Pada tabel Title tidak ada nama penerbit, tetapi ada kode penerbit/Pub_Id yang namanya dapat diperoleh dari tabel Publishers berdasarkan Pub_Id
Select t.Title_Id, t.Title, p.Pub_name from Titles t join Publishers p
on t.Pub_Id = p.Pub_Id
Outer Join
Outer join adalah join yang digunakan memasangkan data dari satu tabel dengan tabel lain berdasarkan kolom yang dihubungkan walaupun salah satu kolom penghubungnya tidak berpasangan.
Syntax :
Select … from tabelA [left|right] outer join tabelB
on tabelA.namakolom operator tabelB.namakolom
Keterangan :
• Kata outer harus ditulis dan didahului kata left atau right
• Kata left dipilih bila nama tabel disebelah kiri kata join yang menjadi acuan
• Kata right dipilih bila nama tabel disebelah kanan kata join yang menjadi acuan
• Klausa where, order by dll dapat disertakan
Contoh :
Menampilkan daftar penerbit & nama-nama pengarang yang berada di kota yang sama (semua penerbit ditampilkan walaupun tidak ada nama pengarang yang satu kota)
Select p.Pub_name, a.Au_Lname, a.Au_Fname from Publishers p
left outer join Authors a on p.City = a.City
dalam syntax lain :
Select p.Pub_name, a.Au_Lname, a.Au_Fname from Publishers p, Authors a
where p.City *= a.City
DDL & DML
Contoh query untuk CREATE table
create table Mahasiswa (
NIM char(8),
Tahun_Ajaran char(4),
Kode_Jurusan char(5),
Nama varchar(50),
Tempat_Lahir varchar(50),
Tanggal_Lahir datetime,
Alamat varchar(150),
Kota varchar(50),
No_Telepon varchar(12)
);
-------------------------------------------------------------------------------------
Menambah Kolom Pada Tabel
Melakukan perubahan struktur tabel seperti penambahan kolom Status_Mahasiswa pada tabel mahasiswa, perintah query menambah kolom adalah :
alter table Mahasiswa add Status_Mahasiswa char(12);
-------------------------------------------------------------------------------------
Merubah Type Data Kolom Pada Tabel
Perintah query untuk mengubah type data suatu tabel (misal mengubah tipe data Status_Mahasiswa dari char(12) menjadi varchar(12)) yaitu:
alter table Mahasiswa alter column Status_Mahasiswa varchar(12);
-------------------------------------------------------------------------------------
Menghapus Kolom Pada Tabel
Jika karena suatu kolom tidak diperlukan dan akan dihilangkan dari suatu tabel dikarenakan sesuatu hal maka perintah query untuk menghapus kolom tersebut yaitu:
alter table Mahasiswa drop column Status_Mahasiswa;
-------------------------------------------------------------------------------------
Menambah Constraint Primary Key Pada Kolom
Perintah query untuk menambah constraint Primary Key pada kolom suatu tabel adalah:
alter table Mahasiswa add constraint pk_Mahasiswa primary key(NIM);
-------------------------------------------------------------------------------------
Menambah Constraint Primary Key Pada Kolom Suatu Tabel
Perlu diketahui bahwa kolom yang akan dijadikan sebagai primary key harus dalam keadaan not null (tidak boleh kosong). Jadi jika kolom yang akan dijadikan primary key tidak dalam keadaan not null (boleh kosong) harus di ubah menjadi not null.
alter table Mahasiswa alter column NIM char(8) not null;
Perintah query untuk menambah constraint Primary Key pada kolom suatu tabel adalah:
alter table Mahasiswa add constraint pk_Mahasiswa primary key(NIM);
-------------------------------------------------------------------------------------
Menambah Constraint Foreign Key Pada Kolom Suatu Tabel
Misalkan, tabel Mahasiswa mereferensi ke tabel jurusan pada kolom Kode_Jurusan.
Maka perintah query untuk menghubungkannya adalah :
alter table Mahasiswa add constraint fk_Mahasiswa_ref_jurusan foreign key (Kode_Jurusan) references Jurusan(Kode_Jurusan);
-------------------------------------------------------------------------------------
Menambah Constraint Pada Kolom Suatu Tabel
Perintah query untuk menghapus constraint adalah :
alter table Mahasiswa drop constraint fk_Mahasiswa_ref_jurusan;
-------------------------------------------------------------------------------------
Menghapus Tabel Dengan Perintah SQL
Perintah query untuk menghapus suatu tabel adalah:
drop table Mahasiswa;
-------------------------------------------------------------------------------------
DML(Data Manipulation Language)
DML merupakan perintah yang sering dipergunakan dalam Transaksi SQL oleh database developer.
Perintah-perintah SQL yang tergolong DML antara lain :
1. INSERT
2. SELECT
3. UPDATE
4. DELETE
-------------------------------------------------------------------------------------
1. INSERT
Perintah insert dipergunakan untuk memasukan data kedalam tabel. Perintahnya adalah :
Insert into Mahasiswa (NIM, Tahun_Ajaran, Kode_Jurusan, Nama, Tempat_Lahir, Tanggal_Lahir, Alamat, Kota,No_Telepon)
values (‘10113025’, ‘2005’, ‘LB’, ’Dina Rukmantara’, ‘Bandung’ ,’08-20-1983’, ‘Jl. Sari Jadi’, ‘Bandung’, ‘081322442269’);
Catatan :
Dalam proses input matakuliah, jika tabel mahasiswa mereferensi ke tabel jurusan maka tabel jurusan harus memiliki data jurusan yang akan dipergunakan/diinputkan dalam tabel mahasiswa.
Perintah insert pada kolom-kolom tabel yang sudah default, data dapat dituliskan langsung sesuai dengan urutan kolom tanpa perlu menuliskan nama kolomnya seperti perintah dibawah :
Insert into Mahasiswa values (‘10113025’, ‘2005’, ‘LB’, ’Dina Rukmantara’, ‘Bandung’ ,’08-20-1983’, ‘Jl. Sari Jadi’, ‘Bandung’, ‘081322442269’);
Untuk memasukan hanya pada kolom-kolom tertentu bisa menggunakan perintah berikut :
Insert into Mahasiswa (NIM, Tahun_Ajaran, Kode_Jurusan, Nama)
values (‘10113025’, ‘2005’, ‘LB’, ’Dina Rukmantara’);
-------------------------------------------------------------------------------------
SELECT
Perintah ini adalah perintah yang paling sering dipergunakan karena kegunaannya adalah untuk membaca (query) isi tabel.
Select * from Mahasiswa ;
Perintah diatas apabila dieksekusi akan menampilkan seluruh data dalam tabel Mahasiswa.
Untuk menampilkan data-data tertentu dari satu atau beberapa tabel kita bisa menggunakan perintah WHERE yang berfungsi sebagai syarat kondisi dari data yang akan ditampilkan. Contoh:
Select * from Mahasiswa where Tahun_Ajaran = ‘2005’;
-------------------------------------------------------------------------------------
Data Mahasiswa yang akan muncul adalah Mahasiswa yang masuk pada tahun ajaran 2005.
MSSQL mengediakan sejumlah operator relasi untuk membuat suatu kondisi yang diinginkan.
Operator Penjelasan
= Sama
<> Tidak sama
< Lebih kecil
<= Lebih kecil atau sama
> Lebih besar
>= Lebih besar atau sama
Like Dapat digunakan dengan simbol %(wildcard)
In Dapat digunakan menggantikan relasi OR
Between Diantara dua kondisi
Not Between Tidak diantara dua kondisi
Null Bernilai kosong
Not Null Tidak bernilai kosong
& Untuk memasukan variabel
&& Untuk memasukan variabel dan nilai disimpan untuk query berikutnya
-------------------------------------------------------------------------------------
OPERATOR “>=” DAN “<=”
Dua kondisi, lebih besar sama dengan dan lebih kecil sama dengan.
Select * from Mahasiswa where Tahun_Ajaran>=2001 and Tahun_Ajaran<=2005;
LIKE
Untuk menyatakan suatu kondisi pencarian pada suatu kolom yang mengandung sebagian karakter yang akan ditampillkan.
Select * from Mahasiswa where Nama Like ‘Dina%’;
Perintah diatas digunakan untuk menampilkan data Mahasiswa yang memiliki nama awal nya Dian. %(Wildcard) untuk menyatakan karakter yang diabaikan.
IN
Perintah untuk menampilkan data yang memiliki nilai sama dengan nilai-nilai yang sudah ditentukan.
Select * from Mahasiswa where Tahun_Ajaran in (2005,2006);
BETWEEN
Perintah untuk menampilkan data yang memiliki nilai diantara dua nilai yang akan dicari.
Select * from Mahasiswa where Tahun_Ajaran Between 2001 and 2006;
Kebalikan dari between adalah not between.
Select * from Mahasiswa where Tahun_Ajaran Between 2001 and 2006;
NULL dan NOT NULL
Null menyatakan suatu nilai Null atau kosong, sedangkan Not Null menyatakan nilai Not Null atau tidak kosong .
Select * from Mahasiswa where No_Telepon IS NOT NULL;
AND
Operator AND berfungsi untuk menentukan bahwa semua kondisi harus dipenuhi untuk menampilkan hasil query.
Select * from Mahasiswa
where Tahun_Ajaran = ‘2005’ and Kode_Jurusan = ‘LB’;
Data yang akan ditampilkan hasil dari perintah diatas adalah yang memiliki Tahun Ajaran = 2005 dan Kode Jurusan = LB
OR
Operator OR berfungsi untuk menentukan bahwa salah satu kondisi harus dipenuhi.
Select * from Mahasiswa
where Tahun_Ajaran = ‘2005’ or Kode_Jurusan = ‘LB’;
GROUP BY
Dipergunakan untuk mengelompokan data.
Select kode_jurusan,count(NIM) from Mahasiswa group by kode_jurusan;
Count untuk menghitung jumlah barus dalam per group.
HAVING
Dipergunakan sebagai kondisi atau syarat yang digunakan setelah group by.
select kode_jurusan,count(NIM) from Mahasiswa
group by kode_jurusan
having count(NIM)>1;
ORDER BY
Operator ORDER BY dipergunakan untuk mengurutkan data yang secara default urutannya menaik atau Ascending (ASC). Selain bisa mengurutkan secara Ascending, kita juga bisa mengurutkan data secara menurun atau Descending (DESC).
Select * from Mahasiswa
where Tahun_Ajaran = ‘2005’ and Kode_Jurusan = ‘LB’ order by NIM asc;
DISTINCT
Ada kalanya, hasil query menampilkan row-row dengan nilai yang sama. Jika nilai yang sama ingin ditampilkan hanya satu kali, maka kita bisa menggunakan operator DISTINCT.
Select distinct(Kode_Jurusan) from Mahasiswa;
-------------------------------------------------------------------------------------
SELECT dari Beberapa Tabel
Select Mahasiswa.NIM, Mahasiswa.Nama, Jurusan.Nama_Jurusan from Mahasiswa,Jurusan where Mahasiswa.kode_jurusan = Jurusan.kode_jurusan;
Dalam perintah diatas bisa dipergunakan perintah alias untuk menggantikan nama objek sebenarnya. Seperti contoh dibawah ini :
Select Mhs.NIM, Mhs.Nama, Jrs.Nama_Jurusan from Mahasiswa Mhs,Jurusan Jrs where Mhs.kode_jurusan = Jrs.kode_jurusan;
Select Mhs.NIM, Mhs.Nama, Jrs.Nama_Jurusan,
Mhs.Tempat_Lahir+’, ’+cast(Mhs.Tanggal_Lahir as varchar) AS TTL
from Mahasiswa Mhs,Jurusan Jrs
where Mhs.kode_jurusan = Jrs.kode_jurusan;
-------------------------------------------------------------------------------------
UPDATE
Perintah update dipergunakan untuk melakukan perubahan data yang sudah disimpan. Perintahnya adalah :
Update Mahasiswa set Nama = ‘Aming Surya Praja’ where NIM =’10113025’;
DELETE
Perintah DELETE dipergunakan untuk menghapus data dalam tabel.
Delete from Mahasiswa where NIM =’10113025’;
create table Mahasiswa (
NIM char(8),
Tahun_Ajaran char(4),
Kode_Jurusan char(5),
Nama varchar(50),
Tempat_Lahir varchar(50),
Tanggal_Lahir datetime,
Alamat varchar(150),
Kota varchar(50),
No_Telepon varchar(12)
);
-------------------------------------------------------------------------------------
Menambah Kolom Pada Tabel
Melakukan perubahan struktur tabel seperti penambahan kolom Status_Mahasiswa pada tabel mahasiswa, perintah query menambah kolom adalah :
alter table Mahasiswa add Status_Mahasiswa char(12);
-------------------------------------------------------------------------------------
Merubah Type Data Kolom Pada Tabel
Perintah query untuk mengubah type data suatu tabel (misal mengubah tipe data Status_Mahasiswa dari char(12) menjadi varchar(12)) yaitu:
alter table Mahasiswa alter column Status_Mahasiswa varchar(12);
-------------------------------------------------------------------------------------
Menghapus Kolom Pada Tabel
Jika karena suatu kolom tidak diperlukan dan akan dihilangkan dari suatu tabel dikarenakan sesuatu hal maka perintah query untuk menghapus kolom tersebut yaitu:
alter table Mahasiswa drop column Status_Mahasiswa;
-------------------------------------------------------------------------------------
Menambah Constraint Primary Key Pada Kolom
Perintah query untuk menambah constraint Primary Key pada kolom suatu tabel adalah:
alter table Mahasiswa add constraint pk_Mahasiswa primary key(NIM);
-------------------------------------------------------------------------------------
Menambah Constraint Primary Key Pada Kolom Suatu Tabel
Perlu diketahui bahwa kolom yang akan dijadikan sebagai primary key harus dalam keadaan not null (tidak boleh kosong). Jadi jika kolom yang akan dijadikan primary key tidak dalam keadaan not null (boleh kosong) harus di ubah menjadi not null.
alter table Mahasiswa alter column NIM char(8) not null;
Perintah query untuk menambah constraint Primary Key pada kolom suatu tabel adalah:
alter table Mahasiswa add constraint pk_Mahasiswa primary key(NIM);
-------------------------------------------------------------------------------------
Menambah Constraint Foreign Key Pada Kolom Suatu Tabel
Misalkan, tabel Mahasiswa mereferensi ke tabel jurusan pada kolom Kode_Jurusan.
Maka perintah query untuk menghubungkannya adalah :
alter table Mahasiswa add constraint fk_Mahasiswa_ref_jurusan foreign key (Kode_Jurusan) references Jurusan(Kode_Jurusan);
-------------------------------------------------------------------------------------
Menambah Constraint Pada Kolom Suatu Tabel
Perintah query untuk menghapus constraint adalah :
alter table Mahasiswa drop constraint fk_Mahasiswa_ref_jurusan;
-------------------------------------------------------------------------------------
Menghapus Tabel Dengan Perintah SQL
Perintah query untuk menghapus suatu tabel adalah:
drop table Mahasiswa;
-------------------------------------------------------------------------------------
DML(Data Manipulation Language)
DML merupakan perintah yang sering dipergunakan dalam Transaksi SQL oleh database developer.
Perintah-perintah SQL yang tergolong DML antara lain :
1. INSERT
2. SELECT
3. UPDATE
4. DELETE
-------------------------------------------------------------------------------------
1. INSERT
Perintah insert dipergunakan untuk memasukan data kedalam tabel. Perintahnya adalah :
Insert into Mahasiswa (NIM, Tahun_Ajaran, Kode_Jurusan, Nama, Tempat_Lahir, Tanggal_Lahir, Alamat, Kota,No_Telepon)
values (‘10113025’, ‘2005’, ‘LB’, ’Dina Rukmantara’, ‘Bandung’ ,’08-20-1983’, ‘Jl. Sari Jadi’, ‘Bandung’, ‘081322442269’);
Catatan :
Dalam proses input matakuliah, jika tabel mahasiswa mereferensi ke tabel jurusan maka tabel jurusan harus memiliki data jurusan yang akan dipergunakan/diinputkan dalam tabel mahasiswa.
Perintah insert pada kolom-kolom tabel yang sudah default, data dapat dituliskan langsung sesuai dengan urutan kolom tanpa perlu menuliskan nama kolomnya seperti perintah dibawah :
Insert into Mahasiswa values (‘10113025’, ‘2005’, ‘LB’, ’Dina Rukmantara’, ‘Bandung’ ,’08-20-1983’, ‘Jl. Sari Jadi’, ‘Bandung’, ‘081322442269’);
Untuk memasukan hanya pada kolom-kolom tertentu bisa menggunakan perintah berikut :
Insert into Mahasiswa (NIM, Tahun_Ajaran, Kode_Jurusan, Nama)
values (‘10113025’, ‘2005’, ‘LB’, ’Dina Rukmantara’);
-------------------------------------------------------------------------------------
SELECT
Perintah ini adalah perintah yang paling sering dipergunakan karena kegunaannya adalah untuk membaca (query) isi tabel.
Select * from Mahasiswa ;
Perintah diatas apabila dieksekusi akan menampilkan seluruh data dalam tabel Mahasiswa.
Untuk menampilkan data-data tertentu dari satu atau beberapa tabel kita bisa menggunakan perintah WHERE yang berfungsi sebagai syarat kondisi dari data yang akan ditampilkan. Contoh:
Select * from Mahasiswa where Tahun_Ajaran = ‘2005’;
-------------------------------------------------------------------------------------
Data Mahasiswa yang akan muncul adalah Mahasiswa yang masuk pada tahun ajaran 2005.
MSSQL mengediakan sejumlah operator relasi untuk membuat suatu kondisi yang diinginkan.
Operator Penjelasan
= Sama
<> Tidak sama
< Lebih kecil
<= Lebih kecil atau sama
> Lebih besar
>= Lebih besar atau sama
Like Dapat digunakan dengan simbol %(wildcard)
In Dapat digunakan menggantikan relasi OR
Between Diantara dua kondisi
Not Between Tidak diantara dua kondisi
Null Bernilai kosong
Not Null Tidak bernilai kosong
& Untuk memasukan variabel
&& Untuk memasukan variabel dan nilai disimpan untuk query berikutnya
-------------------------------------------------------------------------------------
OPERATOR “>=” DAN “<=”
Dua kondisi, lebih besar sama dengan dan lebih kecil sama dengan.
Select * from Mahasiswa where Tahun_Ajaran>=2001 and Tahun_Ajaran<=2005;
LIKE
Untuk menyatakan suatu kondisi pencarian pada suatu kolom yang mengandung sebagian karakter yang akan ditampillkan.
Select * from Mahasiswa where Nama Like ‘Dina%’;
Perintah diatas digunakan untuk menampilkan data Mahasiswa yang memiliki nama awal nya Dian. %(Wildcard) untuk menyatakan karakter yang diabaikan.
IN
Perintah untuk menampilkan data yang memiliki nilai sama dengan nilai-nilai yang sudah ditentukan.
Select * from Mahasiswa where Tahun_Ajaran in (2005,2006);
BETWEEN
Perintah untuk menampilkan data yang memiliki nilai diantara dua nilai yang akan dicari.
Select * from Mahasiswa where Tahun_Ajaran Between 2001 and 2006;
Kebalikan dari between adalah not between.
Select * from Mahasiswa where Tahun_Ajaran Between 2001 and 2006;
NULL dan NOT NULL
Null menyatakan suatu nilai Null atau kosong, sedangkan Not Null menyatakan nilai Not Null atau tidak kosong .
Select * from Mahasiswa where No_Telepon IS NOT NULL;
AND
Operator AND berfungsi untuk menentukan bahwa semua kondisi harus dipenuhi untuk menampilkan hasil query.
Select * from Mahasiswa
where Tahun_Ajaran = ‘2005’ and Kode_Jurusan = ‘LB’;
Data yang akan ditampilkan hasil dari perintah diatas adalah yang memiliki Tahun Ajaran = 2005 dan Kode Jurusan = LB
OR
Operator OR berfungsi untuk menentukan bahwa salah satu kondisi harus dipenuhi.
Select * from Mahasiswa
where Tahun_Ajaran = ‘2005’ or Kode_Jurusan = ‘LB’;
GROUP BY
Dipergunakan untuk mengelompokan data.
Select kode_jurusan,count(NIM) from Mahasiswa group by kode_jurusan;
Count untuk menghitung jumlah barus dalam per group.
HAVING
Dipergunakan sebagai kondisi atau syarat yang digunakan setelah group by.
select kode_jurusan,count(NIM) from Mahasiswa
group by kode_jurusan
having count(NIM)>1;
ORDER BY
Operator ORDER BY dipergunakan untuk mengurutkan data yang secara default urutannya menaik atau Ascending (ASC). Selain bisa mengurutkan secara Ascending, kita juga bisa mengurutkan data secara menurun atau Descending (DESC).
Select * from Mahasiswa
where Tahun_Ajaran = ‘2005’ and Kode_Jurusan = ‘LB’ order by NIM asc;
DISTINCT
Ada kalanya, hasil query menampilkan row-row dengan nilai yang sama. Jika nilai yang sama ingin ditampilkan hanya satu kali, maka kita bisa menggunakan operator DISTINCT.
Select distinct(Kode_Jurusan) from Mahasiswa;
-------------------------------------------------------------------------------------
SELECT dari Beberapa Tabel
Select Mahasiswa.NIM, Mahasiswa.Nama, Jurusan.Nama_Jurusan from Mahasiswa,Jurusan where Mahasiswa.kode_jurusan = Jurusan.kode_jurusan;
Dalam perintah diatas bisa dipergunakan perintah alias untuk menggantikan nama objek sebenarnya. Seperti contoh dibawah ini :
Select Mhs.NIM, Mhs.Nama, Jrs.Nama_Jurusan from Mahasiswa Mhs,Jurusan Jrs where Mhs.kode_jurusan = Jrs.kode_jurusan;
Select Mhs.NIM, Mhs.Nama, Jrs.Nama_Jurusan,
Mhs.Tempat_Lahir+’, ’+cast(Mhs.Tanggal_Lahir as varchar) AS TTL
from Mahasiswa Mhs,Jurusan Jrs
where Mhs.kode_jurusan = Jrs.kode_jurusan;
-------------------------------------------------------------------------------------
UPDATE
Perintah update dipergunakan untuk melakukan perubahan data yang sudah disimpan. Perintahnya adalah :
Update Mahasiswa set Nama = ‘Aming Surya Praja’ where NIM =’10113025’;
DELETE
Perintah DELETE dipergunakan untuk menghapus data dalam tabel.
Delete from Mahasiswa where NIM =’10113025’;
Subscribe to:
Posts (Atom)