basis data(sequel)

Chapter 4
Query Formulation with SQL (2)
McGraw-Hill/Irwin Copyright © 2007 by The McGraw-Hill Companies, Inc. All rights reserved.
􀀛 DML For Multiple Tables
Join Operator
􀂃 Most databases have many tables
􀂃 Combine tables using the join operator
􀂃 Specify matching condition
􀂃 Can be any comparison but usually =
􀂃 PK = FK most common join condition
􀂃 Relationship diagram useful when combining tables
4-2
Inner & Outer Join Operators
Full join
Left Outer Join Inner Join Right Outer Join
4-3
Left Join = Left Outer Join + Inner Join
Right Join = Right Outer Join + Inner Join
Join Topics
􀂃 Inner Join
􀂃 Cross Product Style
􀂃 Inner Join Operator Style
􀂃 Left Join
􀂃 Right Join
􀂃 Full Join
4-4
Inner Join (1) :
Cross Product Style
Kedua query berikut menghasilkan output yang sama, yaitu menampilkan
seluruh record yang matching pada table Rental & Member untuk fieldfield
yang disebutkan dalam query :
SELECT NoBon, Rental.NoMember, Nama, Alamat, LamaRental
FROM Rental Member
4-5
Rental, WHERE Rental.NoMember = Member.NoMember
SELECT NoBon, R.NoMember, Nama, Alamat, LamaRental
FROM Rental R, Member M
WHERE R.NoMember = M.NoMember
Inner Join (2) :
Inner Join Operator Style
Berikut ini adalah query yang sama dengan query sebelumnya, tetapi
menggunakan cara inner join operator :
SELECT NoBon, Rental.NoMember, Nama, Alamat, LamaRental
FROM Rental INNER JOIN Member
4-6
ON Rental.NoMember = Member.NoMember
SELECT NoBon, R.NoMember, Nama, Alamat, LamaRental
FROM Rental R INNER JOIN Member M
ON R.NoMember = M.NoMember
Inner Join (3) :
Contoh Join dengan 3 Table
Cross Product Style :
SELECT NoBon, Nama, Alamat, R.NoMobil, MerkTipe, LamaRental
FROM Member M, Rental R, Mobil C
WHERE (M.NoMember = R.NoMember) AND (R.NoMobil = C.NoMobil)
4-7
Inner Join Operator Style :
SELECT NoBon, Nama, Alamat, R.NoMobil, MerkTipe, LamaRental
FROM Member M INNER JOIN Rental R ON M.NoMember = R.NoMember
INNER JOIN Mobil C ON R.NoMobil = C.NoMobil
Left Join and Right Join
Left Join :
SELECT NoBon, R.NoMember, Nama, Alamat, LamaRental
FROM Rental R LEFT JOIN Member M
ON R.NoMember = M.NoMember
4-8
Right Join :
SELECT NoBon, R.NoMember, Nama, Alamat, LamaRental
FROM Rental R RIGHT JOIN Member M
ON R.NoMember = M.NoMember
Full Join
Access :
SELECT NoBon, R.NoMember, Nama, Alamat, LamaRental
FROM Rental R LEFT JOIN Member M
ON R.NoMember = M.NoMember
UNION SELECT NoBon, R.NoMember, Nama, Alamat, LamaRental
FROM Rental R RIGHT JOIN Member M
4-9
ON R.NoMember = M.NoMember
Oracle :
SELECT NoBon, R.NoMember, Nama, Alamat, LamaRental
FROM Rental R FULL JOIN Member M
ON R.NoMember = M.NoMember
Ilustrasi Perbandingan Join
SELECT NoBon, R.NoMember, Nama, Alamat,
LamaRental
FROM Rental R INNER JOIN Member M
ON R.NoMember = M.NoMember
SELECT NoBon, R.NoMember, Nama, Alamat,
LamaRental
FROM Rental R LEFT JOIN Member M
ON R.NoMember = M.NoMember
SELECT NoBon, R.NoMember, Nama, Alamat,
4-10
LamaRental
FROM Rental R RIGHT JOIN Member M
ON R.NoMember = M.NoMember
SELECT NoBon, R.NoMember, Nama, Alamat,
LamaRental
FROM Rental R FULL JOIN Member M
ON R.NoMember = M.NoMember
Fungsi Agregat
SELECT SUM(UnitTerjual) AS JumlahTerjual
FROM DETILJUAL
SELECT AVG(NilaiUas) AS RataRataNilaiUas
FROM NILAI
SELECT MAX(Harga) AS HargaProdukTermahal
FROM PRODUK
SELECT MIN(Harga) AS HargaProdukTermurah
FROM PRODUK
SELECT COUNT(*) AS J l hP l
4-11
JumlahPelanggan
FROM PELANGGAN
SELECT STD(NilaiTugas) AS StandarDeviasiNilaiTugas
FROM NILAI
Group By (1)
[a] Menampilkan jumlah mahasiswa tiap fakultas :
SELECT Fakultas, COUNT (*) AS JumlahMhs
FROM MAHASISWA
GROUP BY Fakultas
4-12
[b] Menampilkan jumlah mahasiswa per fakultas untuk
tiap jenis kelamin :
SELECT Fakultas, JenisKelamin, COUNT (*) AS
JumlahMhs
FROM MAHASISWA
GROUP BY Fakultas, JenisKelamin
Group By (2)
[c] Menampilkan total stok masing-masing kategori produk :
SELECT Kategori, SUM (Stok) AS TotalStok
FROM PRODUK
GROUP BY Kategori
4-13
g
[d] Menampilkan harga tertinggi tiap merk produk :
SELECT Merk, MAX (Harga) AS HargaTertinggi
FROM PRODUK
GROUP BY Merk
Group By & Having
[e] Menampilkan total stok masing-masing kategori selain
produk Flash Disk :
SELECT Kategori, SUM (Stok) AS TotalStok
FROM Produk GROUP BY Kategori
HAVING Kategori <> ‘Flash Disk’
4-14
Disk
[f] Menampilkan harga tertinggi untuk merk Apacer,
Diamond & Genius :
SELECT Merk, MAX (Harga) AS HargaTertinggi
FROM Produk GROUP BY Merk
HAVING Merk IN (‘Apacer’, ‘Diamond’, ‘Genius’)
SQL Summarization Rules
􀂃 Columns in SELECT and GROUP BY
􀂃 SELECT : non aggregate and aggregate columns
􀂃 GROUP BY : list all non aggregate columns
􀂃 WHERE versus HAVING
􀂃 Row conditions in WHERE
􀂃 Group conditions in HAVING
4-15
p

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s