Praktikum SBD 8a

1.      Insertlah data berikut :

MsMemberType

MemberTypeID	MemberType
MT001	Platinum
MT002	Gold
MT003	Silver
MT004	Bronze

MsTrainer

TrainerID	TrainerName	TrainerShift	TrainerPhone	TrainerAddress
TR001	Dragon Knight	Evening	0819565656	Kebon Batu Street No. 5
TR002	Shandy Murphy	Morning	0819656328	Gurundi Street No. 65
TR003	Brian Knight	Evening	0818795514	Haunted Street No. 13
TR004	Miley Crush	Afternoon	0815487523	Rotten Blackberry Street No. 8
TR005	Burby Haunter	Evening	0859245321	Syaiful Street No. 88
TR006	Milky Waygood	Evening	0865558565	Jamilah Street No. 66
TR007	Andi Suparno	Afternoon	089455222	Gurundi Urban Street No. 2

MsTraining

TrainingID	Training
TN001	Yoga
TN002	Body Pump
TN003	RPM
TN004	Stretch
TN005	Energy Aerobic
TN006	Step Atheletic

MsMember

MemberID	MemberTypeID	MemberName	MemberAddress	MemberPhone
MB001	MT001	Ucok Biba	Melawai Street No. 89	085233333'
MB002	MT002	Bekti Murphy	Gurundi Street No. 4	0856357453
MB003	MT003	Aunty Unclear	Judge Hatred Street No. 22	0818956144
MB004	MT004	Lavigne Aprilia	Fly Goodbye Street No. 8	0856975232
MB005	MT001	Paris Sultan	-	0854586632
MB006	MT002	Richie Poor	Budi Pertiwi Street No. 2	085464875

TrHeaderTraining

TransactionID	TrainerID	TrainingID	Room	BeginTime	EndTime	Days
TG001	TR001	TN005	Weight	01/20/2010 15:00:00	01/20/2010 19:00:00	Wednesday
TG002	TR002	TN002	Cardio	01/21/2010 16:00:00	01/21/2010 17:00:00	Thursday
TG003	TR003	TN001	Yoga Room	01/22/2010 15:00:00	01/22/2010 17:00:00	Friday
TG004	TR004	TN004	Aerobic	01/23/2010 09:00:00	01/23/2010 10:00:00	Saturday
TG005	TR005	TN001	Strength	01/20/2010 10:00:00	01/20/2010 11:30:00	Wednesday
TG006	TR006	TN002	Cardio	01/21/2010 17:00:00	01/21/2010 20:00:00	Thursday
TG007	TR002	TN003	RPM Studio	01/22/2010 20:00:00	01/22/2010 21:00:00	Friday

TrDetailTraining

MemberID	TransactionID
TG001	MB001
TG002	MB002
TG003	MB003
TG004	MB004
TG005	MB005
TG006	MB006
TG007	MB001
TG001	MB002
TG002	MB003
TG003	MB004

2.      Ubah TrainerShift pada table MsTrainer menjadi ‘Afternoon’ dimana TrainerID adalah ‘TR006’ dan kemudian tampilkan seluruh data dari MsTrainer.

(update)

3.      Ubah MemberPhone pada table MsMember menjadi ‘0823222165’ dimana MemberID adalah ‘MB003’ dan kemudian tampilkan seluruh data dari MsMember.

(update)

4.      Ubah EndTime pada table TrHeaderTraining menjadi EndTime = BeginTime ditambah dengan 1 jam dimana Days merupakan ‘Friday’ dan jam pada BeginTime merupakan bilangan genap, kemudian tampilkan seluruh data dari TrHeaderTraining.

(update, datepart, dateadd)

      

5.      Ubah CustomerAddress menjadi ‘Gundah Gulana Street No. 78’ dimana CustomerAddress yang sebelumnya belum terisi. Kemudian tampilkan seluruh data pada MsCustomer.

(update, is null)

6.      Ubah MemberTypeId pada MsMember menjadi ‘MT001’ dimana nama belakang dari MemberName sama dengan nama belakang dari TrainerName yang memberikan training kepada member tersebut. Kemudian tampilkan seluruh data pada MsMember.

(update, substring, charindex, len)

7.      Hapuslah isi table MsTrainer dimana TrainerID tersebut tidak terdapat pada TrHeaderTraining.

(delete, not in)

8.      Tambahkan constraint untuk table MsMember dimana MemberPhone harus diisi dengan angka, data yang telah ada sebelumnya tidak perlu dicek. Kemudian hapuslah constraint tersebut.

(alter table, nocheck, add constraint, drop)

9.        Tambahkan column Duration pada table TrHeaderTraining, dimana Duration berisikan waktu lamanya training berlangsung dalam satuan menit. Lalu hapuslah column tersebut.

(alter table, add, update, datediff, cast, drop).              

10.  Tambahkan constraint untuk table MsTrainer dimana TrainerShift hanya boleh diisi antara ‘Afternoon’, ‘Morning’, atau ‘Evening’. Data yang telah ada sebelumnya tidak perlu dicek. Kemudian hapus constraint tersebut.

