1.
Insertlah
data di bawah ini:
MsEmployee
EmployeeCode
|
EmployeeName
|
EmployeeAddress
|
EmployeePhone
|
EmployeeSalary
|
KK001
|
Wejet Jetjet
|
Jl. Piggy no.8
|
0818056457
|
1300000
|
KK002
|
Bobby Aja
|
Jl. Malioboro no.4
|
0819964780
|
1200000
|
KK003
|
Sugiri Giri
|
Jl. suka suka no.958
|
08155555555
|
1700000
|
KK004
|
Yandi Acek
|
Jl. Bahagia no.456
|
08131145622
|
1800000
|
KK005
|
Rheza Aggi
|
Jl. Kemakmuran no.10
|
0816523781
|
1100000
|
KK006
|
Devina Depe
|
Jl. jalan no.45
|
0816523890
|
1300000
|
KK007
|
Amalia Ameng
|
Jl. Merdeka no.1945
|
0823564780
|
1500000
|
KK008
|
Dewi Kyuk
|
Jl. Hepi no.542
|
0856964780
|
1800000
|
KK009
|
Ivan Darma
|
Jl. Kambing no.31
|
0819999980
|
1900000
|
KK010
|
Fredy Kupred
|
Jl. Kerbau no.99
|
0819945680
|
1750000
|
MsCustomer
CustomerCode
|
CustomerName
|
CustomerAddress
|
CustomerPhone
|
KP001
|
Daniel Suipuit Suex
|
Jl. Sutomo no.57
|
081899656745
|
KP002
|
Ryan Iyan
|
Jl. Sutopo no.32
|
081199652023
|
KP003
|
Dion Dewaman
|
Jl. Kucing no.123
|
081356789821
|
KP004
|
Nopi Enpe
|
Jl. Lemot Bgt no.33
|
081567789834
|
KP005
|
Robertos Eres
|
Jl. Damai Banget no.128
|
081914678913
|
CarDetail
CarCode
|
CarName
|
PricePerHour
|
KM001
|
Honda Jazz
|
26000
|
KM002
|
Toyota Avanza
|
23000
|
KM003
|
Toyota Yaris
|
25000
|
KM004
|
Honda Freed
|
30000
|
KM005
|
Toyota Alphard
|
34000
|
KM006
|
Suzuki Swift
|
24000
|
KM007
|
Honda City
|
27000
|
KM008
|
Toyota Innova
|
28000
|
KM009
|
Suzuki APV
|
27000
|
KM010
|
Toyota Fortuner
|
30000
|
KM011
|
Toyota Rush
|
28000
|
KM012
|
Honda CR-V
|
31000
|
KM013
|
Honda Civic
|
28000
|
MsCar
LicensePlate
|
CarCode
|
B1111DK
|
KM006
|
B1234XX
|
KM001
|
B1285WE
|
KM002
|
B1763CV
|
KM011
|
B2548PQ
|
KM010
|
B4444WD
|
KM007
|
B5287VQ
|
KM013
|
B5555GK
|
KM008
|
B6666RP
|
KM009
|
B7777GG
|
KM003
|
B8888MB
|
KM010
|
B8889EW
|
KM005
|
B9101YY
|
KM002
|
B9342JK
|
KM004
|
B9654QW
|
KM012
|
RentalHeader
TransactionCode
|
EmployeeCode
|
CustomerCode
|
RentTime
|
TR001
|
KK002
|
KP001
|
2010-01-20 15:00:00.000
|
TR002
|
KK001
|
KP002
|
2010-01-21 13:31:00.000
|
TR003
|
KK003
|
KP003
|
2010-01-21 11:50:00.000
|
TR004
|
KK005
|
KP004
|
2010-01-23 16:10:00.000
|
TR005
|
KK004
|
KP005
|
2010-01-24 12:30:00.000
|
RentalDetail
TransactionCode
|
LicensePlate
|
ReturnTime
|
TR001
|
B1234XX
|
2010-01-22 16:42:00.000
|
TR001
|
B7777GG
|
2010-01-25 10:54:00.000
|
TR001
|
B9101YY
|
2010-01-23 18:16:00.000
|
TR001
|
B9342JK
|
2010-01-25 13:36:00.000
|
TR002
|
B1111DK
|
2010-01-22 10:45:00.000
|
TR002
|
B8889EW
|
2010-01-22 10:45:00.000
|
TR003
|
B1763CV
|
2010-01-23 14:08:00.000
|
TR003
|
B4444WD
|
2010-01-23 13:38:00.000
|
TR004
|
B1285WE
|
2010-01-25 16:33:00.000
|
TR004
|
B2548PQ
|
2010-01-25 11:03:00.000
|
TR004
|
B8888MB
|
2010-01-25 16:53:00.000
|
TR005
|
B5555GK
|
2010-01-25 13:22:00.000
|
TR005
|
B8889EW
|
2010-01-25 11:18:00.000
|
2.
Update CustomerName menjadi Ryan Jombang dimana CustomerName adalah Ryan Iyan
(update)
3.
Update EmployeeSalary, tambahkan sebesar 50% dari
gaji sebelumnya dimana digit
terakhir dari EmployeeCode merupakan bilangan ganjil
(update,right)
4.
Update PricePerHour menjadi 24000 dimana LicensePlate adalah B9101YY
(update)
5.
Hapuslah data dari tabel CarDetail dimana CarName dari mobil adalah ‘Toyota’ dan CarName diakhiri dengan
huruf ‘a’.
(delete,like)
6.
Hapuslah data dari tabel MsCar dimana hasil perkalian dari setiap
digit LicensePlate * 10 lebih besar dari PricePerHour dan panjang karakter dari
nama mobil yang di dapat dari kata setelah spasi pertama lebih besar dari 7.
(delete,cast,substring,len,charindex)
7.
Tambahkan kolom status pada tabel MsCar dan
ubahlah status menjadi ‘Booked’ jika
PricePerHour di antara 25000 sampai dengan 30000 dan ubahlan status menjadi ‘Available’ jika PricePerHour tidak di
antara 25000 sampai dengan 30000.
(alter table,update,between)
8.
Tambahkan constraint pada tabel RentalHeader
dimana 3 digit terakhir dari EmployeeCode harus angka dengan nama constraint ‘constraint’ data yang sudah ada tidak
perlu di cek lagi, kemudian hapus constraint tersebut.
(alter table, isnumeric)
9.
Tambahkan constraint pada tabel MsEmployee dimana
EmployeeAddress harus di awali dengan kata ‘Jl.
‘, lakukan pengecekan pada data yang sudah ada sebelumnya, kemudian hapus
constraint tersebut.
(alter table,check,like)
10.
Hapuslah table RentalDetail(drop table)
Jawab
/*****************************************************************
soal 8
******************************************************************/
--1
insert into MsEmployee values('KK001','Wejet Jetjet','Jl. Piggy no.8','0818056457',1300000)
insert into MsEmployee values('KK002','Bobby Aja','Jl. Malioboro no.4','0819964780',1200000)
insert into MsEmployee values('KK003','Sugiri Giri','Jl. suka suka no.958','08155555555',1700000)
insert into MsEmployee values('KK004','Yandi Acek','Jl. Bahagia no.456','08131145622',1800000)
insert into MsEmployee values('KK005','Rheza Aggi','Jl. Kemakmuran no.10','0816523781',1100000)
insert into MsEmployee values('KK006','Devina Depe','Jl. jalan no.45','0816523890',1300000)
insert into MsEmployee values('KK007','Amalia Ameng','Jl. Merdeka no.1945','0823564780',1500000)
insert into MsEmployee values('KK008','Dewi Kyuk','Jl. Hepi no.542','0856964780',1800000)
insert into MsEmployee values('KK009','Ivan Darma','Jl. Kambing no.31','0819999980',1900000)
insert into MsEmployee values('KK010','Fredy Kupred','Jl. Kerbau no.99','0819945680',1750000)
insert into MsCustomer values('KP001','Daniel Suipuit Suex','Jl. Sutomo no.57','081899656745')
insert into MsCustomer values('KP002','Ryan Iyan','Jl. Sutopo no.32','081199652023')
insert into MsCustomer values('KP003','Dion Dewaman','Jl. Kucing no.123','081356789821')
insert into MsCustomer values('KP004','Nopi Enpe','Jl. Lemot Bgt no.33','081567789834')
insert into MsCustomer values('KP005','Robertos Eres','Jl. Damai Banget no.128','081914678913')
insert into CarDetail values('KM001','Honda Jazz',26000)
insert into CarDetail values('KM002','Toyota Avanza',23000)
insert into CarDetail values('KM003','Toyota Yaris',25000)
insert into CarDetail values('KM004','Honda Freed',30000)
insert into CarDetail values('KM005','Toyota Alphard',34000)
insert into CarDetail values('KM006','Suzuki Swift',24000)
insert into CarDetail values('KM007','Honda City',27000)
insert into CarDetail values('KM008','Toyota Innova',28000)
insert into CarDetail values('KM009','Suzuki APV',27000)
insert into CarDetail values('KM010','Toyota Fortuner',30000)
insert into CarDetail values('KM011','Toyota Rush',28000)
insert into CarDetail values('KM012','Honda CR-V',31000)
insert into CarDetail values('KM013','Honda Civic',28000)
insert into MsCar values('B1234XX','KM001')
insert into MsCar values('B9101YY','KM002')
insert into MsCar values('B7777GG','KM003')
insert into MsCar values('B9342JK','KM004')
insert into MsCar values('B8889EW','KM005')
insert into MsCar values('B1111DK','KM006')
insert into MsCar values('B4444WD','KM007')
insert into MsCar values('B5555GK','KM008')
insert into MsCar values('B6666RP','KM009')
insert into MsCar values('B8888MB','KM010')
insert into MsCar values('B1763CV','KM011')
insert into MsCar values('B9654QW','KM012')
insert into MsCar values('B5287VQ','KM013')
insert into MsCar values('B1285WE','KM002')
insert into MsCar values('B2548PQ','KM010')
insert into RentalHeader values('TR001','KK002','KP001','01/20/2010 15:00:00')
insert into RentalHeader values('TR002','KK001','KP002','01/21/2010 13:31:00')
insert into RentalHeader values('TR003','KK003','KP003','01/21/2010 11:50:00')
insert into RentalHeader values('TR004','KK005','KP004','01/23/2010 16:10:00')
insert into RentalHeader values('TR005','KK004','KP005','01/24/2010 12:30:00')
insert into RentalDetail values('TR001','B1234XX','01/22/2010 16:42:00')
insert into RentalDetail values('TR001','B9101YY','01/23/2010 18:16:00')
insert into RentalDetail values('TR001','B7777GG','01/25/2010 10:54:00')
insert into RentalDetail values('TR001','B9342JK','01/25/2010 13:36:00')
insert into RentalDetail values('TR002','B8889EW','01/22/2010 10:45:00')
insert into RentalDetail values('TR002','B1111DK','01/22/2010 10:45:00')
insert into RentalDetail values('TR003','B4444WD','01/23/2010 13:38:00')
insert into RentalDetail values('TR003','B1763CV','01/23/2010 14:08:00')
insert into RentalDetail values('TR004','B2548PQ','01/25/2010 11:03:00')
insert into RentalDetail values('TR004','B1285WE','01/25/2010 16:33:00')
insert into RentalDetail values('TR004','B8888MB','01/25/2010 16:53:00')
insert into RentalDetail values('TR005','B5555GK','01/25/2010 13:22:00')
insert into RentalDetail values('TR005','B8889EW','01/25/2010 11:18:00')
select * from MsEmployee
select * from MsCustomer
select * from CarDetail
select * from MsCar
select * from RentalHeader
select * from RentalDetail
--2 ubah nama pelanggan menjadi ryan jombang dimana namapelanggan Ryan Iyan
update MsCustomer set CustomerName='Ryan Jombang' where CustomerName='Ryan Iyan'
select * from MsCustomer
--3 tambahkan gaji karyawan sebesar 50% dimana digit terakhir dr kd karyawan merupakan bilangan ganjil
update MsEmployee set EmployeeSalary=(0.5*EmployeeSalary)+EmployeeSalary where right(EmployeeCode,1)%2=1
select * from MsEmployee
--4 ubahlah harga sewa mobil dengan no plat B9101YY menjadi 24000
update CarDetail set PricePerHour='24000' from MsCar m,CarDetail mm where m.CarCode=mm.CarCode and m.LicensePlate='B9101YY'
select * from CarDetail
--5 hapus data dari CarDetail dimana CarName dari mobil adalah toyota dan huruf terakhir dari CarName mobil adalah 'a'
delete from CarDetail where CarName like 'toyota%a'
select * from CarDetail
--6 hapus data dari MsCar dimana hasil perkalian dari LicensePlate mobil *10 > dari PricePerHour dan panjang karakter dari nama CarName mobil >7
delete MsCar from MsCar m join CarDetail mm on mm.CarCode=m.CarCode
where (cast(substring(LicensePlate,2,1) as int)*cast(substring(LicensePlate,3,1) as int)*cast(substring(LicensePlate,4,1) as int)*cast(substring(LicensePlate,5,1) as int))*10 > PricePerHour
and len(substring(CarName,charindex(' ',CarName)+1,len(CarName)))>7
select * from MsCar
--7 tambahkan column status pada MsCar dan ubah lah status menjadi 'Booked' dimana PricePerHour di antara 25000-30000 dan ubah lah status menjadi 'Available' dimana PricePerHour tidak di antara 25000-30000
alter table MsCar
add status varchar(20)
update MsCar set status='Booked' from MsCar m,CarDetail mm where m.CarCode=mm.CarCode and PricePerHour between 25000 and 30000
update MsCar set status='Avalable' from MsCar m,CarDetail mm where m.CarCode=mm.CarCode and PricePerHour not between 25000 and 30000
select * from MsCar
--8 tambahkan constraint pada tabel RentalHeader dimana 3 digit terakhir dari EmployeeCode harus angka dengan nama constraint 'constraint' kemudian hapus constraint tersebut
alter table RentalHeader with nocheck
add constraint [constraint] check(isNumeric(right(EmployeeCode,3))=1)
alter table RentalHeader
drop constraint [constraint]
select * from RentalHeader
--9 tambahkan constraint pada tabel MsEmployee dimana EmployeeAddress harus di awali dengan kata 'Jl. ' dan data yang sudah ada harus di cek, kemudian hapus constraint tsb
alter table MsEmployee with check
add constraint [constraint1] check(EmployeeAddress like 'Jl. %')
alter table MsEmployee
drop constraint [constraint1]
select * from MsEmployee
--10 hapus lah table RentalDetail
drop table RentalDetail
Tidak ada komentar:
Posting Komentar