4. List campuses that are represented only by adjuncts, along with the names of the adjunct faculty members who teach at those campuses. 3. List all courses by reference number. If the course is taught by a full-time faculty member, list the first and last name of the faculty member. If the course is not taught by a full time faculty member, list just the reference number and NULL for the first and last names. Order results by last name. Assume there can be multiple sections of a course and a single faculty member may teach more than one section. 100 % Write queries to solve the following problems. All problems require reference to two or more tables. Refer to the Completed Assignment Example in the Week 1 Notes for information on submitting each problem. You will be using the attached Faculty database for Assignment 4. 1. Select all fields in the Faculty table for full time faculty members with any courses scheduled. List each faculty member only once. 100 % Results Bl Messages Faculty_ID First Name Last Name Campus 1 1 Mike Bloomberg Business 2 2 Adam Smith Economics 3 3 Bill Shakespeare English 4 5 AL Einstein Science Results Bi Messages Campus First Name North Tom Cecil Patrick 1 Last Name Brady Mahomes 2 100 % Results el Messages First Name Last Name Ref_Number 1 NULL NULL 12345 2 NULL NULL 90908 3 NULL NULL 90908 4 Mike Bloomberg 96032 5 Mike Bloomberg 13524 6 Mike Bloomberg 24653 7 A Einstein 98765 8 Al 81256 9 Bill Shakespeare 54321 10 Bill Shakespeare 90908 11 Bill Shakespeare 90908 12 Adam Smith 64321 13 Adam Smith 14862 5. The dean wants to reassign all courses taught by adjuncts to full time faculty members that teach at the same campuses. List first and last names of all adjuncts that have campuses in common with full time faculty members. If the adjunct faculty member is scheduled to teach a course, list the reference number along with the faculty name. If the faculty member is not scheduled to teach a class, display NULL. Assume there may be multiple sections of a course and a single faculty member may teach more than one section. Hint: You will need two joins for this one. Einstein 2. Select first and last names of all full-time faculty members. If they have courses, list the reference number for each course. For faculty members who aren't teaching any courses, display NULL for the course reference number. 2 Results Bi Messages First Name Last Name Ref_Number Aaron Rogers 12345 Aaron Rogers 90908 Drew Brees NULL Jared Goff NULL Trevor Lawrence NULL 4. Select the first last names of all full-time faculty members along with the reference numbers of courses they are teaching. Display NULL for the reference number if the full-time faculty member is not teaching any courses. In the same result set, list all reference numbers for which there is no associated full time faculty member and NULL for the first/last name. 3 4 5 100 % Results Bi Messages First Name Last Name 1 Mike Bloomberg 2 Mike Bloomberg 3 Mike Bloomberg 4 Adam Smith 5 Adam Smith 6 Bill Shakespeare 7 Bill Shakespeare 8 Bill Shakespeare 9 Lynn Euler 10 AL Einstein 11 Al Einstein Ref_Number 13524 24653 96032 14862 64321 54321 90908 90908 NULL 98765 81256 6. Display the first and last names of all faculty members (full time and adjunct) from the Kent campus who teach courses with a total revenue of more than $50,000. IU 100% Results ei Messages First Name Last Name Ref_Number 1 NULL NULL 12345 2 Bill Shakespeare 54321 3 Mike Bloomberg 13524 4 Mike Bloomberg 24653 5 A Einstein 98765 6 Adam Smith 14862 Mike 96032 8 A Einstein 81256 9 Adam Smith 64321 10 NULL NULL 90908 11 Bill Shakespeare 90908 12 Bill Shakespeare 90908 13 NULL NULL 90908 14 Lynn Euler NULL Results Bi Messages First Name Last Name Aaron Rogers Mike Bloomberg Bloomberg 1 2 4. List campuses that are represented only by adjuncts, along with the names of the adjunct faculty members who teach at those campuses. 3. List all courses by reference number. If the course is taught by a full-time faculty member, list the first and last name of the faculty member. If the course is not taught by a full time faculty member, list just the reference number and NULL for the first and last names. Order results by last name. Assume there can be multiple sections of a course and a single faculty member may teach more than one section. 100 % Write queries to solve the following problems. All problems require reference to two or more tables. Refer to the Completed Assignment Example in the Week 1 Notes for information on submitting each problem. You will be using the attached Faculty database for Assignment 4. 1. Select all fields in the Faculty table for full time faculty members with any courses scheduled. List each faculty member only once. 100 % Results Bl Messages Faculty_ID First Name Last Name Campus 1 1 Mike Bloomberg Business 2 2 Adam Smith Economics 3 3 Bill Shakespeare English 4 5 AL Einstein Science Results Bi Messages Campus First Name North Tom Cecil Patrick 1 Last Name Brady Mahomes 2 100 % Results el Messages First Name Last Name Ref_Number 1 NULL NULL 12345 2 NULL NULL 90908 3 NULL NULL 90908 4 Mike Bloomberg 96032 5 Mike Bloomberg 13524 6 Mike Bloomberg 24653 7 A Einstein 98765 8 Al 81256 9 Bill Shakespeare 54321 10 Bill Shakespeare 90908 11 Bill Shakespeare 90908 12 Adam Smith 64321 13 Adam Smith 14862 5. The dean wants to reassign all courses taught by adjuncts to full time faculty members that teach at the same campuses. List first and last names of all adjuncts that have campuses in common with full time faculty members. If the adjunct faculty member is scheduled to teach a course, list the reference number along with the faculty name. If the faculty member is not scheduled to teach a class, display NULL. Assume there may be multiple sections of a course and a single faculty member may teach more than one section. Hint: You will need two joins for this one. Einstein 2. Select first and last names of all full-time faculty members. If they have courses, list the reference number for each course. For faculty members who aren't teaching any courses, display NULL for the course reference number. 2 Results Bi Messages First Name Last Name Ref_Number Aaron Rogers 12345 Aaron Rogers 90908 Drew Brees NULL Jared Goff NULL Trevor Lawrence NULL 4. Select the first last names of all full-time faculty members along with the reference numbers of courses they are teaching. Display NULL for the reference number if the full-time faculty member is not teaching any courses. In the same result set, list all reference numbers for which there is no associated full time faculty member and NULL for the first/last name. 3 4 5 100 % Results Bi Messages First Name Last Name 1 Mike Bloomberg 2 Mike Bloomberg 3 Mike Bloomberg 4 Adam Smith 5 Adam Smith 6 Bill Shakespeare 7 Bill Shakespeare 8 Bill Shakespeare 9 Lynn Euler 10 AL Einstein 11 Al Einstein Ref_Number 13524 24653 96032 14862 64321 54321 90908 90908 NULL 98765 81256 6. Display the first and last names of all faculty members (full time and adjunct) from the Kent campus who teach courses with a total revenue of more than $50,000. IU 100% Results ei Messages First Name Last Name Ref_Number 1 NULL NULL 12345 2 Bill Shakespeare 54321 3 Mike Bloomberg 13524 4 Mike Bloomberg 24653 5 A Einstein 98765 6 Adam Smith 14862 Mike 96032 8 A Einstein 81256 9 Adam Smith 64321 10 NULL NULL 90908 11 Bill Shakespeare 90908 12 Bill Shakespeare 90908 13 NULL NULL 90908 14 Lynn Euler NULL Results Bi Messages First Name Last Name Aaron Rogers Mike Bloomberg Bloomberg 1 2