Code using SQL 1. For any faculty member whose supervisor is in the same departm
ID: 3779749 • Letter: C
Question
Code using SQL
1. For any faculty member whose supervisor is in the same department, list the name of the faculty member, the faculty member's department, the name of his or her supervisor.
2. Which faculty members have the same rank as their supervisor? List the name of the faculty member, the rank of the faculty member and the name of his or her supervisor.
3. Which freshmen are on the waiting list directly before seniors?
4. Which student is directly after Peter Garcia on the waiting list?
faculty faculty_id faculty_last faculty_first rank department salary supervisor_id 1000 Doe John Full Administration $150,000.00 1023 Norris Bob Associate marketing $80,000.00 1000 1234 Myers Susan Assistant marketing $70,000.00 1023 2143 Birkin Debra Full Information Systems $85,000.00 1000 2319 Lysne John Associate Accounting $100,000.00 6547 2345 Smith Phil Full Management $90,000.00 1000 3467 Berndt Mike Assistant marketing $75,000.00 1023 4567 Anderson Mary Associate Information Systems $90,000.00 2143 4756 Collins Rick Associate Management $85,000.00 2345 5678 Bottelberghe Dave Associate Information Systems $80,000.00 2143 6547 Lopez Judy Full Accounting $100,000.00 1000 7890 Spears Bill Assistant Accounting $105,000.00 6547 8901 Robbins Becky Associate Management $90,000.00 2345 9012 Plesant Tom Full Information Systems $95,000.00 2143Explanation / Answer
/* 1. For any faculty member whose supervisor is in the same department, list the name of the
faculty member, the faculty member's department, the name of his or her supervisor. */
select concat(a.faculty_last,' ',a.faculty_first) as faculty_name, department,
concat(b.faculty_last,' ',b.faculty_first) as supervisor_name
from faculty as A inner join faculty as B
on a.supervisor_id=b.faculty_id and a.department=b.department;
/* 2. Which faculty members have the same rank as their supervisor? List the name of the faculty
member, the rank of the faculty member and the name of his or her supervisor.*/
select concat(a.faculty_last,' ',a.faculty_first) as faculty_name, rank,
concat(b.faculty_last,' ',b.faculty_first) as supervisor_name
from faculty as A inner join faculty as B
on a.faculty_id=b.supervisor_id and a.rank=b.rank;
/* 3. Which freshmen are on the waiting list directly before seniors? */
select student_id, concat(student_first,' ',student_last) as student_name, class
from student_wait_list
where student_id = (select after_which_id
from student_wait_list
where class='senior');
/* 4. Which student is directly after Peter Garcia on the waiting list? */
select student_id, concat(student_first,' ',student_last) as student_name
from student_wait_list
where after_which_id = (select student_id from student_wait_list
where student_first = 'Peter' and student_last='Garcia');