Write an SQL query that returns the facultyid and name of any faculty that does

Question

Write an SQL query that returns the facultyid and name of any faculty that does not teach any course but has a salary that is higher than 80,000.

book bookid title Introduction of Algorithms rice total copies 84.66 Database System Concepts74.99 Stochastic Calculus for Financ41.02 Stochastic Calculus for Finance II 55.22 course title Algorithms DB Organization Advanced DB Organization Math Finance I Math Finance II faculty enroll studentid courseid student studentid facultyid name gpa Tom 3.3 John3.8 Mary 3.0 Kris 3.6 Alex3.5 James 70000 Sarah 60000 ay 80000 Rache 70000 Pau85000 book_ checkout bookid studentid 2017-08-29 2017-09-02 2017-09-07

Explanation / Answer

SELECT facultyid,name FROM faculty WHERE salary > 80000 AND facultyid NOT IN (SELECT DISTINCT instructorid FROM course);

Explanation: The inner query will return all the distinct instructor ids from the course table. This means the ids of the instructtors who at least teach one course. Our facultyid should not be in that set, and the faculty's salary should be higher than 80000. In our case, the result will be:

facultyid name
----------- --------
5 Paul

Please let me know through the comment in case of any doubt. Thank you!

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.