Write the following queries in SQL:
Display a list of all instructors, showing their ID, name, and the number of sections that they have taught. Make sure to show the number of sections as 0 for instructors who have not taught any section. Your query should use an outer join, and should not use scalar subqueries.

By using the university schema provided by db-book.com the following queries were done on the university database. The first query uses an outer join which works similar to the join operation but it keeps the rows that don’t match between the two tables that would be lost in a join operation. There are three forms of outer join: a) Left outer join displays the results from the left table even if the condition does not find any matching record in the right table. b) Right outer join will displays the results from the right table regardless if there is matching data in the left table. c) Full outer join will retain all rows from both tables, regardless if the data matches or not.

The group by clause when used in a select statement collects data from multiple records and groups the results into one or more columns.

The below is the query using left outer join operation: select ID, name, count(sec_id) as Number_of_sections from instructor natural left outer join teaches group by ID, name;
The next query was written by using a scalar subquery without using an outer join operation. A scalar subquery is where the output of a subquery returns only one row which contains one single column/attribute. By using the select statement with count (*) this guarantees that the query will select one column/attribute and returns the value in just one row. Scalar subqueries may occur in select, where, and having clauses (Silberschatz, Korth & Sudarshan, 2010).

The below query represent scalar subquery: select ID, name,
(select count(*) from teaches B where B.id=F.id) as Number_of_sections from instructor F;

The results (see below) for both queries are the same – each query extracted twelve instructor records from the database. It counted and combined sections that the instructors had taught and display instructors who have not taught any section thus far.

ID | name | Number_of_sections | 10101 | Srinivasan | 3 | 12121 | Wu | 1 | 15151 | Mozart | 1 | 22222 | Einstein | 1 | 32343 | El Said | 1 | 33456 | Gold | 0 | 45565 | Katz | 2 | 58583 | Califieri | 0 | 76543 | Singh | 0 | 76766 | Crick | 2 | 83821 | Brandt | 3 | 98345 | Kim | 1 |