Database praktikum pertemuan 8


Sintaks (Syntax)

JOIN

            SELECT field1, field2, field3
            FROM first_table
            [INNER JOIN / LEFT JOIN / RIGHT JOIN / FULL JOIN] second_table
            ON first_table.keyfield = second_table.foreign_keyfield

UNION

            SQL Statement 1
            [UNION / UNION ALL]
            SQL Statement 2

Tabel Relasional

Tabel Relasional
Tabel Relasional


–1. Tampilkan TreatmentTypeName, TreatmentName dan Price dimana TreatmentTypeName mengandung kata ‘hair’ atau diawali kata ‘nail’ dan harganya kurang dari 100000. (join, like) 

select TreatmentTypeName, TreatmentName, Price
from MsTreatment mt
join MsTreatmentType mtt on mt.TreatmentTypeId = mtt.TreatmentTypeId
where (TreatmentTypeName like ‘%hair%’ or TreatmentTypeName like ‘nail%’)
and Price < 100000

–2.Tampilkan StaffName dan StaffEmail (didapat dari huruf pertama StaffName ditambah nama terakhirStaffName dan diakhiri kata ‘@oosalon.com’ dengan format huruf kecil) dimana transaksi terjadi pada hari kamis dan data yang berulang hanya ditampilkan satu kali.(distinct, lower, left, reverse, charindex, join, datename, weekday, like) 

select distinct StaffName,
StaffEmail = LOWER(LEFT(StaffName, 1) +
REVERSE( LEFT( REVERSE (StaffName), CHARINDEX (‘ ‘, REVERSE (StaffName) )-1 ) ) ) + ‘@oosalon.com’
from MsStaff ms join HeaderSalonServices hs on ms.StaffId = hs.StaffId
where DATENAME(WEEKDAY, TransactionDate) like ‘Thursday’

–3. Tampilkan New Transaction ID  (didapat dari TransactionId dengan mengganti kata ‘TR’ menjadi ‘Trans’), Old Transaction ID (di dapat dari TransactionID), TransactionDate, StaffName dan CustomerName dimana selisih tanggal transaksi dan tanggal ’24 Desember 2012’ kurang dari atau sama dengan 2 hari. (replace, join, datediff, day) 

select [New Transaction ID] = REPLACE(TransactionId, ‘TR’, ‘Trans’),
[Old Transaction ID] = TransactionId, TransactionDate, StaffName, CustomerName
from MsStaff ms join HeaderSalonServices hs
on ms.StaffId = hs.StaffId
join MsCustomer mc
on hs.CustomerId = mc.CustomerId
where DATEDIFF(DAY, TransactionDate, ’12/24/2012′) = 2

–4. Tampilkan New Transaction Date (didapat dari TransactionDate di tambah 5 hari), Old Transaction Date (didapat dari TransactionDate) dan CustomerName dimana transaksi terjadi bukan pada tanggal 20. (dateadd, day, join, datepart) 

select [New Transaction Date] = DATEADD(DAY, 5, TransactionDate),
[Old Transaction Date] = TransactionDate, CustomerName
from HeaderSalonServices hs join MsCustomer mc
on hs.CustomerId = mc.CustomerId
where DATEPART(DAY, TransactionDate) != 20

–5. Tampilkan Day (didapat dari nama hari TransactionDate), CustomerName dan TreatmentName dimana StaffPosition diawali kata ‘TOP’ atau StaffGender adalah ‘Female’. Kemudian urutkan data berdasarkan CustomerName secara ascending. (datename, weekday, join, in, like, order by) 

select [Day] = DATENAME(WEEKDAY, TransactionDate), CustomerName, TreatmentName
from HeaderSalonServices hs join MsCustomer mc on hs.CustomerId = mc.CustomerId
join DetailSalonServices ds on hs.TransactionId = ds.TransactionId
join MsTreatment mt on ds.TreatmentId = mt.TreatmentId
where StaffId in(
select StaffId from MsStaff
where (StaffPosition like ‘TOP%’ or StaffGender = ‘Female’)
)
order by CustomerName

–6. Tampilkan 1 data teratas CustomerId, CustomerName, TransactionId dan Total Treatment (didapat dari banyaknya Treatment per transaksi). Dan urutkan data berdasarkan Total Treatment secara descending. (top, count, join, group by, order by) 

