SQL – Sub Query Lecturer:
Mahendra Data, S.Kom
Let’s practice… (part 1) Sub Queries in WHERE
School Admission Database College cName
Student state
enrollment
sID
sName
GPA
sizeHS
Berkeley
CA
36000
123 Amy
3.9
1000
Brawijaya
CA
1000
234 Bob
3.6
1500
Cornell
NY
21000
345 Craig
3.5
500
MIT
MA
10000
456 Doris
3.9
1000
Stanford
NY
15000
543 Craig
3.4
2000
567 Edward
2.9
2000
654 Amy
3.9
1000
678 Fay
3.8
200
765 Jay
2.9
1500
789 Gary
3.4
800
876 Irene
3.9
400
987 Helen
3.7
800
School Admission Database Apply sID
cName
major
decision
sID
cName
major
decision
123 Berkeley
CS
Y
678 Stanford
history
Y
123 Cornell
EE
Y
765 Cornell
history
N
123 Stanford
CS
Y
765 Cornell
psychology
Y
123 Stanford
EE
N
765 Stanford
history
Y
234 Berkeley
biology
N
876 MIT
biology
Y
345 Cornell
bioengineering
N
876 MIT
marine biology N
345 Cornell
CS
Y
876 Stanford
CS
N
345 Cornell
EE
N
987 Berkeley
CS
Y
345 MIT
bioengineering
Y
987 Stanford
CS
Y
543 MIT
CS
N
• Tampilan ID dan nama mahasiswa yang mendaftar ke bidang CS SELECT sID, sName FROM Student WHERE sID IN (SELECT sID FROM Apply WHERE major = "CS");
College cName state
Student enrollment
sID
sName
Apply GPA
sizeHS
sID
cName
major
decision
• Tampilan ID dan nama mahasiswa yang mendaftar ke bidang CS SELECT DISTINCT S.sID, sName FROM Student S, Apply A WHERE S.sID = A.sID AND major = "CS";
College cName state
Student enrollment
sID
sName
Apply GPA
sizeHS
sID
cName
major
decision
• Tampilan nama mahasiswa yang mendaftar ke bidang CS SELECT sName FROM Student WHERE sID IN (SELECT sID FROM Apply WHERE major = "CS");
College cName state
Student enrollment
sID
sName
Apply GPA
sizeHS
sID
cName
major
decision
• Tampilan nama mahasiswa yang mendaftar ke bidang CS SELECT DISTINCT sName FROM Student S, Apply A WHERE S.sID = A.sID AND major = "CS"; Tidak sepenuhnya benar, query ini akan menghapus salah satu “Craig”, padahal 2 “Craig” sebenarnya memiliki ID yang berbeda
College cName state
Student enrollment
sID
sName
Apply GPA
sizeHS
sID
cName
major
decision
The Importance of Duplicate Value • Tampilan rata-rata GPA mahasiswa yang mendaftar ke bidang CS SELECT avg(GPA) FROM Student WHERE sID IN (SELECT sID FROM Apply WHERE major = "CS");
College cName state
Student enrollment
sID
sName
Apply GPA
sizeHS
sID
cName
major
decision
The Importance of Duplicate Value • Tampilan rata-rata GPA mahasiswa yang mendaftar ke bidang CS SELECT DISTINCT avg(GPA) FROM Student S, Apply A WHERE S.sID = A.sID AND major = "CS"; Akan menghasilkan nilai yang SALAH, query ini akan menghapus salah satu GPA “Craig”, padahal 2 “Craig” sebenarnya memiliki ID dan GPA yang berbeda College cName state
Student enrollment
sID
sName
Apply GPA
sizeHS
sID
cName
major
decision
The Importance of Duplicate Value • Tampilan rata-rata GPA mahasiswa yang mendaftar ke bidang CS SELECT avg(GPA) FROM Student S, Apply A WHERE S.sID = A.sID AND major = "CS"; Akan menghasilkan nilai yang SALAH, query ini akan menghitung GPA “Amy” 2 kali, padahal sebenarnya hanya ada 1 “Amy” College cName state
Student enrollment
sID
sName
Apply GPA
sizeHS
sID
cName
major
decision
• Tampilkan college dengan nilai enrollment tertinggi SELECT cName FROM College C1 WHERE NOT EXISTS (SELECT * FROM College C2 WHERE C2.enrollment > C1.enrollment);
College cName state
Student enrollment
sID
sName
Apply GPA
sizeHS
sID
cName
major
decision
• Tampilkan GPA tertinggi dari mahasiswa SELECT max(GPA) FROM Student;
College cName state
Student enrollment
sID
sName
Apply GPA
sizeHS
sID
cName
major
decision
• Tampilkan Nama dan GPA tertinggi dari mahasiswa SELECT sName, max(GPA) FROM Student;
Hanya akan memunculkan salah satu nama mahasiswa dengan GPA Tertinggi, padahal seharusnya ada beberapa mahasiswa denga GPA Yang sama tingginya College cName state
Student enrollment
sID
sName
Apply GPA
sizeHS
sID
cName
major
decision
• Tampilkan Nama dan GPA tertinggi dari mahasiswa SELECT sName, GPA FROM Student S1 WHERE NOT EXISTS (SELECT * FROM Student S2 WHERE S2.GPA > S1.GPA); Benar, karena query ini dapat menampilkan beberapa nama Mahasiswa yang GPA nya tertinggi College cName state
Student enrollment
sID
sName
Apply GPA
sizeHS
sID
cName
major
decision
• Tampilkan Nama dan GPA tertinggi dari mahasiswa SELECT sName, GPA FROM Student WHERE GPA >= ALL (SELECT GPA FROM Student);
Benar, karena query ini dapat menampilkan beberapa nama Mahasiswa yang GPA nya tertinggi College cName state
Student enrollment
sID
sName
Apply GPA
sizeHS
sID
cName
major
decision
• Tampilkan semua Nama dan GPA dari mahasiswa kecuali mahasiswa dengan GPA tertinggi SELECT sName, GPA FROM Student S1 WHERE EXISTS (SELECT * FROM Student S2 WHERE S2.GPA > S1.GPA);
College cName state
Student enrollment
sID
sName
Apply GPA
sizeHS
sID
cName
major
decision
• Tampilkan semua Nama dan GPA dari mahasiswa kecuali mahasiswa dengan GPA tertinggi SELECT sName, GPA FROM Student WHERE GPA < ANY (SELECT GPA FROM Student);
College cName state
Student enrollment
sID
sName
Apply GPA
sizeHS
sID
cName
major
decision
Let’s practice… (part 2) Sub Queries in SELECT and FROM
• Ubah range GPA dari 0-4 menjadi 0-100 kemudian tampilkan Nama, GPA, dan GPA modifikasi tersebut yang nilainya antara 70-80 SELECT sName, GPA, (GPA / 4 * 100) AS scaledGPA FROM Student WHERE (GPA / 4 * 100) >= 70 and (GPA / 4 * 100) <= 80;
College cName state
Student enrollment
sID
sName
Apply GPA
sizeHS
sID
cName
major
decision
• Ubah range GPA dari 0-4 menjadi 0-100 kemudian tampilkan Nama, GPA, dan GPA modifikasi tersebut yang nilainya diatas 90 SELECT * FROM ( SELECT sName, GPA, (GPA / 4 * 100) AS scaledGPA FROM student) modStudent where scaledGPA >= 70 and scaledGPA <= 80;
College cName state
Student enrollment
sID
sName
Apply GPA
sizeHS
sID
cName
major
decision
• Tampilkan cName, state dan GPA mahasiswa tertinggi yang mendaftar di kampus tersebut. SELECT cName, state, ( SELECT DISTINCT max(GPA) FROM Apply, Student WHERE College.cName = Apply.cName AND Apply.sID = Student.sID ) FROM College;
College cName state
Student enrollment
sID
sName
Apply GPA
sizeHS
sID
cName
major
decision
Tugas • Presentasi tentang Subquery Jelaskan tentang subquery Memberi masing-masing 2 contoh dari: • Subquery di WHERE • Subquery di FROM • Subquery di SELECT
• Tugas Kelompok.
Selamat belajar
SELESAI….
Daftar Pustaka • http://www.w3schools.com/sql/default.asp • http://www.db-class.org/course/class/index
