Transcript Chapter 9 Advanced Query Formulation dengan SQL

Chapter 9
Advanced Query Formulation dengan
SQL
Outline
Masalah Outer join
 Type I nested queries
 Type II nested queries dan perbedaan
masalahnya
 Nested queries dalam FROM clausema
 Masalah Division
 Null value effects

Outer Join Overview
Join meniadakan baris yang tidak sesuai
 Pemeliharan baris yang tidak sesuai adalah
beberapa dari masalah yang penting
 Variasi Outer join

– Full outer join
– One-sided outer join
Outer Join Operators
Full outer join
Left Outer Join
Unmatched rows
of the left table
Join
Right Outer Join
Matched rows
using the join
condition
Unmatched rows
of the right table
Full Outer Join Example
Faculty
FacSSN
FacName
111-11-1111 joe
222-22-2222 sue
333-33-3333 sara
Offering
Offerno
1111
2222
3333
4444
FacSSN
111-11-1111
222-22-2222
111-11-1111
Outer Join of Offering and Faculty
FacSSN
111-11-1111
FacName
joe
OfferNo
1111
222-22-2222
sue
2222
111-11-1111
joe
3333
333-33-3333
sara
4444
University Database
LEFT JOIN dan RIGHT JOIN
Keywords
contoh 1 (Access)
SELECT OfferNo, CourseNo, Offering.FacSSN,
FacFirstName, FacLastName
FROM Offering LEFT JOIN Faculty
ON Offering.FacSSN = Faculty.FacSSN
WHERE CourseNo LIKE 'IS*'
contoh 2 (Access)
SELECT OfferNo, CourseNo, Offering.FacSSN,
FacFirstName, FacLastName
FROM Faculty RIGHT JOIN Offering
ON Offering.FacSSN = Faculty.FacSSN
WHERE CourseNo LIKE 'IS*'
Notasi Oracle 8i untuk OneSided Outer Joins
contoh 3 (Oracle 8i)
SELECT OfferNo, CourseNo, Offering.FacSSN,
FacFirstName, FacLastName
FROM Faculty, Offering
WHERE Offering.FacSSN = Faculty.FacSSN (+)
AND CourseNo LIKE 'IS%'
contoh 4 (Oracle 8i)
SELECT OfferNo, CourseNo, Offering.FacSSN,
FacFirstName, FacLastName
FROM Faculty, Offering
WHERE Faculty.FacSSN (+) = Offering.FacSSN
AND CourseNo LIKE 'IS%'
Contoh I Full Outer Join
contoh 5 (SQL:1999 and Oracle 9i)
SELECT FacSSN, FacFirstName, FacLastName,
FacSalary, StdSSN, StdFirstName,
StdLastName, StdGPA
FROM Faculty FULL JOIN Student
ON Student.StdSSN = Faculty.FacSSN
Contoh II Full Outer Join
contoh 5 (Access)
SELECT FacSSN, FacFirstName, FacLastName,
FacSalary, StdSSN, StdFirstName,
StdLastName, StdGPA
FROM Faculty RIGHT JOIN Student
ON Student.StdSSN = Faculty.FacSSN
UNION
SELECT FacSSN, FacFirstName, FacLastName,
FacSalary, StdSSN, StdFirstName,
StdLastName, StdGPA
FROM Faculty LEFT JOIN Student
ON Student.StdSSN = Faculty.FacSSN
Contoh III Full Outer Join
contoh 5 (Oracle 8i)
SELECT FacSSN, FacFirstName, FacLastName,
FacSalary, StdSSN, StdFirstName,
StdLastName, StdGPA
FROM Faculty, Student
WHERE Student.StdSSN = Faculty.FacSSN (+)
UNION
SELECT FacSSN, FacFirstName, FacLastName,
FacSalary, StdSSN, StdFirstName,
StdLastName, StdGPA
FROM Faculty, Student
WHERE Student.StdSSN (+) = Faculty.FacSSN
Mixing Inner dan Outer Joins I
contoh 6 (Access)
SELECT OfferNo, Offering.CourseNo, OffTerm,
CrsDesc, Faculty.FacSSN, FacLastName
FROM ( Faculty RIGHT JOIN Offering
ON Offering.FacSSN = Faculty.FacSSN )
INNER JOIN Course
ON Course.CourseNo = Offering.CourseNo
WHERE Course.CourseNo LIKE 'IS*'
Mixing Inner dan Outer Joins II
contoh 6 (Oracle 8i)
SELECT OfferNo, Offering.CourseNo, OffTerm,
CrsDesc, Faculty.FacSSN, FacLastName
FROM Faculty, Course, Offering
WHERE Offering.FacSSN = Faculty.FacSSN (+)
AND Course.CourseNo = Offering.CourseNo
AND Course.CourseNo LIKE 'IS%'
Type I Nested Queries
Query di dalam query
 Menggunakan WHERE dan HAVING
