SQL JOINS on HR Database: Display the first name, last name, and department number for those employees who works in the same department as the employee who holds the last name as Taylor

SQL JOINS on HR Database: Exercise-12 with Solution

12. From the following tables, write a SQL query to find those employees who work in a department where the employee of last name 'Taylor' works. Return first name, last name and department ID.

Sample table: employees

Sample Solution:

SELECT E.first_name, E.last_name, E.department_id 
 FROM employees E 
   JOIN employees S
     ON E.department_id = S.department_id
       AND S.last_name = 'Taylor';

Sample Output:

first_name	last_name	department_id
Matthew		Weiss		50
Adam		Fripp		50
Payam		Kaufling	50
Shanta		Vollman		50
Kevin		Mourgos		50
Julia		Nayer		50
Irene		Mikkilineni	50
James		Landry		50
Steven		Markle		50
Laura		Bissot		50
Mozhe		Atkinson	50
James		Marlow		50
TJ		Olson		50
Jason		Mallin		50
Michael		Rogers		50
Ki		Gee		50
Hazel		Philtanker	50
Renske		Ladwig		50
Stephen		Stiles		50
John		Seo		50
Joshua		Patel		50
Trenna		Rajs		50
Curtis		Davies		50
Randall		Matos		50
Peter		Vargas		50
John		Russell		80
Karen		Partners	80
Alberto		Errazuriz	80
Gerald		Cambrault	80
Eleni		Zlotkey		80
Peter		Tucker		80
David		Bernstein	80
Peter		Hall		80
Christopher	Olsen		80
Nanette		Cambrault	80
Oliver		Tuvault		80
Janette		King		80
Patrick		Sully		80
Allan		McEwen		80
Lindsey		Smith		80
Louise		Doran		80
Sarath		Sewall		80
Clara		Vishney		80
Danielle	Greene		80
Mattea		Marvins		80
David		Lee		80
Sundar		Ande		80
Amit		Banda		80
Lisa		Ozer		80
Harrison	Bloom		80
Tayler		Fox		80
William		Smith		80
Elizabeth	Bates		80
Sundita		Kumar		80
Ellen		Abel		80
Alyssa		Hutton		80
Jonathon	Taylor		80
Jack		Livingston	80
Charles		Johnson		80
Winston		Taylor		50
Jean		Fleaur		50
Martha		Sullivan	50
Girard		Geoni		50
Nandita		Sarchand	50
Alexis		Bull		50
Julia		Dellinger	50
Anthony		Cabrio		50
Kelly		Chung		50
Jennifer	Dilly		50
Timothy		Gates		50
Randall		Perkins		50
Sarah		Bell		50
Britney		Everett		50
Samuel		McCain		50
Vance		Jones		50
Alana		Walsh		50
Kevin		Feeney		50
Donald		OConnell	50
Douglas		Grant		50

Relational Algebra Expression:

Relational Algebra Tree:

Pictorial Presentation:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following table, write a SQL query to find the employees and their managers. These managers do not work under any manager. Return the first name of the employee and manager.
Next: From the following tables, write a SQL query to find those employees who joined on 1st January 1993 and leave on or before 31 August 1997. Return job title, department name, employee name, and joining date of the job.