Praktikum SBD 9c

/*****************************************************************
    soal 9
******************************************************************/

--1 tampilkan namakaryawan dalam huruf besar, EmployeeSalary dalam satuan juta dengan format 2 angka di blkg koma tambahkan kata ' million' di blkanngya , alamat karyawan dalam huruf kecil, EmployeePhone dimana EmployeeSalary di < 1500000, dan alamat karyawan mengandung huruf a dan diakhiri oleh angka 45
create view v1 as(
    select upper(EmployeeName) as [Employee's Name] ,cast(cast(cast(EmployeeSalary as numeric)/1000000 as numeric(3,2)) as varchar(20))+' million' as  [Salary],lower(EmployeeAddress) as [Address],EmployeePhone
    from MsEmployee
    where EmployeeAddress like '%a%45'
    and EmployeeSalary < 1500000
)
select * from v1
drop view v1

--2
create view v2 as(
    select replace(EmployeeCode,'KK','Kyuch Employee ') as [Employee Code],EmployeeName,EmployeePhone
    from MsEmployee
    where right(EmployeeAddress,1)%2=0
    and EmployeeSalary between 1000000 and 1500000
)
select * from v2
drop view v2

--3  tampilkan kodetransaksi, inisial karyawan yang di dapat dari huruf pertama dari nama pertama karyawan dan huruf pertama dari nama terakhir karyawan dalam huruf besar, inisial pelanggan yang didapat dari dua huruf pertama dari depan dalam huruf besar, renttime dengan format dd/mm/yyyy dimana huruf terakhir dari nama pertama karyawan berakhiran huruf i, nama kedua dari karyawan berkahiran huruf i
create view v3 as(
    select TransactionCode,upper(left(EmployeeName,1)+substring(employeename,charindex(' ',employeename)+1,1)) as [Employee Initial],upper(substring(CustomerName,1,2)) as [Customer Initial],convert(varchar(20),RentTime,103) as 'Rent Date'
    from RentalHeader th,MsCustomer mp, MsEmployee mk
    where th.EmployeeCode=mk.EmployeeCode and th.CustomerCode=mp.CustomerCode
    and EmployeeName like '%i %i'
)
select * from v3
drop view v3

--4 tampilkan EmployeeCode, namakaryawan, nomor rumah dari alamat karyawan dimana karyawan tersebut pernah melakukan transaksi pada bulan januari dan nomor alamat karyawan habis dibagi 4 dan namakaryawan di awali oleh huruf V-Z
create view v4 as(
    select EmployeeCode,EmployeeName,reverse(left(reverse(EmployeeAddress),charindex('.',reverse(EmployeeAddress))-1)) as [Address Number]
    from MsEmployee
    where EmployeeCode in(
        select distinct EmployeeCode from RentalHeader
        where datename(month,RentTime)='January'
    )
    and cast(reverse(left(reverse(EmployeeAddress),charindex('.',reverse(EmployeeAddress))-1))as int)%4=0
    and EmployeeName like '[V-Z]%'
)
select * from v4
drop view v4

--5 tampilkan kodetransaksi,CustomerName,LicensePlate,CarName,biayatransaksi dimana transaksi peminjaman mobil dengan biaya tertinggi pada bulan january
create view v5 as(
    select th.TransactionCode,CustomerName,td.LicensePlate,CarName,max(biaya)as [Highest Transaction in January] from (
        select ceiling(cast(datediff(minute,RentTime,ReturnTime) as numeric)/60)*PricePerHour as biaya
        from RentalHeader th, RentalDetail td,MsCar mm, CarDetail mCarName
        where th.TransactionCode=td.TransactionCode and mm.LicensePlate=td.LicensePlate and mCarName.CarCode=mm.CarCode
        and datename(month,RentTime)='January'
    ) as a, MsCustomer mp,RentalHeader th, RentalDetail td,MsCar mm, CarDetail mCarName
    where th.CustomerCode=mp.CustomerCode and th.TransactionCode=td.TransactionCode and mm.LicensePlate=td.LicensePlate and mCarName.CarCode=mm.CarCode
    group by th.TransactionCode,CustomerName,RentTime,ReturnTime,PricePerHour,td.LicensePlate,CarName
    having max(biaya)=ceiling(cast(datediff(minute,RentTime,ReturnTime) as numeric)/60)*PricePerHour
)
select * from v5
drop view v5

--6 tampilkan LicensePlate, namamobil di dapat dari kata setelah spasi pertama, biaya perjam dengan menambahkan kata 'Rp. ' di depannya dan kata '.00' di belakangnya dimana mobil tersebut tidak pernah disewa dan CarName mobil adalah honda
create view v6 as(
    select m.LicensePlate,substring(CarName,charindex(' ',CarName)+1,len(CarName)) as [Car Type],'Rp. '+cast(PricePerHour as varchar(20))+'.00' as [Price Per Hour]
    from MsCar m,CarDetail mm
    where LicensePlate not in(
        select distinct LicensePlate from RentalDetail
    )
    and m.CarCode=mm.CarCode
    and CarName like 'Honda%'
)
select * from v6
drop view v6

--7 tampilkan LicensePlate,CarName,PricePerHour,kapasitas bensin yang didapat dari penjumlahan tiap digit dari LicensePlate * 10 dimana CarName mengandung huruf kedua  dari akhir
create view v7 as(
    select LicensePlate,CarName,PricePerHour, cast(((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) as varchar(20))+' liter' as [Fuel Capacity]
    from MsCar mm,CarDetail mCarName
    where mCarName.CarCode=mm.CarCode
    and CarName like '%'+substring(licenseplate,len(licenseplate)-1,1)+'%'
)
select * from v7
drop view v7

--8 tampilkan EmployeeCode,EmployeeName,EmployeeSalary,persentase komisi yang di dapatkan dari jumlah mobil yang dipinjam oleh pelanggan dan di layani oleh karyawan tersebut, komisi dalam ribuan di dapat dari persentase komisi*EmployeeSalary/100 dimana persentase komisi >2 dan komisi dalam satuan ribuan adalah bilangan ganjil
create view v8 as(
    select mk.EmployeeCode,EmployeeName,'Rp. '+cast(EmployeeSalary as varchar(20))+',00' as [Employee Salary],cast(count(LicensePlate) as varchar(20))+' %' as [Bonus Percentage],cast((cast((count(LicensePlate)*EmployeeSalary/100) as int)/1000)as varchar(20))+' thousand Rupiah' as [Bonus]
    from MsEmployee mk,RentalHeader th,RentalDetail td
    where mk.EmployeeCode=th.EmployeeCode and td.TransactionCode=th.TransactionCode
    group by mk.EmployeeCode,EmployeeName,EmployeeSalary
    having count(LicensePlate) >2 and (cast((count(LicensePlate)*EmployeeSalary/100) as int)/1000)%2=1
)
select * from v8
drop view v8

--9 tampilkan LicensePlate,namamobil di dapat dari kata sesudah spasi , jumlah peminjaman di dapat dari jumlah mobil yang pernah di sewa selama bulan januari dimana CarName mobil bukan suzuki dan digit pertama dari LicensePlate tidak sama dengan jumlah peminjaman
create view v9 as(
    select mm.LicensePlate,substring(CarName,charindex(' ',CarName)+1,len(CarName)) as [Car Name],cast(count(mm.LicensePlate)as varchar(20))+' time(s)' as [Rent Amount]
    from RentalDetail td,CarDetail mCarName,MsCar mm, RentalHeader th
    where mm.LicensePlate=td.LicensePlate and mCarName.CarCode=mm.CarCode and th.TransactionCode=td.TransactionCode
    and CarName not like 'Suzuki%'
    and datename(month,RentTime)='January'
    group by mm.LicensePlate,CarName
    having count(mm.LicensePlate)!=cast(substring(mm.LicensePlate,2,1)as int)
)
select * from v9
drop view v9


--10 tampilkan nama keluarga pelanggan yang di dapat dari kata terakhir, durasi peminjaman dalam hari, RentTime,ReturnTime dimana durasi peminjaman lebih dari 1 hari dan namapelanggan terdiri dari 2 kata dan menit pengembalian mobil lebih besar dari 30
create view v10 as(
    select reverse(left(reverse(CustomerName),charindex(' ',reverse(CustomerName))-1)) as [Last Name],cast(cast((ceiling(cast(datediff(minute,RentTime,ReturnTime) as numeric)/60)/24)as int) as varchar(20))+' Days' as [Duration],RentTime,ReturnTime
    from RentalHeader th,RentalDetail td,MsCustomer mp,MsEmployee mk
    where th.TransactionCode=td.TransactionCode and mp.CustomerCode=th.CustomerCode and mk.EmployeeCode=th.EmployeeCode
    and cast((ceiling(cast(datediff(minute,RentTime,ReturnTime) as numeric)/60)/24)as int) > 1
    and CustomerName like '% %'
    and CustomerName not like '% % %'
    and datename(minute,ReturnTime)>30
)
select * from v10
drop view v10