SQL: DML (2)

1. SQL: DML (2) Basis Data Pertemuan 07

2. 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

3. 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.

4. 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;

5. Contoh : Kegunaan COUNT(*) • Tampilkan banyaknya properti yang mempunyai biaya sewa lebih dari £350 per bulan SELECT COUNT(*) AS count FROM PropertyForRent WHERE rent > 350;

6. Kegunaan COUNT(DISTINCT) • Tampilkan banyaknya properti yang berbeda telah dilihat selama May ‘01? SELECT COUNT(DISTINCT propertyNo) AS count FROM Viewing WHERE viewDate BETWEEN ‘1-May-01’ AND ‘31-May-01’;

7. Kegunaan COUNT dan SUM • Tampilkan jumlah manager dan jumlah gaji mereka. SELECT COUNT(staffNo) AS count, SUM(salary) AS sum FROM Staff WHERE position = ‘Manager’;

8. 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;

9. 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.

10. 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 sum FROM Staff GROUP BY branchNo ORDER BY branchNo;

11. 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.

12. 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 branchNo HAVING COUNT(staffNo) > 1 ORDER BY branchNo;

13. 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.

14. 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 Staff WHERE branchNo = (SELECT branchNo FROM Branch WHERE street = ‘163 Main St’);

15. Kemudian SELECT luar menjadi : SELECT staffNo, fName, lName, position FROM Staff WHERE branchNo = ‘B003’;

16. 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);

17. Subquery dengan Aggregate(2) SELECT staffNo, fName, lName, position, salary – 17000 As salDiff FROM Staff WHERE salary > 17000;

18. 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.

19. Queri bersarang : kegunaan IN Tampilkan properti yang ditangani oleh staff di ‘163 Main St’. SELECT propertyNo, street, city, postcode, type, rooms, rent FROM PropertyForRent WHERE staffNo IN (SELECT staffNo FROM Staff WHERE branchNo = (SELECT branchNo FROM Branch WHERE street = ‘163 Main St’));

20. 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.

21. 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 Staff WHERE salary > SOME (SELECT salary FROM Staff WHERE branchNo = ‘B003’);

23. Kegunaan ALL • Tampilkan staff yang gaji-nya lebih besar dari gaji setiap anggota staff cabang B003. SELECT staffNo, fName, lName, position, salary FROM Staff WHERE salary > ALL (SELECT salary FROM Staff WHERE branchNo = ‘B003’);