Multi-Table Queries: Exercise
- Problem: How can we find duplicate last names, in the combined list of last names from both the students and students tables?
- Approach: We must get the count of each last name. Then we will know that any last name with a count > 1 is a duplicate.
- Begin by looking at how to get all the last names of both the students and professors tables from the week2 database, including duplicates.
EXAMPLE 2
- Retrieve a result set that shows the occupation (student/professor), first name, last name, and department code for all students and professors.
We currently do not yet have the occupation nor the department code columns.
- This syntax sets a string literal, either ‘professor’ or ‘student’, as the value for a column aliased as occupation.
- We still have to solve for the department code, cased on the department id.
- This syntax creates a CTE named people from the result set of the union between the professors and students tables.
- SELECT from the temporary people table and JOIN it with the departments table, then retrieve the code for each department.
- However, this result set does not include students who have not yet declared a major.
- To include those students who have a null value for their major department, we must use a LEFT JOIN
Notice null values.
