Sql Functions
SQL FUNCTIONS
- Buat database SQLFunctions
create database SQLFunctions;
- Pada database tersebut buat table barang jual dengan field yang ada pada jobssheet
create table BarangJual
(
[No Ururt] tinyint primary key,
[Nama Barang] varchar(50),
kode varchar(50),
[Jumlah Barang] int,
[Harga Satuan] int
);
- Masukan data pada table tersebut sesuai jobssheet
insert into barangjual
values ('1','TRAFO','CASH','150','5000');
insert into barangjual
values ('2','STABILIZER','KREDIT','200','20000');
insert into barangjual
values ('3','KABEL','CASH','1400','2000');
insert into barangjual
values ('4','LAMPU TL','CASH','175','4500');
insert into barangjual
values ('5','PCB','KREDIT','350','1750');
insert into barangjual
values ('6','BOHLAM','CASH','700','3500');
insert into barangjual
values ('7','STEKER','KREDIT','150','4500');
insert into barangjual
values ('8','CELLOTAPE','CASH','1500','1000');
insert into barangjual
values ('9','REWINDER','CASH','75','30000');
insert into barangjual
values ('10','PARABOLA','KREDIT','15','750000');
- Buat Perintah SQL Dalam satu perintah untuk mendapatkan Total, Discount, dan Harus Bayar dengan ketentuan yang ada pada jobs sheet
case
when kode='cash' then [jumlah barang]*[harga satuan]*0.1
else 0
end,([jumlah barang]*[harga satuan])-(case when kode='cash' then [jumlah barang]*[harga satuan]*0.1 else 0 end) as [harus bayar] from barangjual
- Buat perintah SQL dalam satu perintah untuk mendapatkan nilai minimam jumlah barang, harga satuan, alias total, alias discount, dan alias harus bayar
select min([jumlah barang]) as [minimal jumlah barang],min([harga satuan]) as [minimal harga satuan],min([harga satuan]*[jumlah barang]) as [minimal total],min(case when kode='cash' then [jumlah barang]*[harga satuan]*0.1 else 1000000 end) as [minimal discount],min([jumlah barang]*[harga satuan]-case when kode='cash' then [jumlah barang]*[harga satuan]*0.1 else 0 end) as [minimal total bayar] from barangjual
- Buat perintah SQL dalam satu perintah untuk mendapatkan nilai maximum jumlah barang, harga satuan, alias total, alias discount, dan alias harus bayar
select max([jumlah barang]) as [maximal jumlah barang],max([harga satuan]) as [maximal harga satuan],max([harga satuan]*[jumlah barang]) as [maximal total],max(case when kode='cash' then [jumlah barang]*[harga satuan]*0.1 else 0 end) as [maximal discount],max([jumlah barang]*[harga satuan]-case when kode='cash' then [jumlah barang]*[harga satuan]*0.1 else 0 end) as [maximal total bayar] from barangjual
- Buat perintah SQL dalam satu perintah untuk mendapatkan nilai Rata-rata jumlah barang, harga satuan, alias total, alias discount, dan alias harus bayar
select avg([jumlah barang]) as [rata-rata jumlah barang],avg([harga satuan]) as [rata-rata harga satuan],avg([harga satuan]*[jumlah barang]) as [rata-rata total],avg(case when kode='cash' then [jumlah barang]*[harga satuan]*0.1 else 0 end) as [rata-rata discount],avg([jumlah barang]*[harga satuan]-case when kode='cash' then [jumlah barang]*[harga satuan]*0.1 else 0 end) as [rata-rata total bayar] from barangjual
- Buat perintah SQL dalam satu perintah untuk mendapatkan nilai Total jumlah barang, harga satuan, alias total, alias discount, dan alias harus bayar
select sum([jumlah barang]) as [total jumlah barang],sum([harga satuan]) as [total harga satuan],sum([harga satuan]*[jumlah barang]) as [total harga sebelum discount],sum(case when kode='cash' then [jumlah barang]*[harga satuan]*0.1 else 0 end) as [total discount],sum([jumlah barang]*[harga satuan]-case when kode='cash' then [jumlah barang]*[harga satuan]*0.1 else 0 end) as [total harga setelah discount] from barangjual
- Buat perintah SQL Functions untuk mendapatkan apa saja barang yang harus dibayar melebihi jumlah keseluruhan harga satuan
SELECT [nama barang] from barangjual1 where ([jumlah barang]*[harga satuan])-(case when kode='cash' then [jumlah barang]*[harga satuan]*0.1 else 0 end) > (select sum([harga satuan]) from barangjual)
- Buat perintah SQL functions untuk mendapatakan berapa banyak barang yang dibeli dengan kredit
select count(kode) as [banyak barang yang kredit] from barangjual where kode='kredit'
- Pada database tersebut, buat lagi tabel Gaji seperti yang ditentukan jobssheet
create table gaji
(
[nama pegawai] varchar(50),
Gol varchar(5),
Sex varchar(6),
Status varchar(7),
[jumlah anak] tinyint
);
- Masukan data pada table tersebut sesuai jobssheet
insert into gaji
values ('RIYANTO','I','Pria','Menikah','2');
insert into gaji
values ('FANNY','II','Wanita','Single','0');
insert into gaji
values ('ANDI','I','Pria','Menikah','3');
insert into gaji
values ('MIMI','III','Pria','Menikah','1');
insert into gaji
values ('SANTI','II','Wanita','Menikah','2');
insert into gaji
values ('ENDANG','I','Wanita','Menikah','4');
insert into gaji
values ('LILIS','II','Wanita','Menikah','2');
insert into gaji
values ('EKO','III','Pria','Menikah','1');
insert into gaji
values ('WIJAYA','III','Pria','Single','0');
insert into gaji
values ('RONALD','II','Pria','Menikah','2');
insert into gaji
values ('WENNY','I','Wanita','Menikah','2');
insert into gaji
values ('EFFENDY','I','Pria','Menikah','1');
- Buat perintah SQL Functions dalam satu perintah untuk mendapatkan Gaji pokok, Tunjangan Istri, Tunjangan Anak, Total Gaji dengan ketentuan yang ditentukan pada jobssheet
select [nama pegawai],gol,sex,status,[jumlah anak],'Gaji Tunjangan'=
case
when gol = 'I' then 1500000
when gol = 'II' then 2500000
when gol = 'III' then 3500000
else 0
end,
'Tunjangan Istri'=
case
when gol = 'I' then 750000
when gol = 'II' then 250000
when gol = 'III' then 525000
else 0
end,
'Tunjangan anak'=
case
when sex='pria' or status='menikah' then 50000 * [jumlah anak]
else 0
end,
(case
when gol = 'I' then 1500000
when gol = 'II' then 2500000
when gol = 'III' then 3500000
else 0
end) + (case
when gol = 'I' then 750000
when gol = 'II' then 250000
when gol = 'III' then 525000
else 0
end) + (case
when sex='pria' or status='menikah' then 50000 * [jumlah anak]
else 0
end) as [Total Gaji] from gaji
- Buat perintah SQL Functions dalam satu perintah untuk mendapatkan nilai minimum, maksimum, dan nilai rata-rata total gaji serta jumlah seluruh total gaji
select min(case
when Gol='I' then 1500000
when Gol='II' then 2500000
when Gol='III' then 3500000
else 0
end +
case
when sex='Pria' and Status='Menikah' then 750000
when sex='Pria' and Status='Menikah' then 250000
when sex='Pria' and Status='Menikah' then 525000
else 0
end +
case
when sex='Pria' or Status='Menikah' then 50000 * [Jumlah Anak]
else 0
end) as [Minimal Total Gaji],
max(case
when Gol='I' then 1500000
when Gol='II' then 2500000
when Gol='III' then 3500000
else 0
end +
case
when sex='Pria' and Status='Menikah' then 750000
when sex='Pria' and Status='Menikah' then 250000
when sex='Pria' and Status='Menikah' then 525000
else 0
end +
case
when sex='Pria' or Status='Menikah' then 50000 * [Jumlah Anak]
else 0
end) as [MaksimalTotal Gaji],
avg(case
when Gol='I' then 1500000
when Gol='II' then 2500000
when Gol='III' then 3500000
else 0
end +
case
when sex='Pria' and Status='Menikah' then 750000
when sex='Pria' and Status='Menikah' then 250000
when sex='Pria' and Status='Menikah' then 525000
else 0
end +
case
when sex='Pria' or Status='Menikah' then 50000 * [Jumlah Anak]
else 0
end) as [Rata-Rata Total Gaji],
sum(case
when Gol='I' then 1500000
when Gol='II' then 2500000
when Gol='III' then 3500000
else 0
end +
case
when sex='Pria' and Status='Menikah' then 750000
when sex='Pria' and Status='Menikah' then 250000
when sex='Pria' and Status='Menikah' then 525000
else 0
end +
case
when sex='Pria' or Status='Menikah' then 50000 * [Jumlah Anak]
else 0
end) as [Jumlah Total Gaji]
from gaji;
Perintah-perintah di atas dibuat menggunakan SQL server 2005
