Untitled
raw download clone
SQL
views 14
,
size 4447 b
create table skpd (id_skpd nvarchar(5) not null,
skpd nvarchar (75), primary key (id_skpd))

create table pangkat (id_pangkat nvarchar(5) not null,
pangkat nvarchar(75),primary key (id_pangkat))

create table jabatan (id_jabatan nvarchar(5) not null,
jabatan nvarchar (75), primary key(id_jabatan))


create table biodata_pegawai (nip nvarchar (20) not null,
nama_lengkap nvarchar(50), tgl_bekerja datetime,
id_jabatan nvarchar(5),id_pangkat nvarchar(5), id_skpd nvarchar(5), 
gaji_pokok numeric,primary key(nip),
foreign key (id_jabatan) references jabatan,
foreign key (id_pangkat) references pangkat,
foreign key (id_skpd) references skpd)

insert into skpd (id_skpd,skpd) values ('s001' ,'Dinas Pendidikan')
insert into skpd (id_skpd,skpd) values ('s002' ,'Dinas cipta karya')
insert into skpd (id_skpd,skpd) values ('s003' ,'Dinas perawatan')
insert into skpd (id_skpd,skpd) values ('s004' ,'Dinas Peternakan')
insert into skpd (id_skpd,skpd) values ('s005' ,'Dinas lingkungan hidup')


insert into pangkat (id_pangkat,pangkat) values ('p001' ,'IIIA')
insert into pangkat (id_pangkat,pangkat) values ('p002' ,'IVA')
insert into pangkat (id_pangkat,pangkat) values ('p003' ,'IIB')

insert into jabatan (id_jabatan,jabatan) values ('J001' ,'Kepala dinas')
insert into jabatan (id_jabatan,jabatan) values ('J002' ,'sekretaris')
insert into jabatan (id_jabatan,jabatan) values ('J003' ,'Kepala Bidang')
insert into jabatan (id_jabatan,jabatan) values ('J004' ,'Staff')


insert into biodata_pegawai (nip,nama_lengkap,tgl_bekerja,id_jabatan,id_pangkat,id_skpd,gaji_pokok) values ('N001' ,'Sarman','05/11/1995','J001','P001','S001',5000)
insert into biodata_pegawai (nip,nama_lengkap,tgl_bekerja,id_jabatan,id_pangkat,id_skpd,gaji_pokok) values ('N002' ,'budi','09/10/1996','J002','P002','S002',1000)
insert into biodata_pegawai (nip,nama_lengkap,tgl_bekerja,id_jabatan,id_pangkat,id_skpd,gaji_pokok) values ('N003' ,'udin','07/01/1990','J003','P003','S001',1500)
insert into biodata_pegawai (nip,nama_lengkap,tgl_bekerja,id_jabatan,id_pangkat,id_skpd,gaji_pokok) values ('N004' ,'sean','12/24/1994','J003','P002','S001',1500)
insert into biodata_pegawai (nip,nama_lengkap,tgl_bekerja,id_jabatan,id_pangkat,id_skpd,gaji_pokok) values ('N005' ,'ani','07/02/2001','J003','P001','S001',2000)
insert into biodata_pegawai (nip,nama_lengkap,tgl_bekerja,id_jabatan,id_pangkat,id_skpd,gaji_pokok) values ('N005' ,'adi','04/12/1990','J003','P003','S001',2500)
insert into biodata_pegawai (nip,nama_lengkap,tgl_bekerja,id_jabatan,id_pangkat,id_skpd,gaji_pokok) values ('N006' ,'larsen','07/02/2001','J003','P002','S002',2500)


select biodata_pegawai.nip,biodata_pegawai.nama_lengkap,
biodata_pegawai.id_skpd,skpd.skpd from biodata_pegawai
INNER JOIN skpd ON biodata_pegawai.id_skpd = skpd.id_skpd

select biodata_pegawai.nip,biodata_pegawai.nama_lengkap,
	biodata_pegawai.id_skpd,skpd.skpd from biodata_pegawai
	inner join skpd ON biodata_pegawai.id_skpd = skpd.id_skpd

select biodata_pegawai.nip,biodata_pegawai.nama_lengkap,biodata_pegawai.id_skpd,skpd.skpd,biodata_pegawai.id_jabatan,jabatan.id_jabatan,biodata_pegawai.id_pangkat,pangkat.id_pangkat
	from biodata_pegawai
	inner join skpd ON biodata_pegawai.id_skpd=skpd.id_skpd
	inner join jabatan ON biodata_pegawai.id_jabatan=jabatan.id_jabatan
	inner join pangkat ON biodata_pegawai.id_pangkat=pangkat.id_pangkat

select biodata_pegawai.nip,biodata_pegawai.nama_lengkap,
	biodata_pegawai.id_skpd,skpd.skpd from biodata_pegawai
	inner join skpd ON biodata_pegawai.id_skpd = skpd.id_skpd

select *from biodata_pegawai cross join skpd

select sum (gaji_pokok)as total_gaji from
biodata_pegawai

select cast (round(avg(gaji_pokok),0) as int)as
	rataan_gaji from biodata_pegawai

select sum (gaji_pokok)as total_gaji,avg (gaji_pokok)
as rataan_gaji from biodata_pegawai

select max(gaji_pokok)as gaji_terbesar from 
biodata_pegawai

select min(gaji_pokok)as gaji_terbesar from 
biodata_pegawai

select max(gaji_pokok)as gaji_terbesar, min(gaji_pokok) as gaji_terkecil
from biodata_pegawai

select count(*) jml_data from biodata_pegawai where 
gaji_pokok>1000

select count(distinct id_skpd)jml_skpd from biodata_pegawai

select id_skpd,avg(gaji_pokok) as rataan_gaji from
biodata_pegawai group by id_skpd

select id_skpd,id_jabatan,sum (gaji_pokok) as
total_gaji from biodata_pegawai group by
id_skpd,id_jabatan
close fullscreen
Login or Register to edit or fork this paste. It's free.