I need help code SQL for these three questions: For any faculty member whose sup

Question

I need help code SQL for these three questions:

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.

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.

Which students (by ID number) are from Idaho or have registered for section 2717?

L D qualified faculty id Sourse id date qualified student 1023 ISM 3112 911985 student id student first student last home town home state gpa major Class 911985 12123 Ari Smith Boise CD TNM Dunior Course 1023 ISM 5003 Schmidt Meridian ID 260 TM Freshman 12345 Dre course id Course name 124 ISM 5001 911991 15687 sydney Wilson idaho City DD 3.50 HIIM Freshman ISM 312 Systems Analysis i 2143 ISM 3112 911988 22255 Sandra Sharda Boise ID 3.80 2143 DSM 3114 911998 ystems Design Martin Portland OR 3.15 HIM Sophomore 23232 Dav ISM 3114 Analytics 912000 2319 ISM 5001 23456 Ashleigh George Vancouver Locke og den UT 2.75 scM Junior 24654 Sarah ISM 4930 Networking Eection registration faculty 12123 2716 2712 I-2006 ISM 3113 MBE 8210 faculty id faculty last faculty first rank department Salary Supervisor id 12123 2723 2713 2006 DSM 3113 $150,000.00 12345 2715 2714 2006 ISM 4212 MBEB 206 2715 2006 ISM 4930 2143 dirkin Debra Full Informiton Systems S85.000,00 1000 12345 2723 2716 -2006 ISM 5000 MBE B210 2319 Lysne 10hn Associate Accounting $100,000.00 2717 I-2006 DSM 5000 234S Smith Phil Full Management S90,000.00 5687 2717 2718 -2007 ISM 5000 MBEB 210 4756 Collins Rick Associate Management $85,000.00 2345 15687 2724 15687 2734 5678 Bottel Dave Associate Information Systems $80,000.00 2143 15687 2735 6547 Lopez Judy Full Accounting $100,000.00 1000 18632 2717 22255 2716 8901 Robbins Becky Associate Management $90,000.00 2345 22255 2718 22255 2736

Explanation / Answer

select CONCAT(f.faculty_first,’ ‘,f.faculty_last) as faculty_name, f.department as department,

CONCAT(s.faculty_first,’ ‘,s.faculty_last) as supervisor_name

from faculty f

join

faculty s

on s.faculty_id = f.supervisor_id

and

f.department=s.department

select CONCAT(f.faculty_first,’ ‘,f.faculty_last) as faculty_name, f.rank as rank,

CONCAT(s.faculty_first,’ ‘,s.faculty_last) as supervisor_name

from faculty f

join

faculty s

on s.faculty_id = f.supervisor_id

and

f.rank=s.rank

select s.student_id from student s join registration r

on s.student_id = r.studentid

where s.home_state = ‘ID’

and r.section_no = 2717

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.