conditions
 Sama dengan prosedur nested
 Menjalankan satu kali
 Tidak ada reference ke outer query
 Juga dikenal sebagai non-correlated atau
independent nested query

Contoh Type I Nested Query
Contoh 7 (Access): List finance faculty who teach IS courses.
SELECT FacSSN, FacLastName, FacDept
FROM Faculty
WHERE FacDept = 'FIN' AND FacSSN IN
( SELECT FacSSN FROM Offering
WHERE CourseNo LIKE 'IS*' )
Contoh 8 (Oracle): List finance faculty who teach 4 unit IS courses.
SELECT FacSSN, FacLastName, FacDept
FROM Faculty
WHERE FacDept = 'FIN' AND FacSSN IN
( SELECT FacSSN FROM Offering
WHERE CourseNo LIKE 'IS%' AND CourseNo IN
( SELECT CourseNo FROM Course
WHERE CrsUnits = 4 ) )
Contoh DELETE
• Gunakan Type I nested queries untuk menguji kondisi
pada tabel lain
• Gunakan juga UPDATE statements
Contoh 9: Delete offerings taught by Leonard Vince.
DELETE FROM Offering
WHERE Offering.FacSSN IN
( SELECT FacSSN FROM Faculty
WHERE FacFirstName = 'Leonard'
AND FacLastName = 'Vince' )
Type II Nested Queries
Sama dengan nested loops
 Menjalan sekali saja untuk setiap baris dari
outer query
 Reference ke outer query
 Dikenal juga sebagai correlated or variably
nested query
 Penggunaan masalah difference adalah
bukan joins

Contoh Type II Nested Query
Contoh 10: Retrieve MS faculty who are not teaching in winter 2003.
SELECT FacSSN, FacLastName, FacDept
FROM Faculty
WHERE FacDept = 'MS' AND NOT EXISTS
( SELECT * FROM Offering
WHERE OffTerm = 'WINTER'
AND OffYear = 2003
AND Faculty.FacSSN = Offering.FacSSN )
Alternatif Difference
Formulation
Contoh 11: Retrieve MS faculty who are not teaching in winter 2003.
SELECT FacSSN, FacLastName, FacDept
FROM Faculty
WHERE FacDept = 'MS' AND FacSSN NOT IN
( SELECT FacSSN FROM Offering
WHERE OffTerm = 'WINTER'
AND OffYear = 2003 )
Nested Queries dalam FROM
Clause
Lebih banyak pengenak baru daripada nested
query dalam klausa WHERE dan HAVING
 Konsisten dalam bahasa designnya
 Dimanapun tabel terlihat, ekspresi tabel dapat
terlihat.
 Spesifikasinya:

– Nested aggregates
– Multiple independent aggregate calculations
Contoh Nested FROM Query
Contoh 12: Retrieve the course number, course description, the
number of offerings, and the average enrollment across offering.
SELECT T.CourseNo, T.CrsDesc,
COUNT(*) AS NumOfferings,
Avg(T.EnrollCount) AS AvgEnroll
FROM
(SELECT Course.CourseNo, CrsDesc,
Offering.OfferNo,
COUNT(*) AS EnrollCount
FROM Offering, Enrollment, Course
WHERE Offering.OfferNo = Enrollment.OfferNo
AND Course.CourseNo = Offering.CourseNo
GROUP BY Course.CourseNo, CrsDesc,
Offering.OfferNo) T
GROUP BY T.CourseNo, T.CrsDesc
Operator Divide

