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
Pengelompokkan di atas dapat dilihat secara lebih jelas pada bagian Function.
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')
Join
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
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
Untuk membuat, klik pada bagian Function sbb :
Subquery
Definisi Subquery
Subquery adalah query yang menjadi bagian / ditulis dalam statement lain. Statement bisa berupa SELECT, INSERT, UPDATE ataupun DELETE. Satu atau lebih subquery yang berada dalam query yang lain disebut nested query.
Syntax untuk SELECT statement yang mengandung subquery:
Select … from namatabel
where namakolom operator [ALL|ANY] (select … from namatabel where …)
atau
Select … from namatabel
where [NOT] EXISTS (select … from namatabel where …)
Keterangan :
· Subquery ditulis dalam tanda kurung (…)
· Subquery tidak boleh menggunakan ORDER BY atau COMPUTE BY
· Bila subquery dengan hasil nilai tunggal digunakan operator: =, >, <, <=, >=, !=
· Bila subquery dengan hasil nilai tidak tunggal maka ALL|ANY harus disertakan (seperti: >ALL, >ANY, =ANY, <>ANY, <>ALL dll)
· =ANY dapat diganti dengan IN (sama dengan salah satu)
· <>ANY dapat diganti dengan NOT IN (tidak sama dengan salah satupun)
· [NOT] EXISTS untuk pengecekan, bila benar maka statement dikerjakan, semisal dg
If exists (select … from namatabel where …) Select … from namatabel
Contoh :
Menampilkan ID buku, Judul buku yang harganya lebih mahal dari buku yang berjudul ”Sushi, Anyone?” (ID=”TC7777”)
Select Title_Id, Title from Titles
where price > (select price from Titles where Title_Id=’TC7777’)
a. FHitungJumlahDokter : menghitung jumlah dokter jika dimasukkan spesialisasi. Keluaran : xx dalam bentuk integer
alter function No5b
(@p1 varchar(50))
returns int
as
begin
declare @result int
select @result = COUNT(id_dokter) from dbo.t_dokter
where spesialisasi = @p1
return @result
END
GO
select dbo.No5b ('umum')
b. FHitungJumlahPasienRawat : menghitung jumlah pasien yang sedang dirawat di RS, tanpa input. Keluaran xx dalam bentuk integer.
create function No5c ()
returns int
as
begin
declare @result int
select @result = COUNT(id_pasien) from dbo.t_rawat
where tgl_keluar is null
return @result
END
GO
select dbo.No5c()
1. Ganti kata Gear menjadi GEAR pada nama barang
create function No1
(
@p1 varchar(50)
)
returns varchar(50)
as
begin
declare @result varchar(50)
select @result = replace(@p1, 'Gear', 'GEAR')
return @result
END
GO
select dbo.No1(NamaBarang) from Barang
where NamaBarang like '%Gear%'
2. Cari jumlah barang di faktur tertentu dengan parameter NoFJ(tipenya integer)
create function No2
(@p1 varchar(50))
returns int
as
begin
declare @result int
select @result = COUNT(KodeBarang) from FJDet
where NoFJ = @p1
return @result
END
GO
select DISTINCT dbo.No2('FJ-0000001')
from FJDet
3. Cari Total Qty*Harga dari tabel FJDet, parameternya NoFJ(tipenya money)
create function No3
(@p1 varchar(50))
returns money
as
begin
declare @result money
select @result = SUM(Qty*Harga) from FJDet
where NoFJ = @p1
return @result
END
GO
select DISTINCT dbo.No3('FJ-0000001')
from FJDet
4. Jumlah Nama Barang mengandung kata tertentu pada tabel Baran, parameternya Nama Barang (tipenya integer)
alter function No4
(@p1 varchar(50))
returns int
as
begin
declare @result int
select @result = COUNT(KodeBarang) from Barang
where NamaBarang like '%' + @p1 + '%'
return @result
END
GO
select dbo.No4('gear')
LATIHAN FUNGSI
DIKETAHUI SEBUAH DATABASE SIPP, berisi data penjualan dan pembelian.
1. Seorang operator ingin mengetahui nomor pelanggan terakhir dan nomor pelanggan yang baru. Cari di table Pelanggan. Hasil disimpan di variable @NomorAkhir dan @NomorBaru
Jawaban model 1 :
Declare @NomorAkhir nvarchar(10), @NomorBaru nVarchar(10)
Declare @X int
select top 1 @NomorAkhir=kodepelanggan
from pelanggan
order by kodepelanggan desc
PRINT @NomorAkhir
Set @x = right(@NomorAkhir,5) + 1
print @x
Set @NomorBaru = 'C-'+ right('00000' + CONVERT(varchar,@X),5)
PRINT @NomorBaru
Jawaban Model 2 (menggunakan max):
Declare @NomorAkhir nvarchar(10), @NomorBaru nVarchar(10)
Declare @X int
select @NomorAkhir=max(kodepelanggan)
from pelanggan
PRINT @NomorAkhir
Set @x = right(@NomorAkhir,5) + 1
print @x
Set @NomorBaru = 'C-'+ right('00000' + CONVERT(varchar,@X),5)
PRINT @NomorBaru
2. Seorang operator ingin mengetahui nomor bukti penjualan terakhir dan nomor yang baru. Cari di table FJ. Hasil disimpan di variable @NomorAkhir dan @NomorBaru
Declare @NomorAkhir nvarchar(10), @NomorBaru nVarchar(10)
Declare @X int
select @NomorAkhir=max(noFJ)
from FJ
PRINT @NomorAkhir
Set @x = right(@NomorAkhir,7) + 1
print @x
Set @NomorBaru = 'FJ-'+ right('0000000' + CONVERT(varchar,@X),7)
PRINT @NomorBaru
3. Munculkan semua transaksi penjualan yang hari selasa
a. Tampilkan semua transaksi tersebut
b. Ada Berapa jumlah transaksi
c. Berapa totaltransaksi (dilihat dari TotalFaktur)
--Day of Week : 1=Minggu, 2=Senin, 3=Selasa
select NoFJ, TglFJ, TotalFaktur
from FJ
Where datepart(dw,tglfj)=3
Declare @JumlahFJ money, @TotalFJ money
select @JumlahFJ=Count(*), @TotalFJ=sum(TotalFaktur)
from FJ
Where datepart(dw,tglfj)=3
Select @JumlahFJ as JumlahFaktur, @TotalFJ as TotalFaktur
4. Seorang operator ingin mengetahui INISIAL PELANGGAN (Diambil dari huruf pertama dari setiap kata pada NamaPelanggan
Jawaban : mencari posisi spasi menggunakan charindex
select namapelanggan, left(namapelanggan,1) +
substring( namapelanggan, charindex(' ', namapelanggan)+1, 1)
from pelanggan
5. Tampilan TotalPenjualan Tanggal 1, 2, 3, 4, 5, 6, dan 7 Juli 2005. Simpan di variable @Total1, @Total2, @Total3, @Total4, @Total5, @Total6 dan @Total7
Declare @Total1 money, @Total2 money, @Total3 money, @Total4 money, @Total5 money, @Total6 money, @Total7 money
Select @Total1=Sum(TotalFaktur)
From FJ
Where TglFJ between '07/01/2005' and '07/01/2005 23:59:59'
Select @Total2=Sum(TotalFaktur)
From FJ
Where TglFJ between '07/02/2005' and '07/02/2005 23:59:59'
Select @Total3=Sum(TotalFaktur)
From FJ
Where TglFJ between '07/03/2005' and '07/03/2005 23:59:59'
Select @Total4=Sum(TotalFaktur)
From FJ
Where TglFJ between '07/04/2005' and '07/04/2005 23:59:59'
Select @Total5=Sum(TotalFaktur)
From FJ
Where TglFJ between '07/05/2005' and '07/05/2005 23:59:59'
Select @Total6=Sum(TotalFaktur)
From FJ
Where TglFJ between '07/06/2005' and '07/06/2005 23:59:59'
Select @Total7=Sum(TotalFaktur)
From FJ
Where TglFJ between '07/02/2005' and '07/02/2005 23:59:59'
Select @Total1 as Tg1, @Total2 as Tg2, @Total3 as Tg3, @Total4 as Tg4, @Total5 as Tg5, @Total6 as Tg6, @Total7 as Tg7
alter function No1
( @tglAwal datetime, @tglAkhir datetime)
returns money
as
begin
declare @result money
select @result = SUM(TotalFaktur)
from FJ
where TglFJ between @tglAwal and @tglAkhir + ' 23:59:59'
return @result
end
select dbo.No1 ('6/1/2005', '6/30/2005')
alter function No2
( @tglAwal datetime, @tglAkhir datetime)
returns int
as
begin
declare @result int
select @result = COUNT (DISTINCT(KodePelanggan))
from FJ
where TglFJ between @tglAwal and @tglAkhir + ' 23:59:59'
return @result
end
select dbo.No2 ('6/1/2005', '6/30/2005')
alter function No3
( @tglAwal datetime, @tglAkhir datetime)
returns int
as
begin
declare @result int
select @result = sum(fd.Qty)
from FJ f join FJDet fd on (f.NoFJ = fd.NoFJ)
where f.TglFJ between @tglAwal and @tglAkhir + ' 23:59:59'
return @result
end
select dbo.No3 ('6/1/2005', '6/30/2005')
create function No4
( @tglAwal datetime, @tglAkhir datetime)
returns money
as
begin
declare @result money
select @result = sum(fd.Qty*fd.Harga)
from FJ f join FJDet fd on (f.NoFJ = fd.NoFJ)
where f.TglFJ between @tglAwal and @tglAkhir + ' 23:59:59'
return @result
end
select dbo.No4 ('6/1/2005', '6/30/2005')
Tidak ada komentar:
Posting Komentar