Praktikum SBD 8b

1.      Insertlah data berikut

MsStudent

StudentID	StudentName	Address	Gender	PhoneNumber
SD001	Andy Budi Pratama	Market Street A/12	Male	081809112345
SD002	Aries	Pandan Street C/21	Male	081805846212
SD003	Arya Nata Gani Putra	Golden Street SE/7	Male	081572123422
SD004	Daniel Theodorus	Binus Street Z/32	Male	081805422344
SD005	Darwin Simon	Syahdan Street ZY/3	Male	081805675366
SD006	Diana	Kijang Street DF/14	Female	081228123129
SD007	Empoe Tibea	Salam Street FF/2	Male	081809112345
SD008	Erick Susanto Djatiwidjaja	Bandeng Street XY/1	Male	081805821212
SD009	Harris Kristanto	Singer Street XO/55	Male	081542312348
SD010	Hendry Kosasi	Keluarga Street QY/2	Male	081579357698

MsCourse

CourseID	CourseName	Price
CD001	Science	590000
CD002	Technology	610000
CD003	Mathematic	450000
CD004	Sosiology	550000
CD005	Geographic	510000

MsLecturer

LecturerID	LecturerName	Email	Salary
LD001	Ria Ayu Anggraini	RA@hotmail.com	1000000.00
LD002	Sally	SY@yahoo.com	1000000.00
LD003	Samuel Edwin	SE@yahoo.co.id	1200000.00
LD004	Sonny Salim	SO@yahoo.co.id	1400000.00
LD005	Stefanie Suanita	FN@gmail.com	1500000.00

TrHeaderSchedule

ScheduleID	StudentID	LecturerID	ScheduleDate
SC001	SD009	LD005	1/4/2010
SC002	SD001	LD004	1/5/2010
SC003	SD001	LD003	1/6/2010
SC004	SD009	LD002	1/7/2010
SC005	SD004	LD004	1/8/2010
SC006	SD009	LD001	1/9/2010
SC007	SD003	LD005	1/10/2010
SC008	SD005	LD003	1/11/2010
SC009	SD009	LD004	1/12/2010
SC010	SD007	LD002	1/13/2010

TrDetailSchedule

CourseID	ScheduleID	Qty
SC001	CD005	3
SC001	CD001	3
SC002	CD003	3
SC003	CD002	1
SC003	CD001	2
SC004	CD004	3
SC004	CD001	1
SC005	CD005	1
SC005	CD003	3
SC005	CD001	3
SC006	CD002	3
SC007	CD005	3
SC008	CD001	3
SC008	CD003	3
SC008	CD005	2
SC009	CD005	3
SC010	CD005	3

2.      Ubah StudentName pada tabel MsStudent menjadi ‘Maha Andar’ dimana StudentIDnya ‘SD005’ dan kemudian tampilkan seluruh data dari MsStudent.

(update)

     

3.      Ubah price pada table MsCourse menjadi 600000 dimana CourseIDnya ‘CD001’ atau courseName-nya ‘Mathematic’ dan kemudian tampilkan seluruh data dari MsCourse.

(update)

4.      Ubah Salary pada table MsLecturer menjadi New Salary (didapat dari Salary * digit terakhir LecturerID) dan kemudian tampilkan seluruh data dari MsLecturer

(update, right)

5.      Ubah Address Student menjadi ‘Kebon Jeruk Street YY/1’ dimana PhoneNumber Student berakhiran angka 8 dan kemudian tampilkan seluruh data dari MsStudent

(update, like)

6.      Ubah Qty menjadi Qty baru (Qty lama * digit terakhir ScheduleID) dimana tanggal dari ScheduleDate terjadi antara 5 sampai 8.

(update, right, day, between)

7.      Tambahkan constraint untuk table MsStudent dimana panjang PhoneNumber harus 12,  data yang terdahulu tidak perlu di cek. Kemudian hapuslah constraint tersebut.

(alter table, nocheck, add constraint, drop)

8.      Tambahkan kolom Type pada table MsCourse dimana untuk price kurang dari 550000 Type menjadi ‘Standard’, untuk price diantara 550000 dan 600000 Type menjadi ‘VIP’ dan untuk price yang lebih besar dari 600000 Type menjadi ‘VVIP’. Lalu hapuslah kolom tersebut.

(alter table, add, update, drop, between)

9.      Tambahkan constraint untuk table MsStudent dimana Address setelah ‘/’ harus berupa angka, lalu hapuslah constraint tersebut.

(alter table, nocheck, add constraint, isnumeric, substring, charindex, drop)

10.  Tambahkan constraint untuk table MsStudent dimana PhoneNumber haruslah berupa angka, lalu hapuslah constraint tersebut.

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

 Jawab

-- SOAL NO 1