Pasangan subset values
– Suppliers yang mensupply semua bagian
– Faculty yang mengajar setiap kursus IS
operator khusus
 Secara tipikal diterapkan untuk associative
tabel yang menyajikan M-N relationships

Contoh Division
SuppPart
SuppNo PartNo
s3
p1
s3
p2
s3
p3
s0
p1
s1
p2
Part
SuppPart DIVIDEBY Part
PartNo
p1
p2
p3
SuppNo
s3
s3 {p1, p2, p3}
contains {p1, p2, p3}
Metode COUNT untuk masalah
Division
• Membandingkan jumlah dari baris yang digabungkan
dengan group jumlah total dari subset interest.
• Type I nested query dalam the HAVING clause
Contoh 13: List the students who belong to all clubs.
SELECT StdNo
FROM StdClub
GROUP BY StdNo
HAVING COUNT(*) =
( SELECT COUNT(*) FROM Club )
Masalah Typical Division
• Membandingkan subset yang menarik daripada entire
table
• Menggunakan kondisi yang sama dalam outer dan
nested query
Contoh 13: List the students who belong to all social clubs.
SELECT Student1.StdNo, SName
FROM StdClub, Club, Student1
WHERE StdClub.ClubNo = Club.ClubNo
AND Student1.StdNo = StdClub.StdNo
AND CPurpose = 'SOCIAL'
GROUP BY Student1.StdNo, SName
HAVING COUNT(*) =
( SELECT COUNT(*) FROM Club
WHERE CPurpose = 'SOCIAL' )
Masalah Advanced Division

Jumlah perbedaan nilai daripada baris
– Faculty yang mengajar paling sedikit satu
session dari tawaran kursus yang ada.
– Duplikasi penggunaan table
Menggunakan COUNT(DISTINCT
column)
 Menggunakan stored query atau nested
FROM query dalam Access

Contoh masalah Advanced
Division
Contoh 14: List the SSN and the name of faculty who teach at least
one section of all of the fall, 2002, IS courses.
SELECT Faculty.FacSSN, FacFirstName,
FacLastName
FROM Faculty, Offering
WHERE Faculty.FacSSN = Offering.FacSSN
AND OffTerm = 'FALL' AND CourseNo LIKE 'IS%'
AND OffYear = 2002
GROUP BY Faculty.FacSSN, FacFirstName,
FacLastName
HAVING COUNT(DISTINCT CourseNo) =
( SELECT COUNT(DISTINCT CourseNo)
FROM Offering
WHERE OffTerm = 'FALL' AND OffYear = 2002
AND CourseNo LIKE 'IS%' )
Null Value Efek
Kondisi yang sederhana
 Kondisi campuran
 Pengelompokan dan fungsi agregasi
 SQL:1999 standard tetapi
implementasinya beragam

Kondisi yang sederhana




Kondisi yang sederhana adalah null jika sisi lefthand maupun right-hand adalah null.
Pembuangan evaluasi baris untuk false atau null
Mempertahankan evaluasi baris yang benar
Evaluasi baris untuk null tidak akan nampak
dalam hasil dari kondisi sederhana atau
negasinya.
Compound Conditions
AND
True
False
Null
True
True
False
Null
False
False
False
False
Null
Null
False
Null
OR
True
False
Null
True
True
True
True
False
True
False
Null
Null
True
Null
Null
NOT
True
False
Null
False
True
Null
Fungsi Agregat
Nilai null tidak diperbolehkan
 Pengaruhnya dapat terlihat pada

– COUNT(*) may differ from Count(Column)
– SUM(Column1) + SUM(Column2) may differ
from SUM(Column1 + Column2)
Pengaruh Pengelompkkan
Baris dengan nilai null adalah kelompok
bersama.
 Pengelompokan kolom berisi nilai null.
 Kelompok null bisa diganti dengan
memulai atau mengakhiri dari kelompok
bukan null.

Ringkasan
Masalah sesuai Advanced itu tidak biasa
tetapi penting ketika dibutuhkan.
 Pemahaman outer join, difference, dan
division operators
 Nested queries penting untuk masalah
yang sesuai advanced.
 Banyak latihan buat master query
formulasi dan SQL.
