Praktikum SBD 7a

 Soal

1.         Tampilkan CashierID, CashierName, CustomerName, dan CustomerAddress dimana CashierName diakhiri dengan huruf 'o' dan panjang CustomerAddress merupakan angka genap.

(join, like, len)

         

2.         Tampilkan CustomerID, CustomerName, dan CashierName dimana tanggal transaksi penjualan terjadi pada tanggal 1 dan selisih antara tanggal 1 Februari 2010 dan tanggal transaksi kurang dari 1 bulan.

(join, month, datediff, day)

       

3.         Tampilkan CashierID, CashierName, dan Bonus (didapat dari ‘Rp.’+ jumlah transaksi * 100000) dimana ProductCategory merupakan 'Motherboard' dan TransactionID diakhiri dengan angka 5.

(cast,count,join,group by, right)

       

4.         Tampilkan CashierId, CashierName, CashierChildren (didapat dari digit terakhir CashierID) IncomeTaxPercentage (didapat dari hasil pembulatan antara total Qty dikali 0.1) dimana DomainEmail Customer adalah 'qita.net' digabungkan dengan CashierId, CashierName, CashierChildren dan IncomeTaxPercentage dimana CashierTaxPercentage di antara 0 hingga 10.

(union, sum, round, substring, charindex, len, in, group by, having, join, right,between)

5.         Tampilkan ProductID, ProductName, CustomerName, dan CostumerAge (didapat dari digit terakhir ProductCategoryID + digit terakhir ProductID + digit terakhir CashierID * panjang CustomerName) dimana CustomerAge * 60 lebih besar dari tahun TransactionDate dan huruf ke-3 dari CustomerName berkisar antara 'r-t'.

(join, right, substring, like, len, year, cast)

       

6.         Tampilkan ProductID, ProductName, ProductPrice dimana CustomerName diakhiri huruf 'm' dan transaksi berlangsung antara bulan Januari sampai Maret 2010.

(join,in,month,like,between)

7.         Tampilkan ProductID, ProductName, CustomerName dimana transaksi terjadi pada bulan Mei dan dilayani oleh kasir yang memiliki nama mengandung huruf ‘a’

(join, datename,like, month, in)

8.         Tampilkan CashierID, CashierName, TanggalTransaksi, Average Sold Product (didapat dari rata-rata Qty) dimana NamaProductCategory adalah 'Motherboard' dan tanggal terjadinya transaksi berlangsung pada tanggal 3 dan diurutkan berdasarkan CashierName.

Tampilkan pula total dari Rata Rata Jual

(join, avg, like , group by, day, compute, sum, order by)

9.         Berikan hak akses kepada salah satu teman anda untuk melakukan insert, update, delete pada tabel MsProduct.

(grant)

10.     Cabutlah hak akses delete yang telah diberikan kepada teman anda pada tabel MsProduct.

        (revoke)

Jawab :

--1
select mcas.CashierID, CashierName, CustomerName,CustomerAddress
from MsCashier mcas
join TrHeaderSell ths on mcas.CashierID = ths.CashierID
join MsCustomer mcus on ths.CustomerID = mcus.CustomerID
where CashierName like '%o'
and len(CustomerAddress)%2 = 0

--2
select mcus.CustomerID, CustomerName, CashierName
from MsCustomer mcus
join TrHeaderSell ths on mcus.CustomerID = ths.CustomerID
join MsCashier mcas on ths.CashierID = mcas.CashierID
where day(TransactionDate) = 1 and
datediff(month,'2/1/2010',TransactionDate) < 1

--3
select mcas.CashierID,CashierName, 'Rp. ' + cast(count(ths.TransactionID)*100000 as varchar) as 'Bonus'
from MsCashier mcas
join TrHeaderSell ths on ths.CashierID = mcas.CashierID
join TrDetailSell tds on tds.TransactionID = ths.TransactionID
join MsProduct mpro on mpro.ProductID = tds.ProductID
join MsProductCategory mpc on mpc.ProductCategoryID = mpro.ProductCategoryID
where ProductCategory = 'MotherBoard' and right(ths.transactionID,1)=5
group by mcas.CashierID,CashierName

