[ File # csp10909445, License # 3089529 ]
Licensed through http://www.canstockphoto.com in accordance with the End User License Agreement (http://www.canstockphoto.com/legal.php)
(c) Can Stock Photo Inc. / z_amir

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

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s