SQL: DML (2)

Basis Data

Pertemuan 07

Perintah SELECT – Aggregate(1)

Standar ISO mendefinisikan lima fungsi aggregate :

COUNT Mengembalikan angka dari nilai dalam kolom tertentu

SUM Mengembalikan jumlah dari nilai yang terdapat dalam kolom

AVG Mengembalikan rata-rata dari nilai yang ada dalam kolom

MIN Mengembalikan nilai terkecil dari nilai yang terdapat dalam kolom

MAX Mengembalikan nilai terbesar dari nilai yang terdapat dalam kolom

Perintah SELECT – Aggregate(2)

Setiap fungsi beroperasi pada satu kolom dan mengembalikan satu nilai tunggal.

COUNT, MIN, dan MAX digunakan untuk field numerik dan non-numerik, sedangkan SUM dan AVG hanya dapat digunakan pada field numerik.

Bagian dari COUNT(*), pertama setiap fungsi mengeliminasi null dan mengoperasikan nilai non-null.

COUNT(*) menghitung seluruh baris dalam tabel, walaupun terdapat null atau duplikasi.

Menggunakan DISTINCT sebelum nama kolom untuk menghilangkan duplikasi.

Perintah SELECT – Aggregate(3)

DISTINCT tidak berpengaruh terhadap operasi MIN/MAX, tetapi berpengaruh pada SUM/AVG.

Fungsi Aggregate dapat digunakan dalam daftar SELECT dan clause HAVING clause.

Jika daftar SELECT menyertakan fungsi aggregate dan tidak terdapat clause GROUP BY, daftar SELECT tidak dapat mengacu ke kolom dengan fungsi aggregate. Contoh berikut adalah salah :

SELECT staffNo, COUNT(salary) FROM Staff;

Contoh : Kegunaan COUNT(*)

Tampilkan banyaknya properti yang mempunyai biaya sewa lebih dari £350 per bulan

SELECT COUNT(*) AS count

FROM PropertyForRent

WHERE rent > 350;

Kegunaan COUNT(DISTINCT)

Tampilkan banyaknya properti yang berbeda telah dilihat selama May ‘01?

SELECT COUNT(DISTINCT propertyNo) AS countFROM Viewing

WHERE viewDate BETWEEN ‘1-May-01’ AND ‘31-May-01’;

Kegunaan COUNT dan SUM

Tampilkan jumlah manager dan jumlah gaji mereka.

SELECT COUNT(staffNo) AS count, SUM(salary) AS sumFROM Staff

WHERE position = ‘Manager’;

Kegunaan MIN, MAX, AVG

Carilah minimum, maximum, dan average gaji staff

SELECT MIN(salary) AS min, MAX(salary) AS max, AVG(salary) AS avg

FROM Staff;

Perintah SELECT - Grouping

Menggunakan clause GROUP BY untuk mendapatkan sub-total. SELECT dan GROUP BY terintegrasi :setiap item dalam SELECT

harus berupa single-valued per group, dan clause SELECT hanya dapat mengandung :

Nama kolom Fungsi aggregate Konstanta Ekspresi terdiri dari kombinasi tersebut diatas.

Semua nama kolom dalam SELECT harus ditampilkan dalam clause GROUP BY kecuali jika dinamakan dalam fungsi aggregate.

Jika WHERE digunakan bersama GROUP BY, WHERE dimunculkan lebih dulu, kemudian groups ditampilkan dari sisa baris untuk memenuhi predikat.

Standar ISO mempertimbangkan dua null bernilai sama untuk kegunaan dari clause GROUP BY.

Kegunaan GROUP BY

Tampilkan jumlah staff pada setiap cabang dan total gaji staf pada masing-masing cabang

SELECT branchNo, COUNT(staffNo) AS count,

SUM(salary) AS sumFROM Staff

GROUP BY branchNoORDER BY branchNo;

Pengelompokkan terbatas – HAVING clause

Clause HAVING didesain untuk digunakan bersama GROUP BY untuk membatasi pengelompokkan yang ditampilkan pada tabel hasil akhir.

Hampir sama dengan WHERE, dimana WHERE menyeleksi baris secara individual, sedangkan HAVING secara kelompok.

Nama kolom dalam clause HAVING juga akan ditampilkan dalam daftar GROUP BY atau dimasukan dalam fungsi aggregate.

Kegunaan HAVING