--4
select mcas.CashierID, CashierName, right(mcas.CashierID,1) as CashierChildren,
round(sum(qty*0.1),0) as IncomeTaxPercentage from MsCashier mcas
join TrHeaderSell ths on mcas.CashierId = ths.CashierId
join TrDetailSell tds on tds.TransactionID = ths.TransactionID
where
    customerId in
    (
        select customerID from MsCustomer cus
        where substring(CustomerEmail,charIndex('@',CustomerEmail)+1,len(customeremail)) = 'qita.net'
    )
group by mcas.CashierID, CashierName, right(mcas.CashierID,1)

union

select mcas.CashierId, CashierName, right(mcas.CashierID,1)  as CashierChildren,
round(sum(qty*0.1),0) as CashierTaxPercentage
from MsCashier mcas
join TrHeaderSell ths on ths.CashierID = mcas.CashierID
join TrDetailSell tds on tds.TransactionID = ths.TransactionID

group by mcas.CashierId, CashierName, right(mcas.CashierID,1)
having sum(round(qty*0.1,0))  between 0 and 10

--5
select mpro.ProductID, ProductName, CustomerName, cast(right(mpro.ProductCategoryID,1) + right(mpro.ProductID,1)
+ right(ths.CashierID,1) * len(CustomerName) as varchar) +' years' as 'CustomerAge'
from MsCustomer mcus
join TrHeaderSell ths on mcus.CustomerID = ths.CustomerID
join TrDetailSell tds on ths.TransactionID = tds.TransactionID
join MsProduct mpro on tds.ProductID = mpro.ProductID
join MsProductCategory mpc on mpro.ProductCategoryID = mpc.ProductCategoryID
where right(mpro.ProductCategoryID,1) + right(mpro.ProductID,1) + right(ths.CashierID,1) * len(CustomerName) * 60 < year(TransactionDate)
and substring(CustomerName,3,1) like '[r-t]'

--6
select msp.ProductID, ProductName, ProductPrice from MsProduct msp
join TrDetailSell tdp on msp.ProductID = tdp.ProductID
where TransactionID in
(
    select TransactionID from TrHeaderSell thp
    where thp.TransactionID = tdp.TransactionID
    and month(TransactionDate) between 1 and 3
    and CustomerID in
    (
        select CustomerID from MsCustomer msk
        where msk.CustomerID = thp.CustomerID
        and CustomerName like '%m'
    )
)

--7
select msp.ProductID, ProductName, CustomerName
from MsProduct msp
join TrDetailSell tdp on msp.ProductID = tdp.ProductID
join TrHeaderSell thp on tdp.TransactionID = thp.TransactionID
join MsCustomer msk on thp.CustomerID = msk.CustomerID
where datename(month,TransactionDate) = 'May'
and CashierID in
(
    select CashierID from MsCashier Ms
    where Ms.CashierID = Thp.CashierID and    CashierName like '%a%'
)
--8
select msk.CashierID, CashierName, TransactionDate, avg(Qty) as [Average of Sold Product] from
MsCashier msk
join TrHeaderSell thp on msk.CashierID = thp.CashierID
join TrDetailSell tdp on thp.TransactionID = tdp.TransactionID
join MsProduct msp on tdp.ProductID = msp.ProductID
join MsProductCategory mjp on msp.ProductCategoryID = mjp.ProductCategoryID
where ProductCategory like 'Motherboard'
and day(TransactionDate) = 3
group by msk.CashierID, CashierName, TransactionDate
order by CashierName
compute sum(avg(Qty))

--9
grant insert,update,delete
on Product
to xxxxxx (disesuaikan)

--10
revoke delete
on Product
from xxxxxxx (disesuaikan)