BC RS SQL Exam Answers

Maaf atas keterlambatannya.

Semua perintah saya ambil dr pekerjaan pak Yudho, kecuali trigger.

--1--
create database dbMakmur
on
(
name = dbMakmur01,
filename = 'D:\dtmakmur01.mdf',
size = 2mb,
maxsize = 15mb,
filegrowth = 1 MB
),
(
name = dbMakmur02,
filename = 'D:\dtmakmur02.ndf'
),
(
name = dbMakmur03,
filename = 'D:\dtmakmur03.ndf'
)
Log ON
(
name = dbMakmur_Log,
filename = 'D:\dtmakmurlog.ldf'
)

--2--
use dbmakmur
Create table MsStaff (
KdStaff char(5) not null,
NmStaff varchar(25) not null,
Alamat varchar(40) not null,
NoRek varchar(12) not null,
KdBank Char(1) not null,
LevelStaff char(1) default 5,
Komisi money )

Create table KomisiBulanan (
Bulan int not null,
KdStaff char(5) not null,
KomisiBulanan Money not null,
StatusBayar char(1) default 'T')

Create table MsProspek(
KdProspek char(5) not null,
NamaProspek varchar(25) not null,
JenKel char(1) default 'L',
Alamat varchar(40) not null,
Kota varchar(15) default 'Jakarta',
Telp varchar(15),
TglLahir datetime not null)

create table trAsuransi (
TglTransaksi datetime not null,
kdTransaksi char(5) not null,
kdProspek char(5) not null,
kdStaff char(5) not null,
Status char(1) default 'T')


--3--
insert into msStaff values('S0001','Alan Smith', 'Jl. Mawar No 10','751-3526360','1','3',200)
insert into msStaff values('S0002','Bernard Shaw', 'Jl. Anggrek 23' ,'751-3526360','1','3',200)
insert into msStaff values('S0003','Maria Sand', 'Jl. Anggrek 54' ,'751-3569857','2','4',135)
insert into msStaff values('S0004','Karl Max', 'Jl. Asoka 43' ,'654-5965321','3','4',135)
insert into msStaff values('S0005','Lenny Gosh', 'Jl. Kamboja 54' ,'351-7845235','3','5',75)
insert into msStaff values('S0006','Sorl Karl', 'Jl. Mawar 27' ,'567-9535263','1','5',75)
insert into msStaff values('S0007','Penny G.', 'Jl. Sepatu 51' ,'921-9595356','2','5',75)

insert into KomisiBulanan values(01,'S0006', 75,'T')
insert into KomisiBulanan values(01,'S0002', 400,'T')
insert into KomisiBulanan values(01,'S0007', 75,'T')
insert into KomisiBulanan values(01,'S0005', 75,'T')

insert into msProspek values('P0001','Adam Pasaribu', 'L','Jl Layang 10','Jakarta' ,'7451235','1971-12-05')
insert into msProspek values('P0002','Albert Wijaya', 'L','Jl Warna 15','Jakarta' ,'3512623','1968-03-17')
insert into msProspek values('P0003','Fani S' , 'P','Jl Tumbuk 34','Jakarta' ,'4531626','1979-08-15')
insert into msProspek values('P0004','Stefanus W' , 'L','Jl Warna 20','Jakarta' ,'3516267','1970-02-04')
insert into msProspek values('P0005','Lena Putri' , 'P','Jl Nila 45','Jakarta' ,'5623523','1968-10-05')
insert into msProspek values('P0006','Maria S.' , 'P','Jl Mujair 20','Bandung' ,'5468412','1959-01-30')
insert into msProspek values('P0007','Manise R.' , 'P','Jl Komodo 14','Bandung' ,'784562' ,'1973-02-01')
insert into msProspek values('P0008','Diego Ribas' , 'L','Jl Pangeran 30','Jakarta','4568952','1978-08-06')

insert into TrAsuransi values('2004-01-01','T0001','P0001','S0001','T')
insert into TrAsuransi values('2004-01-01','T0002','P0002','S0006','Y')
insert into TrAsuransi values('2004-01-01','T0003','P0003','S0002','Y')
insert into TrAsuransi values('2004-01-01','T0004','P0004','S0005','T')
insert into TrAsuransi values('2004-01-01','T0005','P0005','S0002','T')
insert into TrAsuransi values('2004-01-01','T0006','P0006','S0007','Y')
insert into TrAsuransi values('2004-01-01','T0007','P0007','S0002','Y')
insert into TrAsuransi values('2004-01-01','T0008','P0008','S0005','Y')

--4--
--Alter--
--1--
alter database dbMakmur
add log file (
name = dbMakmur_Log02,
filename = 'D:\dtmakmurlog02.ldf'
)
--2--
alter database dbMakmur
REMOVE file dbmakmur03
--3--
alter table msStaff
alter column NmStaff varchar(30)
--4--
alter table msProspek
alter column TglLahir Smalldatetime
--5--
alter table msStaff
add constraint pk_KdStaff primary key (kdStaff)