Tampilkan jumlah staff dan jumlah gaji mereka untuk cabang dengan jumlah staff lebih dari 1

SELECT branchNo, COUNT(staffNo) AS count, SUM(salary) AS sum

FROM Staff GROUP BY branchNoHAVING COUNT(staffNo) > 1

ORDER BY branchNo;

Subqueries

Beberapa perintah SQL dapat memiliki SELECT ditambahkan didalamnya.

Suatu subselect digunakan dalam clause WHERE dan HAVING disamping SELECT utama, biasanya disebut subquery atau nested query.

Subselect juga dapat digunakan dalam perintah INSERT, UPDATE, dan DELETE.

Subquery dengan persamaan

Tampilkan detail staff yang bekerja di cabang yang berada di ‘163 Main St’.

SELECT yang didalam mencari no cabang dari cabang yang beralamat di ‘163 Main St’ (‘B003’).

SELECT yang diluar memanggil detail dari seluruh staff yang bekerja dicabang ini.

SELECT staffNo, fName, lName, position FROM StaffWHERE branchNo =

(SELECT branchNo FROM Branch WHERE street = ‘163 Main

St’);

Kemudian SELECT luar menjadi :SELECT staffNo, fName, lName, position FROM

StaffWHERE branchNo = ‘B003’;

Subquery dengan Aggregate

Tampilkan detail staff yang gaji-nya lebih besar dari gaji rata-rata dan tampilkan selisihnya

Tidak dapat dituliskan ‘WHERE salary > AVG(salary)’ Lebih baik digunakan subquery untuk mencari gaji

rata-rata (17000), kemudian menggunakan SELECT luar untuk mencari staff dengan gaji lebih besar dari 17000.

SELECT staffNo, fName, lName, position, salary – (SELECT AVG(salary) FROM Staff) As SalDiff FROM Staff

WHERE salary >(SELECT AVG(salary) FROM Staff);

Subquery dengan Aggregate(2)

SELECT staffNo, fName, lName, position, salary – 17000 As salDiff

FROM Staff

WHERE salary > 17000;

Aturan-aturan Subquery

Clause ORDER BY dapat tidak digunakan dalam subquery (walaupun dapat digunakan dalam SELECT terluar).

Subquery SELECT harus terdiri dari nama kolom tunggal atau ekspresi, kecuali untuk subqueries yang menggunakan EXISTS.

Berdasarkan default, nama kolom mengacu ke nama tabel pada clause FROM dari subquery. Dapat mengacu ke table dalam FROM menggunakan alias.

Ketika subquery merupakan sebuah operand dalam suatu perbandingan, maka harus dituliskan disebelah kanan.

Subquery tidak dapat digunakan sebagai operand dalam suatu ekspresi.

Queri bersarang : kegunaan IN

Tampilkan properti yang ditangani oleh staff di ‘163 Main St’.

SELECT propertyNo, street, city, postcode, type, rooms, rent

FROM PropertyForRentWHERE staffNo IN

(SELECT staffNo FROM StaffWHERE branchNo = (SELECT branchNo FROM Branch

WHERE street = ‘163 Main St’));

ANY dan ALL

ANY dan ALL dapat digunakan dengan subqueries yang menghasilkan satu kolom tunggal.

Dengan ALL, kondisi akan bernilai benar jika terpenuhi oleh semua nilai yang dihasilkan oleh subquery.

Dengan ANY, kondisi akan bernilai benar jika ada nilai yang dihasilkan subquery memenuhi ketentuan.

Jika subquery bernilai kosong (empty), ALL mengembalikan nilai benar (true), dan ANY mengembalikan nilai salah (false).

SOME dapat digunakan sebagai pengganti ANY.

Kegunaan ANY/SOME

Tampilkan staff yang mempunyai gaji lebih besar dari gaji 1 staff dicabang B003.

Inner query menghasilkan himpunan {12000, 18000, 24000} dan outer query mengambil staff yang gaji-nya lebih besar dari semua nilai yang ada di himpunan tersebut.

SELECT staffNo, fName, lName, position, salary FROM StaffWHERE salary > SOME

(SELECT salary FROM Staff WHERE branchNo = ‘B003’);

Kegunaan ALL

Tampilkan staff yang gaji-nya lebih besar dari gaji setiap anggota staff cabang B003.

SELECT staffNo, fName, lName, position, salary FROM StaffWHERE salary > ALL

(SELECT salary FROM Staff WHERE branchNo = ‘B003’);