Database Praktikum pertemuan 6


Sintaks (Syntax)

Berikut ini adalah beberapa fungsi aggregate :
1. Sum(nama_field) = menjumlahkan isi baris-baris pada field tersebut.
2. Count(nama_field) = untuk menghitung banyaknya baris pencacahan data.
3. Avg(nama_field)= untuk menghitung rata-rata dari isi baris- baris data.
4. Max(nama_field)= untuk menghitung nilai max dari baris-baris data.
5. Min(nama_field)= untuk menghitung nilai min dari baris-baris data.

Tambahan syntax :
1. Order by = untuk sorting data. defaultnya ascending.
2. Group by = untuk mengelompokkan data yang tidak menggunakan aggregate.
3. Having = untuk membuat syarat bagi fungsi aggregate yang kita pakai.
4. Compute by = untuk membuat subtotal.

Urutan penggunaan:
group by
having (untuk menggunakan having, harus menggunakan group by)
order by
compute by (untuk menggunakan compute by, harus menggunakan order by)

Tabel Relasional
Tabel Relasional

Soal (case)

/1. Tampilkan Maximum Price (didapat dari Price tertinggi), Minimum Price (didapat dari Price terendah) dan Average Price (didapat dari rata-rata Price yang dibulatkan dengan format dua digit  di belakang koma). 
(max, min, cast, round, avg)/
–Round-> bulatin angka sesudah koma
–numeric(11,2)->umum/bagusan pakai ini, 11->sampai triliun, 2->setelah koma 

select [Maximum Price] = MAX(Price),
[Minimum Price] = MIN(Price),
[Average Price] = cast(ROUND(AVG(Price),-3) as numeric(11,2))
from MsTreatment

/2.Tampilkan StaffPosition, Gender (didapat dari huruf pertama StaffGender)
dan Average Salary (didapat dari rata-rata StaffSalary yang dibulatkan dengan
format dua digit di belakang koma dan diawali dengan kata ‘Rp. ’).
(left, cast, avg, group by)/  

select StaffPosition, Gender=LEFT(StaffGender,1),
[Average Salary] = ‘Rp. ‘ +cast( cast(AVG(StaffSalary) as numeric(11,2) ) as varchar(100) )
from MsStaff

–kalau di-run bagian atas saja pasti error ->bingung 
alasan kenapa eror :
Msg 8120, Level 16, State 1, Line 1
Column ‘MsStaff.StaffPosition’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

group by SatffPosition, StaffGender

/3. Tampilkan Transaction Date (didapat dari TransactionDate dengan format ‘Mon dd, yyyy’)
dan Total Transaction per Day (didapat dari banyaknya transaksi per hari).
Kemudian tampilkan pula rata-rata dari Total Transaction per Day.
(convert, count, group by, compute, avg)/

select TransactionDate = convert(varchar(100), TransactionDate, 107 ),
[Total Transaction per Day]= count(TransactionId)
from HeaderSalonServices
group by TransactionDate
compute avg( count(TransactionId) )

 /4.Tampilkan CustomerGender (didapat dari CustomerGender dengan format huruf kapital)
dan Total Transaction (didapat dari banyaknya transaksi berdasarkan jenis kelamin Customer).
Dan tampilkan pula jumlah Total Transaction.
(upper, count, group by, compute, sum)/ 

select upper(CustomerGender), [Total Transaction]= count(TransactionId)
from MsCustomer a, HeaderSalonServices b
where a.CustomerId = b.CustomerId
group by CustomerGender
compute sum(count(TransactionId) )              –compute [Total Transaction] -> error

/5. Tampilkan TreatmentTypeName, Total Transaction (didapat dari banyaknya transaksi)
kemudian urutkan data berdasarkan Total Transaction secara descending
(count, group by, order by)/ 

select TreatmentTypeName, [Total Transaction]=count(TransactionId)
from MsTreatmentType a, MsTreatment b,DetailSalonServices c
where a.TreatmentTypeId = b.TreatmentTypeId and b.TreatmentId = c.TreatmentId
group by TreatmentTypeName
order by count(TransactionId) desc;
–Cara 2 :order by [Total Transaction] desc;–>sukses

–pada convert , tipe data yg digunakan -> varchar karena kita cuma buat template saja
/tabel ada 2 :
1. tabel master(tidak terikat, berdiri dgn sendirinya) 
2. tabel transaction(bergubungan dgn tabel master(PK dan FK) )/
–composite key -> satu tabel ada 2 primary key, bisa jg ada (PK1, PK2)
–entity : strong , weak
–having mirip where khusus kondisi yg ada aggregate