--INSERT MsStudent
insert into MsStudent values ('SD001','Andy Budi Pratama','Market Street A/12','Male','081809112345')
insert into MsStudent values ('SD002','Aries','Pandan Street C/21','Male','081805846212')
insert into MsStudent values ('SD003','Arya Nata Gani Putra','Golden Street SE/7','Male','081572123422')
insert into MsStudent values ('SD004','Daniel Theodorus','Binus Street Z/32','Male','081805422344')
insert into MsStudent values ('SD005','Darwin Simon','Syahdan Street ZY/3','Male','081805675366')
insert into MsStudent values ('SD006','Diana','Kijang Street DF/14','Female','081228123129')
insert into MsStudent values ('SD007','Empoe Tibea','Salam Street FF/2','Male','081809112345')
insert into MsStudent values ('SD008','Erick Susanto Djatiwidjaja','Bandeng Street XY/1','Male','081805821212')
insert into MsStudent values ('SD009','Harris Kristanto','Singer Street XO/55','Male','081542312348')
insert into MsStudent values ('SD010','Hendry Kosasi','Keluarga Street QY/2','Male','081579357698')

--INSERT MsLEcturer
insert into MsLecturer values ('LD001','Ria Ayu Anggraini','RA@hotmail.com','1000000')
insert into MsLecturer values ('LD002','Sally','SY@yahoo.com','1000000')
insert into MsLecturer values ('LD003','Samuel Edwin','SE@yahoo.co.id','1200000')
insert into MsLecturer values ('LD004','Sonny Salim','SO@yahoo.co.id','1400000')
insert into MsLecturer values ('LD005','Stefanie Suanita','FN@gmail.com','1500000')

--INSERT MsCourse
 insert into MsCourse values ('CD001','Science','590000')
 insert into MsCourse values ('CD002','Techonology','610000')
 insert into MsCourse values ('CD003','Mathematic','450000')
 insert into MsCourse values ('CD004','Sosiology','550000')
 insert into MsCourse values ('CD005','Geographic','510000')

--INSERT TrHeaderSchedule

insert into TrHeaderSchedule values ('SC001','SD009','LD005','1/4/2010')
insert into TrHeaderSchedule values ('SC002','SD001','LD004','1/5/2010')
insert into TrHeaderSchedule values ('SC003','SD001','LD003','1/6/2010')
insert into TrHeaderSchedule values ('SC004','SD009','LD002','1/7/2010')
insert into TrHeaderSchedule values ('SC005','SD004','LD004','1/8/2010')
insert into TrHeaderSchedule values ('SC006','SD009','LD001','1/9/2010')
insert into TrHeaderSchedule values ('SC007','SD003','LD005','1/10/2010')
insert into TrHeaderSchedule values ('SC008','SD005','LD003','1/11/2010')
insert into TrHeaderSchedule values ('SC009','SD009','LD004','1/12/2010')
insert into TrHeaderSchedule values ('SC010','SD007','LD002','1/13/2010')


--INSERT TrDetailSchedule

insert into TrDetailSchedule values ('SC001','CD005',3)
insert into TrDetailSchedule values ('SC001','CD001',3)
insert into TrDetailSchedule values ('SC002','CD003',3)
insert into TrDetailSchedule values ('SC003','CD002',1)
insert into TrDetailSchedule values ('SC003','CD001',2)
insert into TrDetailSchedule values ('SC004','CD004',3)
insert into TrDetailSchedule values ('SC004','CD001',1)
insert into TrDetailSchedule values ('SC005','CD005',1)
insert into TrDetailSchedule values ('SC005','CD003',3)
insert into TrDetailSchedule values ('SC005','CD001',3)
insert into TrDetailSchedule values ('SC006','CD002',3)
insert into TrDetailSchedule values ('SC007','CD005',3)
insert into TrDetailSchedule values ('SC008','CD001',3)
insert into TrDetailSchedule values ('SC008','CD005',3)
insert into TrDetailSchedule values ('SC008','CD003',2)
insert into TrDetailSchedule values ('SC009','CD005',3)
insert into TrDetailSchedule values ('SC010','CD005',3)

--SOAL NO 2

begin tran


update MsStudent set StudentName = 'Maha Andar'
where StudentID = 'SD005'

select * from mscourse
rollback

--SOAL NO 3

begin tran

update MsCourse set price = '600000'
where courseID = 'CD001' or courseName = 'Mathematic'

rollback

--SOAL NO 4

begin tran

update MsLecturer set salary = salary * right(LecturerID,1)

rollback

select * from mslecturer

--SOAL NO 5

begin tran

UPDATE MsStudent set Address = 'Kebon Jeruk Street YY/1'
where phoneNumber like '%8'

rollback

select * from msstudent

--SOAL NO 6

begin tran

update TrDetailSchedule set Qty = (qty * right(td.ScheduleID,2))
from TrDetailSchedule td,TrHeaderSchedule th
where td.ScheduleID = th.ScheduleID
and day(ScheduleDate) between 5 and 8

rollback

select * from trdetailschedule
--SOAL NO 7

alter table MsStudent with nocheck
add constraint cx1 check(len(PhoneNumber) = 12)

alter table MsStudent
drop cx1

 --SOAL NO 8

alter table MsCourse
add [Type] varchar(10)

update MsCourse set [Type] = 'Standard' where Price < 550000
update MsCourse set [Type] = 'VIP' where price between 550000 and 600000
update MsCourse set [Type] = 'VVIP' where price > 600000

alter table MsCourse
drop column [Type]
select * from mscourse

--SOAL NO 9

alter table MsStudent
drop cx1

--SOAL NO 10

alter table MsStudent with nocheck
add constraint cx1 check (isnumeric(PhoneNumber)=1)

alter table MsStudent
drop cx1