alter table KomisiBulanan
add constraint fk_KdStaff
Foreign key (kdStaff) REFERENCES msStaff(kdStaff)

alter table msProspek
add constraint pk_KdProspek primary key (kdProspek)

alter table TrAsuransi
add constraint fk_KdStaff_trAsuransi
Foreign key (kdStaff) REFERENCES msStaff(kdStaff)

alter table TrAsuransi
add constraint fk_kdPorspek_trAsuransi
Foreign key (kdProspek) REFERENCES msProspek(kdProspek)

------
--query--
--1--
select * from trAsuransi where status ='t'
--2--
select * from msProspek where year(tglLahir)>1970
--3--
select a.kdstaff,a.nmstaff from Msstaff as a
left join komisibulanan as b
on a.kdstaff = b.kdstaff
where b.kdstaff is null
--4--
select a.kdstaff,a.nmstaff from Msstaff as a
left join trAsuransi as b
on a.kdstaff = b.kdstaff
where b.kdstaff is null

----
--view--
--1--
CREATE VIEW vw_transaksi AS
SELECT a.tgltransaksi,a.kdprospek, b.NamaProspek, a.kdstaff,c.nmStaff
FROM trAsuransi as a
inner join msprospek as b on a.kdprospek = b.kdprospek
inner join msStaff as c on a.kdstaff = c.kdstaff

--2--
CREATE VIEW vw_transaksi_stj AS
SELECT * FROM trAsuransi where [Status] = 'Y'

--3--
CREATE VIEW vw_transaksi_stj AS
SELECT a.kdstaff,b.nmStaff,count(*) as transaksi
FROM trAsuransi as a
inner join msStaff as b on a.kdStaff = b.kdStaff
group by a.kdstaff,b.nmStaff
-----
--store procedure--

--1--
CREATE PROCEDURE PR_staffTr
(@kdstaff CHAR(5))
AS
select * from trAsuransi
WHERE kdstaff =@kdstaff

exec pr_staffTr 'S0007'

--2--
CREATE PROCEDURE PR_prospekTr
(@kdprospek CHAR(5))
AS
select a.kdprospek,tgltransaksi, NamaProspek,JenKel,Alamat,Kota,Telp,TglLahir,[status]
from trAsuransi as a
inner join msprospek as b on a.kdprospek = b.kdprospek
WHERE a.kdprospek =@kdprospek

exec PR_prospekTr'P0001'

create trigger tr_1 on trasuransi for insert as
declare @status char(1)
set @status = (select status from inserted)
if @status = 'y'
begin
declare @komisi money
set @komisi = (select komisi from msstaff where kdstaff = (select kdstaff from inserted))
declare @bulan int
set @bulan = (select count(bulan) from komisibulanan where kdstaff = (select kdstaff from inserted) and bulan = (select month(tgltransaksi) from inserted))
if @bulan > 0
begin
update komisibulanan set komisibulanan = komisibulanan + @komisi where kdstaff = (select kdstaff from inserted)
print 'Komisi Bulanan terupdate'
end
else
begin
declare @bln int, @kdstaff char(5)
set @bln = (select month(tgltransaksi) from inserted)
set @kdstaff = (select kdstaff from inserted)
insert into komisibulanan values (@bln,@kdstaff,@komisi,'T')
print 'Komisi Bulanan terinsert'
end
end

-- test case tr_1
select * from komisibulanan
insert into TrAsuransi values('2004-01-02','T0009','P0001','S0001','Y')
insert into TrAsuransi values('2004-01-02','T0010','P0001','S0002','Y')
select * from komisibulanan
-- end of test case tr_1

create trigger tr_2 on msprospek for update as
declare @kdprospek_lama char(5), @kdprospek_baru char(5)
set @kdprospek_lama = (select kdprospek from deleted)
set @kdprospek_baru = (select kdprospek from inserted)
if @kdprospek_lama <> @kdprospek_baru
begin
rollback tran
print 'Kode Prospek tidak boleh diubah!!!'
end

-- test case tr_2
insert msprospek values ('p0010','dummy','x','dummy','dummy','dummy','1900-01-01')
select * from msprospek
update msprospek set kdprospek = 'xxx' where kdprospek = 'p0010'
-- end of test case tr_2

create trigger tr_3 on msprospek for delete as
declare @kdprospek char(5), @status char(1)
set @kdprospek = (select kdprospek from deleted)
set @status = (select status from trasuransi where kdprospek = @kdprospek)
if @status = 'T'
begin
rollback tran
print 'Data prospek tidak boleh dihapus!'
end
else
begin
delete from trasuransi where kdprospek = @kdprospek
end

-- test case tr_3
select * from trasuransi
delete from msprospek where kdprospek = 'p0001'
-- end of test case tr_3