Praktikum SBD 7b

/*****************************************************************
    soal 7
******************************************************************/
--1 tampilkan namakaryawan, tanggal sewa
select EmployeeName,convert(varchar(20),RentTime,103) as 'RentTime' from MsEmployee mk join RentalHeader th on mk.EmployeeCode = th.EmployeeCode
where day(RentTime)>20

--2  tampilkan namapelanggan,LicensePlate,RentTime dengan format dddd, dd MMM yyyy dimana RentTime dilakukan pada hari sabtu dan digit dari LicensePlate habis dibagi 5
select CustomerName,LicensePlate,datename(weekday,RentTime)+', '+convert(varchar(20),RentTime,6) as [Date] from MsCustomer mp join RentalHeader th on mp.CustomerCode=th.CustomerCode join RentalDetail td on td.TransactionCode=th.TransactionCode
where datename(weekday,RentTime)='Saturday'
and substring(LicensePlate,5,1)%5=0

--3 tampilkan TransactionCode,LicensePlate,durasi peminjaman mobil dalam satuan jam yang di dapat dari pengurangan RentTime dan ReturnTime
select th.TransactionCode,LicensePlate,cast(ceiling(cast(datediff(minute,RentTime,ReturnTime) as numeric)/60) as varchar(20))+' hour' as [Duration] from RentalHeader th join RentalDetail td on th.TransactionCode=td.TransactionCode

--4 tampilkan biaya peminjaman dan peminjam yang meminjam mobil tersebut dimana namapelanggan hanya terdiri dari 2 kata dan tampilkan total dari biaya transaksi tiap orang
select CustomerName,mm.LicensePlate,ceiling(cast(datediff(minute,RentTime,ReturnTime) as numeric)/60)*PricePerHour as [Fee]
from MsCustomer mp
join RentalHeader th on th.CustomerCode=mp.CustomerCode
join RentalDetail td on th.TransactionCode=td.TransactionCode
join MsCar mm on mm.LicensePlate=td.LicensePlate
join CarDetail mCarName on mCarName.CarCode=mm.CarCode
where CustomerName like '% %' and CustomerName not like '% % %'
order by CustomerName asc
compute sum(ceiling(cast(datediff(minute,RentTime,ReturnTime) as numeric)/60)*PricePerHour)
by CustomerName

--5 tampilkan TransactionCode, namapelanggan,namakaryawan di tambahkan kata 'Mr./Mrs. ' di depannya, tgl sewa dengan format tgl dd mmm yyyy dan total biaya dari transaksi tersebut
select th.TransactionCode,'Mr./Mrs. '+EmployeeName as [Employee's Name],'Mr./Mrs. '+CustomerName  as [Customer's Name],convert(varchar(20),RentTime,106) as [RentDate],
'Rp. '+cast(sum(ceiling(cast(datediff(minute,RentTime,ReturnTime) as numeric)/60)*PricePerHour)as varchar(20))+'.00' as [Total Fee]
from MsCustomer mp
join RentalHeader th on th.CustomerCode=mp.CustomerCode
join MsEmployee mk on mk.EmployeeCode=th.EmployeeCode
join RentalDetail td on th.TransactionCode=td.TransactionCode
join MsCar mm on mm.LicensePlate=td.LicensePlate
join CarDetail mCarName on mCarName.CarCode=mm.CarCode
group by th.TransactionCode,EmployeeName,CustomerName,RentTime
having sum(ceiling(cast(datediff(minute,RentTime,ReturnTime) as numeric)/60)*PricePerHour) between 1000000 and 1500000

--6
/*tampilkan nomor dari kodetransaksi,nama pertama pelanggan, kata kedua dari alamat, nomor telpon dgn format xxxx - xxxxxxxx
dimana pelanggan tersebut melakukan transaksi di antara tanggal 20-25
dan pelanggan tsb meminjam mobil lebih dari 2
dan nama pelanggan terdiri lebih dari 2 kata*/
select distinct cast(substring(td.TransactionCode,charindex('R',td.TransactionCode)+1,len(td.TransactionCode))as int)as[TransactionNumber],substring(CustomerName,1,charindex(' ',CustomerName)) as[FirstName],
substring(CustomerAddress,charindex(' ',CustomerAddress)+1,charindex(' ',substring(CustomerAddress,charindex(' ',CustomerAddress)+1,len(CustomerAddress)))-1) as [Address Name],
substring(CustomerPhone,1,4)+' - '+substring(CustomerPhone,5,len(CustomerPhone)) as [Phone Number]
from MsCustomer mp
join RentalHeader th on th.CustomerCode=mp.CustomerCode
join MsEmployee mk on mk.EmployeeCode=th.EmployeeCode
join RentalDetail td on th.TransactionCode=td.TransactionCode
where day(RentTime) between 20 and 25
and CustomerName like '% %'
group by CustomerName,CustomerAddress,CustomerPhone,td.TransactionCode
having count(td.LicensePlate)>2

--7  tampilkan nama pelanngan dimana huruf pertama pelanggan berada dia antara A-O, alamat pelanggan dari tabel MsCustomer dan gabungkan dengan nama karyawan,alamat karyawan, dimana karakter pertama alamat dari kata kedua berada di antara A-J
select CustomerName as [Name],CustomerAddress[Address] from MsCustomer
where CustomerName like '[A-O]%'
union all
select EmployeeName,EmployeeAddress from MsEmployee
where EmployeeAddress like '% [A-J]%'

--8 tampilkan CustomerCode , dan tanggal yang di dapat dari pengurangan tanggal hari ini dengan 10 dari tabel MsCustomer dan gabungkan dengan TransactionCode,tgl pengembalian dari tabel RentalDetail dimana nomor dari kodetransaksi adalah bilangan genap
select CustomerCode as [Code], convert(varchar(20),cast(datediff(day,right(CustomerCode,1)*10,getdate()) as datetime),106) as [Date] from MsCustomer
union all
select TransactionCode as [Code], convert(varchar(20),ReturnTime,106) as [Date] from RentalDetail
where right(TransactionCode,1)%2=0


--9 Berilah hak untuk insert, select, dan update tabel MsEmployee kesalah satu teman anda.
grant insert,select,update on MsEmployee to xxx

--10 Cabutlah hak akses yang sudah anda berikan kepada teman anda pada nomor sembilan tadi.
revoke insert,select,update on MsEmployee to xxx