B a s i s D a t a - 1 C H A P T E R SQL. Function & Agregat. Copyright 2005 PENS-ITS

SQL

Basis Data-1

C H A P T E R

13

SQL Function & Agregat

Copyright © 2005 PENS-ITS

SQL

Basis Data-1

Objectives

Tujuan: Mengenal perintah SQL dalam: - Operasi Himpunan - Fungsi Agregat


SQL

Basis Data-1

S Q L – Operasi Himpunan • •

•

Pada ekspresi SQL, terdapat operasi yang berhubungan dengan himpunan pada atribut yang sama. Operasi SQL tersebut yaitu: UNION, INTERSECT dan EXCEPT, yang masing-masing memiliki hubungan erat dengan operasi aljabar relasional ∪, ∩ dan −. Contoh: Terdapat 2 himpunan data, yaitu semua nasabah yang memiliki rekening pada suatu bank, dengan SQL: SELECT nama_nasabah FROM penabung

•

Dan himpunan semua pelanggan yang memiliki pinjaman dari suatu bank, dengan SQL: SELECT nama_nasabah FROM peminjam Copyright © 2005 PENS-ITS

SQL

Basis Data-1

S Q L – Operasi Himpunan Operasi UNION (Penggabungan) • Untuk menemukan semua nasabah yang memiliki pinjaman, rekening atau keduanya pada suatu bank tertentu, dapat dituliskan query berikut: (SELECT nama_nasabah FROM penabung) UNION (SELECT nama_nasabah FROM peminjam)

•

Operaso UNION tidak seperti pada klausa SELECT, secara otomatis mengeliminasi duplikat record. Maka pada query diatas, jika seorang nasabah bernama ‘Mike’ yang memiliki beberapa rekening atau pinjaman (atau keduanya), maka ‘Mike’ hanya muncul sekali record data saja. Copyright © 2005 PENS-ITS

SQL

Basis Data-1

S Q L – Operasi Himpunan Operasi UNION (Penggabungan) • Jika ingin mempertahankan adanya duplikasi, maka dapat digunakan operasi UNION ALL sebagai penggani UNION. (SELECT nama_nasabah FROM penabung) UNION ALL (SELECT nama_nasabah FROM peminjam)

•

Dengan demikian, semua rekening dan pinjaman atas nama Mike akan muncul. Jika Mike memiliki 3 rekening dan 2 pinjaman pada bank, maka akan terdapat 5 record data atas nama Mike.


SQL

Basis Data-1

S Q L – Operasi Himpunan Operasi INTERSECT (Irisan) • Untuk menemukan semua nasabah yang memiliki baik rekening maupun pinjaman pada bank, maka dapat digunakan query berikut: (SELECT [Distinct] nama_nasabah FROM penabung) INTERSECT (SELECT [Distinct] nama_nasabah FROM peminjam)

•

Seperti pada operasi UNION, operasi INTERSECT juga mengeliminasi adanya duplikat data.


SQL

Basis Data-1

S Q L – Operasi Himpunan Operasi INTERSECT (Irisan) • Jika ingin ditampilkan semua data yang memenuhi, sehingga mempertahankan adanya duplikat data pada record-recordnya, maka dapat digunakan operasi INTERSECT ALL query berikut: (SELECT [Distinct] nama_nasabah FROM penabung) INTERSECT ALL (SELECT [Distinct] nama_nasabah FROM peminjam)

•

Jadi, jika Mike memiliki 3 rekening dan 2 pinjaman, maka data yang muncul ada 2 record.


SQL

Basis Data-1

S Q L – Operasi Himpunan Operasi EXCEPT (Perkecualian) • Untuk menemukan semua nasabah yang memiliki rekening tapi tidak memiliki pinjaman pada suatu bank, maka bentuk query: (SELECT [Distinct] nama_nasabah FROM penabung) EXCEPT (SELECT [Distinct] nama_nasabah FROM peminjam)

• •

Seperti pada operasi UNION dan INTERSECT sebelumnya, operasi EXCEPT juga mengeliminasi adanya duplikat data. Pada query diatas, nasabah dengan nama Mike akan muncul sekali jika Mike hanya memiliki rekening di bank saja dan tidak memiliki pinjaman. Copyright © 2005 PENS-ITS

SQL

Basis Data-1

S Q L – Operasi Himpunan Operasi EXCEPT (Perkecualian) • Jika ingin mempertahankan adanya duplikasi data, maka dapat digunakan operasi EXCEPT ALL dengan bentuk query: (SELECT [Distinct] nama_nasabah FROM penabung) EXCEPT ALL (SELECT [Distinct] nama_nasabah FROM peminjam)

• •

Dengan query diatas, jika Mike memiliki 3 rekening dan 1 pinjaman, maka akan dihasilkan 2 record data saja. Sebaliknya, jika Mike memiliki r rekening dan 3 pinjaman, maka tidak akan dihasilkan data record (kosong).


SQL

Basis Data-1

S Q L – Fungsi Agregat dan Group Function •

•