/Urutan query : 
Select
FROM
WHERE
Group by
Having
Order by
Compute
/

/6.Tampilkan Date (didapat dari tanggal terjadinya transaksi dengan format ‘dd mon yy’)
dan Revenue per Day (didapat dari jumlah Price dan di awali dengan kata ‘Rp. ’) dimana
Revenue per Day diantara 1000000 dan 5000000.
(convert, cast, sum, group by, having, between)/ 

select Date = convert(varchar(100), TransactionDate, 106) ,
[Revenue per Day] = ‘Rp. ‘ + cast( sum(Price) as varchar (100) )
from HeadersalonServices a, Ms.Treatment b, DetailSalonServices c
where a.TreatmentTypeId = b.TreatmentTypeId and b.TreatmentId = c.TreatmentId
group by TransactionDate
having sum(Price) between 1000000 and 5000000

/7. Tampilkan ID (didapat dari TreatmentTypeID dengan mengganti kata ‘TT0’ menjadi ‘Treatment Type ’), TreatmentTypeName dan Total Treatment per Type (didapat dari banyaknya TreatmentID dengan
diakhiri kata ‘ Treatment’) dimana Total Treatment per Type lebih besar dari 5.
Kemudian urutkan data berdasakan Total Treatment per Type secara descending.
(replace, cast, count, group by, having, order by)/ 

select ID = replace(TreatmentTypeId,’TTo’,’TreatmentType’), TreatmentTypeName,
[Total Treatment per Type] = cast( count(TreatmentId) as varchar(100) ) + ‘Treatment’
from MsTreatmentType a, MsTreatment b
where a.TreatmentTypeId = b.TreatmentTypeId
group by a.TreatmentTypeId, TreatmentTypeName
having COUNT(TreatmentId) >5
order by COUNT(TreatmentId) desc;

/8. Tampilkan StaffName (didapat dari nama pertama StaffName), TransactionID
dan Total Treatment per Transaction (didapat dari banyaknya treatment per transaksi).
Tampilkan pula Total Treatment per Transaction terbesar.
(left, charindex, count, group by, compute, max)/ 

select StaffName = LEFT( StaffName,charindex(‘ ‘,StaffName)-1 ) , TransactionId,
[Total Treatment per Transaction]=count(TreatmentId)
from MsStaff a, HeaderSalonServices b, DetailSalonServices c
where a.StaffId = b.StaffId and b.TransactionId = c.TransactionId
group by StaffName, b.TransactionId
compute max( count(TreatmentId) )

–Kalau group by StaffName, TransactionId ->error
alasan error :
 Msg 209, Level 16, State 1, Line 5
Ambiguous column name ‘TransactionId’.

/9. Tampilkan TransactionDate, CustomerName, TreatmentName dan Price dimana Transaksi terjadi pada hari kamis dan StaffName diawali kata ‘Ryan’.
Tampilkan pula total Price berdasarkan TransactionDate dan CustomerName.
(datename, weekday, like, order by, compute by, sum)/

select TransactionDate, CustomerName, TreatmentName dan Price
from MsCustomer a, MsStaff b, HeaderSalonServices c, DetailSalonServices d , MsTreatment e
where c.TransactionId = d.TransactionId
and b.StaffId = c.StaffId
and a.CustomerId = c.CustomerId
and d.TreatmentId = e.TreatmentId
and datename(weekday,TransactionDate)=’thursday’
and Staff Name like ‘Ryan%’
order by TransactionDate, CustomerName
compute sum(Price) byTransactionDate, CustomerName

/10. Tampilkan TransactionDate, CustomerName dan TotalPrice (didapat dari total Price) dimana transaksi terjadi setelah tanggal 20.
Tampilkan pula grand TotalPrice dan minimum TotalPrice berdasarkan tanggal transaksi.
Urutkan pula data berdasarkan tanggal transaksi.
(sum, day, group by, order by, compute by, min)/ 

select TransactionDate, CustomerName, [Total Price] = sum(Price)
from HeaderSalonServices a, MsCustomer b, DetailSalonServices c, MsTreatment d
where a.CustomerId = b.CustomerId and a.TransactionId = c.TransactionId and c.TreatmentId = d.TreatmentId
and day(TransactionDate) >20
group by TransactionDate, CustomerName
order by TransactionDate
compute sum( sum(price) ) , min( sum(Price) ) by TransactionDate

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.