SQL Query: Count Instructors per Department (No Subqueries)

SELECT d.name, COUNT(i.name) as num_instructors FROM department d LEFT JOIN instructor i ON d.dept_id = i.dept_id GROUP BY d.name;

To display 'null' for departments with no instructors, you can modify the COUNT function to handle NULL values:

SELECT d.name, COUNT(DISTINCT i.name) as num_instructors FROM department d LEFT JOIN instructor i ON d.dept_id = i.dept_id GROUP BY d.name;