MODUL II SQL MENGGUNAKAN SQL SERVER 2000 ------------------------SUBSTRING SUBQUERY AGGREGATE FUNCTION STORED PROCEDURE STORED FUNCTION DAN VIEW
by Abdul Malik Ikhsan
[email protected] http://samsonasik.wordpress.com
/* membuat database modul2 */ create database modul2 /* menggunakan database yang telah dibuat, dalam hal ini, modul2 */ use modul2 /* membuat table pegawai */ create table pegawai(nip char(7) not null primary key, nama_pegawai char(50), alamat_pegawai char(100),gapok float) /* describe table pegawai */ sp_columns pegawai /* membuat table absensi */ create table absensi(tgl datetime,nip char(7),keterangan char(5)) /* inputkan data pegawai ke tabel pegawai*/ insert into pegawai(nip,nama_pegawai,alamat_pegawai,gapok) values('p001','Abdul Malik Ikhsan','Jalan Jakarta No. 28',1000000) insert into pegawai(nip,nama_pegawai,alamat_pegawai,gapok) values('p002','Helmi Faisal Muttaqin','Jalan Labuhan No. 122',2000000) insert into pegawai(nip,nama_pegawai,alamat_pegawai,gapok) values('p003','Moh. Jakaria','Jalan Plesiran 31',3000000)
/* sekarang, inputkan data absensi ke tabel absensi */ insert into absensi(tgl,nip,keterangan) values('2007-0101','p003','hadir') insert into absensi(tgl,nip,keterangan) values('2007-0101','p002','izin') insert into absensi(tgl,nip,keterangan) values('2007-1213','p001','sakit') insert into absensi(tgl,nip,keterangan) values('2007-1012','p002','izin') insert into absensi(tgl,nip,keterangan) values('2007-1112','p002','hadir')
/* seleksi absensi pegawai yang hadir */ select * from absensi where keterangan = 'hadir'
/* seleksi absensi pegawai yang izin */ select * from absensi where keterangan = 'izin' /* seleksi absensi pegawai yang sakit */ select * from absensi where keterangan = 'sakit' /* seleksi absensi pegawai yang izin dan tanggal tertentu */ select * from absensi where keterangan = 'izin' and tgl= '2007-11-12' /* seleksi antara tanggal tertentu sampai tanggal tertentu penggunaan between ini juga berlaku untuk yang lain ... */ select * from absensi where tgl between '2007-01-01' and '2007-11-12'
/* seleksi absensi pegawai yang TIDAK hadir */ select * from absensi where keterangan!='hadir' /* seleksi absensi tabel tertentu dengan like atau = */ select tgl,nip,keterangan from absensi where tgl like '%2007%' select tgl,nip,keterangan from absensi where tgl = '200701-01' /* seleksi tanggal dengan format dd/mm/yy */ SELECT CONVERT(VARCHAR(8), tgl, 3) AS tgl from absensi /* seleksi tanggal dengan format dd/mm/yyyy */ SELECT CONVERT(VARCHAR(10), tgl, 103) AS tgl from absensi /* seleksi tanggal dengan format mm/dd/yy */ SELECT CONVERT(VARCHAR(8), tgl, 1) AS tgl from absensi /* seleksi tanggal dengan format mm/dd/yy */ SELECT CONVERT(VARCHAR(10), tgl, 101) AS tgl from absensi /* SUBSTRING = adalah pengambilan beberapa karakter dari field dengan panjang tertentu */ select substring(nama_pegawai,1,5) as subnama from pegawai select substring(CONVERT(VARCHAR(10),tgl,103),1,5) as tgl from absensi
select substring(CONVERT(VARCHAR(10),tgl,103),4,2) as tgl from absensi /* dengan substring,kita bisa tahu absensi untuk bulan tertentu di bawah ini, tidak menampilkan tahun, semua yang substring bulan = '01' diseleksi :) */ select * from absensi where substring(CONVERT(VARCHAR(10),tgl,103),4,2)='01' /* ----------------------------------------Sekarang, kita akan coba tampilkan data pegawai yang tidak hadir dengan format sebagai berikut */ /* Tgl --- Nama Pegawai --- Keterangan (-- mencari yang tidak hadir --) */ select a.tgl,b.nama_pegawai, a.keterangan from absensi a, pegawai b where b.nip=a.nip and a.keterangan!='hadir' /* Tgl --- Nama Pegawai --- Keterangan (-- mencari yang hadir dan izin saja --) */ select a.tgl,b.nama_pegawai, a.keterangan from absensi a, pegawai b where b.nip=a.nip and a.keterangan in('hadir','izin') /* SUBQUERY menampilkan query dalam query */ /* contoh : menampilkan data pegawai yang tidak melakukan absen /bolos/tidak ada keterangan */ select * from pegawai where nip<>all(select nip from absensi where tgl='2007-01-01') select * from pegawai where nip!=all(select nip from absensi where tgl='2007-01-01')
/* menampilkan data pegawai yang melakukan absen nip ada dalam record2 yang ada dalam table absensi */ select * from pegawai where nip=any(select nip from absensi where tgl='2007-01-01') /* AGGREGATE FUNCTION Berfungsi sebagai fungsi matematis dalam SQL */ /* =======COUNT========== Menghitung jumlah record */ select count(*) as jumlah from pegawai select count(*) as jumlah from pegawai where nip='p001'
/* ========MAX DAN MIN======== -------biasanya max digunakan untuk menghitung nilai tertinggi yang tipe datanya INTEGER atau FLOAT --------*/ /* menghitung nilai tertinggi untuk tipe string/char*/ select max(nip) as nipteratas from pegawai /* menghitung nilai terendah untuk tipe string/char*/ select min(nip) as nipterbawah from pegawai /* CONTOH REAL MAX DAN MIN */ select max(gapok) as maxgaji,min(gapok) as min_gaji from pegawai /* =========AVG========= Digunakan untuk menghitung rata-rata */ select avg(gapok) as rata2gaji from pegawai /* ==========SUM======== Digunakan untuk menjumlahkan nilai */ select sum(gapok) from pegawai
select sum(gapok) from pegawai where nama_pegawai like '%sa%'
/*STORED PROCEDURE */ /* Stored Procedure dapat dikatakan instruksi yang dilakukan untuk menyingkat statement query yang sering diulang2 */ create proc qpeg as select * from pegawai /* panggil procedure */ exec qpeg /* membuat procedure dengan memasukkan parameter */ create proc peg_nip @nip char(7) as select * from pegawai where nip=@nip exec peg_nip 'p001' /* memberi nilai default pada parameter */ create proc peg_nip2 @nip char(7)='p002' as select * from pegawai where nip=@nip /* kita tidak perlu menambahkan parameter karena sudah diberi nilai default */ exec peg_nip2 create proc peg_nip3 @nip char(7)='%p00%' as select * from pegawai where nip like @nip exec peg_nip3 /* Dengan deklarasi */ create proc peg_ket @nip char(7) as begin declare @nama char(20),@keterangan char(50) select @nama=nama_pegawai from pegawai where nip=@nip if (@nama='Abdul Malik Ikhsan') set @keterangan='ganteng' else set @keterangan='biasa aja tuh' print @nama+' orangnya '+@keterangan end
exec peg_ket 'p001' /* STORED FUNCTION hampir sama dengan stored procedure,hanya saja dia mengembalikan nilai yang kita inginkan :) */ /* contoh --> mengembalikan nilai dari tabel */ create function getnama(@nip char(7)) returns table as return (select nama_pegawai from pegawai where nip=@nip) /* cara panggil */ select * from getnama('p001') /* mengembalikan nilai dengan parameter*/ create function getnamalagi(@nip char(7)) returns char(50) as begin declare @nama char(50) set @nama=(select nama_pegawai from pegawai where nip=@nip) return @nama end /* jika kita mendeklarasikan parameter, berarti untuk memanggilnya, kita harus memanggil dbo-nya(database objecknya) */ select dbo.getnamalagi('p001') as nama create function getalamat(@nip char(7)) returns char(50) as begin declare @alamat char(50) set @alamat=(select alamat_pegawai from pegawai where nip=@nip) return @alamat end select dbo.getalamat('p002') /* MENGGABUNGKAN STORED PROCEDURE DAN STORED FUNCTION misalkan, kita punya procedure tampilkan alamat berdasarkan nip
yang diinputkan dalam parameter procedure tersebut... --------kita tampilkan keterangan dengan function :) */ create proc ambilketerangan @nip char(7) as begin select alamat_pegawai from pegawai where nama_pegawai=(select * from getnama(@nip)) end exec ambilketerangan 'p001'
/* VIEW view adalah pandangan yang diciptakan agar proses pengambilan tidak langsung ke tabel, tetapi melalui object view yang kita create hanya saja, ia tidak bisa passing parameter */ create view speg_view as select * from pegawai where nip!=all(select nip from absensi where keterangan='izin') select * from speg_view /* CONTOH KASUS AGGREGATE FUNCTION UNTUK HITUNG TOTAL GAJI ANGGAP SAJA SETIAP KALI ABSENSI DENGAN KETERANGAN 'HADIR' BERARTI ADA TAMBAHAN 30000 YANG NANTINYA DIKALIKAN JUMLAH HARI DAN DITAMBAHKAN GAJI POKOK */ create proc hitunggajitotal @nip char(7) as begin declare @gapok int,@jmlhari int,@gatot int set @jmlhari=(select count(*) as jmlhari from absensi where keterangan='hadir' and nip=@nip) set @gapok=(select gapok from pegawai where nip=@nip) set @gatot=@gapok+(@jmlhari*30000) print @gatot end exec hitunggajitotal 'p002'
