SQLSubQueryS20.ppt - SQL Sub(or Nested Query Examples \u2022 Q Find students whose GPA is below the average \u2013 The criteria itself requires a SQL

SQLSubQueryS20.ppt - SQL Sub(or Nested Query Examples...

This preview shows page 1 - 5 out of 20 pages.

SQL Sub (or Nested ) Query
Image of page 1
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');
Image of page 2
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;
Image of page 3
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.
Image of page 4
Image of page 5

You've reached the end of your free preview.

Want to read all 20 pages?

  • Spring '14
  • B MAK
  • Sid, Find students

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture