materi sql server tentang sub query

SUBQUERY DAN PEMBAHASANNYA

Subquery adalah perintah SELECT yang berada di dalam perintah SQL lain. Subquery sangat berguna ketika kita ingin menampilkan data dengan kondisi yang bergantung
pada data di dalam table itu sendiri.
Sintaks
SELECT select_list
FROM table
WHERE expr operator
( SELECT select_list FROM table );
Contoh:
Menampilkan data karyawan yang bekerja satu departemen dengan Biri.
SELECT last_name, title
FROM employee
WHERE dept_id =
( SELECT dept_id
FROM employee
WHERE UPPER(last_name) = ‘BIRI’ );
6.1. Single Row Subquery
Single row subquery menghasilkan satu baris data dari perintah SELECT terdalam. Tipe subquery ini menggunakan single row operator.
Contoh:
Menampilkan data karyawan yang memiliki jabatan sama dengan Smith.
SELECT last_name, title
FROM employee
WHERE title =
( SELECT title
FROM employee
WHERE last_name = ‘Smith’ );
Kita dapat menampilkan data dari query utama dengan menggunakan fungsi grup (group function) untuk menghasilkan satu baris data.
Contoh:
Menampilkan data karyawan yang memiliki gaji dibawah rata-rata.
SELECT last_name, title, salary
FROM employee
WHERE salary < ( SELECT AVG(salary)
FROM employee );
6.1.1. Kesalahan dalam Subquery
Kesalahan umum dalam subquery adalah lebih dari satu baris data dihasilkan untuk single row subquery.
Contoh:
Subquery ini menghasilkan lebih dari satu baris data dan menggunakan single row operator. Kita dapat membetulkan kesalahan ini dengan mengubah = menjadi IN.
SELECT last_name, first_name, title
FROM employee
WHERE dept_id = ( SELECT id
FROM department
WHERE name = ‘Finance’
OR region_id = 2 );
6.2. Multiple Rows Subquery
Subquery yang menghasilkan lebih dari satu baris data disebut multiple row subquery. Pastikan penggunaan multiple row operator, seperti IN.
Contoh:
Menampilkan data karyawan yang bekerja pada departemen Finance atau pada region 2.
SELECT last_name, first_name, title
FROM employee
WHERE dept_id IN ( SELECT id
FROM department
WHERE name = ‘Finance’
OR region_id = 2 );
6.3. Klausa HAVING
Kita dapat menggunakan subquery tidak hanya dalam klausa WHERE, namun juga klausa HAVING.
Contoh:
Menampilkan data departemen yang memiliki rata-rata gaji diatas rata-rata gaji departemen 32.
SELECT dept_id, AVG (salary)
FROM employee
GROUP BY dept_id
HAVING AVG(salary) > ( SELECT AVG(salary)
FROM employee
WHERE dept_id = 32 );
Menampilkan jabatan dengan rata-rata gaji terkecil.
SELECT title, AVG (salary)
FROM employee
GROUP BY title
HAVING AVG (salary) = ( SELECT MIN(AVG(salary))
FROM employee
GROUP BY title );
6.4. Latihan
Tampilkan nama belakang, jabatan dan gaji bulanan pegawai khusus untuk pegawai yang memiliki gaji dibawah rata-rata gaji bulanan seluruh pegawai.
SELECT last_name, title, salary
FROM employee
WHERE salary < ( SELECT AVG(salary) FROM employee);
Tampilkan nama belakang, nama depan dan jabatan pegawai yang bekerja pada departemen Finance atau bekerja pada daerah dengan kode 2.
SELECT last_name, first_name, title
FROM employee
WHERE dept_id IN
( SELECT id
FROM department
WHERE name = ‘Finance’ OR region_id = 2 );
Tampilkan seluruh departemen yang memiliki rata-rata gaji bulanan lebih besar daripada rata-rata gaji bulanan pegawai yang bekerja pada departemen kode 32.
SELECT dept_id, AVG(salary)
FROM employee
GROUP BY dept_id
HAVING AVG(salary) >
( SELECT AVG(salary)
FROM employee
WHERE dept_id = 32 );
Tampilkan jabatan dengan rata-rata gaji terkecil.
SELECT title, AVG(salary)
FROM employee
GROUP BY title
HAVING AVG(salary) =
( SELECT MIN(AVG(salary))
FROM employee
GROUP BY title );
Tampilkan nama depan, nama belakang, dan tanggal pegawai mulai bekerja untuk pegawai yang bekerja satu departemen dengan Magee.
SELECT first_name, last_name, start_date
FROM employee
WHERE dept_id =
( SELECT dept_id
FROM employee
WHERE last_name = ‘Magee’ );
Tampilkan kode, nama depan, nama belakang pegawai yang memiliki gaji bulanan diatas rata-rata gaji bulanan seluruh pegawai.
SELECT id, first_name, last_name
FROM employee
WHERE salary >
( SELECT AVG(salary)
FROM employee );
Tampilkan nama belakang, jabatan pegawai, dan kode departemen dimana pegawai tersebut bekerja untuk pegawai-pegawai yang bekerja pada daerah 1 atau 2.
SELECT last_name, title, dept_id
FROM employee
WHERE dept_id IN
( SELECT id
FROM department
WHERE region_id = 1 OR region_id = 2 );
Tampilkan nama belakang dan gaji bulanan pegawai yang merupakan bawahan dari LaDoris Ngao.
SELECT last_name, salary
FROM employee
WHERE manager_id =
( SELECT id
FROM employee
WHERE last_name = ‘Ngao’ );
Tampilkan kode, nama depan, dan nama belakang pegawai yang memiliki gaji bulanan diatas rata-rata gaji bulanan seluruh pegawai dan memiliki atasan dengan nama belakang mengandung huruf t.
SELECT id, first_name, last_name
FROM employee
WHERE salary >
( SELECT AVG(salary)
FROM employee
WHERE manager_id IN
( SELECT id
FROM employee
WHERE last_name LIKE ‘%t%’ ));
Tampilkan kode pelanggan, nama pelanggan, nilai kredit, dan nama sales representative untuk seluruh pelanggan yang tinggal di North America dan memiliki sales representative bernama Nguyen.
SELECT id, name, credit_rating
FROM customer
WHERE sales_rep_id =
( SELECT     id
FROM employee
WHERE last_name = ‘Nguyen’)
OR
region_id =
( SELECT id
FROM region
WHERE name = ‘North America’);
Tampilkan nama dan keterangan produk untuk produk yang tidak muncul pada order bulan September 1992.
COLUMN short_desc FORMAT A30;
SELECT name “Product Name”, short_desc “Description”
FROM product
WHERE id IN
( SELECT i.product_id
FROM orders o, items i
WHERE o.id = i.ord_id
AND o.date_ordered NOT LIKE ‘%SEP-92′);
Tampilkan nama pelanggan dan nilai kredit untuk pelanggan yang memiliki sales representative bernama Andre Dumas.
SELECT name, credit_rating
FROM customer
WHERE sales_rep_id =
( SELECT id
FROM employee
WHERE last_name = ‘Dumas’ );
Tampilkan nama belakang sales representative yang bekerja pada daerah dengan kode 1 dan 2, nama pelanggannya dan total order masing-masing pelanggan tersebut.
SELECT e.last_name SALES_NAME, c.name CUSTOMER, o.total
FROM customer c, orders o, employee e
WHERE c.id = o.customer_id AND c.sales_rep_id = e.id
AND c.sales_rep_id IN
( SELECT id
FROM employee
WHERE dept_id IN
( SELECT id
FROM department
WHERE region_id IN (1,2)));