(alter table, nocheck, add constraint, drop, in)

Jawab 

--1
insert into msmembertype values('MT001','Platinum')
insert into msmembertype values('MT002','Gold')
insert into msmembertype values('MT003','Silver')
insert into msmembertype values('MT004','Bronze')

insert into mstrainer values('TR001','Dragon Knight','Evening','0819565656','Kebon Batu Street No. 5')
insert into mstrainer values('TR002','Shandy Murphy','Morning','0819656328','Gurundi Street No. 65')
insert into mstrainer values('TR003','Brian Knight','Evening','0818795514','Haunted Street No. 13')
insert into mstrainer values('TR004','Miley Crush','Afternoon','0815487523','Rotten Blackberry Street No. 8')
insert into mstrainer values('TR005','Burby Haunter','Evening','0859245321','Syaiful Street No. 88')
insert into mstrainer values('TR006','Milky Waygood','Evening','0865558565','Jamilah Street No. 66')
insert into mstrainer values('TR007','Andi Suparno','Afternoon','089455222','Gurundi Urban Street No. 2')


insert into mstraining values('TN001','Yoga')
insert into mstraining values('TN002','Body Pump')
insert into mstraining values('TN003','RPM')
insert into mstraining values('TN004','Stretch')
insert into mstraining values('TN005','Energy Aerobic')
insert into mstraining values('TN006','Step Atheletic')


insert into msmember values('MB001','MT001','Ucok Biba','Melawai Street No. 89','085233333')
insert into msmember values('MB002','MT002','Bekti Murphy','Gurundi Street No. 4','0856357453')
insert into msmember values('MB003','MT003','Aunty Unclear','Judge Hatred Street No. 22','0818956144')
insert into msmember values('MB004','MT004','Lavigne Aprilia','Fly Goodbye Street No. 8','0856975232')
insert into msmember values('MB005','MT001','Paris Sultan',NULL,'0854586632')
insert into msmember values('MB006','MT002','Richie Poor','Budi Pertiwi Street No. 2','085464875')

insert into trheadertraining values('TG001','TR001','TN005','Weight','01/20/2010 15:00:00','01/20/2010 19:00:00','Wednesday')
insert into trheadertraining values('TG002','TR002','TN002','Cardio','01/21/2010 16:00:00','01/21/2010 17:00:00','Thursday')
insert into trheadertraining values('TG003','TR003','TN001','Yoga Room','01/22/2010 15:00:00','01/22/2010 17:00:00','Friday')
insert into trheadertraining values('TG004','TR004','TN004','Aerobic','01/23/2010 09:00:00','01/23/2010 10:00:00','Saturday')
insert into trheadertraining values('TG005','TR005','TN001','Strength','01/20/2010 10:00:00','01/20/2010 11:30:00','Wednesday')
insert into trheadertraining values('TG006','TR006','TN002','Cardio','01/21/2010 17:00:00','01/21/2010 20:00:00','Thursday')
insert into trheadertraining values('TG007','TR002','TN003','RPM Studio','01/22/2010 20:00:00','01/22/2010 21:00:00','Friday')

insert into trdetailtraining values('TG001','MB001')
insert into trdetailtraining values('TG002','MB002')
insert into trdetailtraining values('TG003','MB003')
insert into trdetailtraining values('TG004','MB004')
insert into trdetailtraining values('TG005','MB005')
insert into trdetailtraining values('TG006','MB006')
insert into trdetailtraining values('TG007','MB001')
insert into trdetailtraining values('TG001','MB002')
insert into trdetailtraining values('TG002','MB003')
insert into trdetailtraining values('TG003','MB004')

--2

update mstrainer set trainershift='Afternoon' where TrainerID='TR006'

--3
update msmember set memberphone='0823222165' where memberid='MB003'

--4

update trheadertraining set endtime = dateadd(hour,1,begintime) where days = 'Friday' and
datepart(hour,begintime)%2=0

select * from trheadertraining

--5
update msmember set memberaddress='Gundah Gulana Street No. 78'
where memberaddress is null
where isnull(memberaddress)

--6
update msmember set membertypeid='MT001'
from msmember mb, trdetailtraining td,trheadertraining th,mstrainer mr where
mb.memberid=td.memberid and td.transactionid=th.transactionid
and mr.trainerid=th.trainerid
and
substring(membername,charindex(' ',membername)+1,len(membername))=substring(trainername,charindex(' ',trainername)+1,len(trainername))


--7
begin tran
delete from mstrainer where trainerid not in
(
select trainerid from trheadertraining
)

select * from mstrainer

--8

alter table msmember with nocheck
add constraint
phone_cnst check(isnumeric(memberphone)=1)

alter table msmeber
drop constraint phone_cnst

--9
alter table trheadertraining
add duration varchar(20)

update trheadertraining set duration=cast(datediff(minute,begintime,endtime) as varchar) +' minutes'

select * from trheadertraining

alter table trheadertraining
drop column duration

--10

alter table mstrainer
add constraint trainer_cnst check (trainershift in ('Evening','Morning','Afternoon'))

alter table mstrainer
drop constraint trainer_cnst