select top 1 mc.CustomerId, CustomerName, hs.TransactionId, [Total Treatment] = COUNT(TreatmentId)
from MsCustomer mc join HeaderSalonServices hs on mc.CustomerId = hs.CustomerId
join DetailSalonServices ds on hs.TransactionId = ds.TransactionId
group by mc.CustomerId, CustomerName, hs.TransactionId
order by [Total Treatment] desc

–7. Tampilkan CustomerId, TransactionId, CustomerName, dan Total Price (didapat dari jumlah Price  per transaksi) dimana Total Price di atas rata-rata Total Price. Kemudian urutkan data berdasarkan Total Price secara descending.(sum, join, avg, sum, group by, alias subquery, having, order by) 

select mc.CustomerId, ds.TransactionId, CustomerName, [Total Price] = SUM(Price)
from MsCustomer mc
join HeaderSalonServices hs on mc.CustomerId = hs.CustomerId
join DetailSalonServices ds on hs.TransactionId = ds.TransactionId
join MsTreatment mt on ds.TreatmentId = mt.TreatmentId,
(
select AVG([Total Price]) as rata2
from
(
select TransactionId, SUM(Price) as [Total Price]
from DetailSalonServices ds join MsTreatment mt on ds.TreatmentId = mt.TreatmentId
group by TransactionId
)b
)c
group by mc.CustomerId, ds.TransactionId, CustomerName, c.rata2
having SUM(Price) > c.rata2
order by [Total Price] desc

–8. Tampilkan Name (didapat dari StaffName dengan menambahkan kata ‘Mr. ’ di awal), StaffPosition dan StaffSalary dimana StaffGender adalah ‘Male’. Kemudian gabungkan dengan Name (didapat dari StaffName dengan menambahkan kata ‘Ms. ’ di awal), StaffPosition dan StaffSalary dimana StaffGender adalah ‘Female’. Kemudian urutkan data berdasarkan Name dan StaffPosition secara ascending. (union, order by) 

select Name = ‘Mr. ‘+StaffName, StaffPosition, StaffSalary
from MsStaff
where StaffGender = ‘Male’
union
select Name = ‘Ms. ‘+StaffName, StaffPosition, StaffSalary
from MsStaff
where StaffGender = ‘Female’
order by Name, StaffPosition

–9. Tampilkan TreatmentName, Price (didapat dari Price dengan menambahkan kata ‘Rp. ’ di awal) dan Status adalah ‘Maximum Price’ dimana Price merupakan Price terbesar dari daftar harga. Kemudian gabungkan dengan TreatmentName, Price (didapat dari Price dengan menambahkan kata ‘Rp. ’ di awal) dan Status adalah ‘Minimum Price’ dimana Price merupakan Price terkecil dari daftar harga. (cast, max, alias subquery, union, min) 

select TreatmentName, Price = ‘Rp. ‘+CAST(Price as varchar), Status = ‘Maximum Price’
from MsTreatment,
(select MAX(Price) as maks from MsTreatment) as x
where Price = x.maks
union
select TreatmentName, Price = ‘Rp. ‘+CAST(Price as varchar), Status = ‘Minimum Price’
from MsTreatment,
(select MIN(Price) as mins from MsTreatment) as x
where Price = x.mins

–10. Tampilkan Longest Name of Staff and Customer (didapat dari CustomerName), Length of Name (didapat dari panjang karakter CustomerName) dan Status adalah ‘Customer’ dimana Length of Name merupakan Length of Name terpanjang. Kemudian gabungkan dengan Longest Name of Staff and Customer (didapat dari StaffName), Length of Name (didapat dari panjang karakter StaffName) dan Status adalah ‘Staff’ dimana Length of Name merupakan Length of Name terpanjang. (len, max, alias subquery, union) 

select [Longest Name of Staff and Customer] = CustomerName,
[Length of Name] = LEN(CustomerName), Status = ‘Customer’
FROM MsCustomer,
(
select MAX(LEN(CustomerName)) as makslen
from MsCustomer) as x
where LEN(CustomerName) = x.makslen
union
select [Longest Name of Staff and Customer] = StaffName,
[Length of Name] = LEN(StaffName), Status = ‘Staff’
FROM MsStaff,
(
select MAX(LEN(StaffName)) as makslen
from MsStaff
) as x
where LEN(StaffName) = x.makslen

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.