SELECT fid fname FROM faculty WHERE fid NOT IN SELECT DISTINCT fid FROM student

Select fid fname from faculty where fid not in select

This preview shows page 4 - 9 out of 20 pages.

SELECT fid, fname FROM faculty WHERE fid NOT IN (SELECT DISTINCT fid FROM student);
Image of page 4
More Examples with IN, NOT IN Q: Display students’ ID and name who are taking at least one course. SELECT sid, snameFROM student where sid in(select distinct sid from registration); Q: Display students’ ID and name who do not take any course. SELECT sid, snameFROM student where sid not in(select distinct sid from registration);
Image of page 5
Sub Query with GROUP BY Q: Find students who take more than 5 courses and display their ID and name. SELECT sid, sname FROM Student NATURAL JOIN Registration GROUP BY sid,sname HAVING COUNT(cid)>5; SELECT sid, sname FROM student WHERE sid IN (SELECT sid FROM Registration GROUP BY sid HAVING COUNT(cid) > 5);
Image of page 6
Sub Query with Join Display students’ ID, name and Balance who owe university more than $2000. SELECT sid,sname, Balance FROM student NATURAL JOIN (SELECT * FROM account WHERE balance > 2000); Display students’ ID and name who owe university more than $2000. SELECT sid,sname FROM student NATURAL JOIN (SELECT * FROM account WHERE balance > 2000); SELECT sid,sname FROM student WHERE SID IN (SELECT SID FROM account WHERE balance > 2000);
Image of page 7
Sub Query with ALL/SOME/ANY Q: Find students whose gpa is greater than all/some bus majors’ gpa: SELECT sid, sname FROM student WHERE gpa > ALL(SELECT gpa FROM student WHERE major=‘bus’); SELECT sid, sname FROM student WHERE gpa > SOME (SELECT gpa FROM student WHERE major=‘bus’); SELECT sid, sname FROM student WHERE gpa > ANY (SELECT gpa FROM student WHERE major=‘bus’);
Image of page 8
Image of page 9

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

Stuck? We have tutors online 24/7 who can help you get unstuck.
A+ icon
Ask Expert Tutors You can ask You can ask You can ask (will expire )
Answers in as fast as 15 minutes