SQL Sub (or Nested ) Query
Examples • Q: Find students whose GPA is below the average. – The criteria itself requires a SQL statement. – SELECT * FROM student • WHERE gpa < (SELECT AVG(gpa) FROM student); • Q: Find students whose GPA is higher than student s2, peter’s GPA. – SELECT * FROM student where gpa > (select gpa from student where sid='s2');
Sub Query with IN • SELECT sid, sname FROM student WHERE sid in (‘S1’, ‘S2’); • Use a subquery to retrieve keys that meet criteria. Example: Display students’ ID, name who owe university more than $2000. (Note: not showing balance) SELECT sid,sname FROM student WHERE SID IN (SELECT SID FROM account WHERE balance > 2000); Compare with this example: Display students’ ID, name and Balance who owe university more than $2000. This example requires a join in order to display the balance. SELECT sid, sname, balance FROM student natural join account where balance>2000;
Sub Query with IN, NOT IN • Use a subquery to retrieve keys that meet criteria. • Q: Display faculty’s ID and name if the faculty advises at least one student. – SELECT fid, fname FROM faculty – WHERE fid IN (SELECT DISTINCT fid FROM student); • Q: Display faculty’s name and name if the faculty does not advise any student.
You've reached the end of your free preview.
Want to read all 20 pages?
- Spring '14
- B MAK
- Sid, Find students