Homework SourseCode using SQL 1 For any faculty member whose supervisor is
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_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 | 2143 |
Solution
/* 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\');