Fungsi agregat adalah fungsi-fungsi yang mengambil kumpulan (collection) suatu himpunan data atau beberapa himpunan data dan mengembalikan suatu nilai tunggal. Terdapat 5 fungsi agregasi (agregat) baku, yaitu: 1. AVG 2. COUNT 3. MAX 4. MIN 5. SUM

•

The maximum salary in the EMPLOYEES table.

Contoh:

… Copyright © 2005 PENS-ITS

SQL

Basis Data-1

S Q L – Fungsi Agregat dan Group Function •

Syntax dari Group Function, yaitu: SELECT FROM [WHERE [GROUP BY [ORDER BY

[column,] group_function(column), ... table condition] column] column];


SQL

Basis Data-1

S Q L – Fungsi Agregat dan Group Function Fungsi AVG dan SUM • Fungsi AVG digunakan untuk mencari nilai rata-rata pada suatu kolom data. • Fungsi SUM digunakan untuk mencari nilai jumlah total pada suatu kolom SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE '%REP%';


SQL

Basis Data-1

S Q L – Fungsi Agregat dan Group Function Fungsi MIN dan MAX • Fungsi MIN digunakan untuk mencari nilai data paling kecil (minimum). • Fungsi MAX digunakan untuk mencari nilai data paling besar (Maximum). SELECT MIN(hire_date), MAX(hire_date) FROM employees;


SQL

Basis Data-1

S Q L – Fungsi Agregat dan Group Function Fungsi COUNT • Fungsi COUNT digunakan untuk mencari jumlah record data row (jumlah baris data yang dihasilkan dari query/banyaknya data). SELECT COUNT(*) FROM employees WHERE department_id = 50;


SQL

Basis Data-1

S Q L – Fungsi Agregat dan Group Function Fungsi COUNT • Fungsi COUNT mengabaikan adanya data yang sifatnya NULL VALUE. • Contoh berikut menampilkan jumlah data record pada suatu kolom.

SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80;


SQL

Basis Data-1

S Q L – Fungsi Agregat dan Group Function Membuat Group Data • Ada keadaan penggunaan fungsi agregat untuk menghasilkan beberapa record data sekaligus berdasarkan kondisi khusus atau group dari suatu kolom tertentu. • Maka dapat digunakan klausa GROUP BY. SELECT FROM [WHERE [GROUP BY [ORDER BY

•

column, group_function(column) table condition] group_by_expression] column];

Membagi rows (baris-baris) dalam tabel menjadi group-group data yang lebih kecil dengan klausa GROUP BY.


SQL

Basis Data-1

S Q L – Fungsi Agregat dan Group Function Membuat Group Data EMPLOYEES 4400 9500

The average salary 3500 in EMPLOYEES table 6400 for each department. 10033

…


SQL

Basis Data-1

S Q L – Fungsi Agregat dan Group Function Membuat Group Data SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ;


SQL

Basis Data-1

S Q L – Fungsi Agregat dan Group Function Membuat Group Data

SELECT AVG(salary) FROM employees GROUP BY department_id ;


SQL

Basis Data-1

S Q L – Fungsi Agregat dan Group Function Membuat Group Data Grouping by More Than One Column EMPLOYEES

“Add up the salaries in the EMPLOYEES table for each job, grouped by department.

…


SQL

Basis Data-1

S Q L – Fungsi Agregat dan Group Function Membuat Group Data Using the GROUP BY Clause on Multiple Columns SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id ;


SQL

Basis Data-1

S Q L – Fungsi Agregat dan Group Function Membuat Pembatasan Group Data • Fungsi GROUP BY dapat dibuat pembatasan dari data yang akan dihasilkan dengan menggunakan fungsi HAVING. Dengan klausa HAVING dapat membatasi groups data: 1. Rows (baris-baris) akan di group. 2. Fungsi group dapat diaplikasikan. 3. Groups matching the HAVING clause are displayed. SELECT FROM [WHERE [GROUP BY [HAVING [ORDER BY

column, group_function table condition] group_by_expression] group_condition] column]; Copyright © 2005 PENS-ITS

SQL

Basis Data-1

S Q L – Fungsi Agregat dan Group Function Membuat Pembatasan Group Data EMPLOYEES

…

The maximum salary per department when it is greater than $10,000


SQL

Basis Data-1

S Q L – Fungsi Agregat dan Group Function Membuat Pembatasan Group Data

SELECT FROM GROUP BY HAVING

department_id, MAX(salary) employees department_id MAX(salary)>10000 ;


SQL

Basis Data-1

S Q L – Fungsi Agregat dan Group Function Membuat Pembatasan Group Data

SELECT FROM WHERE GROUP BY HAVING ORDER BY

job_id, SUM(salary) PAYROLL employees job_id NOT LIKE '%REP%' job_id SUM(salary) > 13000 SUM(salary);


SQL

Basis Data-1

S Q L – Fungsi Agregat dan Group Function Membuat Pembatasan Group Data Nesting Group Functions

Display the maximum average salary. SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id;


B a s i s D a t a - 1 C H A P T E R SQL. Function & Agregat. Copyright 2005 PENS-ITS

Recommend Documents