1(a). Write an inner join that joins rows in the Departments table to their corresponding rows in the Faculty table. In the result set, only include the department ID, department name, faculty ID, faculty last name, faculty first name, and faculty highest degree earned values. Make sure that each table has an alias.

SOLUTION:

2. In detail, explain why all references to the DepartmentID column must include the alias created for either the Departments table or the Faculty table.

Answer:

2(a). Write a query that will produce a Cartesian result between the Students and Faculty tables.

SOLUTION:

2. Explain why the Cartesian result from part a) returns 715 records.

Answer:

3. One of the columns in the Students table is StudentMentorID. For each row, the StudentMentorID column value contains the unique student id of the students mentor. If the StudentMentorID column is NULL, the student has not been assigned a mentor. Write a query that returns the student ID, student last name, student first name and GPA of each student who has not been assigned a mentor.

SOLUTION:

4. Joe Faulkner, whose faculty ID is 5000008, has asked that the max class size for just his ENG120 classes be decreased by 5. Before the change is implemented, the Academic Dean would like to see how this would have affected classes to which he has already been assigned. Write a query that returns the faculty ID, course ID, course title, the current max class size and the proposed new max class size of Joes composition classes. Make sure that the query only returns the requested information and includes a column alias for the calculated new max class size and table aliases.

SOLUTION: