[ 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 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 and5000000

 

/*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

compte 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

 

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